MySQL Server supports some extensions that are likely not to be found in other SQL DBMSs. Be warned that if you use them, your code is not 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 following form:
/*! MySQL-specific code */
In this case, MySQL Server parses and executes the code within
the comment as it would any other SQL statement, but other SQL
servers ignore the extensions. For example, MySQL Server
STRAIGHT_JOIN keyword in the
following statement, but other servers do not:
SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...
If you add a version number after the
character, the syntax within the comment is executed only if the
MySQL version is greater than or equal to the specified version
KEY_BLOCK_SIZE clause in the
following comment is executed only by servers from MySQL 5.1.10
CREATE TABLE t1(a INT, KEY (a)) /*!50110 KEY_BLOCK_SIZE=1024 */;
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 maps tables within a database to file names in the database directory. This has a few implications:
Database and table names are case-sensitive in MySQL Server on operating systems that have case-sensitive file names (such as most Unix systems). See Section 9.2.3, “Identifier Case Sensitivity”.
You can use standard system commands to back up, rename, move, delete, and copy tables that are managed by the
MyISAMstorage engine. For example, it is possible to rename a
MyISAMtable by renaming the
.frmfiles to which the table corresponds. (Nevertheless, it is preferable to use
ALTER TABLE ... RENAMEand let the server rename the files.)
General language syntax
By default, strings can be enclosed by
"as well as
'. If the
ANSI_QUOTESSQL mode is enabled, strings can be enclosed only by
'and the server interprets strings enclosed by
\is the escape character in strings.
In SQL statements, you can access tables from different databases with the
db_name.tbl_namesyntax. 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
OPTIMIZE TABLE, and
DROP DATABASE, and
ALTER DATABASEstatements. See Section 13.1.11, “CREATE DATABASE Statement”, Section 13.1.22, “DROP DATABASE Statement”, and Section 13.1.1, “ALTER DATABASE Statement”.
EXPLAIN SELECTto obtain a description of how tables are processed by the query optimizer.
SETstatement. See Section 188.8.131.52, “SET Syntax for Variable Assignment”.
SHOWstatement. See Section 13.7.5, “SHOW Statements”. The information produced by many of the MySQL-specific
SHOWstatements can be obtained in more standard fashion by using
INFORMATION_SCHEMA. See Chapter 24, INFORMATION_SCHEMA Tables.
LOAD DATA. In many cases, this syntax is compatible with Oracle
LOAD DATA. See Section 13.2.6, “LOAD DATA Statement”.
RENAME TABLE. See Section 13.1.33, “RENAME TABLE Statement”.
INSERT. See Section 13.2.8, “REPLACE Statement”.
ALTER TABLEstatements. Use of multiple
CHANGEclauses in an
ALTER TABLEstatement. See Section 13.1.8, “ALTER TABLE Statement”.
Use of index names, indexes on a prefix of a column, and use of
CREATE TABLEstatements. See Section 13.1.18, “CREATE TABLE Statement”.
IF NOT EXISTSwith
The capability of dropping multiple tables with a single
LIMITclauses of the
LOW_PRIORITYclause of the
SELECTstatements. See Section 13.2.9, “SELECT Statement”.
Options such as
You don't need to name all selected columns in the
GROUP BYclause. This gives better performance for some very specific, but quite normal queries. See Section 12.20, “Aggregate Functions”.
You can specify
GROUP BY, not just with
The ability to set variables in a statement with the
:=assignment operator. See Section 9.4, “User-Defined Variables”.
Functions and operators
To make it easier for users who migrate 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
&&operators to mean logical OR and AND, as in the C programming language. In MySQL Server,
ORare synonyms, as are
AND. Because of this nice syntax, MySQL Server doesn't support the standard SQL
||operator for string concatenation; use
CONCAT()takes any number of arguments, it is easy to convert use of the
||operator to MySQL Server.
value_listhas more than one element.
String comparisons are case-insensitive by default, with sort ordering determined by the collation of the current character set, which is
latin1(cp1252 West European) by default. To perform case-sensitive comparisons instead, you should declare your columns with the
BINARYattribute or use the
BINARYcast, which causes comparisons to be done using the underlying character code values rather than a lexical ordering.
%operator is a synonym for
MOD(). That is,
is equivalent to
%is supported for C programmers and for compatibility with PostgreSQL.
LIKEoperators may be used in expressions in the output column list (to the left of the
SELECTstatements. For example:
mysql> SELECT col1=1 AND col2=2 FROM my_table;
LAST_INSERT_ID()function returns the most recent
AUTO_INCREMENTvalue. See Section 12.16, “Information Functions”.
LIKEis permitted on numeric values.
NOT REGEXPextended regular expression operators.
CHAR()with one argument or more than two arguments. (In MySQL Server, these functions can take a variable number of arguments.)
TRIM()to trim substrings. Standard SQL supports removal of single characters only.
GROUP_CONCAT(). See Section 12.20, “Aggregate Functions”.