GNU Info
(mysql.info)Extensions to ANSI
MySQL Extensions to ANSI SQL92
------------------------------
MySQL 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 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 only be
executed if the MySQL version is equal to or newer than the used
version number:
CREATE /*!32302 TEMPORARY */ TABLE (a int);
The above means that if you have Version 3.23.02 or newer, then MySQL
will use the `TEMPORARY' keyword.
MySQL extensions are listed below:
* 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 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 on
operating systems that have case-sensitive filenames (like
most Unix systems). Note: Name 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 backup, 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 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.
Note: `SELECT'.
* The `SQL_SMALL_RESULT' option in a `SELECT' statement.
* `EXPLAIN SELECT' to get a description on 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. Note: `CREATE
TABLE'.
* Use of `TEMPORARY' or `IF NOT EXISTS' with `CREATE TABLE'.
* Use of `COUNT(DISTINCT list)' where 'list' is more than one
element.
* Use of `CHANGE col_name', `DROP col_name', or `DROP INDEX',
`IGNORE' or `RENAME' in an `ALTER TABLE' statement. Note: `ALTER
TABLE'.
* Use of `RENAME TABLE'. Note: `RENAME TABLE'.
* 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 `LIMIT' clause of the `DELETE' statement.
* 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'. *Note `LOAD DATA':
LOAD DATA.
* The `ANALYZE TABLE', `CHECK TABLE', `OPTIMIZE TABLE', and `REPAIR
TABLE' statements.
* The `SHOW' statement. Note: `SHOW'.
* Strings may be enclosed by either `"' or `'', not just by `''.
* Use of the escape `\' character.
* The `SET OPTION' statement. Note: `SET OPTION'.
* 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. Note: Group by functions.
* One can specify `ASC' and `DESC' with `GROUP BY'.
* To make it easier for users who come from other SQL environments,
MySQL supports aliases for many functions. For example, all string
functions support both ANSI SQL syntax and ODBC syntax.
* MySQL understands the `||' and `&&' operators to mean logical OR
and AND, as in the C programming language. In MySQL, `||' and
`OR' are synonyms, as are `&&' and `AND'. Because of this nice
syntax, MySQL doesn't support the ANSI 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.
* `CREATE DATABASE' or `DROP DATABASE'. *Note `CREATE DATABASE':
CREATE DATABASE.
* 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. *Note `mysql_insert_id()':
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, 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. ANSI SQL only supports removal
of single characters.
* The `GROUP BY' functions `STD()', `BIT_OR()', and `BIT_AND()'.
* Use of `REPLACE' instead of `DELETE' + `INSERT'. *Note `REPLACE':
REPLACE.
* The `FLUSH', `RESET' and `DO' statements.
* The possibility 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;
automatically generated by info2www version 1.2.2.9