( Extensions to ANSI

Info Catalog ( ANSI mode ( Compatibility ( 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
      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
         * 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
 SQL statement syntax
           `REPAIR TABLE' statements.
         * The `CREATE DATABASE' and `DROP DATABASE' statements.  
         * The `DO' statement.
         * `EXPLAIN SELECT' to get a description of how tables are
         * 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 `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
         * 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'
         * 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
         * 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
         * 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
                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
         * `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
 `'.  That is the latest
 version of the TODO list in this manual.  TODO.
Info Catalog ( ANSI mode ( Compatibility ( Differences from ANSI
automatically generated byinfo2html