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" then 1 when "b" then 2 END;
-> NULL
The type of the return value (INTEGER, DOUBLE or
STRING) is the same as the type of the first returned value (the
expression after the first THEN).
All mathematical functions return NULL in case of an error.
-
mysql> select - 2;
-> -2
Note that if this operator is used with a BIGINT, the
return value is a BIGINT! This means that you should avoid using
- on integers that may have the value of -2^63!
ABS(X)
X: mysql> select ABS(2);
-> 2
mysql> select ABS(-32);
-> 32
This function is safe to use with BIGINT values.
SIGN(X)
-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)
%
% operator in C). Returns the remainder of
N divided by M: mysql> select MOD(234, 10);
-> 4
mysql> select 253 % 7;
-> 1
mysql> select MOD(29,9);
-> 2
This function is safe to use with BIGINT values.
FLOOR(X)
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)
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)
X, rounded to the nearest integer: mysql> select ROUND(-1.23);
-> -1
mysql> select ROUND(-1.58);
-> -2
mysql> select ROUND(1.58);
-> 2
Note that the behavior of ROUND() when the argument is half
way between two integers depends on the C library implementation. Some round
to the nearest even number, always up, always down, or always towards zero. If
you need one kind of rounding, you should use a well-defined function like
TRUNCATE() or FLOOR() instead.
ROUND(X,D)
X, rounded to a number with
D decimals. If D is 0, the result will
have no decimal point or fractional part: mysql> select ROUND(1.298, 1);
-> 1.3
mysql> select ROUND(1.298, 0);
-> 1
EXP(X)
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)
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)
X: mysql> select LOG10(2);
-> 0.301030
mysql> select LOG10(100);
-> 2.000000
mysql> select LOG10(-100);
-> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y:
mysql> select POW(2,2);
-> 4.000000
mysql> select POW(2,-2);
-> 0.250000
SQRT(X)
X: mysql> select SQRT(4);
-> 2.000000
mysql> select SQRT(20);
-> 4.472136
PI()
mysql> select PI();
-> 3.141593
COS(X)
X, where X is given in
radians: mysql> select COS(PI());
-> -1.000000
SIN(X)
X, where X is given in
radians: mysql> select SIN(PI());
-> 0.000000
TAN(X)
X, where X is given in
radians: mysql> select TAN(PI()+1);
-> 1.557408
ACOS(X)
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)
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)
X, that is, the value whose
tangent is X: mysql> select ATAN(2);
-> 1.107149
mysql> select ATAN(-2);
-> -1.107149
ATAN2(Y,X)
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)
X: mysql> select COT(12);
-> -1.57267341
mysql> select COT(0);
-> NULL
RAND()
RAND(N)
0 to
1.0. If an integer argument N is specified, it is
used as the seed value: mysql> select RAND();
-> 0.5925
mysql> select RAND(20);
-> 0.1811
mysql> select RAND(20);
-> 0.1811
mysql> select RAND();
-> 0.2079
mysql> select RAND();
-> 0.7888
You can't use a column with RAND() values in an ORDER
BY clause, because ORDER BY would evaluate the column
multiple times. In MySQL Version 3.23, you can, however, do:
SELECT * FROM table_name ORDER BY RAND() This is useful to get a
random sample of a set SELECT * FROM table1,table2 WHERE a=b AND c<d
ORDER BY RAND() LIMIT 1000. Note that a RAND() in a
WHERE clause will be re-evaluated every time the
WHERE is executed.
LEAST(X,Y,...)
INTEGER context, or all
arguments are integer-valued, they are compared as integers.
REAL context, or all
arguments are real-valued, they are compared as reals.
mysql> select LEAST(2,0);
-> 0
mysql> select LEAST(34.0,3.0,5.0,767.0);
-> 3.0
mysql> select LEAST("B","A","C");
-> "A"
In MySQL versions prior to Version 3.22.5, you can use
MIN() instead of LEAST.
GREATEST(X,Y,...)
LEAST: mysql> select GREATEST(2,0);
-> 2
mysql> select GREATEST(34.0,3.0,5.0,767.0);
-> 767.0
mysql> select GREATEST("B","A","C");
-> "C"
In MySQL versions prior to Version 3.22.5, you can use
MAX() instead of GREATEST.
DEGREES(X)
X, converted from radians to degrees: mysql> select DEGREES(PI());
-> 180.000000
RADIANS(X)
X, converted from degrees to radians: mysql> select RADIANS(90);
-> 1.570796
TRUNCATE(X,D)
X, truncated to D decimals.
If D is 0, the result will have no decimal point or
fractional part: mysql> select TRUNCATE(1.223,1);
-> 1.2
mysql> select TRUNCATE(1.999,1);
-> 1.9
mysql> select TRUNCATE(1.999,0);
-> 1
Note that as decimal numbers are normally not stored as exact numbers in
computers, but as double values, you may be fooled by the following result: mysql> select TRUNCATE(10.28*100,0);
-> 1027
The above happens because 10.28 is actually stored as something like
10.2799999999999999. String-valued functions return NULL if the length of the result
would be greater than the max_allowed_packet server parameter. See
section 12.2.3
Tuning Server Parameters.
For functions that operate on string positions, the first position is numbered 1.
ASCII(str)
str. Returns 0 if str is
the empty string. Returns NULL if str is
NULL: mysql> select ASCII('2');
-> 50
mysql> select ASCII(2);
-> 50
mysql> select ASCII('dx');
-> 100
See also the ORD() function.
ORD(str)
((first byte ASCII code)*256+(second
byte ASCII code))[*256+third byte ASCII code...]. If the leftmost
character is not a multi-byte character, returns the same value as the like
ASCII() function does: mysql> select ORD('2');
-> 50
CONV(N,from_base,to_base)
N, converted from base
from_base to base to_base. Returns NULL
if any argument is NULL. The argument N is
interpreted as an integer, but may be specified as an integer or a string. The
minimum base is 2 and the maximum base is 36. If
to_base is a negative number, N is regarded as a
signed number. Otherwise, N is treated as unsigned.
CONV works with 64-bit precision: mysql> select CONV("a",16,2);
-> '1010'
mysql> select CONV("6E",18,8);
-> '172'
mysql> select CONV(-17,10,-18);
-> '-H'
mysql> select CONV(10+"10"+'10'+0xa,10,10);
-> '40'
BIN(N)
N,
where N is a longlong (BIGINT) number. This is
equivalent to CONV(N,10,2). Returns NULL if
N is NULL: mysql> select BIN(12);
-> '1100'
OCT(N)
N,
where N is a longlong number. This is equivalent to
CONV(N,10,8). Returns NULL if N is
NULL: mysql> select OCT(12);
-> '14'
HEX(N)
N, where N is a longlong (BIGINT)
number. This is equivalent to CONV(N,10,16). Returns
NULL if N is NULL: mysql> select HEX(255);
-> 'FF'
CHAR(N,...)
CHAR() interprets the arguments as integers and returns a
string consisting of the characters given by the ASCII code values of those
integers. NULL values are skipped: mysql> select CHAR(77,121,83,81,'76');
-> 'MySQL'
mysql> select CHAR(77,77.3,'77.3');
-> 'MMM'
CONCAT(str1,str2,...)
NULL if any argument is NULL. May have more than 2
arguments. A numeric argument is converted to the equivalent string form: mysql> select CONCAT('My', 'S', 'QL');
-> 'MySQL'
mysql> select CONCAT('My', NULL, 'QL');
-> NULL
mysql> select CONCAT(14.3);
-> '14.3'
CONCAT_WS(separator, str1, str2,...)
CONCAT_WS() stands for CONCAT With Separator and is a special
form of CONCAT(). The first argument is the separator for the
rest of the arguments. The separator can be a string as well as the rest of
the arguments. If the separator is NULL, the result will be
NULL. The function will skip any NULLs and empty
strings, after the separator argument. The separator will be added between the
strings to be concatenated: mysql> select CONCAT_WS(",","First name","Second name","Last Name");
-> 'First name,Second name,Last Name'
mysql> select CONCAT_WS(",","First name",NULL,"Last Name");
-> 'First name,Last Name'
LENGTH(str)
OCTET_LENGTH(str)
CHAR_LENGTH(str)
CHARACTER_LENGTH(str)
str: mysql> select LENGTH('text');
-> 4
mysql> select OCTET_LENGTH('text');
-> 4
Note that for CHAR_LENGTH(), multi-byte characters are only
counted once.
LOCATE(substr,str)
POSITION(substr IN str)
substr in string str. Returns 0 if
substr is not in str: mysql> select LOCATE('bar', 'foobarbar');
-> 4
mysql> select LOCATE('xbar', 'foobar');
-> 0
This function is multi-byte safe.
LOCATE(substr,str,pos)
substr in string str, starting at position
pos. Returns 0 if substr is not in
str: mysql> select LOCATE('bar', 'foobarbar',5);
-> 7
This function is multi-byte safe.
INSTR(str,substr)
substr in string str. This is the same as the
two-argument form of LOCATE(), except that the arguments are
swapped: mysql> select INSTR('foobarbar', 'bar');
-> 4
mysql> select INSTR('xbar', 'foobar');
-> 0
This function is multi-byte safe.
LPAD(str,len,padstr)
str, left-padded with the string
padstr until str is len characters
long. If str is longer than len' then it will be
shortened to len characters. mysql> select LPAD('hi',4,'??');
-> '??hi'
RPAD(str,len,padstr)
str, right-padded with the string
padstr until str is len characters
long. If str is longer than len' then it will be
shortened to len characters. mysql> select RPAD('hi',5,'?');
-> 'hi???'
LEFT(str,len)
len characters from the string
str: mysql> select LEFT('foobarbar', 5);
-> 'fooba'
This function is multi-byte safe.
RIGHT(str,len)
len characters from the string
str: mysql> select RIGHT('foobarbar', 4);
-> 'rbar'
This function is multi-byte safe.
SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos FOR len)
MID(str,pos,len)
len characters long from string
str, starting at position pos. The variant form that
uses FROM is ANSI SQL92 syntax: mysql> select SUBSTRING('Quadratically',5,6);
-> 'ratica'
This function is multi-byte safe.
SUBSTRING(str,pos)
SUBSTRING(str FROM pos)
str starting at position
pos: mysql> select SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> select SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
This function is multi-byte safe.
SUBSTRING_INDEX(str,delim,count)
str before
count occurrences of the delimiter delim. If
count is positive, everything to the left of the final delimiter
(counting from the left) is returned. If count is negative,
everything to the right of the final delimiter (counting from the right) is
returned: mysql> select SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> select SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'
This function is multi-byte safe.
LTRIM(str)
str with leading space characters removed:
mysql> select LTRIM(' barbar');
-> 'barbar'
RTRIM(str)
str with trailing space characters
removed: mysql> select RTRIM('barbar ');
-> 'barbar'
This function is multi-byte safe.
TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
str with all remstr prefixes
and/or suffixes removed. If none of the specifiers BOTH,
LEADING or TRAILING are given, BOTH is
assumed. If remstr is not specified, spaces are removed: mysql> select TRIM(' bar ');
-> 'bar'
mysql> select TRIM(LEADING 'x' FROM 'xxxbarxxx');
-> 'barxxx'
mysql> select TRIM(BOTH 'x' FROM 'xxxbarxxx');
-> 'bar'
mysql> select TRIM(TRAILING 'xyz' FROM 'barxxyz');
-> 'barx'
This function is multi-byte safe.
SOUNDEX(str)
str. Two strings that sound
almost the same should have identical soundex strings. A standard soundex
string is 4 characters long, but the SOUNDEX() function returns
an arbitrarily long string. You can use SUBSTRING() on the result
to get a standard soundex string. All non-alphanumeric characters are ignored
in the given string. All international alpha characters outside the A-Z range
are treated as vowels: mysql> select SOUNDEX('Hello');
-> 'H400'
mysql> select SOUNDEX('Quadratically');
-> 'Q36324'
SPACE(N)
N space characters: mysql> select SPACE(6);
-> ' '
REPLACE(str,from_str,to_str)
str with all all occurrences of the string
from_str replaced by the string to_str: mysql> select REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'
This function is multi-byte safe.
REPEAT(str,count)
str repeated
count times. If count <= 0, returns an empty
string. Returns NULL if str or count
are NULL: mysql> select REPEAT('MySQL', 3);
-> 'MySQLMySQLMySQL'
REVERSE(str)
str with the order of the characters
reversed: mysql> select REVERSE('abc');
-> 'cba'
This function is multi-byte safe.
INSERT(str,pos,len,newstr)
str, with the substring beginning at
position pos and len characters long replaced by the
string newstr: mysql> select INSERT('Quadratic', 3, 4, 'What');
-> 'QuWhattic'
This function is multi-byte safe.
ELT(N,str1,str2,str3,...)
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,...)
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)
1 to N if the string
str is in the list strlist consisting of
N substrings. A string list is a string composed of substrings
separated by `,' characters. If the first argument is a constant
string and the second is a column of type SET, the
FIND_IN_SET() function is optimized to use bit arithmetic!
Returns 0 if str is not in strlist or
if strlist is the empty string. Returns NULL if
either argument is NULL. This function will not work properly if
the first argument contains a `,': mysql> SELECT FIND_IN_SET('b','a,b,c,d');
-> 2
MAKE_SET(bits,str1,str2,...)
bits set. str1 corresponds to
bit 0, str2 to bit 1, etc. NULL strings in
str1, str2, ... are not appended to the
result: mysql> SELECT MAKE_SET(1,'a','b','c');
-> 'a'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
-> 'hello,world'
mysql> SELECT MAKE_SET(0,'a','b','c');
-> ''
EXPORT_SET(bits,on,off,[separator,[number_of_bits]])
mysql> select EXPORT_SET(5,'Y','N',',',4)
-> Y,N,Y,N
LCASE(str)
LOWER(str)
str with all characters changed to
lowercase according to the current character set mapping (the default is
ISO-8859-1 Latin1): mysql> select LCASE('QUADRATICALLY');
-> 'quadratically'
This function is multi-byte safe.
UCASE(str)
UPPER(str)
str with all characters changed to
uppercase according to the current character set mapping (the default is
ISO-8859-1 Latin1): mysql> select UCASE('Hej');
-> 'HEJ'
This function is multi-byte safe.
LOAD_FILE(file_name)
max_allowed_packet. If the file doesn't exist or
can't be read due to one of the above reasons, the function returns
NULL: mysql> UPDATE table_name
SET blob_column=LOAD_FILE("/tmp/picture")
WHERE id=1;
If you are not using MySQL Version 3.23, you have to do the
reading of the file inside your application and create an INSERT
statement to update the database with the file information. One way to do this,
if you are using the MySQL++ library, can be found at http://www.mysql.com/documentation/mysql++/mysql++-examples.html.
MySQL automatically converts numbers to strings as necessary, and vice-versa:
mysql> SELECT 1+"1";
-> 2
mysql> SELECT CONCAT(2,' test');
-> '2 test'
If you want to convert a number to a string explicitly, pass it as the
argument to CONCAT().
If a string function is given a binary string as an argument, the resulting string is also a binary string. A number converted to a string is treated as a binary string. This only affects comparisons.
See section 7.3.3 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.
Here is an example that uses date functions. The query below selects all
records with a date_col value from within the last 30 days:
mysql> SELECT something FROM table
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;
DAYOFWEEK(date)
date
(1 = Sunday, 2 = Monday, ... 7 =
Saturday). These index values correspond to the ODBC standard: mysql> select DAYOFWEEK('1998-02-03');
-> 3
WEEKDAY(date)
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)
date, in the range
1 to 31: mysql> select DAYOFMONTH('1998-02-03');
-> 3
DAYOFYEAR(date)
date, in the range
1 to 366: mysql> select DAYOFYEAR('1998-02-03');
-> 34
MONTH(date)
date, in the range 1 to
12: mysql> select MONTH('1998-02-03');
-> 2
DAYNAME(date)
date: mysql> select DAYNAME("1998-02-05");
-> 'Thursday'
MONTHNAME(date)
date: mysql> select MONTHNAME("1998-02-05");
-> 'February'
QUARTER(date)
date, in the range
1 to 4: mysql> select QUARTER('98-04-01');
-> 2
WEEK(date)
WEEK(date,first)
date, in the
range 0 to 53 (yes, there may be the beginnings of a
week 53), for locations where Sunday is the first day of the week. The
two-argument form of WEEK() allows you to specify whether the
week starts on Sunday or Monday. 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
mysql> select WEEK('1998-12-31',1);
-> 53
YEAR(date)
date, in the range 1000 to
9999: mysql> select YEAR('98-02-03');
-> 1998
YEARWEEK(date)
YEARWEEK(date,first)
WEEK(). Note that the year may be
different from the year in the date argument for the first and the last week
of the year: mysql> select YEARWEEK('1987-01-01');
-> 198653
HOUR(time)
time, in the range 0 to
23: mysql> select HOUR('10:05:03');
-> 10
MINUTE(time)
time, in the range 0 to
59: mysql> select MINUTE('98-02-03 10:05:03');
-> 5
SECOND(time)
time, in the range 0 to
59: mysql> select SECOND('10:05:03');
-> 3
PERIOD_ADD(P,N)
N months to period P (in the format
YYMM or YYYYMM). Returns a value in the format
YYYYMM. Note that the period argument P is
not a date value: mysql> select PERIOD_ADD(9801,2);
-> 199803
PERIOD_DIFF(P1,P2)
P1 and
P2. P1 and P2 should be in the format
YYMM or YYYYMM. Note that the period arguments
P1 and P2 are not date values: mysql> select PERIOD_DIFF(9802,199703);
-> 11
DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)
ADDDATE(date,INTERVAL expr type)
SUBDATE(date,INTERVAL expr type)
ADDDATE() and
SUBDATE() are synonyms for DATE_ADD() and
DATE_SUB(). In MySQL Version 3.23, you can use
+ and - instead of DATE_ADD() and
DATE_SUB() if the expression on the right side is a date or
datetime column. (See example) date is a DATETIME or
DATE value specifying the starting date. expr is an
expression specifying the interval value to be added or subtracted from the
starting date. expr is a string; it may start with a
`-' for negative intervals. type is a keyword
indicating how the expression should be interpreted. The EXTRACT(type
FROM date) function returns the 'type' interval from the date. The
following table shows how the type and expr
arguments are related:
type value |
Expected expr format
|
SECOND |
SECONDS |
MINUTE |
MINUTES |
HOUR |
HOURS |
DAY |
DAYS |
MONTH |
MONTHS |
YEAR |
YEARS |
MINUTE_SECOND |
"MINUTES:SECONDS" |
HOUR_MINUTE |
"HOURS:MINUTES" |
DAY_HOUR |
"DAYS HOURS" |
YEAR_MONTH |
"YEARS-MONTHS" |
HOUR_SECOND |
"HOURS:MINUTES:SECONDS" |
DAY_MINUTE |
"DAYS HOURS:MINUTES" |
DAY_SECOND |
"DAYS HOURS:MINUTES:SECONDS"
|
expr format. Those shown in the table are the
suggested delimiters. If the date argument is a DATE
value and your calculations involve only YEAR,
MONTH, and DAY parts (that is, no time parts), the
result is a DATE value. Otherwise the result is a
DATETIME value: mysql> SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
-> 1998-01-01 00:00:00
mysql> SELECT INTERVAL 1 DAY + "1997-12-31";
-> 1998-01-01
mysql> SELECT "1998-01-01" - INTERVAL 1 SECOND;
-> 1997-12-31 23:59:59
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
INTERVAL 1 SECOND);
-> 1998-01-01 00:00:00
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
INTERVAL 1 DAY);
-> 1998-01-01 23:59:59
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
INTERVAL "1:1" MINUTE_SECOND);
-> 1998-01-01 00:01:00
mysql> SELECT DATE_SUB("1998-01-01 00:00:00",
INTERVAL "1 1:1:1" DAY_SECOND);
-> 1997-12-30 22:58:59
mysql> SELECT DATE_ADD("1998-01-01 00:00:00",
INTERVAL "-1 10" DAY_HOUR);
-> 1997-12-30 14:00:00
mysql> SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);
-> 1997-12-02
mysql> SELECT EXTRACT(YEAR FROM "1999-07-02");
-> 1999
mysql> SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03");
-> 199907
mysql> SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03");
-> 20102
If you specify an interval value that is too short (does not include all
the interval parts that would be expected from the type keyword),
MySQL assumes you have left out the leftmost parts of the
interval value. For example, if you specify a type of
DAY_SECOND, the value of expr is expected to have
days, hours, minutes, and seconds parts. If you specify a value like
"1:10", MySQL assumes that the days and hours
parts are missing and the value represents minutes and seconds. In other
words, "1:10" DAY_SECOND is interpreted in such a way that it is
equivalent to "1:10" MINUTE_SECOND. This is analogous to the way
that MySQL interprets TIME values as
representing elapsed time rather than as time of day. Note that if you add or
subtract a date value against something that contains a time part, the date
value will be automatically converted to a datetime value: mysql> select date_add("1999-01-01", interval 1 day);
-> 1999-01-02
mysql> select date_add("1999-01-01", interval 1 hour);
-> 1999-01-01 01:00:00
If you use really incorrect dates, the result is NULL. If
you add MONTH, YEAR_MONTH, or YEAR and
the resulting date has a day that is larger than the maximum day for the new
month, the day is adjusted to the maximum days in the new month: mysql> select DATE_ADD('1998-01-30', Interval 1 month);
-> 1998-02-28
Note from the preceding example that the word INTERVAL and
the type keyword are not case sensitive.
TO_DAYS(date)
date, returns a daynumber (the number of days
since year 0): mysql> select TO_DAYS(950501);
-> 728779
mysql> select TO_DAYS('1997-10-07');
-> 729669
TO_DAYS() is not intended for use with values that precede
the advent of the Gregorian calendar (1582), because it doesn't take into
account the days that were lost when the calendar was changed.
FROM_DAYS(N)
N, returns a DATE value: mysql> select FROM_DAYS(729669);
-> '1997-10-07'
FROM_DAYS() is not intended for use with values that
precede the advent of the Gregorian calendar (1582), because it doesn't take
into account the days that were lost when the calendar was changed.
DATE_FORMAT(date,format)
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 |
%X |
Year for the week where Sunday is the first day of the week, numeric, 4 digits, used with '%V' |
%x |
Year for the week, where Monday is the first day of the week, numeric, 4 digits, used with '%v' |
%a |
Abbreviated weekday name (Sun..Sat) |
%d |
Day of the month, numeric (00..31) |
%e |
Day of the month, numeric (0..31) |
%m |
Month, numeric (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..53), where Sunday is the first
day of the week |
%u |
Week (0..53), where Monday is the first
day of the week |
%V |
Week (1..53), where Sunday is the first
day of the week. Used with '%X' |
%v |
Week (1..53), where Monday is the first
day of the week. Used with '%x' |
%% |
A literal `%'. |
mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
-> 'Saturday October 1997'
mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> select DATE_FORMAT('1997-10-04 22:23:00',
'%D %y %a %d %m %b %j');
-> '4th 97 Sat 04 10 Oct 277'
mysql> select DATE_FORMAT('1997-10-04 22:23:00',
'%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> select DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'
As of MySQL Version 3.23, the `%'
character is required before format specifier characters. In earlier versions
of MySQL, `%' was optional.
TIME_FORMAT(time,format)
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
'YYYY-MM-DD' or
YYYYMMDD format, depending on whether the function is used in a
string or numeric context: mysql> select CURDATE();
-> '1997-12-15'
mysql> select CURDATE() + 0;
-> 19971215
CURTIME()
CURRENT_TIME
'HH:MM:SS' or
HHMMSS format, depending on whether the function is used in a
string or numeric context: mysql> select CURTIME();
-> '23:50:26'
mysql> select CURTIME() + 0;
-> 235026
NOW()
SYSDATE()
CURRENT_TIMESTAMP
'YYYY-MM-DD
HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether
the function is used in a string or numeric context: mysql> select NOW();
-> '1997-12-15 23:50:26'
mysql> select NOW() + 0;
-> 19971215235026
UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)
'1970-01-01 00:00:00' GMT). If UNIX_TIMESTAMP() is
called with a date argument, it returns the value of the argument
as seconds since '1970-01-01 00:00:00' GMT. date may
be a DATE string, a DATETIME string, a
TIMESTAMP, or a number in the format YYMMDD or
YYYYMMDD in local time: mysql> select UNIX_TIMESTAMP();
-> 882226357
mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00');
-> 875996580
When UNIX_TIMESTAMP is used on a TIMESTAMP
column, the function will receive the value directly, with no implicit
``string-to-unix-timestamp'' conversion. If you give
UNIX_TIMESTAMP() a wrong or out-of-range date, it will return 0.
FROM_UNIXTIME(unix_timestamp)
unix_timestamp argument as a
value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS
format, depending on whether the function is used in a string or numeric
context: mysql> select FROM_UNIXTIME(875996580);
-> '1997-10-04 22:23:00'
mysql> select FROM_UNIXTIME(875996580) + 0;
-> 19971004222300
FROM_UNIXTIME(unix_timestamp,format)
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)
seconds argument, converted to hours, minutes,
and seconds, as a value in 'HH:MM:SS' or HHMMSS
format, depending on whether the function is used in a string or numeric
context: mysql> select SEC_TO_TIME(2378);
-> '00:39:38'
mysql> select SEC_TO_TIME(2378) + 0;
-> 3938
TIME_TO_SEC(time)
time argument, converted to seconds: mysql> select TIME_TO_SEC('22:23:00');
-> 80580
mysql> select TIME_TO_SEC('00:39:38');
-> 2378
DATABASE()
mysql> select DATABASE();
-> 'test'
If there is no current database, DATABASE() returns the
empty string.
USER()
SYSTEM_USER()
SESSION_USER()
mysql> select USER();
-> 'davida@localhost'
In MySQL Version 3.22.11 or later, this includes the
client hostname as well as the user name. You can extract just the user name
part like this (which works whether or not the value includes a hostname
part): mysql> select substring_index(USER(),"@",1);
-> 'davida'
PASSWORD(str)
str.
This is the function that is used for encrypting MySQL
passwords for storage in the Password column of the
user grant table: mysql> select PASSWORD('badpwd');
-> '7f84554057dd964b'
PASSWORD() encryption is non-reversible.
PASSWORD() does not perform password encryption in the same way
that Unix passwords are encrypted. 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])
str using the Unix crypt() system call.
The salt argument should be a string with two characters. (As of
MySQL Version 3.22.16, salt may be longer than
two characters.): mysql> select ENCRYPT("hello");
-> 'VxuFAJXVARROc'
If crypt() is not available on your system,
ENCRYPT() always returns NULL.
ENCRYPT() ignores all but the first 8 characters of
str, at least on some systems. This will be determined by the
behavior of the underlying crypt() system call.
ENCODE(str,pass_str)
str using pass_str as the password. To
decrypt the result, use DECODE(). The results is a binary string
of the same length as string. If you want to save it in a column,
use a BLOB column type.
DECODE(crypt_str,pass_str)
crypt_str using
pass_str as the password. crypt_str should be a
string returned from ENCODE().
MD5(string)
mysql> select MD5("testing");
-> 'ae2b1fca515949e5d54fb22b8ed95575'
This is an "RSA Data Security, Inc. MD5 Message-Digest Algorithm".
LAST_INSERT_ID([expr])
AUTO_INCREMENT column. See section 23.1.3.126
mysql_insert_id(). mysql> select LAST_INSERT_ID();
-> 195
The last ID that was generated is maintained in the server on a
per-connection basis. It will not be changed by another client. It will not
even be changed if you update another AUTO_INCREMENT column with
a non-magic value (that is, a value that is not NULL and not
0). If expr is given as an
argument to LAST_INSERT_ID() in an UPDATE clause,
then the value of the argument is returned as a LAST_INSERT_ID()
value. This can be used to simulate sequences. First create the table: mysql> create table sequence (id int not null); mysql> insert into sequence values (0);Then the table can be used to generate sequence numbers like this:
mysql> update sequence set id=LAST_INSERT_ID(id+1);You can generate sequences without calling
LAST_INSERT_ID(), but the utility of using the function this way
is that the ID value is maintained in the server as the last automatically
generated value. You can retrieve the new ID as you would read any normal
AUTO_INCREMENT value in MySQL. For example,
LAST_INSERT_ID() (without an argument) will return the new ID.
The C API function mysql_insert_id() can also be used to get the
value.
FORMAT(X,D)
X to a format like
'#,###,###.##', rounded to D decimals. If
D is 0, the result will have no decimal point or
fractional part: mysql> select FORMAT(12332.123456, 4);
-> '12,332.1235'
mysql> select FORMAT(12332.1,4);
-> '12,332.1000'
mysql> select FORMAT(12332.2,0);
-> '12,332'
VERSION()
mysql> select VERSION();
-> '3.23.13-log'
Note that if your version ends with -log this means that
logging is enabled.
CONNECTION_ID()
thread_id) for the connection.
Every connection has its own unique id: mysql> select CONNECTION_ID();
-> 1
GET_LOCK(str,timeout)
str,
with a timeout of timeout seconds. Returns 1 if the
lock was obtained successfully, 0 if the attempt timed out, or
NULL if an error occurred (such as running out of memory or the
thread was killed with mysqladmin kill). A lock is released when
you execute RELEASE_LOCK(), execute a new
GET_LOCK(), or the thread terminates. This function can be used
to implement application locks or to simulate record locks. It blocks requests
by other clients for locks with the same name; clients that agree on a given
lock string name can use the string to perform cooperative advisory locking: mysql> select GET_LOCK("lock1",10);
-> 1
mysql> select GET_LOCK("lock2",10);
-> 1
mysql> select RELEASE_LOCK("lock2");
-> 1
mysql> select RELEASE_LOCK("lock1");
-> NULL
Note that the second RELEASE_LOCK() call returns
NULL because the lock "lock1" was automatically
released by the second GET_LOCK() call.
RELEASE_LOCK(str)
str that was obtained
with GET_LOCK(). Returns 1 if the lock was released,
0 if the lock wasn't locked by this thread (in which case the
lock is not released), and NULL if the named lock didn't exist.
The lock will not exist if it was never obtained by a call to
GET_LOCK() or if it already has been released.
BENCHMARK(count,expr)
BENCHMARK() function executes the expression
expr repeatedly count times. It may be used to time
how fast MySQL processes the expression. The result value is
always 0. The intended use is in the mysql client,
which reports query execution times: mysql> select BENCHMARK(1000000,encode("hello","goodbye"));
+----------------------------------------------+
| BENCHMARK(1000000,encode("hello","goodbye")) |
+----------------------------------------------+
| 0 |
+----------------------------------------------+
1 row in set (4.74 sec)
The time reported is elapsed time on the client end, not CPU time on the
server end. It may be advisable to execute BENCHMARK() several
times, and interpret the result with regard to how heavily loaded the server
machine is.
INET_NTOA(expr)
mysql> select INET_NTOA(3520061480);
-> "209.207.224.40"
INET_ATON(expr)
mysql> select INET_ATON("209.207.224.40");
-> 3520061480
The generated number is always in network byte order; For example the
above number is calculated as 209*255^3 + 207*255^2 + 224*255
+40.
MASTER_POS_WAIT(log_name, log_pos)
GROUP BY ClausesIf you use a group function in a statement containing no GROUP
BY clause, it is equivalent to grouping on all rows.
COUNT(expr)
NULL
values in the rows retrieved by a SELECT statement: mysql> select student.student_name,COUNT(*)
from student,course
where student.student_id=course.student_id
GROUP BY student_name;
COUNT(*) is somewhat different in that it returns a count
of the number of rows retrieved, whether or not they contain NULL
values. COUNT(*) is optimized to return very quickly if the
SELECT retrieves from one table, no other columns are retrieved,
and there is no WHERE clause. For example: mysql> select COUNT(*) from student;
COUNT(DISTINCT expr,[expr...])
NULL values: mysql> select COUNT(DISTINCT results) from student;In MySQL you can get the number of distinct expression combinations that don't contain NULL by giving a list of expressions. In ANSI SQL you would have to do a concatenation of all expressions inside
CODE(DISTINCT ..).
AVG(expr)
expr: mysql> select student_name, AVG(test_score)
from student
GROUP BY student_name;
MIN(expr)
MAX(expr)
expr.
MIN() and MAX() may take a string argument; in such
cases they return the minimum or maximum string value. See section 12.4
How MySQL Uses Indexes. mysql> select student_name, MIN(test_score), MAX(test_score)
from student
GROUP BY student_name;
SUM(expr)
expr. Note that if the return set has no
rows, it returns NULL!
STD(expr)
STDDEV(expr)
expr. This is an extension
to ANSI SQL. The STDDEV() form of this function is provided for
Oracle compatibility.
BIT_OR(expr)
OR of all bits in expr. The
calculation is performed with 64-bit (BIGINT) precision.
BIT_AND(expr)
AND of all bits in expr. The
calculation is performed with 64-bit (BIGINT) precision. MySQL has extended the use of GROUP
BY. You can use columns or calculations in the SELECT
expressions that don't appear in the GROUP BY part. This stands for
any possible value for this group. You can use this to get better
performance by avoiding sorting and grouping on unnecessary items. For example,
you don't need to group on customer.name in the following query:
mysql> select order.custid,customer.name,max(payments)
from order,customer
where order.custid = customer.custid
GROUP BY order.custid;
In ANSI SQL, you would have to add customer.name to the
GROUP BY clause. In MySQL, the name is redundant
if you don't run in ANSI mode.
Don't use this feature if the columns you omit from the
GROUP BY part aren't unique in the group! You will get
unpredictable results.
In some cases, you can use MIN() and MAX() to
obtain a specific column value even if it isn't unique. The following gives the
value of column from the row containing the smallest value in the
sort column:
substr(MIN(concat(rpad(sort,6,' '),column)),7)
See section 9.3.4 The Rows Holding the Group-wise Maximum of a Certain Field.
Note that if you are using
MySQL Version 3.22 (or earlier) or if you are trying to follow
ANSI SQL, you can't use expressions in GROUP BY or ORDER
BY clauses. You can work around this limitation by using an alias for the
expression:
mysql> SELECT id,FLOOR(value/100) AS val FROM tbl_name
GROUP BY id,val ORDER BY val;
In MySQL Version 3.23 you can do:
mysql> SELECT id,FLOOR(value/100) FROM tbl_name ORDER BY RAND();
CREATE DATABASE SyntaxCREATE DATABASE [IF NOT EXISTS] db_name
CREATE DATABASE creates a database with the given name. Rules
for allowable database names are given in section 7.1.5
Database, Table, Index, Column, and Alias Names. An error occurs if the
database already exists and you didn't specify IF NOT EXISTS.
Databases in MySQL are implemented as directories containing
files that correspond to tables in the database. Because there are no tables in
a database when it is initially created, the CREATE DATABASE
statement only creates a directory under the MySQL data
directory.
You can also create databases with
mysqladmin. See section 14.1
Overview of the Different MySQL Programs.
DROP DATABASE SyntaxDROP DATABASE [IF EXISTS] db_name
DROP DATABASE drops all tables in the database and deletes the
database. If you do a DROP DATABASE on a symbolic linked database,
both the link and the original database is deleted. Be VERY careful with
this command!
DROP DATABASE returns the number of files that were removed from
the database directory. Normally, this is three times the number of tables,
because normally each table corresponds to a `.MYD' file, a
`.MYI' file, and a `.frm' file.
The DROP DATABASE command removes from the given database
directory all files with the following extensions:
| .BAK | .DAT | .HSH | .ISD |
| .ISM | .ISM | .MRG | .MYD |
| .MYI | .db | .frm |
All subdirectories that consists of 2 digits (RAID directories)
are also removed.
In MySQL Version 3.22 or later, you can use the keywords
IF EXISTS to prevent an error from occurring if the database
doesn't exist.
You can also drop databases with mysqladmin.
See section 14.1
Overview of the Different MySQL Programs.
CREATE TABLE SyntaxCREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]
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] (index_col_name,...)
or INDEX [index_name] (index_col_name,...)
or UNIQUE [INDEX] [index_name] (index_col_name,...)
or FULLTEXT [INDEX] [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
table_options:
TYPE = {BDB | HEAP | ISAM | INNODB | MERGE | MYISAM }
or AUTO_INCREMENT = #
or AVG_ROW_LENGTH = #
or CHECKSUM = {0 | 1}
or COMMENT = "string"
or MAX_ROWS = #
or MIN_ROWS = #
or PACK_KEYS = {0 | 1}
or PASSWORD = "string"
or DELAY_KEY_WRITE = {0 | 1}
or ROW_FORMAT= { default | dynamic | fixed | compressed }
or RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=# RAID_CHUNKSIZE=#
or UNION = (table_name,[table_name...])
select_statement:
[IGNORE | REPLACE] SELECT ... (Some legal select statement)
CREATE TABLE creates a table with the given name in the current
database. Rules for allowable table names are given in section 7.1.5
Database, Table, Index, Column, and Alias Names. An error occurs if there is
no current database or if the table already exists.
In MySQL Version 3.22 or later, the table name can be
specified as db_name.tbl_name. This works whether or not there is a
current database.
In MySQL Version 3.23, you can use the
TEMPORARY keyword when you create a table. A temporary table will
automatically be deleted if a connection dies and the name is per connection.
This means that two different connections can both use the same temporary table
name without conflicting with each other or with an existing table of the same
name. (The existing table is hidden until the temporary table is deleted).
In MySQL Version 3.23 or later, you can use the keywords
IF NOT EXISTS so that an error does not occur if the table already
exists. Note that there is no verification that the table structures are
identical.
Each table tbl_name is represented by some files in the database
directory. In the case of MyISAM-type tables you will get:
| File | Purpose |
tbl_name.frm |
Table definition (form) file |
tbl_name.MYD |
Data file |
tbl_name.MYI |
Index file |
For more information on the properties of the various column types, see section 7.3 Column Types:
NULL nor NOT NULL is specified, the
column is treated as though NULL had been specified.
AUTO_INCREMENT. When you insert a value of NULL
(recommended) or 0 into an AUTO_INCREMENT column,
the column is set to value+1, where value is the
largest value for the column currently in the table.
AUTO_INCREMENT sequences begin with 1. See section
23.1.3.126
mysql_insert_id(). If you delete the row containing the
maximum value for an AUTO_INCREMENT column, the value will be
reused with an ISAM table but not with a MyISAM table. If you
delete all rows in the table with DELETE FROM table_name (without
a WHERE) in AUTOCOMMIT mode, the sequence starts
over for both table types. NOTE: There can be only one
AUTO_INCREMENT column per table, and it must be indexed.
MySQL Version 3.23 will also only work properly if the
auto_increment column only has positive values. Inserting a negative number is
regarded as inserting a very large positive number. This is done to avoid
precision problems when numbers 'wrap' over from positive to negative and also
to ensure that one doesn't accidentally get an auto_increment column that
contains 0. To make
MySQL compatible with some ODBC applications, you can find
the last inserted row with the following query: SELECT * FROM tbl_name WHERE auto_col IS NULL
NULL values are handled differently for
TIMESTAMP columns than for other column types. You cannot store a
literal NULL in a TIMESTAMP column; setting the
column to NULL sets it to the current date and time. Because
TIMESTAMP columns behave this way, the NULL and
NOT NULL attributes do not apply in the normal way and are
ignored if you specify them. On the other hand, to make it easier for
MySQL clients to use TIMESTAMP columns, the
server reports that such columns may be assigned NULL values
(which is true), even though TIMESTAMP never actually will
contain a NULL value. You can see this when you use
DESCRIBE tbl_name to get a description of your table. Note that
setting a TIMESTAMP column to 0 is not the same as
setting it to NULL, because 0 is a valid
TIMESTAMP value.
DEFAULT value is specified for a column,
MySQL automatically assigns one. If the column may take
NULL as a value, the default value is NULL. If the
column is declared as NOT NULL, the default value depends on the
column type:
AUTO_INCREMENT attribute, the default is 0. For an
AUTO_INCREMENT column, the default value is the next value in
the sequence.
TIMESTAMP, the default
is the appropriate zero value for the type. For the first
TIMESTAMP column in a table, the default value is the current
date and time. See section 7.3.3
Date and Time Types.
ENUM, the default value is the
empty string. For ENUM, the default is the first enumeration
value. NOW() or CURRENT_DATE.
KEY is a synonym for INDEX.
UNIQUE key can have only
distinct values. An error occurs if you try to add a new row with a key that
matches an existing row.
PRIMARY KEY is a unique KEY
with the extra constraint that all key columns must be defined as NOT
NULL. In MySQL the key is named PRIMARY.
A table can have only one PRIMARY KEY. If you don't have a
PRIMARY KEY and some applications ask for the PRIMARY
KEY in your tables, MySQL will return the first
UNIQUE key, which doesn't have any NULL columns, as
the PRIMARY KEY.
PRIMARY KEY can be a multiple-column index. However, you
cannot create a multiple-column index using the PRIMARY KEY key
attibute in a column specification. Doing so will mark only that single column
as primary. You must use the PRIMARY KEY(index_col_name, ...)
syntax.
PRIMARY or UNIQUE key consists of only
one column and this is of type integer, you can also refer to it as
_rowid (new in Version 3.23.11).
index_col_name, with an optional suffix
(_2, _3, ...) to make it unique. You
can see index names for a table using SHOW INDEX FROM tbl_name.
See section 7.28
SHOW Syntax.
MyISAM table
type supports indexes on columns that can have NULL values. In
other cases you must declare such columns NOT NULL or an error
results.
col_name(length) syntax, you can specify an index that
uses only a part of a CHAR or VARCHAR column. This
can make the index file much smaller. See section 7.3.6
Column Indexes.
MyISAM table type supports indexing on
BLOB and TEXT columns. When putting an index on a
BLOB or TEXT column you MUST always specify the
length of the index: CREATE TABLE test (blob_col BLOB, index(blob_col(10)));
ORDER BY or GROUP BY with a
TEXT or BLOB column, only the first
max_sort_length bytes are used. See section 7.3.4.2
The BLOB and TEXT Types.
MyISAM table type supports FULLTEXT
indexes. They can be created only from VARCHAR and
TEXT columns. Indexing always happens over the entire column,
partial indexing is not supported. See section 25.2
MySQL Full-text Search for details of operation.
FOREIGN KEY, CHECK, and
REFERENCES clauses don't actually do anything. The syntax for
them is provided only for compatibility, to make it easier to port code from
other SQL servers and to run applications that create tables with references.
See section 5.4
Functionality Missing from MySQL.
NULL column takes one bit extra, rounded up to the
nearest byte.
row length = 1
+ (sum of column lengths)
+ (number of NULL columns + 7)/8
+ (number of variable-length columns)
table_options and SELECT options are only
implemented in MySQL Version 3.23 and above. The different
table types are:
| BDB or Berkeley_db | Transaction-safe tables with page locking. See section 8.5 BDB or Berkeley_DB Tables. |
| GEMINI | Transaction-safe tables with row-level locking See section 8.6 GEMINI Tables. |
| HEAP | The data for this table is only stored in memory. See section 8.4 HEAP Tables. |
| ISAM | The original table handler. See section 8.3 ISAM Tables. |
| INNODB | Transaction-safe tables with row locking. See section 8.7 INNODB Tables. |
| MERGE | A collection of MyISAM tables used as one table. See section 8.2 MERGE Tables. |
| MyISAM | The new binary portable table handler that is replacing ISAM. See section 8.1 MyISAM Tables. |
TYPE=BDB is
specified, and that distribution of MySQL does not support
BDB tables, the table will be created as MyISAM
instead. The other table options are used to optimize the behavior of the
table. In most cases, you don't have to specify any of them. The options work
for all table types, if not otherwise indicated:
AUTO_INCREMENT |
The next auto_increment value you want to set for your table (MyISAM). |
AVG_ROW_LENGTH |
An approximation of the average row length for your table. You only need to set this for large tables with variable size records. |
CHECKSUM |
Set this to 1 if you want MySQL to maintain a checksum for all rows (makes the table a little slower to update but makes it easier to find corrupted tables) (MyISAM). |
COMMENT |
A 60-character comment for your table. |
MAX_ROWS |
Max number of rows you plan to store in the table. |
MIN_ROWS |
Minimum number of rows you plan to store in the table. |
PACK_KEYS |
Set this to 1 if you want to have a smaller index. This usually makes updates slower and reads faster (MyISAM, ISAM). |
PASSWORD |
Encrypt the .frm file with a password. This option
doesn't do anything in the standard MySQL version. |
DELAY_KEY_WRITE |
Set this to 1 if want to delay key table updates until the table is closed (MyISAM). |
ROW_FORMAT |
Defines how the rows should be stored (for the future). |
MyISAM table,
MySQL uses the product of max_rows *
avg_row_length to decide how big the resulting table will be. If you
don't specify any of the above options, the maximum size for a table will be
4G (or 2G if your operating systems only supports 2G tables). The reason for
this is just to keep down the pointer sizes to make the index smaller and
faster if you don't really need big files. If you don't use
PACK_KEYS, the default is to only pack strings, not numbers. If
you use PACK_KEYS=1, numbers will be packed as well. When packing
binary number keys, MySQL will use prefix compression. This
means that you will only get a big benefit of this if you have many numbers
that are the same. Prefix compression means that every key needs one extra
byte to indicate how many bytes of the previous key are the same for the next
key (note that the pointer to the row is stored in high-byte-first-order
directly after the key, to improve compression.) This means that if you have
many equal keys on two rows in a row, all following 'same' keys will usually
only take 2 bytes (including the pointer to the row). Compare this to the
ordinary case where the following keys will take storage_size_for_key +
pointer_size (usually 4). On the other hand, if all keys are totally
different, you will lose 1 byte per key, if the key isn't a key that can have
NULL values (In this case the packed key length will be stored in
the same byte that is used to mark if a key is NULL.)
SELECT after the CREATE
STATEMENT, MySQL will create new fields for all
elements in the SELECT. For example: mysql> CREATE TABLE test (a int not null auto_increment,
primary key (a), key(b))
TYPE=MyISAM SELECT b,c from test2;
This will create a MyISAM table with 3 columns. Note that
the table will automatically be deleted if any errors occur while copying data
into the table. To ensure that the update log/binary log can be used to
re-create the original tables, MySQL will not allow
concurrent inserts during CREATE TABLE .... SELECT.
RAID_TYPE option will help you to break the 2G/4G limit
for the MyISAM data file (not the index file) on operating systems that don't
support big files. You can get also more speed from the I/O bottleneck by
putting RAID directories on different physical disks.
RAID_TYPE will work on any OS, as long as you have configured
MySQL with --with-raid. For now the only allowed
RAID_TYPE is STRIPED (1 and
RAID0 are aliases for this). If you specify
RAID_TYPE=STRIPED for a MyISAM table,
MyISAM will create RAID_CHUNKS subdirectories named
00, 01, 02 in the database directory. In each of these directories
MyISAM will create a table_name.MYD. When writing
data to the data file, the RAID handler will map the first
RAID_CHUNKSIZE *1024 bytes to the first file, the next
RAID_CHUNKSIZE *1024 bytes to the next file and so on.
UNION is used when you want to use a collection of identical
tables as one. This only works with MERGE tables. See section 8.2
MERGE Tables. For the moment you need to have SELECT,
UPDATE, and DELETE privileges on the tables you map
to a MERGE table. All mapped tables must be in the same database
as the MERGE table.
PRIMARY key will be placed first,
followed by all UNIQUE keys and then the normal keys. This helps
the MySQL optimizer to prioritize which key to use and also
more quickly detect duplicated UNIQUE keys. In some cases, MySQL silently changes a column specification
from that given in a CREATE TABLE statement. (This may also occur
with ALTER TABLE.):
VARCHAR columns with a length less than four are changed to
CHAR.
VARCHAR, TEXT, or
BLOB), all CHAR columns longer than three characters
are changed to VARCHAR columns. This doesn't affect how you use
the columns in any way; in MySQL, VARCHAR is
just a different way to store characters. MySQL performs this
conversion because it saves space and makes table operations faster. See
section 8
MySQL Table Types.
TIMESTAMP display sizes must be even and in the range from 2
to 14. If you specify a display size of 0 or greater than 14, the size is
coerced to 14. Odd-valued sizes in the range from 1 to 13 are coerced to the
next higher even number.
NULL in a TIMESTAMP
column; setting it to NULL sets it to the current date and time.
Because TIMESTAMP columns behave this way, the NULL
and NOT NULL attributes do not apply in the normal way and are
ignored if you specify them. DESCRIBE tbl_name always reports
that a TIMESTAMP column may be assigned NULL values.
If you want to see whether or not MySQL used a column type
other than the one you specified, issue a DESCRIBE tbl_name
statement after creating or altering your table.
Certain other column type changes may occur if you
compress a table using myisampack. See section 8.1.2.3
Compressed Table Characteristics.
ALTER TABLE SyntaxALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]
alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
or ADD [COLUMN] (create_definition, create_definition,...)
or ADD INDEX [index_name] (index_col_name,...)
or ADD PRIMARY KEY (index_col_name,...)
or ADD UNIQUE [index_name] (index_col_name,...)
or ADD FULLTEXT [index_name] (index_col_name,...)
or ADD [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
[reference_definition]
or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
or CHANGE [COLUMN] old_col_name create_definition
or MODIFY [COLUMN] create_definition
or DROP [COLUMN] col_name
or DROP PRIMARY KEY
or DROP INDEX index_name
or RENAME [TO] new_tbl_name
or ORDER BY col
or table_options
ALTER TABLE allows you to change the structure of an existing
table. For example, you can add or delete columns, create or destroy indexes,
change the type of existing columns, or rename columns or the table itself. You
can also change the comment for the table and type of the table. See section 7.7
CREATE TABLE Syntax.
If you use ALTER TABLE to change a column specification but
DESCRIBE tbl_name indicates that your column was not changed, it is
possible that MySQL ignored your modification for one of the
reasons described in section 7.7.1
Silent Column Specification Changes. For example, if you try to change a
VARCHAR column to CHAR, MySQL will
still use VARCHAR if the table contains other variable-length
columns.
ALTER TABLE works by making a temporary copy of the original
table. The alteration is performed on the copy, then the original table is
deleted and the new one is renamed. This is done in such a way that all updates
are automatically redirected to the new table without any failed updates. While
ALTER TABLE is executing, the original table is readable by other
clients. Updates and writes to the table are stalled until the new table is
ready.
Note that if you use any other option to ALTER TABLE than
RENAME, MySQL will always create a temporary
table, even if the data wouldn't strictly need to be copied (like when you
change the name of a column). We plan to fix this in the future, but as one
doesn't normally do ALTER TABLE that often this isn't that high on
our TODO.
ALTER TABLE, you need select,
insert, delete, update,
create, and drop privileges on the table.
IGNORE is a MySQL extension to ANSI SQL92.
It controls how ALTER TABLE works if there are duplicates on
unique keys in the new table. If IGNORE isn't specified, the copy
is aborted and rolled back. If IGNORE is specified, then for rows
with duplicates on a unique key, only the first row is used; the others are
deleted.
ADD, ALTER,
DROP, and CHANGE clauses in a single ALTER
TABLE statement. This is a MySQL extension to ANSI
SQL92, which allows only one of each clause per ALTER TABLE
statement.
CHANGE col_name, DROP col_name, and DROP
INDEX are MySQL extensions to ANSI SQL92.
MODIFY is an Oracle extension to ALTER TABLE.
TRUNCATE is an Oracle extension. See section 7.18
TRUNCATE Syntax.
COLUMN is a pure noise word and can be
omitted.
ALTER TABLE tbl_name RENAME TO new_name without
any other options, MySQL simply renames the files that
correspond to the table tbl_name. There is no need to create the
temporary table. See section 7.9
RENAME TABLE Syntax.
create_definition clauses use the same syntax for
ADD and CHANGE as for CREATE TABLE.
Note that this syntax includes the column name, not just the column type. See
section 7.7
CREATE TABLE Syntax.
CHANGE old_col_name
create_definition clause. To do so, specify the old and new column
names and the type that the column currently has. For example, to rename an
INTEGER column from a to b, you can do
this: mysql> ALTER TABLE t1 CHANGE a b INTEGER;If you want to change a column's type but not the name,
CHANGE syntax still requires two column names even if they are
the same. For example: mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;However, as of MySQL Version 3.22.16a, you can also use
MODIFY to change a column's type without renaming it: mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
CHANGE or MODIFY to shorten a column
for which an index exists on part of the column (for instance, if you have an
index on the first 10 characters of a VARCHAR column), you cannot
make the column shorter than the number of characters that are indexed.
CHANGE or
MODIFY, MySQL tries to convert data to the new
type as well as possible.
FIRST or ADD ... AFTER col_name to add a column at a
specific position within a table row. The default is to add the column last.
ALTER COLUMN specifies a new default value for a column or
removes the old default value. If the old default is removed and the column
can be NULL, the new default is NULL. If the column
cannot be NULL, MySQL assigns a default value.
Default value assignment is described in section 7.7
CREATE TABLE Syntax.
DROP INDEX removes an index. This is a MySQL
extension to ANSI SQL92.
DROP PRIMARY KEY drops the primary index. If no such index
exists, it drops the first UNIQUE index in the table.
(MySQL marks the first UNIQUE key as the
PRIMARY KEY if no PRIMARY KEY was specified
explicitly.)
ORDER BY allows you to create the new table with the rows in
a specific order. Note that the table will not remain in this order after
inserts and deletes. In some cases, it may make sorting easier for
MySQL if the table is in order by the column that you wish to
order it by later. This option is mainly useful when you know that you are
mostly going to query the rows in a certain order; By using this option after
big changes to the table, you may be able to get higher performance.
ALTER TABLE on a MyISAM table, all
non-unique indexes are created in a separate batch (like in
REPAIR). This should make ALTER TABLE much faster
when you have many indexes.
mysql_info(), you
can find out how many records were copied, and (when IGNORE is
used) how many records were deleted due to duplication of unique key values.
FOREIGN KEY,
CHECK, and REFERENCES clauses don't actually do
anything. The syntax for them is provided only for compatibility, to make it
easier to port code from other SQL servers and to run applications that create
tables with references. See section 5.4
Functionality Missing from MySQL. Here is an example that shows some of the 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 to change column b
from CHAR(10) to CHAR(20) as well as renaming it from
b to c:
mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
To add a new TIMESTAMP column named d:
mysql> ALTER TABLE t2 ADD d TIMESTAMP;
To add an index on column d, and make column a the
primary key:
mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
To remove column c:
mysql> ALTER TABLE t2 DROP COLUMN c;
To add a new AUTO_INCREMENT integer column named c:
mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD INDEX (c);
Note that we indexed c, because AUTO_INCREMENT
columns must be indexed, and also that we declare c as NOT
NULL, because indexed columns cannot be NULL.
When you add an AUTO_INCREMENT column, column values are filled
in with sequence numbers for you automatically. You can set the first sequence
number by executing SET INSERT_ID=# before ALTER TABLE
or using the AUTO_INCREMENT = # table option. See section 7.33
SET Syntax.
See section 20.20
Problems with ALTER TABLE..
RENAME TABLE SyntaxRENAME TABLE tbl_name TO new_table_name[, tbl_name2 TO new_table_name2,...]
The rename is done atomically, which means that no other thread can access any of the tables while the rename is running. This makes it possible to replace a table with an empty one:
CREATE TABLE new_table (...); RENAME TABLE old_table TO backup_table, new_table TO old_table;
The rename is done from left to right, which means that if you want to swap two tables names, you have to:
RENAME TABLE old_table TO backup_table,
new_table TO old_table,
backup_table TO new_table;
As long as two databases are on the same disk you can also rename from one database to another:
RENAME TABLE current_database.table_name TO other_database.table_name;
When you execute RENAME, you can't have any locked tables or
active transactions. You must also have the ALTER and
DROP privilege on the original table and CREATE and
INSERT privilege on the new table.
If MySQL encounters any errors in a multiple table rename, it will do a reverse rename for all renamed tables to get everything back to the original state.
DROP TABLE SyntaxDROP TABLE [IF EXISTS] tbl_name [, tbl_name,...] [RESTRICT | CASCADE]
DROP TABLE removes one or more tables. All table data and the
table definition are removed, so be careful with this
command!
In MySQL Version 3.22 or later, you can use the keywords
IF EXISTS to prevent an error from occurring for tables that don't
exist.
RESTRICT and CASCADE are allowed to make porting
easier. For the moment they don't do anything.
NOTE: DROP TABLE is not transaction safe and
will automatically commit any active transactions.
OPTIMIZE TABLE SyntaxOPTIMIZE TABLE tbl_name[,tbl_name]...
OPTIMIZE TABLE should be used if you have deleted a large part
of a 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 and to defragment the
data file.
For the moment OPTIMIZE TABLE only works on
MyISAM and BDB tables. For BDB
tables, OPTIMIZE TABLE is currently mapped to ANALYZE
TABLE. See section 7.15
ANALYZE TABLE Syntax.
You can get optimize table to work on other table types by starting
mysqld with --skip-new or --safe-mode,
but in this case OPTIMIZE TABLE is just mapped to ALTER
TABLE.
OPTIMIZE TABLE works the following way:
OPTIMIZE TABLE for MyISAM tables is equvialent of
running myisamchk --quick --check-changed-tables --sort-index
--analyze on the table.
Note that the table is locked during the time OPTIMIZE TABLE is
running!
CHECK TABLE SyntaxCHECK TABLE tbl_name[,tbl_name...] [option [option...]] option = QUICK | FAST | MEDIUM | EXTEND | CHANGED
CHECK TABLE only works on MyISAM tables. On
MyISAM tables it's the same thing as running myisamchk -m
table_name on the table.
If you don't specify any option MEDIUM is used.
Checks the table(s) for errors. For MyISAM tables the key
statistics is updated. The command returns a table with the following columns:
| Column | Value |
| Table | Table name. |
| Op | Always ``check''. |
| Msg_type | One of status, error, info, or
warning. |
| Msg_text | The message. |
Note that you can get many rows of information for each checked table. The
last row will be of Msg_type status and should normally be
OK. If you don't get OK, or Not checked
you should normally run a repair of the table. See section 15.1
Using myisamchk for Table Maintenance and Crash Recovery.
Not checked means that the table the given TYPE told
MySQL that there wasn't any need to check the table.
The different check types stand for the following:
| Type | Meaning |
QUICK |
Don't scan the rows to check for wrong links. |
FAST |
Only check tables which haven't been closed properly. |
CHANGED |
Only check tables which have been changed since last check or haven't been closed properly. |
MEDIUM |
Scan rows to verify that deleted links are ok. This also calculates a key checksum for the rows and verifies this with a calcualted checksum for the keys. |
EXTENDED |
Do a full key lookup for all keys for each row. This ensures that the table is 100 % consistent, but will take a long time! |
For dynamic sized MyISAM tables a started check will always do a
MEDIUM check. For static size rows we skip the row scan for
QUICK and FAST as the rows are very seldom corrupted.
You can combine check options as in:
CHECK TABLE test_table FAST QUICK;
Which only would do a quick check on the table if it wasn't closed properly.
NOTE: that in some case CHECK TABLE will change
the table! This happens if the table is marked as 'corrupted' or 'not closed
properly' but CHECK TABLE didn't find any problems in the table. In
this case CHECK TABLE will mark the table as ok.
If a table is corrupted, then it's most likely that the problem is in the indexes and not in the data part. All of the above check types checks the indexes throughly and should thus find most errors.
If you just want to check a table that you assume is ok, you should use no
check options or the QUICK option. The later should be used when
you are in a hurry and can take the very small risk that QUICK
didn't find an error in the data file (In most cases MySQL
should find, under normal usage, any error in the data file. If this happens
then the table will be marked as 'corrupted', in which case the table can't be
used until it's repaired).
FAST and CHANGED are mostly intended to be used
from a script (for example to be executed from cron) if you want to check your
table from time to time. In most cases you FAST is to be prefered
over CHANGED. (The only case when it isn't is when you suspect a
bug you have found a bug in the MyISAM code.).
EXTENDED is only to be used after you have run a normal check
but still get strange errors from a table when MySQL tries to
update a row or find a row by key (this is VERY unlikely to happen if a normal
check has succeeded!).
BACKUP TABLE SyntaxBACKUP TABLE tbl_name[,tbl_name...] TO '/path/to/backup/directory'
Make a copy of all the table files to the backup directory that are the
minimum needed to restore it. Currenlty only works for MyISAM
tables. For MyISAM table, copies .frm (definition) and
.MYD (data) files. The index file can be rebuilt from those two.
During the backup, read lock will be held for each table, one at time, as
they are being backed up. If you want to backup several tables as a snapshot,
you must first issue LOCK TABLES obtaining a read lock for each
table in the group.
The command returns a table with the following columns:
| Column | Value |
| Table | Table name |
| Op | Always ``backup'' |
| Msg_type | One of status, error, info or
warning. |
| Msg_text | The message. |
Note that BACKUP TABLE is only available in
MySQL version 3.23.25 and later.
RESTORE TABLE SyntaxRESTORE TABLE tbl_name[,tbl_name...] FROM '/path/to/backup/directory'
Restores the table(s) from the backup that was made with BACKUP
TABLE. Existing tables will not be overwritten - if you try to restore
over an existing table, you will get an error. Restore will take longer than
BACKUP due to the need to rebuilt the index. The more keys you have, the longer
it is going to take. Just as BACKUP TABLE, currently only works of
MyISAM tables.
The command returns a table with the following columns:
| Column | Value |
| Table | Table name |
| Op | Always ``restore'' |
| Msg_type | One of status, error, info or
warning. |
| Msg_text | The message. |
ANALYZE TABLE SyntaxANALYZE TABLE tbl_name[,tbl_name...]
Analyze and store the key distribution for the table. During the analyze the
table is locked with a read lock. This works on MyISAM and
BDB tables.
This is equivalent to running myisamchk -a on the table.
MySQL uses the stored key distribution to decide in which order tables should be joined when one does a join on something else than a constant.
The command returns a table with the following columns:
| Column | Value |
| Table | Table name |
| Op | Always ``analyze'' |
| Msg_type | One of status, error, info or
warning. |
| Msg_text | The message. |
You can check the stored key distribution with the SHOW INDEX
command. See section 7.28.1
SHOW Information About Databases, Tables, Columns, and Indexes.
If the table hasn't changed since the last ANALYZE TABLE
command, the table will not be analyzed again.
REPAIR TABLE SyntaxREPAIR TABLE tbl_name[,tbl_name...] [QUICK] [EXTENDED]
REPAIR TABLE only works on MyISAM tables and is the
same as running myisamchk -r table_name on the table.
Repair the corrupted table. The command returns a table with the following columns:
| Column | Value |
| Table | Table name |
| Op | Always ``repair'' |
| Msg_type | One of status, error, info or
warning. |
| Msg_text | The message. |
Note that you can get many rows of information for each repaired table. The
last one row will be of Msg_type status and should normally be
OK. If you don't get OK, you should try repairing the
table with myisamchk -o, as REPAIR TABLE does not yet
implement all the options of myisamchk. In the near future, we will
make it more flexible.
If QUICK is given then MySQL will try to do a
REPAIR of only the index tree.
If you use EXTENDED then MySQL will create the
index row by row instead of creating one index at a time with sorting; This may
be better than sorting on fixed-length keys if you have long char()
keys that compress very good.
DELETE SyntaxDELETE [LOW_PRIORITY] FROM tbl_name
[WHERE where_definition]
[LIMIT rows]
DELETE deletes rows from tbl_name that satisfy the
condition given by where_definition, and returns the number of
records deleted.
If you issue a DELETE with no WHERE clause, all
rows are deleted. If you do this in AUTOCOMMIT mode, this works as
TRUNCATE. See section 7.18
TRUNCATE Syntax. One problem with this is that
DELETE will return zero as the number of affected records, but this
will be fixed in 4.0.
If you really want to know how many records are deleted when you are deleting
all rows, and are willing to suffer a speed penalty, you can use a
DELETE statement of this form:
mysql> DELETE FROM tbl_name WHERE 1>0;
Note that this is MUCH slower than DELETE FROM tbl_name with no
WHERE clause, because it deletes rows one at a time.
If you specify the keyword LOW_PRIORITY, execution of the
DELETE is delayed until no other clients are reading from the
table.
Deleted records are maintained in a linked list and subsequent
INSERT operations reuse old record positions. To reclaim unused
space and reduce file sizes, use the OPTIMIZE TABLE statement or
the myisamchk utility to reorganize tables. OPTIMIZE
TABLE is easier, but myisamchk is faster. See section 7.11
OPTIMIZE TABLE Syntax and section 15.4.3
Table Optimization.
The MySQL-specific LIMIT rows option to
DELETE tells the server the maximum number of rows to be deleted
before control is returned to the client. This can be used to ensure that a
specific DELETE command doesn't take too much time. You can simply
repeat the DELETE command until the number of affected rows is less
than the LIMIT value.
TRUNCATE SyntaxTRUNCATE TABLE table_name
Is in 3.23 and the same thing as DELETE FROM table_name. See
section 7.17
DELETE Syntax. The differences are:
TRUNCATE TABLE will automatically end
the current transaction as if COMMIT would have been called.
SELECT SyntaxSELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[HIGH_PRIORITY]
[DISTINCT | DISTINCTROW | ALL]
select_expression,...
[INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
[LIMIT [offset,] rows]
[PROCEDURE procedure_name]
[FOR UPDATE | IN SHARE MODE]]
SELECT is used to retrieve rows selected from one or more
tables. select_expression indicates the columns you want to
retrieve. SELECT may also be used to retrieve rows computed without
reference to any table. For example:
mysql> SELECT 1 + 1;
-> 2
All keywords used must be given in exactly the order shown above. For
example, a HAVING clause must come after any GROUP BY
clause and before any ORDER BY clause.
SELECT expression may
be given an alias using AS. The alias is used as the expression's
column name and can be used with ORDER BY or HAVING
clauses. For example: mysql> select concat(last_name,', ',first_name) AS full_name
from mytable ORDER BY full_name;
FROM table_references clause indicates the tables from
which to retrieve rows. If you name more than one table, you are performing a
join. For information on join syntax, see section 7.20
JOIN Syntax.
col_name,
tbl_name.col_name, or db_name.tbl_name.col_name. You
need not specify a tbl_name or db_name.tbl_name
prefix for a column reference in a SELECT statement unless the
reference would be ambiguous. See section 7.1.5
Database, Table, Index, Column, and Alias Names, for examples of ambiguity
that require the more explicit column reference forms.
tbl_name [AS] alias_name: mysql> select t1.name, t2.salary from employee AS t1, info AS t2
where t1.name = t2.name;
mysql> select t1.name, t2.salary from employee t1, info t2
where t1.name = t2.name;
ORDER BY
and GROUP BY clauses using column names, column aliases, or
column positions. Column positions begin with 1: mysql> select college, region, seed from tournament
ORDER BY region, seed;
mysql> select college, region AS r, seed AS s from tournament
ORDER BY r, s;
mysql> select college, region, seed from tournament
ORDER BY 2, 3;
To sort in reverse order, add the DESC (descending) keyword
to the name of the column in the ORDER BY clause that you are
sorting by. The default is ascending order; this may be specified explicitly
using the ASC keyword.
WHERE clause use any of the functions that
MySQL support. See section 7.4
Functions for Use in SELECT and WHERE Clauses.
HAVING clause can refer to any column or alias named in
the select_expression. It is applied last, just before items are
sent to the client, with no optimization. Don't use HAVING for
items that should be in the WHERE clause. For example, do not
write this: mysql> select col_name from tbl_name HAVING col_name > 0;Write this instead:
mysql> select col_name from tbl_name WHERE col_name > 0;In MySQL Version 3.22.5 or later, you can also write queries like this:
mysql> select user,max(salary) from users
group by user HAVING max(salary)>10;
In older MySQL versions, you can write this instead: mysql> select user,max(salary) AS sum from users
group by user HAVING sum>10;
SQL_SMALL_RESULT, SQL_BIG_RESULT,
SQL_BUFFER_RESULT, STRAIGHT_JOIN, and
HIGH_PRIORITY are MySQL extensions to ANSI
SQL92.
HIGH_PRIORITY will give the SELECT higher
priority than a statement that updates a table. You should only use this for
queries that are very fast and must be done at once. A SELECT
HIGH_PRIORITY query will run if the table is locked for read even if
there is an update statement that is waiting for the table to be free.
SQL_BIG_RESULT can be used with GROUP BY or
DISTINCT to tell the optimizer that the result set will have many
rows. In this case, MySQL will directly use disk-based
temporary tables if needed. MySQL will also, in this case,
prefer sorting to doing a temporary table with a key on the GROUP
BY elements.
GROUP BY, the output rows will
be sorted according to the GROUP BY as if you would have had an
ORDER BY over all the fields in the GROUP BY.
MySQL has extended the GROUP BY so that you can
also specify ASC and DESC to GROUP BY: SELECT a,COUNT(b) FROM test_table GROUP BY a DESC
GROUP BY to
allow you to select fields which are not mentioned in the GROUP
BY clause. If you are not getting the results you expect from your
query, please read the GROUP BY description. See section 7.4.13
Functions for Use with GROUP BY Clauses.
SQL_BUFFER_RESULT will force the result to be put into a
temporary table. This will help MySQL free the table locks
early and will help in cases where it takes a long time to send the result set
to the client.
SQL_SMALL_RESULT, a MySQL-specific option,
can be used with GROUP BY or DISTINCT to tell the
optimizer that the result set will be small. In this case,
MySQL will use fast temporary tables to store the resulting
table instead of using sorting. In MySQL Version 3.23 this
shouldn't normally be needed.
STRAIGHT_JOIN forces the optimizer to join the tables in the
order in which they are listed in the FROM clause. You can use
this to speed up a query if the optimizer joins the tables in non-optimal
order. See section 7.29
EXPLAIN Syntax (Get Information About a SELECT).
LIMIT clause can be used to constrain the number of rows
returned by the SELECT statement. LIMIT takes one or
two numeric arguments. If two arguments are given, the first specifies the
offset of the first row to return, the second specifies the maximum number of
rows to return. The offset of the initial row is 0 (not 1): mysql> select * from table LIMIT 5,10; # Retrieve rows 6-15If one argument is given, it indicates the maximum number of rows to return:
mysql> select * from table LIMIT 5; # Retrieve first 5 rowsIn other words,
LIMIT n is equivalent to LIMIT
0,n.
SELECT ... INTO OUTFILE 'file_name'
form of SELECT writes the selected rows to a file. The file is
created on the server host and cannot already exist (among other things, this
prevents database tables and files such as `/etc/passwd' from being
destroyed). You must have the file privilege on the server
host to use this form of SELECT. SELECT ... INTO
OUTFILE is mainly intended to let you very quickly dump a table on the
server machine. If you want to create the resulting file on some other host
than the server host you can't use SELECT ... INTO OUTFILE. In
this case you should instead use some client program like mysqldump
--tab or mysql -e "SELECT ..." > outfile to generate
the file. SELECT ... INTO OUTFILE is the complement of LOAD
DATA INFILE; the syntax for the export_options part of the
statement consists of the same FIELDS and LINES
clauses that are used with the LOAD DATA INFILE statement. See
section 7.23
LOAD DATA INFILE Syntax. In the resulting text file, only the
following characters are escaped by the ESCAPED BY character:
ESCAPED BY character
FIELDS TERMINATED BY
LINES TERMINATED BY
ASCII 0 is converted to ESCAPED
BY followed by 0 (ASCII 48). The reason for the above is
that you MUST escape any FIELDS TERMINATED BY, ESCAPED
BY, or LINES TERMINATED BY characters to reliably be able
to read the file back. ASCII 0 is escaped to make it easier to
view with some pagers. As the resulting file doesn't have to conform to the
SQL syntax, nothing else need be escaped. Here follows an example of getting a
file in the format used by many old programs. SELECT a,b,a+b INTO OUTFILE "/tmp/result.text" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\n" FROM test_table;
INTO DUMPFILE instead of
INTO OUTFILE, MySQL will only write one row into
the file, without any column or line terminations and without any escaping.
This is useful if you want to store a blob in a file.
INTO OUTFILE and INTO
DUMPFILE is going to be readable for all users! The reason is that the
MySQL server can't create a file that is owned by anyone else
than the user it's running as (you should never run mysqld as
root), the file has to be word readable so that you can retrieve the rows.
FOR UPDATE on a table handler with page/row
locks, the examined rows will be write locked. JOIN SyntaxMySQL supports the following JOIN syntaxes for
use in SELECT statements:
table_reference, table_reference
table_reference [CROSS] JOIN table_reference
table_reference INNER JOIN table_reference join_condition
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference join_condition
table_reference LEFT [OUTER] JOIN table_reference
table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
{ oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr }
table_reference RIGHT [OUTER] JOIN table_reference join_condition
table_reference RIGHT [OUTER] JOIN table_reference
table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference
Where table_reference is defined as:
table_name [[AS] alias] [USE INDEX (key_list)] [IGNORE INDEX (key_list)]
and join_condition is defined as:
ON conditional_expr | USING (column_list)
Note that in versions before Version 3.23.16, the INNER JOIN
didn't take a join condition!
The last LEFT OUTER JOIN
syntax shown above exists only for compatibility with ODBC:
tbl_name AS alias_name
or tbl_name alias_name: mysql> select t1.name, t2.salary from employee AS t1, info AS t2
where t1.name = t2.name;
INNER JOIN and , (comma) are semantically
equivalent. Both do a full join between the tables used. Normally, you specify
how the tables should be linked in the WHERE condition.
ON conditional is any conditional of the form that may be
used in a WHERE clause.
ON
or USING part in a LEFT JOIN, a row with all columns
set to NULL is used for the right table. You can use this fact to
find records in a table that have no counterpart in another table: mysql> select table1.* from table1
LEFT JOIN table2 ON table1.id=table2.id
where table2.id is NULL;
This example finds all rows in table1 with an
id value that is not present in table2 (that is, all
rows in table1 with no corresponding row in table2).
This assumes that table2.id is declared NOT NULL, of
course. See section 12.5.5
How MySQL Optimizes LEFT JOIN and RIGHT JOIN.
USING (column_list) clause names a list of
columns that must exist in both tables. A USING clause such as: A LEFT JOIN B USING (C1,C2,C3,...)is defined to be semantically identical to an
ON expression
like this: A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3,...
NATURAL [LEFT] JOIN of two tables is defined to be
semantically equivalent to an INNER JOIN or a LEFT
JOIN with a USING clause that names all columns that exist
in both tables.
RIGHT JOIN works analogously as LEFT JOIN. To
keep code portable across databases, it's recommended to use LEFT
JOIN instead of RIGHT JOIN.
STRAIGHT_JOIN is identical to JOIN, except that
the left table is always read before the right table. This can be used for
those (few) cases where the join optimizer puts the tables in the wrong order.
EXPLAIN shows that
MySQL is using the wrong index. By specifying USE INDEX
(key_list), you can tell MySQL to use only one of the
specified indexes to find rows in the table. The alternative syntax
IGNORE INDEX (key_list) can be used to tell
MySQL to not use some particular index. Some examples:
mysql> select * from table1,table2 where table1.id=table2.id;
mysql> select * from table1 LEFT JOIN table2 ON table1.id=table2.id;
mysql> select * from table1 LEFT JOIN table2 USING (id);
mysql> select * from table1 LEFT JOIN table2 ON table1.id=table2.id
LEFT JOIN table3 ON table2.id=table3.id;
mysql> select * from table1 USE INDEX (key1,key2) WHERE key1=1 and key2=2 AND
key3=3;
mysql> select * from table1 IGNORE INDEX (key3) WHERE key1=1 and key2=2 AND
key3=3;
See section 12.5.5
How MySQL Optimizes LEFT JOIN and RIGHT JOIN.
INSERT Syntax INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES (expression,...),(...),...
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name
SET col_name=expression, col_name=expression, ...
or INSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name
SELECT ...
INSERT inserts new rows into an existing table. The INSERT
... VALUES form of the statement inserts rows based on explicitly
specified values. The INSERT ... SELECT form inserts rows selected
from another table or tables. The INSERT ... VALUES form with
multiple value lists is supported in MySQL Version 3.22.5 or
later. The col_name=expression syntax is supported in
MySQL Version 3.22.10 or later.
tbl_name is the table into which rows should be inserted. The
column name list or the SET clause indicates which columns the
statement specifies values for:
INSERT ... VALUES or
INSERT ... SELECT, values for all columns must be provided in the
VALUES() list or by the SELECT. If you don't know
the order of the columns in the table, use DESCRIBE tbl_name to
find out.
CREATE TABLE Syntax.
expression may refer to any column that was set earlier in
a value list. For example, you can say this: mysql> INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);But not this:
mysql> INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);
LOW_PRIORITY, execution of the
INSERT is delayed until no other clients are reading from the
table. In this case the client has to wait until the insert statement is
completed, which may take a long time if the table is in heavy use. This is in
contrast to INSERT DELAYED, which lets the client continue at
once. See section 7.21.2
INSERT DELAYED syntax. Note that LOW_PRIORITY
should normally not be used with MyISAM tables as this disables
concurrent inserts. See section 8.1
MyISAM Tables.
IGNORE in an INSERT
with many value rows, any rows that duplicate an existing PRIMARY
or UNIQUE key in the table are ignored and are not inserted. If
you do not specify IGNORE, the insert is aborted if there is any
row that duplicates an existing key value. You can determine with the C API
function mysql_info() how many rows were inserted into the table.
DONT_USE_DEFAULT_FIELDS option, INSERT statements
generate an error unless you explicitly specify values for all columns that
require a non-NULL value. See section 4.7.3
Typical configure Options.
AUTO_INCREMENT column with
the mysql_insert_id function. See section 23.1.3.126
mysql_insert_id(). If you use INSERT ... SELECT or an
INSERT ... VALUES statement with multiple value lists, you can use
the C API function mysql_info() to get information about the query.
The format of the information string is shown below:
Records: 100 Duplicates: 0 Warnings: 0
Duplicates indicates the number of rows that couldn't be
inserted because they would duplicate some existing unique index value.
Warnings indicates the number of attempts to insert column values
that were problematic in some way. Warnings can occur under any of the following
conditions:
NULL into a column that has been declared NOT
NULL. The column is set to its default value.
'10.34 a'. The
trailing garbage is stripped and the remaining numeric part is inserted. If
the value doesn't make sense as a number at all, the column is set to
0.
CHAR, VARCHAR,
TEXT, or BLOB column that exceeds the column's
maximum length. The value is truncated to the column's maximum length.
INSERT ... SELECT SyntaxINSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(column list)] SELECT ...
With INSERT ... SELECT statement you can quickly insert many
rows into a table from one or many tables.
INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE tblTemp1.fldOrder_ID > 100;
The following conditions hold for an INSERT ... SELECT
statement:
ORDER BY clause.
INSERT statement cannot appear in the
FROM clause of the SELECT part of the query because
it's forbidden in ANSI SQL to SELECT from the same table into
which you are inserting. (The problem is that the SELECT possibly
would find records that were inserted earlier during the same run. When using
sub-select clauses, the situation could easily be very confusing!)
AUTO_INCREMENT columns work as usual.
mysql_info() to get
information about the query. See section 7.21
INSERT Syntax.
INSERT .... SELECT. You can of course also use REPLACE instead of
INSERT to overwrite old rows.
INSERT DELAYED syntaxINSERT DELAYED ...
The DELAYED option for the INSERT statement is a
MySQL-specific option that is very useful if you have clients
that can't wait for the INSERT to complete. This is a common
problem when you use MySQL for logging and you also
periodically run SELECT and UPDATE statements that
take a long time to complete. DELAYED was introduced in
MySQL Version 3.22.15. It is a MySQL extension
to ANSI SQL92.
INSERT DELAYED only works with ISAM and
MyISAM tables. Note that as MyISAM tables supports
concurrent SELECT and INSERT, if there is no empty
blocks in the data file, you very seldom need to use INSERT DELAYED
with MyISAM.
When you use INSERT DELAYED, the client will get an OK at once
and the row will be inserted when the table is not in use by any other thread.
Another major benefit of using INSERT DELAYED is that inserts
from many clients are bundled together and written in one block. This is much
faster than doing many separate inserts.
Note that currently the queued rows are only stored in memory until they are
inserted into the table. This means that if you kill mysqld the
hard way (kill -9) or if mysqld dies unexpectedly, any
queued rows that weren't written to disk are lost!
The following describes in detail what happens when you use the
DELAYED option to INSERT or REPLACE. In
this description, the ``thread'' is the thread that received an INSERT
DELAYED command and ``handler'' is the thread that handles all
INSERT DELAYED statements for a particular table.
DELAYED statement for a table, a
handler thread is created to process all DELAYED statements for
the table, if no such handler already exists.
DELAYED lock already; if not, it tells the handler thread to do
so. The DELAYED lock can be obtained even if other threads have a
READ or WRITE lock on the table. However, the
handler will wait for all ALTER TABLE locks or FLUSH
TABLES to ensure that the table structure is up to date.
INSERT statement, but instead of
writing the row to the table, it puts a copy of the final row into a queue
that is managed by the handler thread. Any syntax errors are noticed by the
thread and reported to the client program.
AUTO_INCREMENT value for the resulting row; it can't obtain them
from the server, because the INSERT returns before the insert
operation has been completed. If you use the C API, the
mysql_info() function doesn't return anything meaningful, for the
same reason.
delayed_insert_limit rows are written, the
handler checks whether or not any SELECT statements are still
pending. If so, it allows these to execute before continuing.
INSERT DELAYED commands are received within
delayed_insert_timeout seconds, the handler terminates.
delayed_queue_size rows are pending already in a
specific handler queue, the thread requesting INSERT DELAYED
waits until there is room in the queue. This is done to ensure that the
mysqld server doesn't use all memory for the delayed memory
queue.
delayed_insert in the Command column. It will
be killed if you execute a FLUSH TABLES command or kill it with
KILL thread_id. However, it will first store all queued rows into
the table before exiting. During this time it will not accept any new
INSERT commands from another thread. If you execute an
INSERT DELAYED command after this, a new handler thread will be
created.
INSERT DELAYED commands have
higher priority than normal INSERT commands if there is an
INSERT DELAYED handler already running! Other update commands
will have to wait until the INSERT DELAYED queue is empty,
someone kills the handler thread (with KILL thread_id), or
someone executes FLUSH TABLES.
INSERT
DELAYED commands:
| Variable | Meaning |
Delayed_insert_threads |
Number of handler threads |
Delayed_writes |
Number of rows written with INSERT DELAYED |
Not_flushed_delayed_rows |
Number of rows waiting to be written |
SHOW STATUS statement or by
executing a mysqladmin extended-status command. Note that INSERT DELAYED is slower than a normal INSERT if the
table is not in use. There is also the additional overhead for the server to
handle a separate thread for each table on which you use INSERT
DELAYED. This means that you should only use INSERT DELAYED
when you are really sure you need it!
REPLACE Syntax REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES (expression,...)
or REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
SELECT ...
or REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
SET col_name=expression, col_name=expression,...
REPLACE works exactly like INSERT, except that if
an old record in the table has the same value as a new record on a unique index,
the old record is deleted before the new record is inserted. See section 7.21
INSERT Syntax.
In other words, you can't access the values of the old row from a
REPLACE statement. In some old MySQL version it
looked like you could do this, but that was a bug that has been corrected.
LOAD DATA INFILE SyntaxLOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY '\t']
[[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\' ]
]
[LINES TERMINATED BY '\n']
[IGNORE number LINES]
[(col_name,...)]
The LOAD DATA INFILE statement reads rows from a text file into
a table at a very high speed. If the LOCAL keyword is specified,
the file is read from the client host. If LOCAL is not specified,
the file must be located on the server. (LOCAL is available in
MySQL Version 3.22.6 or later.)
For security reasons, when reading text files located on the server, the
files must either reside in the database directory or be readable by all. Also,
to use LOAD DATA INFILE on server files, you must have the
file privilege on the server host. See section 6.8
Privileges Provided by MySQL.
If you specify the keyword LOW_PRIORITY, execution of the
LOAD DATA statement is delayed until no other clients are reading
from the table.
Using LOCAL will be a bit slower than letting the server access
the files directly, because the contents of the file must travel from the client
host to the server host. On the other hand, you do not need the
file privilege to load local files.
If you are using MySQL before Version 3.23.24 you can't read
from a FIFO with LOAD DATA INFILE. If you need to read from a FIFO
(for example the output from gunzip), use LOAD DATA LOCAL INFILE
instead.
You can also load data files by using the
mysqlimport utility; 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 server's data directory, whereas a file given as `myfile.txt'
is read from the database directory of the current database. For example, the
following LOAD DATA statement reads the file `data.txt'
from the database directory for db1 because db1 is the
current database, even though the statement explicitly loads the file into a
table in the db2 database:
mysql> USE db1; mysql> LOAD DATA INFILE "data.txt" INTO TABLE db2.my_table;
The REPLACE and IGNORE keywords control handling of
input records that duplicate existing records on unique key values. If you
specify REPLACE, new rows replace existing rows 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.
If you load data from a local file using the LOCAL keyword, the
server has no way to stop transmission of the file in the middle of the
operation, so the default bahavior is the same as if IGNORE is
specified.
If you use LOAD DATA INFILE on an empty MyISAM
table, all non-unique indexes are created in a separate batch (like in
REPAIR). This normally makes LOAD DATA INFILE much
faster when you have many indexes.
LOAD DATA INFILE is the complement of SELECT ... INTO
OUTFILE. See section 7.19
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 LOAD DATA INFILE to act as
follows when reading input:
Conversely, the defaults cause SELECT ... INTO OUTFILE to act as
follows when writing output:
Note that to write FIELDS ESCAPED BY '\\', you must specify two
backslashes for the value to be read as a single backslash.
The IGNORE number LINES option can be used to ignore a header of
column names at the start of the file:
mysql> LOAD DATA INFILE "/tmp/file_name" into table test IGNORE 1 LINES;
When you use SELECT ... INTO OUTFILE in tandem with LOAD
DATA INFILE to write data from a database into a file and then read the
file back into the database later, the field and line handling options for both
commands must match. Otherwise, LOAD DATA INFILE will not interpret
the contents of the file properly. Suppose you use SELECT ... INTO
OUTFILE to write a file with fields delimited by commas:
mysql> SELECT * INTO OUTFILE 'data.txt'
FIELDS TERMINATED BY ','
FROM ...;
To read the comma-delimited file back in, the correct statement would be:
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
FIELDS TERMINATED BY ',';
If instead you tried to read in the file with the statement shown 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 'data.txt' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Any of the field or line handling options may specify an empty string
(''). If not empty, the FIELDS [OPTIONALLY] ENCLOSED
BY and FIELDS ESCAPED BY values must be a single character.
The FIELDS TERMINATED BY and LINES TERMINATED BY
values may be more than one character. For example, to write lines that are
terminated by carriage return-linefeed pairs, or to read a file containing such
lines, specify a LINES TERMINATED BY '\r\n' clause.
For example, to read a file of jokes, that are separated with a line of
%%, into a SQL table you can do:
create table jokes (a int not null auto_increment primary key, joke text not null); load data infile "/tmp/jokes.txt" into table jokes fields terminated by "" lines terminated by "\n%%\n" (joke);
FIELDS [OPTIONALLY] ENCLOSED BY controls quoting of fields. For
output (SELECT ... INTO OUTFILE), if you omit the word
OPTIONALLY, all fields are enclosed by the ENCLOSED BY
character. An example of such output (using a comma as the field delimiter) is
shown 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,
it is possible to generate output that cannot be read properly by LOAD
DATA INFILE. For example, the output just shown above would appear as
shown 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:
FIELDS ESCAPED BY character
FIELDS [OPTIONALLY] ENCLOSED BY character
FIELDS TERMINATED BY and
LINES TERMINATED BY values
0 (what is actually written following the escape
character is ASCII '0', not a zero-valued byte) If the FIELDS ESCAPED BY character is empty, no characters are
escaped. It is probably not a good idea to specify an empty escape character,
particularly if field values in your data contain any of the characters in the
list just given.
For input, if the FIELDS ESCAPED BY character is not empty,
occurrences of that character are stripped and the following character is taken
literally as part of a field value. The exceptions are an escaped
`0' or `N' (for example, \0 or
\N if the escape character is `\'). These sequences
are interpreted as ASCII 0 (a zero-valued byte) and
NULL. See below for the rules on NULL handling.
For more information about `\'-escape syntax, see section 7.1 Literals: How to Write Strings and Numbers.
In certain cases, field and line handling options interact:
LINES TERMINATED BY is an empty string and FIELDS
TERMINATED BY is non-empty, lines are also terminated with FIELDS
TERMINATED BY.
FIELDS TERMINATED BY and FIELDS ENCLOSED
BY values are both empty (''), a fixed-row (non-delimited)
format is used. With fixed-row format, no delimiters are used between fields.
Instead, column values are written and read using the ``display'' widths of
the columns. For example, if a column is declared as INT(7),
values for the column are written using 7-character fields. On input, values
for the column are obtained by reading 7 characters. Fixed-row format also
affects handling of NULL values; see below. Note that fixed-size
format will not work if you are using a multi-byte character set. Handling of NULL values varies, depending on the
FIELDS and LINES options you use:
FIELDS and LINES values,
NULL is written as \N for output and \N
is read as NULL for input (assuming the ESCAPED BY
character is `\').
FIELDS ENCLOSED BY is not empty, a field containing the
literal word NULL as its value is read as a NULL
value (this differs from the word NULL enclosed within
FIELDS ENCLOSED BY characters, which is read as the string
'NULL').
FIELDS ESCAPED BY is empty, NULL is written
as the word NULL.
FIELDS TERMINATED
BY and FIELDS ENCLOSED BY are both empty),
NULL is written as an empty string. Note that this causes both
NULL values and empty strings in the table to be
indistinguishable when written to the file because they are both written as
empty strings. If you need to be able to tell the two apart when reading the
file back in, you should not use fixed-row format. Some cases are not supported by LOAD DATA INFILE:
FIELDS TERMINATED BY and FIELDS
ENCLOSED BY both empty) and BLOB or TEXT
columns.
LOAD DATA INFILE won't be able to interpret the input properly.
For example, the following FIELDS clause would cause problems: FIELDS TERMINATED BY '"' ENCLOSED BY '"'
FIELDS ESCAPED BY is empty, a field value that contains an
occurrence of FIELDS ENCLOSED BY or LINES TERMINATED
BY followed by the FIELDS TERMINATED BY value will cause
LOAD DATA INFILE to stop reading a field or line too early. This
happens because LOAD DATA INFILE cannot properly determine where
the field or line value ends. The following example loads all columns of the persondata table:
mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;
No field list is specified, so LOAD DATA INFILE expects input
rows to contain a field for each table column. The default FIELDS
and LINES values are used.
If you wish to load only some of a table's columns, specify a field list:
mysql> LOAD DATA INFILE 'persondata.txt'
INTO TABLE persondata (col1,col2,...);
You must also specify a field list if the order of the fields in the input file differs from the order of the columns in the table. Otherwise, MySQL cannot tell how to match up input fields with table columns.
If a row has too few fields, the columns for which no input field is present
are set to default values. Default value assignment is described in section 7.7
CREATE TABLE Syntax.
An empty field value is interpreted differently than if the field value is missing:
0.
Note that these are the same values that result if you assign an empty string
explicitly to a string, numeric, or date or time type explicitly in an
INSERT or UPDATE statement.
TIMESTAMP columns are only set to the current date and time if
there is a NULL value for the column, or (for the first
TIMESTAMP column only) if the TIMESTAMP column is left
out from the field list when a field list is specified.
If an input row has too many fields, the extra fields are ignored and the number of warnings is incremented.
LOAD DATA INFILE regards all input as strings, so you can't use
numeric values for ENUM or SET columns the way you can
with INSERT statements. All ENUM and SET
values must be specified as strings!
If you are using the C API, you can get information about
the query by calling the API function mysql_info() when the
LOAD DATA INFILE query finishes. The format of the information
string is shown 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.21
INSERT Syntax), except that LOAD DATA INFILE also
generates warnings when there are too few or too many fields in the input row.
The warnings are not stored anywhere; the number of warnings can only be used as
an indication if everything went well. If you get warnings and want to know
exactly why you got them, one way to do this is to use SELECT ... INTO
OUTFILE into another file and compare this to your original input file.
If you need LOAD DATA to read from a pipe, you can use the
following trick:
mkfifo /mysql/db/x/x chmod 666 /mysql/db/x/x cat < /dev/tcp/10.1.1.12/4711 > /nt/mysql/db/x/x mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x
If you are using a version of MySQL older than 3.23.25 you
can only do the above with LOAD DATA LOCAL INFILE.
For more information about the efficiency of INSERT versus
LOAD DATA INFILE and speeding up LOAD DATA INFILE, See
section 12.5.7
Speed of INSERT Queries.
UPDATE SyntaxUPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1, [col_name2=expr2, ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT #]
UPDATE updates columns in existing table rows with new values.
The SET clause indicates which columns to modify and the values
they should be given. The WHERE clause, if given, specifies which
rows should be updated. Otherwise all rows are updated. If the ORDER
BY clause is specified, the rows will be updated in the order that is
specified.
If you specify the keyword LOW_PRIORITY, execution of the
UPDATE is delayed until no other clients are reading from the
table.
If you specify the keyword IGNORE, the update statement will not
abort even if we get duplicate key errors during the update. Rows that would
cause conflicts will not be updated.
If you access a column from tbl_name in an expression,
UPDATE uses the current value of the column. For example, the
following statement sets the age column to one more than its
current value:
mysql> UPDATE persondata SET age=age+1;
UPDATE assignments are evaluated from left to right. For
example, the following statement doubles the age column, then
increments it:
mysql> UPDATE persondata SET age=age*2, age=age+1;
If you set a column to the value it currently has, MySQL notices this and doesn't update it.
UPDATE returns the number of rows that were
actually changed. In MySQL Version 3.22 or later, the C API
function mysql_info() returns the number of rows that were matched
and updated and the number of warnings that occurred during the
UPDATE.
In MySQL Version 3.23, you can use LIMIT # to
ensure that only a given number of rows are changed.
USE SyntaxUSE 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.
FLUSH SyntaxFLUSH flush_option [,flush_option]
You should use the FLUSH command if you want to clear some of
the internal caches MySQL uses. To execute FLUSH,
you must have the reload privilege.
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 more than
max_connect_errors errors occur in a row for a given host
while connection to the MySQL server,
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 20.4.4
Host '...' is blocked Error.) You can start
mysqld with -O max_connection_errors=999999999
to avoid this error message. |
LOGS |
Closes and reopens all log files. If you have specified the update log file or a binary log file without an extension, the extension number of the log file will be incremented by one relative to the previous file. If you have used an extension in the file name, MySQL will close and reopen the update log file. See section 22.3 The Update Log. |
PRIVILEGES |
Reloads the privileges from the grant tables in the mysql
database. |
TABLES |
Closes all open tables and force all tables in use to be closed. |
[TABLE | TABLES] table_name [,table_name...] |
Flushes only the given tables. |
TABLES WITH READ LOCK |
Closes all open tables and locks all tables for all databases with a
read until one executes UNLOCK TABLES. This is very
convenient way to get backups if you have a file system, like Veritas,that
can take snapshots in time. |
STATUS |
Resets 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.
KILL SyntaxKILL 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 have the process privilege, you can see and kill all threads. Otherwise, you can see and kill only your own threads.
You can also use the mysqladmin processlist and mysqladmin
kill commands to examine and kill threads.
When you do a KILL, a thread specific kill flag is
set for the thread.
In most cases it may take some time for the thread to die as the kill flag is only checked at specific intervals.
SELECT, ORDER BY and GROUP BY
loops, the flag is checked after reading a block of rows. If the kill flag is
set the statement is aborted
ALTER TABLE the kill flag is checked before
each block of rows are read from the original table. If the kill flag was set
the command is aborted and the temporary table is deleted.
UPDATE TABLE and DELETE TABLE, the
kill flag is checked after each block read and after each updated or delete
row. If the kill flag is set the statement is aborted. Note that if you are
not using transactions, the changes will not be rolled back!
GET_LOCK() will abort with NULL.
INSERT DELAYED thread will quickly flush all rows it has
in memory and die.
Locked),
the table lock will be quickly aborted.
write call,
the write is aborted with an disk full error message. SHOW SyntaxSHOW DATABASES [LIKE wild] or SHOW [OPEN] TABLES [FROM db_name] [LIKE wild] or SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE wild] or SHOW INDEX FROM tbl_name [FROM db_name] or SHOW TABLE STATUS [FROM db_name] [LIKE wild] or SHOW STATUS [LIKE wild] or SHOW VARIABLES [LIKE wild] or SHOW LOGS or SHOW [FULL] PROCESSLIST or SHOW GRANTS FOR user or SHOW CREATE TABLE table_name or SHOW MASTER STATUS or SHOW MASTER LOGS or SHOW SLAVE STATUS
SHOW provides information about databases, tables, columns, or
status information about the server. If the LIKE wild part is used,
the wild string can be a string that uses the SQL `%'
and `_' wild-card characters.
SHOW Information About Databases, Tables, Columns, and
IndexesYou can use db_name.tbl_name as an alternative to the
tbl_name FROM db_name syntax. 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 in the output from SHOW TABLES or
mysqlshow db_name.
SHOW OPEN TABLES lists the tables that are currently open in the
table cache. See section 12.2.4
How MySQL Opens and Closes Tables. The Comment field tells how
many times the table is cached and in_use.
SHOW COLUMNS lists the columns in a given table. If you specify
the FULL option, you will also get the privileges you have for each
column. If the column types are different than you expect them to be based on a
CREATE TABLE statement, note that MySQL sometimes
changes column types. See section 7.7.1
Silent Column Specification Changes.
The DESCRIBE statement provides information similar to
SHOW COLUMNS. See section 7.30
DESCRIBE Syntax (Get Information About Columns).
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 INDEX returns the index information in a format that
closely resembles the SQLStatistics call in ODBC. The following
columns are returned:
| Column | Meaning |
Table |
Name of the table. |
Non_unique |
0 if the index can't contain duplicates. |
Key_name |
Name of the index. |
Seq_in_index |
Column sequence number in index, starting with 1. |
Column_name |
Column name. |
Collation |
How the column is sorted in the index. In MySQL, this
can have values `A' (Ascending) or NULL (Not
sorted). |
Cardinality |
Number of unique values in the index. This is updated by running
isamchk -a. |
Sub_part |
Number of indexed characters if the column is only partly indexed.
NULL if the entire key is indexed. |
Comment |
Various remarks. For now, it tells whether index is FULLTEXT or not. |
Note that as the Cardinality is counted based on statistics
stored as integers, it's not necessarily accurate for small tables.
SHOW TABLE STATUSSHOW TABLE STATUS [FROM db_name] [LIKE wild]
SHOW TABLE STATUS (new in Version 3.23) works likes SHOW
STATUS, but provides a lot of information about each table. You can also
get this list using the mysqlshow --status db_name command. The
following columns are returned:
| Column | Meaning |
Name |
Name of the table. |
Type |
Type of table. See section 8 MySQL Table Types. |
Row_format |
The row storage format (Fixed, Dynamic, or Compressed). |
Rows |
Number of rows. |
Avg_row_length |
Average row length. |
Data_length |
Length of the data file. |
Max_data_length |
Max length of the data file. |
Index_length |
Length of the index file. |
Data_free |
Number of allocated but not used bytes. |
Auto_increment |
Next autoincrement value. |
Create_time |
When the table was created. |
Update_time |
When the data file was last updated. |
Check_time |
When the table was last checked. |
Create_options |
Extra options used with CREATE TABLE. |
Comment |
The comment used when creating the table (or some information why MySQL couldn't access the table information). |
INNODB tables will report the free space in the tablespace in
the table comment.
SHOW STATUSSHOW STATUS provides server status
information (like mysqladmin extended-status). The output resembles
that shown below, though the format and numbers probably differ:
+--------------------------+------------+ | Variable_name | Value | +--------------------------+------------+ | Aborted_clients | 0 | | Aborted_connects | 0 | | Bytes_received | 155372598 | | Bytes_sent | 1176560426 | | Connections | 30023 | | Created_tmp_disk_tables | 0 | | Created_tmp_tables | 8340 | | Created_tmp_files | 60 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_delete | 462604 | | Handler_read_first | 105881 | | Handler_read_key | 27820558 | | Handler_read_next | 390681754 | | Handler_read_prev | 6022500 | | Handler_read_rnd | 30546748 | | Handler_read_rnd_next | 246216530 | | Handler_update | 16945404 | | Handler_write | 60356676 | | Key_blocks_used | 14955 | | Key_read_requests | 96854827 | | Key_reads | 162040 | | Key_write_requests | 7589728 | | Key_writes | 3813196 | | Max_used_connections | 0 | | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 1 | | Open_files | 2 | | Open_streams | 0 | | Opened_tables | 44600 | | Questions | 2026873 | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 99646 | | Select_range_check | 0 | | Select_scan | 30802 | | Slave_running | OFF | | Slave_open_temp_tables | 0 | | Slow_launch_threads | 0 | | Slow_queries | 0 | | Sort_merge_passes | 30 | | Sort_range | 500 | | Sort_rows | 30296250 | | Sort_scan | 4650 | | Table_locks_immediate | 1920382 | | Table_locks_waited | 0 | | Threads_cached | 0 | | Threads_created | 30022 | | Threads_connected | 1 | | Threads_running | 1 | | Uptime | 80380 | +--------------------------+------------+
The status variables listed above have the following meaning:
| Variable | Meaning |
Aborted_clients |
Number of connections aborted because the client died without closing the connection properly. See section 20.4.9 Communication Errors / Aborted Connection. |
Aborted_connects |
Number of tries to connect to the MySQL server that failed. See section 20.4.9 Communication Errors / Aborted Connection. |
Bytes_received |
Number of bytes received from all clients. |
Bytes_sent |
Number of bytes sent to all clients. |
Connections |
Number of connection attempts to the MySQL server. |
Created_tmp_disk_tables |
Number of implicit temporary tables on disk created while executing statements. |
Created_tmp_tables |
Number of implicit temporary tables in memory created while executing statements. |
Created_tmp_files |
How many temporary files mysqld have created. |
Delayed_insert_threads |
Number of delayed insert handler threads in use. |
Delayed_writes |
Number of rows written with INSERT DELAYED. |
Delayed_errors |
Number of rows written with INSERT DELAYED for which some
error occurred (probably duplicate key). |
Flush_commands |
Number of executed FLUSH commands. |
Handler_delete |
Number of times a row was deleted from a table. |
Handler_read_first |
Number of times the first entry was read from an index. If this is
high, it suggests that the server is doing a lot of full index scans, for
example, SELECT col1 FROM foo, assuming that col1 is indexed.
|
Handler_read_key |
Number of requests to read a row based on a key. If this is high, it is a good indication that your queries and tables are properly indexed. |
Handler_read_next |
Number of requests to read next row in key order. This will be incremented if you are querying an index column with a range constraint. This also will be incremented if you are doing an index scan. |
Handler_read_rnd |
Number of requests to read a row based on a fixed position. This will be high if you are doing a lot of queries that require sorting of the result. |
Handler_read_rnd_next |
Number of requests to read the next row in the datafile. This will be high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have. |
Handler_update |
Number of requests to update a row in a table. |
Handler_write |
Number of requests to insert a row in a table. |
Key_blocks_used |
The number of used blocks in the key cache. |
Key_read_requests |
The number of requests to read a key block from the cache. |
Key_reads |
The number of physical reads of a key block from disk. |
Key_write_requests |
The number of requests to write a key block to the cache. |
Key_writes |
The number of physical writes of a key block to disk. |
Max_used_connections |
The maximum number of connections in use simultaneously. |
Not_flushed_key_blocks |
Keys blocks in the key cache that has changed but hasn't yet been flushed to disk. |
Not_flushed_delayed_rows |
Number of rows waiting to be written in INSERT DELAY
queues. |
Open_tables |
Number of tables that are open. |
Open_files |
Number of files that are open. |
Open_streams |
Number of streams that are open (used mainly for logging). |
Opened_tables |
Number of tables that have been opened. |
Select_full_join |
Number of joins without keys (Should be 0). |
Select_full_range_join |
Number of joins where we used a range search on reference table. |
Select_range |
Number of joins where we used ranges on the first table. (It's normally not critical even if this is big.) |
Select_scan |
Number of joins where we scanned the first table. |
Select_range_check |
Number of joins without keys where we check for key usage after each row (Should be 0). |
Questions |
Number of queries sent to the server. |
Slave_open_temp_tables |
Number of temporary tables currently open by the slave thread |
Slow_launch_threads |
Number of threads that have taken more than
slow_launch_time to connect. |
Slow_queries |
Number of queries that have taken more than
long_query_time. See section 22.5
The Slow Query Log. |
Sort_merge_passes |
Number of merges the sort has to do. If this value is large you should
consider increasing sort_buffer. |
Sort_range |
Number of sorts that where done with ranges. |
Sort_rows |
Number of sorted rows. |
Sort_scan |
Number of sorts that where done by scanning the table. |
Table_locks_immediate |
Number of times a table lock was acquired immediately. Available after 3.23.33. |
Table_locks_waited |
Number of times a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table(s) or use replication. Available after 3.23.33. |
Threads_cached |
Number of threads in the thread cache. |
Threads_connected |
Number of currently open connections. |
Threads_created |
Number of threads created to handle connections. |
Threads_running |
Number of threads that are not sleeping. |
Uptime |
How many seconds the server has been up. |
Some comments about the above:
Opened_tables is big, then your table_cache
variable is probably too small.
key_reads is big, then your key_cache is
probably too small. The cache hit rate can be calculated with
key_reads/key_read_requests.
Handler_read_rnd is big, then you probably have a lot of
queries that require MySQL to scan whole tables or you have
joins that don't use keys properly.
Threads_created is big, you may want to increase the
thread_cache_size variable. SHOW VARIABLESSHOW VARIABLES [LIKE wild]
SHOW VARIABLES shows the values of some 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. See section 4.16.4
mysqld Command-line Options.
The output resembles that shown below, though the format and numbers may differ somewhat:
+-------------------------+---------------------------+ | Variable_name | Value | +-------------------------+---------------------------+ | ansi_mode | OFF | | back_log | 50 | | basedir | /my/monty/ | | bdb_cache_size | 16777216 | | bdb_log_buffer_size | 32768 | | bdb_home | /my/monty/data/ | | bdb_max_lock | 10000 | | bdb_logdir | | | bdb_shared_data | OFF | | bdb_tmpdir | /tmp/ | | binlog_cache_size | 32768 | | concurrent_insert | ON | | connect_timeout | 5 | | datadir | /my/monty/data/ | | delay_key_write | ON | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | flush | OFF | | flush_time | 0 | | have_bdb | YES | | have_gemini | NO | | have_innodb | YES | | have_raid | YES | | have_ssl | NO | | init_file | | | interactive_timeout | 28800 | | join_buffer_size | 131072 | | key_buffer_size | 16776192 | | language | /my/monty/share/english/ | | large_files_support | ON | | log | OFF | | log_update | OFF | | log_bin | OFF | | log_slave_updates | OFF | | long_query_time | 10 | | low_priority_updates | OFF | | lower_case_table_names | 0 | | max_allowed_packet | 1048576 | | max_binlog_cache_size | 4294967295 | | max_connections | 100 | | max_connect_errors | 10 | | max_delayed_threads | 20 | | max_heap_table_size | 16777216 | | max_join_size | 4294967295 | | max_sort_length | 1024 | | max_tmp_tables | 32 | | max_write_lock_count | 4294967295 | | myisam_recover_options | DEFAULT | | myisam_sort_buffer_size | 8388608 | | net_buffer_length | 16384 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | open_files_limit | 0 | | pid_file | /my/monty/data/donna.pid | | port | 3306 | | protocol_version | 10 | | record_buffer | 131072 | | query_buffer_size | 0 | | safe_show_database | OFF | | server_id | 0 | | skip_locking | ON | | skip_networking | OFF | | skip_show_database | OFF | | slow_launch_time | 2 | | socket | /tmp/mysql.sock | | sort_buffer | 2097116 | | table_cache | 64 | | table_type | MYISAM | | thread_cache_size | 4 | | thread_stack | 65536 | | tmp_table_size | 1048576 | | tmpdir | /tmp/ | | version | 3.23.29a-gamma-debug | | wait_timeout | 28800 | +-------------------------+---------------------------+
Each option is described below. Values for buffer sizes, lengths, and stack
sizes are given in bytes. You can specify values with a suffix of
`K' or `M' to indicate kilobytes or megabytes. For
example, 16M indicates 16 megabytes. The case of suffix letters
does not matter; 16M and 16m are equivalent:
ansi_mode.ON if mysqld was started with
--ansi. See section 5.2
Running MySQL in ANSI Mode.
back_logback_log value indicates
how many requests can be stacked during this short time before
MySQL momentarily stops answering new requests. You need to
increase this only if you expect a large number of connections in a short
period of time. In other words, this value is the size of the listen queue for
incoming TCP/IP connections. Your operating system has its own limit on the
size of this queue. The manual page for the Unix listen(2) system
call should have more details. Check your OS documentation for the maximum
value for this variable. Attempting to set back_log higher than
your operating system limit will be ineffective.
basedir--basedir option.
bdb_cache_sizeBDB
tables. If you don't use BDB tables, you should start
mysqld with --skip-bdb to not waste memory for this
cache.
bdb_log_buffer_sizeBDB
tables. If you don't use BDB tables, you should set this to 0 or
start mysqld with --skip-bdb to not waste memory for
this cache.
bdb_home--bdb-home option.
bdb_max_lockbdb: Lock
table is out of available locks or Got error 12 from ...
when you have do long transactions or when mysqld has to examine
a lot of rows to calculate the query.
bdb_logdir--bdb-logdir option.
bdb_shared_dataON if you are using --bdb-shared-data.
bdb_tmpdir--bdb-tmpdir option.
binlog_cache_size. The size of the cache to hold the
SQLBEGIN/COMMIT/ROLLBACK Syntax.
character_setcharacter_setsconcurrent_insertsON (the default), MySQL will allow you to
use INSERT on MyISAM tables at the same time as you
run SELECT queries on them. You can turn this option off by
starting mysqld with --safe or --skip-new.
connect_timeoutmysqld server is waiting for a
connect packet before responding with Bad handshake.
datadir--datadir option.
delay_key_writedelay_key_write option CREATE TABLE. This means that
the key buffer for tables with this option will not get flushed on every index
update, but only when a table is closed. This will speed up writes on keys a
lot, but you should add automatic checking of all tables with myisamchk
--fast --force if you use this. Note that if you start
mysqld with the --delay-key-write-for-all-tables
option this means that all tables will be treated as if they were created with
the delay_key_write option. You can clear this flag by starting
mysqld with --skip-new or --safe-mode.
delayed_insert_limitdelayed_insert_limit rows, the INSERT
DELAYED handler will check if there are any SELECT
statements pending. If so, it allows these to execute before continuing.
delayed_insert_timeoutINSERT DELAYED thread should wait for
INSERT statements before terminating.
delayed_queue_sizeINSERT
DELAYED. If the queue becomes full, any client that does INSERT
DELAYED will wait until there is room in the queue again.
flushON if you have started MySQL with
the --flush option.
flush_timeflush_time
seconds all tables will be closed (to free up resources and sync things to
disk). We only recommend this option on Win95, Win98, or on systems where you
have very little resources.
have_bdbYES if mysqld supports Berkeley DB tables.
DISABLED if --skip-bdb is used.
have_geminiYES if mysqld supports Gemini tables.
DISABLED if --skip-gemini is used.
have_innodbYES if mysqld supports Innodb tables.
DISABLED if --skip-innodb is used.
have_raidYES if mysqld supports the RAID
option.
have_sslYES if mysqld supports SSL (encryption) on the
client/server protocol.
init_file--init-file option
when you start the server. This is a file of SQL statements you want the
server to execute when it starts.
interactive_timeoutCLIENT_INTERACTIVE option to
mysql_real_connect(). See also wait_timeout.
join_buffer_sizekey_buffer_sizekey_buffer_size is the size of the buffer used for index blocks.
Increase this to get better index handling (for all reads and multiple writes)
to as much as you can afford; 64M on a 256M machine that mainly runs
MySQL is quite common. If you, however, make this too big
(more than 50% of your total memory?) your system may start to page and become
REALLY slow. Remember that because MySQL does not cache data
read, that you will have to leave some room for the OS filesystem cache. You
can check the performance of the key buffer by doing show status
and examine the variables Key_read_requests,
Key_reads, Key_write_requests, and
Key_writes. The Key_reads/Key_read_request ratio
should normally be < 0.01. The Key_write/Key_write_requests is
usually near 1 if you are using mostly updates/deletes but may be much smaller
if you tend to do updates that affect many at the same time or if you are
using delay_key_write. See section 7.28
SHOW Syntax. To get even more speed when writing many rows at
the same time, use LOCK TABLES. See section 7.32
LOCK TABLES/UNLOCK TABLES Syntax.
languagelarge_file_supportmysqld was compiled with options for big file support.
locked_in_memorymysqld was locked in memory with --memlock
loglog_updatelog_binlog_slave_updateslong_query_timeSlow_queries counter will be incremented. If you are using
--log-slow-queries, the query will be logged to the slow query
logfile. See section 22.5
The Slow Query Log.
lower_case_table_namesmax_allowed_packetnet_buffer_length bytes, but can grow up to
max_allowed_packet bytes when needed. This value by default is
small, to catch big (possibly wrong) packets. You must increase this value if
you are using big BLOB columns. It should be as big as the
biggest BLOB you want to use. The current protocol limits
max_allowed_packet to 16M.
max_binlog_cache_sizemax_binlog_sizemax_connectionsmysqld requires.
See below for comments on file descriptor limits. See section 20.4.5
Too many connections Error.
max_connect_errorsFLUSH HOSTS.
max_delayed_threadsINSERT
DELAYED statements. If you try to insert data into a new table after
all INSERT DELAYED threads are in use, the row will be inserted
as if the DELAYED attribute wasn't specified.
max_heap_table_sizemax_join_sizemax_join_size
records return an error. Set this value if your users tend to perform joins
that lack a WHERE clause, that take a long time, and that return
millions of rows.
max_sort_lengthBLOB or
TEXT values (only the first max_sort_length bytes of
each value are used; the rest are ignored).
max_user_connectionsmax_tmp_tablesmax_write_lock_countmyisam_recover_options--myisam-recover option.
myisam_sort_buffer_sizeREPAIR or when creating indexes with CREATE INDEX or
ALTER TABLE.
myisam_max_extra_sort_file_size.myisam_max_sort_file_sizeREPAIR,
ALTER TABLE or LOAD DATA INFILE. If the file size
would be bigger than this, the index will be created through the key cache
(which is slower). NOTE that this parameter is given in
megabytes!
net_buffer_lengthmax_allowed_packet bytes.)
net_read_timeoutwrite_timeout.
net_retry_countFreeBSD as
internal interrupts are sent to all threads.
net_write_timeoutopen_files_limitmysqld will use this value to reserve
file descriptors to use with setrlimit(). If this value is 0 then
mysqld will reserve max_connections*5 or
max_connections + table_cache*2 (whichever is larger) number of
files. You should try increasing this if mysqld gives you the
error 'Too many open files'.
pid_file--pid-file option.
port--port option.
protocol_versionrecord_bufferquery_buffer_sizesafe_show_databasesskip_show_databases.
server_id--server-id option.
skip_lockingmysqld uses external locking.
skip_networkingskip_show_databasesSHOW DATABASES if they don't
have the PROCESS_PRIV privilege. This can improve security if
you're concerned about people being able to see what databases other users
have. See also safe_show_databases.
slow_launch_timeSlow_launch_threads counter will be incremented.
socketsort_bufferORDER BY or GROUP BY
operations. See section 20.7
Where MySQL Stores Temporary Files.
table_cachemysqld requires.
MySQL needs two file descriptors for each unique open table.
See below for comments on file descriptor limits. You can check if you need to
increase the table cache by checking the Opened_tables variable.
See section 7.28
SHOW Syntax. If this variable is big and you don't do
FLUSH TABLES a lot (which just forces all tables to be closed and
reopenend), then you should increase the value of this variable. Make sure
that your operating system can handle the number of open file descriptors
implied by the table_cache setting. If table_cache
is set too high, MySQL may run out of file descriptors and
refuse connections, fail to perform queries, and be very unreliable. For
information about how the table cache works, see section 12.2.4
How MySQL Opens and Closes Tables.
table_typethread_cache_sizethread_cache_size threads from before. All new threads are
first taken from the cache, and only when the cache is empty is a new thread
created. This variable can be increased to improve performance if you have a
lot of new connections. (Normally this doesn't give a notable performance
improvement if you have a good thread implementation.) By examing the
difference between the Connections and
Threads_created you can see how efficient the current thread
cache is for you.
thread_concurrencymysqld will call
thr_setconcurrency() with this value.
thr_setconcurrency() permits the application to give the threads
system a hint for the desired number of threads that should be run at the same
time.
thread_stackcrash-me test are dependent on this value. The default is large
enough for normal operation. See section 12.7
Using Your Own Benchmarks.
timezonetmp_table_sizeMyISAM table.
Increase the value of tmp_table_size if you do many advanced
GROUP BY queries and you have lots of memory.
tmpdirversionwait_timeoutinteractive_timeout. The manual section that describes tuning MySQL contains some information of how to tune the above variables. See section 12.2.3 Tuning Server Parameters.
SHOW LOGSSHOW LOGS shows you status information about existing log files.
It currently only displays information about Berkeley DB log files.
File shows the full path to the log file
Type shows the type of the log file (BDB for
Berkeley DB log files)
Status shows the status of the log file (FREE if
the file can be removed, or IN USE if the file is needed by the
transaction subsystem) SHOW PROCESSLISTSHOW PROCESSLIST shows you which threads are running. You can
also get this information using the mysqladmin processlist command.
If you have the process privilege, you can see all threads.
Otherwise, you can see only your own threads. See section 7.27
KILL Syntax. If you don't use the FULL option,
then only the first 100 characters of each query will be shown.
This command is very useful if you get the 'too many connections' error
message and want to find out what's going on. MySQL reserves
one extra connection for a client with the Process_priv privilege
to ensure that you should always be able to login and check the system (assuming
you are not giving this privilege to all your users).
SHOW GRANTSSHOW GRANTS FOR user lists the grant commands that must be
issued to duplicate the grants for a user.
mysql> SHOW GRANTS FOR root@localhost; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+
SHOW CREATE TABLEShows a CREATE TABLE statement that will create the given table:
mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE t (
id int(11) default NULL auto_increment,
s char(60) default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM
SHOW CREATE TABLE will quote table and column names according to
SQL_QUOTE_SHOW_CREATE option. section 7.33
SET Syntax.
EXPLAIN Syntax (Get Information About a
SELECT)EXPLAIN tbl_name or EXPLAIN SELECT select_options
EXPLAIN tbl_name is a synonym for DESCRIBE tbl_name
or SHOW COLUMNS FROM tbl_name.
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
single-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 backtracks through the table list 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
type
possible_keys
possible_keys column indicates which indexes
MySQL could use to find the rows in this table. Note that
this column is totally independent of the order of the tables. That means that
some of the keys in possible_keys may not be usable in practice with the
generated table order. 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. See section 7.8
ALTER TABLE Syntax. To see what indexes a table has, use
SHOW INDEX FROM tbl_name.
key
key column indicates the key that MySQL
actually decided to use. The key is NULL if no index was chosen.
If MySQL chooses the wrong index, you can probably force
MySQL to use another index by using myisamchk
--analyze, See section 15.1.1
myisamchk Invocation Syntax, or by using USE
INDEX/IGNORE INDEX. See section 7.20
JOIN Syntax.
key_len
key_len column indicates the length of the key that
MySQL decided to use. The length is NULL if the
key is NULL. Note that this tells us how many parts
of a multi-part key MySQL will actually use.
ref
ref column shows which columns or constants are used with
the key to select rows from the table.
rows
rows column indicates the number of rows
MySQL believes it must examine to execute the query.
Extra
Distinct
Not exists
LEFT JOIN
optimization on the query and will not examine more rows in this table for a
row combination after it finds one row that matches the LEFT
JOIN criteria.
range checked for each record (index map: #)Using filesort
join type and storing the sort key +
pointer to the row for all rows that match the WHERE. Then the
keys are sorted. Finally the rows are retrieved in sorted order.
Using index
Using temporary
ORDER BY on a different column set than you did a GROUP
BY on.
Where used
WHERE clause will be used to restrict which rows will be
matched against the next table or sent to the client. If you don't have this
information and the table is of type ALL or index,
you may have something wrong in your query (if you don't intend to
fetch/examine all rows from the table). Using
filesort and Using temporary. The different join types are listed below, ordered from best to worst type:
system
const join type.
const
const
tables are very fast as they are read only once!
eq_ref
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
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
key column indicates which index is used.
The key_len contains the longest key part that was used. The
ref column will be NULL for this type.
index
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
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 12.2.3
Tuning Server Parameters.
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:
| Table | Column | Column type |
tt |
ActualPC |
CHAR(10) |
tt |
AssignedPC |
CHAR(10) |
tt |
ClientID |
CHAR(10) |
et |
EMPLOYID |
CHAR(15) |
do |
CUSTNMBR |
CHAR(15) |
| Table | Index |
tt |
ActualPC |
tt |
AssignedPC |
tt |
ClientID |
et |
EMPLOYID (primary key) |
do |
CUSTNMBR (primary key) |
tt.ActualPC values aren't evenly distributed. 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)
Because type is ALL for each table, this output
indicates that MySQL is doing a full join for all tables! 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. In this context,
VARCHAR and CHAR are the same unless they are declared
as different lengths. Because 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 MODIFY 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 for
the tt.AssignedPC = et_1.EMPLOYID and tt.ClientID =
do.CUSTNMBR comparisons:
mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
MODIFY 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> myisamchk --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
Note that the rows column in the output from
EXPLAIN is an educated guess from the MySQL join
optimizer. To optimize a query, you should check if the numbers are even close
to the truth. If not, you may get better performance by using
STRAIGHT_JOIN in your SELECT statement and trying to
list the tables in a different order in the FROM clause.
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 `_' wild-card characters.
If the column types are different than you expect them to be based on a
CREATE TABLE statement, note that MySQL sometimes
changes column types. See section 7.7.1
Silent Column Specification Changes.
This statement is provided for Oracle compatibility.
The SHOW statement provides similar information. See section 7.28
SHOW Syntax.
BEGIN/COMMIT/ROLLBACK SyntaxBy default, MySQL runs in autocommit mode. This
means that as soon as you execute an update, MySQL will store
the update on disk.
If you are using transactions safe tables (like BDB,
INNODB or GEMINI), you can put MySQL
into non-autocommit mode with the following command:
SET AUTOCOMMIT=0
After this you must use COMMIT to store your changes to disk or
ROLLBACK if you want to ignore the changes you have made since the
beginning of your transaction.
If you want to switch from AUTOCOMMIT mode for one series of
statements, you can use the BEGIN or BEGIN WORK
statement:
BEGIN; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summmary=@A WHERE type=1; COMMIT;
Note that if you are using non-transaction-safe tables, the changes will be
stored at once, independent of the status of the autocommit mode.
If you do a ROLLBACK when you have updated a non-transactional
table you will get an error (ER_WARNING_NOT_COMPLETE_ROLLBACK) as a
warning. All transactional safe tables will be restored but any
non-transactional table will not change.
If you are using BEGIN or SET AUTOCOMMIT=0, you
should use the MySQL binary log for backups instead of the old
update log; The transaction is stored in the binary log in one chunk, during
COMMIT, the to ensure and ROLLBACK:ed transactions are
not stored. See section 22.4
The Binary Log.
The following commands automatically ends an transaction (as if you had done
a COMMIT before executing the command):
ALTER TABLE |
BEGIN |
CREATE INDEX |
DROP DATABASE |
DROP TABLE |
RENAME TABLE |
TRUNCATE |
You can change the isolation level for transactions with SET
TRANSACTION ISOLATION LEVEL .... See section 7.34
SET TRANSACTION Syntax.
LOCK TABLES/UNLOCK TABLES SyntaxLOCK TABLES tbl_name [AS alias] {READ | [READ LOCAL] | [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.
The difference between READ LOCAL and READ is that
READ LOCAL allows non-conflicting INSERT statements to
execute while the lock is held. This can't however be used if you are going to
manipulate the database files outside MySQL while you hold the
lock.
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 a time when no threads will have a
READ lock.
When you use LOCK TABLES, you must lock all tables that you are
going to use and you must use the same alias that you are going to use in your
queries! If you are using a table multiple times in a query (with aliases), you
must get a lock for each alias! This policy ensures that table locking is
deadlock free and makes the locking code smaller, simpler and much faster.
Note that you should NOT lock any tables that you are using
with INSERT DELAYED. This is because that in this case the
INSERT is done by a separate thread.
Normally, you don't have to lock tables, as all single UPDATE
statements are atomic; no other thread can interfere with any other currently
executing SQL statement. There are a few cases when you would like to lock
tables anyway:
READ-locked table and no other
thread can read a WRITE-locked table.
LOCK TABLES if you want to
ensure that no other thread comes between a SELECT and an
UPDATE. The example shown below requires LOCK TABLES
in order to execute safely: mysql> LOCK TABLES trans READ, customer WRITE;
mysql> select sum(value) from trans where customer_id= some_id;
mysql> update customer set total_value=sum_from_previous_statement
where customer_id=some_id;
mysql> UNLOCK TABLES;
Without LOCK TABLES, there is a chance that another thread
might insert a new row in the trans table between execution of
the SELECT and UPDATE statements. 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.4.12
Miscellaneous Functions.
See section 12.2.8 How MySQL Locks Tables, for more information on locking policy.
You can also lock all tables in all databases with read locks with the
FLUSH TABLES WITH READ LOCK command. See section 7.26
FLUSH Syntax. This is very convenient way to get backups if you
have a file system, like Veritas, that can take snapshots in time.
NOTE: LOCK TABLES is not transaction safe and
will automatically commit any active transactions before attempting to lock the
tables.
SET SyntaxSET [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.
CHARACTER SET character_set_name | DEFAULT
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')
PASSWORD FOR user = PASSWORD('some password')
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");
or
mysql> UPDATE mysql.user SET password=PASSWORD("newpass") where user="bob' and host="%.loc.gov";
SQL_AUTO_IS_NULL = 0 | 1
1 (default) then one can find the last inserted row
for a table with an auto_increment row with the following construct:
WHERE auto_increment_column IS NULL. This is used by some ODBC
programs like Access.
AUTOCOMMIT= 0 | 1
1 all changes to a table will be done at once. To
start a multi-command transaction, you have to use the BEGIN
statement. See section 7.31
BEGIN/COMMIT/ROLLBACK Syntax. If set to 0 you
have to use COMMIT / ROLLBACK to accept/revoke that
transaction. See section 7.31
BEGIN/COMMIT/ROLLBACK Syntax. Note that when you change from
not AUTOCOMMIT mode to AUTOCOMMIT mode,
MySQL will do an automatic COMMIT on any open
transactions.
SQL_BIG_TABLES = 0 | 1
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 (that is, use in-memory
temporary tables).
SQL_BIG_SELECTS = 0 | 1
0, 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 1 (which will allow all
SELECT statements).
SQL_BUFFER_RESULT = 0 | 1
SQL_BUFFER_RESULT will force the result from
SELECT's to be put into a temporary table. This will help
MySQL free the table locks early and will help in cases where
it takes a long time to send the result set to the client.
SQL_LOW_PRIORITY_UPDATES = 0 | 1
1, all INSERT, UPDATE,
DELETE, and and LOCK TABLE WRITE statements wait
until there is no pending SELECT or LOCK TABLE READ
on the affected table.
SQL_MAX_JOIN_SIZE = value | DEFAULT
SELECTs that will probably need to examine more
than value row combinations. By setting this value, you can catch
SELECTs where keys are not used properly and that would probably
take a long time. Setting this to a value other than DEFAULT will
reset the SQL_BIG_SELECTS flag. If you set the
SQL_BIG_SELECTS flag again, the SQL_MAX_JOIN_SIZE
variable will be ignored. You can set a default value for this variable by
starting mysqld with -O max_join_size=#.
SQL_SAFE_MODE = 0 | 1
1, MySQL will abort if an
UPDATE or DELETE is attempted that doesn't use a key
or LIMIT in the WHERE clause. This makes it possible
to catch wrong updates when creating SQL commands by hand.
SQL_SELECT_LIMIT = value | DEFAULT
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
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_LOG_UPDATE = 0 | 1
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!
SQL_QUOTE_SHOW_CREATE = 0 | 1
1, SHOW CREATE TABLE will quote table
and column names. This is on by default, for replication of
tables with fancy column names to work. section 7.28.8
SHOW CREATE TABLE.
TIMESTAMP = timestamp_value | DEFAULT
LAST_INSERT_ID = #
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 = #
INSERT or
ALTER TABLE command when inserting an AUTO_INCREMENT
value. This is mainly used with the update log. SET TRANSACTION SyntaxSET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE]
Sets the transaction isolation level for the global, whole session or the next transaction.
The default behavior is to set the isolation level for the next (not started) transaction.
If you set the GLOBAL privilege it will affect all new created
threads. You will need the PROCESS privilege to do do this.
Setting the SESSION privilege will affect the following and all
future transactions.
You can set the default isolation level for mysqld with
--transaction-isolation=.... See section 4.16.4
mysqld Command-line Options.
GRANT and REVOKE SyntaxGRANT 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 Version 3.22.11
or later. For earlier MySQL versions, the GRANT
statement does nothing.
The GRANT and REVOKE commands allow system
administrators to grant and revoke rights to MySQL users at
four privilege levels:
mysql.user table.
mysql.db and mysql.host
tables.
mysql.tables_priv table.
mysql.columns_priv table. For examples of how GRANT works, see section 6.14
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 global privileges by using ON *.* syntax. 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. (WARNING: If you specify ON
* and you don't have a current database, you will affect the
global privileges!)
In order to accommodate granting rights to users from arbitrary hosts,
MySQL supports specifying the user_name value in
the form user@host. If you want to specify a user
string containing special characters (such as `-'), or a
host string containing special characters or wild-card characters
(such as `%'), you can quote the user or host name (for example,
'test-user'@'test-hostname').
You can specify wild cards 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 to connect to the
MySQL server (which is the default), you should also add all
local users as user@localhost because otherwise the anonymous user
entry for the local host in the mysql.user table will be used when
the user tries to log into the MySQL server from the local
machine! Anonymous users are defined by inserting entries with
User='' into the mysql.user table. You can verify if
this applies to you by executing this query:
mysql> 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 privileges 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 privileges for a column can be calculated as follows:
global privileges OR (database privileges AND host privileges) OR table privileges OR column privileges
In most cases, you grant rights to a user at only one of the privilege levels, so life isn't normally as complicated as above. The details of the privilege-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 other words, GRANT
may create user table entries, but REVOKE will not
remove them; you must do that explicitly using DELETE.
In MySQL Version 3.22.12 or later, 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.
WARNING: If you create a new user but do not specify an
IDENTIFIED BY clause, the user has no password. This is insecure.
Passwords can also be set with the SET PASSWORD command. See
section 7.33
SET 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 (for example, 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 or column privileges for even one user, the server examines table and column privileges for all users and this will slow down MySQL 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.12
When Privilege Changes Take Effect.
The biggest differences between the ANSI SQL and
MySQL versions of GRANT are:
TRIGGER,
EXECUTE or UNDER privileges.
INSERT grant on
only part of the columns in a table, you can execute INSERT
statements on the table; The columns for which you don't have the
INSERT privilege will set to their default values. ANSI SQL
requires you to have the INSERT privilege on all columns.
REVOKE commands or
by manipulating the MySQL grant tables. CREATE INDEX SyntaxCREATE [UNIQUE|FULLTEXT] INDEX index_name ON tbl_name (col_name[(length)],... )
The CREATE INDEX statement doesn't do anything in
MySQL prior to Version 3.22. In Version 3.22 or later,
CREATE INDEX is mapped to an ALTER TABLE statement to
create indexes. See section 7.8
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.7
CREATE TABLE Syntax. CREATE INDEX allows you to
add indexes to existing tables.
A column list of the form (col1,col2,...) creates a
multiple-column index. Index values are formed by concatenating the values of
the given columns.
For CHAR and VARCHAR columns, indexes can be
created that use only part of a column, using col_name(length)
syntax. (On BLOB and TEXT columns the length is
required). The statement shown below creates an index using the first 10
characters of the name column:
mysql> CREATE INDEX part_of_name ON customer (name(10));
Because most names usually differ in the first 10 characters, this index
should not be much slower than an index created from the entire
name column. Also, using partial columns for indexes can make the
index file much smaller, which could save a lot of disk space and might also
speed up INSERT operations!
Note that you can only add an index on a column that can have
NULL values or on a BLOB/TEXT column if
you are using MySQL Version 3.23.2 or newer and are using the
MyISAM table type.
For more information about how MySQL uses indexes, see section 12.4 How MySQL Uses Indexes.
FULLTEXT indexes can index only VARCHAR and
TEXT columns, and only in MyISAM tables.
FULLTEXT indexes are available in MySQL Version
3.23.23 and later. section 25.2
MySQL Full-text Search.
DROP INDEX SyntaxDROP INDEX index_name ON tbl_name
DROP INDEX drops the index named index_name from
the table tbl_name. DROP INDEX doesn't do anything in
MySQL prior to Version 3.22. In Version 3.22 or later,
DROP INDEX is mapped to an ALTER TABLE statement to
drop the index. See section 7.8
ALTER TABLE Syntax.
The MySQL server supports the # to end of line,
-- to end of line and /* in-line or multiple-line */
comment styles:
mysql> select 1+1; # This comment continues to the end of line mysql> select 1+1; -- This comment continues to the end of line mysql> select 1 /* this is an in-line comment */ + 1; mysql> select 1+ /* this is a multiple-line comment */ 1;
Note that the -- comment style requires you to have at least one
space after the --!
Although the server understands the comment syntax just described, there are
some limitations on the way that the mysql client parses /*
... */ comments:
mysql interactively, you
can tell that it has gotten confused like this because the prompt changes from
mysql> to '> or ">.
These limitations apply both when you run mysql interactively
and when you put commands in a file and tell mysql to read its
input from that file with mysql < some-file.
MySQL doesn't support the `--' ANSI SQL comment style. See section 5.4.7 `--' as the Start of a Comment.
CREATE FUNCTION/DROP FUNCTION SyntaxCREATE [AGGREGATE] 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().
AGGREGATE is a new option for MySQL Version
3.23. An AGGREGATE function works exactly like a native
MySQL GROUP function like SUM or
COUNT().
CREATE FUNCTION saves the function's name, type, and shared
library name in the mysql.func system table. 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 instructions on writing user-definable functions, see section 16
Adding New Functions to MySQL. For the UDF mechanism to work, functions must
be written in C or C++, your operating system must support dynamic loading and
you must have compiled mysqld dynamically (not statically).
A common problem stems from trying to create a table with column names that
use the names of datatypes or functions built into MySQL, such
as TIMESTAMP or GROUP. You're allowed to do it (for
example, ABS is 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 |
aggregate |
all |
alter |
after |
and |
as |
asc |
avg |
avg_row_length |
auto_increment |
between |
bigint |
bit |
binary |
blob |
bool |
both |
by |
cascade |
case |
char |
character |
change |
check |
checksum |
column |
columns |
comment |
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 |
delayed |
delay_key_write |
delete |
desc |
describe |
distinct |
distinctrow |
double |
drop |
end |
else |
escape |
escaped |
enclosed |
enum |
explain |
exists |
fields |
file |
first |
float |
float4 |
float8 |
flush |
foreign |
from |
for |
full |
function |
global |
grant |
grants |
group |
having |
heap |
high_priority |
hour |
hour_minute |
hour_second |
hosts |
identified |
ignore |
in |
index |
infile |
inner |
insert |
insert_id |
int |
integer |
interval |
int1 |
int2 |
int3 |
int4 |
int8 |
into |
if |
is |
isam |
join |
key |
keys |
kill |
last_insert_id |
leading |
left |
length |
like |
lines |
limit |
load |
local |
lock |
logs |
long |
longblob |
longtext |
low_priority |
max |
max_rows |
match |
mediumblob |
mediumtext |
mediumint |
middleint |
min_rows |
minute |
minute_second |
modify |
month |
monthname |
myisam |
natural |
numeric |
no |
not |
null |
on |
optimize |
option |
optionally |
or |
order |
outer |
outfile |
pack_keys |
partial |
password |
precision |
primary |
procedure |
process |
processlist |
privileges |
read |
real |
references |
reload |
regexp |
rename |
replace |
restrict |
returns |
revoke |
rlike |
row |
rows |
second |
select |
set |
show |
shutdown |
smallint |
soname |
sql_big_tables |
sql_big_selects |
sql_low_priority_updates |
sql_log_off |
sql_log_update |
sql_select_limit |
sql_small_result |
sql_big_result |
sql_warnings |
straight_join |
starting |
status |
string |
table |
tables |
temporary |
terminated |
text |
then |
time |
timestamp |
tinyblob |
tinytext |
tinyint |
trailing |
to |
type |
use |
using |
unique |
unlock |
unsigned |
update |
usage |
values |
varchar |
variables |
varying |
varbinary |
with |
write |
when |
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.
ACTION
BIT
DATE
ENUM
NO
TEXT
TIME
TIMESTAMP
Go to the first, previous, next, last section, table of contents.