MySQL Server supports some extensions that you probably won't find in other SQL DBMSs. Be warned that if you use them, your code won't 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 following form:
In this case, MySQL Server parses and executes the code within
the comment as it would any other SQL statement, but other SQL
servers will ignore the extensions. For example, MySQL Server
STRAIGHT_JOIN keyword in the
following statement, but other servers will 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 number. The
TEMPORARY keyword in the following comment is
executed only by servers from MySQL 3.23.02 or higher:
CREATE /*!32302 TEMPORARY */ TABLE t (a INT);
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.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 storage engine. For example,
it is possible to rename a
table by renaming the
files to which the table corresponds. (Nevertheless, it
is preferable to use
ALTER TABLE ...
RENAME and let the server rename the files.)
Database and table names cannot contain path name separator
General language syntax
By default, strings can be enclosed by either
'”, not just by
'”. (If the
ANSI_QUOTES SQL mode
is enabled, strings can be enclosed only by
'” and the server
interprets strings enclosed by
"” as identifiers.)
\” is the 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
CREATE TABLE ralph.my_table ... IN
SQL statement syntax
SELECT to obtain a description of how tables
are processed by the query optimizer.
SHOW statement. See
Section 13.7.5, “SHOW Syntax”. The information produced by many
of the MySQL-specific
SHOW statements can be
obtained in more standard fashion by using
SELECT to query
Chapter 19, INFORMATION_SCHEMA Tables.
statements. Use of multiple
CHANGE clauses in an
ALTER TABLE statement.
See Section 13.1.4, “ALTER TABLE Syntax”.
The capability of dropping multiple tables with a single
DROP TABLE statement.
col_name = ...
Options such as
You don't need to name all selected columns in the
GROUP BY clause. This gives better
performance for some very specific, but quite normal
Section 12.16, “Functions and Modifiers for Use with GROUP BY Clauses”.
You can specify
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,
synonyms, as are
Because of this nice syntax, MySQL Server doesn't
support the standard SQL
for string concatenation; use
CONCAT() instead. Because
CONCAT() takes any number
of arguments, it is easy to convert use of the
to MySQL Server.
value_list has more than one
String comparisons are case-insensitive by default, with
sort ordering determined by the collation of the current
character set, which is
(cp1252 West European) 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 than a lexical ordering.
SELECT col1=1 AND col2=2 FROM my_table;
LIKE is permitted on
TRIM() to trim
substrings. Standard SQL supports removal of single