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:
/*! 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 will ignore the extensions. For example, MySQL Server
recognizes the 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 8.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, it is possible to rename a
MyISAM
table by renaming the
.MYD
, .MYI
,
and .frm
files to which the table
corresponds. (Nevertheless, it is preferable to use
RENAME TABLE
or
ALTER TABLE ... RENAME
and let the
server rename the files.)
Database and table names cannot contain path name separator
characters (“/
”,
“\
”).
General language syntax
By default, strings can be enclosed by either
“"
” or
“'
”, 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
this: CREATE TABLE ralph.my_table ... IN
my_tablespace
.
SQL statement syntax
The ANALYZE TABLE
,
CHECK TABLE
,
OPTIMIZE TABLE
, and
REPAIR TABLE
statements.
The CREATE DATABASE
,
DROP DATABASE
, and
ALTER DATABASE
statements. See Section 12.1.3, “CREATE DATABASE
Syntax”,
Section 12.1.6, “DROP DATABASE
Syntax”, and
Section 12.1.1, “ALTER DATABASE
Syntax”.
The DO
statement.
EXPLAIN
SELECT
to obtain a description of how tables
are processed by the query optimizer.
The
SET
statement. See Section 12.4.4, “SET
Syntax”.
The SHOW
statement. See
Section 12.4.5, “SHOW
Syntax”.
Use of LOAD
DATA INFILE
. In many cases, this syntax is
compatible with Oracle's
LOAD DATA
INFILE
. See Section 12.2.5, “LOAD DATA INFILE
Syntax”.
Use of RENAME TABLE
. See
Section 12.1.9, “RENAME TABLE
Syntax”.
Use of REPLACE
instead of
DELETE
plus
INSERT
. See
Section 12.2.6, “REPLACE
Syntax”.
Use of CHANGE
,
col_name
DROP
, or
col_name
DROP INDEX
,
IGNORE
or RENAME
in ALTER TABLE
statements. Use of multiple ADD
,
ALTER
, DROP
, or
CHANGE
clauses in an
ALTER TABLE
statement.
See Section 12.1.2, “ALTER TABLE
Syntax”.
Use of index names, indexes on a prefix of a column, and
use of INDEX
or
KEY
in CREATE
TABLE
statements. See
Section 12.1.5, “CREATE TABLE
Syntax”.
Use of TEMPORARY
or IF NOT
EXISTS
with CREATE
TABLE
.
Use of IF EXISTS
with
DROP TABLE
and
DROP DATABASE
.
The capability of dropping multiple tables with a single
DROP TABLE
statement.
The ORDER BY
and
LIMIT
clauses of the
UPDATE
and
DELETE
statements.
INSERT INTO
syntax.
tbl_name
SET col_name
= ...
The LOW_PRIORITY
clause of the
INSERT
,
REPLACE
,
DELETE
, and
UPDATE
statements.
Use of INTO OUTFILE
or INTO
DUMPFILE
in
SELECT
statements. See
Section 12.2.7, “SELECT
Syntax”.
Options such as STRAIGHT_JOIN
or
SQL_SMALL_RESULT
in
SELECT
statements.
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
queries. See
Section 11.11, “Functions and Modifiers for Use with GROUP BY
Clauses”.
You can specify ASC
and
DESC
with GROUP
BY
, not just with ORDER 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;
Data types
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
||
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 is easy to convert use of the
||
operator
to MySQL Server.
Use of COUNT(DISTINCT
where
value_list
)value_list
has 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. 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,
is equivalent to
N
%
M
MOD(
.
N
,M
)%
is
supported for C programmers and for compatibility with
PostgreSQL.
The =
,
<>
,
<=
,
<
,
>=
,
>
,
<<
,
>>
,
<=>
,
AND
,
OR
, or
LIKE
operators may be used in expressions in the output
column list (to the left of the FROM
)
in SELECT
statements. For
example:
mysql> SELECT col1=1 AND col2=2 FROM my_table;
The LAST_INSERT_ID()
function returns the most recent
AUTO_INCREMENT
value. See
Section 11.10.3, “Information Functions”.
LIKE
is allowed on numeric
values.
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 a variable 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 11.11, “Functions and Modifiers for Use with GROUP BY
Clauses”.
User Comments
Add your own comment.