MySQL Reference Manual :: 1.7.4 MySQL Extensions to Standard SQL

Database names and table names are case sensitive in MySQL Server on operating systems that have case-sensitive filenames (such as most Unix systems). See Section 9.2.2, “Identifier 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.

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.

The ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE, and REPAIR TABLE statements.

The CREATE DATABASE and DROP DATABASE statements. See Section 13.1.3, “CREATE DATABASE Syntax”.

The DO statement.

EXPLAIN SELECT to get a description of how tables are joined.

The FLUSH and RESET statements.

The SET statement. See Section 13.5.3, “SET Syntax”.

The SHOW statement. See Section 13.5.4, “SHOW Syntax”.

Use of LOAD DATA INFILE. In many cases, this syntax is compatible with Oracle's LOAD DATA INFILE. See Section 13.2.5, “LOAD DATA INFILE Syntax”.

Use of RENAME TABLE. See Section 13.1.9, “RENAME TABLE Syntax”.

Use of REPLACE instead of DELETE + INSERT. See Section 13.2.6, “REPLACE Syntax”.

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. See Section 13.1.2, “ALTER TABLE Syntax”.

Use of index names, indexes on a prefix of a field, and use of INDEX or KEY in a CREATE TABLE statement. See Section 13.1.5, “CREATE TABLE Syntax”.

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. See Section 13.2.7, “SELECT Syntax”.

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. See Section 12.10, “Functions and Modifiers for Use with GROUP BY Clauses”.

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;

The column types MEDIUMINT, SET, ENUM, and the different BLOB and TEXT types.

The column attributes AUTO_INCREMENT, BINARY, NULL, UNSIGNED, and ZEROFILL.

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. See Section 12.9.3, “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(). See Section 12.10, “Functions and Modifiers for Use with GROUP BY Clauses”.