|
|
Table of Contents
This chapter describes the syntax for most of the SQL statements supported by MySQL. Additional statement descriptions can be found in the following chapters:
The EXPLAIN
statement is discussed in
Chapter 7, Optimization.
Statements for writing stored routines are covered in Chapter 17, Stored Procedures and Functions.
Statements for writing triggers are covered in Chapter 18, Triggers.
View-related statements are covered in Chapter 19, Views.
ALTER {DATABASE | SCHEMA} [db_name
]alter_specification
[,alter_specification
] ...alter_specification
: [DEFAULT] CHARACTER SETcharset_name
| [DEFAULT] COLLATEcollation_name
ALTER DATABASE
enables 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.
ALTER SCHEMA
is a synonym for ALTER
DATABASE
as of MySQL 5.0.2.
The CHARACTER SET
clause changes the default
database character set. The COLLATE
clause
changes the default database collation.
Chapter 10, Character Set Support, discusses character set and collation
names.
The database name can be omitted, in which case the statement applies to the default database.
ALTER [IGNORE] TABLEtbl_name
alter_specification
[,alter_specification
] ...alter_specification
: ADD [COLUMN]column_definition
[FIRST | AFTERcol_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] [index_name
] [index_type
] (index_col_name
,...) | ADD [FULLTEXT|SPATIAL] [INDEX] [index_name
] (index_col_name
,...) | ADD [CONSTRAINT [symbol
]] FOREIGN KEY [index_name
] (index_col_name
,...) [reference_definition
] | ALTER [COLUMN]col_name
{SET DEFAULTliteral
| DROP DEFAULT} | CHANGE [COLUMN]old_col_name
column_definition
[FIRST|AFTERcol_name
] | MODIFY [COLUMN]column_definition
[FIRST | AFTERcol_name
] | DROP [COLUMN]col_name
| DROP PRIMARY KEY | DROP INDEXindex_name
| DROP FOREIGN KEYfk_symbol
| DISABLE KEYS | ENABLE KEYS | RENAME [TO]new_tbl_name
| ORDER BYcol_name
| CONVERT TO CHARACTER SETcharset_name
[COLLATEcollation_name
] | [DEFAULT] CHARACTER SETcharset_name
[COLLATEcollation_name
] | DISCARD TABLESPACE | IMPORT TABLESPACE |table_options
ALTER TABLE
enables 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. This
includes table_options
modifications,
for options such as ENGINE
,
AUTO_INCREMENT
, and
AVG_ROW_LENGTH
. (However, ALTER
TABLE
ignores the DATA DIRECTORY
and INDEX DIRECTORY
table options.)
Section 13.1.5, “CREATE TABLE
Syntax”, lists all table options.
Some operations may result in warnings if attempted on a table
for which the storage engine does not support the operation.
These warnings can be displayed with SHOW
WARNINGS
. See Section 13.5.4.25, “SHOW WARNINGS
Syntax”.
If you use ALTER TABLE
to change a column
specification but DESCRIBE
indicates that
your column was not changed, it is possible that MySQL ignored
your modification for one of the reasons described in
Section 13.1.5.1, “Silent Column Specification Changes”.
tbl_name
In most cases, ALTER TABLE
works by making a
temporary copy of the original table. The alteration is
performed on the copy, and 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, and then are automatically redirected to the new table
without any failed updates.
If you use ALTER TABLE
without any
other options, MySQL simply renames any files that correspond to
the table tbl_name
RENAME TO
new_tbl_name
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.1.9, “RENAME TABLE
Syntax”.)
If you use any option to ALTER TABLE
other
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). 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.
To use 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 or if
warnings occur when strict mode is enabled. If
IGNORE
is not specified, the copy is
aborted and rolled back if duplicate-key errors occur. If
IGNORE
is specified, only the first row
is used of rows with duplicates on a unique key, The other
conflicting rows are deleted. Incorrect values are truncated
to the closest matching acceptable value.
You can issue multiple ADD
,
ALTER
, DROP
, and
CHANGE
clauses in a single ALTER
TABLE
statement, separated by commas. 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,
do this:
ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
CHANGE
, col_name
DROP
, and
col_name
DROP INDEX
are MySQL extensions to
standard SQL.
MODIFY
is an Oracle extension to
ALTER TABLE
.
The word COLUMN
is optional and can be
omitted.
column_definition
clauses use the
same syntax for ADD
and
CHANGE
as for CREATE
TABLE
. Note that this syntax includes the column
name, not just its data type. See
Section 13.1.5, “CREATE TABLE
Syntax”.
You can rename a column using a CHANGE
clause. To do so, specify the old and new column names and
the type that the column currently has. For example, to
rename an old_col_name
column_definition
INTEGER
column from
a
to b
, you can do
this:
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:
ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
You can also use MODIFY
to change a
column's type without renaming it:
ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
If you use CHANGE
or
MODIFY
to shorten a column for which an
index exists on the column, and the resulting column length
is less than the index length, MySQL shortens the index
automatically.
When you change a data type using CHANGE
or MODIFY
, MySQL tries to convert
existing column values to the new type as well as possible.
To add a column at a specific position within a table row,
use FIRST
or AFTER
. The default
is to add the column last. You can also use
col_name
FIRST
and AFTER
in
CHANGE
or MODIFY
operations.
ALTER ... SET DEFAULT
or ALTER
... DROP DEFAULT
specify a new default value for a
column or remove the old default value, respectively. 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 11.1.4, “Data Type Default Values”.
DROP INDEX
removes an index. This is a
MySQL extension to standard SQL. See
Section 13.1.7, “DROP INDEX
Syntax”.
If columns are dropped from a table, the columns are also removed from any index of which they are a part. If all columns that make up an index are dropped, the index is dropped as well.
If a table contains only one column, the column cannot be
dropped. If what you intend is to remove the table, use
DROP TABLE
instead.
DROP PRIMARY KEY
drops the primary index.
Note: In older versions of MySQL, if no
primary index existed, DROP PRIMARY KEY
would drop the first UNIQUE
index in the
table. This is not the case in MySQL 5.0, where
trying to use DROP PRIMARY KEY
on a table
with no primary key give rises to an error.
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
enables you to create the new
table with the rows in a specific order. Note that the table
does not remain in this order after inserts and deletes.
This option is useful primarily when you know that you are
mostly to query the rows in a certain order most of the
time. By using this option after major 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.
If you use 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.
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
requires the
INDEX
privilege in addition to the
privileges mentioned earlier.
The FOREIGN KEY
and
REFERENCES
clauses are supported by the
InnoDB
storage engine, which implements
ADD [CONSTRAINT
[
. See
Section 14.2.6.4, “symbol
]] FOREIGN KEY (...)
REFERENCES ... (...)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.1.5, “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.9.5, “MySQL Differences from Standard SQL”.
You cannot add a foreign key and drop a foreign key in
separate clauses of a single ALTER TABLE
statement. You must use separate statements.
InnoDB
supports the use of ALTER
TABLE
to drop foreign keys:
ALTER TABLEtbl_name
DROP FOREIGN KEYfk_symbol
;
You cannot add a foreign key and drop a foreign key in
separate clauses of a single ALTER TABLE
statement. You must use separate statements.
For more information, see
Section 14.2.6.4, “FOREIGN KEY
Constraints”.
If you want to change the table default character set and
all character columns (CHAR
,
VARCHAR
, TEXT
) to a
new character set, use a statement like this:
ALTER TABLEtbl_name
CONVERT TO CHARACTER SETcharset_name
;
Warning: The preceding
operation converts 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.
If you specify CONVERT TO CHARACTER SET
binary
, the CHAR
,
VARCHAR
, and TEXT
columns are converted to their corresponding binary string
types (BINARY
,
VARBINARY
, BLOB
). This
means that the columns no longer will have a character set
and a subsequent CONVERT TO
operation
will not apply to them.
To change only the default character set for a table, use this statement:
ALTER TABLEtbl_name
DEFAULT CHARACTER SETcharset_name
;
The word DEFAULT
is optional. The default
character set is the character set that is used if you do
not specify the character set for a new column which you add
to a table (for example, with ALTER TABLE ... ADD
column
).
For an 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, and
then issue this statement:
ALTER TABLE tbl_name
IMPORT TABLESPACE;
With the mysql_info()
C API function, you can
find out how many rows were copied, and (when
IGNORE
is used) how many rows were deleted
due to duplication of unique key values. See
Section 22.2.3.34, “mysql_info()
”.
Here are some examples that show uses of ALTER
TABLE
. Begin with a table t1
that
is created as shown here:
CREATE TABLE t1 (a INTEGER,b CHAR(10));
To rename the table from t1
to
t2
:
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
:
ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
To add a new TIMESTAMP
column named
d
:
ALTER TABLE t2 ADD d TIMESTAMP;
To add indexes on column d
and on column
a
:
ALTER TABLE t2 ADD INDEX (d), ADD INDEX (a);
To remove column c
:
ALTER TABLE t2 DROP COLUMN c;
To add a new AUTO_INCREMENT
integer column
named c
:
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=
before
value
ALTER TABLE
or by using the
AUTO_INCREMENT=
table option. See Section 13.5.3, “value
SET
Syntax”.
From MySQL 5.0.3, you can use the ALTER TABLE ...
AUTO_INCREMENT=
table
option for value
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 current maximum value in
the column, no error message is given and the current sequence
value is not changed.
With MyISAM
tables, if you do not change the
AUTO_INCREMENT
column, the sequence number is
not affected. If you drop an AUTO_INCREMENT
column and then add another AUTO_INCREMENT
column, the numbers are resequenced beginning with 1.
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS]db_name
[create_specification
[,create_specification
] ...]create_specification
: [DEFAULT] CHARACTER SETcharset_name
| [DEFAULT] COLLATEcollation_name
CREATE DATABASE
creates a database with the
given name. To use CREATE DATABASE
, you need
the CREATE
privilege on the database.
CREATE SCHEMA
is a synonym for
CREATE DATABASE
as of MySQL 5.0.2.
Rules for allowable database names are given in
Section 9.2, “Database, Table, Index, Column, and Alias Names”. An error occurs if the database
exists and you did not specify IF NOT EXISTS
.
create_specification
options 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.
Chapter 10, Character Set Support, discusses character set and collation
names.
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 creates only a
directory under the MySQL data directory and the
db.opt
file.
If you manually create a directory under the data directory (for
example, with mkdir), the server considers it
a database directory and it shows up in the output of
SHOW DATABASES
.
You can also use the mysqladmin program to create databases. See Section 8.7, “mysqladmin — Client for Administering a MySQL Server”.
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEXindex_name
[USINGindex_type
] ONtbl_name
(index_col_name
,...)index_col_name
:col_name
[(length
)] [ASC | DESC]
CREATE INDEX
is mapped to an ALTER
TABLE
statement to create indexes. See
Section 13.1.2, “ALTER TABLE
Syntax”. For more information about how
MySQL uses indexes, see Section 7.4.5, “How MySQL Uses Indexes”.
Normally, you create all indexes on a table at the time the
table itself is created with CREATE TABLE
.
See Section 13.1.5, “CREATE TABLE
Syntax”. CREATE
INDEX
enables 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
, VARCHAR
,
BINARY
, and VARBINARY
columns, indexes can be created that use only part of a column,
using
syntax to specify an index prefix length. Index entries consist
of the first col_name
(length
)length
characters of
each column value for CHAR
and
VARCHAR
columns, and the first
length
bytes of each column value for
BINARY
and VARBINARY
columns. 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));
If names in the column 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 1000 bytes long (767 bytes for
InnoDB
tables). Note that prefix limits are
measured in bytes, whereas the prefix length in CREATE
INDEX
statements is interpreted as number of
characters for non-binary data types (CHAR
,
VARCHAR
, TEXT
). Take this
into account when specifying a prefix length for a column that
uses a multi-byte character set.
In MySQL 5.0:
You can add an index on a column that can have
NULL
values only if you are using the
MyISAM
, InnoDB
,
BDB
, or MEMORY
storage
engine.
You can add an index on a BLOB
or
TEXT
column only if you are using the
MyISAM
, BDB
, or
InnoDB
storage engine.
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.
Some storage engines allow you to specify an index type when
creating an index. The syntax for the
index_type
specifier is
USING
.
The allowable type_name
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 |
Examples:
CREATE TABLE lookup (id INT) ENGINE = MEMORY; CREATE INDEX id_index USING BTREE ON lookup (id);
TYPE
can
be used as a synonym for type_name
USING
to specify an
index type. However, type_name
USING
is the preferred
form. In addition, the index name that precedes the index type
in the index specification syntax is not optional with
TYPE
: Unlike USING
,
TYPE
is not a reserved word and is
interpreted as the index name if no name is given.
If you specify an index type that is not legal for a given storage engine, but there is another index type available that the engine can use without affecting query results, the engine uses the available type.
FULLTEXT
indexes are supported only for
MyISAM
tables and can include only
CHAR
, VARCHAR
, and
TEXT
columns. See
Section 12.7, “Full-Text Search Functions”.
SPATIAL
indexes are supported only for
MyISAM
tables and can include only spatial
columns that are defined as NOT NULL
.
Chapter 16, Spatial Extensions, describes the spatial data
types.
CREATE [TEMPORARY] TABLE [IF NOT EXISTS]tbl_name
[(create_definition
,...)] [table_options
] [select_statement
]
Or:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS]tbl_name
[(] LIKEold_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] [DEFAULTdefault_value
] [AUTO_INCREMENT] [UNIQUE [KEY] | [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 | YEAR | CHAR(length
) [BINARY | ASCII | UNICODE] | VARCHAR(length
) [BINARY] | BINARY(length
) | VARBINARY(length
) | TINYBLOB | BLOB | MEDIUMBLOB | LONGBLOB | TINYTEXT [BINARY] | TEXT [BINARY] | MEDIUMTEXT [BINARY] | LONGTEXT [BINARY] | ENUM(value1
,value2
,value3
,...) | SET(value1
,value2
,value3
,...) |spatial_type
index_col_name
:col_name
[(length
)] [ASC | DESC]reference_definition
: REFERENCEStbl_name
[(index_col_name
,...)] [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] [ON DELETEreference_option
] [ON UPDATEreference_option
]reference_option
: RESTRICT | CASCADE | SET NULL | NO ACTIONtable_options
:table_option
[table_option
] ...table_option
: {ENGINE|TYPE} [=]engine_name
| AUTO_INCREMENT [=]value
| AVG_ROW_LENGTH [=]value
| [DEFAULT] CHARACTER SETcharset_name
[COLLATEcollation_name
] | CHECKSUM [=] {0 | 1} | COMMENT [=] 'string
' | CONNECTION [=] 'connect_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} | UNION [=] (tbl_name
[,tbl_name
]...) | INSERT_METHOD [=] { NO | FIRST | LAST } | DATA DIRECTORY [=] 'absolute path to directory
' | INDEX DIRECTORY [=] 'absolute path to directory
'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 default database. An error occurs if the table exists, if there is no default database, or if the database does not exist.
The table name can be specified as
db_name.tbl_name
to create the table
in a specific database. This works regardless of whether there
is a default database, assuming that the database exists. If you
use quoted identifiers, quote the database and table names
separately. For example, write
`mydb`.`mytbl`
, not
`mydb.mytbl`
.
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.)
To create temporary tables, you must have the CREATE
TEMPORARY TABLES
privilege.
The keywords IF NOT EXISTS
prevent an error
from occurring if the table exists. However, there is no
verification that the existing table has a structure identical
to that indicated by the CREATE TABLE
statement. Note: If you use IF NOT
EXISTS
in a CREATE TABLE ... SELECT
statement, any rows selected by the SELECT
part are inserted regardless of whether the table already
exists.
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 data and index files. Thus, for each
MyISAM
table
tbl_name
, there are three disk files:
File | Purpose |
| Table format (definition) file |
| Data file |
| Index file |
Chapter 14, Storage Engines and Table Types, describes what files each storage engine creates to represent tables.
type
represents the data type is a
column definition. spatial_type
represents a spatial data type. For general information on the
properties of data types other than the spatial types, see
Chapter 11, Data Types. For information about spatial data
types, see Chapter 16, Spatial Extensions.
If neither NULL
nor NOT
NULL
is specified, the column is treated as though
NULL
had been specified.
An integer column can have the additional attribute
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
, where
value
+1value
is the largest value for
the column currently in the table.
AUTO_INCREMENT
sequences begin with
1
.
To retrieve an AUTO_INCREMENT
value after
inserting a row, use the LAST_INSERT_ID()
SQL function or the mysql_insert_id()
C
API function. See Section 12.9.3, “Information Functions”,
and Section 22.2.3.36, “mysql_insert_id()
”.
If the NO_AUTO_VALUE_ON_ZERO
SQL mode is
enabled, you can store 0
in
AUTO_INCREMENT
columns as
0
without generating a new sequence
value. See Section 5.2.5, “The Server SQL Mode”.
Note: There can be only one
AUTO_INCREMENT
column per table, it must
be indexed, and it cannot have a DEFAULT
value. An AUTO_INCREMENT
column works
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 do not 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 * FROMtbl_name
WHEREauto_col
IS NULL
The attribute SERIAL
is an alias for
BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
UNIQUE
.
Character data types (CHAR
,
VARCHAR
, TEXT
) can
include CHARACTER SET
and
COLLATE
attributes to specify the
character set and collation for the column. For details, see
Chapter 10, Character Set Support. CHARSET
is a
synonym for CHARACTER SET
. Example:
CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);
MySQL 5.0 interprets length specifications in character column definitions in characters. (Versions before MySQL 4.1 interpreted them in bytes.)
The 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. See
Section 11.3.1.1, “TIMESTAMP
Properties as of MySQL 4.1”.
If a column definition includes no explicit
DEFAULT
value, MySQL determines the
default value as described in
Section 11.1.4, “Data Type Default Values”.
BLOB
and TEXT
columns
cannot be assigned a default value.
A comment for a column can be specified with the
COMMENT
option. The comment is displayed
by the SHOW CREATE TABLE
and
SHOW FULL COLUMNS
statements.
KEY
is normally a synonym for
INDEX
. 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.
A UNIQUE
index creates a constraint such
that 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 a column with a
UNIQUE
index allows only a single
NULL
.
A PRIMARY KEY
is a unique index where all
key columns must be defined as NOT NULL
.
If they are not explicitly declared as NOT
NULL
, MySQL declares them so implicitly (and
silently). A table can have only one PRIMARY
KEY
. If you do not 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
.
In InnoDB
tables, having a long
PRIMARY KEY
wastes a lot of space. (See
Section 14.2.13, “InnoDB
Table and Index Structures”.)
In the created table, a 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.
A 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 only marks that single column
as primary. You must use a separate PRIMARY
KEY(
clause.
index_col_name
,
...)
If a 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.
In MySQL, the name of a PRIMARY KEY
is
PRIMARY
. For other indexes, if you do not
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
. See
Section 13.5.4.13, “tbl_name
SHOW INDEX
Syntax”.
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
Example:
CREATE TABLE lookup (id INT, INDEX USING BTREE (id)) ENGINE = MEMORY;
For details about USING
, see
Section 13.1.4, “CREATE INDEX
Syntax”.
For more information about how MySQL uses indexes, see Section 7.4.5, “How MySQL Uses Indexes”.
In MySQL 5.0, only the
MyISAM
, InnoDB
,
BDB
, and 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.
With
syntax in an index specification, you can create an index
that uses only part of a column. Index entries consist of
the first col_name
(length
)length
characters of
each column value for CHAR
and
VARCHAR
columns, and the first
length
bytes of each column value
for BINARY
and
VARBINARY
columns. 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
, BDB
, and
InnoDB
storage engines 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 1000 bytes long (767 bytes for
InnoDB
tables). Note that prefix limits
are measured in bytes, whereas the prefix length in
CREATE TABLE
statements is interpreted as
number of characters for non-binary data types
(CHAR
, VARCHAR
,
TEXT
). Take this into account when
specifying a prefix length for a column that uses a
multi-byte character set.
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.
When you use ORDER BY
or GROUP
BY
on a TEXT
or
BLOB
column in a
SELECT
, 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”.
You can create special FULLTEXT
indexes,
which are used for full-text searches. Only the
MyISAM
storage engine 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.7, “Full-Text Search Functions”,
for details of operation.
You can create SPATIAL
indexes on spatial
data types. Spatial types are supported only for
MyISAM
tables and indexed columns must be
declared as NOT NULL
. See
Chapter 16, Spatial Extensions.
InnoDB
tables support checking of foreign
key constraints. See Section 14.2, “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. For the precise syntax, see
Section 14.2.6.4, “FOREIGN KEY
Constraints”.
For other storage engines, MySQL Server parses and ignores
the FOREIGN KEY
and
REFERENCES
syntax in CREATE
TABLE
statements. The CHECK
clause is parsed but ignored by all storage engines. See
Section 1.9.5.5, “Foreign Keys”.
For MyISAM
tables, each
NULL
column takes one bit extra, rounded
up to the nearest byte. The maximum row 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 row 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 ENGINE
table option specifies the storage
engine for the table. TYPE
is a synonym, but
ENGINE
is the preferred option name.
The ENGINE
table option takes the storage
engine names shown in the following table.
Storage Engine | Description |
ARCHIVE | The archiving storage engine. See
Section 14.8, “The ARCHIVE Storage Engine”. |
BDB | Transaction-safe tables with page locking. Also known as
BerkeleyDB . See
Section 14.5, “The BDB (BerkeleyDB ) Storage Engine”. |
CSV | Tables that store rows in comma-separated values format. See
Section 14.9, “The CSV Storage Engine”. |
EXAMPLE | An example engine. See Section 14.6, “The EXAMPLE Storage Engine”. |
FEDERATED | Storage engine that accesses remote tables. See
Section 14.7, “The FEDERATED Storage Engine”. |
HEAP | This is a synonym for MEMORY . |
ISAM (OBSOLETE) | Not available in MySQL 5.0. If you are upgrading to MySQL
5.0 from a previous version, you should
convert any existing ISAM tables to
MyISAM before
performing the upgrade. |
InnoDB | Transaction-safe tables with row locking and foreign keys. See
Section 14.2, “The InnoDB Storage Engine”. |
MEMORY | The data for this storage engine is stored only in memory. See
Section 14.4, “The MEMORY (HEAP ) Storage Engine”. |
MERGE | A collection of MyISAM tables used as one table. Also
known as MRG_MyISAM . See
Section 14.3, “The MERGE Storage Engine”. |
MyISAM | The binary portable storage engine that is the default storage engine
used by MySQL. See
Section 14.1, “The MyISAM Storage Engine”. |
NDBCLUSTER | Clustered, fault-tolerant, memory-based tables. Also known as
NDB . See
Chapter 15, MySQL Cluster. |
If a storage engine is specified that is not available, MySQL
uses the default engine instead. Normally, this is
MyISAM
. 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
5.0, 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 do not have to specify any of them. These options apply to all storage engines unless otherwise indicated:
AUTO_INCREMENT
The initial AUTO_INCREMENT
value for the
table. In MySQL 5.0, this works for
MyISAM
and MEMORY
tables. It is also supported for InnoDB
as of MySQL 5.0.3. To set the first auto-increment value for
engines that do not support the
AUTO_INCREMENT
table option, insert a
“dummy” row with a value one less than the
desired value after creating the table, and then delete the
dummy row.
For engines that support the
AUTO_INCREMENT
table option in
CREATE TABLE
statements, you can also use
ALTER TABLE
to
reset the tbl_name
AUTO_INCREMENT = N
AUTO_INCREMENT
value.
AVG_ROW_LENGTH
An approximation of the average row length for your table. You need to set this only for large tables with variable-size rows.
When you create a MyISAM
table, MySQL
uses the product of the MAX_ROWS
and
AVG_ROW_LENGTH
options to decide how big
the resulting table is. If you don't specify either option,
the maximum size for a table is 65,536TB of data (4GB before
MySQL 5.0.6). (If your operating system does not support
files that large, table sizes are constrained by the file
size limit.) If you want to keep down the pointer sizes to
make the index smaller and faster and you don't really need
big files, you can decrease 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.2, “Server System Variables”.) If you want all
your tables to be able to grow above the default limit and
are willing to have your tables slightly slower and larger
than necessary, you can increase the default pointer size by
setting this variable.
[DEFAULT] CHARACTER SET
Specify a default character set for the table.
CHARSET
is a synonym for
CHARACTER SET
.
COLLATE
Specify a default collation for the table.
CHECKSUM
Set this to 1 if you want MySQL to maintain a live checksum
for all rows (that is, a checksum that MySQL updates
automatically as the table changes). This makes the table a
little slower to update, but also makes it easier to find
corrupted tables. The CHECKSUM TABLE
statement reports the checksum. (MyISAM
only.)
COMMENT
A comment for the table, up to 60 characters long.
CONNECTION
The connection string for a FEDERATED
table. This option is available as of MySQL 5.0.13; before
that, use a COMMENT
option for the
connection string.
MAX_ROWS
The maximum number of rows you plan to store in the table. This is not a hard limit, but rather an indicator that the table must be able to store at least this many rows.
MIN_ROWS
The minimum number of rows you plan to store in the table.
PACK_KEYS
PACK_KEYS
takes effect only with
MyISAM
tables. Set this option to 1 if
you want to have smaller indexes. This usually makes updates
slower and reads faster. Setting the option to 0 disables
all packing of keys. Setting it to
DEFAULT
tells the storage engine to pack
only long CHAR
or
VARCHAR
columns.
If you do not use PACK_KEYS
, the default
is to pack strings, but not numbers. If you use
PACK_KEYS=1
, numbers are packed as well.
When packing binary number keys, MySQL uses prefix compression:
Every key needs one extra byte to indicate how many bytes of the previous key are the same for the next key.
The pointer to the row is stored in high-byte-first order directly after the key, to improve compression.
This means that if you have many equal keys on two
consecutive rows, all following “same” keys
usually only take two bytes (including the pointer to the
row). Compare this to the ordinary case where the following
keys takes storage_size_for_key +
pointer_size
(where the pointer size is usually
4). Conversely, you get a significant benefit from prefix
compression only if you have many numbers that are the same.
If all keys are totally different, you use one byte more per
key, if the key is not a key that can have
NULL
values. (In this case, the packed
key length is stored in the same byte that is used to mark
if a key is NULL
.)
PASSWORD
Encrypt the .frm
file with a password.
This option does nothing in the standard MySQL version.
DELAY_KEY_WRITE
Set this to 1 if you want to delay key updates for the table
until the table is closed. See the description of the
delay_key_write
system variable in
Section 5.2.2, “Server System Variables”.
(MyISAM
only.)
ROW_FORMAT
Defines how the rows should be stored. For
MyISAM
tables, the option value can be
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 5.0.3, for InnoDB
tables, rows are stored in compact format
(ROW_FORMAT=COMPACT
) by default. The
non-compact format used in older versions of MySQL can still
be requested by specifying
ROW_FORMAT=REDUNDANT
.
RAID_TYPE
RAID
support has been removed as of MySQL
5.0. For information on RAID
, see
http://dev.mysql.com/doc/refman/4.1/en/create-table.html.
UNION
UNION
is used when you want to access a
collection of identical MyISAM
tables as
one. This works only with MERGE
tables.
See Section 14.3, “The MERGE
Storage Engine”.
You must have SELECT
,
UPDATE
, and DELETE
privileges for the tables you map to a
MERGE
table. (Note:
Formerly, all tables used had to be in the same database as
the MERGE
table itself. This restriction
no longer applies.)
INSERT_METHOD
If you want to insert data into a MERGE
table, you must specify with
INSERT_METHOD
the table into which the
row should be inserted. INSERT_METHOD
is
an option useful for MERGE
tables only.
Use a value of FIRST
or
LAST
to have inserts go to the first or
last table, or a value of NO
to prevent
inserts. See Section 14.3, “The MERGE
Storage Engine”.
DATA DIRECTORY
, INDEX
DIRECTORY
By using DATA
DIRECTORY='
or directory
'INDEX
DIRECTORY='
you can specify where the directory
'MyISAM
storage
engine should put a table's data file and index file. The
directory must be the full pathname to the directory, not a
relative path.
These options work only 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”, for more
complete information.
You can create one table from another by adding a
SELECT
statement at the end of the
CREATE TABLE
statement:
CREATE TABLEnew_tbl
SELECT * FROMorig_tbl
;
MySQL creates new columns for all elements in the
SELECT
. For example:
mysql>CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
->PRIMARY KEY (a), KEY(b))
->ENGINE=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.
In a table resulting from CREATE TABLE ...
SELECT
, columns named only in the CREATE
TABLE
part come first. Columns named in both parts or
only in the SELECT
part come after that. The
data type of SELECT
columns can be overridden
by also specifying the column in the CREATE
TABLE
part.
If any errors occur while copying the data to the table, it is automatically dropped and not created.
CREATE TABLE ... SELECT
does 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 data 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;
You can also explicitly specify the data type for a generated column:
CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;
Use LIKE
to create an empty table based on
the definition of another table, including any column attributes
and indexes defined in the original table:
CREATE TABLEnew_tbl
LIKEorig_tbl
;
CREATE TABLE ... LIKE
does not preserve 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 rows that duplicate unique key values.
With IGNORE
, new rows that duplicate an
existing row on a unique key value are discarded. With
REPLACE
, new rows replace rows 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 binary log can be used to re-create the
original tables, MySQL does 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. These might be
changes to a data type, to attributes associated with a data
type, or to an index specification.
Possible data type changes are given in the following list. These occur prior to MySQL 5.0.3. As of 5.0.3, an error occurs if a column cannot be created using the specified data type.
VARCHAR
columns with a length less than
four are changed to CHAR
.
If any column in a table has a variable length, the entire
row becomes variable-length as a result. Therefore, if a
table contains any variable-length columns
(VARCHAR
, TEXT
, or
BLOB
), all CHAR
columns longer than three characters are changed to
VARCHAR
columns. This does not 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
Chapter 14, Storage Engines and Table Types.
Before MySQL 5.0.3, a 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
. Note that this conversion
results in a change in behavior with regard to treatment
of trailing spaces.
Similar conversions occur for BINARY
and VARBINARY
, except that they are
converted to a BLOB
type.
Starting with MySQL 5.0.3, a CHAR
or
BINARY
column with a length
specification greater than 255 is not silently converted.
Instead, an error occurs. From MySQL 5.0.6 on, silent
conversion of VARCHAR
and
VARBINARY
columns with a length
specification greater than 65,535 does not occur if strict
SQL mode is enabled. Instead, an error occurs.
For a specification of
DECIMAL(
,
if M
,D
)M
is not larger than
D
, it is adjusted upward. For
example, DECIMAL(10,10)
becomes
DECIMAL(11,10)
.
Other silent column specification changes include changes to attribute or index specifications:
TIMESTAMP
display sizes are discarded.
Note that TIMESTAMP
columns have
changed considerably in recent versions of MySQL prior to
5.0; for a description of these changes, see the
MySQL 3.23, 4.0, 4.1 Reference Manual.
Columns that are part of a PRIMARY KEY
are made NOT NULL
even if not declared
that way.
Trailing spaces are automatically deleted from
ENUM
and SET
member
values when the table is created.
MySQL maps certain data types used by other SQL database vendors to MySQL types. See Section 11.7, “Using Data Types from Other Database Engines”.
If you include a USING
clause to
specify an index type that is not legal for a given
storage engine, but there is another index type available
that the engine can use without affecting query results,
the engine uses the available type.
To see whether MySQL used a data type other than the one you
specified, issue a DESCRIBE
or
SHOW CREATE TABLE
statement after creating
or altering the table.
Certain other data type changes can occur if you compress a table using myisampack. See Section 14.1.3.3, “Compressed Table Characteristics”.
DROP {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. DROP SCHEMA
is a
synonym for DROP DATABASE
as of MySQL 5.0.2.
IF EXISTS
is used to prevent an error from
occurring if the database does not exist.
If you use DROP DATABASE
on a symbolically
linked database, both the link and the original database are
deleted.
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:
All files with these extensions:
.BAK | .DAT | .HSH |
.MRG | .MYD | .ISD |
.MYI | .db | .frm |
All subdirectories with names that consist of two hex digits
00
-ff
. These are
subdirectories used for RAID
tables.
(These directories are not removed as of MySQL 5.0, when
support for RAID
tables was removed. You
should convert any existing RAID
tables
and remove these directories manually before upgrading to
MySQL 5.0. See Section 2.10.2, “Upgrading from MySQL 4.1 to 5.0”.)
The db.opt
file, if it exists.
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.7, “mysqladmin — Client for Administering a MySQL Server”.
DROP INDEXindex_name
ONtbl_name
DROP INDEX
drops the index named
index_name
from the table
tbl_name
. This statement is mapped to
an ALTER TABLE
statement to drop the index.
See Section 13.1.2, “ALTER TABLE
Syntax”.
DROP [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!
Use IF EXISTS
to prevent an error from
occurring for tables that do not exist. A
NOTE
is generated for each non-existent table
when using IF EXISTS
. See
Section 13.5.4.25, “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 use the TEMPORARY
keyword.
The TEMPORARY
keyword has the following
effects:
The statement drops only TEMPORARY
tables.
The statement does not end an ongoing transaction.
No access rights are checked. (A
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 do not accidentally drop a non-TEMPORARY
table.
RENAME TABLEtbl_name
TOnew_tbl_name
[,tbl_name2
TOnew_tbl_name2
] ...
This statement renames one or more tables.
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 (assuming that backup_table
does not
already exist):
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
tmp_table
does not already exist):
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 use
RENAME TABLE
to move a table from one
database to another:
RENAME TABLEcurrent_db.tbl_name
TOother_db.tbl_name;
As of MySQL 5.0.14, RENAME TABLE
also works
for views, as long as you do not try to rename a view into a
different database.
When you execute RENAME
, you cannot 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 does a reverse rename for all renamed tables to return everything to its original state.
Single-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROMtbl_name
[WHEREwhere_condition
] [ORDER BY ...] [LIMITrow_count
]
Multiple-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]tbl_name
[.*] [,tbl_name
[.*]] ... FROMtable_references
[WHEREwhere_condition
]
Or:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROMtbl_name
[.*] [,tbl_name
[.*]] ... USINGtable_references
[WHEREwhere_condition
]
For the single-table syntax, the DELETE
statement deletes rows from tbl_name
and returns the number of rows deleted. The
WHERE
clause, if given, specifies the
conditions that identify which rows to delete. With no
WHERE
clause, all rows are deleted. If the
ORDER BY
clause is specified, the rows are
deleted in the order that is specified. The
LIMIT
clause places a limit on the number of
rows that can be deleted.
For the multiple-table syntax, DELETE
deletes
from each tbl_name
the rows that
satisfy the conditions. In this case, ORDER
BY
and LIMIT
cannot be used.
where_condition
is an expression that
evaluates to true for each row to be deleted. It is specified as
described in Section 13.2.7, “SELECT
Syntax”.
As stated, a DELETE
statement with no
WHERE
clause deletes all rows. A faster way
to do this, when you do not want to know the number of deleted
rows, is to use TRUNCATE TABLE
. See
Section 13.2.9, “TRUNCATE
Syntax”.
If you delete the row containing the maximum value for an
AUTO_INCREMENT
column, the value is reused
later for a BDB
table, but not for a
MyISAM
or InnoDB
table. If
you delete all rows in the table with DELETE FROM
(without a
tbl_name
WHERE
clause) in
AUTOCOMMIT
mode, the sequence starts over for
all storage engines except InnoDB
and
MyISAM
. There are some exceptions to this
behavior for InnoDB
tables, as discussed in
Section 14.2.6.3, “How AUTO_INCREMENT
Columns Work 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:
If you specify LOW_PRIORITY
, the server
delays execution of the DELETE
until no
other clients are reading from the table.
For MyISAM
tables, if you use the
QUICK
keyword, the storage engine does
not merge index leaves during delete, which may speed up
some kinds of delete operations.
The 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
OPTION
are returned as warnings.
The speed of delete operations may also be affected by factors
discussed in Section 7.2.18, “Speed of DELETE
Statements”.
In MyISAM
tables, deleted rows are maintained
in a linked list and subsequent INSERT
operations reuse old row 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 8.2, “myisamchk — MyISAM
Table-Maintenance Utility”.
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 are replaced by similar index values
from rows inserted later. In this case, the holes left by
deleted values are reused.
DELETE QUICK
is not useful when deleted
values lead to undef-filled index blocks spanning a range of
index values for which new inserts 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:
Create a table that contains an indexed
AUTO_INCREMENT
column.
Insert many rows into the table. Each insert results in an index value that is added to the high end of the index.
Delete a block of rows at the low end of the column range
using DELETE QUICK
.
In this scenario, the index blocks associated with the deleted
index values become undef-filled but are not merged with other
index blocks due to the use of QUICK
. They
remain undef-filled when new inserts occur, because new rows do
not have index values in the deleted range. Furthermore, they
remain undef-filled 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 undef-filled blocks. To reclaim
unused index space under these circumstances, 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
option to
row_count
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 given
DELETE
statement does not 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 by
timestamp_column
, and deletes the first
(oldest) one:
DELETE FROM somelog WHERE user = 'jcole' ORDER BY timestamp_column LIMIT 1;
You can specify multiple tables in a DELETE
statement to delete rows from one or more tables depending on
the particular condition in the WHERE
clause.
However, you cannot use ORDER BY
or
LIMIT
in a multiple-table
DELETE
. The
table_references
clause lists the
tables involved in the join. Its syntax is described in
Section 13.2.7.1, “JOIN
Syntax”.
For the first multiple-table syntax, only matching rows from the
tables listed before the FROM
clause are
deleted. For the second multiple-table 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 have additional tables that are used only 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 tables when searching for rows to
delete, but delete matching rows only from tables
t1
and t2
.
The preceding examples show inner joins that use 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, you should 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: If you provide an alias for a table, you must use the alias when referring to the table:
DELETE t1 FROM test AS t1, test2 WHERE ...
Cross-database deletes are supported for multiple-table deletes, but in this case, you must refer to the tables without using aliases. For example:
DELETE test1.tmp1, test2.tmp2 FROM test1.tmp1, test2.tmp2 WHERE ...
Currently, you cannot delete from a table and select from the same table in a subquery.
DOexpr
[,expr
] ...
DO
executes the expressions but does not
return any results. In most respects, DO
is
shorthand for SELECT
, but has the advantage that it is slightly faster
when you do not care about the result. However,
expr
,
...DO
does not report any errors. Instead, it
reports errors as warnings.
DO
is useful primarily with functions that
have side effects, such as RELEASE_LOCK()
.
HANDLERtbl_name
OPEN [ ASalias
] HANDLERtbl_name
READindex_name
{ = | >= | <= | < } (value1
,value2
,...) [ WHEREwhere_condition
] [LIMIT ... ] HANDLERtbl_name
READindex_name
{ FIRST | NEXT | PREV | LAST } [ WHEREwhere_condition
] [LIMIT ... ] HANDLERtbl_name
READ { FIRST | NEXT } [ WHEREwhere_condition
] [LIMIT ... ] HANDLERtbl_name
CLOSE
The HANDLER
statement provides direct access
to table storage engine interfaces. It is available for
MyISAM
and InnoDB
tables.
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
open 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 my_idx
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 ... READ my_idx = (col_a_val,col_b_val,col_c_val) ... HANDLER ... READ my_idx = (col_a_val,col_b_val) ... HANDLER ... READ my_idx = (col_a_val) ...
To employ the HANDLER
interface to refer to a
table's PRIMARY KEY
, use the quoted
identifier `PRIMARY`
:
HANDLER tbl_name
READ `PRIMARY` ...
The second HANDLER ... READ
syntax fetches a
row from the table in index order that matches the
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
when a full
table scan is desired. Natural row order is the order in which
rows are stored in a tbl_name
READ
index_name
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.2.7, “SELECT
Syntax”.
HANDLER ... CLOSE
closes a table that was
opened with HANDLER ... OPEN
.
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 the current thread or other threads)
and these modifications might be only partially visible to
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
:
A designated storage engine handler object is allocated
for the HANDLER ... OPEN
. The object
is reused for subsequent HANDLER
statements for that table; it need not be reinitialized
for each one.
There is less parsing involved.
There is no optimizer or query-checking overhead.
The table does not have to be locked between two handler requests.
The handler interface does not have to provide a
consistent look of the data (for example, dirty reads
are allowed), so the storage engine can use
optimizations that SELECT
does not
normally allow.
For applications that use a low-level
ISAM
-like interface,
HANDLER
makes it much easier to port them
to MySQL.
HANDLER
enables you to traverse a
database in a manner that is difficult (or even impossible)
to accomplish 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 [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO]tbl_name
[(col_name
,...)] VALUES ({expr
| DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATEcol_name
=expr
, ... ]
Or:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO]tbl_name
SETcol_name
={expr
| DEFAULT}, ... [ ON DUPLICATE KEY UPDATEcol_name
=expr
, ... ]
Or:
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO]tbl_name
[(col_name
,...)] SELECT ... [ ON DUPLICATE KEY UPDATEcol_name
=expr
, ... ]
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. INSERT ... SELECT
is
discussed further in Section 13.2.4.1, “INSERT ... SELECT
Syntax”.
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. See
Section 13.2.6, “REPLACE
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:
You can provide a comma-separated list of column names
following the table name. In this case, a value for each
named column must be provided by the
VALUES
list or the
SELECT
statement.
If you do not specify a list of column names for
INSERT ... VALUES
or INSERT ...
SELECT
, values for every column in the table must
be provided by the VALUES
list or the
SELECT
statement. If you do not know the
order of the columns in the table, use DESCRIBE
to find out.
tbl_name
The SET
clause indicates the column names
explicitly.
Column values can be given in several ways:
If you are not running in strict SQL mode, any column not explicitly given a value is set to its default (explicit or implicit) value. For example, if you specify a column list that does not name all the columns in the table, unnamed columns are set to their default values. Default value assignment is described in Section 11.1.4, “Data Type Default Values”. See also Section 1.9.6.2, “Constraints on Invalid Data”.
If you want an INSERT
statement to
generate an error unless you explicitly specify values for
all columns that do not have a default value, you should use
strict mode. See Section 5.2.5, “The Server SQL Mode”.
Use the keyword DEFAULT
to set a column
explicitly to its default value. This makes it easier to
write INSERT
statements that assign
values to all but a few columns, because it enables 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.
You can also use
DEFAULT(
as a more general form that can be used in expressions to
produce a given column's default value.
col_name
)
If both the column list and the VALUES
list are empty, INSERT
creates a row with
each column set to its default value:
INSERT INTO tbl_name
() VALUES();
In strict mode, an error occurs if any column doesn't have a default value. Otherwise, MySQL uses the implicit default value for any column that does not have an explicitly defined default.
You can specify an expression
expr
to provide a column value.
This might involve type conversion if the type of the
expression does not match the type of the column, and
conversion of a given value can result in different inserted
values depending on the data type. For example, inserting
the string '1999.0e-2'
into an
INT
, FLOAT
,
DECIMAL(10,6)
, or YEAR
column results in the values 1999
,
19.9921
, 19.992100
,
and 1999
being inserted, respectively.
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 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 previously been assigned:
INSERT INTO tbl_name
(col1,col2) VALUES(15,col1*2);
But the following is not legal, because the value for
col1
refers to col2
,
which is assigned after col1
:
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
returns a 0
.
INSERT
statements that use
VALUES
syntax can insert multiple rows. To do
this, include multiple lists of column values, each enclosed
within parentheses and separated by commas. Example:
INSERT INTO tbl_name
(a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
The values list for each row must be enclosed within parentheses. The following statement is illegal because the number of values in the list does not match the number of column names:
INSERT INTO tbl_name
(a,b,c) VALUES(1,2,3,4,5,6,7,8,9);
The rows-affected value for an INSERT
can be
obtained using the mysql_affected_rows()
C
API function. See Section 22.2.3.1, “mysql_affected_rows()
”.
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 because Duplicates
can be non-zero.) Duplicates
indicates the
number of rows that could not 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:
Inserting NULL
into a column that has
been declared NOT NULL
. For multiple-row
INSERT
statements or INSERT INTO
... SELECT
statements, the column is set to the
implicit default value for the column data type. This is
0
for numeric types, the empty string
(''
) for string types, and the
“zero” value for date and time types.
INSERT INTO ... SELECT
statements are
handled the same way as multiple-row inserts because the
server does not examine the result set from the
SELECT
to see whether it returns a single
row. (For a single-row INSERT
, no warning
occurs when NULL
is inserted into a
NOT NULL
column. Instead, the statement
fails with an error.)
Setting a numeric column to a value that lies outside the column's range. The value is clipped to the closest endpoint of the range.
Assigning a value such as '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
.
Inserting a string into a string column
(CHAR
, VARCHAR
,
TEXT
, or BLOB
) that
exceeds the column's maximum length. The value is truncated
to the column's maximum length.
Inserting a value into a date or time column that is illegal for the data type. The column is set to the appropriate zero value for the type.
If you are using the C API, the information string can be
obtained by invoking the mysql_info()
function. See Section 22.2.3.34, “mysql_info()
”.
If INSERT
inserts a row into a table that has
an AUTO_INCREMENT
column, you can find the
value used for that column by using the SQL
LAST_INSERT_ID()
function. From within the C
API, use the mysql_insert_id()
function.
However, you should note that the two functions do not always
behave identically. The behavior of INSERT
statements with respect to AUTO_INCREMENT
columns is discussed further in
Section 12.9.3, “Information Functions”, and
Section 22.2.3.36, “mysql_insert_id()
”.
The INSERT
statement supports the following
modifiers:
If you use the DELAYED
keyword, the
server puts the row or rows to be inserted into a buffer,
and the client issuing the INSERT DELAYED
statement can then continue immediately. If the table is in
use, the server holds the rows. When the table is free, the
server begins inserting rows, checking periodically to see
whether there are any new read requests for the table. If
there are, the delayed row queue is suspended until the
table becomes free again. See
Section 13.2.4.2, “INSERT DELAYED
Syntax”.
DELAYED
is ignored with INSERT
... SELECT
or INSERT ... ON DUPLICATE KEY
UPDATE
.
If you use the 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. Note that
LOW_PRIORITY
should normally not be used
with MyISAM
tables because doing so
disables concurrent inserts. See
Section 7.3.3, “Concurrent Inserts”.
If you specify HIGH_PRIORITY
, 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.
If you use the IGNORE
keyword, errors
that occur while executing the INSERT
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 row still is not inserted,
but no error is issued. Data conversions that would trigger
errors abort the statement if IGNORE
is
not specified. With IGNORE
, invalid
values are adjusted to the closest 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 actually inserted into the
table.
If you specify ON DUPLICATE KEY UPDATE
,
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. See Section 13.2.4.3, “INSERT ... ON DUPLICATE KEY UPDATE
Syntax”.
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO]tbl_name
[(col_name
,...)] SELECT ... [ ON DUPLICATE KEY UPDATEcol_name
=expr
, ... ]
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 a INSERT ...
SELECT
statements:
Specify IGNORE
to ignore rows that
would cause duplicate-key violations.
DELAYED
is ignored with INSERT
... SELECT
.
The target table of the INSERT
statement may appear in the FROM
clause
of the SELECT
part of the query. (This
was not possible in some older versions of MySQL.)
AUTO_INCREMENT
columns work as usual.
To ensure that the binary log can be used to re-create the
original tables, MySQL does not allow concurrent inserts
for INSERT ... SELECT
statements.
Currently, you cannot insert into a table and select from the same table in a subquery.
In the values part of ON DUPLICATE KEY
UPDATE
, you can refer to columns in other tables, as
long as you do not use GROUP BY
in the
SELECT
part. One side effect is that you
must qualify non-unique column names in the values part.
INSERT DELAYED ...
The DELAYED
option for the
INSERT
statement is a MySQL extension to
standard SQL that is very useful if you have clients that
cannot or need not wait for the INSERT
to
complete. This is a common situation when you use MySQL for
logging and you also periodically run
SELECT
and UPDATE
statements that take a long time to complete.
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 performing many separate inserts.
Note that INSERT DELAYED
is slower than a
normal INSERT
if the table is not otherwise
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.
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.
There are some constraints on the use of
DELAYED
:
INSERT DELAYED
works only with
MyISAM
, MEMORY
, and
ARCHIVE
tables. See
Section 14.1, “The MyISAM
Storage Engine”,
Section 14.4, “The MEMORY
(HEAP
) Storage Engine”, and
Section 14.8, “The ARCHIVE
Storage Engine”.
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
.
INSERT DELAYED
should be used only for
INSERT
statements that specify value
lists. The server ignores DELAYED
for
INSERT ... SELECT
or INSERT
... ON DUPLICATE KEY UPDATE
statements.
Because the INSERT DELAYED
statement
returns immediately, before the rows are inserted, you
cannot use LAST_INSERT_ID()
to get the
AUTO_INCREMENT
value that the statement
might generate.
DELAYED
rows are not visible to
SELECT
statements until they actually
have been inserted.
DELAYED
is ignored on slave replication
servers because it could cause the slave to have different
data than the master.
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.
When a thread executes a DELAYED
statement for a table, a handler thread is created to
process all DELAYED
statements for the
table, if no such handler already exists.
The thread checks whether the handler has previously
acquired a DELAYED
lock; 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 waits for all ALTER TABLE
locks
or FLUSH TABLES
statements to finish,
to ensure that the table structure is up to date.
The thread executes the 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.
The client cannot obtain from the server the number of
duplicate rows or the 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 does not return
anything meaningful, for the same reason.)
The binary log is updated by the handler thread when the row is inserted into the table. In case of multiple-row inserts, the binary log is updated when the first row is inserted.
Each time that 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.
When the handler has no more rows in its queue, the table
is unlocked. If no new INSERT DELAYED
statements are received within
delayed_insert_timeout
seconds, the
handler terminates.
If more than delayed_queue_size
rows
are pending in a specific handler queue, the thread
requesting INSERT DELAYED
waits until
there is room in the queue. This is done to ensure that
mysqld does not use all memory for the
delayed memory queue.
The handler thread shows up in the MySQL process list with
delayed_insert
in the
Command
column. It is killed if you
execute a FLUSH TABLES
statement or
kill it with KILL
. However,
before exiting, it first stores all queued rows into the
table. During this time it does not accept any new
thread_id
INSERT
statements from other threads.
If you execute an INSERT DELAYED
statement after this, a new handler thread is created.
Note that this means that INSERT
DELAYED
statements have higher priority than
normal INSERT
statements if there is an
INSERT DELAYED
handler running. Other
update statements have to wait until the INSERT
DELAYED
queue is empty, someone terminates the
handler thread (with KILL
), or
someone executes a thread_id
FLUSH TABLES
.
The following status variables provide information about
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 |
You can view these variables by issuing a SHOW
STATUS
statement or by executing a
mysqladmin extended-status command.
If you specify ON DUPLICATE KEY UPDATE
, 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 contains the value
1
, the following two statements have
identical effect:
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1; 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.
If column b
is also unique, the
INSERT
is equivalent to this
UPDATE
statement instead:
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 an ON DUPLICATE
KEY
clause on tables with multiple unique indexes.
You can use the
VALUES(
function in the col_name
)UPDATE
clause to refer to
column values from the INSERT
portion of
the INSERT ... UPDATE
statement. In other
words,
VALUES(
in the col_name
)UPDATE
clause refers to the value of
col_name
that would be inserted,
had 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:
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:
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=3; INSERT INTO table (a,b,c) VALUES (4,5,6) ON DUPLICATE KEY UPDATE c=9;
The DELAYED
option is ignored when you use
ON DUPLICATE KEY UPDATE
.
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name
' [REPLACE | IGNORE] INTO TABLEtbl_name
[FIELDS [TERMINATED BY 'string
'] [[OPTIONALLY] ENCLOSED BY 'char
'] [ESCAPED BY 'char
'] ] [LINES [STARTING BY 'string
'] [TERMINATED BY 'string
'] ] [IGNOREnumber
LINES] [(col_name_or_user_var
,...)] [SETcol_name
=expr
,...)]
The LOAD DATA INFILE
statement reads rows
from a text file into a table at a very high speed. The filename
must be given as a literal string.
LOAD DATA INFILE
is the complement of
SELECT ... INTO OUTFILE
. (See
Section 13.2.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.
For more information about the efficiency of
INSERT
versus LOAD DATA
INFILE
and speeding up LOAD DATA
INFILE
, see Section 7.2.16, “Speed of INSERT
Statements”.
The character set indicated by the
character_set_database
system variable is
used to interpret the information in the file. SET
NAMES
and the setting of
character_set_client
do not affect
interpretation of input.
Note that it is currently not possible to load data files that
use the ucs2
character set.
As of MySQL 5.0.19, the
character_set_filesystem
system variable
controls the interpretation of the filename.
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.12, “mysqlimport — A Data Import Program”.
If you use LOW_PRIORITY
, execution of the
LOAD DATA
statement is delayed until no other
clients are reading from the table.
If you specify CONCURRENT
with a
MyISAM
table that satisfies the condition for
concurrent inserts (that is, it contains no free blocks in the
middle), 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.
The LOCAL
keyword, if specified, is
interpreted with respect to the client end of the connection:
If LOCAL
is specified, the file is read
by the client program on the client host and sent to the
server. The file can be given as a full pathname to specify
its exact location. If given as a relative pathname, the
name is interpreted relative to the directory in which the
client program was started.
If LOCAL
is not specified, the file must
be located on the server host and is read directly by the
server. The server uses the following rules to locate the
file:
If the filename is an absolute pathname, the server uses it as given.
If the filename is a relative pathname with one or more leading components, the server searches for the file relative to the server's data directory.
If a filename with no leading components is given, the server looks for the file in the database directory of the default database.
Note that, in the non-LOCAL
case, these rules
mean that a file named as ./myfile.txt
is
read from the server's data directory, whereas the file named as
myfile.txt
is read from the database
directory of the default database. For example, if
db1
is the default database, the following
LOAD DATA
statement reads the file
data.txt
from the database directory for
db1
, even though the statement explicitly
loads the file into a table in the db2
database:
LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;
Windows pathnames are specified using forward slashes rather than backslashes. If you do use backslashes, you must double them.
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.8.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.
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
does
not work. See Section 5.7.4, “Security Issues with LOAD DATA LOCAL
”.
On Unix, 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
The REPLACE
and IGNORE
keywords control handling of input rows that duplicate existing
rows 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 key or unique index as an existing
row. See Section 13.2.6, “REPLACE
Syntax”.
If you specify IGNORE
, input rows that
duplicate an existing row on a unique key value are skipped.
If you do not specify either option, the behavior depends on
whether 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
). Normally, this makes LOAD DATA
INFILE
much faster when you have many indexes. 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.16, “Speed of INSERT
Statements”.
For both the LOAD DATA INFILE
and
SELECT ... INTO OUTFILE
statements, the
syntax of the FIELDS
and
LINES
clauses is the same. 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 specify no FIELDS
clause, the defaults
are the same as if you had written this:
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
If you specify no LINES
clause, the defaults
are 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:
Look for line boundaries at newlines.
Do not skip over any line prefix.
Break lines into fields at tabs.
Do not expect fields to be enclosed within any quoting characters.
Interpret occurrences of tab, newline, or
‘\
’ preceded by
‘\
’ as literal characters
that are part of field values.
Conversely, the defaults cause SELECT ... INTO
OUTFILE
to act as follows when writing output:
Write tabs between fields.
Do not enclose fields within any quoting characters.
Use ‘\
’ to escape instances
of tab, newline, or ‘\
’ that
occur within field values.
Write newlines at the ends of lines.
Backslash is the MySQL escape character within strings, so to
write FIELDS ESCAPED BY '\\'
, you must
specify two backslashes for the value to be interpreted 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
'
to skip
over the prefix, and anything before it. If
a line does not include the prefix, the entire line is skipped.
Suppose that you issue the following statement:
prefix_string
'
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test FIELDS TERMINATED BY ',' LINES STARTING BY 'xxx';
If the data file looks like this:
xxx"abc",1 something xxx"def",2 "ghi",3
The resulting rows will be ("abc",1)
and
("def",2)
. The third row in the file is
skipped because it does not contain the prefix.
The IGNORE
option can be used to ignore lines at the start
of the file. For example, you can use number
LINESIGNORE 1
LINES
to skip over an initial header line containing
column names:
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:
SELECT * INTO OUTFILE 'data.txt' FIELDS TERMINATED BY ',' FROM table2;
To read the comma-delimited file back in, the correct statement would be:
LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY ',';
If instead you tried to read in the file with the statement
shown following, it wouldn't work because it instructs
LOAD DATA INFILE
to look for tabs between
fields:
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. For example, many programs can
export data in comma-separate values (CSV) format, such that
lines have fields separated by commas and enclosed within double
quotes. If lines in such a file are terminated by newlines, the
statement shown here illustrates the field- and line-handling
options you would use to load the file:
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
CREATE TABLE jokes (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, joke TEXT NOT NULL); 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
values from columns that have a string data type (such as
CHAR
, BINARY
,
TEXT
, or ENUM
):
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 inadvertently 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 regardless of whether 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 are 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:
The FIELDS ESCAPED BY
character
The FIELDS [OPTIONALLY] ENCLOSED BY
character
The first character of the FIELDS TERMINATED
BY
and LINES TERMINATED BY
values
ASCII 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:
If LINES TERMINATED BY
is an empty string
and FIELDS TERMINATED BY
is non-empty,
lines are also terminated with FIELDS TERMINATED
BY
.
If the 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 does not contain all fields, the
rest of the columns are set to their default values. If you
do not 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 does 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:
For the default 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
‘\
’).
If 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'
.
If FIELDS ESCAPED BY
is empty,
NULL
is written as the word
NULL
.
With fixed-row format (which is used when 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 both are 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
:
Fixed-size rows (FIELDS TERMINATED BY
and
FIELDS ENCLOSED BY
both empty) and
BLOB
or TEXT
columns.
If you specify one separator that is the same as or a prefix
of another, LOAD DATA INFILE
cannot
interpret the input properly. For example, the following
FIELDS
clause would cause problems:
FIELDS TERMINATED BY '"' ENCLOSED BY '"'
If 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 causes 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:
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:
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 input fields with table columns.
Before MySQL 5.0.3, the column list must contain only names of
columns in the table being loaded, and the
SET
clause is not supported. As of MySQL
5.0.3, the column list can contain either column names or user
variables. With user variables, the SET
clause enables you to perform transformations on their values
before assigning the result to columns.
User variables in the SET
clause can be used
in several ways. The following example uses the first input
column directly for the value of t1.column1
,
and assigns the second input column to a user variable that is
subjected to a division operation before being used for the
value of t1.column2
:
LOAD DATA INFILE 'file.txt' INTO TABLE t1 (column1, @var1) SET column2 = @var1/100;
The SET
clause can be used to supply values
not derived from the input file. The following statement sets
column3
to the current date and time:
LOAD DATA INFILE 'file.txt' INTO TABLE t1 (column1, column2) SET column3 = CURRENT_TIMESTAMP;
You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column:
LOAD DATA INFILE 'file.txt' INTO TABLE t1 (column1, @dummy, column2, @dummy, column3);
Use of the column/variable list and SET
clause is subject to the following restrictions:
Assignments in the SET
clause should have
only column names on the left hand side of assignment
operators.
You can use subqueries in the right hand side of
SET
assignments. A subquery that returns
a value to be assigned to a column may be a scalar subquery
only. Also, you cannot use a subquery to select from the
table that is being loaded.
Lines ignored by an IGNORE
clause are not
processed for the column/variable list or
SET
clause.
User variables cannot be used when loading data with fixed-row format because user variables do not have a display width.
When processing an input line, LOAD DATA
splits it into fields and uses the values according to the
column/variable list and the SET
clause, if
they are present. Then the resulting row is inserted into the
table. If there are BEFORE INSERT
or
AFTER INSERT
triggers for the table, they are
activated before or after inserting the row, respectively.
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 11.1.4, “Data Type Default Values”.
An empty field value is interpreted differently than if the field value is missing:
For string types, the column is set to the empty string.
For numeric types, the column is set to
0
.
For date and time types, the column is set to the appropriate “zero” value for the type. See Section 11.3, “Date and Time Types”.
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 if the
TIMESTAMP
column's default value is the
current timestamp and it is omitted from the field list when a
field list is specified.
LOAD DATA INFILE
regards all input as
strings, so you cannot 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 22.2.3.34, “mysql_info()
”.
Warnings occur under the same circumstances as when values are
inserted via the INSERT
statement (see
Section 13.2.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.
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.25, “SHOW WARNINGS
Syntax”.
REPLACE [LOW_PRIORITY | DELAYED] [INTO]tbl_name
[(col_name
,...)] VALUES ({expr
| DEFAULT},...),(...),...
Or:
REPLACE [LOW_PRIORITY | DELAYED] [INTO]tbl_name
SETcol_name
={expr
| DEFAULT}, ...
Or:
REPLACE [LOW_PRIORITY | DELAYED] [INTO]tbl_name
[(col_name
,...)] SELECT ...
REPLACE
works exactly like
INSERT
, except that if an old row in the
table has the same value as a new row for a PRIMARY
KEY
or a UNIQUE
index, the old row
is deleted before the new row is inserted. See
Section 13.2.4, “INSERT
Syntax”.
REPLACE
is a MySQL extension to the SQL
standard. It either inserts, or deletes and
inserts. If you're looking for a statement that follows the SQL
standard, and that either inserts or
updates, see
Section 13.2.4.3, “INSERT ... ON DUPLICATE KEY UPDATE
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 cannot refer to values from the
current row and use them in the new row. If you use an
assignment such as SET
, the reference
to the column name on the right hand side is treated as
col_name
=
col_name
+ 1DEFAULT(
,
so the assignment is equivalent to col_name
)SET
.
col_name
=
DEFAULT(col_name
) + 1
To use REPLACE
, you must have both the
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.
MySQL uses the following algorithm for
REPLACE
(and LOAD DATA ...
REPLACE
):
Try to insert the new row into the table
While the insertion fails because a duplicate-key error occurs for a primary key or unique index:
Delete from the table the conflicting row that has the duplicate key value
Try again to insert the new row into the table
SELECT [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
, ... [FROMtable_references
[WHEREwhere_condition
] [GROUP BY {col_name
|expr
|position
} [ASC | DESC], ... [WITH ROLLUP]] [HAVINGwhere_condition
] [ORDER BY {col_name
|expr
|position
} [ASC | DESC], ...] [LIMIT {[offset
,]row_count
|row_count
OFFSEToffset
}] [PROCEDUREprocedure_name
(argument_list
)] [INTO OUTFILE 'file_name
'export_options
| INTO DUMPFILE 'file_name
'] [FOR UPDATE | LOCK IN SHARE MODE]]
SELECT
is used to retrieve rows selected from
one or more tables, and can include UNION
statements and subqueries. See Section 13.2.7.2, “UNION
Syntax”, and
Section 13.2.8, “Subquery Syntax”.
The most commonly used clauses of SELECT
statements are these:
Each select_expr
indicates a
column that you want to retrieve. There must be at least one
select_expr
.
table_references
indicates the
table or tables from which to retrieve rows. Its syntax is
described in Section 13.2.7.1, “JOIN
Syntax”.
The WHERE
clause, if given, indicates the
condition or conditions that rows must satisfy to be
selected. where_condition
is an
expression that evaluates to true for each row to be
selected. The statement selects all rows if there is no
WHERE
clause.
In the WHERE
clause, you can use any of
the functions and operators that MySQL supports, except for
aggregate (summary) functions. See
Chapter 12, Functions and Operators.
SELECT
can also be used to retrieve rows
computed without reference to any table.
For example:
mysql> SELECT 1 + 1;
-> 2
You are allowed to specify DUAL
as a dummy
table name in situations where no tables are referenced:
mysql> SELECT 1 + 1 FROM DUAL;
-> 2
DUAL
is purely for compatibility with some
other database servers that require a FROM
clause. MySQL does not require the clause if no tables are
referenced.
In general, 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. The exception is that the
INTO
clause can appear either as shown in the
syntax description or immediately preceding the
FROM
clause.
A select_expr
can be given an
alias using AS
. The alias
is used as the expression's column name and can be used in
alias_name
GROUP BY
, ORDER BY
, or
HAVING
clauses. For example:
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:
SELECT CONCAT(last_name,', ',first_name) full_name FROM mytable ORDER BY full_name;
However, 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:
SELECT columna columnb FROM mytable;
For this reason, it is good practice to be in the habit of
using AS
explicitly when specifying
column aliases.
It is not allowable to use a column alias in a
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”.
The FROM
clause
indicates the table or 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.2.7.1, “table_references
JOIN
Syntax”.
For each table specified, you can optionally specify an
alias.
tbl_name
[[AS]alias
] [{USE|IGNORE|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.2.7.1, “JOIN
Syntax”.
You can use SET
max_seeks_for_key=
as an alternative way to force MySQL to prefer key scans
instead of table scans. See
Section 5.2.2, “Server System Variables”.
value
You can refer to a table within the default database as
tbl_name
, or as
db_name
.tbl_name
to specify a database explicitly. You can refer to a column
as col_name
,
tbl_name
.col_name
,
or
db_name
.tbl_name
.col_name
.
You need not specify a tbl_name
or
db_name
.tbl_name
prefix for a column reference unless the reference would be
ambiguous. See Section 9.2.1, “Identifier Qualifiers”, for
examples of ambiguity that require the more explicit column
reference forms.
A table reference can be aliased using
or
tbl_name
AS
alias_name
tbl_name alias_name
:
SELECT t1.name, t2.salary FROM employee AS t1, info AS t2 WHERE t1.name = t2.name; SELECT t1.name, t2.salary FROM employee t1, info t2 WHERE t1.name = t2.name;
Columns selected for output can be referred to in
ORDER BY
and GROUP BY
clauses using column names, column aliases, or column
positions. Column positions are integers and begin with 1:
SELECT college, region, seed FROM tournament ORDER BY region, seed; SELECT college, region AS r, seed AS s FROM tournament ORDER BY r, s; 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.
If you use GROUP BY
, output rows are
sorted according to the GROUP BY
columns
as if you had an ORDER BY
for the same
columns. To avoid the overhead of sorting that
GROUP BY
produces, add ORDER BY
NULL
:
SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;
MySQL extends the GROUP BY
clause 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;
MySQL extends the use of GROUP BY
to
allow selecting fields that are not mentioned in the
GROUP BY
clause. If you are not getting
the results that you expect from your query, please read the
description of GROUP BY
found in
Section 12.10, “Functions and Modifiers for Use with GROUP BY
Clauses”.
GROUP BY
allows a WITH
ROLLUP
modifier. See
Section 12.10.2, “GROUP BY
Modifiers”.
The HAVING
clause is applied nearly last,
just before items are sent to the client, with no
optimization. (LIMIT
is applied after
HAVING
.)
Before MySQL 5.0.2, a HAVING
clause can
refer to any column or alias named in a
select_expr
in the
SELECT
list or in outer subqueries, and
to aggregate functions. However, the SQL standard requires
that HAVING
must reference only columns
in the GROUP BY
clause or columns used in
aggregate functions. To accommodate both standard SQL and
the MySQL-specific behavior of being able to refer columns
in the SELECT
list, MySQL 5.0.2 and up
allows HAVING
to refer to columns in the
SELECT
list, columns in the
GROUP BY
clause, columns in outer
subqueries, and to aggregate functions.
For example, the following statement works in MySQL 5.0.2 but produces an error for earlier versions:
mysql> SELECT COUNT(*) FROM t GROUP BY col1 HAVING col1 = 2;
If the HAVING
clause refers to a column
that is ambiguous, a warning occurs. In the following
statement, col2
is ambiguous because it
is used as both an alias and a column name:
SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;
Preference is given to standard SQL behavior, so if a
HAVING
column name is used both in
GROUP BY
and as an aliased column in the
output column list, preference is given to the column in the
GROUP BY
column.
Do not use HAVING
for items that should
be in the WHERE
clause. For example, do
not write the following:
SELECTcol_name
FROMtbl_name
HAVINGcol_name
> 0;
Write this instead:
SELECTcol_name
FROMtbl_name
WHEREcol_name
> 0;
The HAVING
clause can refer to aggregate
functions, which the WHERE
clause cannot:
SELECT user, MAX(salary) FROM users GROUP BY user HAVING MAX(salary) > 10;
(This did not work in some older versions of MySQL.)
The 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 both be non-negative integer constants
(except when using prepared statements).
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):
SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15
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:
SELECT * FROM tbl LIMIT 95,18446744073709551615;
With one argument, the value specifies the number of rows to return from the beginning of the result set:
SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows
In other words, LIMIT
is equivalent
to row_count
LIMIT 0,
.
row_count
For prepared statements, you can use placeholders (supported
as of MySQL version 5.0.7). The following statements will
return one row from the tbl
table:
SET @a=1; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?'; EXECUTE STMT USING @a;
The following statements will return the second to sixth row
from the tbl
table:
SET @skip=1; SET @numrows=5; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?'; EXECUTE STMT USING @skip, @numrows;
For compatibility with PostgreSQL, MySQL also supports the
LIMIT
syntax.
row_count
OFFSET
offset
The SELECT ... INTO OUTFILE
'
form of
file_name
'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. file_name
cannot be an
existing file, which among other things prevents files such
as /etc/passwd
and database tables from
being destroyed. As of MySQL 5.0.19, the
character_set_filesystem
system variable
controls the interpretation of the filename.
The SELECT ... INTO OUTFILE
statement is
intended primarily to let you very quickly dump a table to a
text file on the server machine. If you want to create the
resulting file on some client host other than the server
host, you cannot use SELECT ... INTO
OUTFILE
. In that case, you should instead use a
command such as mysql -e "SELECT ..." >
to generate
the file on the client host.
file_name
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.2.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 as a prefix
that precedes following characters on output:
The FIELDS ESCAPED BY
character
The FIELDS [OPTIONALLY] ENCLOSED BY
character
The first character of the FIELDS TERMINATED
BY
and LINES TERMINATED BY
values
ASCII NUL
(the zero-valued byte; what
is actually written following the escape character is
ASCII ‘0
’, not a
zero-valued byte)
The FIELDS TERMINATED BY
,
ENCLOSED BY
, ESCAPED
BY
, or LINES TERMINATED BY
characters must be escaped so that you
can read the file back in reliably. ASCII
NUL
is escaped to make it easier to view
with some pagers.
The resulting file does not have to conform to SQL syntax, so nothing else need be escaped.
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.
Here is an example that produces a file in the comma-separated values (CSV) format used by many programs:
SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;
If you use 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.
Note: Any file created by
INTO OUTFILE
or INTO
DUMPFILE
is writable by all users on the server
host. The reason for this is that the MySQL server cannot
create a file that is owned by anyone other than the user
under whose account it is running. (You should
never run mysqld as
root
for this and other reasons.) The
file thus must be world-writable so that you can manipulate
its contents.
The SELECT
syntax description at the
beginning this section shows the INTO
clause near the end of the statement. It is also possible to
use INTO OUTFILE
or INTO
DUMPFILE
immediately preceding the
FROM
clause.
A PROCEDURE
clause names a procedure that
should process the data in the result set. For an example,
see Section 24.3.1, “Procedure Analyse”.
If you use FOR UPDATE
with 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 LOCK IN SHARE MODE
sets a shared lock that allows other transactions to read
the examined rows but not to update or delete them. See
Section 14.2.10.5, “SELECT ... FOR UPDATE
and SELECT ... LOCK IN SHARE MODE
Locking Reads”.
Following the SELECT
keyword, you can use 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 removal
of duplicate rows from the result set.
HIGH_PRIORITY
,
STRAIGHT_JOIN
, and options beginning with
SQL_
are MySQL extensions to standard SQL.
HIGH_PRIORITY
gives 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 runs even if there is
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, “Optimizing Queries with EXPLAIN
”.
STRAIGHT_JOIN
also can be used in the
table_references
list. See
Section 13.2.7.1, “JOIN
Syntax”.
SQL_BIG_RESULT
can be used with
GROUP BY
or DISTINCT
to tell the optimizer that the result set has many rows. In
this case, MySQL directly uses disk-based temporary tables
if needed, and prefers 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 is small. In this
case, MySQL uses fast temporary tables to store the
resulting table instead of using sorting. This should not
normally be needed.
SQL_CALC_FOUND_ROWS
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.9.3, “Information Functions”.
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 effects any SELECT
in the query.
See Section 5.14, “The MySQL Query Cache”.
SQL_NO_CACHE
tells MySQL not to store the
query result in the query cache. See
Section 5.14, “The MySQL Query Cache”. For a query that uses
UNION
or subqueries, this option effects
any SELECT
in the query.
MySQL supports the following JOIN
syntaxes
for the table_references
part of
SELECT
statements and multiple-table
DELETE
and UPDATE
statements:
table_references:
table_reference
[,table_reference
] ...table_reference
:table_factor
|join_table
table_factor
:tbl_name
[[AS]alias
] [{USE|IGNORE|FORCE} INDEX (key_list
)] | (table_references
) | { OJtable_reference
LEFT OUTER JOINtable_reference
ONconditional_expr
}join_table
:table_reference
[INNER | CROSS] JOINtable_factor
[join_condition
] |table_reference
STRAIGHT_JOINtable_factor
|table_reference
STRAIGHT_JOINtable_factor
ONcondition
|table_reference
LEFT [OUTER] JOINtable_reference
join_condition
|table_reference
NATURAL [LEFT [OUTER]] JOINtable_factor
|table_reference
RIGHT [OUTER] JOINtable_reference
join_condition
|table_reference
NATURAL [RIGHT [OUTER]] JOINtable_factor
join_condition
: ONconditional_expr
| USING (column_list
)
A table reference is also known as a join expression.
The syntax of table_factor
is
extended in comparison with the SQL Standard. The latter
accepts only table_reference
, not a
list of them inside a pair of parentheses.
This is a conservative extension if we consider each comma in
a list of table_reference
items as
equivalent to an inner join. For example:
SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
is equivalent to:
SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
In MySQL, CROSS JOIN
is a syntactic
equivalent to INNER JOIN
(they can replace
each other. In standard SQL, they are not equivalent.
INNER JOIN
is used with an
ON
clause, CROSS JOIN
is
used otherwise.
In versions of MySQL prior to 5.0.1, parentheses in
table_references
were just omitted
and all join operations were grouped to the left. In general,
parentheses can be ignored in join expressions containing only
inner join operations. As of 5.0.1, nested joins are allowed
(see Section 7.2.10, “Nested Join Optimization”).
Further changes in join processing were made in 5.0.12 to make MySQL more compliant with standard SQL. These charges are described later in this section.
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.
The { OJ ... LEFT OUTER JOIN ...}
syntax
shown in the preceding list exists only for compatibility with
ODBC. The curly braces in the syntax should be written
literally; they are not metasyntax as used elsewhere in syntax
descriptions.
A table reference can be aliased using
or
tbl_name
AS
alias_name
tbl_name alias_name
:
SELECT t1.name, t2.salary FROM employee AS t1, info AS t2 WHERE t1.name = t2.name; SELECT t1.name, t2.salary FROM employee t1, info t2 WHERE t1.name = t2.name;
The ON
conditional is any conditional
expression of the form that can be used in a
WHERE
clause.
If there is no matching row for the right table in the
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 rows in a table that have no
counterpart in another table:
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, “LEFT JOIN
and RIGHT JOIN
Optimization”.
The
USING(
clause names a list of columns that must exist in both
tables. If tables column_list
)a
and
b
both contain columns
c1
, c2
, and
c3
, the following join compares
corresponding columns from the two tables:
a LEFT JOIN b USING (c1,c2,c3)
The 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 produce a Cartesian product between
the specified tables (that is, each and every row in the
first table is joined to each and every row in the second
table).
RIGHT JOIN
works analogously to
LEFT JOIN
. To keep code portable across
databases, it is recommended that you 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.
You can provide hints as to which index MySQL should use when
retrieving information from a table. By specifying
USE INDEX
(
, you can tell
MySQL to use only one of the possible indexes to find rows in
the table. The alternative syntax key_list
)IGNORE INDEX
(
can be used to
tell MySQL to not use some particular index. These hints are
useful if key_list
)EXPLAIN
shows that MySQL is using
the wrong index from the list of possible indexes.
You can also use FORCE INDEX
, which acts
like USE INDEX
(
but with the
addition that a table scan is assumed to be
very expensive. In other words, a table
scan is used only if there is no way to use one of the given
indexes to find rows in the table.
key_list
)
USE INDEX
, IGNORE INDEX
,
and FORCE INDEX
affect only 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 is
used when resolving an ORDER BY
or
GROUP BY
.
USE KEY
, IGNORE KEY
, and
FORCE KEY
are synonyms for USE
INDEX
, IGNORE INDEX
, and
FORCE INDEX
.
Some join examples:
SELECT * FROM table1,table2 WHERE table1.id=table2.id; SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; SELECT * FROM table1 LEFT JOIN table2 USING (id); SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON table2.id=table3.id; SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3; SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3;
Join Processing Changes in MySQL 5.0.12
Beginning with MySQL 5.0.12, natural joins and joins with
USING
, including outer join variants, are
processed according to the SQL:2003 standard. These changes
make MySQL more compliant with standard SQL. However, they can
result in different output columns for some joins. Also, some
queries that appeared to work correctly in older versions must
be rewritten to comply with the standard. The following list
provides more detail about several effects of the 5.0.12
change in join processing. The term “previously”
means “prior to MySQL 5.0.12.”
The columns of a NATURAL
join or a
USING
join may be different from
previously. Specifically, redundant output columns no
longer appear, and the order of columns for
SELECT *
expansion may be different
from before.
Consider this set of statements:
CREATE TABLE t1 (i INT, j INT); CREATE TABLE t2 (k INT, j INT); INSERT INTO t1 VALUES(1,1); INSERT INTO t2 VALUES(1,1); SELECT * FROM t1 NATURAL JOIN t2; SELECT * FROM t1 JOIN t2 USING (j);
Previously, the statements produced this output:
+------+------+------+------+ | i | j | k | j | +------+------+------+------+ | 1 | 1 | 1 | 1 | +------+------+------+------+ +------+------+------+------+ | i | j | k | j | +------+------+------+------+ | 1 | 1 | 1 | 1 | +------+------+------+------+
In the first SELECT
statement, column
i
appears in both tables and thus
becomes a join column, so, according to standard SQL, it
should appear only once in the output, not twice.
Similarly, in the second SELECT statement, column
j
is named in the
USING
clause and should appear only
once in the output, not twice. But in both cases, the
redundant column is not eliminated. Also, the order of the
columns is not correct according to standard SQL.
Now the statements produce this output:
+------+------+------+ | j | i | k | +------+------+------+ | 1 | 1 | 1 | +------+------+------+ +------+------+------+ | j | i | k | +------+------+------+ | 1 | 1 | 1 | +------+------+------+
The redundant column is eliminated. Also, the column order is correct according to standard SQL:
First, columns common to both tables, in the order in which they occur in the first table
Second, columns unique to the first table, in order in which they occur in that table
Third, columns unique to the second table, in order in which they occur in that table
The evaluation of multi-way natural joins differs in a way
that can require query rewriting. Suppose that you have
three tables t1(a,b)
,
t2(c,b)
, and t3(a,c)
that each have one row: t1(1,2)
,
t2(10,2)
, and
t3(7,10)
. Suppose also that you have
this NATURAL JOIN
on the three tables:
SELECT ... FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;
Previously, the left operand of the second join was
considered to be t2
, whereas it should
be the nested join (t1 NATURAL JOIN
t2)
. As a result, the columns of
t3
are checked for common columns only
in t2
, and, if t3
has common columns with t1
, these
columns are not used as equi-join columns. Thus,
previously, the preceding query was transformed to the
following equi-join:
SELECT ... FROM t1, t2, t3 WHERE t1.b = t2.b AND t2.c = t3.c;
That join is missing one more equi-join predicate
(t1.a = t3.a)
. As a result, it produces
one row, not the empty result that it should. The correct
equivalent query is this:
SELECT ... FROM t1, t2, t3 WHERE t1.b = t2.b AND t2.c = t3.c AND t1.a = t3.a;
If you require the same query result in current versions of MySQL as in older versions, rewrite the natural join as the first equi-join.
Previously, the comma operator (,
) and
JOIN
both had the same precedence, so
the join expression t1, t2 JOIN t3
was
interpreted as ((t1, t2) JOIN t3)
. Now
JOIN
has higher precedence, so the
expression is interpreted as (t1, (t2 JOIN
t3))
. This change affects statements that use an
ON
clause, because that clause can
refer only to columns in the operands of the join, and the
change in precedence changes interpretation of what those
operands are.
Example:
CREATE TABLE t1 (i1 INT, j1 INT); CREATE TABLE t2 (i2 INT, j2 INT); CREATE TABLE t3 (i3 INT, j3 INT); INSERT INTO t1 VALUES(1,1); INSERT INTO t2 VALUES(1,1); INSERT INTO t3 VALUES(1,1); SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);
Previously, the SELECT
was legal due to
the implicit grouping of t1,t2
as
(t1,t2)
. Now the
JOIN
takes precedence, so the operands
for the ON
clause are
t2
and t3
. Because
t1.i1
is not a column in either of the
operands, the result is an Unknown column 't1.i1'
in 'on clause'
error. To allow the join to be
processed, group the first two tables explicitly with
parentheses so that the operands for the
ON
clause are
(t1,t2)
and t3
:
SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);
Alternatively, avoid the use of the comma operator and use
JOIN
instead:
SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);
This change also applies to statements that mix the comma
operator with INNER JOIN
,
CROSS JOIN
, LEFT
JOIN
, and RIGHT JOIN
, all of
which now have higher precedence than the comma operator.
Previously, the ON
clause could refer
to columns in tables named to its right. Now an
ON
clause can refer only to its
operands.
Example:
CREATE TABLE t1 (i1 INT); CREATE TABLE t2 (i2 INT); CREATE TABLE t3 (i3 INT); SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3;
Previously, the SELECT
statement was
legal. Now the statement fails with an Unknown
column 'i3' in 'on clause'
error because
i3
is a column in
t3
, which is not an operand of the
ON
clause. The statement should be
rewritten as follows:
SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);
Previously, a USING
clause could be
rewritten as an ON
clause that compares
corresponding columns. For example, 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
Now the two clauses no longer are quite the same:
With respect to determining which rows satisfy the join condition, both joins remain semantically identical.
With respect to determining which columns to display
for SELECT *
expansion, the two
joins are not semantically identical. The
USING
join selects the coalesced
value of corresponding columns, whereas the
ON
join selects all columns from
all tables. For the preceding USING
join, SELECT *
selects these
values:
COALESCE(a.c1,b.c1), COALESCE(a.c2,b.c2), COALESCE(a.c3,b.c3)
For the ON
join, SELECT
*
selects these values:
a.c1, a.c2, a.c3, b.c1, b.c2, b.c3
With an inner join,
COALESCE(a.c1,b.c1)
is the same as
either a.c1
or
b.c1
because both columns will have
the same value. With an outer join (such as
LEFT JOIN
), one of the two columns
can be NULL
. That column will be
omitted from the result.
SELECT ... UNION [ALL | DISTINCT] SELECT ... [UNION [ALL | DISTINCT] SELECT ...]
UNION
is used to combine the result from
multiple SELECT
statements into a single
result set.
The column names from the first SELECT
statement are used as the column names for the results
returned. Selected columns listed in corresponding positions
of each SELECT
statement should have the
same data 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.)
If the data types of corresponding SELECT
columns do not match, the types and lengths of the columns in
the UNION
result take into account the
values retrieved by all of the SELECT
statements. For example, consider the following:
mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a |
| bbbbbbbbbb |
+---------------+
(In some earlier versions of MySQL, only the type and length
from the first SELECT
would have been used
and the second row would have been truncated to a length of
1.)
The SELECT
statements are normal select
statements, but with the following restrictions:
Only the last SELECT
statement can use
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.
The default behavior for UNION
is that
duplicate rows are removed from the result. The optional
DISTINCT
keyword has no effect other than
the default because it also specifies duplicate-row removal.
With the optional ALL
keyword,
duplicate-row removal does not occur and the result includes
all matching rows from all the SELECT
statements.
You can mix UNION ALL
and UNION
DISTINCT
in the same query. 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.
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 t1 WHERE a=10 AND B=1) UNION (SELECT a FROM t2 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
. (Alternatively, refer to the
column in the ORDER BY
using its column
position. However, use of column positions is deprecated.)
Also, if a column to be sorted is aliased, the ORDER
BY
clause must refer to the
alias, not the column name. The first of the following
statements will work, but the second will fail with an
Unknown column 'a' in 'order clause'
error:
(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY b; (SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY a;
To apply ORDER BY
or
LIMIT
to an individual
SELECT
, place the clause inside the
parentheses that enclose the SELECT
:
(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
Use of ORDER BY
for individual
SELECT
statements implies nothing about the
order in which the rows appear in the final result because
UNION
by default produces an unordered set
of rows. If ORDER BY
appears with
LIMIT
, it is used to determine the subset
of the selected rows to retrieve for the
SELECT
, but does not necessarily affect the
order of those rows in the final UNION
result. If ORDER BY
appears without
LIMIT
in a SELECT
, it is
optimized away because it will have no effect anyway.
To cause rows in a UNION
result to consist
of the sets of rows retrieved by each
SELECT
one after the other, select an
additional column in each SELECT
to use as
a sort column and add an ORDER BY
following
the last SELECT
:
(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1) UNION (SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col;
To additionally maintain sort order within individual
SELECT
results, add a secondary column to
the ORDER BY
clause:
(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1) UNION (SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a;
ANY
, IN
, and SOME
ALL
EXISTS
and NOT EXISTS
FROM
clause
A subquery is a SELECT
statement within
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.
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 within the outer
query, and in fact it is possible to nest subqueries within
other subqueries, to a considerable depth. A subquery must
always appear within parentheses.
The main advantages of subqueries are:
They allow queries that are structured so that it is possible to isolate each part of a statement.
They provide alternative ways to perform operations that would otherwise require complex joins and unions.
They are, in many people's opinion, readable. Indeed, it was the innovation of subqueries that gave people the original idea of calling the early SQL “Structured Query Language.”
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. A subquery can contain any of the
keywords or clauses that an ordinary SELECT
can contain: DISTINCT
, GROUP
BY
, ORDER BY
,
LIMIT
, joins, index hints,
UNION
constructs, comments, functions, and so
on.
One restriction is that a subquery's outer statement must be one
of: SELECT
, INSERT
,
UPDATE
, DELETE
,
SET
, or DO
. Another
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
,
UPDATE
, and (because subqueries can be used
in the SET
clause) LOAD DATA
INFILE
.
A more comprehensive discussion of restrictions on subquery use, including performance issues for certain forms of subquery syntax, is given in Section I.3, “Restrictions on Subqueries”.
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 almost anywhere 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
.
There are a few contexts in which a scalar subquery cannot be
used. If a statement allows only a literal value, you cannot
use a subquery. For example, LIMIT
requires
literal integer arguments, and LOAD DATA
INFILE
requires a literal string filename. You
cannot use subqueries to supply these values.
When you see examples in the following sections that contain
the rather spartan construct (SELECT column1 FROM
t1)
, imagine that your own code contains much more
diverse and complex constructions.
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 is 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, but remember 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 operators, the
subquery must return a scalar, with the exception that
=
can be used with row subqueries. See
Section 13.2.8.5, “Row Subqueries”.
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.2.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 is not what is meant by the SQL syntax. The syntax means
“there is some b to which a is not equal.” Using
<> SOME
instead helps ensure that
everyone understands the true meaning of the query.
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 (that is,
NULL
) if table t2
contains (0,NULL,1)
.
Finally, if table t2
is empty, the result
is TRUE
. So, the following statement is
TRUE
when table t2
is
empty:
SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);
But this statement is NULL
when table
t2
is empty:
SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2);
In addition, the following statement is
NULL
when table t2
is
empty:
SELECT * FROM t1 WHERE 1 > ALL (SELECT MAX(s1) FROM t2);
In general, tables containing
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 as well. 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 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);
If a subquery returns any rows at all, EXISTS
is
subquery
TRUE
, and NOT EXISTS
is
subquery
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 makes
no difference.
For the preceding example, if t2
contains
any rows, even rows with nothing but NULL
values, the EXISTS
condition is
TRUE
. This is actually an unlikely example
because a [NOT] EXISTS
subquery almost
always contains correlations. Here are some more realistic
examples:
What kind of store is present in one or more cities?
SELECT DISTINCT store_type FROM stores WHERE EXISTS (SELECT * FROM cities_stores WHERE cities_stores.store_type = stores.store_type);
What kind of store is present in no cities?
SELECT DISTINCT store_type FROM stores WHERE NOT EXISTS (SELECT * FROM cities_stores WHERE cities_stores.store_type = stores.store_type);
What kind of store is present in all cities?
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 is 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 does not 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 (SELECTkey_val
FROMtbl_name
WHEREcorrelated_condition
)
Otherwise, they are inefficient and likely to be slow. Rewriting the query as a join might improve performance.
Correlated subqueries cannot refer to the results of aggregate functions from the outer query.
Subqueries are legal in a SELECT
statement's FROM
clause. The actual syntax
is:
SELECT ... FROM (subquery
) [AS]name
...
The [AS]
clause is mandatory, because every table in a
name
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 the sake of illustration, assume that you have this table:
CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
Here is 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 is another example: Suppose that you want to know the average of a set of sums for a grouped table. This does not work:
SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;
However, this query provides 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. Subqueries in the
FROM
clause cannot be correlated
subqueries.
Subqueries in the FROM
clause are executed
even for the EXPLAIN
statement (that is,
derived temporary tables are built). This occurs because upper
level queries need information about all tables during
optimization phase.
There are some errors that apply only to subqueries. This section describes them.
Incorrect number of columns from subquery:
ERROR 1241 (ER_OPERAND_COL) SQLSTATE = 21000 Message = "Operand should contain 1 column(s)"
This error occurs in cases like this:
SELECT (SELECT column1, column2 FROM t2) FROM t1;
You may use a subquery that returns multiple columns, if the purpose is comparison. See Section 13.2.8.5, “Row Subqueries”. However, in other contexts, the subquery must be a scalar operand.
Incorrect number of rows from subquery:
ERROR 1242 (ER_SUBSELECT_NO_1_ROW) SQLSTATE = 21000 Message = "Subquery returns more than 1 row"
This error occurs for statements where the subquery returns more than one row. Consider the following example:
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
If SELECT column1 FROM t2
returns just
one row, the previous query will work. If the subquery
returns more than one row, error 1242 will occur. In that
case, the query should be rewritten as:
SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2);
Incorrectly used table in subquery:
Error 1093 (ER_UPDATE_TABLE_USED) SQLSTATE = HY000 Message = "You can't specify target table 'x' for update in FROM clause"
This error occurs in cases such as the following:
UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);
You can use a subquery for assignment within an
UPDATE
statement because 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, the 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. The following list provides some interesting tricks that you might want to play with:
Use subquery clauses that affect the number or order of the rows in the subquery. For example:
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);
Replace a join with a subquery. For example, try this:
SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN ( SELECT column1 FROM t2);
Instead of this:
SELECT DISTINCT t1.column1 FROM t1, t2 WHERE t1.column1 = t2.column1;
Some subqueries can be transformed to joins for compatibility with older versions of MySQL that do not support subqueries. However, in some cases, converting a subquery to a join may improve performance. See Section 13.2.8.11, “Rewriting Subqueries as Joins for Earlier MySQL Versions”.
Move clauses from outside to inside the subquery. For example, use this query:
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;
Use a row subquery instead of a correlated subquery. For example, use this query:
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);
Use NOT (a = ANY (...))
rather than
a <> ALL (...)
.
Use x = ANY (
rather than table containing
(1,2)
)x=1 OR
x=2
.
Use = ANY
rather than
EXISTS
.
For uncorrelated subqueries that always return one row,
IN
is always slower than
=
. For example, use this query:
SELECT * FROM t1 WHERE t1.col_name
= (SELECT a FROM t2 WHERE b =some_const
);
Instead of this query:
SELECT * FROM t1 WHERE t1.col_name
IN (SELECT a FROM t2 WHERE b =some_const
);
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. See Section 12.9.3, “Information Functions”.
Some optimizations that MySQL itself makes are:
MySQL executes non-correlated subqueries only once. Use
EXPLAIN
to make sure that a given
subquery really is non-correlated.
MySQL rewrites IN
,
ALL
, ANY
, and
SOME
subqueries in an attempt to take
advantage of the possibility that the select-list columns
in the subquery are indexed.
MySQL replaces subqueries of the following form with an
index-lookup function, which EXPLAIN
describes as a special join type
(unique_subquery
or
index_subquery
):
... IN (SELECTindexed_column
FROMsingle_table
...)
MySQL enhances expressions of the following form with an
expression involving 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, available at http://dev.mysql.com/doc/.
In previous versions of MySQL (prior to MySQL 4.1), only
nested queries of the form INSERT ... SELECT
...
and REPLACE ... SELECT ...
were supported. Although this is not the case in MySQL
5.0, it is still true that there are sometimes
other ways to test membership in a set of values. It is also
true that on some occasions, it is not only possible to
rewrite a query without a subquery, but it can be more
efficient to make use of some of these techniques rather than
to use subqueries. One of these is the IN()
construct:
For example, this query:
SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);
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 be rewritten using IN()
:
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.
Today, MySQL Server and many other modern database systems
offer a wide range of outer join types.
MySQL Server 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.
TRUNCATE [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
before version 5.0.3,
TRUNCATE TABLE
is mapped to
DELETE
, so there is no difference. Starting
with MySQL 5.0.3, fast TRUNCATE TABLE
is
available. However, the operation is still mapped to
DELETE
if there are foreign key constraints
that reference the table. (When fast truncate is used, it resets
any AUTO_INCREMENT
counter. From MySQL 5.0.13
on, the AUTO_INCREMENT
counter is reset by
TRUNCATE TABLE
, regardless of whether there
is a foreign key constraint.)
For other storage engines, TRUNCATE TABLE
differs from DELETE
in the following ways in
MySQL 5.0:
Truncate operations drop and re-create the table, which is much faster than deleting rows one by one.
Truncate operations are not transaction-safe; an error occurs when attempting one in the course of an active transaction or active table lock.
The number of deleted rows is not returned.
As long as the table format file
is valid, the table can be re-created as an empty table with
tbl_name
.frmTRUNCATE TABLE
, even if the data or index
files have become corrupted.
The table handler does not remember the last used
AUTO_INCREMENT
value, but starts counting
from the beginning. This is true even for
MyISAM
and InnoDB
,
which normally do not reuse sequence values.
TRUNCATE TABLE
is an Oracle SQL extension
adopted in MySQL.
Single-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE]tbl_name
SETcol_name1
=expr1
[,col_name2
=expr2
...] [WHEREwhere_condition
] [ORDER BY ...] [LIMITrow_count
]
Multiple-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE]table_references
SETcol_name1
=expr1
[,col_name2
=expr2
...] [WHEREwhere_condition
]
For the single-table syntax, the UPDATE
statement updates columns of existing rows in
tbl_name
with new values. The
SET
clause indicates which columns to modify
and the values they should be given. The
WHERE
clause, if given, specifies the
conditions that identify which rows to update. With no
WHERE
clause, all rows are updated. If the
ORDER BY
clause is specified, the rows are
updated in the order that is specified. The
LIMIT
clause places a limit on the number of
rows that can be updated.
For the multiple-table syntax, UPDATE
updates
rows in each table named in
table_references
that satisfy the
conditions. In this case, ORDER BY
and
LIMIT
cannot be used.
where_condition
is an expression that
evaluates to true for each row to be updated. It is specified as
described in Section 13.2.7, “SELECT
Syntax”.
The UPDATE
statement supports the following
modifiers:
If you use the LOW_PRIORITY
keyword,
execution of the UPDATE
is delayed until
no other clients are reading from the table.
If you use the IGNORE
keyword, the update
statement does 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:
UPDATE persondata SET age=age+1;
UPDATE
assignments are evaluated from left to
right. For example, the following statement doubles the
age
column, and then increments it:
UPDATE persondata SET age=age*2, age=age+1;
If you set a column to the value it currently has, MySQL notices this and does not 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 data 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. 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
.
You can use LIMIT
to restrict the
scope of the row_count
UPDATE
. A
LIMIT
clause 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.
You can also perform UPDATE
operations
covering multiple tables. However, you cannot use ORDER
BY
or LIMIT
with a multiple-table
UPDATE
. The
table_references
clause lists the
tables involved in the join. Its syntax is described in
Section 13.2.7.1, “JOIN
Syntax”. Here is an example:
UPDATE items,month SET items.price=month.price WHERE items.id=month.id;
The preceding example shows an inner join that uses the comma
operator, but multiple-table UPDATE
statements can use any type of join allowed in
SELECT
statements, such as LEFT
JOIN
.
You need the UPDATE
privilege only for
columns referenced in a multiple-table UPDATE
that are actually updated. 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 fails and rolls 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.
See Section 14.2.6.4, “FOREIGN KEY
Constraints”.
Currently, you cannot update a table and select from the same table in a subquery.
{DESCRIBE | DESC}tbl_name
[col_name
|wild
]
DESCRIBE
provides information about the
columns in a table. It is a shortcut for SHOW COLUMNS
FROM
. As of MySQL 5.0.1, these statements also display
information for views. (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 within quotes unless it
contains spaces or other special characters.
mysql> DESCRIBE city;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| Country | char(3) | NO | UNI | | |
| District | char(20) | YES | MUL | | |
| Population | int(11) | NO | | 0 | |
+------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
Field
indicates the column name.
The Null
field indicates whether
NULL
values can be stored in the column.
The Key
field indicates whether the column is
indexed. A value of PRI
indicates that the
column is part of the table's primary key.
UNI
indicates that the column is part of a
UNIQUE
index. The MUL
value indicates that multiple occurrences of a given value are
allowed within the column.
One reason for MUL
to be displayed on a
UNIQUE
index is that several columns form a
composite UNIQUE
index; although the
combination of the columns is unique, each column can still hold
multiple occurrences of a given value. Note that in a composite
index, only the leftmost column of the index has an entry in the
Key
field.
Before MySQL 5.0.11, if the column allows
NULL
values, the Key
value
can be MUL
even when a
UNIQUE
index is used. The rationale was that
multiple rows in a UNIQUE
index can hold a
NULL
value if the column is not declared
NOT NULL
. As of MySQL 5.0.11, the display is
UNI
rather than MUL
regardless of whether the column allows NULL
;
you can see from the Null
field whether or
not the column can contain NULL
.
The Default
field indicates the default value
that is assigned to the column.
The Extra
field contains any additional
information that is available about a given column. In the
example shown, the Extra
field indicates that
the Id
column was created with the
AUTO_INCREMENT
keyword.
If the data types are different from what you expect them to be
based on a CREATE TABLE
statement, note that
MySQL sometimes changes data types. See
Section 13.1.5.1, “Silent Column Specification Changes”.
The DESCRIBE
statement is provided for
compatibility with Oracle.
The SHOW CREATE TABLE
and SHOW TABLE
STATUS
statements also provide information about
tables. See Section 13.5.4, “SHOW
Syntax”.
USE db_name
The USE
statement tells MySQL to use the
db_name
db_name
database as the default
(current) database for subsequent statements. The database
remains the default until the end of the session or another
USE
statement is issued:
USE db1; SELECT COUNT(*) FROM mytable; # selects from db1.mytable USE db2; SELECT COUNT(*) FROM mytable; # selects from db2.mytable
Making a particular database the default 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:
USE db1; SELECT author_name,editor_name FROM author,db2.editor WHERE author.editor_id = db2.editor.editor_id;
The USE
statement is provided for
compatibility with Sybase.
MySQL supports local transactions (within a given client
connection) through statements such as SET
AUTOCOMMIT
, START TRANSACTION
,
COMMIT
, and ROLLBACK
. See
Section 13.4.1, “START TRANSACTION
, COMMIT
, and ROLLBACK
Syntax”. Beginning with MySQL 5.0, XA transaction
support is available, which enables MySQL to participate in
distributed transactions as well. See Section 13.4.7, “XA Transactions”.
START TRANSACTION | BEGIN [WORK] COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] SET AUTOCOMMIT = {0 | 1}
The START TRANSACTION
and
BEGIN
statement begin a new transaction.
COMMIT
commits the current transaction,
making its changes permanent. ROLLBACK
rolls
back the current transaction, canceling its changes. The
SET AUTOCOMMIT
statement disables or enables
the default autocommit mode for the current connection.
Beginning with MySQL 5.0.3, the optional WORK
keyword is supported for COMMIT
and
RELEASE
, as are the CHAIN
and RELEASE
clauses. CHAIN
and RELEASE
can be used for additional
control over transaction completion. The value of the
completion_type
system variable determines
the default completion behavior. See
Section 5.2.2, “Server System Variables”.
The AND CHAIN
clause causes a new transaction
to begin as soon as the current one ends, and the new
transaction has the same isolation level as the just-terminated
transaction. The RELEASE
clause causes the
server to disconnect the current client connection after
terminating the current transaction. Including the
NO
keyword suppresses
CHAIN
or RELEASE
completion, which can be useful if the
completion_type
system variable is set to
cause chaining or release completion by default.
By 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 a transaction-safe storage engine (such as
InnoDB
, BDB
, or
NDB Cluster
), 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.
To disable autocommit mode for a single series of statements,
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
are
supported as aliases of START TRANSACTION
for
initiating a transaction. START TRANSACTION
is standard SQL syntax and is the recommended way to start an
ad-hoc transaction.
The BEGIN
statement differs from the use of
the BEGIN
keyword that starts a
BEGIN ... END
compound statement. The latter
does not begin a transaction. See Section 17.2.5, “BEGIN ... END
Compound Statement Syntax”.
You can also 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 14.2.10.4, “Consistent Non-Locking Read”.
The WITH CONSISTENT SNAPSHOT
clause does not
change the current transaction isolation level, so it provides a
consistent snapshot only if the current isolation level is one
that allows consistent read (REPEATABLE READ
or SERIALIZABLE
).
Beginning a transaction causes an implicit UNLOCK
TABLES
to be performed.
For best results, transactions should be performed using only tables managed by a single transactional storage engine. Otherwise, the following problems can occur:
If you use tables from more than one transaction-safe
storage engine (such as InnoDB
and
BDB
), and the transaction isolation level
is not SERIALIZABLE
, it is possible that
when one transaction commits, another ongoing transaction
that uses the same tables will see only some of the changes
made by the first transaction. That is, the atomicity of
transactions is not guaranteed with mixed engines and
inconsistencies can result. (If mixed-engine transactions
are infrequent, you can use SET TRANSACTION
ISOLATION LEVEL
to set the isolation level to
SERIALIZABLE
on a per-transaction basis
as necessary.)
If you use non-transaction-safe tables within a transaction, any changes to those tables 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 are rolled back,
but not changes to non-transaction-safe tables.
Each transaction is 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.) See
Section 5.12.3, “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 may occur without the
user having explicitly asked for it (for example, when an error
occurs). Because of this, SHOW PROCESSLIST
displays Rolling back
in the
State
column for the connection during
implicit and explicit (ROLLBACK
SQL
statement) rollbacks.
Some statements cannot be rolled back. In general, these include data definition language (DDL) statements, such as those that create or drop databases, those that create, drop, or alter tables or stored routines.
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 in such
cases 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 FUNCTION
, ALTER
PROCEDURE
, ALTER TABLE
,
BEGIN
, CREATE
DATABASE
, CREATE FUNCTION
,
CREATE INDEX
, CREATE
PROCEDURE
, CREATE TABLE
,
DROP DATABASE
, DROP
FUNCTION
, DROP INDEX
,
DROP PROCEDURE
, DROP
TABLE
, LOAD MASTER DATA
,
LOCK TABLES
, RENAME
TABLE
, SET AUTOCOMMIT=1
,
START TRANSACTION
, TRUNCATE
TABLE
, UNLOCK TABLES
.
UNLOCK TABLES
commits a transaction only
if any tables currently are locked.
The CREATE TABLE
, CREATE
DATABASE
DROP DATABASE
, and
TRUNCATE TABLE
statements cause an
implicit commit beginning with MySQL 5.0.8. The
ALTER FUNCTION
, ALTER
PROCEDURE
, CREATE FUNCTION
,
CREATE PROCEDURE
, DROP
FUNCTION
, and DROP PROCEDURE
statements cause an implicit commit beginning with MySQL
5.0.13.
The CREATE TABLE
statement in
InnoDB
is processed as a single
transaction. This means that a ROLLBACK
from the user does not undo CREATE TABLE
statements the user made during that transaction.
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.
SAVEPOINTidentifier
ROLLBACK [WORK] TO SAVEPOINTidentifier
RELEASE SAVEPOINTidentifier
InnoDB
supports the SQL statements
SAVEPOINT
and ROLLBACK TO
SAVEPOINT
. Starting from MySQL 5.0.3, RELEASE
SAVEPOINT
and the optional WORK
keyword for ROLLBACK
are supported as well.
The SAVEPOINT
statement sets a named
transaction savepoint with a name of
identifier
. If the current
transaction 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 ROLLBACK TO SAVEPOINT
statement
returns the following error, it means that no savepoint with the
specified name exists:
ERROR 1181: Got error 153 during ROLLBACK
The RELEASE SAVEPOINT
statement removes the
named savepoint from the set of savepoints of the current
transaction. No commit or rollback occurs. It is an error if the
savepoint does not exist.
All savepoints of the current transaction are deleted if you
execute a COMMIT
, or a
ROLLBACK
that does not name a savepoint.
Beginning with MySQL 5.0.17, a new savepoint level is created when a stored function is invoked or a trigger is activated. The savepoints on previous levels become unavailable and thus do not conflict with savepoints on the new level. When the function or trigger terminates, any savepoints it created are released and the previous savepoint level is restored.
LOCK TABLEStbl_name
[ASalias
] {READ [LOCAL] | [LOW_PRIORITY] WRITE} [,tbl_name
[ASalias
] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ... UNLOCK TABLES
LOCK TABLES
locks tables for the current
thread. If any of the tables are locked by other threads, it
blocks until all locks can be acquired. 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.
A table lock protects only against inappropriate reads or writes
by other clients. The client holding the lock, even a read lock,
can perform table-level operations such as DROP
TABLE
.
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
.
(See Section 13.4.3, “Statements That Cause an Implicit Commit”.)
The correct way to use LOCK TABLES
with
transactional tables, such as InnoDB
tables, 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 can very easily happen. Note
that we do not acquire the InnoDB
table
lock at all if AUTOCOMMIT=1
, to help old
applications avoid unnecessary deadlocks.
ROLLBACK
does not release MySQL's
non-transactional table locks.
To use LOCK TABLES
, you must have the
LOCK TABLES
privilege and the
SELECT
privilege for the involved tables.
The main reasons to use LOCK TABLES
are to
emulate 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 cannot be used if you are going to manipulate the database
files outside MySQL while you hold the lock. For
InnoDB
tables, READ LOCAL
is the same as READ
as of MySQL 5.0.13.
(Before that, READ LOCAL
essentially does
nothing: It does not lock the table at all, so for
InnoDB
tables, the use of READ
LOCAL
is deprecated because 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 a single query. Use aliases instead, in which case you
must obtain 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 by means of an alias, you must lock the table using that same alias. It does 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 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 eventually there will be a time when
no threads have a READ
lock.
LOCK TABLES
works as follows:
Sort all tables to be locked in an internally defined order. From the user standpoint, this order is undefined.
If a table is locked with a read and a write lock, put the write lock before the read lock.
Lock one table at a time until the thread gets all locks.
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 waits 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 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 performed
by a separate thread.
Normally, you do not need to lock tables, because all single
UPDATE
statements are atomic; no other thread
can interfere with any other currently executing SQL statement.
However, there are a few cases when locking tables may provide
an advantage:
If you are going to run many operations on a set of
MyISAM
tables, it is 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
does not flush the key cache for the locked tables until
UNLOCK TABLES
is called. Normally, the
key cache is flushed after each SQL statement.
If you are using a storage engine in MySQL that does not
support transactions, you must use 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:
LOCK TABLES trans READ, customer WRITE; SELECT SUM(value) FROM trans WHERE customer_id=some_id
; UPDATE customer SET total_value=sum_from_previous_statement
WHERE customer_id=some_id
; 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
)
or the value
=value
+new_value
LAST_INSERT_ID()
function. See
Section 1.9.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.9.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 [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 14.2.10.3, “InnoDB
and TRANSACTION ISOLATION LEVEL
”.
InnoDB
supports each of these levels in MySQL
5.0. The default level is REPEATABLE
READ
.
To set the initial default global isolation level for
mysqld, use the
--transaction-isolation
option. See
Section 5.2.1, “mysqld Command Options”.
MySQL 5.0.3 and up provides server-side support for XA
transactions. Currently, this support is available for the
InnoDB
storage engine. The MySQL XA
implementation is based on the X/Open CAE document
Distributed Transaction Processing: The XA
Specification. This document is published by The
Open Group and available at
http://www.opengroup.org/public/pubs/catalog/c193.htm.
Limitations of the current XA implementation are described in
Section I.5, “Restrictions on XA Transactions”.
On the client side, there are no special requirements. The XA
interface to a MySQL server consists of SQL statements that
begin with the XA
keyword. MySQL client
programs must be able to send SQL statements and to understand
the semantics of the XA statement interface. They do not need be
linked against a recent client library. Older client libraries
also will work.
Currently, among the MySQL Connectors, MySQL Connector/J 5.0.0 supports XA directly (by means of a class interface that handles the XA SQL statement interface for you).
XA supports distributed transactions; that is, the ability to allow multiple separate transactional resources to participate in a global transaction. Transactional resources often are RDBMSs but may be other kinds of resources.
A global transaction involves several actions that are
transactional in themselves, but that all must either complete
successfully as a group, or all be rolled back as a group. In
essence, this extends ACID properties “up a level”
so that multiple ACID transactions can be executed in concert as
components of a global operation that also has ACID properties.
(However, for a distributed transaction, you must use the
SERIALIZABLE
isolation level to achieve ACID
properties. It is enough to use REPEATABLE
READ
for a non-distributed transaction, but not for a
distributed transaction.)
Some examples of distributed transactions:
An application may act as an integration tool that combines a messaging service with an RDBMS. The application makes sure that transactions dealing with message sending, retrieval, and processing that also involve a transactional database all happen in a global transaction. You can think of this as “transactional email.”
An application performs actions that involve different database servers, such as a MySQL server and an Oracle server (or multiple MySQL servers), where actions that involve multiple servers must happen as part of a global transaction, rather than as separate transactions local to each server.
A bank keeps account information in an RDBMS and distributes and receives money via automated teller machines (ATMs). It is necessary to ensure that ATM actions are correctly reflected in the accounts, but this cannot be done with the RDBMS alone. A global transaction manager integrates the ATM and database resources to ensure overall consistency of financial transactions.
Applications that use global transactions involve one or more Resource Managers and a Transaction Manager:
A Resource Manager (RM) provides access to transactional resources. A database server is one kind of resource manager. It must be possible to either commit or roll back transactions managed by the RM.
A Transaction Manager (TM) coordinates the transactions that are part of a global transaction. It communicates with the RMs that handle each of these transactions. The individual transactions within a global transaction are “branches” of the global transaction. Global transactions and their branches are identified by a naming scheme described later.
The MySQL implementation of XA MySQL enables a MySQL server to act as a Resource Manager that handles XA transactions within a global transaction. A client program that connects to the MySQL server acts as the Transaction Manager.
To carry out a global transaction, it is necessary to know which components are involved, and bring each component to a point when it can be committed or rolled back. Depending on what each component reports about its ability to succeed, they must all commit or roll back as an atomic group. That is, either all components must commit, or all components musts roll back. To manage a global transaction, it is necessary to take into account that any component or the connecting network might fail.
The process for executing a global transaction uses two-phase commit (2PC). This takes place after the actions performed by the branches of the global transaction have been executed.
In the first phase, all branches are prepared. That is, they are told by the TM to get ready to commit. Typically, this means each RM that manages a branch records the actions for the branch in stable storage. The branches indicate whether they are able to do this, and these results are used for the second phase.
In the second phase, the TM tells the RMs whether to commit or roll back. If all branches indicated when they were prepared that they will be able to commit, all branches are told to commit. If any branch indicated when it was prepared that it will not be able to commit, all branches are told to roll back.
In some cases, a global transaction might use one-phase commit (1PC). For example, when a Transaction Manager finds that a global transaction consists of only one transactional resource (that is, a single branch), that resource can be told to prepare and commit at the same time.
To perform XA transactions in MySQL, use the following statements:
XA {START|BEGIN}xid
[JOIN|RESUME] XA ENDxid
[SUSPEND [FOR MIGRATE]] XA PREPARExid
XA COMMITxid
[ONE PHASE] XA ROLLBACKxid
XA RECOVER
For XA START
, the JOIN
and RESUME
clauses are not supported.
For XA END
the SUSPEND [FOR
MIGRATE]
clause is not supported.
Each XA statement begins with the XA
keyword, and most of them require an
xid
value. An
xid
is an XA transaction
identifier. It indicates which transaction the statement
applies to. xid
values are supplied
by the client, or generated by the MySQL server. An
xid
value has from one to three
parts:
xid
:gtrid
[,bqual
[,formatID
]]
gtrid
is a global transaction
identifier, bqual
is a branch
qualifier, and formatID
is a number
that identifies the format used by the
gtrid
and
bqual
values. As indicated by the
syntax, bqual
and
formatID
are optional. The default
bqual
value is
''
if not given. The default
formatID
value is 1 if not given.
gtrid
and
bqual
must be string literals, each
up to 64 bytes (not characters) long.
gtrid
and
bqual
can be specified in several
ways. You can use a quoted string ('ab'
),
hex string (0x6162
,
X'ab'
), or bit value
(b'
).
nnnn
'
formatID
is an unsigned integer.
The gtrid
and
bqual
values are interpreted in
bytes by the MySQL server's underlying XA support routines.
However, while an SQL statement containing an XA statement is
being parsed, the server works with some specific character
set. To be safe, write gtrid
and
bqual
as hex strings.
xid
values typically are generated
by the Transaction Manager. Values generated by one TM must be
different from values generated by other TMs. A given TM must
be able to recognize its own xid
values in a list of values returned by the XA
RECOVER
statement.
XA START
starts an XA transaction with the given
xid
xid
value. Each XA transaction must
have a unique xid
value, so the
value must not currently be used by another XA transaction.
Uniqueness is assessed using the
gtrid
and
bqual
values. All following XA
statements for the XA transaction must be specified using the
same xid
value as that given in the
XA START
statement. If you use any of those
statements but specify an xid
value
that does not correspond to some existing XA transaction, an
error occurs.
One or more XA transactions can be part of the same global
transaction. All XA transactions within a given global
transaction must use the same gtrid
value in the xid
value. For this
reason, gtrid
values must be
globally unique so that there is no ambiguity about which
global transaction a given XA transaction is part of. The
bqual
part of the
xid
value must be different for
each XA transaction within a global transaction. (The
requirement that bqual
values be
different is a limitation of the current MySQL XA
implementation. It is not part of the XA specification.)
The XA RECOVER
statement returns
information for those XA transactions on the MySQL server that
are in the PREPARED
state. (See
Section 13.4.7.2, “XA Transaction States”.) The output includes a row for
each such XA transaction on the server, regardless of which
client started it.
XA RECOVER
output rows look like this (for
an example xid
value consisting of
the parts 'abc'
, 'def'
,
and 7
):
mysql> XA RECOVER;
+----------+--------------+--------------+--------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+--------+
| 7 | 3 | 3 | abcdef |
+----------+--------------+--------------+--------+
The output columns have the following meanings:
formatID
is the
formatID
part of the
transaction xid
gtrid_length
is the length in bytes of
the gtrid
part of the
xid
bqual_length
is the length in bytes of
the bqual
part of the
xid
data
is the concatenation of the
gtrid
and
bqual
parts of the
xid
An XA transaction progresses through the following states:
Use XA START
to start an XA transaction
and put it in the ACTIVE
state.
For an ACTIVE
XA transaction, issue the
SQL statements that make up the transaction, and then
issue an XA END
statement. XA
END
puts the transaction in the
IDLE
state.
For an IDLE
XA transaction, you can
issue either an XA PREPARE
statement or
an XA COMMIT ... ONE PHASE
statement:
XA PREPARE
puts the transaction in
the PREPARED
state. An XA
RECOVER
statement at this point will include
the transaction's xid
value
in its output, because XA RECOVER
lists all XA transactions that are in the
PREPARED
state.
XA COMMIT ... ONE PHASE
prepares
and commits the transaction. The
xid
value will not be
listed by XA RECOVER
because the
transaction terminates.
For a PREPARED
XA transaction, you can
issue an XA COMMIT
statement to commit
and terminate the transaction, or XA
ROLLBACK
to roll back and terminate the
transaction.
Here is a simple XA transaction that inserts a row into a table as part of a global transaction:
mysql>XA START 'xatest';
Query OK, 0 rows affected (0.00 sec) mysql>INSERT INTO mytable (i) VALUES(10);
Query OK, 1 row affected (0.04 sec) mysql>XA END 'xatest';
Query OK, 0 rows affected (0.00 sec) mysql>XA PREPARE 'xatest';
Query OK, 0 rows affected (0.00 sec) mysql>XA COMMIT 'xatest';
Query OK, 0 rows affected (0.00 sec)
Within the context of a given client connection, XA
transactions and local (non-XA) transactions are mutually
exclusive. For example, if XA START
has
been issued to begin an XA transaction, a local transaction
cannot be started until the XA transaction has been committed
or rolled back. Conversely, if a local transaction has been
started with START TRANSACTION
, no XA
statements can be used until the transaction has been
committed or rolled back.
MySQL account information is stored in the tables of the
mysql
database. This database and the access
control system are discussed extensively in
Chapter 5, Database Administration, which you should
consult for additional details.
Important: Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features. Whenever you update to a new version of MySQL, you should update your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. See Section 5.6.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.
CREATE USERuser
[IDENTIFIED BY [PASSWORD] 'password
'] [,user
[IDENTIFIED BY [PASSWORD] 'password
']] ...
The CREATE USER
statement was added in
MySQL 5.0.2. This statement creates new MySQL accounts. To use
it, you must have the global CREATE USER
privilege or the INSERT
privilege for the
mysql
database. For each account,
CREATE USER
creates a new record in the
mysql.user
table that has no privileges. An
error occurs if the account already exists. Each account is
named using the same format as for the
GRANT
statement; 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 row for the
account.
The account can be given a password with the optional
IDENTIFIED BY
clause. The
user
value and the password are
given the same way as for the GRANT
statement. In particular, to specify the password in plain
text, omit the PASSWORD
keyword. To specify
the password as the hashed value as returned by the
PASSWORD()
function, include the
PASSWORD
keyword. See
Section 13.5.1.3, “GRANT
Syntax”.
DROP USERuser
[,user
] ...
The DROP USER
statement removes one or more
MySQL accounts. To use it, you must have the global
CREATE USER
privilege or the
DELETE
privilege for the
mysql
database. Each account is named using
the same format as for the GRANT
statement;
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 row for the
account.
DROP USER
as present in MySQL 5.0.0 removes
only accounts that have no privileges. In MySQL 5.0.2, it was
modified to remove account privileges as well. This means that
the procedure for removing an account depends on your version
of MySQL.
As of MySQL 5.0.2, you can remove an account and its privileges as follows:
DROP USER user
;
The statement removes privilege rows for the account from all grant tables.
In MySQL 5.0.0 and 5.0.1, DROP USER
deletes
only MySQL accounts that have no privileges. In these MySQL
versions, it serves only to remove each account record from
the user
table. To remove a MySQL account
completely (including all of its privileges), you should use
the following procedure, performing these steps in the order
shown:
Use SHOW GRANTS
to determine what
privileges the account has. See
Section 13.5.4.12, “SHOW GRANTS
Syntax”.
Use REVOKE
to revoke the privileges
displayed by SHOW GRANTS
. This removes
rows 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.3, “GRANT
Syntax”.
Delete the account by using DROP USER
to remove the user
table record.
Important: DROP USER
does not automatically close any open user sessions. Rather,
in the event that a user with an open session is dropped, the
statement does not take effect until that user's session is
closed. Once the session is closed, the user is dropped, and
that user's next attempt to log in will fail. This
is by design.
GRANTpriv_type
[(column_list
)] [,priv_type
[(column_list
)]] ... ON [object_type
] {tbl_name
| * | *.* |db_name
.*} TOuser
[IDENTIFIED BY [PASSWORD] 'password
'] [,user
[IDENTIFIED BY [PASSWORD] 'password
']] ... [REQUIRE NONE | [{SSL| X509}] [CIPHER 'cipher
' [AND]] [ISSUER 'issuer
' [AND]] [SUBJECT 'subject
']] [WITHwith_option
[with_option
] ...]object_type
= TABLE | FUNCTION | PROCEDUREwith_option
= GRANT OPTION | MAX_QUERIES_PER_HOURcount
| MAX_UPDATES_PER_HOURcount
| MAX_CONNECTIONS_PER_HOURcount
| MAX_USER_CONNECTIONScount
The GRANT
statement enables system
administrators to create MySQL user accounts and to grant
rights to from accounts. To use GRANT
, you
must have the GRANT OPTION
privilege, and
you must have the privileges that you are granting. The
REVOKE
statement is related and enables
administrators to remove account privileges. See
Section 13.5.1.5, “REVOKE
Syntax”.
MySQL account information is stored in the tables of the
mysql
database. This database and the
access control system are discussed extensively in
Chapter 5, Database Administration, which you should
consult for additional details.
Important: Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features. Whenever you update to a new version of MySQL, you should update your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. See Section 5.6.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.
If the grant tables hold privilege rows that contain
mixed-case database or table names and the
lower_case_table_names
system variable is
set to a non-zero value, REVOKE
cannot be
used to revoke these privileges. It will be necessary to
manipulate the grant tables directly.
(GRANT
will not create such rows when
lower_case_table_names
is set, but such
rows might have been created prior to setting the variable.)
Privileges can be granted at several levels:
Global level
Global privileges apply to all databases on a given
server. These privileges are stored in the
mysql.user
table. GRANT ALL ON
*.*
and REVOKE ALL ON *.*
grant and revoke only global privileges.
Database level
Database privileges apply to all objects in a given
database. These privileges are stored in the
mysql.db
and
mysql.host
tables. GRANT ALL
ON
and
db_name
.*REVOKE ALL ON
grant and
revoke only database privileges.
db_name
.*
Table level
Table privileges apply to all columns in a given table.
These privileges are stored in the
mysql.tables_priv
table. GRANT
ALL ON
and
db_name.tbl_name
REVOKE ALL ON
grant and revoke only table privileges.
db_name.tbl_name
Column level
Column privileges apply to single columns in a given
table. These privileges are stored in the
mysql.columns_priv
table. When using
REVOKE
, you must specify the same
columns that were granted.
Routine level
The CREATE ROUTINE
, ALTER
ROUTINE
, EXECUTE
, and
GRANT
privileges apply to stored
routines (functions and procedures). They can be granted
at the global and database levels. Also, except for
CREATE ROUTINE
, these privileges can be
granted at the routine level for individual routines and
are stored in the mysql.procs_priv
table.
The object_type
clause was added in
MySQL 5.0.6. It should be specified as
TABLE
, FUNCTION
, or
PROCEDURE
when the following object is a
table, a stored function, or a stored procedure.
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 | Enables use of ALTER TABLE |
ALTER ROUTINE | Enables stored routines to be altered or dropped |
CREATE | Enables use of CREATE TABLE |
CREATE ROUTINE | Enables creation of stored routines |
CREATE TEMPORARY TABLES | Enables use of CREATE TEMPORARY TABLE |
CREATE USER | Enables use of CREATE USER , DROP
USER , RENAME USER , and
REVOKE ALL PRIVILEGES . |
CREATE VIEW | Enables use of CREATE VIEW |
DELETE | Enables use of DELETE |
DROP | Enables use of DROP TABLE |
EXECUTE | Enables the user to run stored routines |
FILE | Enables use of SELECT ... INTO OUTFILE and
LOAD DATA INFILE |
INDEX | Enables use of CREATE INDEX and DROP
INDEX |
INSERT | Enables use of INSERT |
LOCK TABLES | Enables use of LOCK TABLES on tables for which you
have the SELECT privilege |
PROCESS | Enables use of SHOW FULL PROCESSLIST |
REFERENCES | Not implemented |
RELOAD | Enables use of FLUSH |
REPLICATION CLIENT | Enables the user to ask where slave or master servers are |
REPLICATION SLAVE | Needed for replication slaves (to read binary log events from the master) |
SELECT | Enables use of SELECT |
SHOW DATABASES | SHOW DATABASES shows all databases |
SHOW VIEW | Enables use of SHOW CREATE VIEW |
SHUTDOWN | Enables use of mysqladmin shutdown |
SUPER | Enables 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 | Enables use of UPDATE |
USAGE | Synonym for “no privileges” |
GRANT OPTION | Enables privileges to be granted |
The EXECUTE
privilege is not operational
until MySQL 5.0.3. CREATE VIEW
and
SHOW VIEW
were added in MySQL 5.0.1.
CREATE USER
, CREATE
ROUTINE
, and ALTER ROUTINE
were
added in MySQL 5.0.3.
The REFERENCES
privilege currently is
unused.
USAGE
can be specified when you want to
create a user that has no privileges.
Use SHOW GRANTS
to determine what
privileges an account has. See Section 13.5.4.12, “SHOW GRANTS
Syntax”.
You can assign global privileges by using ON
*.*
syntax or database-level privileges by using
ON
syntax. If you specify db_name
.*ON *
and you have
selected a default database, the privileges are granted in
that database. (Warning: If
you specify ON *
and you have
not selected a default database, the
privileges granted are global.)
The 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 priv_type
values that you can
specify for a table are SELECT
,
INSERT
, UPDATE
,
DELETE
, CREATE
,
DROP
, GRANT OPTION
,
INDEX
, ALTER
,
CREATE VIEW
and SHOW
VIEW
.
The priv_type
values that you can
specify for a column (that is, when you use a
column_list
clause) are
SELECT
, INSERT
, and
UPDATE
.
The priv_type
values that you can
specify at the routine level are ALTER
ROUTINE
, EXECUTE
, and
GRANT OPTION
. CREATE
ROUTINE
is not a routine-level privilege because you
must have this privilege to create a routine in the first
place.
For the global, database, table, and routine levels,
GRANT ALL
assigns only the privileges that
exist at the level you are granting. For example,
GRANT ALL ON
is a
database-level statement, so it does not grant any global-only
privileges such as db_name
.*FILE
.
MySQL allows you to grant privileges even on database objects
that do not exist. In such cases, the privileges to be granted
must include the CREATE
privilege.
This behavior is by design, and is
intended to enable the database administrator to prepare user
accounts and privileges for database objects that are to be
created at a later time.
Important: MySQL does not automatically revoke any privileges when you drop a table or database. However, if you drop a routine, any routine-level privileges granted for that routine are revoked.
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 ...
.
To accommodate granting rights to users from arbitrary hosts,
MySQL supports specifying the user
value in the form
.
If a user_name
@host_name
user_name
or
host_name
value is legal as an
unquoted identifier, you need not quote it. However, quotes
are necessary to specify a
user_name
string containing special
characters (such as ‘-
’), or a
host_name
string containing special
characters or wildcard characters (such as
‘%
’); for example,
'test-user'@'test-hostname'
. Quote the
username and hostname separately.
You can specify wildcards in the hostname. For example,
applies to user_name
@'%.loc.gov'user_name
for any host
in the loc.gov
domain, and
applies to user_name
@'144.155.166.%'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 does not support wildcards in
usernames. Anonymous users are defined by inserting
entries with User=''
into the
mysql.user
table or by creating a user with
an empty name with the GRANT
statement:
GRANT ALL ON test.* TO ''@'localhost' ...
When specifying quoted values, quote database, table, column,
and routine names as identifiers, using backticks
(‘`
’). Quote hostnames,
usernames, and passwords as strings, using single quotes
(‘'
’).
Warning: If you allow
anonymous users to connect to the MySQL server, you should
also grant privileges to all local users as
.
Otherwise, the anonymous user account for
user_name
@localhostlocalhost
in the
mysql.user
table (created during MySQL
installation) is used when named users try to log in to the
MySQL server from the local machine. For details, see
Section 5.8.5, “Access Control, Stage 1: Connection Verification”.
You can determine whether this applies to you by executing the following query, which lists any anonymous users:
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:
DELETE FROM mysql.user WHERE Host='localhost' AND User=''; FLUSH PRIVILEGES;
GRANT
supports hostnames up to 60
characters long. Database, table, column, and routine names
can be up to 64 characters. Usernames can be up to 16
characters. Note:
The allowable length for usernames cannot be changed
by altering the mysql.user
table, and
attempting to do so results in unpredictable behavior which
may even make it impossible for users to log in to the MySQL
server. You should never alter any of the tables in
the mysql
database in any manner whatsoever
except by means of the procedure prescribed by MySQL AB that
is described in Section 5.6.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.
The privileges for a table, column, or routine are formed
additively as the logical OR
of the
privileges at each of the 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 OR routine privileges
In most cases, you grant rights to a user at only one of the privilege levels, so life is not normally this complicated. The details of the privilege-checking procedure are presented in Section 5.8, “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
does not remove
them; you must do that explicitly using DROP
USER
or DELETE
.
Warning: If you create a new
user but do not specify an IDENTIFIED BY
clause, the user has no password. This is very insecure. As of
MySQL 5.0.2, you can enable the
NO_AUTO_CREATE_USER
SQL mode to prevent
GRANT
from creating a new user if it would
otherwise do so, unless IDENTIFIED BY
is
given to provide the new user a non-empty password.
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, this is
replaced by the new one.
Passwords can also be set with the SET
PASSWORD
statement. See
Section 13.5.1.6, “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 do not 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.
The SHOW DATABASES
privilege enables the
account to see database names by issuing the SHOW
DATABASE
statement. Accounts that do not have this
privilege see only databases for which they have some
privileges, and cannot use the statement at all if the server
was started with the --skip-show-database
option.
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).
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 which you yourself
do not have; the GRANT OPTION
privilege
enables you to assign only those privileges which you yourself
possess.
Be aware that when you grant a user the GRANT
OPTION
privilege at a particular privilege level,
any privileges the user possesses (or may be given in the
future) at that level can also be granted by that user to
other users. 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
, that user
can give to other users not only the SELECT
privilege, but also INSERT
. If you then
grant the UPDATE
privilege to the user on
the database, the user can grant INSERT
,
SELECT
, and UPDATE
.
For a non-administrative user, you should not grant the
ALTER
privilege globally or for the
mysql
database. 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
, and
count
MAX_CONNECTIONS_PER_HOUR
options limit the
number of queries, updates, and logins a user can perform
during any given one-hour period. If
count
count
is 0
(the
default), this means that there is no limitation for that
user.
The MAX_USER_CONNECTIONS
option, implemented
in MySQL 5.0.3, limits the maximum number of simultaneous
connections that the account can make. If
count
count
is 0
(the
default), the max_user_connections
system
variable determines the number of simultaneous connections for
the account.
Note: To specify any of these resource-limit options for an
existing user without affecting existing privileges, use
GRANT USAGE ON *.* ... WITH MAX_...
.
See Section 5.9.4, “Limiting Account Resources”.
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
information on the use of SSL with MySQL, see
Section 5.9.7, “Using Secure Connections”.)
There are a number of different possibilities for limiting connection types for a given account:
If the account has no SSL or X509 requirements, unencrypted connections are allowed if the username and password are valid. However, encrypted connections can also be used, at the client's option, if the client has the proper certificate and key files.
The 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 rows that allow non-SSL connections.
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.
GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost' IDENTIFIED BY 'goodsecret' REQUIRE X509;
REQUIRE 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
issuer
'SSL
option is unnecessary in this case.
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
'
value should be entered as a single string.
issuer
'
REQUIRE SUBJECT
'
places the
restriction on connection attempts that the client must
present a valid X509 certificate containing the subject
subject
'subject
. If the client presents
a certificate that is valid but has a different subject,
the server rejects the connection.
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
'
value should be entered as a single string.
subject
'
REQUIRE CIPHER
'
is needed to
ensure that ciphers and key lengths of sufficient strength
are used. SSL itself can be weak if old algorithms using
short encryption keys are used. Using this option, you can
ask that a specific cipher method is used to allow a
connection.
cipher
'
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:
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';
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. For details, see Section 5.8.7, “When Privilege Changes Take Effect”.
Note that if you are using table, column, or routine privileges for even one user, the server examines table, column, and routine 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:
In MySQL, privileges are associated with the combination of a hostname and username and not with only a username.
Standard SQL does not have global or database-level privileges, nor does it support all the privilege types that MySQL supports.
MySQL does not support the standard SQL
TRIGGER
or UNDER
privileges.
Standard SQL privileges are structured in a hierarchical
manner. If you remove a user, all privileges the user has
been granted are revoked. This is also true in MySQL 5.0.2
and up if you use DROP USER
. Before
5.0.2, the granted privileges are not automatically
revoked; you must revoke them yourself. See
Section 13.5.1.2, “DROP USER
Syntax”.
In standard SQL, when you drop a table, all privileges for
the table are revoked. In standard SQL, when you revoke a
privilege, all privileges that were granted based on that
privilege are also revoked. In MySQL, privileges can be
dropped only with explicit REVOKE
statements or by manipulating values stored in the MySQL
grant tables.
In MySQL, it is possible to have the
INSERT
privilege for only some of the
columns in a table. In this case, you can still execute
INSERT
statements on the table,
provided that you omit those columns for which you do not
have the INSERT
privilege. The omitted
columns are set to their implicit default values if strict
SQL mode is not enabled. In strict mode, the statement is
rejected if any of the omitted columns have no default
value. (Standard SQL requires you to have the
INSERT
privilege on all columns.)
Section 5.2.5, “The Server SQL Mode”, discusses strict mode.
Section 11.1.4, “Data Type Default Values”, discusses implicit
default values.
RENAME USERold_user
TOnew_user
[,old_user
TOnew_user
] ...
The RENAME USER
statement renames existing
MySQL accounts. To use it, you must have the global
CREATE USER
privilege or the
UPDATE
privilege for the
mysql
database. An error occurs if any old
account does not exist or any new account exists. Each account
is named using the same format as for the
GRANT
statement; 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 row for the
account.
The RENAME USER
statement was added in
MySQL 5.0.2.
REVOKEpriv_type
[(column_list
)] [,priv_type
[(column_list
)]] ... ON [object_type
] {tbl_name
| * | *.* |db_name
.*} FROMuser
[,user
] ... REVOKE ALL PRIVILEGES, GRANT OPTION FROMuser
[,user
] ...
The REVOKE
statement enables system
administrators to revoke privileges from MySQL accounts. To
use REVOKE
, you must have the
GRANT OPTION
privilege, and you must have
the privileges that you are revoking.
For details on the levels at which privileges exist, the
allowable priv_type
values, and the
syntax for specifying users and passwords, see
Section 13.5.1.3, “GRANT
Syntax”
If the grant tables hold privilege rows that contain
mixed-case database or table names and the
lower_case_table_names
system variable is
set to a non-zero value, REVOKE
cannot be
used to revoke these privileges. It will be necessary to
manipulate the grant tables directly.
(GRANT
will not create such rows when
lower_case_table_names
is set, but such
rows might have been created prior to setting the variable.)
To revoke all privileges, use the following syntax, which drops all global, database-, table-, and column-level privileges for the named user or users:
REVOKE ALL PRIVILEGES, GRANT OPTION FROMuser
[,user
] ...
To use this REVOKE
syntax, you must have
the global CREATE USER
privilege or the
UPDATE
privilege for the
mysql
database.
SET PASSWORD [FORuser
] = PASSWORD('some password
')
The SET PASSWORD
statement assigns a
password to an existing MySQL user account.
With no FOR
clause, this statement 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.
With a FOR
clause, this statement sets the
password for a specific account on the current server host.
Only clients that have the UPDATE
privilege
for the mysql
database can do this. The
user
value should be given in
format, where user_name
@host_name
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:
SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass
');
That is equivalent to the following statements:
UPDATE mysql.user SET Password=PASSWORD('newpass
')
WHERE User='bob' AND Host='%.loc.gov';
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 preceding SET PASSWORD
or
UPDATE
statement without reading
Section 5.8.9, “Password Hashing as of 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.
You can see which account the server authenticated you as by
executing SELECT CURRENT_USER()
.
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLEtbl_name
[,tbl_name
] ...
ANALYZE TABLE
analyzes and stores the key
distribution for a table. During the analysis, the table is
locked with a read lock. This statement works with
MyISAM
, BDB
, and
InnoDB
tables. For
MyISAM
tables, this statement is equivalent
to using myisamchk --analyze.
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.
ANALYZE TABLE
returns a result set 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.13, “SHOW INDEX
Syntax”.
If the table has not changed since the last ANALYZE
TABLE
statement, the table is not analyzed again.
ANALYZE TABLE
statements are written to the
binary log unless the optional
NO_WRITE_TO_BINLOG
keyword (or its alias
LOCAL
) is used. This is done so that
ANALYZE TABLE
statements used on a MySQL
server acting as a replication master will be replicated by
default to the replication slave.
BACKUP TABLEtbl_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. To restore the table, use RESTORE
TABLE
.
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), issue a
LOCK TABLES
statement first, to obtain a
read lock for all tables in the group.
BACKUP TABLE
returns a result set 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 |
CHECK TABLEtbl_name
[,tbl_name
] ... [option
] ...option
= {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
CHECK TABLE
checks a table or tables for
errors. CHECK TABLE
works for
MyISAM
, InnoDB
, and (as
of MySQL 5.0.16) ARCHIVE
tables. For
MyISAM
tables, the key statistics are
updated as well.
As of MySQL 5.0.2, CHECK TABLE
can also
check views for problems, such as tables that are referenced
in the view definition that no longer exist.
CHECK TABLE
returns a result set 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 has 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.10.4, “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 FOR UPGRADE
option checks whether the
named tables are compatible with the current version of MySQL.
This option was added in MySQL 5.0.19. With FOR
UPGRADE
, the server checks each table to determine
whether there have been any incompatible changes in any of the
table's data types or indexes since the table was created. If
not, the check succeeds. Otherwise, if there is a possible
incompatibility, the server runs a full check on the table
(which might take some time). If the full check succeeds, the
server marks the table's .frm
file with
the current MySQL version number. Marking the
.frm
file ensures that further checks for
the table with the same version of the server will be fast.
Incompatibilities might occur because the storage format for a data type has changed or because its sort order has changed. Our aim is to avoid these changes, but occasionally they are necessary to correct problems that would be worse than an incompatibility between releases.
Currently, FOR UPGRADE
discovers these
incompatibilities:
The indexing order for end-space in
TEXT
columns for
InnoDB
and MyISAM
tables changed between MySQL 4.1 and 5.0.
The storage method of the new DECIMAL
data type changed between MySQL 5.0.3 and 5.0.5.
The other 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 | Do not scan the rows to check for incorrect links. |
FAST | Check only tables that have not been closed properly. |
CHANGED | Check only tables that have been changed since the last check or that have not been closed properly. |
MEDIUM | Scan rows to verify that deleted links are valid. 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 takes 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 has the same result 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 that does a quick check on the table to determine whether it was closed properly:
CHECK TABLE test_table FAST QUICK;
Note: In some cases,
CHECK TABLE
changes the table. This happens
if the table is marked as “corrupted” or
“not closed properly” but CHECK
TABLE
does not find any problems in the table. In
this case, CHECK TABLE
marks the table as
okay.
If a table is corrupted, it is 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
does not find an error in the data file. (In most cases, under
normal usage, MySQL should find any error in the data file. If
this happens, the table is marked as “corrupted”
and cannot be used until it is 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
tables from time to time. In most cases,
FAST
is to be preferred over
CHANGED
. (The only case when it is not
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
cannot 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 is possible to create a row where the
AUTO_INCREMENT
column is 0 by
explicitly setting the column to 0 with an
UPDATE
statement.)
This is not 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 changes 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, simply execute an
UPDATE
statement to set the column to
some value other than 0.
CHECKSUM TABLEtbl_name
[,tbl_name
] ... [ QUICK | EXTENDED ]
CHECKSUM TABLE
reports a table checksum.
With QUICK
, 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 when
you create the table; currently, this is supported only for
MyISAM
tables. See
Section 13.1.5, “CREATE TABLE
Syntax”.
With EXTENDED
, the entire table is read row
by row and the checksum is calculated. This can be very slow
for large tables.
If neither QUICK
nor
EXTENDED
is specified, MySQL returns a live
checksum if the table storage engine supports it and scans the
table otherwise.
For a non-existent table, CHECKSUM TABLE
returns NULL
and, as of MySQL 5.0.3,
generates a warning.
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLEtbl_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
, VARBINARY
,
BLOB
, or TEXT
columns).
Deleted rows are maintained in a linked list and subsequent
INSERT
operations reuse old row 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 is not likely that you need to do
this more than once a week or month and only on certain
tables.
OPTIMIZE TABLE
works only for
MyISAM
, BDB
, and
InnoDB
tables.
For MyISAM
tables, OPTIMIZE
TABLE
works as follows:
If the table has deleted or split rows, repair the table.
If the index pages are not sorted, sort them.
If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.
For BDB
tables, OPTIMIZE
TABLE
currently is mapped to ANALYZE
TABLE
. See Section 13.5.2.1, “ANALYZE TABLE
Syntax”.
For InnoDB
tables, OPTIMIZE
TABLE
is mapped to ALTER TABLE
,
which rebuilds the table to update index statistics and free
unused space in the clustered index.
You can make OPTIMIZE TABLE
work on other
storage engines by starting mysqld with the
--skip-new
or --safe-mode
option. In this case, OPTIMIZE TABLE
is
just mapped to ALTER TABLE
.
OPTIMIZE TABLE
returns a result set with
the following columns:
Column | Value |
Table | The table name |
Op | Always optimize |
Msg_type | One of status , error ,
info , or warning |
Msg_text | The message |
Note that MySQL locks the table during the time
OPTIMIZE TABLE
is running.
OPTIMIZE TABLE
statements are written to
the binary log unless the optional
NO_WRITE_TO_BINLOG
keyword(or its alias
LOCAL
) is used. This is done so that
OPTIMIZE TABLE
statements used on a MySQL
server acting as a replication master will be replicated by
default to the replication slave.
REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLEtbl_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 for MyISAM
and for
ARCHIVE
tables. See
Section 14.1, “The MyISAM
Storage Engine”, and
Section 14.8, “The ARCHIVE
Storage Engine”.
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”, and
Section 14.1.4, “MyISAM
Table Problems”.
Warning: If the server dies
during a REPAIR TABLE
operation, it is
essential after restarting it that you immediately execute
another REPAIR TABLE
statement for the
table before performing any other operations on it. (It is
always a good idea 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.
REPAIR TABLE
returns a result set 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
has a Msg_type
value of
status
and Msg_test
normally should be OK
. If you do not get
OK
, you should try repairing the table with
myisamchk --safe-recover. (REPAIR
TABLE
does not yet implement all the options of
myisamchk.) With myisamchk
--safe-recover, you can also use options that
REPAIR TABLE
does not support, such as
--max-record-length
.
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. This type of repair is like that done by
myisamchk --safe-recover.
There is also a USE_FRM
mode available for
REPAIR TABLE
. Use this if the
.MYI
index file is missing or if its
header is corrupted. In this mode, MySQL re-creates 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. The .MYI
header contains important table metadata (in particular,
current AUTO_INCREMENT
value and
Delete link
) that are lost in
REPAIR ... USE_FRM
. Don't use
USE_FRM
if the table is compressed because
this information is also stored in the
.MYI
file.
REPAIR TABLE
statements are written to the
binary log unless the optional
NO_WRITE_TO_BINLOG
keyword (or its alias
LOCAL
) is used. This is done so that
REPAIR TABLE
statements used on a MySQL
server acting as a replication master will be replicated by
default to the replication slave.
RESTORE TABLEtbl_name
[,tbl_name
] ... FROM '/path/to/backup/directory
'
RESTORE TABLE
restores the table or tables
from a backup that was made with BACKUP
TABLE
. Existing tables are not overwritten; if you
try to restore over an existing table, an error occurs. Just
as for 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, and 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 takes.
RESTORE TABLE
returns a result set 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 |
SETvariable_assignment
[,variable_assignment
] ...variable_assignment
:user_var_name
=expr
| [GLOBAL | SESSION]system_var_name
=expr
| [@@global. | @@session. | @@]system_var_name
=expr
The SET
statement assigns values to different
types of variables that affect the operation of the server or
your client. Older versions of MySQL employed SET
OPTION
, but this syntax is deprecated in favor of
SET
without OPTION
.
This section describes use of SET
for
assigning values to system variables or user variables. For
general information about these types of variables, see
Section 5.2.2, “Server System Variables”, and
Section 9.3, “User-Defined Variables”. System variables also can be
set at server startup, as described in
Section 5.2.3, “Using System Variables”.
Some variants of SET
syntax are used in other
contexts:
SET PASSWORD
assigns account passwords.
See Section 13.5.1.6, “SET PASSWORD
Syntax”.
SET TRANSACTION ISOLATION LEVEL
sets the
isolation level for transaction processing. See
Section 13.4.6, “SET TRANSACTION
Syntax”.
SET
is used within stored routines to
assign values to local routine variables. See
Section 17.2.7.2, “Variable SET
Statement”.
The following discussion shows the different
SET
syntaxes that you can use to set
variables. The examples use the =
assignment
operator, but the :=
operator also is
allowable.
A user variable is written as
@
and can
be set as follows:
var_name
SET @var_name
=expr
;
Many system variables are dynamic and can be changed while the
server runs by using the SET
statement. For a
list, see Section 5.2.3.2, “Dynamic System Variables”. To change
a system variable with SET
, refer to it as
var_name
, optionally preceded by a
modifier:
To indicate explicitly that a variable is a global variable,
precede its name by GLOBAL
or
@@global.
. The SUPER
privilege is required to set global variables.
To indicate explicitly that a variable is a session
variable, precede its name by SESSION
,
@@session.
, or @@
.
Setting a session variable requires no special privilege,
but a client can change only its own session variables, not
those of any other client.
LOCAL
and @@local.
are
synonyms for SESSION
and
@@session.
.
If no modifier is present, SET
changes
the session variable.
A SET
statement can contain multiple variable
assignments, separated by commas. If you set several system
variables, the most recent GLOBAL
or
SESSION
modifier in the statement is used for
following variables that have no modifier specified.
Examples:
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;
When you assign a value to a system variable with
SET
, you cannot use suffix letters in the
value (as can be done with startup options). However, the value
can take the form of an expression:
SET sort_buffer_size = 10 * 1024 * 1024;
The @@
syntax for system variables is supported for compatibility with
some other database systems.
var_name
If you change a session system variable, the value remains in effect until your session ends or until you change the variable to a different value. The change is not visible to other clients.
If you change a global system variable, the value is remembered
and used for new connections until the server restarts. (To make
a global system variable setting permanent, you should set it in
an option file.) The change is visible to any client that
accesses that global variable. However, the change affects the
corresponding session variable only for clients that connect
after the change. The global variable change does not affect the
session variable for any client that is currently connected (not
even that of the client that issues the SET
GLOBAL
statement).
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
(or
@@global.
) when setting a global variable.
To set a SESSION
variable to the
GLOBAL
value or a GLOBAL
value to the compiled-in MySQL default value, use the
DEFAULT
keyword. 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;
Not all system variables can be set to
DEFAULT
. In such cases, use of
DEFAULT
results in an error.
You can refer to the values of specific global or sesson system
variables in expressions by using one of the
@@
-modifiers. For example, you can retrieve
values in a SELECT
statement like this:
SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;
When you refer to a system variable in an expression as
@@
(that
is, when you do not specify var_name
@@global.
or
@@session.
), MySQL returns the session value
if it exists and the global value otherwise. (This differs from
SET @@
, which always refers
to the session value.)
var_name
=
value
To display system variables names and values, use the
SHOW VARIABLES
statement. (See
Section 13.5.4.24, “SHOW VARIABLES
Syntax”.)
The following list describes options that have non-standard
syntax or that are not described in the list of system variables
found in Section 5.2.2, “Server System Variables”. Although the
options described here 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 |
+--------------+
The lettercase of thse options does not matter.
AUTOCOMMIT = {0 | 1}
Set the autocommit mode. If set to 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. By default, client
connections begin with AUTOCOMMENT
set to
1. 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}
If set to 1, all temporary tables are stored on disk rather
than in memory. This is a little slower, but the error
The table
does not occur for tbl_name
is
fullSELECT
operations that require a large temporary table. The default
value for a new connection is 0 (use in-memory temporary
tables). Normally, you should never need to set this
variable, because in-memory tables are automatically
converted to disk-based tables as required.
(Note: This variable was
formerly named SQL_BIG_TABLES
.)
CHARACTER SET
{
charset_name
|
DEFAULT}
This maps all strings from and to the client with the given
mapping. You can add new mappings by editing
sql/convert.cc
in the MySQL source
distribution. 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
. See
Section 10.4, “Connection Character Sets and Collations”.
The default mapping can be restored by using the value
DEFAULT
.
Note that the syntax for SET CHARACTER
SET
differs from that for setting most other
options.
FOREIGN_KEY_CHECKS = {0 | 1}
If set to 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 from that required by their parent/child
relationships. See
Section 14.2.6.4, “FOREIGN KEY
Constraints”.
IDENTITY =
value
This variable is a synonym for the
LAST_INSERT_ID
variable. It exists for
compatibility with other database systems. You can read its
value with SELECT @@IDENTITY
, and set it
using SET IDENTITY
.
INSERT_ID =
value
Set the value to be used by the following
INSERT
or ALTER TABLE
statement when inserting an
AUTO_INCREMENT
value. This is mainly used
with the binary log.
LAST_INSERT_ID =
value
Set the value to be returned from
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 the 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. Setting
character_set_connection
to
charset_name
also sets
collation_connection
to the default
collation for charset_name
. See
Section 10.4, “Connection Character Sets and Collations”.
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.
ONE_SHOT
This option is a modifier, not a variable. It can be used to
influence the effect of variables that set the character
set, the collation, and the time zone.
ONE_SHOT
is primarily used for
replication purposes: mysqlbinlog uses
SET ONE_SHOT
to modify temporarily the
values of character set, collation, and time zone variables
to reflect at rollforward what they were originally.
ONE_SHOT
is available as of MySQL 5.0.
You cannot use ONE_SHOT
with other than
the allowed set of variables; if you try, you get an error
like this:
mysql> SET ONE_SHOT max_allowed_packet = 1;
ERROR 1382 (HY000): The 'SET ONE_SHOT' syntax is reserved for purposes
internal to the MySQL server
If ONE_SHOT
is used with the allowed
variables, it changes the variables as requested, but only
for the next non-SET
statement. After
that, the server resets all character set, collation, and
time zone-related system variables to their previous values.
Example:
mysql>SET ONE_SHOT character_set_connection = latin5;
mysql>SET ONE_SHOT collation_connection = latin5_turkish_ci;
mysql>SHOW VARIABLES LIKE '%_connection';
+--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | character_set_connection | latin5 | | collation_connection | latin5_turkish_ci | +--------------------------+-------------------+ mysql>SHOW VARIABLES LIKE '%_connection';
+--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | character_set_connection | latin1 | | collation_connection | latin1_swedish_ci | +--------------------------+-------------------+
SQL_AUTO_IS_NULL = {0 | 1}
If set to 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 NULL
This behavior is used by some ODBC programs, such as Access.
SQL_BIG_SELECTS = {0 | 1}
If set to 0, MySQL aborts SELECT
statements that are likely to take a very long time to
execute (that is, statements for which the optimizer
estimates that the number of examined rows exceeds 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
is 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.
SQL_LOG_BIN = {0 | 1}
If set to 0, no logging is done to the binary log for the
client. The client must have the SUPER
privilege to set this option.
SQL_LOG_OFF = {0 | 1}
If set to 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}
This variable is deprecated, and is mapped to
SQL_LOG_BIN
.
SQL_NOTES = {0 | 1}
When set to 1 (the default), warnings of
Note
level are recorded. When set to 0,
Note
warnings are suppressed.
mysqldump includes output to set this
variable to 0 so that reloading the dump file does not
produce warnings for events that do not affect the integrity
of the reload operation. SQL_NOTES
was
added in MySQL 5.0.3.
SQL_QUOTE_SHOW_CREATE = {0 | 1}
If set to 1, the server quotes identifiers for SHOW
CREATE TABLE
and SHOW CREATE
DATABASE
statements. If set to 0, quoting is
disabled. This option is enabled by default so that
replication works for identifiers that require quoting. See
Section 13.5.4.6, “SHOW CREATE TABLE
Syntax”, and
Section 13.5.4.4, “SHOW CREATE DATABASE
Syntax”.
SQL_SAFE_UPDATES = {0 | 1}
If set to 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.
SQL_SELECT_LIMIT =
{
value
| DEFAULT}
The maximum number of rows to return from
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_SELECT_LIMIT
does not apply to
SELECT
statements executed within stored
routines. It also does not apply to
SELECT
statements that do not produce a
result set to be returned to the client. These include
SELECT
statements in subqueries,
CREATE TABLE ... SELECT
, and
INSERT INTO ... SELECT
.
SQL_WARNINGS = {0 | 1}
This variable controls whether single-row
INSERT
statements produce an information
string if warnings occur. The default is 0. Set the value to
1 to produce an information string.
TIMESTAMP =
{
timestamp_value
|
DEFAULT}
Set the time for this client. This is used to get the
original timestamp if you use the binary log to restore
rows. timestamp_value
should be a Unix
epoch timestamp, not a MySQL timestamp.
UNIQUE_CHECKS = {0 | 1}
If set to 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
.
SHOW CHARACTER SET
SyntaxSHOW COLLATION
SyntaxSHOW COLUMNS
SyntaxSHOW CREATE DATABASE
SyntaxSHOW CREATE PROCEDURE
and SHOW CREATE FUNCTION
SyntaxSHOW CREATE TABLE
SyntaxSHOW CREATE VIEW
SyntaxSHOW DATABASES
SyntaxSHOW ENGINE
SyntaxSHOW ENGINES
SyntaxSHOW ERRORS
SyntaxSHOW GRANTS
SyntaxSHOW INDEX
SyntaxSHOW INNODB STATUS
SyntaxSHOW LOGS
SyntaxSHOW OPEN TABLES
SyntaxSHOW PRIVILEGES
SyntaxSHOW PROCEDURE STATUS
and SHOW FUNCTION STATUS
SyntaxSHOW PROCESSLIST
SyntaxSHOW STATUS
SyntaxSHOW TABLE STATUS
SyntaxSHOW TABLES
SyntaxSHOW TRIGGERS
SyntaxSHOW VARIABLES
SyntaxSHOW WARNINGS
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 FROMtbl_name
[FROMdb_name
] [LIKE 'pattern
'] SHOW CREATE DATABASEdb_name
SHOW CREATE FUNCTIONfuncname
SHOW CREATE PROCEDUREprocname
SHOW CREATE TABLEtbl_name
SHOW DATABASES [LIKE 'pattern
'] SHOW ENGINEengine_name
{LOGS | STATUS } SHOW [STORAGE] ENGINES SHOW ERRORS [LIMIT [offset
,]row_count
] SHOW FUNCTION STATUS [LIKE 'pattern
'] SHOW GRANTS FORuser
SHOW INDEX FROMtbl_name
[FROMdb_name
] SHOW INNODB STATUS SHOW PROCEDURE STATUS [LIKE 'pattern
'] SHOW [BDB] LOGS SHOW PRIVILEGES SHOW [FULL] PROCESSLIST SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern
'] SHOW TABLE STATUS [FROMdb_name
] [LIKE 'pattern
'] SHOW [OPEN] TABLES [FROMdb_name
] [LIKE 'pattern
'] SHOW TRIGGERS SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern
'] SHOW WARNINGS [LIMIT [offset
,]row_count
]
The SHOW
statement also has forms that
provide information about replication master and slave servers
and are described in Section 13.6, “Replication Statements”:
SHOW BINLOG EVENTS SHOW MASTER LOGS SHOW MASTER STATUS SHOW SLAVE HOSTS SHOW SLAVE STATUS
If the syntax for a given SHOW
statement
includes a LIKE
'
part,
pattern
''
is a
string that can contain the SQL
‘pattern
'%
’ and
‘_
’ wildcard characters. The
pattern is useful for restricting statement output to matching
values.
Several SHOW
statements also accept a
WHERE
clause that provides more flexibility
in specifying which rows to display. See
Section 20.18, “Extensions to SHOW
Statements”.
SHOW 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 | cp1252 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 required to store one character.
SHOW COLLATION [LIKE 'pattern
']
The output from SHOW COLLATION
includes all
available character sets. It takes an optional
LIKE
clause whose
pattern
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 [FULL] COLUMNS FROMtbl_name
[FROMdb_name
] [LIKE 'pattern
']
SHOW COLUMNS
displays information about the
columns in a given table. It also works for views as of MySQL
5.0.1.
If the data types differ from what you expect them to be based
on your CREATE TABLE
statement, note that
MySQL sometimes changes data types when you create or alter a
table. The conditions for which this occurs are described in
Section 13.1.5.1, “Silent Column Specification Changes”.
The FULL
keyword causes the output to
include the privileges you have as well as any per-column
comments for each column.
You can use db_name.tbl_name
as an
alternative to the
syntax. In other
words, these two statements are equivalent:
tbl_name
FROM
db_name
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”.
SHOW CREATE {DATABASE | SCHEMA} db_name
Shows the CREATE DATABASE
statement that
creates the given database. SHOW CREATE
SCHEMA
is a synonym for SHOW CREATE
DATABASE
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 */ mysql>SHOW CREATE SCHEMA test\G
*************************** 1. row *************************** Database: test Create Database: CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */
SHOW CREATE DATABASE
quotes table and
column names according to the value of the
SQL_QUOTE_SHOW_CREATE
option. See
Section 13.5.3, “SET
Syntax”.
SHOW CREATE {PROCEDURE | FUNCTION} sp_name
This statement is a MySQL extension. Similar to SHOW
CREATE TABLE
, it returns the exact string that can
be used to re-create the named routine.
mysql> SHOW CREATE FUNCTION test.hello\G
*************************** 1. row ***************************
Function: hello
sql_mode:
Create Function: CREATE FUNCTION `test`.`hello`(s CHAR(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!')
SHOW CREATE TABLE tbl_name
Shows the CREATE TABLE
statement that
creates the given table. As of MySQL 5.0.1, this statement
also works with views.
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)
) ENGINE=MyISAM
SHOW CREATE TABLE
quotes table and column
names according to the value of the
SQL_QUOTE_SHOW_CREATE
option. See
Section 13.5.3, “SET
Syntax”.
SHOW CREATE VIEW view_name
This statement shows a CREATE VIEW
statement that creates the given view.
mysql> SHOW CREATE VIEW v;
+------+----------------------------------------------------+
| View | Create View |
+------+----------------------------------------------------+
| v | CREATE VIEW `test`.`v` AS select 1 AS `a`,2 AS `b` |
+------+----------------------------------------------------+
This statement was added in MySQL 5.0.1.
Prior to MySQL 5.0.11, the output columns from this statement
were shown as Table
and Create
Table
.
You can also obtain information about view objects from
INFORMATION_SCHEMA
, which contains a
VIEWS
table. See
Section 20.15, “The INFORMATION_SCHEMA VIEWS
Table”.
SHOW {DATABASES | SCHEMAS} [LIKE 'pattern
']
SHOW DATABASES
lists the databases on the
MySQL server host. SHOW SCHEMAS
is a
synonym for SHOW DATABASES
as of MySQL
5.0.2.
You see only those databases for which you have some kind of
privilege, unless you have the global SHOW
DATABASES
privilege. You can also get this list
using the mysqlshow command.
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 ENGINE engine_name
{LOGS | STATUS }
SHOW ENGINE
displays log or status
information about a storage engine. The following statements
currently are supported:
SHOW ENGINE BDB LOGS SHOW ENGINE INNODB STATUS
SHOW ENGINE BDB LOGS
displays status
information about existing BDB
log files.
It returns the following fields:
File
The full path to the log file.
Type
The log file type (BDB
for Berkeley DB
log files).
Status
The status of the log file (FREE
if the
file can be removed, or IN USE
if the
file is needed by the transaction subsystem)
SHOW ENGINE INNODB STATUS
displays
extensive information about the state of the
InnoDB
storage engine.
The InnoDB
Monitors provide additional
information about InnoDB
processing. See
Section 14.2.11.1, “SHOW ENGINE INNODB STATUS
and the InnoDB
Monitors”.
Older (and now deprecated) synonyms for these statements are
SHOW [BDB] LOGS
and SHOW INNODB
STATUS
.
SHOW [STORAGE] ENGINES
SHOW ENGINES
displays status information
about the server's storage engines. This is particularly
useful for checking whether a storage engine is supported, or
to see what the default engine is. 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: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
*************************** 3. row ***************************
Engine: HEAP
Support: YES
Comment: Alias for MEMORY
*************************** 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: NO
Comment: Clustered, fault-tolerant, memory-based tables
*************************** 13. row ***************************
Engine: NDB
Support: NO
Comment: Alias for NDBCLUSTER
*************************** 14. row ***************************
Engine: EXAMPLE
Support: NO
Comment: Example storage engine
*************************** 15. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
*************************** 16. row ***************************
Engine: CSV
Support: NO
Comment: CSV storage engine
*************************** 17. row ***************************
Engine: FEDERATED
Support: YES
Comment: Federated MySQL storage engine
*************************** 18. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
The 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, the
Support
value for the
InnoDB
row has the value
DEFAULT
. See
Chapter 14, Storage Engines and Table Types.
SHOW 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.
The LIMIT
clause has the same syntax as for
the SELECT
statement. See
Section 13.2.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.25, “SHOW WARNINGS
Syntax”.
SHOW GRANTS FOR user
This statement lists the GRANT
statement or
statements that must be issued to duplicate the privileges
that are granted to a MySQL user account. The account is named
using the same format as for the GRANT
statement; 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 row for the
account.
mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
To list the privileges granted to the account that you are using to connect to the server, you can use any of the following statements:
SHOW GRANTS; SHOW GRANTS FOR CURRENT_USER; SHOW GRANTS FOR CURRENT_USER();
SHOW GRANTS
displays only the privileges
granted explicitly to the named account. Other privileges
might be available to the account, but they are not displayed.
For example, if an anonymous account exists, the named account
might be able to use its privileges, but SHOW
GRANTS
will not display them.
SHOW INDEX FROMtbl_name
[FROMdb_name
]
SHOW INDEX
returns table index information.
The format resembles that of the
SQLStatistics
call in ODBC.
SHOW INDEX
returns the following fields:
Table
The name of the table.
Non_unique
0 if the index cannot contain duplicates, 1 if it can.
Key_name
The name of the index.
Seq_in_index
The column sequence number in the index, starting with 1.
Column_name
The column name.
How the column is sorted in the index. In MySQL, this can
have values ‘A
’ (Ascending)
or NULL
(Not sorted).
An estimate of the number of unique values in the index.
This is updated by running ANALYZE
TABLE
or myisamchk -a.
Cardinality
is counted based on
statistics stored as integers, so the value is not
necessarily exact even for small tables. The higher the
cardinality, the greater the chance that MySQL uses the
index when doing joins.
Sub_part
The number of indexed characters if the column is only
partly indexed, NULL
if the entire
column is indexed.
Packed
Indicates how the key is packed. NULL
if it is not.
Null
Contains YES
if the column may contain
NULL
. If not, the column contains
NO
as of MySQL 5.0.3, and
''
before that.
Index_type
The index method used (BTREE
,
FULLTEXT
, HASH
,
RTREE
).
Comment
Various remarks.
You can use
db_name
.tbl_name
as an alternative to the
syntax. These two
statements are equivalent:
tbl_name
FROM
db_name
SHOW INDEX FROM mytable FROM mydb; 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
In MySQL 5.0, this is a deprecated synonym for
SHOW ENGINE INNODB STATUS
. See
Section 13.5.4.9, “SHOW ENGINE
Syntax”.
SHOW [BDB] LOGS
In MySQL 5.0, this is a deprecated synonym for
SHOW ENGINE BDB LOGS
. See
Section 13.5.4.9, “SHOW ENGINE
Syntax”.
SHOW OPEN TABLES [FROMdb_name
] [LIKE 'pattern
']
SHOW OPEN TABLES
lists the
non-TEMPORARY
tables that are currently
open in the table cache. See Section 7.4.8, “How MySQL Opens and Closes Tables”.
SHOW OPEN TABLES
returns the following
fields:
Database
The database containing the table.
Table
The table name.
In_use
The number of times the table currently is in use by queries. If the count is zero, the table is open, but not currently being used.
Name_locked
Whether the table name is locked. Name locking is used for operations such as dropping or renaming tables.
The FROM
and LIKE
clauses may be used as of MySQL 5.0.12.
SHOW PRIVILEGES
SHOW PRIVILEGES
shows the list of system
privileges that the MySQL server supports. The exact list of
privileges depends on the version of your server.
mysql> SHOW PRIVILEGES\G
*************************** 1. row ***************************
Privilege: Alter
Context: Tables
Comment: To alter the table
*************************** 2. row ***************************
Privilege: Alter routine
Context: Functions,Procedures
Comment: To alter or drop stored functions/procedures
*************************** 3. row ***************************
Privilege: Create
Context: Databases,Tables,Indexes
Comment: To create new databases and tables
*************************** 4. row ***************************
Privilege: Create routine
Context: Functions,Procedures
Comment: To use CREATE FUNCTION/PROCEDURE
*************************** 5. row ***************************
Privilege: Create temporary tables
Context: Databases
Comment: To use CREATE TEMPORARY TABLE
...
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern
']
This statement is a MySQL extension. It returns characteristics of routines, such as the database, name, type, creator, and creation and modification dates. If no pattern is specified, the information for all stored procedures or all stored functions is listed, depending on which statement you use.
mysql> SHOW FUNCTION STATUS LIKE 'hello'\G
*************************** 1. row ***************************
Db: test
Name: hello
Type: FUNCTION
Definer: testuser@localhost
Modified: 2004-08-03 15:29:37
Created: 2004-08-03 15:29:37
Security_type: DEFINER
Comment:
You can also get information about stored routines from the
ROUTINES
table in
INFORMATION_SCHEMA
. See
Section 20.14, “The INFORMATION_SCHEMA ROUTINES
Table”.
SHOW [FULL] PROCESSLIST
SHOW PROCESSLIST
shows you which threads
are running. You can also get this information using the
mysqladmin processlist command. 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 do not use the
FULL
keyword, only the first 100 characters
of each statement are shown in the Info
field.
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).
The output of SHOW PROCESSLIST
may look
like this:
mysql> SHOW FULL PROCESSLIST\G *************************** 1. row *************************** Id: 1 User: system user Host: db: NULL Command: Connect Time: 1030455 State: Waiting for master to send event Info: NULL *************************** 2. row *************************** Id: 2 User: system user Host: db: NULL Command: Connect Time: 1004 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL *************************** 3. row *************************** Id: 3112 User: replikator Host: artemis:2204 db: NULL Command: Binlog Dump Time: 2144 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 4. row *************************** Id: 3113 User: replikator Host: iconnect2:45781 db: NULL Command: Binlog Dump Time: 2086 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 5. row *************************** Id: 3123 User: stefan Host: localhost db: apollon Command: Query Time: 0 State: NULL Info: SHOW FULL PROCESSLIST 5 rows in set (0.00 sec)
The columns have the following meaning:
Id
The connection identifier.
User
The MySQL user who issued the statement. If this is
system user
, it refers to a non-client
thread spawned by the server to handle tasks internally.
This could be the I/O or SQL thread used on replication
slaves or a delayed-row handler. For system
user
, there is no host specified in the
Host
column.
Host
The hostname of the client issuing the statement (except
for system user
where there is no
host). SHOW PROCESSLIST
reports the
hostname for TCP/IP connections in
format to make it easier to determine which client is
doing what.
host_name
:client_port
db
The default database, if one is selected, otherwise
NULL
.
Command
The value of that column corresponds to the
COM_
commands of the client/server protocol. See
Section 5.2.4, “Server Status Variables”
xxx
The Command
value may be one of the
following: Binlog Dump
, Change
user
, Close stmt
,
Connect
, Connect
Out
, Create DB
,
Debug
, Delayed
insert
, Drop DB
,
Error
, Execute
,
Fetch
, Field List
,
Init DB
, Kill
,
Long Data
, Ping
,
Prepare
,
Processlist
, Query
,
Quit
, Refresh
,
Register Slave
, Reset
stmt
, Set option
,
Shutdown
, Sleep
,
Statistics
, Table
Dump
, Time
Time
The time in seconds between the start of the statement or command and now.
State
An action, event, or state, which can be one of the
following: After create
,
Analyzing
, Changing
master
, Checking master
version
, Checking table
,
Connecting to master
, Copying
to group table
, Copying to tmp
table
, Creating delayed
handler
, Creating index
,
Creating sort index
, Creating
table from master dump
, Creating tmp
table
, Execution of
init_command
, FULLTEXT
initialization
, Finished reading one
binlog; switching to next binlog
,
Flushing tables
,
Killed
, Killing
slave
, Locked
,
Making temp file
, Opening
master dump table
, Opening
table
, Opening tables
,
Processing request
, Purging
old relay logs
, Queueing master event
to the relay log
, Reading event from
the relay log
, Reading from
net
, Reading master dump table
data
, Rebuilding the index on master
dump table
, Reconnecting after a failed
binlog dump request
, Reconnecting after
a failed master event read
, Registering
slave on master
, Removing
duplicates
, Reopen tables
,
Repair by sorting
, Repair
done
, Repair with keycache
,
Requesting binlog dump
,
Rolling back
, Saving
state
, Searching rows for
update
, Sending binlog event to
slave
, Sending data
,
Sorting for group
, Sorting for
order
, Sorting index
,
Sorting result
, System
lock
, Table lock
,
Thread initialized
,
Updating
, User lock
,
Waiting for INSERT
, Waiting
for master to send event
, Waiting for
master update
, Waiting for slave mutex
on exit
, Waiting for table
,
Waiting for tables
, Waiting
for the next event in relay log
,
Waiting on cond
, Waiting to
finalize termination
, Waiting to
reconnect after a failed binlog dump request
,
Waiting to reconnect after a failed master event
read
, Writing to net
,
allocating local table
,
cleaning up
, closing
tables
, converting HEAP to
MyISAM
, copy to tmp table
,
creating table
, deleting from
main table
, deleting from reference
tables
,
discard_or_import_tablespace
,
end
, freeing items
,
got handler lock
, got old
table
, info
,
init
, insert
,
logging slow query
,
login
, preparing
,
purging old relay logs
, query
end
, removing tmp table
,
rename
, rename result
table
, reschedule
,
setup
, starting
slave
, statistics
,
storing row into queue
,
update
, updating
,
updating main table
, updating
reference tables
, upgrading
lock
, waiting for delay_list
,
waiting for handler insert
,
waiting for handler lock
,
waiting for handler open
The most common State
values are
described in the rest of this section. Most of the other
State
values are useful only for
finding bugs in the server. See also
Section 6.3, “Replication Implementation Details”, for
additional information about process states for
replication servers.
For the SHOW PROCESSLIST
statement, the
value of State
is
NULL
.
Info
The statement that the thread is executing, or
NULL
if it is not executing any
statement.
Some State
values commonly seen in the
output from SHOW PROCESSLIST
:
Checking table
The thread is performing a table check operation.
Closing tables
Means that the thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, you should verify that you do not have a full disk and that the disk is not in very heavy use.
Connect Out
A replication slave is connecting to its master.
Copying to tmp table
The server is copying to a temporary table in memory.
Copying to tmp table on disk
The server is copying to a temporary table on disk. The
temporary result set was larger than
tmp_table_size
and the thread is
changing the temporary table from in-memory to disk-based
format to save memory.
Creating tmp table
The thread is creating a temporary table to hold a part of the result for the query.
deleting from main table
The server is executing the first part of a multiple-table delete. It is deleting only from the first table, and saving fields and offsets to be used for deleting from the other (reference) tables.
deleting from reference tables
The server is executing the second part of a multiple-table delete and deleting the matched rows from the other tables.
Flushing tables
The thread is executing FLUSH TABLES
and is waiting for all threads to close their tables.
FULLTEXT initialization
The server is preparing to perform a natural-language full-text search.
Killed
Someone has sent a KILL
statement to
the thread and it should abort next time it checks the
kill flag. The flag is checked in each major loop in
MySQL, but in some cases it might still take a short time
for the thread to die. If the thread is locked by some
other thread, the kill takes effect as soon as the other
thread releases its lock.
Locked
The query is locked by another query.
Sending data
The thread is processing rows for a
SELECT
statement and also is sending
data to the client.
Sorting for group
The thread is doing a sort to satisfy a GROUP
BY
.
Sorting for order
The thread is doing a sort to satisfy a ORDER
BY
.
Opening tables
The thread is trying to open a table. This is should be
very fast procedure, unless something prevents opening.
For example, an ALTER TABLE
or a
LOCK TABLE
statement can prevent
opening a table until the statement is finished.
Reading from net
The server is reading a packet from the network.
Removing duplicates
The query was using SELECT DISTINCT
in
such a way that MySQL could not 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
The thread got a lock for the table, but noticed after getting the lock that the underlying table structure changed. It has freed the lock, closed the table, and is trying to reopen it.
Repair by sorting
The repair code is using a sort to create indexes.
Repair with keycache
The repair code is using creating keys one by one through
the key cache. This is much slower than Repair by
sorting
.
Searching rows for update
The thread is doing a first phase to find all matching
rows before updating them. This has to be done if the
UPDATE
is changing the index that is
used to find the involved rows.
Sleeping
The thread is waiting for the client to send a new statement to it.
statistics
The server is calculating statistics to develop a query execution plan.
The thread is waiting to get an external system lock for
the table. If you are not using multiple
mysqld servers that are accessing the
same tables, you can disable system locks with the
--skip-external-locking
option.
Upgrading lock
The INSERT DELAYED
handler is trying to
get a lock for the table to insert rows.
Updating
The thread is searching for rows to update and is updating them.
updating main table
The server is executing the first part of a multiple-table update. It is updating only the first table, and saving fields and offsets to be used for updating the other (reference) tables.
updating reference tables
The server is executing the second part of a multiple-table update and updating the matched rows from the other tables.
User Lock
The thread is waiting on a GET_LOCK()
.
Waiting for tables
The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other threads have closed the table in question.
This notification takes place if another thread has used
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
The INSERT DELAYED
handler has
processed all pending inserts and is waiting for new ones.
Writing to net
The server is writing a packet to the network.
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.
SHOW [GLOBAL | SESSION] 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 names and values may be different for your server. The meaning of each variable is given in 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 |
+--------------------+----------+
The GLOBAL
and SESSION
options are new in MySQL 5.0.2. With the
GLOBAL
modifier, SHOW
STATUS
displays the status values for all
connections to MySQL. With SESSION
, it
displays the status values for the current connection. If no
modifier is present, the default is
SESSION
. LOCAL
is a
synonym for SESSION
.
Some status variables have only a global value. For these, you
get the same value for both GLOBAL
and
SESSION
.
SHOW TABLE STATUS [FROMdb_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.
As of MySQL 5.0.1, this statement also displays information about views.
SHOW TABLE STATUS
returns the following
fields:
Name
The name of the table.
Engine
The storage engine for the table. See Chapter 14, Storage Engines and Table Types.
Version
The version number of the table's
.frm
file.
Row_format
The row storage format (Fixed
,
Dynamic
, Compressed
,
Redundant
, Compact
).
Starting with MySQL/InnoDB 5.0.3, the format of
InnoDB
tables is reported as
Redundant
or
Compact
. Prior to 5.0.3,
InnoDB
tables are always in the
Redundant
format.
Rows
The number of rows. Some storage engines, such as
MyISAM
, 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.
The Rows
value is
NULL
for tables in the
INFORMATION_SCHEMA
database.
Avg_row_length
The average row length.
Data_length
The length of the data file.
Max_data_length
The maximum length of the data file. This is the total number of bytes of data that can be stored in the table, given the data pointer size used.
Index_length
The length of the index file.
Data_free
The number of allocated but unused bytes.
Auto_increment
The next AUTO_INCREMENT
value.
Create_time
When the table was created.
Update_time
When the data file was last updated. For some storage
engines, this value is NULL
. For
example, InnoDB
stores multiple tables
in its tablespace and the data file timestamp does not
apply.
Check_time
When the table was last checked. Not all storage engines
update this time, in which case the value is always
NULL
.
Collation
The table's character set and collation.
Checksum
The live checksum value (if any).
Create_options
Extra options used with CREATE TABLE
.
Comment
The comment used when creating the table (or information as to why MySQL could not access the table information).
In the table comment, InnoDB
tables 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 free
space is for only that table.
For MEMORY
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.
Beginning with MySQL 5.0.3, for NDB Cluster
tables, the output of this statement shows appropriate values
for the Avg_row_length
and
Data_length
columns, with the exception
that BLOB
columns are not taken into
account. In addition, the number of replicas is now shown in
the Comment
column (as
number_of_replicas
).
For views, all the fields displayed by SHOW TABLE
STATUS
are NULL
except that
Name
indicates the view name and
Comment
says view
.
SHOW [FULL] TABLES [FROMdb_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, this statement also lists any
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 for 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 does not show up in the
output from SHOW TABLES
or
mysqlshow db_name.
SHOW TRIGGERS [FROMdb_name
] [LIKEexpr
]
SHOW TRIGGERS
lists the triggers currently
defined on the MySQL server. This statement requires the
SUPER
privilege. It was implemented in
MySQL 5.0.10.
For the trigger ins_sum
as defined in
Section 18.3, “Using Triggers”, the output of this statement
is as shown here:
mysql> SHOW TRIGGERS LIKE 'acc%'\G
*************************** 1. row ***************************
Trigger: ins_sum
Event: INSERT
Table: account
Statement: SET @sum = @sum + NEW.amount
Timing: BEFORE
Created: NULL
sql_mode:
Definer: myname@localhost
Note: When using a
LIKE
clause with SHOW
TRIGGERS
, the expression to be matched
(expr
) is compared with the name of
the table on which the trigger is declared, and not with the
name of the trigger:
mysql> SHOW TRIGGERS LIKE 'ins%';
Empty set (0.01 sec)
A brief explanation of the columns in the output of this statement is shown here:
Trigger
The name of the trigger.
Event
The event that causes trigger activation: one of
'INSERT'
, 'UPDATE'
,
or 'DELETE'
.
Table
The table for which the trigger is defined.
Statement
The statement to be executed when the trigger is
activated. This is the same as the text shown in the
ACTION_STATEMENT
column of
INFORMATION_SCHEMA.TRIGGERS
.
Timing
One of the two values 'BEFORE'
or
'AFTER'
.
Created
Currently, the value of this column is always
NULL
.
sql_mode
The SQL mode in effect when the trigger executes. This column was added in MySQL 5.0.11.
Definer
The account that created the trigger. This column was added in MySQL 5.0.17.
You must have the SUPER
privilege to
execute SHOW TRIGGERS
.
See also Section 20.16, “The INFORMATION_SCHEMA TRIGGERS
Table”.
SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern
']
SHOW VARIABLES
shows the values of MySQL
system variables. This information also can be obtained using
the mysqladmin variables command.
With the GLOBAL
modifier, SHOW
VARIABLES
displays the values that are used for new
connections to MySQL. With SESSION
, it
displays the values that are in effect for the current
connection. If no modifier is present, the default is
SESSION
. LOCAL
is a
synonym for SESSION
.
If the default system variable values are unsuitable, you can
set them using command options when mysqld
starts, and most can be changed at runtime with the
SET
statement. See
Section 5.2.3, “Using System Variables”, and
Section 13.5.3, “SET
Syntax”.
Partial output is shown here. The list of names and values may be different for your server. Section 5.2.2, “Server System Variables”, describes the meaning of each variable, and Section 7.5.2, “Tuning Server Parameters”, provides information about tuning them.
mysql> SHOW VARIABLES;
+---------------------------------+-------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | / |
| bdb_cache_size | 8388600 |
| bdb_home | /var/lib/mysql/ |
| bdb_log_buffer_size | 32768 |
...
| 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 |
...
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 33554432 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.19-Max |
| version_comment | MySQL Community Edition - Max (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+-------------------------------------+
With a LIKE
clause, the statement displays
only those variables that match the pattern. To obtain a
specific variable name, use a LIKE
clause
as shown:
SHOW VARIABLES LIKE 'max_join_size'; SHOW SESSION VARIABLES LIKE 'max_join_size';
To get a list of variables whose name match a pattern, use the
‘%
’ wildcard character in a
LIKE
clause:
SHOW VARIABLES LIKE '%size%'; SHOW GLOBAL VARIABLES LIKE '%size%';
Wildcard characters can be used in any position within the
pattern to be matched. Strictly speaking, because
‘_
’ is a wildcard that matches
any single character, you should escape it as
‘\_
’ to match it literally. In
practice, this is rarely necessary.
SHOW 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. A related statement,
SHOW ERRORS
, shows only the errors. See
Section 13.5.4.11, “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 so low 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.2.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 22.2.3.69, “mysql_warning_count()
”.
Warnings are 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.
As of MySQL 5.0.3, you can set the
SQL_NOTES
session variable to 0 to cause
Note
-level warnings not to be recorded.
CACHE INDEXtbl_index_list
[,tbl_index_list
] ... INkey_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
enables you to
specify that only particular indexes from a table should be
assigned to the cache. 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 5.2.3.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 1284 (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 which client issues the queries.
FLUSH [LOCAL | NO_WRITE_TO_BINLOG]flush_option
[,flush_option
] ...
The FLUSH
statement clears or reloads
various internal caches used by MySQL. To execute
FLUSH
, you must have the
RELOAD
privilege.
The RESET
statement is similar to
FLUSH
. See Section 13.5.5.5, “RESET
Syntax”.
flush_option
can be any of the
following:
HOSTS
Empties the host cache tables. You should flush the host
tables if some of your hosts change IP number or if you
get the error message Host
'
. When more than
host_name
' is
blockedmax_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 '
”. You can start
mysqld with
host_name
' is blocked--max_connect_errors=999999999
to avoid
this error message.
DES_KEY_FILE
Reloads the DES keys from the file that was specified with
the --des-key-file
option at server
startup time.
LOGS
Closes and reopens all log files. If binary logging is
enabled, the sequence number of the binary log file is
incremented by one relative to the previous file. On Unix,
this is the same thing as sending a
SIGHUP
signal to the
mysqld server (except on some Mac OS X
10.3 versions where mysqld ignores
SIGHUP
and SIGQUIT
).
If the server was started with the
--log-error
option, FLUSH
LOGS
causes the error log is renamed with a
suffix of -old
and
mysqld creates a new empty log file. No
renaming occurs if the --log-error
option
was not given.
PRIVILEGES
Reloads the privileges from the grant tables in the
mysql
database.
QUERY CACHE
Defragment the query cache to better utilize its memory.
FLUSH QUERY CACHE
does not remove any
queries from the cache, unlike RESET QUERY
CACHE
.
STATUS
Resets most status variables to zero. This is something you should use only when debugging a query. See Section 1.8, “How to Report Bugs or Problems”.
{TABLE | TABLES}
[
tbl_name
[,
tbl_name
] ...]
When no tables are named, closes all open tables and
forces all tables in use to be closed. This also flushes
the query cache. With one or more table names, flushes
only the given tables. FLUSH TABLES
also removes all query results from the query cache, like
the RESET QUERY CACHE
statement.
TABLES WITH READ LOCK
Closes all open tables and locks all tables for all
databases with a read lock until you execute
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
Resets all per-hour user resources to zero. This enables
clients that have reached their hourly connection, query,
or update limits to resume activity immediately.
FLUSH USER_RESOURCES
does not apply to
the limit on maximum simultaneous connections. See
Section 13.5.1.3, “GRANT
Syntax”.
FLUSH
statements are written to the binary
log unless the optional NO_WRITE_TO_BINLOG
keyword (or its alias LOCAL
) is used. This
is done so that FLUSH
statements used on a
MySQL server acting as a replication master will be replicated
by default to the replication slave.
Note: 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.
Using FLUSH
statements within stored
functions or triggers is not supported in MySQL
5.0. However, you may use
FLUSH
in stored procedures, so long as
these are not called from stored functions or triggers. See
Section I.1, “Restrictions on Stored Routines and Triggers”.
See also Section 13.5.5.5, “RESET
Syntax”, for information about how
the RESET
statement is used with
replication.
KILL [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
statement.
thread_id
In MySQL 5.0.0, KILL
allows the optional
CONNECTION
or QUERY
modifier:
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 is currently executing, but leaves the
connection itself 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 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 any
connection threads of its own.
When you use 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:
In 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.
During 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.
During UPDATE
or
DELETE
operations, 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 are not rolled back.
GET_LOCK()
aborts and returns
NULL
.
An INSERT DELAYED
thread quickly
flushes (inserts) all rows it has in memory and then
terminates.
If the thread is in the table lock handler (state:
Locked
), the table lock is quickly
aborted.
If the thread is waiting for free disk space in a write call, the write is aborted with a “disk full” error message.
Warning: Killing a
REPAIR TABLE
or OPTIMIZE
TABLE
operation on a MyISAM
table results in a table that is corrupted and unusable.
Any reads or writes to such a table fail until you
optimize or repair it again (without interruption).
LOAD INDEX INTO CACHEtbl_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 for 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
enables
you to specify that only particular indexes from a table
should be preloaded. 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.
RESETreset_option
[,reset_option
] ...
The RESET
statement is used to clear the
state of various server operations. You must have the
RELOAD
privilege to execute
RESET
.
RESET
acts as a stronger version of the
FLUSH
statement. See
Section 13.5.5.2, “FLUSH
Syntax”.
reset_option
can be any of the
following:
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. (Known as FLUSH MASTER
in versions of MySQL before 3.23.26.) See
Section 13.6.1, “SQL Statements for Controlling Master Servers”.
QUERY CACHE
Removes all query results from the query cache.
SLAVE
Makes the slave forget its replication position in the
master binary logs. Also resets the relay log by deleting
any existing relay log files and beginning a new one.
(Known as FLUSH SLAVE
in versions of
MySQL before 3.23.26.) See
Section 13.6.2, “SQL Statements for Controlling Slave Servers”.
This section describes SQL statements related to replication. 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 | BINARY} LOGS TO 'log_name
' PURGE {MASTER | BINARY} LOGS BEFORE 'date
'
Deletes all the binary logs listed in the log index 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's
date
argument can be in
'YYYY-MM-DD hh:mm:ss'
format.
MASTER
and BINARY
are
synonyms.
This statement is safe to run while slaves are replicating. You do not need to stop them. 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 has yet to read, the slave will be unable to replicate after it comes up.
To safely purge logs, follow this procedure:
On each slave server, use SHOW SLAVE
STATUS
to check which log it is reading.
Obtain a listing of the logs on the master server with
SHOW MASTER LOGS
.
Determine the earliest log among all the slaves. This is the target log. If all the slaves are up to date, this is the last log on the list.
Make a backup of all the logs you are about to delete. (This step is optional, but always advisable.)
Purge all logs up to but not including the target log.
RESET 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.
SET SQL_LOG_BIN = {0|1}
Disables or enables binary logging for the current connection
(SQL_LOG_BIN
is a session variable) if the
client has the SUPER
privilege. The
statement is refused with an error if the client does not have
that privilege.
SHOW BINLOG EVENTS [IN 'log_name
'] [FROMpos
] [LIMIT [offset
,]row_count
]
Shows the events in the binary log. If you do not specify
'
, the
first binary log is displayed.
log_name
'
The LIMIT
clause has the same syntax as for
the SELECT
statement. See
Section 13.2.7, “SELECT
Syntax”.
Note: Issuing a SHOW
BINLOG EVENTS
with no LIMIT
clause could start a very time- and resource-consuming process
because the server returns to the client the complete contents
of the binary log (which includes all statements executed by
the server that modify data). As an alternative to
SHOW BINLOG EVENTS
, use the
mysqlbinlog utility to save the binary log
to a text file for later examination and analysis. See
Section 8.8, “mysqlbinlog — Utility for Processing Binary Log Files”.
SHOW 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”, that shows how to
determine which logs can be purged.
mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000015 | 724935 |
| binlog.000016 | 733481 |
+---------------+-----------+
SHOW BINARY LOGS
is equivalent to
SHOW MASTER LOGS
. The
File_size
column is displayed as of MySQL
5.0.7.
SHOW MASTER STATUS
Provides status information about the binary log files of the master. Example:
mysql > SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73 | test | manual,mysql |
+---------------+----------+--------------+------------------+
CHANGE MASTER TO
SyntaxLOAD DATA FROM MASTER
SyntaxLOAD TABLE tbl_name
FROM MASTER
SyntaxMASTER_POS_WAIT()
SyntaxRESET SLAVE
SyntaxSET GLOBAL SQL_SLAVE_SKIP_COUNTER
SyntaxSHOW SLAVE STATUS
SyntaxSTART SLAVE
SyntaxSTOP SLAVE
SyntaxReplication 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 TOmaster_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
'
CHANGE MASTER TO
changes the parameters
that the slave server uses for connecting to and communicating
with the master server. It also updates the contents of the
master.info
and
relay-log.info
files.
MASTER_USER
,
MASTER_PASSWORD
,
MASTER_SSL
,
MASTER_SSL_CA
,
MASTER_SSL_CAPATH
,
MASTER_SSL_CERT
,
MASTER_SSL_KEY
, and
MASTER_SSL_CIPHER
provide information to
the slave about how to connect to its master.
The SSL options (MASTER_SSL
,
MASTER_SSL_CA
,
MASTER_SSL_CAPATH
,
MASTER_SSL_CERT
,
MASTER_SSL_KEY
, and
MASTER_SSL_CIPHER
) can be changed even on
slaves that are compiled without SSL support. They are saved
to the master.info
file, but are ignored
unless 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:
STOP SLAVE; -- if replication was running CHANGE MASTER TO MASTER_PASSWORD='new3cret'; 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 number might 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 from 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 cannot 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 there is no discontinuity in
replication, even if the slave SQL thread was late compared to
the slave I/O thread, when you merely want to change, say, the
password to use.
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; the relay_log_purge
global variable
is set silently to 0.
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='
on the slave.
log_name_on_master
',
MASTER_LOG_POS=log_offset_on_master
The following 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:
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;
The next example shows an operation that is less frequently
employed. It is used 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 need only use
CHANGE MASTER TO
and start the SQL thread
(START SLAVE SQL_THREAD
):
CHANGE MASTER TO RELAY_LOG_FILE='slave-relay-bin.006', RELAY_LOG_POS=4025;
You can even use the second operation in a non-replication
setup with a standalone, non-slave server for recovery
following 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),
named (for example) 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. Use
SET GLOBAL relay_log_purge=0
for additional
safety. Then start the server without the
--log-bin
option, Instead, use the
--replicate-same-server-id
,
--relay-log=myhost-bin
(to make the server
believe that these regular binary logs are relay logs) and
--skip-slave-start
options. After the server
starts, issue these statements:
CHANGE MASTER TO RELAY_LOG_FILE='myhost-bin.153', RELAY_LOG_POS=410, MASTER_HOST='some_dummy_string'; START SLAVE SQL_THREAD;
The server reads and executes its own binary logs, thus
achieving crash recovery. Once the recovery is finished, run
STOP SLAVE
, shut down the server, delete
the master.info
and
relay-log.info
files, and restart the
server with its original options.
Specifying the MASTER_HOST
option (even
with a dummy value) is required to make the server think it is
a slave.
LOAD DATA FROM MASTER
This statement 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 starts
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 a user
could use this option to 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 loading tables from the master.
Use of this statement is subject to the following conditions:
It works only for MyISAM
tables.
Attempting to load a non-MyISAM
table
results in the following error:
ERROR 1189 (08S01): Net error reading from master
It acquires a global read lock on the master while taking the snapshot, which prevents updates on the master during the load operation.
If you are loading large tables, you might have to increase
the values of net_read_timeout
and
net_write_timeout
on both the master and
slave servers. See Section 5.2.2, “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.
To use LOAD DATA FROM MASTER
, the
replication account that is used to connect to the master must
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 hides 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.8, “SHOW DATABASES
Syntax”. On
the slave side, the user that issues LOAD DATA FROM
MASTER
must have privileges for dropping and
creating the databases and tables that are copied.
LOAD TABLE tbl_name
FROM MASTER
Transfers a copy of the table from the master to the slave.
This statement is implemented mainly debugging LOAD
DATA FROM MASTER
operations. To use LOAD
TABLE
, the account used for connecting to the master
server must have the RELOAD
and
SUPER
privileges on the master and the
SELECT
privilege for the master table to
load. On the slave side, the user that issues LOAD
TABLE FROM MASTER
must have privileges for dropping
and creating the table.
The conditions for LOAD DATA FROM MASTER
apply here as well. For example, LOAD TABLE FROM
MASTER
works only for MyISAM
tables. The timeout notes for LOAD DATA FROM
MASTER
apply as well.
SELECT MASTER_POS_WAIT('master_log_file
',master_log_pos
)
This is actually 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.9.4, “Miscellaneous Functions”, for a full description.
RESET SLAVE
RESET 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 completely 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.
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N
This statement skips 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.
SHOW SLAVE STATUS
This statement 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 a
semicolon to obtain 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
SHOW SLAVE STATUS
returns the following
fields:
Slave_IO_State
A copy of the State
field of the output
of SHOW PROCESSLIST
for the slave I/O
thread. This tells you what the thread is doing: 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”. It
is necessary to check this field for older versions of
MySQL (prior to 5.0.12) because in these versions the
thread could be running while unsuccessfully trying to
connect to the master; only this field makes 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 can find the error in the
Last_Error
field (described below).
Master_Host
The current master host.
Master_User
The current user used to connect to the master.
Master_Port
The current master port.
Connect_Retry
The current value of the
--master-connect-retry
option.
Master_Log_File
The name of the master binary log file from which the I/O thread is currently reading.
Read_Master_Log_Pos
The position up to which the I/O thread has read in the current master binary log.
Relay_Log_File
The name of the relay log file from which the SQL thread is currently reading and executing.
Relay_Log_Pos
The position up to which the SQL thread has read and executed in the current relay log.
Relay_Master_Log_File
The name of the master binary log file containing the most recent event executed by the SQL thread.
Slave_IO_Running
Whether the I/O thread is started and has connected
successfully to the master. For older versions of MySQL
(prior to 4.1.14 and 5.0.12)
Slave_IO_Running
is
YES
if the I/O thread is started, even
if the slave hasn't connected to the master yet.
Slave_SQL_Running
Whether the SQL thread is started.
Replicate_Do_DB
,
Replicate_Ignore_DB
The lists of databases that were specified with the
--replicate-do-db
and
--replicate-ignore-db
options, if any.
Replicate_Do_Table
,
Replicate_Ignore_Table
,
Replicate_Wild_Do_Table
,
Replicate_Wild_Ignore_Table
The lists of tables that were specified with the
--replicate-do-table
,
--replicate-ignore-table
,
--replicate-wild-do-table
, and
--replicate-wild-ignore_table
options, if
any.
Last_Errno
,
Last_Error
The error number and error message returned by the most
recently executed query. An error number of 0 and message
of the empty string mean “no error.” If the
Last_Error
value is not empty, it also
appears 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
The most recently used value for
SQL_SLAVE_SKIP_COUNTER
.
Exec_Master_Log_Pos
The position of the last event executed by the SQL thread
from the master's binary log
(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
The total combined size of all existing relay logs.
Until_Condition
,
Until_Log_File
,
Until_Log_Pos
The values specified in the 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 stops executing.
Master_SSL_Allowed
,
Master_SSL_CA_File
,
Master_SSL_CA_Path
,
Master_SSL_Cert
,
Master_SSL_Cipher
,
Master_SSL_Key
These fields show the SSL parameters used by the slave to connect to the master, if any.
Master_SSL_Allowed
has these values:
Yes
if an SSL connection to the
master is permitted
No
if an SSL connection to the
master is not permitted
Ignored
if an SSL connection is
permitted but the slave server does not have SSL
support enabled
The values of the other SSL-related fields correspond to
the values of the --master-ca
,
--master-capath
,
--master-cert
,
--master-cipher
, and
--master-key
options.
Seconds_Behind_Master
This field is an indication of how “late” the slave is:
When the slave SQL thread is actively running (processing updates), this field is the number of seconds that have elapsed since the timestamp of the most recent event on the master executed by that thread.
When the SQL thread thread has caught up to the slave I/O thread and goes idle waiting for more events from the I/O thread, this field is zero.
In essence, this field measures the time difference in seconds between the slave SQL thread and the slave I/O thread.
If the network connection between master and slave is
fast, the slave I/O thread is very close to the master, so
this field is a good approximation of how late the slave
SQL thread is compared to the master. If the network is
slow, this is not a good
approximation; the slave SQL thread may quite often be
caught up with the slow-reading slave I/O thread, so
Seconds_Behind_Master
often shows a
value of 0, even if the I/O thread is late compared to the
master. In other words, this column is useful
only for fast networks.
This time difference computation works even though the
master and slave do not 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
is
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 the number of seconds given by the
--master-connect-retry
option before
reconnecting, NULL
is shown, as the
slave cannot know what the master is doing, and so cannot
say reliably how late it is.
This field has one limitation. The timestamp is preserved
through replication, which means that, if a master M1 is
itself a slave of M0, any event from M1's binlog which
originates in replicating an event from M0's binlog has
the timestamp of that event. This enables MySQL to
replicate TIMESTAMP
successfully.
However, the drawback for
Seconds_Behind_Master
is that if M1
also receives direct updates from clients, the value
randomly deviates, because sometimes the last M1's event
is from M0 and sometimes it is the most recent timestamp
from a direct update.
START 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
thread_type
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 do not manage to connect
to the master or read its binary logs, or some other problem).
START SLAVE
does not warn you about this.
You must check the slave's error log for error messages
generated by the slave threads, or check that they are running
satisfactorily with SHOW SLAVE STATUS
.
You can add IO_THREAD
and
SQL_THREAD
options to the statement to name
which of the threads to start.
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
running, the UNTIL
clause is ignored and a
warning is issued.
For 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.
In old versions of MySQL (before 4.0.5), this statement was
called SLAVE START
. This usage is still
accepted in MySQL 5.0 for backward compatibility,
but is deprecated.
STOP SLAVE [thread_type
[,thread_type
] ... ]thread_type
: IO_THREAD | SQL_THREAD
Stops the slave threads. STOP SLAVE
requires the SUPER
privilege.
Like START SLAVE
, this statement may be
used with the IO_THREAD
and
SQL_THREAD
options to name the thread or
threads to be stopped.
In old versions of MySQL (before 4.0.5), this statement was
called SLAVE STOP
. This usage is still
accepted in MySQL 5.0 for backward compatibility,
but is deprecated.
MySQL 5.0 provides support for server-side prepared
statements. This support takes advantage of the efficient
client/server binary protocol implemented in MySQL 4.1, provided
that you use an appropriate client programming interface.
Candidate interfaces include the MySQL C API client library (for C
programs), MySQL Connector/J (for Java programs), and MySQL
Connector/NET. For example, the C API provides a set of function
calls that make up its prepared statement API. See
Section 22.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, one
example being the
mysqli
extension in PHP 5.0.
An alternative SQL interface to prepared statements is available. 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:
You can use it when no programming interface is available to you.
You can use it from any program that allows you to send SQL statements to the server to be executed, such as the mysql client program.
You can use it even if the client is using an old version of the client library. The only requirement is that you be able to connect to a server that is recent enough to support SQL syntax for prepared statements.
SQL syntax for prepared statements is intended to be used for situations such as these:
You want to test how prepared statements work in your application before coding it.
An application has problems executing prepared statements and you want to determine interactively what the problem is.
You want to create a test case that describes a problem you are having with prepared statements, so that you can file a bug report.
You need to use prepared statements but do not have access to a programming API that supports them.
SQL syntax for prepared statements is based on three SQL statements:
PREPARE
stmt_name
FROM
preparable_stmt
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. Parameter markers can be used only where data
values should appear, not for SQL keywords, identifiers, and
so forth.
If a prepared statement with the given name already exists, 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 exists.
The scope of a prepared statement is the client session within which it is created. Other clients cannot see it.
EXECUTE
stmt_name
[USING
@var_name
[,
@var_name
] ...]
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 different variables to it or setting the variables to different values before each execution.
{DEALLOCATE | DROP} PREPARE
stmt_name
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 SQL statements can be used in 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 statement 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 as 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.
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.
SQL syntax for prepared statements cannot be used within stored routines (procedures or functions), or triggers. This restriction is lifted as of MySQL 5.0.13 for stored procedures, but not for stored functions or triggers.
As of MySQL 5.0.7, placeholders can be used for the arguments of
the LIMIT
clause when using prepared
statements. See Section 13.2.7, “SELECT
Syntax”.