- Temporary tables (which are tables designed to be specific to ONE CONNECTION) get replicated to slave databases if you are using statement- or mixed-format replication, wasting everyone's time and CPU. The documented solution is to designate a prefix for your temporary tables and configure the replication to ignore tables with that prefix. - Timestamp columns. Oh man, timestamp columns. The first column defined on a table with the TIMESTAMP type magically gets DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if you don't specify either. If a TIMESTAMP column is NOT NULL, you can assign it NULL to set it to the current timestamp, even in strict mode. - Until MySQL 5.6.5, you could only set DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP on a single TIMESTAMP column. A second TIMESTAMP column could not have either of these properties. This includes when the first TIMESTAMP column had these properties set implicitly. You could also not use these properties for DATETIME columns. - The DEFAULT property of a column can only be set to a constant, with a single exception: the CURRENT_TIMESTAMP function. And only TIMESTAMP or DATETIME columns can use this function as a default. - CURRENT_TIMESTAMP can be called (anywhere) with or without parentheses, for no apparent reason. It shares this property with a few other functions like CURRENT_DATE and LOCALTIME, but not NOW(). This is because these functions are defined this way by the SQL standard. - Built-in functions are not recognized unless immediately followed by parentheses, if they are required. Except for AVG, and any other built-in function that isn't in the defined list of functions affected by the IGNORE_SPACE setting which allows spaces before the following parentheses. - If you try to call COUNT with a space before the following parentheses without IGNORE_SPACE set, you get a syntax error. If you try it with MIN or MAX or SUM, it attempts to call an user-defined function of that name. There's no apparent reason for this disparity. - Various string functions return NULL and a warning when their return value exceeds the size of max_allowed_packet, a variable regulating the maximum packet buffer size. Regardless of whether the value is actually returned in a packet. This fact is considered "not a bug" and is barely documented. There is no SQL mode to promote this warning to an error. - The GROUP_CONCAT aggregate function will truncate to group_concat_max_len bytes (not characters) which defaults to 1024 bytes, and emit a warning if the result was over that size. There is also no SQL mode to promote this warning to an error. - If you send a query that exceeds the server's max_allowed_packet size, which defaults to a measly 4MB, the server closes the connection, resulting in the helpful error message "Lost connection to MySQL server during query". - Values of an ENUM column can be referenced by value or index interchangably. If you insert a number into an ENUM column, it will insert the value at that index unless it is either quoted or the number itself is defined as a value for that ENUM. Also, if you use an ENUM column value in a numeric context such as addition or SUM(), its index will be used. Just don't use ENUM columns for numbers. - Inserting an invalid value into an ENUM column results in a special empty string value with the index 0 when not using strict mode. This means a NOT NULL ENUM column can be set to the empty string in addition to its defined values. - The default 'latin1' character set is in fact cp1252, not ISO-8859-1, meaning it contains the extra characters in the Windows codepage. 'latin2', however, is ISO-8859-2. - The 'utf8' character set is limited to unicode characters that encode to 1-3 bytes in UTF-8. 'utf8mb4' was added in MySQL 5.5.3 and supports up to 4-byte encoded characters. UTF-8 has been defined to encode characters to up to 4 bytes since 2003. - Neither the 'utf8' nor 'utf8mb4' character sets have any case sensitive collation other than 'utf8_bin' and 'utf8mb4_bin', which sort characters by their numeric codepoint. - The invariant storage limits of TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT are in bytes. However, the required declared size of CHAR and VARCHAR types are in characters, regardless of encoding. - InnoDB indexes are arbitrarily limited to 767 bytes per column (not per index) normally. Since index prefix sizes, like CHAR and VARCHAR column sizes, are declared in characters and not bytes, the maximum prefix size for an index depends on the character set of the column it's indexing. - Inserting 0 into an AUTO_INCREMENT column will assign the next incremented value unless you enable the NO_AUTO_VALUE_ON_ZERO SQL mode. Inserting NULL will assign the next incremented value if the column is NOT NULL, regardless of SQL mode. - The TIMESTAMP data type is internally stored as a UTC epoch timestamp, so will continue to refer to the same moment in time regardless of the time_zone setting when retrieving it. The DATETIME data type on the other hand is a calendar date and time stored with no time zone information, so is not particularly useful for storage unless you know what time zone was used to create each value. - The DATE_FORMAT function takes a format that is just similar enough to strftime to be confusing. For example, %M means the full name of the month rather than its usual meaning of minutes. - To change any part of a column's definition, you have to redefine the column entirely. Changing the column type, whether it's nullable, auto_increment, or default requires specifying all of those things again. - The SQL standard defines a shortcut to specify a foreign key REFERENCES inline with a column specification. MySQL accepts and ignores this shortcut. - When a transaction fails due to a timeout in InnoDB, only the last statement is rolled back by default. You have to set innodb_rollback_on_timeout for the whole transaction to be rolled back, as occurs with a deadlock. This setting is still disabled by default in MySQL 8.0 and MariaDB.