A string may have ' or " around it.
\ is a escape character. The following escape characters are recognised:
\0
\n
\t
\r
\b
\'
'
character.
\"
"
character.
\\
\
character.
\%
%
character. This is used in wildcard strings to search after
%
.
\_
_
character. This is used in wildcard strings to search after
_
.
A ' inside a string started with ' may be written as ". A " inside a string started with " may be written as "".
Some example selects that shows how it works.
MySQL> select 'hello', "'hello'", '""hello""', "'h"e"l"l"o"', "hel""lo"; 1 rows in set (0.00 sec) +-------+---------+-----------+-------------+--------+ | hello | 'hello' | ""hello"" | 'h'e'l'l'o' | hel"lo | +-------+---------+-----------+-------------+--------+ | hello | 'hello' | ""hello"" | 'h'e'l'l'o' | hel"lo | +-------+---------+-----------+-------------+--------+
mysql> select 'hello', "hello", '""hello""', "'ello", 'e"l"lo', '\'hello'; 1 rows in set (0.00 sec) +-------+-------+-----------+-------+--------+--------+ | hello | hello | ""hello"" | 'ello | e'l'lo | 'hello | +-------+-------+-----------+-------+--------+--------+ | hello | hello | ""hello"" | 'ello | e'l'lo | 'hello | +-------+-------+-----------+-------+--------+--------+
mysql> select "This\nIs\nFour\nlines"; 1 rows in set (0.00 sec) +--------------------+ | This Is Four lines | +--------------------+ | This Is Four lines | +--------------------+
If you want to insert binary data into a blob the following characters must be represented by escape sequences:
\0
\
'
"
Integers are just a sequence of digits. Floats use .
as a decimal
separator.
Examples of valid numbers are: 1221
, 294.42
,
-32032.6809e+10
.
NULL
When using the text file export formats, NULL
may be represented
by \N
. See section LOAD DATA INFILE syntax
Database, table, index and column names all follow the same rules in MySQL.
A name may use alphanumeric characters from the default character set. This is by default ISO-8859-1 Latin1 but may be changed when compiling MySQL.
Since MySQL needs to be able to decide if something is a name or a number the following special cases occurs.
1e
. This is because
expressions like 1e+1
may be interpreted like the expression
1e + 1
or the number 1e+1
.
So punctuation characters like .
and @
are not allowed
since they will be used to extend MySQL.
The following column types are supported:
Name | Description | Size |
TINYINT[(D)] [UNSIGNED] [ZEROFILL] | A very small integer. Signed range -128 - 127. Unsigned range 0 - 255. | 1 |
SMALLINT[(D)]. [UNSIGNED] [ZEROFILL] | A small integer. Signed range -32768 - 32767. Unsigned range 0 - 65535. | 2 |
MEDIUMINT[(D)] [UNSIGNED] [ZEROFILL] | A medium integer. Signed range -8388608-8388607. Unsigned range 0 - 16777215. | 3 |
INT[(D)] [UNSIGNED] [ZEROFILL] | A normal integer. Signed range -2147483648 - 2147483647. Unsigned range 0 - 4294967295. | 4 |
BIGINT[(D)] [UNSIGNED] [ZEROFILL] | A large integer. Signed range -9223372036854775808 - 9223372036854775807. Unsigned Range 0 - 18446744073709551615. | 8 |
FLOAT(Precision) | A small floating point number. Precision can be 4 or 8. FLOAT(4) is a single precision number and FLOAT(8) is a double precision number (se the DOUBLE entry). This syntax is for ODBC compatibility. Range -3.402823466E+38F - -1.175494351E-38, 0, -1.175494351E-38 - 3.402823466E+38F. | 4 |
FLOAT[(M,D)] | A small floating point number. Cannot be unsigned. Range -3.402823466E+38F - -1.175494351E-38, 0, -1.175494351E-38 - 3.402823466E+38F. | 4 |
DOUBLE PRECISION[(M,D)] | A normal floating point number. Cannot be unsigned. Range -1.7976931348623157E+308 - -2.2250738585072014E-308, 0, 2.2250738585072014E-308 - 1.7976931348623157E+308. | 8 |
REAL[(M,D)] | Same as DOUBLE | 8 |
DECIMAL [(M,D)] | An unpacked floating point number. Cannot be unsigned. Currently the same range maximum range as a double. Behaves as a CHAR column | M+D |
NUMERIC [(M,D)] | Same as DECIMAL | M+D |
TIMESTAMP [(M)] | An automatic timestamp. | 4 |
DATE | A type to store date information. Uses the "YYYY-MM-DD" syntax, but may be updated with a number or a string. Understands at least the following syntaxes: 'YY-MM-DD', 'YYYY-MM-DD', 'YYMMDD', 'YYMM', 'YY'. Range 0000-00-00 to 9999-12-31. | 4 |
TIME | A type to store time information. Uses the "HH:MM:SS" syntax, but may be updated with a number or a string. Understands at least the following syntaxes: 'HH:MM:DD, 'HHMMDD', 'HHMM', 'HH'. | 3 |
DATETIME | A type to store date and time information. Format "YYYY-MM-DD HH:MM:SS". Takes 8 bytes. Range '0000-01-01 00:00:00' - '9999-12-31 23:59:59'. | 8 |
CHAR(M) [binary] | A fixed length string that is always filled up with spaces to the specified length. Range 1 - 255 characters. All end space are removed when retrieved. Is sorted and compared case insensitively unless the binary keyword is given. | M |
VARCHAR(M) [binary] | A variable length string that is stored with its length. Maximum range 1 - 255 characters. Is sorted and compared case insensitively unless the binary keyword is given. | L+1 |
TINYTEXT and TINYBLOB |
A TEXT /BLOB with max length of 255 characters. | L+1 |
TEXT and BLOB |
A TEXT /BLOB with max length of 65535 characters. | L+2 |
MEDIUMTEXT and MEDIUMBLOB |
A TEXT /BLOB with max length of 16777216 characters. | L+3 |
LONGTEXT and LONGBLOB |
A TEXT /BLOB with max length of 4294967295 characters. | L+4 |
ENUM('value','value2',...) | A string object that can have only one set of allowed values. See section More about data types. | 1 or 2 |
SET('value','value2',...) | A string object that can have one or many values of a set of allowed values. See section More about data types. | 1-8 |
In the above table L means the actual length of a instance and M the maximum length. So L+1 for "abcd" means 5 bytes in the database.
If you use any data type with an L in the length field you will get a variable length record format.
All integer types can have an optional argument unsigned
. This
can be used when you only want to allow positive numbers in the column
or you need a little bigger numerical range for the column.
Also for all integer columnsn, the optional argument ZEROFILL
means
that the column will be padded with zeroes up to the maximum length.
Max display size and decimals are for formatting and calculation of maximum column width.
When storing a value in an integer that is outside its range,
MySQL stores the maximum (or minimum) possible value. When
doing an ALTER TABLE
or LOAD DATA INFILE
one gets these
conversions as 'warnings'. We have on the TODO to make INSERT and
UPDATE also can return warnings, but this is scheduled for the next
protocol change.
For example when storing -999999999999999
into an int column the
value ends up as -2147483648
. And 9999999999999999
ends up
as 2147483647
.
And if the int is unsigned the stored values above becomes 0
and
4294967296
.
The same rules goes for all other integer types.
When returning data for an int(4) column that does not fit in it
MySQL will return 9.99. And is an UPDATE
a warning will
be issued.
Note that a type like decimal(4,2)
means maximum 4 characters
with two decimal points. That gives a range between -.99
->
9.99
.
To avoid some rounding problems, MySQL always rounds everything
that it stores in any floating point column according to the number of
decimals. This means that 2.333
stored into float(8,2)
is
stored as 2.33
.
TIMESTAMP
type
Has a range of 1 Dec 1970 time 0.00 to sometime in the year 2106 and a
resolution of one second. Will be automatically updated if not used in a
statement that updates a row or if set to NULL
. Can be (part
of) an index. Note that if you have many timestamp columns in a row, then
only the first timestamp column will be automatically updated. Any
timestamp column will be set to the current time if set to
NULL
. Depending on the display size one gets one of the following
formats: "YYYY-MM-DD HH:MM:SS", "YY-MM-DD HH:MM:SS", "YYYY-MM-DD" or
"YY-MM-DD".
TEXT
and BLOB
types
These are objects that can have a variable length without upper limit.
All TEXT and BLOB objects are stored with is length (saved in 1 to 4
bytes depending on the type of object). The maximum TEXT
and
BLOB
length you can use is dependent on available memory and
client buffers. The only differences between TEXT
and BLOB
is that TEXT
is sorted and compared case insensitively while
BLOB
is compared case sensitively (by character
values). TEXT
and BLOB
objects CANNOT be an index.
A BLOB is a binary large object which can hold any amount of data. There are 4 kinds of blobs See section Column types.. Normally one can regard a BLOB as a VARCHAR without a specified limit.
TEXT
is a BLOB
that is sorted and compared case
insensitively.
A BLOB
/TEXT
column may not be bigger that the message
buffer. Note that you have to change the message buffer on both the
server and the client. See section How does one change the size of MySQL buffers?.
MyODBC
defines BLOB
s as LONGVARBINARY
and
TEXT
s as LONGVARCHAR
.
Restrictions for BLOB
and TEXT
columns:
BLOB
or TEXT
cannot be an index or a part of an index
BLOB
or TEXT
, only the first
max_sort_length
(default 1024) of the blob is used. This value
can be changed by the -O
option when starting the mysqld
daemon. One can group on an expression involving a BLOB
/
TEXT
: SELECT id,SUBSTR(blob,1,100) GROUP BY 2
BLOB
and TEXT
as
there is for CHAR
and VARCHAR
.
ENUM
type
A string object that can have only one set of allowed values. The value
to be stored may be given case independently. If one tries to store a
non-existing value, "" is stored. If used in a number context this
object returns/stores the value index. If there is less than 255
possible values this object occupies 1 byte, else two bytes (with a
maximum of 65535 different values). Note that if an integer is put in the
ENUM
you get the corresponding string with the first counting as
number zero. Sorting on ENUM
types is done after the order of the
strings in the enum.
For example the column test ENUM("one","two", "three")
can have
any of these values:
"one" "two" "three"
SET
type
A string object that can have one or many values from a set of allowed
values. Each value is separated by a ','. If used in a number context
this object returns/stores the bit positions of the used values. This
object occupies (number_of_different_values-1)/8+1 bytes, rounded up to
1,2,3,4 or 8. One can't have more than 64 different values. Note that if
an integer is put in the SET
you get the corresponding string with
the first bit corresponding to the first string. Sorting on SET
types is done numerically.
For example the column test SET("one","two")
can have any of
these values:
"" "one" "two" "one,two"
Try to use the most precise type in all cases. For example for an integer
between 1-99999 a unsigned mediumint
is the best type.
A common problem is representing monetary values accurately. 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 to
important the DOUBLE
type may also be good enough.
For high precision you can always convert to a fixed point type
stored in a BITINT
. This allows you to do all calculation with
integers and only convert the result back to floating point.
See section What are the different row formats? Or when to use VARCHAR/CHAR?.
You can have indexes on all MySQL columns except BLOB
and TEXT
types. Using indexes on the relevant columns is the most
important thing in getting your selects to run fast.
For CHAR
and VARCHAR
columns you can have an index on a
prefix. The example below show how to create an index for the first 10
characters of a column. This is much faster and requires less disk space
than having an index on the whole column.
CREATE TABLE test ( name CHAR(200), KEY index_name (name(10));
MySQL can have one index on parts of different columns.
A multiple-column index can be considered a sorted array where the columns are concatenated. This makes for fast queries where the first column in the index is a known quantity and the other columns are not.
Suppose that you have a table:
CREATE TABLE test ( id INT, last_name CHAR(30), first_name CHAR(30), PRIMARY KEY (id), INDEX name (last_name,first_name));
Then the index name
is an index over last_name and first_name.
The name
index will be used in the following queries:
SELECT * FROM test WHERE last_name="Widenius"; SELECT * FROM test WHERE last_name="Widenius" AND first_name="Michael"; SELECT * FROM test WHERE last_name="Widenius" AND (first_name="Michael" OR first_name="Monty"); SELECT * FROM test WHERE last_name="Widenius" and first_name >="M" and first_name < "N";
The name
index will NOT be used in the following queries:
SELECT * FROM test WHERE first_name="Michael"; SELECT * FROM test WHERE last_name="Widenius" or first_name="Michael";
To support easier use of code from different SQL vendors, MySQL does supports the following mappings:
binary(num) | char(num) binary |
char varying | varchar |
float4 | float |
float8 | double |
int1 | tinyint |
int2 | smallint |
int3 | mediumint |
int4 | int |
int8 | bigint |
long varbinary | blob |
long varchar | text |
middleint | mediumint |
varbinary(num) | varchar(num) binary |
SELECT
and WHERE
clauses
A select_expression
or where_definition
can consist of any
expression using the following functions:
In the examples below the output of the mysql
program has been
shortened. So this:
mysql> select mod(29,9); 1 rows in set (0.00 sec) +-----------+ | mod(29,9) | +-----------+ | 2 | +-----------+
Has been converted to:
mysql> select mod(29,9); -> 2
(
)
mysql> select 1+2*3; -> 7 mysql> select (1+2)*3; -> 9
+
-
*
/
NULL
.
mysql> select 102/(1-1); -> NULL
These have a range of maximum 64 bits because MySQL uses bigint (64 bit) arithmetic.
|
mysql> select 29 | 15; -> 31
&
mysql> select 29 & 15; -> 13
BIT_COUNT()
mysql> select bit_count(29); -> 4
All logical function return 1 (TRUE) or 0 (FALSE).
NOT
!
mysql> select NOT 1; -> 0 mysql> select NOT NULL; -> NULL mysql> select ! (1+1); -> 0 mysql> select ! 1+1; -> 1
OR
||
mysql> select 1 || 0; -> 1 mysql> select 0 || 0; -> 0 mysql> select 1 || NULL; -> 1
AND
&&
mysql> select 1 && NULL; -> 0 mysql> select 1 && 0; -> 0
Returns 1 (TRUE), 0 (FALSE) or NULL
. These functions work for both
numbers and strings. MySQL uses the following rules to decide how the
compare is done:
TIMESTAMP
or DATETIME
column and
the other argument is a constant. In this case the constant is converted
to a timestamp before the comparasion. This is to be more ODBC
friendly.
If one or both of the arguments are NULL
the result of the comparison
is NULL
.
=
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(A)
A
is NULL
else 0.
mysql> select isnull(1+1); -> 0 mysql> select isnull(1/0); -> 1
A BETWEEN B AND C
A
is bigger or equal as B
and A
is smaller or equal
to C
. Does the same thing as (A >= B AND A <= C)
if
all arguments are of the same type. It's the first argument (A
)
that decides how the comparison should be done! If A
is a string
expression, compare as case insensitive strings. If A
is a binary
string, compare as binary strings. If A
is an integer expression
compare as integers, else compare as reals.
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,...)
IN
list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for them item is
then done by using a binary search. This means IN
is very quick
when used with constants in the IN
part.
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,...))
expr LIKE expr
LIKE
you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one % .
|
\_ | Matches one _ .
|
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
LIKE
is allowed on numerical expressions!
NOT (expr LIKE expr)
.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1
NOT (expr REGEXP expr)
.
mysql> select strcmp('text', 'text2'); -> -1 mysql> select strcmp('text2', 'text'); -> 1 mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A
is not NULL
it returns A
, else B
.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A
is true (A <> 0
and A <> NULL
) then return
B
, else return C
.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL
in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(-1.23); -> -1 mysql> select ceiling(1.23); -> 2
ROUND(N)
N
to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number
to a number with Decimals
decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e
(the base of natural logarithms) raised to
the power of N
.
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
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
. If X
is not in the range -1 to 1 NULL
is
returned.
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
. If X
is not in the range -1 to 1 NULL
is
returned.
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(N)
N
.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0
, using the integer
expression X
as the optional 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.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY
functions. The arguments are compared as numbers. If no
records are found NULL
is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY
functions. The arguments are compared as numbers. If no
records are found NULL
is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N
converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N
converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number
to Decimals
decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S
. If
S
is NULL
return NULL
.
mysql> SELECT ascii(2); -> 50 mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs
are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4 mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A
substring in B
. The first position
is 1. Returns 0 if A
is not in B
.
mysql> select locate('bar', 'foobarbar'); -> 4 mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B
in string A
. This is
the same as LOCATE
with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4 mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A
in string B
starting
at C
.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B
characters from end of string A
.
mysql> select right('foobarbar', 5); -> 'arbar' mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A
prefixes and/or suffixes
removed from B
. If BOTH
, LEADING
and
TRAILING
isn't used BOTH
are assumed. If A
is not
given, then 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(S)
S
. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One 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 vocals.
mysql> select soundex('Hello'); -> 'H400' mysql> select soundex('Bättre'); -> 'B360' mysql> select soundex('Quadratically'); -> 'Q36324'
SUBSTRING(A, B, C)
SUBSTRING(A FROM B FOR C)
MID(A, B, C)
A
starting at B
with C
chars. The variant with FROM
is ANSI SQL 92 syntax.
mysql> select substring('Quadratically',5,6); -> ratica
SUBSTRING_INDEX(String, Delimiter, Count)
String
after Count
Delimiters
. If Count
is positive the strings are searched
from left else if count is negative the substrings are searched and
returned from right.
mysql> select substring_index('www.tcx.se', '.', 2); -> 'www.tcx' mysql> select substring_index('www.tcx.se', '.', -2); -> 'tcx.se'
SPACE(N)
N
spaces.
mysql> select SPACE(6); -> ' '
REPLACE(A, B, C)
B
in string A
with
string C
.
mysql> select replace('www.tcx.se', 'w', 'Ww'); -> 'WwWwWw.tcx.se'
REPEAT(String, Count)
String
Count
times. If Count <= 0
returns a
empty string. If String
or Count
is NULL
or
LENGTH(string)*count > max_allowed_size
returns NULL
.
mysql> select repeat('MySQL', 3); -> 'MySQLMySQLMySQL'
REVERSE(String)
mysql> select reverse('abc'); -> 'cba'
INSERT(Org, Start, Length, New)
Org
starging at Start
and
Length
long with New
. First position in Org
is
numbered 1.
mysql> select insert('Quadratic', 3, 4, 'What'); -> 'QuWhattic'
INTERVAL(N, N1, N2, N3...)
Nn
> N3
> N2
> N1
is
this function shall work. This is because a binary search is used (Very
fast). Returns 0 if N
< N1
, 1 if N
< N2
and
so on. All arguments are treated as numbers.
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
ELT(N, A1, A2, A3...)
A1
if N
= 1, A2
if N
= 2 and so
on. If N
is less than 1 or bigger than the number of arguments
NULL
is returned.
mysql> select elt(1, 'ej', 'Heja', 'hej', 'foo'); -> 'ej' mysql> select elt(4, 'ej', 'Heja', 'hej', 'foo'); -> 'foo'
FIELD(S, S1, S2, S3...)
S
in S1
, S2
,
S3
... list. The complement of ELT()
. Return 0 when S is
not found.
mysql> select FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo'); -> 2 mysql> select FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo'); -> 0
FIND_IN_SET(string,string of strings)
mysql> SELECT FIND_IN_SET('b','a,b,c,d') -> 2This function will not work properly if the first argument contains a ','.
LCASE(A)
LOWER(A)
A
to lower case according to current character set
,dmappings (Default Latin1).
mysql> select lcase('QUADRATICALLY'); -> 'quadratically'
UCASE(A)
UPPER(A)
A
to upper case.
mysql> select ucase('Hej'); -> 'HEJ'
Some examples using more than one date function:
Select all record with a date_field from the last 30 days.
SELECT something FROM table WHERE NOW() > FROM_DAYS(TO_DAYS(date_field) + 30);
A Date
expression may be a date string, a datetime string, a
timestamp([6 | 8 | 14]) or a number of format YYMMDD
or YYYYMMDD
.
In a date expression a year may be 2 or 4 digits. 2 digits is assumed to be in the range 1970-2069. Dates 100-199 is converted to 2000-2999 to make year arithmetic easier!
DAYOFWEEK(date expr)
Date
(1 = Sunday, 2 = Monday, 2 = Tuesday ..)
This is according to the ODBC standard.
mysql> select dayofweek('1998-02-03'); -> 3
WEEKDAY(date expr)
Date
(0 = Monday, 1 = Tuesday ..)
mysql> select WEEKDAY('1997-10-04 22:23:00'); -> 5 mysql> select WEEKDAY('1997-11-05'); -> 2
DAYOFMONTH(date expr)
mysql> select DAYOFMONTH('1998-02-03'); -> 3
DAYOFYEAR(date expr)
mysql> select DAYOFYEAR('1998-02-03'); -> 34
MONTH(date expr)
mysql> select MONTH('1998-02-03'); -> 02
QUARTER(date expr)
mysql> select QUARTER('98-04-01'); -> 2
WEEK(date expr)
mysql> select WEEK('98-02-20'); -> 7
YEAR(date expr)
mysql> select YEAR('98-02-03'); -> 1998
HOUR(time expr)
mysql> select HOUR('10:05:03'); -> 10
MINUTE(time expr)
mysql> select MINUTE('98-02-03 10:05:03'); -> 5
SECOND(time expr)
mysql> select SECOND('10:05:03'); -> 3
PERIOD_ADD(P, N)
N
months to period P
(of type YYMM
or
YYYYMM
). Returns YYYYMM
.
mysql> select PERIOD_ADD(9801,2); -> 199803
PERIOD_DIFF(A, B)
A
and B
. A
and
B
should be of format YYMM
or YYYYMM
.
mysql> select PERIOD_DIFF(9802,199703); -> 11
TO_DAYS(Date)
Date
to a daynumber (Number of days since year
0). Date
may be a DATE
string, a DATETIME
string, a
TIMESTAMP([6 | 8 | 14])
or a number of format YYMMDD
or
YYYYMMDD
.
mysql> select TO_DAYS(9505); -> 733364 mysql> select TO_DAYS('1997-10-07); -> 729669
FROM_DAYS()
mysql> select from_days(729669); -> 1997-10-07
DATE_FORMAT(Date, Format)
Date
(a date or a timestamp) according to the
Format
string. The following format commands are known:
M | Month name |
W | Weekday name |
D | Day of the month with english suffix |
Y | Year with 4 digits |
y | Year with 2 digits |
m | Month |
d | Day of the month |
h | hour |
i | Minutes |
s | Seconds |
w | Day of the week (0=Monday..) |
mysql> select date_format('1997-10-04 22:23:00', 'W M Y h:i:s'); -> 'Saturday October 1997 22:23:00'
YYYYMMDD
or 'YYYY-MM-DD'
depending on whether CURDATE()
is used in a number or string
context.
mysql> select CURDATE(); -> '1997-12-15' mysql> select CURDATE()+0; -> 19971215
HHMMSS
or 'HH:MM:SS'
,
depending on whether CURTIME()
is used in a number or string
context.
mysql> select CURTIME(); -> '23:50:20' mysql> select CURTIME()+0; -> 235026
YYYYMMDDHHMMSS
or
'YYYY-MM-DD HH:MM:SS'
depending on whether NOW()
is used
in a number or string context.
mysql> select NOW(); -> '1997-12-15 23:51:26' mysql> select NOW()+0; -> 19971215235131
TIMESTAMP
column as an argument in which case it
returns the columns value in seconds. Date
may also be a date
string, a datetime string, or a number of format YYMMDD or YYYMMDD in
local time.
mysql> select UNIX_TIMESTAMP(); -> 882226357 mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00'); -> 875996580
YYYY-MM-DD HH:MM:SS
or
YYYYMMDDHHMMSS
format depending on context (numeric/string).
mysql> select FROM_UNIXTIME(875996580); -> '1997-10-04 22:23:00'
M | Month, textual |
W | Day (of the week), textual |
D | Day (of the month), numeric plus english suffix |
Y | Year, numeric, 4 digits |
y | Year, numeric, 2 digits |
m | Month, numeric |
d | Day (of the month), numeric |
h | Hour, numeric |
i | Minutes, numeric |
s | Seconds, numeric |
w | Day (of the week), numeric |
All other | All other characters are just copied. |
mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP(), 'Y D M h:m:s x'); -> '1997 23rd December 03:12:30 x'
H:MM:SS
or HMMSS
format depending on context.
mysql> select SEC_TO_TIME(2378); -> '00:39:38' mysql> select SEC_TO_TIME(2378)+0; -> 3938
Time
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'
PASSWORD(String)
String
. This
must be used to store a password in the 'user' grant table.
mysql> select PASSWORD('badpwd'); -> '7f84554057dd964b'
ENCRYPT(String[, Salt])
String
with the unix crypt()
command. The
Salt
should be a string with 2 characters. If crypt()
was
not found NULL
will always be returned.
LAST_INSERT_ID()
mysql> select LAST_INSERT_ID(); -> 1
FORMAT(Nr, Num)
Nr
to a Format like '#,###,###.##' with Num
decimals.
mysql> select FORMAT(12332.33, 2); -> '12,332.33'
VERSION
mysql> select version(); -> '3.21.16-beta-log'
GROUP BY
clause.COUNT(Expr)
NULL
rows. count(*)
is optimised to
return very quickly if no other column is used in the SELECT
.
select count(*) from student; select count(if(length(name)>3,1,NULL)) from student;
AVG(expr)
MIN(expr)
MAX(expr)
min()
and max()
may take a
string argument and will then return the minimum/maximum string value.
SUM(expr)
STD(expr)
ANSI
SQL
.
BIT_OR(expr)
OR
of all bits in expr. Caclulation done with 64 bits
precision.
BIT_AND(expr)
AND
of all bits in expr. Caclulation done with 64
bits precision.
MySQL has extended the use of GROUP BY
. You can use columns or
calculations in the SELECT
expressions which doesn't appear in
the GROUP BY
part. This stands for 'any possible value for this
group'. By using this, one can get a higher performance by avoiding
sorting and grouping on unnecessary items. For example, in the
following query one doesn't need to sort on b.name:
SELECT a.id,b.name,COUNT(*) from a,b WHERE a.id=b.id GROUP BY a.id
CREATE DATABASE database_name
Creates a database with the given name. The name can only contain letters,
numbers or the '_'
character. The max length of the database name is
32 characters.
All databases in MySQL are directories, so a CREATE DATABASE only
creates a directory in the MySQL database directory.
You can also create databases with mysqladmin
.
See section Overview of the different MySQL programs
DROP DATABASE database_name
Drop all tables in the database and deleted the database.
You have to be VERY carefull with this command!
DROP DATABASE
returns how many files was removed from the directory.
Normally this is number of tables*3.
You can also drop databases with mysqladmin
.
See section Overview of the different MySQL programs
CREATE TABLE table_name ( create_definition,... ) create_definition: column_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [ PRIMARY KEY ] [reference_definition] or PRIMARY KEY ( index_column_name,... ) or KEY [key_name] KEY( index_column_name,...) or INDEX [index_name] ( index_column_name,...) or UNIQUE [index_name] ( index_column_name,...) or FOREIGN KEY index_name ( index_column_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 ENUM(value1,value2,value3...) or SET(value1,value2,value3...) index_column_name: column_name [ (length) ] reference_definition: REFERENCES table_name [( index_column_name,...)] [ MATCH FULL | MATCH PARTIAL] [ ON DELETE reference_option] [ ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
See section Column types.
The FOREIGN KEY, CHECK and REFERENCE syntax are only for compatibility. They don't actually do anything. See section What functionality is missing in MySQL.
If a column doesn't have a DEFAULT value and is not declared as NOT NULL, the default value is NULL.
INT(5) ZEROFILL
a value of 5 is retrieved as 00005
.
BINARY
means that the column will be compared case sensitive. The
default is that all strings are compared case insensitive according to
ISO-8859-1 Latin1. BINARY
is 'sticky' which means that if a column
marked BINARY
is used in a expression, the whole expression is
compared BINARY
.
column_name(length)
syntax one can specify an index which is only
a part of a string
column. This can make the index file much smaller.
BLOB
and TEXT
types.
isamchk
utility to
reorganise tables.
VARCHAR
columns with a length of one or two are changed to CHAR
. When using one
VARCHAR
column all CHAR
columns longer than 2 are changed to
VARCHAR
's.
INSERT
/UPDATE
all strings (CHAR
and
VARCHAR
) are silently chopped/padded to the maximal length given
by CREATE. All end spaces are also automatically removed. For example
VARCHAR(10)
means that the column can contain strings with a
length up to 10 characters.
NULL
value.
REGEXP
and RLIKE
) uses
ISO8859-1 (Latin1) when deciding the type of a character.
ALTER [IGNORE] TABLE table_name alter_spec [, alter_spec ...] alter_specification: ADD [COLUMN] create_definition or CHANGE [COLUMN] old_column_name create_definition or ALTER [COLUMN] column_name { SET DEFAULT literal | DROP DEFAULT } or DROP [COLUMN] column_name or DROP PRIMARY KEY or DROP INDEX key_name or RENAME TABLE AS new_table_name
ALTER TABLE
works by creating a temporary table and copying all
information to it and then the old table is deleted and the new one is
renamed. This is done in such a way that all updates are automatically
redirect to the new table without any failed updates. While the
ALTER TABLE
is working, the old table is readable for other
clients. Table updates/writes to the table are stalled and only executed
after the new table is ready.
IGNORE
isn't specified then the copy will be aborted and
rolled back if there exists any duplicated unique index in the new
table. This is a MySQL extension.
CHANGE column_name
, DROP column_name
and DROP
INDEX
are MySQL extensions to ANSI SQL92.
COLUMN
is a pure noise word and can be omitted.
ADD
and CHANGE
takes the same create_definition as
CREATE TABLE
. See section CREATE TABLE syntax..
ALTER COLUMN
sets a new default value or removes the old
default value for a column.
DROP INDEX
removes an index. This is an MySQL extension.
FOREIGN KEY
syntax in MySQL exists only for compatibility.
See section What functionality is missing in MySQL.
DROP PRIMARY KEY
drops index named PRIMARY
or if no such
index exists, it drops the first UNIQUE
index in the table.
CHANGE
tries to convert data to the new format as good as possible.
mysql_info(MYSQL*)
one can retrieve how many records were
copied and how many records were deleted because of multiple indexes.
ALTER TABLE
one needs select, insert, delete,
update, create and drop privileges on the table.
ALTER TABLE table_name RENAME AS new_name
without any
other options, MySQL will only do a fast rename of table table.
DROP TABLE table_name [, table_name...]
Removes one or more tables. All the data and the definition is removed so take it easy with this command!
DELETE FROM table_name WHERE where_definition
Returns records affected.
If one does a delete without a WHERE
clause then the table is
recreated, which is much faster than doing a delete for each row. In
these cases, the command returns zero as affected records. MySQL
can't return the number of deleted row because the recreate is done without
opening the data files to make sure that one can recreate the table as
long as the table definition file table_name.frm
is valid.
SELECT [STRAIGHT_JOIN] [DISTINCT | ALL] select_expression,... [INTO OUTFILE 'file_name' ...] [ FROM table_references [WHERE where_definition ] [GROUP BY column,...] [ ORDER BY column [ASC | DESC] ,..] HAVING where_definition [LIMIT [offset,] rows] [PROCEDURE procedure_name]]
Strings are automatically converted to numbers and numbers to strings
when needed (a-la Perl). If in a compare operation ((=, <>, <= ,<,
>=, >)
) either of the arguments are numerical, the arguments are
compared as numbers, else the arguments are compared as strings. All
string comparisons are by default done case-independent by ISO8859-1
(The Scandinavian letter set which also works excellently with English).
select 1 > '6x'; -> 0 select 7 > '6x'; -> 1 select 0 > 'x6'; -> 0 select 0 = 'x6'; -> 1
HAVING
clause.
select concat(last_name,' ',first_name) as name from table order by name
LEFT OUTER JOIN
references.
See section Join syntax
LIKE
expressions % and _ may be preceded with '\' to skip the
wildcard meaning and get a literal % or _.
IFNULL()
and IF()
return number or string value according to
use.
ORDER
and GROUP
columns may be given as column names,
column aliases or column numbers in SELECT
clauses.
HAVING
clause can take any columns or alias in the
select_expressions. It is applied last, just before items are sent to
the client, without any optimisation. Don't use it for items that should
be in the WHERE clause. You can't write (yet):
SELECT user,MAX(salary) FROM users GROUP BY user HAVING max(salary)>10
Change it to:
SELECT user,MAX(salary) AS sum FROM users GROUP BY user HAVING sum > 10
STRAIGHT_JOIN
forces the optimiser to join the tables in the same
order that the tables are given in the FROM
clause. One can use
this to get a query to be done more quickly if the optimiser joins the
tables in non-optimal order. See section EXPLAIN syntax. Get information about a SELECT.
MySQL supports the following JOIN
syntaxes:
table_reference, table_reference table_reference [CROSS] JOIN table_reference table_reference LEFT [OUTER] JOIN table_reference ON conditional-expr table_reference LEFT [OUTER] JOIN table_reference USING (column-commalist) table_reference NATURAL LEFT [OUTER] JOIN table_reference { oj table_reference LEFT OUTER JOIN table_reference ON conditional-expr }
The last example is ODBC syntax.
table_reference AS alias
or
table_reference alias
.
,
and JOIN
are semantically identical. This does a full join
between the used tables. One normally specifies in the WHERE
condition how the tables should be linked.
ON
conditional is any WHERE
conditional. If there is no
matching record for the right table in a LEFT JOIN
a row with all
columns set to NULL will be used for the right table.
USING
column-list is a list of fields that must exists in both
tables. A LEFT JOIN B USING (C1,C2,C3...)
is defined to be semantically
identical to using an ON
expression
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.
LEFT JOIN
syntax exists only for compatibility with ODBC.
INSERT INTO table [ (column_name,...) ] VALUES (expression,...) or INSERT INTO table [ (column_name,...) ] SELECT ...
An expression may use any previous column in column_name list (or table if no column name list is given).
The following holds for a multi-row INSERT
statement:
ORDER BY
clause.
INSERT
statement cannot appear in the FROM
clause of the query.
INSERT INTO ... SELECT ...
then one can get the following
info string with the C API function mysql_info()
.
Records: 100 Duplicates: 0 Warnings: 0
Duplicates are
rows which couldn't be written because some index would be
duplicated. Warnings are columns which were set to NULL, but have been
declared NOT NULL. These will be set to their default value.
REPLACE INTO table [ (column_name,...) ] VALUES (expression,...) or REPLACE INTO table [ (column_name,...) ] SELECT ...
This works exactly like INSERT
, except that if there was some old
record in the table with the same unique index the old record or records
will be deleted before this record is inserted.
See section INSERT syntax.
LOAD DATA INFILE
'text_file_name.text' [REPLACE
|
IGNORE
] INTO
TABLE
table_name [FIELDS
[TERMINATED BY
',' [OPTIONALLY
] ENCLOSED BY
'"'
ESCAPED BY
'\\' ]] [LINES TERMINATED BY
'\n'] [(Field1,
Field2...)]
This is used to read rows from a text file, which must be located on the
server, at a very high speed. The server-client protocol doesn't yet
support files over a connection. If you only have the file on the
client, use rcp or ftp to copy it, possibly compressed, to the server
before using LOAD DATA INFILE
. All paths to the text file are
relative to the database directory.
To write data to a text file, use the SELECT ... INTO OUTFILE
'interval.txt' fields terminated by ',' enclosed by '"' escaped by '\\'
lines terminated by '\n' FROM ...
syntax.
Normally you don't have to specify any of the text file type options. The
default is a compact text file with columns separated with tab
characters and all rows end with a newline. Tabs, newlines and \
inside fields are prefixed with a \
. NULLs are read and written as
\N.
FIELDS TERMINATED BY
has the default value of \t
.
FIELDS [OPTIONALLY] ENCLOSED BY
has the default value of "
.
FIELDS ESCAPED BY
has the default value of '\\'
.
LINES TERMINATED BY
has the default value of '\n'
.
FIELDS TERMINATED BY
and LINES TERMINATED BY
may be more
than one character.
If LINES TERMINATED BY
is an empty string and FIELDS
TERMINATED BY
is non-empty then lines are also terminated with
FIELDS TERMINATED BY
.
If FIELDS TERMINATED BY
and FIELDS ENCLOSED BY
both are
empty strings ("
) then this gives a fixed row format ("not delimited"
import format). With a fixed row size NULL values are output as a
blank string.
If you specify OPTIONALLY
in ENCLOSED BY
, then only
strings are enclosed in ENCLOSED BY
by the SELECT ... INTO
statement.
Duplicated ENCLOSED BY
chars are removed from strings that start
with ENCLOSED BY
. For example: With ENCLOSED BY '"'
:
"The ""BIG"" boss" -> The "BIG" boss The "BIG" boss -> The "BIG" boss
If ESCAPED BY
is not empty then the following characters will be
prefixed with the escape character: ESCAPED BY
, ASCII 0
,
and the first character in any of FIELDS TERMINATED BY
,
FIELDS ENCLOSED BY
and LINES TERMINATED BY
.
If FIELDS ENCLOSED BY
is not empty then NULL
is read as a
NULL
value. If FIELDS ESCAPED BY
is not empty then
\N
is also read as a NULL
value.
If REPLACE
is used, then the new row will replace all rows which
have the same unique index. If IGNORE
is used, the row will then
be skipped if there already exists a record with an identical unique
index. If none of the above options are used an error will be issued. The
rest of the text file will be ignored if one gets a duplicate index error.
Some possible cases that are not supported by LOAD DATA
:
FIELDS TERMINATED BY
and FIELDS ENCLOSED
BY
both are empty) and BLOB columns.
FIELDS ESCAPED BY
is empty and the data contains LINES
TERMINATED BY
or FIELDS ENCLOSED BY
followed by FIELDS
TERMINATED BY
.
All rows are read into the table. If a row has too few columns, the rest
of the columns are set to default values. TIMESTAMP
columns are only
set to the current time if there is a NULL value for the column or if
the TIMESTAMP
column is left out from the field list when the field
list is used.
For security reasons the text file must either reside in the database
directory or be readable by all. Each user that wants to use
LOAD DATA INFILE
must also have 'Y' in the 'File_priv' column
in the user privilege table!
See section How does the privilege system work?
For more information about the escaped syntax, See section Literals. How do you write strings and numbers?.
When the LOAD DATA
query is done, one can get the following info
string with the C API function mysql_info()
.
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
Warnings are incremented for each column which can't be stored without loss of precision, for each column which didn't get a value from the read text line (happens if the line is too short) and for each line which has more data than can fit into the given columns. A warning is also given for any time, date, timestamp or datetime column that is set to 0.
An example that loads all columns:
LOAD DATA INFILE 'persondata.text' INTO TABLE persondata;
See section How should I arrange my table to be as fast/small as possible?
UPDATE table SET column=expression,... WHERE where_definition
All updates are done from left to right. If one accesses a column in the expression, update will then use the current value (a given value or the default value) of the column.
UPDATE persondata SET count=count+1
SHOW DATABASES [LIKE wild] or SHOW TABLES [FROM database] [LIKE wild] or SHOW COLUMNS FROM table [FROM database] [LIKE wild] or SHOW INDEX FROM table [FROM database] or SHOW STATUS or SHOW VARIABLES [LIKE wild]
Gives information about databases, tables or columns. If the LIKE
wild
part is used the wild
string is a normal SQL wildcard (with
% and _). FIELDS
may be used as an alias for COLUMNS
and
KEYS
may be used as an alias for INDEXES
.
STATUS
gives status information from the server
like mysqladmin status
). The output may differ from the following:
Uptime | Running_threads | Questions | Reloads | Open_tables
|
119 | 1 | 4 | 1 | 3 |
VARIABLES
shows the values of the some of MySQL system variables. Most
of these variables can be changed by different options to mysqld
!
EXPLAIN SELECT select_options
Gives information about how and in which order tables are joined. With
the help of EXPLAIN
one can see when one has to add more indexes
to tables to get a faster select that uses indexes to find the
records. You can also see if the optimiser joins the tables in an
optimal order. One can force the optimiser to use a specific join order
with the STRAIGHT_JOIN
option to select.
The different join types are:
system
const
eq_ref
ref
all
Here is a example of a join which is optimised with the help of
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;
The EXPLAIN
returns the following:
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)
In this case MySQL is doing a full join for all tables! This will take quite a long time as the product of the number of rows in each table must be examined! So if all tables had 1000 records MySQL has to look at 1000^4 = 1000000000000 rows. If the tables are bigger you can only imagine how long it would take...
In this case the first error is that MySQL can't yet use efficiently indexes on columns that are declared differently: (varchar() and char() are not different in this context)
In this case tt.ActualPC
is char(10)
and
et.EMPLOYID
is char(15)
.
Fix:
mysql> alter table tt change ActualPC ActualPC varchar(15);
And the above explanation shows:
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
Which is not perfect but much better. This version is executed in a couple of seconds.
After
mysql> alter table tt change AssignedPC AssignedPC varchar(15), change ClientID Clientid varchar(15);
You get the following from EXPLAIN
:
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
Which is 'almost' as good as it can get. The problem is that
MySQL assumes that tt.AcutalPC
is evenly distributed which
isn't the case in the tt.
Fortunately it is easy to tell MySQL about this:
shell> isamchk -a PATH_TO_MYSQL_DATABASE/tt shell> mysqladmin refresh
And now the join is 'perfect':
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 | DESC) table [column]
Gives information about columns. This command is for Oracle compatibility. See section SHOW syntax. Get information about tables, columns.... Column may be a column name or a string. Strings may contain wild cards.
LOCK TABLES
syntaxLOCK TABLES table_name [AS alias] READ|WRITE [, table_name READ|WRITE] ... UNLOCK TABLES
Locks tables for this thread. If a thread has a READ lock on a table, the thread (and all other threads) can only read from the table. If a thread has a WRITE lock one a table, then only this thread can READ and WRITE on the table. All threads waits until they get all locks (no timeouts).
When one uses LOCK TABLES
one must lock all tables one is going to use!
This policy ensures that table locking is deadlock free.
LOCK TABLES t READ, t as t2 READ SELECT * from t,t2;
All tables are automatically unlocked when one issues another LOCK
TABLES
or if the connection to the server is closed.
SET [OPTION] SQL_VALUE_OPTION=value, ...
The used options remain in effect for the whole current session.
The different options are:
SQL_SELECT_LIMIT=value
SQL_BIG_TABLES= 0 | 1
The table
### is full
anymore for big selects that require a big temporary
tables. The default value for a new connection is 0 (use in memory
temporary tables).
SQL_BIG_SELECTS= 0 | 1
SELECT
s with a erroneous WHERE
statement. A big query is
defined as a SELECT
that will probably have to examine more than
max_join_size
rows. The default value for a new connection is 0
(allow all SELECT's).
CHARACTER SET character_set_name | DEFAULT
cp1251_koi8
,
but one can easily add new mappings by editing the file
mysql_source_directory/sql/convert.cc
.
One can restore the default mapping by using DEFAULT
as the
character_set_name.
SQL_LOG_OFF= 0 | 1
TIMESTAMP= timestamp_value | DEFAULT
LAST_INSERT_ID= #
GRANT (ALL PRIVILEGES | (SELECT, INSERT, UPDATE, DELETE, REFERENCES (column list), USAGE)) ON table TO user,... [WITH GRANT OPTION]
This command doesn't do anything. It is only in MySQL for compatibility reasons. Privileges in MySQL are handled with the mysql grant tables. See section How does the privilege system work?
CREATE [UNIQUE] INDEX index_name ON table_name ( column_name,... )
This function doesn't do anything. It is only in MySQL for
compatibility reasons. You can create a new index with ALTER TABLE
.
See section ALTER TABLE syntax
DROP INDEX index_name
This always succeeds.
You can drop an index with ALTER TABLE
. See section ALTER TABLE syntax
MySQL supports the # to end of line
and
/* multiple line */
comment
styles.
select 1+1; # This comment is to the end of line select 1 /* in-line-comment */ + 1; select 1+ /* This will be ignored */ 1;
MySQL doesn't support the --
ANSI SQL style comments.
See section -- as start of a comment.
A common problem stems from trying to create a table with column names
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 the functions
whose names are also column names.
The following are explictly reserved words in MySQL. Most of
them (for example) group
, are forbidden by ANSI SQL92 as column
and/or table names. A few are because MySQL needs them and is
(currently) using a yacc parser:
action | add | all | alter
|
and | as | asc | auto_increment
|
between | bigint | bit | binary
|
blob | both | by | cascade
|
char | character | change | check
|
column | columns | create | data
|
database | databases | date | datetime
|
day | day_hour | day_minute | day_second
|
dayofweek | dec | decimal | default
|
delete | desc | describe | distinct
|
double | drop | escaped | enclosed
|
enum | explain | fields | float
|
float4 | float8 | foreign | from
|
for | full | grant | group
|
having | hour | hour_minute | hour_second
|
ignore | in | index | infile
|
insert | int | integer | interval
|
int1 | int2 | int3 | int4
|
int8 | into | is | join
|
key | keys | leading | left
|
like | lines | limit | lock
|
load | long | longblob | longtext
|
match | mediumblob | mediumtext | mediumint
|
middleint | minute | minute_second | month
|
natural | numeric | no | not
|
null | on | option | optionally
|
or | order | outer | outfile
|
partial | precision | primary | procedure
|
privileges | read | real | references
|
rename | regexp | repeat | replace
|
restrict | rlike | select | set
|
show | smallint | sql_big_tables | sql_big_selects
|
sql_select_limit | sql_log_off | straight_join | starting
|
table | tables | terminated | text
|
time | timestamp | tinyblob | tinytext
|
tinyint | trailing | to | use
|
using | unique | unlock | unsigned
|
update | usage | values | varchar
|
varying | varbinary | with | write
|
where | year | year_month | zerofill
|
Go to the first, previous, next, last section, table of contents.