MySQL has a very complex, but intuitive and easy to learn SQL interface. This chapter describes the various commands, types, and functions you will need to know in order to use MySQL efficiently and effectively. This chapter also serves as a reference to all functionality included in MySQL. In order to use this chapter effectively, you may find it useful to refer to the various indexes.
This section describes the various ways to write strings and numbers in MySQL. It also covers the various nuances and ``gotchas'' that you may run into when dealing with these basic types in MySQL.
A string is a sequence of characters, surrounded by either single quote (`'') or double quote (`"') characters (only the single quote if you run in ANSI mode). Examples:
'a string' "another string"
Within a string, certain sequences have special meaning. Each of these sequences begins with a backslash (`\'), known as the escape character. MySQL recognizes the following escape sequences:
\0
NUL)
character.
\'
\"
\b
\n
\r
\t
\z
mysql database <
filename).
\\
\%
\_
Note that if you use `\%' or `\_' in some string contexts, these will return the strings `\%' and `\_' and not `%' and `_'.
There are several ways to include quotes within a string:
The SELECT statements shown below demonstrate how quoting and
escaping work:
mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello'; +-------+---------+-----------+--------+--------+ | hello | "hello" | ""hello"" | hel'lo | 'hello | +-------+---------+-----------+--------+--------+ mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello"; +-------+---------+-----------+--------+--------+ | hello | 'hello' | ''hello'' | hel"lo | "hello | +-------+---------+-----------+--------+--------+ mysql> SELECT "This\nIs\nFour\nlines"; +--------------------+ | This Is Four lines | +--------------------+
If you want to insert binary data into a BLOB column, the
following characters must be represented by escape sequences:
NUL
\
'
"
If you write C code, you can use the C API function
mysql_escape_string() to escape characters for the
INSERT statement. See section 23.1.2
C API Function Overview. In Perl, you can use the quote method
of the DBI package to convert special characters to the proper
escape sequences. See section 23.2.2
The DBI Interface.
You should use an escape function on any string that might contain any of the special characters listed above!
Integers are represented as a sequence of digits. Floats use `.' as a decimal separator. Either type of number may be preceded by `-' to indicate a negative value.
Examples of valid integers:
1221 0 -32
Examples of valid floating-point numbers:
294.42 -32032.6809e+10 148.00
An integer may be used in a floating-point context; it is interpreted as the equivalent floating-point number.
MySQL supports hexadecimal values. In number context these act like an integer (64-bit precision). In string context these act like a binary string where each pair of hex digits is converted to a character:
mysql> SELECT 0xa+0;
-> 10
mysql> select 0x5061756c;
-> Paul
Hexadecimal strings are often used by ODBC to give values for BLOB columns.
NULL ValuesThe NULL value means ``no data'' and is different from values
such as 0 for numeric types or the empty string for string types.
See section 20.16
Problems with NULL Values.
NULL may be represented by \N when using the text
file import or export formats (LOAD DATA INFILE, SELECT ...
INTO OUTFILE). See section 7.23
LOAD DATA INFILE Syntax.
Database, table, index, column, and alias names all follow the same rules in MySQL.
Note that the rules changed starting with MySQL Version 3.23.6 when we introduced quoting of identifiers (database, table, and column names) with ``'. `"' will also work to quote identifiers if you run in ANSI mode. See section 5.2 Running MySQL in ANSI Mode.
| Identifier | Max length | Allowed characters |
| Database | 64 | Any character that is allowed in a directory name except `/' or `.'. |
| Table | 64 | Any character that is allowed in a file name, except `/' or `.'. |
| Column | 64 | All characters. |
| Alias | 255 | All characters. |
Note that in addition to the above, you can't have ASCII(0) or ASCII(255) or the quoting character in an identifier.
Note that if the identifier is a restricted word or contains special
characters you must always quote it with ` when you use it:
SELECT * from `select` where `select`.id > 100;
In previous versions of MySQL, the name rules are as follows:
--default-character-set option to mysqld. See
section 10.1.1
The Character Set Used for Data and Sorting.
It is recommended that you do not use names like 1e, because an
expression like 1e+1 is ambiguous. It may be interpreted as the
expression 1e + 1 or as the number 1e+1.
In MySQL you can refer to a column using any of the following forms:
| Column reference | Meaning |
col_name |
Column col_name from whichever table used in the query
contains a column of that name. |
tbl_name.col_name |
Column col_name from table tbl_name of the
current database. |
db_name.tbl_name.col_name |
Column col_name from table tbl_name of the
database db_name. This form is available in
MySQL Version 3.22 or later. |
`column_name` |
A column that is a keyword or contains special characters. |
You need not specify a tbl_name or db_name.tbl_name
prefix for a column reference in a statement unless the reference would be
ambiguous. For example, suppose tables t1 and t2 each
contain a column c, and you retrieve c in a
SELECT statement that uses both t1 and
t2. In this case, c is ambiguous because it is not
unique among the tables used in the statement, so you must indicate which table
you mean by writing t1.c or t2.c. Similarly, if you
are retrieving from a table t in database db1 and from
a table t in database db2, you must refer to columns
in those tables as db1.t.col_name and db2.t.col_name.
The syntax .tbl_name means
the table tbl_name in the current database. This syntax is accepted
for ODBC compatibility, because some ODBC programs prefix table names with a
`.' character.
In MySQL, databases and tables correspond to directories and files within those directories. Consequently, the case sensitivity of the underlying operating system determines the case sensitivity of database and table names. This means database and table names are case sensitive in Unix and case insensitive in Windows. See section 5.1 MySQL Extensions to ANSI SQL92.
NOTE: Although database and table names are case insensitive
for Windows, you should not refer to a given database or table using different
cases within the same query. The following query would not work because it
refers to a table both as my_table and as MY_TABLE:
mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;
Column names are case insensitive in all cases.
Aliases on tables are case sensitive. The following query would not work
because it refers to the alias both as a and as A:
mysql> SELECT col_name FROM tbl_name AS a
WHERE a.col_name = 1 OR A.col_name = 2;
Aliases on columns are case insensitive.
If you have a problem remembering the used cases for a table names, adopt a consistent convention, such as always creating databases and tables using lowercase names.
One way to avoid this problem is to start mysqld with -O
lower_case_table_names=1. By default this option is 1 on windows and 0 on
Unix.
If lower_case_table_names is 1 MySQL will
convert all table names to lower case on storage and lookup. Note that if you
change this option, you need to first convert your old table names to lower case
before starting mysqld.
MySQL supports thread-specific variables with the
@variablename syntax. A variable name may consist of alphanumeric
characters from the current character set and also `_',
`$', and `.' . The default character set is ISO-8859-1
Latin1; this may be changed with the --default-character-set option
to mysqld. See section 10.1.1
The Character Set Used for Data and Sorting.
Variables don't have to be initialized. They contain NULL by
default and can store an integer, real, or string value. All variables for a
thread are automatically freed when the thread exits.
You can set a variable with the SET syntax:
SET @variable= { integer expression | real expression | string expression }
[,@variable= ...].
You can also set a variable in an expression with the
@variable:=expr syntax:
select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3; +----------------------+------+------+------+ | @t1:=(@t2:=1)+@t3:=4 | @t1 | @t2 | @t3 | +----------------------+------+------+------+ | 5 | 5 | 1 | 4 | +----------------------+------+------+------+
(We had to use the := syntax here, because = was
reserved for comparisons.)
User variables may be used where expressions are allowed. Note that this does
not currently include use in contexts where a number is explicitly required,
such as in the LIMIT clause of a SELECT statement, or
the IGNORE number LINES clause of a LOAD DATA
statement.
NOTE: In a SELECT statement, each expression is
only evaluated when it's sent to the client. This means that in the
HAVING, GROUP BY, or ORDER BY clause, you
can't refer to an expression that involves variables that are set in the
SELECT part. For example, the following statement will NOT work as
expected:
SELECT (@aa:=id) AS a, (@aa+3) AS b FROM table_name HAVING b=5;
The reason is that @aa will not contain the value of the current
row, but the value of id for the previous accepted row.
MySQL supports a number of column types, which may be grouped into three categories: numeric types, date and time types, and string (character) types. This section first gives an overview of the types available and summarizes the storage requirements for each column type, then provides a more detailed description of the properties of the types in each category. The overview is intentionally brief. The more detailed descriptions should be consulted for additional information about particular column types, such as the allowable formats in which you can specify values.
The column types supported by MySQL are listed below. The following code letters are used in the descriptions:
M
D
M-2. Square brackets (`[' and `]') indicate parts of type specifiers that are optional.
Note that if you specify ZEROFILL for a column,
MySQL will automatically add the UNSIGNED
attribute to the column.
TINYINT[(M)] [UNSIGNED] [ZEROFILL]
-128 to 127. The unsigned range is 0 to
255.
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
-32768 to
32767. The unsigned range is 0 to
65535.
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
-8388608 to
8388607. The unsigned range is 0 to
16777215.
INT[(M)] [UNSIGNED] [ZEROFILL]
-2147483648 to
2147483647. The unsigned range is 0 to
4294967295.
INTEGER[(M)] [UNSIGNED] [ZEROFILL]
INT.
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
-9223372036854775808 to
9223372036854775807. The unsigned range is 0 to
18446744073709551615. Some things you should be aware about
BIGINT columns:
BIGINT or DOUBLE values, so you shouldn't use
unsigned big integers larger than 9223372036854775807 (63 bits)
except with bit functions! If you do that, some of the last digits in the
result may be wrong because of rounding errors when converting the
BIGINT to a DOUBLE.
BIGINT
column by storing it as a string, as there is in this case there will be no
intermediate double representation.
BIGINT arithmetic when both arguments are INTEGER
values! This means that if you multiply two big integers (or results from
functions that return integers) you may get unexpected results when the
result is larger than 9223372036854775807. FLOAT(precision) [ZEROFILL]
precision can be
<=24 for a single-precision floating-point number and between
25 and 53 for a double-precision floating-point number. These types are like
the FLOAT and DOUBLE types described immediately
below. FLOAT(X) has the same range as the corresponding
FLOAT and DOUBLE types, but the display size and
number of decimals is undefined. In MySQL Version 3.23, this
is a true floating-point value. In earlier MySQL versions,
FLOAT(precision) always has 2 decimals. Note that using
FLOAT may give you some unexpected problems as all calculation in
MySQL is done with double precision. See section 20.19
Solving Problems with No Matching Rows. This syntax is provided for ODBC compatibility.
FLOAT[(M,D)] [ZEROFILL]
-3.402823466E+38 to
-1.175494351E-38, 0, and
1.175494351E-38 to 3.402823466E+38. The M is the
display width and D is the number of decimals. FLOAT without an
argument or with an argument of <= 24 stands for a single-precision
floating-point number.
DOUBLE[(M,D)] [ZEROFILL]
-1.7976931348623157E+308 to
-2.2250738585072014E-308, 0, and
2.2250738585072014E-308 to 1.7976931348623157E+308.
The M is the display width and D is the number of decimals.
DOUBLE without an argument or FLOAT(X) where 25
<= X <= 53 stands for a double-precision floating-point number.
DOUBLE PRECISION[(M,D)] [ZEROFILL]
REAL[(M,D)] [ZEROFILL]
DOUBLE.
DECIMAL[(M[,D])] [ZEROFILL]
CHAR column: ``unpacked'' means the number is stored as a string,
using one character for each digit of the value. The decimal point and, for
negative numbers, the `-' sign, are not counted in M (but space
for these are reserved). If D is 0, values will have no decimal
point or fractional part. The maximum range of DECIMAL values is
the same as for DOUBLE, but the actual range for a given
DECIMAL column may be constrained by the choice of M
and D. If D is left out it's set to 0. If
M is left out it's set to 10. Note that in MySQL
Version 3.22 the M argument had to includes the space needed for
the sign and the decimal point.
NUMERIC(M,D) [ZEROFILL]
DECIMAL.
DATE
'1000-01-01' to
'9999-12-31'. MySQL displays DATE
values in 'YYYY-MM-DD' format, but allows you to assign values to
DATE columns using either strings or numbers. See section 7.3.3.2
The DATETIME, DATE, and TIMESTAMP
Types.
DATETIME
'1000-01-01
00:00:00' to '9999-12-31 23:59:59'. MySQL
displays DATETIME values in 'YYYY-MM-DD HH:MM:SS'
format, but allows you to assign values to DATETIME columns using
either strings or numbers. See section 7.3.3.2
The DATETIME, DATE, and TIMESTAMP
Types.
TIMESTAMP[(M)]
'1970-01-01 00:00:00' to sometime
in the year 2037. MySQL displays
TIMESTAMP values in YYYYMMDDHHMMSS,
YYMMDDHHMMSS, YYYYMMDD, or YYMMDD
format, depending on whether M is 14 (or missing),
12, 8, or 6, but allows you to assign
values to TIMESTAMP columns using either strings or numbers. A
TIMESTAMP column is useful for recording the date and time of an
INSERT or UPDATE operation because it is
automatically set to the date and time of the most recent operation if you
don't give it a value yourself. You can also set it to the current date and
time by assigning it a NULL value. See section 7.3.3
Date and Time Types. A TIMESTAMP is always stored in 4 bytes.
The M argument only affects how the TIMESTAMP column
is displayed. Note that TIMESTAMP(X) columns where X is 8 or 14
are reported to be numbers while other TIMESTAMP(X) columns are
reported to be strings. This is just to ensure that one can reliably dump and
restore the table with these types! See section 7.3.3.2
The DATETIME, DATE, and TIMESTAMP
Types.
TIME
'-838:59:59' to
'838:59:59'. MySQL displays TIME
values in 'HH:MM:SS' format, but allows you to assign values to
TIME columns using either strings or numbers. See section 7.3.3.3
The TIME Type.
YEAR[(2|4)]
1901 to 2155, 0000 in the 4-digit
year format, and 1970-2069 if you use the 2-digit format (70-69).
MySQL displays YEAR values in YYYY
format, but allows you to assign values to YEAR columns using
either strings or numbers. (The YEAR type is new in
MySQL Version 3.22.). See section 7.3.3.4
The YEAR Type.
[NATIONAL] CHAR(M) [BINARY]
M is 1 to 255
characters. Trailing spaces are removed when the value is retrieved.
CHAR values are sorted and compared in case-insensitive fashion
according to the default character set unless the BINARY keyword
is given. NATIONAL CHAR (short form NCHAR) is the
ANSI SQL way to define that a CHAR column should use the default CHARACTER
set. This is the default in MySQL. CHAR is a
shorthand for CHARACTER. MySQL allows you to
create a column of type CHAR(0). This is mainly useful when you
have to be compliant with some old applications that depend on the existence
of a column but that do not actually use the value. This is also quite nice
when you need a column that only can take 2 values: A CHAR(0),
that is not defined as NOT NULL, will only occupy one bit and can
only take 2 values: NULL or "". See section 7.3.4.1
The CHAR and VARCHAR Types.
[NATIONAL] VARCHAR(M) [BINARY]
M is 1 to 255 characters.
VARCHAR values are sorted and compared in case-insensitive
fashion unless the BINARY keyword is given. See section 7.7.1
Silent Column Specification Changes. VARCHAR is a shorthand
for CHARACTER VARYING. See section 7.3.4.1
The CHAR and VARCHAR Types.
TINYBLOB
TINYTEXT
BLOB or TEXT column with a maximum length of
255 (2^8 - 1) characters. See section 7.7.1
Silent Column Specification Changes. See section 7.3.4.2
The BLOB and TEXT Types.
BLOB
TEXT
BLOB or TEXT column with a maximum length of
65535 (2^16 - 1) characters. See section 7.7.1
Silent Column Specification Changes. See section 7.3.4.2
The BLOB and TEXT Types.
MEDIUMBLOB
MEDIUMTEXT
BLOB or TEXT column with a maximum length of
16777215 (2^24 - 1) characters. See section 7.7.1
Silent Column Specification Changes. See section 7.3.4.2
The BLOB and TEXT Types.
LONGBLOB
LONGTEXT
BLOB or TEXT column with a maximum length of
4294967295 (2^32 - 1) characters. See section 7.7.1
Silent Column Specification Changes. Note that because the server/client
protocol and MyISAM tables has currently a limit of 16M per communication
packet / table row, you can't yet use this the whole range of this type. See
section 7.3.4.2
The BLOB and TEXT Types.
ENUM('value1','value2',...)
'value1', 'value2',
..., NULL or the special "" error
value. An ENUM can have a maximum of 65535 distinct values. See
section 7.3.4.3
The ENUM Type.
SET('value1','value2',...)
'value1',
'value2', ... A SET can have a maximum
of 64 members. See section 7.3.4.4
The SET Type. The storage requirements for each of the column types supported by MySQL are listed below by category.
| Column type | Storage required |
TINYINT |
1 byte |
SMALLINT |
2 bytes |
MEDIUMINT |
3 bytes |
INT |
4 bytes |
INTEGER |
4 bytes |
BIGINT |
8 bytes |
FLOAT(X) |
4 if X <= 24 or 8 if 25 <= X <= 53 |
FLOAT |
4 bytes |
DOUBLE |
8 bytes |
DOUBLE PRECISION |
8 bytes |
REAL |
8 bytes |
DECIMAL(M,D) |
M+2 bytes if D > 0, M+1 bytes if D = 0
(D+2, if M < D) |
NUMERIC(M,D) |
M+2 bytes if D > 0, M+1 bytes if D = 0
(D+2, if M < D) |
| Column type | Storage required |
DATE |
3 bytes |
DATETIME |
8 bytes |
TIMESTAMP |
4 bytes |
TIME |
3 bytes |
YEAR |
1 byte |
| Column type | Storage required |
CHAR(M) |
M bytes, 1 <= M <= 255 |
VARCHAR(M) |
L+1 bytes, where L <= M and 1 <=
M <= 255 |
TINYBLOB, TINYTEXT |
L+1 bytes, where L < 2^8 |
BLOB, TEXT |
L+2 bytes, where L < 2^16 |
MEDIUMBLOB, MEDIUMTEXT |
L+3 bytes, where L < 2^24 |
LONGBLOB, LONGTEXT |
L+4 bytes, where L < 2^32 |
ENUM('value1','value2',...) |
1 or 2 bytes, depending on the number of enumeration values (65535 values maximum) |
SET('value1','value2',...) |
1, 2, 3, 4 or 8 bytes, depending on the number of set members (64 members maximum) |
VARCHAR
and the BLOB and TEXT types are variable-length types,
for which the storage requirements depend on the actual length of column values
(represented by L in the preceding table), rather than on the
type's maximum possible size. For example, a VARCHAR(10) column can
hold a string with a maximum length of 10 characters. The actual storage
required is the length of the string (L), plus 1 byte to record the
length of the string. For the string 'abcd', L is 4
and the storage requirement is 5 bytes.
The BLOB and TEXT types require 1, 2, 3, or 4 bytes
to record the length of the column value, depending on the maximum possible
length of the type. See section 7.3.4.2
The BLOB and TEXT Types.
If a table includes any variable-length column types, the record format will also be variable-length. Note that when a table is created, MySQL may, under certain conditions, change a column from a variable-length type to a fixed-length type, or vice-versa. See section 7.7.1 Silent Column Specification Changes.
The size of an ENUM object is determined by
the number of different enumeration values. One byte is used for enumerations
with up to 255 possible values. Two bytes are used for enumerations with up to
65535 values. See section 7.3.4.3
The ENUM Type.
The size of a SET object is determined by the
number of different set members. If the set size is N, the object
occupies (N+7)/8 bytes, rounded up to 1, 2, 3, 4, or 8 bytes. A
SET can have a maximum of 64 members. See section 7.3.4.4
The SET Type.
MySQL supports all of the ANSI/ISO SQL92 numeric types.
These types include the exact numeric data types (NUMERIC,
DECIMAL, INTEGER, and SMALLINT), as well
as the approximate numeric data types (FLOAT, REAL,
and DOUBLE PRECISION). The keyword INT is a synonym
for INTEGER, and the keyword DEC is a synonym for
DECIMAL.
The NUMERIC and DECIMAL types are implemented as
the same type by MySQL, as permitted by the SQL92 standard.
They are used for values for which it is important to preserve exact precision,
for example with monetary data. When declaring a column of one of these types
the precision and scale can be (and usually is) specified; for example:
salary DECIMAL(9,2)
In this example, 9 (precision) represents the
number of significant decimal digits that will be stored for values, and
2 (scale) represents the number of digits that will be
stored following the decimal point. In this case, therefore, the range of values
that can be stored in the salary column is from
-9999999.99 to 9999999.99. In ANSI/ISO SQL92, the
syntax DECIMAL(p) is equivalent to DECIMAL(p,0).
Similarly, the syntax DECIMAL is equivalent to
DECIMAL(p,0), where the implementation is allowed to decide the
value of p. MySQL does not currently support
either of these variant forms of the DECIMAL/NUMERIC
data types. This is not generally a serious problem, as the principal benefits
of these types derive from the ability to control both precision and scale
explicitly.
DECIMAL and NUMERIC values are stored as strings,
rather than as binary floating-point numbers, in order to preserve the decimal
precision of those values. One character is used for each digit of the value,
the decimal point (if scale > 0), and the `-' sign
(for negative numbers). If scale is 0, DECIMAL and
NUMERIC values contain no decimal point or fractional part.
The maximum range of DECIMAL and NUMERIC values is
the same as for DOUBLE, but the actual range for a given
DECIMAL or NUMERIC column can be constrained by the
precision or scale for a given column. When such a
column is assigned a value with more digits following the decimal point than are
allowed by the specified scale, the value is rounded to that
scale. When a DECIMAL or NUMERIC column
is assigned a value whose magnitude exceeds the range implied by the specified
(or defaulted) precision and scale,
MySQL stores the value representing the corresponding end point
of that range.
As an extension to the ANSI/ISO SQL92 standard, MySQL also
supports the integral types TINYINT, MEDIUMINT, and
BIGINT as listed in the tables above. Another extension is
supported by MySQL for optionally specifying the display width
of an integral value in parentheses following the base keyword for the type (for
example, INT(4)). This optional width specification is used to
left-pad the display of values whose width is less than the width specified for
the column, but does not constrain the range of values that can be stored in the
column, nor the number of digits that will be displayed for values whose width
exceeds that specified for the column. When used in conjunction with the
optional extension attribute ZEROFILL, the default padding of
spaces is replaced with zeroes. For example, for a column declared as
INT(5) ZEROFILL, a value of 4 is retrieved as
00004. Note that if you store larger values than the display width
in an integer column, you may experience problems when MySQL
generates temporary tables for some complicated joins, as in these cases
MySQL trusts that the data did fit into the original column
width.
All integral types can have an optional (non-standard) attribute
UNSIGNED. Unsigned values can be used when you want to allow only
positive numbers in a column and you need a little bigger numeric range for the
column.
The FLOAT type is used to represent approximate numeric data
types. The ANSI/ISO SQL92 standard allows an optional specification of the
precision (but not the range of the exponent) in bits following the keyword
FLOAT in parentheses. The MySQL implementation
also supports this optional precision specification. When the keyword
FLOAT is used for a column type without a precision specification,
MySQL uses four bytes to store the values. A variant syntax is
also supported, with two numbers given in parentheses following the
FLOAT keyword. With this option, the first number continues to
represent the storage requirements for the value in bytes, and the second number
specifies the number of digits to be stored and displayed following the decimal
point (as with DECIMAL and NUMERIC). When
MySQL is asked to store a number for such a column with more
decimal digits following the decimal point than specified for the column, the
value is rounded to eliminate the extra digits when the value is stored.
The REAL and DOUBLE PRECISION types do not accept
precision specifications. As an extension to the ANSI/ISO SQL92 standard,
MySQL recognizes DOUBLE as a synonym for the
DOUBLE PRECISION type. In contrast with the standard's requirement
that the precision for REAL be smaller than that used for
DOUBLE PRECISION, MySQL implements both as 8-byte
double-precision floating-point values (when not running in ``ANSI mode''). For
maximum portability, code requiring storage of approximate numeric data values
should use FLOAT or DOUBLE PRECISION with no
specification of precision or number of decimal points.
When asked to store a value in a numeric column that is outside the column type's allowable range, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead.
For example, the range of an INT column is
-2147483648 to 2147483647. If you try to insert
-9999999999 into an INT column, the value is clipped
to the lower endpoint of the range, and -2147483648 is stored
instead. Similarly, if you try to insert 9999999999,
2147483647 is stored instead.
If the INT column is UNSIGNED, the size of the
column's range is the same but its endpoints shift up to 0 and
4294967295. If you try to store -9999999999 and
9999999999, the values stored in the column become 0
and 4294967296.
Conversions that occur due to clipping are reported as ``warnings'' for
ALTER TABLE, LOAD DATA INFILE, UPDATE,
and multi-row INSERT statements.
The date and time types are DATETIME, DATE,
TIMESTAMP, TIME, and YEAR. Each of these
has a range of legal values, as well as a ``zero'' value that is used when you
specify a really illegal value. Note that MySQL allows you to
store certain 'not strictly' legal date values, for example
1999-11-31. The reason for this is that we think it's the
responsibility of the application to handle date checking, not the SQL servers.
To make the date checking 'fast', MySQL only checks that the
month is in the range of 0-12 and the day is in the range of 0-31. The above
ranges are defined this way because MySQL allows you to store,
in a DATE or DATETIME column, dates where the day or
month-day is zero. This is extremely useful for applications that need to store
a birth-date for which you don't know the exact date. In this case you simply
store the date like 1999-00-00 or 1999-01-00. (You
cannot expect to get a correct value from functions like DATE_SUB()
or DATE_ADD for dates like these.)
Here are some general considerations to keep in mind when working with date and time types:
'98-09-04'),
rather than in the month-day-year or day-month-year orders commonly used
elsewhere (for example, '09-04-98', '04-09-98').
TIME values are clipped to the
appropriate endpoint of the TIME range.) The table below shows
the format of the ``zero'' value for each type:
| Column type | ``Zero'' value |
DATETIME |
'0000-00-00 00:00:00' |
DATE |
'0000-00-00' |
TIMESTAMP |
00000000000000 (length depends on display size) |
TIME |
'00:00:00' |
YEAR |
0000 |
'0' or 0, which are easier to write.
NULL in MyODBC
Version 2.50.12 and above, because ODBC can't handle such values. MySQL itself is Y2K-safe (see section 1.8 Year 2000 Compliance), but input values presented to MySQL may not be. Any input containing 2-digit year values is ambiguous, because the century is unknown. Such values must be interpreted into 4-digit form because MySQL stores years internally using four digits.
For DATETIME, DATE, TIMESTAMP, and
YEAR types, MySQL interprets dates with ambiguous
year values using the following rules:
00-69 are converted to
2000-2069.
70-99 are converted to
1970-1999. Remember that these rules provide only reasonable guesses as to what your data mean. If the heuristics used by MySQL don't produce the correct values, you should provide unambiguous input containing 4-digit year values.
ORDER BY will sort 2-digit YEAR/DATE/DATETIME types
properly.
Note also that some functions like MIN() and MAX()
will convert a TIMESTAMP/DATE to a number. This means that a
timestamp with a 2-digit year will not work properly with these functions. The
fix in this case is to convert the TIMESTAMP/DATE to 4-digit year
format or use something like MIN(DATE_ADD(timestamp,INTERVAL 0
DAYS)).
DATETIME, DATE, and TIMESTAMP
TypesThe DATETIME, DATE, and TIMESTAMP
types are related. This section describes their characteristics, how they are
similar, and how they differ.
The DATETIME type is used when you need values that contain both
date and time information. MySQL retrieves and displays
DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The
supported range is '1000-01-01 00:00:00' to '9999-12-31
23:59:59'. (``Supported'' means that although earlier values might work,
there is no guarantee that they will.)
The DATE type is used when you need only a date value, without a
time part. MySQL retrieves and displays DATE
values in 'YYYY-MM-DD' format. The supported range is
'1000-01-01' to '9999-12-31'.
The TIMESTAMP column type provides a type that you can use to
automatically mark INSERT or UPDATE operations with
the current date and time. If you have multiple TIMESTAMP columns,
only the first one is updated automatically.
Automatic updating of the first TIMESTAMP column occurs under
any of the following conditions:
INSERT or
LOAD DATA INFILE statement.
UPDATE statement
and some other column changes value. (Note that an UPDATE that
sets a column to the value it already has will not cause the
TIMESTAMP column to be updated, because if you set a column to
its current value, MySQL ignores the update for efficiency.)
TIMESTAMP column to NULL.
TIMESTAMP columns other than the first may also be set to the
current date and time. Just set the column to NULL or to
NOW().
You can set any TIMESTAMP column to a value different than the
current date and time by setting it explicitly to the desired value. This is
true even for the first TIMESTAMP column. You can use this property
if, for example, you want a TIMESTAMP to be set to the current date
and time when you create a row, but not to be changed whenever the row is
updated later:
TIMESTAMP column explicitly to its current value. On the other hand, you may find it just as easy to use a
DATETIME column that you initialize to NOW() when the
row is created and leave alone for subsequent updates.
TIMESTAMP values may range from the beginning of 1970 to
sometime in the year 2037, with a resolution of one second. Values are displayed
as numbers.
The format in which MySQL retrieves and displays
TIMESTAMP values depends on the display size, as illustrated by the
table below. The `full' TIMESTAMP format is 14 digits, but
TIMESTAMP columns may be created with shorter display sizes:
| Column type | Display format |
TIMESTAMP(14) |
YYYYMMDDHHMMSS |
TIMESTAMP(12) |
YYMMDDHHMMSS |
TIMESTAMP(10) |
YYMMDDHHMM |
TIMESTAMP(8) |
YYYYMMDD |
TIMESTAMP(6) |
YYMMDD |
TIMESTAMP(4) |
YYMM |
TIMESTAMP(2) |
YY |
All TIMESTAMP columns have the same storage size, regardless of
display size. The most common display sizes are 6, 8, 12, and 14. You can
specify an arbitrary display size at table creation time, but values of 0 or
greater than 14 are coerced to 14. Odd-valued sizes in the range from 1 to 13
are coerced to the next higher even number.
You can specify DATETIME, DATE, and
TIMESTAMP values using any of a common set of formats:
'YYYY-MM-DD HH:MM:SS' or
'YY-MM-DD HH:MM:SS' format. A ``relaxed'' syntax is allowed--any
punctuation character may be used as the delimiter between date parts or time
parts. For example, '98-12-31 11:30:45', '98.12.31
11+30+45', '98/12/31 11*30*45', and '98@12@31
11^30^45' are equivalent.
'YYYY-MM-DD' or 'YY-MM-DD'
format. A ``relaxed'' syntax is allowed here, too. For example,
'98-12-31', '98.12.31', '98/12/31', and
'98@12@31' are equivalent.
'YYYYMMDDHHMMSS' or
'YYMMDDHHMMSS' format, provided that the string makes sense as a
date. For example, '19970523091528' and
'970523091528' are interpreted as '1997-05-23
09:15:28', but '971122129015' is illegal (it has a
nonsensical minute part) and becomes '0000-00-00 00:00:00'.
'YYYYMMDD' or
'YYMMDD' format, provided that the string makes sense as a date.
For example, '19970523' and '970523' are interpreted
as '1997-05-23', but '971332' is illegal (it has
nonsensical month and day parts) and becomes '0000-00-00'.
YYYYMMDDHHMMSS or
YYMMDDHHMMSS format, provided that the number makes sense as a
date. For example, 19830905132800 and 830905132800
are interpreted as '1983-09-05 13:28:00'.
YYYYMMDD or YYMMDD format,
provided that the number makes sense as a date. For example,
19830905 and 830905 are interpreted as
'1983-09-05'.
DATETIME, DATE, or TIMESTAMP context,
such as NOW() or CURRENT_DATE. Illegal DATETIME, DATE, or TIMESTAMP
values are converted to the ``zero'' value of the appropriate type
('0000-00-00 00:00:00', '0000-00-00', or
00000000000000).
For values specified as strings that include date part delimiters, it is not
necessary to specify two digits for month or day values that are less than
10. '1979-6-9' is the same as
'1979-06-09'. Similarly, for values specified as strings that
include time part delimiters, it is not necessary to specify two digits for
hour, month, or second values that are less than 10.
'1979-10-30 1:2:3' is the same as '1979-10-30
01:02:03'.
Values specified as numbers should be 6, 8, 12, or 14 digits long. If the
number is 8 or 14 digits long, it is assumed to be in YYYYMMDD or
YYYYMMDDHHMMSS format and that the year is given by the first 4
digits. If the number is 6 or 12 digits long, it is assumed to be in
YYMMDD or YYMMDDHHMMSS format and that the year is
given by the first 2 digits. Numbers that are not one of these lengths are
interpreted as though padded with leading zeros to the closest length.
Values specified as non-delimited
strings are interpreted using their length as given. If the string is 8 or 14
characters long, the year is assumed to be given by the first 4 characters.
Otherwise the year is assumed to be given by the first 2 characters. The string
is interpreted from left to right to find year, month, day, hour, minute, and
second values, for as many parts as are present in the string. This means you
should not use strings that have fewer than 6 characters. For example, if you
specify '9903', thinking that will represent March, 1999, you will
find that MySQL inserts a ``zero'' date into your table. This
is because the year and month values are 99 and 03,
but the day part is missing (zero), so the value is not a legal date.
TIMESTAMP columns store legal values using the full precision
with which the value was specified, regardless of the display size. This has
several implications:
TIMESTAMP(4) or TIMESTAMP(2). Otherwise, the value
will not be a legal date and 0 will be stored.
ALTER TABLE to widen a narrow
TIMESTAMP column, information will be displayed that previously
was ``hidden''.
TIMESTAMP column does not cause
information to be lost, except in the sense that less information is shown
when the values are displayed.
TIMESTAMP values are stored to full precision, the
only function that operates directly on the underlying stored value is
UNIX_TIMESTAMP(). Other functions operate on the formatted
retrieved value. This means you cannot use functions such as
HOUR() or SECOND() unless the relevant part of the
TIMESTAMP value is included in the formatted value. For example,
the HH part of a TIMESTAMP column is not displayed
unless the display size is at least 10, so trying to use HOUR()
on shorter TIMESTAMP values produces a meaningless result.
You can to some extent assign values of one date type to an object of a different date type. However, there may be some alteration of the value or loss of information:
DATE value to a DATETIME or
TIMESTAMP object, the time part of the resulting value is set to
'00:00:00', because the DATE value contains no time
information.
DATETIME or TIMESTAMP value to a
DATE object, the time part of the resulting value is deleted,
because the DATE type stores no time information.
DATETIME, DATE, and
TIMESTAMP values all can be specified using the same set of
formats, the types do not all have the same range of values. For example,
TIMESTAMP values cannot be earlier than 1970 or
later than 2037. This means that a date such as
'1968-01-01', while legal as a DATETIME or
DATE value, is not a valid TIMESTAMP value and will
be converted to 0 if assigned to such an object. Be aware of certain pitfalls when specifying date values:
'10:11:12' might look
like a time value because of the `:' delimiter, but if used in a
date context will be interpreted as the year '2010-11-12'. The
value '10:45:15' will be converted to '0000-00-00'
because '45' is not a legal month.
00-69 are converted to
2000-2069.
70-99 are converted to
1970-1999. TIME TypeMySQL 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:
'D HH:MM:SS.fraction' format. (Note that
MySQL doesn't yet store the fraction for the time column).
One can also use one of the following ``relaxed'' syntax:
HH:MM:SS.fraction, HH:MM:SS, HH:MM,
D HH:MM:SS, D HH:MM, D HH or
SS. Here D is days between 0-33.
'HHMMSS' format, provided
that it makes sense as a time. For example, '101112' is
understood as '10:11:12', but '109712' is illegal
(it has a nonsensical minute part) and becomes '00:00:00'.
HHMMSS format, provided that it makes sense as
a time. For example, 101112 is understood as
'10:11:12'. The following alternative formats are also
understood: SS, MMSS,HHMMSS,
HHMMSS.fraction. Note that MySQL doesn't yet
store the fraction part.
TIME context, such as CURRENT_TIME. For TIME values specified as strings that include a time part
delimiter, it is not necessary to specify two digits for hours, minutes, or
seconds values that are less than 10. '8:3:2' is the
same as '08:03:02'.
Be careful about assigning ``short'' TIME values to a
TIME column. Without semicolon, MySQL interprets
values using the assumption that the rightmost digits represent seconds.
(MySQL interprets TIME values as elapsed time
rather than as time of day.) For example, you might think of '1112'
and 1112 as meaning '11:12:00' (12 minutes after 11
o'clock), but MySQL interprets them as '00:11:12'
(11 minutes, 12 seconds). Similarly, '12' and 12 are
interpreted as '00:00:12'. TIME values with semicolon,
instead, are always treated as time of the day. That is '11:12'
will mean '11:12:00', not '00:11:12'.
Values that lie outside the TIME range but are otherwise legal
are clipped to the appropriate endpoint of the range. For example,
'-850:00:00' and '850:00:00' are converted to
'-838:59:59' and '838:59:59'.
Illegal TIME values are converted to '00:00:00'.
Note that because '00:00:00' is itself a legal TIME
value, there is no way to tell, from a value of '00:00:00' stored
in a table, whether the original value was specified as '00:00:00'
or whether it was illegal.
YEAR TypeThe 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:
'1901' to
'2155'.
1901 to
2155.
'00' to '99'.
Values in the ranges '00' to '69' and
'70' to '99' are converted to YEAR
values in the ranges 2000 to 2069 and
1970 to 1999.
1 to 99.
Values in the ranges 1 to 69 and 70 to
99 are converted to YEAR values in the ranges
2001 to 2069 and 1970 to
1999. Note that the range for two-digit numbers is slightly
different than the range for two-digit strings, because you cannot specify
zero directly as a number and have it be interpreted as 2000. You
must specify it as a string '0' or '00' or
it will be interpreted as 0000.
YEAR context, such as NOW(). Illegal YEAR values are converted to 0000.
The string types are CHAR, VARCHAR,
BLOB, TEXT, ENUM, and SET.
This section describes how these types work, their storage requirements, and how
to use them in your queries.
CHAR and VARCHAR TypesThe CHAR and VARCHAR types are similar, but differ
in the way they are stored and retrieved.
The length of a CHAR column is fixed to the length that you
declare when you create the table. The length can be any value between 1 and
255. (As of MySQL Version 3.23, the length of CHAR
may be 0 to 255.) When CHAR values are stored, they are
right-padded with spaces to the specified length. When CHAR values
are retrieved, trailing spaces are removed.
Values in VARCHAR columns are variable-length strings. You can
declare a VARCHAR column to be any length between 1 and 255, just
as for CHAR columns. However, in contrast to CHAR,
VARCHAR values are stored using only as many characters as are
needed, plus one byte to record the length. Values are not padded; instead,
trailing spaces are removed when values are stored. (This space removal differs
from the ANSI SQL specification.)
If you assign a value to a CHAR or VARCHAR column
that exceeds the column's maximum length, the value is truncated to fit.
The table below illustrates the differences between the two types of columns
by showing the result of storing various string values into CHAR(4)
and VARCHAR(4) columns:
| Value | CHAR(4) |
Storage required | VARCHAR(4) |
Storage required |
'' |
' ' |
4 bytes | '' |
1 byte |
'ab' |
'ab ' |
4 bytes | 'ab' |
3 bytes |
'abcd' |
'abcd' |
4 bytes | 'abcd' |
5 bytes |
'abcdefgh' |
'abcd' |
4 bytes | 'abcd' |
5 bytes |
The values retrieved from the CHAR(4) and
VARCHAR(4) columns will be the same in each case, because trailing
spaces are removed from CHAR columns upon retrieval.
Values in CHAR and VARCHAR columns are sorted and
compared in case-insensitive fashion, unless the BINARY attribute
was specified when the table was created. The BINARY attribute
means that column values are sorted and compared in case-sensitive fashion
according to the ASCII order of the machine where the MySQL
server is running. BINARY doesn't affect how the column is stored
or retrieved.
The BINARY attribute is sticky. This means that if a column
marked BINARY is used in an expression, the whole expression is
compared as a BINARY value.
MySQL may silently change the type of a CHAR or
VARCHAR column at table creation time. See section 7.7.1
Silent Column Specification Changes.
BLOB and TEXT TypesA BLOB is a binary large object that can hold a variable amount
of data. The four BLOB types TINYBLOB,
BLOB, MEDIUMBLOB, and LONGBLOB differ
only in the maximum length of the values they can hold. See section 7.3.1
Column Type Storage Requirements.
The four TEXT types TINYTEXT, TEXT,
MEDIUMTEXT, and LONGTEXT correspond to the four
BLOB types and have the same maximum lengths and storage
requirements. The only difference between BLOB and
TEXT types is that sorting and comparison is performed in
case-sensitive fashion for BLOB values and case-insensitive fashion
for TEXT values. In other words, a TEXT is a
case-insensitive BLOB.
If you assign a value to a BLOB or TEXT column that
exceeds the column type's maximum length, the value is truncated to fit.
In most respects, you can regard a TEXT column as a
VARCHAR column that can be as big as you like. Similarly, you can
regard a BLOB column as a VARCHAR BINARY column. The
differences are:
BLOB and TEXT columns
with MySQL Version 3.23.2 and newer. Older versions of
MySQL did not support this.
BLOB and
TEXT columns when values are stored, as there is for
VARCHAR columns.
BLOB
and TEXT columns cannot have DEFAULT values.
MyODBC defines BLOB values as
LONGVARBINARY and TEXT values as
LONGVARCHAR.
Because BLOB and TEXT values may be extremely long,
you may run up against some constraints when using them:
GROUP BY or ORDER BY on a
BLOB or TEXT column, you must convert the column
value into a fixed-length object. The standard way to do this is with the
SUBSTRING function. For example: mysql> select comment from tbl_name,substring(comment,20) as substr
ORDER BY substr;
If you don't do this, only the first max_sort_length bytes
of the column are used when sorting. The default value of
max_sort_length is 1024; this value can be changed using the
-O option when starting the mysqld server. You can
group on an expression involving BLOB or TEXT values
by specifying the column position or by using an alias: mysql> select id,substring(blob_col,1,100) from tbl_name
GROUP BY 2;
mysql> select id,substring(blob_col,1,100) as b from tbl_name
GROUP BY b;
BLOB or TEXT object is
determined by its type, but the largest value you can actually transmit
between the client and server is determined by the amount of available memory
and the size of the communications buffers. You can change the message buffer
size, but you must do so on both the server and client ends. See section 12.2.3
Tuning Server Parameters. Note that each BLOB or TEXT value is represented
internally by a separately allocated object. This is in contrast to all other
column types, for which storage is allocated once per column when the table is
opened.
ENUM TypeAn ENUM is a string object whose value normally is chosen from a
list of allowed values that are enumerated explicitly in the column
specification at table creation time.
The value may also be the empty string ("") or NULL
under certain circumstances:
ENUM (that is, a
string not present in the list of allowed values), the empty string is
inserted instead as a special error value.
ENUM is declared NULL, NULL
is also a legal value for the column, and the default value is
NULL. If an ENUM is declared NOT NULL,
the default value is the first element of the list of allowed values. Each enumeration value has an index:
SELECT statement to find rows into which
invalid ENUM values were assigned: mysql> SELECT * FROM tbl_name WHERE enum_col=0;
NULL value is NULL. For example, a column specified as ENUM("one", "two", "three")
can have any of the values shown below. The index of each value is also shown:
| Value | Index |
NULL |
NULL |
"" |
0 |
"one" |
1 |
"two" |
2 |
"three" |
3 |
An enumeration can have a maximum of 65535 elements.
Lettercase is irrelevant when you assign values to an ENUM
column. However, values retrieved from the column later have lettercase matching
the values that were used to specify the allowable values at table creation
time.
If you retrieve an ENUM in a numeric context, the column value's
index is returned. For example, you can retrieve numeric values from an
ENUM column like this:
mysql> SELECT enum_col+0 FROM tbl_name;
If you store a number into an ENUM, the number is treated as an
index, and the value stored is the enumeration member with that index. (However,
this will not work with LOAD DATA, which treats all input as
strings.)
ENUM values are sorted according to the order in which the
enumeration members were listed in the column specification. (In other words,
ENUM values are sorted according to their index numbers.) For
example, "a" sorts before "b" for ENUM("a",
"b"), but "b" sorts before "a" for
ENUM("b", "a"). The empty string sorts before non-empty strings,
and NULL values sort before all other enumeration values.
If you want to get all possible values for an ENUM column, you
should use: SHOW COLUMNS FROM table_name LIKE enum_column_name and
parse the ENUM definition in the second column.
SET TypeA SET is a string object that can have zero or more values, each
of which must be chosen from a list of allowed values specified when the table
is created. SET column values that consist of multiple set members
are specified with members separated by commas (`,'). A consequence
of this is that SET member values cannot themselves contain commas.
For example, a column specified as SET("one", "two") NOT NULL
can have any of these values:
"" "one" "two" "one,two"
A SET can have a maximum of 64 different members.
MySQL stores SET values numerically, with the
low-order bit of the stored value corresponding to the first set member. If you
retrieve a SET value in a numeric context, the value retrieved has
bits set corresponding to the set members that make up the column value. For
example, you can retrieve numeric values from a SET column like
this:
mysql> SELECT set_col+0 FROM tbl_name;
If a number is stored into a SET column, the bits that are set
in the binary representation of the number determine the set members in the
column value. Suppose a column is specified as
SET("a","b","c","d"). Then the members have the following bit
values:
SET member |
Decimal value | Binary value |
a |
1 |
0001 |
b |
2 |
0010 |
c |
4 |
0100 |
d |
8 |
1000 |
If you assign a value of 9 to this column, that is
1001 in binary, so the first and fourth SET value
members "a" and "d" are selected and the resulting
value is "a,d".
For a value containing more than one SET element, it does not
matter what order the elements are listed in when you insert the value. It also
does not matter how many times a given element is listed in the value. When the
value is retrieved later, each element in the value will appear once, with
elements listed according to the order in which they were specified at table
creation time. For example, if a column is specified as
SET("a","b","c","d"), then "a,d", "d,a",
and "d,a,a,d,d" will all appear as "a,d" when
retrieved.
SET values are sorted numerically. NULL values sort
before non-NULL SET values.
Normally, you perform a SELECT on a SET column
using the LIKE operator or the FIND_IN_SET() function:
mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;
But the following will also work:
mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2'; mysql> SELECT * FROM tbl_name WHERE set_col & 1;
The first of these statements looks for an exact match. The second looks for values containing the first set member.
If you want to get all possible values for a SET column, you
should use: SHOW COLUMNS FROM table_name LIKE set_column_name and
parse the SET definition in the second column.
For the most efficient use of storage, try to use the most precise type in
all cases. For example, if an integer column will be used for values in the
range between 1 and 99999, MEDIUMINT
UNSIGNED is the best type.
Accurate representation of monetary values is a common problem. In
MySQL, you should use the DECIMAL type. This is
stored as a string, so no loss of accuracy should occur. If accuracy is not too
important, the DOUBLE type may also be good enough.
For high precision, you can always convert to a fixed-point type stored in a
BIGINT. This allows you to do all calculations with integers and
convert results back to floating-point values only when necessary.
All MySQL column types can be indexed. Use of indexes on the
relevant columns is the best way to improve the performance of
SELECT operations.
The maximum number of keys and the maximum index length is defined per table handler. See section 8 MySQL Table Types. You can with all table handlers have at least 16 keys and a total index length of at least 256 bytes.
For CHAR and VARCHAR columns, you can index a
prefix of a column. This is much faster and requires less disk space than
indexing the whole column. The syntax to use in the CREATE TABLE
statement to index a column prefix looks like this:
KEY index_name (col_name(length))
The example below creates an index for the first 10 characters of the
name column:
mysql> CREATE TABLE test (
name CHAR(200) NOT NULL,
KEY index_name (name(10)));
For BLOB and TEXT columns, you must index a prefix
of the column. You cannot index the entire column.
In MySQL Version 3.23.23 or later, you can also create
special FULLTEXT indexes. They are used for full-text search.
Only the MyISAM table type supports FULLTEXT indexes.
They can be created only from VARCHAR and TEXT
columns. Indexing always happens over the entire column and partial indexing is
not supported. See section 25.2
MySQL Full-text Search for details.
MySQL can create indexes on multiple columns. An index may
consist of up to 15 columns. (On CHAR and VARCHAR
columns you can also use a prefix of the column as a part of an index).
A multiple-column index can be considered a sorted array containing values that are created by concatenating the values of the indexed columns.
MySQL uses multiple-column indexes in such a way that
queries are fast when you specify a known quantity for the first column of the
index in a WHERE clause, even if you don't specify values for the
other columns.
Suppose a table is created using the following specification:
mysql> CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name));
Then the index name is an index over last_name and
first_name. The index will be used for queries that specify values
in a known range for last_name, or for both last_name
and first_name. Therefore, the name index will be used
in the following queries:
mysql> SELECT * FROM test WHERE last_name="Widenius";
mysql> SELECT * FROM test WHERE last_name="Widenius"
AND first_name="Michael";
mysql> SELECT * FROM test WHERE last_name="Widenius"
AND (first_name="Michael" OR first_name="Monty");
mysql> SELECT * FROM test WHERE last_name="Widenius"
AND first_name >="M" AND first_name < "N";
However, the name index will NOT be used in the following
queries:
mysql> SELECT * FROM test WHERE first_name="Michael";
mysql> SELECT * FROM test WHERE last_name="Widenius"
OR first_name="Michael";
For more information on the manner in which MySQL uses indexes to improve query performance, see section 12.4 How MySQL Uses Indexes.
To make it easier to use code written for SQL implementations from other vendors, MySQL maps column types as shown in the table below. These mappings make it easier to move table definitions from other database engines to MySQL:
| Other vendor type | MySQL type |
BINARY(NUM) |
CHAR(NUM) BINARY |
CHAR VARYING(NUM) |
VARCHAR(NUM) |
FLOAT4 |
FLOAT |
FLOAT8 |
DOUBLE |
INT1 |
TINYINT |
INT2 |
SMALLINT |
INT3 |
MEDIUMINT |
INT4 |
INT |
INT8 |
BIGINT |
LONG VARBINARY |
MEDIUMBLOB |
LONG VARCHAR |
MEDIUMTEXT |
MIDDLEINT |
MEDIUMINT |
VARBINARY(NUM) |
VARCHAR(NUM) BINARY |
Column type mapping occurs at table creation time. If you create a table with
types used by other vendors and then issue a DESCRIBE tbl_name
statement, MySQL reports the table structure using the
equivalent MySQL types.
SELECT and WHERE ClausesA select_expression or where_definition in a SQL
statement can consist of any expression using the functions described below.
An expression that contains NULL always produces a
NULL value unless otherwise indicated in the documentation for the
operators and functions involved in the expression.
NOTE: There must be no whitespace between a function name and the parenthesis following it. This helps the MySQL parser distinguish between function calls and references to tables or columns that happen to have the same name as a function. Spaces around arguments are permitted, though.
You can force MySQL to accept spaces after the function name
by starting mysqld with --ansi or using the
CLIENT_IGNORE_SPACE to mysql_connect(), but in this
case all function names will become reserved words. See section 5.2
Running MySQL in ANSI Mode.
For the sake of brevity, examples display the output from the
mysql program in abbreviated form. So this:
mysql> select MOD(29,9); 1 rows in set (0.00 sec) +-----------+ | mod(29,9) | +-----------+ | 2 | +-----------+
is displayed like this:
mysql> select MOD(29,9);
-> 2
( ... )
mysql> select 1+2*3;
-> 7
mysql> select (1+2)*3;
-> 9
The usual arithmetic operators are available. Note that in the case of
`-', `+', and `*', the result is
calculated with BIGINT (64-bit) precision if both arguments are
integers!
+
mysql> select 3+5;
-> 8
-
mysql> select 3-5;
-> -2
*
mysql> select 3*5;
-> 15
mysql> select 18014398509481984*18014398509481984.0;
-> 324518553658426726783156020576256.0
mysql> select 18014398509481984*18014398509481984;
-> 0
The result of the last expression is incorrect because the result of the
integer multiplication exceeds the 64-bit range of BIGINT
calculations.
/
mysql> select 3/5;
-> 0.60
Division by zero produces a NULL result: mysql> select 102/(1-1);
-> NULL
A division will be calculated with BIGINT arithmetic only
if performed in a context where its result is converted to an integer!
MySQL uses BIGINT (64-bit) arithmetic for bit
operations, so these operators have a maximum range of 64 bits.
|
mysql> select 29 | 15;
-> 31
&
mysql> select 29 & 15;
-> 13
<<
BIGINT) number to the left: mysql> select 1 << 2;
-> 4
>>
BIGINT) number to the right: mysql> select 4 >> 2;
-> 1
~
mysql> select 5 & ~1;
-> 4
BIT_COUNT(N)
N: mysql> select BIT_COUNT(29);
-> 4
All logical functions return 1 (TRUE), 0 (FALSE) or
NULL (unknown, which is in most cases the same as FALSE):
NOT
!
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
||
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
&&
0 if either argument is 0
or NULL, otherwise returns 1: mysql> select 1 && NULL;
-> 0
mysql> select 1 && 0;
-> 0
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:
NULL, the result of the
comparison is NULL, except for the <=>
operator.
TIMESTAMP or DATETIME column and the other argument
is a constant, the constant is converted to a timestamp before the comparison
is performed. This is done to be more ODBC-friendly.
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
=
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
<>
!=
mysql> select '.01' <> '0.01';
-> 1
mysql> select .01 <> '0.01';
-> 0
mysql> select 'zapp' <> 'zappp';
-> 1
<=
mysql> select 0.1 <= 2;
-> 1
<
mysql> select 2 <= 2;
-> 1
>=
mysql> select 2 >= 2;
-> 1
>
mysql> select 2 > 2;
-> 0
<=>
mysql> select 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
-> 1 1 0
IS NULL
IS NOT NULL
NULL: mysql> select 1 IS NULL, 0 IS NULL, NULL IS NULL;
-> 0 0 1
mysql> select 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
-> 1 1 0
expr BETWEEN min AND max
expr is greater than or equal to min and
expr is less than or equal to max,
BETWEEN returns 1, otherwise it returns
0. This is equivalent to the expression (min <= expr AND
expr <= max) if all the arguments are of the same type. The first
argument (expr) determines how the comparison is performed as
follows:
expr is a TIMESTAMP, DATE, or
DATETIME column, MIN() and MAX() are
formatted to the same format if they are constants.
expr is a case-insensitive string expression, a
case-insensitive string comparison is done.
expr is a case-sensitive string expression, a
case-sensitive string comparison is done.
expr is an integer expression, an integer 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,...)
1 if expr is any of the values in the
IN list, else returns 0. If all values are
constants, then all values are evaluated according to the type of
expr and sorted. The search for the item is then done using a
binary search. This means IN is very quick if the IN
value list consists entirely of constants. If expr is a
case-sensitive string expression, the string comparison is performed in
case-sensitive fashion: mysql> select 2 IN (0,3,5,'wefwf');
-> 0
mysql> select 'wefwf' IN (0,3,5,'wefwf');
-> 1
expr NOT IN (value,...)
NOT (expr IN (value,...)).
ISNULL(expr)
expr is NULL, ISNULL() returns
1, otherwise it returns 0: mysql> select ISNULL(1+1);
-> 0
mysql> select ISNULL(1/0);
-> 1
Note that a comparison of NULL values using =
will always be false!
COALESCE(list)
NULL element in list: mysql> select COALESCE(NULL,1);
-> 1
mysql> select COALESCE(NULL,NULL,NULL);
-> NULL
INTERVAL(N,N1,N2,N3,...)
0 if N < N1,
1 if N < N2 and so on. All arguments
are treated as integers. It is required that N1 <
N2 < N3 < ... <
Nn for this function to work correctly. This is because a binary
search is used (very fast): mysql> select INTERVAL(23, 1, 15, 17, 30, 44, 200);
-> 3
mysql> select INTERVAL(10, 1, 10, 100, 1000);
-> 2
mysql> select INTERVAL(22, 23, 30, 44, 200);
-> 0
Normally, if any expression in a string comparison is case sensitive, the comparison is performed in case-sensitive fashion.
expr LIKE pat [ESCAPE 'escape-char']
1 (TRUE) or 0 (FALSE). With
LIKE you can use the following two wild-card characters in the
pattern:
% |
Matches any number of characters, even zero characters |
_ |
Matches exactly one character |
mysql> select 'David!' LIKE 'David_';
-> 1
mysql> select 'David!' LIKE '%D%v%';
-> 1
To test for literal instances of a wild-card character, precede the
character with the escape character. If you don't specify the
ESCAPE character, `\' is assumed:
\% |
Matches one % character |
\_ |
Matches one _ character |
mysql> select 'David!' LIKE 'David\_';
-> 0
mysql> select 'David_' LIKE 'David\_';
-> 1
To specify a different escape character, use the ESCAPE
clause: mysql> select 'David_' LIKE 'David|_' ESCAPE '|';
-> 1
LIKE is allowed on numeric expressions! (This is a
MySQL extension to the ANSI SQL LIKE.) mysql> select 10 LIKE '1%';
-> 1
Note: Because MySQL uses the C escape syntax in strings
(for example, `\n'), you must double any `\' that
you use in your LIKE strings. For example, to search for
`\n', specify it as `\\n'. To search for
`\', specify it as `\\\\' (the backslashes are
stripped once by the parser and another time when the pattern match is done,
leaving a single backslash to be matched).
expr NOT LIKE pat [ESCAPE 'escape-char']
NOT (expr LIKE pat [ESCAPE 'escape-char']).
expr REGEXP pat
expr RLIKE pat
expr against
a pattern pat. The pattern can be an extended regular expression.
See section J
Description of MySQL regular expression syntax. Returns 1 if
expr matches pat, otherwise returns 0.
RLIKE is a synonym for REGEXP, provided for
mSQL compatibility. Note: Because MySQL uses the
C escape syntax in strings (for example, `\n'), you must double
any `\' that you use in your REGEXP strings. As of
MySQL Version 3.23.4, REGEXP is case insensitive
for normal (not binary) strings: mysql> select 'Monty!' REGEXP 'm%y%%';
-> 0
mysql> select 'Monty!' REGEXP '.*';
-> 1
mysql> select 'new*\n*line' REGEXP 'new\\*.\\*line';
-> 1
mysql> select "a" REGEXP "A", "a" REGEXP BINARY "A";
-> 1 0
mysql> select "a" REGEXP "^[a-d]";
-> 1
REGEXP and RLIKE use the current character set
(ISO-8859-1 Latin1 by default) when deciding the type of a character.
expr NOT REGEXP pat
expr NOT RLIKE pat
NOT (expr REGEXP pat).
STRCMP(expr1,expr2)
STRCMP() returns 0 if the strings are the same,
-1 if the first argument is smaller than the second according to
the current sort order, and 1 otherwise: mysql> select STRCMP('text', 'text2');
-> -1
mysql> select STRCMP('text2', 'text');
-> 1
mysql> select STRCMP('text', 'text');
-> 0
MATCH (col1,col2,...) AGAINST (expr)
MATCH ... AGAINST() is used for full-text search and returns
relevance - similarity measure between the text in columns
(col1,col2,...) and the query expr. Relevance is a
positive floating-point number. Zero relevance means no similarity. For
MATCH ... AGAINST() to work, a FULLTEXT index
must be created first. See section 7.7
CREATE TABLE Syntax. MATCH ... AGAINST() is
available in MySQL Version 3.23.23 or later. For details and
usage examples see section 25.2
MySQL Full-text Search. BINARYBINARY operator casts the string
following it to a binary string. This is an easy way to force a column
comparison to be case sensitive even if the column isn't defined as
BINARY or BLOB: mysql> select "a" = "A";
-> 1
mysql> select BINARY "a" = "A";
-> 0
BINARY was introduced in MySQL Version
3.23.0. Note that in some context MySQL will not be able to
use the index efficiently when you cast an indexed column to
BINARY. If you want to compare a blob case-insensitively you can always convert the blob to upper case before doing the comparison:
SELECT 'A' LIKE UPPER(blob_col) FROM table_name;
We plan to soon introduce casting between different character sets to make string comparison even more flexible.
IFNULL(expr1,expr2)
expr1 is not NULL,
IFNULL() returns expr1, else it returns
expr2. IFNULL() returns a numeric or string value,
depending on the context in which it is used: mysql> select IFNULL(1,0);
-> 1
mysql> select IFNULL(NULL,10);
-> 10
mysql> select IFNULL(1/0,10);
-> 10
mysql> select IFNULL(1/0,'yes');
-> 'yes'
NULLIF(expr1,expr2)
expr1 = expr2 is true, return NULL else
return expr1. This is the same as CASE WHEN x = y THEN NULL
ELSE x END: mysql> select NULLIF(1,1);
-> NULL
mysql> select NULLIF(1,2);
-> 1
Note that expr1 is evaluated twice in
MySQL if the arguments are equal.
IF(expr1,expr2,expr3)
expr1 is TRUE (expr1 <> 0 and
expr1 <> NULL) then IF() returns
expr2, else it returns expr3. IF()
returns a numeric or string value, depending on the context in which it is
used: mysql> select IF(1>2,2,3);
-> 3
mysql> select IF(1<2,'yes','no');
-> 'yes'
mysql> select IF(strcmp('test','test1'),'no','yes');
-> 'no'
expr1 is evaluated as an integer value, which means that if
you are testing floating-point or string values, you should do so using a
comparison operation: mysql> select IF(0.1,1,0);
-> 0
mysql> select IF(0.1<>0,1,0);
-> 1
In the first case above, IF(0.1) returns 0
because 0.1 is converted to an integer value, resulting in a test
of IF(0). This may not be what you expect. In the second case,
the comparison tests the original floating-point value to see whether it is
non-zero. The result of the comparison is used as an integer. The default
return type of IF() (which may matter when it is stored into a
temporary table) is calculated in MySQL Version 3.23 as
follows:
| Expression | Return value |
| expr2 or expr3 returns string | string |
| expr2 or expr3 returns a floating-point value | floating-point |
| expr2 or expr3 returns an integer | integer |
CASE value WHEN [compare-value] THEN result [WHEN [compare-value]
THEN result ...] [ELSE result] END
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...]
[ELSE result] END
result where
value=compare-value. The second version returns the result for
the first condition, which is true. If there was no matching result value,
then the result after ELSE is returned. If there is no
ELSE part then NULL is returned: mysql> SELECT CASE 1 WHEN 1 THEN "one" WHEN 2 THEN "two" ELSE "more" END;
-> "one"
mysql> SELECT CASE WHEN 1>0 THEN "true" ELSE "false" END;
-> "true"
mysql> SELECT CASE BINARY "B" when "a&quo