6. MySQL Language Reference

MySQL has a very complex, but intuitive and easy to learn SQL interface. This chapter describes the various commands, types, and functions you will need to know in order to use MySQL efficiently and effectively. This chapter also serves as a reference to all functionality included in MySQL. In order to use this chapter effectively, you may find it useful to refer to the various indexes.

6.1 Language Structure

6.1.1 Literals: How to Write Strings and Numbers

This section describes the various ways to write strings and numbers in MySQL. It also covers the various nuances and "gotchas" that you may run into when dealing with these basic types in MySQL.

6.1.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 recognises the following escape sequences:

\0

An ASCII 0 (NUL) character.

\'

A single quote (`'') character.

\"

A double quote (`"') character.

\b

A backspace character.

\n

A newline character.

\r

A carriage return character.

\t

A tab character.

\z

ASCII(26) (Control-Z). This character can be encoded to allow you to work around the problem that ASCII(26) stands for END-OF-FILE on Windows. (ASCII(26) will cause problems if you try to use mysql database < filename.)

\\

A backslash (`\') character.

\%

A `%' character. This is used to search for literal instances of `%' in contexts where `%' would otherwise be interpreted as a wildcard character. See section String Comparison Functions.

\_

A `_' character. This is used to search for literal instances of `_' in contexts where `_' would otherwise be interpreted as a wildcard character. See section 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 here 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 string column (such as a BLOB), 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_real_escape_string() to escape characters for the INSERT statement. See section 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 Perl DBI Class.

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

Alternatively, many MySQL APIs provide some sort of placeholder capability that allows you to insert special markers into a query string, and then bind data values to them when you issue the query. In this case, the API takes case of escaping special characters in the values for you automatically.

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

From version 4.1.0, the constant TRUE evaluates to 1 and the constant FALSE evaluates to 0.

6.1.1.3 Hexadecimal Values

MySQL supports hexadecimal values. In numeric 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 x'4D7953514C';
         -> MySQL
mysql> SELECT 0xa+0;
         -> 10
mysql> SELECT 0x5061756c;
         -> Paul

In MySQL 4.1 (and in MySQL 4.0 when using the --new option) the default type of of a hexadecimal value is a string. If you want to be sure that the string is threated as a number, you can use CAST( ... AS UNSIGNED) on the hexadecimal value.

The x'hexstring' syntax (new in 4.0) is based on standard SQL and the 0x syntax is based on ODBC. Hexadecimal strings are often used by ODBC to supply values for BLOB columns. You can convert a string or a number to string in hexadecimal format with the HEX() function.

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

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

6.1.2 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 Running MySQL in ANSI Mode.

Note that in addition to the above, you can't have ASCII(0) or ASCII(255) or the quoting character in an identifier.

Note that if the identifier is a restricted word or contains special characters you must always quote it with a ` (backtick) when you use it:

mysql> SELECT * FROM `select` WHERE `select`.id > 100;

See section Is MySQL Picky About Reserved Words?.

In MySQL versions prior to 3.23.6, 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 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 digit (this differs from many other database systems!). However, a name cannot consist only of digits.
• 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:

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.

6.1.3 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-insensitive in Windows, and case-sensitive in most varieties of Unix. One prominent exception here is Mac OS X, when the default HFS+ file system is being used. However Mac OS X also supports UFS volumes, those are case sensitive on Mac OS X just like they are on any Unix. See section MySQL Extensions To The SQL-92 Standard.

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 and column aliases 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;

If you have trouble remembering the lettercase for database and 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. By default this option is 1 on Windows and 0 on Unix.

If lower_case_table_names is 1 MySQL will convert all table names to lowercase on storage and lookup. (From version 4.0.2, this option also applies to database names. From 4.1.1 this also applies for table alias).

Note that if you change this option, you need to first convert your old table names to lower case before starting mysqld.

If you move MyISAM files from a Windows to a Unix disk, you may in some cases need to use the `mysql_fix_extensions' tool to fix-up the case of the file extensions in each specified database directory (lowercase `.frm', uppercase `.MYI' and `.MYD'). `mysql_fix_extensions' can be found in the `scripts' subdirectory.

6.1.4 User Variables

MySQL supports connection-specific user 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 The Character Set Used for Data and Sorting. User variable names are case insensitive in versions >= 5.0, case sensitive in versions < 5.0.

Variables don't have to be initialised. 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 assign a value to a variable in statements other than SET. However, in this case the assignment operator is := rather than =, because = is reserved for comparisons in non-SET statements:

mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1  | @t2  | @t3  |
+----------------------+------+------+------+
|                    5 |    5 |    1 |    4 |
+----------------------+------+------+------+

User variables may be used where expressions are allowed. Note that this does not currently include 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 evaluated only 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:

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

The rule is to never assign and use the same variable in the same statement.

6.1.5 System Variables

Starting from MySQL 4.0.3 we provide better access to a lot of system and connection variables. One can change most of them without having to take down the server.

There are two kind of system variables: Thread-specific (or connection-specific) variables that are unique to the current connection and global variables that are used to configure global events. Global variables also are used to set up the initial values of the corresponding thread-specific variables for new connections.

When mysqld starts, all global variables are initialised from command line arguments and option files. You can change the value with the SET GLOBAL command. When a new thread is created, the thread-specific variables are initialised from the global variables and they will not change even if you issue a new SET GLOBAL command.

To set the value for a GLOBAL variable, you should use one of the following syntaxes: (Here we use sort_buffer_size as an example variable)

SET GLOBAL sort_buffer_size=value;
SET @@global.sort_buffer_size=value;

To set the value for a SESSION variable, you can use one of the following syntaxes:

SET SESSION sort_buffer_size=value;
SET @@session.sort_buffer_size=value;
SET sort_buffer_size=value;

If you don't specify GLOBAL or SESSION then SESSION is used. See section SET Syntax.

LOCAL is a synonym for SESSION.

To retrieve the value for a GLOBAL variable you can use one of the following commands:

SELECT @@global.sort_buffer_size;
SHOW GLOBAL VARIABLES like 'sort_buffer_size';

To retrieve the value for a SESSION variable you can use one of the following commands:

SELECT @@session.sort_buffer_size;
SHOW SESSION VARIABLES like 'sort_buffer_size';

When you retrieve a variable value with the @@variable_name syntax and you don't specify GLOBAL or SESSION then MySQL will return the thread-specific (SESSION) value if it exists. If not, MySQL will return the global value.

The reason for requiring GLOBAL for setting GLOBAL only variables but not for retrieving them is to ensure that we don't later run into problems if we later would introduce a thread-specific variable with the same name or remove a thread-specific variable. In this case, you could accidentally change the state for the server as a whole, rather than just for your own connection.

The following is a full list of all variables that you change and retrieve and if you can use GLOBAL or SESSION with them.

Variables that are marked with num can be given a numerical value. Variables that are marked with bool can be set to 0, 1, ON or OFF. Variables that are of type enum should normally be set to one of the available values for the variable, but can also be set to the number that correspond to the enum value. (The first enum value is 0).

Here is a description of some of the variables:

A description of the other variable definitions can be found in the startup options section, the description of SHOW VARIABLES and in the SET section. See section mysqld Command-line Options. See section SHOW VARIABLES. See section SET Syntax.

6.1.6 Comment Syntax

The MySQL server supports the # to end of line, -- to end of line and /* in-line or multiple-line */ comment styles:

mysql> SELECT 1+1;     # This comment continues to the end of line
mysql> SELECT 1+1;     -- This comment continues to the end of line
mysql> SELECT 1 /* this is an in-line comment */ + 1;
mysql> SELECT 1+
/*
this is a
multiple-line comment
*/
1;

Note that the -- (double-dash) comment style requires you to have at least one space after the second dash!

Although the server understands the comment syntax just described, there are some limitations on the way that the mysql client parses /* ... */ comments:

• Single-quote and double-quote characters are taken to indicate the beginning of a quoted string, even within a comment. If the quote is not matched by a second quote within the comment, the parser doesn't realise the comment has ended. If you are running mysql interactively, you can tell that it has gotten confused like this because the prompt changes from mysql> to '> or ">.
• A semicolon is taken to indicate the end of the current SQL statement and anything following it to indicate the beginning of the next statement.

These limitations apply both when you run mysql interactively and when you put commands in a file and tell mysql to read its input from that file with mysql < some-file.

MySQL supports the `--' SQL-99 comment style only if the second dash is followed by a space. See section `--' as the Start of a Comment.

6.1.7 Is MySQL Picky About Reserved Words?

A common problem stems from trying to create a table with column names that use the names of datatypes or functions built into MySQL, such as TIMESTAMP or GROUP. You're allowed to do it (for example, ABS is allowed as a column name). However, by default, in function invocations no whitespace is allowed between the function name and the following `(' character, so that a function call can be distinguished from a reference to a column name.

If you start the server with the --ansi or --sql-mode=IGNORE_SPACE option, the server allows function invocations to have whitespace between a function name and the following `(' character. This causes function names to be treated as reserved words; as a result, column names that are the same as function names must be quoted as described in Database, Table, Index, Column, and Alias Names.

The following words are explicitly reserved in MySQL. Most of them are forbidden by SQL-92 as column and/or table names (for example, GROUP). A few are reserved because MySQL needs them and is (currently) using a yacc parser:

The following symbols (from the table above) are disallowed by SQL-99 but allowed by MySQL as column/table names. This is because some of these names are very natural names and a lot of people have already used them.

• ACTION
• BIT
• DATE
• ENUM
• NO
• TEXT
• TIME
• TIMESTAMP

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

Warning: you should be aware that when you use subtraction between integer values where one is of type UNSIGNED, the result will be unsigned! See section Cast Functions.

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.

BIT

BOOL

BOOLEAN

In version 4.0 and earlier, these are synonyms for TINYINT(1). From version 4.1.0, the storage requirement is a single bit (plus the usual requirement for NULL if the column is not specified as NOT NULL).

The BOOLEAN synonym was added in version 4.1.0

Full boolean type handling will be introduced in accordance with SQL-99.

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. Some things you should be aware of with respect to BIGINT columns:

• 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! If you do that, some of the last digits in the result may be wrong because of rounding errors when converting the BIGINT to a DOUBLE.

  MySQL 4.0 can handle BIGINT in the following cases:

  • Use integers to store big unsigned values in a BIGINT column.
  • In MIN(big_int_column) and MAX(big_int_column).
  • When using operators (+, -, *, etc.) where both operands are integers.
• You can always store an exact integer value in a BIGINT column by storing it as a string. In this case, MySQL will perform a string-to-number conversion that involves no intermediate double representation.
• `-', `+', 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 when the result is larger than 9223372036854775807.

FLOAT(precision) [UNSIGNED] [ZEROFILL]

A floating-point number. 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 are 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 calculations in MySQL are done with double precision. See section Solving Problems with No Matching Rows.

This syntax is provided for ODBC compatibility.

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

A small (single-precision) floating-point number. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. If UNSIGNED is specified, negative values are disallowed. The M is the display width and D is the number of decimals. FLOAT without arguments or FLOAT(X) where X <= 24 stands for a single-precision floating-point number.

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

A normal-size (double-precision) floating-point number. Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. If UNSIGNED is specified, negative values are disallowed. The M is the display width and D is the number of decimals. DOUBLE without arguments or FLOAT(X) where 25 <= X <= 53 stands for a double-precision floating-point number.

DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL]

REAL[(M,D)] [UNSIGNED] [ZEROFILL]

These are synonyms for DOUBLE.

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

An unpacked floating-point number. 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 is 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 UNSIGNED is specified, negative values are disallowed.

If D is omitted, the default is 0. If M is omitted, the default is 10.

Prior to MySQL Version 3.23, the M argument must include the space needed for the sign and the decimal point.

DEC[(M[,D])] [UNSIGNED] [ZEROFILL]

NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]

FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]

These are synonyms for DECIMAL.

The FIXED alias was added in version 4.1.0 for compatibility with other servers.

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

In MySQL 4.0 and earlier, TIMESTAMP values are displayed 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.

From MySQL 4.1, TIMESTAMP is returned as a string with the format 'YYYY-MM-DD HH:MM:SS'. If you want to have this as a number you should add +0 to the timestamp column. Different timestamp lengths are not supported. From version 4.0.12, the --new option can be used to make the server behave as in version 4.1.

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 Date and Time Types.

The M argument affects only how a TIMESTAMP column is displayed; its values always are stored using 4 bytes each.

Note that TIMESTAMP(M) columns where M is 8 or 14 are reported to be numbers while other TIMESTAMP(M) 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 DATETIME.

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

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 unavailable prior to MySQL Version 3.22.) See section YEAR.

[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 0 to 255 characters (1 to 255 prior to MySQL Version 3.23). 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.

From version 4.1.0, if the M value specified is greater than 255, the column type is converted to TEXT. This is a compatibility feature.

NATIONAL CHAR (or its equivalent short form, NCHAR) is the SQL-99 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 occupy only one bit and can take only 2 values: NULL or "". See section CHAR.

CHAR

This is a synonym for CHAR(1).

[NATIONAL] VARCHAR(M) [BINARY]

A variable-length string. Note: trailing spaces are removed when the value is stored (this differs from the SQL-99 specification). The range of M is 0 to 255 characters (1 to 255 prior to MySQL Version 4.0.2). VARCHAR values are sorted and compared in case-insensitive fashion unless the BINARY keyword is given. See section Silent Column Specification Changes.

From version 4.1.0, if the M value specified is greater than 255, the column type is converted to TEXT. This is a compatibility feature.

VARCHAR is a shorthand for CHARACTER VARYING. See section The CHAR and VARCHAR Types.

TINYBLOB

TINYTEXT

A BLOB or TEXT column with a maximum length of 255 (2^8 - 1) characters. See section Silent Column Specification Changes. See section The BLOB and TEXT Types.

BLOB

TEXT

A BLOB or TEXT column with a maximum length of 65535 (2^16 - 1) characters. See section Silent Column Specification Changes. See section The BLOB and TEXT Types.

MEDIUMBLOB

MEDIUMTEXT

A BLOB or TEXT column with a maximum length of 16777215 (2^24 - 1) characters. See section Silent Column Specification Changes. See section The BLOB and TEXT Types.

LONGBLOB

LONGTEXT

A BLOB or TEXT column with a maximum length of 4294967295 or 4G (2^32 - 1) characters. See section Silent Column Specification Changes. Upto MySQL version 3.23 the server/client protocol and MyISAM tables had a limit of 16M per communication packet / table row, from version 4.x the maximum allowed length of LONGTEXT or LONGBLOB columns depends on the configured maximum packet size in the client/server protocol and available memory. See section 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 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 The SET Type.

6.2.1 Numeric Types

MySQL supports all of the SQL-92 numeric datatypes. These types include the exact numeric datatypes (NUMERIC, DECIMAL, INTEGER, and SMALLINT), as well as the approximate numeric datatypes (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 SQL-92 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(5,2)

In this example, 5 (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 -99.99 to 99.99. (MySQL can actually store numbers up to 999.99 in this column because it doesn't have to store the sign for positive numbers)

In SQL-92, 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 datatypes. 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 SQL-92 standard, MySQL also supports the integer 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 integer 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 integer 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.

As of MySQL 4.0.2, floating-point types also can be UNSIGNED. As with integer types, this attribute prevents negative values from being stored in the column. Unlike the integer types, the upper range of column values remains the same.

The FLOAT type is used to represent approximate numeric datatypes. The SQL-92 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 SQL-92 standard, MySQL recognises 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.

6.2.2 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 following table 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.

6.2.2.1 Y2K Issues and Date Types

MySQL itself is Y2K-safe (see section 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)).

6.2.2.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 from 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 initialise 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 initialise 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 following table. The `full' TIMESTAMP format is 14 digits, but TIMESTAMP columns may be created with shorter display sizes:

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.

Note: From version 4.1, TIMESTAMP is returned as a string with the format 'YYYY-MM-DD HH:MM:SS' and different timestamp lengths are no longer supported.

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, minute, 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.
• The MySQL server only performs basic checking on the validity of a date: days 00-31, months 00-12, years 1000-9999. Any date not within this range will revert to 0000-00-00. Please note that this still allows you to store invalid dates such as 2002-04-31. It allows web applications to store data from a form without further checking. To ensure a date is valid, perform a check in your application.
• 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 values in the range 70-99 are converted to 1970-1999.

6.2.2.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. Without colons, 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 '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'. TIME values with colons, by contrast, are always treated as time of the day. That is '11:12' will mean '11:12:00', not '00:11: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.

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

6.2.3 String Types

The string types are CHAR, VARCHAR, BLOB, TEXT, ENUM, and SET. This section describes how these types work, their storage requirements, and how to use them in your queries.

6.2.3.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 SQL-99 specification.) No case conversion takes place during storage or retrieval.

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 following table 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:

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.

From version 4.1.0, column type CHAR BYTE is an alias for CHAR BINARY. This is a compatibility feature.

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 Silent Column Specification Changes.

6.2.3.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 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. No case conversion takes place during storage or retrieval.

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.

From version 4.1.0, LONG and LONG VARCHAR map to the MEDIUMTEXT data type. This is a compatibility feature.

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 (max_allowed_packet), but you must do so on both the server and client ends. See section 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.

6.2.3.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. This string can be distinguished from a 'normal' empty string by the fact that this string has the numerical value 0. More about this later.
• 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 here. The index of each value is also shown:

An enumeration can have a maximum of 65535 elements.

Starting from 3.23.51 trailing spaces are automatically deleted from ENUM values when the table is created.

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.) It's not advisable to store numbers in an ENUM string because it will make things confusing.

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. To prevent unexpected results, specify the ENUM list in alphabetical order. You can also use GROUP BY CONCAT(col) to make sure the column is sorted alphabetically rather than by index number.

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.

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

Starting from 3.23.51 trailing spaces are automatically deleted from SET values when the table is created.

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:

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.

If you set a SET column to an unsupported value, the value will be ignored.

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.

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

6.2.5 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 following table. These mappings make it easier to move table definitions from other database engines to MySQL:

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.

6.2.6 Column Type Storage Requirements

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

Storage Requirements for Numeric Types

Storage Requirements for Date and Time Types

Storage Requirements for String Types

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 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 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 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 The SET Type.

The maximum size of a row in a MyISAM table is 65534 bytes. Each BLOB and TEXT column accounts for only 5-9 bytes toward this size.

6.3 Functions for Use in SELECT and WHERE Clauses

A select_expression or where_definition in an 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 parentheses 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 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

6.3.1 Non-Type-Specific Operators and Functions

6.3.1.1 Parentheses

( ... )

Use parentheses to force the order of evaluation in an expression. For example:

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

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

If you are comparing case-insensitive strings with any of the standard operators (=, <>..., but not LIKE) trailing whitespace (spaces, tabs and newlines) will be ignored.

mysql> SELECT "a" ="A \n";
        -> 1

The following examples 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; -> 0

>=

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

To be able to work well with other programs, MySQL supports the following extra features when using IS NULL:

• You can find the last inserted row with:

  SELECT * FROM tbl_name WHERE auto_col IS NULL

  This can be disabled by setting SQL_AUTO_IS_NULL=0. See section SET Syntax.

• For NOT NULL DATE and DATETIME columns you can find the special date 0000-00-00 by using:

  SELECT * FROM tbl_name WHERE date_column IS NULL

  This is needed to get some ODBC applications to work (as ODBC doesn't support a 0000-00-00 date)

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. Otherwise type conversion takes place, according to the rules above, but applied to all the three arguments. Note that before 4.0.5 arguments were converted to the type of expr instead.

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 NOT BETWEEN min AND max

Same as NOT (expr BETWEEN min AND max).

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

The number of values in the IN list is only limited by the max_allowed_packet value.

From 4.1 (to comply with the SQL-99 standard), IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.

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 or -1 if N is NULL. 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

6.3.1.3 Logical Operators

In SQL, all logical operators evaluate to TRUE, FALSE or NULL (UNKNOWN). In MySQL, this is implemented as 1 (TRUE), 0 (FALSE), and NULL. Most of this is common between different SQL databases, however some may return any non-zero value for TRUE.

NOT

!

Logical NOT. Evaluates to 1 if the operand is 0, to 0 if the operand is non-zero, and NOT NULL returns NULL.

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

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

AND

&&

Logical AND. Evaluates to 1 if all operands are non-zero and not NULL, to 0 if one or more operands are 0, otherwise NULL is returned.

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

Please note that MySQL versions prior to 4.0.5 stop evaluation when a NULL is encountered, rather than continuing the process to check for possible 0s. This means that in these versions, SELECT (NULL AND 0) returns NULL instead of 0. In 4.0.5 the code has been re-engineered so that the result will always be as prescribed by the SQL standards while still using the optimisation wherever possible.

OR

||

Logical OR. Evaluates to 1 if any operand is non-zero, to NULL if any operand is NULL, otherwise 0 is returned.

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

XOR

Logical XOR. Returns NULL if either operand is NULL. For non-NULL operands, evaluates to 1 if an odd number of operands is non-zero, otherwise 0 is returned.

example_for_help_topic XOR mysql> SELECT 1 XOR 1; -> 0 mysql> SELECT 1 XOR 0; -> 1 mysql> SELECT 1 XOR NULL; -> NULL mysql> SELECT 1 XOR 1 XOR 1; -> 1

a XOR b is mathematically equal to (a AND (NOT b)) OR ((NOT a) and b).

XOR was added in version 4.0.2.

6.3.1.4 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'

In 4.0.6 and above the default result value of IFNULL(expr1,expr2) is the more 'general' of the two expressions, in the order STRING, REAL or INTEGER.The difference to earlier MySQL versions are mostly notable when you create a table based on expressions or MySQL has to internally store a value from IFNULL() in a temporary table.

CREATE TABLE foo SELECT IFNULL(1,"test") as test;

In MySQL 4.0.6 the type for column 'test' is CHAR(4) while in earlier versions you would get BIGINT.

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 not 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'

If expr2 or expr3 is explicitely NULL then the result type of the IF() function is the type of the not NULL column. (This behaviour is new in MySQL 4.0.3).

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

If expr2 and expr3 are strings, then the result is case-insensitive if both strings are case-insensitive. (Starting from 3.23.51)

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

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

See also the ORD() function.

ORD(str)

If the leftmost character of the string str is a multi-byte character, returns the code for that character, calculated from the ASCII code values of its constituent characters using this formula: ((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]. If the leftmost character is not a multi-byte character, returns the same value that the ASCII() function does:

mysql> SELECT ORD('2'); -> 50

CONV(N,from_base,to_base)

Converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base. Returns NULL if any argument is NULL. The argument N is interpreted as an integer, but may be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned. CONV works with 64-bit precision:

mysql> SELECT CONV("a",16,2); -> '1010' mysql> SELECT CONV("6E",18,8); -> '172' mysql> SELECT CONV(-17,10,-18); -> '-H' mysql> SELECT CONV(10+"10"+'10'+0xa,10,10); -> '40'

BIN(N)

Returns a string representation of the binary value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,2). Returns NULL if N is NULL:

mysql> SELECT BIN(12); -> '1100'

OCT(N)

Returns a string representation of the octal value of N, where N is a longlong number. This is equivalent to CONV(N,10,8). Returns NULL if N is NULL:

mysql> SELECT OCT(12); -> '14'

HEX(N_or_S)

If N_OR_S is a number, returns a string representation of the hexadecimal value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,16).

If N_OR_S is a string, returns a hexadecimal string of N_OR_S where each character in N_OR_S is converted to 2 hexadecimal digits. This is the invers of the 0xff strings.

mysql> SELECT HEX(255); -> 'FF' mysql> SELECT HEX("abc"); -> 616263 mysql> SELECT 0x616263; -> "abc"

CHAR(N,...)

CHAR() interprets the arguments as integers and returns a string consisting of the characters given by the ASCII code values of those integers. NULL values are skipped:

mysql> SELECT CHAR(77,121,83,81,'76'); -> 'MySQL' mysql> SELECT CHAR(77,77.3,'77.3'); -> 'MMM'

CONCAT(str1,str2,...)

Returns the string that results from concatenating the arguments. Returns NULL if any argument is NULL. May have more than 2 arguments. A numeric argument is converted to the equivalent string form:

mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL' mysql> SELECT CONCAT('My', NULL, 'QL'); -> NULL mysql> SELECT CONCAT(14.3); -> '14.3'

CONCAT_WS(separator, str1, str2,...)

CONCAT_WS() stands for CONCAT With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator can be a string as well as the rest of the arguments. If the separator is NULL, the result will be NULL. The function will skip any NULL values after the separator argument. The separator will be added between the strings to be concatenated:

mysql> SELECT CONCAT_WS(",","First name","Second name","Last Name"); -> 'First name,Second name,Last Name' mysql> SELECT CONCAT_WS(",","First name",NULL,"Last Name"); -> 'First name,Last Name'

Before MySQL 4.1.1, CONCAT_WS() skips empty strings as well as NULL values.

LENGTH(str)

OCTET_LENGTH(str)

CHAR_LENGTH(str)

CHARACTER_LENGTH(str)

Returns the length of the string str:

mysql> SELECT LENGTH('text'); -> 4 mysql> SELECT OCTET_LENGTH('text'); -> 4

LENGTH() and OCTET_LENGTH() are synonyms, and measure string length in bytes (octets). A multi-byte character counts as multiple bytes. CHAR_LENGTH() and CHARACTER_LENGTH() are synonyms, and measure string length in characters. A multiple-byte character counts as a single character. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

BIT_LENGTH(str)

Returns the length of the string str in bits:

mysql> SELECT BIT_LENGTH('text'); -> 32

LOCATE(substr,str)

POSITION(substr IN str)

Returns the position of the first occurrence of substring substr in string str. Returns 0 if substr is not in str:

mysql> SELECT LOCATE('bar', 'foobarbar'); -> 4 mysql> SELECT LOCATE('xbar', 'foobar'); -> 0

This function is multi-byte safe. In MySQL 3.23 this function is case sensitive, while in 4.0 it's only case-sensitive if either argument is a binary string.

LOCATE(substr,str,pos)

Returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str:

mysql> SELECT LOCATE('bar', 'foobarbar',5); -> 7

This function is multi-byte safe. In MySQL 3.23 this function is case sensitive, while in 4.0 it's only case-sensitive if either argument is a binary string.

INSTR(str,substr)

Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the arguments are swapped:

mysql> SELECT INSTR('foobarbar', 'bar'); -> 4 mysql> SELECT INSTR('xbar', 'foobar'); -> 0

This function is multi-byte safe. In MySQL 3.23 this function is case sensitive, while in 4.0 it's only case-sensitive if either argument is a binary string.

LPAD(str,len,padstr)

Returns the string str, left-padded with the string padstr until str is len characters long. If str is longer than len' then it will be shortened to len characters.

mysql> SELECT LPAD('hi',4,'??'); -> '??hi'

RPAD(str,len,padstr)

Returns the string str, right-padded with the string padstr until str is len characters long. If str is longer than len' then it will be shortened to len characters.

mysql> SELECT RPAD('hi',5,'?'); -> 'hi???'

LEFT(str,len)

Returns the leftmost len characters from the string str:

mysql> SELECT LEFT('foobarbar', 5); -> 'fooba'

This function is multi-byte safe.

RIGHT(str,len)

Returns the rightmost len characters from the string str:

mysql> SELECT RIGHT('foobarbar', 4); -> 'rbar'

This function is multi-byte safe.

SUBSTRING(str,pos,len)

SUBSTRING(str FROM pos FOR len)

MID(str,pos,len)

Returns a substring len characters long from string str, starting at position pos. The variant form that uses FROM is SQL-92 syntax:

mysql> SELECT SUBSTRING('Quadratically',5,6); -> 'ratica'

This function is multi-byte safe.

SUBSTRING(str,pos)

SUBSTRING(str FROM pos)

Returns a substring from string str starting at position pos:

mysql> SELECT SUBSTRING('Quadratically',5); -> 'ratically' mysql> SELECT SUBSTRING('foobarbar' FROM 4); -> 'barbar'

This function is multi-byte safe.

SUBSTRING_INDEX(str,delim,count)

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned:

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2); -> 'www.mysql' mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2); -> 'mysql.com'

This function is multi-byte safe.

LTRIM(str)

Returns the string str with leading space characters removed:

mysql> SELECT LTRIM(' barbar'); -> 'barbar'

RTRIM(str)

Returns the string str with trailing space characters removed:

mysql> SELECT RTRIM('barbar '); -> 'barbar'

This function is multi-byte safe.

TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)

Returns the string str with all remstr prefixes and/or suffixes removed. If none of the specifiers BOTH, LEADING or TRAILING are given, BOTH is assumed. If remstr is not specified, spaces are removed:

mysql> SELECT TRIM(' bar '); -> 'bar' mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); -> 'barxxx' mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); -> 'bar' mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); -> 'barx'

This function is multi-byte safe.

SOUNDEX(str)

Returns a soundex string from str. Two strings that sound almost the same should have identical soundex strings. A standard soundex string is 4 characters long, but the SOUNDEX() function returns an arbitrarily long string. You can use SUBSTRING() on the result to get a standard soundex string. All non-alphanumeric characters are ignored in the given string. All international alpha characters outside the A-Z range are treated as vowels:

mysql> SELECT SOUNDEX('Hello'); -> 'H400' mysql> SELECT SOUNDEX('Quadratically'); -> 'Q36324'

SPACE(N)

Returns a string consisting of N space characters:

mysql> SELECT SPACE(6); -> ' '

REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string from_str replaced by the string to_str:

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww'); -> 'WwWwWw.mysql.com'

This function is multi-byte safe.

REPEAT(str,count)

Returns a string consisting of the string str repeated count times. If count <= 0, returns an empty string. Returns NULL if str or count are NULL:

mysql> SELECT REPEAT('MySQL', 3); -> 'MySQLMySQLMySQL'

REVERSE(str)

Returns the string str with the order of the characters reversed:

mysql> SELECT REVERSE('abc'); -> 'cba'

This function is multi-byte safe.

INSERT(str,pos,len,newstr)

Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr:

mysql> SELECT INSERT('Quadratic', 3, 4, 'What'); -> 'QuWhattic'

This function is multi-byte safe.

ELT(N,str1,str2,str3,...)

Returns str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT() is the complement of FIELD():

mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo'); -> 'ej' mysql> SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo'); -> 'foo'

FIELD(str,str1,str2,str3,...)

Returns the index of str in the str1, str2, str3, ... list. Returns 0 if str is not found. FIELD() is the complement of ELT():

mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo'); -> 2 mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo'); -> 0

FIND_IN_SET(str,strlist)

Returns a value 1 to N if the string str is in the list strlist consisting of N substrings. A string list is a string composed of substrings separated by `,' characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimised to use bit arithmetic! Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function will not work properly if the first argument contains a `,':

mysql> SELECT FIND_IN_SET('b','a,b,c,d'); -> 2

MAKE_SET(bits,str1,str2,...)

Returns a set (a string containing substrings separated by `,' characters) consisting of the strings that have the corresponding bit in bits set. str1 corresponds to bit 0, str2 to bit 1, etc. NULL strings in str1, str2, ... are not appended to the result:

mysql> SELECT MAKE_SET(1,'a','b','c'); -> 'a' mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world'); -> 'hello,world' mysql> SELECT MAKE_SET(0,'a','b','c'); -> ''

EXPORT_SET(bits,on,off,[separator,[number_of_bits]])

Returns a string where for every bit set in 'bit', you get an 'on' string and for every reset bit you get an 'off' string. Each string is separated with 'separator' (default ',') and only 'number_of_bits' (default 64) of 'bits' is used:

mysql> SELECT EXPORT_SET(5,'Y','N',',',4) -> Y,N,Y,N

LCASE(str)

LOWER(str)

Returns the string str with all characters changed to lowercase according to the current character set mapping (the default is ISO-8859-1 Latin1):

mysql> SELECT LCASE('QUADRATICALLY'); -> 'quadratically'

This function is multi-byte safe.

UCASE(str)

UPPER(str)

Returns the string str with all characters changed to uppercase according to the current character set mapping (the default is ISO-8859-1 Latin1):

mysql> SELECT UCASE('Hej'); -> 'HEJ'

This function is multi-byte safe.

LOAD_FILE(file_name)

Reads the file and returns the file contents as a string. The file must be on the server, you must specify the full pathname to the file, and you must have the FILE privilege. The file must be readable by all and be smaller than max_allowed_packet.

If the file doesn't exist or can't be read due to one of the above reasons, the function returns NULL:

mysql> UPDATE tbl_name SET blob_column=LOAD_FILE("/tmp/picture") WHERE id=1;

If you are not using MySQL Version 3.23, you have to do the reading of the file inside your application and create an INSERT statement to update the database with the file information. One way to do this, if you are using the MySQL++ library, can be found at http://www.mysql.com/documentation/mysql++/mysql++-examples.html.

QUOTE(str)

Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement. The string is returned surrounded by single quotes and with each instance of single quote (`''), backslash (`\'), ASCII NUL, and Control-Z preceded by a backslash. If the argument is NULL, the return value is the word "NULL" without surrounding single quotes. The QUOTE function was added in MySQL version 4.0.3.

mysql> SELECT QUOTE("Don't"); -> 'Don\'t!' mysql> SELECT QUOTE(NULL); -> NULL

6.3.2.1 String Comparison Functions

MySQL automatically converts numbers to strings as necessary, and vice-versa:

mysql> SELECT 1+"1";
        -> 2
mysql> SELECT CONCAT(2,' test');
        -> '2 test'

If you want to convert a number to a string explicitly, pass it as the argument to CONCAT().

If a string function is given a binary string as an argument, the resulting string is also a binary string. A number converted to a string is treated as a binary string. This only affects comparisons.

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 wildcard characters in the pattern:

Char	Description
%	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 wildcard character, precede the character with the escape character. If you don't specify the ESCAPE character, `\' is assumed:

String	Description
\%	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

The following two statements illustrate that string comparisons are case-insensitive unless one of the operands is a binary string:

mysql> SELECT 'abc' LIKE 'ABC'; -> 1 mysql> SELECT 'abc' LIKE BINARY 'ABC'; -> 0

LIKE is allowed on numeric expressions! (This is a MySQL extension to the SQL-99 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).

Note: Currently LIKE is not multi-byte character safe. Comparison is done character by character.

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

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

expr SOUNDS LIKE expr

Same as SOUNDEX(expr)=SOUNDEX(expr) (available only in version 4.1 or later).

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 MySQL Regular Expressions. 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 (col1,col2,...) AGAINST (expr IN BOOLEAN MODE)

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. MATCH ... AGAINST() is available in MySQL version 3.23.23 or later. IN BOOLEAN MODE extension was added in version 4.0.1. For details and usage examples, see MySQL Full-text Search.

6.3.2.2 Case-Sensitivity

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 string is a shorthand for CAST(string AS BINARY). See section Cast Functions. 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.

6.3.3 Numeric Functions

6.3.3.1 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! If one of the argument is an unsigned integer, and the other argument is also an integer, the result will be an unsigned integer. See section Cast Functions.

+

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!

6.3.3.2 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 mysql> SELECT 29 MOD 9; -> 2

This function is safe to use with BIGINT values. The last example only works in MySQL 4.1

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)

CEIL(X)

Returns the smallest integer value not less than X:

mysql> SELECT CEILING(1.23); -> 2 mysql> SELECT CEIL(-1.23); -> -1

The CEIL() alias was added in version 4.0.6.

Note that the return value is converted to a BIGINT!

ROUND(X)

ROUND(X,D)

Returns the argument X, rounded to the nearest integer. With two arguments rounded to a number to D decimals.

mysql> SELECT ROUND(-1.23); -> -1 mysql> SELECT ROUND(-1.58); -> -2 mysql> SELECT ROUND(1.58); -> 2 mysql> SELECT ROUND(1.298, 1); -> 1.3 mysql> SELECT ROUND(1.298, 0); -> 1 mysql> SELECT ROUND(23.298, -1); -> 20

Note that the behaviour of ROUND() when the argument is half way between two integers depends on the C library implementation. Some round to the nearest even number, always up, always down, or always toward zero. If you need one kind of rounding, you should use a well-defined function like TRUNCATE() or FLOOR() instead.

DIV

Integer division. Similar to FLOOR() but safe with BIGINT values.

mysql> SELECT 5 DIV 2 -> 2

DIV is new in MySQL 4.1.0.

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

LN(X)

Returns the natural logarithm of X:

mysql> SELECT LN(2); -> 0.693147 mysql> SELECT LN(-2); -> NULL

This function was added in MySQL version 4.0.3. It is synonymous with LOG(X) in MySQL.

LOG(X)

LOG(B,X)

If called with one parameter, this function returns the natural logarithm of X:

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

If called with two parameters, this function returns the logarithm of X for an arbitary base B:

mysql> SELECT LOG(2,65536); -> 16.000000 mysql> SELECT LOG(1,100); -> NULL

The arbitrary base option was added in MySQL version 4.0.3. LOG(B,X) is equivalent to LOG(X)/LOG(B).

LOG2(X)

Returns the base-2 logarithm of X:

mysql> SELECT LOG2(65536); -> 16.000000 mysql> SELECT LOG2(-100); -> NULL

LOG2() is useful for finding out how many bits a number would require for storage. This function was added in MySQL version 4.0.3. In earlier versions, you can use LOG(X)/LOG(2) instead.

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. The default shown number of decimals is 5, but MySQL internally uses the full double precession for PI.

mysql> SELECT PI(); -> 3.141593 mysql> SELECT PI()+0.000000000000000000; -> 3.141592653589793116

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

ATAN(Y,X)

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 ATAN2(PI(),0); -> 1.570796

COT(X)

Returns the cotangent of X:

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

CRC32(expr)

Computes a cyclic redundancy check value and returns a 32-bit unsigned value. The result is NULL if the argument is NULL. The argument is expected be a string and will be treated as one if it is not.

mysql> SELECT CRC32('MySQL'); -> 3259397556

CRC32() is available as of MySQL 4.1.0.

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 (producing a repeatable sequence):

mysql> SELECT RAND(); -> 0.9233482386203 mysql> SELECT RAND(20); -> 0.15888261251047 mysql> SELECT RAND(20); -> 0.15888261251047 mysql> SELECT RAND(); -> 0.63553050033332 mysql> SELECT RAND(); -> 0.70100469486881

You can't use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. From version 3.23 you can 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.

RAND() is not meant to be a perfect random generator, but instead a fast way to generate ad hoc random numbers that will be portable between platforms for the same MySQL version.

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 mysql> SELECT TRUNCATE(-1.999,1); -> -1.9

Starting from MySQL 3.23.51, all numbers are rounded toward zero.

If D is negative, then the whole part of the number is zeroed out:

mysql> SELECT TRUNCATE(122,-2); -> 100

Note that as decimal numbers are normally not stored as exact numbers in computers, but as double-precision values, you may be fooled by the following result:

mysql> SELECT TRUNCATE(10.28*100,0); -> 1027

The above happens because 10.28 is actually stored as something like 10.2799999999999999.

6.3.4 Date and Time Functions

This section describes the functions that can be used to manipulate temporal values. See Date and Time Types for a description of the range of values each date and time type has and the valid formats in which values may be specified.

Here is an example that uses date functions. The following query selects all records with a date_col value from within the last 30 days:

mysql> SELECT something FROM tbl_name
           WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;

(Note that the query will also select records with dates that lie in the future.)

Functions that expect date values usually will accept datetime values and ignore the time part. Functions that expect time values usually will accept datetime values and ignore the date part.

Functions that return the current date or time each are evaluated only once per query at the start of query execution. This means that multiple references to a function such as NOW() within a single query will always produce the same result. This principle also applies to CURDATE(), CURTIME(), UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP(), and any of their synonyms.

The return value ranges in the following function descriptions apply for complete dates. If a date is a "zero" value or an incomplete date such as '2001-11-00', functions that extract a part of a date may return 0. For example, DAYOFMONTH('2001-11-00') returns 0.

DATE(expr)

Extracts the date part of the date or datetime expression expr.

mysql> SELECT DATE('2003-12-31 01:02:03'); -> '2003-12-31'

DATE() is available as of MySQL 4.1.1.

TIME(expr)

Extracts the time part of the time or datetime expression expr.

mysql> SELECT TIME('2003-12-31 01:02:03'); -> '01:02:03' mysql> SELECT TIME('2003-12-31 01:02:03.000123'); -> '01:02:03.000123'

TIME() is available as of MySQL 4.1.1.

TIMESTAMP(expr)

TIMESTAMP(expr,expr2)

With one argument, returns the date or datetime expression expr as a datetime value. With two arguments, adds the time expression expr2 to the date or datetime expression expr and returns a datetime value.

mysql> SELECT TIMESTAMP('2003-12-31'); -> '2003-12-31 00:00:00' mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00'); -> '2004-01-01 00:00:00'

TIMESTAMP() is available as of MySQL 4.1.1.

DAYOFWEEK(date)

Returns the weekday index for date (1 = Sunday, 2 = Monday, ... 7 = Saturday). These index values correspond to the ODBC standard.

mysql> SELECT DAYOFWEEK('1998-02-03'); -> 3

WEEKDAY(date)

Returns the weekday index for date (0 = Monday, 1 = Tuesday, ... 6 = Sunday):

mysql> SELECT WEEKDAY('1998-02-03 22:23:00'); -> 1 mysql> SELECT WEEKDAY('1997-11-05'); -> 2

DAYOFMONTH(date)

Returns the day of the month for date, in the range 1 to 31:

mysql> SELECT DAYOFMONTH('1998-02-03'); -> 3

DAY(date)

DAY() is a synonym for DAYOFMONTH(). It is available as of MySQL 4.1.1.

DAYOFYEAR(date)

Returns the day of the year for date, in the range 1 to 366:

mysql> SELECT DAYOFYEAR('1998-02-03'); -> 34

MONTH(date)

Returns the month for date, in the range 1 to 12:

mysql> SELECT MONTH('1998-02-03'); -> 2

DAYNAME(date)

Returns the name of the weekday for date:

mysql> SELECT DAYNAME('1998-02-05'); -> 'Thursday'

MONTHNAME(date)

Returns the name of the month for date:

mysql> SELECT MONTHNAME('1998-02-05'); -> 'February'

QUARTER(date)

Returns the quarter of the year for date, in the range 1 to 4:

mysql> SELECT QUARTER('98-04-01'); -> 2

WEEK(date)

WEEK(date,start)

With a single argument, returns the week for date, in the range 0 to 53 (yes, there may be the beginnings of a week 53), for locations where Sunday is the first day of the week. The two-argument form of WEEK() allows you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range 0-53 or 1-52.

The following table demonstrates how the start argument works:

Value	Meaning
0	Week starts on Sunday; return value range is 0 to 53
1	Week starts on Monday; return value range is 0 to 53
2	Week starts on Sunday; return value range is 1 to 53
3	Week starts on Monday; return value range is 1 to 53 (ISO 8601)

The start value of 3 can be used as of MySQL 4.0.5.

mysql> SELECT WEEK('1998-02-20'); -> 7 mysql> SELECT WEEK('1998-02-20',0); -> 7 mysql> SELECT WEEK('1998-02-20',1); -> 8 mysql> SELECT WEEK('1998-12-31',1); -> 53

For MySQL 3.23 and 4.0, the default value for the start argument is 0. In MySQL 4.1, you can control the default value of the start argument by using the default_week_format variable. The syntax for setting default_week_format is:

SET [SESSION | GLOBAL] default_week_format = {0|1|2|3};

Note: In Version 4.0, WEEK(date,0) was changed to match the calendar in the USA. Before that, WEEK() was calculated incorrectly for dates in USA. (In effect, WEEK(date) and WEEK(date,0) was incorrect for all cases.)

Note that if a date falls in the last week of the previous year, MySQL will return 0 if you don't use 2 or 3 as the optional start argument:

mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0); -> 2000, 0

One might argue that MySQL should return 52 for the WEEK() function, because the given date actually occurs in the 52nd week of 1999. We decided to return 0 instead as we want the function to return "the week number in the given year." This makes the usage of the WEEK() function reliable when combined with other functions that extract a date part from a date.

If you would prefer the result to be evaluated with respect to the year that contains the first day of the week for the given date, you should use 2 or 3 as the optional start argument.

mysql> SELECT WEEK('2000-01-01',2); -> 52

Alternatively, use the YEARWEEK() function:

mysql> SELECT YEARWEEK('2000-01-01'); -> 199952 mysql> SELECT MID(YEARWEEK('2000-01-01'),5,2); -> '52'

WEEKOFYEAR(date)

Returns the calendar week of the date as a number in the range from 1 to 53.

mysql> SELECT WEEKOFYEAR('1998-02-20'); -> 8

WEEKOFYEAR() is available as of MySQL 4.1.1.

YEAR(date)

Returns the year for date, in the range 1000 to 9999:

mysql> SELECT YEAR('98-02-03'); -> 1998

YEARWEEK(date)

YEARWEEK(date,start)

Returns year and week for a date. The start argument works exactly like the start argument to WEEK(). Note that the year in the result may be different from the year in the date argument for the first and the last week of the year:

mysql> SELECT YEARWEEK('1987-01-01'); -> 198653

Note that the week number is different from what the WEEK() function would return (0) for optional arguments 0 or 1, as WEEK() then returns the week in the context of the given year.

HOUR(time)

Returns the hour for time. The range of the return value will be 0 to 23 for time-of-day values:

mysql> SELECT HOUR('10:05:03'); -> 10

However, the range of TIME values actually is much larger, so HOUR can return values greater than 23:

mysql> SELECT HOUR('272:59:59'); -> 272

MINUTE(time)

Returns the minute for time, in the range 0 to 59:

mysql> SELECT MINUTE('98-02-03 10:05:03'); -> 5

SECOND(time)

Returns the second for time, in the range 0 to 59:

mysql> SELECT SECOND('10:05:03'); -> 3

MICROSECOND(expr)

Returns the microseconds from the time or datetime expression expr as a number in the range from 0 to 999999.

mysql> SELECT MICROSECOND('12:00:00.123456'); -> 123456 mysql> SELECT MICROSECOND('1997-12-31 23:59:59.000010'); -> 10

MICROSECOND() is available as of MySQL 4.1.1.

PERIOD_ADD(P,N)

Adds N months to period P (in the format YYMM or YYYYMM). Returns a value in the format YYYYMM.

Note that the period argument P is not a date value:

mysql> SELECT PERIOD_ADD(9801,2); -> 199803

PERIOD_DIFF(P1,P2)

Returns the number of months between periods P1 and P2. P1 and P2 should be in the format YYMM or YYYYMM.

Note that the period arguments P1 and P2 are not date values:

mysql> SELECT PERIOD_DIFF(9802,199703); -> 11

DATE_ADD(date,INTERVAL expr type)

DATE_SUB(date,INTERVAL expr type)

These functions perform date arithmetic.

As of MySQL Version 3.23, INTERVAL expr type is allowed on either side of the + operator if the expression on the other side is a date or datetime value. For the - operator, INTERVAL expr type is allowed only on the right side, because it makes no sense to subtract a date or datetime value from an interval. (See examples below.)

date is a DATETIME or DATE value specifying the starting date. expr is an expression specifying the interval value to be added or subtracted from the starting date. expr is a string; it may start with a `-' for negative intervals. type is a keyword indicating how the expression should be interpreted.

The following table shows how the type and expr arguments are related:

type Value	Expected expr Format
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
MONTH	MONTHS
YEAR	YEARS
MINUTE_SECOND	'MINUTES:SECONDS'
HOUR_MINUTE	'HOURS:MINUTES'
DAY_HOUR	'DAYS HOURS'
YEAR_MONTH	'YEARS-MONTHS'
HOUR_SECOND	'HOURS:MINUTES:SECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
DAY_MICROSECOND	'DAYS.MICROSECONDS'
HOUR_MICROSECOND	'HOURS.MICROSECONDS'
MINUTE_MICROSECOND	'MINUTES.MICROSECONDS'
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'
MICROSECOND	'MICROSECONDS'

The type values DAY_MICROSECOND, HOUR_MICROSECOND, MINUTE_MICROSECOND, SECOND_MICROSECOND, and MICROSECOND are allowed as of MySQL 4.1.1.

MySQL allows any punctuation delimiter in the expr format. Those shown in the table are the suggested delimiters. If the date argument is a DATE value and your calculations involve only YEAR, MONTH, and DAY parts (that is, no time parts), the result is a DATE value. Otherwise, the result is a DATETIME value:

mysql> SELECT '1997-12-31 23:59:59' + INTERVAL 1 SECOND; -> '1998-01-01 00:00:00' mysql> SELECT INTERVAL 1 DAY + '1997-12-31'; -> '1998-01-01' mysql> SELECT '1998-01-01' - INTERVAL 1 SECOND; -> '1997-12-31 23:59:59' mysql> SELECT DATE_ADD('1997-12-31 23:59:59', -> INTERVAL 1 SECOND); -> '1998-01-01 00:00:00' mysql> SELECT DATE_ADD('1997-12-31 23:59:59', -> INTERVAL 1 DAY); -> '1998-01-01 23:59:59' mysql> SELECT DATE_ADD('1997-12-31 23:59:59', -> INTERVAL '1:1' MINUTE_SECOND); -> '1998-01-01 00:01:00' mysql> SELECT DATE_SUB('1998-01-01 00:00:00', -> INTERVAL '1 1:1:1' DAY_SECOND); -> '1997-12-30 22:58:59' mysql> SELECT DATE_ADD('1998-01-01 00:00:00', -> INTERVAL '-1 10' DAY_HOUR); -> '1997-12-30 14:00:00' mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY); -> '1997-12-02' mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002', -> INTERVAL '1.999999' SECOND_MICROSECOND); -> '1993-01-01 00:00:01.000001'

If you specify an interval value that is too short (does not include all the interval parts that would be expected from the type keyword), MySQL assumes you have left out the leftmost parts of the interval value. For example, if you specify a type of DAY_SECOND, the value of expr is expected to have days, hours, minutes, and seconds parts. If you specify a value like '1:10', MySQL assumes that the days and hours parts are missing and the value represents minutes and seconds. In other words, '1:10' DAY_SECOND is interpreted in such a way that it is equivalent to '1:10' MINUTE_SECOND. This is analogous to the way that MySQL interprets TIME values as representing elapsed time rather than as time of day.

Note that if you add to or subtract from a date value something that contains a time part, the result is automatically converted to a datetime value:

mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 DAY); -> '1999-01-02' mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 HOUR); -> '1999-01-01 01:00:00'

If you use really malformed dates, the result is NULL. If you add MONTH, YEAR_MONTH, or YEAR and the resulting date has a day that is larger than the maximum day for the new month, the day is adjusted to the maximum days in the new month:

mysql> SELECT DATE_ADD('1998-01-30', interval 1 month); -> '1998-02-28'

Note from the preceding example that the keyword INTERVAL and the type specifier are not case-sensitive.

ADDDATE(date,INTERVAL expr type)

SUBDATE(date,INTERVAL expr type)

ADDDATE(expr,days)

SUBDATE(expr,days)

When invoked with the INTERVAL form of the second argument, ADDDATE() and SUBDATE() are synonyms for DATE_ADD() and DATE_SUB().

mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY); -> '1998-02-02' mysql> SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY); -> '1998-02-02' mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY); -> '1997-12-02' mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY); -> '1997-12-02'

As of MySQL 4.1.1, the second syntax is allowed, where expr is a date or datetime expression and days is the number of days to be added to or subtracted from expr.

mysql> SELECT ADDDATE('1998-01-02', 31); -> '1998-02-02' mysql> SELECT SUBDATE('1998-01-02 12:00:00', 31); -> '1997-12-02 12:00:00'

ADDTIME(expr,expr2)

SUBTIME(expr,expr2)

expr is a date or datetime expression, and expr2 is a time expression.

[Rest of description to be added here]

NEED EXAMPLE

ADDTIME() and SUBTIME() were added in MySQL 4.1.1.

EXTRACT(type FROM date)

The EXTRACT() function uses the same kinds of interval type specifiers as DATE_ADD() or DATE_SUB(), but extracts parts from the date rather than performing date arithmetic.

mysql> SELECT EXTRACT(YEAR FROM "1999-07-02"); -> 1999 mysql> SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03"); -> 199907 mysql> SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03"); -> 20102 mysql> SELECT EXTRACT(MICROSECOND FROM "2003-01-02 10:30:00.00123"); -> 123

DATEDIFF(ARGUMENTS)

TIMEDIFF(ARGUMENTS)

[Rest of description to be added here]

NEED EXAMPLE

DATEDIFF() and TIMEDIFF() were added in MySQL 4.1.1.

TO_DAYS(date)

Given a date date, returns a daynumber (the number of days since year 0):

mysql> SELECT TO_DAYS(950501); -> 728779 mysql> SELECT TO_DAYS('1997-10-07'); -> 729669

TO_DAYS() is not intended for use with values that precede the advent of the Gregorian calendar (1582), because it doesn't take into account the days that were lost when the calendar was changed.

FROM_DAYS(N)

Given a daynumber N, returns a DATE value:

mysql> SELECT FROM_DAYS(729669); -> '1997-10-07'

FROM_DAYS() is not intended for use with values that precede the advent of the Gregorian calendar (1582), because it doesn't take into account the days that were lost when the calendar was changed.

DATE_FORMAT(date,format)

Formats the date value according to the format string. The following specifiers may be used in the format string:

Specifier	Description
%M	Month name (January..December)
%W	Weekday name (Sunday..Saturday)
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, etc.)
%Y	Year, numeric, 4 digits
%y	Year, numeric, 2 digits
%X	Year for the week where Sunday is the first day of the week, numeric, 4 digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, 4 digits; used with %v
%a	Abbreviated weekday name (Sun..Sat)
%d	Day of the month, numeric (00..31)
%e	Day of the month, numeric (0..31)
%m	Month, numeric (00..12)
%c	Month, numeric (0..12)
%b	Abbreviated month name (Jan..Dec)
%j	Day of year (001..366)
%H	Hour (00..23)
%k	Hour (0..23)
%h	Hour (01..12)
%I	Hour (01..12)
%l	Hour (1..12)
%i	Minutes, numeric (00..59)
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%T	Time, 24-hour (hh:mm:ss)
%S	Seconds (00..59)
%s	Seconds (00..59)
%f	Microseconds (000000..999999)
%p	AM or PM
%w	Day of the week (0=Sunday..6=Saturday)
%U	Week (00..53), where Sunday is the first day of the week
%u	Week (00..53), where Monday is the first day of the week
%V	Week (01..53), where Sunday is the first day of the week; used with %X
%v	Week (01..53), where Monday is the first day of the week; used with %x
%%	A literal `%'.

All other characters are just copied to the result without interpretation.

The %f format specifier is available as of MySQL 4.1.1.

As of MySQL Version 3.23, the `%' character is required before format specifier characters. In earlier versions of MySQL, `%' was optional.

The reason the ranges for the month and day specifiers begin with zero is that MySQL allows incomplete dates such as '2004-00-00' to be stored as of MySQL 3.23.

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y'); -> 'Saturday October 1997' mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s'); -> '22:23:00' mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%D %y %a %d %m %b %j'); -> '4th 97 Sat 04 10 Oct 277' mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w'); -> '22 22 10 10:23:00 PM 22:23:00 00 6' mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V'); -> '1998 52'

TIME_FORMAT(time,format)

This is used like the DATE_FORMAT() function above, but the format string may contain only those format specifiers that handle hours, minutes, and seconds. Other specifiers produce a NULL value or 0.

If the time value contains an hour part that is greater than 23, the %H and %k hour format specifiers produce a value larger than the usual range of 0..23. The other hour format specifiers produce the hour value modulo 12:

mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l'); -> '100 100 04 04 4'

MAKEDATE(year,dayofyear)

Returns a date, given year and day-of-year values. dayofyear must be greater than 0 or the result will NULL.

mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32); -> '2001-01-31', '2001-02-01' mysql> SELECT MAKEDATE(2001,365), MAKEDATE(2004,365); -> '2001-12-31', '2004-12-30' mysql> SELECT MAKEDATE(2001,0); -> NULL

MAKEDATE() is available as of MySQL 4.1.1.

MAKETIME(hour,minute,second)

Returns a time value calculated from the hour, minute, and second arguments.

mysql> SELECT MAKETIME(12,15,30); -> '12:15:30'

MAKETIME() is available as of MySQL 4.1.1.

CURDATE()

CURRENT_DATE

Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context:

mysql> SELECT CURDATE(); -> '1997-12-15' mysql> SELECT CURDATE() + 0; -> 19971215

CURTIME()

CURRENT_TIME

Returns the current time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context:

mysql> SELECT CURTIME(); -> '23:50:26' mysql> SELECT CURTIME() + 0; -> 235026

NOW()

SYSDATE()

CURRENT_TIMESTAMP

Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context:

mysql> SELECT NOW(); -> '1997-12-15 23:50:26' mysql> SELECT NOW() + 0; -> 19971215235026

UNIX_TIMESTAMP()

UNIX_TIMESTAMP(date)

If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' GMT) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' GMT. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD in local time:

mysql> SELECT UNIX_TIMESTAMP(); -> 882226357 mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00'); -> 875996580

When UNIX_TIMESTAMP is used on a TIMESTAMP column, the function returns the internal timestamp value directly, with no implicit "string-to-Unix-timestamp" conversion. If you pass an out-of-range date to UNIX_TIMESTAMP() it returns 0, but please note that only basic checking is performed (year 1970-2037, month 01-12, day 01-31).

If you want to subtract UNIX_TIMESTAMP() columns, you may want to cast the result to signed integers. See section Cast Functions.

FROM_UNIXTIME(unix_timestamp)

FROM_UNIXTIME(unix_timestamp,format)

Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context:

mysql> SELECT FROM_UNIXTIME(875996580); -> '1997-10-04 22:23:00' mysql> SELECT FROM_UNIXTIME(875996580) + 0; -> 19971004222300

If format is given, the result is formatted according to the format string. format may contain the same specifiers as those listed in the entry for the DATE_FORMAT() function:

mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), -> '%Y %D %M %h:%i:%s %x'); -> '2003 6th August 06:22:58 2003'

SEC_TO_TIME(seconds)

Returns the seconds argument, converted to hours, minutes, and seconds, as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context:

mysql> SELECT SEC_TO_TIME(2378); -> '00:39:38' mysql> SELECT SEC_TO_TIME(2378) + 0; -> 3938

TIME_TO_SEC(time)

Returns the time argument, converted to seconds:

mysql> SELECT TIME_TO_SEC('22:23:00'); -> 80580 mysql> SELECT TIME_TO_SEC('00:39:38'); -> 2378

UTC_DATE()

Returns the current UTC date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context:

mysql> SELECT UTC_DATE(), UTC_DATE() + 0; -> '2003-08-14', 20030814

UTC_DATE() is available as of MySQL 4.1.1.

UTC_TIME()

Returns the current UTC time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context:

mysql> SELECT UTC_TIME(), UTC_TIME() + 0; -> '18:07:53', 180753

UTC_TIME() is available as of MySQL 4.1.1.

UTC_TIMESTAMP()

Returns the current UTC date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context:

mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0; -> '2003-08-14 18:08:04', 20030814180804

UTC_TIMESTAMP() is available as of MySQL 4.1.1.

6.3.5 Cast Functions

The CAST() and CONVERT() functions may be used to take a value of one type and produce a value of another type. Their syntax is:

CAST(expression AS type)
CONVERT(expression,type)
CONVERT(expr USING transcoding_name)

The type value can be one of the following:

• BINARY
• CHAR
• DATE
• DATETIME
• SIGNED {INTEGER}
• TIME
• UNSIGNED {INTEGER}

CAST() and CONVERT() are available as of MySQL 4.0.2. The CHAR conversion type is available as of 4.0.6. The USING form of CONVERT() is available as of 4.1.0.

CAST() and CONVERT(... USING ...) are SQL-99 syntax. The non-USING form of CONVERT() is ODBC syntax.

The cast functions are useful when you want to create a column with a specific type in a CREATE ... SELECT statement:

CREATE TABLE new_table SELECT CAST('2000-01-01' AS DATE);

The functions also can be useful for sorting ENUM columns in lexical order. Normally sorting of ENUM columns occurs using the internal numeric values. Casting the values to CHAR results in a lexical sort:

SELECT enum_col FROM tbl_name ORDER BY CAST(enum_col AS CHAR);

CAST(string AS BINARY) is the same thing as BINARY string. CAST(expr AS CHAR) treats the expression as a string with the default character set.

NOTE: In MysQL 4.0 the CAST() to DATE, DATETIME, or TIME only marks the column to be a specific type but doesn't change the value of the column.

In MySQL 4.1.0 the value is converted to the correct column type when it's sent to the user (this is a feature of how the new protocol in 4.1 sends date information to the client):

mysql> SELECT CAST(NOW() AS DATE);
       -> 2003-05-26

In later MySQL versions (probably 4.1.2 or 5.0) we will fix that CAST also changes the result if you use it as part of a more complex expression, like CONCAT("Date: ",CAST(NOW() AS DATE)).

You should not use CAST() to extract data in different formats but instead use string functions like LEFT or EXTRACT(). See section Date and Time Functions.

To cast a string to a numeric value, you don't normally have to do anything; just use the string value as it would be a number:

mysql> SELECT 1+'1';
       -> 2

If you use a number in string context, the number will automatically be converted to a BINARY string.

mysql> SELECT CONCAT("hello you ",2);
       ->  "hello you 2"

MySQL supports arithmetic with both signed and unsigned 64-bit values. If you are using numerical operations (like +) and one of the operands is unsigned integer, the result will be unsigned. You can override this by using the SIGNED and UNSIGNED cast operators to cast the operation to a signed or unsigned 64-bit integer, respectively.

mysql> SELECT CAST(1-2 AS UNSIGNED)
        -> 18446744073709551615
mysql> SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED);
        -> -1

Note that if either operand is a floating-point value, the result is a floating-point value and is not affected by the above rule. (In this context, DECIMAL values are regarded as floating-point values.)

mysql> SELECT CAST(1 AS UNSIGNED) - 2.0;
        -> -1.0

If you are using a string in an arithmetic operation, this is converted to a floating-point number.

The handing of unsigned values was changed in MySQL 4.0 to be able to support BIGINT values properly. If you have some code that you want to run in both MySQL 4.0 and 3.23 (in which case you probably can't use the CAST() function), you can use the following technique to get a signed result when subtracting two unsigned integer columns:

SELECT (unsigned_column_1+0.0)-(unsigned_column_2+0.0);

The idea is that the columns are converted to floating-point values before the subtraction occurs.

If you get a problem with UNSIGNED columns in your old MySQL application when porting to MySQL 4.0, you can use the --sql-mode=NO_UNSIGNED_SUBTRACTION option when starting mysqld. Note however that as long as you use this, you will not be able to make efficient use of the BIGINT UNSIGNED column type.

CONVERT() with USING is used to convert data between different character sets. In MySQL, transcoding names are the same as the corresponding character set names. For example, this statement converts the string 'abc' in the server's default character set to the corresponding string in the utf8 character set:

SELECT CONVERT('abc' USING utf8);

6.3.6 Other Functions

6.3.6.1 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

The result is an unsigned 64-bit integer.

&

Bitwise AND

mysql> SELECT 29 & 15; -> 13

The result is an unsigned 64-bit integer.

^

Bitwise XOR

mysql> SELECT 1 ^ 1; -> 0 mysql> SELECT 1 ^ 0; -> 1 mysql> SELECT 11 ^ 3; -> 8

The result is an unsigned 64-bit integer.

XOR was added in version 4.0.2.

<<

Shifts a longlong (BIGINT) number to the left:

mysql> SELECT 1 << 2; -> 4

The result is an unsigned 64-bit integer.

>>

Shifts a longlong (BIGINT) number to the right:

mysql> SELECT 4 >> 2; -> 1

The result is an unsigned 64-bit integer.

~

Invert all bits:

mysql> SELECT 5 & ~1; -> 4

The result is an unsigned 64-bit integer.

BIT_COUNT(N)

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

mysql> SELECT BIT_COUNT(29); -> 4