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

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

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 9.1.2 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 9.5.2 The DBI Interface.

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.

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 A.5.3 Problems with NULL Values.

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

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

Identifier Max length Allowed characters
Database 64 Any character that is allowed in a directory name except `/', `\' or `.'.
Table 64 Any character that is allowed in a file name, except `/' or `.'.
Column 64 All characters.
Alias 255 All characters.

Note that in addition to the above, you can't have ASCII(0) or ASCII(255) 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 6.1.7 Is MySQL Picky About Reserved Words?.

In MySQL versions prior to 3.23.6, the name rules are as follows:

It is recommended that you do not use names like 1e, because an expression like 1e+1 is ambiguous. It may be interpreted as the expression 1e + 1 or as the number 1e+1.

In MySQL you can refer to a column using any of the following forms:

Column reference Meaning
col_name Column col_name from whichever table used in the query contains a column of that name.
tbl_name.col_name Column col_name from table tbl_name of the current database.
db_name.tbl_name.col_name Column col_name from table tbl_name of the database db_name. This form is available in MySQL Version 3.22 or later.
`column_name` A column that is a keyword or contains special characters.

You need not specify a tbl_name or db_name.tbl_name prefix for a column reference in a statement unless the reference would be ambiguous. For example, suppose tables t1 and t2 each contain a column c, and you retrieve c in a SELECT statement that uses both t1 and t2. In this case, c is ambiguous because it is not unique among the tables used in the statement, so you must indicate which table you mean by writing t1.c or t2.c. Similarly, if you are retrieving from a table t in database db1 and from a table t in database db2, you must refer to columns in those tables as db1.t.col_name and db2.t.col_name.

The syntax .tbl_name means the table tbl_name in the current database. This syntax is accepted for ODBC compatibility, because some ODBC programs prefix table names with a `.' character.

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 1.8.3 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 4.6.1 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 5.5.6 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.

Variable name Value type Type
autocommit bool SESSION
big_tables bool SESSION
binlog_cache_size num GLOBAL
bulk_insert_buffer_size num GLOBAL | SESSION
concurrent_insert bool GLOBAL
connect_timeout num GLOBAL
convert_character_set string SESSION
delay_key_write OFF | ON | ALL GLOBAL
delayed_insert_limit num GLOBAL
delayed_insert_timeout num GLOBAL
delayed_queue_size num GLOBAL
error_count num LOCAL
flush bool GLOBAL
flush_time num GLOBAL
foreign_key_checks bool SESSION
identity num SESSION
insert_id bool SESSION
interactive_timeout num GLOBAL | SESSION
join_buffer_size num GLOBAL | SESSION
key_buffer_size num GLOBAL
last_insert_id bool SESSION
local_infile bool GLOBAL
log_warnings bool GLOBAL
long_query_time num GLOBAL | SESSION
low_priority_updates bool GLOBAL | SESSION
max_allowed_packet num GLOBAL | SESSION
max_binlog_cache_size num GLOBAL
max_binlog_size num GLOBAL
max_connect_errors num GLOBAL
max_connections num GLOBAL
max_error_count num GLOBAL | SESSION
max_delayed_threads num GLOBAL
max_heap_table_size num GLOBAL | SESSION
max_join_size num GLOBAL | SESSION
max_sort_length num GLOBAL | SESSION
max_tmp_tables num GLOBAL
max_user_connections num GLOBAL
max_write_lock_count num GLOBAL
myisam_max_extra_sort_file_size num GLOBAL | SESSION
myisam_repair_threads num GLOBAL | SESSION
myisam_max_sort_file_size num GLOBAL | SESSION
myisam_sort_buffer_size num GLOBAL | SESSION
net_buffer_length num GLOBAL | SESSION
net_read_timeout num GLOBAL | SESSION
net_retry_count num GLOBAL | SESSION
net_write_timeout num GLOBAL | SESSION
query_cache_limit num GLOBAL
query_cache_size num GLOBAL
query_cache_type enum GLOBAL
read_buffer_size num GLOBAL | SESSION
read_rnd_buffer_size num GLOBAL | SESSION
rpl_recovery_rank num GLOBAL
safe_show_database bool GLOBAL
server_id num GLOBAL
slave_compressed_protocol bool GLOBAL
slave_net_timeout num GLOBAL
slow_launch_time num GLOBAL
sort_buffer_size num GLOBAL | SESSION
sql_auto_is_null bool SESSION
sql_big_selects bool SESSION
sql_big_tables bool SESSION
sql_buffer_result bool SESSION
sql_log_binlog bool SESSION
sql_log_off bool SESSION
sql_log_update bool SESSION
sql_low_priority_updates bool GLOBAL | SESSION
sql_max_join_size num GLOBAL | SESSION
sql_quote_show_create bool SESSION
sql_safe_updates bool SESSION
sql_select_limit bool SESSION
sql_slave_skip_counter num GLOBAL
sql_warnings bool SESSION
table_cache num GLOBAL
table_type enum GLOBAL | SESSION
thread_cache_size num GLOBAL
timestamp bool SESSION
tmp_table_size enum GLOBAL | SESSION
tx_isolation enum GLOBAL | SESSION
version string GLOBAL
wait_timeout num GLOBAL | SESSION
warning_count num LOCAL
unique_checks bool SESSION

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:

Variable Description
identity Alias for last_insert_id (Sybase compatiblity)
sql_low_priority_updates Alias for low_priority_updates
sql_max_join_size Alias for max_join_size
delay_key_write_for_all_tables If this and delay_key_write are set, then all new MyISAM tables that are opened will use delayed key writes.
version Alias for VERSION() (Sybase (?) compatability)

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 4.1.1 mysqld Command-line Options. See section 4.5.7.4 SHOW VARIABLES. See section 5.5.6 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:

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 1.8.4.7 `--' 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 section 6.1.2 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:

Word Word Word
ADD ALL ALTER
ANALYZE AND AS
ASC ASENSITIVE AUTO_INCREMENT
BDB BEFORE BERKELEYDB
BETWEEN BIGINT BINARY
BLOB BOTH BTREE
BY CALL CASCADE
CASE CHANGE CHAR
CHARACTER CHECK COLLATE
COLUMN COLUMNS CONNECTION
CONSTRAINT CREATE CROSS
CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP
CURSOR DATABASE DATABASES
DAY_HOUR DAY_MINUTE DAY_SECOND
DEC DECIMAL DECLARE
DEFAULT DELAYED DELETE
DESC DESCRIBE DISTINCT
DISTINCTROW DIV DOUBLE
DROP ELSE ELSEIF
ENCLOSED ERRORS ESCAPED
EXISTS EXPLAIN FALSE
FIELDS FLOAT FOR
FORCE FOREIGN FROM
FULLTEXT GRANT GROUP
HASH HAVING HIGH_PRIORITY
HOUR_MINUTE HOUR_SECOND IF
IGNORE IN INDEX
INFILE INNER INNODB
INOUT INSENSITIVE INSERT
INT INTEGER INTERVAL
INTO IS ITERATE
JOIN KEY KEYS
KILL LEADING LEAVE
LEFT LIKE LIMIT
LINES LOAD LOCALTIME
LOCALTIMESTAMP LOCK LONG
LONGBLOB LONGTEXT LOOP
LOW_PRIORITY MASTER_SERVER_ID MATCH
MEDIUMBLOB MEDIUMINT MEDIUMTEXT
MIDDLEINT MINUTE_SECOND MOD
MRG_MYISAM NATURAL NOT
NULL NUMERIC ON
OPTIMIZE OPTION OPTIONALLY
OR ORDER OUT
OUTER OUTFILE PRECISION
PRIMARY PRIVILEGES PROCEDURE
PURGE READ REAL
REFERENCES REGEXP RENAME
REPEAT REPLACE REQUIRE
RESTRICT RETURN RETURNS
REVOKE RIGHT RLIKE
RTREE SELECT SENSITIVE
SEPARATOR SET SHOW
SMALLINT SOME SONAME
SPATIAL SPECIFIC SQL_BIG_RESULT
SQL_CALC_FOUND_ROWS SQL_SMALL_RESULT SSL
STARTING STRAIGHT_JOIN STRIPED
TABLE TABLES TERMINATED
THEN TINYBLOB TINYINT
TINYTEXT TO TRAILING
TRUE TYPES UNION
UNIQUE UNLOCK UNSIGNED
UNTIL UPDATE USAGE
USE USER_RESOURCES USING
VALUES VARBINARY VARCHAR
VARCHARACTER VARYING WARNINGS
WHEN WHERE WHILE
WITH WRITE XOR
YEAR_MONTH ZEROFILL

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.

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 6.3.5 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
These are synonyms for TINYINT(1).
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:
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 A.5.6 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]
These are synonyms for DECIMAL.
DATE
A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but allows you to assign values to DATE columns using either strings or numbers. See section 6.2.2.2 The DATETIME, DATE, and TIMESTAMP Types.
DATETIME
A date and time combination. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format, but allows you to assign values to DATETIME columns using either strings or numbers. See section 6.2.2.2 The DATETIME, DATE, and TIMESTAMP Types.
TIMESTAMP[(M)]
A timestamp. The range is '1970-01-01 00:00:00' to sometime in the year 2037. 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 string with the format 'YYYY-MM-DD HH:MM:DD'. 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 6.2.2 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 6.2.2.2 The DATETIME, DATE, and TIMESTAMP Types.
TIME
A time. The range is '-838:59:59' to '838:59:59'. MySQL displays TIME values in 'HH:MM:SS' format, but allows you to assign values to TIME columns using either strings or numbers. See section 6.2.2.3 The TIME Type.
YEAR[(2|4)]
A year in 2- or 4-digit format (default is 4-digit). The allowable values are 1901 to 2155, 0000 in the 4-digit year format, and 1970-2069 if you use the 2-digit format (70-69). MySQL displays YEAR values in YYYY format, but allows you to assign values to YEAR columns using either strings or numbers. (The YEAR type is unavailable prior to MySQL Version 3.22.) See section 6.2.2.4 The YEAR Type.
[NATIONAL] CHAR(M) [BINARY]
A fixed-length string that is always right-padded with spaces to the specified length when stored. The range of M is 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. 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 6.2.3.1 The CHAR and VARCHAR Types.
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 6.5.3.1 Silent Column Specification Changes. VARCHAR is a shorthand for CHARACTER VARYING. See section 6.2.3.1 The CHAR and VARCHAR Types.
TINYBLOB
TINYTEXT
A BLOB or TEXT column with a maximum length of 255 (2^8 - 1) characters. See section 6.5.3.1 Silent Column Specification Changes. See section 6.2.3.2 The BLOB and TEXT Types.
BLOB
TEXT
A BLOB or TEXT column with a maximum length of 65535 (2^16 - 1) characters. See section 6.5.3.1 Silent Column Specification Changes. See section 6.2.3.2 The BLOB and TEXT Types.
MEDIUMBLOB
MEDIUMTEXT
A BLOB or TEXT column with a maximum length of 16777215 (2^24 - 1) characters. See section 6.5.3.1 Silent Column Specification Changes. See section 6.2.3.2 The BLOB and TEXT Types.
LONGBLOB
LONGTEXT
A BLOB or TEXT column with a maximum length of 4294967295 (2^32 - 1) characters. See section 6.5.3.1 Silent Column Specification Changes. Note that because the server/client protocol and MyISAM tables has currently a limit of 16M per communication packet / table row, you can't yet use this the whole range of this type. See section 6.2.3.2 The BLOB and TEXT Types.
ENUM('value1','value2',...)
An enumeration. A string object that can have only one value, chosen from the list of values 'value1', 'value2', ..., NULL or the special "" error value. An ENUM can have a maximum of 65535 distinct values. See section 6.2.3.3 The ENUM Type.
SET('value1','value2',...)
A set. A string object that can have zero or more values, each of which must be chosen from the list of values 'value1', 'value2', ... A SET can have a maximum of 64 members. See section 6.2.3.4 The SET Type.

6.2.1 Numeric Types

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

The NUMERIC and DECIMAL types are implemented as the same type by MySQL, as permitted by the 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 data types. This is not generally a serious problem, as the principal benefits of these types derive from the ability to control both precision and scale explicitly.

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

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

As an extension to the 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 data types. 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.

Type Bytes From To
TINYINT 1 -128 127
SMALLINT 2 -32768 32767
MEDIUMINT 3 -8388608 8388607
INT 4 -2147483648 2147483647
BIGINT 8 -9223372036854775808 9223372036854775807

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:

6.2.2.1 Y2K Issues and Date Types

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

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:

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:

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:

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

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

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

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

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:

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:

Be aware of certain pitfalls when specifying date values:

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:

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:

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.

Type Max.size Bytes
TINYTEXT or TINYBLOB 2^8-1 255
TEXT or BLOB 2^16-1 (64K-1) 65535
MEDIUMTEXT or MEDIUMBLOB 2^24-1 (16M-1) 16777215
LONGBLOB 2^32-1 (4G-1) 4294967295

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:

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

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

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

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

MySQL may silently change the type of a CHAR or VARCHAR column at table creation time. See section 6.5.3.1 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 6.2.6 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:

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:

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:

Each enumeration value has an index:

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:

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

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:

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

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

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

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:

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

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

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

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

Storage Requirements for Date and Time Types

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

Storage Requirements for String Types

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

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

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

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

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

The size of a SET object is determined by the number of different set members. If the set size is N, the object occupies (N+7)/8 bytes, rounded up to 1, 2, 3, 4, or 8 bytes. A SET can have a maximum of 64 members. See section 6.2.3.4 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 towards 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 1.8.2 Running MySQL in ANSI Mode.

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

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

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

is displayed like this: 

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

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:

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

The 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 good with other programs, MySQL supports the following extra features when using IS NULL:
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. 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

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

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 5.5.2 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 NULLs and empty strings, 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'
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
Note that for CHAR_LENGTH() and CHARACTER_LENGTH(), multi-byte characters are only counted once.
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 G 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 section 6.8 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 6.3.5 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 6.3.5 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)
Returns the smallest integer value not less than X: 
mysql> SELECT CEILING(1.23);
        -> 2
mysql> SELECT CEILING(-1.23);
        -> -1
Note that the return value is converted to a BIGINT!
ROUND(X)
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
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: 
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 towards 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 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

See section 6.2.2 Date and Time Types for a description of the range of values each type has and the valid formats in which date and time values may be specified. Note that 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.

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

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
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,first)
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. Here is a table for how the second argument works:
Value Meaning
0 Week starts on Sunday and return value is in range 0-53
1 Week starts on Monday and return value is in range 0-53
2 Week starts on Sunday and return value is in range 1-53
3 Week starts on Monday and return value is in range 1-53 (ISO 8601) 
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 second argument is 0. In MySQL 4.1 You can set the default value of the second argument by a variable default_week_format. The syntax of default_week_format is: SET [SESSION | GLOBAL] default_week_format = [0|1|2|3]; Note: in Version 4.0, WEEK(#,0) was changed to match the calendar in the USA. Before that WEEK() was calculated wrong for dates in USA. (In effect WEEK(#) and WEEK(#,0) was wrong for all cases). Note that if a week is the last week of the previous year, MySQL will return 0 if you don't use 2 or 3 as the optional argument: 
mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);
        -> 2000, 0
mysql> SELECT WEEK('2000-01-01',2);
        -> 52
One could argue that MySQL should return 52 for the WEEK() function as the given date is actually the 52 second 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 extracts a date part from a date. If you would prefer to know the correct year-week, then you should use the 2 or 3 as the optional argument or use the YEARWEEK() function: 
mysql> SELECT YEARWEEK('2000-01-01');
        -> 199952
mysql> SELECT MID(YEARWEEK('2000-01-01'),5,2);
        -> 52
YEAR(date)
Returns the year for date, in the range 1000 to 9999: 
mysql> SELECT YEAR('98-02-03');
        -> 1998
YEARWEEK(date)
YEARWEEK(date,first)
Returns year and week for a date. The second arguments works exactly like the second argument to WEEK(). Note that the year 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, in the range 0 to 23: 
mysql> SELECT HOUR('10:05:03');
        -> 10
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
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)
ADDDATE(date,INTERVAL expr type)
SUBDATE(date,INTERVAL expr type)
These functions perform date arithmetic. ADDDATE() and SUBDATE() are synonyms for DATE_ADD() and DATE_SUB(). In MySQL Version 3.23, you can use + and - instead of DATE_ADD() and DATE_SUB() if the expression on the right side is a date or datetime column. (See example 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"
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
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 or subtract a date value against something that contains a time part, the date value will be 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 incorrect 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 word INTERVAL and the type keyword are not case-sensitive.
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
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 [AP]M)
%T Time, 24-hour (hh:mm:ss)
%S Seconds (00..59)
%s Seconds (00..59)
%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: 
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'
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.
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.
CURDATE()
CURRENT_DATE
Returns today's 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
Note that NOW() is only evaluated once per query, namely at the start of query execution. This means that multiple references to NOW() within a single query will always give the same time.
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 will return 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 will return 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 6.3.5 Cast Functions.
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: If format is given the restult 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(875996580);
        -> '1997-10-04 22:23:00'
mysql> SELECT FROM_UNIXTIME(875996580) + 0;
        -> 19971004222300
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
                            '%Y %D %M %h:%i:%s %x');
        -> '1997 23rd December 03:43:30 1997'
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

6.3.5 Cast Functions

The syntax of the CAST function is: 

CAST(expression AS type)

or

CONVERT(expression,type)

Where type is one of:

CAST() is SQL-99 syntax and CONVERT() is ODBC syntax.

The cast function is mainly useful when you want to create a column with a specific type in a CREATE ... SELECT : 

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

CAST(string AS BINARY is the same thing as BINARY string. CAST(expr AS CHAR threats expression to be a string with the default character set.

NOTE: In MysQL 4.0 the CAST to DATE, DATETIME and 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 will be converted to the correct column when it's sent to the user: 

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

You should not use CAST to extract data in different formats but instead use string functions like LEFT or EXTRACT(). See section 6.3.4 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 an numerical operations (like +) and one of the operands are unsigned integer, then the result will be unsigned. You can override this by using the SIGNED and UNSIGNED cast operators, which will 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 operation is a floating-point value (In this context DECIMAL() is regarded as a floating-point value) the result will be a floating-point value and is not affected by the above rule. 

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 CAST() and CONVERT() functions were added in MySQL 4.0.2.

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 trick 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 before doing the subtraction.

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 UNSIGNED BIGINT column type.

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

6.3.6.2 Miscellaneous Functions

DATABASE()
Returns the current database name: 
mysql> SELECT DATABASE();
        -> 'test'
If there is no current database, DATABASE() returns the empty string.
USER()
SYSTEM_USER()
SESSION_USER()
Returns the current MySQL user name: 
mysql> SELECT USER();
        -> 'davida@localhost'
In MySQL Version 3.22.11 or later, this includes the client hostname as well as the user name. You can extract just the user name part like this (which works whether the value includes a hostname part): 
mysql> SELECT SUBSTRING_INDEX(USER(),"@",1);
        -> 'davida'
CURRENT_USER()
Returns the user name that the current session was authenticated as: 
mysql> SELECT USER();
        -> 'davida@localhost'
mysql> SELECT * FROM mysql.user;
        -> ERROR 1044: Access denied for user: '@localhost' to database 'mysql'
mysql> SELECT CURRENT_USER();
        -> '@localhost'
PASSWORD(str)
OLD_PASSWORD(str)
Calculates a password string from the plaintext password str. This is the function that is used for encrypting MySQL passwords for storage in the Password column of the user grant table: 
mysql> SELECT PASSWORD('badpwd');
        -> '7f84554057dd964b'
PASSWORD() encryption is non-reversible. PASSWORD() does not perform password encryption in the same way that Unix passwords are encrypted. See ENCRYPT(). Note: The PASSWORD() function is used by the authentication system in MySQL Server, you should NOT use it in your own applications. For that purpose, use MD5() or SHA1() instead. Also see RFC-2195 for more information about handling passwords and authentication securely in your application.
ENCRYPT(str[,salt])
Encrypt str using the Unix crypt() system call. The salt argument should be a string with two characters. (As of MySQL Version 3.22.16, salt may be longer than two characters.): 
mysql> SELECT ENCRYPT("hello");
        -> 'VxuFAJXVARROc'
ENCRYPT() ignores all but the first 8 characters of str, at least on some systems. This will be determined by the behaviour of the underlying crypt() system call. If crypt() is not available on your system, ENCRYPT() always returns NULL. Because of this we recommend that you use MD5() or SHA1() instead; these two functions exist on all platforms.
ENCODE(str,pass_str)
Encrypt str using pass_str as the password. To decrypt the result, use DECODE(). The results is a binary string of the same length as string. If you want to save it in a column, use a BLOB column type.
DECODE(crypt_str,pass_str)
Descrypts the encrypted string crypt_str using pass_str as the password. crypt_str should be a string returned from ENCODE().
MD5(string)
Calculates an MD5 128 bit checksum for the string. The value is returned as a 32 digit hex number that may, for example, be used as a hash key: 
mysql> SELECT MD5("testing");
        -> 'ae2b1fca515949e5d54fb22b8ed95575'
This is the "RSA Data Security, Inc. MD5 Message-Digest Algorithm".
SHA1(string)
SHA(string)
Calculates an SHA1 160 bit checksum for the string, as described in RFC 3174 (Secure Hash Algorithm). The value is returned as a 40 digit hex number, or NULL in case the input argument was NULL. One of the possible uses for this function is as a hash key. You can also use it as cryptographically safe function for storing passwords. 
mysql> SELECT SHA1("abc");
        -> 'a9993e364706816aba3e25717850c26c9cd0d89d'
SHA1() was added in version 4.0.2, and can be considered a cryptographically more secure equivalent of MD5(). SHA() is synonym for SHA1().
AES_ENCRYPT(string,key_string)
AES_DECRYPT(string,key_string)
These functions allow encryption/decryption of data using the official AES (Advanced Encryption Standard) algorithm, previously known as Rijndael. Encoding with 128 bit key length is used, but you can extend it up to 256 bit by patching the source. We chose 128 bits because it is much faster and it is usually secure enough. The input arguments may be any length. If either argument is NULL, the result of this function is also NULL. As AES is a block level algorithm, padding is used to encode uneven length strings and so the result string length may be calculated as 16*(trunc(string_length/16)+1). If AES_DECRYPT() detects invalid data or incorrect padding, it will return NULL. However, it is possible for AES_DECRYPT() to return a non-NULL value (possibly garbage) if the input data or the key was invalid. You can use the AES functions to store data in an encrypted form by modifying your queries: 
INSERT INTO t VALUES (1,AES_ENCRYPT("text","password"));
You can get even more security by avoiding transferring the key over the connection for each query, which can be accomplished by storing it in a server side variable at connection time: 
SELECT @password:="my password";
INSERT INTO t VALUES (1,AES_ENCRYPT("text",@password));
AES_ENCRYPT() and AES_DECRYPT() were added in version 4.0.2, and can be considered the most cryptographically secure encryption functions currently available in MySQL.
DES_ENCRYPT(string_to_encrypt [, (key_number | key_string) ] )
Encrypts the string with the given key using the Triple-DES algorithm. Note that this function only works if you have configured MySQL with SSL support. See section 4.3.9 Using Secure Connections. The encryption key to use is chosen the following way:
Argument Description
Only one argument The first key from des-key-file is used.
key number The given key (0-9) from the des-key-file is used.
string The given key_string will be used to crypt string_to_encrypt.
The return string will be a binary string where the first character will be CHAR(128 | key_number). The 128 is added to make it easier to recognise an encrypted key. If you use a string key, key_number will be 127. On error, this function returns NULL. The string length for the result will be new_length= org_length + (8-(org_length % 8))+1. The des-key-file has the following format: 
key_number des_key_string
key_number des_key_string
Each key_number must be a number in the range from 0 to 9. Lines in the file may be in any order. des_key_string is the string that will be used to encrypt the message. Between the number and the key there should be at least one space. The first key is the default key that will be used if you don't specify any key argument to DES_ENCRYPT() You can tell MySQL to read new key values from the key file with the FLUSH DES_KEY_FILE command. This requires the Reload_priv privilege. One benefit of having a set of default keys is that it gives applications a way to check for the existence of encrypted column values, without giving the end user the right to decrypt those values. 
mysql> SELECT customer_address FROM customer_table WHERE
       crypted_credit_card = DES_ENCRYPT("credit_card_number");
DES_DECRYPT(string_to_decrypt [, key_string])
Decrypts a string encrypted with DES_ENCRYPT(). Note that this function only works if you have configured MySQL with SSL support. See section 4.3.9 Using Secure Connections. If no key_string argument is given, DES_DECRYPT() examines the first byte of the encrypted string to determine the DES key number that was used to encrypt the original string, then reads the key from the des-key-file to decrypt the message. For this to work the user must have the SUPER privilege. If you pass this function a key_string argument, that string is used as the key for decrypting the message. If the string_to_decrypt doesn't look like an encrypted string, MySQL will return the given string_to_decrypt. On error, this function returns NULL.
COMPRESS(string_to_compress)
Compresses a string. 
mysql> SELECT LENGTH(COMPRESS(REPEAT("a",1000)));
        -> 21
mysql> SELECT LENGTH(COMPRESS(""));
        -> 0
mysql> SELECT LENGTH(COMPRESS("a"));
        -> 13
mysql> SELECT LENGTH(COMPRESS(REPEAT("a",16)));
        -> 15
COMPRESS() was added in MySQL version 4.1.1.
UNCOMPRESS(string_to_uncompress)
Uncompresses a string compressed by the COMPRESS() function. 
mysql> SELECT UNCOMPRESS(COMPRESS("any string"));
        -> 'any string'
UNCOMPRESS() was added in MySQL version 4.1.1.
UNCOMPRESS_LENGTH(compressed_string)
Returns the length of a compressed string before compressing. 
mysql> SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT("a",30)));
        -> 30
UNCOMPRESSED_LENGTH() was added in MySQL version 4.1.1.
LAST_INSERT_ID([expr])
Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column. See section 9.1.3.31 mysql_insert_id(). 
mysql> SELECT LAST_INSERT_ID();
        -> 195
The last ID that was generated is maintained in the server on a per-connection basis. It will not be changed by another client. It will not even be changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0). If you insert many rows at the same time with an insert statement, LAST_INSERT_ID() returns the value for the first inserted row. The reason for this is to make it possible to easily reproduce the same INSERT statement against some other server. If expr is given as an argument to LAST_INSERT_ID(), then the value of the argument is returned by the function, and is set as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences: First create the table: 
mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);
Then the table can be used to generate sequence numbers like this: 
mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
You can generate sequences without calling LAST_INSERT_ID(), but the utility of using the function this way is that the ID value is maintained in the server as the last automatically generated value (multi-user safe). You can retrieve the new ID as you would read any normal AUTO_INCREMENT value in MySQL. For example, LAST_INSERT_ID() (without an argument) will return the new ID. The C API function mysql_insert_id() can also be used to get the value. Note that as mysql_insert_id() is only updated after INSERT and UPDATE statements, so you can't use the C API function to retrieve the value for LAST_INSERT_ID(expr) after executing other SQL statements like SELECT or SET.
FORMAT(X,D)
Formats the number X to a format like '#,###,###.##', rounded to D decimals. If D is 0, the result will have no decimal point or fractional part: 
mysql> SELECT FORMAT(12332.123456, 4);
        -> '12,332.1235'
mysql> SELECT FORMAT(12332.1,4);
        -> '12,332.1000'
mysql> SELECT FORMAT(12332.2,0);
        -> '12,332'
VERSION()
Returns a string indicating the MySQL server version: 
mysql> SELECT VERSION();
        -> '3.23.13-log'
Note that if your version ends with -log this means that logging is enabled.
CONNECTION_ID()
Returns the connection id (thread_id) for the connection. Every connection has its own unique id: 
mysql> SELECT CONNECTION_ID();
        -> 1
GET_LOCK(str,timeout)
Tries to obtain a lock with a name given by the string str, with a timeout of timeout seconds. Returns 1 if the lock was obtained successfully, 0 if the attempt timed out, or NULL if an error occurred (such as running out of memory or the thread was killed with mysqladmin kill). A lock is released when you execute RELEASE_LOCK(), execute a new GET_LOCK(), or the thread terminates. This function can be used to implement application locks or to simulate record locks. It blocks requests by other clients for locks with the same name; clients that agree on a given lock string name can use the string to perform cooperative advisory locking: 
mysql> SELECT GET_LOCK("lock1",10);
        -> 1
mysql> SELECT IS_FREE_LOCK("lock2");
        -> 1
mysql> SELECT GET_LOCK("lock2",10);
        -> 1
mysql> SELECT RELEASE_LOCK("lock2");
        -> 1
mysql> SELECT RELEASE_LOCK("lock1");
        -> NULL
Note that the second RELEASE_LOCK() call returns NULL because the lock "lock1" was automatically released by the second GET_LOCK() call.
RELEASE_LOCK(str)
Releases the lock named by the string str that was obtained with GET_LOCK(). Returns 1 if the lock was released, 0 if the lock wasn't locked by this thread (in which case the lock is not released), and NULL if the named lock didn't exist. The lock will not exist if it was never obtained by a call to GET_LOCK() or if it already has been released. The DO statement is convinient to use with RELEASE_LOCK(). See section 6.4.10 DO Syntax.
IS_FREE_LOCK(str)
Checks if the lock named str is free to use (that is, not locked). Returns 1 if the lock is free (no one is using the lock), 0 if the lock is in use, and NULL on errors (like incorrect arguments).
BENCHMARK(count,expr)
The BENCHMARK() function executes the expression expr repeatedly count times. It may be used to time how fast MySQL processes the expression. The result value is always 0. The intended use is in the mysql client, which reports query execution times: 
mysql> SELECT BENCHMARK(1000000,ENCODE("hello","goodbye"));
+----------------------------------------------+
| BENCHMARK(1000000,ENCODE("hello","goodbye")) |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+
1 row in set (4.74 sec)
The time reported is elapsed time on the client end, not CPU time on the server end. It may be advisable to execute BENCHMARK() several times, and interpret the result with regard to how heavily loaded the server machine is.
INET_NTOA(expr)
Given a numeric network address (4 or 8 byte), returns the dotted-quad representation of the address as a string: 
mysql> SELECT INET_NTOA(3520061480);
       ->  "209.207.224.40"
INET_ATON(expr)
Given the dotted-quad representation of a network address as a string, returns an integer that represents the numeric value of the address. Addresses may be 4 or 8 byte addresses: 
mysql> SELECT INET_ATON("209.207.224.40");
       ->  3520061480
The generated number is always in network byte order; for example the above number is calculated as 209*256^3 + 207*256^2 + 224*256 +40.
MASTER_POS_WAIT(log_name, log_pos [, timeout])
Blocks until the slave reaches (that is, has read and applied all updates up to) the specified position in the master log. If master information is not initialised, or if the arguments are incorrect, returns NULL. If the slave is not running, will block and wait until it is started and goes to or past the specified position. If the slave is already past the specified position, returns immediately. If timeout (new in 4.0.10) is specified, will give up waiting when timeout seconds have elapsed. timeout must be greater than 0; a zero or negative timeout means no timeout. The return value is the number of log events it had to wait to get to the specified position, or NULL in case of error, or -1 if the timeout has been exceeded. This command is useful for control of master-slave synchronisation.
FOUND_ROWS()
Returns the number of rows that the preceding SELECT statement would have returned, if it had not been restricted with LIMIT. For FOUND_ROWS() to work correctly following a SELECT statement that includes a LIMIT clause, the statement must include the SQL_CALC_FOUND_ROWS option: 
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
       WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();
The second SELECT will return a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause. Note that if you are using SELECT SQL_CALC_FOUND_ROWS ... MySQL has to calculate all rows in the result set. However, this is faster than if you would not use LIMIT, as the result set need not be sent to the client. If the preceding SELECT statement does not include the SQL_CALC_FOUND_ROWS option, then FOUND_ROWS() may return a different result when LIMIT is used than when it is not. SQL_CALC_FOUND_ROWS and FOUND_ROWS() are available starting at MySQL version 4.0.0.

6.3.7 Functions and Modifiers for Use with GROUP BY Clauses

6.3.7.1 GROUP BY Functions

If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.

COUNT(expr)
Returns a count of the number of non-NULL values in the rows retrieved by a SELECT statement: 
mysql> SELECT student.student_name,COUNT(*)
    ->        FROM student,course
    ->        WHERE student.student_id=course.student_id
    ->        GROUP BY student_name;
COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values. COUNT(*) is optimised to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. For example: 
mysql> SELECT COUNT(*) FROM student;
This optimisation applies only to MyISAM and ISAM tables only, because an exact record count is stored for these table types and can be accessed very quickly. For transactional storage engines (InnodB, BDB), storing an exact row count is more problematic because multiple transactions may be occurring, each of which may affect the count.
COUNT(DISTINCT expr,[expr...])
Returns a count of the number of different non-NULL values: 
mysql> SELECT COUNT(DISTINCT results) FROM student;
In MySQL you can get the number of distinct expression combinations that don't contain NULL by giving a list of expressions. In SQL-99 you would have to do a concatenation of all expressions inside COUNT(DISTINCT ...).
AVG(expr)
Returns the average value of expr: 
mysql> SELECT student_name, AVG(test_score)
    ->        FROM student
    ->        GROUP BY student_name;
MIN(expr)
MAX(expr)
Returns the minimum or maximum value of expr. MIN() and MAX() may take a string argument; in such cases they return the minimum or maximum string value. See section 5.4.3 How MySQL Uses Indexes. 
mysql> SELECT student_name, MIN(test_score), MAX(test_score)
    ->        FROM student
    ->        GROUP BY student_name;
In MIN(), MAX() and other aggregate functions, MySQL currently compares ENUM and SET columns by their string value rather than by the string's relative position in the set. This will be rectified.
SUM(expr)
Returns the sum of expr. Note that if the return set has no rows, it returns NULL!
GROUP_CONCAT(expr)
Full syntax: 
GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col ...]]
             [SEPARATOR str_val])
This function was added in MySQL version 4.1. It returns a string result with the concatenated values from a group: 
mysql> SELECT student_name,
    ->        GROUP_CONCAT(test_score)
    ->        FROM student 
    ->        GROUP BY student_name;
or
mysql> SELECT student_name,
    ->        GROUP_CONCAT(DISTINCT test_score
    ->                     ORDER BY test_score DESC SEPARATOR " ")
    ->        FROM student
    ->        GROUP BY student_name;
In MySQL you can get the concatenated values of expression combinations. You can eliminate duplicate values by using DISTINCT. If you want to sort values in the result you should use ORDER BY clause. To sort in reverse order, add the DESC (descending) keyword to the name of the column you are sorting by in the ORDER BY clause. The default is ascending order; this may be specified explicitly using the ASC keyword. SEPARATOR is the string value which should be inserted between values of result. The default is a comma (`","'). You can remove the separator altogether by specifying SEPARATOR "". You can set a maximum allowed length with the variable group_concat_max_len in your configuration. The syntax to do this at runtime is: 
SET [SESSION | GLOBAL] group_concat_max_len = unsigned_integer;
If a maximum length has been set, the result is truncated to this maximum length. The GROUP_CONCAT() function is an enhanced implementation of the basic LIST() function supported by Sybase SQL Anywhere. GROUP_CONCAT() is backward compatible with the extremely limited functionality of LIST(), if only one column and no other options are specified. LIST() does have a default sorting order.
VARIANCE(expr)
Returns the standard variance of expr. This is an extension to SQL-99 (available only in version 4.1 or later).
STD(expr)
STDDEV(expr)
Returns the standard deviation of expr. This is an extension to SQL-99. The STDDEV() form of this function is provided for Oracle compatibility.
BIT_OR(expr)
Returns the bitwise OR of all bits in expr. The calculation is performed with 64-bit (BIGINT) precision. Function returns 0 if there was no matching rows.
BIT_AND(expr)
Returns the bitwise AND of all bits in expr. The calculation is performed with 64-bit (BIGINT) precision. Function returns -1 if there was no matching rows.

MySQL has extended the use of GROUP BY. You can use columns or calculations in the SELECT expressions that don't appear in the GROUP BY part. This stands for any possible value for this group. You can use this to get better performance by avoiding sorting and grouping on unnecessary items. For example, you don't need to group on customer.name in the following query: 

mysql> SELECT order.custid,customer.name,MAX(payments)
    ->        FROM order,customer
    ->        WHERE order.custid = customer.custid
    ->        GROUP BY order.custid;

In standard SQL, you would have to add customer.name to the GROUP BY clause. In MySQL, the name is redundant if you don't run in ANSI mode.

Don't use this feature if the columns you omit from the GROUP BY part aren't unique in the group! You will get unpredictable results.

In some cases, you can use MIN() and MAX() to obtain a specific column value even if it isn't unique. The following gives the value of column from the row containing the smallest value in the sort column: 

SUBSTR(MIN(CONCAT(RPAD(sort,6,' '),column)),7)

See section 3.5.4 The Rows Holding the Group-wise Maximum of a Certain Field.

Note that if you are using MySQL Version 3.22 (or earlier) or if you are trying to follow SQL-99, you can't use expressions in GROUP BY or ORDER BY clauses. You can work around this limitation by using an alias for the expression: 

mysql> SELECT id,FLOOR(value/100) AS val FROM tbl_name
    ->        GROUP BY id,val ORDER BY val;

In MySQL Version 3.23 you can do: 

mysql> SELECT id,FLOOR(value/100) FROM tbl_name ORDER BY RAND();

6.3.7.2 GROUP BY Modifiers

As of MySQL 4.1.1, the GROUP BY clause allows a WITH ROLLUP modifier that causes extra rows to be added to the summary output. These rows represent higher-level (or super-aggregate) summary operations. ROLLUP thus allows you to answer questions at multiple levels of analysis with a single query. It can be used, for example, to provide support for OLAP (Online Analytical Processing) operations.

As an illustration, suppose that a table named sales has year, country, product, and profit columns for recording sales profitability: 

CREATE TABLE sales
(
    year    INT NOT NULL,
    country VARCHAR(20) NOT NULL,
    product VARCHAR(32) NOT NULL,
    profit  INT
);

The table's contents can be summarized per year with a simple GROUP BY like this: 

mysql> SELECT year, SUM(profit) FROM sales GROUP BY year;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 |        4525 |
| 2001 |        3010 |
+------+-------------+

This output shows the total profit for each year, but if you also want to determine the total profit summed over all years, you must add up the individual values yourself or run an additional query.

Or you can use ROLLUP, which provides both levels of analysis with a single query. Adding a WITH ROLLUP modifier to the GROUP BY clause causes the query to produce another row that shows the grand total over all year values: 

mysql> SELECT year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 |        4525 |
| 2001 |        3010 |
| NULL |        7535 |
+------+-------------+

The grand total super-aggregate line is identified by the value NULL in the year column.

ROLLUP has a more complex effect when there are multiple GROUP BY columns. In this case, each time there is a ``break'' (change in value) in any but the last grouping column, the query produces an extra super-aggregate summary row.

For example, without ROLLUP, a summary on the sales table based on year, country, and product might look like this: 

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product;
+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
| 2000 | USA     | Calculator |          75 |
| 2000 | USA     | Computer   |        1500 |
| 2001 | Finland | Phone      |          10 |
| 2001 | USA     | Calculator |          50 |
| 2001 | USA     | Computer   |        2700 |
| 2001 | USA     | TV         |         250 |
+------+---------+------------+-------------+

The output indicates summary values only at the year/country/product level of analysis. When ROLLUP is added, the query produces several extra rows: 

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | Finland | NULL       |        1600 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
| 2000 | India   | NULL       |        1350 |
| 2000 | USA     | Calculator |          75 |
| 2000 | USA     | Computer   |        1500 |
| 2000 | USA     | NULL       |        1575 |
| 2000 | NULL    | NULL       |        4525 |
| 2001 | Finland | Phone      |          10 |
| 2001 | Finland | NULL       |          10 |
| 2001 | USA     | Calculator |          50 |
| 2001 | USA     | Computer   |        2700 |
| 2001 | USA     | TV         |         250 |
| 2001 | USA     | NULL       |        3000 |
| 2001 | NULL    | NULL       |        3010 |
| NULL | NULL    | NULL       |        7535 |
+------+---------+------------+-------------+

For this query, adding ROLLUP causes the output to include summary information at four levels of analysis, not just one. Here's how to interpret the ROLLUP output:

Other Considerations When using ROLLUP

The following items list some behaviours specific to the MySQL implementation of ROLLUP:

When you use ROLLUP, you cannot also use an ORDER BY clause to sort the results. (In other words, ROLLUP and ORDER BY are mutually exclusive.) However, you still have some control over sort order. GROUP BY in MySQL sorts results, and you can use explicit ASC and DESC keywords with columns named in the GROUP BY list to specify sort order for individual columns. (The higher-level summary rows added by ROLLUP still appear after the rows from which they are calculated, regardless of the sort order.)

LIMIT can be used to restrict the number of rows returned to the client. LIMIT is applied after ROLLUP, so the limit applies against the extra rows added by ROLLUP. For example: 

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product WITH ROLLUP
    -> LIMIT 5;
+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | Finland | NULL       |        1600 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
+------+---------+------------+-------------+

Note that using LIMIT with ROLLUP may produce results that are more difficult to interpret, because you have less context for understanding the super-aggregate rows.

The NULL indicators in each super-aggregate row are produced when the row is sent to the client. The server looks at the columns named in the GROUP BY clause following the leftmost one that has changed value. For any column in the result set with a name that is a lexical match to any of those names, its value is set to NULL. (If you specify grouping columns by column number, the server identifies which columns to set to NULL by number.)

Because the NULL values in the super-aggregate rows are placed into the result set at such a late stage in query processing, you cannot test them as NULL values within the query itself. For example, you cannot add HAVING product IS NULL to the query to eliminate from the output all but the super-aggregate rows.

On the other hand, the NULL values do appear as NULL on the client side and can be tested as such using any MySQL client programming interface.

6.4 Data Manipulation: SELECT, INSERT, UPDATE, DELETE

6.4.1 SELECT Syntax

SELECT [STRAIGHT_JOIN]
       [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
       [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
       [DISTINCT | DISTINCTROW | ALL]
    select_expression,...
    [INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
    [FROM table_references
      [WHERE where_definition]
      [GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...
        [WITH ROLLUP]]
      [HAVING where_definition]
      [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
      [LIMIT [offset,] rows | rows OFFSET offset]
      [PROCEDURE procedure_name(argument_list)]
      [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT is used to retrieve rows selected from one or more tables. select_expression indicates the columns you want to retrieve. SELECT may also be used to retrieve rows computed without reference to any table. For example: 

mysql> SELECT 1 + 1;
         -> 2

All keywords used must be given in exactly the order shown above. For example, a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause.

6.4.1.1 JOIN Syntax

MySQL supports the following JOIN syntaxes for use in SELECT statements: 

table_reference, table_reference
table_reference [CROSS] JOIN table_reference
table_reference INNER JOIN table_reference join_condition
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference join_condition
table_reference LEFT [OUTER] JOIN table_reference
table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
{ OJ table_reference LEFT OUTER JOIN table_reference ON conditional_expr }
table_reference RIGHT [OUTER] JOIN table_reference join_condition
table_reference RIGHT [OUTER] JOIN table_reference
table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

Where table_reference is defined as: 

table_name [[AS] alias] [[USE INDEX (key_list)] | [IGNORE INDEX (key_list)] | [FORCE INDEX (key_list)]]

and join_condition is defined as: 

ON conditional_expr |
USING (column_list)

You should generally not have any conditions in the ON part that are used to restrict which rows you have in the result set (there are exceptions to this rule). If you want to restrict which rows should be in the result, you have to do this in the WHERE clause.

Note that in versions before Version 3.23.17, the INNER JOIN didn't take a join_condition!

The last LEFT OUTER JOIN syntax shown above exists only for compatibility with ODBC:

Some examples: 

mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id);
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
    ->          LEFT JOIN table3 ON table2.id=table3.id;
mysql> SELECT * FROM table1 USE INDEX (key1,key2)
    ->          WHERE key1=1 AND key2=2 AND key3=3;
mysql> SELECT * FROM table1 IGNORE INDEX (key3)
    ->          WHERE key1=1 AND key2=2 AND key3=3;

See section 5.2.7 How MySQL Optimises LEFT JOIN and RIGHT JOIN.

6.4.1.2 UNION Syntax

SELECT ...
UNION [ALL]
SELECT ...
  [UNION
   SELECT ...]

UNION is implemented in MySQL 4.0.0.

UNION is used to combine the result from many SELECT statements into one result set.

The columns listed in the select_expression portion of the SELECT should have the same type. The column names used in the first SELECT query will be used as the column names for the results returned.

The SELECT commands are normal select commands, but with the following restrictions:

If you don't use the keyword ALL for the UNION, all returned rows will be unique, as if you had done a DISTINCT for the total result set. If you specify ALL, then you will get all matching rows from all the used SELECT statements.

If you want to use an ORDER BY for the total UNION result, you should use parentheses: 

(SELECT a FROM table_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM table_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10)
ORDER BY a;

6.4.2 HANDLER Syntax

HANDLER tbl_name OPEN [ AS alias ]
HANDLER tbl_name READ index_name { = | >= | <= | < } (value1,value2,...)
    [ WHERE ... ] [LIMIT ... ]
HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST }
    [ WHERE ... ] [LIMIT ... ]
HANDLER tbl_name READ { FIRST | NEXT }
    [ WHERE ... ] [LIMIT ... ]
HANDLER tbl_name CLOSE

The HANDLER statement provides direct access to the MyISAM table storage engine interface.

The first form of HANDLER statement opens a table, making it accessible via subsequent HANDLER ... READ statements. This table object is not shared by other threads and will not be closed until the thread calls HANDLER tbl_name CLOSE or the thread dies.

The second form fetches one row (or more, specified by LIMIT clause) where the index specified complies to the condition and WHERE condition is met. If the index consists of several parts (spans over several columns) the values are specified in comma-separated list, providing values only for few first columns is possible.

The third form fetches one row (or more, specified by LIMIT clause) from the table in index order, matching WHERE condition.

The fourth form (without index specification) fetches one row (or more, specified by LIMIT clause) from the table in natural row order (as stored in datafile) matching WHERE condition. It is faster than HANDLER tbl_name READ index_name when a full table scan is desired.

HANDLER ... CLOSE closes a table that was opened with HANDLER ... OPEN.

Note: If you're using HANDLER interface for PRIMARY KEY you should remember to quote the keyword PRIMARY with backticks: HANDLER tbl READ `PRIMARY` > (...)

HANDLER is a somewhat low-level statement. For example, it does not provide consistency. That is, HANDLER ... OPEN does NOT take a snapshot of the table, and does NOT lock the table. This means that after a HANDLER ... OPEN is issued, table data can be modified (by this or any other thread) and these modifications may appear only partially in HANDLER ... NEXT or HANDLER ... PREV scans.

The reasons to use this interface instead of normal SQL are:

6.4.3 INSERT Syntax

    INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        VALUES ((expression | DEFAULT),...),(...),...
        [ ON DUPLICATE KEY UPDATE col_name=expression, ... ]
or  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...
or  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name
        SET col_name=(expression | DEFAULT), ...
        [ ON DUPLICATE KEY UPDATE col_name=expression, ... ]

INSERT inserts new rows into an existing table. The INSERT ... VALUES form of the statement inserts rows based on explicitly specified values. The INSERT ... SELECT form inserts rows selected from another table or tables. The INSERT ... VALUES form with multiple value lists is supported in MySQL Version 3.22.5 or later. The col_name=expression syntax is supported in MySQL Version 3.22.10 or later.

tbl_name is the table into which rows should be inserted. The column name list or the SET clause indicates which columns the statement specifies values for:

If you use INSERT ... SELECT or an INSERT ... VALUES statement with multiple value lists, you can use the C API function mysql_info() to get information about the query. The format of the information string is shown here: 

Records: 100 Duplicates: 0 Warnings: 0

Duplicates indicates the number of rows that couldn't be inserted because they would duplicate some existing unique index value. Warnings indicates the number of attempts to insert column values that were problematic in some way. Warnings can occur under any of the following conditions:

6.4.3.1 INSERT ... SELECT Syntax

INSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(column list)] SELECT ...

With INSERT ... SELECT statement you can quickly insert many rows into a table from one or many tables. 

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE
tblTemp1.fldOrder_ID > 100;

The following conditions hold for an INSERT ... SELECT statement:

You can of course also use REPLACE instead of INSERT to overwrite old rows.

6.4.4 INSERT DELAYED Syntax

INSERT DELAYED ...

The DELAYED option for the INSERT statement is a MySQL-specific option that is very useful if you have clients that can't wait for the INSERT to complete. This is a common problem when you use MySQL for logging and you also periodically run SELECT and UPDATE statements that take a long time to complete. DELAYED was introduced in MySQL Version 3.22.15. It is a MySQL extension to SQL-92.

INSERT DELAYED only works with ISAM and MyISAM tables. Note that as MyISAM tables supports concurrent SELECT and INSERT, if there is no free blocks in the middle of the datafile, you very seldom need to use INSERT DELAYED with MyISAM. See section 7.1 MyISAM Tables.

When you use INSERT DELAYED, the client will get an OK at once and the row will be inserted when the table is not in use by any other thread.

Another major benefit of using INSERT DELAYED is that inserts from many clients are bundled together and written in one block. This is much faster than doing many separate inserts.

Note that currently the queued rows are only stored in memory until they are inserted into the table. This means that if you kill mysqld the hard way (kill -9) or if mysqld dies unexpectedly, any queued rows that weren't written to disk are lost!

The following describes in detail what happens when you use the DELAYED option to INSERT or REPLACE. In this description, the ``thread'' is the thread that received an INSERT DELAYED command and ``handler'' is the thread that handles all INSERT DELAYED statements for a particular table.

Note that INSERT DELAYED is slower than a normal INSERT if the table is not in use. There is also the additional overhead for the server to handle a separate thread for each table on which you use INSERT DELAYED. This means that you should only use INSERT DELAYED when you are really sure you need it!

6.4.5 UPDATE Syntax

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT rows]

or

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...]
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]

UPDATE updates columns in existing table rows with new values. The SET clause indicates which columns to modify and the values they should be given. The WHERE clause, if given, specifies which rows should be updated. Otherwise, all rows are updated. If the ORDER BY clause is specified, the rows will be updated in the order that is specified.

If you specify the keyword LOW_PRIORITY, execution of the UPDATE is delayed until no other clients are reading from the table.

If you specify the keyword IGNORE, the update statement will not abort even if we get duplicate key errors during the update. Rows that would cause conflicts will not be updated.

If you access a column from tbl_name in an expression, UPDATE uses the current value of the column. For example, the following statement sets the age column to one more than its current value: 

mysql> UPDATE persondata SET age=age+1;

UPDATE assignments are evaluated from left to right. For example, the following statement doubles the age column, then increments it: 

mysql> UPDATE persondata SET age=age*2, age=age+1;

If you set a column to the value it currently has, MySQL notices this and doesn't update it.

UPDATE returns the number of rows that were actually changed. In MySQL Version 3.22 or later, the C API function mysql_info() returns the number of rows that were matched and updated and the number of warnings that occurred during the UPDATE. If you update a column that has been declared NOT NULL by setting to NULL, the column is set to the default value appropriate for the column type and the warning count is incremented. The default value is is 0 for numeric types, the empty string ('') for string types, and the ``zero'' value for date and time types.

Starting from MySQL version 3.23, you can use LIMIT # to ensure that only a given number of rows are changed. MySQL will stop the update as soon as it has found LIMIT rows that satisfies the WHERE clause, independent of the rows changed content or not.

If an ORDER BY clause is used (available from MySQL 4.0.0), the rows will be updated in that order. This is really only useful in conjunction with LIMIT.

Starting with MySQL Version 4.0.4, you can also perform UPDATE operations that cover multiple tables: 

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

Note: you can not use ORDER BY or LIMIT with multi-table UPDATE.

6.4.6 DELETE Syntax

DELETE [LOW_PRIORITY] [QUICK] FROM table_name
       [WHERE where_definition]
       [ORDER BY ...]
       [LIMIT rows]

or

DELETE [LOW_PRIORITY] [QUICK] table_name[.*] [, table_name[.*] ...]
       FROM table-references
       [WHERE where_definition]

or

DELETE [LOW_PRIORITY] [QUICK]
       FROM table_name[.*] [, table_name[.*] ...]
       USING table-references
       [WHERE where_definition]

DELETE deletes rows from table_name that satisfy the condition given by where_definition, and returns the number of records deleted.

If you issue a DELETE with no WHERE clause, all rows are deleted. If you do this in AUTOCOMMIT mode, this works as TRUNCATE. See section 6.4.7 TRUNCATE Syntax. In MySQL 3.23, DELETE without a WHERE clause will return zero as the number of affected records.

If you really want to know how many records are deleted when you are deleting all rows, and are willing to suffer a speed penalty, you can use a DELETE statement of this form: 

mysql> DELETE FROM table_name WHERE 1>0;

Note that this is much slower than DELETE FROM table_name with no WHERE clause, because it deletes rows one at a time.

If you specify the keyword LOW_PRIORITY, execution of the DELETE is delayed until no other clients are reading from the table.

If you specify the word QUICK then the storage engine will not merge index leaves during delete, which may speed up certain kind of deletes.

In MyISAM tables, deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. To reclaim unused space and reduce file-sizes, use the OPTIMIZE TABLE statement or the myisamchk utility to reorganise tables. OPTIMIZE TABLE is easier, but myisamchk is faster. See section 4.5.1 OPTIMIZE TABLE Syntax and section 4.4.6.10 Table Optimisation.

The first multi-table delete format is supported starting from MySQL 4.0.0. The second multi-table delete format is supported starting from MySQL 4.0.2.

The idea is that only matching rows from the tables listed before the FROM or before the USING clause are deleted. The effect is that you can delete rows from many tables at the same time and also have additional tables that are used for searching.

The .* after the table names is there just to be compatible with Access: 

DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

or

DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

In the above case we delete matching rows just from tables t1 and t2.

If an ORDER BY clause is used (available from MySQL 4.0.0), the rows will be deleted in that order. This is really only useful in conjunction with LIMIT. For example: 

DELETE FROM somelog
WHERE user = 'jcole'
ORDER BY timestamp
LIMIT 1

This will delete the oldest entry (by timestamp) where the row matches the WHERE clause.

The MySQL-specific LIMIT rows option to DELETE tells the server the maximum number of rows to be deleted before control is returned to the client. This can be used to ensure that a specific DELETE command doesn't take too much time. You can simply repeat the DELETE command until the number of affected rows is less than the LIMIT value.

From MySQL 4.0, you can specify multiple tables in the DELETE statement to delete rows from one table depending on a particular condition in multiple tables. However, you can not use ORDER BY or LIMIT in a multi-table DELETE.

6.4.7 TRUNCATE Syntax

TRUNCATE TABLE table_name

In 3.23 TRUNCATE TABLE is mapped to COMMIT ; DELETE FROM table_name. See section 6.4.6 DELETE Syntax.

TRUNCATE TABLE differs from DELETE FROM ... in the following ways:

TRUNCATE TABLE is an Oracle SQL extension. This statement was added in MySQL 3.23.28, although from 3.23.28 to 3.23.32, the keyword TABLE must be omitted.

6.4.8 REPLACE Syntax

    REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name [(col_name,...)]
        VALUES (expression,...),(...),...
or  REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...
or  REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name
        SET col_name=expression, col_name=expression,...

REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a UNIQUE index or PRIMARY KEY, the old record is deleted before the new record is inserted. See section 6.4.3 INSERT Syntax.

In other words, you can't access the values of the old row from a REPLACE statement. In some old MySQL versions it appeared that you could do this, but that was a bug that has been corrected.

To be able to use REPLACE you must have INSERT and DELETE privileges for the table.

When you use a REPLACE command, mysql_affected_rows() will return 2 if the new row replaced an old row. This is because one row was inserted after the duplicate was deleted.

This fact makes it easy to determine whether REPLACE added or replaced a row: check whether the affected-rows value is 1 (added) or 2 (replaced).

Note that unless you use a UNIQUE index or PRIMARY KEY, using a REPLACE command makes no sense, since it would just do an INSERT.

Here follows the used algorithm in more detail: (This is also used with LOAD DATA ... REPLACE. 

- Insert the row into the table
  - While duplicate key error for primary or unique key
    - Revert changed keys
    - Read conflicting row from the table through the duplicate key value
    - Delete conflicting row
    - Try again to insert the original primary key and unique keys in the tree

6.4.9 LOAD DATA INFILE Syntax

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [[OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\' ]
    ]
    [LINES 
        [STARTING BY '']    
        [TERMINATED BY '\n']
    ]
    [IGNORE number LINES]
    [(col_name,...)]

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. If the LOCAL keyword is specified, the file is read from the client host. If LOCAL is not specified, the file must be located on the server. (LOCAL is available in MySQL Version 3.22.6 or later.)

For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege on the server host. See section 4.2.7 Privileges Provided by MySQL.

In MySQL 3.23.49 and MySQL 4.0.2 LOCAL will only work if you have not started mysqld with --local-infile=0 or if you have not enabled your client to support LOCAL. See section 4.2.4 Security issues with LOAD DATA LOCAL.

If you specify the keyword LOW_PRIORITY, execution of the LOAD DATA statement is delayed until no other clients are reading from the table.

If you specify the keyword CONCURRENT with a MyISAM table, then other threads can retrieve data from the table while LOAD DATA is executing. Using this option will of course affect the performance of LOAD DATA a bit even if no other thread is using the table at the same time.

Using LOCAL will be a bit slower than letting the server access the files directly, because the contents of the file must travel from the client host to the server host. On the other hand, you do not need the FILE privilege to load local files.

If you are using MySQL before Version 3.23.24 you can't read from a FIFO with LOAD DATA INFILE. If you need to read from a FIFO (for example the output from gunzip), use LOAD DATA LOCAL INFILE instead.

You can also load datafiles by using the mysqlimport utility; it operates by sending a LOAD DATA INFILE command to the server. The --local option causes mysqlimport to read datafiles from the client host. You can specify the --compress option to get better performance over slow networks if the client and server support the compressed protocol.

When locating files on the server host, the server uses the following rules:

Note that these rules mean a file given as `./myfile.txt' is read from the server's data directory, whereas a file given as `myfile.txt' is read from the database directory of the current database. For example, the following LOAD DATA statement reads the file `data.txt' from the database directory for db1 because db1 is the current database, even though the statement explicitly loads the file into a table in the db2 database: 

mysql> USE db1;
mysql> LOAD DATA INFILE "data.txt" INTO TABLE db2.my_table;

The REPLACE and IGNORE keywords control handling of input records that duplicate existing records on unique key values.

If you specify REPLACE, new rows replace existing rows (in other words rows that has the same value for a primary or unique index as an existing row). See section 6.4.8 REPLACE Syntax.

If you specify IGNORE, input rows that duplicate an existing row on a unique key value are skipped. If you don't specify either option, an error occurs when a duplicate key value is found, and the rest of the text file is ignored.

If you want to ignore foreign key constraints during load you can do SET FOREIGN_KEY_CHECKS=0 before executing LOAD DATA.

If you load data from a local file using the LOCAL keyword, the server has no way to stop transmission of the file in the middle of the operation, so the default behaviour is the same as if IGNORE is specified.

If you use LOAD DATA INFILE on an empty MyISAM table, all non-unique indexes are created in a separate batch (like in REPAIR). This normally makes LOAD DATA INFILE much faster when you have many indexes. Normally this is very fast, but in some extreme cases you can create the indexes even faster by turning them off with ALTER TABLE .. DISABLE KEYS and use ALTER TABLE .. ENABLE KEYS to recreate the indexes. See section 4.4.6 Using myisamchk for Table Maintenance and Crash Recovery.

LOAD DATA INFILE is the complement of SELECT ... INTO OUTFILE. See section 6.4.1 SELECT Syntax. To write data from a database to a file, use SELECT ... INTO OUTFILE. To read the file back into the database, use LOAD DATA INFILE. The syntax of the FIELDS and LINES clauses is the same for both commands. Both clauses are optional, but FIELDS must precede LINES if both are specified.

If you specify a FIELDS clause, each of its subclauses (TERMINATED BY, [OPTIONALLY] ENCLOSED BY, and ESCAPED BY) is also optional, except that you must specify at least one of them.

If you don't specify a FIELDS clause, the defaults are the same as if you had written this: 

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

If you don't specify a LINES clause, the default is the same as if you had written this: 

LINES TERMINATED BY '\n'

Note: If you have generated the text file on a Windows system you may have to change the above to: LINES TERMINATED BY '\r\n' as Windows uses two characters as a line terminator. Some programs, like wordpad, may use \r as a line terminator.

If all the lines you want to read in has a common prefix that you want to skip, you can use LINES STARTING BY prefix_string for this.

In other words, the defaults cause LOAD DATA INFILE to act as follows when reading input:

Conversely, the defaults cause SELECT ... INTO OUTFILE to act as follows when writing output:

Note that to write FIELDS ESCAPED BY '\\', you must specify two backslashes for the value to be read as a single backslash.

The IGNORE number LINES option can be used to ignore a header of column names at the start of the file: 

mysql> LOAD DATA INFILE "/tmp/file_name" INTO TABLE test IGNORE 1 LINES;

When you use SELECT ... INTO OUTFILE in tandem with LOAD DATA INFILE to write data from a database into a file and then read the file back into the database later, the field and line handling options for both commands must match. Otherwise, LOAD DATA INFILE will not interpret the contents of the file properly. Suppose you use SELECT ... INTO OUTFILE to write a file with fields delimited by commas: 

mysql> SELECT * INTO OUTFILE 'data.txt'
    ->          FIELDS TERMINATED BY ','
    ->          FROM ...;

To read the comma-delimited file back in, the correct statement would be: 

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
    ->           FIELDS TERMINATED BY ',';

If instead you tried to read in the file with the statement shown here, it wouldn't work because it instructs LOAD DATA INFILE to look for tabs between fields: 

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
    ->           FIELDS TERMINATED BY '\t';

The likely result is that each input line would be interpreted as a single field.

LOAD DATA INFILE can be used to read files obtained from external sources, too. For example, a file in dBASE format will have fields separated by commas and enclosed in double quotes. If lines in the file are terminated by newlines, the command shown here illustrates the field and line handling options you would use to load the file: 

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
    ->           FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    ->           LINES TERMINATED BY '\n';

Any of the field or line handling options may specify an empty string (''). If not empty, the FIELDS [OPTIONALLY] ENCLOSED BY and FIELDS ESCAPED BY values must be a single character. The FIELDS TERMINATED BY and LINES TERMINATED BY values may be more than one character. For example, to write lines that are terminated by carriage return-linefeed pairs, or to read a file containing such lines, specify a LINES TERMINATED BY '\r\n' clause.

For example, to read a file of jokes, that are separated with a line of %%, into an SQL table you can do: 

CREATE TABLE jokes (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, joke TEXT
NOT NULL);
LOAD DATA INFILE "/tmp/jokes.txt" INTO TABLE jokes FIELDS TERMINATED BY ""
LINES TERMINATED BY "\n%%\n" (joke);

FIELDS [OPTIONALLY] ENCLOSED BY controls quoting of fields. For output (SELECT ... INTO OUTFILE), if you omit the word OPTIONALLY, all fields are enclosed by the ENCLOSED BY character. An example of such output (using a comma as the field delimiter) is shown here: 

"1","a string","100.20"
"2","a string containing a , comma","102.20"
"3","a string containing a \" quote","102.20"
"4","a string containing a \", quote and comma","102.20"

If you specify OPTIONALLY, the ENCLOSED BY character is used only to enclose CHAR and VARCHAR fields: 

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a \" quote",102.20
4,"a string containing a \", quote and comma",102.20

Note that occurrences of the ENCLOSED BY character within a field value are escaped by prefixing them with the ESCAPED BY character. Also note that if you specify an empty ESCAPED BY value, it is possible to generate output that cannot be read properly by LOAD DATA INFILE. For example, the output just shown above would appear as shown here if the escape character is empty. Observe that the second field in the fourth line contains a comma following the quote, which (erroneously) appears to terminate the field: 

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a " quote",102.20
4,"a string containing a ", quote and comma",102.20

For input, the ENCLOSED BY character, if present, is stripped from the ends of field values. (This is true whether OPTIONALLY is specified; OPTIONALLY has no effect on input interpretation.) Occurrences of the ENCLOSED BY character preceded by the ESCAPED BY character are interpreted as part of the current field value. In addition, duplicated ENCLOSED BY characters occurring within fields are interpreted as single ENCLOSED BY characters if the field itself starts with that character. For example, if ENCLOSED BY '"' is specified, quotes are handled as shown here: 

"The ""BIG"" boss"  -> The "BIG" boss
The "BIG" boss      -> The "BIG" boss
The ""BIG"" boss    -> The ""BIG"" boss

FIELDS ESCAPED BY controls how to write or read special characters. If the FIELDS ESCAPED BY character is not empty, it is used to prefix the following characters on output:

If the FIELDS ESCAPED BY character is empty, no characters are escaped. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the list just given.

For input, if the FIELDS ESCAPED BY character is not empty, occurrences of that character are stripped and the following character is taken literally as part of a field value. The exceptions are an escaped `0' or `N' (for example, \0 or \N if the escape character is `\'). These sequences are interpreted as ASCII 0 (a zero-valued byte) and NULL. See below for the rules on NULL handling.

For more information about `\'-escape syntax, see section 6.1.1 Literals: How to Write Strings and Numbers.

In certain cases, field and line handling options interact:

Handling of NULL values varies, depending on the FIELDS and LINES options you use:

Some cases are not supported by LOAD DATA INFILE:

The following example loads all columns of the persondata table: 

mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

No field list is specified, so LOAD DATA INFILE expects input rows to contain a field for each table column. The default FIELDS and LINES values are used.

If you wish to load only some of a table's columns, specify a field list: 

mysql> LOAD DATA INFILE 'persondata.txt'
    ->           INTO TABLE persondata (col1,col2,...);

You must also specify a field list if the order of the fields in the input file differs from the order of the columns in the table. Otherwise, MySQL cannot tell how to match up input fields with table columns.

If a row has too few fields, the columns for which no input field is present are set to default values. Default value assignment is described in section 6.5.3 CREATE TABLE Syntax.

An empty field value is interpreted differently than if the field value is missing:

Note that these are the same values that result if you assign an empty string explicitly to a string, numeric, or date or time type explicitly in an INSERT or UPDATE statement.

TIMESTAMP columns are only set to the current date and time if there is a NULL value for the column, or (for the first TIMESTAMP column only) if the TIMESTAMP column is left out from the field list when a field list is specified.

If an input row has too many fields, the extra fields are ignored and the number of warnings is incremented. Note that before MySQL 4.1.1 the warnings is just a number to indicate that something went wrong. In MySQL 4.1.1 you can do SHOW WARNINGS to get more information for what went wrong.

LOAD DATA INFILE regards all input as strings, so you can't use numeric values for ENUM or SET columns the way you can with INSERT statements. All ENUM and SET values must be specified as strings!

If you are using the C API, you can get information about the query by calling the API function mysql_info() when the LOAD DATA INFILE query finishes. The format of the information string is shown here: 

Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

Warnings occur under the same circumstances as when values are inserted via the INSERT statement (see section 6.4.3 INSERT Syntax), except that LOAD DATA INFILE also generates warnings when there are too few or too many fields in the input row. The warnings are not stored anywhere; the number of warnings can only be used as an indication if everything went well.

If you get warnings and want to know exactly why you got them, one way to do this is to use SELECT ... INTO OUTFILE into another file and compare this to your original input file.

If you need LOAD DATA to read from a pipe, you can use the following trick: 

mkfifo /mysql/db/x/x
chmod 666 /mysql/db/x/x
cat < /dev/tcp/10.1.1.12/4711 > /nt/mysql/db/x/x
mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x

If you are using a version of MySQL older than 3.23.25 you can only do the above with LOAD DATA LOCAL INFILE.

In MySQL 4.1.1 you can use SHOW WARNINGS to get a list of the first max_error_count warnings. See section 4.5.7.9 SHOW WARNINGS | ERRORS.

For more information about the efficiency of INSERT versus LOAD DATA INFILE and speeding up LOAD DATA INFILE, See section 5.2.10 Speed of INSERT Queries.

6.4.10 DO Syntax

DO expression, [expression, ...]

Execute the expression but don't return any results. This is a shorthand of SELECT expression, expression, but has the advantage that it's slightly faster when you don't care about the result.

This is mainly useful with functions that has side effects, like RELEASE_LOCK.

6.5 Data Definition: CREATE, DROP, ALTER

6.5.1 CREATE DATABASE Syntax

CREATE DATABASE [IF NOT EXISTS] db_name

CREATE DATABASE creates a database with the given name.

Rules for allowable database names are given in section 6.1.2 Database, Table, Index, Column, and Alias Names. An error occurs if the database already exists and you didn't specify IF NOT EXISTS.

Databases in MySQL are implemented as directories containing files that correspond to tables in the database. Because there are no tables in a database when it is initially created, the CREATE DATABASE statement only creates a directory under the MySQL data directory.

You can also create databases with mysqladmin. See section 4.8 MySQL Client-Side Scripts and Utilities.

6.5.2 DROP DATABASE Syntax

DROP DATABASE [IF EXISTS] db_name

DROP DATABASE drops all tables in the database and deletes the database. If you do a DROP DATABASE on a symbolic linked database, both the link and the original database is deleted. Be VERY careful with this command!

DROP DATABASE returns the number of files that were removed from the database directory. Normally, this is three times the number of tables, because normally each table corresponds to a `.MYD' file, a `.MYI' file, and a `.frm' file.

The DROP DATABASE command removes from the given database directory all files with the following extensions:

Ext Ext Ext Ext
.BAK .DAT .HSH .ISD
.ISM .ISM .MRG .MYD
.MYI .db .frm

All subdirectories that consists of 2 digits (RAID directories) are also removed.

In MySQL Version 3.22 or later, you can use the keywords IF EXISTS to prevent an error from occurring if the database doesn't exist.

You can also drop databases with mysqladmin. See section 4.8 MySQL Client-Side Scripts and Utilities.

6.5.3 CREATE TABLE Syntax

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]

or

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (LIKE old_table_name);

create_definition:
    col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
            [PRIMARY KEY] [reference_definition]
  | PRIMARY KEY (index_col_name,...)
  | KEY [index_name] (index_col_name,...)
  | INDEX [index_name] (index_col_name,...)
  | UNIQUE [INDEX] [index_name] (index_col_name,...)
  | FULLTEXT [INDEX] [index_name] (index_col_name,...)
  | [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
            [reference_definition]
  | CHECK (expr)

type:
    TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  | INT[(length)] [UNSIGNED] [ZEROFILL]
  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
  | NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
  | CHAR(length) [BINARY]
  | VARCHAR(length) [BINARY]
  | DATE
  | TIME
  | TIMESTAMP
  | DATETIME
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT
  | TEXT
  | MEDIUMTEXT
  | LONGTEXT
  | ENUM(value1,value2,value3,...)
  | SET(value1,value2,value3,...)

index_col_name:
        col_name [(length)]

reference_definition:
        REFERENCES tbl_name [(index_col_name,...)]
                   [MATCH FULL | MATCH PARTIAL]
                   [ON DELETE reference_option]
                   [ON UPDATE reference_option]

reference_option:
        RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options: table_option [table_option] ...

table_option:
    TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM }
  | AUTO_INCREMENT = #
  | AVG_ROW_LENGTH = #
  | CHECKSUM = {0 | 1}
  | COMMENT = "string"
  | MAX_ROWS = #
  | MIN_ROWS = #
  | PACK_KEYS = {0 | 1 | DEFAULT}
  | PASSWORD = "string"
  | DELAY_KEY_WRITE = {0 | 1}
  | ROW_FORMAT= { default | dynamic | fixed | compressed }
  | RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=#  RAID_CHUNKSIZE=#
  | UNION = (table_name,[table_name...])
  | INSERT_METHOD= {NO | FIRST | LAST }
  | DATA DIRECTORY="absolute path to directory"
  | INDEX DIRECTORY="absolute path to directory"

select_statement:
        [IGNORE | REPLACE] SELECT ...  (Some legal select statement)

CREATE TABLE creates a table with the given name in the current database.

Rules for allowable table names are given in section 6.1.2 Database, Table, Index, Column, and Alias Names. An error occurs if there is no current database or if the table already exists.

In MySQL Version 3.22 or later, the table name can be specified as db_name.tbl_name. This works regardless of whether there is a current database.

From MySQL Version 3.23, you can use the TEMPORARY keyword when you create a table. The name is restricted to the current connection, and the temporary table will automatically be deleted when the connection is closed. This means that two different connections can both use the same temporary table name without conflicting with each other or with an existing table of the same name. (The existing table is hidden until the temporary table is deleted.). From MySQL 4.0.2 one must have the CREATE TEMPORARY TABLES privilege to be able to create temporary tables.

In MySQL Version 3.23 or later, you can use the keywords IF NOT EXISTS so that an error does not occur if the table already exists. Note that there is no verification that the table structures are identical.

In MySQL 4.1 you can use LIKE to create a table based on a table definition in another table. In MySQL 4.1 you can also specify the type for a generated column: 

CREATE TABLE foo (a tinyint not null) SELECT b+1 AS 'a' FROM bar;

Each table tbl_name is represented by some files in the database directory. In the case of MyISAM-type tables you will get:

File Purpose
tbl_name.frm Table definition (form) file
tbl_name.MYD Datafile
tbl_name.MYI Index file

For more information on the properties of the various column types, see section 6.2 Column Types:

6.5.3.1 Silent Column Specification Changes

In some cases, MySQL silently changes a column specification from that given in a CREATE TABLE statement. (This may also occur with ALTER TABLE.):

If you want to see whether MySQL used a column type other than the one you specified, issue a DESCRIBE tbl_name statement after creating or altering your table.

Certain other column type changes may occur if you compress a table using myisampack. See section 7.1.2.3 Compressed Table Characteristics.

6.5.4 ALTER TABLE Syntax

ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification ...]

alter_specification:
    ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
  | ADD [COLUMN] (create_definition, create_definition,...)
  | ADD INDEX [index_name] (index_col_name,...)
  | ADD PRIMARY KEY (index_col_name,...)
  | ADD UNIQUE [index_name] (index_col_name,...)
  | ADD FULLTEXT [index_name] (index_col_name,...)
  | ADD [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
           [reference_definition]
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name create_definition
           [FIRST | AFTER column_name]
  | MODIFY [COLUMN] create_definition [FIRST | AFTER column_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP INDEX index_name
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO] new_tbl_name
  | ORDER BY col
  | table_options

ALTER TABLE allows you to change the structure of an existing table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. You can also change the comment for the table and type of the table. See section 6.5.3 CREATE TABLE Syntax.

If you use ALTER TABLE to change a column specification but DESCRIBE tbl_name indicates that your column was not changed, it is possible that MySQL ignored your modification for one of the reasons described in section 6.5.3.1 Silent Column Specification Changes. For example, if you try to change a VARCHAR column to CHAR, MySQL will still use VARCHAR if the table contains other variable-length columns.

ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, then the original table is deleted and the new one is renamed. This is done in such a way that all updates are automatically redirected to the new table without any failed updates. While ALTER TABLE is executing, the original table is readable by other clients. Updates and writes to the table are stalled until the new table is ready.

Note that if you use any other option to ALTER TABLE than RENAME, MySQL will always create a temporary table, even if the data wouldn't strictly need to be copied (like when you change the name of a column). We plan to fix this in the future, but as one doesn't normally do ALTER TABLE that often this isn't that high on our TODO. For MyISAM tables, you can speed up the index recreation part (which is the slowest part of the recreation process) by setting the myisam_sort_buffer_size variable to a high value.

Here is an example that shows some of the uses of ALTER TABLE. We begin with a table t1 that is created as shown here: 

mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));

To rename the table from t1 to t2: 

mysql> ALTER TABLE t1 RENAME t2;

To change column a from INTEGER to TINYINT NOT NULL (leaving the name the same), and to change column b from CHAR(10) to CHAR(20) as well as renaming it from b to c: 

mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

To add a new TIMESTAMP column named d: 

mysql> ALTER TABLE t2 ADD d TIMESTAMP;

To add an index on column d, and make column a the primary key: 

mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

To remove column c: 

mysql> ALTER TABLE t2 DROP COLUMN c;

To add a new AUTO_INCREMENT integer column named c: 

mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
           ADD INDEX (c);

Note that we indexed c, because AUTO_INCREMENT columns must be indexed, and also that we declare c as NOT NULL, because indexed columns cannot be NULL.

When you add an AUTO_INCREMENT column, column values are filled in with sequence numbers for you automatically. You can set the first sequence number by executing SET INSERT_ID=# before ALTER TABLE or using the AUTO_INCREMENT = # table option. See section 5.5.6 SET Syntax.

With MyISAM tables, if you don't change the AUTO_INCREMENT column, the sequence number will not be affected. If you drop an AUTO_INCREMENT column and then add another AUTO_INCREMENT column, the numbers will start from 1 again.

See section A.7.1 Problems with ALTER TABLE..

6.5.5 RENAME TABLE Syntax

RENAME TABLE tbl_name TO new_tbl_name[, tbl_name2 TO new_tbl_name2,...]

The rename is done atomically, which means that no other thread can access any of the tables while the rename is running. This makes it possible to replace a table with an empty one : 

CREATE TABLE new_table (...);
RENAME TABLE old_table TO backup_table, new_table TO old_table;

The rename is done from left to right, which means that if you want to swap two tables names, you have to: 

RENAME TABLE old_table    TO backup_table,
             new_table    TO old_table,
             backup_table TO new_table;

As long as two databases are on the same disk you can also rename from one database to another: 

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

When you execute RENAME, you can't have any locked tables or active transactions. You must also have the ALTER and DROP privileges on the original table, and the CREATE and INSERT privileges on the new table.

If MySQL encounters any errors in a multiple-table rename, it will do a reverse rename for all renamed tables to get everything back to the original state.

RENAME TABLE was added in MySQL 3.23.23.

6.5.6 DROP TABLE Syntax

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name,...] [RESTRICT | CASCADE]

DROP TABLE removes one or more tables. All table data and the table definition are removed, so be careful with this command!

In MySQL Version 3.22 or later, you can use the keywords IF EXISTS to prevent an error from occurring for tables that don't exist. In 4.1 one gets a NOTE for all not existing tables when using IF EXISTS. See section 4.5.7.9 SHOW WARNINGS | ERRORS.

RESTRICT and CASCADE are allowed to make porting easier. For the moment they don't do anything.

Note: DROP TABLE will automatically commit current active transaction (except if you are using 4.1 and the TEMPORARY key word.

Option TEMPORARY is ignored in 4.0. In 4.1 this option works as follows:

Using TEMPORARY is a good way to ensure that you don't accidently drop a real table.

6.5.7 CREATE INDEX Syntax

CREATE [UNIQUE|FULLTEXT] INDEX index_name
       ON tbl_name (col_name[(length)],... )

The CREATE INDEX statement doesn't do anything in MySQL prior to Version 3.22. In Version 3.22 or later, CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. See section 6.5.4 ALTER TABLE Syntax.

Normally, you create all indexes on a table at the time the table itself is created with CREATE TABLE. See section 6.5.3 CREATE TABLE Syntax. CREATE INDEX allows you to add indexes to existing tables.

A column list of the form (col1,col2,...) creates a multiple-column index. Index values are formed by concatenating the values of the given columns.

For CHAR and VARCHAR columns, indexes can be created that use only part of a column, using col_name(length) syntax. (On BLOB and TEXT columns the length is required.) The statement shown here creates an index using the first 10 characters of the name column: 

mysql> CREATE INDEX part_of_name ON customer (name(10));

Because most names usually differ in the first 10 characters, this index should not be much slower than an index created from the entire name column. Also, using partial columns for indexes can make the index file much smaller, which could save a lot of disk space and might also speed up INSERT operations!

Note that you can only add an index on a column that can have NULL values or on a BLOB/TEXT column if you are using MySQL Version 3.23.2 or newer and are using the MyISAM table type.

For more information about how MySQL uses indexes, see section 5.4.3 How MySQL Uses Indexes.

FULLTEXT indexes can index only VARCHAR and TEXT columns, and only in MyISAM tables. FULLTEXT indexes are available in MySQL Version 3.23.23 and later. section 6.8 MySQL Full-text Search.

6.5.8 DROP INDEX Syntax

DROP INDEX index_name ON tbl_name

DROP INDEX drops the index named index_name from the table tbl_name. DROP INDEX doesn't do anything in MySQL prior to Version 3.22. In Version 3.22 or later, DROP INDEX is mapped to an ALTER TABLE statement to drop the index. See section 6.5.4 ALTER TABLE Syntax.

6.6 Basic MySQL User Utility Commands

6.6.1 USE Syntax

USE db_name

The USE db_name statement tells MySQL to use the db_name database as the default database for subsequent queries. The database remains current until the end of the session or until another USE statement is issued: 

mysql> USE db1;
mysql> SELECT COUNT(*) FROM mytable;      # selects from db1.mytable
mysql> USE db2;
mysql> SELECT COUNT(*) FROM mytable;      # selects from db2.mytable

Making a particular database current by means of the USE statement does not preclude you from accessing tables in other databases. The following example accesses the author table from the db1 database and the editor table from the db2 database: 

mysql> USE db1;
mysql> SELECT author_name,editor_name FROM author,db2.editor
    ->        WHERE author.editor_id = db2.editor.editor_id;

The USE statement is provided for Sybase compatibility.

6.6.2 DESCRIBE Syntax (Get Information About Columns)

{DESCRIBE | DESC} tbl_name [col_name | wild]

DESCRIBE is a shortcut for SHOW COLUMNS FROM. See section 4.5.7.1 Retrieving information about Database, Tables, Columns, and Indexes.

DESCRIBE provides information about a table's columns. col_name may be a column name or a string containing the SQL `%' and `_' wildcard characters. There is no need to enclose the string in quotes.

If the column types are different from what you expect them to be based on a CREATE TABLE statement, note that MySQL sometimes changes column types. See section 6.5.3.1 Silent Column Specification Changes.

This statement is provided for Oracle compatibility.

The SHOW statement provides similar information. See section 4.5.7 SHOW Syntax.

6.7 MySQL Transactional and Locking Commands

6.7.1 BEGIN/COMMIT/ROLLBACK Syntax

By default, MySQL runs in autocommit mode. This means that as soon as you execute an update, MySQL will store the update on disk.

If you are using transactions safe tables (like InnoDB, BDB, you can put MySQL into non-autocommit mode with the following command: 

SET AUTOCOMMIT=0

After this you must use COMMIT to store your changes to disk or ROLLBACK if you want to ignore the changes you have made since the beginning of your transaction.

If you want to switch from AUTOCOMMIT mode for one series of statements, you can use the START TRANSACTION or BEGIN or BEGIN WORK statement : 

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summmary=@A WHERE type=1;
COMMIT;

START TRANSACTION was added to MySQL 4.0.11; This is the recommended way to start an ad-hoc transaction as this is SQL-99 syntax,

Note that if you are using non-transaction-safe tables, the changes will be stored at once, independent of the status of the autocommit mode.

If you do a ROLLBACK when you have updated a non-transactional table you will get an error (ER_WARNING_NOT_COMPLETE_ROLLBACK) as a warning. All transaction-safe tables will be restored but any non-transaction-safe table will not change.

If you are using START TRANSACTION or SET AUTOCOMMIT=0, you should use the MySQL binary log for backups instead of the older update log. Transactions are stored in the binary log in one chunk, upon COMMIT, to ensure that transactions which are rolled back are not stored. See section 4.9.4 The Binary Log.

The following commands automatically end a transaction (as if you had done a COMMIT before executing the command):

Command Command Command
ALTER TABLE BEGIN CREATE INDEX
DROP DATABASE DROP INDEX DROP TABLE
LOAD MASTER DATA LOCK TABLES RENAME TABLE
SET AUTOCOMMIT=1 TRUNCATE

UNLOCK TABLES also ends a transaction if any tables currently are locked.

You can change the isolation level for transactions with SET TRANSACTION ISOLATION LEVEL .... See section 6.7.3 SET TRANSACTION Syntax.

6.7.2 LOCK TABLES/UNLOCK TABLES Syntax

LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
            [, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} ...]
...
UNLOCK TABLES

LOCK TABLES locks tables for the current thread. UNLOCK TABLES releases any locks held by the current thread. All tables that are locked by the current thread are automatically unlocked when the thread issues another LOCK TABLES, or when the connection to the server is closed.

To use LOCK TABLES in MySQL 4.0.2 you need the global LOCK TABLES privilege and a SELECT privilege on the involved tables. In MySQL 3.23 you need to have SELECT, insert, DELETE and UPDATE privileges for the tables.

The main reasons to use LOCK TABLES are for emulating transactions or getting more speed when updating tables. This is explained in more detail later.

If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, then only the thread holding the lock can read from or write to the table. Other threads are blocked.

The difference between READ LOCAL and READ is that READ LOCAL allows non-conflicting INSERT statements to execute while the lock is held. This can't however be used if you are going to manipulate the database files outside MySQL while you hold the lock.

When you use LOCK TABLES, you must lock all tables that you are going to use and you must use the same alias that you are going to use in your queries! If you are using a table multiple times in a query (with aliases), you must get a lock for each alias!

WRITE locks normally have higher priority than READ locks, to ensure that updates are processed as soon as possible. This means that if one thread obtains a READ lock and then another thread requests a WRITE lock, subsequent READ lock requests will wait until the WRITE thread has gotten the lock and released it. You can use LOW_PRIORITY WRITE locks to allow other threads to obtain READ locks while the thread is waiting for the WRITE lock. You should only use LOW_PRIORITY WRITE locks if you are sure that there will eventually be a time when no threads will have a READ lock.

LOCK TABLES works as follows:

  1. Sort all tables to be locked in a internally defined order (from the user standpoint the order is undefined).
  2. If a table is locked with a read and a write lock, put the write lock before the read lock.
  3. Lock one table at a time until the thread gets all locks.

This policy ensures that table locking is deadlock free. There is however other things one needs to be aware of with this schema:

If you are using a LOW_PRIORITY WRITE lock for a table, this means only that MySQL will wait for this particlar lock until there is no threads that wants a READ lock. When the thread has got the WRITE lock and is waiting to get the lock for the next table in the lock table list, all other threads will wait for the WRITE lock to be released. If this becomes a serious problem with your application, you should consider converting some of your tables to transactions safe tables.

You can safely kill a thread that is waiting for a table lock with KILL. See section 4.5.6 KILL Syntax.

Note that you should not lock any tables that you are using with INSERT DELAYED. This is because that in this case the INSERT is done by a separate thread.

Normally, you don't have to lock tables, as all single UPDATE statements are atomic; no other thread can interfere with any other currently executing SQL statement. There are a few cases when you would like to lock tables anyway:

By using incremental updates (UPDATE customer SET value=value+new_value) or the LAST_INSERT_ID() function, you can avoid using LOCK TABLES in many cases.

You can also solve some cases by using the user-level lock functions GET_LOCK() and RELEASE_LOCK(). These locks are saved in a hash table in the server and implemented with pthread_mutex_lock() and pthread_mutex_unlock() for high speed. See section 6.3.6.2 Miscellaneous Functions.

See section 5.3.1 How MySQL Locks Tables, for more information on locking policy.

You can lock all tables in all databases with read locks with the FLUSH TABLES WITH READ LOCK command. See section 4.5.3 FLUSH Syntax. This is very convenient way to get backups if you have a filesystem, like Veritas, that can take snapshots in time.

NOTE: LOCK TABLES is not transaction-safe and will automatically commit any active transactions before attempting to lock the tables.

6.7.3 SET TRANSACTION Syntax

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

Sets the transaction isolation level for the global, whole session or the next transaction.

The default behaviour is to set the isolation level for the next (not started) transaction. If you use the GLOBAL keyword, the statement sets the default transaction level globally for all new connections created from that point on. You will need the SUPER privilege to do this. Using the SESSION keyword sets the default transaction level for all future transactions performed on the current connection.

You can set the default global isolation level for mysqld with --transaction-isolation=.... See section 4.1.1 mysqld Command-line Options.

6.8 MySQL Full-text Search

As of Version 3.23.23, MySQL has support for full-text indexing and searching. Full-text indexes in MySQL are an index of type FULLTEXT. FULLTEXT indexes are used with MyISAM tables and can be created from CHAR, VARCHAR, or TEXT columns at CREATE TABLE time or added later with ALTER TABLE or CREATE INDEX. For large datasets, it will be much faster to load your data into a table that has no FULLTEXT index, then create the index with ALTER TABLE (or CREATE INDEX). Loading data into a table that already has a FULLTEXT index will be slower.

Full-text searching is performed with the MATCH() function. 

mysql> CREATE TABLE articles (
    ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->   title VARCHAR(200),
    ->   body TEXT,
    ->   FULLTEXT (title,body)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO articles VALUES
    -> (NULL,'MySQL Tutorial', 'DBMS stands for DataBase ...'),
    -> (NULL,'How To Use MySQL Efficiently', 'After you went through a ...'),
    -> (NULL,'Optimising MySQL','In this tutorial we will show ...'),
    -> (NULL,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    -> (NULL,'MySQL vs. YourSQL', 'In the following database comparison ...'),
    -> (NULL,'MySQL Security', 'When configured properly, MySQL ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM articles
    ->          WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

The MATCH() function performs a natural language search for a string against a text collection (a set of one or more columns included in a FULLTEXT index). The search string is given as the argument to AGAINST(). The search is performed in case-insensitive fashion. For every row in the table, MATCH() returns a relevance value, that is, a similarity measure between the search string and the text in that row in the columns named in the MATCH() list.

When MATCH() is used in a WHERE clause (see example above) the rows returned are automatically sorted with highest relevance first. Relevance values are non-negative floating-point numbers. Zero relevance means no similarity. Relevance is computed based on the number of words in the row, the number of unique words in that row, the total number of words in the collection, and the number of documents (rows) that contain a particular word.

It is also possible to perform a boolean mode search. This is explained later in the section.

The preceding example is a basic illustration showing how to use the MATCH() function. Rows are returned in order of decreasing relevance.

The next example shows how to retrieve the relevance values explicitly. As neither WHERE nor ORDER BY clauses are present, returned rows are not ordered. 

mysql> SELECT id,MATCH (title,body) AGAINST ('Tutorial') FROM articles;
+----+-----------------------------------------+
| id | MATCH (title,body) AGAINST ('Tutorial') |
+----+-----------------------------------------+
|  1 |                        0.64840710366884 |
|  2 |                                       0 |
|  3 |                        0.66266459031789 |
|  4 |                                       0 |
|  5 |                                       0 |
|  6 |                                       0 |
+----+-----------------------------------------+
6 rows in set (0.00 sec)

The following example is more complex. The query returns the relevance and still sorts the rows in order of decreasing relevance. To achieve this result, you should specify MATCH() twice. This will cause no additional overhead, because the MySQL optimiser will notice that the two MATCH() calls are identical and invoke the full-text search code only once. 

mysql> SELECT id, body, MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root') AS score
    -> FROM articles WHERE MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root');
+----+-------------------------------------+-----------------+
| id | body                                | score           |
+----+-------------------------------------+-----------------+
|  4 | 1. Never run mysqld as root. 2. ... | 1.5055546709332 |
|  6 | When configured properly, MySQL ... |   1.31140957288 |
+----+-------------------------------------+-----------------+
2 rows in set (0.00 sec)

MySQL uses a very simple parser to split text into words. A ``word'' is any sequence of characters consisting of letters, digits, `'', and `_'. Any ``word'' that is present in the stopword list or is just too short (3 characters or less) is ignored.

Every correct word in the collection and in the query is weighted according to its significance in the query or collection. This way, a word that is present in many documents will have lower weight (and may even have a zero weight), because it has lower semantic value in this particular collection. Otherwise, if the word is rare, it will receive a higher weight. The weights of the words are then combined to compute the relevance of the row.

Such a technique works best with large collections (in fact, it was carefully tuned this way). For very small tables, word distribution does not reflect adequately their semantic value, and this model may sometimes produce bizarre results. 

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL');
Empty set (0.00 sec)

The search for the word MySQL produces no results in the above example, because that word is present in more than half the rows. As such, it is effectively treated as a stopword (that is, a word with zero semantic value). This is the most desirable behaviour -- a natural language query should not return every second row from a 1 GB table.

A word that matches half of rows in a table is less likely to locate relevant documents. In fact, it will most likely find plenty of irrelevant documents. We all know this happens far too often when we are trying to find something on the Internet with a search engine. It is with this reasoning that such rows have been assigned a low semantic value in this particular dataset.

As of Version 4.0.1, MySQL can also perform boolean full-text searches using the IN BOOLEAN MODE modifier. 

mysql> SELECT * FROM articles WHERE MATCH (title,body)
    ->     AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
+----+------------------------------+-------------------------------------+
| id | title                        | body                                |
+----+------------------------------+-------------------------------------+
|  1 | MySQL Tutorial               | DBMS stands for DataBase ...        |
|  2 | How To Use MySQL Efficiently | After you went through a ...        |
|  3 | Optimising MySQL             | In this tutorial we will show ...   |
|  4 | 1001 MySQL Tricks            | 1. Never run mysqld as root. 2. ... |
|  6 | MySQL Security               | When configured properly, MySQL ... |
+----+------------------------------+-------------------------------------+

This query retrieved all the rows that contain the word MySQL (note: the 50% threshold is not used), but that do not contain the word YourSQL. Note that a boolean mode search does not automatically sort rows in order of decreasing relevance. You can see this from result of the preceding query, where the row with the highest relevance (the one that contains MySQL twice) is listed last, not first. A boolean full-text search can also work even without a FULLTEXT index, although it would be slow.

The boolean full-text search capability supports the following operators:

+
A leading plus sign indicates that this word must be present in every row returned.
-
A leading minus sign indicates that this word must not be present in any row returned.
By default (when neither plus nor minus is specified) the word is optional, but the rows that contain it will be rated higher. This mimicks the behaviour of MATCH() ... AGAINST() without the IN BOOLEAN MODE modifier.
< >
These two operators are used to change a word's contribution to the relevance value that is assigned to a row. The < operator decreases the contribution and the > operator increases it. See the example below.
( )
Parentheses are used to group words into subexpressions.
~
A leading tilde acts as a negation operator, causing the word's contribution to the row relevance to be negative. It's useful for marking noise words. A row that contains such a word will be rated lower than others, but will not be excluded altogether, as it would be with the - operator.
*
An asterisk is the truncation operator. Unlike the other operators, it should be appended to the word, not prepended.
"
The phrase, that is enclosed in double quotes ", matches only rows that contain this phrase literally, as it was typed.

And here are some examples:

apple banana
find rows that contain at least one of these words.
+apple +juice
... both words.
+apple macintosh
... word ``apple'', but rank it higher if it also contain ``macintosh''.
+apple -macintosh
... word ``apple'' but not ``macintosh''.
+apple +(>pie <strudel)
... ``apple'' and ``pie'', or ``apple'' and ``strudel'' (in any order), but rank ``apple pie'' higher than ``apple strudel''.
apple*
... ``apple'', ``apples'', ``applesauce'', and ``applet''.
"some words"
... ``some words of wisdom'', but not ``some noise words''.

6.8.1 Full-text Restrictions

6.8.2 Fine-tuning MySQL Full-text Search

Unfortunately, full-text search has few user-tunable parameters yet, although adding some is very high on the TODO. If you have a MySQL source distribution (see section 2.3 Installing a MySQL Source Distribution), you can exert more control over full-text searching behaviour.

Note that full-text search was carefully tuned for the best searching effectiveness. Modifying the default behaviour will, in most cases, only make the search results worse. Do not alter the MySQL sources unless you know what you are doing!

The full-text variables described in the following list must be set at server startup time. You cannot modify them dynamically while the server is running.

For those changes that require you to rebuild your FULLTEXT indexes, the easiest way to do so for a MyISAM table is to use the following statement, which rebuilds the index file: 

mysql> REPAIR TABLE tbl_name QUICK;

6.8.3 Full-text Search TODO

6.9 MySQL Query Cache

From version 4.0.1, MySQL server features a Query Cache. When in use, the query cache stores the text of a SELECT query together with the corresponding result that was sent to the client. If an identical query is later received, the server will retrieve the results from the query cache rather than parsing and executing the same query again.

NOTE: The query cache does not return stale data. When data is modified, any relevant entries in the query cache are flushed.

The query cache is extremely useful in an environment where (some) tables don't change very often and you have a lot of identical queries. This is a typical situation for many web servers that use a lot of dynamic content.

Below is some performance data for the query cache. (These results were generated by running the MySQL benchmark suite on a Linux Alpha 2 x 500 MHz with 2 GB RAM and a 64 MB query cache):

6.9.1 How The Query Cache Operates

Queries are compared before parsing, thus 

SELECT * FROM tbl_name

and 

Select * from tbl_name

are regarded as different queries for query cache, so queries need to be exactly the same (byte for byte) to be seen as identical. In addition, a query may be seen as different if for instance one client is using a new communication protocol format or another character set than another client.

Queries that uses different databases, uses different protocol versions or the uses different default character sets are considered different queries and cached separately.

The cache does work for SELECT SQL_CALC_FOUND_ROWS ... and SELECT FOUND_ROWS() ... type queries because the number of found rows is also stored in the cache.

If query result was returned from query cache then status variable Com_select will not be increased, but Qcache_hits will be. See section 6.9.4 Query Cache Status and Maintenance.

If a table changes (INSERT, UPDATE, DELETE, TRUNCATE, ALTER or DROP TABLE|DATABASE), then all cached queries that used this table (possibly through a MRG_MyISAM table!) become invalid and are removed from the cache.

Transactional InnoDB tables that have been changed will be invalidated when a COMMIT is performed.

In MySQL 4.0, the query cache is disabled inside of transactions (it does not return results). Beginning with MySQL 4.1.1, the query cache will also work inside of transactions when using InnoDB tables (it will use the table version number to detect if the data is still current or not).

A query cannot be cached if it contains one of the functions:
Function Function Function
User-Defined Functions CONNECTION_ID FOUND_ROWS
GET_LOCK RELEASE_LOCK LOAD_FILE
MASTER_POS_WAIT NOW SYSDATE
CURRENT_TIMESTAMP CURDATE CURRENT_DATE
CURTIME CURRENT_TIME DATABASE
ENCRYPT (with one parameter) LAST_INSERT_ID RAND
UNIX_TIMESTAMP (without parameters) USER BENCHMARK

Nor can a query be cached if it contains user variables, references the mysql system database, is of the form SELECT ... IN SHARE MODE, SELECT ... INTO OUTFILE ..., SELECT ... INTO DUMPFILE ... or of the form SELECT * FROM AUTOINCREMENT_FIELD IS NULL (to retrieve last insert id - ODBC work around).

However, FOUND_ROWS() will return the correct value, even if the preceding query was fetched from the cache.

In case a query does not use any tables, or uses temporary tables, or if the user has a column privilege for any of the involved tables, that query will not be cached.

Before a query is fetched from the query cache, MySQL will check that the user has SELECT privilege to all the involved databases and tables. If this is not the case, the cached result will not be used.

6.9.2 Query Cache Configuration

The query cache adds a few MySQL system variables for mysqld which may be set in a configuration file, on the command-line when starting mysqld.

Inside a thread (connection), the behaviour of the query cache can be changed from the default. The syntax is as follows:

QUERY_CACHE_TYPE = OFF | ON | DEMAND QUERY_CACHE_TYPE = 0 | 1 | 2

Option Description
0 or OFF Don't cache or retrieve results.
1 or ON Cache all results except SELECT SQL_NO_CACHE ... queries.
2 or DEMAND Cache only SELECT SQL_CACHE ... queries.

6.9.3 Query Cache Options in SELECT

There are two possible query cache related parameters that may be specified in a SELECT query:

Option Description
SQL_CACHE If QUERY_CACHE_TYPE is DEMAND, allow the query to be cached. If QUERY_CACHE_TYPE is ON, this is the default. If QUERY_CACHE_TYPE is OFF, do nothing.
SQL_NO_CACHE Make this query non-cachable, don't allow this query to be stored in the cache.

6.9.4 Query Cache Status and Maintenance

With the FLUSH QUERY CACHE command you can defragment the query cache to better utilise its memory. This command will not remove any queries from the cache. FLUSH TABLES also flushes the query cache.

The RESET QUERY CACHE command removes all query results from the query cache. You can check whether the query cache is present in your MySQL version: 

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.00 sec)

You can monitor query cache performance in SHOW STATUS:

Variable Description
Qcache_queries_in_cache Number of queries registered in the cache.
Qcache_inserts Number of queries added to the cache.
Qcache_hits Number of cache hits.
Qcache_lowmem_prunes Number of queries that were deleted from cache because of low memory.
Qcache_not_cached Number of non-cached queries (not cachable, or due to QUERY_CACHE_TYPE).
Qcache_free_memory Amount of free memory for query cache.
Qcache_free_blocks Number of free memory blocks in query cache.
Qcache_total_blocks Total number of blocks in query cache.

Total number of queries = Qcache_inserts + Qcache_hits + Qcache_not_cached.

The query cache uses variable length blocks, so Qcache_total_blocks and Qcache_free_blocks may indicate query cache memory fragmentation. After FLUSH QUERY CACHE only a single (big) free block remains.

Note: Every query needs a minimum of two blocks (one for the query text and one or more for the query results). Also, every table that is used by a query needs one block, but if two or more queries use same table only one block needs to be allocated.

You can use the Qcache_lowmem_prunes status variable to tune the query cache size. It counts the number of queries that have been removed from the cache to free up memory for caching new queries. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache.

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