MySQL Reference Manual for version 3.23.5-alpha. - 7.1 Literals: how to write strings and numbers

7.1 Literals: how to write strings and numbers

7.1.1 Strings

A string is a sequence of characters, surrounded by either single quote (`'') or double quote (`"') characters. 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 recognizes the following escape sequences:

\0: An ASCII 0 (NUL) character.
\n: A newline character.
\t: A tab character.
\r: A carriage return character.
\b: A backspace character.
\': A single quote (`'') character.
\": A double quote (`"') character.
\\: 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.
\_: A `_' character. This is used to search for literal instances of `_' in contexts where `_' would otherwise be interpreted as a wildcard character.

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 below 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 BLOB column, 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_escape_string() to escape characters for the INSERT statement. 20.3 C API function overview. In Perl, you can use the quote method of the DBI package to convert special characters to the proper escape sequences. Perl DBI Class.

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

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

7.1.3 Hexadecimal values

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

mysql> SELECT 0xa+0
       -> 10
mysql> select 0x5061756c;
       -> Paul

Hexadecimal strings is often used by ODBC to give values for BLOB columns.

7.1.4 `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. Problems with NULL.

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

7.1.5 Database, table, index, column and alias names

Database, table, index, column and alias names all follow the same rules in MySQL:

A 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 by recompiling MySQL. 9.1.1 The character set used for data and sorting.
A database, table, index or column name can be up to 64 characters long. An alias name can be up to 256 characters long.
A name may start with any character that is legal in a name. In particular, a name may start with a number (this differs from many other database systems!). However, a name cannot consist only of numbers.
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.
You cannot use the `.' character in names because it is used to extend the format by which you can refer to columns (see immediately below).

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 3.22 or later.

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.

7.1.5.1 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 sensitive in Unix and case insensitive in Win32.

Note: Although database and table names are case insensitive for Win32, 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 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;

Aliases on columns are case insensitive.