MySQL Reference Manual for version 3.22.14b-gamma.


7 MySQL language reference

7.1 Literals: how to write strings and numbers

7.1.1 Strings

A string is a sequence of characters, surrounded by either single quote (`'') or double quote (`"') characters. Examples:

'a string'
"another string"

Within a string, certain sequences have special meaning. Each of these sequences begins with a backslash (`\'), known as the escape character. MySQL recognizes the following escape sequences:

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

There are several ways to include quotes within a string:

The SELECT statements shown below demonstrate how quoting and escaping work:

mysql> SELECT 'hello', '"hello"', '""hello""', 'hel"lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT "hello", "'hello'", ""hello"", "hel""lo", "\"hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | "hello" | hel"lo | "hello |
+-------+---------+-----------+--------+--------+
| hello | 'hello' | "hello" | hel"lo | "hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT "This\nIs\nFour\nlines";
+--------------------+
| This
Is
Four
lines |
+--------------------+
| This
Is
Four
lines |
+--------------------+

If you want to insert binary data into a BLOB column, the following characters must be represented by escape sequences:

NUL
ASCII 0. Should be represented by `\0' (a backslash and an ASCII `0' character).
\
ASCII 92, backslash
'
ASCII 39, single quote
"
ASCII 34, double quote

If you write C code, you can use the C API function mysql_escape_string() to escape characters for the INSERT clause. See section 18.3 C API function overview. In Perl, you can use the quote method of the DBI package to convert special characters to the proper escape sequences. See section 18.5.1.1 The DBI interface.

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

7.1.2 Numbers

Integers are just a sequence of digits. Floats use `.' as a decimal separator.

Examples of valid numbers:

1221
294.42
-32032.6809e+10

7.1.3 NULL values

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

Note that NULL means "no data" and is different from values such as 0 for numeric types and the empty string for string types. See section 16.12 Problems with NULL values.

7.1.4 Database, table, index, column and alias names

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

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 that is used in the query contains a column named col_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 not available in versions of MySQL prior to 3.22.

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 because some ODBC prefix table names with a `.' character.

7.1.4.1 Case sensitivity in names

Database and table names are case sensitive in Unix and case insensitive in Win32, because directory and file names are case sensitive in Unix but not in Win32. (In MySQL, databases and tables correspond to directories and files within those directories, so the case sensitivity of the underlying operating system determines how MySQL behaves.)

Note: although database and file names are case insensitive for Win32, you should not refer to a given database or table using different cases within the same query.

Column names are case insensitive in all cases.

Aliases on tables are case sensitive and aliases on columns are case insensitive.

7.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 (or character) types. This section first gives an overview of the types available, then summarizes the storage requirements for each column type and 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:

Square brackets (`[' and `]') indicate parts of type specifiers that are optional.

TINYINT[(M)] [UNSIGNED] [ZEROFILL]
A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
A medium-size integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.
INT[(M)] [UNSIGNED] [ZEROFILL]
A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.
INTEGER[(M)] [UNSIGNED] [ZEROFILL]
This is a synonym for INT.
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615. Note that all arithmetic is done using signed BIGINT or DOUBLE values, so you shouldn't use unsigned big integers larger than 9223372036854775807 (63 bits) except with bit functions! Note that -, + and * will use BIGINT arithmetic when both arguments are INTEGER values! This means that if you multiply two big integers (or results from functions that return integers) you may get unexpected results if the result is bigger than 9223372036854775807.
FLOAT(precision) [ZEROFILL]
A floating-point number. Cannot be unsigned. precision can be 4 or 8. FLOAT(4) is a single-precision number and FLOAT(8) is a double-precision number. These types are like the FLOAT and DOUBLE types described immediately below. FLOAT(4) and FLOAT(8) have the same ranges as the corresponding FLOAT and DOUBLE types, but their display size and number of decimals is undefined. This syntax is provided for ODBC compatibility.
FLOAT[(M,D)] [ZEROFILL]
A small (single-precision) floating-point number. Cannot be unsigned. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0 and -1.175494351E-38 to 3.402823466E+38.
DOUBLE[(M,D)] [ZEROFILL]
A normal-size (double-precision) floating-point number. Cannot be unsigned. Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0 and 2.2250738585072014E-308 to 1.7976931348623157E+308.
DOUBLE PRECISION[(M,D)] [ZEROFILL]
REAL[(M,D)] [ZEROFILL]
These are synonyms for DOUBLE.
DECIMAL(M,D) [ZEROFILL]
An unpacked floating-point number. Cannot be unsigned. Behaves like a CHAR column (`unpacked' means the number is stored as a string, using one character for each digit of the value, the decimal point, and, for negative numbers, the `-' sign). 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.
NUMERIC(M,D) [ZEROFILL]
This is a synonym for DECIMAL.
DATE
A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but allows you to assign values to DATE columns using either strings or numbers.
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.
TIMESTAMP[(M)]
A timestamp. The range is '1970-01-01 00:00:00' to sometime in the year 2106. MySQL displays TIMESTAMP values in YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD or YYMMDD format, depending on whether M is 14 (or missing), 12, 8 or 6, but allows you to assign values to TIMESTAMP columns using either strings or numbers. A TIMESTAMP column is useful for recording the time of an INSERT or UPDATE operation because it is automatically set to the time of the last operation. You can also set it to the current time by giving it a NULL value. See section 7.2.6 Date and time 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.
YEAR
A year. The allowable values are 1901 to 2155, and 0000. MySQL displays YEAR values in YYYY format, but allows you to assign values to YEAR columns using either strings or numbers. (YEAR is a new type for MySQL 3.22.)
CHAR(M) [BINARY]
A fixed-length string that is always right-padded with spaces to the specified length when stored. The range of M is 1 to 255 characters. Trailing spaces are removed when the value is retrieved. CHAR values are sorted and compared in case-insensitive fashion unless the BINARY keyword is given.
VARCHAR(M) [BINARY]
A variable-length string. NOTE: Trailing spaces are removed when the value is stored (this differs from the ANSI SQL specification). The range of M is 1 to 255 characters. VARCHAR values are sorted and compared in case-insensitive fashion unless the BINARY keyword is given.
TINYBLOB
TINYTEXT
A BLOB or TEXT column with a maximum length of 255 (2^8 - 1) characters.
BLOB
TEXT
A BLOB or TEXT column with a maximum length of 65535 (2^16 - 1) characters.
MEDIUMBLOB
MEDIUMTEXT
A BLOB or TEXT column with a maximum length of 16777215 (2^24 - 1) characters.
LONGBLOB
LONGTEXT
A BLOB or TEXT column with a maximum length of 4294967295 (2^32 - 1) characters.
ENUM('value1','value2',...)
An enumeration. A string object that can have only one value, chosen from the list of values 'value1', 'value2',... (or NULL). An ENUM can have a maxiumum of 65535 distinct values.
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.

7.2.1 Column type storage requirements

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

7.2.2 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(4) 4 bytes
FLOAT(8) 8 bytes
FLOAT 4 bytes
DOUBLE 8 bytes
DOUBLE PRECISION 8 bytes
REAL 8 bytes
DECIMAL(M,D) M bytes (D+2, if M < D)
NUMERIC(M,D) M bytes (D+2, if M < D)

7.2.3 Date and time types

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

7.2.4 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 maxiumum possible length of the type.

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, and vice-versa. See section 7.6 CREATE TABLE syntax.

The size of an ENUM object is determined by the number of different enumeration values. 1 byte is used for enumerations with up to 255 possible values. 2 bytes are used for enumerations with up to 65535 values.

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.

7.2.5 Numeric types

All integer types can have an optional 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.

All numeric types can have an optional attribute ZEROFILL. Values for ZEROFILL columns are left-padded with zeroes up to the maximum display length when they are displayed. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004.

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.

The maximum display size (M) and number of decimals (D) are used for formatting and calculation of maximum column width.

MySQL will store any value that fits a column's storage type even if the value exceeds the display size. For example, an INT(4) column has a display size of 4. Suppose you insert a value which has more than 4 digits into the column, such as 12345. The display size is exceeded, but the allowable range of the INT type is not, so MySQL stores the actual value, 12345. When retrieving the value from the column, MySQL returns the actual value stored in the column.

The DECIMAL type is considered a numeric type (as is its synonym, NUMERIC), but such values are stored as strings. One character is used for each digit of the value, the decimal point (if D > 0) and the `-' sign (for negative numbers). If D is 0, DECIMAL and NUMERIC values contain 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. For example, a type specification such as DECIMAL(4,2) indicates a maximum length of four characters with two digits after the decimal point. Due to the way the DECIMAL type is stored, this specification results in an allowable range of -.99 to 9.99, much less than the range of a DOUBLE.

To avoid some rounding problems, MySQL always rounds everything that it stores in any floating-point column according to the number of decimals. Suppose you have a column type of FLOAT(8,2). The number of decimals is 2, so a value such as 2.333 is rounded to two decimals and stored as 2.33.

7.2.6 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 an illegal value.

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

7.2.6.1 The DATETIME, DATE and TIMESTAMP types

The DATETIME, DATE and TIMESTAMP types are related. This section describes 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, they are not guaranteed to.)

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 time. (`Current time' means `current date and time' in TIMESTAMP contexts.) A TIMESTAMP column is updated automatically under either of the following conditions:

If you have multiple TIMESTAMP columns, only the first one is updated automatically. However, you can set any TIMESTAMP column to the current time by setting it to NULL (or by setting it to NOW(), obviously).

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

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

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

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

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

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

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

Values specified as non-delimited strings are interpreted using their length as given. If the string is 8 or 14 characters long, the year is assumed to be given by the first 4 characters. Otherwise the year is assumed to be given by the first 2 characters. The string is interpreted from left to right to find year, month, day, hour, minute and second values, for as many parts as are present in the string.

Year values specified as two digits are ambiguous, since the century is unknown. MySQL interprets 2-digit year values using the following rules:

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:

TIMESTAMP values are stored to full precision regardless of the display size. However, the only function that operates directly on the underlying stored value is UNIX_TIMESTAMP(). Other functions operate on the formatted retrieved value. This means you cannot use functions such as HOUR() or SECOND() unless the relevant part of the TIMESTAMP value is included in the formatted value. For example, the HH part of a TIMESTAMP column is not displayed unless the display size is at least 10, so trying to use HOUR() on shorter TIMESTAMP values produces a meaningless result.

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

Be aware of certain pitfalls when specifying date values:

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

If you assign a `short' TIME value to a TIME column, MySQL interprets the value as specifying seconds, or minutes and seconds. For example, '12' and 12 are interpreted as '00:00:12', whereas '11:12', '1112' and 1112 are interpreted as '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 since '00:00:00' is itself a legal TIME value, there is no way to distinguish a value of '00:00:00' that was specified explicitly from one that resulted from an illegal value.

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

7.2.7 String types

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

7.2.7.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 it when you create the table. You can declare it to be any length between 1 and 255; when 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 to be any length between 1 and 255 as well. This length is the maximum length, but in contrast to CHAR, values are stored using only as many characters as are needed. Values are not padded; instead, trailing spaces are removed when values are stored. (This space removal differs from the ANSI SQL specification.)

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

To illustrate the differences between the two types of columns, the table below shows the result of storing various string values into CHAR(4) and VARCHAR(4) columns:

Value CHAR(4) VARCHAR(4)
" ' ' "
'ab' 'ab ' 'ab'
'abcd' 'abcd' 'abcd'
'abcdef' 'abcd' 'abcd'

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.

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

MySQL may silently change the type of a CHAR or VARCHAR column at table creation time. See section 7.6 CREATE TABLE syntax.

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

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.

BLOB and TEXT columns cannot be indexed, unlike all other MySQL column types.

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.

There is no trailing space truncation for BLOB and TEXT columns as there is for VARCHAR columns.

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 that you cannot index BLOB or TEXT columns, and there is no trailing-space removal for BLOB and TEXT columns when values are stored. BLOB and TEXT can not have DEFAULT values and will also always be NULL columns.

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 contraints when using them:

Note that each BLOB/TEXT column is represented internally by a unique alloced object. This is in contrast to all other column types that are alloced once when the table is opened.

7.2.7.3 The ENUM type

An ENUM (enumeration) is a string object that can have only one value, chosen from a list of allowed values, or NULL. For example, a column specified as ENUM("one", "two", "three") can have any of these values:

NULL
"one"
"two"
"three"

An enumeration can have a maximum of 65535 elements.

When you assign a value to an ENUM column, the case of the value to be stored does not matter; the stored value is converted to the case that was used to specify the ENUM column when the table was created.

If you retrieve an ENUM in a numeric context, the column value's index is returned. If you store a number into an ENUM, the value stored is the enumeration member whose index is that number. Enumeration values are indexed beginning with 1 (0 is reserved for incorrect enumeration values).

Sorting of ENUM values is done according to the order in which the enumeration members were listed in the column specification. For example, "a" sorts before "b" for ENUM("a", "b"), but "a" sorts after "b" for ENUM("b", "a"). NULL values sort before other enumeration values.

If an ENUM is declared NOT NULL, the default value is the first value, otherwise the default value is NULL.

7.2.7.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. SET column values that are composed of multiple set members are specified with members separated by commas (`,'). For example, a column specified as SET("one", "two") NOT NULL can have any of these values:

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

A SET can have a maximum of 64 different members.

MySQL stores SET values numerically, with the low-order bit of the stored value corresponding to the first set member. If you retrieve a set value into a numeric context, the value retrieved has the bit (or bits) set corresponding to the set member (or members) that make up the column value. If a number is stored into a SET column, the bit (or bits) that are set in the number determine the set member (or members) in the column value. Sorting of SET values is done numerically. NULL values sort before other set members.

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

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'; # Exact match
mysql> SELECT * FROM tbl_name WHERE set_col & 1;           # Is in first group

7.2.8 Choosing the right type for a column

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.

See section 10.14 What are the different row formats? Or, when should VARCHAR/CHAR be used?.

7.2.9 Column indexes

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

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

For CHAR and VARCHAR columns, you can index a prefix of a column. This is much faster and requires less disk space than indexing the whole column.

The syntax to use in the CREATE TABLE statement to index a column prefix looks like this:

KEY index_name (col_name(length))

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

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

7.2.10 Multiple-column indexes

MySQL can create indexes from multiple columns.

A multiple-column index can be considered a sorted array where the values of the indexed columns are concatenated.

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

An index may consist up up to 15 columns (or column prefixes, for CHAR and VARCHAR columns).

Suppose you have a table that has the following specification:

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

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

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

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

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

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

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

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

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

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

7.2.11 Using column types from other database engines

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

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

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

7.3 Functions for use in SELECT and WHERE clauses

A select_expression or where_definition can consist of any expression using the functions described below.

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

Note: there must be no whitespace between a function name and the parenthesis following it. This helps the MySQL parser distinguish between function calls and references to tables or columns that happen to have the same name as a function.

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

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

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

Is displayed like this:

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

7.3.1 Grouping functions

( ... )
Parentheses. Use these to force the order of evaluation in an expression.
mysql> select 1+2*3;
        -> 7
mysql> select (1+2)*3;
        -> 9

7.3.2 Normal arithmetic operations

Note that in the case of -, + and *, the result is calculated with BIGINT precision if both arguments are integers!

+
Addition
mysql> select 3+5;
        -> 8
-
Subtraction
mysql> select 3-5;
        -> -2
*
Multiplication
mysql> select 3*5;
        -> 15
mysql> select 18014398509481984*18014398509481984.0;
        -> 324518553658426726783156020576256.0
mysql> select 18014398509481984*18014398509481984;
        -> 0
The result of the last expression is incorrect because the result of the integer multiplication is over the 64-bit range.
/
Division. A division by zero produces a NULL result.
mysql> select 3/5;
        -> 0.60
mysql> select 102/(1-1);
        -> NULL
A division will be calculated with BIGINT arithmetic only if it's used in a context where its result is converted to an integer!

7.3.3 Bit functions

These have a maximum range of 64 bits because MySQL uses BIGINT (64-bit) arithmetic for bit operations.

|
Bitwise OR
mysql> select 29 | 15;
        -> 31
&
Bitwise AND
mysql> select 29 & 15;
        -> 13
<<
Shifts a longlong number to the left.
mysql> select 1 << 2
        -> 4
>>
Shifts a longlong number to the right.
mysql> select 4 >> 2
        -> 1
BIT_COUNT(N)
Returns the number of bits that are set in the argument N.
mysql> select BIT_COUNT(29);
        -> 4

7.3.4 Logical operations

All logical functions return 1 (TRUE) or 0 (FALSE).

NOT
!
Logical NOT. Returns 1 if the argument is 0, otherwise returns 0. Exception: NOT NULL returns NULL.
mysql> select NOT 1;
        -> 0
mysql> select NOT NULL;
        -> NULL
mysql> select ! (1+1);
        -> 0
mysql> select ! 1+1;
        -> 1
The last example returns 1 because the expression evaluates the same way as (!1)+1.
OR
||
Logical OR. Returns 1 if either argument is not 0 and not NULL.
mysql> select 1 || 0;
        -> 1
mysql> select 0 || 0;
        -> 0
mysql> select 1 || NULL;
        -> 1

AND
&&
Logical AND. Returns 0 if either argument is 0 or NULL, otherwise returns 1.
mysql> select 1 && NULL;
        -> 0
mysql> select 1 && 0;
        -> 0

7.3.5 Comparison operators

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

MySQL performs comparisons using the following rules:

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

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

mysql> SELECT 1 > '6x';
         -> 0
mysql> SELECT 7 > '6x';
         -> 1
mysql> SELECT 0 > 'x6';
         -> 0
mysql> SELECT 0 = 'x6';
         -> 1
=
Equal
mysql> select 1 = 0;
        -> 0
mysql> select '0' = 0;
        -> 1
mysql> select '0.0' = 0;
        -> 1
mysql> select '0.01' = 0;
        -> 0
mysql> select '.01' = 0.01;
        -> 1
<>
!=
Not equal
mysql> select '.01' <> '0.01';
        -> 1
mysql> select .01 <> '0.01';
        -> 0
mysql> select 'zapp' <> 'zappp';
        -> 1
<=
Less than or equal
mysql> select 0.1 <= 2;
        -> 1
<
Less than
mysql> select 2 <= 2;
        -> 1
>=
Greater than or equal
mysql> select 2 >= 2;
        -> 1
>
Greater than
mysql> select 2 > 2;
        -> 0
ISNULL(expr)
If expr is NULL, returns 1, otherwise returns 0.
mysql> select ISNULL(1+1);
        -> 0
mysql> select ISNULL(1/0);
        -> 1
expr BETWEEN min AND max
If expr is greater than or equal to min and expr is less than or equal to max, returns 1, otherwise returns 0. Does the same thing as the expression (min <= expr AND expr <= max) if all the arguments are of the same type. The first argument (expr) determines how the comparison is performed. If expr is a string expression, a case-insensitive string comparison is done. If expr is a binary string, a case-sensitive string comparison is done. If expr is an integer expression, an integer comparison is done. Otherwise, a floating-point (real) comparison is done.
mysql> select 1 BETWEEN 2 AND 3;
        -> 0
mysql> select 'b' BETWEEN 'a' AND 'c';
        -> 1
mysql> select 2 BETWEEN 2 AND '3';
        -> 1
mysql> select 2 BETWEEN 2 AND 'x-3';
        -> 0
expr IN (value,...)
Returns 1 if expr is any of the values in the IN list, else returns 0. If all values are constants, then all values are evaluated according to the type of expr and sorted. The search for the item is then done using a binary search. This means IN is very quick when used with constants in the IN value list. If expr is a case-sensitive string expression, the string comparison is done in case-sensitive fashion.
mysql> select 2 IN (0,3,5,'wefwf');
        -> 0
mysql> select 'wefwf' IN (0,3,5,'wefwf');
        -> 1
expr NOT IN (value,...)
Same as NOT (expr IN (value,...)).
INTERVAL(N,N1,N2,N3...)
Returns 0 if N < N1, 1 if N < N2 and so on. All arguments are treated as numbers. It is required that N1 < N2 < N3 < Nn for this function to work correctly. This is because a binary search is used (very fast).
mysql> select INTERVAL(23, 1, 15, 17, 30, 44, 200);
        -> 3
mysql> select INTERVAL(10, 1, 10, 100, 1000);
        -> 2
mysql> select INTERVAL(22, 23, 30, 44, 200);
        -> 0

7.3.6 String comparison functions

Normally, if one expression to be compared is not case sensitive, string comparisons are done in case-insensitive fashion.

expr1 LIKE expr2 [ESCAPE string-of-one-character]
SQL simple regular expression comparison. Returns 1 (TRUE) or 0 (FALSE). With LIKE you can use the following two wildcard characters:
% Matches any number of characters, even zero characters
_ Matches exactly one character
If one doesn't specify the ESCAPE character '\' will be used. To test for literal instances of the wildcard characters, use the following sequences:
\% Matches one % character
\_ Matches one _ character
mysql> select 'David!' LIKE 'David_';
        -> 1
mysql> select 'David!' LIKE 'David\_';
        -> 0
mysql> select 'David_' LIKE 'David\_';
        -> 1
mysql> select 'David!' LIKE '%D%v%';
        -> 1
mysql> select 10 LIKE '1%';
        -> 1
mysql> select 'David_' LIKE 'David|_' ESCAPE '|'
LIKE is allowed on numeric expressions! (This is a MySQL extension to the ANSI SQL LIKE.)
expr1 NOT LIKE expr2 [ESCAPE 'string-of-one-character']
Same as NOT (expr1 LIKE expr2 [ESCAPE 'string-of-one-character']).
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 H Description of MySQL regular expression syntax. Returns 1 if expr matches pat, otherwise returns 0. RLIKE is a synonym for REGEXP, provided for mSQL compatibility. NOTE: Because MySQL uses the C escape syntax in strings (\n), you must double any '\' that you use in your REGEXP strings.
mysql> select 'Monty!' REGEXP 'm%y%%';
        -> 0
mysql> select 'Monty!' REGEXP '.*';
        -> 1
mysql> select 'new*\n*line' REGEXP 'new\\*.\\*line';
        -> 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 expr
Same as NOT (expr REGEXP expr).
STRCMP(expr1,expr2)
Returns 0 if the strings are the same. Returns -1 if the first argument is smaller than the second according to the current sort order. Otherwise returns 1.
mysql> select STRCMP('text', 'text2');
        -> -1
mysql> select STRCMP('text2', 'text');
        -> 1
mysql> select STRCMP('text', 'text');
        -> 0

7.3.7 Control flow functions

IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1, else returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it are used.
mysql> select IFNULL(1,0);
        -> 1
mysql> select IFNULL(0,10);
        -> 0
mysql> select IFNULL(1/0,10);
        -> 10
mysql> select IFNULL(1/0,'yes');
        -> 'yes'
IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then returns expr2, else returns expr3. IFNULL() returns a numeric or string value, depending on the context in which it are used. expr1 is evaluated as an INTEGER, which means that if you are testing floating-point values, you should do so using a comparison operation.
mysql> select IF(1>2,2,3);
        -> 3
mysql> select IF(1<2,'yes','no');
        -> 'yes'
mysql> select IF(strcmp('test','test1'),'yes','no');
        -> 'no'
mysql> select IF(0.1<>0,1,0);
        -> 1
mysql> select IF(0.1,1,0);
        -> 0

7.3.8 Mathematical functions

All mathematical functions return NULL in case of an error.

-
Sign. Changes the sign of the argument.
mysql> select - 2;
        -> -2
Note that if this function 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 (-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 % in C). Returns the remainder of N divided by M.
mysql> select MOD(234, 10);
        -> 4
mysql> select 253 % 7;
        -> 1
mysql> select MOD(29,9);
        -> 2
This function is safe to use with BIGINT values.
FLOOR(X)
Returns the largest integer value not greater than X.
mysql> select FLOOR(1.23);
        -> 1
mysql> select FLOOR(-1.23);
        -> -2
Note that the return value is converted to a BIGINT !
CEILING(X)
Returns the smallest integer value not less than X.
mysql> select CEILING(1.23);
        -> 2
mysql> select CEILING(-1.23);
        -> -1
Note that the return value is converted to a BIGINT !
ROUND(X)
Returns the argument X, rounded to an integer.
mysql> select ROUND(-1.23);
        -> -1
mysql> select ROUND(-1.58);
        -> -2
mysql> select ROUND(1.58);
        -> 2
Note that the return value is converted to a BIGINT !
ROUND(X,D)
Returns the argument X, rounded to a number with D decimals.
mysql> select ROUND(1.298, 1);
        -> 1.3
Note that the return value is converted to a BIGINT !
EXP(X)
Returns the value of e (the base of natural logarithms) raised to the power of X.
mysql> select EXP(2);
        -> 7.389056
mysql> select EXP(-2);
        -> 0.135335
LOG(X)
Returns the natural logarithm of X.
mysql> select LOG(2);
        -> 0.693147
mysql> select LOG(-2);
        -> NULL
If you want the log of a number X to some arbitary base B, use the formula LOG(X)/LOG(B).
LOG10(X)
Returns the base-10 logarithm of X.
mysql> select LOG10(2);
        -> 0.301030
mysql> select LOG10(100);
        -> 2.000000
mysql> select LOG10(-100);
        -> NULL
POW(X,Y)
POWER(X,Y)
Returns the value of X raised to the power of Y.
mysql> select POW(2,2);
        -> 4.000000
mysql> select POW(2,-2);
        -> 0.250000
SQRT(X)
Returns the non-negative square root of X.
mysql> select SQRT(4);
        -> 2.000000
mysql> select SQRT(20);
        -> 4.472136
PI()
Returns the value of PI.
mysql> select PI();
        -> 3.141593
COS(X)
Returns the cosine of X, where X is given in radians.
mysql> select COS(PI());
        -> -1.000000
SIN(X)
Returns the sine of X, where X is given in radians.
mysql> select SIN(PI());
        -> 0.000000
TAN(X)
Returns the tangent of X, where X is given in radians.
mysql> select TAN(PI()+1);
        -> 1.557408
ACOS(X)
Returns the arc cosine of X, that is, the value whose cosine is X. Returns NULL if X is not in the range -1 to 1.
mysql> select ACOS(1);
        -> 0.000000
mysql> select ACOS(1.0001);
        -> NULL
mysql> select ACOS(0);
        -> 1.570796
ASIN(X)
Returns the arc sine of X, that is, the value whose sine is X. Returns NULL if X is not in the range -1 to 1.
mysql> select ASIN(0.2);
        -> 0.201358
mysql> select ASIN('foo');
        -> 0.000000
ATAN(X)
Returns the arc tangent of X, that is, the value whose tangent is X.
mysql> select ATAN(2);
        -> 1.107149
mysql> select ATAN(-2);
        -> -1.107149
ATAN2(X,Y)
Returns the arc tangent of the two variables X and Y. It is similar to calculating the arc tangent of Y / X, except that the signs of both arguments are used to determine the quadrant of the result.
mysql> select ATAN(-2,2);
        -> -0.785398
mysql> select ATAN(PI(),0);
        -> 1.570796
COT(X)
Returns the cotangent of X.
mysql> select COT(12);
        -> -1.57267341
mysql> select COT(0);
        -> NULL
RAND()
RAND(N)
Returns a random floating-point value in the range 0 to 1.0. If an integer argument N is specified, it is used as the seed value.
mysql> select RAND();
        -> 0.5925
mysql> select RAND(20);
        -> 0.1811
mysql> select RAND(20);
        -> 0.1811
mysql> select RAND();
        -> 0.2079
mysql> select RAND();
        -> 0.7888
You can't do an ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
LEAST(X,Y...)
With two or more arguments, returns the smallest (minimum-valued) argument. The arguments are compared according to 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 3.22.5, you can use MIN() instead of LEAST.
GREATEST(X,Y...)
Returns the largest (maximum-valued) argument. The arguments are compared according to 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 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.
mysql> select TRUNCATE(1.223,1);
        -> 1.2
mysql> select TRUNCATE(1.999,1);
        -> 1.9
mysql> select TRUNCATE(1.999,0);
        -> 1

7.3.9 String functions

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('dx');
        -> 100
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. 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 number. This is the same as 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 the same as CONV(N,10,8). Returns NULL if N is NULL.
mysql> select OCT(12);
        -> '14'
HEX(N)
Returns a string representation of the hexadecimal value of N where N is a longlong number. This is the same as CONV(N,10,16). Returns NULL if N is NULL.
mysql> select HEX(255);
        -> 'FF'
CHAR(N,...)
Returns a string consisting of the characters given by the ASCII code values of the arguments. NULL values are skipped.
mysql> select CHAR(77,121,83,81,'76');
        -> 'MySQL'
CONCAT(X,Y...)
Returns the string that results from concatenating the arguments. Returns NULL if any argument is NULL. May have more than 2 arguments.
mysql> select CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> select CONCAT('My', NULL, 'QL');
        -> NULL
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
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
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
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
LPAD(str,len,padstr)
Returns the string str, left-padded with the string padstr until str is len characters long.
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.
mysql> select RPAD('hi',5,'?');
        -> 'hi???'
LEFT(str,len)
Returns the leftmost len characters from the string str.
mysql> select LEFT('foobarbar', 5);
        -> 'fooba'
RIGHT(str,len)
SUBSTRING(str FROM len)
Returns the rightmost len characters from the string str.
mysql> select RIGHT('foobarbar', 4);
        -> 'rbar'
mysql> select SUBSTRING('foobarbar' from 4);
        -> 'rbar'
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 ANSI SQL 92 syntax.
mysql> select SUBSTRING('Quadratically',5,6);
        -> 'ratica'
SUBSTRING(str,pos)
Returns a substring from string str starting at position pos.
mysql> select SUBSTRING('Quadratically',5);
        -> 'ratically'
SUBSTRING_INDEX(str,delim,count)
Returns the substring from string str after 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.tcx'
mysql> select SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'tcx.se'
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'
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'
SOUNDEX(str)
Returns a soundex string from str. Two strings that sound "about 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-alpha characters are ignored in the given string. All 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,to)
Returns the string str with all all occurrences of the string from replaced by the string to.
mysql> select REPLACE('www.mysql.com', 'w', 'Ww');
        -> 'WwWwww.mysql.com'
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 or if LENGTH(str)*count > max_allowed_packet.
mysql> select REPEAT('MySQL', 3);
        -> 'MySQLMySQLMySQL'
REVERSE(str)
Returns the string str with the order of the characters reversed.
mysql> select REVERSE('abc');
        -> 'cba'
INSERT(str,start,len,newstr)
Returns the string str, with the substring beginning at position start and len characters long replaced by the string newstr.
mysql> select INSERT('Quadratic', 3, 4, 'What');
        -> 'QuWhattic'
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 itself a string with its individual 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 is optimized to use bit arithmetic! Returns 0 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,strlist)
Returns a set (a string separated with `,') of the strings that have the corresponding bit set. NULL strings in the set list 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');
        -> "
LCASE(str)
LOWER(str)
Returns the string str with all characters changed to lowercase according to the current character set mapping (the default is Latin1).
mysql> select LCASE('QUADRATICALLY');
        -> 'quadratically'
UCASE(str)
UPPER(str)
Returns the string str with all characters changed to uppercase according to the current character set mapping (the default is Latin1).
mysql> select UCASE('Hej');
        -> 'HEJ'

There is no string function to convert a number to a char. This is not needed as MySQL automaticly converts numbers to string and vice versa:

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

If a string function gets 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.

7.3.10 Date and time functions

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

mysql> SELECT something FROM table
           WHERE TO_DAYS(NOW()) - TO_DAYS(date_field) <= 30;

See section 7.2.6 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.

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('1997-10-04 22:23:00');
        -> 5
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 52, 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. The week starts on Sunday if the second argument is 0, on Monday if the second argument is 1.
mysql> select WEEK('1998-02-20');
        -> 7
mysql> select WEEK('1998-02-20',0);
        -> 7
mysql> select WEEK('1998-02-20',1);
        -> 8
YEAR(date)
Returns the year for date, in the range 1000 to 9999.
mysql> select YEAR('98-02-03');
        -> 1998
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.
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.
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. They are new for MySQL 3.22. ADDDATE() and SUBDATE() are synonyms for DATE_ADD() and DATE_SUB(). date is the starting date (a DATETIME or DATE value). expr is an expression specifying the interval value to be added or substracted from the starting date. expr is a string; it may start with a `-' for negative intervals. type is an interval type keyword indicating how the expression should be interpreted.
type value Meaning expr format
SECOND Seconds SECONDS
MINUTE Minutes MINUTES
HOUR Hours HOURS
DAY Days DAYS
MONTH Months MINUTES
YEAR Years YEARS
MINUTE_SECOND Minutes and seconds "MINUTES:SECONDS"
HOUR_MINUTE Hours and minutes "HOURS:MINUTES"
DAY_HOUR Days and hours "DAYS HOURS"
YEAR_MONTH Years and months "YEARS-MONTHS"
HOUR_SECOND Hours, minutes, seconds "HOURS:MINUTES:SECONDS"
DAY_MINUTE Days, hours, minutes "DAYS HOURS:MINUTES"
DAY_SECOND Days, hours, minutes, seconds "DAYS HOURS:MINUTES:SECONDS"
MySQL allows any non-numeric delimiter in the format. The ones shown in the table are the suggested delimiters. If the date is a DATE value and your calculations involve only YEAR, MONTH and DAY (that is, no time parts), the result is a DATE value. Otherwise the result is a DATETIME value.
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: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("1997-12-31 23:59:59",INTERVAL 1 DAY);
        -> 1998-01-01 23:59: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 interval 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 day, hours, minutes and seconds parts. If you specify a value like "1:10", MySQL assumes that the day and hours parts are missing and the the value represents minutes and seconds. In other words, "1:10" DAY_SECOND is interpreted as "1:10" MINUTE_SECOND. If you use 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
TO_DAYS(date)
Given a date date, returns a daynumber (the number of days since year 0). TO_DAYS() is not intended for use with values that precede the advent of the Gregorian calendar (1582).
mysql> select TO_DAYS(950501);
        -> 728779
mysql> select TO_DAYS('1997-10-07);
        -> 729669
FROM_DAYS(N)
Given a daynumber N, returns a DATE value. FROM_DAYS() is not intended for use with values that precede the advent of the Gregorian calendar (1582).
mysql> select FROM_DAYS(729669);
        -> '1997-10-07'
DATE_FORMAT(date,format)
Formats the date value according to the format string. The following specifiers may be used in the format string:
%M Month name (January..December)
%W Weekday name (Sunday..Saturday)
%D Day of the month with english suffix (1st, 2nd, 3rd, etc.)
%Y Year, numeric, 4 digits
%y Year, numeric, 2 digits
%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 (01..12)
%c Month, numeric (1..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 (0..52), where Sunday is the first day of the week.
%u Week (0..52), where Monday is the first day of the week.
%% Single `%' characters are ignored. Use %% to produce a literal `%' (for future extensions).
All other characters are just copied to the result.
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'
For the moment, % is optional. In future versions of MySQL, % will be required.
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. The format is YYYYMMDD or 'YYYY-MM-DD', depending on whether the function is used in a numeric or string context.
mysql> select CURDATE();
        -> '1997-12-15'
mysql> select CURDATE()+0;
        -> 19971215
CURTIME()
CURRENT_TIME
Returns the current time. The format is HHMMSS or 'HH:MM:SS', depending on whether the function is used in a numeric or string context.
mysql> select CURTIME();
        -> '23:50:26'
mysql> select CURTIME()+0;
        -> 235026
NOW()
SYSDATE()
CURRENT_TIMESTAMP
Returns the current time, in the format YYYYMMDDHHMMSS or 'YYYY-MM-DD HH:MM:SS', depending on whether the function is used in a numeric or string context.
mysql> select NOW();
        -> '1997-12-15 23:50:26'
mysql> select NOW()+0;
        -> 19971215235026
UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)
If called with no argument, returns a Unix timestamp (seconds in GMT since '1970-01-01 00:00:00'). Normally, it is called with a TIMESTAMP-valued argument, in which case it returns the value of the argument in seconds. 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 get the value without an implicit `string-to-unix-timestamp' conversion.
FROM_UNIXTIME(Unix_timestamp)
Returns a representation of the timestamp value. The format is YYYYMMDDHHMMSS or 'YYYY-MM-DD HH:MM:SS', depending on whether the function is used in a numeric or string context.
mysql> select FROM_UNIXTIME(875996580);
        -> '1997-10-04 22:23:00'
mysql> select FROM_UNIXTIME(875996580)+0;
        -> 19971004222300
FROM_UNIXTIME(Unix_timestamp,format)
Returns a string representation of the Unix timestamp, formatted according to the format string. format may contain the same specifiers as those listed in the entry for the DATE_FORMAT() function.
mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x');
        -> '1997 23rd December 03:43:30 x'
SEC_TO_TIME(seconds)
Returns the seconds argument, converted to hours, minutes and seconds in the format HHMMSS or HH:MM:SS, depending on whether the function is used in a numeric or string 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

7.3.11 Miscellaneous functions

DATABASE()
Returns the current database name.
mysql> select DATABASE();
        -> 'test'
USER()
SYSTEM_USER()
SESSION_USER()
Returns the current MySQL user name.
mysql> select USER();
        -> 'davida@localhost'
PASSWORD(str)
Calculates a password string from the plaintext password str. To store a password in the user grant table, this function must be used.
mysql> select PASSWORD('badpwd');
        -> '7f84554057dd964b'
PASSWORD() performs password encryption, but it does not do so in the same way that Unix passwords are encrypted. You should not assume that if your Unix password and your MySQL password are the same, PASSWORD() will result in the same encrypted value as is stored in the Unix password file. See ENCRYPT().
ENCRYPT(str[,salt])
Encrypt str using the Unix crypt() system call. The salt argument should be a string with 2 characters. If crypt() is not available on your system, ENCRYPT() always returns NULL.
mysql> select ENCRYPT("hello");
        -> 'VxuFAJXVARROc'
LAST_INSERT_ID([expr])
Returns the last automatically-generated value that was set in an AUTO_INCREMENT column. See section 18.4.49 How can I get the unique ID for the last inserted row?.
mysql> select LAST_INSERT_ID();
        -> 1
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 expr is given in an UPDATE clause, then the used value is returned as a last_insert_id value. This can be used to simulate sequences: First create the table:
create table sequence (id int not null);
insert into sequence values (0);
This can now be used to generate sequence numbers with:
UPDATE sequence SET id=last_insert_id(id+1);
The new id can be read as you would read any normal auto_increment value in MySQL (For example LAST_INSERT_ID() will return the new id).
FORMAT(X,D)
Formats the number X to a format like '#,###,###.##' with D decimals.
mysql> select FORMAT(12332.33, 2);
        -> '12,332.33'
VERSION()
Returns a string indicating the MySQL server version.
mysql> select VERSION();
        -> '3.21.16-beta-log'
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.
mysql> select GET_LOCK("automatically released",10);
        -> 1
mysql> select GET_LOCK("test",10);
        -> 1
mysql> select RELEASE_LOCK("test");
        -> 1
mysql> select RELEASE_LOCK("automatically released");
        -> NULL
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 and NULL if the named lock didn't exist.

7.3.12 Functions for use with GROUP BY clauses

COUNT(expr)
Returns a count of the number of non-NULL rows.
mysql> select COUNT(if(length(name)>3,1,NULL)) from student;
COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved and there is no WHERE clause.
mysql> select COUNT(*) from student;
AVG(expr)
Returns the average value of expr.
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.
SUM(expr)
Returns the sum of expr.
STD(expr)
STDDEV(expr)
Returns the standard deviation of expr. This is an extension to ANSI SQL. The STDDEV() form of this function is provided for Oracle compatability.
BIT_OR(expr)
Returns the bitwise OR of all bits in expr. The calculation is performed with 64-bit precision.
BIT_AND(expr)
Returns the bitwise AND of all bits in expr. The calculation is performed with 64-bit precision.

MySQL has extended the use of GROUP BY. You can use columns or calculations in the SELECT expressions which 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 b.name in the following query:

mysql> select a.id,b.name,count(*) from a,b where a.id=b.id GROUP BY a.id;

In ANSI SQL, you would have to add customer.name to the GROUP BY for the following query. In MySQL, the name is redundant.

mysql> select order.custid,customer.name,max(payments)
           from order,customer
           where order.custid = customer.custid
           GROUP BY order.custid;

Don't use this feature if the columns you omit from the GROUP BY part aren't unique in the group!

In some specific cases, you can use LEAST() and GREATEST() to get a specific column even if it isn't unique. The following gives the value from the row with the smallest "sort" value.

substr(LEAST(concat(sort,space(6-length(sort)),column),7,length(column)))

Note that you can't yet use expressions in GROUP BY or ORDER BY clauses. On the other hand, you can use an alias on an expression to solve the problem:

mysql> select id,floor(value/100) as val from tbl_name
           GROUP BY id,val ORDER BY val;

7.4 CREATE DATABASE syntax

CREATE DATABASE db_name

CREATE DATABASE creates a database with the given name. Rules for allowable database names are given in section 7.1.4 Database, table, index, column and alias names.

Databases in MySQL are implemented as directories containing files that correspond to tables in the database. Since 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 12.1 Overview of the different MySQL programs.

7.5 DROP DATABASE syntax

DROP DATABASE [IF EXISTS] db_name

DROP DATABASE drops all tables in the database and deletes the database. You must 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, since each table corresponds to a `.ISD' file, a `.ISM' file and a `.frm' file.

In MySQL 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 12.1 Overview of the different MySQL programs.

7.6 CREATE TABLE syntax

CREATE TABLE tbl_name (create_definition,...)

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

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

CREATE TABLE creates a table with the given name in the current database. Rules for allowable table names are given in See section 7.1.4 Database, table, index, column and alias names.

Each table is represented by three files in the database directory:

File Purpose
`tbl_name.frm' Table definition (form) file
`tbl_name.ISD' Data file
`tbl_name.ISM' Index file

In MySQL 3.22, the table name can be given as db_name.tbl_name.

For more information on the properties of the various column types, see section 7.2 Column types.

7.6.1 Silent column specification changes

In some cases, MySQL silently changes a column specification from that given in the CREATE TABLE statement:

Certain other column type changes may occur if you compress a table using pack_isam. See section 10.14 What are the different row formats? Or, when should VARCHAR/CHAR be used?.

7.7 ALTER TABLE syntax

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

alter_specification:
        ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
  or    ADD INDEX [index_name] (index_col_name,...)
  or    ADD UNIQUE [index_name] (index_col_name,...)
  or    ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  or    CHANGE [COLUMN] old_col_name create_definition
  or    DROP [COLUMN] col_name
  or    DROP PRIMARY KEY
  or    DROP INDEX key_name
  or    RENAME [AS] new_tbl_name

ALTER TABLE allows you to change the structure of any 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.

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.

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

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 change column b from CHAR(10) to CHAR(20) (and rename it from b to c):

mysql> ALTER TABLE t2 CHANGE a 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 which cannot be NULL, and index it at the same time (since AUTO_INCREMENT columns must be indexed):

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

7.8 OPTIMIZE TABLE syntax

OPTIMIZE TABLE tbl_name

OPTIMZE TABLE should be used if you have deleted a large part of the table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB or TEXT columns). Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. You can use OPTIMIZE TABLE to reclaim the unused space.

OPTIMIZE TABLE works by making a temporary copy of the original table. The old table is copied to the new table (without the unused rows), 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 OPTIMIZE 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.

7.9 DROP TABLE syntax

DROP TABLE [IF EXISTS] tbl_name [, tbl_name...]

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

In MySQL 3.22 or later, you can use the keywords IF EXISTS to prevent an error from occurring for tables that don't exist.

7.10 DELETE syntax

DELETE [LOW_PRIORITY] FROM tbl_name [WHERE where_definition]

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

If you issue a DELETE with no WHERE clause, all rows are deleted. MySQL does this by recreating the table as an empty table, which is much faster than deleting each row. In this case, DELETE returns zero as the number of affected records. (MySQL can't return the number of rows that were actually deleted, since the recreate is done without opening the data files. As long as the table definition file `tbl_name.frm' is valid, the table can be recreated this way, even if the data or index files have become corrupted.)

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

Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. To get smaller files, use the OPTIMIZE TABLE statement or the isamchk utility to reorganize tables. OPTIMIZE TABLE is easier, but isamchk is faster. See section 13.5.3 Table optimization.

7.11 SELECT syntax

SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [DISTINCT | ALL]
    select_expression,...
    [INTO OUTFILE 'file_name' export_options]
    [FROM table_references
        [WHERE where_definition]
        [GROUP BY col_name,...]
        [HAVING where_definition]
        [ORDER BY {unsigned_integer | col_name} [ASC | DESC] ,...]
        [LIMIT [offset,] rows]
        [PROCEDURE procedure_name] ]

SELECT is usually used to retrieve rows selected from one or more tables. 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.

7.12 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 STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference ON conditional_expr
table_reference LEFT [OUTER] JOIN table_reference USING (column_list)
table_reference NATURAL LEFT [OUTER] JOIN table_reference
{ oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr }

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 table3.id=table2.id;

7.13 INSERT syntax

    INSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)]
        VALUES (expression,...),(...),...
or  INSERT [LOW_PRIORITY] [INTO] tbl_name [(col_name,...)]
        SELECT ...
or  INSERT [LOW_PRIORITY] [INTO] tbl_name SET col_name=expression,
        col_name=expression,...

INSERT inserts new rows into an existing table. The INSERT ... VALUES form 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 3.22.5 or later. The col_name=expression syntax is supported in MySQL 3.22.10 and later.

tbl_name is the table to insert rows into. The column name list indicates which columns the rest of the statement specifies values for.

If you use INSERT INTO ... SELECT ... or a INSERT INTO ... 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 below:

Records: 100 Duplicates: 0 Warnings: 0

Duplicates indicates the number of rows which couldn't be inserted because some unique index value in existing rows would be duplicated. 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:

7.14 REPLACE syntax

    REPLACE [LOW_PRIORITY] [INTO] tbl_name [(col_name,...)]
        VALUES (expression,...)
or  REPLACE [LOW_PRIORITY] [INTO] tbl_name [(col_name,...)]
        SELECT ...
or  REPLACE [LOW_PRIORITY] [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 on a unique index as a new record, the old record is deleted before the new record is inserted. See section 7.13 INSERT syntax.

7.15 LOAD DATA INFILE syntax

LOAD DATA [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [OPTIONALLY] ENCLOSED BY "]
        [ESCAPED BY '\\' ]]
    [LINES TERMINATED BY '\n']
    [(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 3.22.6 or later.) Using LOCAL will be a bit slower than letting the server access the files directly, since the contents of the file must travel from the client host to the server host.

The mysqlimport utility can be used to read data files; it operates by sending a LOAD DATA INFILE command to the server. The --local option causes mysqlimport to read data files 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 database directory, whereas a file given as `./myfile.txt' is read from the server's data directory.

For security reasons, when reading text files from 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 for the database. See section 6.4 How the privilege system works.

LOAD DATA INFILE is the complement of SELECT ... INTO OUTFILE. See section 7.11 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'

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

Conversely, the defaults cause LOAD DATA INFILE to act as follows when reading input:

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

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 * FROM table1 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 below, 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 below illustrates the field and line handling options you would use to load the file:

mysql> LOAD DATA INFILE 'file_name.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.

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

"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, you may generate output that cannot be read properly by LOAD DATA INFILE. For example, the output just shown above would appear as shown below 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 or not 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 below:

"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' (e.g., \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 7.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:

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 that have the same unique key value. 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.

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, so that MySQL can 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. TIMESTAMP columns are only set to the current 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. Default value assignment is described in section 7.6 CREATE TABLE syntax.

If an input row has too many fields, the extra fields are ignored and a warning is generated.

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 given as strings!

When the LOAD DATA INFILE query finishes, you can use the C API function mysql_info() to get information about the query. The format of the information string is shown below:

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 7.13 INSERT syntax), except that LOAD DATA INFILE also generates warnings when there are too few or too many fields in the input row.

For more information about the efficiency of INSERT versus LOAD DATA INFILE and speeding up LOAD DATA INFILE, see section 10.10 How to arrange a table to be as fast/small as possible.

7.16 UPDATE syntax

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

UPDATE updates columns in existing table rows with new values. The WHERE clause, if given, specifies which rows should be updated. Otherwise all rows are updated.

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

If you access a tbl_name column 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 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.

7.17 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 example below 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.

7.18 FLUSH syntax (clearing caches)

FLUSH flush_option [,flush_option]

You should use the FLUSH command if you want to clear some of the internal caches MySQL uses. flush_option can be any of the following:

HOSTS Empties the host cache tables. You should flush the host tables if some of your hosts change IP number or if you get the error message Host ... is blocked. (When too many connections errors occur for a given host, MySQL assumes something is wrong and blocks the host from further connection requests. Flushing the host tables allows the host to attempt to connect again. See section 16.1.3 Host '...' is blocked error.)
LOGS Closes and reopens the standard and update log files. If you have specified the update log file without an extension, the extension number of the new update log file will be incremented by 1 relative to the previous file.
PRIVILEGES Reloads the privileges from the grant tables in the mysql database.
TABLES Closes all open tables.
STATUS Reset most status variables to zero.

You can also access each of the commands shown above with the mysqladmin utility, using the flush-hosts, flush-logs, reload or flush-tables commands.

To execute the FLUSH command, you must have the reload privilege.

7.19 KILL syntax

KILL thread_id

Each connection to mysqld runs in a separate thread. You can see which threads are running with the SHOW PROCESSLIST command, and kill a thread with the KILL thread_id command.

If you do not have the process privilege, you can see and kill only your own threads.

You can also use mysqladmin processlist and mysqladmin kill to examine and kill threads.

7.20 SHOW syntax (Get information about tables, columns...)

   SHOW DATABASES [LIKE wild]
or SHOW TABLES [FROM db_name] [LIKE wild]
or SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]
or SHOW INDEX FROM tbl_name [FROM db_name]
or SHOW STATUS
or SHOW VARIABLES [LIKE wild]
or SHOW PROCESSLIST

SHOW provides information about databases, tables, columns or the server. If the LIKE wild part is used, the wild string should be a normal SQL wildcard string that uses the `%' and `_' wildcard characters.

Instead of using tbl_name FROM db_name syntax, you can also use db_name.tbl_name. These two statements are equivalent:

mysql> SHOW INDEX FROM mytable FROM mydb;
mysql> SHOW INDEX FROM mydb.mytable;

SHOW DATABASES lists the databases on the MySQL server host. You can also get this list using the mysqlshow command.

SHOW TABLES lists the tables in a given database. You can also get this list using the mysqlshow db_name command.

NOTE: If a user doesn't have any privileges for a table, the table will not show up when requesting a list of tables with SHOW TABLES or mysqlshow db_name.

SHOW FIELDS is a synonym for SHOW COLUMNS and SHOW KEYS is a synonym for SHOW INDEX. You can also list a table's columns or indexes with mysqlshow db_name tbl_name or mysqlshow -k db_name tbl_name.

SHOW STATUS provides server status information (like mysqladmin extended-status). The output resembles that shown below, though the format and numbers may differ somewhat:

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Created_tmp_tables     | 0     |
| Deletes                | 0     |
| Flush_tables           | 1     |
| Key_blocks_used        | 2     |
| Key_read_requests      | 4     |
| Key_reads              | 2     |
| Key_write_requests     | 0     |
| Key_writes             | 0     |
| Not_flushed_key_blocks | 0     |
| Open_tables            | 1     |
| Open_files             | 2     |
| Open_streams           | 0     |
| Opened_tables          | 12    |
| Questions              | 25    |
| Read_key               | 2     |
| Read_next              | 2     |
| Read_rnd               | 35    |
| Read_first             | 0     |
| Running_threads        | 1     |
| Slow_queries           | 0     |
| Uptime                 | 36944 |
| Write                  | 0     |
+------------------------+-------+

SHOW VARIABLES shows the values of the some of MySQL system variables. You can also get this information using the mysqladmin variables command. If the default values are unsuitable, you can set most of these variables using command-line options when mysqld starts up.

SHOW PROCESSLIST shows you which threads are running. You can also get this information using the mysqladmin processlist command. If you do not have the process privilege, you can see only your own threads. See section 7.19 KILL syntax.

7.21 EXPLAIN syntax (Get information about a SELECT)

EXPLAIN SELECT select_options

When you precede a SELECT statement with the keyword EXPLAIN, MySQL explains how it would process the SELECT, providing information about how tables are joined and in which order.

With the help of EXPLAIN, you can see when you must add indexes to tables to get a faster SELECT that uses indexes to find the records. You can also see if the optimizer joins the tables in an optimal order. To force the optimizer to use a specific join order for a SELECT statement, add a STRAIGHT_JOIN clause.

For non-simple joins, EXPLAIN returns a row of information for each table used in the SELECT statement. The tables are listed in the order they would be read. MySQL resolves all joins using a one-sweep multi-join method. This means that MySQL reads a row from the first table, then finds a matching row in the second table, then in the third table and so on. When all tables are processed, it outputs the selected columns and the table list is back-tracked until a table is found for which there are more matching rows. The next row is read from this table and the process continues with the next table.

Output from EXPLAIN includes the following columns:

table
The table to which the row of output refers.
type
The join type. Information about the various types is given below.
possible_keys
The possible_keys column indicates which indexes MySQL could use to find the rows in the table. If this column is empty, there are no relevant indexes. In this case, you may be able to improve the performance of your query by examining the WHERE clause to see if it refers to some column or columns that would be suitable for indexing. If so, create an appropriate index and check the query with EXPLAIN again. To see what indexes a table has, use SHOW INDEX FROM tbl_name.
key
The key column indicates the key that MySQL actually decided to use. The key is NULL if no index was chosen.
key_len
The key_len column indicates the length of the key that MySQL decided to use. The length is NULL if the key is NULL.
ref
The ref column shows which columns or constants are used with the key to select rows from the table.
rows
The rows column indicates the number of rows MySQL must examine to execute the query.
Extra
If the Extra column includes the text Only index, this means that only information from the index tree is used to retrieve information from the table (which should be much faster than scanning the entire table). If the Extra column includes the text where used, it means that a WHERE clause will be used to restrict which rows will be matched against the next table or sent to the client.

The different join types are listed below, ordered from best to worst type:

system
The table has only one row (= system table). This is a special case of the const join type.
const
The table has at most one matching row, which will be read at the start of the query. Since there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast as they are read only once!
eq_ref
One row will be read from this table for each combination of rows from the previous tables. This the best possible join type, other than the const types. It is used when all parts of an index are used by the join and the index is UNIQUE or a PRIMARY KEY.
ref
All rows with matching index values will be read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key, or if the key is not UNIQUE or a PRIMARY KEY (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this join type is good.
range
Only rows that are in a given range will be retrieved, using an index to select the rows. The ref column indicates which index is used.
index
This is the same as ALL, except that only the index tree is scanned. This is usually faster than ALL, as the index file is usually smaller than the data file.
ALL
A full table scan will be done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. You normally can avoid ALL by adding more indexes, so that the row can be retrieved based on constant values or column values from earlier tables.

You can get a good indication of how good a join is by multiplying all values in the rows column of the EXPLAIN output. This should tell you roughly how many rows MySQL must examine to execute the query. This number is also used when you restrict queries with the max_join_size variable. See section 10.1 Changing the size of MySQL buffers

The following example shows how a JOIN can be optimized progressively using the information provided by EXPLAIN.

Suppose you have the SELECT statement shown below, that you examine using EXPLAIN:

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
            tt.ProjectReference, tt.EstimatedShipDate,
            tt.ActualShipDate, tt.ClientID,
            tt.ServiceCodes, tt.RepetitiveID,
            tt.CurrentProcess, tt.CurrentDPPerson,
            tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
            et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
            AND tt.ActualPC = et.EMPLOYID
            AND tt.AssignedPC = et_1.EMPLOYID
            AND tt.ClientID = do.CUSTNMBR;

For this example, assume that:

Initially, before any optimizations have been performed, the EXPLAIN statement produces the following information:

table type possible_keys                key  key_len ref  rows  Extra
et    ALL  PRIMARY                      NULL NULL    NULL 74
do    ALL  PRIMARY                      NULL NULL    NULL 2135
et_1  ALL  PRIMARY                      NULL NULL    NULL 74
tt    ALL  AssignedPC,ClientID,ActualPC NULL NULL    NULL 3872
      range checked for each record (key map: 35)

This output indicates that MySQL is doing a full join for all tables---type is ALL for each table! This will take quite a long time, as the product of the number of rows in each table must be examined! For the case at hand, this is 74 * 2135 * 74 * 3872 = 45,268,558,720 rows. If the tables were bigger, you can only imagine how long it would take...

One problem here is that MySQL can't (yet) use indexes on columns efficiently if they are declared differently. VARCHAR and CHAR are not different in this context, unless they are not declared to be the same length. Since tt.ActualPC is declared as CHAR(10) and et.EMPLOYID is declared as CHAR(15), there is a length mismatch.

To fix this disparity between column lengths, use ALTER TABLE to lengthen ActualPC from 10 characters to 15 characters:

mysql> ALTER TABLE tt CHANGE ActualPC ActualPC VARCHAR(15);

Now tt.ActualPC and et.EMPLOYID are both VARCHAR(15). Executing the EXPLAIN statement again produces this result:

table type   possible_keys   key     key_len ref         rows    Extra
tt    ALL    AssignedPC,ClientID,ActualPC NULL NULL NULL 3872    where used
do    ALL    PRIMARY         NULL    NULL    NULL        2135
      range checked for each record (key map: 1)
et_1  ALL    PRIMARY         NULL    NULL    NULL        74
      range checked for each record (key map: 1)
et    eq_ref PRIMARY         PRIMARY 15      tt.ActualPC 1

This is not perfect, but is much better (the product of the rows values is now less by a factor of 74). This version is executed in a couple of seconds.

A second alteration can be made to eliminate the column length mismatches in the tt.AssignedPC = et_1.EMPLOYID and tt.ClientID = do.CUSTNMBR comparisons:

mysql> ALTER TABLE tt CHANGE AssignedPC AssignedPC VARCHAR(15),
                      CHANGE ClientID   ClientID   VARCHAR(15);

Now EXPLAIN produces the output shown below:

table type   possible_keys   key     key_len ref            rows     Extra
et    ALL    PRIMARY         NULL    NULL    NULL           74
tt    ref    AssignedPC,ClientID,ActualPC ActualPC 15 et.EMPLOYID 52 where used
et_1  eq_ref PRIMARY         PRIMARY 15      tt.AssignedPC  1
do    eq_ref PRIMARY         PRIMARY 15      tt.ClientID    1

This is "almost" as good as it can get.

The remaining problem is that, by default, MySQL assumes that values in the tt.ActualPC column are evenly distributed, and that isn't the case for the tt table. Fortunately, it is easy to tell MySQL about this:

shell> isamchk --analyze PATH_TO_MYSQL_DATABASE/tt
shell> mysqladmin refresh

Now the join is "perfect", and EXPLAIN produces this result:

table type   possible_keys   key     key_len ref            rows    Extra
tt    ALL    AssignedPC,ClientID,ActualPC NULL NULL NULL    3872    where used
et    eq_ref PRIMARY         PRIMARY 15      tt.ActualPC    1
et_1  eq_ref PRIMARY         PRIMARY 15      tt.AssignedPC  1
do    eq_ref PRIMARY         PRIMARY 15      tt.ClientID    1

7.22 DESCRIBE syntax (Get information about columns)

{DESCRIBE | DESC} tbl_name {col_name | wild}

DESCRIBE provides information about a table's columns. col_name may be a column name or a string containing the SQL `%' and `_' wildcard characters.

This statement is provided for Oracle compatibility.

The SHOW statement provides similar information. See section 7.20 SHOW syntax (Get information about tables, columns...).

7.23 LOCK TABLES/UNLOCK TABLES syntax

LOCK TABLES tbl_name [AS alias] {READ | [LOW_PRIORITY] WRITE}
            [, tbl_name {READ | [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.

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.

Each thread waits (without timing out) until it obtains all the locks it has requested.

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 time when there are no threads that have a READ lock.

When you use LOCK TABLES, you must lock all tables that you are going to use! If you are using a table multiple times in a query (with alias), you have to get a lock for each alias! This policy ensures that table locking is deadlock free.

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 7.3.11 Miscellaneous functions.

See section 10.9 How MySQL locks tables, for more information on locking policy.

7.24 SET OPTION syntax

SET [OPTION] SQL_VALUE_OPTION= value, ...

SET OPTION sets various options that affect the operation of the server or your client. Any option you set remains in effect until the current session ends, or until you set the option to a different value.

The options are:

CHARACTER SET character_set_name | DEFAULT
This maps all strings from and to the client with the given mapping. Currently the only option for character_set_name is cp1251_koi8, but you can easily add new mappings by editing the `sql/convert.cc' file in the MySQL source distribution. The default mapping can be restored by using a character_set_name value of DEFAULT. Note that the syntax for setting the CHARACTER SET option differs from the syntax for setting the other options.
PASSWORD = PASSWORD('some password')
Set the password for the current user. Any non-anonymous user can change his password!
PASSWORD FOR user = PASSWORD('some password')
Set the password for a specific user on the current host. Only a user with database access to the mysql database can do this. The user should be given in user@hostname format, where user and hostname are exactly as they are listed in the User and Host columns of the mysql.user table entry. For example, if you had an entry with User and Host fields of 'bob' and '%.loc.gov', you would write:
mysql> SET PASSWORD FOR bob@"%.loc.gov" = PASSWORD("newpass");
SQL_BIG_TABLES= 0 | 1
If set to 1, all temporary tables are stored on disk rather than in memory. This will be a little slower, but you will not get the error The table tbl_name is full for big SELECT operations that require a large temporary table. The default value for a new connection is 0 (i.e., use in-memory temporary tables).
SQL_BIG_SELECTS= 0 | 1
If set to 1, MySQL will abort if a SELECT is attempted that probably will take a very long time. This is useful when an inadvisable WHERE statement has been issued. A big query is defined as a SELECT that probably will have to examine more than max_join_size rows. The default value for a new connection is 0 (which will allow all SELECT statements).
SQL_LOW_PRIORITY_UPDATES= 0 | 1
If set to 1, all INSERT, UPDATE and DELETE statements wait until there is no pending SELECT on the affected table.
SQL_SELECT_LIMIT= value | DEFAULT
The maximum number of records to return from SELECT statements. If a SELECT has a LIMIT clause, the LIMIT takes precedence over the value of SQL_SELECT_LIMIT. The default value for a new connection is "unlimited". If you have changed the limit, the default value can be restored by using a SQL_SELECT_LIMIT value of DEFAULT.
SQL_LOG_OFF= 0 | 1
If set to 1, no logging will be done to the standard log for this client, if the client has the process privilege. This does not affect the update log!
SQL_UPDATE_LOG= 0 | 1
If set to 0, no logging will be done to the update log for the client, if the client has the process privilege. This does not affect the standard log!
TIMESTAMP= timestamp_value | DEFAULT
Set the time for this client. This is used to get the original timestamp if you use the update log to restore rows.
LAST_INSERT_ID= #
Set the value to be returned from LAST_INSERT_ID(). This is stored in the update log when you use LAST_INSERT_ID() in a command that updates a table.
INSERT_ID= #
Set the value to be used by the following INSERT command when inserting an AUTO_INCREMENT value. This is mainly used with the update log.

7.25 GRANT and REVOKE syntax

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    TO user_name [IDENTIFIED BY 'password']
        [, user_name [IDENTIFIED BY 'password'] ...]
    [WITH GRANT OPTION]

REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    FROM user_name [, user_name ...]

GRANT is implemented in MySQL 3.22.11 and up. For earlier MySQL versions, the GRANT statement does nothing.

The purpose of the GRANT and REVOKE commands is to enable system administrators to grant and revoke rights to MySQL users at four privilege levels:

Global level
Global privileges apply to all databases on the server. These privileges are stored in the mysql.user table.
Database level
Database privileges apply to all tables in a given database. These privileges are stored in the mysql.db and mysql.host tables.
Table level
Table privileges apply to all columns in a table. These privileges are stored in the mysql.tables_priv table.
Column level
Column privileges apply to single columns in a table. These privileges are stored in the mysql.columns_priv table.

For examples of how GRANT works, see section 6.9 Adding new user privileges to MySQL.

For the GRANT and REVOKE statements, priv_type may be specified as any of the following:

ALL PRIVILEGES      FILE                RELOAD
ALTER               INDEX               SELECT
CREATE              INSERT              SHUTDOWN
DELETE              PROCESS             UPDATE
DROP                REFERENCES          USAGE

ALL is a synonym for ALL PRIVILEGES. REFERENCES is not yet implemented. USAGE is currently a synonym for "no privileges". It can be used when you want to create a user that has no privileges.

To revoke the grant privilege from a user, use a priv_type value of GRANT OPTION:

REVOKE GRANT OPTION ON ... FROM ...;

The only priv_type values you can specify for a table are SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT, INDEX and ALTER.

The only priv_type values you can specify for a column (that is, when you use a column_list clause) are SELECT, INSERT and UPDATE.

You can set database privileges by using ON db_name.* syntax. If you specify ON * and you have a current database, you will set the privileges for that database. If you specify ON * and you don't have a current database, or if you specify ON *.*, you will affect the global privileges.

In order to accommodate granting rights to users from other hosts, MySQL supports specifying the user_name value in the form user@host. If you want to specify a user_name value containing wildcard characters or special characters (such as `%'), you can quote the user or host name (e.g., 'test-user'@'test-hostname').

You can specify wildcards in the hostname. For example, user@"%.loc.gov" applies to user for any host in the loc.gov domain, and user@"144.155.166.%" applies to user for any host in the 144.155.166 class C subnet.

The simple form user is a synonym for user@"%". NOTE: If you allow anonymous users (this is the default) to connect to the MySQL server, you should add all local user with username@localhost because else the anonymous user will be used when the user tries to log into the MySQL server from the same machine! Anonymous users is defined by inserting entries with user=" into the mysql.user table. You can verify if this applies to you by executing:

select host,user from mysql.user where user=";

For the moment, GRANT only supports host, table, database and column names up to 60 characters long. A user name can be up to 16 characters.

The total access rights for a table or column are formed from the logical OR of the privileges at each of the four privilege levels. For example, if the mysql.user table specifies that a user has a global select privilege, this can't be denied by an entry at the database, table or column level.

The total rights for a column can be calculated as follows:

global rights
OR (database rights AND host rights)
OR table rights
OR column rights

In most cases, you grant rights to a user at one of the different privilege levels, so life isn't normally as complicated as above. :) The details of the access-right checking procedure are presented in section 6 The MySQL access privilege system.

If you grant privileges for a user/hostname combination that does not exist in the mysql.user table, an entry is added and remains there until deleted with a DELETE command.

In MySQL 3.22.12 and up, if a new user is created or if you have global grant privileges, the user's password will be set to the password specified by the IDENTIFIED BY clause, if one is given. If the user already had a password, it is replaced by the new one.

Passwords can also be set with the SET PASSWORD command. See section 7.24 SET OPTION syntax.

If you grant privileges for a database, an entry in the mysql.db table is created if needed. When all privileges for the database have been removed with REVOKE, this entry is deleted.

If a user doesn't have any privileges on a table, the table is not displayed when the user requests a list of tables (e.g., with a SHOW TABLES statement).

The WITH GRANT OPTION clause gives the user the ability to give to other users any privileges the user has at the specified privilege level. You should be careful to whom you give the grant privilege, as two users with different privileges may be able to join privileges!

You cannot grant another user a privilege you don't have yourself; the grant privilege allows you to give away only those privileges you possess.

Be aware that when you grant a user the grant privilege at a particular privilege level, any privileges the user already possesses (or is given in the future!) at that level are also grantable by that user. Suppose you grant a user the insert privilege on a database. If you then grant the select privilege on the database and specify WITH GRANT OPTION, the user can give away not only the select privilege, but also insert. If you then grant the update privilege to the user on the database, the user can give away the insert, select and update.

You should not grant alter privileges to a normal user. If you do that, the user can try to subvert the privilege system by renaming tables!

Note that if you are using table/column privileges for even one user, MySQL examines table/column privileges for all users and this will slow down mysqld a bit.

When mysqld starts, all privileges are read into memory. Database, table and column privileges take effect at once and user level privileges take effect the next time the user connects. Modifications to the grant tables that you perform using GRANT or REVOKE are noticed by the server immediately. If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges to tell the server to reload the grant tables. See section 6.7 When privilege changes take effect.

The biggest differences between the ANSI SQL and MySQL versions of GRANT are:

7.26 CREATE INDEX syntax (Compatibility function)

CREATE [UNIQUE] 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 3.22 or later, CREATE INDEX is mapped to an ALTER TABLE call to create indexes. See section 7.7 ALTER TABLE syntax.

Normally, you create all indexes on a table at the time the table itself is created with CREATE TABLE. See section 7.6 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. The statement shown below creates an index using the first 10 characters of the name column:

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

Use of partial columns for indexes can make the index file much smaller. Since 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, it could save a lot of disk space and might also speed up INSERT operations!

For more information about how MySQL uses indexes, see section 10.4 How MySQL uses indexes.

7.27 DROP INDEX syntax (Compatibility function)

DROP INDEX index_name

DROP INDEX doesn't do anything in MySQL prior to version 3.22. In 3.22 or later, DROP INDEX is mapped to an ALTER TABLE call to drop the INDEX or UNIQUE definition. See section 7.7 ALTER TABLE syntax.

7.28 Comment syntax

MySQL supports the # 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 /* this is an in-line comment */ + 1;
mysql> select 1+
/*
this is a
multiple-line comment
*/
1;

MySQL doesn't support the `--' ANSI SQL comment style. See section 5.2.7 `--' as the start of a comment.

7.29 CREATE FUNCTION/DROP FUNCTION syntax

CREATE FUNCTION function_name RETURNS {STRING|REAL|INTEGER}
       SONAME shared_library_name

DROP FUNCTION function_name

A user-definable function (UDF) is a way to extend MySQL with a new function that works like native (built in) MySQL functions such as ABS() and CONCAT().

CREATE FUNCTION saves the function's name, type and shared library name in the system table func in the mysql database. You must have the insert and delete privileges for the mysql database to create and drop functions.

All active functions are reloaded each time the server starts, unless you start mysqld with the --skip-grant-tables option. In this case, UDF initialization is skipped and UDFs are unavailable. (An active function is one that has been loaded with CREATE FUNCTION and not removed with DROP FUNCTION.)

For the UDF mechanism to work, functions must be written in C or C++ and your operating system must support dynamic loading.

For information on how to write user-definable functions, see section 14 Adding new functions to MySQL.

7.30 Is MySQL picky about reserved words?

A common problem stems from trying to create a table with column names like TIMESTAMP or GROUP, the names of datatypes and functions built into MySQL. You're allowed to do it (for example, ABS is an allowed column name), but whitespace is not allowed between a function name and the `(' when using functions whose names are also column names.

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

action add all alter
after and as asc
auto_increment between bigint bit
binary blob bool both
by cascade char character
change check column columns
constraint create cross current_date
current_time current_timestamp data database
databases date datetime day
day_hour day_minute day_second dayofmonth
dayofweek dayofyear dec decimal
default delete desc describe
distinct distinctrow double drop
escaped enclosed enum explain
exists fields first float
float4 float8 foreign from
for full function grant
group having hour hour_minute
hour_second ignore in index
infile insert int integer
interval int1 int2 int3
int4 int8 into if
is join key keys
last_insert_id leading left like
lines limit load lock
long longblob longtext low_priority
match mediumblob mediumtext mediumint
middleint minute minute_second month
monthname natural numeric no
not null on option
optionally or order outer
outfile partial password precision
primary procedure processlist privileges
quarter read real references
rename regexp reverse repeat
replace restrict returns rlike
second select set show
smallint soname sql_big_tables sql_big_selects
sql_select_limit sql_low_priority_updates sql_log_off sql_log_update
straight_join starting status string
table tables terminated text
time timestamp tinyblob tinytext
tinyint trailing to use
using unique unlock unsigned
update usage values varchar
variables varying varbinary with
write where year year_month
zerofill

The following symbols (from the table above) are disallowed by ANSI SQL 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.


This document was generated on 3 January 1999 using the texi2html translator version 1.52 (extended by davida@detron.se).