MySQL Tutorials

MySQL and other database engines have reserved words which should not be used as database, table, or field names. A work-around for this is that MySQL recommends using the backtick character (the one right below escape, to the left of number 1 on top left corner of keyboard). For example, you can create a table called `select` – see it's flanked by backticks. This is bad since not all db engines like it. Don't do it, just use non-reserved words.

Interestingly, in PHP the query string SHOULD NOT end with semicolon: http://us3.php.net/function.mysql-query.

MySQL Data Types

Numeric Data Types

Name Range Attributes Default
TINYINT[(M)] -128 TO 127
[0 to 255 if UNSIGNED]
AUTO_INCREMENT,
UNSIGNED, ZEROFILL
NULL
[0 if NOT NULL]
SMALLINT[(M)] -32,768 to 32,767
[0 to 65,535]
AUTO_INCREMENT,
UNSIGNED, ZEROFILL
NULL
[0 if NOT NULL]
MEDIUMINT[(M)] -8,388,608 to 8,388,607
[0 to 16,777,215]
AUTO_INCREMENT,
UNSIGNED, ZEROFILL
NULL
[0 if NOT NULL]
INT[(M)] -2,147,483,648 to 2,147,483,647
[0 to 4,294,967,295]
AUTO_INCREMENT,
UNSIGNED, ZEROFILL
NULL
[0 if NOT NULL]
BIGINT[(M)] -/+9,223,372,036,854,775,807
[0 to 18,446,744,073,709,551,615]
AUTO_INCREMENT,
UNSIGNED, ZEROFILL
NULL
[0 if NOT NULL]
FLOAT[(M,D)] Min=+/-1.175E-38
Max=+/-3.403E+38
ZEROFILL NULL
[0 if NOT NULL]
DOUBLE[(M,D)] Min=+/-2.225E-308
Max=+/-1.800E+308
ZEROFILL NULL
[0 if NOT NULL]
DECIMAL(M,D)
Stored as string
Max Range = DOUBLE range ZEROFILL NULL
[0 if NOT NULL]

String Data Types

Name Range Attributes Default
CHAR(M) M=0-255 Characters
Right Padded
BINARY NULL
[“” if NOT NULL]
VARCHAR(M) M=0-255 Characters before v5.0.3 and 0-65,535 Char after v5.0.3
Trailing spaces removed
BINARY NULL
[“” if NOT NULL]
TINYBLOB 0-255 bytes (case sensitive) NULL
[“” if NOT NULL]
BLOB 0-65,535 bytes (case sensitive) NULL
[“” if NOT NULL]
MEDIUMBLOB 0-16,777,215 bytes (case sensitive) NULL
[“” if NOT NULL]
LONGBLOB 0-4,294,967,295 bytes (case sensitive) NULL
[“” if NOT NULL]
TINYTEXT 0-255 bytes (not case sensitive) NULL
[“” if NOT NULL]
TEXT 0-65,535 bytes (not case sensitive) NULL
[“” if NOT NULL]
MEDIUMTEXT 0-16,777,215 bytes (not case sensitive) NULL
[“” if NOT NULL]
LONGTEXT 0-4,294,967,295 (not case sensitive) NULL
[“” if NOT NULL]
ENUM
(“A1”,“A2”,…)
Column is exactly 1 of values 1-255 values (not case sensitive) NULL
[“” if NOT NULL]
SET
(“A1”,“A2”,…)
Column is 0 or more values in list 1-64 members (not case sensitive) NULL
[“” if NOT NULL]

Date & Time Data Types

Name Range Attributes Default
DATE “1000-01-01” - “9999-12-31” (YYYY-MM-DD) NULL
[“0000-00-00” if NOT NULL]
TIME “-838:59:59” - “838:59:59” (hh:mm:ss) NULL
[“00:00:00” if NOT NULL]
DATETIME “1000-01-01 00:00:00” -
“9999-12-31 23:59:59”
(YYYY-MM-DD hh:mm:ss) NULL [“0000-00-00 00:00:00”
if NOT NULL]
TIMESTAMP 19700101000000 -
2037+
(YYYYMMDDhhmmss) Current Date & Time
YEAR 1900 - 2155 (YYYY) NULL
[“0000” if NOT NULL]

Useful Tips

If deleting a record and you want to reset the AUTO_INCREMENT value.

ALTER TABLE tablename AUTO_INCREMENT = 1;
# May SET the auto INCREMENT TO the NUMBER you want TO START WITH OR reset it TO 0.
# MySQL will just START WITH NEXT available NUMBER IN either CASE.
 
# Don't use SET insert_id to just change the immediate next INSERT query, it only works on MyISAM and not InnoDB tables.

The ALTER TABLE query is very useful for many things, such as ADD/DROP fields, indexes (PRIMARY KEY, UNIQUE, FULLTEXT), MODIFY type of field, CHANGE name and type of field. See manual.

Useful instead of transactions “BEGIN” and “COMMIT”, and much faster. Google it and learn more about locking tables in MYISAM.

You normally cannot run multiple statements with php. Use below trick, which is very useful to run multiple statements to insert/update multiple related tables (e.g. invoice and invoice-products).

Originally from http://www.dev-explorer.com/articles/multiple-mysql-queries

/** VERY USEFUL FOR MULTIPLE TABLE INSERT/UPDATES **/
$sql = "LOCK TABLES parentTable WRITE, childTable1 WRITE, childTable2 WRITE;";
$sql .= "INSERT INTO parentTable (autoincrement_id, fld2, fld3) 
	VALUES (NULL, 'bar', 'bam');"; 
// each sql statement must have ending semicolon for preg_split, except for last statement
$sql .= "INSERT INTO childTable1 (foreignkey2parentTable, field2, field3) 
	 VALUES (LAST_INSERT_ID(),'moo','bii'),
		(LAST_INSERT_ID(),'moo2','bii2');"; // LAST_INSERT_ID() is the last parentTable.autoincrement_id.
$sql .= "INSERT INTO childTable2 (foreignkey2parentTable, field, field1) 
	VALUES (LAST_INSERT_ID(),'fou','bam');"; 
$sql .= "UNLOCK TABLES";  // The last sql statement doesn't need the ending semilogin.
 
// below preg_split uses "look ahead" regex denoted by (?=(regex))
$queries = preg_split("/;+(?=([^'|^\\\']*['|\\\'][^'|^\\\']*['|\\\'])*[^'|^\\\']*[^'|^\\\']$)/", $sql);
foreach ($queries as $query){
	   if (strlen(trim($query)) > 0) mysql_unbuffered_query($query); // or use mysql_query()
}

Foreign Key

Foreign key constraints works well all the time only in InnoDB table format (see http://dev.mysql.com/tech-resources/articles/mysql-enforcing-foreign-keys.html). In case creating a foreign key relation in the child table gives the error 1452, below sql can help you find exactly which child.id (table child , field id) has some rows with info that's or NOT IN in parent.id. FYI, NOT IN is faster than doing a LEFT JOIN to find the mismatched info (see http://stackoverflow.com/questions/1253459/mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa)

SELECT DISTINCT id_field FROM child_table 
WHERE id_field NOT IN (SELECT id_field FROM parent_table);

Then you can search for each id, and either delete the row or update them to an id that exists in parent.id.

If you get error 1215, then more than likely the data types are a bid different, usually foreign keys are of INT type, indexed, and make sure both are either UNSIGNED (most likely) or SIGNED (not necessary most of the time).

InnoDB vs MyISAM

For comparison between InnoDB and MyISAM see http://en.wikipedia.org/wiki/Comparison_of_MySQL_database_engines

Quick pick, from http://stackoverflow.com/questions/20148/myisam-versus-innodb or http://developer99.blogspot.com/2011/07/mysql-innodb-vs-myisam.html

                                                 MyISAM   InnoDB
----------------------------------------------------------------
Required full-text search                        Yes      5.6.4
----------------------------------------------------------------
Require transactions                                      Yes
----------------------------------------------------------------
Frequent select queries                          Yes      
----------------------------------------------------------------
Frequent insert, update, delete                           Yes
----------------------------------------------------------------
Row locking (multi processing on single table)            Yes
----------------------------------------------------------------
Relational base design                                    Yes
To summarize:

Frequent reading, almost no writing   => MyISAM
Full-text search in MySQL <= 5.5      => MyISAM

In all other circumstances, InnoDB is usually the best way to go.