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

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

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.

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. MySQL 5.0 allows the use of identifier quote characters in identifiers, although it is best to avoid doing so if possible.

In MySQL 5.0, identifiers are stored using Unicode (UTF8). 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 equate to numbers of characters; this means that (unlike the case for 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. For a list of reserved words, see Section 9.6, “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 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 5.3.2, “The Server SQL Mode”.

In MySQL 5.0, 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 pattern XeX, such as 1e or 2e2, because an expression like 1e+1 is ambiguous. It might be interpreted as the expression 1e + 1 or as the number 1e+1, depending on context.

Be careful when using MD5 to produce table names, as it can produce illegal table names such as the ones listed above.

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 5.0 supports user variables in which you can store a value and then refer to it later; this 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 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 cp1252 (Latin1). 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”. User variable names are not case sensitive in 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.

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

If you refer to a variable that has not been initialized, its value is NULL.

In MySQL 5.0, if a user variable is assigned a string value, it also 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 does 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 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.

An unassigned variable has a value of NULL with a type of string.

MySQL provides access to many system and connection variables. Many variables can be changed dynamically while the server is running. This often allows you to modify server operation without having to stop and restart it.

The mysqld server maintains two kinds of variables. 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, 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 is visible to any client that accesses that global variable. However, it affects the corresponding session variable that is initialized from the global variable only for clients that connect after the change. It does not affect the session variable for any client that is currently connected (not even that 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 13.5.3, “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 @@sort_buffer_size;
mysql> SELECT @@session.sort_buffer_size;
mysql> SHOW SESSION VARIABLES like 'sort_buffer_size';

Here, too, LOCAL is a synonym for SESSION.

When you retrieve a variable with SELECT @@var_name (that is, you do not specify global., session., or local., MySQL returns the SESSION value if it exists and the GLOBAL value otherwise.

For SHOW VARIABLES, if you do not specify GLOBAL, SESSION, or LOCAL, MySQL returns the SESSION values.

The reason for requiring the GLOBAL keyword when setting GLOBAL-only variables but not when retrieving them is to prevent problems in the future. If we remove a SESSION variable with the same name as a GLOBAL variable, a client with the SUPER privilege might accidentally change the GLOBAL variable rather than just the SESSION variable for its own connection. If we add a SESSION variable with the same name as a GLOBAL variable, a client that intends to change the GLOBAL variable might find only its own SESSION variable changed.

Further information about system startup options and system variables can be found in Section 5.3.1, “mysqld Command-Line Options” and Section 5.3.3, “Server System Variables”. A list of the variables that can be set at runtime is given in Section 5.3.3.1, “Dynamic System Variables”.

hot_cache.key_buffer_size
hot_cache.key_cache_block_size
cold_cache.key_cache_block_size

shell> mysqld --hot_cache.key_buffer_size=64K

[mysqld]
hot_cache.key_buffer_size=64K

shell> mysqld --key_buffer_size=256K \
         --extra_cache.key_buffer_size=128K \
         --extra_cache.key_cache_block_size=2048

shell> mysqld --key_buffer_size=6M \
         --hot_cache.key_buffer_size=2M \
         --cold_cache.key_buffer_size=2M

mysql> SET GLOBAL hot_cache.key_buffer_size = 10*1024*1024;
mysql> SET @@global.hot_cache.key_buffer_size = 10*1024*1024;

mysql> SELECT @@global.hot_cache.key_buffer_size;

mysql> SHOW GLOBAL VARIABLES LIKE 'hot_cache.key_buffer_size';

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

In MySQL 5.0, the only limitation on the way that mysql parses /* ... */ comments is that an exclamation point used with this style of comment delimiter marks portions of SQL statements for conditional execution. This applies 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. For more information and examples, see Section 1.8.4, “MySQL Extensions to Standard SQL”.

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 data type or function, such as TIMESTAMP or GROUP. You are permitted to do this (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);

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 9.2, “Database, Table, Index, Column, and Alias Names”. The server SQL mode is controlled as described in Section 5.3.2, “The Server SQL Mode”.

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 words in the following table are explicitly reserved in MySQL. Most of them are forbidden by standard SQL 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 if you quote it.

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