Go to the first, previous, next, last section, table of contents.

MySQL language reference

Literals. How do you write strings and numbers?


A string may have ' or " around it.

\ is a escape character. The following escape characters are recognised:

An ascii 0 character.
A newline character.
A tab character.
A return character.
A backspace character.
A ' character.
A " character.
A \ character.
A % character. This is used in wildcard strings to search after %.
A _ 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
lines |
| This
lines |

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

Ascii 0. Should be replaced with "\0" (A backslash and a 0 digit).
Ascii 92, backslash
Ascii 39, Single quote
Ascii 33, Double quote


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

Examples of valid numbers are: 1221, 294.42, -32032.6809e+10.


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

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.

So punctuation characters like . and @ are not allowed since they will be used to extend MySQL.

Column types.

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

More about data types

Database size info.

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.

The numeric types

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.


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


Restrictions for BLOB and TEXT columns:

  1. A BLOB or TEXT cannot be an index or a part of an index
  2. When one sorts or groups a 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
  3. There is no end space truncation for 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:


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:


Choosing the right type for a column.

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

Column indexes

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.

name CHAR(200),
KEY index_name (name(10));

Multiple column indexes

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:

id INT,
last_name CHAR(30),
first_name CHAR(30),
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";

Type mapping to ease moving table definitions between different databases engines

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

Functions for use in 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

Grouping functions.

Parenthesis. Force order of evaluation in a expression.
mysql> select 1+2*3;                     ->      7
mysql> select (1+2)*3;                   ->      9

Normal arithmetic operations.

Division. A division by zero results in a NULL.
mysql> select 102/(1-1);                 ->      NULL

Bit functions.

These have a range of maximum 64 bits because MySQL uses bigint (64 bit) arithmetic.

Bitwise OR.
mysql> select 29 | 15;                   ->     31
Bitwise and.
mysql> select 29 & 15;                   ->      13
Number of set bits in an argument.
mysql> select bit_count(29);             ->      4

Logical operations.

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

Logical NOT. Return 1 if argument is 0 else return 0.
mysql> select NOT 1;                    ->      0
mysql> select NOT NULL;                 ->      NULL
mysql> select ! (1+1);                  ->      0
mysql> select ! 1+1;                    ->      1
Logical OR. Return 1 if any of the arguments are differs from 0.
mysql> select 1 || 0;                   ->      1
mysql> select 0 || 0;                   ->      0
mysql> select 1 || NULL;                ->      1

Logical AND. Return 1 if all of the arguments is differs from 0.
mysql> select 1 && NULL;                ->      0
mysql> select 1 && 0;                   ->      0

Comparison operators.

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:

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
Not equal.
mysql> select '.01' <> '0.01';          ->      1
mysql> select .01 <> '0.01';            ->      0
mysql> select 'zapp' <> 'zappp';        ->      1
Smaller than or equal.
mysql> select 0.1 <= 2;                 ->      1
Smaller than.
mysql> select 2 <= 2;                   ->      1
Bigger than or equal.
mysql> select 2 >= 2;                   ->      1
Bigger than.
mysql> select 2 > 2;                    ->      0
Returns 1 if A is NULL else 0.
mysql> select isnull(1+1);              ->      0
mysql> select isnull(1/0);              ->      1
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

String comparison functions.

expr IN (value,...)
Returns 1 if expr is any of the values in the 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,...)
Same as NOT (expr IN (value,...))
expr LIKE expr
SQL simple regular expression comparison. Returns 1 (TRUE) or 0 (FALSE). With 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!
  • expr NOT LIKE expr Same as NOT (expr LIKE expr).
  • expr REGEXP expr Checks string against extended regular expr. See section Description of MySQL regular expression syntax..
    mysql> select 'Monty!' regexp 'm%y%%';            ->      0
    mysql> select 'Monty!' regexp '.*';               ->      1
  • expr NOT REGEXP expr Same as NOT (expr REGEXP expr).
  • STRCMP() Returns 0 if the strings are the same. Otherwise return -1 if the first argument is smaller according to the current sort-order, otherwise return 1.
    mysql> select strcmp('text', 'text2');            -> -1
    mysql> select strcmp('text2', 'text');            -> 1
    mysql> select strcmp('text', 'text');             -> 0
  • Control flow functions.

    If 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 is true (A <> 0 and A <> NULL) then return B, else return C.
    mysql> select if(1>2,2,3);        -> 3

    Mathematical functions.

    All mathematical functions returns NULL in the case of a error.

    Sign. Changes sign of argument.
    mysql> select - 2;                -> -2
    Absolute value.
    mysql> select abs(2);             -> 2
    mysql> select abs(-32);           -> 32
    Sign of argument. Returns -1, 0 or 1.
    mysql> select sign(-32);          -> -1
    mysql> select sign(0);            -> 0
    mysql> select sign(234);          -> 1
    Module (like % in C).
    mysql> select mod(234, 10);       -> 4
    mysql> select 253 % 7;            -> 1
    mysql> select mod(29,9);                -> 2
    Largest integer value not greater than x.
    mysql> select floor(1.23);              -> 1
    mysql> select floor(-1.23);             -> -2
    Largest integer value not greater than x.
    mysql> select ceiling(-1.23);           -> -1
    mysql> select ceiling(1.23);            -> 2
    Round argument N to an integer.
    mysql> select round(-1.23);             -> -1
    mysql> select round(-1.58);             -> -2
    mysql> select round(1.58);              -> 2
    Round argument Number to a number with Decimals decimals.
    mysql> select ROUND(1.298, 1);          -> 1.3
    Returns the value of e (the base of natural logarithms) raised to the power of N.
    mysql> select exp(2);                   -> 7.389056
    mysql> select exp(-2);                  -> 0.135335
    Return the natural logarithm of X.
    mysql> select log(2);             -> 0.693147
    mysql> select log(-2);            -> NULL
    return the base-10 logarithm of X.
    mysql> select log10(2);           -> 0.301030
    mysql> select log10(100);                 -> 2.000000
    mysql> select log10(-100);                -> NULL
    Return the value of X raised to the power of Y.
    mysql> select pow(2,2);           -> 4.000000
    mysql> select pow(2,-2);                  -> 0.250000
    Returns the non-negative square root of X.
    mysql> select sqrt(4);            -> 2.000000
    mysql> select sqrt(20);           -> 4.472136
    Return the value of PI.
    mysql> select PI();               -> 3.141593
    Return the cosine of X, where X is given in radians.
    mysql> select cos(PI());                  -> -1.000000
    Return the sine of X, where X is given in radians.
    mysql> select sin(PI());                  -> 0.000000
    Returns the tangent of X, where X is given in radians.
    mysql> select tan(PI()+1);                -> 1.557408
    Return the arc cosine of 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
    Return the arc sine of 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
    Return the arc tangent of X; that is the value whose tangent is X.
    mysql> select ATAN(2);          -> 1.107149
    mysql> select ATAN(-2);         -> -1.107149
    Return the arc tangent of the two variables X and Y. It is similar to calculating the arc tangent of Y / X, except that the signs of both arguments are used to determine the quadrant of the result.
    mysql> select ATAN(-2,2);               -> -0.785398
    mysql> select ATAN(PI(),0);             -> 1.570796
    Return the cotangens of N.
    mysql> select COT(12);          -> -1.57267341
    mysql> select COT(0);           -> NULL
    Returns a random float, 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.7888
    One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
    Min value of arguments. Must have 2 or more arguments, else these are 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 value of arguments. Must have 2 or more arguments, else these are 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
    Return N converted from radians to degrees.
    mysql> select DEGREES(PI());                    -> 180.000000
    Return N converted from degrees to radians.
    mysql> select RADIANS(90);                      -> 1.570796
    TRUNCATE(Number, Decimals)
    Truncate number 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

    String functions.

    Returns the ASCII code value of the leftmost character of S. If S is NULL return NULL.
    mysql> SELECT ascii(2);                         -> 50
    mysql> SELECT ascii('dx');                      -> 100
    Returns a string that consists of the characters given by the ASCII code values of the arguments. NULLs are skipped.
    mysql> SELECT char(77,121,83,81,'76');          -> 'MySQL'
    Concatenates strings. May have more than 2 arguments.
    mysql> SELECT CONCAT('My', 'S', 'QL');          -> 'MySQL'
    Length of string.
    mysql> SELECT length('text');                   -> 4
    mysql> SELECT octet_length('text');             -> 4
    Returns position of 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
    Returns position of first substring 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
    Returns position of first substring A in string B starting at C.
    mysql> select locate('bar', 'foobarbar',5);        -> 7
    Gets length in characters from beginning of string.
    mysql> select left('foobarbar', 5);             -> 'fooba'
    Gets B characters from end of string A.
    mysql> select right('foobarbar', 5);            -> 'arbar'
    mysql> select substring('foobarbar' from 5);    -> 'arbar'
    Removes space characters from the beginning of string.
    mysql> select ltrim('  barbar');                -> 'barbar'
    Removes space characters from the end of string. mysql> select rtrim('barbar '); -> 'barbar'
    Returns a character string with all 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'
    Gets a soundex string from 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'
    MID(A, B, C)
    Returns substring from 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)
    Returns the substring from 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'
    Return a string of N spaces.
    mysql> select SPACE(6);         -> '      '
    REPLACE(A, B, C)
    Replaces all occurrences of string B in string A with string C.
    mysql> select replace('www.tcx.se', 'w', 'Ww');  -> 'WwWwWw.tcx.se'
    REPEAT(String, Count)
    Repeats 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'
    Reverses all characters in string.
    mysql> select reverse('abc');  -> 'cba'
    INSERT(Org, Start, Length, New)
    Replaces substring in 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...)
    It is required that 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...)
    Returns 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...)
    Returns index of 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)
    Returns a value 1-N if the 'string' is in 'string of strings'. A 'string of strings' is a string where each different value is separated with a ','. 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!
    mysql> SELECT FIND_IN_SET('b','a,b,c,d')		-> 2
    This function will not work properly if the first argument contains a ','.
    Changes A to lower case according to current character set ,dmappings (Default Latin1).
    mysql> select lcase('QUADRATICALLY');           -> 'quadratically'
    Changes A to upper case.
    mysql> select ucase('Hej');             -> 'HEJ'

    Date and time functions

    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)
    Gets weekday for Date (1 = Sunday, 2 = Monday, 2 = Tuesday ..) This is according to the ODBC standard.
    mysql> select dayofweek('1998-02-03');		-> 3
    WEEKDAY(date expr)
    Gets weekday for 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)
    Returns day of month (1-31)
    mysql> select DAYOFMONTH('1998-02-03');		-> 3
    DAYOFYEAR(date expr)
    Returns day of year (1-366)
    mysql> select DAYOFYEAR('1998-02-03');		-> 34
    MONTH(date expr)
    Returns month (1-12)
    mysql> select MONTH('1998-02-03');		-> 02
    QUARTER(date expr)
    Returns quarter (1-4).
    mysql> select QUARTER('98-04-01');		-> 2
    WEEK(date expr)
    Returns week (1-53).
    mysql> select WEEK('98-02-20');			-> 7
    YEAR(date expr)
    Returns year (1000-9999).
    mysql> select YEAR('98-02-03');			-> 1998
    HOUR(time expr)
    Returns hour (0-23)
    mysql> select HOUR('10:05:03');			-> 10
    MINUTE(time expr)
    Returns minute (0-59).
    mysql> select MINUTE('98-02-03 10:05:03');	-> 5
    SECOND(time expr)
    Returns seconds (1000-9999).
    mysql> select SECOND('10:05:03');		-> 3
    Adds N months to period P (of type YYMM or YYYYMM). Returns YYYYMM.
    mysql> select PERIOD_ADD(9801,2);               -> 199803
    Returns months between periods A and B. A and B should be of format YYMM or YYYYMM.
    mysql> select PERIOD_DIFF(9802,199703);         -> 11
    Changes a 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
    Changes a daynumber to a DATE.
    mysql> select from_days(729669);                -> 1997-10-07       
    DATE_FORMAT(Date, Format)
    Formats the 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..)
    All other characters are copied to the result.
    mysql> select date_format('1997-10-04 22:23:00', 'W M Y h:i:s');
            -> 'Saturday October 1997 22:23:00'
  • CURRENT_DATE() Returns todays date. In form 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
  • CURRENT_TIME() Returns current time in form 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
  • NOW()
  • CURRENT_TIMESTAMP() Returns the current time in format 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
  • UNIX_TIMESTAMP([date expression]) If called without any arguments, returns the current time as a unix timestamp (seconds in GMT since 1970.01.01 00:00:00). Normally it is called with a 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
  • FROM_UNIXTIME(Unix_timestamp) Returns a string of the timestamp in 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'
  • FROM_UNIXTIME(Unix_timestamp, Format_string) Returns a string of the timestamp formated according to the Format_string. The format string may contain:
    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'
  • SEC_TO_TIME(Seconds) Returns the hours, minutes and seconds of the argument in 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_SEC(Time) Converts Time to seconds.
    mysql> select TIME_TO_SEC('22:23:00');  -> 80580
    mysql> select TIME_TO_SEC('00:39:38');  -> 2378
  • Miscellaneous functions.

    Returns current database name.
    mysql> select DATABASE();               -> 'test'
    Returns current user name.
    mysql> select USER();                   -> 'davida'
    Calculates a password string from plaintext password String. This must be used to store a password in the 'user' grant table.
    mysql> select PASSWORD('badpwd');       -> '7f84554057dd964b'
    ENCRYPT(String[, Salt])
    Crypt 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.
    Returns the last automaticly generated value that was set in a auto_increment column. See section How can I get the unique ID for the last inserted row?.
    mysql> select LAST_INSERT_ID();         -> 1
    FORMAT(Nr, Num)
    Formats number Nr to a Format like '#,###,###.##' with Num decimals.
    mysql> select FORMAT(12332.33, 2);      -> '12,332.33'
    Return the version of the MySQL server.
    mysql> select version();                -> '3.21.16-beta-log'

    Functions for GROUP BY clause.

    Count number of non 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;
    Average value of expr.
    Minimum/Maximum value of expr. min() and max() may take a string argument and will then return the minimum/maximum string value.
    Sum of expr.
    Standard derivation of expression. This is a extension to ANSI SQL.
    The bitwise OR of all bits in expr. Caclulation done with 64 bits precision.
    The bitwise 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 syntax.

    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

    Create database syntax.

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

    CREATE TABLE table_name ( 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)
            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...)
            column_name [ (length) ]
            REFERENCES table_name [( index_column_name,...)]
                       [ MATCH FULL | MATCH PARTIAL]
                       [ ON DELETE reference_option]
                       [ ON UPDATE reference_option]

    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.

    ALTER TABLE syntax

    ALTER [IGNORE] TABLE table_name alter_spec [, alter_spec ...]
    	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 INDEX key_name
    or	RENAME TABLE AS new_table_name

    DROP TABLE syntax.

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

    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 syntax

    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

    Join syntax

    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.

    INSERT syntax

    	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:

    REPLACE syntax

    	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:

    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 syntax

    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 syntax. Get information about tables, columns...

    or 	SHOW TABLES [FROM database] [LIKE wild]
    or	SHOW COLUMNS FROM table [FROM database] [LIKE wild]
    or	SHOW INDEX FROM table [FROM database]

    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 syntax. Get information about a SELECT.

    	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:

    The table has only one record (= system table)
    The table has at most one matching record which will be read at the start of the query. All columns in this table will be regarded as constants by the rest of the optimiser.
    One record will be read from this table for each combination of the previous tables.
    All records with matching indexes will be read from this table for each combination of the previous tables.
    A full table scan will be done for each combination of the previous tables.

    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,
            WHERE tt.SubmitTime Is Null and tt.ActualPC =
            et.EMPLOYID and tt.AssignedPC =
            et_1.EMPLOYID and tt.ClientID =

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


    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.


    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 syntax. Get information about columns.

    (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 syntax

    LOCK TABLES table_name [AS alias] READ|WRITE [, table_name READ|WRITE]

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


    The used options remain in effect for the whole current session.

    The different options are:

    The maximum number of records to return in any select. If a select has a limit clause it overrides this statement. The default value for a new connection is 'unlimited'.
    SQL_BIG_TABLES= 0 | 1
    If set to 1 then all temporary tables are stored on disk based. This will be a little slower, but one will not get the error 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
    If set to 1 then MySQL will abort if one tries to make a select which probably will take a very long time. This is nice for terminating SELECTs 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
    This maps all strings from and to the client with the given mapping. Currently the only option for character_set_name is cp1251_koi8, but 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
    If set to 1 then no logging will done to the standard log for this client if the client has process list privileges. This doesn't affect the update log!
    TIMESTAMP= timestamp_value | DEFAULT
    Set the time for this client. This is used to get the original timestamp if one uses the update log to restore rows.
    Set the value to be returned from LAST_INSERT_ID(). This is stored in the update log when one uses LAST_INSERT_ID() in a command that updates a table.

    GRANT syntax. (Compatibility function).

     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 INDEX syntax (Compatibility function).

    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 syntax (Compatibility function).

    DROP INDEX index_name

    This always succeeds. You can drop an index with ALTER TABLE. See section ALTER TABLE syntax

    Comment 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

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

    Is MySQL picky about reserved words?

    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
    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 theese names are very natural names so many people have already used them.

    Go to the first, previous, next, last section, table of contents.

    Casa de Bender