5.1 MySQL extensions to ANSI SQL92

MySQL includes some extensions that you probably will not find in other SQL databases. Be warned that if you use them, your code will not be portable to other SQL servers. In some cases, you can write code that includes MySQL extensions, but is still portable, by using comments of the form /*! ... */. In this case, MySQL will parse and execute the code within the comment as it would any other MySQL statement, but other SQL servers will ignore the extensions. For example: 

SELECT /*! STRAIGHT_JOIN */ col_name FROM table1,table2 WHERE ...

If you add a version number after the '!', the syntax will only be executed if the MySQL version is equal or newer than the used version number: 

CREATE /*!32302 TEMPORARY */ TABLE (a int);

The above means that if you have 3.23.02 or newer, then MySQL will use the TEMPORARY keyword.

MySQL extensions are listed below:

  • The field types MEDIUMINT, SET, ENUM and the different BLOB and TEXT types.
  • The field attributes AUTO_INCREMENT, BINARY, UNSIGNED and ZEROFILL.
  • All string comparisons are case insensitive by default, with sort ordering determined by the current character set (ISO-8859-1 Latin1 by default). If you don't like this, you should declare your columns with the BINARY attribute or use the BINARY cast, which causes comparisons to be done according to the ASCII order used on the MySQL server host.
  • MySQL maps each database to a directory under the MySQL data directory, and tables within a database to filenames in the database directory. This has two implications:
    • Database names and table names are case sensitive in MySQL on operating systems that have case sensitive filenames (like most Unix systems). If you have a problem remembering table names, adopt a consistent convention, such as always creating databases and tables using lowercase names.
    • Database, table, index, column or alias names may begin with a digit (but may not consist solely of digits).
    • You can use standard system commands to backup, rename, move, delete and copy tables. For example, to rename a table, rename the `.ISD', `.ISM' and `.frm' files to which the table corresponds.
  • In SQL statements, you can access tables from different databases with the db_name.tbl_name syntax. Some SQL servers provide the same functionality but call this User space. MySQL dosen't support tablespaces like in: create table ralph.my_table...IN my_tablespace.
  • LIKE is allowed on numeric columns.
  • Use of INTO OUTFILE and STRAIGHT_JOIN in a SELECT statement. SELECT.
  • The SQL_SMALL_RESULT option in a SELECT statement.
  • EXPLAIN SELECT to get a description on how tables are joined.
  • Use of index names, indexes on a prefix of a field, and use of INDEX or KEY in a CREATE TABLE statement. CREATE TABLE.
  • Use of TEMPORARY or IF NOT EXISTS with CREATE TABLE.
  • Use of COUNT(DISTINCT list) where 'list' is more than one element.
  • Use of CHANGE col_name, DROP col_name or DROP INDEX in an ALTER TABLE statement. ALTER TABLE.
  • Use of IGNORE in an ALTER TABLE statement.
  • Use of multiple ADD, ALTER, DROP or CHANGE clauses in an ALTER TABLE statement.
  • Use of DROP TABLE with the keywords IF EXISTS.
  • You can drop multiple tables with a single DROP TABLE statement.
  • The LIMIT clause of the DELETE statement.
  • The DELAYED clause of the INSERT and REPLACE statements.
  • The LOW_PRIORITY clause of the INSERT, REPLACE, DELETE and UPDATE statements.
  • Use of LOAD DATA INFILE. In many cases, this syntax is compatible with Oracle's LOAD DATA INFILE. LOAD DATA.
  • The OPTIMIZE TABLE statement. OPTIMIZE TABLE.
  • The SHOW statement. SHOW.
  • Strings may be enclosed by either `"' or `'', not just by `''.
  • Use of the escape `\' character.
  • The SET OPTION statement. SET OPTION.
  • You don't need to name all selected columns in the GROUP BY part. This gives better performance for some very specific, but quite normal queries. 7.3.13 Functions for use with GROUP BY clauses.
  • To make it easier for users that come from other SQL environments, MySQL supports aliases for many functions. For example, all string functions support both ANSI SQL syntax and ODBC syntax.
  • MySQL understands the || and && operators to mean logical OR and AND, as in the C programming language. In MySQL, || and OR are synonyms, as are && and AND. Because of this nice syntax, MySQL doesn't support the ANSI SQL || operator for string concatenation; use CONCAT() instead. Since CONCAT() takes any number of arguments, it's easy to convert use of the || operator to MySQL.
  • CREATE DATABASE or DROP DATABASE. CREATE DATABASE.
  • The % operator is a synonym for MOD(). That is, N % M is equivalent to MOD(N,M). % is supported for C programmers and for compatibility with PostgreSQL.
  • The =, <>, <= ,<, >=,>, <<, >>, <=>, AND, OR or LIKE operators may be used in column comparisons to the left of the FROM in SELECT statements. For example: 
    mysql> SELECT col1=1 AND col2=2 FROM tbl_name;
  • The LAST_INSERT_ID() function. mysql_insert_id().
  • The REGEXP and NOT REGEXP extended regular expression operators.
  • CONCAT() or CHAR() with one argument or more than two arguments. (In MySQL, these functions can take any number of arguments.)
  • The BIT_COUNT(), CASE, ELT(), FROM_DAYS(), FORMAT(), IF(), PASSWORD(), ENCRYPT(), md5(), ENCODE(), DECODE(), PERIOD_ADD(), PERIOD_DIFF(), TO_DAYS(), or WEEKDAY() functions.
  • Use of TRIM() to trim substrings. ANSI SQL only supports removal of single characters.
  • The GROUP BY functions STD(), BIT_OR() and BIT_AND().
  • Use of REPLACE instead of DELETE + INSERT. REPLACE.
  • The FLUSH flush_option statement.