Go to the first, previous, next, last section, table of contents.

7 MySQL Language Reference

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 (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!

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

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

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

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

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

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

7.3.1 Column Type Storage Requirements

The storage requirements for each of the column types supported by MySQL are listed below by category.

7.3.1.1 Storage requirements for numeric types

Column type	Storage required
`TINYINT`	1 byte
`SMALLINT`	2 bytes
`MEDIUMINT`	3 bytes
`INT`	4 bytes
`INTEGER`	4 bytes
`BIGINT`	8 bytes
`FLOAT(X)`	4 if X <= 24 or 8 if 25 <= X <= 53
`FLOAT`	4 bytes
`DOUBLE`	8 bytes
`DOUBLE PRECISION`	8 bytes
`REAL`	8 bytes
`DECIMAL(M,D)`	`M+2` bytes if D > 0, `M+1` bytes if D = 0 (`D`+2, if `M < D`)
`NUMERIC(M,D)`	`M+2` bytes if D > 0, `M+1` bytes if D = 0 (`D`+2, if `M < D`)

7.3.1.2 Storage requirements for date and time types

Column type	Storage required
`DATE`	3 bytes
`DATETIME`	8 bytes
`TIMESTAMP`	4 bytes
`TIME`	3 bytes
`YEAR`	1 byte

7.3.1.3 Storage requirements for string types

Column type	Storage required
`CHAR(M)`	`M` bytes, `1 <= M <= 255`
`VARCHAR(M)`	`L`+1 bytes, where `L <= M` and `1 <= M <= 255`
`TINYBLOB`, `TINYTEXT`	`L`+1 bytes, where `L` < 2^8
`BLOB`, `TEXT`	`L`+2 bytes, where `L` < 2^16
`MEDIUMBLOB`, `MEDIUMTEXT`	`L`+3 bytes, where `L` < 2^24
`LONGBLOB`, `LONGTEXT`	`L`+4 bytes, where `L` < 2^32
`ENUM('value1','value2',...)`	1 or 2 bytes, depending on the number of enumeration values (65535 values maximum)
`SET('value1','value2',...)`	1, 2, 3, 4 or 8 bytes, depending on the number of set members (64 members maximum)

VARCHAR and the BLOB and TEXT types are variable-length types, for which the storage requirements depend on the actual length of column values (represented by L in the preceding table), rather than on the type's maximum possible size. For example, a VARCHAR(10) column can hold a string with a maximum length of 10 characters. The actual storage required is the length of the string (L), plus 1 byte to record the length of the string. For the string 'abcd', L is 4 and the storage requirement is 5 bytes.

The BLOB and TEXT types require 1, 2, 3, or 4 bytes to record the length of the column value, depending on the maximum possible length of the type. See section 7.3.4.2 The BLOB and TEXT Types.

If a table includes any variable-length column types, the record format will also be variable-length. Note that when a table is created, MySQL may, under certain conditions, change a column from a variable-length type to a fixed-length type, or vice-versa. See section 7.7.1 Silent Column Specification Changes.

The size of an ENUM object is determined by the number of different enumeration values. One byte is used for enumerations with up to 255 possible values. Two bytes are used for enumerations with up to 65535 values. See section 7.3.4.3 The ENUM Type.

The size of a SET object is determined by the number of different set members. If the set size is N, the object occupies (N+7)/8 bytes, rounded up to 1, 2, 3, 4, or 8 bytes. A SET can have a maximum of 64 members. See section 7.3.4.4 The SET Type.

7.3.2 Numeric Types

MySQL supports all of the ANSI/ISO SQL92 numeric types. These types include the exact numeric data types (NUMERIC, DECIMAL, INTEGER, and SMALLINT), as well as the approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION). The keyword INT is a synonym for INTEGER, and the keyword DEC is a synonym for DECIMAL.

The NUMERIC and DECIMAL types are implemented as the same type by MySQL, as permitted by the SQL92 standard. They are used for values for which it is important to preserve exact precision, for example with monetary data. When declaring a column of one of these types the precision and scale can be (and usually is) specified; for example:

    salary DECIMAL(9,2)

In this example, 9 (precision) represents the number of significant decimal digits that will be stored for values, and 2 (scale) represents the number of digits that will be stored following the decimal point. In this case, therefore, the range of values that can be stored in the salary column is from -9999999.99 to 9999999.99. In ANSI/ISO SQL92, the syntax DECIMAL(p) is equivalent to DECIMAL(p,0). Similarly, the syntax DECIMAL is equivalent to DECIMAL(p,0), where the implementation is allowed to decide the value of p. MySQL does not currently support either of these variant forms of the DECIMAL/NUMERIC data types. This is not generally a serious problem, as the principal benefits of these types derive from the ability to control both precision and scale explicitly.

DECIMAL and NUMERIC values are stored as strings, rather than as binary floating-point numbers, in order to preserve the decimal precision of those values. One character is used for each digit of the value, the decimal point (if scale > 0), and the `-' sign (for negative numbers). If scale is 0, DECIMAL and NUMERIC values contain no decimal point or fractional part.

The maximum range of DECIMAL and NUMERIC values is the same as for DOUBLE, but the actual range for a given DECIMAL or NUMERIC column can be constrained by the precision or scale for a given column. When such a column is assigned a value with more digits following the decimal point than are allowed by the specified scale, the value is rounded to that scale. When a DECIMAL or NUMERIC column is assigned a value whose magnitude exceeds the range implied by the specified (or defaulted) precision and scale, MySQL stores the value representing the corresponding end point of that range.

As an extension to the ANSI/ISO SQL92 standard, MySQL also supports the integral types TINYINT, MEDIUMINT, and BIGINT as listed in the tables above. Another extension is supported by MySQL for optionally specifying the display width of an integral value in parentheses following the base keyword for the type (for example, INT(4)). This optional width specification is used to left-pad the display of values whose width is less than the width specified for the column, but does not constrain the range of values that can be stored in the column, nor the number of digits that will be displayed for values whose width exceeds that specified for the column. When used in conjunction with the optional extension attribute ZEROFILL, the default padding of spaces is replaced with zeroes. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004. Note that if you store larger values than the display width in an integer column, you may experience problems when MySQL generates temporary tables for some complicated joins, as in these cases MySQL trusts that the data did fit into the original column width.

All integral types can have an optional (non-standard) attribute UNSIGNED. Unsigned values can be used when you want to allow only positive numbers in a column and you need a little bigger numeric range for the column.

The FLOAT type is used to represent approximate numeric data types. The ANSI/ISO SQL92 standard allows an optional specification of the precision (but not the range of the exponent) in bits following the keyword FLOAT in parentheses. The MySQL implementation also supports this optional precision specification. When the keyword FLOAT is used for a column type without a precision specification, MySQL uses four bytes to store the values. A variant syntax is also supported, with two numbers given in parentheses following the FLOAT keyword. With this option, the first number continues to represent the storage requirements for the value in bytes, and the second number specifies the number of digits to be stored and displayed following the decimal point (as with DECIMAL and NUMERIC). When MySQL is asked to store a number for such a column with more decimal digits following the decimal point than specified for the column, the value is rounded to eliminate the extra digits when the value is stored.

The REAL and DOUBLE PRECISION types do not accept precision specifications. As an extension to the ANSI/ISO SQL92 standard, MySQL recognizes DOUBLE as a synonym for the DOUBLE PRECISION type. In contrast with the standard's requirement that the precision for REAL be smaller than that used for DOUBLE PRECISION, MySQL implements both as 8-byte double-precision floating-point values (when not running in ``ANSI mode''). For maximum portability, code requiring storage of approximate numeric data values should use FLOAT or DOUBLE PRECISION with no specification of precision or number of decimal points.

When asked to store a value in a numeric column that is outside the column type's allowable range, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead.

For example, the range of an INT column is -2147483648 to 2147483647. If you try to insert -9999999999 into an INT column, the value is clipped to the lower endpoint of the range, and -2147483648 is stored instead. Similarly, if you try to insert 9999999999, 2147483647 is stored instead.

If the INT column is UNSIGNED, the size of the column's range is the same but its endpoints shift up to 0 and 4294967295. If you try to store -9999999999 and 9999999999, the values stored in the column become 0 and 4294967296.

Conversions that occur due to clipping are reported as ``warnings'' for ALTER TABLE, LOAD DATA INFILE, UPDATE, and multi-row INSERT statements.

7.3.3 Date and Time Types

The date and time types are DATETIME, DATE, TIMESTAMP, TIME, and YEAR. Each of these has a range of legal values, as well as a ``zero'' value that is used when you specify a really illegal value. Note that MySQL allows you to store certain 'not strictly' legal date values, for example 1999-11-31. The reason for this is that we think it's the responsibility of the application to handle date checking, not the SQL servers. To make the date checking 'fast', MySQL only checks that the month is in the range of 0-12 and the day is in the range of 0-31. The above ranges are defined this way because MySQL allows you to store, in a DATE or DATETIME column, dates where the day or month-day is zero. This is extremely useful for applications that need to store a birth-date for which you don't know the exact date. In this case you simply store the date like 1999-00-00 or 1999-01-00. (You cannot expect to get a correct value from functions like DATE_SUB() or DATE_ADD for dates like these.)

Here are some general considerations to keep in mind when working with date and time types:

MySQL retrieves values for a given date or time type in a standard format, but it attempts to interpret a variety of formats for values that you supply (for example, when you specify a value to be assigned to or compared to a date or time type). Nevertheless, only the formats described in the following sections are supported. It is expected that you will supply legal values, and unpredictable results may occur if you use values in other formats.
Although MySQL tries to interpret values in several formats, it always expects the year part of date values to be leftmost. Dates must be given in year-month-day order (for example, '98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example, '09-04-98', '04-09-98').
MySQL automatically converts a date or time type value to a number if the value is used in a numeric context, and vice versa.
When MySQL encounters a value for a date or time type that is out of range or otherwise illegal for the type (see the start of this section), it converts the value to the ``zero'' value for that type. (The exception is that out-of-range TIME values are clipped to the appropriate endpoint of the TIME range.) The table below shows the format of the ``zero'' value for each type:

Column type ``Zero'' value

DATETIME '0000-00-00 00:00:00'

DATE '0000-00-00'

TIMESTAMP 00000000000000 (length depends on display size)

TIME '00:00:00'

YEAR 0000
The ``zero'' values are special, but you can store or refer to them explicitly using the values shown in the table. You can also do this using the values '0' or 0, which are easier to write.
``Zero'' date or time values used through MyODBC are converted automatically to NULL in MyODBC Version 2.50.12 and above, because ODBC can't handle such values.

7.3.3.1 Y2K Issues and Date Types

MySQL itself is Y2K-safe (see section 1.8 Year 2000 Compliance), but input values presented to MySQL may not be. Any input containing 2-digit year values is ambiguous, because the century is unknown. Such values must be interpreted into 4-digit form because MySQL stores years internally using four digits.

For DATETIME, DATE, TIMESTAMP, and YEAR types, MySQL interprets dates with ambiguous year values using the following rules:

Year values in the range 00-69 are converted to 2000-2069.
Year values in the range 70-99 are converted to 1970-1999.

Remember that these rules provide only reasonable guesses as to what your data mean. If the heuristics used by MySQL don't produce the correct values, you should provide unambiguous input containing 4-digit year values.

ORDER BY will sort 2-digit YEAR/DATE/DATETIME types properly.

Note also that some functions like MIN() and MAX() will convert a TIMESTAMP/DATE to a number. This means that a timestamp with a 2-digit year will not work properly with these functions. The fix in this case is to convert the TIMESTAMP/DATE to 4-digit year format or use something like MIN(DATE_ADD(timestamp,INTERVAL 0 DAYS)).

7.3.3.2 The `DATETIME`, `DATE`, and `TIMESTAMP` Types

The DATETIME, DATE, and TIMESTAMP types are related. This section describes their characteristics, how they are similar, and how they differ.

The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. (``Supported'' means that although earlier values might work, there is no guarantee that they will.)

The DATE type is used when you need only a date value, without a time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically.

Automatic updating of the first TIMESTAMP column occurs under any of the following conditions:

The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement.
The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.)
You explicitly set the TIMESTAMP column to NULL.

TIMESTAMP columns other than the first may also be set to the current date and time. Just set the column to NULL or to NOW().

You can set any TIMESTAMP column to a value different than the current date and time by setting it explicitly to the desired value. This is true even for the first TIMESTAMP column. You can use this property if, for example, you want a TIMESTAMP to be set to the current date and time when you create a row, but not to be changed whenever the row is updated later:

Let MySQL set the column when the row is created. This will initialize it to the current date and time.
When you perform subsequent updates to other columns in the row, set the TIMESTAMP column explicitly to its current value.

On the other hand, you may find it just as easy to use a DATETIME column that you initialize to NOW() when the row is created and leave alone for subsequent updates.

TIMESTAMP values may range from the beginning of 1970 to sometime in the year 2037, with a resolution of one second. Values are displayed as numbers.

The format in which MySQL retrieves and displays TIMESTAMP values depends on the display size, as illustrated by the table below. The `full' TIMESTAMP format is 14 digits, but TIMESTAMP columns may be created with shorter display sizes:

Column type	Display format
`TIMESTAMP(14)`	`YYYYMMDDHHMMSS`
`TIMESTAMP(12)`	`YYMMDDHHMMSS`
`TIMESTAMP(10)`	`YYMMDDHHMM`
`TIMESTAMP(8)`	`YYYYMMDD`
`TIMESTAMP(6)`	`YYMMDD`
`TIMESTAMP(4)`	`YYMM`
`TIMESTAMP(2)`	`YY`

All TIMESTAMP columns have the same storage size, regardless of display size. The most common display sizes are 6, 8, 12, and 14. You can specify an arbitrary display size at table creation time, but values of 0 or greater than 14 are coerced to 14. Odd-valued sizes in the range from 1 to 13 are coerced to the next higher even number.

You can specify DATETIME, DATE, and TIMESTAMP values using any of a common set of formats:

As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A ``relaxed'' syntax is allowed--any punctuation character may be used as the delimiter between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45', and '98@12@31 11^30^45' are equivalent.
As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A ``relaxed'' syntax is allowed here, too. For example, '98-12-31', '98.12.31', '98/12/31', and '98@12@31' are equivalent.
As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '19970523091528' and '970523091528' are interpreted as '1997-05-23 09:15:28', but '971122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.
As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '19970523' and '970523' are interpreted as '1997-05-23', but '971332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.
As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.
As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.
As the result of a function that returns a value that is acceptable in a DATETIME, DATE, or TIMESTAMP context, such as NOW() or CURRENT_DATE.

Illegal DATETIME, DATE, or TIMESTAMP values are converted to the ``zero'' value of the appropriate type ('0000-00-00 00:00:00', '0000-00-00', or 00000000000000).

For values specified as strings that include date part delimiters, it is not necessary to specify two digits for month or day values that are less than 10. '1979-6-9' is the same as '1979-06-09'. Similarly, for values specified as strings that include time part delimiters, it is not necessary to specify two digits for hour, month, or second values that are less than 10. '1979-10-30 1:2:3' is the same as '1979-10-30 01:02:03'.

Values specified as numbers should be 6, 8, 12, or 14 digits long. If the number is 8 or 14 digits long, it is assumed to be in YYYYMMDD or YYYYMMDDHHMMSS format and that the year is given by the first 4 digits. If the number is 6 or 12 digits long, it is assumed to be in YYMMDD or YYMMDDHHMMSS format and that the year is given by the first 2 digits. Numbers that are not one of these lengths are interpreted as though padded with leading zeros to the closest length.

Values specified as non-delimited strings are interpreted using their length as given. If the string is 8 or 14 characters long, the year is assumed to be given by the first 4 characters. Otherwise the year is assumed to be given by the first 2 characters. The string is interpreted from left to right to find year, month, day, hour, minute, and second values, for as many parts as are present in the string. This means you should not use strings that have fewer than 6 characters. For example, if you specify '9903', thinking that will represent March, 1999, you will find that MySQL inserts a ``zero'' date into your table. This is because the year and month values are 99 and 03, but the day part is missing (zero), so the value is not a legal date.

TIMESTAMP columns store legal values using the full precision with which the value was specified, regardless of the display size. This has several implications:

Always specify year, month, and day, even if your column types are TIMESTAMP(4) or TIMESTAMP(2). Otherwise, the value will not be a legal date and 0 will be stored.
If you use ALTER TABLE to widen a narrow TIMESTAMP column, information will be displayed that previously was ``hidden''.
Similarly, narrowing a TIMESTAMP column does not cause information to be lost, except in the sense that less information is shown when the values are displayed.
Although TIMESTAMP values are stored to full precision, the only function that operates directly on the underlying stored value is UNIX_TIMESTAMP(). Other functions operate on the formatted retrieved value. This means you cannot use functions such as HOUR() or SECOND() unless the relevant part of the TIMESTAMP value is included in the formatted value. For example, the HH part of a TIMESTAMP column is not displayed unless the display size is at least 10, so trying to use HOUR() on shorter TIMESTAMP values produces a meaningless result.

You can to some extent assign values of one date type to an object of a different date type. However, there may be some alteration of the value or loss of information:

If you assign a DATE value to a DATETIME or TIMESTAMP object, the time part of the resulting value is set to '00:00:00', because the DATE value contains no time information.
If you assign a DATETIME or TIMESTAMP value to a DATE object, the time part of the resulting value is deleted, because the DATE type stores no time information.
Remember that although DATETIME, DATE, and TIMESTAMP values all can be specified using the same set of formats, the types do not all have the same range of values. For example, TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not a valid TIMESTAMP value and will be converted to 0 if assigned to such an object.

Be aware of certain pitfalls when specifying date values:

The relaxed format allowed for values specified as strings can be deceiving. For example, a value such as '10:11:12' might look like a time value because of the `:' delimiter, but if used in a date context will be interpreted as the year '2010-11-12'. The value '10:45:15' will be converted to '0000-00-00' because '45' is not a legal month.
Year values specified as two digits are ambiguous, because the century is unknown. MySQL interprets 2-digit year values using the following rules:
- Year values in the range 00-69 are converted to 2000-2069.
- Year year values in the range 70-99 are converted to 1970-1999.

7.3.3.3 The `TIME` Type

MySQL retrieves and displays TIME values in 'HH:MM:SS' format (or 'HHH:MM:SS' format for large hours values). TIME values may range from '-838:59:59' to '838:59:59'. The reason the hours part may be so large is that the TIME type may be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).

You can specify TIME values in a variety of formats:

As a string in 'D HH:MM:SS.fraction' format. (Note that MySQL doesn't yet store the fraction for the time column). One can also use one of the following ``relaxed'' syntax: HH:MM:SS.fraction, HH:MM:SS, HH:MM, D HH:MM:SS, D HH:MM, D HH or SS. Here D is days between 0-33.
As a string with no delimiters in 'HHMMSS' format, provided that it makes sense as a time. For example, '101112' is understood as '10:11:12', but '109712' is illegal (it has a nonsensical minute part) and becomes '00:00:00'.
As a number in HHMMSS format, provided that it makes sense as a time. For example, 101112 is understood as '10:11:12'. The following alternative formats are also understood: SS, MMSS,HHMMSS, HHMMSS.fraction. Note that MySQL doesn't yet store the fraction part.
As the result of a function that returns a value that is acceptable in a TIME context, such as CURRENT_TIME.

For TIME values specified as strings that include a time part delimiter, it is not necessary to specify two digits for hours, minutes, or seconds values that are less than 10. '8:3:2' is the same as '08:03:02'.

Be careful about assigning ``short'' TIME values to a TIME column. MySQL interprets values using the assumption that the rightmost digits represent seconds. (MySQL interprets TIME values as elapsed time rather than as time of day.) For example, you might think of '11:12', '1112', and 1112 as meaning '11:12:00' (12 minutes after 11 o'clock), but MySQL interprets them as '00:11:12' (11 minutes, 12 seconds). Similarly, '12' and 12 are interpreted as '00:00:12'.

Values that lie outside the TIME range but are otherwise legal are clipped to the appropriate endpoint of the range. For example, '-850:00:00' and '850:00:00' are converted to '-838:59:59' and '838:59:59'.

Illegal TIME values are converted to '00:00:00'. Note that because '00:00:00' is itself a legal TIME value, there is no way to tell, from a value of '00:00:00' stored in a table, whether the original value was specified as '00:00:00' or whether it was illegal.

7.3.3.4 The `YEAR` Type

The YEAR type is a 1-byte type used for representing years.

MySQL retrieves and displays YEAR values in YYYY format. The range is 1901 to 2155.

You can specify YEAR values in a variety of formats:

As a four-digit string in the range '1901' to '2155'.
As a four-digit number in the range 1901 to 2155.
As a two-digit string in the range '00' to '99'. Values in the ranges '00' to '69' and '70' to '99' are converted to YEAR values in the ranges 2000 to 2069 and 1970 to 1999.
As a two-digit number in the range 1 to 99. Values in the ranges 1 to 69 and 70 to 99 are converted to YEAR values in the ranges 2001 to 2069 and 1970 to 1999. Note that the range for two-digit numbers is slightly different than the range for two-digit strings, because you cannot specify zero directly as a number and have it be interpreted as 2000. You must specify it as a string '0' or '00' or it will be interpreted as 0000.
As the result of a function that returns a value that is acceptable in a YEAR context, such as NOW().

Illegal YEAR values are converted to 0000.

7.3.4 String Types

The string types are CHAR, VARCHAR, BLOB, TEXT, ENUM, and SET.

7.3.4.1 The `CHAR` and `VARCHAR` Types

The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved.

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value between 1 and 255. (As of MySQL Version 3.23, the length of CHAR may be 0 to 255.) When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.

Values in VARCHAR columns are variable-length strings. You can declare a VARCHAR column to be any length between 1 and 255, just as for CHAR columns. However, in contrast to CHAR, VARCHAR values are stored using only as many characters as are needed, plus one byte to record the length. Values are not padded; instead, trailing spaces are removed when values are stored. (This space removal differs from the ANSI SQL specification.)

If you assign a value to a CHAR or VARCHAR column that exceeds the column's maximum length, the value is truncated to fit.

The table below illustrates the differences between the two types of columns by showing the result of storing various string values into CHAR(4) and VARCHAR(4) columns:

Value	`CHAR(4)`	Storage required	`VARCHAR(4)`	Storage required
`''`	`' '`	4 bytes	`''`	1 byte
`'ab'`	`'ab '`	4 bytes	`'ab'`	3 bytes
`'abcd'`	`'abcd'`	4 bytes	`'abcd'`	5 bytes
`'abcdefgh'`	`'abcd'`	4 bytes	`'abcd'`	5 bytes

The values retrieved from the CHAR(4) and VARCHAR(4) columns will be the same in each case, because trailing spaces are removed from CHAR columns upon retrieval.

Values in CHAR and VARCHAR columns are sorted and compared in case-insensitive fashion, unless the BINARY attribute was specified when the table was created. The BINARY attribute means that column values are sorted and compared in case-sensitive fashion according to the ASCII order of the machine where the MySQL server is running. BINARY doesn't affect how the column is stored or retrieved.

The BINARY attribute is sticky. This means that if a column marked BINARY is used in an expression, the whole expression is compared as a BINARY value.

MySQL may silently change the type of a CHAR or VARCHAR column at table creation time. See section 7.7.1 Silent Column Specification Changes.

7.3.4.2 The `BLOB` and `TEXT` Types

A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB differ only in the maximum length of the values they can hold. See section 7.3.1 Column Type Storage Requirements.

The four TEXT types TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT correspond to the four BLOB types and have the same maximum lengths and storage requirements. The only difference between BLOB and TEXT types is that sorting and comparison is performed in case-sensitive fashion for BLOB values and case-insensitive fashion for TEXT values. In other words, a TEXT is a case-insensitive BLOB.

If you assign a value to a BLOB or TEXT column that exceeds the column type's maximum length, the value is truncated to fit.

In most respects, you can regard a TEXT column as a VARCHAR column that can be as big as you like. Similarly, you can regard a BLOB column as a VARCHAR BINARY column. The differences are:

You can have indexes on BLOB and TEXT columns with MySQL Version 3.23.2 and newer. Older versions of MySQL did not support this.
There is no trailing-space removal for BLOB and TEXT columns when values are stored, as there is for VARCHAR columns.
BLOB and TEXT columns cannot have DEFAULT values.

MyODBC defines BLOB values as LONGVARBINARY and TEXT values as LONGVARCHAR.

Because BLOB and TEXT values may be extremely long, you may run up against some constraints when using them:

If you want to use GROUP BY or ORDER BY on a BLOB or TEXT column, you must convert the column value into a fixed-length object. The standard way to do this is with the SUBSTRING function. For example:
```
mysql> select comment from tbl_name,substring(comment,20) as substr
       ORDER BY substr;
```
If you don't do this, only the first max_sort_length bytes of the column are used when sorting. The default value of max_sort_length is 1024; this value can be changed using the -O option when starting the mysqld server. You can group on an expression involving BLOB or TEXT values by specifying the column position or by using an alias:
```
mysql> select id,substring(blob_col,1,100) from tbl_name
           GROUP BY 2;
mysql> select id,substring(blob_col,1,100) as b from tbl_name
           GROUP BY b;
```
The maximum size of a BLOB or TEXT object is determined by its type, but the largest value you can actually transmit between the client and server is determined by the amount of available memory and the size of the communications buffers. You can change the message buffer size, but you must do so on both the server and client ends. See section 12.2.3 Tuning Server Parameters.

Note that each BLOB or TEXT value is represented internally by a separately allocated object. This is in contrast to all other column types, for which storage is allocated once per column when the table is opened.

7.3.4.3 The `ENUM` Type

An ENUM is a string object whose value normally is chosen from a list of allowed values that are enumerated explicitly in the column specification at table creation time.

The value may also be the empty string ("") or NULL under certain circumstances:

If you insert an invalid value into an ENUM (that is, a string not present in the list of allowed values), the empty string is inserted instead as a special error value.
If an ENUM is declared NULL, NULL is also a legal value for the column, and the default value is NULL. If an ENUM is declared NOT NULL, the default value is the first element of the list of allowed values.

Each enumeration value has an index:

Values from the list of allowable elements in the column specification are numbered beginning with 1.
The index value of the empty string error value is 0. This means that you can use the following SELECT statement to find rows into which invalid ENUM values were assigned:
```
mysql> SELECT * FROM tbl_name WHERE enum_col=0;
```
The index of the NULL value is NULL.

For example, a column specified as ENUM("one", "two", "three") can have any of the values shown below. The index of each value is also shown:

Value	Index
`NULL`	`NULL`
`""`	0
`"one"`	1
`"two"`	2
`"three"`	3

An enumeration can have a maximum of 65535 elements.

Lettercase is irrelevant when you assign values to an ENUM column. However, values retrieved from the column later have lettercase matching the values that were used to specify the allowable values at table creation time.

If you retrieve an ENUM in a numeric context, the column value's index is returned. For example, you can retrieve numeric values from an ENUM column like this:

mysql> SELECT enum_col+0 FROM tbl_name;

If you store a number into an ENUM, the number is treated as an index, and the value stored is the enumeration member with that index. (However, this will not work with LOAD DATA, which treats all input as strings.)

ENUM values are sorted according to the order in which the enumeration members were listed in the column specification. (In other words, ENUM values are sorted according to their index numbers.) For example, "a" sorts before "b" for ENUM("a", "b"), but "b" sorts before "a" for ENUM("b", "a"). The empty string sorts before non-empty strings, and NULL values sort before all other enumeration values.

If you want to get all possible values for an ENUM column, you should use: SHOW COLUMNS FROM table_name LIKE enum_column_name and parse the ENUM definition in the second column.

7.3.4.4 The `SET` Type

A SET is a string object that can have zero or more values, each of which must be chosen from a list of allowed values specified when the table is created. SET column values that consist of multiple set members are specified with members separated by commas (`,'). A consequence of this is that SET member values cannot themselves contain commas.

For example, a column specified as SET("one", "two") NOT NULL can have any of these values:

""
"one"
"two"
"one,two"

A SET can have a maximum of 64 different members.

MySQL stores SET values numerically, with the low-order bit of the stored value corresponding to the first set member. If you retrieve a SET value in a numeric context, the value retrieved has bits set corresponding to the set members that make up the column value. For example, you can retrieve numeric values from a SET column like this:

mysql> SELECT set_col+0 FROM tbl_name;

If a number is stored into a SET column, the bits that are set in the binary representation of the number determine the set members in the column value. Suppose a column is specified as SET("a","b","c","d"). Then the members have the following bit values:

`SET` member	Decimal value	Binary value
`a`	`1`	`0001`
`b`	`2`	`0010`
`c`	`4`	`0100`
`d`	`8`	`1000`

If you assign a value of 9 to this column, that is 1001 in binary, so the first and fourth SET value members "a" and "d" are selected and the resulting value is "a,d".

For a value containing more than one SET element, it does not matter what order the elements are listed in when you insert the value. It also does not matter how many times a given element is listed in the value. When the value is retrieved later, each element in the value will appear once, with elements listed according to the order in which they were specified at table creation time. For example, if a column is specified as SET("a","b","c","d"), then "a,d", "d,a", and "d,a,a,d,d" will all appear as "a,d" when retrieved.

SET values are sorted numerically. NULL values sort before non-NULL SET values.

Normally, you perform a SELECT on a SET column using the LIKE operator or the FIND_IN_SET() function:

mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;

But the following will also work:

mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
mysql> SELECT * FROM tbl_name WHERE set_col & 1;

The first of these statements looks for an exact match. The second looks for values containing the first set member.

If you want to get all possible values for a SET column, you should use: SHOW COLUMNS FROM table_name LIKE set_column_name and parse the SET definition in the second column.

7.3.5 Choosing the Right Type for a Column

For the most efficient use of storage, try to use the most precise type in all cases. For example, if an integer column will be used for values in the range between 1 and 99999, MEDIUMINT UNSIGNED is the best type.

Accurate representation of monetary values is a common problem. In MySQL, you should use the DECIMAL type. This is stored as a string, so no loss of accuracy should occur. If accuracy is not too important, the DOUBLE type may also be good enough.

For high precision, you can always convert to a fixed-point type stored in a BIGINT. This allows you to do all calculations with integers and convert results back to floating-point values only when necessary.

7.3.6 Column Indexes

All MySQL column types can be indexed. Use of indexes on the relevant columns is the best way to improve the performance of SELECT operations.

A table may have up to 16 indexes. The maximum index length is 256 bytes, although this may be changed when compiling MySQL.

For CHAR and VARCHAR columns, you can index a prefix of a column. This is much faster and requires less disk space than indexing the whole column. The syntax to use in the CREATE TABLE statement to index a column prefix looks like this:

KEY index_name (col_name(length))

The example below creates an index for the first 10 characters of the name column:

mysql> CREATE TABLE test (
           name CHAR(200) NOT NULL,
           KEY index_name (name(10)));

For BLOB and TEXT columns, you must index a prefix of the column. You cannot index the entire column.

In MySQL Version 3.23.23 or later, you can also create special FULLTEXT indexes. They are used for full-text search. Only the MyISAM table type supports FULLTEXT indexes. They can be created only from VARCHAR and TEXT columns. Indexing always happens over the entire column and partial indexing is not supported. See section 25.2 MySQL Full-text Search for details.

7.3.7 Multiple-column Indexes

MySQL can create indexes on multiple columns. An index may consist of up to 15 columns. (On CHAR and VARCHAR columns you can also use a prefix of the column as a part of an index).

A multiple-column index can be considered a sorted array containing values that are created by concatenating the values of the indexed columns.

MySQL uses multiple-column indexes in such a way that queries are fast when you specify a known quantity for the first column of the index in a WHERE clause, even if you don't specify values for the other columns.

Suppose a table is created using the following specification:

mysql> CREATE TABLE test (
           id INT NOT NULL,
           last_name CHAR(30) NOT NULL,
           first_name CHAR(30) NOT NULL,
           PRIMARY KEY (id),
           INDEX name (last_name,first_name));

Then the index name is an index over last_name and first_name. The index will be used for queries that specify values in a known range for last_name, or for both last_name and first_name. Therefore, the name index will be used in the following queries:

mysql> SELECT * FROM test WHERE last_name="Widenius";

mysql> SELECT * FROM test WHERE last_name="Widenius"
                          AND first_name="Michael";

mysql> SELECT * FROM test WHERE last_name="Widenius"
                          AND (first_name="Michael" OR first_name="Monty");

mysql> SELECT * FROM test WHERE last_name="Widenius"
                          AND first_name >="M" AND first_name < "N";

However, the name index will NOT be used in the following queries:

mysql> SELECT * FROM test WHERE first_name="Michael";

mysql> SELECT * FROM test WHERE last_name="Widenius"
                          OR first_name="Michael";

For more information on the manner in which MySQL uses indexes to improve query performance, see section 12.4 How MySQL Uses Indexes.

7.3.8 Using Column Types from Other Database Engines

To make it easier to use code written for SQL implementations from other vendors, MySQL maps column types as shown in the table below. These mappings make it easier to move table definitions from other database engines to MySQL:

Other vendor type	MySQL type
`BINARY(NUM)`	`CHAR(NUM) BINARY`
`CHAR VARYING(NUM)`	`VARCHAR(NUM)`
`FLOAT4`	`FLOAT`
`FLOAT8`	`DOUBLE`
`INT1`	`TINYINT`
`INT2`	`SMALLINT`
`INT3`	`MEDIUMINT`
`INT4`	`INT`
`INT8`	`BIGINT`
`LONG VARBINARY`	`MEDIUMBLOB`
`LONG VARCHAR`	`MEDIUMTEXT`
`MIDDLEINT`	`MEDIUMINT`
`VARBINARY(NUM)`	`VARCHAR(NUM) BINARY`

Column type mapping occurs at table creation time. If you create a table with types used by other vendors and then issue a DESCRIBE tbl_name statement, MySQL reports the table structure using the equivalent MySQL types.

7.4 Functions for Use in `SELECT` and `WHERE` Clauses

A select_expression or where_definition in a SQL statement can consist of any expression using the functions described below.

An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for the operators and functions involved in the expression.

NOTE: There must be no whitespace between a function name and the parenthesis following it. This helps the MySQL parser distinguish between function calls and references to tables or columns that happen to have the same name as a function. Spaces around arguments are permitted, though.

You can force MySQL to accept spaces after the function name by starting mysqld with --ansi or using the CLIENT_IGNORE_SPACE to mysql_connect(), but in this case all function names will become reserved words. See section 5.2 Running MySQL in ANSI Mode.

For the sake of brevity, examples display the output from the mysql program in abbreviated form. So this:

mysql> select MOD(29,9);
1 rows in set (0.00 sec)

+-----------+
| mod(29,9) |
+-----------+
|         2 |
+-----------+

is displayed like this:

mysql> select MOD(29,9);
        -> 2

7.4.1 Grouping Functions

( ... )

Parentheses. Use these to force the order of evaluation in an expression:

mysql> select 1+2*3;
        -> 7
mysql> select (1+2)*3;
        -> 9

7.4.2 Normal Arithmetic Operations

The usual arithmetic operators are available. Note that in the case of `-', `+', and `*', the result is calculated with BIGINT (64-bit) precision if both arguments are integers!

+

Addition:

mysql> select 3+5;
        -> 8

-

Subtraction:

mysql> select 3-5;
        -> -2

*

Multiplication:

mysql> select 3*5;
        -> 15
mysql> select 18014398509481984*18014398509481984.0;
        -> 324518553658426726783156020576256.0
mysql> select 18014398509481984*18014398509481984;
        -> 0

The result of the last expression is incorrect because the result of the integer multiplication exceeds the 64-bit range of BIGINT calculations.

/

Division:

mysql> select 3/5;
        -> 0.60

Division by zero produces a NULL result:

mysql> select 102/(1-1);
        -> NULL

A division will be calculated with BIGINT arithmetic only if performed in a context where its result is converted to an integer!

7.4.3 Bit Functions

MySQL uses BIGINT (64-bit) arithmetic for bit operations, so these operators have a maximum range of 64 bits.

|

Bitwise OR:

mysql> select 29 | 15;
        -> 31

&

Bitwise AND:

mysql> select 29 & 15;
        -> 13

<<

Shifts a longlong (BIGINT) number to the left:

mysql> select 1 << 2
        -> 4

>>

Shifts a longlong (BIGINT) number to the right:

mysql> select 4 >> 2
        -> 1

~

Invert all bits:

mysql> select 5 & ~1
        -> 4

BIT_COUNT(N)

Returns the number of bits that are set in the argument N:

mysql> select BIT_COUNT(29);
        -> 4

7.4.4 Logical Operations

All logical functions return 1 (TRUE), 0 (FALSE) or NULL (unknown, which is in most cases the same as FALSE):

NOT

!

Logical NOT. Returns 1 if the argument is 0, otherwise returns 0. Exception: NOT NULL returns NULL:

mysql> select NOT 1;
        -> 0
mysql> select NOT NULL;
        -> NULL
mysql> select ! (1+1);
        -> 0
mysql> select ! 1+1;
        -> 1

The last example returns 1 because the expression evaluates the same way as (!1)+1.

OR

||

Logical OR. Returns 1 if either argument is not 0 and not NULL:

mysql> select 1 || 0;
        -> 1
mysql> select 0 || 0;
        -> 0
mysql> select 1 || NULL;
        -> 1

AND

&&

Logical AND. Returns 0 if either argument is 0 or NULL, otherwise returns 1:

mysql> select 1 && NULL;
        -> 0
mysql> select 1 && 0;
        -> 0

7.4.5 Comparison Operators

Comparison operations result in a value of 1 (TRUE), 0 (FALSE), or NULL. These functions work for both numbers and strings. Strings are automatically converted to numbers and numbers to strings as needed (as in Perl).

MySQL performs comparisons using the following rules:

If one or both arguments are NULL, the result of the comparison is NULL, except for the <=> operator.
If both arguments in a comparison operation are strings, they are compared as strings.
If both arguments are integers, they are compared as integers.
Hexadecimal values are treated as binary strings if not compared to a number.
If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly.
In all other cases, the arguments are compared as floating-point (real) numbers.

By default, string comparisons are done in case-independent fashion using the current character set (ISO-8859-1 Latin1 by default, which also works excellently for English).

The examples below illustrate conversion of strings to numbers for comparison operations:

mysql> SELECT 1 > '6x';
         -> 0
mysql> SELECT 7 > '6x';
         -> 1
mysql> SELECT 0 > 'x6';
         -> 0
mysql> SELECT 0 = 'x6';
         -> 1

=

Equal:

mysql> select 1 = 0;
        -> 0
mysql> select '0' = 0;
        -> 1
mysql> select '0.0' = 0;
        -> 1
mysql> select '0.01' = 0;
        -> 0
mysql> select '.01' = 0.01;
        -> 1

<>

!=

Not equal:

mysql> select '.01' <> '0.01';
        -> 1
mysql> select .01 <> '0.01';
        -> 0
mysql> select 'zapp' <> 'zappp';
        -> 1

<=

Less than or equal:

mysql> select 0.1 <= 2;
        -> 1

<

Less than:

mysql> select 2 <= 2;
        -> 1

>=

Greater than or equal:

mysql> select 2 >= 2;
        -> 1

>

Greater than:

mysql> select 2 > 2;
        -> 0

<=>

Null safe equal:

mysql> select 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1 1 0

IS NULL

IS NOT NULL

Test whether or not a value is or is not NULL:

mysql> select 1 IS NULL, 0 IS NULL, NULL IS NULL:
        -> 0 0 1
mysql> select 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
        -> 1 1 0

expr BETWEEN min AND max

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. The first argument (expr) determines how the comparison is performed as follows:

If expr is a TIMESTAMP, DATE, or DATETIME column, MIN() and MAX() are formatted to the same format if they are constants.
If expr is a case-insensitive string expression, a case-insensitive string comparison is done.
If expr is a case-sensitive string expression, a case-sensitive string comparison is done.
If expr is an integer expression, an integer comparison is done.
Otherwise, a floating-point (real) comparison is done.

mysql> select 1 BETWEEN 2 AND 3;
        -> 0
mysql> select 'b' BETWEEN 'a' AND 'c';
        -> 1
mysql> select 2 BETWEEN 2 AND '3';
        -> 1
mysql> select 2 BETWEEN 2 AND 'x-3';
        -> 0

expr IN (value,...)

Returns 1 if expr is any of the values in the IN list, else returns 0. If all values are constants, then all values are evaluated according to the type of expr and sorted. The search for the item is then done using a binary search. This means IN is very quick if the IN value list consists entirely of constants. If expr is a case-sensitive string expression, the string comparison is performed in case-sensitive fashion:

mysql> select 2 IN (0,3,5,'wefwf');
        -> 0
mysql> select 'wefwf' IN (0,3,5,'wefwf');
        -> 1

expr NOT IN (value,...)

Same as NOT (expr IN (value,...)).

ISNULL(expr)

If expr is NULL, ISNULL() returns 1, otherwise it returns 0:

mysql> select ISNULL(1+1);
        -> 0
mysql> select ISNULL(1/0);
        -> 1

Note that a comparison of NULL values using = will always be false!

COALESCE(list)

Returns first non-NULL element in list:

mysql> select COALESCE(NULL,1);
        -> 1
mysql> select COALESCE(NULL,NULL,NULL);
        -> NULL

INTERVAL(N,N1,N2,N3,...)

Returns 0 if N < N1, 1 if N < N2 and so on. All arguments are treated as integers. It is required that N1 < N2 < N3 < ... < Nn for this function to work correctly. This is because a binary search is used (very fast):

mysql> select INTERVAL(23, 1, 15, 17, 30, 44, 200);
        -> 3
mysql> select INTERVAL(10, 1, 10, 100, 1000);
        -> 2
mysql> select INTERVAL(22, 23, 30, 44, 200);
        -> 0

7.4.6 String Comparison Functions

Normally, if any expression in a string comparison is case sensitive, the comparison is performed in case-sensitive fashion.

expr LIKE pat [ESCAPE 'escape-char']

Pattern matching using SQL simple regular expression comparison. Returns 1 (TRUE) or 0 (FALSE). With LIKE you can use the following two wild-card characters in the pattern:

`%`	Matches any number of characters, even zero characters
`_`	Matches exactly one character

mysql> select 'David!' LIKE 'David_';
        -> 1
mysql> select 'David!' LIKE '%D%v%';
        -> 1

To test for literal instances of a wild-card character, precede the character with the escape character. If you don't specify the ESCAPE character, `\' is assumed:

`\%`	Matches one `%` character
`\_`	Matches one `_` character

mysql> select 'David!' LIKE 'David\_';
        -> 0
mysql> select 'David_' LIKE 'David\_';
        -> 1

To specify a different escape character, use the ESCAPE clause:

mysql> select 'David_' LIKE 'David|_' ESCAPE '|';
        -> 1

LIKE is allowed on numeric expressions! (This is a MySQL extension to the ANSI SQL LIKE.)

mysql> select 10 LIKE '1%';
        -> 1

Note: Because MySQL uses the C escape syntax in strings (for example, `\n'), you must double any `\' that you use in your LIKE strings. For example, to search for `\n', specify it as `\\n'. To search for `\', specify it as `\\\\' (the backslashes are stripped once by the parser and another time when the pattern match is done, leaving a single backslash to be matched).

expr NOT LIKE pat [ESCAPE 'escape-char']

Same as NOT (expr LIKE pat [ESCAPE 'escape-char']).

expr REGEXP pat

expr RLIKE pat

Performs a pattern match of a string expression expr against a pattern pat. The pattern can be an extended regular expression. See section J Description of MySQL regular expression syntax. Returns 1 if expr matches pat, otherwise returns 0. RLIKE is a synonym for REGEXP, provided for mSQL compatibility. Note: Because MySQL uses the C escape syntax in strings (for example, `\n'), you must double any `\' that you use in your REGEXP strings. As of MySQL Version 3.23.4, REGEXP is case insensitive for normal (not binary) strings:

mysql> select 'Monty!' REGEXP 'm%y%%';
        -> 0
mysql> select 'Monty!' REGEXP '.*';
        -> 1
mysql> select 'new*\n*line' REGEXP 'new\\*.\\*line';
        -> 1
mysql> select "a" REGEXP "A", "a" REGEXP BINARY "A";
        -> 1  0
mysql> select "a" REGEXP "^[a-d]";
        -> 1

REGEXP and RLIKE use the current character set (ISO-8859-1 Latin1 by default) when deciding the type of a character.

expr NOT REGEXP pat

expr NOT RLIKE pat

Same as NOT (expr REGEXP pat).

STRCMP(expr1,expr2)

STRCMP() returns 0 if the strings are the same, -1 if the first argument is smaller than the second according to the current sort order, and 1 otherwise:

mysql> select STRCMP('text', 'text2');
        -> -1
mysql> select STRCMP('text2', 'text');
        -> 1
mysql> select STRCMP('text', 'text');
        -> 0

MATCH (col1,col2,...) AGAINST (expr)

MATCH ... AGAINST() is used for full-text search and returns relevance - similarity measure between the text in columns (col1,col2,...) and the query expr. Relevance is a positive floating-point number. Zero relevance means no similarity. For MATCH ... AGAINST() to work, a FULLTEXT index must be created first. See section 7.7 CREATE TABLE Syntax. MATCH ... AGAINST() is available in MySQL Version 3.23.23 or later. For details and usage examples see section 25.2 MySQL Full-text Search.

7.4.7 Cast Operators

BINARY

The BINARY operator casts the string following it to a binary string. This is an easy way to force a column comparison to be case sensitive even if the column isn't defined as BINARY or BLOB:

mysql> select "a" = "A";
        -> 1
mysql> select BINARY "a" = "A";
        -> 0

BINARY was introduced in MySQL Version 3.23.0. Note that in some context MySQL will not be able to use the index efficiently when you cast an indexed column to BINARY.

If you want to compare a blob case-insensitively you can always convert the blob to upper case before doing the comparison:

SELECT 'A' LIKE UPPER(blob_col) FROM table_name;

We plan to soon introduce casting between different character sets to make string comparison even more flexible.

7.4.8 Control Flow Functions

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1, else it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used:

mysql> select IFNULL(1,0);
        -> 1
mysql> select IFNULL(NULL,10);
        -> 10
mysql> select IFNULL(1/0,10);
        -> 10
mysql> select IFNULL(1/0,'yes');
        -> 'yes'

NULLIF(expr1,expr2)

If expr1 = expr2 is true, return NULL else return expr1. This is the same as CASE WHEN x = y THEN NULL ELSE x END:

mysql> select NULLIF(1,1);
        -> NULL
mysql> select NULLIF(1,2);
        -> 1

Note that expr1 is evaluated twice in MySQL if the arguments are equal.

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2, else it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used:

mysql> select IF(1>2,2,3);
        -> 3
mysql> select IF(1<2,'yes','no');
        -> 'yes'
mysql> select IF(strcmp('test','test1'),'no','yes');
        -> 'no'

expr1 is evaluated as an integer value, which means that if you are testing floating-point or string values, you should do so using a comparison operation:

mysql> select IF(0.1,1,0);
        -> 0
mysql> select IF(0.1<>0,1,0);
        -> 1

In the first case above, IF(0.1) returns 0 because 0.1 is converted to an integer value, resulting in a test of IF(0). This may not be what you expect. In the second case, the comparison tests the original floating-point value to see whether it is non-zero. The result of the comparison is used as an integer. The default return type of IF() (which may matter when it is stored into a temporary table) is calculated in MySQL Version 3.23 as follows:

Expression	Return value
expr2 or expr3 returns string	string
expr2 or expr3 returns a floating-point value	floating-point
expr2 or expr3 returns an integer	integer

CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END

CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

The first version returns the result where value=compare-value. The second version returns the result for the first condition, which is true. If there was no matching result value, then the result after ELSE is returned. If there is no ELSE part then NULL is returned:

mysql> SELECT CASE 1 WHEN 1 THEN "one" WHEN 2 THEN "two" ELSE "more" END;
       -> "one"
mysql> SELECT CASE WHEN 1>0 THEN "true" ELSE "false" END;
       -> "true"
mysql> SELECT CASE BINARY "B" when "a" then 1 when "b" then 2 END;
       -> NULL

The type of the return value (INTEGER, DOUBLE or STRING) is the same as the type of the first returned value (the expression after the first THEN).

7.4.9 Mathematical Functions

All mathematical functions return NULL in case of an error.

-

Unary minus. Changes the sign of the argument:

mysql> select - 2;
        -> -2

Note that if this operator is used with a BIGINT, the return value is a BIGINT! This means that you should avoid using - on integers that may have the value of -2^63!

ABS(X)

Returns the absolute value of X:

mysql> select ABS(2);
        -> 2
mysql> select ABS(-32);
        -> 32

This function is safe to use with BIGINT values.

SIGN(X)

Returns the sign of the argument as -1, 0, or 1, depending on whether X is negative, zero, or positive:

mysql> select SIGN(-32);
        -> -1
mysql> select SIGN(0);
        -> 0
mysql> select SIGN(234);
        -> 1

MOD(N,M)

%

Modulo (like the % operator in C). Returns the remainder of N divided by M:

mysql> select MOD(234, 10);
        -> 4
mysql> select 253 % 7;
        -> 1
mysql> select MOD(29,9);
        -> 2

This function is safe to use with BIGINT values.

FLOOR(X)

Returns the largest integer value not greater than X:

mysql> select FLOOR(1.23);
        -> 1
mysql> select FLOOR(-1.23);
        -> -2

Note that the return value is converted to a BIGINT!

CEILING(X)

Returns the smallest integer value not less than X:

mysql> select CEILING(1.23);
        -> 2
mysql> select CEILING(-1.23);
        -> -1

Note that the return value is converted to a BIGINT!

ROUND(X)

Returns the argument X, rounded to the nearest integer:

mysql> select ROUND(-1.23);
        -> -1
mysql> select ROUND(-1.58);
        -> -2
mysql> select ROUND(1.58);
        -> 2

ROUND(X,D)

Returns the argument X, rounded to a number with D decimals. If D is 0, the result will have no decimal point or fractional part:

mysql> select ROUND(1.298, 1);
        -> 1.3
mysql> select ROUND(1.298, 0);
        -> 1

EXP(X)

Returns the value of e (the base of natural logarithms) raised to the power of X:

mysql> select EXP(2);
        -> 7.389056
mysql> select EXP(-2);
        -> 0.135335

LOG(X)

Returns the natural logarithm of X:

mysql> select LOG(2);
        -> 0.693147
mysql> select LOG(-2);
        -> NULL

If you want the log of a number X to some arbitary base B, use the formula LOG(X)/LOG(B).

LOG10(X)

Returns the base-10 logarithm of X:

mysql> select LOG10(2);
        -> 0.301030
mysql> select LOG10(100);
        -> 2.000000
mysql> select LOG10(-100);
        -> NULL

POW(X,Y)

POWER(X,Y)

Returns the value of X raised to the power of Y:

mysql> select POW(2,2);
        -> 4.000000
mysql> select POW(2,-2);
        -> 0.250000

SQRT(X)

Returns the non-negative square root of X:

mysql> select SQRT(4);
        -> 2.000000
mysql> select SQRT(20);
        -> 4.472136

PI()

Returns the value of PI:

mysql> select PI();
        -> 3.141593

COS(X)

Returns the cosine of X, where X is given in radians:

mysql> select COS(PI());
        -> -1.000000

SIN(X)

Returns the sine of X, where X is given in radians:

mysql> select SIN(PI());
        -> 0.000000

TAN(X)

Returns the tangent of X, where X is given in radians:

mysql> select TAN(PI()+1);
        -> 1.557408

ACOS(X)

Returns the arc cosine of X, that is, the value whose cosine is X. Returns NULL if X is not in the range -1 to 1:

mysql> select ACOS(1);
        -> 0.000000
mysql> select ACOS(1.0001);
        -> NULL
mysql> select ACOS(0);
        -> 1.570796

ASIN(X)

Returns the arc sine of X, that is, the value whose sine is X. Returns NULL if X is not in the range -1 to 1:

mysql> select ASIN(0.2);
        -> 0.201358
mysql> select ASIN('foo');
        -> 0.000000

ATAN(X)

Returns the arc tangent of X, that is, the value whose tangent is X:

mysql> select ATAN(2);
        -> 1.107149
mysql> select ATAN(-2);
        -> -1.107149

ATAN2(Y,X)

Returns the arc tangent of the two variables X and Y. It is similar to calculating the arc tangent of Y / X, except that the signs of both arguments are used to determine the quadrant of the result:

mysql> select ATAN(-2,2);
        -> -0.785398
mysql> select ATAN(PI(),0);
        -> 1.570796

COT(X)

Returns the cotangent of X:

mysql> select COT(12);
        -> -1.57267341
mysql> select COT(0);
        -> NULL

RAND()

RAND(N)

Returns a random floating-point value in the range 0 to 1.0. If an integer argument N is specified, it is used as the seed value:

mysql> select RAND();
        -> 0.5925
mysql> select RAND(20);
        -> 0.1811
mysql> select RAND(20);
        -> 0.1811
mysql> select RAND();
        -> 0.2079
mysql> select RAND();
        -> 0.7888

You can't use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. In MySQL Version 3.23, you can, however, do: SELECT * FROM table_name ORDER BY RAND() This is useful to get a random sample of a set

SELECT * FROM
table1,table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000

. Note that a RAND() in a WHERE clause will be re-evaluated every time the WHERE is executed.

LEAST(X,Y,...)

With two or more arguments, returns the smallest (minimum-valued) argument. The arguments are compared using the following rules:

If the return value is used in an INTEGER context, or all arguments are integer-valued, they are compared as integers.
If the return value is used in a REAL context, or all arguments are real-valued, they are compared as reals.
If any argument is a case-sensitive string, the arguments are compared as case-sensitive strings.
In other cases, the arguments are compared as case-insensitive strings:

mysql> select LEAST(2,0);
        -> 0
mysql> select LEAST(34.0,3.0,5.0,767.0);
        -> 3.0
mysql> select LEAST("B","A","C");
        -> "A"

In MySQL versions prior to Version 3.22.5, you can use MIN() instead of LEAST.

GREATEST(X,Y,...)

Returns the largest (maximum-valued) argument. The arguments are compared using the same rules as for LEAST:

mysql> select GREATEST(2,0);
        -> 2
mysql> select GREATEST(34.0,3.0,5.0,767.0);
        -> 767.0
mysql> select GREATEST("B","A","C");
        -> "C"

In MySQL versions prior to Version 3.22.5, you can use MAX() instead of GREATEST.

DEGREES(X)

Returns the argument X, converted from radians to degrees:

mysql> select DEGREES(PI());
        -> 180.000000

RADIANS(X)

Returns the argument X, converted from degrees to radians:

mysql> select RADIANS(90);
        -> 1.570796

TRUNCATE(X,D)

Returns the number X, truncated to D decimals. If D is 0, the result will have no decimal point or fractional part:

mysql> select TRUNCATE(1.223,1);
        -> 1.2
mysql> select TRUNCATE(1.999,1);
        -> 1.9
mysql> select TRUNCATE(1.999,0);
        -> 1

7.4.10 String Functions

String-valued functions return NULL if the length of the result would be greater than the max_allowed_packet server parameter. See section 12.2.3 Tuning Server Parameters.

For functions that operate on string positions, the first position is numbered 1.

ASCII(str)

Returns the ASCII code value of the leftmost character of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL:

mysql> select ASCII('2');
        -> 50
mysql> select ASCII(2);
        -> 50
mysql> select ASCII('dx');
        -> 100

Column type	``Zero'' value
`DATETIME`	`'0000-00-00 00:00:00'`
`DATE`	`'0000-00-00'`
`TIMESTAMP`	`00000000000000` (length depends on display size)
`TIME`	`'00:00:00'`
`YEAR`	`0000`