|
|
Table of Contents
This chapter discusses the rules for writing the following elements of SQL statements when using MySQL:
Literal values such as strings and numbers
Identifiers such as database, table, and column names
User-defined and system variables
Comments
Reserved words
This section describes how to write literal values in MySQL. These
include strings, numbers, hexadecimal values, boolean values, and
NULL
. The section also covers the various
nuances and “gotchas” that you may run into when
dealing with these basic types in MySQL.
A string is a sequence of bytes or characters, enclosed within
either single quote (‘'
’) or
double quote (‘"
’) characters.
Examples:
'a string' "another string"
If the ANSI_QUOTES
SQL mode is enabled,
string literals can be quoted only within single quotes because
a string quoted within double quotes is interpreted as an
identifier.
A binary string is a string of bytes that has no character set or collation. A non-binary string is a string of characters that has a character set and collation. For both types of strings, comparisons are based on the numeric values of the string unit. For binary strings, the unit is the byte. For non-binary strings the unit is the character and some character sets allow multi-byte characters. Character value ordering is a function of the string collation.
String literals may have an optional character set introducer
and COLLATE
clause:
[_charset_name
]'string
' [COLLATEcollation_name
]
Examples:
SELECT _latin1'string
'; SELECT _latin1'string
' COLLATE latin1_danish_ci;
For more information about these forms of string syntax, see Section 10.3.5, “Character String Literal Character Set and Collation”.
Within a string, certain sequences have special meaning. Each of
these sequences begins with a backslash
(‘\
’), known as the
escape character. MySQL recognizes the
following escape sequences:
For all other escape sequences, backslash is ignored. That is,
the escaped character is interpreted as if it was not escaped.
For example, ‘\x
’ is just
‘x
’.
These sequences are case sensitive. For example,
‘\b
’ is interpreted as a
backspace, but ‘\B
’ is
interpreted as ‘B
’.
The ASCII 26 character can be encoded as
‘\Z
’ to enable you to work around
the problem that ASCII 26 stands for END-OF-FILE on Windows.
ASCII 26 within a file causes problems if you try to use
mysql
.
db_name
<
file_name
The ‘\%
’ and
‘\_
’ sequences are used to search
for literal instances of ‘%
’ and
‘_
’ in pattern-matching contexts
where they would otherwise be interpreted as wildcard
characters. See the description of the LIKE
operator in Section 12.3.1, “String Comparison Functions”. If
you use ‘\%
’ or
‘\_
’ in non-pattern-matching
contexts, they evaluate to the strings
‘\%
’ and
‘\_
’, not to
‘%
’ and
‘_
’.
There are several ways to include quote characters within a string:
A ‘'
’ inside a string quoted
with ‘'
’ may be written as
‘''
’.
A ‘"
’ inside a string quoted
with ‘"
’ may be written as
‘""
’.
Precede the quote character by an escape character
(‘\
’).
A ‘'
’ inside a string quoted
with ‘"
’ needs no special
treatment and need not be doubled or escaped. In the same
way, ‘"
’ inside a string
quoted with ‘'
’ needs no
special treatment.
The following SELECT
statements demonstrate
how quoting and escaping work:
mysql>SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+ | hello | "hello" | ""hello"" | hel'lo | 'hello | +-------+---------+-----------+--------+--------+ mysql>SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
+-------+---------+-----------+--------+--------+ | hello | 'hello' | ''hello'' | hel"lo | "hello | +-------+---------+-----------+--------+--------+ mysql>SELECT 'This\nIs\nFour\nLines';
+--------------------+ | This Is Four Lines | +--------------------+ mysql>SELECT 'disappearing\ backslash';
+------------------------+ | disappearing backslash | +------------------------+
If you want to insert binary data into a string column (such as
a BLOB
column), the following characters must
be represented by escape sequences:
NUL | NUL byte (ASCII 0). Represent this character by
‘\0 ’ (a backslash
followed by an ASCII ‘0 ’
character). |
\ | Backslash (ASCII 92). Represent this character by
‘\\ ’. |
' | Single quote (ASCII 39). Represent this character by
‘\' ’. |
" | Double quote (ASCII 34). Represent this character by
‘\" ’. |
When writing application programs, any string that might contain any of these special characters must be properly escaped before the string is used as a data value in an SQL statement that is sent to the MySQL server. You can do this in two ways:
Process the string with a function that escapes the special
characters. In a C program, you can use the
mysql_real_escape_string()
C API function
to escape characters. See
Section 22.2.3.52, “mysql_real_escape_string()
”. The Perl DBI
interface provides a quote
method to
convert special characters to the proper escape sequences.
See Section 22.4, “MySQL Perl API”. Other language interfaces may
provide a similar capability.
As an alternative to explicitly escaping special characters, many MySQL APIs provide a placeholder capability that enables you to insert special markers into a statement string, and then bind data values to them when you issue the statement. In this case, the API takes care of escaping special characters in the values for you.
Integers are represented as a sequence of digits. Floats use
‘.
’ as a decimal separator.
Either type of number may be preceded by
‘-
’ or
‘+
’ to indicate a negative or
positive value, respectively
Examples of valid integers:
1221 0 -32
Examples of valid floating-point numbers:
294.42 -32032.6809e+10 148.00
An integer may be used in a floating-point context; it is interpreted as the equivalent floating-point number.
MySQL supports hexadecimal values. In numeric contexts, these act like integers (64-bit precision). In string contexts, these act like binary strings, where each pair of hex digits is converted to a character:
mysql>SELECT x'4D7953514C';
-> 'MySQL' mysql>SELECT 0xa+0;
-> 10 mysql>SELECT 0x5061756c;
-> 'Paul'
The default type of a hexadecimal value is a string. If you want
to ensure that the value is treated as a number, you can use
CAST(... AS UNSIGNED)
:
mysql> SELECT 0x41, CAST(0x41 AS UNSIGNED);
-> 'A', 65
The x'
syntax is based on standard SQL. The hexstring
'0x
syntax is based on ODBC. Hexadecimal strings are often used by
ODBC to supply values for BLOB
columns.
You can convert a string or a number to a string in hexadecimal
format with the HEX()
function:
mysql>SELECT HEX('cat');
-> '636174' mysql>SELECT 0x636174;
-> 'cat'
The constants TRUE
and
FALSE
evaluate to 1
and
0
, respectively. The constant names can be
written in any lettercase.
mysql> SELECT TRUE, true, FALSE, false;
-> 1, 1, 0, 0
Beginning with MySQL 5.0.3, bit-field values can be written
using b'
notation. value
'value
is a binary value
written using zeroes and ones.
Bit-field notation is convenient for specifying values to be
assigned to BIT
columns:
mysql>CREATE TABLE t (b BIT(8));
mysql>INSERT INTO t SET b = b'11111111';
mysql>INSERT INTO t SET b = b'1010';
+------+----------+----------+----------+ | b+0 | BIN(b+0) | OCT(b+0) | HEX(b+0) | +------+----------+----------+----------+ | 255 | 11111111 | 377 | FF | | 10 | 1010 | 12 | A | +------+----------+----------+----------+
The NULL
value means “no data.”
NULL
can be written in any lettercase.
Be aware that the NULL
value is different
from values such as 0
for numeric types or
the empty string for string types. See
Section A.5.3, “Problems with NULL
Values”.
For text file import or export operations performed with
LOAD DATA INFILE
or SELECT ... INTO
OUTFILE
, NULL
is represented by the
\N
sequence. See Section 13.2.5, “LOAD DATA INFILE
Syntax”.
Database, table, index, column, and alias names are identifiers. This section describes the allowable syntax for identifiers in MySQL.
The following table describes the maximum length and allowable characters for each type of identifier.
Identifier | Maximum Length (bytes) | Allowed Characters |
Database | 64 | Any character that is allowed in a directory name, except
‘/ ’,
‘\ ’, or
‘. ’ |
Table | 64 | Any character that is allowed in a filename, except
‘/ ’,
‘\ ’, or
‘. ’ |
Column | 64 | All characters |
Index | 64 | All characters |
Alias | 255 | All characters |
In addition to the restrictions noted in the table, no identifier can contain ASCII 0 or a byte with a value of 255. Database, table, and column names should not end with space characters. The use of identifier quote characters in identifiers is permitted, although it is best to avoid doing so if possible.
Identifiers are stored using Unicode (UTF-8). This applies to
identifiers in table definitions that stored in
.frm
files and to identifiers stored in the
grant tables in the mysql
database. The sizes
of the string columns in the grant tables (and in any other
tables) in MySQL 5.0 are given as number of
characters. This means that (unlike some earlier versions of
MySQL) you can use multi-byte characters without reducing the
number of characters allowed for values stored in these columns.
An identifier may be quoted or unquoted. If an identifier is a
reserved word or contains special characters, you
must quote it whenever you refer to it.
(Exception: A word that follows a period in a qualified name must
be an identifier, so it is not necessary to quote it, even if it
is a reserved word.) For a list of reserved words, see
Section 9.5, “Treatment of Reserved Words in MySQL”. Special characters are those
outside the set of alphanumeric characters from the current
character set, ‘_
’, and
‘$
’.
The identifier quote character is the backtick
(‘`
’):
mysql> SELECT * FROM `select` WHERE `select`.id > 100;
If the ANSI_QUOTES
SQL mode is enabled, it is
also allowable to quote identifiers within double quotes:
mysql>CREATE TABLE "test" (col INT);
ERROR 1064: You have an error in your SQL syntax. (...) mysql>SET sql_mode='ANSI_QUOTES';
mysql>CREATE TABLE "test" (col INT);
Query OK, 0 rows affected (0.00 sec)
Note: Because ANSI_QUOTES
causes the server to
interpret double-quoted strings as identifiers, string literals
must be enclosed within single quotes. They cannot be enclosed
within double quotes when ANSI_QUOTES
is
enabled.
The server SQL mode is controlled as described in Section 5.2.5, “The Server SQL Mode”.
Identifier quote characters can be included within an identifier
if you quote the identifier. If the character
to be included within the identifier is the same as that used to
quote the identifier itself, then you need to double the
character. The following statement creates a table named
a`b
that contains a column named
c"d
:
mysql> CREATE TABLE `a``b` (`c"d` INT);
It is recommended that you do not use names of the form
or
M
e
,
such as M
eN
1e
or 2e2
, because
an expression such as 1e+3
is ambiguous.
Depending on context, it might be interpreted as the expression
1e + 3
or as the number
1e+3
.
Be careful when using MD5()
to produce table
names because it can produce names in illegal or ambiguous formats
such as those just described.
MySQL allows names that consist of a single identifier or
multiple identifiers. The components of a multiple-part name
should be separated by period
(‘.
’) characters. The initial
parts of a multiple-part name act as qualifiers that affect the
context within which the final identifier is interpreted.
In MySQL you can refer to a column using any of the following forms:
Column Reference | Meaning |
col_name | The column col_name from whichever table used
in the statement contains a column of that name. |
tbl_name.col_name | The column col_name from table
tbl_name of the default
database. |
db_name.tbl_name.col_name | The column col_name from table
tbl_name of the database
db_name . |
If any components of a multiple-part name require quoting, quote
them individually rather than quoting the name as a whole. For
example, write `my-table`.`my-column`
, not
`my-table.my-column`
.
You need not specify a tbl_name
or
db_name.tbl_name
prefix for a column
reference in a statement unless the reference would be
ambiguous. Suppose that tables t1
and
t2
each contain a column
c
, and you retrieve c
in a
SELECT
statement that uses both
t1
and t2
. In this case,
c
is ambiguous because it is not unique among
the tables used in the statement. You must qualify it with a
table name as t1.c
or t2.c
to indicate which table you mean. Similarly, to retrieve from a
table t
in database db1
and from a table t
in database
db2
in the same statement, you must refer to
columns in those tables as
db1.t.
and
col_name
db2.t.
.
col_name
A word that follows a period in a qualified name must be an identifier, so it is not necessary to quote it, even if it is a reserved word.
The syntax .tbl_name
means the table
tbl_name
in the default database.
This syntax is accepted for ODBC compatibility because some ODBC
programs prefix table names with a
‘.
’ character.
In MySQL, databases correspond to directories within the data
directory. Each table within a database corresponds to at least
one file within the database directory (and possibly more,
depending on the storage engine). Consequently, the case
sensitivity of the underlying operating system determines the
case sensitivity of database and table names. This means
database and table names are case sensitive in most varieties of
Unix, and not case sensitive in Windows. One notable exception
is Mac OS X, which is Unix-based but uses a default filesystem
type (HFS+) that is not case sensitive. However, Mac OS X also
supports UFS volumes, which are case sensitive just as on any
Unix. See Section 1.9.4, “MySQL Extensions to Standard SQL”. The
lower_case_table_names
system variable also
affects how the server handles identifier case sensitivity, as
described later in this section.
Note: Although database and
table names are not case sensitive on some platforms, you should
not refer to a given database or table using different cases
within the same statement. The following statement would not
work because it refers to a table both as
my_table
and as MY_TABLE
:
mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;
Column, index, stored routine, and trigger names are not case sensitive on any platform, nor are column aliases.
By default, table aliases are case sensitive on Unix, but not so
on Windows or Mac OS X. The following statement would not work
on Unix, because it refers to the alias both as
a
and as A
:
mysql>SELECT
->col_name
FROMtbl_name
AS aWHERE a.
col_name
= 1 OR A.col_name
= 2;
However, this same statement is permitted on Windows. To avoid problems caused by such differences, it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names. This convention is recommended for maximum portability and ease of use.
How table and database names are stored on disk and used in
MySQL is affected by the
lower_case_table_names
system variable, which
you can set when starting mysqld.
lower_case_table_names
can take the values
shown in the following table. On Unix, the default value of
lower_case_table_names
is 0. On Windows the
default value is 1. On Mac OS X, the default value is 2.
Value | Meaning |
0 | Table and database names are stored on disk using the lettercase
specified in the CREATE TABLE or
CREATE DATABASE statement. Name
comparisons are case sensitive. Note that if you force
this variable to 0 with
--lower-case-table-names=0 on a
case-insensitive filesystem and access
MyISAM tablenames using different
lettercases, index corruption may result. |
1 | Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases. |
2 | Table and database names are stored on disk using the lettercase
specified in the CREATE TABLE or
CREATE DATABASE statement, but MySQL
converts them to lowercase on lookup. Name comparisons
are not case sensitive.
Note: This works
only on filesystems that are not
case sensitive! InnoDB table names
are stored in lowercase, as for
lower_case_table_names=1 . |
If you are using MySQL on only one platform, you don't normally
have to change the lower_case_table_names
variable. However, you may encounter difficulties if you want to
transfer tables between platforms that differ in filesystem case
sensitivity. For example, on Unix, you can have two different
tables named my_table
and
MY_TABLE
, but on Windows these two names are
considered identical. To avoid data transfer problems stemming
from lettercase of database or table names, you have two
options:
Use lower_case_table_names=1
on all
systems. The main disadvantage with this is that when you
use SHOW TABLES
or SHOW
DATABASES
, you don't see the names in their
original lettercase.
Use lower_case_table_names=0
on Unix and
lower_case_table_names=2
on Windows. This
preserves the lettercase of database and table names. The
disadvantage of this is that you must ensure that your
statements always refer to your database and table names
with the correct lettercase on Windows. If you transfer your
statements to Unix, where lettercase is significant, they do
not work if the lettercase is incorrect.
Exception: If you are using
InnoDB
tables, you should set
lower_case_table_names
to 1 on all
platforms to force names to be converted to lowercase.
Note that if you plan to set the
lower_case_table_names
system variable to 1
on Unix, you must first convert your old database and table
names to lowercase before restarting mysqld
with the new variable setting.
You can store a value in a user-defined variable and then refer to it later. This enables you to pass values from one statement to another. User-defined variables are connection-specific. That is, a user variable defined by one client cannot be seen or used by other clients. All variables for a given client connection are automatically freed when that client exits.
User variables are written as
@
, where the
variable name var_name
var_name
may consist of
alphanumeric characters from the current character set,
‘.
’,
‘_
’, and
‘$
’. The default character set is
latin1
(cp1252 West European). This may be
changed with the --default-character-set
option
to mysqld. See
Section 5.11.1, “The Character Set Used for Data and Sorting”. A user variable name can contain
other characters if you quote it as a string or identifier (for
example, @'my-var'
,
@"my-var"
, or @`my-var`
).
Note: User variable names are case sensitive before MySQL 5.0 and not case sensitive in MySQL 5.0 and up.
One way to set a user-defined variable is by issuing a
SET
statement:
SET @var_name
=expr
[, @var_name
=expr
] ...
For SET
, either =
or
:=
can be used as the assignment operator. The
expr
assigned to each variable can
evaluate to an integer, real, string, or NULL
value.
You can also assign a value to a user variable in statements other
than SET
. In this case, the assignment operator
must be :=
and not =
because
=
is treated as a comparison operator in
non-SET
statements:
mysql>SET @t1=0, @t2=0, @t3=0;
mysql>SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+ | @t1:=(@t2:=1)+@t3:=4 | @t1 | @t2 | @t3 | +----------------------+------+------+------+ | 5 | 5 | 1 | 4 | +----------------------+------+------+------+
User variables may be used in contexts where expressions are
allowed. This does not currently include contexts that explicitly
require a literal value, such as in the LIMIT
clause of a SELECT
statement, or the
IGNORE
clause of a N
LINESLOAD DATA
statement.
If a user variable is assigned a string value, it has the same character set and collation as the string. The coercibility of user variables is implicit as of MySQL 5.0.3. (This is the same coercibility as for table column values.)
Note: In a
SELECT
statement, each expression is evaluated
only when sent to the client. This means that in a
HAVING
, GROUP BY
, or
ORDER BY
clause, you cannot refer to an
expression that involves variables that are set in the
SELECT
list. For example, the following
statement does not work as expected:
mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name
HAVING b=5;
The reference to b
in the
HAVING
clause refers to an alias for an
expression in the SELECT
list that uses
@aa
. This does not work as expected:
@aa
contains the value of id
from the previous selected row, not from the current row.
The general rule is to never assign a value to a user variable in one part of a statement and use the same variable in some other part the same statement. You might get the results you expect, but this is not guaranteed.
Another issue with setting a variable and using it in the same statement is that the default result type of a variable is based on the type of the variable at the start of the statement. The following example illustrates this:
mysql>SET @a='test';
mysql>SELECT @a,(@a:=20) FROM
tbl_name
;
For this SELECT
statement, MySQL reports to the
client that column one is a string and converts all accesses of
@a
to strings, even though @a is set to a
number for the second row. After the SELECT
statement executes, @a
is regarded as a number
for the next statement.
To avoid problems with this behavior, either do not set and use
the same variable within a single statement, or else set the
variable to 0
, 0.0
, or
''
to define its type before you use it.
If you refer to a variable that has not been initialized, it has a
value of NULL
and a type of string.
MySQL Server supports three comment styles:
From a ‘#
’ character to the end
of the line.
From a ‘--
’ sequence to
the end of the line. In MySQL, the
‘--
’ (double-dash)
comment style requires the second dash to be followed by at
least one whitespace or control character (such as a space,
tab, newline, and so on). This syntax differs slightly from
standard SQL comment syntax, as discussed in
Section 1.9.5.7, “'--
' as the Start of a Comment”.
From a /*
sequence to the following
*/
sequence, as in the C programming
language. This syntax allows a comment to extend over multiple
lines because the beginning and closing sequences need not be
on the same line.
The following example demonstrates all three comment styles:
mysql>SELECT 1+1; # This comment continues to the end of line
mysql>SELECT 1+1; -- This comment continues to the end of line
mysql>SELECT 1 /* this is an in-line comment */ + 1;
mysql>SELECT 1+
/*
this is a
multiple-line comment
*/
1;
MySQL Server supports some variants of C-style comments. These enable you to 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 comment syntax just described applies to how the mysqld server parses SQL statements. The mysql client program also performs some parsing of statements before sending them to the server. (It does this to determine statement boundaries within a multiple-statement input line.)
A common problem stems from trying to use an identifier such as a
table or column name that is a reserved word such as
SELECT
or the name of a built-in MySQL data
type or function such as TIMESTAMP
or
GROUP
.
If an identifier is a reserved word, you must quote it as described in Section 9.2, “Database, Table, Index, Column, and Alias Names”. Exception: A word that follows a period in a qualified name must be an identifier, so it is not necessary to quote it, even if it is a reserved word.
You are permitted to use function names as identifiers. For
example, ABS
is acceptable as a column name.
However, by default, no whitespace is allowed in function
invocations between the function name and the following
‘(
’ character. This requirement
allows a function call to be distinguished from a reference to a
column name.
A side effect of this behavior is that omitting a space in some contexts causes an identifier to be interpreted as a function name. For example, this statement is legal:
mysql> CREATE TABLE abs (val INT);
But omitting the space after abs
causes a
syntax error because the statement then appears to invoke the
ABS()
function:
mysql> CREATE TABLE abs(val INT);
ERROR 1064 (42000) at line 2: You have an error in your SQL
syntax ... near 'abs(val INT)'
If the IGNORE_SPACE
SQL mode is enabled, the
server allows function invocations to have whitespace between a
function name and the following ‘(
’
character. This causes function names to be treated as reserved
words. As a result, identifiers that are the same as function
names must be quoted as described in
Section 9.2, “Database, Table, Index, Column, and Alias Names”. The server SQL mode is controlled
as described in Section 5.2.5, “The Server SQL Mode”.
The words in the following table are explicitly reserved in MySQL
5.0. At some point, you might update to a higher
version, so it's a good idea to have a look at future reserved
words, too. You can find these in the manuals that cover higher
versions of MySQL. Most of the words in the table are forbidden by
standard SQL as column or table names (for example,
GROUP
). A few are reserved because MySQL needs
them and (currently) uses a yacc parser. A
reserved word can be used as an identifier if you quote it.
ADD | ALL | ALTER |
ANALYZE | AND | AS |
ASC | ASENSITIVE | BEFORE |
BETWEEN | BIGINT | BINARY |
BLOB | BOTH | BY |
CALL | CASCADE | CASE |
CHANGE | CHAR | CHARACTER |
CHECK | COLLATE | COLUMN |
CONDITION | CONNECTION | CONSTRAINT |
CONTINUE | CONVERT | CREATE |
CROSS | CURRENT_DATE | CURRENT_TIME |
CURRENT_TIMESTAMP | CURRENT_USER | CURSOR |
DATABASE | DATABASES | DAY_HOUR |
DAY_MICROSECOND | DAY_MINUTE | DAY_SECOND |
DEC | DECIMAL | DECLARE |
DEFAULT | DELAYED | DELETE |
DESC | DESCRIBE | DETERMINISTIC |
DISTINCT | DISTINCTROW | DIV |
DOUBLE | DROP | DUAL |
EACH | ELSE | ELSEIF |
ENCLOSED | ESCAPED | EXISTS |
EXIT | EXPLAIN | FALSE |
FETCH | FLOAT | FLOAT4 |
FLOAT8 | FOR | FORCE |
FOREIGN | FROM | FULLTEXT |
GOTO | GRANT | GROUP |
HAVING | HIGH_PRIORITY | HOUR_MICROSECOND |
HOUR_MINUTE | HOUR_SECOND | IF |
IGNORE | IN | INDEX |
INFILE | INNER | INOUT |
INSENSITIVE | INSERT | INT |
INT1 | INT2 | INT3 |
INT4 | INT8 | INTEGER |
INTERVAL | INTO | IS |
ITERATE | JOIN | KEY |
KEYS | KILL | LABEL |
LEADING | LEAVE | LEFT |
LIKE | LIMIT | LINES |
LOAD | LOCALTIME | LOCALTIMESTAMP |
LOCK | LONG | LONGBLOB |
LONGTEXT | LOOP | LOW_PRIORITY |
MATCH | MEDIUMBLOB | MEDIUMINT |
MEDIUMTEXT | MIDDLEINT | MINUTE_MICROSECOND |
MINUTE_SECOND | MOD | MODIFIES |
NATURAL | NOT | NO_WRITE_TO_BINLOG |
NULL | NUMERIC | ON |
OPTIMIZE | OPTION | OPTIONALLY |
OR | ORDER | OUT |
OUTER | OUTFILE | PRECISION |
PRIMARY | PROCEDURE | PURGE |
RAID0 | READ | READS |
REAL | REFERENCES | REGEXP |
RELEASE | RENAME | REPEAT |
REPLACE | REQUIRE | RESTRICT |
RETURN | REVOKE | RIGHT |
RLIKE | SCHEMA | SCHEMAS |
SECOND_MICROSECOND | SELECT | SENSITIVE |
SEPARATOR | SET | SHOW |
SMALLINT | SONAME | SPATIAL |
SPECIFIC | SQL | SQLEXCEPTION |
SQLSTATE | SQLWARNING | SQL_BIG_RESULT |
SQL_CALC_FOUND_ROWS | SQL_SMALL_RESULT | SSL |
STARTING | STRAIGHT_JOIN | TABLE |
TERMINATED | THEN | TINYBLOB |
TINYINT | TINYTEXT | TO |
TRAILING | TRIGGER | TRUE |
UNDO | UNION | UNIQUE |
UNLOCK | UNSIGNED | UPDATE |
UPGRADE | USAGE | USE |
USING | UTC_DATE | UTC_TIME |
UTC_TIMESTAMP | VALUES | VARBINARY |
VARCHAR | VARCHARACTER | VARYING |
WHEN | WHERE | WHILE |
WITH | WRITE | X509 |
XOR | YEAR_MONTH | ZEROFILL |
The following are new reserved words in MySQL 5.0:
ASENSITIVE
, CALL
,
CONDITION
, CONNECTION
,
CONTINUE
, CURSOR
,
DECLARE
, DETERMINISTIC
,
EACH
, ELSEIF
,
EXIT
, FETCH
,
GOTO
, INOUT
,
INSENSITIVE
, ITERATE
,
LABEL
, LEAVE
,
LOOP
, MODIFIES
,
OUT
, READS
,
RELEASE
, REPEAT
,
RETURN
, SCHEMA
,
SCHEMAS
, SENSITIVE
,
SPECIFIC
, SQL
,
SQLEXCEPTION
, SQLSTATE
,
SQLWARNING
, TRIGGER
,
UNDO
, UPGRADE
,
WHILE
.
MySQL allows some keywords to be used as unquoted identifiers because many people previously used them. Examples are those in the following list:
ACTION
BIT
DATE
ENUM
NO
TEXT
TIME
TIMESTAMP