Reference & Manual > MySQL Reference Manual > LANGUAGE-STRUCTURE

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'
"another string"

[_charset_name]'string' [COLLATE collation_name]

SELECT _latin1'string';
SELECT _latin1'string' COLLATE latin1_danish_ci;

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 |
+------------------------+

1221
0
-32

294.42
-32032.6809e+10
148.00

mysql> SELECT x'4D7953514C';
        -> 'MySQL'
mysql> SELECT 0xa+0;
        -> 10
mysql> SELECT 0x5061756c;
        -> 'Paul'

mysql> SELECT 0x41, CAST(0x41 AS UNSIGNED);
        -> 'A', 65

mysql> SELECT HEX('cat');
        -> '636174'
mysql> SELECT 0x636174;
        -> 'cat'

mysql> SELECT TRUE, true, FALSE, false;
        -> 1, 1, 0, 0

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 for each type of identifier.

There are some restrictions on the characters that may appear in identifiers:

Beginning with MySQL 4.1, 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. Although Unicode identifiers can include multi-byte characters, note that the maximum lengths shown in the table are byte counts until MySQL 4.1.5; until that version, if an identifier does contain multi-byte characters, the number of characters allowed in the identifier is less than the value shown in the table.

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 the ANSI_QUOTES mode causes the server to interpret double-quoted strings as identifiers, string literals must be enclosed within single quotes when this mode is enabled. They cannot be enclosed within double quotes.

The server SQL mode is controlled as described in Section 5.2.5, “The Server SQL Mode”.

As of MySQL 4.1, 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, 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);

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, you cannot use identifiers that require quotes, so the rules for legal identifiers are more restrictive:

It is recommended that you do not use names of the form Me or MeN, where M and N are integers. For example, avoid using 1e or 2e2 as identifiers, 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> SELECT * FROM my_table WHERE MY_TABLE.col=1;

mysql> SELECT col_name FROM tbl_name AS a
    -> WHERE a.col_name = 1 OR A.col_name = 2;

MySQL supports user variables as of version 3.23.6. 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 @var_name, where the variable 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.10.1, “The Character Set Used for Data and Sorting”. Section 5.10.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 prior to MySQL 5.0.

One way to set a user 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. However, if the value of the variable is selected in a result set, it is returned to the client as a string.

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 N LINES clause of a LOAD DATA statement.

Beginning with MySQL 4.1.1, 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 4.1.11 and 5.0.3. (This is the same coercibility as 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:

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.) However, there are some limitations on the way that mysql parses /* ... */ comments:

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 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 4.1. 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.

The following are new reserved words in MySQL 4.0: CHECK, FORCE, LOCALTIME, LOCALTIMESTAMP, REQUIRE, SQL_CALC_FOUND_ROWS, SSL, X509, XOR.

The following are new reserved words in MySQL 4.1: BEFORE, COLLATE, CONVERT, CURRENT_USER, DAY_MICROSECOND, DIV, DUAL, FALSE, HOUR_MICROSECOND, MINUTE_MICROSECOND, MOD, NO_WRITE_TO_BINLOG, SECOND_MICROSECOND, SEPARATOR, SPATIAL, TRUE, UTC_DATE, UTC_TIME, UTC_TIMESTAMP, VARCHARACTER.

MySQL allows some keywords to be used as unquoted identifiers because many people previously used them. Examples are those in the following list: