MySQL Reference Manual

Chapter 6. MySQL Language Reference

MySQL has a very complex but intuitive and easy-to-learn SQL interface. This chapter describes the various commands, types, and functions you will need to know in order to use MySQL efficiently and effectively. This chapter also serves as a reference to all functionality included in MySQL. In order to use this chapter effectively, you may find it useful to refer to the various indexes.

Language Structure

Literals: How to Write Strings and Numbers

This section describes the various ways to write strings and numbers in MySQL. It also covers the various nuances and “gotchas” that you may run into when dealing with these basic types in MySQL.

Strings

A string is a sequence of characters, surrounded by either single quote (') or double quote (“) characters (only the single quote if you run in ANSI mode). Examples:

'a string'
"another string"

Within a string, certain sequences have special meaning. Each of these sequences begins with a backslash (\), known as the escape character. MySQL recognises the following escape sequences:

\0

An ASCII 0 (NUL) character.

\'

A single quote (') character.

\"

A double quote (") character.

\b

A backspace character.

\n

A newline character.

\r

A carriage return character.

\t

A tab character.

\z

ASCII(26) (Control-Z). This character can be encoded to allow you to work around the problem that ASCII(26) stands for END-OF-FILE on Windows. (ASCII(26) will cause problems if you try to use mysql database < filename.)

\\

A backslash (\) character.

\%

A % character. This is used to search for literal instances of % in contexts where % would otherwise be interpreted as a wildcard character. See Section 6.3.2.1.

\_

A _ character. This is used to search for literal instances of _ in contexts where _ would otherwise be interpreted as a wildcard character. See Section 6.3.2.1.

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:

• A ' inside a string quoted with ' may be written as “.

• A " inside a string quoted with " may be written as “”.

• You can precede the quote character with 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 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

ASCII 0. You should represent this by \0 (a backslash and an ASCII 0 character).

\

ASCII 92, backslash. Represent this by \\.

'

ASCII 39, single quote. Represent this by \‘.

"

ASCII 34, double quote. Represent this by \“.

If you write C code, you can use the C API function mysql_real_escape_string( ) to escape characters for the INSERT statement. See Section 8.4.2. In Perl, you can use the quote method of the DBI package to convert special characters to the proper escape sequences. See Section 8.2.2.

You should use an escape function on any string that might contain any of the special characters listed previously!

Alternatively, many MySQL APIs provide some sort of placeholder capability that allows you to insert special markers into a query string, and then bind data values to them when you issue the query. In this case, the API takes case of escaping special characters in the values for you automatically.

Numbers

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.

Hexadecimal values

MySQL supports hexadecimal values. In numeric context these act like an integer (64-bit precision). In string context these act like a binary string where each pair of hex digits is converted to a character:

mysql> SELECT x'FF'
         -> 255
mysql> SELECT 0xa+0;
         -> 10
mysql> SELECT 0x5061756c;
         -> Paul

The x'hexstring' syntax (new in 4.0) is based on ANSI SQL and the 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 hexadecimal with the HEX( ) function.

NULL values

The NULL value means “no data” and is different from values such as 0 for numeric types or the empty string for string types. See Section A.5.3.

NULL may be represented by \N when using the text file import or export formats (LOAD DATA INFILE, SELECT ... INTO OUTFILE). See Section 6.4.9.

Database, Table, Index, Column, and Alias Names

Database, table, index, column, and alias names all follow the same rules in MySQL. The following table shows the lengths and characters allowed.

Note that the rules changed starting with MySQL Version 3.23.6 when we introduced quoting of identifiers (database, table, and column names) with ‘. " will also work to quote identifiers if you run in ANSI mode. See Section 1.7.2.

Identifier	Max length	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
Alias	255	All characters

Note that in addition to this, you can’t have ASCII(0) or ASCII(255) or the quoting character in an identifier.

Note that if the identifier is a restricted word or contains special characters, you must always quote it with ' when you use it:

mysql> SELECT * FROM `select` WHERE `select`.id > 100;

In previous versions of MySQL, the name rules are as follows:

• A name may consist of alphanumeric characters from the current character set as well as _ and $. The default character set is ISO-8859-1 Latin1; this may be changed with the --default-character-set option to mysqld. See Section 4.6.1.

• A name may start with any character that is legal in a name. In particular, a name may start with a digit (this differs from many other database systems!). However, a name cannot consist only of digits.

• You cannot use the . character in names because it is used to extend the format by which you can refer to columns.

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.

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 form is available in MySQL Versions 3.22 or later.
`column_name`	A column that is a keyword or contains special characters.

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. For example, 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, so you must indicate which table you mean by writing t1.c or t2.c. Similarly, if you are retrieving from a table t in database db1 and from a table t in database db2, 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.

Case Sensitivity in Names

In MySQL, databases and tables correspond to directories and files within those directories. 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-insensitive in Windows and case-sensitive in most varieties of Unix (Mac OS X being an exception). See Section 1.7.3.

Note: although database and table names are case-insensitive for Windows, 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 and column aliases are case-insensitive in all cases.

Aliases on tables are case-sensitive. 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.

One way to avoid this problem is to start mysqld with -O lower_case_table_names=1. By default this option is 1 on Windows and 0 on Unix.

If lower_case_table_names is 1 MySQL will convert all table names to lowercase on storage and lookup. Note that if you change this option, you need to first convert your old table names to lowercase before starting mysqld.

User Variables

MySQL supports thread-specific variables with the @variablename syntax. A variable name may consist of alphanumeric characters from the current character set and also _, $, and . . The default character set is ISO-8859-1 Latin1; this may be changed with the --default-character-set option to mysqld. See Section 4.6.1.

Variables don’t have to be initialised. They contain NULL by default and can store an integer, real, or string value. All variables for a thread are automatically freed when the thread exits.

You can set a variable with the SET syntax:

SET @variable= { integer expression | real expression | string expression }
[,@variable= ...].

You can also assign a value to a variable in statements other than SET. However, in this case the assignment operator is := rather than = because = is reserved for comparisons in non-SET statements:

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. Note that this does not currently include contexts where a number is explicitly required, such as in the LIMIT clause of a SELECT statement, or the IGNORE number LINES clause of a LOAD DATA statement.

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 for the previously accepted row.

Comment Syntax

The MySQL server supports the # to end of line, -- to end of line, and /* in-line or multiple-line */ 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;

Note that the -- (double-dash) comment style requires you to have at least one space after the second dash!

Although the server understands the comment syntax just described, there are some limitations on the way that the mysql client parses /* ... */ comments:

• Single-quote and double-quote characters are taken to indicate the beginning of a quoted string, even within a comment. If the quote is not matched by a second quote within the comment, the parser doesn’t realise the comment has ended. If you are running mysql interactively, you can tell that it has gotten confused like this because the prompt changes from mysql> to '> or ">.

• A semicolon is taken to indicate the end of the current SQL statement and anything following it to indicate the beginning of the next statement.

These limitations apply both when you run mysql interactively and when you put commands in a file and tell mysql to read its input from that file with mysql < some-file.

MySQL supports the -- ANSI SQL comment style only if the second dash is followed by a space. See Section 1.7.4.7.

Is MySQL Picky About Reserved Words?

A common problem stems from trying to create a table with column names that use the names of datatypes or functions built into MySQL, such as TIMESTAMP or GROUP. You’re allowed to do it (for example, ABS is an allowed column name), but whitespace is not allowed between a function name and the immediately following ( when using functions whose names are also column names.

The following words are explicitly reserved in MySQL. Most of them are forbidden by ANSI SQL92 as column and/or table names (for example, GROUP). A few are reserved because MySQL needs them and is (currently) using a yacc parser:

Word	Word	Word
ADD	ALL	ALTER
ANALYZE	AND	AS
ASC	AUTO_INCREMENT	BDB
BERKELEYDB	BETWEEN	BIGINT
BINARY	BLOB	BOTH
BY	CASCADE	CASE
CHANGE	CHAR	CHARACTER
COLUMN	COLUMNS	CONSTRAINT
CREATE	CROSS	CURRENT_DATE
CURRENT_TIME	CURRENT_TIMESTAMP	DATABASE
DATABASES	DAY_HOUR	DAY_MINUTE
DAY_SECOND	DEC	DECIMAL
DEFAULT	DELAYED	DELETE
DESC	DESCRIBE	DISTINCT
DISTINCTROW	DOUBLE	DROP
ELSE	ENCLOSED	ESCAPED
EXISTS	EXPLAIN	FIELDS
FLOAT	FOR	FOREIGN
FROM	FULLTEXT	FUNCTION
GRANT	GROUP	HAVING
HIGH_PRIORITY	HOUR_MINUTE	HOUR_SECOND
IF	IGNORE	IN
INDEX	INFILE	INNER
INNODB	INSERT	INSERT_ID
INT	INTEGER	INTERVAL
INTO	IS	JOIN
KEY	KEYS	KILL
LAST_INSERT_ID	LEADING	LEFT
LIKE	LIMIT	LINES
LOAD	LOCK	LONG
LONGBLOB	LONGTEXT	LOW_PRIORITY
MASTER_SERVER_ID	MATCH	MEDIUMBLOB
MEDIUMINT	MEDIUMTEXT	MIDDLEINT
MINUTE_SECOND	MRG_MYISAM	NATURAL
NOT	NULL	NUMERIC
ON	OPTIMIZE	OPTION
OPTIONALLY	OR	ORDER
OUTER	OUTFILE	PARTIAL
PRECISION	PRIMARY	PRIVILEGES
PROCEDURE	PURGE	READ
REAL	REFERENCES	REGEXP
RENAME	REPLACE	REQUIRE
RESTRICT	RETURNS	REVOKE
RIGHT	RLIKE	SELECT
SET	SHOW	SMALLINT
SONAME	SQL_AUTO_IS_NULL	SQL_BIG_RESULT
SQL_BIG_SELECTS	SQL_BIG_TABLES	SQL_BUFFER_RESULT
SQL_CALC_FOUND_ROWS	SQL_LOG_BIN	SQL_LOG_OFF
SQL_LOG_UPDATE	SQL_LOW_PRIORITY_UPDATES	SQL_MAX_JOIN_SIZE
SQL_QUOTE_SHOW_CREATE	SQL_SAFE_UPDATES	SQL_SELECT_LIMIT
SQL_SLAVE_SKIP_COUNTER	SQL_SMALL_RESULT	SQL_WARNINGS
SSL	STARTING	STRAIGHT_JOIN
STRIPED	TABLE	TABLES
TERMINATED	THEN	TINYBLOB
TINYINT	TINYTEXT	TO
TRAILING	UNION	UNIQUE
UNLOCK	UNSIGNED	UPDATE
USAGE	USE	USING
VALUES	VARBINARY	VARCHAR
VARYING	WHEN	WHERE
WITH	WRITE	YEAR_MONTH
ZEROFILL

The following symbols (from the preceding table) are disallowed by ANSI SQL but allowed by MySQL as column/table names. This is because some of these names are very natural names and a lot of people have already used them.

• ACTION

• BIT

• DATE

• ENUM

• NO

• TEXT

• TIME

• TIMESTAMP