1.8.3 MySQL Extensions To The SQL-92 Standard

MySQL Server 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 Server 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 be executed only if the MySQL version is equal to or newer than the used version number:

CREATE /*!32302 TEMPORARY */ TABLE t (a INT);

This means that if you have Version 3.23.02 or newer, MySQL Server will use the TEMPORARY keyword.

The following is a list of MySQL extensions:

• The field types MEDIUMINT, SET, ENUM, and the different BLOB and TEXT types.
• The field attributes AUTO_INCREMENT, BINARY, NULL, 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 Server maps each database to a directory under the MySQL data directory, and tables within a database to filenames in the database directory. This has a few implications:
  • Database names and table names are case-sensitive in MySQL Server on operating systems that have case-sensitive filenames (like most Unix systems). See section 6.1.3 Case Sensitivity in 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 back up, rename, move, delete, and copy tables. For example, to rename a table, rename the `.MYD', `.MYI', 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 Server doesn't support tablespaces as 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. See section 6.4.1 SELECT Syntax.
• The SQL_SMALL_RESULT option in a SELECT statement.
• EXPLAIN SELECT to get a description of 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. See section 6.5.3 CREATE TABLE Syntax.
• Use of TEMPORARY or IF NOT EXISTS with CREATE TABLE.
• Use of COUNT(DISTINCT list) where list has more than one element.
• Use of CHANGE col_name, DROP col_name, or DROP INDEX, IGNORE or RENAME in an ALTER TABLE statement. See section 6.5.4 ALTER TABLE Syntax.
• Use of RENAME TABLE. See section 6.5.5 RENAME TABLE Syntax.
• 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 ORDER BY and LIMIT clauses of the UPDATE and DELETE statements.
• 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. See section 6.4.9 LOAD DATA INFILE Syntax.
• The ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE, and REPAIR TABLE statements.
• The SHOW statement. See section 4.5.7 SHOW Syntax.
• Strings may be enclosed by either `"' or `'', not just by `''.
• Use of the escape `\' character.
• The SET statement. See section 5.5.6 SET Syntax.
• 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. See section 6.3.7 Functions and Modifiers for Use with GROUP BY Clauses.
• One can specify ASC and DESC with GROUP BY.
• To make it easier for users who come from other SQL environments, MySQL Server supports aliases for many functions. For example, all string functions support both standard SQL syntax and ODBC syntax.
• MySQL Server understands the || and && operators to mean logical OR and AND, as in the C programming language. In MySQL Server, || and OR are synonyms, as are && and AND. Because of this nice syntax, MySQL Server doesn't support the standard SQL-99 || operator for string concatenation; use CONCAT() instead. Because CONCAT() takes any number of arguments, it's easy to convert use of the || operator to MySQL Server.
• CREATE DATABASE or DROP DATABASE. See section 6.5.1 CREATE DATABASE Syntax.
• 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. See section 9.1.3.31 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 Server, 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. SQL-99 supports removal of single characters only.
• The GROUP BY functions STD(), BIT_OR(), BIT_AND(), and GROUP_CONCAT(). See section 6.3.7 Functions and Modifiers for Use with GROUP BY Clauses.
• Use of REPLACE instead of DELETE + INSERT. See section 6.4.8 REPLACE Syntax.
• The FLUSH, RESET and DO statements.
• The ability to set variables in a statement with :=:

  SELECT @a:=SUM(total),@b=COUNT(*),@a/@b AS avg FROM test_table;
  SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
  

User Comments

Add your own comment.