What extensions has MySQL to ANSI SQL92?

The following are useful extensions in MySQL 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.

• 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 by default are case independent with case according to ISO-8859-1 Latin1. If you don't like this you should declare your strings with the BINARY attribute.
• MySQL maps all tables to filenames and with MySQL one can use standard system tools to backup, rename, move, delete and copy tables. This forces MySQL to be case sensitive on table names on operating systems that have case sensitive filenames (like most Unix systems). If you have a problem remembering table names, create everything in lowercase.
• LIKE is allowed on numerical columns.
• Use of INTO OUTFILE and STRAIGHT_JOIN in a SELECT statement. See section SELECT syntax.
• EXPLAIN SELECT to get a description on how tables are joined.
• Use of index names, indexes on a subpart of a field, and use of INDEX or KEY in a CREATE TABLE statement. See section CREATE TABLE syntax.
• Use of DROP column or CHANGE column in an ALTER TABLE statement. See section ALTER TABLE syntax.
• Use of LOAD DATA INFILE. This syntax is in many cases compatible with Oracle's LOAD DATA INFILE. See section LOAD DATA INFILE syntax.
• Using " instead of ' to enclose strings.
• Using the escape \ character.
• The SET OPTION statement. See section SET OPTION syntax.
• One doesn't have to have all columns in the GROUP BY part. See section Functions for GROUP BY clause.
• To make it easier for a user that comes from different SQL environments mysql supports a a lot of aliases for many functions. For example all string functions support both the ANSI SQL and the ODBC syntax.
• The || and && operators are, in MySQL, synonyms for OR and AND, like in the C programming language. Likewise | and & stand for bitwise OR and AND. Because of this nice syntax, MySQL doesn't support the ANSI SQL operator || for string concatenation, one has to use CONCAT() instead. As CONCAT() takes any number of arguments it's easy to convert use of the || operator to MySQL.
• Use of any of the following functions or commands:
  • CREATE DATABASE or DROP DATABASE. See section Create database syntax.
  • % instead of mod(). % is supported for C programmers and for compatibility with PostgreSQL.
  • =, <>, <= ,<, >=,>, AND, OR, or LIKE in a column statement
  • LAST_INSERT_ID. See section How can I get the unique ID for the last inserted row?
  • REGEXP or NOT REGEXP.
  • CONCAT() or CHAR() with one or more than two arguments. In MySQL the above functions can take any number of arguments.
  • BIT_COUNT(), ELT(), FROM_DAYS(), FORMAT(), IF(), PASSWORD(), ENCRYPT(), PERIOD_ADD(), PERIOD_DIFF(), TO_DAYS(), or WEEKDAY().
  • Use of TRIM to trim substrings. ANSI SQL only supports removal of single characters.
  • The STD(), BIT_OR and BIT_AND group functions.
  • Use of MIN() or MAX() as normal functions, not only group functions.
  • Use of REPLACE instead of DELETE + INSERT. See section REPLACE syntax

Go to the first, previous, next, last section, table of contents.