MySQL数据库

开发平台：

Visual C++

manual.txt：源码内容

The first of these statements looks for an exact match. The second
looks for values containing the first set member.
If you want to get all possible values for a `SET' column, you should
use: `SHOW COLUMNS FROM table_name LIKE set_column_name' and parse the
`SET' definition in the second column.
Choosing the Right Type for a Column
------------------------------------
For the most efficient use of storage, try to use the most precise type
in all cases. For example, if an integer column will be used for values
in the range between `1' and `99999', `MEDIUMINT UNSIGNED' is the best
type.
Accurate representation of monetary values is a common problem. In
*MySQL*, you should use the `DECIMAL' type. This is stored as a string,
so no loss of accuracy should occur. If accuracy is not too important,
the `DOUBLE' type may also be good enough.
For high precision, you can always convert to a fixed-point type stored
in a `BIGINT'. This allows you to do all calculations with integers and
convert results back to floating-point values only when necessary.
Column Indexes
--------------
All *MySQL* column types can be indexed. Use of indexes on the
relevant columns is the best way to improve the performance of `SELECT'
operations.
The maximum number of keys and the maximum index length is defined per
table handler. *Note Table types::. You can with all table handlers have
at least 16 keys and a total index length of at least 256 bytes.
For `CHAR' and `VARCHAR' columns, you can index a prefix of a column.
This is much faster and requires less disk space than indexing the
whole column. The syntax to use in the `CREATE TABLE' statement to
index a column prefix looks like this:
KEY index_name (col_name(length))
The example below creates an index for the first 10 characters of the
`name' column:
mysql> CREATE TABLE test (
name CHAR(200) NOT NULL,
KEY index_name (name(10)));
For `BLOB' and `TEXT' columns, you must index a prefix of the column.
You cannot index the entire column.
In *MySQL* Version 3.23.23 or later, you can also create special
*FULLTEXT* indexes. They are used for full-text search. Only the
`MyISAM' table type supports `FULLTEXT' indexes. They can be created
only from `VARCHAR' and `TEXT' columns. Indexing always happens over
the entire column and partial indexing is not supported. See *Note
MySQL full-text search:: for details.
Multiple-column Indexes
-----------------------
*MySQL* can create indexes on multiple columns. An index may consist
of up to 15 columns. (On `CHAR' and `VARCHAR' columns you can also use
a prefix of the column as a part of an index).
A multiple-column index can be considered a sorted array containing
values that are created by concatenating the values of the indexed
columns.
*MySQL* uses multiple-column indexes in such a way that queries are
fast when you specify a known quantity for the first column of the
index in a `WHERE' clause, even if you don't specify values for the
other columns.
Suppose a table is created using the following specification:
mysql> CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name));
Then the index `name' is an index over `last_name' and `first_name'.
The index will be used for queries that specify values in a known range
for `last_name', or for both `last_name' and `first_name'. Therefore,
the `name' index will be used in the following queries:
mysql> SELECT * FROM test WHERE last_name="Widenius";
mysql> SELECT * FROM test WHERE last_name="Widenius"
AND first_name="Michael";
mysql> SELECT * FROM test WHERE last_name="Widenius"
AND (first_name="Michael" OR first_name="Monty");
mysql> SELECT * FROM test WHERE last_name="Widenius"
AND first_name >="M" AND first_name < "N";
However, the `name' index will NOT be used in the following queries:
mysql> SELECT * FROM test WHERE first_name="Michael";
mysql> SELECT * FROM test WHERE last_name="Widenius"
OR first_name="Michael";
For more information on the manner in which *MySQL* uses indexes to
improve query performance, see *Note *MySQL* indexes: MySQL indexes.
Using Column Types from Other Database Engines
----------------------------------------------
To make it easier to use code written for SQL implementations from other
vendors, *MySQL* maps column types as shown in the table below. These
mappings make it easier to move table definitions from other database
engines to *MySQL*:
*Other vendor type* *MySQL type*
`BINARY(NUM)' `CHAR(NUM) BINARY'
`CHAR VARYING(NUM)' `VARCHAR(NUM)'
`FLOAT4' `FLOAT'
`FLOAT8' `DOUBLE'
`INT1' `TINYINT'
`INT2' `SMALLINT'
`INT3' `MEDIUMINT'
`INT4' `INT'
`INT8' `BIGINT'
`LONG VARBINARY' `MEDIUMBLOB'
`LONG VARCHAR' `MEDIUMTEXT'
`MIDDLEINT' `MEDIUMINT'
`VARBINARY(NUM)' `VARCHAR(NUM) BINARY'
Column type mapping occurs at table creation time. If you create a
table with types used by other vendors and then issue a `DESCRIBE
tbl_name' statement, *MySQL* reports the table structure using the
equivalent *MySQL* types.
Functions for Use in `SELECT' and `WHERE' Clauses
=================================================
A `select_expression' or `where_definition' in a SQL statement can
consist of any expression using the functions described below.
An expression that contains `NULL' always produces a `NULL' value
unless otherwise indicated in the documentation for the operators and
functions involved in the expression.
*NOTE:* There must be no whitespace between a function name and the
parenthesis following it. This helps the *MySQL* parser distinguish
between function calls and references to tables or columns that happen
to have the same name as a function. Spaces around arguments are
permitted, though.
You can force *MySQL* to accept spaces after the function name by
starting `mysqld' with `--ansi' or using the `CLIENT_IGNORE_SPACE' to
`mysql_connect()', but in this case all function names will become
reserved words. *Note ANSI mode::.
For the sake of brevity, examples display the output from the `mysql'
program in abbreviated form. So this:
mysql> select MOD(29,9);
1 rows in set (0.00 sec)
+-----------+
| mod(29,9) |
+-----------+
| 2 |
+-----------+
is displayed like this:
mysql> select MOD(29,9);
-> 2
Grouping Functions
------------------
`( ... )'
Parentheses. Use these to force the order of evaluation in an
expression:
mysql> select 1+2*3;
-> 7
mysql> select (1+2)*3;
-> 9
Normal Arithmetic Operations
----------------------------
The usual arithmetic operators are available. Note that in the case of
`-', `+', and `*', the result is calculated with `BIGINT' (64-bit)
precision if both arguments are integers!
`+'
Addition:
mysql> select 3+5;
-> 8
`-'
Subtraction:
mysql> select 3-5;
-> -2
`*'
Multiplication:
mysql> select 3*5;
-> 15
mysql> select 18014398509481984*18014398509481984.0;
-> 324518553658426726783156020576256.0
mysql> select 18014398509481984*18014398509481984;
-> 0
The result of the last expression is incorrect because the result
of the integer multiplication exceeds the 64-bit range of `BIGINT'
calculations.
`/'
Division:
mysql> select 3/5;
-> 0.60
Division by zero produces a `NULL' result:
mysql> select 102/(1-1);
-> NULL
A division will be calculated with `BIGINT' arithmetic only if
performed in a context where its result is converted to an integer!
Bit Functions
-------------
*MySQL* uses `BIGINT' (64-bit) arithmetic for bit operations, so these
operators have a maximum range of 64 bits.
`|'
Bitwise OR:
mysql> select 29 | 15;
-> 31
`&'
Bitwise AND:
mysql> select 29 & 15;
-> 13
`<<'
Shifts a longlong (`BIGINT') number to the left:
mysql> select 1 << 2
-> 4
`>>'
Shifts a longlong (`BIGINT') number to the right:
mysql> select 4 >> 2
-> 1
`~'
Invert all bits:
mysql> select 5 & ~1
-> 4
`BIT_COUNT(N)'
Returns the number of bits that are set in the argument `N':
mysql> select BIT_COUNT(29);
-> 4
Logical Operations
------------------
All logical functions return `1' (TRUE), `0' (FALSE) or `NULL'
(unknown, which is in most cases the same as FALSE):
`NOT'
`!'
Logical NOT. Returns `1' if the argument is `0', otherwise returns
`0'. Exception: `NOT NULL' returns `NULL':
mysql> select NOT 1;
-> 0
mysql> select NOT NULL;
-> NULL
mysql> select ! (1+1);
-> 0
mysql> select ! 1+1;
-> 1
The last example returns `1' because the expression evaluates the
same way as `(!1)+1'.
`OR'
`||'
Logical OR. Returns `1' if either argument is not `0' and not
`NULL':
mysql> select 1 || 0;
-> 1
mysql> select 0 || 0;
-> 0
mysql> select 1 || NULL;
-> 1
`AND'
`&&'
Logical AND. Returns `0' if either argument is `0' or `NULL',
otherwise returns `1':
mysql> select 1 && NULL;
-> 0
mysql> select 1 && 0;
-> 0
Comparison Operators
--------------------
Comparison operations result in a value of `1' (TRUE), `0' (FALSE), or
`NULL'. These functions work for both numbers and strings. Strings are
automatically converted to numbers and numbers to strings as needed (as
in Perl).
*MySQL* performs comparisons using the following rules:
* If one or both arguments are `NULL', the result of the comparison
is `NULL', except for the `<=>' operator.
* If both arguments in a comparison operation are strings, they are
compared as strings.
* If both arguments are integers, they are compared as integers.
* Hexadecimal values are treated as binary strings if not compared
to a number.
* If one of the arguments is a `TIMESTAMP' or `DATETIME' column and
the other argument is a constant, the constant is converted to a
timestamp before the comparison is performed. This is done to be
more ODBC-friendly.
* In all other cases, the arguments are compared as floating-point
(real) numbers.
By default, string comparisons are done in case-independent fashion
using the current character set (ISO-8859-1 Latin1 by default, which
also works excellently for English).
The examples below illustrate conversion of strings to numbers for
comparison operations:
mysql> SELECT 1 > '6x';
-> 0
mysql> SELECT 7 > '6x';
-> 1
mysql> SELECT 0 > 'x6';
-> 0
mysql> SELECT 0 = 'x6';
-> 1
`='
Equal:
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
`<='
Less than or equal:
mysql> select 0.1 <= 2;
-> 1
`<'
Less than:
mysql> select 2 <= 2;
-> 1
`>='
Greater than or equal:
mysql> select 2 >= 2;
-> 1
`>'
Greater than:
mysql> select 2 > 2;
-> 0
`<=>'
Null safe equal:
mysql> select 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
-> 1 1 0
`IS NULL'
`IS NOT NULL'
Test whether or not a value is or is not `NULL':
mysql> select 1 IS NULL, 0 IS NULL, NULL IS NULL:
-> 0 0 1
mysql> select 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
-> 1 1 0
`expr BETWEEN min AND max'
If `expr' is greater than or equal to `min' and `expr' is less
than or equal to `max', `BETWEEN' returns `1', otherwise it
returns `0'. This is equivalent to the expression `(min <= expr
AND expr <= max)' if all the arguments are of the same type. The
first argument (`expr') determines how the comparison is performed
as follows:
* If `expr' is a `TIMESTAMP', `DATE', or `DATETIME' column,
`MIN()' and `MAX()' are formatted to the same format if they
are constants.
* If `expr' is a case-insensitive string expression, a
case-insensitive string comparison is done.
* If `expr' is a case-sensitive string expression, a
case-sensitive string comparison is done.
* If `expr' is an integer expression, an integer comparison is
done.
* Otherwise, a floating-point (real) comparison is done.
mysql> select 1 BETWEEN 2 AND 3;
-> 0
mysql> select 'b' BETWEEN 'a' AND 'c';
-> 1
mysql> select 2 BETWEEN 2 AND '3';
-> 1
mysql> select 2 BETWEEN 2 AND 'x-3';
-> 0
`expr IN (value,...)'
Returns `1' if `expr' is any of the values in the `IN' list, else
returns `0'. If all values are constants, then all values are
evaluated according to the type of `expr' and sorted. The search
for the item is then done using a binary search. This means `IN'
is very quick if the `IN' value list consists entirely of
constants. If `expr' is a case-sensitive string expression, the
string comparison is performed in case-sensitive fashion:
mysql> select 2 IN (0,3,5,'wefwf');
-> 0
mysql> select 'wefwf' IN (0,3,5,'wefwf');
-> 1
`expr NOT IN (value,...)'
Same as `NOT (expr IN (value,...))'.
`ISNULL(expr)'
If `expr' is `NULL', `ISNULL()' returns `1', otherwise it returns
`0':
mysql> select ISNULL(1+1);
-> 0
mysql> select ISNULL(1/0);
-> 1
Note that a comparison of `NULL' values using `=' will always be
false!
`COALESCE(list)'
Returns first non-`NULL' element in list:
mysql> select COALESCE(NULL,1);
-> 1
mysql> select COALESCE(NULL,NULL,NULL);
-> NULL
`INTERVAL(N,N1,N2,N3,...)'
Returns `0' if `N' < `N1', `1' if `N' < `N2' and so on. All
arguments are treated as integers. It is required that `N1' <
`N2' < `N3' < `...' < `Nn' for this function to work correctly.
This is because a binary search is used (very fast):
mysql> select INTERVAL(23, 1, 15, 17, 30, 44, 200);
-> 3
mysql> select INTERVAL(10, 1, 10, 100, 1000);
-> 2
mysql> select INTERVAL(22, 23, 30, 44, 200);
-> 0
String Comparison Functions
---------------------------
Normally, if any expression in a string comparison is case sensitive,
the comparison is performed in case-sensitive fashion.
`expr LIKE pat [ESCAPE 'escape-char']'
Pattern matching using SQL simple regular expression comparison.
Returns `1' (TRUE) or `0' (FALSE). With `LIKE' you can use the
following two wild-card characters in the pattern:
`%' Matches any number of characters, even zero characters
`_' Matches exactly one character
mysql> select 'David!' LIKE 'David_';
-> 1
mysql> select 'David!' LIKE '%D%v%';
-> 1
To test for literal instances of a wild-card character, precede
the character with the escape character. If you don't specify the
`ESCAPE' character, `' is assumed:
`%' Matches one `%' character
`_' Matches one `_' character
mysql> select 'David!' LIKE 'David_';
-> 0
mysql> select 'David_' LIKE 'David_';
-> 1
To specify a different escape character, use the `ESCAPE' clause:
mysql> select 'David_' LIKE 'David|_' ESCAPE '|';
-> 1
`LIKE' is allowed on numeric expressions! (This is a *MySQL*
extension to the ANSI SQL `LIKE'.)
mysql> select 10 LIKE '1%';
-> 1
Note: Because *MySQL* uses the C escape syntax in strings (for
example, `n'), you must double any `' that you use in your `LIKE'
strings. For example, to search for `n', specify it as `\n'. To
search for `', specify it as `\\' (the backslashes are stripped
once by the parser and another time when the pattern match is
done, leaving a single backslash to be matched).
`expr NOT LIKE pat [ESCAPE 'escape-char']'
Same as `NOT (expr LIKE pat [ESCAPE 'escape-char'])'.
`expr REGEXP pat'
`expr RLIKE pat'
Performs a pattern match of a string expression `expr' against a
pattern `pat'. The pattern can be an extended regular expression.
*Note Regexp::. Returns `1' if `expr' matches `pat', otherwise
returns `0'. `RLIKE' is a synonym for `REGEXP', provided for
`mSQL' compatibility. Note: Because *MySQL* uses the C escape
syntax in strings (for example, `n'), you must double any `' that
you use in your `REGEXP' strings. As of *MySQL* Version 3.23.4,
`REGEXP' is case insensitive for normal (not binary) strings:
mysql> select 'Monty!' REGEXP 'm%y%%';
-> 0
mysql> select 'Monty!' REGEXP '.*';
-> 1
mysql> select 'new*n*line' REGEXP 'new\*.\*line';
-> 1
mysql> select "a" REGEXP "A", "a" REGEXP BINARY "A";
-> 1 0
mysql> select "a" REGEXP "^[a-d]";
-> 1
`'
`REGEXP' and `RLIKE' use the current character set (ISO-8859-1
Latin1 by default) when deciding the type of a character.
`expr NOT REGEXP pat'
`expr NOT RLIKE pat'
Same as `NOT (expr REGEXP pat)'.
`STRCMP(expr1,expr2)'
`STRCMP()' returns `0' if the strings are the same, `-1' if the
first argument is smaller than the second according to the current
sort order, and `1' otherwise:
mysql> select STRCMP('text', 'text2');
-> -1
mysql> select STRCMP('text2', 'text');
-> 1
mysql> select STRCMP('text', 'text');
-> 0
`MATCH (col1,col2,...) AGAINST (expr)'
`MATCH ... AGAINST()' is used for full-text search and returns
relevance - similarity measure between the text in columns
`(col1,col2,...)' and the query `expr'. Relevance is a positive
floating-point number. Zero relevance means no similarity. For
`MATCH ... AGAINST()' to work, a *FULLTEXT* index must be created
first. *Note `CREATE TABLE': CREATE TABLE. `MATCH ... AGAINST()'
is available in *MySQL* Version 3.23.23 or later. For details and
usage examples *note MySQL full-text search::.
Cast Operators
--------------
``BINARY''
The `BINARY' operator casts the string following it to a binary
string. This is an easy way to force a column comparison to be
case sensitive even if the column isn't defined as `BINARY' or
`BLOB':
mysql> select "a" = "A";
-> 1
mysql> select BINARY "a" = "A";
-> 0
`BINARY' was introduced in *MySQL* Version 3.23.0.
Note that in some context *MySQL* will not be able to use the
index efficiently when you cast an indexed column to `BINARY'.
If you want to compare a blob case-insensitively you can always convert
the blob to upper case before doing the comparison:
SELECT 'A' LIKE UPPER(blob_col) FROM table_name;
We plan to soon introduce casting between different character sets to
make string comparison even more flexible.
Control Flow Functions
----------------------
`IFNULL(expr1,expr2)'
If `expr1' is not `NULL', `IFNULL()' returns `expr1', else it
returns `expr2'. `IFNULL()' returns a numeric or string value,
depending on the context in which it is used:
mysql> select IFNULL(1,0);
-> 1
mysql> select IFNULL(NULL,10);
-> 10
mysql> select IFNULL(1/0,10);
-> 10
mysql> select IFNULL(1/0,'yes');
-> 'yes'
`NULLIF(expr1,expr2)'
If `expr1 = expr2' is true, return `NULL' else return `expr1'.
This is the same as `CASE WHEN x = y THEN NULL ELSE x END':
mysql> select NULLIF(1,1);
-> NULL
mysql> select NULLIF(1,2);
-> 1
Note that `expr1' is evaluated twice in *MySQL* if the arguments
are equal.
`IF(expr1,expr2,expr3)'
If `expr1' is TRUE (`expr1 <> 0' and `expr1 <> NULL') then `IF()'
returns `expr2', else it returns `expr3'. `IF()' returns a
numeric or string value, depending on the context in which it is
used:
mysql> select IF(1>2,2,3);
-> 3
mysql> select IF(1<2,'yes','no');
-> 'yes'
mysql> select IF(strcmp('test','test1'),'no','yes');
-> 'no'
`expr1' is evaluated as an integer value, which means that if you
are testing floating-point or string values, you should do so
using a comparison operation:
mysql> select IF(0.1,1,0);
-> 0
mysql> select IF(0.1<>0,1,0);
-> 1
In the first case above, `IF(0.1)' returns `0' because `0.1' is
converted to an integer value, resulting in a test of `IF(0)'.
This may not be what you expect. In the second case, the
comparison tests the original floating-point value to see whether
it is non-zero. The result of the comparison is used as an
integer.
The default return type of `IF()' (which may matter when it is
stored into a temporary table) is calculated in *MySQL* Version
3.23 as follows:
*Expression* *Return value*
expr2 or expr3 returns string string
expr2 or expr3 returns a floating-point
floating-point value
expr2 or expr3 returns an integer integer
`CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END'
`CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END'
The first version returns the `result' where
`value=compare-value'. The second version returns the result for
the first condition, which is true. If there was no matching result
value, then the result after `ELSE' is returned. If there is no
`ELSE' part then `NULL' is returned:
mysql> SELECT CASE 1 WHEN 1 THEN "one" WHEN 2 THEN "two" ELSE "more" END;
-> "one"
mysql> SELECT CASE WHEN 1>0 THEN "true" ELSE "false" END;
-> "true"
mysql> SELECT CASE BINARY "B" when "a" then 1 when "b" then 2 END;
-> NULL
The type of the return value (`INTEGER', `DOUBLE' or `STRING') is the
same as the type of the first returned value (the expression after the
first `THEN').
Mathematical Functions
----------------------
All mathematical functions return `NULL' in case of an error.
`-'
Unary minus. Changes the sign of the argument:
mysql> select - 2;
-> -2
Note that if this operator is used with a `BIGINT', the return
value is a `BIGINT'! This means that you should avoid using `-'
on integers that may have the value of `-2^63'!
`ABS(X)'
Returns the absolute value of `X':
mysql> select ABS(2);
-> 2
mysql> select ABS(-32);
-> 32
This function is safe to use with `BIGINT' values.
`SIGN(X)'
Returns the sign of the argument as `-1', `0', or `1', depending
on whether `X' is negative, zero, or positive:
mysql> select SIGN(-32);
-> -1
mysql> select SIGN(0);
-> 0
mysql> select SIGN(234);
-> 1
`MOD(N,M)'
`%'
Modulo (like the `%' operator in C). Returns the remainder of `N'
divided by `M':
mysql> select MOD(234, 10);
-> 4
mysql> select 253 % 7;
-> 1
mysql> select MOD(29,9);
-> 2
This function is safe to use with `BIGINT' values.
`FLOOR(X)'
Returns the largest integer value not greater than `X':
mysql> select FLOOR(1.23);
-> 1
mysql> select FLOOR(-1.23);
-> -2
Note that the return value is converted to a `BIGINT'!
`CEILING(X)'
Returns the smallest integer value not less than `X':
mysql> select CEILING(1.23);
-> 2
mysql> select CEILING(-1.23);
-> -1
Note that the return value is converted to a `BIGINT'!
`ROUND(X)'
Returns the argument `X', rounded to the nearest integer:
mysql> select ROUND(-1.23);
-> -1
mysql> select ROUND(-1.58);
-> -2
mysql> select ROUND(1.58);
-> 2
`ROUND(X,D)'
Returns the argument `X', rounded to a number with `D' decimals.
If `D' is `0', the result will have no decimal point or fractional
part:
mysql> select ROUND(1.298, 1);
-> 1.3
mysql> select ROUND(1.298, 0);
-> 1
`EXP(X)'
Returns the value of `e' (the base of natural logarithms) raised to
the power of `X':
mysql> select EXP(2);
-> 7.389056
mysql> select EXP(-2);
-> 0.135335
`LOG(X)'
Returns the natural logarithm of `X':
mysql> select LOG(2);
-> 0.693147
mysql> select LOG(-2);
-> NULL
If you want the log of a number `X' to some arbitary base `B', use
the formula `LOG(X)/LOG(B)'.
`LOG10(X)'
Returns the base-10 logarithm of `X':
mysql> select LOG10(2);
-> 0.301030
mysql> select LOG10(100);
-> 2.000000
mysql> select LOG10(-100);
-> NULL
`POW(X,Y)'
`POWER(X,Y)'
Returns the value of `X' raised to the power of `Y':
mysql> select POW(2,2);
-> 4.000000
mysql> select POW(2,-2);
-> 0.250000
`SQRT(X)'
Returns the non-negative square root of `X':
mysql> select SQRT(4);
-> 2.000000
mysql> select SQRT(20);
-> 4.472136
`PI()'
Returns the value of PI:
mysql> select PI();
-> 3.141593
`COS(X)'
Returns the cosine of `X', where `X' is given in radians:
mysql> select COS(PI());
-> -1.000000
`SIN(X)'
Returns the sine of `X', where `X' is given in radians:
mysql> select SIN(PI());
-> 0.000000
`TAN(X)'
Returns the tangent of `X', where `X' is given in radians:
mysql> select TAN(PI()+1);
-> 1.557408
`ACOS(X)'
Returns the arc cosine of `X', that is, the value whose cosine is
`X'. Returns `NULL' if `X' is not in the range `-1' to `1':
mysql> select ACOS(1);
-> 0.000000
mysql> select ACOS(1.0001);
-> NULL
mysql> select ACOS(0);
-> 1.570796
`ASIN(X)'
Returns the arc sine of `X', that is, the value whose sine is `X'.
Returns `NULL' if `X' is not in the range `-1' to `1':
mysql> select ASIN(0.2);
-> 0.201358
mysql> select ASIN('foo');
-> 0.000000
`ATAN(X)'
Returns 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
`ATAN2(Y,X)'
Returns 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
`COT(X)'
Returns the cotangent of `X':
mysql> select COT(12);
-> -1.57267341
mysql> select COT(0);
-> NULL
`RAND()'
`RAND(N)'
Returns a random floating-point value in the range `0' to `1.0'.
If an integer argument `N' is specified, it is used as the seed
value:
mysql> select RAND();
-> 0.5925
mysql> select RAND(20);
-> 0.1811
mysql> select RAND(20);
-> 0.1811
mysql> select RAND();
-> 0.2079
mysql> select RAND();
-> 0.7888
You can't use a column with `RAND()' values in an `ORDER BY'
clause, because `ORDER BY' would evaluate the column multiple
times. In *MySQL* Version 3.23, you can, however, do: `SELECT *
FROM table_name ORDER BY RAND()'
This is useful to get a random sample of a set `SELECT * FROM
table1,table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000'.
Note that a `RAND()' in a `WHERE' clause will be re-evaluated
every time the `WHERE' is executed.
`LEAST(X,Y,...)'
With two or more arguments, returns the smallest (minimum-valued)
argument. The arguments are compared using the following rules:
* If the return value is used in an `INTEGER' context, or all
arguments are integer-valued, they are compared as integers.
* If the return value is used in a `REAL' context, or all
arguments are real-valued, they are compared as reals.
* If any argument is a case-sensitive string, the arguments are
compared as case-sensitive strings.
* In other cases, the arguments are compared as
case-insensitive strings:
mysql> select LEAST(2,0);
-> 0
mysql> select LEAST(34.0,3.0,5.0,767.0);
-> 3.0
mysql> select LEAST("B","A","C");
-> "A"
In *MySQL* versions prior to Version 3.22.5, you can use `MIN()'
instead of `LEAST'.
`GREATEST(X,Y,...)'
Returns the largest (maximum-valued) argument. The arguments are
compared using the same rules as for `LEAST':
mysql> select GREATEST(2,0);
-> 2
mysql> select GREATEST(34.0,3.0,5.0,767.0);
-> 767.0
mysql> select GREATEST("B","A","C");
-> "C"
In *MySQL* versions prior to Version 3.22.5, you can use `MAX()'
instead of `GREATEST'.
`DEGREES(X)'
Returns the argument `X', converted from radians to degrees:
mysql> select DEGREES(PI());
-> 180.000000
`RADIANS(X)'
Returns the argument `X', converted from degrees to radians:
mysql> select RADIANS(90);
-> 1.570796
`TRUNCATE(X,D)'
Returns the number `X', truncated to `D' decimals. If `D' is `0',
the result will have no decimal point or fractional part:
mysql> select TRUNCATE(1.223,1);
-> 1.2
mysql> select TRUNCATE(1.999,1);
-> 1.9
mysql> select TRUNCATE(1.999,0);
-> 1
Note that as decimal numbers are normally not stored as exact
numbers in computers, but as double values, you may be fooled by
the following result:
mysql> select TRUNCATE(10.28*100,0);
-> 1027
The above happens because 10.28 is actually stored as something
like 10.2799999999999999.
String Functions
----------------
String-valued functions return `NULL' if the length of the result would
be greater than the `max_allowed_packet' server parameter. *Note
Server parameters::.
For functions that operate on string positions, the first position is
numbered 1.
`ASCII(str)'
Returns the ASCII code value of the leftmost character of the
string `str'. Returns `0' if `str' is the empty string. Returns
`NULL' if `str' is `NULL':
mysql> select ASCII('2');
-> 50
mysql> select ASCII(2);
-> 50
mysql> select ASCII('dx');
-> 100
See also the `ORD()' function.
`ORD(str)'
If the leftmost character of the string str is a multi-byte
character, returns the code of multi-byte character by returning
the ASCII code value of the character in the format of: `((first
byte ASCII code)*256+(second byte ASCII code))[*256+third byte
ASCII code...]'. If the leftmost character is not a multi-byte
character, returns the same value as the like `ASCII()' function
does:
mysql> select ORD('2');
-> 50
`CONV(N,from_base,to_base)'
Converts numbers between different number bases. Returns a string
representation of the number `N', converted from base `from_base'
to base `to_base'. Returns `NULL' if any argument is `NULL'. The
argument `N' is interpreted as an integer, but may be specified as
an integer or a string. The minimum base is `2' and the maximum
base is `36'. If `to_base' is a negative number, `N' is regarded
as a signed number. Otherwise, `N' is treated as unsigned.
`CONV' works with 64-bit precision:
mysql> select CONV("a",16,2);
-> '1010'
mysql> select CONV("6E",18,8);
-> '172'
mysql> select CONV(-17,10,-18);
-> '-H'
mysql> select CONV(10+"10"+'10'+0xa,10,10);
-> '40'
`BIN(N)'
Returns a string representation of the binary value of `N', where
`N' is a longlong (`BIGINT') number. This is equivalent to
`CONV(N,10,2)'. Returns `NULL' if `N' is `NULL':
mysql> select BIN(12);
-> '1100'
`OCT(N)'
Returns a string representation of the octal value of `N', where
`N' is a longlong number. This is equivalent to `CONV(N,10,8)'.
Returns `NULL' if `N' is `NULL':
mysql> select OCT(12);
-> '14'
`HEX(N)'
Returns a string representation of the hexadecimal value of `N',
where `N' is a longlong (`BIGINT') number. This is equivalent to
`CONV(N,10,16)'. Returns `NULL' if `N' is `NULL':
mysql> select HEX(255);
-> 'FF'
`CHAR(N,...)'
`CHAR()' interprets the arguments as integers and returns a string
consisting of the characters given by the ASCII code values of
those integers. `NULL' values are skipped:
mysql> select CHAR(77,121,83,81,'76');
-> 'MySQL'
mysql> select CHAR(77,77.3,'77.3');
-> 'MMM'
`CONCAT(str1,str2,...)'
Returns the string that results from concatenating the arguments.
Returns `NULL' if any argument is `NULL'. May have more than 2
arguments. A numeric argument is converted to the equivalent
string form:
mysql> select CONCAT('My', 'S', 'QL');
-> 'MySQL'
mysql> select CONCAT('My', NULL, 'QL');
-> NULL
mysql> select CONCAT(14.3);
-> '14.3'
`CONCAT_WS(separator, str1, str2,...)'
`CONCAT_WS()' stands for CONCAT With Separator and is a special
form of `CONCAT()'. The first argument is the separator for the
rest of the arguments. The separator can be a string as well as
the rest of the arguments. If the separator is `NULL', the result
will be `NULL'. The function will skip any `NULL's and empty
strings, after the separator argument. The separator will be added
between the strings to be concatenated:
mysql> select CONCAT_WS(",","First name","Second name","Last Name");
-> 'First name,Second name,Last Name'
mysql> select CONCAT_WS(",","First name",NULL,"Last Name");
-> 'First name,Last Name'
`LENGTH(str)'
`OCTET_LENGTH(str)'
`CHAR_LENGTH(str)'
`CHARACTER_LENGTH(str)'
Returns the length of the string `str':
mysql> select LENGTH('text');
-> 4
mysql> select OCTET_LENGTH('text');
-> 4
Note that for `CHAR_LENGTH()', multi-byte characters are only
counted once.
`LOCATE(substr,str)'
`POSITION(substr IN str)'
Returns the position of the first occurrence of substring `substr'
in string `str'. Returns `0' if `substr' is not in `str':
mysql> select LOCATE('bar', 'foobarbar');
-> 4
mysql> select LOCATE('xbar', 'foobar');
-> 0
This function is multi-byte safe.
`LOCATE(substr,str,pos)'
Returns the position of the first occurrence of substring `substr'
in string `str', starting at position `pos'. Returns `0' if
`substr' is not in `str':
mysql> select LOCATE('bar', 'foobarbar',5);
-> 7
This function is multi-byte safe.
`INSTR(str,substr)'
Returns the position of the first occurrence of substring `substr'
in string `str'. This is the same as the two-argument form of
`LOCATE()', except that the arguments are swapped:
mysql> select INSTR('foobarbar', 'bar');
-> 4
mysql> select INSTR('xbar', 'foobar');
-> 0
This function is multi-byte safe.
`LPAD(str,len,padstr)'
Returns the string `str', left-padded with the string `padstr'
until `str' is `len' characters long. If `str' is longer than
`len'' then it will be shortened to `len' characters.
mysql> select LPAD('hi',4,'??');
-> '??hi'
`RPAD(str,len,padstr)'
Returns the string `str', right-padded with the string `padstr'
until `str' is `len' characters long. If `str' is longer than
`len'' then it will be shortened to `len' characters.
mysql> select RPAD('hi',5,'?');
-> 'hi???'
`LEFT(str,len)'
Returns the leftmost `len' characters from the string `str':
mysql> select LEFT('foobarbar', 5);
-> 'fooba'
This function is multi-byte safe.
`RIGHT(str,len)'
Returns the rightmost `len' characters from the string `str':
mysql> select RIGHT('foobarbar', 4);
-> 'rbar'
This function is multi-byte safe.
`SUBSTRING(str,pos,len)'
`SUBSTRING(str FROM pos FOR len)'
`MID(str,pos,len)'
Returns a substring `len' characters long from string `str',
starting at position `pos'. The variant form that uses `FROM' is
ANSI SQL92 syntax:
mysql> select SUBSTRING('Quadratically',5,6);
-> 'ratica'
This function is multi-byte safe.
`SUBSTRING(str,pos)'
`SUBSTRING(str FROM pos)'
Returns a substring from string `str' starting at position `pos':
mysql> select SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> select SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
This function is multi-byte safe.
`SUBSTRING_INDEX(str,delim,count)'
Returns the substring from string `str' before `count' occurrences
of the delimiter `delim'. If `count' is positive, everything to
the left of the final delimiter (counting from the left) is
returned. If `count' is negative, everything to the right of the
final delimiter (counting from the right) is returned:
mysql> select SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> select SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'
This function is multi-byte safe.
`LTRIM(str)'
Returns the string `str' with leading space characters removed:
mysql> select LTRIM(' barbar');
-> 'barbar'
`RTRIM(str)'
Returns the string `str' with trailing space characters removed:
mysql> select RTRIM('barbar ');
-> 'barbar'
This function is multi-byte safe.
`TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)'
Returns the string `str' with all `remstr' prefixes and/or suffixes
removed. If none of the specifiers `BOTH', `LEADING' or `TRAILING'
are given, `BOTH' is assumed. If `remstr' is not specified, spaces
are removed:
mysql> select TRIM(' bar ');
-> 'bar'
mysql> select TRIM(LEADING 'x' FROM 'xxxbarxxx');
-> 'barxxx'
mysql> select TRIM(BOTH 'x' FROM 'xxxbarxxx');
-> 'bar'
mysql> select TRIM(TRAILING 'xyz' FROM 'barxxyz');
-> 'barx'
This function is multi-byte safe.
`SOUNDEX(str)'
Returns a soundex string from `str'. Two strings that sound almost
the same should have identical soundex strings. A standard soundex
string is 4 characters long, but the `SOUNDEX()' function returns
an arbitrarily long string. You can use `SUBSTRING()' on the
result to get a standard soundex string. All non-alphanumeric
characters are ignored in the given string. All international
alpha characters outside the A-Z range are treated as vowels:
mysql> select SOUNDEX('Hello');
-> 'H400'
mysql> select SOUNDEX('Quadratically');
-> 'Q36324'
`SPACE(N)'
Returns a string consisting of `N' space characters:
mysql> select SPACE(6);
-> ' '
`REPLACE(str,from_str,to_str)'
Returns the string `str' with all all occurrences of the string
`from_str' replaced by the string `to_str':
mysql> select REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'
This function is multi-byte safe.
`REPEAT(str,count)'
Returns a string consisting of the string `str' repeated `count'
times. If `count <= 0', returns an empty string. Returns `NULL' if
`str' or `count' are `NULL':
mysql> select REPEAT('MySQL', 3);
-> 'MySQLMySQLMySQL'
`REVERSE(str)'
Returns the string `str' with the order of the characters reversed:
mysql> select REVERSE('abc');
-> 'cba'
This function is multi-byte safe.
`INSERT(str,pos,len,newstr)'
Returns the string `str', with the substring beginning at position
`pos' and `len' characters long replaced by the string `newstr':
mysql> select INSERT('Quadratic', 3, 4, 'What');
-> 'QuWhattic'
This function is multi-byte safe.
`ELT(N,str1,str2,str3,...)'
Returns `str1' if `N' = `1', `str2' if `N' = `2', and so on.
Returns `NULL' if `N' is less than `1' or greater than the number
of arguments. `ELT()' is the complement of `FIELD()':
mysql> select ELT(1, 'ej', 'Heja', 'hej', 'foo');
-> 'ej'
mysql> select ELT(4, 'ej', 'Heja', 'hej', 'foo');
-> 'foo'
`FIELD(str,str1,str2,str3,...)'
Returns the index of `str' in the `str1', `str2', `str3', `...'
list. Returns `0' if `str' is not found. `FIELD()' is the
complement of `ELT()':
mysql> select FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
-> 2
mysql> select FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
-> 0
`FIND_IN_SET(str,strlist)'
Returns a value `1' to `N' if the string `str' is in the list
`strlist' consisting of `N' substrings. A string list is a string
composed of substrings separated by `,' characters. If the first
argument is a constant string and the second is a column of type
`SET', the `FIND_IN_SET()' function is optimized to use bit
arithmetic! Returns `0' if `str' is not in `strlist' or if
`strlist' is the empty string. Returns `NULL' if either argument
is `NULL'. This function will not work properly if the first
argument contains a `,':
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
-> 2
`MAKE_SET(bits,str1,str2,...)'
Returns a set (a string containing substrings separated by `,'
characters) consisting of the strings that have the corresponding
bit in `bits' set. `str1' corresponds to bit 0, `str2' to bit 1,
etc. `NULL' strings in `str1', `str2', `...' are not appended to
the result:
mysql> SELECT MAKE_SET(1,'a','b','c');
-> 'a'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
-> 'hello,world'
mysql> SELECT MAKE_SET(0,'a','b','c');
-> ''
`EXPORT_SET(bits,on,off,[separator,[number_of_bits]])'
Returns a string where for every bit set in 'bit', you get an 'on'
string and for every reset bit you get an 'off' string. Each
string is separated with 'separator' (default ',') and only
'number_of_bits' (default 64) of 'bits' is used:
mysql> select EXPORT_SET(5,'Y','N',',',4)
-> Y,N,Y,N
`LCASE(str)'
`LOWER(str)'
Returns the string `str' with all characters changed to lowercase
according to the current character set mapping (the default is
ISO-8859-1 Latin1):
mysql> select LCASE('QUADRATICALLY');
-> 'quadratically'
This function is multi-byte safe.
`UCASE(str)'
`UPPER(str)'
Returns the string `str' with all characters changed to uppercase
according to the current character set mapping (the default is
ISO-8859-1 Latin1):
mysql> select UCASE('Hej');
-> 'HEJ'
This function is multi-byte safe.
`LOAD_FILE(file_name)'
Reads the file and returns the file contents as a string. The file
must be on the server, you must specify the full pathname to the
file, and you must have the *file* privilege. The file must be
readable by all and be smaller than `max_allowed_packet'.
If the file doesn't exist or can't be read due to one of the above
reasons, the function returns `NULL':
mysql> UPDATE table_name
SET blob_column=LOAD_FILE("/tmp/picture")
WHERE id=1;
If you are not using *MySQL* Version 3.23, you have to do the reading
of the file inside your application and create an `INSERT' statement to
update the database with the file information. One way to do this, if
you are using the *MySQL*++ library, can be found at
`http://www.mysql.com/documentation/mysql++/mysql++-examples.html'.
*MySQL* automatically converts numbers to strings as necessary, and
vice-versa:
mysql> SELECT 1+"1";
-> 2
mysql> SELECT CONCAT(2,' test');
-> '2 test'
If you want to convert a number to a string explicitly, pass it as the
argument to `CONCAT()'.
If a string function is given a binary string as an argument, the
resulting string is also a binary string. A number converted to a
string is treated as a binary string. This only affects comparisons.
Date and Time Functions
-----------------------
See *Note Date and time types:: for a description of the range of values
each type has and the valid formats in which date and time values may be
specified.
Here is an example that uses date functions. The query below selects
all records with a `date_col' value from within the last 30 days:
mysql> SELECT something FROM table
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;
`DAYOFWEEK(date)'
Returns the weekday index
for `date' (`1' = Sunday, `2' = Monday, ... `7' = Saturday).
These index values correspond to the ODBC standard:
mysql> select DAYOFWEEK('1998-02-03');
-> 3
`WEEKDAY(date)'
Returns the weekday index for `date' (`0' = Monday, `1' = Tuesday,
... `6' = Sunday):
mysql> select WEEKDAY('1997-10-04 22:23:00');
-> 5
mysql> select WEEKDAY('1997-11-05');
-> 2
`DAYOFMONTH(date)'
Returns the day of the month for `date', in the range `1' to `31':
mysql> select DAYOFMONTH('1998-02-03');
-> 3
`DAYOFYEAR(date)'
Returns the day of the year for `date', in the range `1' to `366':
mysql> select DAYOFYEAR('1998-02-03');
-> 34
`MONTH(date)'
Returns the month for `date', in the range `1' to `12':
mysql> select MONTH('1998-02-03');
-> 2
`DAYNAME(date)'
Returns the name of the weekday for `date':
mysql> select DAYNAME("1998-02-05");
-> 'Thursday'
`MONTHNAME(date)'
Returns the name of the month for `date':
mysql> select MONTHNAME("1998-02-05");
-> 'February'
`QUARTER(date)'
Returns the quarter of the year for `date', in the range `1' to
`4':
mysql> select QUARTER('98-04-01');
-> 2
`WEEK(date)'
`WEEK(date,first)'
With a single argument, returns the week for `date', in the range
`0' to `53' (yes, there may be the beginnings of a week 53), for
locations where Sunday is the first day of the week. The
two-argument form of `WEEK()' allows you to specify whether the
week starts on Sunday or Monday. The week starts on Sunday if the
second argument is `0', on Monday if the second argument is `1':
mysql> select WEEK('1998-02-20');
-> 7
mysql> select WEEK('1998-02-20',0);
-> 7
mysql> select WEEK('1998-02-20',1);
-> 8
mysql> select WEEK('1998-12-31',1);
-> 53
`YEAR(date)'
Returns the year for `date', in the range `1000' to `9999':
mysql> select YEAR('98-02-03');
-> 1998
`YEARWEEK(date)'
`YEARWEEK(date,first)'
Returns year and week for a date. The second arguments works
exactly like the second argument to `WEEK()'. Note that the year
may be different from the year in the date argument for the first
and the last week of the year:
mysql> select YEARWEEK('1987-01-01');
-> 198653
`HOUR(time)'
Returns the hour for `time', in the range `0' to `23':
mysql> select HOUR('10:05:03');
-> 10
`MINUTE(time)'
Returns the minute for `time', in the range `0' to `59':
mysql> select MINUTE('98-02-03 10:05:03');
-> 5
`SECOND(time)'
Returns the second for `time', in the range `0' to `59':
mysql> select SECOND('10:05:03');
-> 3
`PERIOD_ADD(P,N)'
Adds `N' months to period `P' (in the format `YYMM' or `YYYYMM').
Returns a value in the format `YYYYMM'.
Note that the period argument `P' is _not_ a date value:
mysql> select PERIOD_ADD(9801,2);
-> 199803
`PERIOD_DIFF(P1,P2)'
Returns the number of months between periods `P1' and `P2'. `P1'
and `P2' should be in the format `YYMM' or `YYYYMM'.
Note that the period arguments `P1' and `P2' are _not_ date values:
mysql> select PERIOD_DIFF(9802,199703);
-> 11
`DATE_ADD(date,INTERVAL expr type)'
`DATE_SUB(date,INTERVAL expr type)'
`ADDDATE(date,INTERVAL expr type)'
`SUBDATE(date,INTERVAL expr type)'
These functions perform date arithmetic. They are new for *MySQL*
Version 3.22. `ADDDATE()' and `SUBDATE()' are synonyms for
`DATE_ADD()' and `DATE_SUB()'.
In *MySQL* Version 3.23, you can use `+' and `-' instead of
`DATE_ADD()' and `DATE_SUB()' if the expression on the right side
is a date or datetime column. (See example)
`date' is a `DATETIME' or `DATE' value specifying the starting
date. `expr' is an expression specifying the interval value to be
added or substracted from the starting date. `expr' is a string;
it may start with a `-' for negative intervals. `type' is a
keyword indicating how the expression should be interpreted.
The `EXTRACT(type FROM date)' function returns the 'type' interval
from the date.
The following table shows how the `type' and `expr' arguments are
related:
`type' *value* *Expected* `expr' *format*
`SECOND' `SECONDS'
`MINUTE' `MINUTES'
`HOUR' `HOURS'
`DAY' `DAYS'
`MONTH' `MONTHS'
`YEAR' `YEARS'
`MINUTE_SECOND' `"MINUTES:SECONDS"'
`HOUR_MINUTE' `"HOURS:MINUTES"'
`DAY_HOUR' `"DAYS HOURS"'
`YEAR_MONTH' `"YEARS-MONTHS"'
`HOUR_SECOND' `"HOURS:MINUTES:SECONDS"'
`DAY_MINUTE' `"DAYS HOURS:MINUTES"'
`DAY_SECOND' `"DAYS HOURS:MINUTES:SECONDS"'
*MySQL* allows any punctuation delimiter in the `expr' format.
Those shown in the table are the suggested delimiters. If the
`date' argument is a `DATE' value and your calculations involve
only `YEAR', `MONTH', and `DAY' parts (that is, no time parts), the
result is a `DATE' value. Otherwise the result is a `DATETIME'
value:
mysql> SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
-> 1998-01-01 00:00:00
mysql> SELECT INTERVAL 1 DAY + "1997-12-31";
-> 1998-01-01
mysql> SELECT "1998-01-01" - INTERVAL 1 SECOND;
-> 1997-12-31 23:59:59
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
INTERVAL 1 SECOND);
-> 1998-01-01 00:00:00
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
INTERVAL 1 DAY);
-> 1998-01-01 23:59:59
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
INTERVAL "1:1" MINUTE_SECOND);
-> 1998-01-01 00:01:00
mysql> SELECT DATE_SUB("1998-01-01 00:00:00",
INTERVAL "1 1:1:1" DAY_SECOND);
-> 1997-12-30 22:58:59
mysql> SELECT DATE_ADD("1998-01-01 00:00:00",
INTERVAL "-1 10" DAY_HOUR);
-> 1997-12-30 14:00:00
mysql> SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);
-> 1997-12-02
mysql> SELECT EXTRACT(YEAR FROM "1999-07-02");
-> 1999
mysql> SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03");
-> 199907
mysql> SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03");
-> 20102
If you specify an interval value that is too short (does not
include all the interval parts that would be expected from the
`type' keyword), *MySQL* assumes you have left out the leftmost
parts of the interval value. For example, if you specify a `type'
of `DAY_SECOND', the value of `expr' is expected to have days,
hours, minutes, and seconds parts. If you specify a value like
`"1:10"', *MySQL* assumes that the days and hours parts are
missing and the value represents minutes and seconds. In other
words, `"1:10" DAY_SECOND' is interpreted in such a way that it is
equivalent to `"1:10" MINUTE_SECOND'. This is analogous to the
way that *MySQL* interprets `TIME' values as representing elapsed
time rather than as time of day.
Note that if you add or subtract a date value against something
that contains a time part, the date value will be automatically
converted to a datetime value:
mysql> select date_add("1999-01-01", interval 1 day);
-> 1999-01-02
mysql> select date_add("1999-01-01", interval 1 hour);
-> 1999-01-01 01:00:00
If you use really incorrect dates, the result is `NULL'. If you add
`MONTH', `YEAR_MONTH', or `YEAR' and the resulting date has a day
that is larger than the maximum day for the new month, the day is
adjusted to the maximum days in the new month:
mysql> select DATE_ADD('1998-01-30', Interval 1 month);
-> 1998-02-28
Note from the preceding example that the word `INTERVAL' and the
`type' keyword are not case sensitive.
`TO_DAYS(date)'
Given a date `date', returns a daynumber (the number of days since
year 0):
mysql> select TO_DAYS(950501);
-> 728779
mysql> select TO_DAYS('1997-10-07');
-> 729669
`TO_DAYS()' is not intended for use with values that precede the
advent of the Gregorian calendar (1582), because it doesn't take
into account the days that were lost when the calender was changed.
`FROM_DAYS(N)'
Given a daynumber `N', returns a `DATE' value:
mysql> select FROM_DAYS(729669);
-> '1997-10-07'
`FROM_DAYS()' is not intended for use with values that precede the
advent of the Gregorian calendar (1582), because it doesn't take
into account the days that were lost when the calender was changed.
`DATE_FORMAT(date,format)'
Formats the `date' value according to the `format' string. The
following specifiers may be used in the `format' string:
`%M' Month name (`January'..`December')
`%W' Weekday name (`Sunday'..`Saturday')
`%D' Day of the month with English suffix
(`1st', `2nd', `3rd', etc.)
`%Y' Year, numeric, 4 digits
`%y' Year, numeric, 2 digits
`%X' Year for the week where Sunday is the
first day of the week, numeric, 4
digits, used with '%V'
`%x' Year for the week, where Monday is the
first day of the week, numeric, 4
digits, used with '%v'
`%a' Abbreviated weekday name (`Sun'..`Sat')
`%d' Day of the month, numeric (`00'..`31')
`%e' Day of the month, numeric (`0'..`31')
`%m' Month, numeric (`01'..`12')
`%c' Month, numeric (`1'..`12')
`%b' Abbreviated month name (`Jan'..`Dec')
`%j' Day of year (`001'..`366')
`%H' Hour (`00'..`23')
`%k' Hour (`0'..`23')
`%h' Hour (`01'..`12')
`%I' Hour (`01'..`12')
`%l' Hour (`1'..`12')
`%i' Minutes, numeric (`00'..`59')
`%r' Time, 12-hour (`hh:mm:ss [AP]M')
`%T' Time, 24-hour (`hh:mm:ss')
`%S' Seconds (`00'..`59')
`%s' Seconds (`00'..`59')
`%p' `AM' or `PM'
`%w' Day of the week
(`0'=Sunday..`6'=Saturday)
`%U' Week (`0'..`53'), where Sunday is the
first day of the week
`%u' Week (`0'..`53'), where Monday is the
first day of the week
`%V' Week (`1'..`53'), where Sunday is the
first day of the week. Used with '%X'
`%v' Week (`1'..`53'), where Monday is the
first day of the week. Used with '%x'
`%%' A literal `%'.
All other characters are just copied to the result without
interpretation:
mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
-> 'Saturday October 1997'
mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> select DATE_FORMAT('1997-10-04 22:23:00',
'%D %y %a %d %m %b %j');
-> '4th 97 Sat 04 10 Oct 277'
mysql> select DATE_FORMAT('1997-10-04 22:23:00',
'%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> select DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'
As of *MySQL* Version 3.23, the `%' character is required before
format specifier characters. In earlier versions of *MySQL*, `%'
was optional.
`TIME_FORMAT(time,format)'
This is used like the `DATE_FORMAT()' function above, but the
`format' string may contain only those format specifiers that
handle hours, minutes, and seconds. Other specifiers produce a
`NULL' value or `0'.
`CURDATE()'
`CURRENT_DATE'
Returns today's date as a value in `'YYYY-MM-DD'' or `YYYYMMDD'
format, depending on whether the function is used in a string or
numeric context:
mysql> select CURDATE();
-> '1997-12-15'
mysql> select CURDATE() + 0;
-> 19971215
`CURTIME()'
`CURRENT_TIME'
Returns the current time as a value in `'HH:MM:SS'' or `HHMMSS'
format, depending on whether the function is used in a string or
numeric context:
mysql> select CURTIME();
-> '23:50:26'
mysql> select CURTIME() + 0;
-> 235026
`NOW()'
`SYSDATE()'
`CURRENT_TIMESTAMP'
Returns the current date and time as a value in `'YYYY-MM-DD
HH:MM:SS'' or `YYYYMMDDHHMMSS' format, depending on whether the
function is used in a string or numeric context:
mysql> select NOW();
-> '1997-12-15 23:50:26'
mysql> select NOW() + 0;
-> 19971215235026
`UNIX_TIMESTAMP()'
`UNIX_TIMESTAMP(date)'
If called with no argument, returns a Unix timestamp (seconds since
`'1970-01-01 00:00:00'' GMT). If `UNIX_TIMESTAMP()' is called with
a `date' argument, it returns the value of the argument as seconds
since `'1970-01-01 00:00:00'' GMT. `date' may be a `DATE' string,
a `DATETIME' string, a `TIMESTAMP', or a number in the format
`YYMMDD' or `YYYYMMDD' in local time:
mysql> select UNIX_TIMESTAMP();
-> 882226357
mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00');
-> 875996580
When `UNIX_TIMESTAMP' is used on a `TIMESTAMP' column, the function
will receive the value directly, with no implicit
"string-to-unix-timestamp" conversion. If you give
`UNIX_TIMESTAMP()' a wrong or out-of-range date, it will return 0.
`FROM_UNIXTIME(unix_timestamp)'
Returns a representation of the `unix_timestamp' argument as a
value in `'YYYY-MM-DD HH:MM:SS'' or `YYYYMMDDHHMMSS' format,
depending on whether the function is used in a string or numeric
context:
mysql> select FROM_UNIXTIME(875996580);
-> '1997-10-04 22:23:00'
mysql> select FROM_UNIXTIME(875996580) + 0;
-> 19971004222300
`FROM_UNIXTIME(unix_timestamp,format)'
Returns a string representation of the Unix timestamp, formatted
according to the `format' string. `format' may contain the same
specifiers as those listed in the entry for the `DATE_FORMAT()'
function:
mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP(),
'%Y %D %M %h:%i:%s %x');
-> '1997 23rd December 03:43:30 x'
`SEC_TO_TIME(seconds)'
Returns the `seconds' argument, converted to hours, minutes, and
seconds, as a value in `'HH:MM:SS'' or `HHMMSS' format, depending
on whether the function is used in a string or numeric context:
mysql> select SEC_TO_TIME(2378);
-> '00:39:38'
mysql> select SEC_TO_TIME(2378) + 0;
-> 3938
`TIME_TO_SEC(time)'
Returns the `time' argument, converted to seconds:
mysql> select TIME_TO_SEC('22:23:00');
-> 80580
mysql> select TIME_TO_SEC('00:39:38');
-> 2378
Miscellaneous Functions
-----------------------
`DATABASE()'
Returns the current database name:
mysql> select DATABASE();
-> 'test'
If there is no current database, `DATABASE()' returns the empty
string.
`USER()'
`SYSTEM_USER()'
`SESSION_USER()'
Returns the current *MySQL* user name:
mysql> select USER();
-> 'davida@localhost'
In *MySQL* Version 3.22.11 or later, this includes the client
hostname as well as the user name. You can extract just the user
name part like this (which works whether or not the value includes
a hostname part):
mysql> select substring_index(USER(),"@",1);
-> 'davida'
`PASSWORD(str)'
Calculates a password string from the plaintext password `str'.
This is the function that is used for encrypting *MySQL* passwords
for storage in the `Password' column of the `user' grant table:
mysql> select PASSWORD('badpwd');
-> '7f84554057dd964b'
`PASSWORD()' encryption is non-reversible.
`PASSWORD()' does not perform password encryption in the same way
that Unix passwords are encrypted. You should not assume that if
your Unix password and your *MySQL* password are the same,
`PASSWORD()' will result in the same encrypted value as is stored
in the Unix password file. See `ENCRYPT()'.
`ENCRYPT(str[,salt])'
Encrypt `str' using the Unix `crypt()' system call. The `salt'
argument should be a string with two characters. (As of *MySQL*
Version 3.22.16, `salt' may be longer than two characters.):
mysql> select ENCRYPT("hello");
-> 'VxuFAJXVARROc'
If `crypt()' is not available on your system, `ENCRYPT()' always
returns `NULL'.
`ENCRYPT()' ignores all but the first 8 characters of `str', at
least on some systems. This will be determined by the behavior of
the underlying `crypt()' system call.
`ENCODE(str,pass_str)'
Encrypt `str' using `pass_str' as the password. To decrypt the
result, use `DECODE()'.
The results is a binary string of the same length as `string'. If
you want to save it in a column, use a `BLOB' column type.
`DECODE(crypt_str,pass_str)'
Descrypts the encrypted string `crypt_str' using `pass_str' as the
password. `crypt_str' should be a string returned from `ENCODE()'.
`MD5(string)'
Calculates a MD5 checksum for the string. Value is returned as a
32 long hex number that may, for example, be used as a hash key:
mysql> select MD5("testing")
-> 'ae2b1fca515949e5d54fb22b8ed95575'
This is an "RSA Data Security, Inc. MD5 Message-Digest Algorithm".
`LAST_INSERT_ID([expr])'
Returns the last automatically generated value that was inserted
into an `AUTO_INCREMENT' column. *Note `mysql_insert_id()':
mysql_insert_id.
mysql> select LAST_INSERT_ID();
-> 195
The last ID that was generated is maintained in the server on a
per-connection basis. It will not be changed by another client.
It will not even be changed if you update another `AUTO_INCREMENT'
column with a non-magic value (that is, a value that is not `NULL'
and not `0').
If `expr' is given as an argument to `LAST_INSERT_ID()' in an
`UPDATE' clause, then the value of the argument is returned as a
`LAST_INSERT_ID()' value. This can be used to simulate sequences.
First create the table:
mysql> create table sequence (id int not null);
mysql> insert into sequence values (0);
Then the table can be used to generate sequence numbers like this:
mysql> update sequence set id=LAST_INSERT_ID(id+1);
You can generate sequences without calling `LAST_INSERT_ID()', but
the utility of using the function this way is that the ID value is
maintained in the server as the last automatically generated
value. You can retrieve the new ID as you would read any normal
`AUTO_INCREMENT' value in *MySQL*. For example,
`LAST_INSERT_ID()' (without an argument) will return the new ID.
The C API function `mysql_insert_id()' can also be used to get the
value.
`FORMAT(X,D)'
Formats the number `X' to a format like `'#,###,###.##'', rounded
to `D' decimals. If `D' is `0', the result will have no decimal
point or fractional part:
mysql> select FORMAT(12332.123456, 4);
-> '12,332.1235'
mysql> select FORMAT(12332.1,4);
-> '12,332.1000'
mysql> select FORMAT(12332.2,0);
-> '12,332'
`VERSION()'
Returns a string indicating the *MySQL* server version:
mysql> select VERSION();
-> '3.23.13-log'
Note that if your version ends with `-log' this means that logging
is enabled.
`CONNECTION_ID()'
Returns the connection id (`thread_id') for the connection. Every
connection has its own unique id:
mysql> select CONNECTION_ID();
-> 1
`GET_LOCK(str,timeout)'
Tries to obtain a lock with a name given by the string `str', with
a timeout of `timeout' seconds. Returns `1' if the lock was
obtained successfully, `0' if the attempt timed out, or `NULL' if
an error occurred (such as running out of memory or the thread was
killed with `mysqladmin kill'). A lock is released when you
execute `RELEASE_LOCK()', execute a new `GET_LOCK()', or the thread
terminates. This function can be used to implement application
locks or to simulate record locks. It blocks requests by other
clients for locks with the same name; clients that agree on a
given lock string name can use the string to perform cooperative
advisory locking:
mysql> select GET_LOCK("lock1",10);
-> 1
mysql> select GET_LOCK("lock2",10);
-> 1
mysql> select RELEASE_LOCK("lock2");
-> 1
mysql> select RELEASE_LOCK("lock1");
-> NULL
Note that the second `RELEASE_LOCK()' call returns `NULL' because
the lock `"lock1"' was automatically released by the second
`GET_LOCK()' call.
`RELEASE_LOCK(str)'
Releases the lock named by the string `str' that was obtained with
`GET_LOCK()'. Returns `1' if the lock was released, `0' if the
lock wasn't locked by this thread (in which case the lock is not
released), and `NULL' if the named lock didn't exist. The lock
will not exist if it was never obtained by a call to `GET_LOCK()'
or if it already has been released.
`BENCHMARK(count,expr)'
The `BENCHMARK()' function executes the expression `expr'
repeatedly `count' times. It may be used to time how fast *MySQL*
processes the expression. The result value is always `0'. The
intended use is in the `mysql' client, which reports query
execution times:
mysql> select BENCHMARK(1000000,encode("hello","goodbye"));
+----------------------------------------------+
| BENCHMARK(1000000,encode("hello","goodbye")) |
+----------------------------------------------+
| 0 |
+----------------------------------------------+
1 row in set (4.74 sec)
The time reported is elapsed time on the client end, not CPU time
on the server end. It may be advisable to execute `BENCHMARK()'
several times, and interpret the result with regard to how heavily
loaded the server machine is.
`INET_NTOA(expr)'
Returns the network address (4 or 8 byte) for the numeric
expression:
mysql> select INET_NTOA(3520061480);
-> "209.207.224.40"
`INET_ATON(expr)'
Returns an integer that represents the numeric value for a network
address. Addresses may be 4 or 8 byte addresses:
mysql> select INET_ATON("209.207.224.40");
-> 3520061480
`MASTER_POS_WAIT(log_name, log_pos)'
Blocks until the slave reaches the specified position in the
master log during replication. If master information is not
initialized, returns NULL. If the slave is not running, will block
and wait until it is started and goes to or past the specified
postion. If the slave is already past the specified postion,
returns immediately. The return value is the number of log events
it had to wait to get to the specified position, or NULL in case
of error. Useful for control of master-slave synchronization, but
was originally written to facilate replication testing.
Functions for Use with `GROUP BY' Clauses
-----------------------------------------
If you use a group function in a statement containing no `GROUP BY'
clause, it is equivalent to grouping on all rows.
`COUNT(expr)'
Returns a count of the number of non-`NULL' values in the rows
retrieved by a `SELECT' statement:
mysql> select student.student_name,COUNT(*)
from student,course
where student.student_id=course.student_id
GROUP BY student_name;
`COUNT(*)' is somewhat different in that it returns a count of the
number of rows retrieved, whether or not they contain `NULL'
values.
`COUNT(*)' is optimized to return very quickly if the `SELECT'
retrieves from one table, no other columns are retrieved, and
there is no `WHERE' clause. For example:
mysql> select COUNT(*) from student;
`COUNT(DISTINCT expr,[expr...])'
Returns a count of the number of different non-`NULL' values:
mysql> select COUNT(DISTINCT results) from student;
In *MySQL* you can get the number of distinct expression
combinations that don't contain NULL by giving a list of
expressions. In ANSI SQL you would have to do a concatenation of
all expressions inside `CODE(DISTINCT ..)'.
`AVG(expr)'
Returns the average value of `expr':
mysql> select student_name, AVG(test_score)
from student
GROUP BY student_name;
`MIN(expr)'
`MAX(expr)'
Returns the minimum or maximum value of `expr'. `MIN()' and
`MAX()' may take a string argument; in such cases they return the
minimum or maximum string value. *Note MySQL indexes::.
mysql> select student_name, MIN(test_score), MAX(test_score)
from student
GROUP BY student_name;
`SUM(expr)'
Returns the sum of `expr'. Note that if the return set has no
rows, it returns NULL!
`STD(expr)'
`STDDEV(expr)'
Returns the standard deviation of `expr'. This is an extension to
ANSI SQL. The `STDDEV()' form of this function is provided for
Oracle compatability.
`BIT_OR(expr)'
Returns the bitwise `OR' of all bits in `expr'. The calculation is
performed with 64-bit (`BIGINT') precision.
`BIT_AND(expr)'
Returns the bitwise `AND' of all bits in `expr'. The calculation is
performed with 64-bit (`BIGINT') precision.
*MySQL* has extended the use of `GROUP BY'. You can use columns or
calculations in the `SELECT' expressions that don't appear in the
`GROUP BY' part. This stands for _any possible value for this group_.
You can use this to get better performance by avoiding sorting and
grouping on unnecessary items. For example, you don't need to group on
`customer.name' in the following query:
mysql> select order.custid,customer.name,max(payments)
from order,customer
where order.custid = customer.custid
GROUP BY order.custid;
In ANSI SQL, you would have to add `customer.name' to the `GROUP BY'
clause. In *MySQL*, the name is redundant if you don't run in ANSI
mode.
*Don't use this feature* if the columns you omit from the `GROUP BY'
part aren't unique in the group! You will get unpredictable results.
In some cases, you can use `MIN()' and `MAX()' to obtain a specific
column value even if it isn't unique. The following gives the value of
`column' from the row containing the smallest value in the `sort'
column:
substr(MIN(concat(rpad(sort,6,' '),column)),7)
*Note example-Maximum-column-group-row::.
Note that if you are using *MySQL* Version 3.22 (or earlier) or if you
are trying to follow ANSI SQL, you can't use expressions in `GROUP BY'
or `ORDER BY' clauses. You can work around this limitation by using an
alias for the expression:
mysql> SELECT id,FLOOR(value/100) AS val FROM tbl_name
GROUP BY id,val ORDER BY val;
In *MySQL* Version 3.23 you can do:
mysql> SELECT id,FLOOR(value/100) FROM tbl_name ORDER BY RAND();
`CREATE DATABASE' Syntax
========================
CREATE DATABASE [IF NOT EXISTS] db_name
`CREATE DATABASE' creates a database with the given name. Rules for
allowable database names are given in *Note Legal names::. An error
occurs if the database already exists and you didn't specify `IF NOT
EXISTS'.
Databases in *MySQL* are implemented as directories containing files
that correspond to tables in the database. Because there are no tables
in a database when it is initially created, the `CREATE DATABASE'
statement only creates a directory under the *MySQL* data directory.
You can also create databases with `mysqladmin'. *Note Programs::.
`DROP DATABASE' Syntax
======================
DROP DATABASE [IF EXISTS] db_name
`DROP DATABASE' drops all tables in the database and deletes the
database. If you do a `DROP DATABASE' on a symbolic linked database,
both the link and the original database is deleted. *Be VERY careful
with this command!*
`DROP DATABASE' returns the number of files that were removed from the
database directory. Normally, this is three times the number of
tables, because normally each table corresponds to a `.MYD' file, a
`.MYI' file, and a `.frm' file.
The `DROP DATABASE' command removes from the given database directory
all files with the following extensions:
.BAK .DAT .HSH .ISD
.ISM .ISM .MRG .MYD
.MYI .db .frm
All subdirectories that consists of 2 digits (`RAID' directories) are
also removed.
In *MySQL* Version 3.22 or later, you can use the keywords `IF EXISTS'
to prevent an error from occurring if the database doesn't exist.
You can also drop databases with `mysqladmin'. *Note Programs::.
`CREATE TABLE' Syntax
=====================
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]
create_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
[PRIMARY KEY] [reference_definition]
or PRIMARY KEY (index_col_name,...)
or KEY [index_name] (index_col_name,...)
or INDEX [index_name] (index_col_name,...)
or UNIQUE [INDEX] [index_name] (index_col_name,...)
or FULLTEXT [INDEX] [index_name] (index_col_name,...)
or [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
[reference_definition]
or CHECK (expr)
type:
TINYINT[(length)] [UNSIGNED] [ZEROFILL]
or SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
or MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
or INT[(length)] [UNSIGNED] [ZEROFILL]
or INTEGER[(length)] [UNSIGNED] [ZEROFILL]
or BIGINT[(length)] [UNSIGNED] [ZEROFILL]
or REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
or FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
or NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
or CHAR(length) [BINARY]
or VARCHAR(length) [BINARY]
or DATE
or TIME
or TIMESTAMP
or DATETIME
or TINYBLOB
or BLOB
or MEDIUMBLOB
or LONGBLOB
or TINYTEXT
or TEXT
or MEDIUMTEXT
or LONGTEXT
or ENUM(value1,value2,value3,...)
or SET(value1,value2,value3,...)
index_col_name:
col_name [(length)]
reference_definition:
REFERENCES tbl_name [(index_col_name,...)]
[MATCH FULL | MATCH PARTIAL]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
table_options:
TYPE = {BDB | HEAP | ISAM | INNOBASE | MERGE | MYISAM }
or AUTO_INCREMENT = #
or AVG_ROW_LENGTH = #
or CHECKSUM = {0 | 1}
or COMMENT = "string"
or MAX_ROWS = #
or MIN_ROWS = #
or PACK_KEYS = {0 | 1}
or PASSWORD = "string"
or DELAY_KEY_WRITE = {0 | 1}
or ROW_FORMAT= { default | dynamic | static | compressed }
or RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=# RAID_CHUNKSIZE=#
or UNION = (table_name,[table_name...])
select_statement:
[IGNORE | REPLACE] SELECT ... (Some legal select statement)
`CREATE TABLE' creates a table with the given name in the current
database. Rules for allowable table names are given in *Note Legal
names::. An error occurs if there is no current database or if the
table already exists.
In *MySQL* Version 3.22 or later, the table name can be specified as
`db_name.tbl_name'. This works whether or not there is a current
database.
In *MySQL* Version 3.23, you can use the `TEMPORARY' keyword when you
create a table. A temporary table will automatically be deleted if a
connection dies and the name is per connection. This means that two
different connections can both use the same temporary table name
without conflicting with each other or with an existing table of the
same name. (The existing table is hidden until the temporary table is
deleted).
In *MySQL* Version 3.23 or later, you can use the keywords `IF NOT
EXISTS' so that an error does not occur if the table already exists.
Note that there is no verification that the table structures are
identical.
Each table `tbl_name' is represented by some files in the database
directory. In the case of MyISAM-type tables you will get:
*File* *Purpose*
`tbl_name.frm' Table definition (form) file
`tbl_name.MYD' Data file
`tbl_name.MYI' Index file
For more information on the properties of the various column types, see
*Note Column types:::
* If neither `NULL' nor `NOT NULL' is specified, the column is
treated as though `NULL' had been specified.
* An integer column may have the additional attribute
`AUTO_INCREMENT'. When you insert a value of `NULL' (recommended)
or `0' into an `AUTO_INCREMENT' column, the column is set to
`value+1', where `value' is the largest value for the column
currently in the table. `AUTO_INCREMENT' sequences begin with `1'.
*Note `mysql_insert_id()': mysql_insert_id.
If you delete the row containing the maximum value for an
`AUTO_INCREMENT' column, the value will be reused with an ISAM
table but not with a `MyISAM' table. If you delete all rows in the
table with `DELETE FROM table_name' (without a `WHERE') in
`AUTOCOMMIT' mode, the sequence starts over for both table types.
*NOTE:* There can be only one `AUTO_INCREMENT' column per table,
and it must be indexed. *MySQL* Version 3.23 will also only work
properly if the auto_increment column only has positive values.
Inserting a negative number is regarded as inserting a very large
positive number. This is done to avoid precision problems when
numbers 'wrap' over from positive to negative and also to ensure
that one doesn't accidently get an auto_increment column that
contains 0.
To make *MySQL* compatible with some ODBC applications, you can
find the last inserted row with the following query:
SELECT * FROM tbl_name WHERE auto_col IS NULL
* `NULL' values are handled differently for `TIMESTAMP' columns than
for other column types. You cannot store a literal `NULL' in a
`TIMESTAMP' column; setting the column to `NULL' sets it to the
current date and time. Because `TIMESTAMP' columns behave this
way, the `NULL' and `NOT NULL' attributes do not apply in the
normal way and are ignored if you specify them.
On the other hand, to make it easier for *MySQL* clients to use
`TIMESTAMP' columns, the server reports that such columns may be
assigned `NULL' values (which is true), even though `TIMESTAMP'
never actually will contain a `NULL' value. You can see this when
you use `DESCRIBE tbl_name' to get a description of your table.
Note that setting a `TIMESTAMP' column to `0' is not the same as
setting it to `NULL', because `0' is a valid `TIMESTAMP' value.
* If no `DEFAULT' value is specified for a column, *MySQL*
automatically assigns one.
If the column may take `NULL' as a value, the default value is
`NULL'.
If the column is declared as `NOT NULL', the default value depends
on the column type:
- For numeric types other than those declared with the
`AUTO_INCREMENT' attribute, the default is `0'. For an
`AUTO_INCREMENT' column, the default value is the next value
in the sequence.
- For date and time types other than `TIMESTAMP', the default
is the appropriate zero value for the type. For the first
`TIMESTAMP' column in a table, the default value is the
current date and time. *Note Date and time types::.
- For string types other than `ENUM', the default value is the
empty string. For `ENUM', the default is the first
enumeration value.
Default values must be constants. This means, for example, that
you cannot set the default for a date column to be the value of a
function such as `NOW()' or `CURRENT_DATE'.
* `KEY' is a synonym for `INDEX'.
* In *MySQL*, a `UNIQUE' key can have only distinct values. An error
occurs if you try to add a new row with a key that matches an
existing row.
* A `PRIMARY KEY' is a unique `KEY' with the extra constraint that
all key columns must be defined as `NOT NULL'. In *MySQL* the key
is named `PRIMARY'. A table can have only one `PRIMARY KEY'. If
you don't have a `PRIMARY KEY' and some applications ask for the
`PRIMARY KEY' in your tables, *MySQL* will return the first
`UNIQUE' key, which doesn't have any `NULL' columns, as the
`PRIMARY KEY'.
* A `PRIMARY KEY' can be a multiple-column index. However, you
cannot create a multiple-column index using the `PRIMARY KEY' key
attibute in a column specification. Doing so will mark only that
single column as primary. You must use the `PRIMARY
KEY(index_col_name, ...)' syntax.
* If the `PRIMARY' or `UNIQUE' key consists of only one column and
this is of type integer, you can also refer to it as `_rowid' (new
in Version 3.23.11).
* If you don't assign a name to an index, the index will be assigned
the same name as the first `index_col_name', with an optional
suffix (`_2', `_3', `...') to make it unique. You can see index
names for a table using `SHOW INDEX FROM tbl_name'. *Note `SHOW':
SHOW.
* Only the `MyISAM' table type supports indexes on columns that can
have `NULL' values. In other cases you must declare such columns
`NOT NULL' or an error results.
* With `col_name(length)' syntax, you can specify an index that uses
only a part of a `CHAR' or `VARCHAR' column. This can make the
index file much smaller. *Note Indexes::.
* Only the `MyISAM' table type supports indexing on `BLOB' and
`TEXT' columns. When putting an index on a `BLOB' or `TEXT'
column you MUST always specify the length of the index:
CREATE TABLE test (blob_col BLOB, index(blob_col(10)));
* When you use `ORDER BY' or `GROUP BY' with a `TEXT' or `BLOB'
column, only the first `max_sort_length' bytes are used. *Note
`BLOB': BLOB.
* In *MySQL* Version 3.23.23 or later, you can also create special
*FULLTEXT* indexes. They are used for full-text search. Only the
`MyISAM' table type supports `FULLTEXT' indexes. They can be
created only from `VARCHAR' and `TEXT' columns. Indexing always
happens over the entire column, partial indexing is not supported.
See *Note MySQL full-text search:: for details of operation.
* The `FOREIGN KEY', `CHECK', and `REFERENCES' clauses don't
actually do anything. The syntax for them is provided only for
compatibility, to make it easier to port code from other SQL
servers and to run applications that create tables with references.
*Note Missing functions::.
* Each `NULL' column takes one bit extra, rounded up to the nearest
byte.
* The maximum record length in bytes can be calculated as follows:
row length = 1
+ (sum of column lengths)
+ (number of NULL columns + 7)/8
+ (number of variable-length columns)
* The `table_options' and `SELECT' options are only implemented in
*MySQL* Version 3.23 and above.
The different table types are:
BDB or Transaction-safe tables with page locking. *Note
Berkeley_db BDB::.
GEMINI Transaction-safe tables with row-level locking *Note
GEMINI::.
HEAP The data for this table is only stored in memory.
*Note HEAP::.
ISAM The original table handler. *Note ISAM::.
INNOBASE Transaction-safe tables with row locking. *Note
INNOBASE::.
MERGE A collection of MyISAM tables used as one table.
*Note MERGE::.
MyISAM The new binary portable table handler that is
replacing ISAM. *Note MyISAM::.
*Note Table types::.
If a table type is specified, and that particular type is not
available, *MySQL* will choose the closest table type to the one
that you have specified. For example, if `TYPE=BDB' is specified,
and that distribution of *MySQL* does not support `BDB' tables,
the table will be created as `MyISAM' instead.
The other table options are used to optimize the behavior of the
table. In most cases, you don't have to specify any of them. The
options work for all table types, if not otherwise indicated:
`AUTO_INCREMENT'The next auto_increment value you want to set for
your table (MyISAM).
`AVG_ROW_LENGTH'An approximation of the average row length for your
table. You only need to set this for large tables
with variable size records.
`CHECKSUM' Set this to 1 if you want *MySQL* to maintain a
checksum for all rows (makes the table a little
slower to update but makes it easier to find
corrupted tables) (MyISAM).
`COMMENT' A 60-character comment for your table.
`MAX_ROWS' Max number of rows you plan to store in the table.
`MIN_ROWS' Minimum number of rows you plan to store in the table.
`PACK_KEYS' Set this to 1 if you want to have a smaller index.
This usually makes updates slower and reads faster
(MyISAM, ISAM).
`PASSWORD' Encrypt the `.frm' file with a password. This option
doesn't do anything in the standard *MySQL* version.
`DELAY_KEY_WRITE'Set this to 1 if want to delay key table updates
until the table is closed (MyISAM).
`ROW_FORMAT' Defines how the rows should be stored (for the
future).
When you use a `MyISAM' table, *MySQL* uses the product of
`max_rows * avg_row_length' to decide how big the resulting table
will be. If you don't specify any of the above options, the
maximum size for a table will be 4G (or 2G if your operating
systems only supports 2G tables). The reason for this is just to
keep down the pointer sizes to make the index smaller and faster
if you don't really need big files.
If you don't use `PACK_KEYS', the default is to only pack strings,
not numbers. If you use `PACK_KEYS=1', numbers will be packed as
well.
When packing binary number keys, *MySQL* will use prefix
compression. This means that you will only get a big benefit of
this if you have many numbers that are the same. Prefix
compression means that every key needs one extra byte to indicate
how many bytes of the previous key are the same for the next key
(note that the pointer to the row is stored in
high-byte-first-order directly after the key, to improve
compression.) This means that if you have many equal keys on two
rows in a row, all following 'same' keys will usually only take 2
bytes (including the pointer to the row). Compare this to the
ordinary case where the following keys will take
storage_size_for_key + pointer_size (usually 4). On the other
hand, if all keys are totally different, you will lose 1 byte per
key, if the key isn't a key that can have `NULL' values (In this
case the packed key length will be stored in the same byte that is
used to mark if a key is `NULL'.)
* If you specify a `SELECT' after the `CREATE STATEMENT', *MySQL*
will create new fields for all elements in the `SELECT'. For
example:
mysql> CREATE TABLE test (a int not null auto_increment,
primary key (a), key(b))
TYPE=MyISAM SELECT b,c from test2;
This will create a `MyISAM' table with 3 columns. Note that the
table will automatically be deleted if any errors occur while
copying data into the table.
* The `RAID_TYPE' option will help you to break the 2G/4G limit for
the MyISAM data file (not the index file) on operating systems
that don't support big files. You can get also more speed from the
I/O bottleneck by putting `RAID' directories on different physical
disks. `RAID_TYPE' will work on any OS, as long as you have
configured *MySQL* with `--with-raid'. For now the only allowed
`RAID_TYPE' is `STRIPED' (`1' and `RAID0' are aliases for this).
If you specify `RAID_TYPE=STRIPED' for a `MyISAM' table, `MyISAM'
will create `RAID_CHUNKS' subdirectories named 00, 01, 02 in the
database directory. In each of these directories `MyISAM' will
create a `table_name.MYD'. When writing data to the data file,
the `RAID' handler will map the first `RAID_CHUNKSIZE' *1024 bytes
to the first file, the next `RAID_CHUNKSIZE' *1024 bytes to the
next file and so on.
* `UNION' is used when you want to use a collection of identical
tables as one. This only works with MERGE tables. *Note MERGE::.
For the moment you need to have `SELECT', `UPDATE', and `DELETE'
privileges on the tables you map to a `MERGE' table. All mapped
tables must be in the same database as the `MERGE' table.
* In the created table the `PRIMARY' key will be placed first,
followed by all `UNIQUE' keys and then the normal keys. This
helps the *MySQL* optimizer to prioritize which key to use and
also more quickly detect duplicated `UNIQUE' keys.
Silent Column Specification Changes
-----------------------------------
In some cases, *MySQL* silently changes a column specification from
that given in a `CREATE TABLE' statement. (This may also occur with
`ALTER TABLE'.):
* `VARCHAR' columns with a length less than four are changed to
`CHAR'.
* If any column in a table has a variable length, the entire row is
variable-length as a result. Therefore, if a table contains any
variable-length columns (`VARCHAR', `TEXT', or `BLOB'), all `CHAR'
columns longer than three characters are changed to `VARCHAR'
columns. This doesn't affect how you use the columns in any way;
in *MySQL*, `VARCHAR' is just a different way to store characters.
*MySQL* performs this conversion because it saves space and makes
table operations faster. *Note Table types::.
* `TIMESTAMP' display sizes must be even and in the range from 2 to
14. If you specify a display size of 0 or greater than 14, the
size is coerced to 14. Odd-valued sizes in the range from 1 to 13
are coerced to the next higher even number.
* You cannot store a literal `NULL' in a `TIMESTAMP' column; setting
it to `NULL' sets it to the current date and time. Because
`TIMESTAMP' columns behave this way, the `NULL' and `NOT NULL'
attributes do not apply in the normal way and are ignored if you
specify them. `DESCRIBE tbl_name' always reports that a
`TIMESTAMP' column may be assigned `NULL' values.
* *MySQL* maps certain column types used by other SQL database
vendors to *MySQL* types. *Note Other-vendor column types::.
If you want to see whether or not *MySQL* used a column type other than
the one you specified, issue a `DESCRIBE tbl_name' statement after
creating or altering your table.
Certain other column type changes may occur if you compress a table
using `myisampack'. *Note Compressed format::.
`ALTER TABLE' Syntax
====================
ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]
alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
or ADD [COLUMN] (create_definition, create_definition,...)
or ADD INDEX [index_name] (index_col_name,...)
or ADD PRIMARY KEY (index_col_name,...)
or ADD UNIQUE [index_name] (index_col_name,...)
or ADD FULLTEXT [index_name] (index_col_name,...)
or ADD [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
[reference_definition]
or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
or CHANGE [COLUMN] old_col_name create_definition
or MODIFY [COLUMN] create_definition
or DROP [COLUMN] col_name
or DROP PRIMARY KEY
or DROP INDEX index_name
or RENAME [TO] new_tbl_name
or ORDER BY col
or table_options
`ALTER TABLE' allows you to change the structure of an existing table.
For example, you can add or delete columns, create or destroy indexes,
change the type of existing columns, or rename columns or the table
itself. You can also change the comment for the table and type of the
table. *Note `CREATE TABLE': CREATE TABLE.
If you use `ALTER TABLE' to change a column specification but `DESCRIBE
tbl_name' indicates that your column was not changed, it is possible
that *MySQL* ignored your modification for one of the reasons described
in *Note Silent column changes::. For example, if you try to change a
`VARCHAR' column to `CHAR', *MySQL* will still use `VARCHAR' if the
table contains other variable-length columns.
`ALTER TABLE' works by making a temporary copy of the original table.
The alteration is performed on the copy, then the original table is
deleted and the new one is renamed. This is done in such a way that all
updates are automatically redirected to the new table without any
failed updates. While `ALTER TABLE' is executing, the original table is
readable by other clients. Updates and writes to the table are stalled
until the new table is ready:
* To use `ALTER TABLE', you need *select*, *insert*, *delete*,
*update*, *create*, and *drop* privileges on the table.
* `IGNORE' is a *MySQL* extension to ANSI SQL92. It controls how
`ALTER TABLE' works if there are duplicates on unique keys in the
new table. If `IGNORE' isn't specified, the copy is aborted and
rolled back. If `IGNORE' is specified, then for rows with
duplicates on a unique key, only the first row is used; the others
are deleted.
* You can issue multiple `ADD', `ALTER', `DROP', and `CHANGE'
clauses in a single `ALTER TABLE' statement. This is a *MySQL*
extension to ANSI SQL92, which allows only one of each clause per
`ALTER TABLE' statement.
* `CHANGE col_name', `DROP col_name', and `DROP INDEX' are *MySQL*
extensions to ANSI SQL92.
* `MODIFY' is an Oracle extension to `ALTER TABLE'.
* `TRUNCATE' is an Oracle extension. *Note TRUNCATE::.
* The optional word `COLUMN' is a pure noise word and can be omitted.
* If you use `ALTER TABLE tbl_name RENAME TO new_name' without any
other options, *MySQL* simply renames the files that correspond to
the table `tbl_name'. There is no need to create the temporary
table. *Note `RENAME TABLE': RENAME TABLE.
* `create_definition' clauses use the same syntax for `ADD' and
`CHANGE' as for `CREATE TABLE'. Note that this syntax includes
the column name, not just the column type. *Note `CREATE TABLE':
CREATE TABLE.
* You can rename a column using a `CHANGE old_col_name
create_definition' clause. To do so, specify the old and new
column names and the type that the column currently has. For
example, to rename an `INTEGER' column from `a' to `b', you can do
this:
mysql> ALTER TABLE t1 CHANGE a b INTEGER;
If you want to change a column's type but not the name, `CHANGE'
syntax still requires two column names even if they are the same.
For example:
mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
However, as of *MySQL* Version 3.22.16a, you can also use `MODIFY'
to change a column's type without renaming it:
mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
* If you use `CHANGE' or `MODIFY' to shorten a column for which an
index exists on part of the column (for instance, if you have an
index on the first 10 characters of a `VARCHAR' column), you
cannot make the column shorter than the number of characters that
are indexed.
* When you change a column type using `CHANGE' or `MODIFY', *MySQL*
tries to convert data to the new type as well as possible.
* In *MySQL* Version 3.22 or later, you can use `FIRST' or `ADD ...
AFTER col_name' to add a column at a specific position within a
table row. The default is to add the column last.
* `ALTER COLUMN' specifies a new default value for a column or
removes the old default value. If the old default is removed and
the column can be `NULL', the new default is `NULL'. If the column
cannot be `NULL', *MySQL* assigns a default value. Default value
assignment is described in *Note `CREATE TABLE': CREATE TABLE.
* `DROP INDEX' removes an index. This is a *MySQL* extension to ANSI
SQL92.
* If columns are dropped from a table, the columns are also removed
from any index of which they are a part. If all columns that make
up an index are dropped, the index is dropped as well.
* `DROP PRIMARY KEY' drops the primary index. If no such index
exists, it drops the first `UNIQUE' index in the table. (*MySQL*
marks the first `UNIQUE' key as the `PRIMARY KEY' if no `PRIMARY
KEY' was specified explicitly.)
* `ORDER BY' allows you to create the new table with the rows in a
specific order. Note that the table will not remain in this order
after inserts and deletes. In some cases, it may make sorting
easier for *MySQL* if the table is in order by the column that you
wish to order it by later. This option is mainly useful when you
know that you are mostly going to query the rows in a certain
order; By using this option after big changes to the table, you
may be able to get higher performance.
* If you use `ALTER TABLE' on a `MyISAM' table, all non-unique
indexes are created in a separate batch (like in `REPAIR'). This
should make `ALTER TABLE' much faster when you have many indexes.
* With the C API function `mysql_info()', you can find out how many
records were copied, and (when `IGNORE' is used) how many records
were deleted due to duplication of unique key values.
* The `FOREIGN KEY', `CHECK', and `REFERENCES' clauses don't
actually do anything. The syntax for them is provided only for
compatibility, to make it easier to port code from other SQL
servers and to run applications that create tables with references.
*Note Missing functions::.
Here is an example that shows some of the uses of `ALTER TABLE'. We
begin with a table `t1' that is created as shown below:
mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));
To rename the table from `t1' to `t2':
mysql> ALTER TABLE t1 RENAME t2;
To change column `a' from `INTEGER' to `TINYINT NOT NULL' (leaving the
name the same), and to change column `b' from `CHAR(10)' to `CHAR(20)'
as well as renaming it from `b' to `c':
mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
To add a new `TIMESTAMP' column named `d':
mysql> ALTER TABLE t2 ADD d TIMESTAMP;
To add an index on column `d', and make column `a' the primary key:
mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
To remove column `c':
mysql> ALTER TABLE t2 DROP COLUMN c;
To add a new `AUTO_INCREMENT' integer column named `c':
mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD INDEX (c);
Note that we indexed `c', because `AUTO_INCREMENT' columns must be
indexed, and also that we declare `c' as `NOT NULL', because indexed
columns cannot be `NULL'.
When you add an `AUTO_INCREMENT' column, column values are filled in
with sequence numbers for you automatically. You can set the first
sequence number by executing `SET INSERT_ID=#' before `ALTER TABLE' or
using the `AUTO_INCREMENT = #' table option. *Note SET OPTION::.
*Note ALTER TABLE problems::.
`RENAME TABLE' Syntax
=====================
RENAME TABLE tbl_name TO new_table_name[, tbl_name2 TO new_table_name2,...]