MySQL Reference Manual - 4.0.20

[ < ]

[ > ]

[ << ]

[ Up ]

[ >> ]

[Top]

[Contents]

[Index]

[ ? ]

1.8.4 MySQL Extensions to the SQL 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 '!' character, the syntax within the comment will be executed only if the MySQL version is equal to or newer than the specified 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 descriptions list MySQL extensions, organized by category.

Organization of data on disk

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 (such as most Unix systems). See section Identifier Case Sensitivity.
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 that are managed by the MyISAM or ISAM storage engines. For example, to rename a MyISAM table, rename the `.MYD', `.MYI', and `.frm' files to which the table corresponds.

General language syntax

Strings may be enclosed by either `"' or `'', not just by `''.
Use of `\' as an escape character in strings.
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 such as used in statements like this: CREATE TABLE ralph.my_table...IN my_tablespace.

SQL statement syntax

The ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE, and REPAIR TABLE statements.
The CREATE DATABASE and DROP DATABASE statements. See section CREATE DATABASE.
The DO statement.
EXPLAIN SELECT to get a description of how tables are joined.
The FLUSH and RESET statements.
The SET statement. See section SET.
The SHOW statement. See section SHOW.
Use of LOAD DATA INFILE. In many cases, this syntax is compatible with Oracle's LOAD DATA INFILE. See section LOAD DATA.
Use of RENAME TABLE. See section RENAME TABLE.
Use of REPLACE instead of DELETE + INSERT. See section REPLACE.
Use of CHANGE col_name, DROP col_name, or DROP INDEX, IGNORE or RENAME in an ALTER TABLE statement. Use of multiple ADD, ALTER, DROP, or CHANGE clauses in an ALTER TABLE statement. See section ALTER TABLE.
Use of index names, indexes on a prefix of a field, and use of INDEX or KEY in a CREATE TABLE statement. See section CREATE TABLE.
Use of TEMPORARY or IF NOT EXISTS with CREATE TABLE.
Use of IF EXISTS with DROP TABLE.
You can drop multiple tables with a single DROP TABLE statement.
The ORDER BY and LIMIT clauses of the UPDATE and DELETE statements.
INSERT INTO ... SET col_name = ... syntax.
The DELAYED clause of the INSERT and REPLACE statements.
The LOW_PRIORITY clause of the INSERT, REPLACE, DELETE, and UPDATE statements.
Use of INTO OUTFILE and STRAIGHT_JOIN in a SELECT statement. See section SELECT.
The SQL_SMALL_RESULT option in a SELECT statement.
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 Functions and Modifiers for Use with GROUP BY Clauses.
You can specify ASC and DESC with GROUP BY.

The ability to set variables in a statement with the := assignment operator:

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

Column types

The column types MEDIUMINT, SET, ENUM, and the different BLOB and TEXT types.
The column attributes AUTO_INCREMENT, BINARY, NULL, UNSIGNED, and ZEROFILL.

Functions and operators

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 || 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.
Use of COUNT(DISTINCT list) where list has more than one element.
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 using the underlying character code values rather then a lexical ordering.
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 Information Functions.
LIKE is allowed on numeric columns.
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. Standard SQL supports removal of single characters only.
The GROUP BY functions STD(), BIT_OR(), BIT_AND(), BIT_XOR(), and GROUP_CONCAT(). See section Functions and Modifiers for Use with GROUP BY Clauses.

For a prioritized list indicating when new extensions will be added to MySQL Server, you should consult the online MySQL TODO list at http://dev.mysql.com/doc/mysql/en/TODO.html. That is the latest version of the TODO list in this manual. See section MySQL and the Future (the TODO).