A string is a sequence of characters, surrounded by either single quote (`'') or double quote (`"') characters. Examples:
'a string' "another string"
Within a string, certain sequences have special meaning. Each of these sequences begins with a backslash (`\'), known as the escape character. MySQL recognizes the following escape sequences:
\0
NUL) character.
\n
\t
\r
\b
\'
\"
\\
\%
\_
There are several ways to include quotes within a string:
The SELECT statements shown below demonstrate how quoting and
escaping work:
mysql> SELECT 'hello', '"hello"', '""hello""', 'hel"lo', '\'hello'; +-------+---------+-----------+--------+--------+ | hello | "hello" | ""hello"" | hel'lo | 'hello | +-------+---------+-----------+--------+--------+ | hello | "hello" | ""hello"" | hel'lo | 'hello | +-------+---------+-----------+--------+--------+ mysql> SELECT "hello", "'hello'", ""hello"", "hel""lo", "\"hello"; +-------+---------+-----------+--------+--------+ | hello | 'hello' | "hello" | hel"lo | "hello | +-------+---------+-----------+--------+--------+ | hello | 'hello' | "hello" | hel"lo | "hello | +-------+---------+-----------+--------+--------+ mysql> SELECT "This\nIs\nFour\nlines"; +--------------------+ | This Is Four lines | +--------------------+ | This Is Four lines | +--------------------+
If you want to insert binary data into a BLOB column, the following
characters must be represented by escape sequences:
NUL
\
'
"
If you write C code, you can use the C API function
mysql_escape_string() to escape characters for the INSERT
clause. See section 18.3 C API function overview. In Perl, you can use the
quote method of the DBI package to convert special
characters to the proper escape sequences. See section 18.5.1.1 The DBI interface.
You should use an escape function on every possible string that may contain any of the special characters listed above!
Integers are just a sequence of digits. Floats use `.' as a decimal separator.
Examples of valid numbers:
1221 294.42 -32032.6809e+10
NULL values
When using the text file export formats (SELECT ... INTO OUTFILE),
NULL may be represented by \N. See section 7.15 LOAD DATA INFILE syntax.
Note that NULL means "no data" and is different from values such
as 0 for numeric types and the empty string for string types.
See section 16.12 Problems with NULL values.
Database, table, index, column and alias names all follow the same rules in MySQL:
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 that is used in the query
contains a column named col_name
|
tbl_name.col_name | Column col_name from table
tbl_name of the current database
|
db_name.tbl_name.col_name | Column col_name from table
tbl_name of the database db_name. This form is not available in
versions of MySQL prior to 3.22.
|
You need not specify a tbl_name or db_name.tbl_name prefix for
a column reference in a statement unless the reference would be ambiguous.
For example, suppose tables t1 and t2 each contain a column
c, and you retrieve c in a SELECT statement that uses
both t1 and t2. In this case, c is ambiguous because it
is not unique among the tables used in the statement, so you must indicate
which table you mean by writing t1.c or t2.c. Similarly, if
you are retrieving from a table t in database db1 and from a
table t in database db2, you must refer to columns in those
tables as db1.t.col_name and db2.t.col_name.
The syntax .tbl_name means the table tbl_name in the current
database. This syntax is accepted because some ODBC prefix table names
with a `.' character.
Database and table names are case sensitive in Unix and case insensitive in Win32, because directory and file names are case sensitive in Unix but not in Win32. (In MySQL, databases and tables correspond to directories and files within those directories, so the case sensitivity of the underlying operating system determines how MySQL behaves.)
Note: although database and file names are case insensitive for Win32, you should not refer to a given database or table using different cases within the same query.
Column names are case insensitive in all cases.
Aliases on tables are case sensitive and aliases on columns are case insensitive.
MySQL supports a number of column types, which may be grouped into three categories: numeric types, date and time types, and string (or character) types. This section first gives an overview of the types available, then summarizes the storage requirements for each column type and provides a more detailed description of the properties of the types in each category. The overview is intentionally brief. The more detailed descriptions should be consulted for additional information about particular column types, such as the allowable formats in which you can specify values.
The column types supported by MySQL are listed below. The following code letters are used in the descriptions:
M indicates the maximum display size.
D applies to floating-point types and indicates the number of digits
following the decimal point.
Square brackets (`[' and `]') indicate parts of type specifiers that are optional.
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. Note
that all arithmetic is done using signed BIGINT or DOUBLE
values, so you shouldn't use unsigned big integers larger than
9223372036854775807 (63 bits) except with bit functions!
Note that -, + and * will use BIGINT arithmetic when
both arguments are INTEGER values! This means that if you multiply two big integers
(or results from functions that return integers) you may get unexpected results
if the result is bigger than 9223372036854775807.
FLOAT(precision) [ZEROFILL]
precision can be
4 or 8. FLOAT(4) is a single-precision number and
FLOAT(8) is a double-precision number. These types are like the
FLOAT and DOUBLE types described immediately below.
FLOAT(4) and FLOAT(8) have the same ranges as the corresponding
FLOAT and DOUBLE types, but their display size and number of
decimals is undefined.
This syntax is provided for ODBC compatibility.
FLOAT[(M,D)] [ZEROFILL]
-3.402823466E+38 to
-1.175494351E-38, 0 and -1.175494351E-38 to
3.402823466E+38.
DOUBLE[(M,D)] [ZEROFILL]
-1.7976931348623157E+308 to
-2.2250738585072014E-308, 0 and
2.2250738585072014E-308 to 1.7976931348623157E+308.
DOUBLE PRECISION[(M,D)] [ZEROFILL]
REAL[(M,D)] [ZEROFILL]
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). If D is 0, values will have
no decimal point or fractional part. The maximum range of
DECIMAL values is the same as for DOUBLE, but the actual range
for a given DECIMAL column may be constrained by the choice of
M and D.
NUMERIC(M,D) [ZEROFILL]
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.
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.
TIMESTAMP[(M)]
'1970-01-01 00:00:00' to sometime in
the year 2106. MySQL displays TIMESTAMP values
in YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD or
YYMMDD format, depending on whether M is 14 (or
missing), 12, 8 or 6, but allows you to assign
values to TIMESTAMP columns using either strings or numbers. A
TIMESTAMP column is useful for recording the time of an
INSERT or UPDATE operation because it is automatically set
to the time of the last operation if you don't give it an value
yourself. You can also set it to the current time by giving it a
NULL value. See section 7.2.6 Date and time types.
TIME
'-838:59:59' to '838:59:59'.
MySQL displays TIME values in 'HH:MM:SS' format, but
allows you to assign values to TIME columns using either strings or
numbers.
YEAR
1901 to 2155, and 0000.
MySQL displays YEAR values in YYYY format, but allows
you to assign values to YEAR columns using either strings or numbers.
(YEAR is a new type for MySQL 3.22.)
CHAR(M) [BINARY]
M is 1 to 255 characters.
Trailing spaces are removed when the value is retrieved. CHAR values
are sorted and compared in case-insensitive fashion unless the BINARY
keyword is given.
VARCHAR(M) [BINARY]
M is 1 to 255 characters.
VARCHAR values are sorted and compared in case-insensitive fashion
unless the BINARY keyword is given.
TINYBLOB
TINYTEXT
BLOB or TEXT column with a maximum length of 255 (2^8 - 1)
characters.
BLOB
TEXT
BLOB or TEXT column with a maximum length of 65535 (2^16 - 1)
characters.
MEDIUMBLOB
MEDIUMTEXT
BLOB or TEXT column with a maximum length of 16777215
(2^24 - 1) characters.
LONGBLOB
LONGTEXT
BLOB or TEXT column with a maximum length of 4294967295
(2^32 - 1) characters.
ENUM('value1','value2',...)
'value1', 'value2',... (or NULL). An ENUM can have
a maxiumum of 65535 distinct values.
SET('value1','value2',...)
'value1', 'value2',...
A SET can have a maximum of 64 members.
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(4) | 4 bytes |
FLOAT(8) | 8 bytes |
FLOAT | 4 bytes |
DOUBLE | 8 bytes |
DOUBLE PRECISION | 8 bytes |
REAL | 8 bytes |
DECIMAL(M,D) | M bytes (D+2, if M < D)
|
NUMERIC(M,D) | M bytes (D+2, if M < D)
|
| Column type | Storage required |
DATETIME | 8 bytes |
DATE | 3 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 maxiumum possible length of
the type.
If a table includes any variable-length column types, the record format will
also be variable-length. Note that when a table is created, MySQL
may under certain conditions change a column from a variable-length type to a
fixed-length type, and vice-versa. See section 7.6 CREATE TABLE syntax.
The size of an ENUM object is determined by the number of different
enumeration values. 1 byte is used for enumerations with up to 255 possible
values. 2 bytes are used for enumerations with up to 65535 values.
The size of a SET object is determined by the number of different
set members. If the set size is N, the object occupies (N+7)/8
bytes, rounded up to 1, 2, 3, 4 or 8 bytes. A SET can have a maximum
of 64 members.
All integer types can have an optional attribute UNSIGNED. Unsigned
values can be used when you want to allow only positive numbers in a column
and you need a little bigger numeric range for the column.
All numeric types can have an optional attribute ZEROFILL. Values for
ZEROFILL columns are left-padded with zeroes up to the maximum display
length when they are displayed. For example, for a column declared as
INT(5) ZEROFILL, a value of 4 is retrieved as 00004.
When asked to store a value in a numeric column that is outside the column type's allowable range, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead.
For example, the range of an INT column is -2147483648 to
2147483647. If you try to insert -9999999999 into an
INT column, the value is clipped to the lower endpoint of the range,
and -2147483648 is stored instead. Similarly, if you try to insert
9999999999, 2147483647 is stored instead.
If the INT column is UNSIGNED, the size of the column's
range is the same but its endpoints shift up to 0 and 4294967295.
If you try to store -9999999999 and 9999999999,
the values stored in the column become 0 and 4294967296.
Conversions that occur due to clipping are reported as `warnings' for
ALTER TABLE, LOAD DATA INFILE, UPDATE and
multi-row INSERT statements.
The maximum display size (M) and number of decimals (D)
are used for formatting and calculation of maximum column width.
MySQL will store any value that fits a column's storage type even if
the value exceeds the display size. For example, an INT(4) column has
a display size of 4. Suppose you insert a value which has more than 4 digits
into the column, such as 12345. The display size is exceeded, but the
allowable range of the INT type is not, so MySQL stores the
actual value, 12345. When retrieving the value from the column,
MySQL returns the actual value stored in the column.
The DECIMAL type is considered a numeric type (as is its synonym,
NUMERIC), but such values are stored as strings. One character is
used for each digit of the value, the decimal point (if D > 0) and the
`-' sign (for negative numbers). If D is 0, DECIMAL and
NUMERIC values contain no decimal point or fractional part.
The maximum range of DECIMAL values is the same as for
DOUBLE, but the actual range for a given DECIMAL column
may be constrained by the choice of M and D. For
example, a type specification such as DECIMAL(4,2) indicates a
maximum length of four characters with two digits after the decimal
point. Due to the way the DECIMAL type is stored, this
specification results in an allowable range of -.99 to
9.99, much less than the range of a DOUBLE.
To avoid some rounding problems, MySQL always rounds everything that
it stores in any floating-point column according to the number of decimals.
Suppose you have a column type of FLOAT(8,2). The number of decimals
is 2, so a value such as 2.333 is rounded to two decimals and stored
as 2.33.
The date and time types are DATETIME, DATE, TIMESTAMP,
TIME and YEAR. Each of these has a range of legal values, as
well as a `zero' value that is used when you specify an illegal value.
Here are some general considerations to keep in mind when working with date and time types:
'98-09-04'), rather than in the
month-day-year or day-month-year orders commonly used elsewhere (e.g.,
'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 2.50.12 and above, because
ODBC can't handle such values.
DATETIME, DATE and TIMESTAMP types
The DATETIME, DATE and TIMESTAMP types are related.
This section describes how they are similar and how they differ.
The DATETIME type is used when you need values that contain both date
and time information. MySQL retrieves and displays DATETIME
values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is
'1000-01-01 00:00:00' to '9999-12-31 23:59:59'. ("Supported"
means that although earlier values might work, they are not guaranteed to.)
The DATE type is used when you need only a date value, without a
time part.
MySQL retrieves and displays DATE values in
'YYYY-MM-DD' format. The supported range is '1000-01-01' to
'9999-12-31'.
The TIMESTAMP column type provides a type that you can use to
automatically mark INSERT or UPDATE operations with the current
time. (`Current time' means `current date and time' in TIMESTAMP
contexts.) A TIMESTAMP column is updated automatically under either of
the following conditions:
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.
If you have multiple TIMESTAMP columns, only the
first one is updated automatically. However, you can set any
TIMESTAMP column to the current time by setting it
to NULL (or by setting it to NOW(), obviously).
TIMESTAMP values may range from the beginning of 1970 to
sometime in the year 2106, with a resolution of one second.
Values are displayed as numbers.
The format in which MySQL retrieves and displays TIMESTAMP
values depends on the display size, as illustrated by the table below. The
`full' TIMESTAMP format is 14 digits, but TIMESTAMP columns may
be created with shorter display sizes:
| Column type | Display format |
TIMESTAMP(14) | YYYYMMDDHHMMSS
|
TIMESTAMP(12) | YYMMDDHHMMSS
|
TIMESTAMP(10) | YYMMDDHHMM
|
TIMESTAMP(8) | YYYYMMDD
|
TIMESTAMP(6) | YYMMDD
|
TIMESTAMP(4) | YYMM
|
TIMESTAMP(2) | YY
|
All TIMESTAMP columns have the same storage size, regardless of
display size. The most common display sizes are 6, 8, 12, and 14. (You can
specify an arbitrary display size at table creation time, but values of 0 or
greater than 14 are coerced to 14. Odd-valued sizes in the range from 1 to
13 are coerced to the next higher even number.)
You can specify DATETIME, DATE and TIMESTAMP values using
any of a common set of formats:
'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD
HH:MM:SS' format. A `relaxed' syntax is allowed--any non-numeric
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 '971122459015' 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.
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 will be 99 and 03, but the day part is missing (zero),
so the value is not a legal date. Always specify year, month, and day, even
if your columns are TIMESTAMP(4) or TIMESTAMP(2) types. (Note
that TIMESTAMP columns store legal values with full precision, so
if you use ALTER TABLE to widen a narrow TIMESTAMP column,
information will be displayed that previously was "hidden".)
Year values specified as two digits are ambiguous, since the century is unknown. MySQL interprets 2-digit year values using the following rules:
00-69 are converted to 2000-2069.
70-99 are converted to 1970-1999.
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.
TIMESTAMP values are stored to full precision regardless of the
display size. However, the only function that operates directly on the
underlying stored value is UNIX_TIMESTAMP(). Other functions operate
on the formatted retrieved value. This means you cannot use functions
such as HOUR() or SECOND() unless the relevant part of the
TIMESTAMP value is included in the formatted value. For example, the
HH part of a TIMESTAMP column is not displayed unless the
display size is at least 10, so trying to use HOUR() on shorter
TIMESTAMP values produces a meaningless result.
Illegal DATETIME, DATE or TIMESTAMP values are converted
the `zero' value of the appropriate type ('0000-00-00 00:00:00',
'0000-00-00' or 00000000000000).
Be aware of certain pitfalls when specifying date values:
'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.
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 2036. For example,
'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.
TIME type
MySQL retrieves and displays TIME values in 'HH:MM:SS'
format (or 'HHH:MM:SS' format for large hours values). TIME
values may range from '-838:59:59' to '838:59:59'. The reason
the hours part may be so large is that the TIME type may be used not
only to represent a time of day (which must be less than 24 hours), but also
elapsed time or a time interval between two events (which may be much greater
than 24 hours, or even negative).
You can specify TIME values in a variety of formats:
'HH:MM:SS' format.
A `relaxed' syntax is allowed--any non-numeric character may be
used as the delimiter between time parts. For example, '10:11:12'
and '10.11.12' are equivalent.
'HHMMSS' format, provided that
it makes sense as a time. 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.
Example: 101112 is understood as '10:11:12'.
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'.
If you assign a `short' TIME value to a TIME column,
MySQL interprets the value as specifying seconds, or minutes and
seconds. For example, '12' and 12 are interpreted as
'00:00:12', whereas '11:12', '1112' and 1112 are
interpreted as '00:11:12'.
Values that lie outside the TIME range
but are otherwise legal are clipped to the appropriate
endpoint of the range. For example, '-850:00:00' and
'850:00:00' are converted to '-838:59:59' and
'838:59:59'.
Illegal TIME values are converted to '00:00:00'. Note that
since '00:00:00' is itself a legal TIME value, there is no way
to distinguish a value of '00:00:00' that was specified explicitly
from one that resulted from an illegal value.
YEAR type
The YEAR type is a 1-byte type used for representing years.
MySQL retrieves and displays YEAR values in YYYY
format. The range is 1901 to 2155.
You can specify YEAR values in a variety of formats:
'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, and are sorted as such.
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, and are sorted as such.
Note that the range for two-digit numbers is slightly different than the
range for two-digit strings, since 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.
CHAR and VARCHAR types
The CHAR and VARCHAR types are similar, but differ in the
way they are stored and retrieved.
The length of a CHAR column is fixed to the length that you declare it
when you create the table. You can declare it to be any length between 1 and
255; when values are stored, they are right-padded with spaces to the
specified length. When CHAR values are retrieved, trailing spaces are
removed.
Values in VARCHAR columns are variable-length strings. You can
declare a VARCHAR to be any length between 1 and 255 as well. This
length is the maximum length, but in contrast to CHAR, values are
stored using only as many characters as are needed. Values are not padded;
instead, trailing spaces are removed when values are stored. (This space
removal differs from the ANSI SQL specification.)
If you assign a value to a CHAR or VARCHAR column that
exceeds the column's maximum length, the value is truncated to fit.
To illustrate the differences between the two types of columns, the table
below shows the result of storing various string values into
CHAR(4) and VARCHAR(4) columns:
| Value | CHAR(4) | VARCHAR(4)
|
" | ' ' | "
|
'ab' | 'ab ' | 'ab'
|
'abcd' | 'abcd' | 'abcd'
|
'abcdef' | 'abcd' | 'abcd'
|
The values retrieved from the CHAR(4) and VARCHAR(4) columns
will be the same in each case, because trailing spaces are removed from
CHAR columns upon retrieval.
Values in CHAR and VARCHAR columns are sorted and compared in
case-insensitive fashion, unless the BINARY attribute was specified
when the table was created. The BINARY attribute means that column
values are sorted and compared in case-sensitive fashion according to the
ASCII order of the machine where the MySQL server is running.
The BINARY attribute is "sticky". This means that if a column marked
BINARY is used in an expression, the whole expression is compared as a
BINARY value.
MySQL may silently change the type of a CHAR or VARCHAR
column at table creation time.
See section 7.6 CREATE TABLE syntax.
BLOB and TEXT types
A BLOB is a binary large object that can hold a variable amount of
data. The four BLOB types TINYBLOB, BLOB,
MEDIUMBLOB and LONGBLOB differ only in the maximum
length of the values they can hold.
The four TEXT types TINYTEXT, TEXT, MEDIUMTEXT
and LONGTEXT correspond to the four BLOB types and have the
same maximum lengths and storage requirements. The only difference between
BLOB and TEXT types is that sorting and comparison is performed
in case-sensitive fashion for BLOB values and case-insensitive fashion
for TEXT values. In other words, a TEXT is a case-insensitive
BLOB.
BLOB and TEXT columns cannot be indexed, unlike all other
MySQL column types.
If you assign a value to a BLOB or TEXT column that exceeds
the column type's maximum length, the value is truncated to fit.
There is no trailing space truncation for BLOB and TEXT columns
as there is for VARCHAR columns.
In most respects, you can regard a TEXT column as a VARCHAR
column that can be as big as you like. Similarly, you can regard a
BLOB column as a VARCHAR BINARY column. The differences are
that you cannot index BLOB or TEXT columns, and there is no
trailing-space removal for BLOB and TEXT columns when values
are stored. BLOB and TEXT can not have DEFAULT values
and will also always be NULL columns.
MyODBC defines BLOB values as LONGVARBINARY and
TEXT values as LONGVARCHAR.
Because BLOB and TEXT values may be extremely long, you
may run up against some contraints when using them:
BLOB or TEXT values, only the first
max_sort_length bytes of the column are used. The default value
of max_sort_length is 1024; this value
can be changed by the -O option when starting the mysqld
daemon. You can group on an expression involving BLOB or
TEXT values:
mysql> SELECT id,substr(blob_col,1,100) GROUP BY 2;
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 10.1 Changing the size of MySQL buffers.
Note that each BLOB/TEXT column is represented internally by a
unique alloced object. This is in contrast to all other column types that
are alloced once when the table is opened.
ENUM type
An ENUM (enumeration) is a string object that can have only one value,
chosen from a list of allowed values, or NULL. For example, a column
specified as ENUM("one", "two", "three") can have any of these
values:
NULL "one" "two" "three"
An enumeration can have a maximum of 65535 elements.
When you assign a value to an ENUM column, the case of the value to be
stored does not matter; the stored value is converted to the case that was
used to specify the ENUM column when the table was created.
If you retrieve an ENUM in a numeric context, the column value's index
is returned. If you store a number into an ENUM, the value stored is
the enumeration member whose index is that number. Enumeration values are
indexed beginning with 1 (0 is reserved for incorrect enumeration values).
Sorting of ENUM values is done according to the order in which the
enumeration members were listed in the column specification. For example,
"a" sorts before "b" for ENUM("a", "b"),
but "a" sorts after "b" for ENUM("b", "a").
NULL values sort before other enumeration values.
If an ENUM is declared NOT NULL, the default value is the first
value, otherwise the default value is NULL.
SET type
A SET is a string object that can have zero or more values, each of
which must be chosen from a list of allowed values. SET column values
that are composed of multiple set members are specified with members
separated by commas (`,'). For example, a column specified as
SET("one", "two") NOT NULL can have any of these values:
"" "one" "two" "one,two"
A SET can have a maximum of 64 different members.
MySQL stores SET values numerically, with the low-order bit
of the stored value corresponding to the first set member. If you retrieve a
set value into a numeric context, the value retrieved has the bit (or bits)
set corresponding to the set member (or members) that make up the column
value. If a number is stored into a SET column, the bit (or bits)
that are set in the number determine the set member (or members) in the
column value. Sorting of SET values is done numerically. NULL
values sort before other set members.
Normally, you perform a SELECT on a SET column using
LIKE or FIND_IN_SET():
mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;
But the following will also work:
mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2'; # Exact match mysql> SELECT * FROM tbl_name WHERE set_col & 1; # Is in first group
Try to use the most precise type in all cases. For example, if an integer
column will be used for values in the range
between 1 and 99999, MEDIUMINT UNSIGNED is the best type.
Accurate representation of monetary values is a common problem. In
MySQL you should use the DECIMAL type. This is stored as
a string, so no loss of accuracy should occur. If accuracy is not
too important, the DOUBLE type may also be good enough.
For high precision, you can always convert to a fixed-point type
stored in a BIGINT. This allows you to do all calculations with
integers and convert results back to floating-point values only when necessary.
See section 10.14 What are the different row formats? Or, when should VARCHAR/CHAR be used?.
All MySQL column types can be indexed except BLOB
and TEXT types. Use of indexes on the relevant columns is the best
way to improve the performance of SELECT operations.
A table may have up to 16 indexes. The maximum index length is 256 bytes, although this may be changed when compiling MySQL.
For CHAR and VARCHAR columns, you can index a prefix of a
column. This is much faster and requires less disk space than indexing the
whole column.
The syntax to use in the CREATE TABLE statement to index a column
prefix looks like this:
KEY index_name (col_name(length))
The example below creates an index for the first 10 characters of the
name column:
mysql> CREATE TABLE test (
name CHAR(200) NOT NULL,
KEY index_name (name(10)));
MySQL can create indexes from multiple columns.
A multiple-column index can be considered a sorted array where the values of the indexed columns are concatenated.
MySQL uses multiple-column indexes in such a way that queries are
fast when you specify a known quantity for the first column of the index in a
WHERE clause, even if you don't specify values for the other columns.
An index may consist up up to 15 columns (or column prefixes, for CHAR
and VARCHAR columns).
Suppose you have a table that has the following specification:
mysql> CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name));
Then the index name is an index over last_name and
first_name. The index will be used for queries that specify
values in a known range for last_name, or for both last_name
and first_name.
Therefore, the name index will be used in the following queries:
mysql> SELECT * FROM test WHERE last_name="Widenius";
mysql> SELECT * FROM test WHERE last_name="Widenius"
AND first_name="Michael";
mysql> SELECT * FROM test WHERE last_name="Widenius"
AND (first_name="Michael" OR first_name="Monty");
mysql> SELECT * FROM test WHERE last_name="Widenius"
AND first_name >="M" AND first_name < "N";
However, the name index will NOT be used in the following queries:
mysql> SELECT * FROM test WHERE first_name="Michael"; mysql> SELECT * FROM test WHERE last_name="Widenius" or first_name="Michael";
For more information on the manner in which MySQL uses indexes to improve query performance, see section 10.4 How MySQL uses indexes.
To make it easier to use code written for SQL implementations from other vendors, MySQL supports the column type mappings shown in the table below. These mappings make it easier to move table definitions from other database engines to MySQL:
| Other vendor type | MySQL type |
BINARY(NUM) | CHAR(NUM) BINARY
|
CHAR VARYING(NUM) | VARCHAR(NUM)
|
FLOAT4 | FLOAT
|
FLOAT8 | DOUBLE
|
INT1 | TINYINT
|
INT2 | SMALLINT
|
INT3 | MEDIUMINT
|
INT4 | INT
|
INT8 | BIGINT
|
LONG VARBINARY | MEDIUMBLOB
|
LONG VARCHAR | MEDIUMTEXT
|
MIDDLEINT | MEDIUMINT
|
VARBINARY(NUM) | VARCHAR(NUM) BINARY
|
Column type mapping occurs at table creation time, so
if you create a table with types used by other vendors and then issue a
DESCRIBE tbl_name statement, MySQL reports the table
structure using the equivalent MySQL types.
SELECT and WHERE clauses
A select_expression or where_definition can consist of any
expression using the functions described below.
An expression that contains NULL always produces a NULL value
unless otherwise indicated in the documentation for the operators and
functions involved in the expression.
Note: there must be no whitespace between a function name and the parenthesis following it. This helps the MySQL parser distinguish between function calls and references to tables or columns that happen to have the same name as a function.
For the sake of brevity, the examples shown below display the output from
the mysql program in abbreviated form.
So this:
mysql> select MOD(29,9); 1 rows in set (0.00 sec) +-----------+ | mod(29,9) | +-----------+ | 2 | +-----------+
Is displayed like this:
mysql> select MOD(29,9);
-> 2
( ... )
mysql> select 1+2*3;
-> 7
mysql> select (1+2)*3;
-> 9
Note that in the case of -, + and *, the result is calculated with
BIGINT 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 is over the 64-bit range.
/
NULL result.
mysql> select 3/5;
-> 0.60
mysql> select 102/(1-1);
-> NULL
A division will be calculated with BIGINT arithmetic only if it's
used in a context where its result is converted to an integer!
These have a maximum range of 64 bits because MySQL uses BIGINT
(64-bit) arithmetic for bit operations.
|
mysql> select 29 | 15;
-> 31
&
mysql> select 29 & 15;
-> 13
<<
mysql> select 1 << 2
-> 4
>>
mysql> select 4 >> 2
-> 1
BIT_COUNT(N)
N.
mysql> select BIT_COUNT(29);
-> 4
All logical functions return 1 (TRUE) or 0 (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.
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
ISNULL(expr)
expr is NULL, returns 1, otherwise returns 0.
mysql> select ISNULL(1+1);
-> 0
mysql> select ISNULL(1/0);
-> 1
expr BETWEEN min AND max
expr is greater than or equal to min and expr is less
than or equal to max, returns 1, otherwise returns 0.
Does the same thing as the expression (min <= expr AND expr <= max) if
all the arguments are of the same type. The first argument (expr)
determines how the comparison is performed. If expr is a string
expression, a case-insensitive string comparison is done. If expr is
a binary string, a case-sensitive string comparison is done. If expr
is an integer expression, an integer comparison is done. Otherwise, a
floating-point (real) comparison is done.
mysql> select 1 BETWEEN 2 AND 3;
-> 0
mysql> select 'b' BETWEEN 'a' AND 'c';
-> 1
mysql> select 2 BETWEEN 2 AND '3';
-> 1
mysql> select 2 BETWEEN 2 AND 'x-3';
-> 0
expr IN (value,...)
1 if expr is any of the values in the IN list,
else returns 0. If all values are constants, then all values are
evaluated according to the type of expr and sorted. The search for the
item is then done using a binary search. This means IN is very quick
when used with constants in the IN value list. If expr is a
case-sensitive string expression, the string comparison is done in
case-sensitive fashion.
mysql> select 2 IN (0,3,5,'wefwf');
-> 0
mysql> select 'wefwf' IN (0,3,5,'wefwf');
-> 1
expr NOT IN (value,...)
NOT (expr IN (value,...)).
INTERVAL(N,N1,N2,N3...)
0 if N < N1, 1 if N < N2
and so on. All arguments are treated as numbers. It is required that
N1 < N2 < N3 < Nn for this function to work
correctly. This is because a binary search is used (very fast).
mysql> select INTERVAL(23, 1, 15, 17, 30, 44, 200);
-> 3
mysql> select INTERVAL(10, 1, 10, 100, 1000);
-> 2
mysql> select INTERVAL(22, 23, 30, 44, 200);
-> 0
Normally, if one expression to be compared is not case sensitive, string comparisons are done in case-insensitive fashion.
expr1 LIKE expr2 [ESCAPE string-of-one-character]
1 (TRUE) or 0
(FALSE). With LIKE you can use the following two wildcard characters:
% | Matches any number of characters, even zero characters |
_ | Matches exactly one character |
ESCAPE character '\' will be used.
To test for literal instances of the wildcard characters, use the following
sequences:
\% | Matches one % character
|
\_ | Matches one _ character
|
mysql> select 'David!' LIKE 'David_';
-> 1
mysql> select 'David!' LIKE 'David\_';
-> 0
mysql> select 'David_' LIKE 'David\_';
-> 1
mysql> select 'David!' LIKE '%D%v%';
-> 1
mysql> select 10 LIKE '1%';
-> 1
mysql> select 'David_' LIKE 'David|_' ESCAPE '|'
LIKE is allowed on numeric expressions! (This is a MySQL
extension to the ANSI SQL LIKE.)
expr1 NOT LIKE expr2 [ESCAPE 'string-of-one-character']
NOT (expr1 LIKE expr2 [ESCAPE 'string-of-one-character']).
expr REGEXP pat
expr RLIKE pat
expr against a pattern
pat. The pattern can be an extended regular expression.
See section H Description of MySQL regular expression syntax. Returns 1 if expr matches pat, otherwise
returns 0. RLIKE is a synonym for REGEXP, provided for
mSQL compatibility. NOTE: Because MySQL uses the C escape
syntax in strings (\n), you must double any '\' that you use in
your REGEXP strings.
mysql> select 'Monty!' REGEXP 'm%y%%';
-> 0
mysql> select 'Monty!' REGEXP '.*';
-> 1
mysql> select 'new*\n*line' REGEXP 'new\\*.\\*line';
-> 1
REGEXP and RLIKE use the current character set (ISO-8859-1
Latin1 by default) when deciding the type of a character.
expr NOT REGEXP expr
NOT (expr REGEXP expr).
STRCMP(expr1,expr2)
0 if the strings are the same. Returns -1 if the first
argument is smaller than the second according to the current sort order.
Otherwise returns 1.
mysql> select STRCMP('text', 'text2');
-> -1
mysql> select STRCMP('text2', 'text');
-> 1
mysql> select STRCMP('text', 'text');
-> 0
IFNULL(expr1,expr2)
expr1 is not NULL, IFNULL() returns expr1,
else returns expr2. IFNULL() returns a numeric or string
value, depending on the context in which it are used.
mysql> select IFNULL(1,0);
-> 1
mysql> select IFNULL(0,10);
-> 0
mysql> select IFNULL(1/0,10);
-> 10
mysql> select IFNULL(1/0,'yes');
-> 'yes'
IF(expr1,expr2,expr3)
expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then
returns expr2, else returns expr3. IFNULL() returns a
numeric or string value, depending on the context in which it are used.
expr1 is evaluated as an INTEGER, which means that if you are
testing floating-point values, you should do so using a comparison
operation.
mysql> select IF(1>2,2,3);
-> 3
mysql> select IF(1<2,'yes','no');
-> 'yes'
mysql> select IF(strcmp('test','test1'),'yes','no');
-> 'no'
mysql> select IF(0.1<>0,1,0);
-> 1
mysql> select IF(0.1,1,0);
-> 0
All mathematical functions return NULL in case of an error.
-
mysql> select - 2;
-> -2
Note that if this function is used with a BIGINT, the return value is a
BIGINT! This means that you should avoid using - on integers that
may have the value of -2^63 !
ABS(X)
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)
%
% 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 an integer.
mysql> select ROUND(-1.23);
-> -1
mysql> select ROUND(-1.58);
-> -2
mysql> select ROUND(1.58);
-> 2
Note that the return value is converted to a BIGINT !
ROUND(X,D)
X, rounded to a number with D decimals.
mysql> select ROUND(1.298, 1);
-> 1.3
Note that the return value is converted to a BIGINT !
EXP(X)
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(X,Y)
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 do an ORDER BY on a column with RAND() values because
ORDER BY would evaluate the column multiple times.
LEAST(X,Y...)
INTEGER, or all arguments are integer-valued,
then they are compared as integers.
REAL, or all arguments are real-valued, then
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 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 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.
mysql> select TRUNCATE(1.223,1);
-> 1.2
mysql> select TRUNCATE(1.999,1);
-> 1.9
mysql> select TRUNCATE(1.999,0);
-> 1
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('dx');
-> 100
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. 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 number.
This is the same as 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 the same as CONV(N,10,8).
Returns NULL if N is NULL.
mysql> select OCT(12);
-> '14'
HEX(N)
N where
N is a longlong number.
This is the same as CONV(N,10,16).
Returns NULL if N is NULL.
mysql> select HEX(255);
-> 'FF'
CHAR(N,...)
NULL values are skipped.
mysql> select CHAR(77,121,83,81,'76');
-> 'MySQL'
CONCAT(X,Y...)
NULL if any argument is NULL. May have more than 2 arguments.
mysql> select CONCAT('My', 'S', 'QL');
-> 'MySQL'
mysql> select CONCAT('My', NULL, 'QL');
-> NULL
LENGTH(str)
OCTET_LENGTH(str)
CHAR_LENGTH(str)
CHARACTER_LENGTH(str)
str.
mysql> select LENGTH('text');
-> 4
mysql> select OCTET_LENGTH('text');
-> 4
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
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
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
LPAD(str,len,padstr)
str, left-padded with the string
padstr until str is len characters long.
mysql> select LPAD('hi',4,'??');
-> '??hi'
RPAD(str,len,padstr)
str, right-padded with the string
padstr until str is len characters long.
mysql> select RPAD('hi',5,'?');
-> 'hi???'
LEFT(str,len)
len characters from the string str.
mysql> select LEFT('foobarbar', 5);
-> 'fooba'
RIGHT(str,len)
SUBSTRING(str FROM len)
len characters from the string str.
mysql> select RIGHT('foobarbar', 4);
-> 'rbar'
mysql> select SUBSTRING('foobarbar' from 4);
-> 'rbar'
SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos FOR len)
MID(str,pos,len)
len characters long from string str,
starting at position pos.
The variant form that uses FROM is ANSI SQL 92 syntax.
mysql> select SUBSTRING('Quadratically',5,6);
-> 'ratica'
SUBSTRING(str,pos)
str starting at position pos.
mysql> select SUBSTRING('Quadratically',5);
-> 'ratically'
SUBSTRING_INDEX(str,delim,count)
str after count
occurrences of the delimiter delim.
If count is positive, everything to the left of the final delimiter
(counting from the left) is returned.
If count is negative, everything to the right of the final delimiter
(counting from the right) is returned.
mysql> select SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> select SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'
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'
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'
SOUNDEX(str)
str. Two strings that sound "about the
same" should have identical soundex strings. A "standard" soundex string
is 4 characters long, but the SOUNDEX() function returns an arbitrarily
long string. You can use SUBSTRING() on the result to get a "standard"
soundex string. All non-alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vowels.
mysql> select SOUNDEX('Hello');
-> 'H400'
mysql> select SOUNDEX('Quadratically');
-> 'Q36324'
SPACE(N)
N space characters.
mysql> select SPACE(6);
-> ' '
REPLACE(str,from,to)
str with all
all occurrences of the string from replaced by the string to.
mysql> select REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwww.mysql.com'
REPEAT(str,count)
str repeated count times. If count <= 0, returns an
empty string. Returns NULL if str or count are NULL
or if LENGTH(str)*count > max_allowed_packet.
mysql> select REPEAT('MySQL', 3);
-> 'MySQLMySQLMySQL'
REVERSE(str)
str with the order of the characters reversed.
mysql> select REVERSE('abc');
-> 'cba'
INSERT(str,start,len,newstr)
str, with the substring beginning at position
start and len characters long replaced by the string
newstr.
mysql> select INSERT('Quadratic', 3, 4, 'What');
-> 'QuWhattic'
ELT(N,str1,str2,str3...)
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 itself a
string with its individual substrings separated by ',' characters. If the
first argument is a constant string and the second is a column of type
SET, the FIND_IN_SET is optimized to use bit arithmetic!
Returns 0 if strlist is the empty string. Returns NULL if
either argument is NULL. This function will not work properly if the
first argument contains a ','.
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
-> 2
MAKE_SET(bits,strlist)
NULL strings in the set list are not
appended to the result.
mysql> SELECT MAKE_SET(1,'a','b','c');
-> 'a'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
-> 'hello,world'
mysql> SELECT MAKE_SET(0,'a','b','c');
-> "
LCASE(str)
LOWER(str)
str with all characters changed to lowercase
according to the current character set mapping (the default is Latin1).
mysql> select LCASE('QUADRATICALLY');
-> 'quadratically'
UCASE(str)
UPPER(str)
str with all characters changed to uppercase
according to the current character set mapping (the default is Latin1).
mysql> select UCASE('Hej');
-> 'HEJ'
There is no string function to convert a number to a char. This is not needed as MySQL automaticly converts numbers to string and vice versa:
SELECT 1+"1"; -> 2 SELECT concat(2,' test'); -> '2 test'
If a string function gets a binary string as an argument, the resulting string is also a binary string. A number converted to a string is treated as a binary string. This only affects comparisons.
Here is an example that uses date functions. The query below selects
all records with a date_field value from the last 30 days:
mysql> SELECT something FROM table
WHERE TO_DAYS(NOW()) - TO_DAYS(date_field) <= 30;
See section 7.2.6 Date and time types for a description of the range of values each type has, and the valid formats in which date and time values may be specified.
DAYOFWEEK(date)
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 52, for locations where Sunday is the first day of the week.
The two-argument form of WEEK() allows you to specify whether the
week starts on Sunday or Monday. The week starts on Sunday if the second
argument is 0, on Monday if the second argument is 1.
mysql> select WEEK('1998-02-20');
-> 7
mysql> select WEEK('1998-02-20',0);
-> 7
mysql> select WEEK('1998-02-20',1);
-> 8
YEAR(date)
date, in the range 1000 to 9999.
mysql> select YEAR('98-02-03');
-> 1998
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.
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.
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().
date is the starting date (a DATETIME or DATE value).
expr is an expression specifying the interval value to be added or
substracted from the starting date. expr is a string; it may start
with a `-' for negative intervals. type is an interval type
keyword indicating how the expression should be interpreted.
type value | Meaning | expr format
|
SECOND | Seconds | SECONDS
|
MINUTE | Minutes | MINUTES
|
HOUR | Hours | HOURS
|
DAY | Days | DAYS
|
MONTH | Months | MONTHS
|
YEAR | Years | YEARS
|
MINUTE_SECOND | Minutes and seconds | "MINUTES:SECONDS"
|
HOUR_MINUTE | Hours and minutes | "HOURS:MINUTES"
|
DAY_HOUR | Days and hours | "DAYS HOURS"
|
YEAR_MONTH | Years and months | "YEARS-MONTHS"
|
HOUR_SECOND | Hours, minutes, seconds | "HOURS:MINUTES:SECONDS"
|
DAY_MINUTE | Days, hours, minutes | "DAYS HOURS:MINUTES"
|
DAY_SECOND | Days, hours, minutes, seconds | "DAYS HOURS:MINUTES:SECONDS"
|
DATE value and your calculations involve only
YEAR, MONTH and DAY (that is, no time parts), the result
is a DATE value. Otherwise the result is a DATETIME value.
mysql> select DATE_ADD("1997-12-31 23:59:59",INTERVAL 1 SECOND);
-> 1998-01-01 00:00:00
mysql> select DATE_ADD("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND);
-> 1998-01-01 00:01:00
mysql> select DATE_SUB("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND);
-> 1997-12-30 22:58:59
mysql> select DATE_ADD("1997-12-31 23:59:59",INTERVAL 1 DAY);
-> 1998-01-01 23:59:59
mysql> select DATE_ADD("1998-01-01 00:00:00",INTERVAL "-1 10" DAY_HOUR);
-> 1997-12-30 14:00:00
mysql> select DATE_SUB("1998-01-02",INTERVAL 31 DAY);
-> 1997-12-02
If you specify an interval value that is too short (does not include all the
interval parts that would be expected from the interval type keyword),
MySQL assumes you have left out the leftmost parts of the interval
value. For example, if you specify a type of DAY_SECOND, the
value of expr is expected to have day, hours, minutes and seconds
parts. If you specify a value like "1:10", MySQL assumes
that the day and hours parts are missing and the the value represents minutes
and seconds. In other words, "1:10" DAY_SECOND is interpreted as
"1:10" MINUTE_SECOND.
If you use incorrect dates, the result is NULL. If you add
MONTH, YEAR_MONTH or YEAR and the resulting date
has a day that is larger than the maximum day for the new month, the day is
adjusted to the maximum days in the new month.
mysql> select date_add('1998-01-30',Interval 1 month);
-> 1998-02-28
TO_DAYS(date)
date, returns a daynumber (the number of days since year
0). TO_DAYS() is not intended for use with values that precede the
advent of the Gregorian calendar (1582).
mysql> select TO_DAYS(950501);
-> 728779
mysql> select TO_DAYS('1997-10-07);
-> 729669
FROM_DAYS(N)
N, returns a DATE value.
FROM_DAYS() is not intended for use with values that precede the
advent of the Gregorian calendar (1582).
mysql> select FROM_DAYS(729669);
-> '1997-10-07'
DATE_FORMAT(date,format)
date value according to the format string. The
following specifiers may be used in the format string:
%M | Month name (January..December)
|
%W | Weekday name (Sunday..Saturday)
|
%D | Day of the month with english suffix (1st, 2nd, 3rd, etc.)
|
%Y | Year, numeric, 4 digits |
%y | Year, numeric, 2 digits |
%a | Abbreviated weekday name (Sun..Sat)
|
%d | Day of the month, numeric (00..31)
|
%e | Day of the month, numeric (0..31)
|
%m | Month, numeric (01..12)
|
%c | Month, numeric (1..12)
|
%b | Abbreviated month name (Jan..Dec)
|
%j | Day of year (001..366)
|
%H | Hour (00..23)
|
%k | Hour (0..23)
|
%h | Hour (01..12)
|
%I | Hour (01..12)
|
%l | Hour (1..12)
|
%i | Minutes, numeric (00..59)
|
%r | Time, 12-hour (hh:mm:ss [AP]M)
|
%T | Time, 24-hour (hh:mm:ss)
|
%S | Seconds (00..59)
|
%s | Seconds (00..59)
|
%p | AM or PM
|
%w | Day of the week (0=Sunday..6=Saturday)
|
%U | Week (0..52), where Sunday is the first day of the week.
|
%u | Week (0..52), where Monday is the first day of the week.
|
%% | Single `%' characters are ignored. Use %% to produce a literal `%' (for future extensions).
|
mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
-> 'Saturday October 1997'
mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%D %y %a %d %m %b %j');
-> '4th 97 Sat 04 10 Oct 277'
mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
For the moment, % is optional. In future versions of MySQL,
% will be required.
TIME_FORMAT(time,format)
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
YYYYMMDD or 'YYYY-MM-DD',
depending on whether the function is used in a numeric or string context.
mysql> select CURDATE();
-> '1997-12-15'
mysql> select CURDATE()+0;
-> 19971215
CURTIME()
CURRENT_TIME
HHMMSS or 'HH:MM:SS',
depending on whether the function is used in a numeric or string context.
mysql> select CURTIME();
-> '23:50:26'
mysql> select CURTIME()+0;
-> 235026
NOW()
SYSDATE()
CURRENT_TIMESTAMP
YYYYMMDDHHMMSS or
'YYYY-MM-DD HH:MM:SS', depending on whether the function is used
in a numeric or string context.
mysql> select NOW();
-> '1997-12-15 23:50:26'
mysql> select NOW()+0;
-> 19971215235026
UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)
'1970-01-01 00:00:00'). Normally, it is called with a
TIMESTAMP-valued argument, in which case it returns the value of the
argument in seconds. date may be a DATE string, a
DATETIME string, a TIMESTAMP, or a number in the format
YYMMDD or YYYYMMDD in local time.
mysql> select UNIX_TIMESTAMP();
-> 882226357
mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00');
-> 875996580
When UNIX_TIMESTAMP is used on a TIMESTAMP column, the function
will get the value without an implicit `string-to-unix-timestamp' conversion.
FROM_UNIXTIME(Unix_timestamp)
YYYYMMDDHHMMSS or 'YYYY-MM-DD HH:MM:SS', depending on whether
the function is used in a numeric or string context.
mysql> select FROM_UNIXTIME(875996580);
-> '1997-10-04 22:23:00'
mysql> select FROM_UNIXTIME(875996580)+0;
-> 19971004222300
FROM_UNIXTIME(Unix_timestamp,format)
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 in the format
HHMMSS or HH:MM:SS, depending on whether
the function is used in a numeric or string context.
mysql> select SEC_TO_TIME(2378);
-> '00:39:38'
mysql> select SEC_TO_TIME(2378)+0;
-> 3938
TIME_TO_SEC(time)
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'
USER()
SYSTEM_USER()
SESSION_USER()
mysql> select USER();
-> 'davida@localhost'
PASSWORD(str)
str. To
store a password in the user grant table, this function must be used.
mysql> select PASSWORD('badpwd');
-> '7f84554057dd964b'
PASSWORD() performs password encryption, but it does not do so in the
same way that Unix passwords are encrypted. You should not assume that if
your Unix password and your MySQL password are the same,
PASSWORD() will result in the same encrypted value as is stored in the
Unix password file. See ENCRYPT().
ENCRYPT(str[,salt])
str using the Unix crypt() system call. The
salt argument should be a string with 2 characters. If
crypt() is not available on your system, ENCRYPT()
always returns NULL.
mysql> select ENCRYPT("hello");
-> 'VxuFAJXVARROc'
LAST_INSERT_ID([expr])
AUTO_INCREMENT column.
See section 18.4.49 How can I get the unique ID for the last inserted row?.
mysql> select LAST_INSERT_ID();
-> 1
The last ID that was generated is maintained in the server on a
per-connection basis. It will not be changed by another client. It will not
even be changed if you update another AUTO_INCREMENT column with a
non-magic value (that is, a value that is not NULL and not 0).
If expr is given in an UPDATE clause, then the used value is
returned as a last_insert_id value. This can be used to simulate sequences:
First create the table:
create table sequence (id int not null); insert into sequence values (0);This can now be used to generate sequence numbers with:
UPDATE sequence SET id=last_insert_id(id+1);The new id can be read as you would read any normal auto_increment value in MySQL (For example
LAST_INSERT_ID() will return the
new id).
FORMAT(X,D)
X to a format like '#,###,###.##' with
D decimals.
mysql> select FORMAT(12332.33, 2);
-> '12,332.33'
VERSION()
mysql> select VERSION();
-> '3.21.16-beta-log'
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.
mysql> select GET_LOCK("automatically released",10);
-> 1
mysql> select GET_LOCK("test",10);
-> 1
mysql> select RELEASE_LOCK("test");
-> 1
mysql> select RELEASE_LOCK("automatically released");
-> NULL
RELEASE_LOCK(str)
str that was obtained with
GET_LOCK(). Returns 1 if the lock was released, 0 if the
lock wasn't locked by this thread and NULL if the named lock didn't
exist.
GROUP BY clausesCOUNT(expr)
NULL rows.
mysql> select COUNT(if(length(name)>3,1,NULL)) from student;
COUNT(*) is optimized to
return very quickly if the SELECT retrieves from one table, no
other columns are retrieved and there is no WHERE clause.
mysql> select COUNT(*) from student;
AVG(expr)
expr.
MIN(expr)
MAX(expr)
expr.
MIN() and MAX() may take a
string argument; in such cases they return the minimum or maximum string value.
SUM(expr)
expr.
STD(expr)
STDDEV(expr)
expr. This is an extension to
ANSI SQL.
The STDDEV() form of this function is provided for Oracle compatability.
BIT_OR(expr)
OR of all bits in expr. The calculation is
performed with 64-bit precision.
BIT_AND(expr)
AND of all bits in expr. The calculation is
performed with 64-bit precision.
MySQL has extended the use of GROUP BY. You can use columns or
calculations in the SELECT expressions which don't appear in
the GROUP BY part. This stands for any possible value for this
group. You can use this to get better performance by avoiding sorting and
grouping on unnecessary items. For example, you don't need to group on
b.name in the following query:
mysql> select a.id,b.name,count(*) from a,b where a.id=b.id GROUP BY a.id;
In ANSI SQL, you would have to add customer.name to the GROUP BY
for the following query. In MySQL, the name is redundant.
mysql> select order.custid,customer.name,max(payments)
from order,customer
where order.custid = customer.custid
GROUP BY order.custid;
Don't use this feature if the columns you omit from the
GROUP BY part aren't unique in the group!
In some specific cases, you can use LEAST() and GREATEST() to
get a specific column even if it isn't unique. The following gives the value
from the row with the smallest "sort" value.
substr(LEAST(concat(sort,space(6-length(sort)),column),7,length(column)))
Note that you can't yet use expressions in GROUP BY or
ORDER BY clauses. On the other hand, you can use an alias on
an expression to solve the problem:
mysql> select id,floor(value/100) as val from tbl_name
GROUP BY id,val ORDER BY val;
CREATE DATABASE syntaxCREATE DATABASE db_name
CREATE DATABASE creates a database with the given name. Rules for
allowable database names are given in section 7.1.4 Database, table, index, column and alias names.
Databases in MySQL are implemented as directories containing files
that correspond to tables in the database. Since there are no tables in a
database when it is initially created, the CREATE DATABASE statement
only creates a directory under the MySQL data directory.
You can also create databases with mysqladmin.
See section 12.1 Overview of the different MySQL programs.
DROP DATABASE syntaxDROP DATABASE [IF EXISTS] db_name
DROP DATABASE drops all tables in the database and deletes the
database. You must be VERY careful with this command! DROP
DATABASE returns the number of files that were removed from the database
directory. Normally, this is three times the number of tables, since each
table corresponds to a `.ISD' file, a `.ISM' file and a `.frm'
file.
In MySQL 3.22 or later, you can use the keywords IF EXISTS to
prevent an error from occurring if the database doesn't exist.
You can also drop databases with mysqladmin.
See section 12.1 Overview of the different MySQL programs.
CREATE TABLE syntax
CREATE TABLE tbl_name (create_definition,...)
create_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
[PRIMARY KEY] [reference_definition]
or PRIMARY KEY (index_col_name,...)
or KEY [index_name] KEY(index_col_name,...)
or INDEX [index_name] (index_col_name,...)
or UNIQUE [index_name] (index_col_name,...)
or [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
[reference_definition]
or CHECK (expr)
type:
TINYINT[(length)] [UNSIGNED] [ZEROFILL]
or SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
or MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
or INT[(length)] [UNSIGNED] [ZEROFILL]
or INTEGER[(length)] [UNSIGNED] [ZEROFILL]
or BIGINT[(length)] [UNSIGNED] [ZEROFILL]
or REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
or FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
or NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
or CHAR(length) [BINARY]
or VARCHAR(length) [BINARY]
or DATE
or TIME
or TIMESTAMP
or DATETIME
or TINYBLOB
or BLOB
or MEDIUMBLOB
or LONGBLOB
or TINYTEXT
or TEXT
or MEDIUMTEXT
or LONGTEXT
or ENUM(value1,value2,value3...)
or SET(value1,value2,value3...)
index_col_name:
col_name [(length)]
reference_definition:
REFERENCES tbl_name [(index_col_name,...)]
[MATCH FULL | MATCH PARTIAL]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
CREATE TABLE
creates a table with the given name in the current database. Rules for
allowable table names are given in See section 7.1.4 Database, table, index, column and alias names.
Each table is represented by three files in the database directory:
| File | Purpose |
| `tbl_name.frm' | Table definition (form) file |
| `tbl_name.ISD' | Data file |
| `tbl_name.ISM' | Index file |
In MySQL 3.22, the table name can be given as db_name.tbl_name.
For more information on the properties of the various column types, see section 7.2 Column types.
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.
See section 18.4.49 How can I get the unique ID for the last inserted row?.
If you delete the row containing the maximum value for an AUTO_INCREMENT
column, the value will be reused. If you delete all rows in the table, the
sequence starts over.
Note: there can be only one AUTO_INCREMENT column per table,
and it must be indexed.
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 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 it to NULL sets it to the current
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 the TIMESTAMP may
take NULL values, even though TIMESTAMP never will actually
hold 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, and the column is
not declared as NOT NULL, the default value is NULL.
DEFAULT value is specified for a column, and the column is
declared as NOT NULL,
MySQL automatically assigns a default value for the field. The
default depends on the column type:
0. Exception: for an
AUTO_INCREMENT column, the default value is the next value in the
sequence.
TIMESTAMP column in
the table, the default value is the current time.
See section 7.2.6 Date and time types.
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. A table can have only one
PRIMARY KEY. MySQL marks the first UNIQUE key
as the PRIMARY KEY if no PRIMARY KEY is specified explicitly.
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.
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.20 SHOW syntax (Get information about tables, columns...).
NULL values.
You must declare such columns NOT NULL or an error results.
col_name(length) syntax, you can specify an index which
uses only a part of a CHAR or VARCHAR column. This can
make the index file much smaller.
See section 7.2.9 Column indexes.
TEXT and BLOB columns cannot be indexed.
TEXT or BLOB column, only the
first max_sort_length bytes are used. See section 5.4 Limitations of BLOB and TEXT types.
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.2 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)
In some cases, MySQL silently changes a column specification from
that given in the CREATE TABLE statement:
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
VARCHARs. This doesn't affect how you use the columns in any way; in
MySQL, VARCHAR is just a different way to store characters.
MySQL does the conversion because it saves space and makes table
operations faster.
See section 10.14 What are the different row formats? Or, when should VARCHAR/CHAR be used?.
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.
Certain other column type changes may occur if you compress a table using
pack_isam.
See section 10.14 What are the different row formats? Or, when should VARCHAR/CHAR be used?.
ALTER TABLE syntax
ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]
alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
or ADD INDEX [index_name] (index_col_name,...)
or ADD PRIMARY KEY (index_col_name,...)
or ADD UNIQUE [index_name] (index_col_name,...)
or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
or CHANGE [COLUMN] old_col_name create_definition
or DROP [COLUMN] col_name
or DROP PRIMARY KEY
or DROP INDEX key_name
or RENAME [AS] new_tbl_name
ALTER TABLE allows you to change the structure of any existing table.
For example, you can add or delete columns, create or destroy indexes, change
the type of existing columns, or rename columns or the table itself.
ALTER TABLE works by making a temporary copy of the original table.
The alteration is performed on the copy, then the original table is
deleted and the new one is renamed. This is done in such a way that
all updates are automatically redirected to the new table without
any failed updates. While ALTER TABLE is executing, the original
table is readable by other clients. Updates and writes to the table
are stalled until the new table is ready.
ALTER TABLE, you need select, insert,
delete, update, create and drop
privileges on the table.
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.
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.
CHANGE col_name, DROP col_name and DROP
INDEX are MySQL extensions to ANSI SQL92.
COLUMN is a pure noise word and can be omitted.
ALTER TABLE tbl_name RENAME AS 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.
create_definition uses the same syntax for ADD and CHANGE
as for CREATE TABLE. See section 7.6 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, the syntax still requires two column names even if they are the same. For example:
mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
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 (at the end of the row).
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.6 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.)
CHANGE, MySQL tries to
convert data to the new type as well as possible.
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.2 Functionality missing from MySQL.
Here is an example that shows some of the uses of
uses of ALTER TABLE. We begin with a table t1 that is
created as shown below:
mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));
To rename the table from t1 to t2:
mysql> ALTER TABLE t1 RENAME t2;
To change column a from INTEGER to TINYINT NOT NULL
(leaving the name the same), and change column b from CHAR(10)
to CHAR(20) (and rename it from b to c):
mysql> ALTER TABLE t2 CHANGE a a TINYINT NOT NULL, CHANGE b c CHAR(20);
To add a new TIMESTAMP column named d:
mysql> ALTER TABLE t2 ADD d TIMESTAMP;
To add an index on column d, and make column a the primary key:
mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
To remove column c:
mysql> ALTER TABLE t2 DROP COLUMN c;
To add a new AUTO_INCREMENT integer column named c which cannot
be NULL, and index it at the same time (since AUTO_INCREMENT
columns must be indexed):
mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD INDEX (c);
OPTIMIZE TABLE syntaxOPTIMIZE TABLE tbl_name
OPTIMZE TABLE should be used if you have deleted a large part of the
table or if you have made many changes to a table with variable-length rows
(tables that have VARCHAR, BLOB or TEXT columns).
Deleted records are maintained in a linked list and subsequent INSERT
operations reuse old record positions. You can use OPTIMIZE TABLE to
reclaim the unused space.
OPTIMIZE TABLE works by making a temporary copy of the original
table. The old table is copied to the new table (without the unused rows),
then the original table is deleted and the new one is renamed. This is done
in such a way that all updates are automatically redirected to the new table
without any failed updates. While OPTIMIZE TABLE is executing, the
original table is readable by other clients. Updates and writes to the table
are stalled until the new table is ready.
DROP TABLE syntaxDROP TABLE [IF EXISTS] tbl_name [, tbl_name...]
DROP TABLE removes one or more tables. All table data and the table
definition are removed, so take it easy with this command!
In MySQL 3.22 or later, you can use the keywords IF EXISTS to
prevent an error from occurring for tables that don't exist.
DELETE syntaxDELETE [LOW_PRIORITY] FROM tbl_name [WHERE where_definition]
DELETE deletes rows from tbl_name that satisfy the condition
given by where_definition, and returns the number of records affected.
If you issue a DELETE with no WHERE clause, all rows are
deleted. MySQL does this by recreating the table as an empty table,
which is much faster than deleting each row. In this case, DELETE
returns zero as the number of affected records. (MySQL can't return
the number of rows that were actually deleted, since the recreate is done
without opening the data files. As long as the table definition file
`tbl_name.frm' is valid, the table can be recreated this way, even if
the data or index files have become corrupted.).
If you really want to know how many rows where deleted, you can use
DELETE FROM TABLE WHERE 1>0. Note that this is MUCH slower than the
above.
If you specify the keyword LOW_PRIORITY, execution of the
DELETE is delayed until no other clients are reading from the table.
Deleted records are maintained in a linked list and subsequent INSERT
operations reuse old record positions. To get smaller files, use the
OPTIMIZE TABLE statement or the isamchk utility to reorganize
tables. OPTIMIZE TABLE is easier, but isamchk is faster.
See section 13.5.3 Table optimization.
SELECT syntax
SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [DISTINCT | ALL]
select_expression,...
[INTO OUTFILE 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY col_name,...]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name} [ASC | DESC] ,...]
[LIMIT [offset,] rows]
[PROCEDURE procedure_name] ]
SELECT is usually used to retrieve rows selected from one or more
tables. SELECT may also be used to retrieve rows computed without
reference to any table. For example:
mysql> SELECT 1 + 1;
-> 2
All keywords used must be given in exactly the order shown above. For example,
a HAVING clause must come after any GROUP BY clause and before
any ORDER BY clause.
tbl_name AS alias_name or
tbl_name alias_name.
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.4 Database, table, index, column and alias names, for examples of ambiguity that require the more
explicit column reference forms.
SELECT expression may be given an alias using AS. The alias
is used as the expression's column name and can be used with SORT BY,
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 a list of tables to join
(i.e., one or more tables from which to select rows). This list may also
contain LEFT OUTER JOIN references.
See section 7.12 JOIN syntax.
LIKE expressions, the wildcard characters `%' and `_'
may be preceded with `\' to suppress their usual wildcard meaning and
search for literal instances of `%' and `_'.
ORDER BY and
GROUP BY clauses using column names, column aliases or column numbers.
Column numbers begin with 1.
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 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;
STRAIGHT_JOIN forces the optimizer to join the tables in the same
order as that in which the tables 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.21 EXPLAIN syntax (Get information about a SELECT).
SQL_SMALL_RESULT can be used with GROUP BY or
DISTINCT to tell the optimizer that it the result set will be
small. In this case MySQL will use fast temporary tables to store
the resulting table instead of using sorting.
LIMIT takes one or two numeric arguments:
mysql> select * from table LIMIT 5; # Retrieve first 5 rows
mysql> select * from table LIMIT 5,10; # Retrieve rows 6-15
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). 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.15 LOAD DATA INFILE syntax.
Note that, by default, the escape character, ASCII 0 (nul) and all
terminator characters will be escaped when you use INTO OUTFILE.
JOIN syntax
MySQL supports the following JOIN syntaxes for use in
SELECT statements:
table_reference, table_reference
table_reference [CROSS] JOIN table_reference
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference ON conditional_expr
table_reference LEFT [OUTER] JOIN table_reference USING (column_list)
table_reference NATURAL LEFT [OUTER] JOIN table_reference
{ oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr }
The last LEFT OUTER JOIN syntax shown above exists only for
compatibility with ODBC.
tbl_name AS alias_name or
tbl_name alias_name.
JOIN and , (comma) are semantically identical. 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. If there is no matching record for the right table 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 (i.e., all rows in table1 with no
corresponding row in table2). This assumes that table2.id is
declared NOT NULL, of course.
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
identical to a USING with all column names that exist in both
tables.
STRAIGHT_JOIN is identical to JOIN, except that the left table
is always read before the right table. This can be used in the few
cases where the join optimizer puts the tables in the wrong order.
Some examples:
mysql> select * from table1,table2 where table1.id=table2.id;
mysql> select * from table1 LEFT JOIN table2 ON table1.id=table2.id;
mysql> select * from table1 LEFT JOIN table2 USING (id);
mysql> select * from table1 LEFT JOIN table2 ON table1.id=table2.id
LEFT JOIN table3 ON table3.id=table2.id;
INSERT syntax
INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name,...)]
VALUES (expression,...),(...),...
or INSERT [LOW_PRIORITY] [INTO] tbl_name [(col_name,...)]
SELECT ...
or INSERT [LOW_PRIORITY] [INTO] tbl_name SET col_name=expression,
col_name=expression,...
INSERT inserts new rows into an existing table. The INSERT ...
VALUES form inserts rows based on explicitly-specified values. The
INSERT ... SELECT form inserts rows selected from another table or
tables. The INSERT ... VALUES form with multiple value lists is
supported in MySQL 3.22.5 or later. The col_name=expression
syntax is supported in MySQL 3.22.10 and later.
tbl_name is the table to insert rows into. The column name
list indicates which columns the rest of the statement specifies values for.
DESCRIBE tbl_name
to find out.
CREATE TABLE syntax.
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.
NULL into a TIMESTAMP column, the column is set
to the current time. If you insert other values, the column is simply set to
the value specified.
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 (colA,colB) VALUES(15,colA*2);But not this:
mysql> INSERT INTO tbl_name (colA,colB) VALUES(colB*2,15);
LOW_PRIORITY, execution of the
INSERT is delayed until no other clients are reading from the table.
IGNORE to an INSERT with many
value rows, the insert will be aborted if there is any row with a similar
PRIMARY or UNIQUE key in the table. If you specify IGNORE,
the rows with a duplicate key value will be not be inserted. You can check
with the mysql_info() how many rows was inserted into the table.
INSERT INTO ... 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.
If you use INSERT INTO ... SELECT ... or a INSERT INTO ...
VALUES() statement with multiple value lists, you can use the C API function
mysql_info() to get information about the query. The format of the
information string is shown below:
Records: 100 Duplicates: 0 Warnings: 0
Duplicates indicates the number of rows which couldn't be inserted
because some unique index value in existing rows would be duplicated.
Warnings indicates the number of attempts to insert column values
that were problematic in some way. Warnings can occur under
any of the following conditions:
NULL into a column that has been declared NOT NULL.
The column is set to its default value.
0.
CHAR, VARCHAR, TEXT or
BLOB column that exceeds the column's maximum length. The value is
truncated to the column's maximum length.
The DELAYED option (introduced in MySQL 3.22.15) to 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 common when you use MySQL for logging
and you also periodically run SELECT statements that takes a
long time to complete.
Another major benefits of using INSERT DELAYED are 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, all your
queued rows that weren't written to disk are lost!
The following happens when you use the DELAYED option to
INSERT or REPLACE:
In the following the 'thread' is the thread which got a INSERT
DELAYED command and 'handler' is the thread that handles all
INSERT DELAYED statements for a specific table.
DELAYED statement for a new
table, a handler thread is created that will handle all DELAYED
statements for this table.
DELAYED lock; if
not it tells the handler thread to obtain a lock. The DELAYED lock
can get 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 the client program.
AUTO_INCREMENT value for the resulting row.
delayed_insert_limit writes, the handler checks whether or
not there are any pending SELECT statements. If there are, it
allows these to execute before continuing.
INSERT DELAYED commands in
delayed_insert_timeout, the handler will end.
delayed_queue_size rows already pending in a
specific handler queue, the thread will wait until there is room in
the queue. This is useful 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 rows in the
queue before exiting. During this time it will not accept any new
INSERT commands from another thread; If one executes an INSERT
DELAYED command after this a new handler thread will be created.
INSERT DELAYED commands have higher
priority than normal INSERT if there is a INSERT DELAYED handler
running! Other update commands will have to wait until the INSERT DELAY
queue is empty, someone kills the handler thread (with KILL thread-id
or someone executes FLUSH TABLES.
INSERT DELAYED commands:
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 |
REPLACE syntax
REPLACE [LOW_PRIORITY] [INTO] tbl_name [(col_name,...)]
VALUES (expression,...)
or REPLACE [LOW_PRIORITY] [INTO] tbl_name [(col_name,...)]
SELECT ...
or REPLACE [LOW_PRIORITY] [INTO] tbl_name SET col_name=expression,
col_name=expression,...
REPLACE works exactly like INSERT, except that if an old
record in the table has the same value on a unique index as a new record,
the old record is deleted before the new record is inserted.
See section 7.13 INSERT syntax.
LOAD DATA INFILE syntax
LOAD DATA [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY '\t']
[OPTIONALLY] ENCLOSED BY "]
[ESCAPED BY '\\' ]]
[LINES TERMINATED BY '\n']
[(col_name,...)]
The LOAD DATA INFILE statement reads rows from a text file into a
table at a very high speed. If the LOCAL keyword is specified, the
file is read from the client host. If LOCAL is not specified, the
file must be located on the server. (LOCAL is available in
MySQL 3.22.6 or later.) Using LOCAL will be a bit slower than
letting the server access the files directly, since the contents of the file
must travel from the client host to the server host.
The mysqlimport utility can be used to read data files; it operates by
sending a LOAD DATA INFILE command to the server. The --local
option causes mysqlimport to read data files from the client host.
You can specify the --compress option to get better performance over
slow networks if the client and server support the compressed protocol.
When locating files on the server host, the server uses the following rules:
Note that these rules mean a file given as `myfile.txt' is read from the database directory, whereas a file given as `./myfile.txt' is read from the server's data directory.
For security reasons, when reading text files from the server, the files must
either reside in the database directory or be readable by all. Also, to
use LOAD DATA INFILE on server files, you must have the file
privilege for the database.
See section 6.4 How the privilege system works.
LOAD DATA INFILE is the complement of SELECT ... INTO OUTFILE.
See section 7.11 SELECT syntax.
To write data from a database to a file, use SELECT ... INTO OUTFILE.
To read the file back into the database, use LOAD DATA INFILE.
The syntax of the FIELDS and LINES clauses is the same for
both commands. Both clauses are optional, but FIELDS
must precede LINES if both are specified.
If you specify a FIELDS clause,
each of its subclauses (TERMINATED BY, [OPTIONALLY] ENCLOSED
BY and ESCAPED BY) is also optional, except that you must
specify at least one of them.
If you don't specify a FIELDS clause, the defaults are the
same as if you had written this:
FIELDS TERMINATED BY '\t' ENCLOSED BY " ESCAPED BY '\\'
If you don't specify a LINES clause, the default
is the same as if you had written this:
LINES TERMINATED BY '\n'
In other words, the defaults cause SELECT ... INTO OUTFILE
to act as follows when writing output:
Conversely, the defaults cause LOAD DATA INFILE to act
as follows when reading input:
Note that to write FIELDS ESCAPED BY '\\', you must specify two
backslashes for the value to be read as a single backslash.
When you use SELECT ... INTO OUTFILE in tandem with LOAD
DATA INFILE to write data from a database into a file and then read
the file back into the database later, the field and line handling
options for both commands must match. Otherwise, LOAD DATA
INFILE will not interpret the contents of the file properly. Suppose
you use SELECT ... INTO OUTFILE to write a file with
fields delimited by commas:
mysql> SELECT * FROM table1 INTO OUTFILE 'data.txt'
FIELDS TERMINATED BY ','
FROM ...
To read the comma-delimited file back in, the correct statement would be:
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
FIELDS TERMINATED BY ',';
If instead you tried to read in the file with the statement shown below, it
wouldn't work because it instructs LOAD DATA INFILE to look for
tabs between fields:
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
FIELDS TERMINATED BY '\t';
The likely result is that each input line would be interpreted as a single field.
LOAD DATA INFILE can be used to read files obtained from
external sources, too. For example, a file in DBASE format will have
fields separated by commas and enclosed in double quotes. If lines in
the file are terminated by newlines, the command shown below
illustrates the field and line handling options you would use to load
the file:
mysql> LOAD DATA INFILE 'file_name.txt' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Any of the field or line handling options may specify an empty string
("). If not empty, the FIELDS [OPTIONALLY] ENCLOSED BY
and FIELDS ESCAPED BY values must be a single character. The
FIELDS TERMINATED BY and LINES TERMINATED BY values may
be more than one character. For example, to write lines that are
terminated by carriage return-linefeed pairs, or to read a file
containing such lines, specify a LINES TERMINATED BY '\r\n'
clause.
FIELDS [OPTIONALLY] ENCLOSED BY controls quoting of fields. For
output (SELECT ... INTO OUTFILE), if you omit the word
OPTIONALLY, all fields are enclosed by the ENCLOSED BY
character. An example of such output (using a comma as the field
delimiter) is shown below:
"1","a string","100.20" "2","a string containing a , comma","102.20" "3","a string containing a \" quote","102.20" "4","a string containing a \", quote and comma","102.20"
If you specify OPTIONALLY, the ENCLOSED BY character is
used only to enclose CHAR and VARCHAR fields:
1,"a string",100.20 2,"a string containing a , comma",102.20 3,"a string containing a \" quote",102.20 4,"a string containing a \", quote and comma",102.20
Note that occurrences of the ENCLOSED BY character within a
field value are escaped by prefixing them with the ESCAPED BY
character. Also note that if you specify an empty ESCAPED BY
value, you may generate output that cannot be read properly by LOAD
DATA INFILE. For example, the output just shown above would appear
as shown below if the escape character is empty. Observe that the second
field in the fourth line contains a comma following the quote, which
(erroneously) appears to terminate the field:
1,"a string",100.20 2,"a string containing a , comma",102.20 3,"a string containing a " quote",102.20 4,"a string containing a ", quote and comma",102.20
For input, the ENCLOSED BY character, if present, is stripped from the
ends of field values. (This is true whether or not OPTIONALLY is
specified; OPTIONALLY has no effect on input interpretation.)
Occurrences of the ENCLOSED BY character preceded by the
ESCAPED BY character are interpreted as part of the current field
value. In addition, duplicated ENCLOSED BY characters occurring
within fields are interpreted as single ENCLOSED BY characters if the
field itself starts with that character. For example, if ENCLOSED BY
'"' is specified, quotes are handled as shown below:
"The ""BIG"" boss" -> The "BIG" boss The "BIG" boss -> The "BIG" boss The ""BIG"" boss -> The ""BIG"" boss
FIELDS ESCAPED BY controls how to write or read special characters.
If the FIELDS ESCAPED BY character is not empty, it is used to prefix
the following characters on output:
FIELDS ESCAPED BY character
FIELDS [OPTIONALLY] ENCLOSED BY character
FIELDS TERMINATED BY and
LINES TERMINATED BY values
'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' (e.g., \0 or \N if the escape character is
`\'). These sequences are interpreted as ASCII 0 (a zero-valued byte)
and NULL. See below for the rules on NULL handling.
For more information about `\'-escape syntax, see section 7.1 Literals: how to write strings and numbers.
In certain cases, field and line handling options interact:
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.
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 value of the literal word
NULL 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 a
blank string. Note that this makes NULL values and blank values
indistinguishable in the file. If you need to be able to tell the two
apart when reading the file back in, you should not use fixed-row format.
The REPLACE and IGNORE keywords control handling of input
records that duplicate existing records on unique key values. If you specify
REPLACE, new rows replace existing rows that have the same unique key
value. If you specify IGNORE, input rows that duplicate an existing
row on a unique key value are skipped. If you don't specify either option, an
error occurs when a duplicate key value is found, and the rest of the text
file is ignored.
Some cases are not supported by LOAD DATA INFILE:
FIELDS TERMINATED BY and FIELDS ENCLOSED
BY both empty) and BLOB 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, so that MySQL can tell how to match up input fields with table columns.
If a row has too few fields, the columns for which no input field is present
are set to default values. TIMESTAMP columns are only set to the
current time if there is a NULL value for the column, or (for the
first TIMESTAMP column only) if the TIMESTAMP column is left
out from the field list when a field list is specified. Default value
assignment is described in section 7.6 CREATE TABLE syntax.
If an input row has too many fields, the extra fields are ignored and a warning is generated.
LOAD DATA INFILE regards all input as strings, so you can't use
numeric values for ENUM or SET columns the way you can with
INSERT statements. All ENUM and SET values must be given
as strings!
When the LOAD DATA INFILE query finishes, you can use the C API
function mysql_info() to get information about the query. The format
of the information string is shown below:
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
Warnings occur under the same circumstances as when values are inserted
via the INSERT statement (see section 7.13 INSERT syntax), except that LOAD
DATA INFILE also generates warnings when there are too few or too many
fields in the input row. 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 file.
For more information about the efficiency of INSERT versus
LOAD DATA INFILE and speeding up LOAD DATA INFILE,
see section 10.10 How to arrange a table to be as fast/small as possible.
UPDATE syntax
UPDATE [LOW_PRIORITY] tbl_name SET col_name1=expr1,col_name2=expr2,...
[WHERE where_definition]
UPDATE updates columns in existing table rows with new values.
The WHERE clause, if given, specifies which rows should be updated.
Otherwise all rows are updated.
If you specify the keyword LOW_PRIORITY, execution of the
UPDATE is delayed until no other clients are reading from the table.
If you access a tbl_name column in an expression, UPDATE uses
the current value of the column. For example, the following statement sets
the age column to one more than its current value:
mysql> UPDATE persondata SET age=age+1;
UPDATE assignments are evaluated from left to right. For example, the
following statement doubles the age column, then increments it:
mysql> UPDATE persondata SET age=age*2,age=age+1;
If you set a column to the value it currently has, MySQL notices this and doesn't update it.
UPDATE returns the number of rows that were actually changed.
In MySQL 3.22 or later, the C API function mysql_info()
returns the number of rows that were matched and updated and the number of
warnings that occurred during the UPDATE.
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 syntax (clearing caches)FLUSH flush_option [,flush_option]
You should use the FLUSH command if you want to clear some of the
internal caches MySQL uses. flush_option can be any of the
following:
HOSTS | Empties the host cache tables. You should flush the
host tables if some of your hosts change IP number or if you get the error
message Host ... is blocked. (When too many connections errors occur
for a given host, MySQL assumes something is wrong and blocks the
host from further connection requests. Flushing the host tables allows the
host to attempt to connect again. See section 16.1.3 Host '...' is blocked error.)
|
LOGS | Closes and reopens the standard and update log files. If you have specified the update log file without an extension, the extension number of the new update log file will be incremented by 1 relative to the previous file. |
PRIVILEGES | Reloads the privileges from the grant tables in
the mysql database.
|
TABLES | Closes all open tables. |
STATUS | Reset most status variables to zero. |
You can also access each of the commands shown above with the mysqladmin
utility, using the flush-hosts, flush-logs, reload
or flush-tables commands.
To execute the FLUSH command, you must have the reload
privilege.
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 do not have the process privilege, you can see and kill only your own threads.
You can also use mysqladmin processlist and mysqladmin kill to
examine and kill threads.
SHOW syntax (Get information about tables, columns...)SHOW DATABASES [LIKE wild] or SHOW TABLES [FROM db_name] [LIKE wild] or SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE wild] or SHOW INDEX FROM tbl_name [FROM db_name] or SHOW STATUS or SHOW VARIABLES [LIKE wild] or SHOW PROCESSLIST
SHOW provides information about databases, tables, columns or the
server. If the LIKE wild part is used, the wild string should
be a normal SQL wildcard string that uses the `%' and `_'
wildcard characters.
Instead of using tbl_name FROM db_name syntax, you can also use
db_name.tbl_name. These two statements are equivalent:
mysql> SHOW INDEX FROM mytable FROM mydb; mysql> SHOW INDEX FROM mydb.mytable;
SHOW DATABASES lists the databases on the MySQL server
host. You can also get this list using the mysqlshow command.
SHOW TABLES lists the tables in a given database. You can also
get this list using the mysqlshow db_name command.
NOTE: If a user doesn't have any privileges for a table, the table
will not show up when requesting a list of tables with SHOW TABLES or
mysqlshow db_name.
SHOW FIELDS is a synonym for SHOW COLUMNS and
SHOW KEYS is a synonym for SHOW INDEX. You can also
list a table's columns or indexes with mysqlshow db_name tbl_name
or mysqlshow -k db_name tbl_name.
SHOW STATUS provides server status information
(like mysqladmin extended-status). The output resembles that shown
below, though the format and numbers may differ somewhat:
+------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Aborted_clients | 0 | | Aborted_connects | 0 | | Created_tmp_tables | 0 | | Deletes | 0 | | Flush_commands | 1 | | Key_blocks_used | 1 | | Key_read_requests | 2 | | Key_reads | 1 | | Key_write_requests | 0 | | Key_writes | 0 | | Not_flushed_key_blocks | 0 | | Open_tables | 1 | | Open_files | 2 | | Open_streams | 0 | | Opened_tables | 7 | | Questions | 9 | | Read_key | 1 | | Read_next | 1 | | Read_rnd | 28 | | Read_first | 5 | | Running_threads | 1 | | Slow_queries | 0 | | Uptime | 45768 | | Write | 0 | +------------------------+-------+
SHOW VARIABLES shows the values of the some of MySQL system
variables. You can also get this information using the mysqladmin
variables command. If the default values are unsuitable, you can set most
of these variables using command-line options when mysqld starts up.
SHOW PROCESSLIST shows you which threads are running. You can also
get this information using the mysqladmin processlist command. If you
do not have the process privilege, you can see only your own
threads.
See section 7.19 KILL syntax.
EXPLAIN syntax (Get information about a SELECT)EXPLAIN SELECT select_options
When you precede a SELECT statement with the keyword EXPLAIN,
MySQL explains how it would process the SELECT, providing
information about how tables are joined and in which order.
With the help of EXPLAIN, you can see when you must add indexes
to tables to get a faster SELECT that uses indexes to find the
records. You can also see if the optimizer joins the tables in an optimal
order. To force the optimizer to use a specific join order for a
SELECT statement, add a STRAIGHT_JOIN clause.
For non-simple joins, EXPLAIN returns a row of information for each
table used in the SELECT statement. The tables are listed in the order
they would be read. MySQL resolves all joins using a one-sweep
multi-join method. This means that MySQL reads a row from
the first table, then finds a matching row in the second table, then in the
third table and so on. When all tables are processed, it outputs the selected
columns and the table list is back-tracked until a table is found for which
there are more matching rows. The next row is read from this table and the
process continues with the next table.
Output from EXPLAIN includes the following columns:
table
type
possible_keys
possible_keys column indicates which indexes MySQL could
use to find the rows in the table. If this column is empty, there are no
relevant indexes. In this case, you may be able to improve the performance
of your query by examining the WHERE clause to see if it refers to
some column or columns that would be suitable for indexing. If so, create an
appropriate index and check the query with EXPLAIN again.
To see what indexes a table has, use SHOW INDEX FROM tbl_name.
key
key column indicates the key that MySQL actually
decided to use. The key is NULL if no index was chosen.
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.
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 must
examine to execute the query.
Extra
Extra column includes the text Only index, this means
that only information from the index tree is used to retrieve information from
the table (which should be much faster than scanning the entire table).
If the Extra column includes the text where used, it means that
a WHERE clause will be used to restrict which rows will be matched
against the next table or sent to the client.
The different join types are listed below, ordered from best to worst type:
system
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
ref column indicates which index is used.
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 10.1 Changing the size of MySQL buffers
The following example shows how a JOIN can be optimized progressively
using the information provided by EXPLAIN.
Suppose you have the SELECT statement shown below, that you examine
using EXPLAIN:
EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
tt.ProjectReference, tt.EstimatedShipDate,
tt.ActualShipDate, tt.ClientID,
tt.ServiceCodes, tt.RepetitiveID,
tt.CurrentProcess, tt.CurrentDPPerson,
tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
et_1.COUNTRY, do.CUSTNAME
FROM tt, et, et AS et_1, do
WHERE tt.SubmitTime IS NULL
AND tt.ActualPC = et.EMPLOYID
AND tt.AssignedPC = et_1.EMPLOYID
AND tt.ClientID = do.CUSTNMBR;
For this example, assume that:
| 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)
This output indicates that MySQL is doing a full join for all
tables---type is ALL for each table! This will take quite a
long time, as the product of the number of rows in each table must be
examined! For the case at hand, this is 74 * 2135 * 74 * 3872 =
45,268,558,720 rows. If the tables were bigger, you can only imagine how
long it would take...
One problem here is that MySQL can't (yet) use indexes on columns
efficiently if they are declared differently. VARCHAR and CHAR
are not different in this context, unless they are not declared to be the
same length. Since tt.ActualPC is declared as CHAR(10) and
et.EMPLOYID is declared as CHAR(15), there is a length
mismatch.
To fix this disparity between column lengths, use ALTER TABLE to
lengthen ActualPC from 10 characters to 15 characters:
mysql> ALTER TABLE tt CHANGE ActualPC ActualPC VARCHAR(15);
Now tt.ActualPC and et.EMPLOYID are both VARCHAR(15).
Executing the EXPLAIN statement again produces this result:
table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 where used
do ALL PRIMARY NULL NULL NULL 2135
range checked for each record (key map: 1)
et_1 ALL PRIMARY NULL NULL NULL 74
range checked for each record (key map: 1)
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
This is not perfect, but is much better (the product of the rows
values is now less by a factor of 74). This version is executed in a couple
of seconds.
A second alteration can be made to eliminate the column length mismatches
in the tt.AssignedPC = et_1.EMPLOYID and tt.ClientID =
do.CUSTNMBR comparisons:
mysql> ALTER TABLE tt CHANGE AssignedPC AssignedPC VARCHAR(15),
CHANGE ClientID ClientID VARCHAR(15);
Now EXPLAIN produces the output shown below:
table type possible_keys key key_len ref rows Extra et ALL PRIMARY NULL NULL NULL 74 tt ref AssignedPC,ClientID,ActualPC ActualPC 15 et.EMPLOYID 52 where used et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1 do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
This is "almost" as good as it can get.
The remaining problem is that, by default, MySQL assumes that values
in the tt.ActualPC column are evenly distributed, and that isn't the
case for the tt table. Fortunately, it is easy to tell MySQL
about this:
shell> isamchk --analyze PATH_TO_MYSQL_DATABASE/tt shell> mysqladmin refresh
Now the join is "perfect", and EXPLAIN produces this result:
table type possible_keys key key_len ref rows Extra tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 where used et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1 et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1 do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
DESCRIBE syntax (Get information about columns)
{DESCRIBE | DESC} tbl_name {col_name | wild}
DESCRIBE provides information about a table's columns. col_name
may be a column name or a string containing the SQL `%' and `_'
wildcard characters.
This statement is provided for Oracle compatibility.
The SHOW statement provides similar information.
See section 7.20 SHOW syntax (Get information about tables, columns...).
LOCK TABLES/UNLOCK TABLES syntax
LOCK TABLES tbl_name [AS alias] {READ | [LOW_PRIORITY] WRITE}
[, tbl_name {READ | [LOW_PRIORITY] WRITE} ...]
...
UNLOCK TABLES
LOCK TABLES locks tables for the current thread. UNLOCK TABLES
releases any locks held by the current thread. All tables that are locked by
the current thread are automatically unlocked when the thread issues another
LOCK TABLES, or when the connection to the server is closed.
If a thread obtains a READ lock on a table, that thread (and all other
threads) can only read from the table. If a thread obtains a WRITE
lock on a table, then only the thread holding the lock can READ from
or WRITE to the table. Other threads are blocked.
Each thread waits (without timing out) until it obtains all the locks it has requested.
WRITE locks normally have higher priority than READ locks, to
ensure that updates are processed as soon as possible. This means that if one
thread obtains a READ lock and then another thread requests a
WRITE lock, subsequent READ lock requests will wait until the
WRITE thread has gotten the lock and released it. You can use
LOW_PRIORITY WRITE locks to allow other threads to obtain READ
locks while the thread is waiting for the WRITE lock. You should only
use LOW_PRIORITY WRITE locks if you are sure that there will
eventually be time when there are no threads that have a READ lock.
When you use LOCK TABLES, you must lock all tables that you are
going to use! If you are using a table multiple times in a query (with
alias), you have to get a lock for each alias! This policy ensures that
table locking is deadlock free.
Normally, you don't have to lock tables, as all single UPDATE statements
are atomic; no other thread can interfere with any other currently executing
SQL statement. There are a few cases when you would like to lock tables
anyway:
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. For example, the example shown below
requires LOCK TABLES in order to execute safely! 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:
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;
By using incremental updates (UPDATE customer set value=value+new_value)
or the LAST_INSERT_ID() function you can avoid using LOCK TABLES
in many cases.
You can also solve some cases by using the user-level lock functions
GET_LOCK() and RELEASE_LOCK(). These locks are saved in a hash
table in the server and implemented with pthread_mutex_lock() and
pthread_mutex_unlock() for high speed.
See section 7.3.11 Miscellaneous functions.
See section 10.9 How MySQL locks tables, for more information on locking policy.
SET OPTION 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.
The options are:
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");
SQL_BIG_TABLES= 0 | 1
The table tbl_name is full for big SELECT operations that
require a large temporary table. The default value for a new connection is
0 (i.e., use in-memory temporary tables).
SQL_BIG_SELECTS= 0 | 1
1, MySQL will abort if a SELECT is attempted
that probably will take a very long time. This is useful when an inadvisable
WHERE statement has been issued. A big query is defined as a
SELECT that probably will have to examine more than
max_join_size rows. The default value for a new connection is
0 (which will allow all SELECT statements).
SQL_LOW_PRIORITY_UPDATES= 0 | 1
1, all INSERT, UPDATE and DELETE
statements wait until there is no pending SELECT on the affected
table.
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_UPDATE_LOG= 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!
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 command when inserting
an AUTO_INCREMENT value. This is mainly used with the update log.
GRANT and REVOKE syntax
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY 'password']
[, user_name [IDENTIFIED BY 'password'] ...]
[WITH GRANT OPTION]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
FROM user_name [, user_name ...]
GRANT is implemented in MySQL 3.22.11 and up. For
earlier MySQL versions, the GRANT statement does nothing.
The purpose of the GRANT and REVOKE commands is to
enable system administrators to grant and revoke rights to MySQL
users at four privilege levels:
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.9 Adding new user privileges to MySQL.
For the GRANT and REVOKE statements,
priv_type may be specified as
any of the following:
ALL PRIVILEGES FILE RELOAD ALTER INDEX SELECT CREATE INSERT SHUTDOWN DELETE PROCESS UPDATE DROP REFERENCES USAGE
ALL is a synonym for ALL PRIVILEGES.
REFERENCES is not yet implemented.
USAGE is currently a synonym for "no privileges". It can be
used when you want to create a user that has no privileges.
To revoke the grant privilege from a user, use a priv_type
value of GRANT OPTION:
REVOKE GRANT OPTION ON ... FROM ...;
The only priv_type values you can specify for a table are SELECT,
INSERT, UPDATE, DELETE, CREATE, DROP,
GRANT, INDEX and ALTER.
The only priv_type values you can specify for a column (that is, when
you use a column_list clause) are SELECT, INSERT and
UPDATE.
You can set database privileges by using ON db_name.* syntax. If you
specify ON * and you have a current database, you will set the
privileges for that database. If you specify ON * and you don't have
a current database, or if you specify ON *.*, you will affect the
global privileges.
In order to accommodate granting rights to users from other hosts,
MySQL supports specifying the user_name value in the form
user@host. If you want to specify a user_name value
containing wildcard characters or special characters (such as `%'), you
can quote the user or host name (e.g., 'test-user'@'test-hostname').
You can specify wildcards in the hostname. For example,
user@"%.loc.gov" applies to user for any host in the
loc.gov domain, and user@"144.155.166.%" applies to user
for any host in the 144.155.166 class C subnet.
The simple form user is a synonym for user@"%".
NOTE: If you allow anonymous users (this is the default) to
connect to the MySQL server, you should add all local user with
username@localhost because else the anonymous user will be used when
the user tries to log into the MySQL server from the same
machine! Anonymous users is defined by inserting entries with user="
into the mysql.user table. You can verify if this applies to you
by executing:
select host,user from mysql.user where user=";
For the moment, GRANT only supports host, table, database and
column names up to 60 characters long. A user name can be up to 16
characters.
The total access rights for a table or column are formed from the
logical OR of the privileges at each of the four privilege
levels. For example, if the mysql.user table specifies that a
user has a global select privilege, this can't be denied by an
entry at the database, table or column level.
The total rights for a column can be calculated as follows:
global rights OR (database rights AND host rights) OR table rights OR column rights
In most cases, you grant rights to a user at one of the different privilege levels, so life isn't normally as complicated as above. :) The details of the access-right checking procedure are presented in section 6 The MySQL access privilege system.
If you grant privileges for a user/hostname combination that does not exist
in the mysql.user table, an entry is added and remains there
until deleted with a DELETE command.
In MySQL 3.22.12 and up,
if a new user is created or if you have global grant privileges, the user's
password will be set to the password specified by the IDENTIFIED BY
clause, if one is given. If the user already had a password, it is replaced
by the new one.
Passwords can also be set with the SET PASSWORD command.
See section 7.24 SET OPTION syntax.
If you grant privileges for a database, an entry in the mysql.db
table is created if needed. When all privileges for the database have been
removed with REVOKE, this entry is deleted.
If a user doesn't have any privileges on a table, the table is not displayed
when the user requests a list of tables (e.g., with a SHOW TABLES
statement).
The WITH GRANT OPTION clause gives the user the ability to give
to other users any privileges the user has at the specified privilege level.
You should be careful to whom you give the grant privilege, as two
users with different privileges may be able to join privileges!
You cannot grant another user a privilege you don't have yourself; the grant privilege allows you to give away only those privileges you possess.
Be aware that when you grant a user the grant privilege at a
particular privilege level, any privileges the user already possesses (or
is given in the future!) at that level are also grantable by that user.
Suppose you grant a user the insert privilege on a database. If
you then grant the select privilege on the database and specify
WITH GRANT OPTION, the user can give away not only the select
privilege, but also insert. If you then grant the update
privilege to the user on the database, the user can give away the
insert, select and update.
You should not grant alter privileges to a normal user. If you do that, the user can try to subvert the privilege system by renaming tables!
Note that if you are using table/column privileges for even one user,
MySQL examines table/column privileges for all users and this will
slow down mysqld a bit.
When mysqld starts, all privileges are read into memory.
Database, table and column privileges take effect at once and
user level privileges take effect the next time the user connects.
Modifications to the grant tables that you perform using GRANT or
REVOKE are noticed by the server immediately.
If you modify the grant tables manually (using INSERT, UPDATE,
etc.), you should execute a FLUSH PRIVILEGES statement or run
mysqladmin flush-privileges to tell the server to reload the grant
tables.
See section 6.7 When privilege changes take effect.
The biggest differences between the ANSI SQL and MySQL versions of
GRANT are:
REVOKE commands or by manipulating the
MySQL grant tables.
CREATE INDEX syntax (Compatibility function)CREATE [UNIQUE] INDEX index_name ON tbl_name (col_name[(length]),... )
The CREATE INDEX statement doesn't do anything in MySQL prior
to version 3.22. In 3.22 or later, CREATE INDEX is mapped to an
ALTER TABLE call to create indexes.
See section 7.7 ALTER TABLE syntax.
Normally, you create all indexes on a table at the time the table itself
is created with CREATE TABLE. See section 7.6 CREATE TABLE syntax. CREATE
INDEX allows you to add indexes to existing tables.
A column list of the form (col1,col2,...) creates a multiple-column
index. Index values are formed by concatenating the values of the given
columns.
For CHAR and VARCHAR columns, indexes can be created that
use only part of a column, using col_name(length) syntax. The
statement shown below creates an index using the first 10 characters
of the name column:
mysql> CREATE INDEX part_of_name ON customer (name(10));
Use of partial columns for indexes can make the index file much smaller.
Since most names usually differ in the first 10 characters, this index should
not be much slower than an index created from the entire name column,
it could save a lot of disk space and might also speed up INSERT
operations!
For more information about how MySQL uses indexes, see section 10.4 How MySQL uses indexes.
DROP INDEX syntax (Compatibility function)DROP INDEX index_name
DROP INDEX
doesn't do anything in MySQL prior to version 3.22.
In 3.22 or later, DROP INDEX
is mapped to an ALTER TABLE call to drop the INDEX or
UNIQUE definition.
See section 7.7 ALTER TABLE syntax.
MySQL supports the # to end of line and /* in-line or
multiple-line */ comment styles:
mysql> select 1+1; # This comment continues to the end of line mysql> select 1 /* this is an in-line comment */ + 1; mysql> select 1+ /* this is a multiple-line comment */ 1;
MySQL doesn't support the `--' ANSI SQL comment style. See section 5.2.7 `--' as the start of a comment.
CREATE FUNCTION/DROP FUNCTION syntax
CREATE FUNCTION function_name RETURNS {STRING|REAL|INTEGER}
SONAME shared_library_name
DROP FUNCTION function_name
A user-definable function (UDF) is a way to extend MySQL with a new
function that works like native (built in) MySQL functions such as
ABS() and CONCAT().
CREATE FUNCTION saves the function's name, type and shared library
name in the system table func in the mysql database. You must
have the insert and delete privileges for the mysql
database to create and drop functions.
All active functions are reloaded each time the server starts, unless
you start mysqld with the --skip-grant-tables option. In
this case, UDF initialization is skipped and UDFs are unavailable.
(An active function is one that has been loaded with CREATE FUNCTION
and not removed with DROP FUNCTION.)
For the UDF mechanism to work, functions must be written in C or C++ and your operating system must support dynamic loading.
For information on how to write user-definable functions, see section 14 Adding new functions to MySQL.
A common problem stems from trying to create a table with column names like
TIMESTAMP or GROUP, the names of datatypes and functions
built into MySQL. You're allowed to do it (for example,
ABS is an allowed column name), but whitespace is not allowed
between a function name and the `(' when using functions
whose names are also column names.
The following words are explicitly reserved in MySQL. Most of
them are forbidden by ANSI SQL92 as column and/or table names
(for example, group).
A few are reserved because MySQL needs them and is
(currently) using a yacc parser:
action | add | all | alter
|
after | and | as | asc
|
auto_increment | between | bigint | bit
|
binary | blob | bool | both
|
by | cascade | char | character
|
change | check | column | columns
|
constraint | create | cross | current_date
|
current_time | current_timestamp | data | database
|
databases | date | datetime | day
|
day_hour | day_minute | day_second | dayofmonth
|
dayofweek | dayofyear | dec | decimal
|
default | delete | desc | describe
|
distinct | distinctrow | double | drop
|
escaped | enclosed | enum | explain
|
exists | fields | first | float
|
float4 | float8 | foreign | from
|
for | full | function | grant
|
group | having | hour | hour_minute
|
hour_second | ignore | in | index
|
infile | insert | int | integer
|
interval | int1 | int2 | int3
|
int4 | int8 | into | if
|
is | join | key | keys
|
last_insert_id | leading | left | like
|
lines | limit | load | lock
|
long | longblob | longtext | low_priority
|
match | mediumblob | mediumtext | mediumint
|
middleint | minute | minute_second | month
|
monthname | natural | numeric | no
|
not | null | on | option
|
optionally | or | order | outer
|
outfile | partial | password | precision
|
primary | procedure | processlist | privileges
|
quarter | read | real | references
|
rename | regexp | reverse | repeat
|
replace | restrict | returns | rlike
|
second | select | set | show
|
smallint | soname | sql_big_tables | sql_big_selects
|
sql_select_limit | sql_low_priority_updates | sql_log_off | sql_log_update
|
straight_join | starting | status | string
|
table | tables | terminated | text
|
time | timestamp | tinyblob | tinytext
|
tinyint | trailing | to | use
|
using | unique | unlock | unsigned
|
update | usage | values | varchar
|
variables | varying | varbinary | with
|
write | where | year | year_month
|
zerofill
|
The following symbols (from the table above) are disallowed by ANSI SQL but allowed by MySQL as column/table names. This is because some of these names are very natural names and a lot of people have already used them.
ACTION
BIT
DATE
ENUM
NO
TEXT
TIME
TIMESTAMP
Go to the first, previous, next, last section, table of contents.