InnoDB is used in production at numerous large database sites requiring high performance.
Tag Archives: MySQL
list storage engines – mysql
MyISAM Storage Engine
Storage engine should be mentioned when we careate tables
CREATE TABLE table (no INT) ENGINE = MYISAM;
Normally, it is unnecessary to use ENGINE to specify the MyISAM storage engine. MyISAM is the default engine unless the default has been changed. To ensure that MyISAM is used in situations where the default might have been changed, include the ENGINE option explicitly.
You can check or repair MyISAM tables with the mysqlcheck client or myisamchk utility.
All data values are stored with the low byte first. This makes the data machine and operating system independent.
All numeric key values are stored with the high byte first to permit better index compression.
Large files (up to 63-bit file length) are supported on file systems and operating systems that support large files.
There is a limit of 232 rows in a MyISAM table. If you build MySQL with the
The maximum number of indexes per MyISAM table is 64.
The maximum number of columns per index is 16.
The maximum key length is 1000 bytes.
When rows are inserted in sorted order ,the index tree is split so that the high node only contains one key. This improves space utilization in the index tree.
MyISAM supports concurrent inserts:
You can put the data file and index file in different directories on different physical devices
BLOB and TEXT columns can be indexed.
NULL values are permitted in indexed columns.
There is a flag in the MyISAM index file that indicates whether the table was closed correctly.
The sum of the lengths of the VARCHAR and CHAR columns in a table may be up to 64KB.
Storage Engines
mysql functions
- mysql_affected_rows – Returns the number affected rows in the last mysql operation
- mysql_client_encoding – Returns the name of the character set
- mysql_close – Close the mysql connection
- mysql_connect – Establish a connection to the mysql server
- mysql_create_db – Create a new database
- mysql_data_seek – move the pointer to the specific row
- mysql_db_name – Returns the database name from selected databases
- mysql_db_query() – Its Deprecated. Sends a MySQL query
- mysql_drop_db – Delete the databse . Its depricated
- mysql_errno – Shows the numerical values of the error messages due to mysql operations
- mysql_error – Shows the error messages due to mysql operations
- mysql_escape_string – Modify the string to use in a mysql query
- mysql_fetch_array – Fetch the result both in a numeric array and associative array
- mysql_fetch_assoc – Fetch the result in a associative array
- mysql_fetch_field – Returns column information
- mysql_fetch_lengths– Returns length of each output
- mysql_fetch_object – Fetch the result as object
- mysql_fetch_row – Returns the rows in an array
- mysql_field_flags – Returns the flags associated with the latest result
- mysql_field_len – Returns the length of a specific field
- mysql_field_name – Return the name of the field in the result
- mysql_field_seek – Can be used to move the result pointer
- mysql_field_table – Returns the table name the specified field is in
- mysql_field_type – Returns the type of the specified field in the result
- mysql_free_result – Free the memory that the result used
- mysql_get_client_info – Returns mysql information
- mysql_get_host_info – Returns the host information
- mysql_get_proto_info – Returns the Protocol information
- mysql_get_server_info – Returns the Server information
- mysql_info – Returns the information about the last query
- mysql_insert_id – Return the id generated the previous query
- mysql_list_dbs – List all the databases
- mysql_list_fields – list all the fields in atable
- mysql_list_processes – List mysql process
- mysql_list_tables – List all the tables
- mysql_num_fields – Returns the number of fields in the result
- mysql_num_rows – Returns the number of rows in the result
- mysql_pconnect – Establish a persistent connection to a MySQL server
- mysql_ping – Can be used to the connection to the server
- mysql_query – send a query to the server
- mysql_real_escape_string – Escapes special characters that a query does not support
- mysql_result – Can be used to display result in a specified row
- mysql_select_db – Used to select a database
- mysql_set_charset – Can be used to set a character set
- mysql_stat – returns current status of the mysql server
- mysql_tablename – Returns the table name of a field
- mysql_thread_id – Returns current thread id
- mysql_unbuffered_query – Send the query to the server and buffering the result
Import data into csv – php
<?php
mysql_connect(“localhost”,”root”,”password”);
mysql_select_db(“phpcodez”); // Dont forget to put correct database name
$filename = “users-csv”.date(‘-Y-M-d-D-H-i-s’).”.csv”;
$fp = fopen($filename, ‘w’) or die (‘file cant be opened’);
$fieldsQry = mysql_query(“SHOW COLUMNS FROM user”);
while ($fields = mysql_fetch_assoc($fieldsQry)) {
$fieldNames[] = $fields[‘Field’];
}
fputcsv($fp, $fieldNames);
$usersQry = mysql_query(“SELECT * FROM user”);
while($userInfo=mysql_fetch_assoc($usersQry)){
fputcsv($fp, $userInfo);$i++;
}
system(“chmod 777 $filename”);
fclose($fp);
echo “Imported “.$i.” items”
?>
Column ‘ID’ in field list is ambiguous
it is just because you have 2 tables with both a field ID, so the database engine will get confused about the table name from which the the data to be fixed
Use table1.id, or table2.id, based on the table you want the data
Test if MySQL table exists – PHP
if(mysql_num_rows( mysql_query(“SHOW TABLES LIKE ‘table_name'”)))
echo ” Table exists “