Chapter 13. SQL Statement Syntax

Table of Contents

13.1. Data Definition Statements
13.1.1. ALTER DATABASE Syntax
13.1.2. ALTER TABLE Syntax
13.1.3. CREATE DATABASE Syntax
13.1.4. CREATE INDEX Syntax
13.1.5. CREATE TABLE Syntax
13.1.6. DROP DATABASE Syntax
13.1.7. DROP INDEX Syntax
13.1.8. DROP TABLE Syntax
13.1.9. RENAME TABLE Syntax
13.2. Data Manipulation Statements
13.2.1. DELETE Syntax
13.2.2. DO Syntax
13.2.3. HANDLER Syntax
13.2.4. INSERT Syntax
13.2.5. LOAD DATA INFILE Syntax
13.2.6. REPLACE Syntax
13.2.7. SELECT Syntax
13.2.8. Subquery Syntax
13.2.9. TRUNCATE Syntax
13.2.10. UPDATE Syntax
13.3. MySQL Utility Statements
13.3.1. DESCRIBE Syntax (Get Information About Columns)
13.3.2. USE Syntax
13.4. MySQL Transactional and Locking Statements
13.4.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax
13.4.2. Statements That Cannot Be Rolled Back
13.4.3. Statements That Cause an Implicit Commit
13.4.4. SAVEPOINT and ROLLBACK TO SAVEPOINT Syntax
13.4.5. LOCK TABLES and UNLOCK TABLES Syntax
13.4.6. SET TRANSACTION Syntax
13.4.7. XA Transactions
13.5. Database Administration Statements
13.5.1. Account Management Statements
13.5.2. Table Maintenance Statements
13.5.3. SET Syntax
13.5.4. SHOW Syntax
13.5.5. Other Administrative Statements
13.6. Replication Statements
13.6.1. SQL Statements for Controlling Master Servers
13.6.2. SQL Statements for Controlling Slave Servers
13.7. SQL Syntax for Prepared Statements

This chapter describes the syntax for SQL statements.

13.1. Data Definition Statements

13.1.1. ALTER DATABASE Syntax

ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification [, alter_specification] ...

alter_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name

ALTER DATABASE allows you to change the overall characteristics of a database. These characteristics are stored in the db.opt file in the database directory. To use ALTER DATABASE, you need the ALTER privilege on the database.

The CHARACTER SET clause changes the default database character set. The COLLATE clause changes the default database collation. Character set and collation names are discussed in Chapter 10, Character Set Support.

The database name can be omitted, in which case, the statement applies to the default database. ALTER SCHEMA can be used as of MySQL 5.0.2.

13.1.2. ALTER TABLE Syntax

ALTER [IGNORE] TABLE tbl_name
    alter_specification [, alter_specification] ...

alter_specification:
    ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
  | ADD [COLUMN] (column_definition,...)
  | ADD INDEX [index_name] [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        PRIMARY KEY [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [index_name] [index_type] (index_col_name,...)
  | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        [reference_definition]
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP INDEX index_name
  | DROP FOREIGN KEY fk_symbol
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO] new_tbl_name
  | ORDER BY col_name
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
  | DISCARD TABLESPACE
  | IMPORT TABLESPACE
  | table_options

ALTER TABLE allows you to change the structure of an existing table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. You can also change the comment for the table and type of the table.

The syntax for many of the allowable alterations is similar to clauses of the CREATE TABLE statement. This includes table_options modifications, for options such as ENGINE, AUTO_INCREMENT, and AVG_ROW_LENGTH. See Section 13.1.5, “CREATE TABLE Syntax”.

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.22, “SHOW WARNINGS Syntax”.

If you use ALTER TABLE to change a column specification but DESCRIBE tbl_name indicates that your column was not changed, it is possible that MySQL ignored your modification for one of the reasons described in Section 13.1.5.1, “Silent Column Specification Changes”. For example, if you try to change a VARCHAR column to CHAR, MySQL still uses VARCHAR if the table contains other variable-length columns.

ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, then the original table is deleted and the new one is renamed. While ALTER TABLE is executing, the original table is readable by other clients. Updates and writes to the table are stalled until the new table is ready, then are automatically redirected to the new table without any failed updates.

Note that if you use any other option to ALTER TABLE than RENAME, MySQL always creates a temporary table, even if the data wouldn't strictly need to be copied (such as when you change the name of a column). 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, then for rows with duplicates on a unique key, only the first row is used. The others conflicting rows are deleted. Wrong 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:

    mysql> ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
    
  • CHANGE col_name, DROP col_name, and DROP INDEX are MySQL extensions to standard SQL.

  • MODIFY is an Oracle extension to ALTER TABLE.

  • The word COLUMN is purely optional and can be omitted.

  • If you use ALTER TABLE tbl_name RENAME TO new_tbl_name without any other options, MySQL simply renames any files that correspond to the table tbl_name. There is no need to create a temporary table. (You can also use the RENAME TABLE statement to rename tables. See Section 13.1.9, “RENAME TABLE Syntax”.)

  • 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 the column type. See Section 13.1.5, “CREATE TABLE Syntax”.

  • You can rename a column using a CHANGE old_col_name column_definition clause. To do so, specify the old and new column names and the type that the column currently has. For example, to rename an INTEGER column from a to b, you can do this:

    mysql> ALTER TABLE t1 CHANGE a b INTEGER;
    

    If you want to change a column's type but not the name, CHANGE syntax still requires an old and new column name, even if they are the same. For example:

    mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
    

    You can also use MODIFY to change a column's type without renaming it:

    mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
    
  • If you use CHANGE or MODIFY to shorten a column for which an index exists on the column, and the resulting column length is less than the index length, MySQL shortens the index automatically.

  • When you change a column type using CHANGE or MODIFY, MySQL tries to convert existing column values to the new type as well as possible.

  • You can use FIRST or AFTER col_name to add a column at a specific position within a table row. The default is to add the column last. You can also use FIRST and AFTER in CHANGE or MODIFY operations.

  • ALTER COLUMN specifies a new default value for a column or removes the old default value. If the old default is removed and the column can be NULL, the new default is NULL. If the column cannot be NULL, MySQL assigns a default value, as described in Section 13.1.5, “CREATE TABLE Syntax”.

  • 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, then 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 will give rise 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 allows 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 mainly useful when you know that you are mostly going to query the rows in a certain order; by using this option after big changes to the table, you might be able to get higher performance. In some cases, it might make sorting easier for MySQL if the table is in order by the column that you want to order it by later.

  • 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 [symbol]] FOREIGN KEY (...) REFERENCES ... (...). See Section 14.2.6.4, “FOREIGN KEY Constraints”. For other storage engines, the clauses are parsed but ignored. The CHECK clause is parsed but ignored by all storage engines. See Section 13.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.8.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 TABLE yourtablename DROP FOREIGN KEY fk_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”.

  • ALTER TABLE ignores the DATA DIRECTORY and INDEX DIRECTORY table options.

  • 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 TABLE tbl_name CONVERT TO CHARACTER SET charset_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 TABLE tbl_name DEFAULT CHARACTER SET charset_name;
    

    The word DEFAULT is optional. The default character set is the character set that is used if you do not specify the character set for a new column which you add to a table (for example, with ALTER TABLE ... ADD column).

    Warning: ALTER TABLE ... DEFAULT CHARACTER SET and ALTER TABLE ... CHARACTER SET are equivalent and change only the default table character set.

  • 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, then issue this statement:

    ALTER TABLE tbl_name IMPORT TABLESPACE;
    

    See Section 14.2.6.6, “Using Per-Table Tablespaces”.

  • With the mysql_info() C API function, you can find out how many records were copied, and (when IGNORE is used) how many records were deleted due to duplication of unique key values. See Section 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:

mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));

To rename the table from t1 to t2:

mysql> ALTER TABLE t1 RENAME t2;

To change column a from INTEGER to TINYINT NOT NULL (leaving the name the same), and to change column b from CHAR(10) to CHAR(20) as well as renaming it from b to c:

mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

To add a new TIMESTAMP column named d:

mysql> ALTER TABLE t2 ADD d TIMESTAMP;

To add indexes on column d and on column a:

mysql> ALTER TABLE t2 ADD INDEX (d), ADD INDEX (a);

To remove column c:

mysql> ALTER TABLE t2 DROP COLUMN c;

To add a new AUTO_INCREMENT integer column named c:

mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->     ADD PRIMARY KEY (c);

Note that we indexed c (as a PRIMARY KEY), because AUTO_INCREMENT columns must be indexed, and also that we declare c as NOT NULL, because primary key columns cannot be NULL.

When you add an AUTO_INCREMENT column, column values are filled in with sequence numbers for you automatically. For MyISAM tables, you can set the first sequence number by executing SET INSERT_ID=value before ALTER TABLE or by using the AUTO_INCREMENT=value table option. See Section 13.5.3, “SET Syntax”.

From MySQL 5.0.3, you can use the ALTER TABLE ... AUTO_INCREMENT=value table option for InnoDB tables to set the sequence number for new rows if the value is greater than the maximum value in the AUTO_INCREMENT column. If the value is less than the 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.

See Section A.7.1, “Problems with ALTER TABLE.

13.1.3. CREATE DATABASE Syntax

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification [, create_specification] ...]

create_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name

CREATE DATABASE creates a database with the given name. To use CREATE DATABASE, you need the CREATE privilege on the database.

Rules for allowable database names are given in Section 9.2, “Database, Table, Index, Column, and Alias Names”. An error occurs if the database 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. Character set and collation names are discussed in Chapter 10, Character Set Support.

Databases in MySQL are implemented as directories containing files that correspond to tables in the database. Because there are no tables in a database when it is initially created, the CREATE DATABASE statement 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.

CREATE SCHEMA can be used as of MySQL 5.0.2.

You can also use the mysqladmin program to create databases. See Section 8.5, “mysqladmin — Client for Administering a MySQL Server”.

13.1.4. CREATE INDEX Syntax

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [USING index_type]
    ON tbl_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”.

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 allows you to add indexes to existing tables.

A column list of the form (col1,col2,...) creates a multiple-column index. Index values are formed by concatenating the values of the given columns.

For CHAR and VARCHAR columns, indexes can be created that use only part of a column, using col_name(length) syntax to index a prefix consisting of the first length characters of each column value. BLOB and TEXT columns also can be indexed, but a prefix length must be given.

The statement shown here creates an index using the first 10 characters of the name column:

CREATE INDEX part_of_name ON customer (name(10));

Because most names usually differ in the first 10 characters, this index should not be much slower than an index created from the entire name column. Also, using partial columns for indexes can make the index file much smaller, which could save a lot of disk space and might also speed up INSERT operations.

Prefixes can be up to 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. 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, or BDB table type.

  • You can add an index on a BLOB or TEXT column only if you are using the MyISAM, BDB, or InnoDB table type.

An index_col_name specification can end with ASC or DESC. These keywords are allowed for future extensions for specifying ascending or descending index value storage. Currently they are parsed but ignored; index values are always stored in ascending order.

Some storage engines allow you to specify an index type when creating an index. The syntax for the index_type specifier is USING type_name. The allowable type_name values supported by different storage engines are shown in the following table. Where multiple index types are listed, the first one is the default when no index_type specifier is given.

Storage EngineAllowable Index Types
MyISAMBTREE
InnoDBBTREE
MEMORY/HEAPHASH, BTREE

Example:

CREATE TABLE lookup (id INT) ENGINE = MEMORY;
CREATE INDEX id_index USING BTREE ON lookup (id);

TYPE type_name can be used as a synonym for USING type_name to specify an index type. However, USING is the preferred form. In addition, the index name that precedes the index type in the index specification syntax is not optional with TYPE. This is because, unlike USING, TYPE is not a reserved word and thus is interpreted as an index name.

If you specify an index type that is not legal for a 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.

For more information about how MySQL uses indexes, see Section 7.4.5, “How MySQL Uses Indexes”.

FULLTEXT indexes can index only CHAR, VARCHAR, and TEXT columns, and only in MyISAM tables. See Section 12.7, “Full-Text Search Functions”.

SPATIAL indexes can index only spatial columns, and only in MyISAM tables. Spatial column types are described in Chapter 16, Spatial Extensions in MySQL.

13.1.5. CREATE TABLE Syntax

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options] [select_statement]

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(] LIKE old_tbl_name [)];

create_definition:
    column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
  | KEY [index_name] [index_type] (index_col_name,...)
  | INDEX [index_name] [index_type] (index_col_name,...)
  | [CONSTRAINT [symbol]] UNIQUE [INDEX]
        [index_name] [index_type] (index_col_name,...)
  | [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,...)
  | [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (index_col_name,...) [reference_definition]
  | CHECK (expr)

column_definition:
    col_name type [NOT NULL | NULL] [DEFAULT default_value]
        [AUTO_INCREMENT] [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
  | CHAR(length) [BINARY | ASCII | UNICODE]
  | VARCHAR(length) [BINARY]
  | 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:
    REFERENCES tbl_name [(index_col_name,...)]
               [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
               [ON DELETE reference_option]
               [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

table_options: table_option [table_option] ...

table_option:
    {ENGINE|TYPE} = engine_name
  | AUTO_INCREMENT = value
  | AVG_ROW_LENGTH = value
  | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_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 current database. An error occurs if the table exists, if there is no current 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 whether or not there is a current database. If you use quoted identifiers, quote the database and table names separately. For example, `mydb`.`mytbl` is legal, but `mydb.mytbl` is not.

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.) You must have the CREATE TEMPORARY TABLES privilege to be able to create temporary tables.

The keywords IF NOT EXISTS prevent an error from occurring if the table exists. Note that 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 records selected by the SELECT part are inserted whether or not 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:

FilePurpose
tbl_name.frmTable format (definition) file
tbl_name.MYDData file
tbl_name.MYIIndex file

The files created by each storage engine to represent tables are described in Chapter 14, Storage Engines and Table Types.

For general information on the properties of the various column types, see Chapter 11, Column Types. For information about spatial column types, see Chapter 16, Spatial Extensions in MySQL.

  • 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 value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1. Such a column must be defined as one of the integer types as described in Section 11.1.1, “Overview of Numeric Types”. (The value 1.0 is not an integer.) See Section 22.2.3.36, “mysql_insert_id().

    Specifying the NO_AUTO_VALUE_ON_ZERO flag for the --sql-mode server option or the sql_mode system variable allows you to store 0 in AUTO_INCREMENT columns as 0 without generating a new sequence value. See Section 5.3.1, “mysqld Command-Line Options”.

    Note: There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. 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 * FROM tbl_name WHERE auto_col IS NULL
    
  • Character column definitions can include a CHARACTER SET attribute to specify the character set and, optionally, a collation for the column. For details, see Chapter 10, Character Set Support. CHARSET is a synonym for CHARACTER SET.

    CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);
    

    MySQL 5.0 interprets length specifications in character column definitions in characters. (Some earlier versions 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”.

    Prior to MySQL 5.0.2, if a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:

    If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause.

    If the column cannot take NULL as the value, MySQL defines the column with an explicit DEFAULT clause, using the implicit default value for the column data type. Implicit defaults are defined as follows:

    • For numeric types other than those declared with the AUTO_INCREMENT attribute, the default is 0. For an AUTO_INCREMENT column, the default value is the next value in the sequence.

    • For date and time types other than TIMESTAMP, the default is the appropriate “zero” value for the type. For the first TIMESTAMP column in a table, the default value is the current date and time. See Section 11.3, “Date and Time Types”.

    • For string types other than ENUM, the default value is the empty string. For ENUM, the default is the first enumeration value.

    BLOB and TEXT columns cannot be assigned a default value.

    As of MySQL 5.0.2, if a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:

    If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. This is the same as before 5.0.2.

    If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause. For data entry, if an INSERT or REPLACE statement includes no value for the column, MySQL handles the column according to the SQL mode in effect at the time:

    • If strict mode is not enabled, MySQL sets the column to the implicit default value for the column data type.

    • If strict mode is enabled, an error occurs for transactional tables and the statement is rolled back. For non-transactional tables, an error occurs, but if this happens for the the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.

    Suppose that a table t is defined as follows:

    CREATE TABLE t (i INT NOT NULL);
    

    In this case, i has no explicit default, so in strict mode each of the following statements produce an error and no row is inserted. When not using strict mode, only the third statement produces an error; the implicit default is inserted for the first two statements, but the third fails because DEFAULT(i) cannot produce a value:

    INSERT INTO t VALUES();
    INSERT INTO t VALUES(DEFAULT);
    INSERT INTO t VALUES(DEFAULT(i));
    

    See Section 5.3.2, “The Server SQL Mode”.

    For a given table, you can use the SHOW CREATE TABLE statement to see which columns have an explicit DEFAULT clause.

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

  • The attribute SERIAL can be used as an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

  • 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 is one in which all values in the index must be distinct. An error occurs if you try to add a new row with a key that matches an existing row. The exception to this is that if a column in the index is allowed to contain NULL values, it can contain multiple NULL values. This exception does not apply to BDB tables, for which an indexed column allows only a single NULL.

  • A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL 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 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(index_col_name, ...) clause.

  • 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 tbl_name. See Section 13.5.4.11, “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 col_name(length) syntax in an index specification, you can create an index that uses only the first length characters of a CHAR or VARCHAR column. Indexing only a prefix of column values like this can make the index file much smaller. See Section 7.4.3, “Column Indexes”.

    The MyISAM and InnoDB storage engines also support indexing on BLOB and TEXT columns. When indexing a BLOB or TEXT column, you must specify a prefix length for the index. For example:

    CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
    

    Prefixes can be up to 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. Be sure to 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 table type supports FULLTEXT indexes. They can be created only from CHAR, VARCHAR, and TEXT columns. Indexing always happens over the entire column; partial indexing is not supported and any prefix length is ignored if specified. See Section 12.7, “Full-Text Search Functions” for details of operation.

  • You can create SPATIAL indexes on spatial column types. Spatial types are supported only for MyISAM tables and indexed columns must be declared as NOT NULL. See Chapter 16, Spatial Extensions in MySQL.

  • 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 the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements, but without further action being taken. The CHECK clause is parsed but ignored by all storage engines. See Section 1.8.5.5, “Foreign Keys”.

  • For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte. The maximum record length in bytes can be calculated as follows:

    row length = 1
                 + (sum of column lengths)
                 + (number of NULL columns + delete_flag + 7)/8
                 + (number of variable-length columns)
    

    delete_flag is 1 for tables with static record format. Static tables use a bit in the row record for a flag that indicates whether the row has been deleted. delete_flag is 0 for dynamic tables because the flag is stored in the dynamic row header.

    These calculations do not apply for InnoDB tables, for which storage size is no different for NULL columns than for NOT NULL columns.

The ENGINE and TYPE options specify the storage engine for the table. ENGINE is the preferred option name.

The ENGINE and TYPE options take the following values:

Storage EngineDescription
ARCHIVEThe archiving storage engine. See Section 14.8, “The ARCHIVE Storage Engine”.
BDBTransaction-safe tables with page locking. Also known as BerkeleyDB. See Section 14.5, “The BDB (BerkeleyDB) Storage Engine”.
CSVTables that store rows in comma-separated values format. See Section 14.9, “The CSV Storage Engine”.
EXAMPLEAn example engine. See Section 14.6, “The EXAMPLE Storage Engine”.
FEDERATEDStorage engine that accesses remote tables. See Section 14.7, “The FEDERATED Storage Engine”.
HEAPSee Section 14.4, “The MEMORY (HEAP) Storage Engine”.
(OBSOLETE) ISAMNot 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. See Chapter 14, Storage Engines and Table Types.
InnoDBTransaction-safe tables with row locking and foreign keys. See Section 14.2, “The InnoDB Storage Engine”.
MEMORYThe data for this table type is stored only in memory. (Known in earlier MySQL versions as HEAP.)
MERGEA collection of MyISAM tables used as one table. Also known as MRG_MyISAM. See Section 14.3, “The MERGE Storage Engine”.
MyISAMThe binary portable storage engine that is the default storage engine used by MySQL. See Section 14.1, “The MyISAM Storage Engine”.
NDBCLUSTERClustered, fault-tolerant, memory-based tables. Also known as NDB. See Chapter 15, MySQL Cluster.

For more information about MySQL's storage engines, see Chapter 14, Storage Engines and Table Types.

If a storage engine is specified that is not available, MySQL uses MyISAM instead. For example, if a table definition includes the ENGINE=BDB option but the MySQL server does not support BDB tables, the table is created as a MyISAM table. This makes it possible to have a replication setup where you have transactional tables on the master but tables created on the slave are non-transactional (to get more speed). In MySQL 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 work for 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 tbl_name AUTO_INCREMENT = n to reset the 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 records.

    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 operating system 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.3.3, “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 may 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 this.

    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

    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 (MyISAM only).

    If you do not use PACK_KEYS, the default is to pack only 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 big 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 not do anything 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 (MyISAM only).

  • ROW_FORMAT

    Defines how the rows should be stored. Currently this option works only with MyISAM tables. The option value can FIXED or DYNAMIC for static or variable-length row format. myisampack sets the type to COMPRESSED. See Section 14.1.3, “MyISAM Table Storage Formats”.

    Starting with MySQL/InnoDB-5.0.3, InnoDB records are stored in 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 use a collection of identical 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='directory' or INDEX DIRECTORY='directory' you can specify where the MyISAM storage engine should put a table's data file and index file. Note that the directory should be a full path to the directory (not a relative path).

    These options work only 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 TABLE new_tbl SELECT * FROM orig_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 column types might occur. For example, the AUTO_INCREMENT attribute is not preserved, and VARCHAR columns can become CHAR columns.

When creating a table with CREATE ... SELECT, make sure to alias any function calls or expressions in the query. If you do not, the CREATE statement might fail or result in undesirable column names.

CREATE TABLE artists_and_works
SELECT artist.name, COUNT(work.artist_id) AS number_of_works
FROM artist LEFT JOIN work ON artist.id = work.artist_id
GROUP BY artist.id;

You can also explicitly specify the 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 TABLE new_tbl LIKE orig_tbl;

CREATE TABLE ... LIKE does not copy any DATA DIRECTORY or INDEX DIRECTORY table options that were specified for the original table, or any foreign key definitions.

You can precede the SELECT by IGNORE or REPLACE to indicate how to handle records that duplicate unique key values. With IGNORE, new records that duplicate an existing record on a unique key value are discarded. With REPLACE, new records replace records that have the same unique key value. If neither IGNORE nor REPLACE is specified, duplicate unique key values result in an error.

To ensure that the update log/binary log can be used to re-create the original tables, MySQL does not allow concurrent inserts during CREATE TABLE ... SELECT.

13.1.5.1. Silent Column Specification Changes

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.

  • Previous to 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(M,D), if 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 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 column types used by other SQL database vendors to MySQL types. See Section 11.7, “Using Column 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 column type other than the one you specified, issue a DESCRIBE or SHOW CREATE TABLE statement after creating or altering the table.

Certain other column type changes can occur if you compress a table using myisampack. See Section 14.1.3.3, “Compressed Table Characteristics”.

13.1.6. DROP DATABASE Syntax

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.

IF EXISTS is used to prevent an error from occurring if the database does not exist.

DROP SCHEMA can be used as of MySQL 5.0.2.

If you use DROP DATABASE on a symbolically linked database, both the link and the original database are deleted.

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 Version 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.5, “mysqladmin — Client for Administering a MySQL Server”.

13.1.7. DROP INDEX Syntax

DROP INDEX index_name ON tbl_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”.

13.1.8. DROP 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.22, “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.

13.1.9. RENAME TABLE Syntax

RENAME TABLE tbl_name TO new_tbl_name
    [, tbl_name2 TO new_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:

CREATE TABLE new_table (...);
RENAME TABLE old_table TO backup_table, new_table TO old_table;

If the statement renames more than one table, renaming operations are done from left to right. If you want to swap two table names, you can do so like this (assuming that no table named tmp_table already exists):

RENAME TABLE old_table TO tmp_table,
             new_table TO old_table,
             tmp_table TO new_table;

As long as two databases are on the same filesystem you can also rename a table to move it from one database to another:

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

When you execute RENAME, you 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.

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.

13.2. Data Manipulation Statements

13.2.1. DELETE Syntax

Single-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*] ...]
    FROM table_references
    [WHERE where_definition]

Or:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*] ...]
    USING table_references
    [WHERE where_definition]

DELETE deletes rows from tbl_name that satisfy the condition given by where_definition, and returns the number of records deleted.

If you issue a DELETE statement with no WHERE clause, all rows are deleted. A faster way to do this, when you 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 for a BDB table, but not for a MyISAM or InnoDB table. If you delete all rows in the table with DELETE FROM tbl_name (without a WHERE clause) in AUTOCOMMIT mode, the sequence starts over for all table types except InnoDB and MyISAM. There are some exceptions to this behavior for InnoDB tables, as discussed in Section 14.2.6.3, “How an AUTO_INCREMENT Column Works in InnoDB.

For MyISAM and BDB tables, you can specify an AUTO_INCREMENT secondary column in a multiple-column key. In this case, reuse of values deleted from the top of the sequence occurs even for MyISAM tables. See Section 3.6.9, “Using AUTO_INCREMENT.

The DELETE statement supports the following modifiers:

  • If you specify LOW_PRIORITY, execution of the DELETE is delayed 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 this 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 records are maintained in a linked list and subsequent INSERT operations reuse old record positions. To reclaim unused space and reduce file sizes, use the OPTIMIZE TABLE statement or the myisamchk utility to reorganize tables. OPTIMIZE TABLE is easier, but myisamchk is faster. See Section 13.5.2.5, “OPTIMIZE TABLE Syntax” and Chapter 7, Optimization.

The QUICK modifier affects whether index leaves are merged for delete operations. DELETE QUICK is most useful for applications where index values for deleted rows 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 underfilled 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:

  1. Create a table that contains an indexed AUTO_INCREMENT column.

  2. Insert many records into the table. Each insert results in an index value that is added to the high end of the index.

  3. Delete a block of records at the low end of the column range using DELETE QUICK.

In this scenario, the index blocks associated with the deleted index values become underfilled but are not merged with other index blocks due to the use of QUICK. They remain underfilled when new inserts occur, because new records does not have index values in the deleted range. Furthermore, they remain underfilled even if you later use DELETE without QUICK, unless some of the deleted index values happen to lie in index blocks within or adjacent to the underfilled blocks. To reclaim unused index space under these circumstances, use OPTIMIZE TABLE.

If you are going to delete many rows from a table, it might be faster to use DELETE QUICK followed by OPTIMIZE TABLE. This rebuilds the index rather than performing many index block merge operations.

The MySQL-specific LIMIT row_count option to DELETE tells the server the maximum number of rows to be deleted before control is returned to the client. This can be used to ensure that a specific DELETE statement 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 a particular condition in multiple tables. However, you cannot use ORDER BY or LIMIT in a multiple-table DELETE.

The table_references part lists the tables involved in the join. Its syntax is described in Section 13.2.7.1, “JOIN Syntax”.

For the first syntax, only matching rows from the tables listed before the FROM clause are deleted. For the second syntax, only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted. The effect is that you can delete rows from many tables at the same time and have additional tables that are used for searching:

DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

Or:

DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

These statements use all three tables when searching for rows to delete, but delete matching rows only from tables t1 and t2.

The examples above show inner joins using the comma operator, but multiple-table DELETE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN.

The syntax allows .* after the table names for compatibility with Access.

If you use a multiple-table DELETE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, 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: You must use the alias (if one was given) when referring to a table name:

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.

13.2.2. DO Syntax

DO expr [, expr] ...

DO executes the expressions but does not return any results. This is shorthand for SELECT expr, ..., but has the advantage that it is slightly faster when you do not care about the result.

DO is useful mainly with functions that have side effects, such as RELEASE_LOCK().

13.2.3. HANDLER Syntax

HANDLER tbl_name OPEN [ AS alias ]
HANDLER tbl_name READ index_name { = | >= | <= | < } (value1,value2,...)
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST }
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ { FIRST | NEXT }
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name CLOSE

The HANDLER statement provides direct access to table storage engine interfaces. It is available for MyISAM 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 table with other HANDLER statements must use the alias rather than the table name.

The first HANDLER ... READ syntax fetches a row where the index specified satisfies the given values and the WHERE condition is met. If you have a multiple-column index, specify the index column values as a comma-separated list. Either specify values for all the columns in the index, or specify values for a leftmost prefix of the index columns. Suppose that an index includes three columns named col_a, col_b, and col_c, in that order. The HANDLER statement can specify values for all three columns in the index, or for the columns in a leftmost prefix. For example:

HANDLER ... index_name = (col_a_val,col_b_val,col_c_val) ...
HANDLER ... index_name = (col_a_val,col_b_val) ...
HANDLER ... index_name = (col_a_val) ...

The second HANDLER ... READ syntax fetches a row from the table in index order that matches 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 tbl_name READ index_name when a full table scan is desired. Natural row order is the order in which rows are stored in a MyISAM table data file. This statement works for InnoDB tables as well, but there is no such concept because there is no separate data file.

Without a LIMIT clause, all forms of HANDLER ... READ fetch a single row if one is available. To return a specific number of rows, include a LIMIT clause. It has the same syntax as for the SELECT statement. See Section 13.2.7, “SELECT Syntax”.

HANDLER ... CLOSE closes a table that was opened with HANDLER ... OPEN.

Note: To employ the HANDLER interface to refer to a table's PRIMARY KEY, use the quoted identifier `PRIMARY`:

HANDLER tbl_name READ `PRIMARY` > (...);

HANDLER is a somewhat low-level statement. For example, it does not provide consistency. That is, HANDLER ... OPEN does not take a snapshot of the table, and does not lock the table. This means that after a HANDLER ... OPEN statement is issued, table data can be modified (by this or any other thread) and these modifications might appear only partially in HANDLER ... NEXT or HANDLER ... PREV scans.

There are several reasons to use the HANDLER interface instead of normal SELECT statements:

  • HANDLER is faster than SELECT:

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

  • HANDLER makes it much easier to port applications that use an ISAM-like interface to MySQL.

  • HANDLER allows you to traverse a database in a manner that is 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.

13.2.4. INSERT Syntax

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    VALUES ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

Or:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_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”.

tbl_name is the table into which rows should be inserted. The columns for which the statement provides values can be specified as follows:

  • The column name list or the SET clause indicates the columns explicitly.

  • If you do not specify the column list for INSERT ... VALUES or INSERT ... SELECT, values for every column in the table must be provided in the VALUES list or by the SELECT. If you do not know the order of the columns in the table, use DESCRIBE tbl_name to find out.

Column values can be given in several ways:

  • If you are not running in strict 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 doesn't name all the columns in the table, unnamed columns are set to their default values. Default value assignment is described in Section 13.1.5, “CREATE TABLE Syntax”. See also Section 1.8.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.3.2, “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 allows you to avoid writing an incomplete VALUES list that does not include a value for each column in the table. Otherwise, you would have to write out the list of column names corresponding to each value in the VALUES list.

    You can also use DEFAULT(col_name) as a more general form that can be used in expressions for producing a column's default value.

  • If both the column list and the VALUES list are empty, INSERT creates a row with each column set to its default value:

    mysql> INSERT INTO tbl_name () VALUES();
    

    In STRICT mode you will get an error if a column doesn't have a default value. Otherwise, MySQL will use the implicit default value for any column without 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 column 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:

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

    mysql> INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);
    

    One exception involves columns that contain AUTO_INCREMENT values. Because the AUTO_INCREMENT value is generated after other value assignments, any reference to an AUTO_INCREMENT column in the assignment returns a 0.

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. 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 if 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”.

  • 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. See Section 13.2.4.2, “INSERT DELAYED Syntax”.) Note that LOW_PRIORITY should normally not be used with MyISAM tables because doing so disables concurrent inserts. See Section 14.1, “The MyISAM Storage Engine”.

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

  • 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 the IGNORE keyword in an INSERT statement, errors that occur while executing the statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is still 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. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have identical effect:

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)
    -> ON DUPLICATE KEY UPDATE c=c+1;

mysql> UPDATE table SET c=c+1 WHERE a=1;

The rows-affected value is 1 if the row is inserted as a new record and 2 if an existing record is updated.

Note: If column b is also unique, the INSERT would be equivalent to this UPDATE statement instead:

mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

If a=1 OR b=2 matches several rows, only one row is updated. In general, you should try to avoid using an ON DUPLICATE KEY clause on tables with multiple unique keys.

You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT ... UPDATE statement. In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, 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:

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
    -> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

That statement is identical to the following two statements:

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)
    -> ON DUPLICATE KEY UPDATE c=3;
mysql> INSERT INTO table (a,b,c) VALUES (4,5,6)
    -> ON DUPLICATE KEY UPDATE c=9;

When you use ON DUPLICATE KEY UPDATE, the DELAYED option is ignored.

You can find the value used for an AUTO_INCREMENT column by using the 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().

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, since 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 column 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().

13.2.4.1. INSERT ... SELECT Syntax

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_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 an INSERT ... SELECT statement:

  • Specify IGNORE explicitly to ignore records that would cause duplicate-key violations.

  • Do not use DELAYED 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 during INSERT ... SELECT.

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

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.

13.2.4.2. INSERT DELAYED Syntax

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 wait for the INSERT to complete. This is a common problem when you use MySQL for logging and you also periodically run SELECT and UPDATE statements that take a long time to complete.

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.

There are some constraints on the use of DELAYED:

  • INSERT DELAYED works only with MyISAM, MEMORY, and ARCHIVE tables. For MyISAM tables, if there are no free blocks in the middle of the data file, concurrent SELECT and INSERT statements are supported. Under these circumstances, you very seldom need to use INSERT DELAYED with MyISAM. See Section 14.1, “The MyISAM Storage Engine”, Section 14.4, “The MEMORY (HEAP) Storage Engine”, and Section 14.8, “The ARCHIVE Storage Engine”.

  • INSERT DELAYED should be used only for INSERT statements that specify value lists. The server ignores DELAYED for INSERT DELAYED ... SELECT statements.

  • The server ignores DELAYED for INSERT DELAYED ... ON DUPLICATE UPDATE statements.

  • Because the statement returns immediately before the rows are inserted, you cannot use LAST_INSERT_ID() to get the AUTO_INCREMENT value which 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.

Note that currently the queued rows are held only in memory until they are inserted into the table. This means that if you terminate mysqld forcibly (for example, with kill -9) or if mysqld dies unexpectedly, any queued rows that have not been written to disk are lost.

The following describes in detail what happens when you use the DELAYED option to INSERT or REPLACE. In this description, the “thread” is the thread that received an INSERT DELAYED statement and “handler” is the thread that handles all INSERT DELAYED statements for a particular table.

  • 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 previously 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 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 records 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 thread_id. However, before exiting, it first stores all queued rows into the table. During this time it does not accept any new 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 thread_id), or someone executes a FLUSH TABLES.

  • The following status variables provide information about INSERT DELAYED statements:

    Status VariableMeaning
    Delayed_insert_threadsNumber of handler threads
    Delayed_writesNumber of rows written with INSERT DELAYED
    Not_flushed_delayed_rowsNumber 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.

Note that INSERT DELAYED is slower than a normal INSERT if the table is not in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows. This means that you should use INSERT DELAYED only when you are really sure that you need it.

13.2.5. LOAD DATA INFILE Syntax

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char' ]
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_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.

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's currently not possible to load UCS2 data files.

You can also load data files by using the mysqlimport utility; it operates by sending a LOAD DATA INFILE statement to the server. The --local option causes mysqlimport to read data files from the client host. You can specify the --compress option to get better performance over slow networks if the client and server support the compressed protocol. See Section 8.10, “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), then other threads can retrieve data from the table while LOAD DATA is executing. Using this option affects the performance of LOAD DATA a bit, even if no other thread is using the table at the same time.

If LOCAL is specified, it 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.

When locating files on the server host, the server uses the following rules:

  • If an absolute pathname is given, the server uses the pathname as is.

  • If a relative pathname with one or more leading components is given, 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 these rules mean that a file named as ./myfile.txt is read from the server's data directory, whereas the same file named as myfile.txt is read from the database directory of the default database. For example, the following LOAD DATA statement reads the file data.txt from the database directory for db1 because db1 is the current database, even though the statement explicitly loads the file into a table in the db2 database:

mysql> USE db1;
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

Note that 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.7.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, then LOCAL does not work. See Section 5.6.4, “Security Issues with LOAD DATA LOCAL.

If you need LOAD DATA to read from a pipe, you can use the following technique (here we load the listing of the / directory into a table):

mkfifo /mysql/db/x/x
chmod 666 /mysql/db/x/x
find / -ls > /mysql/db/x/x
mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x

The REPLACE and IGNORE keywords control handling of input records that duplicate existing records on unique key values.

If you specify REPLACE, input rows replace existing rows (in other words, rows that have the same value for a primary or unique index as an existing row). See Section 13.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 or not the LOCAL keyword is specified. Without LOCAL, an error occurs when a duplicate key value is found, and the rest of the text file is ignored. With LOCAL, the default behavior is the same as if IGNORE is specified; this is because the server has no way to stop transmission of the file in the middle of the operation.

If you want to ignore foreign key constraints during the load operation, you can issue a SET FOREIGN_KEY_CHECKS=0 statement before executing LOAD DATA.

If you use LOAD DATA INFILE on an empty MyISAM table, all non-unique indexes are created in a separate batch (as for REPAIR TABLE). This normally makes LOAD DATA INFILE much faster when you have many indexes. Normally this is very fast, but in some extreme cases, you can create the indexes even faster by turning them off with ALTER TABLE ... DISABLE KEYS before loading the file into the table and using ALTER TABLE ... ENABLE KEYS to re-create the indexes after loading the file. See Section 7.2.16, “Speed of INSERT Statements”.

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.

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 do not specify a FIELDS clause, the defaults are the same as if you had written this:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

If you do not specify a LINES clause, the default is the same as if you had written this:

LINES TERMINATED BY '\n' STARTING BY ''

In other words, the defaults cause LOAD DATA INFILE to act as follows when reading input:

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

Note that to write FIELDS ESCAPED BY '\\', you must specify two backslashes for the value to be read as a single backslash.

Note: If you have generated the text file on a Windows system, you might have to use LINES TERMINATED BY '\r\n' to read the file properly, because Windows programs typically use two characters as a line terminator. Some programs, such as WordPad, might use \r as a line terminator when writing files. To read such files, use LINES TERMINATED BY '\r'.

If all the lines you want to read in have a common prefix that you want to ignore, you can use LINES STARTING BY 'prefix_string' to skip over the prefix (and anything before it). If a line does not include the prefix, the entire line is skipped. Note: prefix_string may occur in the middle of a line.

Example:

mysql> LOAD DATA INFILE '/tmp/test.txt'
    -> INTO TABLE test LINES STARTING BY "xxx";

With this you can read in a file that contains something like:

xxx"row",1
something xxx"row",2

And just get the data ("row",1) and ("row",2).

The IGNORE number LINES option can be used to ignore lines at the start of the file. For example, you can use IGNORE 1 LINES to skip over an initial header line containing column names:

mysql> LOAD DATA INFILE '/tmp/test.txt'
    -> INTO TABLE test IGNORE 1 LINES;

When you use SELECT ... INTO OUTFILE in tandem with LOAD DATA INFILE to write data from a database into a file and then read the file back into the database later, the field- and line-handling options for both statements must match. Otherwise, LOAD DATA INFILE does not interpret the contents of the file properly. Suppose that you use SELECT ... INTO OUTFILE to write a file with fields delimited by commas:

mysql> SELECT * INTO OUTFILE 'data.txt'
    ->          FIELDS TERMINATED BY ','
    ->          FROM table2;

To read the comma-delimited file back in, the correct statement would be:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
    ->           FIELDS TERMINATED BY ',';

If instead you tried to read in the file with the statement shown here, it wouldn't work because it instructs LOAD DATA INFILE to look for tabs between fields:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
    ->           FIELDS TERMINATED BY '\t';

The likely result is that each input line would be interpreted as a single field.

LOAD DATA INFILE can be used to read files obtained from external sources, too. For example, a file in dBASE format has fields separated by commas and enclosed within double quotes. If lines in the file are terminated by newlines, the statement shown here illustrates the field- and line-handling options you would use to load the file:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
    ->           FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    ->           LINES TERMINATED BY '\n';

Any of the field- or line-handling options can specify an empty string (''). If not empty, the FIELDS [OPTIONALLY] ENCLOSED BY and FIELDS ESCAPED BY values must be a single character. The FIELDS TERMINATED BY, LINES STARTING BY, and LINES TERMINATED BY values can be more than one character. For example, to write lines that are terminated by carriage return/linefeed pairs, or to read a file containing such lines, specify a LINES TERMINATED BY '\r\n' clause.

To read a file containing jokes that are separated by lines consisting of %%, you can do this

mysql> CREATE TABLE jokes
    ->     (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     joke TEXT NOT NULL);
mysql> LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes
    ->     FIELDS TERMINATED BY ''
    ->     LINES TERMINATED BY '\n%%\n' (joke);

FIELDS [OPTIONALLY] ENCLOSED BY controls quoting of fields. For output (SELECT ... INTO OUTFILE), if you omit the word OPTIONALLY, all fields are enclosed by the ENCLOSED BY character. An example of such output (using a comma as the field delimiter) is shown here:

"1","a string","100.20"
"2","a string containing a , comma","102.20"
"3","a string containing a \" quote","102.20"
"4","a string containing a \", quote and comma","102.20"

If you specify OPTIONALLY, the ENCLOSED BY character is used only to enclose values in 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 generate output that cannot be read properly by LOAD DATA INFILE. For example, the preceding output just shown would appear as follows if the escape character is empty. Observe that the second field in the fourth line contains a comma following the quote, which (erroneously) appears to terminate the field:

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a " quote",102.20
4,"a string containing a ", quote and comma",102.20

For input, the ENCLOSED BY character, if present, is stripped from the ends of field values. (This is true whether or not OPTIONALLY is specified; OPTIONALLY has no effect on input interpretation.) Occurrences of the ENCLOSED BY character preceded by the ESCAPED BY character are interpreted as part of the current field value.

If the field begins with the ENCLOSED BY character, instances of that character are recognized as terminating a field value only if followed by the field or line TERMINATED BY sequence. To avoid ambiguity, occurrences of the ENCLOSED BY character within a field value can be doubled and 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 happens 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:

mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list:

mysql> LOAD DATA INFILE 'persondata.txt'
    ->           INTO TABLE persondata (col1,col2,...);

You must also specify a column list if the order of the fields in the input file differs from the order of the columns in the table. Otherwise, MySQL cannot tell how to match up input fields with table columns.

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, and the SET clause is supported. This enables you to assign input values to user variables, and then perform transformations on those 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 column in the data file directly for the value of t1.column1, and assigns the second column to a user variable that is subjected to a division operation before being used for the value of t2.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 the assignment operator.

  • 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 13.1.5, “CREATE TABLE Syntax”.

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 (for the first TIMESTAMP column only) if the TIMESTAMP column is omitted from the field list when a field list is specified.

LOAD DATA INFILE regards all input as strings, so you 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.22, “SHOW WARNINGS Syntax”.

13.2.6. REPLACE Syntax

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [(col_name,...)]
    VALUES ({expr | DEFAULT},...),(...),...

Or:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...

Or:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...

REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted. See Section 13.2.4, “INSERT Syntax”.

Note that unless the table has a PRIMARY KEY or UNIQUE index, using a REPLACE statement makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.

Values for all columns are taken from the values specified in the REPLACE statement. Any missing columns are set to their default values, just as happens for INSERT. You cannot refer to values from the current row and use them in the new row. If you use an assignment such as SET col_name = col_name + 1, the reference to the column name on the right hand side is treated as DEFAULT(col_name), so the assignment is equivalent to SET col_name = DEFAULT(col_name) + 1.

To be able 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.

Here follows in more detail the algorithm that is used (it is also used with LOAD DATA ... REPLACE):

  1. Try to insert the new row into the table

  2. While the insertion fails because a duplicate-key error occurs for a primary or unique key:

    1. Delete from the table the conflicting row that has the duplicate key value

    2. Try again to insert the new row into the table

13.2.7. SELECT Syntax

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, ...
    [INTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name']
    [FROM table_references
    [WHERE where_definition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_definition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC] , ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT is used to retrieve rows selected from one or more tables, and can include UNION statements and subqueries. See Section 13.2.7.2, “UNION Syntax” and Section 13.2.8, “Subquery Syntax”.

  • Each select_expr indicates a column you want to retrieve.

  • table_references indicates the table or tables from which to retrieve rows. Its syntax is described in Section 13.2.7.1, “JOIN Syntax”.

  • where_definition consists of the keyword WHERE followed by an expression that indicates the condition or conditions that rows must satisfy to be selected.

SELECT can also be used to retrieve rows computed without reference to any table.

For example:

mysql> SELECT 1 + 1;
        -> 2

All clauses used must be given in exactly the order shown in the syntax description. For example, a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause.

  • A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses. For example:

    mysql> SELECT CONCAT(last_name,', ',first_name) AS full_name
        -> FROM mytable ORDER BY full_name;
    

    The AS keyword is optional when aliasing a select_expr. The preceding example could have been written like this:

    mysql> SELECT CONCAT(last_name,', ',first_name) full_name
        -> FROM mytable ORDER BY full_name;
    

    Because the AS is optional, a subtle problem can occur if you forget the comma between two select_expr expressions: MySQL interprets the second as an alias name. For example, in the following statement, columnb is treated as an alias name:

    mysql> SELECT columna columnb FROM mytable;
    

    For this reason, it is good practice to be in the habit of specifying column aliases explicitly using AS.

  • 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 table_references clause indicates the tables from which to retrieve rows. If you name more than one table, you are performing a join. For information on join syntax, see Section 13.2.7.1, “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=value as an alternative way to force MySQL to prefer key scans instead of table scans.

  • You can refer to a table within the current database as tbl_name (within the current database), 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, “Database, Table, Index, Column, and Alias Names” for examples of ambiguity that require the more explicit column reference forms.

  • 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 servers that require a FROM clause. MySQL does not require the clause if no tables are referenced, and the preceding statement can be written this way:

    mysql> SELECT 1 + 1;
            -> 2
    
  • A table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name:

    mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
        ->     WHERE t1.name = t2.name;
    mysql> SELECT t1.name, t2.salary FROM employee t1, info t2
        ->     WHERE t1.name = t2.name;
    
  • In the WHERE clause, you can use any of the functions that MySQL supports, except for aggregate (summary) functions. See Chapter 12, Functions and Operators.

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

    mysql> SELECT college, region, seed FROM tournament
        ->     ORDER BY region, seed;
    mysql> SELECT college, region AS r, seed AS s FROM tournament
        ->     ORDER BY r, s;
    mysql> SELECT college, region, seed FROM tournament
        ->     ORDER BY 2, 3;
    

    To sort in reverse order, add the DESC (descending) keyword to the name of the column in the ORDER BY clause that you are sorting by. The default is ascending order; this can be specified explicitly using the ASC keyword.

    Use of column positions is deprecated because the syntax has been removed from the SQL standard.

  • 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. 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 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:

    mysql> SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;
    

    Preference is given to standard SQL behavior, so that if a HAVING column name is used both in GROUP BY and as an aliased column in the output column list, preferences 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:

    mysql> SELECT col_name FROM tbl_name HAVING col_name > 0;
    

    Write this instead:

    mysql> SELECT col_name FROM tbl_name WHERE col_name > 0;
    
  • The HAVING clause can refer to aggregate functions, which the WHERE clause cannot:

    mysql> SELECT user, MAX(salary) FROM users
        ->     GROUP BY user HAVING MAX(salary)>10;
    

    (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):

    mysql> SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15
    

    For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax.

    To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

    mysql> SELECT * FROM tbl LIMIT 95,18446744073709551615;
    

    With one argument, the value specifies the number of rows to return from the beginning of the result set:

    mysql> SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows
    

    In other words, LIMIT n is equivalent to LIMIT 0,n.

    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:

    mysql> SET @a=1;
    mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?";
    mysql> EXECUTE STMT USING @a;
    

    The following statements will return the second to sixth row from the tbl table:

    mysql> SET @skip=1; SET @numrows=5;
    mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?, ?";
    mysql> EXECUTE STMT USING @skip, @numrows;
    
  • The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed.

    The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table on the server machine. If you want to create the resulting file on some client host other than the server host, you cannot use SELECT ... INTO OUTFILE. In that case, you should instead use some command like mysql -e "SELECT ..." > file_name on the client host to generate the file.

    SELECT ... INTO OUTFILE is the complement of LOAD DATA INFILE; the syntax for the export_options part of the statement consists of the same FIELDS and LINES clauses that are used with the LOAD DATA INFILE statement. See Section 13.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 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.

    The reason for the above is that you must escape any FIELDS TERMINATED BY, ENCLOSED BY, ESCAPED BY, or LINES TERMINATED BY characters to be able to read the file back 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.

    Here is an example that produces a file in the comma-separated values format used by many programs:

    SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    FROM test_table;
    
  • 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.

  • 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 on a storage engine that uses page or row locks, rows examined by the query are write-locked until the end of the current transaction. Using LOCK IN SHARE MODE sets a shared lock that prevents other transactions from updating or deleting the examined rows. See Section 14.2.10.5, “Locking Reads SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE.

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 that duplicate rows in the result set should be removed.

HIGH_PRIORITY, STRAIGHT_JOIN, and options beginning with SQL_ are MySQL extensions to standard SQL.

  • HIGH_PRIORITY 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, “EXPLAIN Syntax (Get Information About a SELECT)”. 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. MySQL also, in this case, 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. In MySQL 5.0, 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.13, “The MySQL Query Cache”.

  • SQL_NO_CACHE tells MySQL not to store the query result in the query cache. See Section 5.13, “The MySQL Query Cache”. For a query that uses UNION or subqueries, this option effects any SELECT in the query.

13.2.7.1. JOIN Syntax

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 )
  | { OJ table_reference LEFT OUTER JOIN table_reference
        ON conditional_expr }

join_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_condition]
  | table_reference STRAIGHT_JOIN table_factor
  | table_reference STRAIGHT_JOIN table_factor ON condition
  | table_reference LEFT [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [LEFT [OUTER]] JOIN table_factor
  | table_reference RIGHT [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor

join_condition:
    ON conditional_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, “How MySQL Optimizes Nested Joins”).

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 tbl_name AS alias_name or tbl_name alias_name:

    mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
        ->        WHERE t1.name = t2.name;
    mysql> SELECT t1.name, t2.salary FROM employee t1, info t2
        ->        WHERE t1.name = t2.name;
    
  • The ON conditional is any conditional expression of the form that can be used in a WHERE clause.

  • If there is no matching record 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 records in a table that have no counterpart in another table:

    mysql> SELECT table1.* FROM table1
        ->        LEFT JOIN table2 ON table1.id=table2.id
        ->        WHERE table2.id IS NULL;
    

    This example finds all rows in table1 with an id value that is not present in table2 (that is, all rows in table1 with no corresponding row in table2). This assumes that table2.id is declared NOT NULL. See Section 7.2.9, “How MySQL Optimizes LEFT JOIN and RIGHT JOIN.

  • The USING(column_list) clause names a list of columns that must exist in both tables. If tables 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 (key_list), you can tell MySQL to use only one of the possible indexes to find rows in the table. The alternative syntax IGNORE INDEX (key_list) can be used to tell MySQL to not use some particular index. These hints are useful if EXPLAIN shows that MySQL is using the wrong index from the list of possible indexes.

You can also use FORCE INDEX, which acts like USE INDEX (key_list) 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.

USE KEY, IGNORE KEY, and FORCE KEY are synonyms for USE INDEX, IGNORE INDEX, and FORCE INDEX.

Note: USE INDEX, IGNORE INDEX, and FORCE INDEX only affect which indexes are used when MySQL decides how to find rows in the table and how to do the join. They do not affect whether an index is used when resolving an ORDER BY or GROUP BY.

Some join examples:

mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id);
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
    ->          LEFT JOIN table3 ON table2.id=table3.id;
mysql> SELECT * FROM table1 USE INDEX (key1,key2)
    ->          WHERE key1=1 AND key2=2 AND key3=3;
mysql> SELECT * FROM table1 IGNORE INDEX (key3)
    ->          WHERE key1=1 AND key2=2 AND key3=3;

See Section 7.2.9, “How MySQL Optimizes LEFT JOIN and RIGHT JOIN.

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 columns of a NATURAL join or a USING join may be different from 5.0.12 on. Specifically, redundant output columns no longer appear, and the order of columns for SELECT * expansion may be different than before.

    Example:

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

    Before 5.0.12, the statements produce 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 it should appear only once in the output according to standard SQL. Similarly, in the second SELECT statement, column j is named in the USING clause and should appear only once in the output. But in both cases, the redundant column is not eliminated. Also, the order of the columns is not correct according to standard SQL.

    As of 5.0.12, the statements produce this output:

    +------+------+------+
    | j    | i    | k    |
    +------+------+------+
    |    1 |    1 |    1 |
    +------+------+------+
    +------+------+------+
    | j    | i    | k    |
    +------+------+------+
    |    1 |    1 |    1 |
    +------+------+------+
    

    The redundant column is eliminated, and 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 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). Also suppose that you have this NATURAL JOIN on the three tables:

    SELECT … FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;
    

    Prior to MySQL 5.0.12, the left operand of the second join is 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, in older versions of MySQL, the preceding query is 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 5.0.12 or later as before 5.0.12, rewrite the natural join as the first equi-join.

  • Before MySQL 5.0.12, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was intrepreted 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);
    

    Prior to 5.0.12, the SELECT is legal due to the implicit grouping of t1,t2 as (t1,t2). From 5.0.12 on, 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);
    

    This change also applies to INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN.

  • Before MySQL 5.0.12, 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;
    

    Before 5.0.12, the SELECT statement is legal. From 5.0.12 on, the statement fails with an Unknown column 'i3' in 'on clause' error because i3 is a table 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);
    
  • Before MySQL 5.0.12, a USING clause can 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
    

    From 5.0.12 on, 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 correspondinging 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.

13.2.7.2. UNION Syntax

SELECT ...
UNION [ALL | DISTINCT]
SELECT ...
[UNION [ALL | DISTINCT]
SELECT ...]

UNION is used to combine the result from a number of SELECT statements into one result set.

Selected columns listed in corresponding positions of each SELECT statement should have the same type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.) The column names used in the first SELECT statement are used as the column names for the results returned.

The SELECT statements are normal select statements, but with the following restrictions:

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

If you do not use the keyword ALL for the UNION, all returned rows are unique, as if you had done a DISTINCT for the total result set. If you specify ALL, you get all matching rows from all the used SELECT statements.

The DISTINCT keyword is an optional word which has no effect, but is allowed in the syntax as required by the SQL standard. (In MySQL, DISTINCT represents the default behavior of a union.)

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.

If you want to use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one. The following example uses both clauses:

(SELECT a FROM tbl_name WHERE a=10 AND B=1)
UNION
(SELECT a FROM tbl_name WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;

This kind of ORDER BY cannot use column references that include a table name (that is, names in tbl_name.col_name format). Instead, provide a column alias in the first SELECT statement and refer to the alias in the ORDER BY, or else refer to the column in the ORDER BY using its column position. (An alias is preferable because using 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 tbl_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM tbl_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

ORDER BY for individual SELECT statements within parentheses has an effect only when combined with LIMIT. Otherwise, the ORDER BY is optimized away.

The types and lengths of the columns in the result set of a UNION 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, the second row would have been truncated to a length of 1.)

13.2.8. Subquery Syntax

A subquery is a SELECT statement within another statement.

MySQL supports all subquery forms and operations that the SQL standard requires, 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, and UPDATE. A more comprehensive discussion of restrictions on subquery use is given in Appendix I, Feature Restrictions.

13.2.8.1. The Subquery as Scalar Operand

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

For example, suppose that we make two tables:

CREATE TABLE t1 (s1 INT);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (s1 INT);
INSERT INTO t2 VALUES (2);

Then perform a SELECT:

SELECT (SELECT s1 FROM t2) FROM t1;

The result 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. Don't forget the parentheses, even if the subquery is an operand that provides an argument for a function. For example:

SELECT UPPER((SELECT s1 FROM t1)) FROM t2;

13.2.8.2. Comparisons Using Subqueries

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

13.2.8.3. Subqueries with ANY, IN, and SOME

Syntax:

operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)

The ANY keyword, which must follow a comparison operator, means “return TRUE if the comparison is TRUE for ANY of the values in the column that the subquery returns”. For example:

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

Suppose that there is a row in table t1 containing (10). The expression is TRUE if table t2 contains (21,14,7) because there is a value 7 in t2 that is less than 10. The expression is FALSE if table t2 contains (20,10), or if table t2 is empty. The expression is UNKNOWN if table t2 contains (NULL,NULL,NULL).

The word IN is an alias for = ANY. Thus, these two statements are the same:

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN    (SELECT s1 FROM t2);

However, NOT IN is not an alias for <> ANY, but for <> ALL. See Section 13.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.

13.2.8.4. Subqueries with ALL

Syntax:

operand comparison_operator ALL (subquery)

The word ALL, which must follow a comparison operator, means “return TRUE if the comparison is TRUE for ALL of the values in the column that the subquery returns.” For example:

SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);

Suppose that there is a row in table t1 containing (10). The expression is TRUE if table t2 contains (-5,0,+5) because 10 is greater than all three values in t2. The expression is FALSE if table t2 contains (12,6,NULL,-100) because there is a single value 12 in table t2 that is greater than 10. The expression is unknown (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);

13.2.8.5. Row Subqueries

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

13.2.8.6. EXISTS and NOT EXISTS

If a subquery returns any rows at all, then EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE. For example:

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference.

For the preceding example, if t2 contains any rows, even rows with nothing but NULL values, then the EXISTS condition is TRUE. This is actually an unlikely example, since almost always a [NOT] EXISTS subquery 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?

13.2.8.7. Correlated Subqueries

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 (SELECT key_val FROM tbl_name WHERE correlated_condition)

Otherwise, they are inefficient and likely to be slow. Rewriting the query as a join might improve performance.

Correlated subqueries cannot refer to the results of aggregate functions from the outer query.

13.2.8.8. Subqueries in the FROM clause

Subqueries are legal in a SELECT statement's FROM clause. The actual syntax is:

SELECT ... FROM (subquery) [AS] name ...

The [AS] name clause is mandatory, because every table in a FROM clause must have a name. Any columns in the subquery select list must have unique names. You can find this syntax described elsewhere in this manual, where the term used is “derived tables”.

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

13.2.8.9. Subquery Errors

There are some errors that apply only to subqueries. This section groups them together.

  • 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, then 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, since subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table (in this case table t1) for both the subquery's FROM clause and the update target.

For transactional storage engines, 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.

13.2.8.10. Optimizing Subqueries

Development is ongoing, so no optimization tip is reliable for the long term. Some interesting tricks that you might want to play with are:

  • 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 (table containing (1,2)) rather than 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.

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 (SELECT indexed_column FROM single_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/.

13.2.8.11. Rewriting Subqueries as Joins for Earlier MySQL Versions

In previous versions of MySQL (prior to MySQL 4.1), only nested queries of the form INSERT ... SELECT ... and REPLACE ... SELECT ... were supported. While 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 that 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 also 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.

13.2.9. TRUNCATE Syntax

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 FROM 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 definition file tbl_name.frm is valid, the table can be re-created as an empty table with TRUNCATE 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.

13.2.10. UPDATE Syntax

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]

The UPDATE statement updates columns in existing table rows with new values. The SET clause indicates which columns to modify and the values they should be given. The WHERE clause, if given, specifies which rows should be updated. Otherwise, all rows are updated. If the ORDER BY clause is specified, the rows are updated in the order that is specified. The LIMIT clause places a limit on the number of rows that can be updated.

The UPDATE statement supports the following modifiers:

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

mysql> UPDATE persondata SET age=age+1;

UPDATE assignments are evaluated from left to right. For example, the following statement doubles the age column, then increments it:

mysql> UPDATE persondata SET age=age*2, age=age+1;

If you set a column to the value it currently has, MySQL notices this and 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 column type and the warning count is incremented. The default value is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types.

UPDATE returns the number of rows that were actually changed. 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 row_count to restrict the scope of the 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. 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 example above shows an inner join using the comma operator, but multiple-table UPDATE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN.

Note: You cannot use ORDER BY or LIMIT with a multiple-table UPDATE.

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.

13.3. MySQL Utility Statements

13.3.1. DESCRIBE Syntax (Get Information About Columns)

{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 in 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)

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 our example, the Extra field indicates that the Id column was created with the AUTO_INCREMENT keyword.

If the column types are different from what you expect them to be based on a CREATE TABLE statement, note that MySQL sometimes changes column types. See Section 13.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”.

13.3.2. USE Syntax

USE db_name

The USE db_name statement tells MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default until the end of the session or until a different USE statement is issued:

mysql> USE db1;
mysql> SELECT COUNT(*) FROM mytable;   # selects from db1.mytable
mysql> USE db2;
mysql> SELECT COUNT(*) FROM mytable;   # selects from db2.mytable

Making a particular database current by means of the USE statement does not preclude you from accessing tables in other databases. The following example accesses the author table from the db1 database and the editor table from the db2 database:

mysql> USE db1;
mysql> SELECT author_name,editor_name FROM author,db2.editor
    ->        WHERE author.editor_id = db2.editor.editor_id;

The USE statement is provided for compatibility with Sybase.

13.4. MySQL Transactional and Locking Statements

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 so that MySQL can participate in distributed transactions as well. See Section 13.4.7, “XA Transactions”.

13.4.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax

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 or 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.3.3, “Server System Variables”.

The AND CHAIN clause causes a new transaction to begin as soon as the current one end, 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 (like 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.

If you want to disable autocommit mode for a single series of statements, you can use the START TRANSACTION statement:

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state.

BEGIN and BEGIN WORK 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.7, “BEGIN ... END Compound Statement”.

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

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.11.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 command) rollbacks.

13.4.2. Statements That Cannot Be Rolled Back

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.

13.4.3. Statements That Cause an Implicit Commit

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.

13.4.4. SAVEPOINT and ROLLBACK TO SAVEPOINT Syntax

SAVEPOINT identifier
ROLLBACK [WORK] TO SAVEPOINT identifier
RELEASE SAVEPOINT identifier

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 save point with a name of identifier. If the current transaction has a save point with the same name, the old save point is deleted and a new one is set.

The ROLLBACK TO SAVEPOINT statement rolls back a transaction to the named save point. Modifications that the current transaction made to rows after the save point was set are undone in the rollback, but InnoDB does not release the row locks that were stored in memory after the save point. (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 save point are deleted.

If the statement returns the following error, it means that no save point with the specified name exists:

ERROR 1181: Got error 153 during ROLLBACK

The RELEASE SAVEPOINT statement removes the named save point from the set of save points of the current transaction. No commit or rollback occurs. It is an error if the save point does not exist.

All save points of the current transaction are deleted if you execute a COMMIT, or a ROLLBACK that does not name a save point.

13.4.5. LOCK TABLES and UNLOCK TABLES Syntax

LOCK TABLES
    tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
    [, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
UNLOCK TABLES

LOCK TABLES locks tables for the current thread. 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, like InnoDB, is to set AUTOCOMMIT = 0 and not to call UNLOCK TABLES until you commit the transaction explicitly. When you call LOCK TABLES, InnoDB internally takes its own table lock, and MySQL takes its own table lock. InnoDB releases its table lock at the next commit, but for MySQL to release its table lock, you have to call UNLOCK TABLES. You should not have AUTOCOMMIT = 1, because then InnoDB releases its table lock immediately after the call of LOCK TABLES, and deadlocks can very easily happen. Note that we do not acquire the InnoDB table lock at all if AUTOCOMMIT=1, in order 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 for emulating transactions or to get more speed when updating tables. This is explained in more detail later.

If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, only the thread holding the lock can write to the table. Other threads are blocked from doing so until the lock has been released.

The difference between READ LOCAL and READ is that READ LOCAL allows non-conflicting INSERT statements (concurrent inserts) to execute while the lock is held. However, this 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 using an alias, then 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:

  1. Sort all tables to be locked in an internally defined order. From the user standpoint, this order is undefined.

  2. If a table is locked with a read and a write lock, put the write lock before the read lock.

  3. 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:

    mysql> LOCK TABLES trans READ, customer WRITE;
    mysql> SELECT SUM(value) FROM trans WHERE customer_id=some_id;
    mysql> UPDATE customer
        ->     SET total_value=sum_from_previous_statement
        ->     WHERE customer_id=some_id;
    mysql> UNLOCK TABLES;
    

    Without LOCK TABLES, it is possible that another thread might insert a new row in the trans table between execution of the SELECT and UPDATE statements.

You can avoid using LOCK TABLES in many cases by using relative updates (UPDATE customer SET value=value+new_value) or the LAST_INSERT_ID() function, See Section 1.8.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.

13.4.6. SET TRANSACTION Syntax

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.

You can set the initial default global isolation level for mysqld with the --transaction-isolation option. See Section 5.3.1, “mysqld Command-Line Options”.

13.4.7. XA Transactions

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 (that is, through 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 mus 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 e-mail.

  • 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 allows 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's 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's 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.

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

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

13.4.7.1. XA Transaction SQL Syntax

To perform XA transactions in MySQL, use the following statements:

XA {START|BEGIN} xid [JOIN|RESUME]

XA END xid [SUSPEND [FOR MIGRATE]]

XA PREPARE xid

XA COMMIT xid [ONE PHASE]

XA ROLLBACK xid

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 a 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 xid starts an XA transaction with the given 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

13.4.7.2. XA Transaction States

An XA transaction progresses through the following states:

  1. Use XA START to start an XA transaction and put it in the ACTIVE state.

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

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

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

13.5. Database Administration Statements

13.5.1. Account Management Statements

13.5.1.1. CREATE USER Syntax

CREATE USER user [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.

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 keyword PASSWORD. See Section 13.5.1.3, “GRANT and REVOKE Syntax”.

13.5.1.2. DROP USER Syntax

DROP USER user [, user] ...

The DROP USER statement deletes 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 GRANT or REVOKE; for example, 'jeffrey'@'localhost'. The user and host parts of the account name correspond to the User and Host column values of the user table record for the account.

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

  1. Use SHOW GRANTS to determine what privileges the account has. See Section 13.5.4.10, “SHOW GRANTS Syntax”.

  2. Use REVOKE to revoke the privileges displayed by SHOW GRANTS. This removes records for the account from all the grant tables except the user table, and revokes any global privileges listed in the user table. See Section 13.5.1.3, “GRANT and REVOKE Syntax”.

  3. 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 command 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.

13.5.1.3. GRANT and REVOKE Syntax

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON [object_type] {tbl_name | * | *.* | db_name.*}
    TO user [IDENTIFIED BY [PASSWORD] 'password']
        [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
    [REQUIRE
        NONE |
        [{SSL| X509}]
        [CIPHER 'cipher' [AND]]
        [ISSUER 'issuer' [AND]]
        [SUBJECT 'subject']]
    [WITH with_option [with_option] ...]

object_type =
    TABLE
  | FUNCTION
  | PROCEDURE

with_option =
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON [object_type] {tbl_name | * | *.* | db_name.*}
    FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

The GRANT and REVOKE statements allow system administrators to create MySQL user accounts and to grant rights to and revoke them from accounts.

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.

If the grant tables hold privilege records that contain mixed-case database or table names and the lower_case_table_names system variable is set, REVOKE cannot be used to revoke the privileges. It will be necessary to manipulate the grant tables directly. (GRANT will not create such records when lower_case_table_names is set, but such records 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 db_name.* and REVOKE ALL ON db_name.* grant and revoke only database privileges.

  • 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 db_name.tbl_name and REVOKE ALL ON db_name.tbl_name grant and revoke only table privileges.

  • 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. 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. To use this clause when upgrading from a version of MySQL older than 5.0.6, you must upgrade your grant tables. See Section 2.10.3, “Upgrading the Grant Tables”.

To use GRANT or REVOKE, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting or revoking.

To revoke all privileges, use the following syntax, which drops all global, database-, table-, and column-level privileges for the named user(s):

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

To use this REVOKE syntax, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql database.

For the GRANT and REVOKE statements, priv_type can be specified as any of the following:

PrivilegeMeaning
ALL [PRIVILEGES]Sets all simple privileges except GRANT OPTION
ALTERAllows use of ALTER TABLE
ALTER ROUTINEAlter or drop stored routines
CREATEAllows use of CREATE TABLE
CREATE ROUTINECreate stored routines
CREATE TEMPORARY TABLESAllows use of CREATE TEMPORARY TABLE
CREATE USERAllows use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.
CREATE VIEWAllows use of CREATE VIEW
DELETEAllows use of DELETE
DROPAllows use of DROP TABLE
EXECUTEAllows the user to run stored routines
FILEAllows use of SELECT ... INTO OUTFILE and LOAD DATA INFILE
INDEXAllows use of CREATE INDEX and DROP INDEX
INSERTAllows use of INSERT
LOCK TABLESAllows use of LOCK TABLES on tables for which you have the SELECT privilege
PROCESSAllows use of SHOW FULL PROCESSLIST
REFERENCESNot implemented
RELOADAllows use of FLUSH
REPLICATION CLIENTAllows the user to ask where slave or master servers are
REPLICATION SLAVENeeded for replication slaves (to read binary log events from the master)
SELECTAllows use of SELECT
SHOW DATABASESSHOW DATABASES shows all databases
SHOW VIEWAllows use of SHOW CREATE VIEW
SHUTDOWNAllows use of mysqladmin shutdown
SUPERAllows 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
UPDATEAllows use of UPDATE
USAGESynonym for “no privileges
GRANT OPTIONAllows 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. To use these privileges when upgrading from an earlier version of MySQL that does not have them, you must first upgrade the grant tables, as described in Section 2.10.3, “Upgrading the Grant Tables”.

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 the account has. See Section 13.5.4.10, “SHOW GRANTS Syntax”.

You can assign global privileges by using ON *.* syntax or database-level privileges by using ON db_name.* syntax. If you specify 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 only priv_type values you can specify for a table are SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, INDEX, and ALTER.

The only priv_type values you can specify for a column (that is, when you use a column_list clause) are SELECT, INSERT, and UPDATE.

The only priv_type values 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 be able 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, if you use GRANT ALL ON db_name.*, that is a database-level statement, so none of the global-only privileges such as FILE are granted.

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

In order to accommodate granting rights to users from arbitrary hosts, MySQL supports specifying the user value in the form user_name@host_name. If a 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, user_name@'%.loc.gov' applies to user_name for any host in the loc.gov domain, and user_name@'144.155.166.%' applies to user_name for any host in the 144.155.166 class C subnet.

The simple form user_name is a synonym for user_name@'%'.

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

mysql> 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 user_name@localhost. Otherwise, the anonymous user account for localhost 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.

You can determine whether this applies to you by executing the following query, which lists any anonymous users:

mysql> SELECT Host, User FROM mysql.user WHERE User='';

If you want to delete the local anonymous user account to avoid the problem just described, use these statements:

mysql> DELETE FROM mysql.user WHERE Host='localhost' AND User='';
mysql> FLUSH PRIVILEGES;

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 attempt to alter the grant tables in any way except by means of the mysql_fix_privilege_tables script as supplied by MySQL AB for use in upgrading the MySQL server.

The privileges for a table or column are formed additively as the logical OR of the privileges at each of the four privilege levels. For example, if the mysql.user table specifies that a user has a global SELECT privilege, the privilege cannot be denied by an entry at the database, table, or column level.

The privileges for a column can be calculated as follows:

global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges

In most cases, you grant rights to a user at only one of the privilege levels, so life is not normally this complicated. The details of the privilege-checking procedure are presented in Section 5.7, “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.

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.

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 keep GRANT from creating a new user if it would otherwise do so, unless IDENTIFIED BY is given to provide the new user a password.

Passwords can also be set with the SET PASSWORD statement. See Section 13.5.1.5, “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.

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 SHOW DATABASES privilege allows 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.

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

You should not grant ALTER privileges to a normal user. If you do that, the user can try to subvert the privilege system by renaming tables!

The MAX_QUERIES_PER_HOUR count, MAX_UPDATES_PER_HOUR count, and MAX_CONNECTIONS_PER_HOUR count options limit the number of queries, updates, and logins a user can perform during any given one-hour period. If count is 0 (the default), this means that there is no limitation for that user.

The MAX_USER_CONNECTIONS count option, implemented in MySQL 5.0.3, limits the maximum number of simultaneous connections that the account can make. If 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.8.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.8.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 records that allow non-SSL connections.

    mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
        -> IDENTIFIED BY 'goodsecret' REQUIRE SSL;
    
  • REQUIRE X509 means that the client must have a valid certificate but that the exact certificate, issuer, and subject do not matter. The only requirement is that it should be possible to verify its signature with one of the CA certificates.

    mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
        -> IDENTIFIED BY 'goodsecret' REQUIRE X509;
    
  • REQUIRE ISSUER 'issuer' places the restriction on connection attempts that the client must present a valid X509 certificate issued by CA 'issuer'. If the client presents the client presents a certificate that is valid but has a different issuer, the server rejects the connection. Use of X509 certificates always implies encryption, so the SSL option is unnecessary in this case.

    mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
        -> IDENTIFIED BY 'goodsecret'
        -> REQUIRE ISSUER '/C=FI/ST=Some-State/L=Helsinki/
           O=MySQL Finland AB/CN=Tonu Samuel/Email=tonu@example.com';
    

    Note that the ISSUER value should be entered as a single string.

  • REQUIRE SUBJECT 'subject' places the restriction on connection attempts that the client must present a valid X509 certificate containing the subject subject. If the client presents a certificate that is valid but has a different subject, the server rejects the connection.

    mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
        -> IDENTIFIED BY 'goodsecret'
        -> REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/
           O=MySQL demo client certificate/
           CN=Tonu Samuel/Email=tonu@example.com';
    

    Note that the SUBJECT value should be entered as a single string.

  • REQUIRE CIPHER 'cipher' is needed to ensure thatciphers 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.

    mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
        -> IDENTIFIED BY 'goodsecret'
        -> REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';
    

The SUBJECT, ISSUER, and CIPHER options can be combined in the REQUIRE clause like this:

mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
    -> IDENTIFIED BY 'goodsecret'
    -> REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/
       O=MySQL demo client certificate/
       CN=Tonu Samuel/Email=tonu@example.com'
    -> AND ISSUER '/C=FI/ST=Some-State/L=Helsinki/
       O=MySQL Finland AB/CN=Tonu Samuel/Email=tonu@example.com'
    -> AND CIPHER 'EDH-RSA-DES-CBC3-SHA';

Note that the SUBJECT and ISSUER values each should be entered as a single string.

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.7.7, “When Privilege Changes Take Effect”.

Note that if you are using table or column privileges for even one user, the server examines table and column privileges for all users and this slows down MySQL a bit. Similarly, if you limit the number of queries, updates, or connections for any users, the server must monitor these values.

The biggest differences between the standard SQL and MySQL versions of GRANT are:

  • 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. Section 5.3.2, “The Server SQL Mode” discusses strict mode. Section 13.1.5, “CREATE TABLE Syntax” discusses implicit default values.

    The columns for which you don't have the INSERT privilege are set to their default values. Standard SQL requires you to have the INSERT privilege on all columns.

    In MySQL, if you have the INSERT privilege on only some of the columns in a table, you can still execute INSERT statements on the table — provided you omit the columns on which you do not have the privilege from your INSERT statement; those columns will be set to their default values. Under strict mode (i.e. when sql_mode='traditional', if any of the omitted columns have no default value, the INSERT statement will be rejected.

13.5.1.4. RENAME USER Syntax

RENAME USER old_user TO new_user
    [, old_user TO new_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. The old_user and new_user values are given the same way as for the GRANT statement.

The RENAME USER statement was added in MySQL 5.0.2.

13.5.1.5. SET PASSWORD Syntax

SET PASSWORD = PASSWORD('some password')
SET PASSWORD FOR user = PASSWORD('some password')

The SET PASSWORD statement assigns a password to an existing MySQL user account.

The first syntax sets the password for the current user. Any client that has connected to the server using a non-anonymous account can change the password for that account.

The second syntax sets the password for a specific account on the current server host. Only clients with the UPDATE privilege for the mysql database can do this. The user value should be given in user_name@host_name format, where user_name and host_name are exactly as they are listed in the User and Host columns of the mysql.user table entry. For example, if you had an entry with User and Host column values of 'bob' and '%.loc.gov', you would write the statement like this:

mysql> SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');

That is equivalent to the following statements:

mysql> UPDATE mysql.user SET Password=PASSWORD('newpass')
    -> WHERE User='bob' AND Host='%.loc.gov';
mysql> FLUSH PRIVILEGES;

Note: If you are connecting to a MySQL 4.1 or later server using a pre-4.1 client program, do not use the preceding SET PASSWORD or UPDATE statement without reading Section 5.7.9, “Password Hashing in MySQL 4.1” first. The password format changed in MySQL 4.1, and under certain circumstances it is possible that if you change your password, you might not be able to connect to the server afterward.

You can see what your current authentication user@host entry is by executing SELECT CURRENT_USER().

13.5.2. Table Maintenance Statements

13.5.2.1. ANALYZE TABLE Syntax

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

This statement analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock. This works with MyISAM, BDB, and InnoDB tables. For MyISAM tables, this statement is equivalent to using myisamchk -a.

MySQL uses the stored key distribution to decide the order in which tables should be joined when you perform a join on something other than a constant.

The statement returns a table with the following columns:

ColumnValue
TableThe table name
OpAlways analyze
Msg_typeOne of status, error, info, or warning
Msg_textThe message

You can check the stored key distribution with the SHOW INDEX statement. See Section 13.5.4.11, “SHOW INDEX Syntax”.

If the table 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.

13.5.2.2. BACKUP TABLE Syntax

BACKUP TABLE tbl_name [, tbl_name] ... TO '/path/to/backup/directory'

Note: This statement is deprecated. We are working on a better replacement for it that will provide online backup capabilities. In the meantime, the mysqlhotcopy script can be used instead.

BACKUP TABLE copies to the backup directory the minimum number of table files needed to restore the table, after flushing any buffered changes to disk. The statement works only for MyISAM tables. It copies the .frm definition and .MYD data files. The .MYI index file can be rebuilt from those two files. The directory should be specified as a full pathname.

Before using this statement, please see Section 5.9.1, “Database Backups”.

During the backup, a read lock is held for each table, one at time, as they are being backed up. If you want to back up several tables as a snapshot (preventing any of them from being changed during the backup operation), you must first issue a LOCK TABLES statement to obtain a read lock for every table in the group.

The statement returns a table with the following columns:

ColumnValue
TableThe table name
OpAlways backup
Msg_typeOne of status, error, info, or warning
Msg_textThe message

13.5.2.3. CHECK TABLE Syntax

CHECK TABLE tbl_name [, tbl_name] ... [option] ...

option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

Checks a table or tables for errors. CHECK TABLE works for MyISAM and InnoDB tables. For MyISAM tables, the key statistics are updated.

As of MySQL 5.0.2, CHECK TABLE can also check views for problems, such as tables that are referenced in the view definition that no longer exist.

The CHECK TABLE statement returns a table with the following columns:

ColumnValue
TableThe table name
OpAlways check
Msg_typeOne of status, error, info, or warning
Msg_textThe 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.9.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 different check options that can be given are shown in the following table. These options apply only to checking MyISAM tables and are ignored for InnoDB tables and views.

TypeMeaning
QUICKDo not scan the rows to check for incorrect links.
FASTCheck only tables that have not been closed properly.
CHANGEDCheck only tables that have been changed since the last check or that have not been closed properly.
MEDIUMScan 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.
EXTENDEDDo 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, which does a quick check on the table to see whether it was closed properly:

CHECK TABLE test_table FAST QUICK;

Note: In some cases, CHECK TABLE 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.

13.5.2.4. CHECKSUM TABLE Syntax

CHECKSUM TABLE tbl_name [, tbl_name] ... [ QUICK | EXTENDED ]

Reports a table checksum.

If QUICK is specified, the live table checksum is reported if it is available, or NULL otherwise. This is very fast. A live checksum is enabled by specifying the CHECKSUM=1 table option, currently supported only for MyISAM tables. See Section 13.1.5, “CREATE TABLE Syntax”.

In EXTENDED mode the whole table is read row by row and the checksum is calculated. This can be very slow for large tables.

By default, if neither QUICK nor EXTENDED is specified, MySQL returns a live checksum if the table storage engine supports it and scans the table otherwise.

CHECKSUM TABLE returns NULL for non-existent tables. As of MySQL 5.0.3, a warning is generated for this condition.

13.5.2.5. OPTIMIZE TABLE Syntax

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB, or TEXT columns). Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.

In most setups, you need not run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable-length rows, it 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 on MyISAM, BDB, and InnoDB tables.

For MyISAM tables, OPTIMIZE TABLE works as follows:

  1. If the table has deleted or split rows, repair the table.

  2. If the index pages are not sorted, sort them.

  3. 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. For InnoDB tables, it is mapped to ALTER TABLE, which rebuilds the table. Rebuilding updates index statistics and frees unused space in the clustered index. See Section 13.5.2.1, “ANALYZE TABLE Syntax”.

You can make OPTIMIZE TABLE work on other table types by starting mysqld with the --skip-new or --safe-mode option; in this case, OPTIMIZE TABLE is just mapped to ALTER TABLE.

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 has been done so that OPTIMIZE TABLE commands used on a MySQL server acting as a replication master will be replicated by default to the replication slave.

13.5.2.6. REPAIR TABLE Syntax

REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE
    tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]

REPAIR TABLE repairs a possibly corrupted table. By default, it has the same effect as myisamchk --recover tbl_name. REPAIR TABLE works on MyISAM and on ARCHIVE tables. See Section 14.1, “The MyISAM Storage Engine”, 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”. See also Section 14.1.4, “MyISAM Table Problems”.

This statement returns a table with the following columns:

ColumnValue
TableThe table name
OpAlways repair
Msg_typeOne of status, error, info, or warning
Msg_textThe 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, because REPAIR TABLE does not yet implement all the options of myisamchk. We plan to make it more flexible in the future.

If QUICK is given, REPAIR TABLE tries to repair only the index tree. This type of repair is like that done by myisamchk --recover --quick.

If you use EXTENDED, MySQL creates the index row by row instead of creating one index at a time with sorting. 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. .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, as 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.

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.

13.5.2.7. RESTORE TABLE Syntax

RESTORE TABLE tbl_name [, tbl_name] ... FROM '/path/to/backup/directory'

Restores the table or tables from a backup that was made with BACKUP TABLE. Existing tables are not overwritten; if you try to restore over an existing table, you get an error. Just as BACKUP TABLE, RESTORE TABLE currently works only for MyISAM tables. The directory should be specified as a full pathname.

The backup for each table consists of its .frm format file and .MYD data file. The restore operation restores those files, then uses them to rebuild the .MYI index file. Restoring takes longer than backing up due to the need to rebuild the indexes. The more indexes the table has, the longer it takes.

The statement returns a table with the following columns:

ColumnValue
TableThe table name
OpAlways restore
Msg_typeOne of status, error, info, or warning
Msg_textThe message

13.5.3. SET Syntax

SET variable_assignment [, variable_assignment] ...

variable_assignment:
      user_var_name = expr
    | [GLOBAL | SESSION] system_var_name = expr
    | @@[global. | session.]system_var_name = expr

SET sets different types of variables that affect the operation of the server or your client. It can be used to assign values to user variables or system variables.

The SET PASSWORD statement for assigning account passwords is described in See Section 13.5.1.5, “SET PASSWORD Syntax”.

Most system variables can be changed at runtime. The system variables that can be set dynamically are described in Section 5.3.3.1, “Dynamic System Variables”.

Note: Older versions of MySQL employed SET OPTION for this command, but this usage is now deprecated in favor of SET.

The following example show the different syntaxes you can use to set variables.

A user variable is written as @var_name and can be set as follows:

SET @var_name = expr;

Further information about user variables is given in Section 9.3, “User Variables”.

System variables can be referred to in SET statements as var_name. The name optionally can be preceded by GLOBAL or @@global. to indicate explicitly that the variable is a global variable, or by SESSION, @@session., or @@ to indicate that it is a session variable. LOCAL and @@local. are synonyms for SESSION and @@session.. If no modifier is present, SET sets the session variable.

The @@var_name syntax for system variables is supported to make MySQL syntax compatible with some other database systems.

If you set several system variables in the same statement, the last used GLOBAL or SESSION option is used for variables that have no mode specified.

SET sort_buffer_size=10000;
SET @@local.sort_buffer_size=10000;
SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;
SET @@sort_buffer_size=1000000;
SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;

If you set a system variable using SESSION (the default), the value remains in effect until the current session ends or until you set the variable to a different value. If you set a system variable using GLOBAL, which requires the SUPER privilege, the value is remembered and used for new connections until the server restarts. If you want to make a variable setting permanent, you should put it in an option file. See Section 4.3.2, “Using Option Files”.

To prevent incorrect usage, MySQL produces an error if you use SET GLOBAL with a variable that can only be used with SET SESSION or if you do not specify GLOBAL (or @@) when setting a global variable.

If you want to set a SESSION variable to the GLOBAL value or a GLOBAL value to the compiled-in MySQL default value, 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;

You can get a list of most system variables with SHOW VARIABLES. (See Section 13.5.4.21, “SHOW VARIABLES Syntax”.) To obtain a specific variable name or list of names that match a pattern, use a LIKE clause as shown:

SHOW VARIABLES LIKE 'max_join_size';
SHOW GLOBAL VARIABLES LIKE 'max_join_size';

To get a list of variables whose name match a pattern, use the wildcard character ‘%’:

SHOW VARIABLES LIKE 'have%';
SHOW GLOBAL VARIABLES LIKE 'have%';

The wildcard character can be used in any position within the pattern to be matched.

You can also get the value for a specific value by using the @@[global.|local.]var_name syntax with SELECT:

SELECT @@max_join_size, @@global.max_join_size;

When you retrieve a variable with SELECT @@var_name (that is, you do not specify global., session., or local.), MySQL returns the SESSION value if it exists and the GLOBAL value otherwise.

The following list describes variables that have non-standard syntax or that are not described in the list of system variables that is found in Section 5.3.3, “Server System Variables”. Although these variables are not displayed by SHOW VARIABLES, you can obtain their values with SELECT (with the exception of CHARACTER SET and SET NAMES). For example:

mysql> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+
  • AUTOCOMMIT = {0 | 1}

    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. 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 tbl_name is full does not occur for SELECT operations that require a large temporary table. The default value for a new connection is 0 (use in-memory temporary tables). 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.

    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 than that required by their parent/child relationships. See Section 14.2.6.4, “FOREIGN KEY Constraints”.

  • IDENTITY = value

    The variable is a synonym for the LAST_INSERT_ID variable. It exists for compatibility with other databases. 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.

    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 is not a server system variable, but 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 timezone 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 resets, after the next statement, all character set, collation, and time zone-related server system variables. The only exception when resetting doesn't happen is when the next statement is a SET statement. In other words, resetting takes place after the next non-SET statement. 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, SHOW CREATE TABLE quotes table and column names. If set to 0, quoting is disabled. This option is enabled by default so that replication works for tables with table and column names that require quoting. See Section 13.5.4.5, “SHOW CREATE TABLE 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 records 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.

13.5.4. SHOW Syntax

SHOW has many forms that provide information about databases, tables, columns, or status information about the server. This section describes those following:

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']
SHOW CREATE DATABASE db_name
SHOW CREATE TABLE tbl_name
SHOW DATABASES [LIKE 'pattern']
SHOW ENGINE engine_name {LOGS | STATUS }
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW INNODB STATUS
SHOW [BDB] LOGS
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern']
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
SHOW [OPEN] TABLES [FROM db_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 'pattern' part, 'pattern' is a string that can contain the SQL ‘%’ and ‘_’ wildcard characters. The pattern is useful for restricting statement output to matching values.

13.5.4.1. SHOW CHARACTER SET Syntax

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 used to store one character.

13.5.4.2. SHOW COLLATION Syntax

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.

13.5.4.3. SHOW COLUMNS Syntax

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_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 column types differ from what you expect them to be based on your CREATE TABLE statement, note that MySQL sometimes changes column types when you create or alter a table. The conditions for which this occurs are described in Section 13.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 tbl_name FROM db_name syntax. In other words, these two statements are equivalent:

mysql> SHOW COLUMNS FROM mytable FROM mydb;
mysql> SHOW COLUMNS FROM mydb.mytable;

SHOW FIELDS is a synonym for SHOW COLUMNS. You can also list a table's columns with the mysqlshow db_name tbl_name command.

The DESCRIBE statement provides information similar to SHOW COLUMNS. See Section 13.3.1, “DESCRIBE Syntax (Get Information About Columns)”.

13.5.4.4. SHOW CREATE DATABASE Syntax

SHOW CREATE {DATABASE | SCHEMA} db_name

Shows a CREATE DATABASE statement that creates the given database. SHOW CREATE SCHEMA can be used as of MySQL 5.0.2.

mysql> SHOW CREATE DATABASE test\G
*************************** 1. row ***************************
       Database: test
Create Database: CREATE DATABASE `test`
                 /*!40100 DEFAULT CHARACTER SET latin1 */

mysql> SHOW CREATE SCHEMA test\G
*************************** 1. row ***************************
       Database: test
Create Database: CREATE DATABASE `test`
                 /*!40100 DEFAULT CHARACTER SET latin1 */

13.5.4.5. SHOW CREATE TABLE Syntax

SHOW CREATE TABLE tbl_name

Shows a 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”.

13.5.4.6. SHOW DATABASES Syntax

SHOW {DATABASES | SCHEMAS} [LIKE 'pattern']

SHOW DATABASES lists the databases on the MySQL server host. You can also get this list using the mysqlshow command. You see only those databases for which you have some kind of privilege, unless you have the global SHOW DATABASES privilege.

If the server was started with the --skip-show-database option, you cannot use this statement at all unless you have the SHOW DATABASES privilege.

SHOW SCHEMAS can be used as of MySQL 5.0.2

13.5.4.7. SHOW ENGINE Syntax

SHOW ENGINE engine_name {LOGS | STATUS }

SHOW ENGINE displays log or status information about storage engines. 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.

Older (and now deprecated) synonyms for these statements are SHOW [BDB] LOGS and SHOW INNODB STATUS.

SHOW ENGINE can be used as of MySQL 4.1.2.

13.5.4.8. SHOW ENGINES Syntax

SHOW [STORAGE] ENGINES

SHOW ENGINES shows you status information about the storage engines. This is particularly useful for checking whether a storage engine is supported, or to see what the default engine is. 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)

A Support value indicates whether the particular storage engine is supported, and which is the default engine. For example, if the server is started with the --default-table-type=InnoDB option, then the Support value for the InnoDB row has the value DEFAULT. See Chapter 14, Storage Engines and Table Types.

13.5.4.9. SHOW ERRORS Syntax

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.22, “SHOW WARNINGS Syntax”.

13.5.4.10. SHOW GRANTS Syntax

SHOW GRANTS FOR user

This statement lists the GRANT statements that must be issued to duplicate the privileges that are granted to a MySQL user 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.

13.5.4.11. SHOW INDEX Syntax

SHOW INDEX FROM tbl_name [FROM db_name]

SHOW INDEX returns table index information in a format that resembles the SQLStatistics call in ODBC.

SHOW INDEX returns the following fields:

  • Table

    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.

  • Collation

    How the column is sorted in the index. In MySQL, this can have values ‘A’ (Ascending) or NULL (Not sorted).

  • Cardinality

    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 tbl_name FROM db_name syntax. These two statements are equivalent:

mysql> SHOW INDEX FROM mytable FROM mydb;
mysql> SHOW INDEX FROM mydb.mytable;

SHOW KEYS is a synonym for SHOW INDEX. You can also list a table's indexes with the mysqlshow -k db_name tbl_name command.

13.5.4.12. SHOW INNODB STATUS Syntax

SHOW INNODB STATUS

In MySQL 5.0, this is a deprecated synonym for SHOW ENGINE INNODB STATUS. See Section 13.5.4.7, “SHOW ENGINE Syntax”.

13.5.4.13. SHOW LOGS Syntax

SHOW [BDB] LOGS

In MySQL 5.0, this is a deprecated synonym for SHOW ENGINE BDB LOGS. See Section 13.5.4.7, “SHOW ENGINE Syntax”.

13.5.4.14. SHOW OPEN TABLES Syntax

SHOW OPEN TABLES [FROM db_name] [LIKE 'pattern']

SHOW OPEN TABLES lists the non-TEMPORARY tables that are currently open in the table cache. See Section 7.4.9, “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 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.

13.5.4.15. SHOW PRIVILEGES Syntax

SHOW PRIVILEGES

SHOW PRIVILEGES shows the list of system privileges that the MySQL server supports. The exact output 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
*************************** 6. row ***************************
Privilege: Create view
Context: Tables
Comment: To create new views
*************************** 7. row ***************************
Privilege: Create user
Context: Server Admin
Comment: To create new users
*************************** 8. row ***************************
Privilege: Delete
Context: Tables
Comment: To delete existing rows
*************************** 9. row ***************************
Privilege: Drop
Context: Databases,Tables
Comment: To drop databases, tables, and views
*************************** 10. row ***************************
Privilege: Execute
Context: Functions,Procedures
Comment: To execute stored routines
*************************** 11. row ***************************
Privilege: File
Context: File access on server
Comment: To read and write files on the server
*************************** 12. row ***************************
Privilege: Grant option
Context: Databases,Tables,Functions,Procedures
Comment: To give to other users those privileges you possess
*************************** 13. row ***************************
Privilege: Index
Context: Tables
Comment: To create or drop indexes
*************************** 14. row ***************************
Privilege: Insert
Context: Tables
Comment: To insert data into tables
*************************** 15. row ***************************
Privilege: Lock tables
Context: Databases
Comment: To use LOCK TABLES (together with SELECT privilege)
*************************** 16. row ***************************
Privilege: Process
Context: Server Admin
Comment: To view the plain text of currently executing queries
*************************** 17. row ***************************
Privilege: References
Context: Databases,Tables
Comment: To have references on tables
*************************** 18. row ***************************
Privilege: Reload
Context: Server Admin
Comment: To reload or refresh tables, logs and privileges
*************************** 19. row ***************************
Privilege: Replication client
Context: Server Admin
Comment: To ask where the slave or master servers are
*************************** 20. row ***************************
Privilege: Replication slave
Context: Server Admin
Comment: To read binary log events from the master
*************************** 21. row ***************************
Privilege: Select
Context: Tables
Comment: To retrieve rows from table
*************************** 22. row ***************************
Privilege: Show databases
Context: Server Admin
Comment: To see all databases with SHOW DATABASES
*************************** 23. row ***************************
Privilege: Show view
Context: Tables
Comment: To see views with SHOW CREATE VIEW
*************************** 24. row ***************************
Privilege: Shutdown
Context: Server Admin
Comment: To shut down the server
*************************** 25. row ***************************
Privilege: Super
Context: Server Admin
Comment: To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.
*************************** 26. row ***************************
Privilege: Update
Context: Tables
Comment: To update existing rows
*************************** 27. row ***************************
Privilege: Usage
Context: Server Admin
Comment: No privileges - allow connect only

13.5.4.16. SHOW PROCESSLIST Syntax

SHOW [FULL] PROCESSLIST

SHOW PROCESSLIST shows you which threads are running. You can also get this information using the mysqladmin processlist statement. If you have the SUPER privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). See Section 13.5.5.3, “KILL Syntax”. If you do not use the FULL keyword, only the first 100 characters of each query are shown.

This statement reports the hostname for TCP/IP connections in host_name:client_port format to make it easier to determine which client is doing what.

This statement is very useful if you get the “too many connections” error message and want to find out what is going on. MySQL reserves one extra connection to be used by accounts that have the SUPER privilege, to ensure that administrators should always be able to connect and check the system (assuming that you are not giving this privilege to all your users).

Some states commonly seen in the output from SHOW PROCESSLIST:

  • Checking table

    The thread is performing (automatic) checking of the table.

  • 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, then you should verify that you do not have a full disk and that the disk is not in very heavy use.

  • Connect Out

    Slave connecting to 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 command 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.

  • System lock

    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 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 be able 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.

There are some other states that are not mentioned in the preceding list, but many of them are useful only for finding bugs in the server.

13.5.4.17. SHOW STATUS Syntax

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 variables and their values may be different for your server. The meaning of each variable is given in Section 5.3.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 GLOBAL, you get the status values for all connections to MySQL. With SESSION, you get the status values for the current connection. If you use neither option, the default is SESSION. LOCAL is a synonym for SESSION.

Note that some status variables have only a global value. For these you get the same value for both GLOBAL and SESSION.

13.5.4.18. SHOW TABLE STATUS Syntax

SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']

SHOW TABLE STATUS works likes SHOW TABLE, but provides a lot of information about each table. You can also get this list using the mysqlshow --status db_name command.

As of MySQL 5.0.1, this statement also displays information about views.

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

SHOW TABLE STATUS returns the following fields:

  • Name

    The name of the table.

  • Engine

    The storage engine for the table. Before MySQL 4.1.2, this value is labeled as Type. 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.

  • 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 (HEAP) tables, the Data_length, Max_data_length, and Index_length values approximate the actual amount of allocated memory. The allocation algorithm reserves memory in large amounts to reduce the number of allocation operations.

For views, all the fields displayed by SHOW TABLE STATUS are NULL except that Name indicates the view name and Comment says view.

13.5.4.19. SHOW TABLES Syntax

SHOW [FULL] TABLES [FROM db_name] [LIKE 'pattern']

SHOW TABLES lists the non-TEMPORARY tables in a given database. You can also get this list using the mysqlshow db_name command.

Before MySQL 5.0.1, the output from SHOW TABLES contains a single column of table names. Beginning with MySQL 5.0.1, this command 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.

13.5.4.20. SHOW TRIGGERS Syntax

SHOW TRIGGERS [FROM db_name] [LIKE expr]

SHOW TRIGGERS lists the triggers currently defined on the MySQL server. 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 invoking the trigger. Must be 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 invoked. 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 in order to execute SHOW TRIGGERS.

See also Section 20.1.16, “The INFORMATION_SCHEMA TRIGGERS Table”.

13.5.4.21. SHOW VARIABLES Syntax

SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern']

SHOW VARIABLES shows the values of some MySQL system variables. This information also can be obtained using the mysqladmin variables command.

With the GLOBAL option, you obtain the values that are used for new connections to MySQL. With SESSION, you get the values that are in effect for the current connection. If you use neither option, the default is SESSION.

LOCAL is a synonym for SESSION.

If the default values are unsuitable, you can set most of these variables using command-line options when mysqld starts or at runtime with the SET statement. See Section 5.3.1, “mysqld Command-Line Options” and Section 13.5.3, “SET Syntax”.

Partial output is shown here. The list of variables and their values may be different for your server. The meaning of each variable is given in Section 5.3.3, “Server System Variables”. Information about tuning them is provided in Section 7.5.2, “Tuning Server Parameters”.

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.7-beta-Max                                          |
| version_bdb                     | Sleepycat Software: Berkeley DB 4.1.24: (June 11, 2005) |
| 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:

mysql> SHOW VARIABLES LIKE 'have%';
+-----------------------+----------+
| Variable_name         | Value    |
+-----------------------+----------+
| have_archive          | NO       |
| have_bdb              | YES      |
| have_blackhole_engine | YES      |
| have_compress         | YES      |
| have_crypt            | YES      |
| have_csv              | YES      |
| have_example_engine   | YES      |
| have_federated_engine | YES      |
| have_geometry         | YES      |
| have_innodb           | YES      |
| have_isam             | NO       |
| have_ndbcluster       | DISABLED |
| have_openssl          | NO       |
| have_query_cache      | YES      |
| have_raid             | NO       |
| have_rtree_keys       | YES      |
| have_symlink          | YES      |
+-----------------------+----------+ 

13.5.4.22. SHOW WARNINGS Syntax

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.9, “SHOW ERRORS Syntax”.

The list of messages is reset for each new statement that uses a table.

The SHOW COUNT(*) WARNINGS statement displays the total number of errors, warnings, and notes. You can also retrieve this number from the warning_count variable:

SHOW COUNT(*) WARNINGS;
SELECT @@warning_count;

The value of warning_count might be greater than the number of messages displayed by SHOW WARNINGS if the max_error_count system variable is set 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.

13.5.5. Other Administrative Statements

13.5.5.1. CACHE INDEX Syntax

CACHE INDEX
  tbl_index_list [, tbl_index_list] ...
  IN key_cache_name

tbl_index_list:
  tbl_name [[INDEX|KEY] (index_name[, index_name] ...)]

The CACHE INDEX statement assigns table indexes to a specific key cache. It is used only for MyISAM tables.

The following statement assigns indexes from the tables t1, t2, and t3 to the key cache named hot_cache:

mysql> CACHE INDEX t1, t2, t3 IN hot_cache;
+---------+--------------------+----------+----------+
| Table   | Op                 | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status   | OK       |
| test.t2 | assign_to_keycache | status   | OK       |
| test.t3 | assign_to_keycache | status   | OK       |
+---------+--------------------+----------+----------+

The syntax of CACHE INDEX allows you to specify that only particular indexes from a table should be assigned to the cache. However, the current implementation assigns all the table's indexes to the cache, so there is no reason to specify anything other than the table name.

The key cache referred to in a CACHE INDEX statement can be created by setting its size with a parameter setting statement or in the server parameter settings. For example:

mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;

Key cache parameters can be accessed as members of a structured system variable. See Section 9.4.1, “Structured System Variables”.

A key cache must exist before you can assign indexes to it:

mysql> CACHE INDEX t1 IN non_existent_cache;
ERROR 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 what client issues the queries.

13.5.5.2. FLUSH Syntax

FLUSH [LOCAL | NO_WRITE_TO_BINLOG] flush_option [, flush_option] ...

You should use the FLUSH statement if you want to clear some of the internal caches MySQL uses. To execute FLUSH, you must have the RELOAD privilege.

flush_option can be any of the following:

  • HOSTS

    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 host_name is blocked. When more than max_connect_errors errors occur successively for a given host while connecting to the MySQL server, MySQL assumes that something is wrong and blocks the host from further connection requests. Flushing the host tables allows the host to attempt to connect again. See Section A.2.5, “Host 'host_name' is blocked. You can start mysqld with --max_connect_errors=999999999 to avoid this error message.

  • DES_KEY_FILE

    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 you have specified an update log file or a binary log file without an extension, the extension number of the log file is incremented by one relative to the previous file. If you have used an extension in the file name, MySQL closes and reopens the log 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. This statement 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.7.1.3, “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 and REVOKE Syntax”.

FLUSH statements are written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) is used. 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.16. As of MySQL 5.0.12, it is no longer possible to do so at all. 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.

13.5.5.3. KILL Syntax

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 thread_id statement.

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 do a KILL, a thread-specific kill flag is set for the thread. In most cases, it might take some time for the thread to die, because the kill flag is checked only at specific intervals:

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

13.5.5.4. LOAD INDEX INTO CACHE Syntax

LOAD INDEX INTO CACHE
  tbl_index_list [, tbl_index_list] ...

tbl_index_list:
  tbl_name
    [[INDEX|KEY] (index_name[, index_name] ...)]
    [IGNORE LEAVES]

The LOAD INDEX INTO CACHE statement preloads a table index into the key cache to which it has been assigned by an explicit CACHE INDEX statement, or into the default key cache otherwise. LOAD INDEX INTO CACHE is used only for MyISAM tables.

The IGNORE LEAVES modifier causes only blocks for the non-leaf nodes of the index to be preloaded.

The following statement preloads nodes (index blocks) of indexes 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 allows you to specify that only particular indexes from a table should be preloaded. However, the current implementation preloads all the table's indexes into the cache, so there is no reason to specify anything other than the table name.

13.5.5.5. RESET Syntax

RESET reset_option [, reset_option] ...

The RESET statement is used to clear the state of various server operations. It also acts as a stronger version of the FLUSH statement. See Section 13.5.5.2, “FLUSH Syntax”.

You must have the RELOAD privilege in order to execute RESET.

reset_option can be any of the following:

13.6. Replication Statements

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.

13.6.1. SQL Statements for Controlling Master 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.

13.6.1.1. PURGE MASTER LOGS Syntax

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.

If you have an active slave that currently is reading one of the logs you are trying to delete, this statement does nothing and fails with an error. However, if a slave is dormant and you happen to purge one of the logs it wants to read, the slave is unable to replicate once it comes up. The statement is safe to run while slaves are replicating. You do not need to stop them.

To purge logs, follow this procedure:

  1. On each slave server, use SHOW SLAVE STATUS to check which log it is reading.

  2. Obtain a listing of the logs on the master server with SHOW MASTER LOGS.

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

  4. Make a backup of all the logs you are about to delete. (This step is optional, but always advisable.)

  5. Purge all logs up to but not including the target log.

13.6.1.2. RESET MASTER Syntax

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.

13.6.1.3. SET SQL_LOG_BIN Syntax

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 connects using an account that has the SUPER privilege. A statement is refused with an error if the client does not have that privilege.

13.6.1.4. SHOW BINLOG EVENTS Syntax

SHOW BINLOG EVENTS
   [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

Shows the events in the binary log. If you do not specify 'log_name', the first binary log is displayed.

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 as the server dumps the complete contents of the binary log (which includes most of the queries executed by MySQL) to stdout. To save the binary log to a text file for later examination and analysis, use the mysqlbinlog utility. See Section 8.6, “mysqlbinlog — Utility for Processing Binary Log Files”.

13.6.1.5. SHOW MASTER LOGS Syntax

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” for determining 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.

13.6.1.6. SHOW MASTER STATUS Syntax

SHOW MASTER STATUS

Provides status information on 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     |
+---------------+----------+--------------+------------------+

13.6.1.7. SHOW SLAVE HOSTS Syntax

SHOW SLAVE HOSTS

Displays a list of replication slaves currently registered with the master. Any slave not started with the --report-host=slave_name option is not visible in this list.

13.6.2. SQL Statements for Controlling Slave Servers

Replication can be controlled through the SQL interface. This section discusses statements for managing slave replication servers. Section 13.6.1, “SQL Statements for Controlling Master Servers” discusses statements for managing master servers.

13.6.2.1. CHANGE MASTER TO Syntax

  CHANGE MASTER TO master_def [, master_def] ...

master_def:
      MASTER_HOST = 'host_name'
    | MASTER_USER = 'user_name'
    | MASTER_PASSWORD = 'password'
    | MASTER_PORT = port_num
    | MASTER_CONNECT_RETRY = count
    | MASTER_LOG_FILE = 'master_log_name'
    | MASTER_LOG_POS = master_log_pos
    | RELAY_LOG_FILE = 'relay_log_name'
    | RELAY_LOG_POS = relay_log_pos
    | MASTER_SSL = {0|1}
    | MASTER_SSL_CA = 'ca_file_name'
    | MASTER_SSL_CAPATH = 'ca_directory_name'
    | MASTER_SSL_CERT = 'cert_file_name'
    | MASTER_SSL_KEY = 'key_file_name'
    | MASTER_SSL_CIPHER = 'cipher_list'

Changes the parameters that the slave server uses for connecting to and communicating with the master server.

MASTER_USER, MASTER_PASSWORD, MASTER_SSL, MASTER_SSL_CA, MASTER_SSL_CAPATH, MASTER_SSL_CERT, MASTER_SSL_KEY, and MASTER_SSL_CIPHER provide information 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 until you use a server that has SSL support enabled.

If you don't specify a given parameter, it keeps its old value, except as indicated in the following discussion. For example, if the password to connect to your MySQL master has changed, you just need to issue these statements to tell the slave about the new password:

mysql> STOP SLAVE; -- if replication was running
mysql> CHANGE MASTER TO MASTER_PASSWORD='new3cret';
mysql> START SLAVE; -- if you want to restart replication

There is no need to specify the parameters that do not change (host, port, user, and so forth).

MASTER_HOST and MASTER_PORT are the hostname (or IP address) of the master host and its TCP/IP port. Note that if MASTER_HOST is equal to localhost, then, like in other parts of MySQL, the port may be ignored (if Unix socket files can be used, for example).

If you specify MASTER_HOST or MASTER_PORT, the slave assumes that the master server is different than before (even if you specify a host or port value that is the same as the current value.) In this case, the old values for the master binary log name and position are considered no longer applicable, so if you do not specify MASTER_LOG_FILE and MASTER_LOG_POS in the statement, MASTER_LOG_FILE='' and MASTER_LOG_POS=4 are silently appended to it.

MASTER_LOG_FILE and MASTER_LOG_POS are the coordinates at which the slave I/O thread should begin reading from the master the next time the thread starts. If you specify either of them, you 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 TO updates the contents of the master.info and relay-log.info files.

CHANGE MASTER is useful for setting up a slave when you have the snapshot of the master and have recorded the log and the offset corresponding to it. After loading the snapshot into the slave, you can run CHANGE MASTER TO MASTER_LOG_FILE='log_name_on_master', MASTER_LOG_POS=log_offset_on_master on the slave.

Examples:

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master2.mycompany.com',
    ->     MASTER_USER='replication',
    ->     MASTER_PASSWORD='bigs3cret',
    ->     MASTER_PORT=3306,
    ->     MASTER_LOG_FILE='master2-bin.001',
    ->     MASTER_LOG_POS=4,
    ->     MASTER_CONNECT_RETRY=10;

mysql> CHANGE MASTER TO
    ->     RELAY_LOG_FILE='slave-relay-bin.006',
    ->     RELAY_LOG_POS=4025;

The first example changes the master and master's binary log coordinates. This is used when you want to set up the slave to replicate the master.

The second example shows an operation that is less frequently 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).

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. Once the server starts, issue these statements:

mysql> CHANGE MASTER TO
    ->     RELAY_LOG_FILE='myhost-bin.153',
    ->     RELAY_LOG_POS=410,
    ->     MASTER_HOST='some_dummy_string';
mysql> START SLAVE SQL_THREAD;

The server reads and executes its own binary logs, thus achieving crash recovery. Once the recovery is finished, run STOP SLAVE, shut down the server, delete master.info and relay-log.info, and restart the server with its original options.

Specifying MASTER_HOST (even with a dummy value) is required to make the server think it is a slave.

13.6.2.2. LOAD DATA FROM MASTER Syntax

LOAD DATA FROM MASTER

This command 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. This is because a user could, with this option, set up a non-unique mapping such as --replicate-rewrite-db=db1->db3 and --replicate-rewrite-db=db2->db3, which would confuse the slave when loading tables from the master.

Use of this statement is subject to the following conditions:

  • It works only with 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.3.3, “Server System Variables”.

Note that LOAD DATA FROM MASTER does not copy any tables from the mysql database. This makes it easy to have different users and privileges on the master and the slave.

The LOAD DATA FROM MASTER statement requires the replication account that is used to connect to the master to have the RELOAD and SUPER privileges on the master and the SELECT privilege for all master tables you want to load. All master tables for which the user does not have the SELECT privilege are ignored by LOAD DATA FROM MASTER. This is because the master 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.6, “SHOW DATABASES Syntax”. On the slave side, the user that issues LOAD DATA FROM MASTER should have grants to drop and create the databases and tables that are copied.

13.6.2.3. LOAD TABLE tbl_name FROM MASTER Syntax

LOAD TABLE tbl_name FROM MASTER

Transfers a copy of the table from the master to the slave. This statement is implemented mainly for debugging of LOAD DATA FROM MASTER. It requires that the account used for connecting to the master server has the RELOAD and SUPER privileges on the master and the SELECT privilege on the master table to load. On the slave side, the user that issues LOAD TABLE FROM MASTER should have privileges to drop and create the table.

The conditions for LOAD DATA FROM MASTER apply here as well. For example, LOAD TABLE FROM MASTER works only for MyISAM tables. The timeout notes for LOAD DATA FROM MASTER apply as well.

13.6.2.4. MASTER_POS_WAIT() Syntax

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.

13.6.2.5. RESET SLAVE Syntax

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.

13.6.2.6. SET GLOBAL SQL_SLAVE_SKIP_COUNTER Syntax

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n

Skip the next n events from the master. This is useful for recovering from replication stops caused by a statement.

This statement is valid only when the slave thread is not running. Otherwise, it produces an error.

13.6.2.7. SHOW SLAVE STATUS Syntax

SHOW SLAVE STATUS

Provides status information on essential parameters of the slave threads. If you issue this statement using the mysql client, you can use a \G statement terminator rather than 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 if the thread is trying to connect to the master, waiting for events from the master, reconnecting to the master, and so on. Possible states are listed in Section 6.3, “Replication Implementation Details”. Looking at this field is necessary 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 or not 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 will be set to YES if the I/O thread is started, even if the slave hasn't connected to the master yet.

  • Slave_SQL_Running

    Whether or not 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 that thread has caught up on the slave I/O thread and goes idle waiting for more events from the I/O thread this field is zero. In sum, this field measures in seconds the time difference 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 master-connect-retry seconds 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, then the value randomly deviates, because sometimes the last M1's event is from M0 and sometimes it is from a direct update, and so is the most recent timestamp.

13.6.2.8. START SLAVE Syntax

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 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 satisfactorilly 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 previous versions of MySQL, this statement was called SLAVE START. This usage is still accepted in MySQL 5.0 for backward compatibility, but is now deprecated.

13.6.2.9. STOP SLAVE Syntax

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 previous versions of MySQL, this statement was called SLAVE STOP. This usage is still accepted in MySQL 5.0 for backward compatibility, but is now deprecated.

13.7. SQL Syntax for Prepared Statements

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 may want to test how prepared statements work in your application before coding it, or perhaps 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;

EXECUTE stmt_name [USING @var_name [, @var_name] ...];

{DEALLOCATE | DROP} PREPARE stmt_name;

The PREPARE statement prepares a statement and assigns it a name, stmt_name, by which to refer to the statement later. Statement names are not case sensitive. preparable_stmt is either a string literal or a user variable that contains the text of the statement. The text must represent a single SQL statement, not multiple statements. Within the statement, ‘?’ characters can be used as parameter markers to indicate where data values are to be bound to the query later when you execute it. The ‘?’ characters should not be enclosed within quotes, even if you intend to bind them to string values. Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.

If a prepared statement with that 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.

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.

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 in the LIMIT clause when using prepared statements. See Section 13.2.7, “SELECT Syntax”.