(mysql.info.gz) Extensions to ANSI
Info Catalog
(mysql.info.gz) ANSI mode
(mysql.info.gz) Compatibility
(mysql.info.gz) Differences from ANSI
1.5.4 MySQL Extensions to Standard SQL
--------------------------------------
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). Name case
sensitivity.
* 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.
Database, table, index, column, or alias names may begin with a
digit (but may not consist solely of digits).
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.
`CREATE DATABASE' CREATE DATABASE.
* The `DO' statement.
* `EXPLAIN SELECT' to get a description of how tables are
joined.
* The `FLUSH' and `RESET' statements.
* The `SET' statement. `SET' SET OPTION.
* The `SHOW' statement. `SHOW' SHOW.
* Use of `LOAD DATA INFILE'. In many cases, this syntax is
compatible with Oracle's `LOAD DATA INFILE'. `LOAD
DATA' LOAD DATA.
* Use of `RENAME TABLE'. `RENAME TABLE' RENAME TABLE.
* Use of `REPLACE' instead of `DELETE' + `INSERT'.
`REPLACE' 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. `ALTER TABLE' ALTER TABLE.
* Use of index names, indexes on a prefix of a field, and use of
`INDEX' or `KEY' in a `CREATE TABLE' statement. `CREATE
TABLE' 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. `SELECT' 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. Group by functions and
modifiers.
* 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 that returns the most recent
`AUTO_INCREMENT' value. 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()', 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()'. Group by functions
and modifiers.
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. TODO.
Info Catalog
(mysql.info.gz) ANSI mode
(mysql.info.gz) Compatibility
(mysql.info.gz) Differences from ANSI
automatically generated byinfo2html