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

Get MySQL Reference Manual now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.