This chapter describes the syntax for the SQL statements supported in MySQL.
DELETE
SyntaxSingle-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]
Multiple-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.*] [, tbl_name[.*] ...] FROM table_references [WHERE where_definition]
Or:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[.*] [, tbl_name[.*] ...] USING table_references [WHERE where_definition]
DELETE
deletes rows from tbl_name that satisfy the condition
given by where_definition, and returns the number of records deleted.
If you issue a DELETE
statement with no WHERE
clause, all
rows are deleted. A faster way to do this, when you don't want to know
the number of deleted rows, is to use TRUNCATE TABLE
.
See section 13.1.9 TRUNCATE
Syntax.
In MySQL 3.23, DELETE
without a WHERE
clause returns zero
as the number of affected records.
In MySQL 3.23, if you really want to know how many records are deleted
when you are deleting all rows, and are willing to suffer a speed
penalty, you can use a DELETE
statement that includes a
WHERE
clause with an expression that is true for every row. For
example:
mysql> DELETE FROM tbl_name WHERE 1>0;
This is much slower than TRUNCATE tbl_name
, because it deletes
rows one at a time.
If you delete the row containing the maximum value for an
AUTO_INCREMENT
column, the value will be reused for an ISAM
or BDB
table, but not for a MyISAM
or InnoDB
table.
If you delete all rows in the table with DELETE FROM tbl_name
(without a WHERE
) in AUTOCOMMIT
mode, the sequence starts
over for all table types except for InnoDB
and (as of MySQL 4.0)
MyISAM
. There are some exceptions to this behavior for InnoDB
tables, discussed in
section 15.7.3 How an AUTO_INCREMENT
Column Works in InnoDB
.
For MyISAM
and BDB
tables, you can specify an
AUTO_INCREMENT
secondary column in a multiple-column key. In this
case, reuse of values deleted from the top of the sequence occurs even
for MyISAM
tables.
See section 3.6.9 Using AUTO_INCREMENT
.
The DELETE
statement supports the following modifiers:
LOW_PRIORITY
keyword, execution of the
DELETE
is delayed until no other clients are reading from the table.
MyISAM
tables, if you specify the QUICK
keyword, the
storage engine does not merge index leaves during delete, which may speed up
certain kind of deletes.
IGNORE
keyword causes MySQL to ignore all errors during the
process of deleting rows. (Errors encountered during the parsing stage are
processed in the usual manner.) Errors that are ignored due to the use of
this option are returned as warnings. This option first appeared in MySQL
4.1.1.
The speed of delete operations may also be affected by factors discussed in
section 7.2.16 Speed of DELETE
Statements.
In MyISAM
tables, deleted records are maintained in a linked list and
subsequent INSERT
operations reuse old record positions. To
reclaim unused space and reduce file sizes, use the OPTIMIZE
TABLE
statement or the myisamchk
utility to reorganize tables.
OPTIMIZE TABLE
is easier, but myisamchk
is faster. See
section 13.5.2.5 OPTIMIZE TABLE
Syntax and section 5.7.2.10 Table Optimization.
The QUICK
modifier affects whether index leaves are merged for
delete operations. DELETE QUICK
is most useful for applications
where index values for deleted rows will be replaced by similar index
values from rows inserted later. In this case, the holes left by deleted
values will be reused.
DELETE QUICK
is not useful when deleted values lead to underfilled
index blocks spanning a range of index values for which new inserts will
occur again. In this case, use of QUICK
can lead to wasted space in
the index that remains unreclaimed. Here is an example of such a scenario:
AUTO_INCREMENT
column.
DELETE QUICK
.
In this scenario, the index blocks associated with the deleted index
values become underfilled but are not merged with other index blocks due
to the use of QUICK
. They will remain underfilled when new inserts
occur, because new records will not have index values in the deleted range.
Furthermore, they will remain underfilled even if you later use DELETE
without QUICK
, unless some of the deleted index values happen
to lie in index blocks within or adjacent to the underfilled blocks.
To reclaim unused index space under these circumstances, you can use
OPTIMIZE TABLE
.
If you are going to delete many rows from a table, it might be faster to use
DELETE QUICK
followed by OPTIMIZE TABLE
. This rebuilds the index
rather than performing many index block merge operations.
The MySQL-specific LIMIT row_count
option to DELETE
tells
the server the maximum number of rows to be deleted before control is
returned to the client. This can be used to ensure that a specific
DELETE
statement doesn't take too much time. You can simply repeat
the DELETE
statement until the number of affected rows is less than
the LIMIT
value.
If the DELETE
statement includes an ORDER BY
clause, the rows
are deleted in the order specified by the clause. This is really useful only
in conjunction with LIMIT
. For example, the following statement
finds rows matching the WHERE
clause, sorts them in timestamp
order, and deletes the first (oldest) one:
DELETE FROM somelog WHERE user = 'jcole' ORDER BY timestamp LIMIT 1
ORDER BY
can be used with DELETE
beginning with MySQL 4.0.0.
From MySQL 4.0, you can specify multiple tables in the DELETE
statement to delete rows from one or more tables depending on a particular
condition in multiple tables. However, you cannot use ORDER BY
or LIMIT
in a multiple-table DELETE
.
The first multiple-table DELETE
syntax is supported starting from
MySQL 4.0.0. The second is supported starting from MySQL 4.0.2. The
table_references
part lists the tables involved in the join.
Its syntax is described in section 13.1.7.1 JOIN
Syntax.
For the first syntax, only matching rows from the tables listed before the
FROM
clause are deleted. For the second syntax, only matching rows
from the tables listed in the FROM
clause (before the USING
clause) are deleted. The effect is that you can delete rows from many
tables at the same time and also have additional tables that are used for
searching:
DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
Or:
DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
These statements use all three files when searching for rows to delete, but
delete matching rows only from tables t1
and t2
.
The examples show inner joins using the comma operator, but
multiple-table DELETE
statements can use any type of
join allowed in SELECT
statements, such as LEFT JOIN
.
The syntax allows .*
after the table names for compatibility with
Access
.
If you use a multiple-table DELETE
statement involving
InnoDB
tables for which there are foreign key constraints,
the MySQL optimizer might process tables in an order that differs from
that of their parent/child relationship. In this case, the statement
fails and rolls back. Instead, delete from a single table and rely on the
ON DELETE
capabilities that InnoDB
provides to cause the
other tables to be modified accordingly.
Note: In MySQL 4.0, you should refer to the table names to be deleted with the true table name. In MySQL 4.1, you must use the alias (if one was given) when referring to a table name:
In MySQL 4.0:
DELETE test FROM test AS t1, test2 WHERE ...
In MySQL 4.1:
DELETE t1 FROM test AS t1, test2 WHERE ...
The reason we didn't make this change in 4.0 is that we didn't want to break any old 4.0 applications that were using the old syntax.
Currently, you cannot delete from a table and select from the same table in a subquery.
DO
SyntaxDO expr [, expr] ...
DO
executes the expressions but doesn't return any results. This is
shorthand for SELECT expr, ...
, but has the advantage that it's
slightly faster when you don't care about the result.
DO
is useful mainly with functions that have side effects, such as
RELEASE_LOCK()
.
DO
was added in MySQL 3.23.47.
HANDLER
SyntaxHANDLER tbl_name OPEN [ AS alias ] HANDLER tbl_name READ index_name { = | >= | <= | < } (value1,value2,...) [ WHERE where_condition ] [LIMIT ... ] HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST } [ WHERE where_condition ] [LIMIT ... ] HANDLER tbl_name READ { FIRST | NEXT } [ WHERE where_condition ] [LIMIT ... ] HANDLER tbl_name CLOSE
The HANDLER
statement provides direct access to table storage engine
interfaces. It is available for MyISAM
tables as MySQL 4.0.0 and
InnoDB
tables as of MySQL 4.0.3.
The HANDLER ... OPEN
statement opens a table, making
it accessible via subsequent HANDLER ... READ
statements.
This table object is not shared by other threads and is not closed
until the thread calls HANDLER ... CLOSE
or the thread terminates.
If you open the table using an alias, further references to the table with
other HANDLER
statements must use the alias rather than the table
name.
The first HANDLER ... READ
syntax fetches a row where the index
specified satisfies the given values and the WHERE
condition is met.
If you have a multiple-column index, specify the index column values as a
comma-separated list. Either specify values for all the columns in the
index, or specify values for a leftmost prefix of the index columns. Suppose
that an index includes three columns named col_a
, col_b
, and
col_c
, in that order. The HANDLER
statement can specify
values for all three columns in the index, or for the columns in a leftmost
prefix. For example:
HANDLER ... index_name = (col_a_val,col_b_val,col_c_val) ... HANDLER ... index_name = (col_a_val,col_b_val) ... HANDLER ... index_name = (col_a_val) ...
The second HANDLER ... READ
syntax fetches a row from the table in
index order that matches WHERE
condition.
The third HANDLER ... READ
syntax fetches a row from the table in
natural row order that matches the WHERE
condition. It is faster than
HANDLER tbl_name READ index_name
when a full table scan is desired.
Natural row order is the order in which rows are stored in a MyISAM
table data file. This statement works for InnoDB
tables as well, but
there is no such concept because there is no separate data file.
Without a LIMIT
clause, all forms of HANDLER ... READ
fetch a
single row if one is available. To return a specific number of rows, include a
LIMIT
clause. It has the same syntax as for the SELECT
statement.
See section 13.1.7 SELECT
Syntax.
HANDLER ... CLOSE
closes a table that was opened with
HANDLER ... OPEN
.
Note: To use the HANDLER
interface to refer to a table's
PRIMARY KEY
, use the quoted identifier `PRIMARY`
:
HANDLER tbl_name READ `PRIMARY` > (...);
HANDLER
is a somewhat low-level statement. For example, it does not
provide consistency. That is, HANDLER ... OPEN
does not
take a snapshot of the table, and does not lock the table. This
means that after a HANDLER ... OPEN
statement is issued, table data
can be modified (by this or any other thread) and these modifications might
appear only partially in HANDLER ... NEXT
or HANDLER ... PREV
scans.
There are several reasons to use the HANDLER
interface instead of
normal SELECT
statements:
HANDLER
is faster than SELECT
:
HANDLER ... OPEN
. The object is reused for the following
HANDLER
statements for the table; it need not be reinitialized for
each one.
SELECT
doesn't normally allow.
HANDLER
makes it much easier to port applications that use an
ISAM
-like interface to MySQL.
HANDLER
allows you to traverse a database in a manner that is not
easy (or perhaps even impossible) to do with SELECT
. The HANDLER
interface is a more natural way to look at data when working with
applications that provide an interactive user interface to the database.
INSERT
SyntaxINSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
Or:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name SET col_name={expr | DEFAULT}, ... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
Or:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ...
INSERT
inserts new rows into an existing table. The INSERT ...
VALUES
and INSERT ... SET
forms of the statement insert rows based
on explicitly specified values. The INSERT ... SELECT
form inserts
rows selected from another table or tables. The INSERT ... VALUES
form with multiple value lists is supported in MySQL 3.22.5 or
later. The INSERT ... SET
syntax is supported in MySQL
3.22.10 or later.
INSERT ... SELECT
is discussed further in
See section 13.1.4.1 INSERT ... SELECT
Syntax.
tbl_name is the table into which rows should be inserted. The columns for which the statement provides values can be specified as follows:
SET
clause indicates the columns
explicitly.
INSERT ... VALUES
or
INSERT ... SELECT
, values for every column in the table must be
provided in the VALUES()
list or by the SELECT
. If you don't
know the order of the columns in the table, use DESCRIBE tbl_name
to
find out.
Column values can be given in several ways:
CREATE TABLE
Syntax.
See section 1.5.6.2 Constraints on Invalid Data.
If you want INSERT
statements to generate an error unless you
explicitly specify values for all columns that don't have a default
value, you should use STRICT mode. See section 5.2.2 The Server SQL Mode.
DEFAULT
to explicitly set a column to its
default value. (New in MySQL 4.0.3.) This makes it easier to write
INSERT
statements that assign values to all but a few columns,
because it allows you to avoid writing an incomplete VALUES
list
that does not include a value for each column in the table.
Otherwise, you would have to write out the list of column names
corresponding to each value in the VALUES
list.
As of MySQL 4.1.0, you can use DEFAULT(col_name)
as a more
general form that can be used in expressions to produce a column's
default value.
VALUES
list are empty, INSERT
creates a row with each column set to its default value:
mysql> INSERT INTO tbl_name () VALUES();
'1999.0e-2'
into an INT
, FLOAT
,
DECIMAL(10,6)
, or YEAR
column results in the values 1999
,
19.9921
, 19.992100
, and 1999
. The reason the value
stored in the INT
and YEAR
columns is 1999
is that the
string-to-integer conversion looks only at as much of the initial part of
the string as may be considered a valid integer or year. For the
floating-point and fixed-point columns, the string-to-floating-point
conversion considers the entire string as a valid floating-point value.
An expression expr can refer to any column that was set earlier in a
value list. For example, you can do this because the value for col2
refers to col1
, which has already been assigned:
mysql> INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);But you cannot do this because the value for
col1
refers to
col2
, which is assigned after col1
:
mysql> INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);One exception involves columns that contain
AUTO_INCREMENT
values.
Because the AUTO_INCREMENT value is generated after other value assignments,
any reference to an AUTO_INCREMENT column in the assignment will return a 0.
The INSERT
statement supports the following modifiers:
DELAYED
keyword, the server puts the row or
rows to be inserted into a buffer, and the client issuing the INSERT
DELAYED
statement then can continue on. If the table is busy, the server
holds the rows. When the table becomes free, it begins inserting rows,
checking periodically to see whether there are new read requests for the
table. If there are, the delayed row queue is suspended until the table
becomes free again.
See section 13.1.4.2 INSERT DELAYED
Syntax.
DELAYED
was added in MySQL 3.22.5.
LOW_PRIORITY
keyword, execution of the
INSERT
is delayed until no other clients are reading from the
table. This includes other clients that began reading while existing
clients are reading, and while the INSERT LOW_PRIORITY
statement
is waiting. It is possible, therefore, for a client that issues an
INSERT LOW_PRIORITY
statement to wait for a very long time (or
even forever) in a read-heavy environment.
(This is in contrast to INSERT DELAYED
, which lets the client
continue at once.) See section 13.1.4.2 INSERT DELAYED
Syntax. Note
that LOW_PRIORITY
should normally not be used with MyISAM
tables because doing so disables concurrent inserts.
See section 14.1 The MyISAM
Storage Engine.
LOW_PRIORITY
was added in MySQL 3.22.5.
HIGH_PRIORITY
keyword, it overrides the
effect of the --low-priority-updates
option if the server was
started with that option. It also causes concurrent inserts not to be
used. HIGH_PRIORITY
was added in MySQL 3.23.11.
INSERT
can be obtained using the
mysql_affected_rows()
C API function.
See section 21.2.3.1 mysql_affected_rows()
.
IGNORE
keyword in an INSERT
statement,
errors that occur while executing the statement are treated as warnings
instead. For example, without IGNORE
, a row that duplicates an
existing UNIQUE
index or PRIMARY KEY
value in the table causes
a duplicate-key error and the statement is aborted. With IGNORE
,
the error is ignored and the row is not inserted. Data conversions that
would trigger errors abort the statement if IGNORE
is not specified. With IGNORE
, invalid values are adjusted to
the closest value values and inserted; warnings are produced but the
statement does not abort. You can determine with the mysql_info()
C API function how many rows were inserted into the table.
If you specify the ON DUPLICATE KEY UPDATE
clause (new in MySQL
4.1.0), and a row is inserted that would cause a duplicate value in a
UNIQUE
index or PRIMARY KEY
, an UPDATE
of the old row
is performed. For example, if column a
is declared as UNIQUE
and already contains the value 1
, the following two statements have
identical effect:
mysql> INSERT INTO table (a,b,c) VALUES (1,2,3) -> ON DUPLICATE KEY UPDATE c=c+1; mysql> UPDATE table SET c=c+1 WHERE a=1;
The rows-affected value is 1 if the row is inserted as a new record and 2 if an existing record is updated.
Note: If column b
is unique too, the INSERT
would be
equivalent to this UPDATE
statement instead:
mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
If a=1 OR b=2
matches several rows, only one row
is updated! In general, you should try to avoid using the
ON DUPLICATE KEY
clause on tables with multiple UNIQUE
keys.
As of MySQL 4.1.1, you can use the VALUES(col_name)
function in the
UPDATE
clause to refer to column values from the INSERT
part
of the INSERT ... UPDATE
statement. In other words,
VALUES(col_name)
in the UPDATE
clause refers to the value of
col_name that would be inserted if no duplicate-key conflict
occurred. This function is especially useful in multiple-row inserts. The
VALUES()
function is meaningful only in INSERT ... UPDATE
statements and returns NULL
otherwise.
Example:
mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) -> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
That statement is identical to the following two statements:
mysql> INSERT INTO table (a,b,c) VALUES (1,2,3) -> ON DUPLICATE KEY UPDATE c=3; mysql> INSERT INTO table (a,b,c) VALUES (4,5,6) -> ON DUPLICATE KEY UPDATE c=9;
When you use ON DUPLICATE KEY UPDATE
, the DELAYED
option is
ignored.
You can find the value used for an AUTO_INCREMENT
column by using the
LAST_INSERT_ID()
function. From within the C API, use the
mysql_insert_id()
function. However, note that the two functions do
not behave quite identically under all circumstances.
The behavior of INSERT
statements with respect to AUTO_INCREMENT
columns is discussed further in section 12.8.3 Information Functions and
section 21.2.3.33 mysql_insert_id()
.
If you use an INSERT ... VALUES
statement with multiple value lists
or INSERT ... SELECT
, the statement returns an information string in
this format:
Records: 100 Duplicates: 0 Warnings: 0
Records
indicates the number of rows processed by the statement.
(This is not necessarily the number of rows actually inserted.
Duplicates
can be non-zero.)
Duplicates
indicates the number of rows that couldn't be inserted
because they would duplicate some existing unique index value.
Warnings
indicates the number of attempts to insert column values that
were problematic in some way. Warnings can occur under any of the following
conditions:
NULL
into a column that has been declared NOT NULL
.
For multiple-row INSERT
statements or INSERT ... SELECT
statements,
the column is set to the default value appropriate for the column type.
This is 0
for numeric types, the empty string (''
) for
string types, and the ``zero'' value for date and time types.
'10.34 a'
to a numeric column. The
trailing non-numeric text is stripped off and the remaining numeric part is
inserted. If the string value has no leading numeric part, the column is
set to 0
.
CHAR
, VARCHAR
, TEXT
, or
BLOB
) that exceeds the column's maximum length. The value is
truncated to the column's maximum length.
If you are using the C API, the information string can be obtained by invoking
the mysql_info()
function.
See section 21.2.3.31 mysql_info()
.
INSERT ... SELECT
SyntaxINSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(column_list)] SELECT ...
With INSERT ... SELECT
, you can quickly insert many rows
into a table from one or many tables.
For example:
INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
The following conditions hold for an INSERT ... SELECT
statement:
INSERT ... SELECT
implicitly operates in
IGNORE
mode. As of MySQL 4.0.1, specify IGNORE
explicitly to ignore records that would cause duplicate-key violations.
DELAYED
with INSERT ... SELECT
.
INSERT
statement cannot appear in the
FROM
clause of the SELECT
part of the query.
This limitation is lifted in 4.0.14.
AUTO_INCREMENT
columns work as usual.
INSERT ... SELECT
.
You can use REPLACE
instead of INSERT
to overwrite old rows.
REPLACE
is the counterpart to INSERT IGNORE
in the treatment
of new rows that contain unique key values that duplicate old rows:
The new rows are used to replace the old rows rather than being discarded.
INSERT DELAYED
SyntaxINSERT DELAYED ...
The DELAYED
option for the INSERT
statement is a
MySQL extension to standard SQL that is very useful if you have clients
that can't wait for the INSERT
to complete. This is a common
problem when you use MySQL for logging and you also
periodically run SELECT
and UPDATE
statements that take a
long time to complete. DELAYED
was introduced in MySQL
3.22.15.
When a client uses INSERT DELAYED
, it gets an okay from the server at
once, and the row is queued to be inserted when the table is not in use by
any other thread.
Another major benefit of using INSERT DELAYED
is that inserts
from many clients are bundled together and written in one block. This is much
faster than doing many separate inserts.
There are some constraints on the use of DELAYED
:
INSERT DELAYED
works only with MyISAM
and ISAM
tables.
For MyISAM
tables, if there are no free blocks in the middle of the
data file, concurrent SELECT
and INSERT
statements are supported.
Under these circumstances, you very seldom need to use INSERT
DELAYED
with MyISAM
.
See section 14.1 The MyISAM
Storage Engine.
INSERT DELAYED
should be used only for INSERT
statements that
specify value lists. This is enforced as of MySQL 4.0.18. The server ignores
DELAYED
for INSERT DELAYED ... SELECT
statements.
DELAYED
for INSERT DELAYED ... ON DUPLICATE UPDATE
statements.
LAST_INSERT_ID()
to get the AUTO_INCREMENT
value the statement might generate.
DELAYED
rows are not visible to SELECT
statements until they
actually have been inserted.
Note that currently the queued rows are held only in memory until they are
inserted into the table. This means that if you terminate mysqld
forcibly (for example, with kill -9
) or if mysqld
dies
unexpectedly, any queued rows that have not been written to disk are lost!
The following describes in detail what happens when you use the
DELAYED
option to INSERT
or REPLACE
. In this
description, the ``thread'' is the thread that received an INSERT
DELAYED
statement and ``handler'' is the thread that handles all
INSERT DELAYED
statements for a particular table.
DELAYED
statement for a table, a handler
thread is created to process all DELAYED
statements for the table, if
no such handler already exists.
DELAYED
lock already; if not, it tells the handler thread to do so. The
DELAYED
lock can be obtained even if other threads have a READ
or WRITE
lock on the table. However, the handler will wait for all
ALTER TABLE
locks or FLUSH TABLES
to ensure that the table
structure is up to date.
INSERT
statement, but instead of writing
the row to the table, it puts a copy of the final row into a queue that
is managed by the handler thread. Any syntax errors are noticed by the
thread and reported to the client program.
AUTO_INCREMENT
value for the resulting row, because the
INSERT
returns before the insert operation has been completed. (If
you use the C API, the mysql_info()
function doesn't return anything
meaningful, for the same reason.)
delayed_insert_limit
rows are written, the handler checks
whether any SELECT
statements are still pending. If so, it
allows these to execute before continuing.
INSERT DELAYED
statements are received within
delayed_insert_timeout
seconds, the handler terminates.
delayed_queue_size
rows are pending already in a
specific handler queue, the thread requesting INSERT DELAYED
waits until there is room in the queue. This is done to ensure that
the mysqld
server doesn't use all memory for the delayed memory
queue.
delayed_insert
in the Command
column. It will be killed if
you execute a FLUSH TABLES
statement or kill it with KILL
thread_id
. However, before exiting, it will first store all queued rows into
the table. During this time it will not accept any new INSERT
statements from another thread. If you execute an INSERT DELAYED
statement after this, a new handler thread will be created.
Note that this means that INSERT DELAYED
statements have higher
priority than normal INSERT
statements if there is an INSERT
DELAYED
handler already running! Other update statements will have to wait
until the INSERT DELAYED
queue is empty, someone terminates the handler
thread (with KILL thread_id
), or someone executes FLUSH TABLES
.
INSERT
DELAYED
statements:
Status Variable | Meaning |
Delayed_insert_threads | Number of handler threads |
Delayed_writes | Number of rows written with INSERT DELAYED
|
Not_flushed_delayed_rows | Number of rows waiting to be written |
SHOW STATUS
statement or
by executing a mysqladmin extended-status
command.
Note that INSERT DELAYED
is slower than a normal INSERT
if the
table is not in use. There is also the additional overhead for the server
to handle a separate thread for each table for which there are delayed rows.
This means that you should use INSERT DELAYED
only when you are
really sure that you need it!
LOAD DATA INFILE
SyntaxLOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY '\t'] [[OPTIONALLY] ENCLOSED BY ''] [ESCAPED BY '\\' ] ] [LINES [STARTING BY ''] [TERMINATED BY '\n'] ] [IGNORE number LINES] [(col_name,...)]
The LOAD DATA INFILE
statement reads rows from a text file into a
table at a very high speed.
For more information about the efficiency of INSERT
versus
LOAD DATA INFILE
and speeding up LOAD DATA INFILE
,
section 7.2.14 Speed of INSERT
Statements.
You can also load data files by using the mysqlimport
utility; it
operates by sending a LOAD DATA INFILE
statement to the server. The
--local
option causes mysqlimport
to read data files from the
client host. You can specify the --compress
option to get better
performance over slow networks if the client and server support the
compressed protocol.
See section 8.10 The mysqlimport
Data Import Program.
If you specify the LOW_PRIORITY
keyword, execution of the
LOAD DATA
statement is delayed until no other clients are reading
from the table.
If you specify the CONCURRENT
keyword with a MyISAM
table that
satisfies the condition for concurrent inserts (that is, it contains no free
blocks in the middle),
then other threads can retrieve data from the table while LOAD DATA
is executing. Using this option affects the performance of LOAD DATA
a bit, even if no other thread is using the table at the same time.
If the LOCAL
keyword is specified, it is
interpreted with respect to the client end of the connection:
LOCAL
is specified, the file is read by the client program on the
client host and sent to the server.
LOCAL
is not specified, the
file must be located on the server host and is read directly by the server.
LOCAL
is available in MySQL 3.22.6 or later.
For security reasons, when reading text files located on the server, the
files must either reside in the database directory or be readable by all.
Also, to use LOAD DATA INFILE
on server files, you must have the
FILE
privilege.
See section 5.5.3 Privileges Provided by MySQL.
Using LOCAL
is a bit slower than letting the server access the files
directly, because the contents of the file must be sent over the connection
by the client to the server. On the other hand, you do not need the
FILE
privilege to load local files.
As of MySQL 3.23.49 and MySQL 4.0.2 (4.0.13 on Windows),
LOCAL
works only if your server
and your client both have been enabled to allow it. For example, if
mysqld
was started with --local-infile=0
, LOCAL
will
not work.
See section 5.4.4 Security Issues with LOAD DATA LOCAL
.
If you need LOAD DATA
to read from a pipe, you can use the
following technique (here we load the listing of the '/' directory into a
table):
mkfifo /mysql/db/x/x chmod 666 /mysql/db/x/x find / -ls > /mysql/db/x/x mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x
If you are using a version of MySQL older than 3.23.25,
you can use this technique only with LOAD DATA LOCAL INFILE
.
If you are using MySQL before Version 3.23.24, you can't read from a
FIFO with LOAD DATA INFILE
. If you need to read from a FIFO (for
example, the output from gunzip
), use LOAD DATA LOCAL INFILE
instead.
When locating files on the server host, the server uses the following rules:
Note that these rules mean that a file named as `./myfile.txt' is read from
the server's data directory, whereas the same file named as `myfile.txt' is
read from the database directory of the default database. For example,
the following LOAD DATA
statement reads the file `data.txt'
from the database directory for db1
because db1
is the current
database, even though the statement explicitly loads the file into a
table in the db2
database:
mysql> USE db1; mysql> LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;
The REPLACE
and IGNORE
keywords control handling of input
records that duplicate existing records on unique key values.
If you specify REPLACE
, input rows replace existing rows (in other
words, rows that have the same value for a primary or unique index as an
existing row). See section 13.1.6 REPLACE
Syntax.
If you specify IGNORE
, input rows that duplicate an existing row
on a unique key value are skipped. If you don't specify either option,
the behavior depends on whether or not the LOCAL
keyword is specified.
Without LOCAL
, an error occurs when a duplicate key value is
found, and the rest of the text file is ignored. With LOCAL
,
the default behavior is the same as if IGNORE
is specified;
this is because the server has no way to stop transmission of the file
in the middle of the operation.
If you want to ignore foreign key constraints during the load operation, you
can issue a SET FOREIGN_KEY_CHECKS=0
statement before executing
LOAD DATA
.
If you use LOAD DATA INFILE
on an empty MyISAM
table, all
non-unique indexes are created in a separate batch (as for
REPAIR TABLE
). This normally makes LOAD DATA INFILE
much faster
when you have many indexes. Normally this is very fast, but in some
extreme cases, you can create the indexes even faster by turning them off
with ALTER TABLE .. DISABLE KEYS
before loading the file into the
table and using ALTER TABLE .. ENABLE KEYS
to re-create the indexes
after loading the file.
See section 7.2.14 Speed of INSERT
Statements.
LOAD DATA INFILE
is the complement of SELECT ... INTO OUTFILE
.
See section 13.1.7 SELECT
Syntax.
To write data from a table to a file, use SELECT ... INTO OUTFILE
.
To read the file back into a table, use LOAD DATA INFILE
.
The syntax of the FIELDS
and LINES
clauses is the same for
both statements. Both clauses are optional, but FIELDS
must precede LINES
if both are specified.
If you specify a FIELDS
clause,
each of its subclauses (TERMINATED BY
, [OPTIONALLY] ENCLOSED
BY
, and ESCAPED BY
) is also optional, except that you must
specify at least one of them.
If you don't specify a FIELDS
clause, the defaults are the
same as if you had written this:
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
If you don't specify a LINES
clause, the default
is the same as if you had written this:
LINES TERMINATED BY '\n' STARTING BY ''
In other words, the defaults cause LOAD DATA INFILE
to act as follows
when reading input:
Conversely, the defaults cause SELECT ... INTO OUTFILE
to act as
follows when writing output:
Note that to write FIELDS ESCAPED BY '\\'
, you must specify two
backslashes for the value to be read as a single backslash.
Note: If you have generated the text file on a Windows system, you
might have to use LINES TERMINATED BY '\r\n'
to read the file
properly, because Windows programs typically use two characters as a line
terminator. Some programs, such as WordPad
, might use \r
as a line
terminator when writing files. To read such files, use LINES
TERMINATED BY '\r'
.
If all the lines you want to read in have a common prefix that you want
to ignore, you can use LINES STARTING BY 'prefix_string'
to skip
over the prefix (and anything before it). If a line doesn't include the
prefix, the entire line is skipped. Note that
prefix_string
may be in the middle of the line!
Example:
mysql> LOAD DATA INFILE '/tmp/test.txt' -> INTO TABLE test LINES STARTING BY "xxx";
With this you can read in a file that contains something like:
xxx"Row",1 something xxx"Row",2
And just get the data ("row",1)
and ("row",2)
.
The IGNORE number LINES
option can be used to ignore lines at
the start of the file. For example, you can use IGNORE 1 LINES
to skip over an initial header line containing column names:
mysql> LOAD DATA INFILE '/tmp/test.txt' -> INTO TABLE test IGNORE 1 LINES;
When you use SELECT ... INTO OUTFILE
in tandem with LOAD
DATA INFILE
to write data from a database into a file and then read
the file back into the database later, the field- and line-handling
options for both statements must match. Otherwise, LOAD DATA
INFILE
will not interpret the contents of the file properly. Suppose
that you use SELECT ... INTO OUTFILE
to write a file with
fields delimited by commas:
mysql> SELECT * INTO OUTFILE 'data.txt' -> FIELDS TERMINATED BY ',' -> FROM table2;
To read the comma-delimited file back in, the correct statement would be:
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2 -> FIELDS TERMINATED BY ',';
If instead you tried to read in the file with the statement shown here, it
wouldn't work because it instructs LOAD DATA INFILE
to look for
tabs between fields:
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2 -> FIELDS TERMINATED BY '\t';
The likely result is that each input line would be interpreted as a single field.
LOAD DATA INFILE
can be used to read files obtained from
external sources, too. For example, a file in dBASE format will have
fields separated by commas and enclosed within double quotes. If lines in
the file are terminated by newlines, the statement shown here
illustrates the field- and line-handling options you would use to load
the file:
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name -> FIELDS TERMINATED BY ',' ENCLOSED BY '"' -> LINES TERMINATED BY '\n';
Any of the field- or line-handling options can specify an empty string
(''
). If not empty, the FIELDS [OPTIONALLY] ENCLOSED BY
and FIELDS ESCAPED BY
values must be a single character. The
FIELDS TERMINATED BY
, LINES STARTING BY
, and LINES
TERMINATED BY
values can be more than one character. For example, to write
lines that are terminated by carriage return/linefeed pairs, or to read a
file containing such lines, specify a LINES TERMINATED BY '\r\n'
clause.
To read a file containing jokes that are separated by lines consisting
of %%
, you can do this
mysql> CREATE TABLE jokes -> (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> joke TEXT NOT NULL); mysql> LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes -> FIELDS TERMINATED BY '' -> LINES TERMINATED BY '\n%%\n' (joke);
FIELDS [OPTIONALLY] ENCLOSED BY
controls quoting of fields. For
output (SELECT ... INTO OUTFILE
), if you omit the word
OPTIONALLY
, all fields are enclosed by the ENCLOSED BY
character. An example of such output (using a comma as the field
delimiter) is shown here:
"1","a string","100.20" "2","a string containing a , comma","102.20" "3","a string containing a \" quote","102.20" "4","a string containing a \", quote and comma","102.20"
If you specify OPTIONALLY
, the ENCLOSED BY
character is
used only to enclose CHAR
and VARCHAR
fields:
1,"a string",100.20 2,"a string containing a , comma",102.20 3,"a string containing a \" quote",102.20 4,"a string containing a \", quote and comma",102.20
Note that occurrences of the ENCLOSED BY
character within a
field value are escaped by prefixing them with the ESCAPED BY
character. Also note that if you specify an empty ESCAPED BY
value, it is possible to generate output that cannot be read properly by
LOAD DATA INFILE
. For example, the preceding output just shown would
appear as follows if the escape character is empty. Observe that the
second field in the fourth line contains a comma following the quote, which
(erroneously) appears to terminate the field:
1,"a string",100.20 2,"a string containing a , comma",102.20 3,"a string containing a " quote",102.20 4,"a string containing a ", quote and comma",102.20
For input, the ENCLOSED BY
character, if present, is stripped
from the ends of field values. (This is true whether or not OPTIONALLY
is specified; OPTIONALLY
has no effect on input interpretation.)
Occurrences of the ENCLOSED BY
character preceded by the
ESCAPED BY
character are interpreted as part of the current
field value.
If the field begins with the ENCLOSED BY
character, instances
of that character are recognized as terminating a field value only
if followed by the field or line TERMINATED BY
sequence.
To avoid ambiguity, occurrences of the ENCLOSED BY
character
within a field value can be doubled and will be interpreted as a
single instance of the character. For example, if ENCLOSED
BY '"'
is specified, quotes are handled as shown here:
"The ""BIG"" boss" -> The "BIG" boss The "BIG" boss -> The "BIG" boss The ""BIG"" boss -> The ""BIG"" boss
FIELDS ESCAPED BY
controls how to write or read special characters.
If the FIELDS ESCAPED BY
character is not empty, it is used to prefix
the following characters on output:
FIELDS ESCAPED BY
character
FIELDS [OPTIONALLY] ENCLOSED BY
character
FIELDS TERMINATED BY
and
LINES TERMINATED BY
values
0
(what is actually written following the escape character is
ASCII `0', not a zero-valued byte)
If the FIELDS ESCAPED BY
character is empty, no characters are
escaped and NULL
is output as NULL
, not \N
. It is
probably not a good idea to specify an empty escape character,
particularly if field values in your data contain any of the characters
in the list just given.
For input, if the FIELDS ESCAPED BY
character is not empty, occurrences
of that character are stripped and the following character is taken literally
as part of a field value. The exceptions are an escaped `0' or
`N' (for example, \0
or \N
if the escape character is
`\'). These sequences are interpreted as ASCII NUL (a zero-valued
byte) and NULL
. The rules for NULL
handling are described later
in this section.
For more information about `\'-escape syntax, see section 9.1 Literal Values.
In certain cases, field- and line-handling options interact:
LINES TERMINATED BY
is an empty string and FIELDS
TERMINATED BY
is non-empty, lines are also terminated with
FIELDS TERMINATED BY
.
FIELDS TERMINATED BY
and FIELDS ENCLOSED BY
values
are both empty (''
), a fixed-row (non-delimited) format is used.
With fixed-row format, no delimiters are used between fields (but you
can still have a line terminator). Instead, column values are written
and read using the ``display'' widths of the columns. For example, if a
column is declared as INT(7)
, values for the column are written
using seven-character fields. On input, values for the column are obtained
by reading seven characters.
LINES TERMINATED BY
is still used to separate lines. If a line
doesn't contain all fields, the rest of the columns are set to their
default values. If you don't have a line terminator, you should set this
to ''
. In this case, the text file must contain all fields for
each row.
Fixed-row format also affects handling of NULL
values, as described
later.
Note that fixed-size format will not work if you are using a multi-byte
character set.
Handling of NULL
values varies according to the FIELDS
and
LINES
options in use:
FIELDS
and LINES
values, NULL
is
written as a field value of \N
for output, and a field value of
\N
is read as NULL
for input (assuming that the ESCAPED BY
character is `\').
FIELDS ENCLOSED BY
is not empty, a field containing the literal
word NULL
as its value is read as a NULL
value. This differs
from the word NULL
enclosed within FIELDS ENCLOSED BY
characters, which is read as the string 'NULL'
.
FIELDS ESCAPED BY
is empty, NULL
is written as the word
NULL
.
FIELDS TERMINATED BY
and
FIELDS ENCLOSED BY
are both empty), NULL
is written as an empty
string. Note that this causes both NULL
values and empty strings in
the table to be indistinguishable when written to the file because they are
both written as empty strings. If you need to be able to tell the two apart
when reading the file back in, you should not use fixed-row format.
Some cases are not supported by LOAD DATA INFILE
:
FIELDS TERMINATED BY
and FIELDS ENCLOSED
BY
both empty) and BLOB
or TEXT
columns.
LOAD DATA INFILE
won't be able to interpret the input properly.
For example, the following FIELDS
clause would cause problems:
FIELDS TERMINATED BY '"' ENCLOSED BY '"'
FIELDS ESCAPED BY
is empty, a field value that contains an occurrence
of FIELDS ENCLOSED BY
or LINES TERMINATED BY
followed by the FIELDS TERMINATED BY
value will cause LOAD
DATA INFILE
to stop reading a field or line too early.
This happens because LOAD DATA INFILE
cannot properly determine
where the field or line value ends.
The following example loads all columns of the persondata
table:
mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;
By default, when no column list is provided at the end of the LOAD
DATA INFILE
statement, input lines are expected to contain a field for each
table column. If you want to load only some of a table's columns, specify a
column list:
mysql> LOAD DATA INFILE 'persondata.txt' -> INTO TABLE persondata (col1,col2,...);
You must also specify a column list if the order of the fields in the input file differs from the order of the columns in the table. Otherwise, MySQL cannot tell how to match up input fields with table columns.
If an input line has too many fields, the extra fields are ignored and the number of warnings is incremented.
If an input line has too few fields, the table columns for which input
fields are missing are set to their default values. Default value assignment
is described in section 13.2.6 CREATE TABLE
Syntax.
An empty field value is interpreted differently than if the field value is missing:
0
.
These are the same values that result if you assign an empty
string explicitly to a string, numeric, or date or time type explicitly
in an INSERT
or UPDATE
statement.
TIMESTAMP
columns are set to the current date and time only if there
is a NULL
value for the column (that is, \N
), or (for the
first TIMESTAMP
column only) if the TIMESTAMP
column is
omitted from the field list when a field list is specified.
LOAD DATA INFILE
regards all input as strings, so you can't use
numeric values for ENUM
or SET
columns the way you can with
INSERT
statements. All ENUM
and SET
values must be
specified as strings!
When the LOAD DATA INFILE
statement finishes, it returns an information string in the following format:
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
If you are using the C API, you can get information about the statement by
calling the mysql_info()
function.
See section 21.2.3.31 mysql_info()
.
Warnings occur under the same circumstances as when values are inserted
via the INSERT
statement (see section 13.1.4 INSERT
Syntax), except
that LOAD DATA INFILE
also generates warnings when there are too few
or too many fields in the input row. The warnings are not stored anywhere;
the number of warnings can be used only as an indication of whether everything went
well.
From MySQL 4.1.1 on, you can use SHOW WARNINGS
to get a list of the
first max_error_count
warnings as information about what went wrong.
See section 13.5.4.20 SHOW WARNINGS
Syntax.
Before MySQL 4.1.1, only a warning count is available to indicate that
something went wrong. If you get warnings and want to know exactly why you
got them, one way to do this is to dump the table into another file using
SELECT ... INTO OUTFILE
and compare the file to your original input
file.
REPLACE
SyntaxREPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),...
Or:
REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name SET col_name={expr | DEFAULT}, ...
Or:
REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)] SELECT ...
REPLACE
works exactly like INSERT
, except that if an old
record in the table has the same value as a new record for a PRIMARY
KEY
or a UNIQUE
index, the old record is deleted before the new
record is inserted.
See section 13.1.4 INSERT
Syntax.
Note that unless the table has a PRIMARY KEY
or UNIQUE
index,
using a REPLACE
statement makes no sense. It becomes equivalent to
INSERT
, because there is no index to be used to determine whether a new
row duplicates another.
Values for all columns are taken from the values specified in the
REPLACE
statement. Any missing columns are set to their default
values, just as happens for INSERT
. You can't refer to values from
the old row and use them in the new row. It appeared that you could do this
in some old MySQL versions, but that was a bug that has been corrected.
To be able to use REPLACE
, you must have INSERT
and
DELETE
privileges for the table.
The REPLACE
statement returns a count to indicate the number of rows
affected. This is the sum of the rows deleted and inserted. If the count is 1
for a single-row REPLACE
, a row was inserted and no rows were deleted.
If the count is greater than 1, one or more old rows were deleted before the
new row was inserted. It is possible for a single row to replace more than one
old row if the table contains multiple unique indexes and the new row
duplicates values for different old rows in different unique indexes.
The affected-rows count makes it easy to determine whether REPLACE
only added a row or whether it also replaced any rows: Check whether the
count is 1 (added) or greater (replaced).
If you are using the C API, the affected-rows count can be obtained using the
mysql_affected_rows()
function.
Currently, you cannot replace into a table and select from the same table in a subquery.
Here follows in more detail the algorithm that is used
(it is also used with LOAD DATA ... REPLACE
):
SELECT
SyntaxSELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr, ... [INTO OUTFILE 'file_name' export_options | INTO DUMPFILE 'file_name'] [FROM table_references [WHERE where_definition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_definition] [ORDER BY {col_name | expr | position} [ASC | DESC] , ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [FOR UPDATE | LOCK IN SHARE MODE]]
SELECT
is used to retrieve rows selected from one or more tables.
Support for UNION
statements and subqueries is available as of MySQL
4.0 and 4.1, respectively.
See section 13.1.7.2 UNION
Syntax and section 13.1.8 Subquery Syntax.
JOIN
Syntax.
WHERE
followed by
an expression that indicates the condition or conditions that rows
must satisfy to be selected.
SELECT
can also be used to retrieve rows computed without reference to
any table.
For example:
mysql> SELECT 1 + 1; -> 2
All clauses used must be given in exactly the order shown in the syntax
description. For example,
a HAVING
clause must come after any GROUP BY
clause and before
any ORDER BY
clause.
AS alias_name
.
The alias is used as the expression's column name and can be used in
GROUP BY
,
ORDER BY
, or HAVING
clauses. For example:
mysql> SELECT CONCAT(last_name,', ',first_name) AS full_name -> FROM mytable ORDER BY full_name;The
AS
keyword is optional when aliasing a select_expr.
The preceding example could have been written like this:
mysql> SELECT CONCAT(last_name,', ',first_name) full_name -> FROM mytable ORDER BY full_name;Because the
AS
is optional, a subtle problem can occur
if you forget the comma between two select_expr expressions: MySQL
interprets the second as an alias name. For example, in the following
statement, columnb
is treated as an alias name:
mysql> SELECT columna columnb FROM mytable;
WHERE
clause,
because the column value might not yet be determined when the
WHERE
clause is executed.
See section A.5.4 Problems with Column Aliases.
FROM table_references
clause indicates the tables from which to
retrieve rows. If you name more than one table, you are performing a
join. For information on join syntax, see section 13.1.7.1 JOIN
Syntax.
For each table specified, you can optionally specify an alias.
tbl_name [[AS] alias] [[USE INDEX (key_list)] | [IGNORE INDEX (key_list)] | [FORCE INDEX (key_list)]]The use of
USE INDEX
,
IGNORE INDEX
,
FORCE INDEX
to give the optimizer hints about how to choose indexes is described in
section 13.1.7.1 JOIN
Syntax.
In MySQL 4.0.14, you can use SET max_seeks_for_key=value
as an
alternative way to force MySQL to prefer key scans instead of table scans.
DUAL
as a dummy
table name in situations where no tables are referenced:
mysql> SELECT 1 + 1 FROM DUAL; -> 2
DUAL
is purely a compatibility feature. Some other servers require
this syntax.
tbl_name AS alias_name
or
tbl_name alias_name:
mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2 -> WHERE t1.name = t2.name; mysql> SELECT t1.name, t2.salary FROM employee t1, info t2 -> WHERE t1.name = t2.name;
WHERE
clause, you can use any of the functions that
MySQL supports, except for aggregate (summary) functions.
See section 12 Functions and Operators.
ORDER BY
and
GROUP BY
clauses using column names, column aliases, or column
positions. Column positions are integers and begin with 1:
mysql> SELECT college, region, seed FROM tournament -> ORDER BY region, seed; mysql> SELECT college, region AS r, seed AS s FROM tournament -> ORDER BY r, s; mysql> SELECT college, region, seed FROM tournament -> ORDER BY 2, 3;To sort in reverse order, add the
DESC
(descending) keyword to the
name of the column in the ORDER BY
clause that you are sorting by.
The default is ascending order; this can be specified explicitly using
the ASC
keyword.
Use of column positions is deprecated because the syntax has been removed from
the SQL standard.
GROUP BY
, output rows are sorted according to the
GROUP BY
columns as if you had an ORDER BY
for the same columns.
MySQL has extended the GROUP BY
clause as of version 3.23.34 so that
you can also specify ASC
and DESC
after columns named in the
clause:
SELECT a, COUNT(b) FROM test_table GROUP BY a DESC
GROUP BY
to allow you to
select fields that are not mentioned in the GROUP BY
clause.
If you are not getting the results you expect from your query, please
read the GROUP BY
description.
See section 12.9 Functions and Modifiers for Use with GROUP BY
Clauses.
GROUP BY
allows a WITH ROLLUP
modifier.
See section 12.9.2 GROUP BY
Modifiers.
HAVING
clause can refer to any column or alias named in a
select_expr. It is applied nearly last, just before items are
sent to the client, with no optimization.
(LIMIT
is applied after HAVING
.)
HAVING
for items that
should be in the WHERE
clause. For example, do not write this:
mysql> SELECT col_name FROM tbl_name HAVING col_name > 0;Write this instead:
mysql> SELECT col_name FROM tbl_name WHERE col_name > 0;
HAVING
clause can refer to aggregate functions, which the
WHERE
clause cannot:
mysql> SELECT user, MAX(salary) FROM users -> GROUP BY user HAVING MAX(salary)>10;However, that does not work in older MySQL servers (before version 3.22.5). Instead, you can use a column alias in the select list and refer to the alias in the
HAVING
clause:
mysql> SELECT user, MAX(salary) AS max_salary FROM users -> GROUP BY user HAVING max_salary>10;
LIMIT
clause can be used to constrain the number of rows returned
by the SELECT
statement. LIMIT
takes one or two numeric
arguments, which must be integer constants.
With two arguments, the first argument specifies the offset of the first row to
return, and the second specifies the maximum number of rows to return.
The offset of the initial row is 0 (not 1):
mysql> SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15For compatibility with PostgreSQL, MySQL also supports the
LIMIT row_count OFFSET offset
syntax.
To retrieve all rows from a certain offset up to the end of the result set,
you can use some large number for the second parameter. This statement
retrieves all rows from the 96th row to the last:
mysql> SELECT * FROM table LIMIT 95,18446744073709551615;With one argument, the value specifies the number of rows to return from the beginning of the result set:
mysql> SELECT * FROM table LIMIT 5; # Retrieve first 5 rowsIn other words,
LIMIT n
is equivalent to LIMIT 0,n
.
SELECT ... INTO OUTFILE 'file_name'
form of SELECT
writes
the selected rows to a file. The file is created on the server host, so you
must have the FILE
privilege to use this syntax. The
file cannot already exist, which among other things prevents files such as
`/etc/passwd' and database tables from being destroyed.
The SELECT ... INTO OUTFILE
statement is intended primarily to let
you very quickly dump a table on the server machine. If you want to create
the resulting file on some client host other than the server host, you can't use
SELECT ... INTO OUTFILE
. In that case, you should instead use some
command like mysql -e "SELECT ..." > file_name
on the client host to generate the file.
SELECT ... INTO OUTFILE
is the complement of LOAD DATA
INFILE
; the syntax for the export_options
part of the statement
consists of the same FIELDS
and LINES
clauses that are used
with the LOAD DATA INFILE
statement.
See section 13.1.5 LOAD DATA INFILE
Syntax.
FIELDS ESCAPED BY
controls how to write special characters.
If the FIELDS ESCAPED BY
character is not empty, it is used to prefix
the following characters on output:
FIELDS ESCAPED BY
character
FIELDS [OPTIONALLY] ENCLOSED BY
character
FIELDS TERMINATED BY
and
LINES TERMINATED BY
values
0
(what is actually written following the escape character is
ASCII `0', not a zero-valued byte)
FIELDS ESCAPED BY
character is empty, no characters are
escaped and NULL
is output as NULL
, not \N
. It is
probably not a good idea to specify an empty escape character,
particularly if field values in your data contain any of the characters
in the list just given.
The reason for the above is that you must escape any FIELDS
TERMINATED BY
, ENCLOSED BY
, ESCAPED BY
, or LINES TERMINATED BY
characters to reliably be able to read the file back. ASCII NUL is
escaped to make it easier to view with some pagers.
The resulting file doesn't have to conform to SQL syntax, so nothing
else need be escaped.
Here is an example that produces a file in the comma-separated values format
used by many programs:
SELECT a,b,a+b INTO OUTFILE '/tmp/result.text' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;
INTO DUMPFILE
instead of INTO OUTFILE
, MySQL writes
only one row into the file, without any column or line termination and
without performing any escape processing. This is useful if you want to
store a BLOB
value in a file.
INTO OUTFILE
or INTO
DUMPFILE
is writable by all users on the server host. The reason for
this is that the MySQL server can't create a file that is owned by anyone
other than the user it's running as (you should never run mysqld
as
root
). The file thus must be world-writable so that you can
manipulate its contents.
PROCEDURE
clause names a procedure that should process the data
in the result set. For an example, see section 24.3.1 Procedure Analyse.
FOR UPDATE
on a storage engine that uses page or row locks,
rows examined by the query are write-locked until the end of the current
transaction. Using IN SHARE MODE
sets a shared lock that prevents
other transactions from updating or deleting the examined rows.
See section 15.11.4 Locking Reads SELECT ... FOR UPDATE
and SELECT ... LOCK IN SHARE MODE
.
Following the SELECT
keyword, you can give a number of options
that affect the operation of the statement.
The ALL
, DISTINCT
, and DISTINCTROW
options specify
whether duplicate rows should be returned. If none of these options are
given, the default is ALL
(all matching rows are returned).
DISTINCT
and DISTINCTROW
are synonyms and specify that
duplicate rows in the result set should be removed.
HIGH_PRIORITY
, STRAIGHT_JOIN
, and options beginning with
SQL_
are MySQL extensions to standard SQL.
HIGH_PRIORITY
will give the SELECT
higher priority than
a statement that updates a table. You should use this only for queries
that are very fast and must be done at once. A SELECT HIGH_PRIORITY
query that is issued while the table is locked for reading will run even if
there is already an update statement waiting for the table to be free.
HIGH_PRIORITY
cannot be used with SELECT
statements that
are part of a UNION
.
STRAIGHT_JOIN
forces the optimizer to join the tables in the order in
which they are listed in the FROM
clause. You can use this to speed up
a query if the optimizer joins the tables in non-optimal order.
See section 7.2.1 EXPLAIN
Syntax (Get Information About a SELECT
).
STRAIGHT_JOIN
also can be used in the table_references list.
See section 13.1.7.1 JOIN
Syntax.
SQL_BIG_RESULT
can be used with GROUP BY
or DISTINCT
to tell the optimizer that the result set will have many rows. In this case,
MySQL will directly use disk-based temporary tables if needed.
MySQL will also, in this case, prefer sorting to using a
temporary table with a key on the GROUP BY
elements.
SQL_BUFFER_RESULT
forces the result to be put into a temporary
table. This helps MySQL free the table locks early and helps
in cases where it takes a long time to send the result set to the client.
SQL_SMALL_RESULT
can be used
with GROUP BY
or DISTINCT
to tell the optimizer that the
result set will be small. In this case, MySQL uses fast
temporary tables to store the resulting table instead of using sorting. In
MySQL 3.23 and up, this shouldn't normally be needed.
SQL_CALC_FOUND_ROWS
(available in MySQL 4.0.0 and up) tells MySQL
to calculate how many rows there would be in the result set, disregarding
any LIMIT
clause. The number of rows can then be retrieved with
SELECT FOUND_ROWS()
.
See section 12.8.3 Information Functions.
Before MySQL 4.1.0, this option does not work with
LIMIT 0
, which is optimized to return instantly (resulting in a
row count of 0).
See section 7.2.12 How MySQL Optimizes LIMIT
.
SQL_CACHE
tells MySQL to store the query result in the query cache if
you are using a query_cache_type
value of 2
or DEMAND
.
For a query that uses UNION
or subqueries, this option takes effect
to be used in any SELECT
of the query.
See section 5.11 The MySQL Query Cache.
SQL_NO_CACHE
tells MySQL not to store the query result
in the query cache. See section 5.11 The MySQL Query Cache.
For a query that uses UNION
or subqueries, this
option takes effect to be used in any SELECT
of the query.
JOIN
Syntax
MySQL supports the following JOIN
syntaxes for the
table_references part of SELECT
statements and multiple-table
DELETE
and UPDATE
statements:
table_reference, table_reference table_reference [INNER | CROSS] JOIN table_reference [join_condition] table_reference STRAIGHT_JOIN table_reference table_reference LEFT [OUTER] JOIN table_reference [join_condition] table_reference NATURAL [LEFT [OUTER]] JOIN table_reference { OJ table_reference LEFT OUTER JOIN table_reference ON conditional_expr } table_reference RIGHT [OUTER] JOIN table_reference [join_condition] table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference
table_reference is defined as:
tbl_name [[AS] alias] [[USE INDEX (key_list)] | [IGNORE INDEX (key_list)] | [FORCE INDEX (key_list)]]
join_condition is defined as:
ON conditional_expr | USING (column_list)
You should generally not have any conditions in the ON
part that are
used to restrict which rows you want in the result set, but rather specify
these conditions in the WHERE
clause. There are exceptions to this rule.
Note that INNER JOIN
syntax allows a join_condition
only from
MySQL 3.23.17 on. The same is true for JOIN
and CROSS JOIN
only
as of MySQL 4.0.11.
The { OJ ... LEFT OUTER JOIN ...}
syntax shown in the preceding list
exists only for compatibility with ODBC.
tbl_name AS alias_name
or
tbl_name alias_name:
mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2 -> WHERE t1.name = t2.name; mysql> SELECT t1.name, t2.salary FROM employee t1, info t2 -> WHERE t1.name = t2.name;
ON
conditional is any conditional expression of the form that can
be used in a WHERE
clause.
ON
or
USING
part in a LEFT JOIN
, a row with all columns set to
NULL
is used for the right table. You can use this fact to find
records in a table that have no counterpart in another table:
mysql> SELECT table1.* FROM table1 -> LEFT JOIN table2 ON table1.id=table2.id -> WHERE table2.id IS NULL;This example finds all rows in
table1
with an id
value that is
not present in table2
(that is, all rows in table1
with no
corresponding row in table2
). This assumes that table2.id
is
declared NOT NULL
.
See section 7.2.9 How MySQL Optimizes LEFT JOIN
and RIGHT JOIN
.
USING
(column_list)
clause names a list of columns that must
exist in both tables. The following two clauses are semantically identical:
a LEFT JOIN b USING (c1,c2,c3) a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3
NATURAL [LEFT] JOIN
of two tables is defined to be
semantically equivalent to an INNER JOIN
or a LEFT JOIN
with a USING
clause that names all columns that exist in both
tables.
INNER JOIN
and ,
(comma) are semantically equivalent in
the absence of a join condition: both will produce a Cartesian product
between the specified tables (that is, each and every row in the first table
will be joined onto all rows in the second table).
RIGHT JOIN
works analogously to LEFT JOIN
. To keep code
portable across databases, it's recommended to use LEFT JOIN
instead of RIGHT JOIN
.
STRAIGHT_JOIN
is identical to JOIN
, except that the left table
is always read before the right table. This can be used for those (few)
cases for which the join optimizer puts the tables in the wrong order.
As of MySQL 3.23.12, you can give hints about which index MySQL
should use when retrieving information from a table. By specifying
USE INDEX (key_list)
, you can tell MySQL to use only one of the
possible indexes to find rows in the table. The alternative syntax
IGNORE INDEX (key_list)
can be used to tell MySQL to not use some
particular index. These hints are useful if EXPLAIN
shows that MySQL
is using the wrong index from the list of possible indexes.
From MySQL 4.0.9 on, you can also use FORCE INDEX
. This acts likes
USE INDEX (key_list)
but with the addition that a table scan
is assumed to be very expensive. In other words, a table scan will
only be used if there is no way to use one of the given indexes to
find rows in the table.
USE KEY
, IGNORE KEY
, and FORCE KEY
are synonyms for
USE INDEX
, IGNORE INDEX
, and FORCE INDEX
.
Note: USE INDEX
, IGNORE INDEX
, and FORCE INDEX
only affect which indexes are used when MySQL decides how to find rows in
the table and how to do the join. They do not affect whether an index will
be used when resolving an ORDER BY
or GROUP BY
.
Some join examples:
mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id; mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id); mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id -> LEFT JOIN table3 ON table2.id=table3.id; mysql> SELECT * FROM table1 USE INDEX (key1,key2) -> WHERE key1=1 AND key2=2 AND key3=3; mysql> SELECT * FROM table1 IGNORE INDEX (key3) -> WHERE key1=1 AND key2=2 AND key3=3;
See section 7.2.9 How MySQL Optimizes LEFT JOIN
and RIGHT JOIN
.
UNION
SyntaxSELECT ... UNION [ALL | DISTINCT] SELECT ... [UNION [ALL | DISTINCT] SELECT ...]
UNION
is used to combine the result from many SELECT
statements into one result set. UNION
is available from MySQL 4.0.0
on.
Selected columns listed in corresponding positions of each SELECT
statement should have the same type. (For example, the first column selected
by the first statement should have the same type as the first column selected
by the other statements.) The column names used in
the first SELECT
statement are used as the column names for the
results returned.
The SELECT
statements are normal select statements, but with the
following restrictions:
SELECT
statement can have INTO OUTFILE
.
HIGH_PRIORITY
cannot be used with SELECT
statements that
are part of a UNION
. If you specify it for the first SELECT
,
it has no effect. If you specify it for any subsequent SELECT
statements, a syntax error results.
If you don't use the keyword ALL
for the UNION
, all
returned rows will be unique, as if you had done a DISTINCT
for
the total result set. If you specify ALL
, you will get all
matching rows from all the used SELECT
statements.
The DISTINCT
keyword is an optional word (introduced in MySQL 4.0.17).
It does nothing, but is allowed in the syntax as required by the SQL standard.
Before MySQL 4.1.2, you cannot mix UNION ALL
and UNION
DISTINCT
in the same query. If you use ALL
for one
UNION
, it is used for all of them. As of MySQL 4.1.2, mixed
UNION
types are treated such that a DISTINCT
union overrides
any ALL
union to its left. A DISTINCT
union can be produced
explicitly by using UNION DISTINCT
or implicitly by using UNION
with no following DISTINCT
or ALL
keyword.
If you want to use an ORDER BY
or LIMIT
clause to sort or limit
the entire UNION
result, parenthesize the individual SELECT
statements and place the ORDER BY
or LIMIT
after the last one.
The following example uses both clauses:
(SELECT a FROM tbl_name WHERE a=10 AND B=1) UNION (SELECT a FROM tbl_name WHERE a=11 AND B=2) ORDER BY a LIMIT 10;
This kind of ORDER BY
cannot use column references that include a
table name (that is, names in tbl_name.col_name format). Instead,
provide a column alias in the first SELECT
statement and refer to the
alias in the ORDER BY
, or else refer to the column in the ORDER
BY
using its column position. (An alias is preferable because use of
column positions is deprecated.)
To apply ORDER BY
or LIMIT
to an individual SELECT
,
place the clause inside the parentheses that enclose the SELECT
:
(SELECT a FROM tbl_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM tbl_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
The types and lengths of the columns in the result set of a UNION
take into account the values retrieved by all the SELECT
statements.
Before MySQL 4.1.1, a limitation of UNION
is that only the values from
the first SELECT
are used to determine result column types and lengths.
This could result in value truncation if, for example, the first
SELECT
retrieves shorter values than the second SELECT
:
mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10); +---------------+ | REPEAT('a',1) | +---------------+ | a | | b | +---------------+
That limitation has been removed as of MySQL 4.1.1:
mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10); +---------------+ | REPEAT('a',1) | +---------------+ | a | | bbbbbbbbbb | +---------------+
A subquery is a SELECT
statement inside another statement.
Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific.
With MySQL versions prior to 4.1, it was necessary to work around or avoid the use of subqueries. In many cases, subqueries can successfully be rewritten using joins and other methods. See section 13.1.8.11 Rewriting Subqueries as Joins for Earlier MySQL Versions.
Here is an example of a subquery:
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
In this example, SELECT * FROM t1 ...
is the outer query
(or outer statement), and (SELECT column1 FROM t2)
is the
subquery.
We say that the subquery is nested in the outer query, and in fact
it's possible to nest subqueries within other subqueries, to a great depth.
A subquery must always appear within parentheses.
The main advantages of subqueries are:
Here is an example statement that shows the major points about subquery syntax as specified by the SQL standard and supported in MySQL:
DELETE FROM t1 WHERE s11 > ANY (SELECT COUNT(*) /* no hint */ FROM t2 WHERE NOT EXISTS (SELECT * FROM t3 WHERE ROW(5*t2.s1,77)= (SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM (SELECT * FROM t5) AS t5)));
A subquery can return a scalar (a single value), a single row, a single column, or a table (one or more rows of one or more columns). These are called scalar, column, row, and table subqueries. Subqueries that return a particular kind of result often can be used only in certain contexts, as described in the following sections.
There are few restrictions on the type of statements in which subqueries can be used:
SELECT
, INSERT
, UPDATE
, DELETE
,
SET
, or DO
.
SELECT
can contain:
DISTINCT
, GROUP BY
, ORDER BY
, LIMIT
,
joins, index hints, UNION
constructs, comments, functions, and so on.
One restriction is that currently you cannot modify a table and select from
the same table in a subquery. This applies to statements such as
DELETE
, INSERT
, REPLACE
, and UPDATE
.
In its simplest form, a subquery is a scalar subquery that returns a single
value. A scalar subquery is a simple operand, and you can use it wherever a
single column value or literal is legal, and you can expect it to have those
characteristics that all operands have: a data type, a length, an indication
whether it can be NULL
, and so on. For example:
CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL); INSERT INTO t1 VALUES(100, 'abcde'); SELECT (SELECT s2 FROM t1);
The subquery in this SELECT
returns a single value ('abcde'
)
that has a data type of CHAR
, a length of 5, a character set and
collation equal to the defaults in effect at CREATE TABLE
time, and
an indication that the value in the column can be NULL
. In fact,
almost all subqueries can be NULL
. If the table used in the example
were empty, the value of the subquery would be NULL
.
When you see examples in the following sections that contain the rather
spartan construct (SELECT column1 FROM t1)
, imagine that your own
code will contain much more diverse and complex constructions.
For example, suppose that we make two tables:
CREATE TABLE t1 (s1 INT); INSERT INTO t1 VALUES (1); CREATE TABLE t2 (s1 INT); INSERT INTO t2 VALUES (2);
Then perform a SELECT
:
SELECT (SELECT s1 FROM t2) FROM t1;
The result will be 2
because there is a row in t2
containing a
column s1
that has a value of 2
.
A scalar subquery can be part of an expression. Don't forget the parentheses, even if the subquery is an operand that provides an argument for a function. For example:
SELECT UPPER((SELECT s1 FROM t1)) FROM t2;
The most common use of a subquery is in the form:
non_subquery_operand comparison_operator (subquery)
Where comparison_operator is one of these operators:
= > < >= <= <>
For example:
... 'a' = (SELECT column1 FROM t1)
At one time the only legal place for a subquery was on the right side of a comparison, and you might still find some old DBMSs that insist on this.
Here is an example of a common-form subquery comparison that you cannot do
with a join. It finds all the values in table t1
that are equal to a
maximum value in table t2
:
SELECT column1 FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2);
Here is another example, which again is impossible with a join because it
involves aggregating for one of the tables. It finds all rows in table
t1
containing a value that occurs twice in a given column:
SELECT * FROM t1 AS t WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);
For a comparison performed with one of these comparison operators, the
subquery must return a scalar, with the exception that =
can be used
with row subqueries.
See section 13.1.8.5 Row Subqueries.
ANY
, IN
, and SOME
Syntax:
operand comparison_operator ANY (subquery) operand IN (subquery) operand comparison_operator SOME (subquery)
The ANY
keyword, which must follow a comparison operator, means
``return TRUE
if the comparison is TRUE
for ANY
of the
values in the column that the subquery returns.''
For example:
SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);
Suppose that there is a row in table t1
containing (10)
.
The expression is TRUE
if table t2
contains (21,14,7)
because there is a value 7
in t2
that is less than 10
.
The expression is FALSE
if table t2
contains (20,10)
,
or if table t2
is empty. The expression is UNKNOWN
if table
t2
contains (NULL,NULL,NULL)
.
The word IN
is an alias for = ANY
. Thus these two statements
are the same:
SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2); SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);
However, NOT IN
is not an alias for <> ANY
, but for
<> ALL
. See section 13.1.8.4 Subqueries with ALL
.
The word SOME
is an alias for ANY
. Thus these two statements
are the same:
SELECT s1 FROM t1 WHERE s1 <> ANY (SELECT s1 FROM t2); SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);
Use of the word SOME
is rare, but this example shows why it might be
useful. To most people's ears, the English phrase ``a is not equal to any
b'' means ``there is no b which is equal to a,'' but that isn't what is
meant by the SQL syntax. Using <> SOME
instead helps ensure that
everyone understands the true meaning of the query.
ALL
Syntax:
operand comparison_operator ALL (subquery)
The word ALL
, which must follow a comparison operator, means
``return TRUE
if the comparison is TRUE
for ALL
of
the values in the column that the subquery returns.''
For example:
SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);
Suppose that there is a row in table t1
containing (10)
.
The expression is TRUE
if table t2
contains (-5,0,+5)
because 10
is greater than all three values in t2
.
The expression is FALSE
if table t2
contains
(12,6,NULL,-100)
because there is a single value 12
in table t2
that is greater than 10
.
The expression is UNKNOWN
if table t2
contains (0,NULL,1)
.
Finally, if table t2
is empty, the result is TRUE
.
You might think the result should be UNKNOWN
, but
sorry, it's TRUE
. So, rather oddly, the following statement
is TRUE
when table t2
is empty:
SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);
But this statement is UNKNOWN
when table t2
is empty:
SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2);
In addition, the following statement is UNKNOWN
when table t2
is empty:
SELECT * FROM t1 WHERE 1 > ALL (SELECT MAX(s1) FROM t2);
In general, tables with NULL
values and empty tables are
edge cases. When writing subquery code, always consider whether
you have taken those two possibilities into account.
NOT IN
is an alias for <> ALL
. Thus these two statements
are the same:
SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2); SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);
The discussion to this point has been of scalar or column subqueries, that is, subqueries that return a single value or a column of values. A row subquery is a subquery variant that returns a single row and can thus return more than one column value. Here are two examples:
SELECT * FROM t1 WHERE (1,2) = (SELECT column1, column2 FROM t2); SELECT * FROM t1 WHERE ROW(1,2) = (SELECT column1, column2 FROM t2);
The queries here are both TRUE
if table t2
has
a row where column1 = 1
and column2 = 2
.
The expressions (1,2)
and ROW(1,2)
are sometimes called
row constructors. The two are equivalent.
They are legal in other contexts, too. For example, the following two
statements are semantically equivalent (although currently only the second one
can be optimized):
SELECT * FROM t1 WHERE (column1,column2) = (1,1); SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;
The normal use of row constructors, though, is for comparisons with
subqueries that return two or more columns. For example, the following query answers
the request, ``find all rows in table t1
that also exist in table
t2
'':
SELECT column1,column2,column3 FROM t1 WHERE (column1,column2,column3) IN (SELECT column1,column2,column3 FROM t2);
EXISTS
and NOT EXISTS
If a subquery returns any rows at all, then EXISTS subquery
is
TRUE
, and NOT EXISTS subquery
is FALSE
.
For example:
SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);
Traditionally, an EXISTS
subquery starts with SELECT *
, but it
could begin with SELECT 5
or SELECT column1
or anything at
all. MySQL ignores the SELECT
list in such a subquery, so it
doesn't matter.
For the preceding example, if t2
contains any rows, even rows with
nothing but NULL
values, then the EXISTS
condition is
TRUE
. This is actually an unlikely example, since almost always a
[NOT] EXISTS
subquery will contain correlations.
Here are some more realistic examples:
SELECT DISTINCT store_type FROM Stores WHERE EXISTS (SELECT * FROM Cities_Stores WHERE Cities_Stores.store_type = Stores.store_type);
SELECT DISTINCT store_type FROM Stores WHERE NOT EXISTS (SELECT * FROM Cities_Stores WHERE Cities_Stores.store_type = Stores.store_type);
SELECT DISTINCT store_type FROM Stores S1 WHERE NOT EXISTS ( SELECT * FROM Cities WHERE NOT EXISTS ( SELECT * FROM Cities_Stores WHERE Cities_Stores.city = Cities.city AND Cities_Stores.store_type = Stores.store_type));
The last example is a double-nested NOT EXISTS
query. That is, it has a
NOT EXISTS
clause within a NOT EXISTS
clause. Formally, it
answers the question ``does a city exist with a store that is not in
Stores
?'' But it's easier to say that a nested NOT EXISTS
answers
the question ``is x TRUE for all y?''
A correlated subquery is a subquery that contains a reference to a table that also appears in the outer query. For example:
SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2 WHERE t2.column2 = t1.column2);
Notice that the subquery contains a reference to a column
of t1
, even though the subquery's FROM
clause doesn't mention
a table t1
. So, MySQL looks outside the subquery, and finds t1
in the
outer query.
Suppose that table t1
contains a row where column1 = 5
and
column2 = 6
; meanwhile, table t2
contains a row where
column1 = 5
and column2 = 7
. The simple expression
... WHERE column1 = ANY (SELECT column1 FROM t2)
would be
TRUE
, but in this example, the WHERE
clause within the
subquery is FALSE
(because (5,6)
is not equal to (5,7)
),
so the subquery as a whole is FALSE
.
Scoping rule: MySQL evaluates from inside to outside. For example:
SELECT column1 FROM t1 AS x WHERE x.column1 = (SELECT column1 FROM t2 AS x WHERE x.column1 = (SELECT column1 FROM t3 WHERE x.column2 = t3.column1));
In this statement, x.column2
must be a column in table t2
because
SELECT column1 FROM t2 AS x ...
renames t2
. It is not a
column in table t1
because SELECT column1 FROM t1 ...
is an
outer query that is farther out.
For subqueries in HAVING
or ORDER BY
clauses, MySQL also
looks for column names in the outer select list.
For certain cases, a correlated subquery is optimized. For example:
val IN (SELECT key_val FROM tbl_name WHERE correlated_condition)
Otherwise, they are inefficient and likely to be slow. Rewriting the query as a join might improve performance.
FROM
clause
Subqueries are legal in a SELECT
statement's FROM
clause.
The syntax that you'll actually see is:
SELECT ... FROM (subquery) AS name ...
The AS name
clause is mandatory, because every table in a
FROM
clause must have a name. Any columns in the subquery
select list must have unique names. You can find this syntax described
elsewhere in this manual, where the term used is ``derived tables.''
For illustration, assume that you have this table:
CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
Here's how to use a subquery in the FROM
clause, using
the example table:
INSERT INTO t1 VALUES (1,'1',1.0); INSERT INTO t1 VALUES (2,'2',2.0); SELECT sb1,sb2,sb3 FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb WHERE sb1 > 1;
Result: 2, '2', 4.0
.
Here's another example: Suppose that you want to know the average of a set of sums for a grouped table. This won't work:
SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;
But this query will provide the desired information:
SELECT AVG(sum_column1) FROM (SELECT SUM(column1) AS sum_column1 FROM t1 GROUP BY column1) AS t1;
Notice that the column name used within the subquery
(sum_column1)
is recognized in the outer query.
Subqueries in the FROM
clause can return a scalar, column, row, or
table. At the moment, subqueries in the FROM
clause cannot be
correlated subqueries.
Subqueries in the FROM
clause will be executed even for the
EXPLAIN
statement (that is, derived temporary tables will be built).
This occurs because upper level queries need information about all tables
during optimization phase.
There are some new error returns that apply only to subqueries. This section groups them together because reviewing them will help remind you of some points.
ERROR 1235 (ER_NOT_SUPPORTED_YET) SQLSTATE = 42000 Message = "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'"This means that statements of the following form will not work, although this happens only in some early versions, such as MySQL 4.1.1:
SELECT * FROM t1 WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1)
ERROR 1241 (ER_OPERAND_COL) SQLSTATE = 21000 Message = "Operand should contain 1 column(s)"This error will occur in cases like this:
SELECT (SELECT column1, column2 FROM t2) FROM t1;It's okay to use a subquery that returns multiple columns, if the purpose is comparison. See section 13.1.8.5 Row Subqueries. But in other contexts, the subquery must be a scalar operand.
ERROR 1242 (ER_SUBSELECT_NO_1_ROW) SQLSTATE = 21000 Message = "Subquery returns more than 1 row"This error will occur for statements such as the following one, but only when there is more than one row in
t2
:
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);That means this error might occur in code that had been working for years, because somebody happened to make a change that affected the number of rows that the subquery can return. Remember that if the object is to find any number of rows, not just one, then the correct statement would look like this:
SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2);
Error 1093 (ER_UPDATE_TABLE_USED) SQLSTATE = HY000 Message = "You can't specify target table 'x' for update in FROM clause"This error will occur in cases like this:
UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);It's okay to use a subquery for assignment within an
UPDATE
statement, since subqueries are legal in UPDATE
and DELETE
statements as well as in SELECT
statements.
However, you cannot use the same table, in this case table t1
, for
both the subquery's FROM
clause and the update target.
For transactional storage engines, a failure of a subquery causes the entire statement to fail. For non-transactional storage engines, data modifications made before the error was encountered are preserved.
Development is ongoing, so no optimization tip is reliable for the long term. Some interesting tricks that you might want to play with are:
SELECT * FROM t1 WHERE t1.column1 IN (SELECT column1 FROM t2 ORDER BY column1); SELECT * FROM t1 WHERE t1.column1 IN (SELECT DISTINCT column1 FROM t2); SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 LIMIT 1);
SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN ( SELECT column1 FROM t2);Instead of this query:
SELECT DISTINCT t1.column1 FROM t1, t2 WHERE t1.column1 = t2.column1;
SELECT * FROM t1 WHERE s1 IN (SELECT s1 FROM t1 UNION ALL SELECT s1 FROM t2);Instead of this query:
SELECT * FROM t1 WHERE s1 IN (SELECT s1 FROM t1) OR s1 IN (SELECT s1 FROM t2);For another example, use this query:
SELECT (SELECT column1 + 5 FROM t1) FROM t2;Instead of this query:
SELECT (SELECT column1 FROM t1) + 5 FROM t2;
SELECT * FROM t1 WHERE (column1,column2) IN (SELECT column1,column2 FROM t2);Instead of this query:
SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1 AND t2.column2=t1.column2);
NOT (a = ANY (...))
rather than a <> ALL (...)
.
x = ANY (table containing (1,2))
rather than
x=1 OR x=2
.
= ANY
rather than EXISTS
.
These tricks might cause programs to go faster or slower. Using MySQL
facilities like the BENCHMARK()
function, you can get an idea about
what helps in your own situation. Don't worry too much about transforming
to joins except for compatibility with older versions of MySQL before 4.1
that do not support subqueries.
Some optimizations that MySQL itself makes are:
EXPLAIN
to make sure that a given subquery really is non-correlated.
IN
/ALL
/ANY
/SOME
subqueries
in an attempt to take advantage of the possibility that the select-list
columns in the subquery are indexed.
EXPLAIN
will describe as a
special join type:
... IN (SELECT indexed_column FROM single_table ...)
MIN()
or MAX()
, unless NULL
values or empty sets are involved:
value {ALL|ANY|SOME} {> | < | >= | <=} (non-correlated subquery)For example, this
WHERE
clause:
WHERE 5 > ALL (SELECT x FROM t)might be treated by the optimizer like this:
WHERE 5 > (SELECT MAX(x) FROM t)
There is a chapter titled ``How MySQL Transforms Subqueries'' in the MySQL Internals Manual. You can obtain this document by downloading the MySQL source package and looking for a file named `internals.texi' in the `Docs' directory.
Before MySQL 4.1, only nested queries of the form
INSERT ... SELECT ...
and REPLACE ... SELECT ...
are supported.
The IN()
construct can be used in other contexts to test membership in
a set of values.
It is often possible to rewrite a query without a subquery:
SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);
This can be rewritten as:
SELECT DISTINCT t1.* FROM t1, t2 WHERE t1.id=t2.id;
The queries:
SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2); SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);
Can be rewritten as:
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL;
A LEFT [OUTER] JOIN
can be faster than an equivalent subquery
because the server might be able to optimize it better--a fact that is
not specific to MySQL Server alone.
Prior to SQL-92, outer joins did not exist, so subqueries were the only way
to do certain things in those bygone days. Today, MySQL Server and many
other modern database systems offer a whole range of outer join types.
For more complicated subqueries, you can often create temporary tables
to hold the subquery. In some cases, however, this option will not
work. The most frequently encountered of these cases arises with
DELETE
statements, for which standard SQL does not support joins
(except in subqueries). For this situation, there are three options
available:
DELETE
statements.
SELECT
query to obtain the primary keys
for the records to be deleted, and then use these values to construct
the DELETE
statement (DELETE FROM ... WHERE key_col IN (key1,
key2, ...)
).
DELETE
statements automatically, using the MySQL
extension CONCAT()
(in lieu of the standard ||
operator).
For example:
SELECT CONCAT('DELETE FROM tab1 WHERE pkid = ', "'", tab1.pkid, "'", ';') FROM tab1, tab2 WHERE tab1.col1 = tab2.col2;You can place this query in a script file, use the file as input to one instance of the
mysql
program, and use the program output
as input to a second instance of mysql
:
shell> mysql --skip-column-names mydb < myscript.sql | mysql mydb
MySQL Server 4.0 supports multiple-table DELETE
statements that can be used to
efficiently delete rows based on information from one table or even
from many tables at the same time.
Multiple-table UPDATE
statements are also supported as of MySQL 4.0.
TRUNCATE
SyntaxTRUNCATE TABLE tbl_name
TRUNCATE TABLE
empties a table completely.
Logically, this is equivalent to a DELETE
statement that deletes all
rows, but there are practical differences under some circumstances.
For InnoDB
, TRUNCATE TABLE
is mapped to
DELETE
, so there is no difference. For other storage engines,
TRUNCATE TABLE
differs from DELETE FROM ...
in the following ways from MySQL 4.0 and up:
TRUNCATE
TABLE
, even if the data or index files have become corrupted.
AUTO_INCREMENT
value, but starts counting from the beginning. This is true even for
MyISAM
, which normally does not reuse sequence values.
In MySQL 3.23, TRUNCATE TABLE
is mapped to
COMMIT; DELETE FROM tbl_name
, so it behaves like DELETE
.
See section 13.1.1 DELETE
Syntax.
TRUNCATE TABLE
is an Oracle SQL extension.
This statement was added in MySQL 3.23.28, although from 3.23.28
to 3.23.32, the keyword TABLE
must be omitted.
UPDATE
SyntaxSingle-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]
Multiple-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...] SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]
The UPDATE
statement updates columns in existing table rows with
new values. The SET
clause indicates which columns to modify
and the values they should be given. The WHERE
clause, if given,
specifies which rows should be updated. Otherwise, all rows are updated. If
the ORDER BY
clause is specified, the rows will be updated in the
order that is specified. The LIMIT
clause places a limit on the
number of rows that can be updated.
The UPDATE
statement supports the following modifiers:
LOW_PRIORITY
keyword, execution of the
UPDATE
is delayed until no other clients are reading from the table.
IGNORE
keyword, the update statement will not
abort even if errors occur during the update. Rows for which duplicate-key
conflicts occur are not updated. Rows for which columns are updated to
values that would cause data conversion errors are updated to the closet
valid values instead.
If you access a column from tbl_name in an expression,
UPDATE
uses the current value of the column. For example, the
following statement sets the age
column to one more than its
current value:
mysql> UPDATE persondata SET age=age+1;
UPDATE
assignments are evaluated from left to right. For example, the
following statement doubles the age
column, then increments it:
mysql> UPDATE persondata SET age=age*2, age=age+1;
If you set a column to the value it currently has, MySQL notices this and doesn't update it.
If you update a column that has been declared NOT NULL
by
setting to NULL
, the column is set to the default value appropriate
for the column type and the warning count is incremented. The default
value is 0
for numeric types, the empty string (''
)
for string types, and the ``zero'' value for date and time types.
UPDATE
returns the number of rows that were actually changed.
In MySQL 3.22 or later, the mysql_info()
C API function
returns the number of rows that were matched and updated and the number of
warnings that occurred during the UPDATE
.
Starting from MySQL 3.23, you can use LIMIT row_count
to
restrict the scope of the UPDATE
. A LIMIT
clause works as
follows:
LIMIT
is a rows-affected restriction.
The statement stops as soon as it has changed row_count rows that
satisfy the WHERE
clause.
LIMIT
is a rows-matched restriction. The statement
stops as soon as it has found row_count rows that satisfy the
WHERE
clause, whether or not they actually were changed.
If an UPDATE
statement includes an ORDER BY
clause, the rows
are updated in the order specified by the clause.
ORDER BY
can be used from MySQL 4.0.0.
Starting with MySQL 4.0.4, you can also perform UPDATE
operations that cover multiple tables:
UPDATE items,month SET items.price=month.price WHERE items.id=month.id;
The example shows an inner join using the comma operator, but
multiple-table UPDATE
statements can use any type of
join allowed in SELECT
statements, such as LEFT JOIN
.
Note: You cannot use ORDER BY
or LIMIT
with multiple-table
UPDATE
.
Before MySQL 4.0.18, you need the UPDATE
privilege for all
tables used in a multiple-table UPDATE
, even if they were not
updated. As of MySQL 4.0.18, you need only the SELECT
privilege for
any columns that are read but not modified.
If you use a multiple-table UPDATE
statement involving
InnoDB
tables for which there are foreign key constraints,
the MySQL optimizer might process tables in an order that differs from
that of their parent/child relationship. In this case, the statement will
fail and roll back. Instead, update a single table and rely on the
ON UPDATE
capabilities that InnoDB
provides to cause the
other tables to be modified accordingly.
Currently, you cannot update a table and select from the same table in a subquery.
ALTER DATABASE
SyntaxALTER {DATABASE | SCHEMA} [db_name] alter_specification [, alter_specification] ... alter_specification: [DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
ALTER DATABASE
allows you to change the overall characteristics of a
database. These characteristics are stored in the `db.opt' file in the
database directory.
To use ALTER DATABASE
, you need the ALTER
privilege on the
database.
The CHARACTER SET
clause changes the default database character set.
The COLLATE
clause changes the default database collation.
Character set and collation names are discussed in
section 10 Character Set Support.
ALTER DATABASE
was added in MySQL 4.1.1.
Beginning with MySQL 4.1.8, the database name can be omitted.
The statement applies to the default database in this case.
ALTER SCHEMA
can be used as of MySQL 5.0.2.
ALTER TABLE
SyntaxALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] ... alter_specification: ADD [COLUMN] column_definition [FIRST | AFTER col_name ] | ADD [COLUMN] (column_definition,...) | ADD INDEX [index_name] [index_type] (index_col_name,...) | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) | ADD [CONSTRAINT [symbol]] UNIQUE [index_name] [index_type] (index_col_name,...) | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...) | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) [reference_definition] | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name] | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY | DROP INDEX index_name | DROP FOREIGN KEY fk_symbol | DISABLE KEYS | ENABLE KEYS | RENAME [TO] new_tbl_name | ORDER BY col_name | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name] | DISCARD TABLESPACE | IMPORT TABLESPACE | 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.
The syntax for many of the allowable alterations is similar to clauses of the
CREATE TABLE
statement.
See section 13.2.6 CREATE TABLE
Syntax.
Some operations may result in warnings if attempted on a table for which
the storage engine does not support the operation. In MySQL 4.1 and up,
these warnings can be displayed with SHOW WARNINGS
.
See section 13.5.4.20 SHOW WARNINGS
Syntax.
If you use ALTER TABLE
to change a column specification but
DESCRIBE tbl_name
indicates that your column was not changed, it is
possible that MySQL ignored your modification for one of the reasons
described in section 13.2.6.1 Silent Column Specification Changes. For example, if you try to change
a VARCHAR
column to CHAR
, MySQL will still use
VARCHAR
if the table contains other variable-length columns.
ALTER TABLE
works by making a temporary copy of the original table.
The alteration is performed on the copy, then the original table is deleted
and the new one is renamed. 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, then are automatically
redirected to the new table without any failed updates.
Note that if you use any other option to ALTER TABLE
than
RENAME
, MySQL always creates a temporary table, even if the data
wouldn't strictly need to be copied (such as when you change the name of a
column). We plan to fix this in the future, but because ALTER TABLE
is not a statement that is normally used frequently, this isn't high on our
TODO list. For MyISAM
tables, you can speed up the index re-creation
operation (which is the slowest part of the alteration process) by setting
the myisam_sort_buffer_size
system variable to a high value.
ALTER TABLE
, you need ALTER
, INSERT
,
and CREATE
privileges for the table.
IGNORE
is a MySQL extension to standard SQL.
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
duplicate-key errors occur.
If IGNORE
is specified, then for rows with duplicates on a unique
key, only the first row is used. The others are deleted.
ADD
, ALTER
, DROP
, and
CHANGE
clauses in a single ALTER TABLE
statement. This is a
MySQL extension to standard SQL, which allows only one of each clause
per ALTER TABLE
statement. For example, to drop multiple columns in a single statement:
mysql> ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
CHANGE col_name
, DROP col_name
, and DROP
INDEX
are MySQL extensions to standard SQL.
MODIFY
is an Oracle extension to ALTER TABLE
.
COLUMN
is purely optional and can be omitted.
ALTER TABLE tbl_name RENAME TO new_tbl_name
without any other
options, MySQL simply renames any files that correspond to the table
tbl_name. There is no need to create a temporary table.
(You can also use the RENAME TABLE
statement to rename tables.
See section 13.2.12 RENAME TABLE
Syntax.)
ADD
and
CHANGE
as for CREATE TABLE
. Note that this syntax includes
the column name, not just the column type.
See section 13.2.6 CREATE TABLE
Syntax.
CHANGE old_col_name column_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 an old and new column name, even if they are the same.
For example:
mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;However, as of MySQL 3.22.16a, you can also use
MODIFY
to change a column's type without renaming it:
mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
CHANGE
or MODIFY
to shorten a column for which
an index exists on part of the column (for example, if you have an index
on the first 10 characters of a VARCHAR
column), you cannot make
the column shorter than the number of characters that are indexed.
CHANGE
or MODIFY
, MySQL
tries to convert existing column values to the new type as well as possible.
FIRST
or
AFTER col_name
to add a column at a specific position
within a table row. The default is to add the column last.
From MySQL 4.0.1 on, you can also use FIRST
and
AFTER
in CHANGE
or MODIFY
operations.
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, as described in
section 13.2.6 CREATE TABLE
Syntax.
DROP INDEX
removes an index. This is a MySQL extension to
standard SQL. See section 13.2.9 DROP INDEX
Syntax.
DROP TABLE
instead.
DROP PRIMARY KEY
drops the primary index. (Prior to MySQL 4.1.2,
if no primary index exists, DROP PRIMARY KEY
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.)
If you add a UNIQUE INDEX
or PRIMARY KEY
to a table, it
is stored before any non-unique index so that MySQL can detect
duplicate keys as early as possible.
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. 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 might be able to get higher performance.
In some cases, it might make sorting easier for MySQL if the table is in
order by the column that you want to order it by later.
ALTER TABLE
on a MyISAM
table, all non-unique
indexes are created in a separate batch (as for REPAIR TABLE
).
This should make ALTER TABLE
much faster when you have many indexes.
As of MySQL 4.0, this feature can be activated explicitly. ALTER
TABLE ... DISABLE KEYS
tells MySQL to stop updating non-unique indexes for a
MyISAM
table. ALTER TABLE ... ENABLE KEYS
then should be used
to re-create missing indexes. MySQL does this with a special algorithm that
is much faster than inserting keys one by one, so disabling keys before
performing bulk insert operations should give a considerable speedup. Using
ALTER TABLE ... DISABLE KEYS
will require the INDEX
privilege
in addition to the privileges mentioned earlier.
FOREIGN KEY
and REFERENCES
clauses are supported by the
InnoDB
storage engine, which implements
ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES ... (...)
.
See section 15.7.4 FOREIGN KEY
Constraints.
For other storage engines, the clauses are parsed but ignored.
The CHECK
clause is parsed but ignored by all storage engines.
See section 13.2.6 CREATE TABLE
Syntax.
The reason for accepting but ignoring syntax clauses is for compatibility,
to make it easier to port code from other SQL servers, and to run applications
that create tables with references.
See section 1.5.5 MySQL Differences from Standard SQL.
InnoDB
supports the use of ALTER
TABLE
to drop foreign keys:
ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;For more information, see section 15.7.4
FOREIGN KEY
Constraints.
ALTER TABLE
ignores the DATA DIRECTORY
and INDEX
DIRECTORY
table options.
CHAR
, VARCHAR
,
TEXT
) to a new character set, use a statement like this:
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;This is useful, for example, after upgrading from MySQL 4.0.x to 4.1.x. See section 10.10 Upgrading Character Sets from MySQL 4.0. Warning: The preceding operation will convert column values between the character sets. This is not what you want if you have a column in one character set (like
latin1
) but the stored values actually use
some other, incompatible character set (like utf8
). In this case,
you have to do the following for each such column:
ALTER TABLE t1 CHANGE c1 c1 BLOB; ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;The reason this works is that there is no conversion when you convert to or from
BLOB
columns.
To change only the default character set for a table, use this
statement:
ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;The word
DEFAULT
is optional.
The default character set is the character set that is used if
you don't specify the character set for a new column you add to a table
(for example, with ALTER TABLE ... ADD column
).
Warning: From MySQL 4.1.2 and up, ALTER TABLE ... DEFAULT
CHARACTER SET
and ALTER TABLE ... CHARACTER SET
are equivalent and
change only the default table character set. In MySQL 4.1 releases before
4.1.2, ALTER TABLE ... DEFAULT CHARACTER SET
changes the default
character set, but ALTER TABLE ... CHARACTER SET
(without
DEFAULT
) changes the default character set and also converts
all columns to the new character set.
InnoDB
table that is created with its own tablespace in an
`.ibd' file, that file can be discarded and imported. To discard the
`.ibd' file, use this statement:
ALTER TABLE tbl_name DISCARD TABLESPACE;This deletes the current `.ibd' file, so be sure that you have a backup first. Attempting to access the table while the tablespace file is discarded results in an error. To import the backup `.ibd' file back into the table, copy it into the database directory, then issue this statement:
ALTER TABLE tbl_name IMPORT TABLESPACE;See section 15.7.6 Using Per-Table Tablespaces.
mysql_info()
C API function, 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.
See section 21.2.3.31 mysql_info()
.
Here are some examples that show uses of ALTER TABLE
.
Begin with a table t1
that is created as shown here:
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 indexes on column d
and on column a
:
mysql> ALTER TABLE t2 ADD INDEX (d), ADD INDEX (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 PRIMARY KEY (c);
Note that we indexed c
(as a PRIMARY KEY
), because
AUTO_INCREMENT
columns must be indexed, and also that we declare
c
as NOT NULL
, because primary key columns cannot be
NULL
.
When you add an AUTO_INCREMENT
column, column values are filled in
with sequence numbers for you automatically. For MyISAM
tables,
you can set the first
sequence number by executing SET INSERT_ID=value
before
ALTER TABLE
or by using the AUTO_INCREMENT=value
table option.
See section 13.5.3 SET
Syntax.
From MySQL 5.0.3, you can use the ALTER TABLE ...
AUTO_INCREMENT=value
table option for InnoDB
tables
to set the sequence number for new rows if the value is greater than the
maximum value in the AUTO_INCREMENT
column. If the value is less
than the maximum column value, no error message is given and the current
sequence value is not changed.
With MyISAM
tables, if you don't change the AUTO_INCREMENT
column, the sequence number will not be affected. If you drop an
AUTO_INCREMENT
column and then add another AUTO_INCREMENT
column, the numbers are resequenced beginning with 1.
See section A.7.1 Problems with ALTER TABLE
.
ALTER VIEW
SyntaxALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
This statement changes the definition of an existing view.
The syntax is similar to that for CREATE VIEW
.
See section 13.2.7 CREATE VIEW
Syntax.
This statement was added in MySQL 5.0.1.
CREATE DATABASE
SyntaxCREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification [, create_specification] ...] create_specification: [DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
CREATE DATABASE
creates a database with the given name.
To use CREATE DATABASE
, you need the CREATE
privilege on the
database.
Rules for
allowable database names are given in section 9.2 Database, Table, Index, Column, and Alias Names. An error occurs if
the database already exists and you didn't specify IF NOT EXISTS
.
As of MySQL 4.1.1, create_specification
options can be given to
specify database characteristics. Database characteristics are stored in
the `db.opt' file in the database directory. The CHARACTER SET
clause specifies the default database character set. The COLLATE
clause specifies the default database collation. Character set and
collation names are discussed in section 10 Character Set Support.
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 (and the `db.opt'
file, for MySQL 4.1.1 and up).
CREATE SCHEMA
can be used as of MySQL 5.0.2.
You can also use the mysqladmin
program to create databases.
See section 8.4 mysqladmin
, Administering a MySQL Server.
CREATE INDEX
SyntaxCREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON tbl_name (index_col_name,...) index_col_name: col_name [(length)] [ASC | DESC]
In MySQL 3.22 or later, CREATE INDEX
is mapped to an
ALTER TABLE
statement to create indexes.
See section 13.2.2 ALTER TABLE
Syntax.
The CREATE INDEX
statement doesn't do anything prior
to MySQL 3.22.
Normally, you create all indexes on a table at the time the table itself
is created with CREATE TABLE
.
See section 13.2.6 CREATE TABLE
Syntax.
CREATE INDEX
allows you to add indexes to existing tables.
A column list of the form (col1,col2,...)
creates a multiple-column
index. Index values are formed by concatenating the values of the given
columns.
For CHAR
and VARCHAR
columns, indexes can be created that
use only part of a column, using col_name(length)
syntax to index
a prefix consisting of the first length characters of each column
value. BLOB
and TEXT
columns also can be indexed, but a prefix
length must be given.
The statement shown here creates an index using the first 10 characters
of the name
column:
CREATE INDEX part_of_name ON customer (name(10));
Because most names usually differ in the first 10 characters, this index should
not be much slower than an index created from the entire name
column.
Also, using partial columns for indexes can make the index file much smaller,
which could save a lot of disk space and might also speed up INSERT
operations!
Prefixes can be up to 255 bytes long (or 1000 bytes for MyISAM
and InnoDB
tables as of MySQL 4.1.2). Note that prefix limits
are measured in bytes, whereas the prefix length in CREATE INDEX
statements is interpreted as number of characters. Take this into account
when specifying a prefix length for a column that uses a multi-byte
character set.
You can add an index on a column that can have NULL
values only if you are using MySQL 3.23.2 or newer and are using the
MyISAM
, InnoDB
, or BDB
table type. You can only add an
index on a BLOB
or TEXT
column if you are using
MySQL 3.23.2 or newer and are using the MyISAM
or BDB
table type, or MySQL 4.0.14 or newer and the InnoDB
table type.
An index_col_name specification can end with ASC
or DESC
.
These keywords are allowed for future extensions for specifying ascending
or descending index value storage. Currently they are parsed but ignored;
index values are always stored in ascending order.
From MySQL 4.1.0 on, some storage engines allow you to specify an index
type when creating an index. The syntax for the index_type
specifier is USING type_name
. The allowable type_name
values supported by different storage engines are shown in the following
table. Where multiple index types are listed, the first one is the
default when no index_type specifier is given.
Storage Engine | Allowable Index Types |
MyISAM | BTREE
|
InnoDB | BTREE
|
MEMORY/HEAP | HASH , BTREE
|
Example:
CREATE TABLE lookup (id INT) ENGINE = MEMORY; CREATE INDEX id_index USING BTREE ON lookup (id);
TYPE type_name
can be used as a synonym for USING type_name
to specify an index type. However, USING
is the preferred form.
Also, the index name that precedes the index type in the index
specification syntax is not optional with TYPE
. This is because,
unlike USING
, TYPE
is not a reserved word and thus is
interpreted as an index name.
If you specify an index type that is not legal for a storage engine, but there is another index type available that the engine can use without affecting query results, the engine will use the available type.
For more information about how MySQL uses indexes, see section 7.4.5 How MySQL Uses Indexes.
FULLTEXT
indexes can index only CHAR
, VARCHAR
, and
TEXT
columns, and only in MyISAM
tables. FULLTEXT
indexes
are available in MySQL 3.23.23 or later.
section 12.6 Full-Text Search Functions.
SPATIAL
indexes can index only spatial columns,
and only in MyISAM
tables. SPATIAL
indexes
are available in MySQL 4.1 or later. Spatial column types are described in
section 18 Spatial Extensions in MySQL.
CREATE TABLE
SyntaxCREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement]
Or:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(] LIKE old_tbl_name [)]; create_definition: column_definition | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) | KEY [index_name] [index_type] (index_col_name,...) | INDEX [index_name] [index_type] (index_col_name,...) | [CONSTRAINT [symbol]] UNIQUE [INDEX] [index_name] [index_type] (index_col_name,...) | [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,...) | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) [reference_definition] | CHECK (expr) column_definition: col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'string'] [reference_definition] type: TINYINT[(length)] [UNSIGNED] [ZEROFILL] | SMALLINT[(length)] [UNSIGNED] [ZEROFILL] | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL] | INT[(length)] [UNSIGNED] [ZEROFILL] | INTEGER[(length)] [UNSIGNED] [ZEROFILL] | BIGINT[(length)] [UNSIGNED] [ZEROFILL] | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] | DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL] | NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL] | DATE | TIME | TIMESTAMP | DATETIME | CHAR(length) [BINARY | ASCII | UNICODE] | VARCHAR(length) [BINARY] | TINYBLOB | BLOB | MEDIUMBLOB | LONGBLOB | TINYTEXT | TEXT | MEDIUMTEXT | LONGTEXT | ENUM(value1,value2,value3,...) | SET(value1,value2,value3,...) | spatial_type index_col_name: col_name [(length)] [ASC | DESC] reference_definition: REFERENCES tbl_name [(index_col_name,...)] [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT table_options: table_option [table_option] ... table_option: {ENGINE|TYPE} = {BDB|HEAP|ISAM|InnoDB|MERGE|MRG_MYISAM|MYISAM} | AUTO_INCREMENT = value | AVG_ROW_LENGTH = value | CHECKSUM = {0 | 1} | COMMENT = 'string' | MAX_ROWS = value | MIN_ROWS = value | PACK_KEYS = {0 | 1 | DEFAULT} | PASSWORD = 'string' | DELAY_KEY_WRITE = {0 | 1} | ROW_FORMAT = {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} | RAID_TYPE = { 1 | STRIPED | RAID0 } RAID_CHUNKS = value RAID_CHUNKSIZE = value | UNION = (tbl_name[,tbl_name]...) | INSERT_METHOD = { NO | FIRST | LAST } | DATA DIRECTORY = 'absolute path to directory' | INDEX DIRECTORY = 'absolute path to directory' | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name] select_statement: [IGNORE | REPLACE] [AS] SELECT ... (Some legal select statement)
CREATE TABLE
creates a table with the given name.
You must have the CREATE
privilege for the table.
Rules for allowable table names are given in section 9.2 Database, Table, Index, Column, and Alias Names. By default, the table is created in the current database. An error occurs if the table already exists, if there is no current database, or if the database does not exist.
In MySQL 3.22 or later, the table name can be specified as
db_name.tbl_name to create the table in a specific database.
This works whether or not there is a current database.
If you use quoted identifiers, quote the database and table names
separately. For example, `mydb`.`mytbl`
is legal, but
`mydb.mytbl`
is not.
From MySQL 3.23 on, you can use the TEMPORARY
keyword when
creating a table. A TEMPORARY
table is visible only to the
current connection, and is dropped automatically when the
connection is closed. This means that two different
connections can use the same temporary table name without conflicting
with each other or with an existing non-TEMPORARY
table of the same
name. (The existing table is hidden until the temporary table is dropped.)
From MySQL 4.0.2 on, you must have the CREATE TEMPORARY TABLES
privilege to be able to create temporary tables.
In MySQL 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 existing table has a
structure identical to that indicated by the CREATE TABLE
statement.
MySQL represents each table by an `.frm' table format
(definition) file in the database directory. The storage engine for the table
might create other files as well.
In the case of MyISAM
tables, the storage engine creates three files
for a table named tbl_name:
File | Purpose |
tbl_name.frm | Table format (definition) file |
tbl_name.MYD | Data file |
tbl_name.MYI | Index file |
The files created by each storage engine to represent tables are described in section 14 MySQL Storage Engines and Table Types.
For general information on the properties of the various column types, see section 11 Column Types. For information about spatial column types, see section 18 Spatial Extensions in MySQL.
NULL
nor NOT NULL
is specified, the column
is treated as though NULL
had been specified.
AUTO_INCREMENT
.
When you insert a value of NULL
(recommended) or 0
into an
indexed
AUTO_INCREMENT
column, the column is set to the next sequence value.
Typically this is value+1
, where
value is the largest value for the column currently in the table.
AUTO_INCREMENT
sequences begin with 1
.
See section 21.2.3.33 mysql_insert_id()
.
As of MySQL 4.1.1, specifying the NO_AUTO_VALUE_ON_ZERO
flag for the
--sql-mode
server option or the sql_mode
system variable allows
you to store 0
in AUTO_INCREMENT
columns as 0
without
generating a new sequence value.
See section 5.2.1 mysqld
Command-Line Options.
Note: There can be only one AUTO_INCREMENT
column per
table, it must be indexed, and it cannot have a DEFAULT
value.
As of MySQL 3.23, an AUTO_INCREMENT
column will work properly
only if it contains only 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 you don't accidentally
get an AUTO_INCREMENT
column that contains 0
.
For MyISAM
and BDB
tables, you can specify an
AUTO_INCREMENT
secondary column in a multiple-column key.
See section 3.6.9 Using AUTO_INCREMENT
.
To make MySQL compatible with some ODBC applications, you can find the
AUTO_INCREMENT
value for the last inserted row with the following query:
SELECT * FROM tbl_name WHERE auto_col IS NULL
CHARACTER
SET
attribute to specify the character set and, optionally, a collation
for the column. For details, see section 10 Character Set Support.
CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);Also as of 4.1, MySQL interprets length specifications in character column definitions in characters. (Earlier versions interpret them in bytes.)
NULL
values are handled differently for TIMESTAMP
columns
than for other column types. Before MySQL 4.1.6, 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 can be assigned NULL
values (which is true), even though TIMESTAMP
never actually will
contain a NULL
value. You can see this when you use DESCRIBE
tbl_name
to get a description of your table.
Note that setting a TIMESTAMP
column to 0
is not the same
as setting it to NULL
, because 0
is a valid TIMESTAMP
value.
DEFAULT
clause specifies a default value for a column.
With one exception, the default value must be a constant; it cannot be
a function or an expression. 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
. The exception is that you can specify
CURRENT_TIMESTAMP
as the default for a TIMESTAMP
column as
of MySQL 4.1.2.
See section 11.3.1.2 TIMESTAMP
Properties as of MySQL 4.1.
Prior to MySQL 5.0.2, if a column definition includes no explicit
DEFAULT
value, MySQL determines the default value as follows:
If the column can take NULL
as a value, the column is defined with
an explicit DEFAULT NULL
clause.
If the column cannot take NULL
as the value, MySQL defines the
column with an explicit DEFAULT
clause, using the implicit default
value for the column data type. Implicit defaults are defined as follows:
AUTO_INCREMENT
attribute, the default is 0
. For an AUTO_INCREMENT
column, the
default value is the next value in the sequence.
TIMESTAMP
, the default is the
appropriate ``zero'' value for the type. For the first TIMESTAMP
column in a table, the default value is the current date and time.
See section 11.3 Date and Time Types.
ENUM
, the default value is the empty
string. For ENUM
, the default is the first enumeration value.
BLOB
and TEXT
columns cannot be assigned a default value.
As of MySQL 5.0.2, if a column definition includes no explicit
DEFAULT
value, MySQL determines the default value as follows:
If the column can take NULL
as a value, the column is defined with
an explicit DEFAULT NULL
clause. This is the same as before 5.0.2.
If the column cannot take NOT NULL
as the value, MySQL defines
the column with no explicit DEFAULT
clause. For data entry, if an
INSERT
or REPLACE
statement includes no value for the column,
MySQL handles the column according to the SQL mode in effect at the time:
SHOW CREATE TABLE
statement to
see which columns have an explicit DEFAULT
clause.
COMMENT
option.
The comment is displayed by the
SHOW CREATE TABLE
and SHOW FULL COLUMNS
statements.
This option is operational as of MySQL 4.1.
(It is allowed but ignored in earlier versions.)
SERIAL
can be used as an alias
for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
. This is a
compatibility feature.
KEY
is normally a synonym for INDEX
. From MySQL 4.1, the key
attribute PRIMARY KEY
can also be specified as just KEY
when
given in a column definition. This was implemented for compatibility with
other database systems.
UNIQUE
index is one in which all values in the index
must be distinct. An error occurs if you try to add a new row with a key
that matches an existing row. The exception to this is that if a column
in the index is allowed to contain NULL
values, it can contain
multiple NULL
values. This exception does not apply to BDB
tables, for which an indexed column allows only a single NULL
.
PRIMARY KEY
is a unique KEY
where all key columns must be
defined as NOT NULL
. If they are not explicitly declared as NOT
NULL
, MySQL will declare them so implicitly (and silently). A table can
have only one PRIMARY KEY
. If you don't have a PRIMARY KEY
and an application asks for the PRIMARY KEY
in your tables, MySQL
returns the first UNIQUE
index that has no NULL
columns as
the PRIMARY KEY
.
PRIMARY KEY
is placed first, followed
by all UNIQUE
indexes, and then the non-unique indexes. This helps the
MySQL optimizer to prioritize which index to use and also more quickly
to detect duplicated UNIQUE
keys.
PRIMARY KEY
can be a multiple-column index. However, you cannot
create a multiple-column index using the PRIMARY KEY
key attribute in a
column specification. Doing so will mark only that single column as primary.
You must use a separate PRIMARY KEY(index_col_name, ...)
clause.
PRIMARY KEY
or UNIQUE
index consists of only one column
that has an integer type, you can also refer to the column as _rowid
in SELECT
statements (new in MySQL 3.23.11).
PRIMARY KEY
is PRIMARY
. For other
indexes, if you don't assign a name, the index is assigned the same name as
the first indexed column, with an optional suffix (_2
, _3
,
...
) to make it unique. You can see index names for a table using
SHOW INDEX FROM tbl_name
.
See section 13.5.4.11 SHOW INDEX
Syntax.
USING type_name
.
Example:
CREATE TABLE lookup (id INT, INDEX USING BTREE (id)) ENGINE = MEMORY;For details about
USING
, see
section 13.2.5 CREATE INDEX
Syntax.
For more information about how MySQL uses indexes, see
section 7.4.5 How MySQL Uses Indexes.
MyISAM
, InnoDB
, BDB
, and (as of MySQL 4.0.2)
MEMORY
storage engines support indexes on columns that can have
NULL
values. In other cases, you must declare indexed columns
as NOT NULL
or an error results.
col_name(length)
syntax in an index specification, you can create
an index that uses only the first length characters of a CHAR
or VARCHAR
column. Indexing only a prefix of column values like this
can make the index file much smaller.
See section 7.4.3 Column Indexes.
The MyISAM
and (as of MySQL 4.0.14) InnoDB
storage engines also
support indexing on BLOB
and TEXT
columns. When indexing
a BLOB
or TEXT
column, you must specify a prefix
length for the index. For example:
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));Prefixes can be up to 255 bytes long (or 1000 bytes for
MyISAM
and InnoDB
tables as of MySQL 4.1.2). Note that prefix limits
are measured in bytes, whereas the prefix length in CREATE TABLE
statements is interpreted as number of characters. Take this into account
when specifying a prefix length for a column that uses a multi-byte
character set.
ASC
or DESC
.
These keywords are allowed for future extensions for specifying ascending
or descending index value storage. Currently they are parsed but ignored;
index values are always stored in ascending order.
ORDER BY
or GROUP BY
with a TEXT
or
BLOB
column, the server sorts values using only the initial number of
bytes indicated by the max_sort_length
system variable.
See section 11.4.3 The BLOB
and TEXT
Types.
FULLTEXT
indexes. They are used for full-text search. Only the
MyISAM
table type supports FULLTEXT
indexes. They can be created
only from CHAR
, VARCHAR
, and TEXT
columns.
Indexing always happens over the entire column; partial indexing is not
supported and any prefix length is ignored if specified. See section 12.6 Full-Text Search Functions for details of operation.
SPATIAL
indexes on
spatial column types. Spatial types are supported only for MyISAM
tables and indexed columns must be declared as NOT NULL
. See
section 18 Spatial Extensions in MySQL.
InnoDB
tables support checking of
foreign key constraints. See section 15 The InnoDB
Storage Engine. Note that the
FOREIGN KEY
syntax in InnoDB
is more restrictive than
the syntax presented for the CREATE TABLE
statement at the beginning of
this section: The columns of the referenced
table must always be explicitly named.
InnoDB
supports both ON DELETE
and ON UPDATE
actions on foreign keys as of MySQL 3.23.50 and 4.0.8, respectively.
For the precise syntax, see
section 15.7.4 FOREIGN KEY
Constraints.
For other storage engines, MySQL Server parses the FOREIGN KEY
and REFERENCES
syntax in CREATE TABLE
statements,
but without further action being taken.
The CHECK
clause is parsed but ignored by all storage engines.
See section 1.5.5.5 Foreign Keys.
MyISAM
and ISAM
tables,
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 + delete_flag + 7)/8 + (number of variable-length columns)delete_flag is 1 for tables with static record format. Static tables use a bit in the row record for a flag that indicates whether the row has been deleted. delete_flag is 0 for dynamic tables because the flag is stored in the dynamic row header. These calculations do not apply for
InnoDB
tables, for which
storage size is no different for NULL
columns than for NOT
NULL
columns.
The table_options part of the CREATE TABLE
syntax can be used
in MySQL 3.23 and above.
The ENGINE
and TYPE
options specify the storage engine for the
table. ENGINE
was added in MySQL 4.0.18 (for 4.0) and 4.1.2 (for 4.1).
It is the preferred option name as of those versions, and TYPE
has
become deprecated. TYPE
will be supported throughout the 4.x series,
but likely will be removed in MySQL 5.1.
The ENGINE
and TYPE
options take the following values:
Storage Engine | Description |
BDB | Transaction-safe tables with page locking. See section 14.4 The BDB (BerkeleyDB ) Storage Engine.
|
BerkeleyDB | An alias for BDB .
|
HEAP | The data for this table is stored only in memory. See section 14.3 The MEMORY (HEAP ) Storage Engine.
|
ISAM | The original MySQL storage engine. See section 14.9 The ISAM Storage Engine.
|
InnoDB | Transaction-safe tables with row locking and foreign keys. See section 15 The InnoDB Storage Engine.
|
MEMORY | An alias for HEAP . (Actually, as of MySQL 4.1, MEMORY is the preferred term.)
|
MERGE | A collection of MyISAM tables used as one table. See section 14.2 The MERGE Storage Engine.
|
MRG_MyISAM | An alias for MERGE .
|
MyISAM | The binary portable storage engine that is the improved replacement for ISAM . See section 14.1 The MyISAM Storage Engine.
|
See section 14 MySQL Storage Engines and Table Types.
If a storage engine is specified that is not available,
MySQL uses MyISAM
instead.
For example, if a table definition includes the ENGINE=BDB
option but the
MySQL server does not support BDB
tables, the table is created
as a MyISAM
table. This makes it possible to have a replication
setup where you have transactional tables on the master but tables created
on the slave are non-transactional (to get more speed). In MySQL 4.1.1, a
warning occurs if the storage engine specification is not honored.
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 storage engines unless otherwise indicated:
AUTO_INCREMENT
AUTO_INCREMENT
value for the table. This works for
MyISAM
only. To set the first auto-increment value for an InnoDB
table, insert a dummy row with a value one less than the desired value after
creating the table, and then delete the dummy row.
In MySQL 5.0.3 or later, the initial AUTO_INCREMENT
value for the table
works also for the InnoDB
table.
AVG_ROW_LENGTH
MyISAM
table, MySQL uses the product of the
MAX_ROWS
and AVG_ROW_LENGTH
options to decide how big the
resulting table will be. If you don't specify either option, the maximum
size for a table will be 4GB (or 2GB if your operating system only supports
2GB 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 want all your tables to be able to grow above the 4GB limit and are
willing to have your smaller tables slightly slower and larger than
necessary, you may increase the default pointer size by setting the
myisam_data_pointer_size
system variable, which was added in MySQL
4.1.2.
See section 5.2.3 Server System Variables.
CHECKSUM
CHECKSUM TABLE
statement reports the
checksum. (MyISAM
only.)
COMMENT
MAX_ROWS
MIN_ROWS
PACK_KEYS
DEFAULT
(MySQL 4.0) tells the storage engine to only pack long
CHAR
/VARCHAR
columns.
(MyISAM
and ISAM
only.)
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 uses prefix compression:
storage_size_for_key + pointer_size
(where the pointer
size is usually 4). Conversely,
you will get a big benefit from prefix compression only if you have many
numbers that are the same. If all keys are totally different, you will
use one byte more 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
.)
PASSWORD
DELAY_KEY_WRITE
MyISAM
only.)
ROW_FORMAT
MyISAM
tables. The option value can FIXED
or DYNAMIC
for
static or variable-length row format. myisampack
sets the type to
COMPRESSED
.
See section 14.1.3 MyISAM
Table Storage Formats.
Starting with MySQL/InnoDB-5.0.3, InnoDB records are stored in a more
compact format (ROW_FORMAT=COMPACT
) by default. The old format
can be requested by specifying ROW_FORMAT=REDUNDANT
.
RAID_TYPE
RAID_TYPE
option can help you to exceed the 2GB/4GB limit for
the MyISAM
data file (not the index file) on operating systems that
don't support big files. This option is unnecessary and not recommended for
filesystems that support big files.
You can get more speed from the I/O bottleneck by putting RAID
directories on different physical disks. For now, the only allowed
RAID_TYPE
is STRIPED
. 1
and RAID0
are aliases
for STRIPED
.
If you specify the RAID_TYPE
option for a MyISAM
table,
specify the RAID_CHUNKS
and RAID_CHUNKSIZE
options as well.
The maximum RAID_CHUNKS
value is 255.
MyISAM
will create RAID_CHUNKS
subdirectories named `00',
`01', `02', ... `09', `0a', `0b', ...
in the database directory. In each of these directories, MyISAM
will create a file `tbl_name.MYD'. When writing data to the data file,
the RAID
handler maps the first RAID_CHUNKSIZE*1024
bytes to
the first file, the next RAID_CHUNKSIZE*1024
bytes to the next file,
and so on.
RAID_TYPE
works on any operating system, as long as you have built
MySQL with the --with-raid
option to configure
. To determine
whether a server supports RAID
tables, use SHOW VARIABLES LIKE
'have_raid'
to see whether the variable value is YES
.
UNION
UNION
is used when you want to use a collection of identical
tables as one. This works only with MERGE
tables.
See section 14.2 The MERGE
Storage Engine.
For the moment, you must have SELECT
, UPDATE
, and
DELETE
privileges for the tables you map to a MERGE
table.
Originally, all used tables had to be in the same database
as the MERGE
table itself. This restriction has been lifted as of
MySQL 4.1.1.
INSERT_METHOD
MERGE
table, you have to specify with
INSERT_METHOD
into which table the row should be inserted.
INSERT_METHOD
is an option useful for MERGE
tables only.
This option was introduced in MySQL 4.0.0.
See section 14.2 The MERGE
Storage Engine.
DATA DIRECTORY
INDEX DIRECTORY
DATA DIRECTORY='directory'
or INDEX
DIRECTORY='directory'
you can specify where the MyISAM
storage engine should
put a table's data file and index file. Note that the directory should be a full
path to the directory (not a relative path).
These options work only for MyISAM
tables from MySQL 4.0 on, when
you are not using the --skip-symbolic-links
option. Your operating
system must also have a working, thread-safe realpath()
call.
See section 7.6.1.2 Using Symbolic Links for Tables on Unix.
As of MySQL 3.23, you can create one table from another by adding a
SELECT
statement at the end of the CREATE TABLE
statement:
CREATE TABLE new_tbl SELECT * FROM orig_tbl;
MySQL will create new column 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 creates a MyISAM
table with three columns, a
, b
,
and c
. Notice that the columns from the SELECT
statement
are appended to the right side of the table, not overlapped onto it.
Take the following example:
mysql> SELECT * FROM foo; +---+ | n | +---+ | 1 | +---+ mysql> CREATE TABLE bar (m INT) SELECT n FROM foo; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM bar; +------+---+ | m | n | +------+---+ | NULL | 1 | +------+---+ 1 row in set (0.00 sec)
For each row in table foo
, a row is inserted in bar
with
the values from foo
and default values for the new columns.
If any errors occur while copying the data to the table, it is automatically dropped and not created.
CREATE TABLE ... SELECT
will not automatically create any indexes
for you. This is done intentionally to make the statement as flexible as
possible. If you want to have indexes in the created table, you should
specify these before the SELECT
statement:
mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;
Some conversion of column types might occur. For example, the
AUTO_INCREMENT
attribute is not preserved, and VARCHAR
columns can become CHAR
columns.
When creating a table with CREATE ... SELECT
, make sure to alias any
function calls or expressions in the query. If you do not, the CREATE
statement might fail or result in undesirable column names.
CREATE TABLE artists_and_works SELECT artist.name, COUNT(work.artist_id) AS number_of_works FROM artist LEFT JOIN work ON artist.id = work.artist_id GROUP BY artist.id;
As of MySQL 4.1, you can explicitly specify the type for a generated column:
CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;
In MySQL 4.1, you can also use LIKE
to create an empty table based on the
definition of another table, including any column attributes and
indexes the original table has:
CREATE TABLE new_tbl LIKE orig_tbl;
CREATE TABLE ... LIKE
does not copy any DATA DIRECTORY
or
INDEX DIRECTORY
table options that were specified for the original
table, or any foreign key definitions.
You can precede the SELECT
by IGNORE
or REPLACE
to indicate how to handle records that duplicate unique key values.
With IGNORE
, new records that duplicate an existing record on a
unique key value are discarded. With REPLACE
, new records replace
records that have the same unique key value. If neither IGNORE
nor REPLACE
is specified, duplicate unique key values result in
an error.
To ensure that the update log/binary log can be used to re-create the
original tables, MySQL will not allow concurrent inserts during
CREATE TABLE ... SELECT
.
In some cases, MySQL silently changes column specifications from
those given in a CREATE TABLE
or ALTER TABLE
statement:
VARCHAR
columns with a length less than four are changed to
CHAR
.
VARCHAR
, TEXT
, or BLOB
),
all CHAR
columns longer than three characters are changed to
VARCHAR
columns. This doesn't affect how you use the columns in
any way; in MySQL, VARCHAR
is just a different way to
store characters. MySQL performs this conversion because it
saves space and makes table operations faster. See section 14 MySQL Storage Engines and Table Types.
CHAR
or VARCHAR
column with a
length specification greater than 255 is converted to the smallest TEXT
type that can hold values of the given length.
For example, VARCHAR(500)
is converted to TEXT
, and
VARCHAR(200000)
is converted to MEDIUMTEXT
.
This is a compatibility feature.
TIMESTAMP
display sizes are discarded from MySQL 4.1 on, due
to changes made to the TIMESTAMP
column type in that version.
Before MySQL 4.1, TIMESTAMP
display sizes must be even and in the
range from 2 to 14. If you specify a display size of 0 or greater than
14, the size is coerced to 14. Odd-valued sizes in the range from 1 to
13 are coerced to the next higher even number.
NULL
in a TIMESTAMP
column; setting
it to NULL
sets it to the current date and time. Because
TIMESTAMP
columns behave this way, the NULL
and NOT NULL
attributes do not apply in the normal way and are ignored if you specify
them. DESCRIBE tbl_name
always reports that a TIMESTAMP
column can be assigned NULL
values.
PRIMARY KEY
are made NOT NULL
even if
not declared that way.
ENUM
and SET
member values when the table is created.
USING
clause to specify an index type that is not
legal for a storage engine, but there is another index type available that
the engine can use without affecting query results, the engine will use the
available type.
To see whether MySQL used a column type other
than the one you specified, issue a DESCRIBE
or SHOW
CREATE TABLE
statement after creating or altering your table.
Certain other column type changes can occur if you compress a table
using myisampack
. See section 14.1.3.3 Compressed Table Characteristics.
CREATE VIEW
SyntaxCREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
This statement creates a new view, or replaces an existing one if the
OR REPLACE
clause is given. The select_statement is a
SELECT
statement that provides the definition of the view.
The statement can select from base tables or other views.
A view belongs to a database. By default, a new view is created in the current database. To create the view explicitly in a given database, specify the name as db_name.view_name when you create it.
mysql> CREATE VIEW test.v AS SELECT * FROM t;
Tables and views share the same namespace within a database, so a database cannot contain a table and a view that have the same name.
Views must have unique column names with no duplicates, just like base
tables. By default, the names of the columns retrieved by the SELECT
statement are used for the view column names. To define explicit names for
the view columns, the optional column list can be given. In this case, the
number of names in column_list must be the same as the number of
columns retrieved by the SELECT
statement.
Columns retrieved by the SELECT
statement can be simple references to
table columns. They can also be expressions that use operators, functions,
constant values, and so forth.
A view can refer to columns of tables or views in other databases by qualifying the table or view name with the proper database name.
A view definition is subject to the following constraints:
SELECT
statement cannot contain a subquery in the FROM
clause.
SELECT
statement cannot refer to user variables.
CHECK TABLE
statement.
TEMPORARY
table, and you cannot
create a TEMPORARY
view.
The WITH CHECK OPTION
clause can be given for an updatable view to
prevent inserts or updates to rows except those for which the WHERE
clause in the select_statement is true.
A view can be created from many kinds of SELECT
statements.
For example, the SELECT
can refer to a single table, a join of
multiple tables, or a UNION
. The SELECT
need not even
refer to any tables. The following example defines a view that
selects two columns from another table, as well as an expression calculated
from those columns:
mysql> CREATE TABLE t (qty INT, price INT); mysql> INSERT INTO t VALUES(3, 50); mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t; mysql> SELECT * FROM v; +------+-------+-------+ | qty | price | value | +------+-------+-------+ | 3 | 50 | 150 | +------+-------+-------+
In a WITH CHECK OPTION
clause for an updatable view, the LOCAL
and CASCADED
keywords determine the scope of check testing when the
view is defined in terms of another view. LOCAL
keyword restricts
the CHECK OPTION
only to the view being defined. CASCADED
causes the checks for underlying views to be evaluated as well. When neither
keyword is given, the default is CASCADED
. Consider the definitions
for the following table and set of views:
mysql> CREATE TABLE t1 (a INT); mysql> CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2 -> WITH CHECK OPTION; mysql> CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0 -> WITH LOCAL CHECK OPTION; mysql> CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0 -> WITH CASCADED CHECK OPTION;
Here the v2
and v3
views are defined in terms of another view,
v1
. v2
has a LOCAL
check option, so inserts are tested
only against the v2
check. v3
has a CASCADED
check
option, so inserts are tested not only against its own check, but against
those of underlying views. The following statements illustrate these
differences:
ql> INSERT INTO v2 VALUES (2); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO v3 VALUES (2); ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'
The CREATE VIEW
statement was added in MySQL 5.0.1. The WITH
CHECK OPTION
clause was implemented in MySQL 5.0.2.
DROP DATABASE
SyntaxDROP {DATABASE | SCHEMA} [IF EXISTS] db_name
DROP DATABASE
drops all tables in the database and deletes the
database. Be very careful with this statement!
To use DROP DATABASE
, you need the DROP
privilege on the
database.
In MySQL 3.22 or later, you can use the keywords IF EXISTS
to prevent an error from occurring if the database doesn't exist.
DROP SCHEMA
can be used as of MySQL 5.0.2.
If you use DROP DATABASE
on a symbolically linked
database, both the link and the original database are deleted.
As of MySQL 4.1.2, DROP DATABASE
returns the number of tables that
were removed. This corresponds to the number of `.frm' files removed.
The DROP DATABASE
statement removes from the given database directory
those files and directories that MySQL itself may create during normal
operation:
.BAK | .DAT | .HSH | .ISD
|
.ISM | .ISM | .MRG | .MYD
|
.MYI | .db | .frm |
00
-ff
. These are subdirectories used for RAID
tables.
If other files or directories remain in the database directory after MySQL
removes those just listed, the database directory cannot be removed. In this
case, you must remove any remaining files or directories manually and issue
the DROP DATABASE
statement again.
You can also drop databases with mysqladmin
.
See section 8.4 mysqladmin
, Administering a MySQL Server.
DROP INDEX
SyntaxDROP INDEX index_name ON tbl_name
DROP INDEX
drops the index named index_name from the table
tbl_name. In MySQL 3.22 or later, DROP INDEX
is mapped to an
ALTER TABLE
statement to drop the index. See section 13.2.2 ALTER TABLE
Syntax. DROP INDEX
doesn't do anything prior to MySQL
3.22.
DROP TABLE
SyntaxDROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE]
DROP TABLE
removes one or more tables. You must have the DROP
privilege for each table. All table data and the table
definition are removed, so be careful with this statement!
In MySQL 3.22 or later, you can use the keywords IF EXISTS
to prevent an error from occurring for tables that don't exist. As of
MySQL 4.1, a NOTE
is generated for each non-existent table when
using IF EXISTS
.
See section 13.5.4.20 SHOW WARNINGS
Syntax.
RESTRICT
and CASCADE
are allowed to make porting easier.
For the moment, they do nothing.
Note: DROP TABLE
automatically commits the current
active transaction, unless you are using MySQL 4.1 or higher and the
TEMPORARY
keyword.
The TEMPORARY
keyword is ignored in MySQL 4.0. As of 4.1, it has the
following effect:
TEMPORARY
tables.
TEMPORARY
table is visible
only to the client that created it, so no check is necessary.)
Using TEMPORARY
is a good way to ensure that you don't accidentally
drop a non-TEMPORARY
table.
DROP VIEW
SyntaxDROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE]
DROP VIEW
removes one or more views. You must have the DROP
privilege for each view.
You can use the keywords IF EXISTS
to prevent an error from occurring
for views that don't exist. When this clause is given, a NOTE
is
generated for each non-existent view.
See section 13.5.4.20 SHOW WARNINGS
Syntax.
RESTRICT
and CASCADE
, if given, are parsed and ignored.
This statement was added in MySQL 5.0.1.
RENAME TABLE
SyntaxRENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...
This statement renames one or more tables. It was added in MySQL 3.23.23.
The rename operation is done atomically, which means that no other thread
can access any of the tables while the rename is running. For example,
if you have an existing table old_table
, you can create another
table new_table
that has the same structure but is empty, and then
replace the existing table with the empty one as follows:
CREATE TABLE new_table (...); RENAME TABLE old_table TO backup_table, new_table TO old_table;
If the statement renames more than one table, renaming operations are done
from left to right. If you want to swap two table names, you can do so
like this (assuming that no table named tmp_table
currently exists):
RENAME TABLE old_table TO tmp_table, new_table TO old_table, tmp_table TO new_table;
As long as two databases are on the same filesystem you can also rename a table to move it from one database to another:
RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
When you execute RENAME
, you can't have any locked tables or
active transactions. You must also have the ALTER
and DROP
privileges on the original table, and the CREATE
and INSERT
privileges on the new table.
If MySQL encounters any errors in a multiple-table rename, it will do a reverse rename for all renamed tables to get everything back to the original state.
DESCRIBE
Syntax (Get Information About Columns){DESCRIBE | DESC} tbl_name [col_name | wild]
DESCRIBE
provides information about a table's columns. It is a
shortcut for SHOW COLUMNS FROM
.
See section 13.5.4.3 SHOW COLUMNS
Syntax.
col_name can be a column name, or a string containing the SQL `%' and `_' wildcard characters to obtain output only for the columns with names matching the string. There is no need to enclose the string in quotes unless it contains spaces or other special characters.
mysql> DESCRIBE city; +------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+----------------+ | Id | int(11) | | PRI | NULL | auto_increment | | Name | char(35) | | | | | | Country | char(3) | | UNI | | | | District | char(20) | YES | MUL | | | | Population | int(11) | | | 0 | | +------------+----------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
The Null
column indicates whether NULL values can be stored,
with YES displayed when NULL values are allowed.
The Key
column indicates whether the field is indexed. A value of PRI
indicates that the field is part of the table's primary key. UNI
indicates that
the field is part of a UNIQUE
index. The MUL
value
indicates that multiple occurences of a given value allowed within the field.
A field can be designated as MUL
even if a UNIQUE
index is used if
NULL
values are allowed, as multiple rows in a UNIQUE
index can hold
a NULL
value if the column is not declared NOT NULL
. Another cause
for MUL
on a UNIQUE
index is when two columns form a composite
UNIQUE
index; while the combination of the columns will be unique, each
column can still hold multiple occurences of a given value. Note that in a composite index only the leftmost field of the index will have an entry in the Key
column.
The Default
column indicates the default value that is assigned to the field.
The Extra
column contains any additional information that is available about
a given field. In our example the Extra
column indicates that our Id
column
was created with the AUTO_INCREMENT
keyword.
If the column types are different from what you expect them to be based on a
CREATE TABLE
statement, note that MySQL sometimes
changes column types. See section 13.2.6.1 Silent Column Specification Changes.
The DESCRIBE
statement is provided for Oracle compatibility.
The SHOW CREATE TABLE
and SHOW TABLE STATUS
statements also
provide information about tables.
See section 13.5.4 SHOW
Syntax.
USE
SyntaxUSE db_name
The USE db_name
statement tells MySQL to use the db_name
database as the default (current) database for subsequent statements.
The database remains the default until the end of the session or until
another USE
statement is issued:
mysql> USE db1; mysql> SELECT COUNT(*) FROM mytable; # selects from db1.mytable mysql> USE db2; mysql> SELECT COUNT(*) FROM mytable; # selects from db2.mytable
Making a particular database current by means of the USE
statement
does not preclude you from accessing tables in other databases. The following example
accesses the author
table from the db1
database and the
editor
table from the db2
database:
mysql> USE db1; mysql> SELECT author_name,editor_name FROM author,db2.editor -> WHERE author.editor_id = db2.editor.editor_id;
The USE
statement is provided for Sybase compatibility.
START TRANSACTION
, COMMIT
, and ROLLBACK
SyntaxBy default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk.
If you are using transaction-safe tables (like InnoDB
or BDB
),
you can disable autocommit mode with the following statement:
SET AUTOCOMMIT=0;
After disabling autocommit mode by setting the AUTOCOMMIT
variable to
zero, you must use COMMIT
to store your changes to disk or
ROLLBACK
if you want to ignore the changes you have made since
the beginning of your transaction.
If you want to disable autocommit mode for a single series of
statements, you can use the START TRANSACTION
statement:
START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; COMMIT;
With START TRANSACTION
, autocommit remains disabled until you end the
transaction with COMMIT
or ROLLBACK
. The autocommit mode then
reverts to its previous state.
BEGIN
and BEGIN WORK
can be used instead of START
TRANSACTION
to initiate a transaction. START TRANSACTION
was
added in MySQL 4.0.11. This is standard SQL syntax and is the recommended way
to start an ad-hoc transaction. BEGIN
and BEGIN WORK
are
available from MySQL 3.23.17 and 3.23.19, respectively.
As of MySQL 4.1.8, you can begin a transaction like this:
START TRANSACTION WITH CONSISTENT SNAPSHOT;
The WITH CONSISTENT SNAPSHOT
clause starts a consistent read for
storage engines that are capable of it. Currently, this applies only
to InnoDB
. The effect is the same as issuing a START
TRANSACTION
followed by a SELECT
from any InnoDB
table.
See section 15.11.3 Consistent Non-Locking Read.
Beginning a transaction causes an implicit UNLOCK TABLES
to be
performed.
Note that if you are not using transaction-safe tables, any changes are stored at once, regardless of the status of autocommit mode.
If you issue a ROLLBACK
statement after updating a non-transactional
table within a transaction, an ER_WARNING_NOT_COMPLETE_ROLLBACK
warning occurs. Changes to transaction-safe tables will be rolled back, but
not changes to non-transaction-safe tables.
If you are using START TRANSACTION
or SET AUTOCOMMIT=0
, you
should use the MySQL binary log for backups instead of the
older update log. Transactions are stored in the binary log
in one chunk, upon COMMIT
. Transactions that are
rolled back are not logged.
(Exception: Modifications to non-transactional tables cannot be rolled
back. If a transaction that is rolled back includes modifications
to non-transactional tables, the entire transaction is logged with a
ROLLBACK
statement at the end to ensure that the modifications to
those tables are replicated. This is true as of MySQL 4.0.15.)
See section 5.9.4 The Binary Log.
You can change the isolation level for transactions with
SET TRANSACTION ISOLATION LEVEL
.
See section 13.4.6 SET TRANSACTION
Syntax.
Rolling back can be a slow operation that can occur without the user having
explicitly asked for it (for example, when an error occurs). Because of
this, SHOW PROCESSLIST
will display Rolling back
in the
State
column for the connection during implicit rollback and explicit
(ROLLBACK
SQL command) rollbacks, starting from MySQL 4.1.8.
Some statements cannot be rolled back. In general, these include data definition language (DDL) statements, such as those that create or drop databases, or those that create, drop, or alter tables.
You should design your transactions not to include such statements.
If you issue a statement early in a transaction that cannot be rolled back,
and then another statement later fails, the full effect of the transaction
cannot be rolled back by issuing a ROLLBACK
statement.
Each of the following statements (and any synonyms for them) implicitly end
a transaction, as if you had done a COMMIT
before executing the
statement:
ALTER TABLE | BEGIN | CREATE INDEX
|
DROP DATABASE | DROP INDEX | DROP TABLE
|
LOAD MASTER DATA | LOCK TABLES | RENAME TABLE
|
SET AUTOCOMMIT=1 | START TRANSACTION | TRUNCATE TABLE
|
UNLOCK TABLES
also ends a transaction if any tables currently are
locked. Prior to MySQL 4.0.13, CREATE TABLE
ends a transaction if
the binary update log is enabled.
Transactions cannot be nested. This is a consequence of the implicit
COMMIT
performed for any current transaction when you issue a
START TRANSACTION
statement or one of its synonyms.
SAVEPOINT
and ROLLBACK TO SAVEPOINT
SyntaxSAVEPOINT identifier ROLLBACK TO SAVEPOINT identifier
Starting from MySQL 4.0.14 and 4.1.1, InnoDB
supports the SQL statements
SAVEPOINT
and ROLLBACK TO SAVEPOINT
.
The SAVEPOINT
statement sets a named transaction savepoint with a name
of identifier
. If the current transaction already has a savepoint
with the same name, the old savepoint is deleted and a new one is set.
The ROLLBACK TO SAVEPOINT
statement rolls back a transaction to the
named savepoint. Modifications that the current transaction made to rows
after the savepoint was set are undone in the rollback, but InnoDB
does not release the row locks that were stored in memory after
the savepoint. (Note that for a new inserted row, the lock information is
carried by the transaction ID stored in the row; the lock is not separately
stored in memory. In this case, the row lock is released in the undo.)
Savepoints that were set at a later time than the named savepoint are
deleted.
If the statement returns the following error, it means that no savepoint with the specified name exists:
ERROR 1181: Got error 153 during ROLLBACK
All savepoints of the current transaction are deleted if you execute a
COMMIT
, or a ROLLBACK
that does not name a savepoint.
LOCK TABLES
and UNLOCK TABLES
SyntaxLOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} [, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ... UNLOCK TABLES
LOCK TABLES
locks tables for the current thread. UNLOCK
TABLES
releases any locks held by the current thread. All tables that
are locked by the current thread are implicitly unlocked when the
thread issues another LOCK TABLES
, or when the connection to the
server is closed.
Note the following regarding the use of LOCK TABLES
with transactional
tables:
LOCK TABLES
is not transaction-safe and implicitly
commits any active transactions before attempting to lock the tables.
Also, beginning a transaction (for example, with START TRANSACTION
)
implicitly performs an UNLOCK TABLES
.
LOCK TABLES
with transactional
tables, like InnoDB, is to set AUTOCOMMIT = 0
and not to call
UNLOCK TABLES
until you commit the transaction explicitly.
When you call LOCK TABLES
, InnoDB internally takes its own table
lock, and MySQL takes its own table lock. InnoDB releases its table lock at
the next commit, but for MySQL to release its table lock, you have to call
UNLOCK TABLES
. You should not have AUTOCOMMIT = 1
, because
then InnoDB releases its table lock immediately after the call of
LOCK TABLES
, and deadlocks will very easily happen.
Starting from 4.1.9, we do not acquire the InnoDB table lock at all
if AUTOCOMMIT=1
. That helps old applications to avoid unnecessary
deadlocks.
As of MySQL 4.0.2, to use LOCK TABLES
you must have the
LOCK TABLES
privilege and a SELECT
privilege for the
involved tables. In MySQL 3.23, you must have SELECT
,
INSERT
, DELETE
, and UPDATE
privileges for the
tables.
The main reasons to use LOCK TABLES
are for emulating transactions
or to get more speed when updating tables. This is explained in more
detail later.
If a thread obtains a READ
lock on a table, that thread (and all other
threads) can only read from the table. If a thread obtains a WRITE
lock on a table, only the thread holding the lock can
write to the table. Other threads are blocked from doing so until the
lock has been released.
The difference between READ LOCAL
and READ
is that READ
LOCAL
allows non-conflicting INSERT
statements (concurrent inserts)
to execute while the lock is held. However, this can't be used if you are
going to manipulate the database files outside MySQL while you hold the
lock. For InnoDB, READ LOCAL
essentially does nothing: it does
not lock the table at all. The use of READ LOCAL
for InnoDB
tables is deprecated, because for InnoDB, a plain consistent read
SELECT
does the same thing, and no locks are needed.
When you use LOCK TABLES
, you must lock all tables that you are going to
use in your queries. While the locks obtained with a LOCK TABLES
statement are in effect, you cannot access any tables that were not locked by
the statement. Also, you cannot use a locked table multiple times in one query
- use aliases for that. Note that in that case you must get a lock for each
alias separately.
mysql> LOCK TABLE t WRITE, t AS t1 WRITE; mysql> INSERT INTO t SELECT * FROM t; ERROR 1100: Table 't' was not locked with LOCK TABLES mysql> INSERT INTO t SELECT * FROM t AS t1;
If your queries refer to a table using an alias, then you must lock the table using that same alias. It will not work to lock the table without specifying the alias:
mysql> LOCK TABLE t READ; mysql> SELECT * FROM t AS myalias; ERROR 1100: Table 'myalias' was not locked with LOCK TABLES
Conversely, if you lock a table using an alias, you must refer to it in your queries using that alias:
mysql> LOCK TABLE t AS myalias READ; mysql> SELECT * FROM t; ERROR 1100: Table 't' was not locked with LOCK TABLES mysql> SELECT * FROM t AS myalias;
WRITE
locks normally have higher priority than READ
locks to
ensure that updates are processed as soon as possible. This means that if one
thread obtains a READ
lock and then another thread requests a
WRITE
lock, subsequent READ
lock requests will wait until the
WRITE
thread has gotten the lock and released it. You can use
LOW_PRIORITY WRITE
locks to allow other threads to obtain READ
locks while the thread is waiting for the WRITE
lock. You should
use LOW_PRIORITY WRITE
locks only if you are sure that there will
eventually be a time when no threads will have a READ
lock.
LOCK TABLES
works as follows:
This policy ensures that table locking is deadlock free. There are, however, other things you need to be aware of about this policy:
If you are using a LOW_PRIORITY WRITE
lock for a table, it
means only that MySQL will wait for this particular lock until
there are no threads that want a READ
lock. When the thread has
gotten the WRITE
lock and is waiting to get the lock for the next
table in the lock table list, all other threads will wait for the
WRITE
lock to be released. If this becomes a serious problem
with your application, you should consider converting some of your
tables to transaction-safe tables.
You can safely use KILL
to terminate a thread that is waiting for a
table lock.
See section 13.5.5.3 KILL
Syntax.
Note that you should not lock any tables that you are using with
INSERT DELAYED
because in that case the INSERT
is done by a
separate thread.
Normally, you don't have to lock tables, because all single UPDATE
statements
are atomic; no other thread can interfere with any other currently executing
SQL statement. There are a few cases when you would like to lock tables
anyway:
MyISAM
tables,
it's much faster to lock the tables you are going to use. Locking
MyISAM
tables speeds up inserting, updating, or deleting on them.
The downside is that no thread can update a READ
-locked table
(including the one holding the lock) and no thread can access a
WRITE
-locked table other than the one holding the lock.
The reason some MyISAM
operations are faster under LOCK TABLES
is that MySQL will not flush the key cache for the locked tables until
UNLOCK TABLES
is called. Normally, the key cache is flushed after
each SQL statement.
LOCK TABLES
if you want to ensure that
no other thread comes between a SELECT
and an UPDATE
. The
example shown here requires LOCK TABLES
to execute safely:
mysql> LOCK TABLES trans READ, customer WRITE; mysql> SELECT SUM(value) FROM trans WHERE customer_id=some_id; mysql> UPDATE customer -> SET total_value=sum_from_previous_statement -> WHERE customer_id=some_id; mysql> UNLOCK TABLES;Without
LOCK TABLES
, it is possible that another thread might
insert a new row in the trans
table between execution of the
SELECT
and UPDATE
statements.
You can avoid using LOCK TABLES
in many cases
by using relative updates (UPDATE customer SET
value=value+new_value
) or the LAST_INSERT_ID()
function,
See section 1.5.5.3 Transactions and Atomic Operations.
You can also avoid locking tables in some cases by using the user-level
advisory lock functions GET_LOCK()
and RELEASE_LOCK()
.
These locks are saved in a hash table in the server and implemented with
pthread_mutex_lock()
and pthread_mutex_unlock()
for high speed.
See section 12.8.4 Miscellaneous Functions.
See section 7.3.1 Locking Methods, for more information on locking policy.
You can lock all tables in all databases with read locks with the FLUSH
TABLES WITH READ LOCK
statement. See section 13.5.5.2 FLUSH
Syntax. This
is a very convenient way to get backups if you have a filesystem such as
Veritas that can take snapshots in time.
Note: If you use ALTER TABLE
on a locked table, it may become
unlocked. See section A.7.1 Problems with ALTER TABLE
.
SET TRANSACTION
SyntaxSET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
This statement sets the transaction isolation level for the next transaction, globally, or for the current session.
The default behavior of SET TRANSACTION
is to set the isolation level
for the next (not yet
started) transaction. If you use the GLOBAL
keyword, the statement
sets the default transaction level globally for all new connections
created from that point on. Existing connections are unaffected.
You need the SUPER
privilege to do this. Using the SESSION
keyword sets the
default transaction level for all future transactions performed on the
current connection.
For descriptions of each InnoDB
transaction isolation level, see
section 15.11.2 InnoDB
and TRANSACTION ISOLATION LEVEL
.
InnoDB
supports each of these levels
from MySQL 4.0.5 on. The default level is REPEATABLE READ
.
You can set the initial default global isolation level for mysqld
with
the --transaction-isolation
option.
See section 5.2.1 mysqld
Command-Line Options.
DROP USER
SyntaxDROP USER user [, user] ...
The DROP USER
statement deletes one or more MySQL accounts that
don't have any privileges. It serves to remove each account record from
the user
table. The account is named using the same format as for
GRANT
or REVOKE
; for example, 'jeffrey'@'localhost'
.
The user and host parts of the account name correspond to the User
and
Host
column values of the user
table record for the account.
To remove a MySQL user account, you should use the following procedure, performing the steps in the order shown:
SHOW GRANTS
to determine what privileges the account has.
See section 13.5.4.10 SHOW GRANTS
Syntax.
REVOKE
to revoke the privileges displayed by SHOW GRANTS
.
This removes records for the account from all the grant tables except the
user
table, and revokes any global privileges listed in the user
table.
See section 13.5.1.2 GRANT
and REVOKE
Syntax.
DROP USER
to remove the user
table
record.
The DROP USER
statement was added in MySQL 4.1.1. Before 4.1.1,
you should first revoke the account privileges as just described. Then
delete the user
table record and flush the grant tables like this:
mysql> DELETE FROM mysql.user -> WHERE User='user_name' and Host='host_name'; mysql> FLUSH PRIVILEGES;
GRANT
and REVOKE
SyntaxGRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON {tbl_name | * | *.* | db_name.*} TO user [IDENTIFIED BY [PASSWORD] 'password'] [, user [IDENTIFIED BY [PASSWORD] 'password']] ... [REQUIRE NONE | [{SSL| X509}] [CIPHER 'cipher' [AND]] [ISSUER 'issuer' [AND]] [SUBJECT 'subject']] [WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count]]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON {tbl_name | * | *.* | db_name.*} FROM user [, user] ... REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
The GRANT
and REVOKE
statements allow system administrators to
create MySQL user accounts and to grant rights to and revoke them from
accounts. GRANT
and REVOKE
are implemented in MySQL 3.22.11
or later. For earlier MySQL versions, these statements do nothing.
MySQL account information is stored in the tables of the mysql
database. This database and the access control system are discussed
extensively in section 5 Database Administration, which you should consult
for additional details.
Privileges can be granted at four levels:
mysql.user
table.
GRANT ALL ON *.*
and
REVOKE ALL ON *.*
grant and revoke only global privileges.
mysql.db
and mysql.host
tables.
GRANT ALL ON db_name.*
and
REVOKE ALL ON db_name.*
grant and revoke only database privileges.
mysql.tables_priv
table.
GRANT ALL ON db_name.tbl_name
and
REVOKE ALL ON db_name.tbl_name
grant and revoke only table privileges.
mysql.columns_priv
table.
When using REVOKE
, you must specify the same columns that were granted.
To make it easy to revoke all privileges, MySQL 4.1.2 has added the following syntax, which drops all global, database-, table-, and column-level privileges for the named users:
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
Before MySQL 4.1.2, all privileges cannot be dropped at once. Two statements are necessary:
REVOKE ALL PRIVILEGES ON *.* FROM user [, user] ... REVOKE GRANT OPTION ON *.* FROM user [, user] ...
For the GRANT
and REVOKE
statements, priv_type
can be
specified as any of the following:
Privilege | Meaning |
ALL [PRIVILEGES] | Sets all simple privileges except GRANT OPTION
|
ALTER | Allows use of ALTER TABLE
|
CREATE | Allows use of CREATE TABLE
|
CREATE TEMPORARY TABLES | Allows use of CREATE TEMPORARY TABLE
|
CREATE VIEW | Allows use of CREATE VIEW
|
DELETE | Allows use of DELETE
|
DROP | Allows use of DROP TABLE
|
EXECUTE | Allows the user to run stored procedures (MySQL 5.0) |
FILE | Allows use of SELECT ... INTO OUTFILE and LOAD DATA INFILE
|
INDEX | Allows use of CREATE INDEX and DROP INDEX
|
INSERT | Allows use of INSERT
|
LOCK TABLES | Allows use of LOCK TABLES on tables for which you have the SELECT privilege
|
PROCESS | Allows use of SHOW FULL PROCESSLIST
|
REFERENCES | Not yet implemented |
RELOAD | Allows use of FLUSH
|
REPLICATION CLIENT | Allows the user to ask where the slave or master servers are |
REPLICATION SLAVE | Needed for replication slaves (to read binary log events from the master) |
SELECT | Allows use of SELECT
|
SHOW DATABASES | SHOW DATABASES shows all databases
|
SHOW VIEW | Allows use of SHOW CREATE VIEW
|
SHUTDOWN | Allows use of mysqladmin shutdown
|
SUPER | Allows use of CHANGE MASTER , KILL ,
PURGE MASTER LOGS , and SET GLOBAL statements, the mysqladmin debug command; allows you to connect (once) even if max_connections is reached
|
UPDATE | Allows use of UPDATE
|
USAGE | Synonym for ``no privileges'' |
GRANT OPTION | Allows privileges to be granted |
USAGE
can be used when you want to create a user that has no privileges.
Use SHOW GRANTS
to determine what privileges the account has.
See section 13.5.4.10 SHOW GRANTS
Syntax.
The CREATE TEMPORARY TABLES
, EXECUTE
,
LOCK TABLES
, REPLICATION ...
, SHOW DATABASES
and
SUPER
privileges are new in MySQL 4.0.2. To use them
after upgrading to 4.0.2 or later, you must upgrade your grant tables.
Similarly,
the CREATE VIEW
and SHOW VIEW
privileges are new in MySQL 5.0.1. To use them
after upgrading to 5.0.1 or later, you must upgrade your grant tables.
See section 2.10.7 Upgrading the Grant Tables.
In older MySQL versions that do not have the SUPER
privilege, the
PROCESS
privilege can be used instead.
You can assign global privileges by using ON *.*
syntax or
database privileges by using ON db_name.*
syntax. If you specify
ON *
and you have a current database, the privileges will be granted in
that database. (Warning: If you specify ON *
and you
don't have a current database, the privileges granted will be global!)
The EXECUTION
, FILE
, PROCESS
, RELOAD
,
REPLICATION CLIENT
, REPLICATION SLAVE
, SHOW DATABASES
,
SHUTDOWN
, and SUPER
privileges are administrative privileges
that can only be granted globally (using ON *.*
syntax).
Other privileges can be granted globally or at more specific levels.
The only priv_type
values you can specify for a table are SELECT
,
INSERT
, UPDATE
, DELETE
, CREATE
, DROP
,
GRANT OPTION
, INDEX
, and ALTER
.
The only priv_type
values you can specify for a column (that is, when
you use a column_list
clause) are SELECT
, INSERT
, and
UPDATE
.
For the global, database, and table levels,
GRANT ALL
assigns only the privileges that exist at the level you are
granting. For example, if you use GRANT ALL ON db_name.*
, that is a
database-level statement, so none of the global-only privileges such as
FILE
will be granted.
For column-level privileges (that is, when you specify column_list),
you must explicitly name the privileges to be granted. You cannot use
ALL
as a privilege specifier.
MySQL allows you to create database-level privileges even if the database doesn't exist, to make it easy to prepare for database use. However, MySQL currently does not allow you to create table-level or column-level privileges if the table doesn't exist.
MySQL does not automatically revoke any privileges even if you drop a table or drop a database.
Note: the `_' and `%' wildcards are allowed when
specifying database names in GRANT
statements that grant privileges at
the global or database levels. This means, for example, that if you
want to use a `_' character as part of a database name,
you should specify it as `\_' in the GRANT
statement, to prevent
the user from being able to access additional databases matching the
wildcard pattern; for example, GRANT ... ON `foo\_bar`.* TO ...
.
In order to accommodate granting rights to users from arbitrary hosts,
MySQL supports specifying the user value in the form
user_name@host_name
. If you want to specify a user_name string
containing special characters (such as `-'), or a host_name string
containing special characters or wildcard characters (such as `%'), you
can quote the username or hostname (for example,
'test-user'@'test-hostname'
). Quote the username and hostname
separately.
You can specify wildcards in the hostname. For example,
user_name@'%.loc.gov'
applies to user_name for any host in the
loc.gov
domain, and user_name@'144.155.166.%'
applies to
user_name for any host in the 144.155.166
class C subnet.
The simple form user_name is a synonym for user_name@'%'
.
MySQL doesn't support wildcards in usernames. Anonymous users are
defined by inserting entries with User=''
into the
mysql.user
table or creating a user with an empty name with the
GRANT
statement:
mysql> GRANT ALL ON test.* TO ''@'localhost' ...
When specifying quoted values, quote database, table, or column names as identifiers, using backticks (``'). Quote hostnames, usernames, or passwords as strings, using apostrophes (`'').
Warning: If you allow anonymous users to connect to the MySQL
server, you should also grant privileges to all local users as
user_name@localhost
. Otherwise, the anonymous-user account for
the local host in the mysql.user
table will be used when named users
try to log in to the MySQL server from the local machine!
(This anonymous-user account is created during MySQL installation.)
You can determine whether this applies to you by executing the following query:
mysql> SELECT Host, User FROM mysql.user WHERE User='';
If you want to delete the local anonymous-user account to avoid the problem just described, use these statements:
mysql> DELETE FROM mysql.user WHERE Host='localhost' AND User=''; mysql> FLUSH PRIVILEGES;
For the moment, GRANT
only supports host, table, database, and
column names up to 60 characters long. A username can be up to 16
characters.
The privileges for a table or column are formed additively from the
logical OR of the privileges at each of the four privilege
levels. For example, if the mysql.user
table specifies that a
user has a global SELECT
privilege, the privilege cannot be denied by an
entry at the database, table, or column level.
The privileges for a column can be calculated as follows:
global privileges OR (database privileges AND host privileges) OR table privileges OR column privileges
In most cases, you grant rights to a user at only one of the privilege levels, so life isn't normally this complicated. The details of the privilege-checking procedure are presented in section 5.5 The MySQL Access Privilege System.
If you grant privileges for a username/hostname combination that does not exist
in the mysql.user
table, an entry is added and remains there until
deleted with a DELETE
statement. In other words, GRANT
may
create user
table entries, but REVOKE
will not remove them;
you must do that explicitly using DROP USER
or DELETE
.
In MySQL 3.22.12 or later,
if a new user is created or if you have global grant privileges, the user's
password is set to the password specified by the IDENTIFIED BY
clause, if one is given. If the user already had a password, it is replaced
by the new one.
Warning: If you create a new user but do not specify an
IDENTIFIED BY
clause, the user has no password. This is insecure.
As of MySQL 5.0.2, you can enable the NO_AUTO_CREATE_USER
SQL mode
to prevent GRANT
from creating new users if it would otherwise do so,
unless IDENTIFIED BY
is given to provide a password.
Passwords can also be set with the SET PASSWORD
statement.
See section 13.5.1.3 SET PASSWORD
Syntax.
In the IDENTIFIED BY
clause, the password should be given as the
literal password value. It is unnecessary to use the PASSWORD()
function as it is for the SET PASSWORD
statement. For example:
GRANT ... IDENTIFIED BY 'mypass';
If you don't want to send the password in clear text and you know the hashed
value that PASSWORD()
would return for the password, you can specify
the hashed value preceded by the keyword PASSWORD
:
GRANT ... IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';
In a C program, you can get the hashed value by using the
make_scrambled_password()
C API function.
If you grant privileges for a database, an entry in the mysql.db
table is created if needed. If all privileges for the database are
removed with REVOKE
, this entry is deleted.
If a user has no privileges for a table, the table name is not displayed
when the user requests a list of tables (for example, with a SHOW TABLES
statement). If a user has no privileges for a database, the database name
is not displayed by SHOW DATABASES
unless the user has the SHOW
DATABASES
privilege.
The WITH GRANT OPTION
clause gives the user the ability to give
to other users any privileges the user has at the specified privilege level.
You should be careful to whom you give the GRANT OPTION
privilege,
because two users with different privileges may be able to join privileges!
You cannot grant another user a privilege you don't have yourself; the
GRANT OPTION
privilege allows you to give away only those privileges
you possess.
Be aware that when you grant a user the GRANT OPTION
privilege at a
particular privilege level, any privileges the user already possesses (or
is given in the future!) at that level are also grantable by that user.
Suppose that you grant a user the INSERT
privilege on a database. If
you then grant the SELECT
privilege on the database and specify
WITH GRANT OPTION
, the user can give away not only the SELECT
privilege, but also INSERT
. If you then grant the UPDATE
privilege to the user on the database, the user can give away
INSERT
, SELECT
, and UPDATE
.
You should not grant ALTER
privileges to a normal user. If you
do that, the user can try to subvert the privilege system by renaming
tables!
The MAX_QUERIES_PER_HOUR count
, MAX_UPDATES_PER_HOUR count
,
and MAX_CONNECTIONS_PER_HOUR count
options are new in MySQL 4.0.2.
They limit the number of queries, updates, and logins a user can perform
during one hour. If count
is 0 (the default), this means there is no
limitation for that user. See section 5.6.4 Limiting Account Resources. Note: To specify any of
these options for an existing user without affecting existing privileges,
use GRANT USAGE ON *.* ... WITH MAX_...
.
MySQL can check X509 certificate attributes in addition to the usual
authentication that is based on the username and password. To specify
SSL-related options for a MySQL account, use the REQUIRE
clause of
the GRANT
statement.
(For background on the use of SSL with MySQL, see section 5.6.7 Using Secure Connections.)
There are different possibilities for limiting connection types for an account:
REQUIRE SSL
option tells the server to allow only SSL-encrypted
connections for the account. Note that this option can be omitted
if there are any access-control records that allow non-SSL connections.
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost' -> IDENTIFIED BY 'goodsecret' REQUIRE SSL;
REQUIRE X509
means that the client must have a valid certificate
but that the exact certificate, issuer, and subject do not matter.
The only requirement is that it should be possible to verify its
signature with one of the CA certificates.
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost' -> IDENTIFIED BY 'goodsecret' REQUIRE X509;
REQUIRE ISSUER 'issuer'
places the restriction on connection attempts
that the client must present a valid X509 certificate issued by CA
'issuer'
. If the client presents a certificate that is valid but has
a different issuer, the server rejects the connection. Use of X509
certificates always implies encryption, so the SSL
option is
unneccessary.
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost' -> IDENTIFIED BY 'goodsecret' -> REQUIRE ISSUER '/C=FI/ST=Some-State/L=Helsinki/ O=MySQL Finland AB/CN=Tonu Samuel/Email=tonu@example.com';Note that the
ISSUER
value should be entered as a single string.
REQUIRE SUBJECT 'subject'
places the restriction on connection
attempts that the client must present a valid X509 certificate with subject
'subject'
in it. If the client presents a certificate that is valid
but has a different subject, the server rejects the connection.
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost' -> IDENTIFIED BY 'goodsecret' -> REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/ O=MySQL demo client certificate/ CN=Tonu Samuel/Email=tonu@example.com';Note that the
SUBJECT
value should be entered as a single string.
REQUIRE CIPHER 'cipher'
is needed to ensure that strong enough ciphers
and key lengths will be used. SSL itself can be weak if old algorithms
with short encryption keys are used. Using this option, you can ask for
some exact cipher method to allow a connection.
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost' -> IDENTIFIED BY 'goodsecret' -> REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';
The SUBJECT
, ISSUER
, and CIPHER
options can be
combined in the REQUIRE
clause like this:
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost' -> IDENTIFIED BY 'goodsecret' -> REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/ O=MySQL demo client certificate/ CN=Tonu Samuel/Email=tonu@example.com' -> AND ISSUER '/C=FI/ST=Some-State/L=Helsinki/ O=MySQL Finland AB/CN=Tonu Samuel/Email=tonu@example.com' -> AND CIPHER 'EDH-RSA-DES-CBC3-SHA';
Note that the SUBJECT
and ISSUER
values each should be entered
as a single string.
Starting from MySQL 4.0.4, the AND
keyword is optional between
REQUIRE
options.
The order of the options does not matter, but no option can be specified twice.
When mysqld
starts, all privileges are read into memory.
Database, table, and column privileges take effect at once, and
user-level privileges take effect the next time the user connects.
Modifications to the grant tables that you perform using GRANT
or
REVOKE
are noticed by the server immediately.
If you modify the grant tables manually (using INSERT
, UPDATE
,
and so on), you should execute a FLUSH PRIVILEGES
statement or run
mysqladmin flush-privileges
to tell the server to reload the grant
tables.
See section 5.5.7 When Privilege Changes Take Effect.
Note that if you are using table or column privileges for even one user, the server examines table and column privileges for all users and this slows down MySQL a bit. Similarly, if you limit the number of queries, updates, or connections for any users, the server must monitor these values.
The biggest differences between the standard SQL and MySQL versions of
GRANT
are:
TRIGGER
or UNDER
privileges.
REVOKE
statements or
by manipulating the MySQL grant tables.
INSERT
privilege on only some of the
columns in a table, you can execute INSERT
statements on the
table; the columns for which you don't have the INSERT
privilege
will be set to their default values. Standard SQL requires you to have the
INSERT
privilege on all columns.
SET PASSWORD
SyntaxSET PASSWORD = PASSWORD('some password') SET PASSWORD FOR user = PASSWORD('some password')
The SET PASSWORD
statement assigns a password to an existing
MySQL user account.
The first syntax sets the password for the current user. Any client that has connected to the server using a non-anonymous account can change the password for that account.
The second syntax sets the password for a specific account on the current
server host. Only clients with access to the mysql
database can do
this. The user value should be given in user_name@host_name
format, where user_name and host_name are exactly as they are
listed in the User
and Host
columns of the mysql.user
table entry. For example, if you had an entry with User
and
Host
column values of 'bob'
and '%.loc.gov'
, you would
write the statement like this:
Starting from MySQL 4.1 you can check your current authentication
user@host
entry by executing SELECT current_user()
..
mysql> SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');
That is equivalent to the following statements:
mysql> UPDATE mysql.user SET Password=PASSWORD('newpass') -> WHERE User='bob' AND Host='%.loc.gov'; mysql> FLUSH PRIVILEGES;
Note: If you are connecting to a MySQL 4.1 or later server
using a pre-4.1 client program, do not use the above UPDATE
statement without reading section 5.5.9 Password Hashing in MySQL 4.1 first. The password
format changed in MySQL 4.1, and under certain circumstances it is
possible that if you change your password, you might not be able to
connect to the server afterward.
ANALYZE TABLE
SyntaxANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
This statement analyzes and stores the key distribution for a table.
During the analysis, the table is locked with a read lock. This works on
MyISAM
and BDB
tables and (as of MySQL 4.0.13) InnoDB
tables. For MyISAM
tables, this
statement is equivalent to using myisamchk -a
.
MySQL uses the stored key distribution to decide the order in which tables should be joined when you perform a join on something other than a constant.
The statement returns a table with the following columns:
Column | Value |
Table | The table name |
Op | Always analyze
|
Msg_type | One of status , error , info , or warning
|
Msg_text | The message |
You can check the stored key distribution with the SHOW INDEX
statement.
See section 13.5.4.11 SHOW INDEX
Syntax.
If the table hasn't changed since the last ANALYZE TABLE
statement,
the table will not be analyzed again.
Before MySQL 4.1.1, ANALYZE TABLE
statements are not written
to the binary log. As of MySQL 4.1.1, they are written to the binary
log unless the optional NO_WRITE_TO_BINLOG
keyword
(or its alias LOCAL
) is used.
BACKUP TABLE
SyntaxBACKUP TABLE tbl_name [, tbl_name] ... TO '/path/to/backup/directory'
Note: This statement is deprecated. We are working on a better
replacement for it that will provide online backup capabilities.
In the meantime, the mysqlhotcopy
script can be used instead.
BACKUP TABLE
copies to the backup directory the minimum number of
table files needed to restore the table, after flushing any buffered changes
to disk. The statement works only for MyISAM
tables. It copies the
`.frm' definition and `.MYD' data files. The `.MYI'
index file can be rebuilt from those two files.
The directory should be specified as a full pathname.
Before using this statement, please see section 5.7.1 Database Backups.
During the backup, a read lock is held for each table, one at time,
as they are being backed up. If you want to back up several tables as
a snapshot (preventing any of them from being changed during the backup
operation), you must first issue a LOCK TABLES
statement to obtain a read
lock for every table in the group.
The statement returns a table with the following columns:
Column | Value |
Table | The table name |
Op | Always backup
|
Msg_type | One of status , error , info , or warning
|
Msg_text | The message |
BACKUP TABLE
is available in MySQL 3.23.25 and later.
CHECK TABLE
SyntaxCHECK TABLE tbl_name [, tbl_name] ... [option] ... option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
Checks a table or tables for errors. CHECK TABLE
works for
MyISAM
and InnoDB
tables. For MyISAM
tables, the key statistics are updated.
As of MySQL 5.0.2, CHECK TABLE
also can check views for problems, such
as tables that are referenced in the view definition that no longer exist.
The CHECK TABLE
statement returns a table with the following columns:
Column | Value |
Table | The table name |
Op | Always check
|
Msg_type | One of status , error , info , or warning
|
Msg_text | The message |
Note that the statement might produce many rows of information for each
checked table. The last row will have a Msg_type
value of
status
and the Msg_text
normally should be OK
. If you
don't get OK
, or Table is already up to date
you should
normally run a repair of the table. See section 5.7.2 Table Maintenance and Crash Recovery. Table is
already up to date
means that the storage engine for the table indicated
that there was no need to check the table.
The different check options that can be given are shown in the following
table. These options apply only to checking MyISAM
tables and are
ignored for InnoDB
tables and views.
Type | Meaning |
QUICK | Don't scan the rows to check for incorrect links. |
FAST | Only check tables that haven't been closed properly. |
CHANGED | Only check tables that have been changed since the last check or haven't been closed properly. |
MEDIUM | Scan rows to verify that deleted links are okay. This also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys. |
EXTENDED | Do a full key lookup for all keys for each row. This ensures that the table is 100% consistent, but will take a long time! |
If none of the options QUICK
, MEDIUM
, or EXTENDED
are
specified, the default check type for dynamic-format MyISAM
tables
is MEDIUM
. This is the same thing as running myisamchk
--medium-check tbl_name
on the table. The default check type
also is MEDIUM
for static-format MyISAM
tables, unless
CHANGED
or FAST
is specified. In that case, the default is
QUICK
. The row scan is skipped for CHANGED
and FAST
because the rows are very seldom corrupted.
You can combine check options, as in the following example, which does a quick check on the table to see whether it was closed properly:
CHECK TABLE test_table FAST QUICK;
Note: In some cases, CHECK TABLE
will change the
table! This happens if the table is marked as ``corrupted'' or ``not
closed properly'' but CHECK TABLE
doesn't find any problems in the
table. In this case, CHECK TABLE
marks the table as okay.
If a table is corrupted, it's most likely that the problem is in the indexes and not in the data part. All of the preceding check types check the indexes thoroughly and should thus find most errors.
If you just want to check a table that you assume is okay, you should use
no check options or the QUICK
option. The latter should be used
when you are in a hurry and can take the very small risk that
QUICK
doesn't find an error in the data file. (In most cases,
MySQL should find, under normal usage, any error in the data file.
If this happens, the table is marked as ``corrupted'' and cannot
be used until it's repaired.)
FAST
and CHANGED
are mostly intended to be used from a
script (for example, to be executed from cron
) if you want to check your
table from time to time. In most cases, FAST
is to be preferred
over CHANGED
. (The only case when it isn't preferred is when you suspect
that you have found a bug in the MyISAM
code.)
EXTENDED
is to be used only after you have run a normal check but
still get strange errors from a table when MySQL tries to
update a row or find a row by key. (This is very unlikely if a
normal check has succeeded!)
Some problems reported by CHECK TABLE
can't be corrected automatically:
Found row where the auto_increment column has the value 0
.
This means that you have a row in the table where the
AUTO_INCREMENT
index column contains the value 0.
(It's possible to create a row where the AUTO_INCREMENT
column is 0 by
explicitly setting the column to 0 with an UPDATE
statement.)
This isn't an error in itself, but could cause trouble if you decide to
dump the table and restore it or do an ALTER TABLE
on the
table. In this case, the AUTO_INCREMENT
column will change value
according to the rules of AUTO_INCREMENT
columns, which could cause
problems such as a duplicate-key error.
To get rid of the warning, just execute an UPDATE
statement
to set the column to some other value than 0.
CHECKSUM TABLE
SyntaxCHECKSUM TABLE tbl_name [, tbl_name] ... [ QUICK | EXTENDED ]
Reports a table checksum.
If QUICK
is specified, the live table checksum is reported if it is
available, or NULL
otherwise. This is very fast. A live checksum
is enabled by specifying the CHECKSUM=1
table option, currently
supported only for MyISAM
tables.
See section 13.2.6 CREATE TABLE
Syntax.
In EXTENDED
mode the whole table is read row by row and the checksum
is calculated. This can be very slow for large tables.
By default, if neither QUICK
nor EXTENDED
is specified, MySQL
returns a live checksum if the table storage engine supports it and scans
the table otherwise.
This statement is implemented in MySQL 4.1.1.
OPTIMIZE TABLE
SyntaxOPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
OPTIMIZE TABLE
should be used if you have deleted a large part of a
table or if you have made many changes to a table with variable-length rows
(tables that have VARCHAR
, BLOB
, or TEXT
columns).
Deleted records are maintained in a linked list and subsequent INSERT
operations reuse old record positions. You can use OPTIMIZE TABLE
to
reclaim the unused space and to defragment the data file.
In most setups, you need not run OPTIMIZE TABLE
at all. Even
if you do a lot of updates to variable-length rows, it's not likely that
you need to do this more than once a week or month and only on certain
tables.
For the moment, OPTIMIZE TABLE
works only on MyISAM
,
BDB
and InnoDB
tables. For BDB
tables, OPTIMIZE
TABLE
is currently mapped to ANALYZE TABLE
. It was also the case for
InnoDB
tables before MySQL 4.1.3; starting from this version it is
mapped to ALTER TABLE
.
See section 13.5.2.1 ANALYZE TABLE
Syntax.
You can get OPTIMIZE TABLE
to work on other table types by starting
mysqld
with the --skip-new
or --safe-mode
option;
in this case, OPTIMIZE TABLE
is just mapped to ALTER TABLE
.
OPTIMIZE TABLE
works as follows:
Note that MySQL locks the table during the time OPTIMIZE TABLE
is
running.
Before MySQL 4.1.1, OPTIMIZE TABLE
statements are not written
to the binary log. As of MySQL 4.1.1, they are written to the binary
log unless the optional NO_WRITE_TO_BINLOG
keyword
(or its alias LOCAL
) is used.
REPAIR TABLE
SyntaxREPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]
REPAIR TABLE
repairs a possibly corrupted table.
By default,
it has the same effect as myisamchk --recover tbl_name
.
REPAIR TABLE
works only on MyISAM
tables.
Normally you should never have to run this statement. However, if disaster
strikes, REPAIR TABLE
is very likely to get back all your data from
a MyISAM
table. If your tables become corrupted often, you should try
to find the reason for it, to eliminate the need to use REPAIR TABLE
.
See section A.4.2 What to Do If MySQL Keeps Crashing. See section 14.1.4 MyISAM
Table Problems.
The statement returns a table with the following columns:
Column | Value |
Table | The table name |
Op | Always repair
|
Msg_type | One of status , error , info , or warning
|
Msg_text | The message |
The REPAIR TABLE
statement might produce many rows of information
for each repaired table. The last row will have a Msg_type
value of
status
and Msg_test
normally should be OK
. If you
don't get OK
, you should try repairing the table with myisamchk
--safe-recover
, because REPAIR TABLE
does not yet implement all the
options of myisamchk
. We plan to make it more flexible in the future.
If QUICK
is given, REPAIR TABLE
tries to repair
only the index tree. This type of repair is like that done by
myisamchk --recover --quick
.
If you use EXTENDED
, MySQL creates the index row by row instead of
creating one index at a time with sorting. (Before MySQL 4.1, this might
be better than sorting on fixed-length keys if you have long CHAR
keys that compress very well.) This type of repair is like that done by
myisamchk --safe-recover
.
As of MySQL 4.0.2, there is a USE_FRM
mode for REPAIR TABLE
.
Use it if the `.MYI' index file is missing or if its header is
corrupted. In this mode, MySQL will re-create the `.MYI' file using
information from the `.frm' file. This kind of repair cannot be done
with myisamchk
. Note: Use this mode only if you cannot
use regular REPAIR
modes. `.MYI' header contains important
table metadata (in particular, current AUTO_INCREMENT
value
and Delete link
) that will be lost in REPAIR ... USE_FRM
.
Don't use USE_FRM
if the table is compressed, as this information
is also stored in `.MYI' file.
Before MySQL 4.1.1, REPAIR TABLE
statements are not written
to the binary log. As of MySQL 4.1.1, they are written to the binary
log unless the optional NO_WRITE_TO_BINLOG
keyword
(or its alias LOCAL
) is used.
Warning: If the server dies during a REPAIR TABLE
operation,
it's essential after restarting it that you immediately execute another
REPAIR TABLE
statement for the table before performing any other
operations on it. (It's always good to start by making a backup.) In the
worst case, you might have a new clean index file without information about
the data file, and then the next operation you perform could overwrite the
data file. This is an unlikely, but possible scenario.
RESTORE TABLE
SyntaxRESTORE TABLE tbl_name [, tbl_name] ... FROM '/path/to/backup/directory'
Restores the table or tables from a backup that was made with BACKUP
TABLE
. Existing tables will not be overwritten; if you try to restore over
an existing table, you will get an error. Just as BACKUP TABLE
,
RESTORE TABLE
currently works only for MyISAM
tables.
The directory should be specified as a full pathname.
The backup for each table consists of its `.frm' format file and `.MYD' data file. The restore operation restores those files, then uses them to rebuild the `.MYI' index file. Restoring takes longer than backing up due to the need to rebuild the indexes. The more indexes the table has, the longer it will take.
The statement returns a table with the following columns:
Column | Value |
Table | The table name |
Op | Always restore
|
Msg_type | One of status , error , info , or warning
|
Msg_text | The message |
SET
SyntaxSET variable_assignment [, variable_assignment] ... variable_assignment: user_var_name = expr | [GLOBAL | SESSION] system_var_name = expr | @@[global. | session.]system_var_name = expr
SET
sets different types of variables that affect the operation of the
server or your client. It can be used to assign values to user variables or
system variables.
The SET PASSWORD
statement for assigning account passwords is
described in
See section 13.5.1.3 SET PASSWORD
Syntax.
In MySQL 4.0.3, we added the GLOBAL
and SESSION
options
and allowed most important system variables to be changed dynamically at
runtime.
The system variables that you can set at runtime are described in
section 5.2.3.1 Dynamic System Variables.
In older versions of MySQL, SET OPTION
is used instead of SET
,
but this is now deprecated; just leave out the word OPTION
.
The following example show the different syntaxes you can use to set variables.
A user variable is written as @var_name
and can be set as follows:
SET @var_name = expr;
Further information about user variables is given in section 9.3 User Variables.
System variables can be referred to in SET
statements as
var_name. The name optionally can be preceded by GLOBAL
or
@@global.
to indicate explicitly that the variable is a global
variable, or by SESSION
, @@session.
, or @@
to
indicate that it is a session variable. LOCAL
and @@local.
are synonyms for SESSION
and @@session.
. If no modifier is
present, SET
sets the session variable.
The @@var_name
syntax for system variables is supported to make
MySQL syntax compatible with some other database systems.
If you set several system variables in the same statement, the last used
GLOBAL
or SESSION
option is used for variables that have no mode
specified.
SET sort_buffer_size=10000; SET @@local.sort_buffer_size=10000; SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000; SET @@sort_buffer_size=1000000; SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;
If you set a system variable using SESSION
(the default), the value
remains in effect until the current session ends or until you set the
variable to a different value. If you set a system variable using
GLOBAL
, which requires the SUPER
privilege, the value is
remembered and used for new connections until the server restarts. If you
want to make a variable setting permanent, you should put it in an option
file. See section 4.3.2 Using Option Files.
To prevent incorrect usage, MySQL produces an error if you use SET
GLOBAL
with a variable that can only be used with SET SESSION
or if
you do not specify GLOBAL
when setting a global variable.
If you want to set a SESSION
variable to the GLOBAL
value or a
GLOBAL
value to the compiled-in MySQL default value, you can set it to
DEFAULT
. For example, the following two statements are identical in
setting the session value of max_join_size
to the global value:
SET max_join_size=DEFAULT; SET @@session.max_join_size=@@global.max_join_size;
You can get a list of most system variables with SHOW VARIABLES
.
See section 13.5.4.19 SHOW VARIABLES
Syntax.
To get a specific variable name or list of names that match a pattern, use a
LIKE
clause:
SHOW VARIABLES LIKE 'max_join_size'; SHOW GLOBAL VARIABLES LIKE 'max_join_size';
You can also get the value for a specific value by using the
@@[global.|local.]var_name
syntax with SELECT
:
SELECT @@max_join_size, @@global.max_join_size;
When you retrieve a variable with SELECT @@var_name
(that is, you
do not specify global.
, session.
, or local.
), MySQL returns
the SESSION
value if it exists and the GLOBAL
value otherwise.
The following list describes variables that have non-standard syntax or that are
not described in the list of system variables that is found in
section 5.2.3 Server System Variables.
Although these variables are not displayed by SHOW VARIABLES
, you can
obtain their values with SELECT
(with the exception of CHARACTER
SET
and SET NAMES
). For example:
mysql> SELECT @@AUTOCOMMIT; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+
AUTOCOMMIT = {0 | 1}
1
, all changes to a table take effect immediately.
If set to 0
, you have to use COMMIT
to accept a transaction or
ROLLBACK
to cancel it. If you change AUTOCOMMIT
mode from 0
to 1
, MySQL performs an automatic COMMIT
of any open transaction. Another way to begin a transaction is to use a
START TRANSACTION
or BEGIN
statement.
See section 13.4.1 START TRANSACTION
, COMMIT
, and ROLLBACK
Syntax.
BIG_TABLES = {0 | 1}
1
, all temporary tables are stored on disk rather than in
memory. This is a little slower, but the error The table tbl_name is
full
will not occur for SELECT
operations that require a large
temporary table. The default value for a new connection is
0
(use in-memory temporary tables). As of MySQL 4.0, you
should normally never need to set this variable, because MySQL automatically
converts in-memory tables to disk-based tables as necessary. This variable
previously was named SQL_BIG_TABLES
.
CHARACTER SET {charset_name | DEFAULT}
cp1251_koi8
, but you can add new mappings by editing the
`sql/convert.cc' file in the MySQL source distribution. As of MySQL
4.1.1, SET CHARACTER SET
sets three session system variables:
character_set_client
and character_set_results
are set to
the given character set, and character_set_connection
to the value
of character_set_database
.
The default mapping can be restored by using a value of DEFAULT
.
Note that the syntax for SET CHARACTER SET
differs
from that for setting most other options.
FOREIGN_KEY_CHECKS = {0 | 1}
1
(the default), foreign key constraints for InnoDB
tables are checked. If set to 0
, they are ignored. Disabling foreign
key checking can be useful for reloading InnoDB
tables in an order
different than that required by their parent/child relationships.
This variable was added in MySQL 3.23.52.
See section 15.7.4 FOREIGN KEY
Constraints.
IDENTITY = value
LAST_INSERT_ID
variable.
It exists for compatibility with other databases.
As of MySQL 3.23.25, you can read its value with SELECT @@IDENTITY
.
As of MySQL 4.0.3, you can also set its value with SET IDENTITY
.
INSERT_ID = value
INSERT
or ALTER TABLE
statement when inserting an AUTO_INCREMENT
value. This is mainly used
with the binary log.
LAST_INSERT_ID = value
LAST_INSERT_ID()
. This is stored in
the binary log when you use LAST_INSERT_ID()
in a statement that updates
a table. Setting this variable does not update theh value returned by the
mysql_insert_id()
C API function.
NAMES {'charset_name' | DEFAULT}
SET NAMES
sets the three session system variables
character_set_client
, character_set_connection
, and
character_set_results
to the given character set.
The default mapping can be restored by using a value of DEFAULT
.
Note that the syntax for SET NAMES
differs
from that for setting most other options.
This statement is available as of MySQL 4.1.0.
SQL_AUTO_IS_NULL = {0 | 1}
1
(the default), you can find the last inserted row for a table
that contains an AUTO_INCREMENT
column by using the following construct:
WHERE auto_increment_column IS NULLThis behavior is used by some ODBC programs, such as Access.
SQL_AUTO_IS_NULL
was added in MySQL 3.23.52.
SQL_BIG_SELECTS = {0 | 1}
0
, MySQL aborts SELECT
statements
that probably will take a very long time (that is, statements for which
the optimizer estimates that the number of examined rows will
exceed the value of max_join_size
).
This is useful when an inadvisable WHERE
statement has been
issued. The default value for a new connection is 1
, which allows
all SELECT
statements.
If you set the max_join_size
system variable to a value other than
DEFAULT
, SQL_BIG_SELECTS
will be set to 0
.
SQL_BUFFER_RESULT = {0 | 1}
SQL_BUFFER_RESULT
forces results from SELECT
statements
to be put into temporary tables. This helps MySQL free the
table locks early and can be beneficial in cases where it takes a long time to
send results to the client.
This variable was added in MySQL 3.23.13.
SQL_LOG_BIN = {0 | 1}
0
, no logging is done to the binary log for the client.
The client must have the SUPER
privilege to set this option.
This variable was added in MySQL 3.23.16.
SQL_LOG_OFF = {0 | 1}
1
, no logging is done to the general query log for this
client. The client must have the SUPER
privilege to set this option.
SQL_LOG_UPDATE = {0 | 1}
0
, no logging is done to the update log for the client.
The client must have the SUPER
privilege to set this option.
This variable was added in MySQL 3.22.5.
Starting from MySQL 5.0.0, it is deprecated and is mapped to
SQL_LOG_BIN
(see section D.1.4 Changes in release 5.0.0 (22 Dec 2003: Alpha)).
SQL_QUOTE_SHOW_CREATE = {0 | 1}
1
, SHOW CREATE TABLE
quotes table and column
names. If set to 0
, quoting is disabled. This option is enabled
by default so that replication will work for tables with table and column
names that require quoting.
This variable was added in MySQL 3.23.26.
section 13.5.4.5 SHOW CREATE TABLE
Syntax.
SQL_SAFE_UPDATES = {0 | 1}
1
, MySQL aborts UPDATE
or DELETE
statements
that do not use a key in the WHERE
clause or a LIMIT
clause.
This makes it possible to catch UPDATE
or DELETE
statements
where keys are not used properly and that would probably change or delete a
large number of rows.
This variable was added in MySQL 3.22.32.
SQL_SELECT_LIMIT = {value | DEFAULT}
SELECT
statements. The
default value for a new connection is ``unlimited.'' If you have changed the
limit, the default value can be restored by using a SQL_SELECT_LIMIT
value of DEFAULT
.
If a SELECT
has a LIMIT
clause, the LIMIT
takes
precedence over the value of SQL_SELECT_LIMIT
.
SQL_WARNINGS = {0 | 1}
INSERT
statements produce
an information string if warnings occur. The default is 0. Set the value
to 1 to produce an information string.
This variable was added in MySQL 3.22.11.
TIMESTAMP = {timestamp_value | DEFAULT}
timestamp_value
should be a
Unix epoch timestamp, not a MySQL timestamp.
UNIQUE_CHECKS = {0 | 1}
1
(the default), uniqueness checks for secondary indexes in
InnoDB
tables are performed. If set to 0
, uniqueness
checks are not done for index entries inserted into InnoDB's insert
buffer. If you know for certain that your data does not contain
uniqueness violations, you can set this to 0 to speed up large table
imports to InnoDB. This variable was added in MySQL 3.23.52.
SHOW
Syntax
SHOW
has many forms that provide information about databases,
tables, columns, or status information about the server.
This section describes those following:
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern'] SHOW CREATE DATABASE db_name SHOW CREATE TABLE tbl_name SHOW DATABASES [LIKE 'pattern'] SHOW [STORAGE] ENGINES SHOW ERRORS [LIMIT [offset,] row_count] SHOW GRANTS FOR user SHOW INDEX FROM tbl_name [FROM db_name] SHOW INNODB STATUS SHOW [BDB] LOGS SHOW PRIVILEGES SHOW [FULL] PROCESSLIST SHOW STATUS [LIKE 'pattern'] SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern'] SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern'] SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern'] SHOW WARNINGS [LIMIT [offset,] row_count]
If the syntax for a given SHOW
statement includes a LIKE
'pattern'
part, 'pattern'
is a string that can contain the SQL `%'
and `_' wildcard characters.
The pattern is useful for restricting statement output to matching values.
Note that there are other forms of these statements described elsewhere:
SHOW
statement has forms that provide information about
replication master and slave servers:
SHOW BINLOG EVENTS SHOW MASTER LOGS SHOW MASTER STATUS SHOW SLAVE HOSTS SHOW SLAVE STATUSThese forms of
SHOW
are described in section 13.6 Replication Statements.
SHOW CHARACTER SET
SyntaxSHOW CHARACTER SET [LIKE 'pattern']
The SHOW CHARACTER SET
statement shows all available character sets.
It takes an optional LIKE
clause that indicates which character set
names to match. For example:
mysql> SHOW CHARACTER SET LIKE 'latin%'; +---------+-----------------------------+-------------------+--------+ | Charset | Description | Default collation | Maxlen | +---------+-----------------------------+-------------------+--------+ | latin1 | ISO 8859-1 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | +---------+-----------------------------+-------------------+--------+
The Maxlen
column shows the maximum number of bytes used to
store one character.
SHOW CHARACTER SET
is available as of MySQL 4.1.0.
SHOW COLLATION
SyntaxSHOW COLLATION [LIKE 'pattern']
The output from SHOW COLLATION
includes all available character
sets.
It takes an optional LIKE
clause that indicates which collation
names to match. For example:
mysql> SHOW COLLATION LIKE 'latin1%'; +-------------------+---------+----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +-------------------+---------+----+---------+----------+---------+ | latin1_german1_ci | latin1 | 5 | | | 0 | | latin1_swedish_ci | latin1 | 8 | Yes | Yes | 0 | | latin1_danish_ci | latin1 | 15 | | | 0 | | latin1_german2_ci | latin1 | 31 | | Yes | 2 | | latin1_bin | latin1 | 47 | | Yes | 0 | | latin1_general_ci | latin1 | 48 | | | 0 | | latin1_general_cs | latin1 | 49 | | | 0 | | latin1_spanish_ci | latin1 | 94 | | | 0 | +-------------------+---------+----+---------+----------+---------+
The Default
column indicates whether a collation is the
default for its character set.
Compiled
indicates whether the character set is
compiled into the server.
Sortlen
is related to the amount of memory required to sort strings
expressed in the character set.
SHOW COLLATION
is available as of MySQL 4.1.0.
SHOW COLUMNS
SyntaxSHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']
SHOW COLUMNS
lists the columns in a given table. If the column types
differ from what you expect them to be based on your CREATE TABLE
statement, note that MySQL sometimes changes column types when you create or
alter a table. The conditions for which this occurs are described in
section 13.2.6.1 Silent Column Specification Changes.
The FULL
keyword can be used from MySQL 3.23.32 on. It causes the
output to include the privileges you have for each column. As of MySQL 4.1,
FULL
also causes any per-column comments to be displayed.
You can use db_name.tbl_name as an alternative to the tbl_name
FROM db_name
syntax. These two statements are equivalent:
mysql> SHOW COLUMNS FROM mytable FROM mydb; mysql> SHOW COLUMNS FROM mydb.mytable;
SHOW FIELDS
is a synonym for SHOW COLUMNS
. You can also
list a table's columns with the mysqlshow db_name tbl_name
command.
The DESCRIBE
statement provides information similar to
SHOW COLUMNS
.
See section 13.3.1 DESCRIBE
Syntax (Get Information About Columns).
SHOW CREATE DATABASE
SyntaxSHOW CREATE {DATABASE | SCHEMA} db_name
Shows a CREATE DATABASE
statement that will create the given
database. It was added in MySQL 4.1. SHOW CREATE SCHEMA
can be used
as of MySQL 5.0.2.
mysql> SHOW CREATE DATABASE test\G *************************** 1. row *************************** Database: test Create Database: CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */
SHOW CREATE TABLE
SyntaxSHOW CREATE TABLE tbl_name
Shows a CREATE TABLE
statement that will create the given table.
It was added in MySQL 3.23.20.
mysql> SHOW CREATE TABLE t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE t ( id INT(11) default NULL auto_increment, s char(60) default NULL, PRIMARY KEY (id) ) TYPE=MyISAM
SHOW CREATE TABLE
quotes table and column names according to
the value of the SQL_QUOTE_SHOW_CREATE
option.
section 13.5.3 SET
Syntax.
SHOW CREATE VIEW
SyntaxSHOW CREATE VIEW view_name
This statement shows a CREATE VIEW
statement that will create
the given view.
mysql> SHOW CREATE VIEW v; +-------+----------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------+ | v | CREATE VIEW `test`.`v` AS select 1 AS `a`,2 AS `b` | +-------+----------------------------------------------------+
This statement was added in MySQL 5.0.1.
SHOW DATABASES
SyntaxSHOW {DATABASES | SCHEMAS} [LIKE 'pattern']
SHOW DATABASES
lists the databases on the MySQL server host.
You can also get this list using the mysqlshow
command.
As of MySQL 4.0.2, you will see only those databases for which you have some
kind of privilege, if you don't have the global SHOW DATABASES
privilege.
If the server was started with the --skip-show-database
option, you
cannot use this statement at all unless you have the SHOW DATABASES
privilege.
SHOW SCHEMAS
can be used as of MySQL 5.0.2
SHOW ENGINES
SyntaxSHOW [STORAGE] ENGINES
SHOW ENGINES
shows you status information about the storage engines.
This is particularly useful for checking whether a storage engine is
supported, or to see what the default engine is.
This statement is implemented in MySQL 4.1.2.
SHOW TABLE TYPES
is a deprecated synonym.
mysql> SHOW ENGINES\G *************************** 1. row *************************** Engine: MyISAM Support: DEFAULT Comment: Default engine as of MySQL 3.23 with great performance *************************** 2. row *************************** Engine: HEAP Support: YES Comment: Alias for MEMORY *************************** 3. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables *************************** 4. row *************************** Engine: MERGE Support: YES Comment: Collection of identical MyISAM tables *************************** 5. row *************************** Engine: MRG_MYISAM Support: YES Comment: Alias for MERGE *************************** 6. row *************************** Engine: ISAM Support: NO Comment: Obsolete storage engine, now replaced by MyISAM *************************** 7. row *************************** Engine: MRG_ISAM Support: NO Comment: Obsolete storage engine, now replaced by MERGE *************************** 8. row *************************** Engine: InnoDB Support: YES Comment: Supports transactions, row-level locking, and foreign keys *************************** 9. row *************************** Engine: INNOBASE Support: YES Comment: Alias for INNODB *************************** 10. row *************************** Engine: BDB Support: YES Comment: Supports transactions and page-level locking *************************** 11. row *************************** Engine: BERKELEYDB Support: YES Comment: Alias for BDB *************************** 12. row *************************** Engine: NDBCLUSTER Support: YES Comment: Clustered, fault-tolerant, memory-based tables *************************** 13. row *************************** Engine: NDB Support: YES Comment: Alias for NDBCLUSTER *************************** 14. row *************************** Engine: EXAMPLE Support: YES Comment: Example storage engine *************************** 15. row *************************** Engine: ARCHIVE Support: YES Comment: Archive storage engine *************************** 16. row *************************** Engine: CSV Support: YES Comment: CSV storage engine *************************** 17. row *************************** Engine: FEDERATED Support: YES Comment: Federated MySQL storage engine
A Support
value indicates whether the particular storage engine is
supported, and which is the default engine. For example, if the server
is started with the --default-table-type=InnoDB
option, then
the Support
value for the InnoDB
row will have the value
DEFAULT
.
SHOW ERRORS
SyntaxSHOW ERRORS [LIMIT [offset,] row_count] SHOW COUNT(*) ERRORS
This statement is similar to SHOW WARNINGS
, except that instead
of displaying errors, warnings, and notes, it displays only errors.
SHOW ERRORS
is available as of MySQL 4.1.0.
The LIMIT
clause has the same syntax as for the SELECT
statement.
See section 13.1.7 SELECT
Syntax.
The SHOW COUNT(*) ERRORS
statement displays the number of errors.
You can also retrieve this number from the error_count
variable:
SHOW COUNT(*) ERRORS; SELECT @@error_count;
For more information, see section 13.5.4.20 SHOW WARNINGS
Syntax.
SHOW GRANTS
SyntaxSHOW GRANTS FOR user
This statement lists the GRANT
statements that must be issued to
duplicate the privileges for a MySQL user account.
mysql> SHOW GRANTS FOR 'root'@'localhost'; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+
As of MySQL 4.1.2, to list privileges for the current session, you can use any of the following statements:
SHOW GRANTS; SHOW GRANTS FOR CURRENT_USER; SHOW GRANTS FOR CURRENT_USER();
Before MySQL 4.1.2, you can find out what user the
session was authenticated as by selecting the value
of the CURRENT_USER()
function (new in MySQL 4.0.6).
Then use that value in the SHOW GRANTS
statement.
See section 12.8.3 Information Functions.
SHOW GRANTS
is available as of MySQL 3.23.4.
SHOW INDEX
SyntaxSHOW INDEX FROM tbl_name [FROM db_name]
SHOW INDEX
returns table index information in a format that
resembles the SQLStatistics
call in ODBC.
SHOW INDEX
returns the following fields:
Table
Non_unique
Key_name
Seq_in_index
Column_name
Collation
NULL
(Not sorted).
Cardinality
ANALYZE TABLE
or myisamchk -a
. Cardinality
is counted
based on statistics stored as integers, so it's not necessarily accurate for
small tables. The higher the cardinality, the greater the chance that MySQL
will use the index when doing joins.
Sub_part
NULL
if the entire column is indexed.
Packed
NULL
if it is not.
Null
YES
if the column may contain NULL
, ''
if not.
Index_type
BTREE
, FULLTEXT
, HASH
,
RTREE
).
Comment
Index_type
column was
added, Comment
indicates whether an index is FULLTEXT
.
The Packed
and Comment
columns were added in MySQL 3.23.0.
The Null
and Index_type
columns were added in MySQL 4.0.2.
You can use db_name.tbl_name as an alternative to the tbl_name
FROM db_name
syntax. These two statements are equivalent:
mysql> SHOW INDEX FROM mytable FROM mydb; mysql> SHOW INDEX FROM mydb.mytable;
SHOW KEYS
is a synonym for SHOW INDEX
. You can also list
a table's indexes with the mysqlshow -k db_name tbl_name
command.
SHOW INNODB STATUS
SyntaxSHOW INNODB STATUS
This statement shows extensive information about the state of the
InnoDB
storage engine.
SHOW LOGS
SyntaxSHOW [BDB] LOGS
SHOW LOGS
displays status information about existing log files.
It was implemented in MySQL 3.23.29. Currently, it displays only information
about Berkeley DB log files, so an alias for it (available as of MySQL
4.1.1) is SHOW BDB LOGS
.
SHOW LOGS
returns the following fields:
File
Type
BDB
for Berkeley DB log files).
Status
FREE
if the file can be removed, or
IN USE
if the file is needed by the transaction subsystem)
SHOW PRIVILEGES
SyntaxSHOW PRIVILEGES
SHOW PRIVILEGES
shows the list of system privileges that the underlying
MySQL server supports.
This statement is implemented as of MySQL 4.1.0.
mysql> SHOW PRIVILEGES\G *************************** 1. row *************************** Privilege: Select Context: Tables Comment: To retrieve rows from table *************************** 2. row *************************** Privilege: Insert Context: Tables Comment: To insert data into tables *************************** 3. row *************************** Privilege: Update Context: Tables Comment: To update existing rows *************************** 4. row *************************** Privilege: Delete Context: Tables Comment: To delete existing rows *************************** 5. row *************************** Privilege: Index Context: Tables Comment: To create or drop indexes *************************** 6. row *************************** Privilege: Alter Context: Tables Comment: To alter the table *************************** 7. row *************************** Privilege: Create Context: Databases,Tables,Indexes Comment: To create new databases and tables *************************** 8. row *************************** Privilege: Drop Context: Databases,Tables Comment: To drop databases and tables *************************** 9. row *************************** Privilege: Grant Context: Databases,Tables Comment: To give to other users those privileges you possess *************************** 10. row *************************** Privilege: References Context: Databases,Tables Comment: To have references on tables *************************** 11. row *************************** Privilege: Reload Context: Server Admin Comment: To reload or refresh tables, logs and privileges *************************** 12. row *************************** Privilege: Shutdown Context: Server Admin Comment: To shutdown the server *************************** 13. row *************************** Privilege: Process Context: Server Admin Comment: To view the plain text of currently executing queries *************************** 14. row *************************** Privilege: File Context: File access on server Comment: To read and write files on the server
SHOW PROCESSLIST
SyntaxSHOW [FULL] PROCESSLIST
SHOW PROCESSLIST
shows you which threads are running.
You can also get this information using the mysqladmin processlist
statement. If you have the SUPER
privilege, you can see all
threads. Otherwise, you can see only your own threads (that is, threads
associated with the MySQL account that you are using).
See section 13.5.5.3 KILL
Syntax.
If you don't use the FULL
keyword, only the first 100
characters of each query are shown.
Starting from MySQL 4.0.12, the statement reports the hostname for TCP/IP
connections in host_name:client_port
format to make it easier to
determine which client is doing what.
This statement is very useful if you get the "too many connections" error
message and want to find out what is going on. MySQL reserves one extra
connection to be used by accounts that have the SUPER
privilege, to
ensure that administrators should always be able to connect and check the
system (assuming that you are not giving this privilege to all your users).
Some states commonly seen in the output from SHOW PROCESSLIST
:
Checking table
Closing tables
Connect Out
Copying to tmp table on disk
tmp_table_size
and the
thread is now changing the temporary table from in-memory to disk-based
format to save memory.
Creating tmp table
deleting from main table
deleting from reference tables
Flushing tables
FLUSH TABLES
and is waiting for all
threads to close their tables.
Killed
Locked
Sending data
SELECT
statement and
also is sending data to the client.
Sorting for group
GROUP BY
.
Sorting for order
ORDER BY
.
Opening tables
ALTER TABLE
or a
LOCK TABLE
statement can prevent opening a table until the statement
is finished.
Removing duplicates
SELECT DISTINCT
in such a way that MySQL
couldn't optimize away the distinct operation at an early stage. Because of
this, MySQL requires an extra stage to remove all duplicated rows before
sending the result to the client.
Reopen table
Repair by sorting
Repair with keycache
Repair by sorting
.
Searching rows for update
UPDATE
is changing
the index that is used to find the involved rows.
Sleeping
System lock
mysqld
servers that are accessing
the same tables, you can disable system locks with the
--skip-external-locking
option.
Upgrading lock
INSERT DELAYED
handler is trying to get a lock for the table
to insert rows.
Updating
User Lock
GET_LOCK()
.
Waiting for tables
FLUSH TABLES
or one of the following statements on the table in question: FLUSH
TABLES tbl_name
, ALTER TABLE
, RENAME TABLE
,
REPAIR TABLE
, ANALYZE TABLE
, or OPTIMIZE TABLE
.
waiting for handler insert
INSERT DELAYED
handler has processed all pending inserts and is
waiting for new ones.
Most states correspond to very quick operations. If a thread stays in any of these states for many seconds, there might be a problem that needs to be investigated.
There are some other states that are not mentioned in the preceding list, but many of them are useful only for finding bugs in the server.
SHOW STATUS
SyntaxSHOW STATUS [LIKE 'pattern']
SHOW STATUS
provides server status information. This information
also can be obtained using the mysqladmin extended-status
command.
Partial output is shown here. The list of variables and their values may be different for your server. The meaning of each variable is given in See section 5.2.4 Server Status Variables.
mysql> SHOW STATUS; +--------------------------+------------+ | Variable_name | Value | +--------------------------+------------+ | Aborted_clients | 0 | | Aborted_connects | 0 | | Bytes_received | 155372598 | | Bytes_sent | 1176560426 | | Connections | 30023 | | Created_tmp_disk_tables | 0 | | Created_tmp_tables | 8340 | | Created_tmp_files | 60 | ... | Open_tables | 1 | | Open_files | 2 | | Open_streams | 0 | | Opened_tables | 44600 | | Questions | 2026873 | ... | Table_locks_immediate | 1920382 | | Table_locks_waited | 0 | | Threads_cached | 0 | | Threads_created | 30022 | | Threads_connected | 1 | | Threads_running | 1 | | Uptime | 80380 | +--------------------------+------------+
With a LIKE
clause, the statement displays only those variables that
match the pattern:
mysql> SHOW STATUS LIKE 'Key%'; +--------------------+----------+ | Variable_name | Value | +--------------------+----------+ | Key_blocks_used | 14955 | | Key_read_requests | 96854827 | | Key_reads | 162040 | | Key_write_requests | 7589728 | | Key_writes | 3813196 | +--------------------+----------+
SHOW TABLE STATUS
SyntaxSHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
SHOW TABLE STATUS
works likes SHOW TABLE
, but provides a lot
of information about each table. You can also get this list using the
mysqlshow --status db_name
command. This statement was
added in MySQL 3.23. As of MySQL 5.0.1, it also displays information about
views.
SHOW TABLE STATUS
returns the following fields:
Name
Engine
Type
.
See section 14 MySQL Storage Engines and Table Types.
Version
Row_format
Fixed
, Dynamic
, Compressed
).
Rows
MyISAM
and ISAM
, store the
exact count.
For other storage engines, such as InnoDB
, this value is an
approximation, and may vary from the actual value by as much as
40 to 50%. In such cases, use SELECT COUNT(*)
to obtain an
accurate count.
Avg_row_length
Data_length
Max_data_length
Index_length
Data_free
Auto_increment
AUTO_INCREMENT
value.
Create_time
Update_time
Check_time
Collation
Checksum
Create_options
CREATE TABLE
.
Comment
In the table comment, InnoDB
tables will report the free space of
the tablespace to which the table belongs. For a table located in the
shared tablespace, this is the free space of the shared tablespace. If
you are using multiple tablespaces and the table has its own tablespace,
the freespace is for just that table.
For MEMORY
(HEAP
) tables, the Data_length
,
Max_data_length
, and Index_length
values approximate the
actual amount of allocated memory. The allocation algorithm reserves memory
in large amounts to reduce the number of allocation operations.
For views, all the fields displayed by SHOW TABLE STATUS
are
NULL
except that Name
indicates the view name and Comment
says view
.
SHOW TABLES
SyntaxSHOW [FULL|OPEN] TABLES [FROM db_name] [LIKE 'pattern']
SHOW TABLES
lists the non-TEMPORARY
tables in a given
database. You can also get this list using the mysqlshow db_name
command.
Before MySQL 5.0.1, the output from SHOW TABLES
contains a single
column of table names. Beginning with MySQL 5.0.1, also lists the views in
the database. As of MySQL 5.0.2, the FULL
modifier is supported such
that SHOW FULL TABLES
displays a second output column. Values in the
second column are BASE TABLE
for a table and VIEW
for a view.
Note: If you have no privileges for a table, the table
will not show up in the output from SHOW TABLES
or mysqlshow
db_name
.
SHOW OPEN TABLES
lists the tables that are currently open in
the table cache. See section 7.4.8 How MySQL Opens and Closes Tables. The Comment
field in the output
tells how many times the table is cached
and in_use
.
OPEN
can be used from MySQL 3.23.33 on.
SHOW VARIABLES
SyntaxSHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern']
SHOW VARIABLES
shows the values of some MySQL system variables. This
information also can be obtained using the mysqladmin variables
command.
The GLOBAL
and SESSION
options are new in MySQL 4.0.3.
With GLOBAL
, you will get the values that will be used for new
connections to MySQL. With SESSION
, you will get the values that
are in effect for the current connection. If you use neither option, the
default SESSION
. LOCAL
is a synonym for SESSION
.
If the default values are unsuitable, you can set most of these variables
using command-line options when mysqld
starts or at runtime with
the SET
statement.
See section 5.2.1 mysqld
Command-Line Options and section 13.5.3 SET
Syntax.
Partial output is shown here. The list of variables and their values may be different for your server. The meaning of each variable is given in See section 5.2.3 Server System Variables. Information about tuning them is provided in section 7.5.2 Tuning Server Parameters.
mysql> SHOW VARIABLES; +---------------------------------+------------------------------+ | Variable_name | Value | +---------------------------------+------------------------------| | back_log | 50 | | basedir | /usr/local/mysql | | bdb_cache_size | 8388572 | | bdb_log_buffer_size | 32768 | | bdb_home | /usr/local/mysql | ... | max_connections | 100 | | max_connect_errors | 10 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 16777216 | | max_join_size | 4294967295 | | max_relay_log_size | 0 | | max_sort_length | 1024 | ... | timezone | EEST | | tmp_table_size | 33554432 | | tmpdir | /tmp/:/mnt/hd2/tmp/ | | version | 4.0.4-beta | | wait_timeout | 28800 | +---------------------------------+------------------------------+
With a LIKE
clause, the statement displays only those variables that
match the pattern:
mysql> SHOW VARIABLES LIKE 'have%'; +--------------------+----------+ | Variable_name | Value | +--------------------+----------+ | have_bdb | YES | | have_innodb | YES | | have_isam | YES | | have_raid | NO | | have_symlink | DISABLED | | have_openssl | YES | | have_query_cache | YES | +--------------------+----------+
SHOW WARNINGS
SyntaxSHOW WARNINGS [LIMIT [offset,] row_count] SHOW COUNT(*) WARNINGS
SHOW WARNINGS
shows the error, warning, and note messages that
resulted from the last statement that generated messages, or nothing if the
last statement that used a table generated no messages.
This statement is implemented as of MySQL 4.1.0.
A related statement, SHOW ERRORS
, shows only the errors.
See section 13.5.4.9 SHOW ERRORS
Syntax.
The list of messages is reset for each new statement that uses a table.
The SHOW COUNT(*) WARNINGS
statement displays the total number of
errors, warnings, and notes. You can also retrieve this number from the
warning_count
variable:
SHOW COUNT(*) WARNINGS; SELECT @@warning_count;
The value of warning_count
might be greater than the number of
messages displayed by SHOW WARNINGS
if the max_error_count
system variable is set low enough that not all messages are stored. An
example shown later in this section demonstrates how this can happen.
The LIMIT
clause has the same syntax as for the SELECT
statement.
See section 13.1.7 SELECT
Syntax.
The MySQL server sends back the total number of errors, warnings, and
notes resulting from the last statement.
If you are using the C API, this value can be obtained by calling
mysql_warning_count()
.
See section 21.2.3.59 mysql_warning_count()
.
Note that the framework for warnings was added in MySQL 4.1.0, at which
point many statements did not generate warnings. In 4.1.1, the situation is
much improved, with warnings generated for statements such as LOAD
DATA INFILE
and DML statements such as INSERT
, UPDATE
,
CREATE TABLE
, and ALTER TABLE
.
The following DROP TABLE
statement results in a note:
mysql> DROP TABLE IF EXISTS no_such_table; mysql> SHOW WARNINGS; +-------+------+-------------------------------+ | Level | Code | Message | +-------+------+-------------------------------+ | Note | 1051 | Unknown table 'no_such_table' | +-------+------+-------------------------------+
Here is a simple example that shows a syntax warning for CREATE TABLE
and conversion warnings for INSERT
:
mysql> CREATE TABLE t1 (a TINYINT NOT NULL, b CHAR(4)) TYPE=MyISAM; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Warning Code: 1287 Message: 'TYPE=storage_engine' is deprecated, use 'ENGINE=storage_engine' instead 1 row in set (0.00 sec) mysql> INSERT INTO t1 VALUES(10,'mysql'),(NULL,'test'), -> (300,'Open Source'); Query OK, 3 rows affected, 4 warnings (0.01 sec) Records: 3 Duplicates: 0 Warnings: 4 mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Warning Code: 1265 Message: Data truncated for column 'b' at row 1 *************************** 2. row *************************** Level: Warning Code: 1263 Message: Data truncated, NULL supplied to NOT NULL column 'a' at row 2 *************************** 3. row *************************** Level: Warning Code: 1264 Message: Data truncated, out of range for column 'a' at row 3 *************************** 4. row *************************** Level: Warning Code: 1265 Message: Data truncated for column 'b' at row 3 4 rows in set (0.00 sec)
The maximum number of error, warning, and note messages to store is controlled
by the max_error_count
system variable. By default, its value is 64.
To change the number of messages you want stored, change the value of
max_error_count
.
In the following example, the ALTER TABLE
statement produces three
warning messages, but only one is stored because max_error_count
has been set to 1
:
mysql> SHOW VARIABLES LIKE 'max_error_count'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_error_count | 64 | +-----------------+-------+ 1 row in set (0.00 sec) mysql> SET max_error_count=1; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t1 MODIFY b CHAR; Query OK, 3 rows affected, 3 warnings (0.00 sec) Records: 3 Duplicates: 0 Warnings: 3 mysql> SELECT @@warning_count; +-----------------+ | @@warning_count | +-----------------+ | 3 | +-----------------+ 1 row in set (0.01 sec) mysql> SHOW WARNINGS; +---------+------+----------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------+ | Warning | 1263 | Data truncated for column 'b' at row 1 | +---------+------+----------------------------------------+ 1 row in set (0.00 sec)
To disable warnings, set max_error_count
to 0
. In this case,
warning_count
still indicates how many warnings have occurred, but
none of the messages are stored.
CACHE INDEX
SyntaxCACHE INDEX tbl_index_list [, tbl_index_list] ... IN key_cache_name tbl_index_list: tbl_name [[INDEX|KEY] (index_name[, index_name] ...)]
The CACHE INDEX
statement assigns table indexes to a specific key
cache. It is used only for MyISAM
tables.
The following statement assigns indexes from the tables t1
,
t2
, and t3
to the key cache named hot_cache
:
mysql> CACHE INDEX t1, t2, t3 IN hot_cache; +---------+--------------------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+--------------------+----------+----------+ | test.t1 | assign_to_keycache | status | OK | | test.t2 | assign_to_keycache | status | OK | | test.t3 | assign_to_keycache | status | OK | +---------+--------------------+----------+----------+
The syntax of CACHE INDEX
allows you to specify that only particular
indexes from a table should be assigned to the cache. However, the current
implementation assigns all the table's indexes to the cache, so there is no
reason to specify anything other than the table name.
The key cache referred to in a CACHE INDEX
statement can be created
by setting its size with a parameter setting statement or in the server
parameter settings. For example:
mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;
Key cache parameters can be accessed as members of a structured system variable. See section 9.4.1 Structured System Variables.
A key cache must exist before you can assign indexes to it:
mysql> CACHE INDEX t1 IN non_existent_cache; ERROR 1283 (HY000): Unknown key cache 'non_existent_cache'
By default, table indexes are assigned to the main (default) key cache created at the server startup. When a key cache is destroyed, all indexes assigned to it become assigned to the default key cache again.
Index assignment affects the server globally: If one client assigns an index to a given cache, this cache is used for all queries involving the index, no matter what client issues the queries.
CACHE INDEX
was added in MySQL 4.1.1.
FLUSH
SyntaxFLUSH [LOCAL | NO_WRITE_TO_BINLOG] flush_option [, flush_option] ...
You should use the FLUSH
statement if you want to clear some of the
internal caches MySQL uses. To execute FLUSH
, you must have
the RELOAD
privilege.
flush_option can be any of the following:
HOSTS
Host ... is blocked
. When more than
max_connect_errors
errors occur successively for a given host while
connecting to the MySQL server, MySQL assumes that
something is wrong and blocks the host from further connection requests.
Flushing the host tables allows the host to attempt to connect
again. See section A.2.5 Host 'host_name
' is blocked. You can start mysqld
with
--max_connect_errors=999999999
to avoid this error message.
DES_KEY_FILE
--des-key-file
option at server startup time.
LOGS
SIGHUP
signal to the mysqld
server (except on some Mac OS X 10.3 versions
where mysqld
will ignore SIGHUP
and SIGQUIT
).
PRIVILEGES
mysql
database.
QUERY CACHE
RESET QUERY CACHE
.
STATUS
{TABLE | TABLES} [tbl_name [, tbl_name] ...]
FLUSH TABLES
also removes all query results from the query cache, like
the RESET QUERY CACHE
statement.
TABLES WITH READ LOCK
UNLOCK
TABLES
. This is very convenient way to get backups if you have a filesystem
such as Veritas that can take snapshots in time.
USER_RESOURCES
GRANT
and REVOKE
Syntax.
Before MySQL 4.1.1, FLUSH
statements are not written to the binary
log. As of MySQL 4.1.1, they are written to the binary log unless the
optional NO_WRITE_TO_BINLOG
keyword (or its alias LOCAL
) is
used. Exceptions are that FLUSH LOGS
, FLUSH MASTER
,
FLUSH SLAVE
, and FLUSH TABLES WITH READ LOCK
are not logged in
any case because they would cause problems if replicated to a slave.
You can also access some of these statements with the mysqladmin
utility, using the flush-hosts
, flush-logs
, flush-privileges
,
flush-status
, or flush-tables
commands.
Take also a look at the RESET
statement used with replication.
See section 13.5.5.5 RESET
Syntax.
KILL
SyntaxKILL [CONNECTION | QUERY] thread_id
Each connection to mysqld
runs in a separate thread. You can see
which threads are running with the SHOW PROCESSLIST
statement and kill
a thread with the KILL thread_id
statement.
As of MySQL 5.0.0, KILL
allows the optional CONNECTION
or
QUERY
modifiers:
KILL CONNECTION
is the same as KILL
with no modifier:
It terminates the connection associated with the given thread_id.
KILL QUERY
terminates the statement that the connection currently
is executing, but leaves the connection intact.
If you have the PROCESS
privilege, you can see all threads.
If you have the SUPER
privilege, you can kill all threads and
statements. Otherwise, you can see and kill only your own threads and
statements.
You can also use the mysqladmin processlist
and mysqladmin kill
commands to examine and kill threads.
Note: You currently cannot use KILL
with the Embedded MySQL
Server library, because the embedded server merely runs inside the threads
of the host application, it does not create connection threads of its own.
When you do a KILL
, a thread-specific kill flag is set for
the thread.
In most cases, it might take some time for the thread to die, because the kill
flag is checked only at specific intervals:
SELECT
, ORDER BY
and GROUP BY
loops, the flag is
checked after reading a block of rows. If the kill flag is set, the
statement is aborted.
ALTER TABLE
, the kill flag is checked before each block of
rows are read from the original table. If the kill flag was set, the statement
is aborted and the temporary table is deleted.
UPDATE
or DELETE
, the kill flag
is checked after each block read and after each updated or deleted
row. If the kill flag is set, the statement is aborted. Note that if you
are not using transactions, the changes will not be rolled back!
GET_LOCK()
will abort and return NULL
.
INSERT DELAYED
thread will quickly flush (insert) all rows it has in
memory and terminate.
Locked
),
the table lock will be quickly aborted.
REPAIR TABLE
,
CHECK TABLE
, and OPTIMIZE TABLE
cannot be killed before
MySQL 4.1 and run to completion. This now is changed: REPAIR TABLE
and OPTIMIZE TABLE
can be killed as of MySQL 4.1.0, as can CHECK TABLE
as of MySQL 4.1.3.
However, killing a REPAIR TABLE
or OPTIMIZE TABLE
operation
on a MyISAM
table results in a table that IS corrupted and will be
unusable (reads and writes to it will fail) until you optimize or repair it
again.
LOAD INDEX INTO CACHE
SyntaxLOAD INDEX INTO CACHE tbl_index_list [, tbl_index_list] ... tbl_index_list: tbl_name [[INDEX|KEY] (index_name[, index_name] ...)] [IGNORE LEAVES]
The LOAD INDEX INTO CACHE
statement preloads a table index into the
key cache to which it has been assigned by an explicit CACHE INDEX
statement, or into the default key cache otherwise. LOAD INDEX INTO
CACHE
is used only for MyISAM
tables.
The IGNORE LEAVES
modifier causes only blocks for the non-leaf
nodes of the index to be preloaded.
The following statement preloads nodes (index blocks) of indexes of the
tables t1
and t2
:
mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES; +---------+--------------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+--------------+----------+----------+ | test.t1 | preload_keys | status | OK | | test.t2 | preload_keys | status | OK | +---------+--------------+----------+----------+
This statement preloads all index blocks from t1
. It preloads only
blocks for the non-leaf nodes from t2
.
The syntax of LOAD INDEX INTO CACHE
allows you to specify that only
particular indexes from a table should be preloaded. However, the current
implementation preloads all the table's indexes into the cache, so there is
no reason to specify anything other than the table name.
LOAD INDEX INTO CACHE
was added in MySQL 4.1.1.
RESET
SyntaxRESET reset_option [, reset_option] ...
The RESET
statement is used to clear the state of various server
operations. It also acts as a stronger
version of the FLUSH
statement. See section 13.5.5.2 FLUSH
Syntax.
To execute RESET
, you must have the RELOAD
privilege.
reset_option can be any of the following:
MASTER
FLUSH MASTER
.
See section 13.6.1 SQL Statements for Controlling Master Servers.
QUERY CACHE
SLAVE
FLUSH SLAVE
.
See section 13.6.2 SQL Statements for Controlling Slave Servers.
This section describes replication-related SQL statements. One group of statements is used for controlling master servers. The other is used for controlling slave servers.
Replication can be controlled through the SQL interface. This section discusses statements for managing master replication servers. section 13.6.2 SQL Statements for Controlling Slave Servers discusses statements for managing slave servers.
PURGE MASTER LOGS
SyntaxPURGE {MASTER | BINARY} LOGS TO 'log_name' PURGE {MASTER | BINARY} LOGS BEFORE 'date'
Deletes all the binary logs listed in the log index that are strictly prior to the specified log or date. The logs also are removed from the list recorded in the log index file, so that the given log becomes the first.
Example:
PURGE MASTER LOGS TO 'mysql-bin.010'; PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26';
The BEFORE
variant is available as of MySQL 4.1. Its date argument
can be in 'YYYY-MM-DD hh:mm:ss'
format.
MASTER
and BINARY
are synonyms, but BINARY
can be used
only as of MySQL 4.1.1.
If you have an active slave that currently is reading one of the logs you are trying to delete, this statement does nothing and fails with an error. However, if a slave is dormant and you happen to purge one of the logs it wants to read, the slave will be unable to replicate once it comes up. The statement is safe to run while slaves are replicating. You do not need to stop them.
To purge logs, follow this procedure:
SHOW SLAVE STATUS
to check which log it is
reading.
SHOW MASTER LOGS
.
RESET MASTER
SyntaxRESET MASTER
Deletes all binary logs listed in the index file, resets the binary log index file to be empty, and creates a new binary log file.
This statement was named FLUSH MASTER
before MySQL 3.23.26.
SET SQL_LOG_BIN
SyntaxSET SQL_LOG_BIN = {0|1}
Disables or enables binary logging for the current connection
(SQL_LOG_BIN
is a session variable)
if the client connects using an account that has the SUPER
privilege.
The statement is refused with an error if the client does not have that
privilege. (Before MySQL 4.1.2, the statement was simply ignored in that case.)
SHOW BINLOG EVENTS
SyntaxSHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
Shows the events in the binary log.
If you do not specify 'log_name'
, the first binary log will be displayed.
The LIMIT
clause has the same syntax as for the SELECT
statement.
See section 13.1.7 SELECT
Syntax.
This statement is available as of MySQL 4.0.
SHOW MASTER LOGS
SyntaxSHOW MASTER LOGS SHOW BINARY LOGS
Lists the binary log files on the server. This statement is used as part of
the procedure described in section 13.6.1.1 PURGE MASTER LOGS
Syntax
for determining which logs can be purged.
SHOW MASTER LOGS
was added in MySQL 3.23.38. As of MySQL 4.1.1,
you can also use SHOW BINARY LOGS
, which is equivalent.
SHOW MASTER STATUS
SyntaxSHOW MASTER STATUS
Provides status information on the binary log files of the master.
SHOW SLAVE HOSTS
SyntaxSHOW SLAVE HOSTS
Displays a list of slaves currently registered with the master.
Any slave not started with the --report-host=slave_name
option will not be visible in that list.
Replication can be controlled through the SQL interface. This section discusses statements for managing slave replication servers. section 13.6.1 SQL Statements for Controlling Master Servers discusses statements for managing master servers.
CHANGE MASTER TO
SyntaxCHANGE MASTER TO master_def [, master_def] ... master_def: MASTER_HOST = 'host_name' | MASTER_USER = 'user_name' | MASTER_PASSWORD = 'password' | MASTER_PORT = port_num | MASTER_CONNECT_RETRY = count | MASTER_LOG_FILE = 'master_log_name' | MASTER_LOG_POS = master_log_pos | RELAY_LOG_FILE = 'relay_log_name' | RELAY_LOG_POS = relay_log_pos | MASTER_SSL = {0|1} | MASTER_SSL_CA = 'ca_file_name' | MASTER_SSL_CAPATH = 'ca_directory_name' | MASTER_SSL_CERT = 'cert_file_name' | MASTER_SSL_KEY = 'key_file_name' | MASTER_SSL_CIPHER = 'cipher_list'
Changes the parameters that the slave server uses for connecting to and communicating with the master server.
MASTER_USER
, MASTER_PASSWORD
, MASTER_SSL
,
MASTER_SSL_CA
, MASTER_SSL_CAPATH
, MASTER_SSL_CERT
,
MASTER_SSL_KEY
, and MASTER_SSL_CIPHER
provide information for
the slave about how to connect to its master.
The relay log options (RELAY_LOG_FILE
and RELAY_LOG_POS
) are
available beginning with MySQL 4.0.
The SSL options
(MASTER_SSL
,
MASTER_SSL_CA
,
MASTER_SSL_CAPATH
,
MASTER_SSL_CERT
,
MASTER_SSL_KEY
,
and
MASTER_SSL_CIPHER
)
are available beginning with MySQL 4.1.1.
You can change these options even on slaves that are compiled without SSL
support. They are saved to the `master.info' file, but are ignored
until you use a server that has SSL support enabled.
If you don't specify a given parameter, it keeps its old value, except as indicated in the following discussion. For example, if the password to connect to your MySQL master has changed, you just need to issue these statements to tell the slave about the new password:
mysql> STOP SLAVE; -- if replication was running mysql> CHANGE MASTER TO MASTER_PASSWORD='new3cret'; mysql> START SLAVE; -- if you want to restart replication
There is no need to specify the parameters that do not change (host, port, user, and so forth).
MASTER_HOST
and MASTER_PORT
are the hostname (or IP address) of
the master host and its TCP/IP port. Note that if MASTER_HOST
is
equal to localhost
, then, like in other parts of MySQL, the port
may be ignored (if Unix socket files can be used, for example).
If you specify MASTER_HOST
or MASTER_PORT
,
the slave assumes that the master server is different than
before (even if you specify a host or port value that is
the same as the current value.) In this case, the old values for the master
binary log name and position are considered no longer applicable, so if you
do not specify MASTER_LOG_FILE
and MASTER_LOG_POS
in the
statement, MASTER_LOG_FILE=''
and MASTER_LOG_POS=4
are
silently appended to it.
MASTER_LOG_FILE
and MASTER_LOG_POS
are the coordinates
at which the slave I/O thread should begin reading from the master the
next time the thread starts.
If you specify either of them, you can't specify RELAY_LOG_FILE
or
RELAY_LOG_POS
.
If neither of MASTER_LOG_FILE
or MASTER_LOG_POS
are
specified, the slave uses the last coordinates of the slave SQL thread
before CHANGE MASTER
was issued. This ensures that
replication has no discontinuity, even if the slave SQL thread was late
compared to the slave I/O thread, when you just want to change, say, the
password to use. This safe behavior was introduced starting from MySQL
4.0.17 and 4.1.1. (Before these versions, the coordinates used were
the last coordinates of the slave I/O thread before CHANGE MASTER
was issued. This caused the SQL thread to possibly lose some events
from the master, thus breaking replication.)
CHANGE MASTER
deletes all relay log files and starts
a new one, unless you specify RELAY_LOG_FILE
or
RELAY_LOG_POS
. In that case, relay logs are kept;
as of MySQL 4.1.1 the relay_log_purge
global variable
is set silently to 0.
CHANGE MASTER TO
updates the contents of the `master.info' and
`relay-log.info' files.
CHANGE MASTER
is useful for setting up a slave when you have
the snapshot of the master and have recorded the log and the offset
corresponding to it. After loading the snapshot into the slave, you
can run CHANGE MASTER TO MASTER_LOG_FILE='log_name_on_master',
MASTER_LOG_POS=log_offset_on_master
on the slave.
Examples:
mysql> CHANGE MASTER TO -> MASTER_HOST='master2.mycompany.com', -> MASTER_USER='replication', -> MASTER_PASSWORD='bigs3cret', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='master2-bin.001', -> MASTER_LOG_POS=4, -> MASTER_CONNECT_RETRY=10; mysql> CHANGE MASTER TO -> RELAY_LOG_FILE='slave-relay-bin.006', -> RELAY_LOG_POS=4025;
The first example changes the master and master's binary log coordinates. This is used when you want to set up the slave to replicate the master.
The second example shows an operation that is less frequently used. It
is done when the slave has relay logs that you want it to execute again
for some reason. To do this, the master need not be reachable. You just
have to use CHANGE MASTER TO
and start the SQL thread (START
SLAVE SQL_THREAD
).
You can even use the second operation in a non-replication setup with a
standalone, non-slave server, to recover after a crash. Suppose that your
server has crashed and you have restored a backup. You want to replay
the server's own binary logs (not relay logs, but regular binary logs),
supposedly named `myhost-bin.*'. First, make a backup copy of these
binary logs in some safe place, in case you don't exactly follow the
procedure below and accidentally have the server purge the binary logs.
If using MySQL 4.1.1 or newer, use SET GLOBAL relay_log_purge=0
for
additional safety. Then start the server without the --log-bin
option. Before MySQL 4.0.19, start it with a new (different from before) server
id; in newer versions there is no need, just use the
--replicate-same-server-id
option. Start it with
--relay-log=myhost-bin
(to make the server believe that these
regular binary logs are relay logs) and with --skip-slave-start
. After
the server starts, issue these statements:
mysql> CHANGE MASTER TO -> RELAY_LOG_FILE='myhost-bin.153', -> RELAY_LOG_POS=410, -> MASTER_HOST='some_dummy_string'; mysql> START SLAVE SQL_THREAD;
The server will read and execute its own binary logs, thus achieving
crash recovery. Once the recovery is finished, run STOP SLAVE
,
shut down the server, delete `master.info' and `relay-log.info',
and restart the server with its original options.
For the moment, specifying MASTER_HOST
(even with a dummy value) is
required to make the server think it is a slave. In the future, we plan to add
options to get rid of these small constraints.
LOAD DATA FROM MASTER
SyntaxLOAD DATA FROM MASTER
Takes a snapshot of the master and copies it to the slave. It updates the
values of MASTER_LOG_FILE
and MASTER_LOG_POS
so that the slave
will start replicating from the correct position. Any table and database
exclusion rules specified with the --replicate-*-do-*
and
--replicate-*-ignore-*
options are honored.
--replicate-rewrite-db
is not taken into account (because one user
could, with this option, set up a non-unique mapping such as
--replicate-rewrite-db=db1->db3
and
--replicate-rewrite-db=db2->db3
, which would confuse the slave when
it loads the master's tables).
Use of this statement is subject to the following conditions:
MyISAM
tables.
In the future, it is planned to make this statement work with
InnoDB
tables and to remove the need for a global read lock by using
non-blocking online backup.
If you are loading big tables, you might have to increase the values
of net_read_timeout
and net_write_timeout
on both your master and slave servers.
See section 5.2.3 Server System Variables.
Note that LOAD DATA FROM MASTER
does not copy any
tables from the mysql
database. This makes it easy to have
different users and privileges on the master and the slave.
The LOAD DATA FROM MASTER
statement
requires the replication account that is used to connect to the master
to have the RELOAD
and SUPER
privileges on the master and the
SELECT
privilege for all master tables you want to load. All
master tables for which the user does not have the SELECT
privilege are
ignored by LOAD DATA FROM MASTER
. This is because the
master will hide them from the user: LOAD DATA FROM MASTER
calls
SHOW DATABASES
to know the master databases to load, but
SHOW DATABASES
returns only databases for which the user has
some privilege.
See section 13.5.4.7 SHOW DATABASES
Syntax.
On the slave's side, the user that issues LOAD DATA FROM MASTER
should
have grants to drop and create the databases and tables that are copied.
LOAD TABLE tbl_name FROM MASTER
SyntaxLOAD TABLE tbl_name FROM MASTER
Transfers a copy of the table from master to the slave. This statement is
implemented mainly for debugging of LOAD DATA FROM MASTER
.
It requires that the account used for connecting to the master server has the
RELOAD
and SUPER
privileges on the master and the
SELECT
privilege on the master table to load.
On the slave side, the user that issues LOAD TABLE FROM MASTER
should
have privileges to drop and create the table.
The conditions for LOAD DATA FROM MASTER
apply here, too. For
example, LOAD TABLE FROM MASTER
works only for MyISAM
tables.
The timeout notes for LOAD DATA FROM MASTER
apply as well.
MASTER_POS_WAIT()
SyntaxSELECT MASTER_POS_WAIT('master_log_file', master_log_pos)
This is a function, not a statement. It is used to ensure that the slave has read and executed events up to a given position in the master's binary log. See section 12.8.4 Miscellaneous Functions for a full description.
RESET SLAVE
SyntaxRESET SLAVE
Makes the slave forget its replication position in the master's binary logs. This statement is meant to be used for a clean start: It deletes the `master.info' and `relay-log.info' files, all the relay logs, and starts a new relay log.
Note: All relay logs are deleted, even if they have not been
totally executed by the slave SQL thread. (This is a condition likely to
exist on a replication slave if you have issued a STOP SLAVE
statement or if the slave is highly loaded.)
Connection information stored in the `master.info' file is immediately
reset using any values specified in the corresponding startup options.
This information includes values such as master host, master port, master
user, and master password. If the slave SQL thread was in the middle of
replicating temporary tables when it was stopped, and RESET SLAVE
is issued, these replicated temporary tables are deleted on the slave.
This statement was named FLUSH SLAVE
before MySQL 3.23.26.
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
SyntaxSET GLOBAL SQL_SLAVE_SKIP_COUNTER = n
Skip the next n events from the master. This is useful for recovering from replication stops caused by a statement.
This statement is valid only when the slave thread is not running. Otherwise, it produces an error.
Before MySQL 4.0, omit the GLOBAL
keyword from the statement.
SHOW SLAVE STATUS
SyntaxSHOW SLAVE STATUS
Provides status information on
essential parameters of the slave threads. If you issue this statement using
the
mysql
client, you can use a \G
statement terminator rather than
semicolon to get a more readable vertical layout:
mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: root Master_Port: 3306 Connect_Retry: 3 Master_Log_File: gbichot-bin.005 Read_Master_Log_Pos: 79 Relay_Log_File: gbichot-relay-bin.005 Relay_Log_Pos: 548 Relay_Master_Log_File: gbichot-bin.005 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 79 Relay_Log_Space: 552 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 8
Depending on your version of MySQL, you may not see all the fields just shown. In particular, several fields are present only as of MySQL 4.1.1.
SHOW SLAVE STATUS
returns the following fields:
Slave_IO_State
State
field of
the output of SHOW PROCESSLIST
for the slave I/O thread. This
tells you if the thread is trying to connect to the master, waiting
for events from the master, reconnecting to the master, and so on. Possible
states are listed in section 6.3 Replication Implementation Details. Looking at
this field is necessary because, for example, the thread can be running
but unsuccessfully trying to connect to the master; only this field
will make you aware of the connection problem.
The state of the SQL thread is not copied because it is simpler.
If it is running, there is no
problem; if it is not, you will find the error in the
Last_Error
field (described below).
This field is present beginning with MySQL 4.1.1.
Master_Host
Master_User
Master_Port
Connect_Retry
--master-connect-retry
option.
Master_Log_File
Read_Master_Log_Pos
Relay_Log_File
Relay_Log_Pos
Relay_Master_Log_File
Slave_IO_Running
Slave_SQL_Running
Replicate_Do_DB, Replicate_Ignore_DB
--replicate-do-db
and --replicate-ignore-db
options, if any.
These fields are present beginning with MySQL 4.1.1.
Replicate_Do_Table, Replicate_Ignore_Table, Replicate_Wild_Do_Table, Replicate_Wild_Ignore_Table
--replicate-do-table
,
--replicate-ignore-table
,
--replicate-wild-do-table
,
and
--replicate-wild-ignore_table
options, if any.
These fields are present beginning with MySQL 4.1.1.
Last_Errno, Last_Error
Last_Error
value is not empty, it will also appear as a
message in the slave's error log.
For example:
Last_Errno: 1051 Last_Error: error 'Unknown table 'z'' on query 'drop table z'The message indicates that the table
z
existed on the master and was dropped there, but it
did not exist on the slave, so DROP TABLE
failed on the slave.
(This might occur, for example, if you forget to copy the table to the
slave when setting up replication.)
Skip_Counter
SQL_SLAVE_SKIP_COUNTER
.
Exec_Master_Log_Pos
Relay_Master_Log_File
).
(Relay_Master_Log_File
, Exec_Master_Log_Pos
) in the
master's binary log corresponds to
(Relay_Log_File
, Relay_Log_Pos
)
in the relay log.
Relay_Log_Space
Until_Condition, Until_Log_File, Until_Log_Pos
UNTIL
clause of the START SLAVE
statement.
Until_Condition
has these values:
None
if no UNTIL
clause was specified
Master
if the slave is reading until a given position in the master's
binary logs
Relay
if the slave is reading until a given position in its relay logs
Until_Log_File
and Until_Log_Pos
indicate the log filename and
position values that define the point at which the SQL thread will stop
executing.
These fields are present beginning with MySQL 4.1.1.
Master_SSL_Allowed, Master_SSL_CA_File, Master_SSL_CA_Path, Master_SSL_Cert, Master_SSL_Cipher, Master_SSL_Key
Master_SSL_Allowed
has these values:
Yes
if an SSL connection to the master is allowed
No
if an SSL connection to the master is not allowed
Ignored
if an SSL connection is allowed but the slave server does not
have SSL support enabled
--master-ca
,
--master-capath
,
--master-cert
,
--master-cipher
,
and
--master-key
options.
These fields are present beginning with MySQL 4.1.1.
Seconds_Behind_Master
Seconds_Behind_Master
will often show a value of 0, even if I/O
thread is late compared to master. In other words, this column is
useful only for fast networks.
This time difference computation will work even though your master and slave
don't have identical clocks (the clock difference is computed when the slave
I/O thread starts, and assumed to remain constant from then on).
Seconds_Behind_Master
will be NULL
(which means ``unknown'') if
the slave SQL thread is not running, or if the slave I/O thread is not running
or not connected to master. For example if the slave I/O thread is sleeping for
master-connect-retry
seconds before reconnecting, NULL
will be
shown, as the slave cannot know what the master is doing now, so cannot
reliably say how late it is.
This field has one limitation. Indeed the timestamp is preserved through
replication, which means that if your master M1 is itself a slave of M0, any
event from M1's binlog which has its origin in replication of an event of M0's
binlog, has the timestamp of that last event. This enables MySQL to replicate
TIMESTAMP
successfully. But the drawback for
Seconds_Behind_Master
is that if M1 also receives direct updates from
clients, then the value will randomly go up and down, because sometimes the
last M1's event will be from M0 and sometimes it will be from a direct update,
and so will be the last timestamp.
START SLAVE
SyntaxSTART SLAVE [thread_type [, thread_type] ... ] START SLAVE [SQL_THREAD] UNTIL MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos START SLAVE [SQL_THREAD] UNTIL RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos thread_type: IO_THREAD | SQL_THREAD
START SLAVE
with no options starts both of the slave threads.
The I/O thread reads queries from the master server and stores them in the
relay log. The SQL thread reads the relay log and executes the
queries.
START SLAVE
requires the SUPER
privilege.
If START SLAVE
succeeds in starting the slave threads, it
returns without any error. However, even in that case, it might be that the slave
threads start and then later stop (for example, because they don't manage to
connect to the master or read its binary logs, or some other
problem). START SLAVE
will not warn you about this. You must
check your slave's error log for error messages generated by
the slave threads, or check that they are running fine with SHOW
SLAVE STATUS
.
As of MySQL 4.0.2, you can add IO_THREAD
and SQL_THREAD
options to the statement to name which of the threads to start.
As of MySQL 4.1.1, an UNTIL
clause may be added to specify that
the slave should start and run until the SQL thread reaches a given point in
the master binary logs or in the slave relay logs. When the SQL thread reaches
that point, it stops. If the SQL_THREAD
option is specified in the
statement, it starts only the SQL thread. Otherwise, it starts both slave
threads. If the SQL thread is already running, the UNTIL
clause is
ignored and a warning is issued.
With an UNTIL
clause, you must specify both a log filename and
position. Do not mix master and relay log options.
Any UNTIL
condition is reset by a subsequent STOP SLAVE
statement, a START SLAVE
statement that includes no UNTIL
clause, or a server restart.
The UNTIL
clause can be useful for debugging replication, or to
cause replication to proceed until just before the point where you want
to avoid having the slave replicate a statement. For example, if an unwise
DROP TABLE
statement was executed on the master, you can use
UNTIL
to tell the slave to execute up to that point but no farther.
To find what the event is, use mysqlbinlog
with the master logs or
slave relay logs, or by using a SHOW BINLOG EVENTS
statement.
If you are using UNTIL
to have the slave process replicated queries in
sections, it is recommended that you start the slave with the
--skip-slave-start
option to prevent the SQL thread from running
when the slave server starts. It is probably best to use this option in an
option file rather than on the command line, so that an unexpected server
restart does not cause it to be forgotten.
The SHOW SLAVE STATUS
statement includes output fields that display
the current values of the UNTIL
condition.
This statement is called SLAVE START
before MySQL 4.0.5.
For the moment, SLAVE START
is still accepted for backward
compatibility, but is deprecated.
STOP SLAVE
SyntaxSTOP SLAVE [thread_type [, thread_type] ... ] thread_type: IO_THREAD | SQL_THREAD
Stops the slave threads.
STOP SLAVE
requires the SUPER
privilege.
Like START SLAVE
, as of MySQL 4.0.2, this statement
may be used with the IO_THREAD
and SQL_THREAD
options to name
the thread or threads to stop.
This statement is called SLAVE STOP
before MySQL 4.0.5.
For the moment, SLAVE STOP
is still accepted for backward
compatibility, but is deprecated.
Support for server-side prepared statements was added in MySQL 4.1. This support takes advantage of the efficient client/server binary protocol, provided that you use an appropriate client programming interface. Candidate interfaces include the MySQL C API client library (for C programs) or MySQL Connector/J (for Java programs). For example, the C API provides a set of function calls that make up its prepared statement API. See section 21.2.4 C API Prepared Statements. Other language interfaces can provide support for prepared statements that use the binary protocol by linking in the C client library. (The mysqli extension in PHP 5.0 does this, for example.)
Beginning with MySQL 4.1.3, an alternative interface to prepared statements is available: SQL syntax for prepared statements. This interface is not as efficient as using the binary protocol through a prepared statement API, but requires no programming because it is available directly at the SQL level:
mysql
client program.
SQL syntax for prepared statements is intended to be used for situations such as these:
SQL syntax for prepared statements is based on three SQL statements:
PREPARE stmt_name FROM preparable_stmt; EXECUTE stmt_name [USING @var_name [, @var_name] ...]; {DEALLOCATE | DROP} PREPARE stmt_name;
The PREPARE
statement prepares a statement and assigns it
a name, stmt_name, by which to refer to the statement later.
Statement names are not case sensitive.
preparable_stmt is either a string literal or a user variable that
contains the text of the statement. The text must represent a single SQL
statement, not multiple statements. Within the statement, `?'
characters can be used as parameter markers to indicate where data values
are to be bound to the query later when you execute it. The `?'
characters should not be enclosed within quotes, even if you intend to bind
them to string values.
If a prepared statement already exists with the same name, it is deallocated implicitly before the new statement is prepared. This means that if the new statement contains an error and cannot be prepared, an error is returned and no statement with the given name will exist.
The scope of a prepared statement is the client session within which it is created. Other clients cannot see it.
After preparing a statement, you execute it with an EXECUTE
statement that refers to the prepared statement name. If the prepared
statement contains any parameter markers, you must supply a USING
clause that lists user variables containing the values to be bound to
the parameters.
Parameter values can be supplied only by user variables, and the USING
clause must name exactly as many variables as the number of parameter markers
in the statement.
You can execute a given prepared statement multiple times, passing it different variables or setting the variables to different values before each execution.
To deallocate a prepared statement, use the DEALLOCATE PREPARE
statement. Attempting to execute a prepared statement after deallocating
it results in an error.
If you terminate a client session without deallocating a previously prepared statement, the server deallocates it automatically.
The following statements can be used as prepared statements:
CREATE TABLE
,
DELETE
,
DO
,
INSERT
,
REPLACE
,
SELECT
,
SET
,
UPDATE
,
and most
SHOW
,
statements.
Other statements are not yet supported.
The following examples show two equivalent ways of preparing a statement that computes the hypotenuse of a triangle given the lengths of the two sides.
The first example shows how to create a prepared statment by using a string literal to supply the text of the statement:
mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; mysql> SET @a = 3; mysql> SET @b = 4; mysql> EXECUTE stmt1 USING @a, @b; +------------+ | hypotenuse | +------------+ | 5 | +------------+ mysql> DEALLOCATE PREPARE stmt1;
The second example is similar, but supplies the text of the statement with a user variable:
mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; mysql> PREPARE stmt2 FROM @s; mysql> SET @a = 6; mysql> SET @b = 8; mysql> EXECUTE stmt2 USING @a, @b; +------------+ | hypotenuse | +------------+ | 10 | +------------+ mysql> DEALLOCATE PREPARE stmt2;
SQL syntax for prepared statements cannot be used in nested fashion.
That is, a statement passed to PREPARE
cannot itself be a
PREPARE
, EXECUTE
, or DEALLOCATE PREPARE
statement.
Also, SQL syntax for prepared statements is distinct from using
prepared statement API calls. For example, you cannot use the
mysql_stmt_prepare()
C API function to prepare a PREPARE
,
EXECUTE
, or DEALLOCATE PREPARE
statement.
Go to the first, previous, next, last section, table of contents.