(mysql.info) extensions-to-ansi
Info Catalog
(mysql.info) ansi-mode
(mysql.info) compatibility
(mysql.info) differences-from-ansi
1.9.4 MySQL Extensions to Standard SQL
--------------------------------------
MySQL Server supports some extensions that you probably won't find in
other SQL DBMSs. Be warned that if you use them, your code won't 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 following form:
/*! MYSQL-SPECIFIC CODE */
In this case, MySQL Server parses and executes the code within the
comment as it would any other SQL statement, but other SQL servers will
ignore the extensions. For example, MySQL Server recognizes the
`STRAIGHT_JOIN' keyword in the following statement, but other servers
will not:
SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...
If you add a version number after the ‘`!'’ character, the syntax
within the comment is executed only if the MySQL version is greater
than or equal to the specified version number. The `TEMPORARY' keyword
in the following comment is executed only by servers from MySQL 3.23.02
or higher:
CREATE /*!32302 TEMPORARY */ TABLE t (a INT);
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 maps tables within a database to filenames in
the database directory. This has a few implications:
* Database and table names are case sensitive in MySQL Server
on operating systems that have case-sensitive filenames (such
as most Unix systems). See name-case-sensitivity.
* You can use standard system commands to back up, rename,
move, delete, and copy tables that are managed by the
`MyISAM' storage engine. For example, it is possible to
rename a `MyISAM' table by renaming the `.MYD', `.MYI', and
`.frm' files to which the table corresponds. (Nevertheless, it
is preferable to use `RENAME TABLE' or `ALTER TABLE ...
RENAME' and let the server rename the files.)
Database and table names cannot contain pathname separator
characters (‘`/'’, ‘`\'’).
* General language syntax
* By default, strings can be enclosed by either ‘`"'’ or
‘`''’, not just by ‘`''’. (If the `ANSI_QUOTES' SQL
mode is enabled, strings can be enclosed only by ‘`''’
and the server interprets strings enclosed by ‘`"'’ as
identifiers.)
* 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', `DROP DATABASE', and `ALTER DATABASE'
statements. See create-database,
drop-database, and alter-database.
* The `DO' statement.
* `EXPLAIN SELECT' to obtain a description of how tables are
processed by the query optimizer.
* The `FLUSH' and `RESET' statements.
* The `SET' statement. See set-option.
* The `SHOW' statement. See show. As of MySQL 5.0, the
information produced by many of the MySQL-specific `SHOW'
statements can be obtained in more standard fashion by using
`SELECT' to query `INFORMATION_SCHEMA'. See
information-schema.
* Use of `LOAD DATA INFILE'. In many cases, this syntax is
compatible with Oracle's `LOAD DATA INFILE'. See
load-data.
* Use of `RENAME TABLE'. See rename-table.
* Use of `REPLACE' instead of `DELETE' plus `INSERT'. See
replace.
* Use of `CHANGE COL_NAME', `DROP COL_NAME', or `DROP INDEX',
`IGNORE' or `RENAME' in `ALTER TABLE' statements. Use of
multiple `ADD', `ALTER', `DROP', or `CHANGE' clauses in an
`ALTER TABLE' statement. See alter-table.
* Use of index names, indexes on a prefix of a column, and use
of `INDEX' or `KEY' in `CREATE TABLE' statements. See
create-table.
* Use of `TEMPORARY' or `IF NOT EXISTS' with `CREATE TABLE'.
* Use of `IF EXISTS' with `DROP TABLE' and `DROP DATABASE'.
* The capability of dropping 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' or `INTO DUMPFILE' in `SELECT'
statements. See select.
* Options such as `STRAIGHT_JOIN' or `SQL_SMALL_RESULT' in
`SELECT' statements.
* You don't need to name all selected columns in the `GROUP BY'
clause. This gives better performance for some very specific,
but quite normal queries. See
group-by-functions-and-modifiers.
* You can specify `ASC' and `DESC' with `GROUP BY', not just
with `ORDER 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;
* Data types
* The `MEDIUMINT', `SET', and `ENUM' data types, and the
various `BLOB' and `TEXT' data types.
* The `AUTO_INCREMENT', `BINARY', `NULL', `UNSIGNED', and
`ZEROFILL' data type attributes.
* Functions and operators
* To make it easier for users who migrate 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 VALUE_LIST)' where VALUE_LIST has more
than one element.
* String comparisons are case-insensitive by default, with sort
ordering determined by the current character set, which is
`latin1' (cp1252 West European) 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 expressions in the
output column list (to the left of the `FROM') in `SELECT'
statements. For example:
mysql> SELECT col1=1 AND col2=2 FROM my_table;
* The `LAST_INSERT_ID()' function returns the most recent
`AUTO_INCREMENT' value. See information-functions.
* `LIKE' is allowed on numeric values.
* 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 a
variable number of arguments.)
* The `BIT_COUNT()', `CASE', `ELT()', `FROM_DAYS()',
`FORMAT()', `IF()', `PASSWORD()', `ENCRYPT()', `MD5()',
`ENCODE()', `DECODE()', `PERIOD_ADD()', `PERIOD_DIFF()',
`TO_DAYS()', and `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
group-by-functions-and-modifiers.
For a prioritized list indicating when new extensions are added to
MySQL Server, you should consult the online MySQL development roadmap
at `http://dev.mysql.com/doc/mysql/en/roadmap.html'.
Info Catalog
(mysql.info) ansi-mode
(mysql.info) compatibility
(mysql.info) differences-from-ansi
automatically generated byinfo2html