This chapter discusses the rules for writing the following elements of SQL statements when using MySQL:
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 characters, surrounded by either single quote (`'') or double quote. Examples:
'a string' "another string"
If the server SQL mode has ANSI_QUOTES enabled, string literals can be
quoted only with single quotes. (A string quoted with double quotes will be
interpreted as an identifier.)
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:
\0
NUL) character.
\'
\"
\b
\n
\r
\t
\z
mysql db_name < file_name.)
\\
\%
\_
These sequences are case sensitive. For example, `\b' is interpreted as a backslash, but `\B' is interpreted as `B'.
Note that if you use `\%' or `\_' in some string contexts, these will return the strings `\%' and `\_' and not `%' and `_'.
There are several ways to include quotes within a string:
The SELECT statements shown here 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 | +--------------------+
If you want to insert binary data into a string column (such as a
BLOB), 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).
\
'
"
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 a SQL statement that is sent to the MySQL server. You can do this in two ways:
mysql_real_escape_string() to escape characters.
See section 19.1.2 C API Function Overview. The Perl DBI interface provides a
quote method to convert special characters to the proper escape
sequences. See section 19.5 MySQL Perl API.
Integers are represented as a sequence of digits. Floats use `.' as a decimal separator. Either type of number may be preceded by `-' to indicate a negative value.
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'
In MySQL 4.1 (and in MySQL 4.0 when using the --new option) the default
type of 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 0x syntax is based on ODBC. Hexadecimal strings are often used by
ODBC to supply values for BLOB columns.
The x'hexstring' syntax is new in 4.0 and is based on standard SQL.
Beginning with MySQL 4.0.1, 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'
Beginning with MySQL 4.1.0, the constant TRUE evaluates to 1 and
the constant FALSE evaluates to 0. The constant names can be
written in any lettercase.
mysql> SELECT TRUE, true, FALSE, false;
-> 1, 1, 0, 0
NULL Values
The NULL value means ``no data.''
NULL can be written in any lettercase.
Be aware that the NULL value is different than values such
as 0 for numeric types or the empty string for string types.
See section A.5.3 Problems with NULL Values.
NULL may be represented by \N when using the text file import
or export formats (LOAD DATA INFILE, SELECT ... INTO OUTFILE).
See section 13.1.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, ASCII 255, or the quoting character.
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. For a list of reserved words, see section 10.6 Treatment of Reserved Words in MySQL. Special characters are those outside the set of alphanumeric characters from the current character set, `_', and `$'.
The quote character is the backtick (``'):
mysql> SELECT * FROM `select` WHERE `select`.id > 100;
If the server SQL mode includes the ANSI_QUOTES mode option,
it is also allowable to quote identifiers with 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)
See section 1.8.2 Selecting SQL Modes.
Identifier quoting was introduced in MySQL 3.23.6 to allow use of identifiers that are reserved words or that contain special characters. Before 3.23.6, cannot use identifiers that require quotes, so the rules for legal identifiers are more restrictive:
--default-character-set option
to mysqld.
See section 5.6.1 The Character Set Used for Data and Sorting.
It is recommended that you do not use names like 1e, because
an expression like 1e+1 is ambiguous. It may be interpreted as the
expression 1e + 1 or as the number 1e+1.
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 | Column col_name
from whichever table used in the query contains a column of that name.
|
tbl_name.col_name | Column col_name from table
tbl_name of the current database.
|
db_name.tbl_name.col_name | Column col_name from table
tbl_name of the database db_name. This syntax is unavailable
before MySQL Version 3.22.
|
If any components of a multiple-part name require quoting, quote them
individually rather than quoting the name as a whole. For example,
`my-table`.`my-column` is legal, but `my-table.my-column` is
not.
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 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.col_name
and db2.t.col_name.
The syntax .tbl_name means the table tbl_name in the current
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. Tables within a database correspond 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 not case sensitive in Windows, and case sensitive in most varieties of Unix. One notable exception is Mac OS X, for which the default filesystem type (HFS+) is not case sensitive. However Mac OS X also supports UFS volumes, which are case sensitive just as on any Unix. See section 1.8.4 MySQL Extensions to the SQL-92 Standard.
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 query. The following query 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 names, index names, and column aliases are not case sensitive on any platform.
Table aliases are case sensitive before MySQL 4.1.1. The following
query would not work because it refers to the alias both as a and
as A:
mysql> SELECT col_name FROM tbl_name AS a
-> WHERE a.col_name = 1 OR A.col_name = 2;
If you have trouble remembering the lettercase for database and table names, adopt a consistent convention, such as always creating databases and tables using lowercase names.
How table names are stored on disk and used in MySQL is defined by the
lower_case_table_names variable, which you can set when starting
mysqld. It can take one of the following values:
| Value | Meaning |
| 0 | Table and database names are case sensitive and are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. This is the default on Unix systems.
|
| 1 | Table and database names are not case sensitive and are stored on disk in lowercase. MySQL will convert all table names to lowercase on storage and lookup. This is the default on Windows and Mac OS X systems. (This option also applies to database names as of MySQL 4.0.2, and to table aliases as of 4.1.1.) |
| 2 | New in 4.0.18: Table and database names are not case sensitive, but are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. MySQL will convert all table names to lowercase on storage and lookup. Note: This works only on file systems that are not case sensitive!
|
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 those names
are considered the same. To avoid data transfer problems stemming from
database or table name lettercase, you have two options:
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 case.
lower_case_table_names=0 on Unix and lower_case_table_names=2
on Windows. This will preserve the case of database and table names.
The disadvantage of this is that you must ensure that you always refer
to your database and table names with the correct case on Windows as things will
not work on Unix if you use the wrong case (as Unix is case sensitive).
Note that if you set lower_case_table_names to 1 on Unix, you must
first convert your old database and table names to lowercase before restarting
mysqld.
MySQL supports user variables as of version 3.23.6. You can store a value in a user variable and refer to it later, which allows you to pass values from one statement to another. User variables are connection-specific. That is, a variable defined by one client cannot be seen or used by other clients. All variables for a client connection are automatically freed when the client exits.
User variables are written as @var_name, where the variable name
var_name may consist of alphanumeric characters from the current
character set, `_', `$', and `.' . The default character set
is ISO-8859-1 (Latin1). This may be changed with the
--default-character-set option to mysqld. See section 5.6.1 The Character Set Used for Data and Sorting. User variable names are not case insensitive beginning with MySQL 5.0.
Before that, they are case sensitive.
One way to set a user variable is by issuing a SET statement:
SET @var_name = expression [,@var_name = expression] ...
The expression assigned to the 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. However, in this case, the assignment operator is :=
rather than =, 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 where expressions are allowed. However, this
does not currently include contexts that explicitly require a number, such
as in the LIMIT clause of a SELECT statement, or the
IGNORE number LINES clause of a LOAD DATA statement.
If you refer to a variable that has not been initialized, its value
is NULL.
Note: In a SELECT statement, each expression is evaluated
only when it's sent to the client. This means that in the HAVING,
GROUP BY, or ORDER BY clause, you can't refer to an expression
that involves variables that are set in the SELECT part. For example,
the following statement will not work as expected:
mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM table_name HAVING b=5;
The reason is that @aa will not contain the value of the current
row, but the value of id from the previous selected row.
The general rule is to never assign and use the same variable in the same statement.
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 table_name;
For the SELECT statement, MySQL will report to the client that
column one is a string and convert all accesses of @a to strings,
even if @a will be set to a number for the second row. After the
SELECT statement is executed, @a will be regarded as a
number for the next statement.
An unassigned variable has a value of NULL with a type of string.
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.
Starting from MySQL 4.0.3, we provide better access to a lot of system and connection variables. Many variables can be changed dynamically while the server is running. This allows you to modify server operation without having to stop and restart it.
The mysqld server maintains two kinds of variables while it runs.
Global variables affect the overall operation of the server.
Session variables affect its operation for individual client connections.
When the server starts, it initializes all global variables to their default
values. These defaults may be changed by options specified in option files
or on the command line. After the server starts up, those global variables
that are dynamic can be changed by connecting to the server and issuing
a SET GLOBAL var_name statement. To change a global variable,
you must have the SUPER privilege.
The server also maintains a set of session variables for each client
that connects. The client's session variables are initialized at connect
time using the current values of the corresponding global variables. For
those session variables that are dynamic, the client can change them
by issuing a SET SESSION var_name statement. Setting a session
variable requires no special privilege, but a client can change only its
own session variables, not those of any other client.
A change to a global variable affects only client connections that occur
after the change. It does not affect session variables for any client
that is already connected (not even those of the client that issues the
SET GLOBAL statement).
Global or session variables may be set or retrieved using several syntax
forms. The following examples use sort_buffer_size as a sample variable
name.
To set the value of a GLOBAL variable, use one
of the following syntaxes:
mysql> SET GLOBAL sort_buffer_size=value; mysql> SET @@global.sort_buffer_size=value;
To set the value of a SESSION variable, use one of the
following syntaxes:
mysql> SET SESSION sort_buffer_size=value; mysql> SET @@session.sort_buffer_size=value; mysql> SET sort_buffer_size=value;
LOCAL is a synonym for SESSION.
If you don't specify GLOBAL, SESSION, or LOCAL when
setting a variable, SESSION is the default.
See section 7.5.6 SET Syntax.
To retrieve the value of a GLOBAL variable, use one of the
following statements:
mysql> SELECT @@global.sort_buffer_size; mysql> SHOW GLOBAL VARIABLES like 'sort_buffer_size';
To retrieve the value of a SESSION variable, use one of the
following statements:
mysql> SELECT @@session.sort_buffer_size; mysql> SHOW SESSION VARIABLES like 'sort_buffer_size';
Here, too, LOCAL is a synonym for SESSION.
If you don't specify @@global, @@session, or
@@local when retrieving a variable with SELECT, MySQL returns
the SESSION value if it exists and the GLOBAL value otherwise.
If you don't specify GLOBAL, SESSION, or LOCAL for
SHOW VARIABLES, MySQL returns the SESSION value.
The reason for requiring the GLOBAL keyword when setting
GLOBAL-only variables but not when retrieving them is to ensure that
we don't later run into problems if we would introduce a SESSION
variable with the same name or remove a SESSION variable. In that
case, a client with the SUPER privilege might change the
GLOBAL variable by accident, rather than just the SESSION
variable for the client's own connection.
Further information about system variables can be found in the
startup options section, and in the descriptions of the SHOW VARIABLES
and SET statements.
See section 5.2.1 mysqld Command-line Options.
See section 13.5.3.4 SHOW VARIABLES.
See section 7.5.6 SET Syntax.
The following table shows the full list of all variables that you can change
and retrieve using GLOBAL or SESSION. The last column indicates
for each variable whether GLOBAL or SESSION (or both) apply.
| Variable name | Value type | Type |
autocommit | bool | SESSION
|
big_tables | bool | SESSION
|
binlog_cache_size | num | GLOBAL
|
bulk_insert_buffer_size | num | GLOBAL | SESSION
|
concurrent_insert | bool | GLOBAL
|
connect_timeout | num | GLOBAL
|
convert_character_set | string | GLOBAL | SESSION
|
delay_key_write | OFF | ON | ALL | GLOBAL
|
delayed_insert_limit | num | GLOBAL
|
delayed_insert_timeout | num | GLOBAL
|
delayed_queue_size | num | GLOBAL
|
error_count | num | SESSION
|
flush | bool | GLOBAL
|
flush_time | num | GLOBAL
|
foreign_key_checks | bool | SESSION
|
identity | num | SESSION
|
insert_id | bool | SESSION
|
interactive_timeout | num | GLOBAL | SESSION
|
join_buffer_size | num | GLOBAL | SESSION
|
key_buffer_size | num | GLOBAL
|
last_insert_id | num | SESSION
|
local_infile | bool | GLOBAL
|
log_warnings | bool | GLOBAL
|
long_query_time | num | GLOBAL | SESSION
|
low_priority_updates | bool | GLOBAL | SESSION
|
max_allowed_packet | num | GLOBAL | SESSION
|
max_binlog_cache_size | num | GLOBAL
|
max_binlog_size | num | GLOBAL
|
max_connect_errors | num | GLOBAL
|
max_connections | num | GLOBAL
|
max_error_count | num | GLOBAL | SESSION
|
max_delayed_threads | num | GLOBAL
|
max_heap_table_size | num | GLOBAL | SESSION
|
max_join_size | num | GLOBAL | SESSION
|
max_relay_log_size | num | GLOBAL
|
max_seeks_for_key | num | GLOBAL | SESSION
|
max_sort_length | num | GLOBAL | SESSION
|
max_tmp_tables | num | GLOBAL
|
max_user_connections | num | GLOBAL
|
max_write_lock_count | num | GLOBAL
|
myisam_max_extra_sort_file_size | num | GLOBAL | SESSION
|
myisam_repair_threads | num | GLOBAL | SESSION
|
myisam_max_sort_file_size | num | GLOBAL | SESSION
|
myisam_sort_buffer_size | num | GLOBAL | SESSION
|
net_buffer_length | num | GLOBAL | SESSION
|
net_read_timeout | num | GLOBAL | SESSION
|
net_retry_count | num | GLOBAL | SESSION
|
net_write_timeout | num | GLOBAL | SESSION
|
query_alloc_block_size | num | GLOBAL | SESSION
|
query_cache_limit | num | GLOBAL
|
query_cache_size | num | GLOBAL
|
query_cache_type | enum | GLOBAL
|
query_prealloc_size | num | GLOBAL | SESSION
|
range_alloc_block_size | num | GLOBAL | SESSION
|
read_buffer_size | num | GLOBAL | SESSION
|
read_rnd_buffer_size | num | GLOBAL | SESSION
|
rpl_recovery_rank | num | GLOBAL
|
safe_show_database | bool | GLOBAL
|
server_id | num | GLOBAL
|
slave_compressed_protocol | bool | GLOBAL
|
slave_net_timeout | num | GLOBAL
|
slow_launch_time | num | GLOBAL
|
sort_buffer_size | num | GLOBAL | SESSION
|
sql_auto_is_null | bool | SESSION
|
sql_big_selects | bool | SESSION
|
sql_big_tables | bool | SESSION
|
sql_buffer_result | bool | SESSION
|
sql_log_binlog | bool | SESSION
|
sql_log_off | bool | SESSION
|
sql_log_update | bool | SESSION
|
sql_low_priority_updates | bool | GLOBAL | SESSION
|
sql_max_join_size | num | GLOBAL | SESSION
|
sql_quote_show_create | bool | SESSION
|
sql_safe_updates | bool | SESSION
|
sql_select_limit | bool | SESSION
|
sql_slave_skip_counter | num | GLOBAL
|
sql_warnings | bool | SESSION
|
table_cache | num | GLOBAL
|
table_type | enum | GLOBAL | SESSION
|
thread_cache_size | num | GLOBAL
|
timestamp | bool | SESSION
|
tmp_table_size | enum | GLOBAL | SESSION
|
transaction_alloc_block_size | num | GLOBAL | SESSION
|
transaction_prealloc_size | num | GLOBAL | SESSION
|
tx_isolation | enum | GLOBAL | SESSION
|
wait_timeout | num | GLOBAL | SESSION
|
warning_count | num | SESSION
|
unique_checks | bool | SESSION
|
Variables that are marked as num can be given a numeric
value. Variables that are marked as bool can be set to 0, 1,
ON or OFF. Variables that are marked as enum should
normally be set to one of the available values for the variable, but can
also be set to the number that correspond to the desired enumeration value.
(The first enumeration value corresponds to 0; note that this differs
from ENUM columns, for which the first enumeration value
corresponds to 1.)
Here is a description of some of the variables:
| Variable | Description |
identity | Alias for last_insert_id (Sybase compatibility)
|
sql_low_priority_updates | Alias for low_priority_updates
|
sql_max_join_size | Alias for max_join_size
|
version | Alias for VERSION() (Sybase (?) compatibility)
|
Structured system variables are supported beginning with MySQL 4.1.1. A structured variable differs from a regular system variable in two respects:
Currently, MySQL supports one structured variable type. It specifies parameters that govern the operation of key caches. A key cache structured variable has these components:
key_buffer_size
key_cache_block_size
key_cache_division_limit
key_cache_age_threshold
The purpose of this section is to describe the syntax for referring to structured variables. Key cache variables are used for syntax examples, but specific details about how key caches operate are found elsewhere, in section 7.4.6 The MyISAM Key Cache.
To refer to a component of a structured variable instance, you can use a
compound name in instance_name.component_name format. Examples:
hot_cache.key_buffer_size hot_cache.key_cache_block_size. cold_cache.key_cache_block_size.
An instance with the name of default is always predefined for each
structured system variable. If you refer to a component of a structured
variable without any instance name, the default instance is used.
Thus, default.key_buffer_size and key_buffer_size both refer
to the same system variable.
The naming rules for structured variable instances and components are as follows:
default,
so default is not unique across variable types.
At the moment, these rules have no possibility of being violated, because the only structured variable type is the one for key caches. If some other type of structured variable is created in the future, these rules will assume greater significance.
With one exception, it is allowable to refer to structured variable components using compound names in any context where simple variable names can occur.
For example, you can assign a value to a structured variable using a command line option:
shell> mysqld --hot_cache.key_buffer_size=64K
In an option file, do this:
[mysqld] hot_cache.key_buffer_size=64K
If you start the server with such an option, it creates a key cache
named hot_cache with a size of 64 KB in addition to the default
key cache that has a default size of 8 MB.
Suppose you start the server as follows:
shell> mysqld --key_buffer_size=256K \
--extra_cache.key_buffer_size=128K \
--extra_cache.key_cache_block_size=2096
In this case, the server sets the size of the default key cache to 256 KB.
(You could also have written --default.key_buffer_size=256.)
In addition, the server creates a second key cache named extra_cache
that has a size of 128 KB, with the size of block buffers for caching
table index blocks set to 2096 bytes.
The following example starts the server with three different key caches having sizes in a 3:1:1 ratio:
shell> mysqld --key_buffer_size=6M \
--hot_cache.key_buffer_size=2M \
--cold_cache.key_buffer_size=2M
Structured variable values may be set and retrieved at runtime as well.
For example, to set a key cache named hot_cache to a size of 10 MB,
use either of these statements:
mysql> SET GLOBAL hot_cache.key_buffer_size = 10*1024*1024; mysql> SET @global.hot_cache.key_buffer_size = 10*1024*1024;
To retrieve the cache size, do this:
mysql> SELECT @global.hot_cache.key_buffer_size;
However, the following statement does not work, because the variable is not
interpreted as a compound name, but as a simple string for a LIKE
pattern-matching operation:
mysql> SHOW GLOBAL VARIABLES LIKE 'hot_cache.key_buffer_size';
This is the exception to being able to use structured variable names anywhere a simple variable name may occur.
The MySQL server supports three comment styles:
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;
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. (For example, it
does this to determine statement boundaries within a multiple-statement input
line.) However, there are some limitations on the way that mysql
parses /* ... */ comments:
mysql interactively, you can tell that it
has gotten confused like this because the prompt changes from mysql>
to '> or ">.
This problem was fixed in MySQL 4.1.1.
For affected versions of MySQL,
these limitations apply both when you run mysql interactively
and when you put commands in a file and use mysql in batch mode to
process the file with mysql < file_name.
A common problem stems from trying to use an identifier such as a table or
column name that is the name of a built-in MySQL datatype or function,
such as TIMESTAMP or GROUP. You're allowed
to do this (for example, ABS is allowed 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);
If the server SQL mode includes the IGNORE_SPACE mode value, 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 10.2 Database, Table, Index, Column, and Alias Names. The server
SQL mode is controlled as described in section 1.8.2 Selecting SQL Modes.
The words in the following table are explicitly reserved in MySQL. Most of
them are forbidden by SQL-92 as column and/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 by
quoting it.
| Word | Word | Word |
ADD
| ALL
| ALTER
|
ANALYZE
| AND
| AS
|
ASC
| ASENSITIVE
| AUTO_INCREMENT
|
BDB
| BEFORE
| BERKELEYDB
|
BETWEEN
| BIGINT
| BINARY
|
BLOB
| BOTH
| BY
|
CALL
| CASCADE
| CASE
|
CHANGE
| CHAR
| CHARACTER
|
CHECK
| COLLATE
| COLUMN
|
COLUMNS
| CONDITION
| CONNECTION
|
CONSTRAINT
| CONTINUE
| CREATE
|
CROSS
| CURRENT_DATE
| CURRENT_TIME
|
CURRENT_TIMESTAMP
| 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
| ELSE
| ELSEIF
|
ENCLOSED
| ESCAPED
| EXISTS
|
EXIT
| EXPLAIN
| FALSE
|
FETCH
| FIELDS
| FLOAT
|
FOR
| FORCE
| FOREIGN
|
FOUND
| FRAC_SECOND
| FROM
|
FULLTEXT
| GRANT
| GROUP
|
HAVING
| HIGH_PRIORITY
| HOUR_MICROSECOND
|
HOUR_MINUTE
| HOUR_SECOND
| IF
|
IGNORE
| IN
| INDEX
|
INFILE
| INNER
| INNODB
|
INOUT
| INSENSITIVE
| INSERT
|
INT
| INTEGER
| INTERVAL
|
INTO
| IO_THREAD
| IS
|
ITERATE
| JOIN
| KEY
|
KEYS
| KILL
| LEADING
|
LEAVE
| LEFT
| LIKE
|
LIMIT
| LINES
| LOAD
|
LOCALTIME
| LOCALTIMESTAMP
| LOCK
|
LONG
| LONGBLOB
| LONGTEXT
|
LOOP
| LOW_PRIORITY
| MASTER_SERVER_ID
|
MATCH
| MEDIUMBLOB
| MEDIUMINT
|
MEDIUMTEXT
| MIDDLEINT
| MINUTE_MICROSECOND
|
MINUTE_SECOND
| MOD
| NATURAL
|
NOT
| NO_WRITE_TO_BINLOG
| NULL
|
NUMERIC
| ON
| OPTIMIZE
|
OPTION
| OPTIONALLY
| OR
|
ORDER
| OUT
| OUTER
|
OUTFILE
| PRECISION
| PRIMARY
|
PRIVILEGES
| PROCEDURE
| PURGE
|
READ
| REAL
| REFERENCES
|
REGEXP
| RENAME
| REPEAT
|
REPLACE
| REQUIRE
| RESTRICT
|
RETURN
| REVOKE
| RIGHT
|
RLIKE
| SECOND_MICROSECOND
| SELECT
|
SENSITIVE
| SEPARATOR
| SET
|
SHOW
| SMALLINT
| SOME
|
SONAME
| SPATIAL
| SPECIFIC
|
SQL
| SQLEXCEPTION
| SQLSTATE
|
SQLWARNING
| SQL_BIG_RESULT
| SQL_CALC_FOUND_ROWS
|
SQL_SMALL_RESULT
| SQL_TSI_DAY
| SQL_TSI_FRAC_SECOND
|
SQL_TSI_HOUR
| SQL_TSI_MINUTE
| SQL_TSI_MONTH
|
SQL_TSI_QUARTER
| SQL_TSI_SECOND
| SQL_TSI_WEEK
|
SQL_TSI_YEAR
| SSL
| STARTING
|
STRAIGHT_JOIN
| STRIPED
| TABLE
|
TABLES
| TERMINATED
| THEN
|
TIMESTAMPADD
| TIMESTAMPDIFF
| TINYBLOB
|
TINYINT
| TINYTEXT
| TO
|
TRAILING
| TRUE
| UNDO
|
UNION
| UNIQUE
| UNLOCK
|
UNSIGNED
| UPDATE
| USAGE
|
USE
| USER_RESOURCES
| USING
|
UTC_DATE
| UTC_TIME
| UTC_TIMESTAMP
|
VALUES
| VARBINARY
| VARCHAR
|
VARCHARACTER
| VARYING
| WHEN
|
WHERE
| WHILE
| WITH
|
WRITE
| XOR
| YEAR_MONTH
|
ZEROFILL
|
The following symbols (from the table above) are disallowed by SQL-99 but allowed by MySQL as column/table names. This is because they are very natural names and a lot of people have already used them.
ACTION
BIT
DATE
ENUM
NO
TEXT
TIME
TIMESTAMP
Go to the first, previous, next, last section, table of contents.