GNU Info

Info Node: (mysql.info)Extensions to ANSI

(mysql.info)Extensions to ANSI


Next: Differences from ANSI Prev: Compatibility Up: Compatibility
Enter node , (file) or (file)node

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