Literals: How to Write Strings and Numbers
1 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 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 wild-card character. See section 7.4.6 String Comparison Functions.
\_
A `_' character. This is used to search for literal instances of `_' in contexts where `_' would otherwise be interpreted as a wild-card character. See section 7.4.6 String Comparison Functions.
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. See section 23.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. See section 23.5.2 The DBI Interface.
You should use an escape function on any string that might contain any of the special characters listed above!
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.
3 Hexadecimal Values
MySQL supports hexadecimal values. In number 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 0xa+0
-> 10
mysql> select 0x5061756c;
-> Paul
Hexadecimal strings are often used by ODBC to give values for BLOB columns.
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. See section 20.17 Problems with NULL Values.
NULL may be represented by \N when using the text file import or export formats (LOAD DATA INFILE, SELECT ... INTO OUTFILE). See section 7.23 LOAD DATA INFILE Syntax.
5 Database, Table, Index, Column, and Alias Names
Database, table, index, column, and alias names all follow the same rules in MySQL.
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 5.2 Running MySQL in ANSI Mode.
| Identifier | Max length | Allowed characters |
| Database | 64 | Any character that is allowed in a directory name except `/'. |
| Table | 64 | Any character that is allowed in a file name, except `/' or `.'. |
| Column | 64 | All characters. |
| Alias | 255 | All characters. |
Note that in addition to the above, you can't have ASCII(0) or ASCII(255) in an identifier.
Note that if the identifer is a restricted word or contains special characters you must always quote it with ` when you use it:
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 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 10.1.1 The Character Set Used for Data and Sorting.
- 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.
- 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).
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 Version 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.
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 Windows. See section 5.1 MySQL Extensions to ANSI SQL92.
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 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.
If you have a problem remembering the used cases for a 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.
In this case MySQL will convert all table names to lower case on storage and lookup. Note that you need to first convert your old table names to lower case before starting mysqld with this option.
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 10.1.1 The Character Set Used for Data and Sorting.
Variables don't have to be initialized. 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 set a variable in an expression with the @variable:=expr syntax:
select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1 | @t2 | @t3 |
+----------------------+------+------+------+
| 5 | 5 | 1 | 4 |
+----------------------+------+------+------+
(We had to use the := syntax here, because = was reserved for comparisons.)
User variables may be used where expressions are allowed. Note that this does not currently include use in 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 only evaluated 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:
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 previous accepted row.
Column Types
MySQL supports a number of column types, which may be grouped into three categories: numeric types, date and time types, and string (character) types. This section first gives an overview of the types available and summarizes the storage requirements for each column type, then provides a more detailed description of the properties of the types in each category. The overview is intentionally brief. The more detailed descriptions should be consulted for additional information about particular column types, such as the allowable formats in which you can specify values.
The column types supported by MySQL are listed below. The following code letters are used in the descriptions:
M
Indicates the maximum display size. The maximum legal display size is 255.
D
Applies to floating-point types and indicates the number of digits following the decimal point. The maximum possible value is 30, but should be no greater than M-2.
Square brackets (`[' and `]') indicate parts of type specifiers that are optional.
Note that if you specify ZEROFILL for a column, MySQL will automatically add the UNSIGNED attribute to the column.
TINYINT[(M)] [UNSIGNED] [ZEROFILL]
A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
A medium-size integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.
INT[(M)] [UNSIGNED] [ZEROFILL]
A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.
INTEGER[(M)] [UNSIGNED] [ZEROFILL]
This is a synonym for INT.
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615. Note that all arithmetic is done using signed BIGINT or DOUBLE values, so you shouldn't use unsigned big integers larger than 9223372036854775807 (63 bits) except with bit functions! Note that `-', `+', and `*' will use BIGINT arithmetic when both arguments are INTEGER values! This means that if you multiply two big integers (or results from functions that return integers) you may get unexpected results if the result is larger than 9223372036854775807.
FLOAT(precision) [ZEROFILL]
A floating-point number. Cannot be unsigned. precision can be <=24 for a single-precision floating-point number and between 25 and 53 for a double-precision floating-point number. These types are like the FLOAT and DOUBLE types described immediately below. FLOAT(X) has the same range as the corresponding FLOAT and DOUBLE types, but the display size and number of decimals is undefined. In MySQL Version 3.23, this is a true floating-point value. In earlier MySQL versions, FLOAT(precision) always has 2 decimals. Note that using FLOAT may give you some unexpected problems as all calculation in MySQL is done with double precision. See section 20.20 Solving Problems with No Matching Rows. This syntax is provided for ODBC compatibility.
FLOAT[(M,D)] [ZEROFILL]
A small (single-precision) floating-point number. Cannot be unsigned. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. The M is the display width and D is the number of decimals. FLOAT without an argument or with an argument of <= 24 stands for a single-precision floating-point number.
DOUBLE[(M,D)] [ZEROFILL]
A normal-size (double-precision) floating-point number. Cannot be unsigned. Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. The M is the display width and D is the number of decimals. DOUBLE without an argument or FLOAT(X) where 25 <= X <= 53 stands for a double-precision floating-point number.
DOUBLE PRECISION[(M,D)] [ZEROFILL]
REAL[(M,D)] [ZEROFILL]
These are synonyms for DOUBLE.
DECIMAL[(M[,D])] [ZEROFILL]
An unpacked floating-point number. Cannot be unsigned. Behaves like a CHAR column: ``unpacked'' means the number is stored as a string, using one character for each digit of the value. The decimal point and, for negative numbers, the `-' sign, are not counted in M (but space for these are reserved). If D is 0, values will have no decimal point or fractional part. The maximum range of DECIMAL values is the same as for DOUBLE, but the actual range for a given DECIMAL column may be constrained by the choice of M and D. If D is left out it's set to 0. If M is left out it's set to 10. Note that in MySQL Version 3.22 the M argument had to includes the space needed for the sign and the decimal point.
NUMERIC(M,D) [ZEROFILL]
This is a synonym for DECIMAL.
DATE
A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but allows you to assign values to DATE columns using either strings or numbers. See section 7.3.3.2 The DATETIME, DATE, and TIMESTAMP Types.
DATETIME
A date and time combination. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format, but allows you to assign values to DATETIME columns using either strings or numbers. See section 7.3.3.2 The DATETIME, DATE, and TIMESTAMP Types.
TIMESTAMP[(M)]
A timestamp. The range is '1970-01-01 00:00:00' to sometime in the year 2037. MySQL displays TIMESTAMP values in YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD format, depending on whether M is 14 (or missing), 12, 8, or 6, but allows you to assign values to TIMESTAMP columns using either strings or numbers. A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation because it is automatically set to the date and time of the most recent operation if you don't give it a value yourself. You can also set it to the current date and time by assigning it a NULL value. See section 7.3.3 Date and Time Types. A TIMESTAMP is always stored in 4 bytes. The M argument only affects how the TIMESTAMP column is displayed. Note that TIMESTAMP(X) columns where X is 8 or 14 are reported to be numbers while other TIMESTAMP(X) columns are reported to be strings. This is just to ensure that one can reliably dump and restore the table with these types! See section 7.3.3.2 The DATETIME, DATE, and TIMESTAMP Types.
TIME
A time. The range is '-838:59:59' to '838:59:59'. MySQL displays TIME values in 'HH:MM:SS' format, but allows you to assign values to TIME columns using either strings or numbers. See section 7.3.3.3 The TIME Type.
YEAR[(2|4)]
A year in 2- or 4-digit format (default is 4-digit). The allowable values are 1901 to 2155, 0000 in the 4-digit year format, and 1970-2069 if you use the 2-digit format (70-69). MySQL displays YEAR values in YYYY format, but allows you to assign values to YEAR columns using either strings or numbers. (The YEAR type is new in MySQL Version 3.22.). See section 7.3.3.4 The YEAR Type.
[NATIONAL] CHAR(M) [BINARY]
A fixed-length string that is always right-padded with spaces to the specified length when stored. The range of M is 1 to 255 characters. Trailing spaces are removed when the value is retrieved. CHAR values are sorted and compared in case-insensitive fashion according to the default character set unless the BINARY keyword is given. NATIONAL CHAR (short form NCHAR) is the ANSI SQL way to define that a CHAR column should use the default CHARACTER set. This is the default in MySQL. CHAR is a shorthand for CHARACTER. MySQL allows you to create a column of type CHAR(0). This is mainly useful when you have to be compliant with some old applications that depend on the existence of a column but that do not actually use the value. This is also quite nice when you need a column that only can take 2 values: A CHAR(0), that is not defined as NOT NULL, will only occupy one bit and can only take 2 values: NULL or "". See section 7.3.4.1 The CHAR and VARCHAR Types.
[NATIONAL] VARCHAR(M) [BINARY]
A variable-length string. NOTE: Trailing spaces are removed when the value is stored (this differs from the ANSI SQL specification). The range of M is 1 to 255 characters. VARCHAR values are sorted and compared in case-insensitive fashion unless the BINARY keyword is given. See section 7.7.1 Silent Column Specification Changes. VARCHAR is a shorthand for CHARACTER VARYING. See section 7.3.4.1 The CHAR and VARCHAR Types.
TINYBLOB
TINYTEXT
A BLOB or TEXT column with a maximum length of 255 (2^8 - 1) characters. See section 7.7.1 Silent Column Specification Changes. See section 7.3.4.2 The BLOB and TEXT Types.
BLOB
TEXT
A BLOB or TEXT column with a maximum length of 65535 (2^16 - 1) characters. See section 7.7.1 Silent Column Specification Changes. See section 7.3.4.2 The BLOB and TEXT Types.
MEDIUMBLOB
MEDIUMTEXT
A BLOB or TEXT column with a maximum length of 16777215 (2^24 - 1) characters. See section 7.7.1 Silent Column Specification Changes. See section 7.3.4.2 The BLOB and TEXT Types.
LONGBLOB
LONGTEXT
A BLOB or TEXT column with a maximum length of 4294967295 (2^32 - 1) characters. See section 7.7.1 Silent Column Specification Changes. Note that because the server/client protocol and MyISAM tables has currently a limit of 16M per communication packet / table row, you can't yet use this the whole range of this type. See section 7.3.4.2 The BLOB and TEXT Types.
ENUM('value1','value2',...)
An enumeration. A string object that can have only one value, chosen from the list of values 'value1', 'value2', ..., NULL or the special "" error value. An ENUM can have a maximum of 65535 distinct values. See section 7.3.4.3 The ENUM Type.
SET('value1','value2',...)
A set. A string object that can have zero or more values, each of which must be chosen from the list of values 'value1', 'value2', ... A SET can have a maximum of 64 members. See section 7.3.4.4 The SET Type.
