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

13 SQL Statement Syntax

This chapter describes the syntax for the SQL statements supported in MySQL.

13.1 Data Manipulation Statements

13.1.1 DELETE Syntax

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

or: 

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] table_name[.*] [, table_name[.*] ...]
       FROM table-references
       [WHERE where_definition]

or: 

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
       FROM table_name[.*] [, table_name[.*] ...]
       USING table-references
       [WHERE where_definition]

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

If you issue a DELETE with no WHERE clause, all rows are deleted. If you do this in AUTOCOMMIT mode, this works as TRUNCATE. See section 13.1.9 TRUNCATE Syntax. In MySQL 3.23, DELETE without a WHERE clause will return zero as the number of affected records.

If you really want to know how many records are deleted when you are deleting all rows, and are willing to suffer a speed penalty, you can use a DELETE statement of this form: 

mysql> DELETE FROM table_name WHERE 1>0;

Note that this is much slower than DELETE FROM table_name with no WHERE clause, because it deletes rows one at a time.

If you specify the keyword LOW_PRIORITY, execution of the DELETE is delayed until no other clients are reading from the table.

For MyISAM tables, if you specify the word QUICK then the storage engine will not merge index leaves during delete, which may speed up certain kind of deletes.

The speed of delete operations may also be affected by factors discussed in section 7.2.13 Speed of DELETE Queries.

Option IGNORE causes MySQL to ignore all errors during the process of deleting rows. Errors encountered during the parsing stage are processed in the usual manner. Errors that are ignored due to the use of this option are returned as warnings. This option first appeared in version 4.1.1.

In MyISAM tables, deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. To reclaim unused space and reduce file-sizes, use the OPTIMIZE TABLE statement or the myisamchk utility to reorganize tables. OPTIMIZE TABLE is easier, but myisamchk is faster. See section 13.5.2.5 OPTIMIZE TABLE Syntax and section 5.5.2.10 Table Optimization.

The first multiple-table delete format is supported starting from MySQL 4.0.0. The second multiple-table delete format is supported starting from MySQL 4.0.2.

The idea is that only matching rows from the tables listed before the FROM or before the USING clause are deleted. The effect is that you can delete rows from many tables at the same time and also have additional tables that are used for searching.

The .* after the table names is there just to be compatible with Access: 

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

In the above case we delete matching rows only from tables t1 and t2.

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

If an ORDER BY clause is used (available from MySQL 4.0.0), the rows will be deleted in that order. This is really only useful in conjunction with LIMIT. For example: 

DELETE FROM somelog
WHERE user = 'jcole'
ORDER BY timestamp
LIMIT 1

This will delete the oldest entry (by timestamp) where the row matches the WHERE clause.

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 command doesn't take too much time. You can simply repeat the DELETE command until the number of affected rows is less than the LIMIT value.

From MySQL 4.0, you can specify multiple tables in the DELETE statement to delete rows from one or more tables depending on a particular condition in multiple tables. However, you cannot use ORDER BY or LIMIT in a multiple-table DELETE.

13.1.2 DO Syntax

DO expression, [expression, ...]

Execute the expression but don't return any results. This is a shorthand of SELECT expression, expression, but has the advantage that it's slightly faster when you don't care about the result.

This is mainly useful with functions that has side effects, like RELEASE_LOCK.

13.1.3 HANDLER Syntax

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

The HANDLER statement provides direct access to the MyISAM table storage engine interface.

The first form of HANDLER statement opens a table, making it accessible via subsequent HANDLER ... READ statements. This table object is not shared by other threads and will not be closed until the thread calls HANDLER tbl_name CLOSE or the thread dies.

The second form fetches one row (or more, specified by LIMIT clause) 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 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 third form fetches one row (or more, specified by LIMIT clause) from the table in index order, matching WHERE condition.

The fourth form (without index specification) fetches one row (or more, specified by LIMIT clause) from the table in natural row order (as stored in datafile) matching WHERE condition. It is faster than HANDLER tbl_name READ index_name when a full table scan is desired.

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

Note: If you're using HANDLER interface for PRIMARY KEY you should remember to quote the keyword PRIMARY with backticks: HANDLER tbl 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 is issued, table data can be modified (by this or any other thread) and these modifications may appear only partially in HANDLER ... NEXT or HANDLER ... PREV scans.

The reasons to use this interface instead of normal SQL are:

13.1.4 INSERT Syntax

    INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        VALUES ((expression | DEFAULT),...),(...),...
        [ ON DUPLICATE KEY UPDATE col_name=expression, ... ]
or  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...
or  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name
        SET col_name=(expression | DEFAULT), ...
        [ ON DUPLICATE KEY UPDATE col_name=expression, ... ]

INSERT inserts new rows into an existing table. The INSERT ... VALUES form of the statement inserts rows based on explicitly specified values. The INSERT ... SELECT form inserts rows selected from another table or tables. The INSERT ... VALUES form with multiple value lists is supported in MySQL Version 3.22.5 or later. The col_name=expression syntax is supported in MySQL Version 3.22.10 or later.

tbl_name is the table into which rows should be inserted. The column name list or the SET clause indicates which columns the statement specifies values for:

If you use INSERT ... SELECT or an INSERT ... VALUES statement with multiple value lists, you can use the C API function mysql_info() to get information about the query. The format of the information string is shown here: 

Records: 100 Duplicates: 0 Warnings: 0

Duplicates indicates the number of rows that couldn't be inserted because they would duplicate some existing unique index value. Warnings indicates the number of attempts to insert column values that were problematic in some way. Warnings can occur under any of the following conditions:

13.1.4.1 INSERT ... SELECT Syntax

INSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(column list)] SELECT ...

With INSERT ... SELECT statement you can quickly insert many rows into a table from one or many tables. 

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE
tblTemp1.fldOrder_ID > 100;

The following conditions hold for an INSERT ... SELECT statement:

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.1.4.2 INSERT DELAYED Syntax

INSERT DELAYED ...

The DELAYED option for the INSERT statement is a MySQL-specific option that is very useful if you have clients that can't wait for the INSERT to complete. This is a common problem when you use MySQL for logging and you also periodically run SELECT and UPDATE statements that take a long time to complete. DELAYED was introduced in MySQL Version 3.22.15. It is a MySQL extension to SQL-92.

INSERT DELAYED only works with ISAM and MyISAM tables. Note that as MyISAM tables support concurrent SELECT and INSERT, if there is no free blocks in the middle of the datafile, you very seldom need to use INSERT DELAYED with MyISAM. See section 14.1 MyISAM Tables.

INSERT DELAYED should be used only for INSERT statements that specify value lists. This is enforced as of MySQL 4.0.18; the server ignores DELAYED for INSERT DELAYED ... SELECT statements.

When you use INSERT DELAYED, the client will get an OK at once and the row will be inserted when the table is not in use by any other thread.

Another major benefit of using INSERT DELAYED is that inserts from many clients are bundled together and written in one block. This is much faster than doing many separate inserts.

Note that currently the queued rows are only stored in memory until they are inserted into the table. This means that if you kill mysqld the hard way (kill -9) or if mysqld dies unexpectedly, any queued rows that weren't 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 command and ``handler'' is the thread that handles all INSERT DELAYED statements for a particular table.

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 on which you use INSERT DELAYED. This means that you should only use INSERT DELAYED when you are really sure you need it!

13.1.5 LOAD DATA INFILE Syntax

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [[OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\' ]
    ]
    [LINES 
        [STARTING BY '']    
        [TERMINATED BY '\n']
    ]
    [IGNORE number LINES]
    [(col_name,...)]

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. If the LOCAL keyword is specified, it is interpreted with respect to the client end of the connection. When LOCAL is specified, the file is read by the client program on the client host and sent to the server. If LOCAL is not specified, the file must be located on the server host and is read directly by the server. (LOCAL is available in MySQL Version 3.22.6 or later.)

For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege on the server host. See section 5.3.7 Privileges Provided by MySQL.

As of MySQL 3.23.49 and MySQL 4.0.2 (4.0.13 on Windows), LOCAL will work only if your server and your client both have been enabled to allow it. For example, if mysqld was started with --local-infile=0, LOCAL will not work. See section 5.3.4 Security issues with LOAD DATA LOCAL.

If you specify the keyword LOW_PRIORITY, execution of the LOAD DATA statement is delayed until no other clients are reading from the table.

If you specify the keyword CONCURRENT with a MyISAM table, then other threads can retrieve data from the table while LOAD DATA is executing. Using this option will affect the performance of LOAD DATA a bit even if no other thread is using the table at the same time.

Using LOCAL will be 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.

If you are using MySQL before Version 3.23.24 you can't read from a FIFO with LOAD DATA INFILE. If you need to read from a FIFO (for example the output from gunzip), use LOAD DATA LOCAL INFILE instead.

You can also load datafiles by using the mysqlimport utility; it operates by sending a LOAD DATA INFILE command to the server. The --local option causes mysqlimport to read datafiles 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.

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

Note that these rules mean 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 current database. For example, the following LOAD DATA statement reads the file `data.txt' from the database directory for db1 because db1 is the current database, even though the statement explicitly loads the file into a table in the db2 database: 

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

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

If you specify REPLACE, input rows replace existing rows (in other words rows that has the same value for a primary or unique index as an existing row). See section 13.1.6 REPLACE Syntax.

If you specify IGNORE, input rows that duplicate an existing row on a unique key value are skipped. If you don't specify either option, the behavior depends on whether or not the LOCAL keyword is specified. Without LOCAL, an error occurs when a duplicate key value is found, and the rest of the text file is ignored. With LOCAL, the default behavior is the same as if IGNORE is specified; this is because the server has no way to stop transmission of the file in the middle of the operation.

If you want to ignore foreign key constraints during load you can do SET FOREIGN_KEY_CHECKS=0 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 (like in REPAIR). 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 and use ALTER TABLE .. ENABLE KEYS to recreate the indexes. See section 5.5.2 Using myisamchk for Table Maintenance and Crash Recovery.

LOAD DATA INFILE is the complement of SELECT ... INTO OUTFILE. See section 13.1.7 SELECT Syntax. To write data from a table to a file, use SELECT ... INTO OUTFILE. To read the file back into a table, use LOAD DATA INFILE. The syntax of the FIELDS and LINES clauses is the same for both commands. Both clauses are optional, but FIELDS must precede LINES if both are specified.

If you specify a FIELDS clause, each of its subclauses (TERMINATED BY, [OPTIONALLY] ENCLOSED BY, and ESCAPED BY) is also optional, except that you must specify at least one of them.

If you don't specify a FIELDS clause, the defaults are the same as if you had written this: 

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

If you don't specify a LINES clause, the default is the same as if you had written this: 

LINES TERMINATED BY '\n'

Note: If you have generated the text file on a Windows system you may have to change the above to: LINES TERMINATED BY '\r\n' as Windows uses two characters as a line terminator. Some programs, like wordpad, may use \r as a line terminator.

If all the lines you want to read in has a common prefix that you want to skip, you can use LINES STARTING BY prefix_string for this.

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

Conversely, the defaults cause SELECT ... INTO OUTFILE to act as follows when writing output:

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

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/file_name" 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 commands must match. Otherwise, LOAD DATA INFILE will not interpret the contents of the file properly. Suppose you use SELECT ... INTO OUTFILE to write a file with fields delimited by commas: 

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

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

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

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

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

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

LOAD DATA INFILE can be used to read files obtained from external sources, too. For example, a file in dBASE format will have fields separated by commas and enclosed in double quotes. If lines in the file are terminated by newlines, the command 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 may 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 and LINES TERMINATED BY values may 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.

For example, to read a file of jokes, that are separated with a line of %%, into an SQL table you can do: 

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

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

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

If you specify OPTIONALLY, the ENCLOSED BY character is used only to enclose CHAR and VARCHAR fields: 

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

Note that occurrences of the ENCLOSED BY character within a field value are escaped by prefixing them with the ESCAPED BY character. Also note that if you specify an empty ESCAPED BY value, it is possible to generate output that cannot be read properly by LOAD DATA INFILE. For example, the preceding output just shown would appear as follows if the escape character is empty. Observe that the second field in the fourth line contains a comma following the quote, which (erroneously) appears to terminate the field: 

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

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

If the field begins with the ENCLOSED BY character, instances of that character are recognized as terminating a field value only if followed by the field or line TERMINATED BY sequence. To avoid ambiguity, occurrences of the ENCLOSED BY character within a field value can be doubled and will be interpreted as a single instance of the character. For example, if ENCLOSED BY '"' is specified, quotes are handled as shown here: 

"The ""BIG"" boss"  -> The "BIG" boss
The "BIG" boss      -> The "BIG" boss
The ""BIG"" boss    -> The ""BIG"" boss

FIELDS ESCAPED BY controls how to write or read special characters. If the FIELDS ESCAPED BY character is not empty, it is used to prefix the following characters on output:

If the FIELDS ESCAPED BY character is empty, no characters are escaped. 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 0 (a zero-valued byte) and NULL. See below for the rules on NULL handling.

For more information about `\'-escape syntax, see section 10.1 Literal Values.

In certain cases, field and line handling options interact:

Handling of NULL values varies, depending on the FIELDS and LINES options you use:

Some cases are not supported by LOAD DATA INFILE:

The following example loads all columns of the persondata table: 

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

No field list is specified, so LOAD DATA INFILE expects input rows to contain a field for each table column. The default FIELDS and LINES values are used.

If you wish to load only some of a table's columns, specify a field list: 

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

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

If a row has too few fields, the columns for which no input field is present are set to default values. Default value assignment is described in section 13.2.5 CREATE TABLE Syntax.

An empty field value is interpreted differently than if the field value is missing:

Note that 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 only set to the current date and time 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.

If an input row has too many fields, the extra fields are ignored and the number of warnings is incremented. Note that before MySQL 4.1.1 the warnings is just a number to indicate that something went wrong. In MySQL 4.1.1 you can do SHOW WARNINGS to get more information for what went wrong.

LOAD DATA INFILE regards all input as strings, so you can't use numeric values for ENUM or SET columns the way you can with INSERT statements. All ENUM and SET values must be specified as strings!

If you are using the C API, you can get information about the query by calling the API function mysql_info() when the LOAD DATA INFILE query finishes. The format of the information string is shown here: 

Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

Warnings occur under the same circumstances as when values are inserted via the INSERT statement (see section 13.1.4 INSERT Syntax), except that LOAD DATA INFILE also generates warnings when there are too few or too many fields in the input row. The warnings are not stored anywhere; the number of warnings can only be used as an indication if everything went well.

If you get warnings and want to know exactly why you got them, one way to do this is to use SELECT ... INTO OUTFILE into another file and compare this to your original input file.

If you need LOAD DATA to read from a pipe, you can use the following trick: 

mkfifo /mysql/db/x/x
chmod 666 /mysql/db/x/x
cat < /dev/tcp/10.1.1.12/4711 > /nt/mysql/db/x/x
mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x

If you are using a version of MySQL older than 3.23.25 you can only do the above with LOAD DATA LOCAL INFILE.

In MySQL 4.1.1 you can use SHOW WARNINGS to get a list of the first max_error_count warnings. See section 13.5.3.9 SHOW WARNINGS | ERRORS.

For more information about the efficiency of INSERT versus LOAD DATA INFILE and speeding up LOAD DATA INFILE, See section 7.2.11 Speed of INSERT Queries.

13.1.6 REPLACE Syntax

    REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name [(col_name,...)]
        VALUES (expression,...),(...),...
or  REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...
or  REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name
        SET col_name=expression, col_name=expression,...

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

In other words, you can't access the values of the old row from a REPLACE statement. In some old MySQL versions it appeared that you could do this, but that was a bug that has been corrected.

To be able to use REPLACE you must have INSERT and DELETE privileges for the table.

When you use a REPLACE command, mysql_affected_rows() will return 2 if the new row replaced an old row. This is because one row was inserted after the duplicate was deleted.

This fact makes it easy to determine whether REPLACE added or replaced a row: check whether the affected-rows value is 1 (added) or 2 (replaced).

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

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

- Insert the row into the table
  - While duplicate key error for primary or unique key
    - Revert changed keys
    - Read conflicting row from the table through the duplicate key value
    - Delete conflicting row
    - Try again to insert the original primary key and unique keys in the tree

13.1.7 SELECT Syntax

SELECT [STRAIGHT_JOIN]
       [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
       [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
       [DISTINCT | DISTINCTROW | ALL]
    select_expression,...
    [INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
    [FROM table_references
      [WHERE where_definition]
      [GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...
        [WITH ROLLUP]]
      [HAVING where_definition]
      [ORDER BY {unsigned_integer | col_name | formula} [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. Each select_expression indicates a column you want to retrieve. SELECT may 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.

13.1.7.1 JOIN Syntax

MySQL supports the following JOIN syntaxes for use in SELECT statements: 

table_reference, table_reference
table_reference [INNER | CROSS] JOIN table_reference [join_condition]
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference [join_condition]
table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
{ OJ table_reference LEFT OUTER JOIN table_reference ON conditional_expr }
table_reference RIGHT [OUTER] JOIN table_reference [join_condition]
table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

Where table_reference is defined as: 

table_name [[AS] alias] [[USE INDEX (key_list)] | [IGNORE INDEX (key_list)] | [FORCE INDEX (key_list)]]

and join_condition is defined as: 

ON conditional_expr |
USING (column_list)

You should generally not have any conditions in the ON part that are used to restrict which rows you want in the result set, but rather specify these conditions in the WHERE clause. There are exceptions to this rule.

Note that INNER JOIN syntax allows a join_condition only from MySQL 3.23.17 on. The same is true for JOIN and CROSS JOIN only as of MySQL 4.0.11.

The last LEFT OUTER JOIN syntax shown in the preceding list exists only for compatibility with ODBC:

Note: USE/IGNORE/FORCE INDEX only affects which indexes are used when MySQL decides how to find rows in the table and how to do the join. It doesn't affect whether an index will be used when resolving an ORDER BY or GROUP BY.

Some 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.8 How MySQL Optimizes LEFT JOIN and RIGHT JOIN.

13.1.7.2 UNION Syntax

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

UNION is implemented in MySQL 4.0.0.

UNION is used to combine the result from many SELECT statements into one result set.

The columns listed in the select_expression portion of the SELECT should have the same type. The column names used in the first SELECT query will be used as the column names for the results returned.

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

If you don't use the keyword ALL for the UNION, all returned rows will be unique, as if you had done a DISTINCT for the total result set. If you specify ALL, then you will get all matching rows from all the used SELECT statements.

The DISTINCT keyword is an optional word (introduced in MySQL 4.0.17) that does nothing. But it is required by the SQL standard.

If you want to use an ORDER BY for the total UNION result, you should use parentheses: 

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

Note: You cannot mix UNION ALL and UNION DISTINCT in the same query yet. If you use ALL for one UNION then it is used for all of them.

The types and lengths of the columns in the result set of a UNION take into acccount the values retrieved by all the SELECT statements. Before MySQL 4.1.1, a limitation of UNION is that only the values from the first SELECT were used to determine result types and lengths. This could result in value truncation if, for example, the second SELECT retrieved longer values than the first SELECT: 

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a             |
| b             |
+---------------+

That limitation has been removed as of MySQL 4.1.1: 

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a             |
| bbbbbbbbbb    |
+---------------+

13.1.8 Subquery Syntax

A subquery is a SELECT statement inside another statement. For example: 

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

In the above example, SELECT * FROM t1 ... is the outer query (or outer statement), and (SELECT column1 FROM t2) is the subquery. We say that the subquery is nested in the outer query, and in fact it's possible to nest subqueries within other subqueries, to a great depth. A subquery must always be inside parentheses.

Starting with version 4.1, MySQL supports all subquery forms and operations which the SQL standard requires, as well as a few features which are MySQL-specific. The main advantages of subqueries are:

With earlier MySQL versions it was necessary to work around or avoid subqueries, but people starting to write code now will find that subqueries are a very useful part of the toolkit.

Here is an example statement which 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)));

For MySQL versions prior to 4.1, most subqueries can be successfully rewritten using joins and and other methods. See section 13.1.8.11 Rewriting Subqueries for Earlier MySQL Versions.

13.1.8.1 The Subquery as Scalar Operand

In its simplest form (the scalar subquery as opposed to the row or table subqueries which will be discussed later), a subquery is a simple operand. Thus you can use it wherever a 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);
SELECT (SELECT s2 FROM t1);

The subquery in the above SELECT 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, because if the table is empty -- as in the example -- then the value of the subquery will be NULL. There are few restrictions.

So, when you see examples in the following sections that contain the rather Spartan construct (SELECT column1 FROM t1), imagine that your own code will contain much more diverse and complex constructions.

For example, suppose we make two tables: 

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

Then perform a SELECT: 

SELECT (SELECT s1 FROM t2) FROM t1;

The result will be 2 because there is a row in t2, with a column s1, with a value of 2.

The subquery may be part of an expression. If it is an operand for a function, don't forget the parentheses. For example: 

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

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

= > < >= <= <>

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 which insist on that.

Here is an example of a common-form subquery comparison which you can't do with a join: find all the values in table t1 which 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: find all rows in table t1 which contain a value which occurs twice. 

SELECT * FROM t1
       WHERE 2 = (SELECT COUNT(column1) FROM t1);

13.1.8.3 Subqueries with ANY, IN, and SOME

Syntax: 

<operand> <comparison operator> ANY (<subquery>)
<operand> IN (<subquery>)
<operand> <comparison operator> SOME (<subquery>)

The word ANY, which must follow a comparison operator, means ``return TRUE if the comparison is TRUE for ANY of the rows 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 in t2 -- 7 -- which 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);

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 the above example shows why it might be useful. The English phrase ``a is not equal to any b'' means, to most people's ears, ``there is no b which is equal to a'' -- which isn't what is meant by the SQL syntax. By using <> SOME instead, you ensure that everyone understands the true meaning of the query.

13.1.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 rows 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 all three values in t2 are less than 10. The expression is FALSE if table t2 contains {12,6,NULL,-100} because there is a single value in table t2 -- 12 -- which is greater than 10. The expression is UNKNOWN if table t2 contains {0,NULL,1}.

Finally, if table t2 is empty, the result is TRUE. You might think the result should be UNKNOWN, but sorry, it's TRUE. So, rather oddly, 

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

is TRUE when table t2 is empty, but 

SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2);

is UNKNOWN when table t2 is empty. In addition, 

SELECT * FROM t1 WHERE 1 > ALL (SELECT MAX(s1) FROM t2);

is UNKNOWN when table t2 is empty. In general, tables with NULLs and empty tables are edge cases -- when writing subquery code, always consider whether you have taken those two possibilities into account.

13.1.8.5 Correlated Subqueries

A correlated subquery is a subquery which contains a reference to a column which is also in the outer query. For example: 

SELECT * FROM t1 WHERE column1 = ANY
       (SELECT column1 FROM t2 WHERE t2.column2 = t1.column2);

Notice, in the example, that the subquery contains a reference to a column of t1, even though the subquery's FROM clause doesn't mention a table t1. So MySQL looks outside the subquery, and finds t1 in the outer query.

Suppose that table t1 contains a row where column1 = 5 and column2 = 6; meanwhile table t2 contains a row where column1 = 5 and column2 = 7. The simple expression ... WHERE column1 = ANY (SELECT column1 FROM t2) would be TRUE, but in this example the WHERE clause within the subquery is FALSE (because 7 <> 5), 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 the above, 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 which is further out.

For subqueries in HAVING or ORDER BY clauses, MySQL also looks for column names in the outer select list.

MySQL's unofficial recommendation is: avoid correlation because it makes your queries look more complex, and run more slowly.

13.1.8.6 EXISTS and NOT EXISTS

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

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

Traditionally an EXISTS subquery starts with SELECT * but it could begin with SELECT 5 or SELECT column1 or anything at all -- MySQL ignores the SELECT list in such a subquery, so it doesn't matter.

For the above example, if t2 contains any rows, even rows with nothing but NULL values, then the EXISTS condition is TRUE. This is actually an unlikely example, since almost always a [NOT] EXISTS subquery will contain correlations. Here are some more realistic examples.

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

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

Example: 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 -- it has a NOT EXISTS clause within a NOT EXISTS clause. Formally, it answers the question ``does a city exist with a store which is not in Stores?''. But it's easier to say that a nested NOT EXISTS answers the question ``is x TRUE for all y?''.

13.1.8.7 Row Subqueries

The discussion to this point has been of column (or scalar) subqueries -- subqueries which return a single column value. A row subquery is a subquery variant that returns a single row value -- and may 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 above are both TRUE if table t2 has a row where column1 = 1 and column2 = 2.

The expression (1,2) is sometimes called a row constructor and is legal in other contexts too. For example 

SELECT * FROM t1 WHERE (column1,column2) = (1,1);

is equivalent to 

SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;

The normal use of row constructors, though, is for comparisons with subqueries that return two or more columns. For example, this query answers the request: ``find all rows in table t1 which are duplicated in table t2'': 

SELECT column1,column2,column3
       FROM t1
       WHERE (column1,column2,column3) IN
             (SELECT column1,column2,column3 FROM t2);

13.1.8.8 Subqueries in the FROM clause

Subqueries are legal in a SELECT statement's FROM clause. The syntax that you'll actually see is: 

SELECT ... FROM (<subquery>) AS <name> ...

The AS <name> clause is mandatory, because any table in a FROM clause must have a name. Any columns in the <subquery> select list must have unique names. You may find this syntax described elsewhere in this manual, where the term used is ``derived tables''.

For illustration, assume you have this table: 

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);

Here's how to use the Subqueries in the FROM clause feature, using the example table: 

INSERT INTO t1 VALUES (1,'1',1.0);
INSERT INTO t1 VALUES (2,'2',2.0);
SELECT sb1,sb2,sb3
       FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
       WHERE sb1 > 1;

Result: 2, '2', 4.0.

Here's another example: Suppose you want to know the average of the sum for a grouped table. This won't work: 

SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;

But this query will provide the desired information: 

SELECT AVG(sum_column1)
       FROM (SELECT SUM(column1) AS sum_column1
             FROM t1 GROUP BY column1) AS t1;

Notice that the column name used within the subquery (sum_column1) is recognized in the outer query.

At the moment, subqueries in the FROM clause cannot be correlated subqueries.

13.1.8.9 Subquery Errors

There are some new error returns which apply only to subqueries. This section groups them together because reviewing them will help remind you of some points.

It's okay to use a subquery for assignment within an UPDATE statement, since subqueries are legal in UPDATE and in 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.

Usually, failure of the subquery causes the entire statement to fail.

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

The above tricks may cause programs to go faster or slower. Using MySQL facilities like the BENCHMARK() function, you can get an idea about what helps in your own situation. Don't worry too much about transforming to joins except for compatibility with older versions.

Some optimizations that MySQL itself will make are:

  1. MySQL will execute non-correlated subqueries only once, (use EXPLAIN to make sure that a given subquery really is non-correlated),
  2. MySQL will rewrite IN/ALL/ANY/SOME subqueries in an attempt to take advantage of the possibility that the select-list columns in the subquery are indexed,
  3. MySQL will replace subqueries of the form 
    ... IN (SELECT indexed_column FROM single_table ...)
    with an index-lookup function, which EXPLAIN will describe as a special join type,
  4. MySQL will enhance expressions of the form 
    value {ALL|ANY|SOME} {> | < | >= | <=} (non-correlated subquery)
    with an expression involving MIN or MAX (unless NULL values or empty sets are involved). For example, 
    WHERE 5 > ALL (SELECT x FROM t)
    might be treated as 
    WHERE 5 > (SELECT MAX(x) FROM t)

There is a chapter titled ``How MySQL Transforms Subqueries'' in the MySQL Internals Manual, which you can find by downloading the MySQL source package and looking for a file named `internals.texi'.

13.1.8.11 Rewriting Subqueries for Earlier MySQL Versions

Up to version 4.0, only nested queries of the form INSERT ... SELECT ... and REPLACE ... SELECT ... are supported. The IN() construct can be used in other contexts.

It is often possible to rewrite a query without a subquery: 

SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);

This can be rewritten as: 

SELECT t1.* FROM t1,t2 WHERE t1.id=t2.id;

The queries: 

SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);
SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);

Can be rewritten as: 

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

A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better -- a fact that is not specific to MySQL Server alone. Prior to SQL-92, outer joins did not exist, so subqueries were the only way to do certain things in those bygone days. Today, MySQL Server and many other modern database systems offer a whole range of outer joins types.

For more complicated subqueries you can often create temporary tables to hold the subquery. In some cases, however, this option will not work. The most frequently encountered of these cases arises with DELETE statements, for which standard SQL does not support joins (except in subqueries). For this situation there are three options available:

MySQL Server 4.0 supports multiple-table DELETE statements that can be used to efficiently delete rows based on information from one table or even from many tables at the same time. Multiple-table UPDATE statements are also supported from version 4.0.

13.1.9 TRUNCATE Syntax

TRUNCATE TABLE table_name

In 3.23 TRUNCATE TABLE is mapped to COMMIT; DELETE FROM table_name. See section 13.1.1 DELETE Syntax.

TRUNCATE TABLE differs from DELETE FROM ... in the following ways:

TRUNCATE TABLE is an Oracle SQL extension. This statement was added in MySQL 3.23.28, although from 3.23.28 to 3.23.32, the keyword TABLE must be omitted.

13.1.10 UPDATE Syntax

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

or: 

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

UPDATE updates columns in existing table rows with new values. The SET clause indicates which columns to modify and the values they should be given. The WHERE clause, if given, specifies which rows should be updated. Otherwise, all rows are updated. If the ORDER BY clause is specified, the rows will be updated in the order that is specified.

If you specify the keyword LOW_PRIORITY, execution of the UPDATE is delayed until no other clients are reading from the table.

If you specify the keyword IGNORE, the update statement will not abort even if we get duplicate key errors during the update. Rows that would cause conflicts will not be updated.

If you access a column from tbl_name in an expression, UPDATE uses the current value of the column. For example, the following statement sets the age column to one more than its current value: 

mysql> UPDATE persondata SET age=age+1;

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

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

If you set a column to the value it currently has, MySQL notices this and doesn't update it.

UPDATE returns the number of rows that were actually changed. In MySQL Version 3.22 or later, the C API function mysql_info() returns the number of rows that were matched and updated and the number of warnings that occurred during the UPDATE. 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 is 0 for numeric types, the empty string ('') for string types, and the ``zero'' value for date and time types.

Starting from MySQL version 3.23, you can use LIMIT row_count to restrict the scope of the UPDATE. A LIMIT clause works as follows:

If an ORDER BY clause is used (available from MySQL 4.0.0), the rows will be updated in that order. This is really only useful in conjunction with LIMIT.

Starting with MySQL Version 4.0.4, you can also perform UPDATE operations that cover multiple tables: 

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

The example shows an inner join using the comma operator, but multiple-table UPDATE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN.

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

13.2 Data Definition Statements

13.2.1 ALTER DATABASE Syntax

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

The CHARACTER SET clause changes the database character set. The COLLATE clause changes the database collation.

Database characteristics are stored in the `db.opt' file in the database directory.

To use ALTER DATABASE, you need the ALTER privilege on the database.

ALTER DATABASE was added in MySQL 4.1.1.

13.2.2 ALTER TABLE Syntax

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

alter_specification:
    ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
  | ADD [COLUMN] (create_definition, create_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 [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 create_definition
           [FIRST | AFTER column_name]
  | MODIFY [COLUMN] create_definition [FIRST | AFTER column_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP INDEX index_name
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO] new_tbl_name
  | ORDER BY col
  | CHARACTER SET character_set_name [COLLATE collation_name]
  | 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. See section 13.2.5 CREATE TABLE Syntax.

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

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

Note that if you use any other option to ALTER TABLE than RENAME, MySQL will always create a temporary table, even if the data wouldn't strictly need to be copied (like when you change the name of a column). We plan to fix this in the future, but as one doesn't normally do ALTER TABLE that often this isn't that high on our TODO. For MyISAM tables, you can speed up the index recreation part (which is the slowest part of the recreation process) by setting the myisam_sort_buffer_size variable to a high value.

Here is an example that shows some of the uses of ALTER TABLE. We 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 an index on column d, and make column a the primary key: 

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

To remove column c: 

mysql> ALTER TABLE t2 DROP COLUMN c;

To add a new AUTO_INCREMENT integer column named c: 

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

Note that we indexed c, because AUTO_INCREMENT columns must be indexed, and also that we declare c as NOT NULL, because indexed columns cannot be NULL.

When you add an AUTO_INCREMENT column, column values are filled in with sequence numbers for you automatically. You can set the first sequence number by executing SET INSERT_ID=value before ALTER TABLE or using the AUTO_INCREMENT=value table option. See section 7.5.6 SET Syntax.

With MyISAM tables, if you don't change the AUTO_INCREMENT column, the sequence number will not be affected. If you drop an AUTO_INCREMENT column and then add another AUTO_INCREMENT column, the numbers will start from 1 again.

See section A.7.1 Problems with ALTER TABLE..

13.2.3 CREATE DATABASE Syntax

CREATE DATABASE [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.

Rules for allowable database names are given in section 10.2 Database, Table, Index, Column, and Alias Names. An error occurs if the database already exists and you didn't specify IF NOT EXISTS.

As of MySQL 4.1.1, create_specification options may be given. The CHARACTER SET clause specifies the database character set. The COLLATE clause specifies the database collation.

Database characteristics are stored in the `db.opt' file in the database directory.

Databases in MySQL are implemented as directories containing files that correspond to tables in the database. Because there are no tables in a database when it is initially created, the CREATE DATABASE statement only creates a directory under the MySQL data directory.

You can also create databases with mysqladmin. See section 8 MySQL Client and Utility Programs.

13.2.4 CREATE INDEX Syntax

CREATE [UNIQUE|FULLTEXT] INDEX index_name [index_type]
       ON tbl_name (index_col_name,...)

index_col_name:
        col_name [(length)] [ASC | DESC]

The CREATE INDEX statement doesn't do anything in MySQL prior to Version 3.22. In Version 3.22 or later, CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. See section 13.2.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.2.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 the first length bytes of each column value. (For BLOB and TEXT columns, a prefix length is required; length may be a value up to 255.) The statement shown here creates an index using the first 10 characters of the name column: 

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

Note that you can add an index on a column that can have NULL values only if you are using MySQL Version 3.23.2 or newer and are using the MyISAM, InnoDB, or BDB table type. You can only add an index on a BLOB/TEXT column if you are using MySQL Version 3.23.2 or newer and are using the MyISAM or BDB table type, or MySQL Version 4.0.14 or newer and the InnoDB table type. For an index on a BLOB or TEXT column, a prefix length must always be specified.

An index_col_name specification may 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.

For more information about how MySQL uses indexes, see section 7.4.3 How MySQL Uses Indexes.

FULLTEXT indexes can index only CHAR, VARCHAR, and TEXT columns, and only in MyISAM tables. FULLTEXT indexes are available in MySQL Version 3.23.23 and later. section 13.7 MySQL Full-text Search.

13.2.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:
    col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
            [[PRIMARY] KEY] [COMMENT 'string'] [reference_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 [INDEX] [index_name] (index_col_name,...)
  | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...)
            [reference_definition]
  | CHECK (expr)

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]
  | CHAR(length) [BINARY | ASCII | UNICODE]
  | VARCHAR(length) [BINARY]
  | DATE
  | TIME
  | TIMESTAMP
  | DATETIME
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT
  | TEXT
  | MEDIUMTEXT
  | LONGTEXT
  | ENUM(value1,value2,value3,...)
  | SET(value1,value2,value3,...)

index_col_name:
        col_name [(length)] [ASC | DESC]

reference_definition:
        REFERENCES tbl_name [(index_col_name,...)]
                   [MATCH FULL | MATCH PARTIAL]
                   [ON DELETE reference_option]
                   [ON UPDATE reference_option]

reference_option:
        RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options: table_option [table_option] ...

table_option:
    {ENGINE | TYPE} = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM}
  | AUTO_INCREMENT = #
  | AVG_ROW_LENGTH = #
  | CHECKSUM = {0 | 1}
  | COMMENT = 'string'
  | MAX_ROWS = #
  | MIN_ROWS = #
  | PACK_KEYS = {0 | 1 | DEFAULT}
  | PASSWORD = 'string'
  | DELAY_KEY_WRITE = {0 | 1}
  | ROW_FORMAT = { DEFAULT | DYNAMIC | FIXED | COMPRESSED }
  | RAID_TYPE = { 1 | STRIPED | RAID0 } RAID_CHUNKS=#  RAID_CHUNKSIZE=#
  | UNION = (table_name,[table_name...])
  | INSERT_METHOD = { NO | FIRST | LAST }
  | DATA DIRECTORY = 'absolute path to directory'
  | INDEX DIRECTORY = 'absolute path to directory'
  | DEFAULT CHARACTER SET character_set_name [COLLATE collation_name]

select_statement:
    [IGNORE | REPLACE] [AS] SELECT ...     (Some legal select statement)

CREATE TABLE creates a table with the given name. Rules for allowable table names are given in section 10.2 Database, Table, Index, Column, and Alias Names. By default, the table is created in the current database. An error occurs if the table already exists, if there is no current database, or if the database does not exist.

In MySQL Version 3.22 or later, the table name can be specified as db_name.tbl_name to create the table in a specific database. This works regardless of whether there is a current database.

From MySQL Version 3.23, you can use the TEMPORARY keyword when you create a table. The temporary table is visible only to the current connection, and will be deleted automatically when the connection is closed. This means that two different connections can both use the same temporary table name without conflicting with each other or with an existing table of the same name. (The existing table is hidden until the temporary table is deleted.) From MySQL 4.0.2 on, you must have the CREATE TEMPORARY TABLES privilege to be able to create temporary tables.

In MySQL Version 3.23 or later, you can use the keywords IF NOT EXISTS so that an error does not occur if the table already exists. Note that there is no verification that the existing table has a structure identical to that indicated by the CREATE TABLE statement.

From version 4.1.0, the attribute SERIAL can be used as an alias for BIGINT NOT NULL AUTO_INCREMENT UNIQUE. This is compatibility feature.

As of MySQL 3.23, you can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement: 

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

Indexes are not carried over to the new table, and some conversion of column types may occur. For example, the AUTO_INCREMENT attribute is not preserved, and VARCHAR columns may 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 may fail or result in undesirable column names. 

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

As of MySQL 4.1, you can explicitly specify the type for a generated column: 

CREATE TABLE foo (a tinyint not null) SELECT b+1 AS 'a' FROM bar;

In MySQL 4.1, you can also use LIKE to create a table based on the definition of another table, including any column attributes and indexes the original table has: 

CREATE TABLE new_tbl LIKE orig_tbl;

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

Each table tbl_name is represented by some files in the database directory. In the case of MyISAM-type tables you will get:

File Purpose
tbl_name.frm Table format (definition) file
tbl_name.MYD Datafile
tbl_name.MYI Index file

For more information on the properties of the various column types, see section 11 Column Types:

13.2.5.1 Silent Column Specification Changes

In some cases, MySQL silently changes a column specification from that given in a CREATE TABLE statement. (This may also occur with ALTER TABLE.):

If you want to see whether MySQL used a column type other than the one you specified, issue a DESCRIBE tbl_name statement after creating or altering your table.

Certain other column type changes may occur if you compress a table using myisampack. See section 14.1.2.3 Compressed Table Characteristics.

13.2.6 DROP DATABASE Syntax

DROP DATABASE [IF EXISTS] db_name

DROP DATABASE drops all tables in the database and deletes the database. If you do a DROP DATABASE on a symbolic linked database, both the link and the original database is deleted. Be VERY careful with this command!

DROP DATABASE returns the number of files that were removed from the database directory. For MyISAM tables, this is three times the number of tables, because normally each table corresponds to a `.MYD' file, a `.MYI' file, and a `.frm' file.

The DROP DATABASE command removes from the given database directory all files with the following extensions:

Ext Ext Ext Ext
.BAK .DAT .HSH .ISD
.ISM .ISM .MRG .MYD
.MYI .db .frm

All subdirectories that consists of 2 digits (RAID directories) are also removed.

In MySQL Version 3.22 or later, you can use the keywords IF EXISTS to prevent an error from occurring if the database doesn't exist.

You can also drop databases with mysqladmin. See section 8 MySQL Client and Utility Programs.

13.2.7 DROP INDEX Syntax

DROP INDEX index_name ON tbl_name

DROP INDEX drops the index named index_name from the table tbl_name. DROP INDEX doesn't do anything in MySQL prior to Version 3.22. In Version 3.22 or later, DROP INDEX is mapped to an ALTER TABLE statement to drop the index. See section 13.2.2 ALTER TABLE Syntax.

13.2.8 DROP TABLE Syntax

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name,...] [RESTRICT | CASCADE]

DROP TABLE removes one or more tables. All table data and the table definition are removed, so be careful with this command!

In MySQL Version 3.22 or later, you can use the keywords IF EXISTS to prevent an error from occurring for tables that don't exist. In 4.1 one gets a NOTE for all not existing tables when using IF EXISTS. See section 13.5.3.9 SHOW WARNINGS | ERRORS.

RESTRICT and CASCADE are allowed to make porting easier. For the moment they don't do anything.

Note: DROP TABLE will automatically commit current active transaction (except if you are using 4.1 and the TEMPORARY key word.

Option TEMPORARY is ignored in 4.0. In 4.1 this option works as follows:

Using TEMPORARY is a good way to ensure that you don't accidently drop a real table.

13.2.9 RENAME TABLE Syntax

RENAME TABLE tbl_name TO new_tbl_name[, tbl_name2 TO new_tbl_name2,...]

The rename is done atomically, which means that no other thread can access any of the tables while the rename is running. This makes it possible to replace a table with an empty one : 

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

The rename is done from left to right, which means that if you want to swap two table names, you have to: 

RENAME TABLE old_table    TO backup_table,
             new_table    TO old_table,
             backup_table TO new_table;

As long as two databases are on the same disk you can also rename from one database to another: 

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

When you execute RENAME, you can't have any locked tables or active transactions. You must also have the ALTER and DROP privileges on the original table, and the CREATE and INSERT privileges on the new table.

If MySQL encounters any errors in a multiple-table rename, it will do a reverse rename for all renamed tables to get everything back to the original state.

RENAME TABLE was added in MySQL 3.23.23.

13.3 Basic MySQL User Utility Statements

13.3.1 DESCRIBE Syntax (Get Information About Columns)

{DESCRIBE | DESC} tbl_name [col_name | wild]

DESCRIBE is a shortcut for SHOW COLUMNS FROM. See section 13.5.3.1 Retrieving Information about Database, Tables, Columns, and Indexes.

DESCRIBE provides information about a table's columns. col_name may 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.

If the column types are different from what you expect them to be based on a CREATE TABLE statement, note that MySQL sometimes changes column types. See section 13.2.5.1 Silent Column Specification Changes.

This statement is provided for Oracle compatibility.

The SHOW statement provides similar information. See section 13.5.3 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 database for subsequent queries. The database remains current until the end of the session or until another USE statement is issued: 

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

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

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

The USE statement is provided for Sybase compatibility.

13.4 MySQL Transactional and Locking Statements

13.4.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax

By default, MySQL runs in autocommit mode. This means that as soon as you execute a statement that updates (modifies) a table, MySQL will store the update on disk.

If you are using transaction-safe tables (like InnoDB or BDB), you can put MySQL into non-autocommit mode with the following command: 

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 summmary=@A WHERE type=1;
COMMIT;

BEGIN and BEGIN WORK can be used instead of START TRANSACTION to initiate a transaction. START TRANSACTION was added in MySQL 4.0.11; it is SQL-99 syntax and is the recommended way to start an ad-hoc transaction. BEGIN and BEGIN WORK are available from MySQL 3.23.17 and 3.23.19, respectively.

Note that if you are not using transaction-safe tables, any changes will be stored at once, regardless of the status of autocommit mode.

If you issue a ROLLBACK statement after updating a non-transactional table, you will get an error (ER_WARNING_NOT_COMPLETE_ROLLBACK) as a warning. All transaction-safe tables will be restored but any non-transaction-safe table will not change.

If you are using START TRANSACTION or SET AUTOCOMMIT=0, you should use the MySQL binary log for backups instead of the older update log. Transactions are stored in the binary log in one chunk, upon COMMIT, to ensure that transactions that are rolled back are not stored. See section 5.7.4 The Binary Log.

You can change the isolation level for transactions with SET TRANSACTION ISOLATION LEVEL. See section 13.4.6 SET TRANSACTION Syntax.

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, or those that create, drop, or alter tables.

You may wish to design your transactions not to include such statements. If you issue a statement that cannot be rolled back early in a transaction, and then another statement later fails, the full effect of the transaction cannot be rolled back by issuing a ROLLBACK statement.

13.4.3 Statements That Cause an Implicit Commit

The following commands implicitly end a transaction (as if you had done a COMMIT before executing the command):

Command Command Command
ALTER TABLE BEGIN CREATE INDEX
DROP DATABASE DROP INDEX DROP TABLE
LOAD MASTER DATA LOCK TABLES RENAME TABLE
SET AUTOCOMMIT=1 START TRANSACTION TRUNCATE

UNLOCK TABLES also ends a transaction if any tables currently are locked. Prior to MySQL 4.0.13, CREATE TABLE ends a transaction if the binary update log is enabled.

Transactions cannot be nested. This is a consequence of the implicit COMMIT performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms.

13.4.4 SAVEPOINT and ROLLBACK TO SAVEPOINT Syntax

Starting from MySQL 4.0.14 and 4.1.1, InnoDB supports the SQL commands SAVEPOINT and ROLLBACK TO SAVEPOINT. 

SAVEPOINT identifier

This statement sets a named transaction savepoint whose name is identifier. If the current transaction already has a savepoint with the same name, the old savepoint is deleted and a new one is set. 

ROLLBACK TO SAVEPOINT identifier

This statement rolls back a transaction to the named savepoint. Modifications that this transaction made to rows after the savepoint was set are undone in the rollback, but InnoDB does not release the row locks that were stored in memory after the savepoint. (Note that for a new inserted row, the lock information is carried by the transaction ID stored in the row; the lock is not separately stored in memory. In this case, the row lock is released in the undo.) Savepoints that were set at a later time than the named savepoint are deleted.

If the command returns the following error, it means that no savepoint with the specified name exists: 

ERROR 1181: Got error 153 during ROLLBACK

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

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

To use LOCK TABLES in MySQL 4.0.2 you need the global LOCK TABLES privilege and a SELECT privilege on the involved tables. In MySQL 3.23 you need to have SELECT, insert, DELETE and UPDATE privileges for the tables.

The main reasons to use LOCK TABLES are for emulating transactions or getting 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, then only the thread holding the lock can read from or write to the table. Other threads are blocked.

The difference between READ LOCAL and READ is that READ LOCAL allows non-conflicting INSERT statements to execute while the lock is held. This can't however be used if you are going to manipulate the database files outside MySQL while you hold the lock.

When you use LOCK TABLES, you must lock all tables that you are going to use and you must use the same alias that you are going to use in your queries! If you are using a table multiple times in a query (with aliases), you must get a lock for each alias!

WRITE locks normally have higher priority than READ locks, to ensure that updates are processed as soon as possible. This means that if one thread obtains a READ lock and then another thread requests a WRITE lock, subsequent READ lock requests will wait until the WRITE thread has gotten the lock and released it. You can use LOW_PRIORITY WRITE locks to allow other threads to obtain READ locks while the thread is waiting for the WRITE lock. You should only use LOW_PRIORITY WRITE locks if you are sure that there will eventually be a time when no threads will have a READ lock.

LOCK TABLES works as follows:

  1. Sort all tables to be locked in a internally defined order (from the user standpoint the 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 is however other things one needs to be aware of with this schema:

If you are using a LOW_PRIORITY WRITE lock for a table, this means only that MySQL will wait for this particlar lock until there is no threads that wants a READ lock. When the thread has got the WRITE lock and is waiting to get the lock for the next table in the lock table list, all other threads will wait for the WRITE lock to be released. If this becomes a serious problem with your application, you should consider converting some of your tables to transaction-safe tables.

You can safely kill a thread that is waiting for a table lock with KILL. See section 13.5.4.2 KILL Syntax.

Note that you should not lock any tables that you are using with INSERT DELAYED, because in this case the INSERT is done by a separate thread.

Normally, you don't have to lock tables, as all single UPDATE statements are atomic; no other thread can interfere with any other currently executing SQL statement. There are a few cases when you would like to lock tables anyway:

By using incremental updates (UPDATE customer SET value=value+new_value) or the LAST_INSERT_ID() function, you can avoid using LOCK TABLES in many cases.

You can also solve some cases by using the user-level 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.6.4 Miscellaneous Functions.

See section 7.3.1 How MySQL Locks Tables, for more information on locking policy.

You can lock all tables in all databases with read locks with the FLUSH TABLES WITH READ LOCK command. See section 13.5.4.1 FLUSH Syntax. This is very convenient way to get backups if you have a filesystem, like Veritas, that can take snapshots in time.

NOTE: LOCK TABLES is not transaction-safe and will implicitly commit any active transactions before attempting to lock the tables.

13.4.6 SET TRANSACTION Syntax

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

Sets the transaction isolation level for the global, whole session or the next transaction.

The default behavior is to set the isolation level for the next (not 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 (but not existing connections). 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 description of each InnoDB transaction isolation level, see section 14.4.9.1 InnoDB and SET ... TRANSACTION ISOLATION LEVEL .... InnoDB supports each of these levels from MySQL 4.0.5 on. The default level is REPEATABLE READ.

You can set the default global isolation level for mysqld with --transaction-isolation=.... See section 5.2.1 mysqld Command-line Options.

13.5 Database Administration Statements

13.5.1 Account Management Statements

13.5.1.1 GRANT and REVOKE Syntax

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    TO user_name [IDENTIFIED BY [PASSWORD] 'password']
        [, user_name [IDENTIFIED BY [PASSWORD] 'password'] ...]
    [REQUIRE
        NONE |
        [{SSL| X509}]
        [CIPHER cipher [AND]]
        [ISSUER issuer [AND]]
        [SUBJECT subject]]
    [WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |
                          MAX_UPDATES_PER_HOUR # |
                          MAX_CONNECTIONS_PER_HOUR #]]

REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    FROM user_name [, user_name ...]

REVOKE ALL PRIVILEGES,GRANT FROM user_name [, user_name ...]

GRANT is implemented in MySQL Version 3.22.11 or later. For earlier MySQL versions, the GRANT statement does nothing.

The GRANT and REVOKE commands allow system administrators to create users and grant and revoke rights to MySQL users at four privilege 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 *.* will grant and revoke only global privileges.
Database level
Database privileges apply to all tables in a given database. These privileges are stored in the mysql.db and mysql.host tables. GRANT ALL ON db.* and REVOKE ALL ON db.* will 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.table and REVOKE ALL ON db.table will 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.

To make it easy to revoke all privileges for a user, MySQL 4.1.1 has added the syntax: 

REVOKE ALL PRIVILEGES,GRANT FROM user_name [, user_name ...]

This will drop all database, table and column level privileges for the user.

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

ALL [PRIVILEGES] Sets all simple privileges except WITH GRANT OPTION
ALTER Allows usage of ALTER TABLE
CREATE Allows usage of CREATE TABLE
CREATE TEMPORARY TABLES Allows usage of CREATE TEMPORARY TABLE
DELETE Allows usage of DELETE
DROP Allows usage of DROP TABLE.
EXECUTE Allows the user to run stored procedures (MySQL 5.0)
FILE Allows usage of SELECT ... INTO OUTFILE and LOAD DATA INFILE.
INDEX Allows usage of CREATE INDEX and DROP INDEX
INSERT Allows usage of INSERT
LOCK TABLES Allows usage of LOCK TABLES on tables for which one has the SELECT privilege.
PROCESS Allows usage of SHOW FULL PROCESSLIST
REFERENCES For the future
RELOAD Allows usage of FLUSH
REPLICATION CLIENT Gives the right to the user to ask where the slaves/masters are.
REPLICATION SLAVE Needed for the replication slaves (to read binlogs from master).
SELECT Allows usage of SELECT
SHOW DATABASES SHOW DATABASES shows all databases.
SHUTDOWN Allows usage of mysqladmin shutdown
SUPER Allows one connect (once) even if max_connections is reached and execute commands CHANGE MASTER, KILL thread, mysqladmin debug, PURGE MASTER LOGS and SET GLOBAL
UPDATE Allows usage of UPDATE
USAGE Synonym for ``no privileges.''
GRANT OPTION Synonym for WITH GRANT OPTION

USAGE can be used when you want to create a user that has no privileges.

The privileges CREATE TEMPORARY TABLES, EXECUTE, LOCK TABLES, REPLICATION ..., SHOW DATABASES and SUPER are new for in version 4.0.2. To use these new privileges after upgrading to 4.0.2, you have to run the mysql_fix_privilege_tables script. See section 2.5.8 Upgrading the Grant Tables.

In older MySQL versions, the PROCESS privilege gives the same rights as the new SUPER privilege.

To revoke the GRANT privilege from a user, use a priv_type value of GRANT OPTION: 

mysql> REVOKE GRANT OPTION ON ... FROM ...;

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.

MySQL allows you to create database level privileges even if the database doesn't exist, to make it easy to prepare for database usage. Currently MySQL does however not allow one to create table level grants if the table doesn't exist. MySQL will not automatically revoke any privileges even if you drop a table or drop a database.

You can set global privileges by using ON *.* syntax. You can set database privileges by using ON db_name.* syntax. If you specify ON * and you have a current database, you will set the privileges for that database. (Warning: if you specify ON * and you don't have a current database, you will affect the global privileges!)

Please note: the `_' and `%' wildcards are allowed when specifying database names in GRANT commands. This means that if you wish to use for instance a `_' character as part of a database name, you should specify it as `\_' in the GRANT command, 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_name value in the form user@host. If you want to specify a user string containing special characters (such as `-'), or a host string containing special characters or wildcard characters (such as `%'), you can quote the username or hostname (for example, 'test-user'@'test-hostname').

You can specify wildcards in the hostname. For example, user@'%.loc.gov' applies to user for any host in the loc.gov domain, and user@'144.155.166.%' applies to user for any host in the 144.155.166 class C subnet.

The simple form user is a synonym for user@"%".

MySQL doesn't support wildcards in usernames. Anonymous users are defined by inserting entries with User='' into the mysql.user table or creating an user with an empty name with the GRANT command.

Note: if you allow anonymous users to connect to the MySQL server, you should also grant privileges to all local users as user@localhost because otherwise the anonymous user entry for the local host in the mysql.user table will be used when the user tries to log into the MySQL server from the local machine!

You can verify if this applies to you by executing this query: 

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

For the moment, GRANT only supports host, table, database, and column names up to 60 characters long. A username can be up to 16 characters.

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

The privileges for a column can be calculated as follows: 

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

In most cases, you grant rights to a user at only one of the privilege levels, so life isn't normally as complicated as above. The details of the privilege-checking procedure are presented in section 5.3 General Security Issues and the MySQL Access Privilege System.

If you grant privileges for a user/hostname combination that does not exist in the mysql.user table, an entry is added and remains there until deleted with a DELETE command. In other words, GRANT may create user table entries, but REVOKE will not remove them; you must do that explicitly using DELETE.

In MySQL Version 3.22.12 or later, if a new user is created or if you have global grant privileges, the user's password will be set to the password specified by the IDENTIFIED BY clause, if one is given. If the user already had a password, it is replaced by the new one.

If you don't want to send the password in clear text you can use the PASSWORD option followed by a scrambled password from SQL function PASSWORD() or the C API function make_scrambled_password(char *to, const char *password).

Warning: if you create a new user but do not specify an IDENTIFIED BY clause, the user has no password. This is insecure.

Passwords can also be set with the SET PASSWORD command. See section 7.5.6 SET Syntax.

If you grant privileges for a database, an entry in the mysql.db table is created if needed. When all privileges for the database have been removed with REVOKE, this entry is deleted.

If a user doesn't have any privileges on a table, the table is not displayed when the user requests a list of tables (for example, with a SHOW TABLES statement). The same is true for SHOW DATABASES.

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 privilege, as two users with different privileges may be able to join privileges!

MAX_QUERIES_PER_HOUR #, MAX_UPDATES_PER_HOUR # and MAX_CONNECTIONS_PER_HOUR # are new in MySQL version 4.0.2. These options limit the number of queries/updates and logins the user can do during one hour. If # is 0 (default), then this means that there are no limitations for that user. See section 5.4.6 Limiting user resources. Note: to specify any of these options for an existing user without adding other additional privileges, use GRANT USAGE ON *.* ... WITH MAX_....

You cannot grant another user a privilege you don't have yourself; the GRANT privilege allows you to give away only those privileges you possess.

Be aware that when you grant a user the GRANT privilege at a particular privilege level, any privileges the user already possesses (or is given in the future!) at that level are also grantable by that user. Suppose you grant a user the INSERT privilege on a database. If you then grant the SELECT privilege on the database and specify WITH GRANT OPTION, the user can give away not only the SELECT privilege, but also INSERT. If you then grant the UPDATE privilege to the user on the database, the user can give away the 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!

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 will slow down MySQL a bit.

When mysqld starts, all privileges are read into memory. Database, table, and column privileges take effect at once, and user-level privileges take effect the next time the user connects. Modifications to the grant tables that you perform using GRANT or REVOKE are noticed by the server immediately. If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges to tell the server to reload the grant tables. See section 5.4.2 When Privilege Changes Take Effect.

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

For a description of using REQUIRE, see section 5.4.9 Using Secure Connections.

13.5.2 Table Maintenance Statements

13.5.2.1 ANALYZE TABLE Syntax

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

Analyze and store the key distribution for the table. During the analysis, the table is locked with a read lock. This works on MyISAM and BDB tables.

This is equivalent to running myisamchk -a on the table.

MySQL uses the stored key distribution to decide in which order tables should be joined when one does a join on something else than a constant.

The command returns a table with the following columns:

Column Value
Table Table name
Op Always analyze
Msg_type One of status, error, info, or warning
Msg_text The message

You can check the stored key distribution with the SHOW INDEX command. See section 13.5.3.1 Retrieving Information about Database, Tables, Columns, and Indexes.

If the table hasn't changed since the last ANALYZE TABLE command, the table will not be analyzed again.

Before MySQL 4.1.1, ANALYZE commands are not written to the binary log. Since MySQL 4.1.1 they are written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) was 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.

Copies to the backup directory the minimum number of table files needed to restore the table, after flushing any buffered changes to disk. Currently works only for MyISAM tables. For MyISAM tables, copies `.frm' (definition) and `.MYD' (data) files. The index file can be rebuilt from those two.

Before using this command, please see section 5.5.1 Database Backups.

During the backup, a read lock will be held for each table, one at time, as they are being backed up. If you want to back up several tables as a snapshot, you must first issue LOCK TABLES obtaining a read lock for each table in the group.

The command returns a table with the following columns:

Column Value
Table Table name
Op Always backup
Msg_type One of status, error, info, or warning
Msg_text The message

Note that BACKUP TABLE is only available in MySQL version 3.23.25 and later.

13.5.2.3 CHECK TABLE Syntax

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

option = QUICK | FAST | MEDIUM | EXTENDED | CHANGED

CHECK TABLE works only on MyISAM and InnoDB tables. On MyISAM tables, it's the same thing as running myisamchk --medium-check table_name on the table.

If you don't specify any option, MEDIUM is used.

Checks the table or tables for errors. For MyISAM tables, the key statistics are updated. The command returns a table with the following columns:

Column Value
Table Table name
Op Always check
Msg_type One of status, error, info, or warning
Msg_text The message

Note that the statement may produce many rows of information for each checked table. The last row will be of Msg_type status and should normally 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.5.2 Using myisamchk for Table Maintenance and Crash Recovery. Table is already up to date means that the storage manager for the table indicated that there was no need to check the table.

The different check types are as follows:

Type Meaning
QUICK Don't scan the rows to check for incorrect links.
FAST Only check tables that haven't been closed properly.
CHANGED Only check tables that have been changed since the last check or haven't been closed properly.
MEDIUM Scan rows to verify that deleted links are okay. This also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys.
EXTENDED Do a full key lookup for all keys for each row. This ensures that the table is 100% consistent, but will take a long time!

For dynamically sized MyISAM tables, a started check will always do a MEDIUM check. For statically sized rows, we skip the row scan for QUICK and FAST as the rows are very seldom corrupted.

You can combine check options, as in the following example that does a quick check on the table to see whether it was closed properly: 

CHECK TABLE test_table FAST QUICK;

Note: that in some cases CHECK TABLE will change the table! This happens if the table is marked as 'corrupted' or 'not closed properly' but CHECK TABLE didn't find any problems in the table. In this case, CHECK TABLE will mark the table as okay.

If a table is corrupted, then it's most likely that the problem is in the indexes and not in the data part. All of the above check types checks 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 didn't find an error in the datafile. (In most cases MySQL should find, under normal usage, any error in the datafile. If this happens then the table will be marked as 'corrupted', in which case the table can't be used until it's repaired.)

FAST and CHANGED are mostly intended to be used from a script (for example to be executed from cron) if you want to check your table from time to time. In most cases, FAST is to be prefered over CHANGED. (The only case when it isn't is when you suspect that you have found a bug in the MyISAM code.)

EXTENDED is only to be used after you have run a normal check but still get strange errors from a table when MySQL tries to update a row or find a row by key (this is very unlikely if a normal check has succeeded!).

Some problems reported by CHECK TABLE can't be corrected automatically:

13.5.2.4 CHECKSUM TABLE Syntax

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

Reports a table checksum. If QUICK is specified, live table checksum is reported, or NULL if the table does not support live checksum. This is very fast. 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 - with neither QUICK nor EXTENDED - MySQL returns live checksum if the table support it and scans the table otherwise.

This statement is implemented in MySQL 4.1.1.

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

In most setups you don't have to run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable length rows it's not likely that you need to do this more than once a month/week and only on certain tables.

For the moment, OPTIMIZE TABLE works only on MyISAM and BDB tables. For BDB tables, OPTIMIZE TABLE is currently mapped to ANALYZE TABLE. See section 13.5.2.1 ANALYZE TABLE Syntax.

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

OPTIMIZE TABLE works the following way:

Note that the table is locked during the time OPTIMIZE TABLE is running!

Before MySQL 4.1.1, OPTIMIZE commands are not written to the binary log. Since MySQL 4.1.1 they are written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) was used.

13.5.2.6 REPAIR TABLE Syntax

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

REPAIR TABLE works only on MyISAM tables and is the same as running myisamchk -r table_name on the table.

Normally you should never have to run this command, but if disaster strikes you are very likely to get back all your data from a MyISAM table with REPAIR TABLE. If your tables get corrupted a lot, you should try to find the reason for it, to eliminate the need to use REPAIR TABLE. See section A.4.1 What To Do If MySQL Keeps Crashing. See section 14.1.3 MyISAM Table Problems.

REPAIR TABLE repairs a possibly corrupted table. The command returns a table with the following columns:

Column Value
Table Table name
Op Always repair
Msg_type One of status, error, info, or warning
Msg_text The message

Note that the statement may produce many rows of information for each repaired table. The last one row will be of Msg_type status and should normally be OK. If you don't get OK, you should try repairing the table with myisamchk --safe-recover, as REPAIR TABLE does not yet implement all the options of myisamchk. In the near future, we will make it more flexible.

If QUICK is given, REPAIR TABLE tries to repair only the index tree.

If you use EXTENDED, MySQL will create the index row by row instead of creating one index at a time with sorting; this may be better than sorting on fixed-length keys if you have long CHAR keys that compress very well. This type of repair is like that done by myisamchk --safe-recover.

As of MySQL 4.0.2, there is a USE_FRM mode for REPAIR. Use it if the `.MYI' file is missing or if its header is corrupted. In this mode MySQL will recreate the table, using information from the `.frm' file. This kind of repair cannot be done with myisamchk.

Warning: If mysqld dies during a REPAIR TABLE, it's essential that you do at once another REPAIR on the table before executing any other commands on it. (It's always good to start by making a backup). In the worst case you can have a new clean index file without information about the datafile and when the next command you do may overwrite the datafile. This is not a likely, but possible scenario.

Before MySQL 4.1.1, REPAIR commands are not written to the binary log. Since MySQL 4.1.1 they are written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) was used.

13.5.2.7 RESTORE TABLE Syntax

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

Restores the table or tables from the backup that was made with BACKUP TABLE. Existing tables will not be overwritten; if you try to restore over an existing table, you will get an error. Restoring will take longer than backing up due to the need to rebuild the index. The more keys you have, the longer it will take. Just as BACKUP TABLE, RESTORE TABLE currently works only for MyISAM tables.

The command returns a table with the following columns:

Column Value
Table Table name
Op Always restore
Msg_type One of status, error, info, or warning
Msg_text The message

13.5.3 SHOW Syntax

   SHOW DATABASES [LIKE wild]
or SHOW [OPEN] TABLES [FROM db_name] [LIKE wild]
or SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]
or SHOW INDEX FROM tbl_name [FROM db_name]
or SHOW TABLE STATUS [FROM db_name] [LIKE wild]
or SHOW STATUS [LIKE wild]
or SHOW VARIABLES [LIKE wild]
or SHOW [BDB] LOGS
or SHOW [FULL] PROCESSLIST
or SHOW GRANTS FOR user
or SHOW CREATE TABLE table_name
or SHOW MASTER STATUS
or SHOW MASTER LOGS
or SHOW SLAVE STATUS
or SHOW WARNINGS [LIMIT row_count]
or SHOW ERRORS [LIMIT row_count]
or SHOW TABLE TYPES

SHOW provides information about databases, tables, columns, or status information about the server. If the LIKE wild part is used, the wild string can be a string that uses the SQL `%' and `_' wildcard characters.

Note that there are other forms of the SHOW statement that provide information about replication master and slave servers. They are described in section 13.6 Replication Statements.

13.5.3.1 Retrieving Information about Database, Tables, Columns, and Indexes

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 DATABASES lists the databases on the MySQL server host. You can also get this list using the mysqlshow command line tool. In version 4.0.2 you will only see those databases for which you have some kind of privilege, if you don't have the global SHOW DATABASES privilege.

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

Note: if a user doesn't have any privileges for a table, the table will not show up in the output from SHOW TABLES or mysqlshow db_name.

SHOW OPEN TABLES lists the tables that are currently open in the table cache. See section 7.4.7 How MySQL Opens and Closes Tables. The Comment field tells how many times the table is cached and in_use.

SHOW COLUMNS lists the columns in a given table. If you specify the FULL option, you will also get the privileges you have for each column. If the column types are different from what you expect them to be based on a CREATE TABLE statement, note that MySQL sometimes changes column types. See section 13.2.5.1 Silent Column Specification Changes. As of MySQL 4.1, the FULL keyword also causes any per-column comments to be displayed.

The DESCRIBE statement provides information similar to SHOW COLUMNS. See section 13.3.1 DESCRIBE Syntax (Get Information About Columns).

SHOW FIELDS is a synonym for SHOW COLUMNS, and SHOW KEYS is a synonym for SHOW INDEX. You can also list a table's columns or indexes with mysqlshow db_name tbl_name or mysqlshow -k db_name tbl_name.

SHOW INDEX returns the index information in a format that closely resembles the SQLStatistics call in ODBC. The following columns are returned:

Column Meaning
Table Name of the table.
Non_unique 0 if the index can't contain duplicates, 1 if it can.
Key_name Name of the index.
Seq_in_index Column sequence number in index, starting with 1.
Column_name Column name.
Collation How the column is sorted in the index. In MySQL, this can have values `A' (Ascending) or NULL (Not sorted).
Cardinality Number of unique values in the index. This is updated by running isamchk -a.
Sub_part Number of indexed characters if the column is only partly indexed. NULL if the entire key is indexed.
Null Contains 'YES' if the column may contain NULL.
Index_type Index method used.
Comment Various remarks. For now, it tells in MySQL < 4.0.2 whether index is FULLTEXT or not.

Note that as the Cardinality is counted based on statistics stored as integers, it's not necessarily accurate for small tables.

The Null and Index_type columns were added in MySQL 4.0.2.

13.5.3.2 SHOW TABLE STATUS

SHOW TABLE STATUS [FROM db_name] [LIKE wild]

SHOW TABLE STATUS (new in Version 3.23) 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. The following columns are returned:

Column Meaning
Name Name of the table.
Type Type of table. See section 14 MySQL Table Types.
Row_format The row storage format (Fixed, Dynamic, or Compressed).
Rows Number of rows.
Avg_row_length Average row length.
Data_length Length of the datafile.
Max_data_length Max length of the datafile. For fixed row formats, this is the max number of rows in the table. For dynamic row formats, this is the total number of data bytes that can be stored in the table, given the data pointer size used.
Index_length Length of the index file.
Data_free Number of allocated but not used bytes.
Auto_increment Next autoincrement value.
Create_time When the table was created.
Update_time When the datafile was last updated.
Check_time When the table was last checked.
Collation Table's character set and collation. (new 4.1.1)
Checksum Live checksum value (if any). (new in 4.1.1)
Create_options Extra options used with CREATE TABLE.
Comment The comment used when creating the table (or some information why MySQL couldn't access the table information).

InnoDB tables will report the free space in the tablespace in the table comment.

13.5.3.3 SHOW STATUS

SHOW STATUS provides server status information (like mysqladmin extended-status). The output resembles that shown here, though the format and numbers probably differ: 

+--------------------------+------------+
| 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         |
| Delayed_insert_threads   | 0          |
| Delayed_writes           | 0          |
| Delayed_errors           | 0          |
| Flush_commands           | 1          |
| Handler_delete           | 462604     |
| Handler_read_first       | 105881     |
| Handler_read_key         | 27820558   |
| Handler_read_next        | 390681754  |
| Handler_read_prev        | 6022500    |
| Handler_read_rnd         | 30546748   |
| Handler_read_rnd_next    | 246216530  |
| Handler_update           | 16945404   |
| Handler_write            | 60356676   |
| Key_blocks_used          | 14955      |
| Key_read_requests        | 96854827   |
| Key_reads                | 162040     |
| Key_write_requests       | 7589728    |
| Key_writes               | 3813196    |
| Max_used_connections     | 0          |
| Not_flushed_key_blocks   | 0          |
| Not_flushed_delayed_rows | 0          |
| Open_tables              | 1          |
| Open_files               | 2          |
| Open_streams             | 0          |
| Opened_tables            | 44600      |
| Questions                | 2026873    |
| Select_full_join         | 0          |
| Select_full_range_join   | 0          |
| Select_range             | 99646      |
| Select_range_check       | 0          |
| Select_scan              | 30802      |
| Slave_running            | OFF        |
| Slave_open_temp_tables   | 0          |
| Slow_launch_threads      | 0          |
| Slow_queries             | 0          |
| Sort_merge_passes        | 30         |
| Sort_range               | 500        |
| Sort_rows                | 30296250   |
| Sort_scan                | 4650       |
| Table_locks_immediate    | 1920382    |
| Table_locks_waited       | 0          |
| Threads_cached           | 0          |
| Threads_created          | 30022      |
| Threads_connected        | 1          |
| Threads_running          | 1          |
| Uptime                   | 80380      |
+--------------------------+------------+

The status variables listed above have the following meaning:

Variable Meaning
Aborted_clients Number of connections aborted because the client died without closing the connection properly. See section A.2.10 Communication Errors / Aborted Connection.
Aborted_connects Number of tries to connect to the MySQL server that failed. See section A.2.10 Communication Errors / Aborted Connection.
Bytes_received Number of bytes received from all clients.
Bytes_sent Number of bytes sent to all clients.
Com_xxx Number of times each xxx command has been executed.
Connections Number of connection attempts to the MySQL server.
Created_tmp_disk_tables Number of implicit temporary tables on disk created while executing statements.
Created_tmp_tables Number of implicit temporary tables in memory created while executing statements.
Created_tmp_files How many temporary files mysqld has created.
Delayed_insert_threads Number of delayed insert handler threads in use.
Delayed_writes Number of rows written with INSERT DELAYED.
Delayed_errors Number of rows written with INSERT DELAYED for which some error occurred (probably duplicate key).
Flush_commands Number of executed FLUSH commands.
Handler_commit Number of internal COMMIT commands.
Handler_delete Number of times a row was deleted from a table.
Handler_read_first Number of times the first entry was read from an index. If this is high, it suggests that the server is doing a lot of full index scans, for example, SELECT col1 FROM foo, assuming that col1 is indexed.
Handler_read_key Number of requests to read a row based on a key. If this is high, it is a good indication that your queries and tables are properly indexed.
Handler_read_next Number of requests to read next row in key order. This will be incremented if you are querying an index column with a range constraint. This also will be incremented if you are doing an index scan.
Handler_read_prev Number of requests to read previous row in key order. This is mainly used to optimize ORDER BY ... DESC.
Handler_read_rnd Number of requests to read a row based on a fixed position. This will be high if you are doing a lot of queries that require sorting of the result.
Handler_read_rnd_next Number of requests to read the next row in the datafile. This will be high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
Handler_rollback Number of internal ROLLBACK commands.
Handler_update Number of requests to update a row in a table.
Handler_write Number of requests to insert a row in a table.
Key_blocks_used The number of used blocks in the key cache. You can use this value to determine how much of the key cache is in use. See the discussion of key_buffer_size in section 13.5.3.4 SHOW VARIABLES.
Key_read_requests The number of requests to read a key block from the cache.
Key_reads The number of physical reads of a key block from disk.
Key_write_requests The number of requests to write a key block to the cache.
Key_writes The number of physical writes of a key block to disk.
Max_used_connections The maximum number of connections in use simultaneously.
Not_flushed_key_blocks Keys blocks in the key cache that has changed but hasn't yet been flushed to disk.
Not_flushed_delayed_rows Number of rows waiting to be written in INSERT DELAY queues.
Open_tables Number of tables that are open.
Open_files Number of files that are open.
Open_streams Number of streams that are open (used mainly for logging).
Opened_tables Number of tables that have been opened.
Rpl_status Status of failsafe replication. (Not yet in use).
Select_full_join Number of joins without keys (If this is not 0, you should carefully check the indexes of your tables).
Select_full_range_join Number of joins where we used a range search on reference table.
Select_range Number of joins where we used ranges on the first table. (It's normally not critical even if this is big.)
Select_scan Number of joins where we did a full scan of the first table.
Select_range_check Number of joins without keys where we check for key usage after each row (If this is not 0, you should carefully check the indexes of your tables).
Questions Number of queries sent to the server.
Slave_open_temp_tables Number of temporary tables currently open by the slave thread
Slave_running Is ON if this is a slave that is connected to a master.
Slow_launch_threads Number of threads that have taken more than slow_launch_time to create.
Slow_queries Number of queries that have taken more than long_query_time seconds. See section 5.7.5 The Slow Query Log.
Sort_merge_passes Number of merges passes the sort algoritm have had to do. If this value is large you should consider increasing sort_buffer.
Sort_range Number of sorts that were done with ranges.
Sort_rows Number of sorted rows.
Sort_scan Number of sorts that were done by scanning the table.
ssl_xxx Variables used by SSL; Not yet implemented.
Table_locks_immediate Number of times a table lock was acquired immediately. Available after 3.23.33.
Table_locks_waited Number of times a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication. Available after 3.23.33.
Threads_cached Number of threads in the thread cache.
Threads_connected Number of currently open connections.
Threads_created Number of threads created to handle connections.
Threads_running Number of threads that are not sleeping.
Uptime How many seconds the server has been up.

Some comments about the above:

13.5.3.4 SHOW VARIABLES

SHOW [GLOBAL | SESSION] VARIABLES [LIKE wild]

SHOW VARIABLES shows the values of some MySQL system variables. The options GLOBAL and SESSION are new in MySQL 4.0.3. With GLOBAL you will get the variables that will be used for new connections to MySQL. With SESSION you will get the values that are in effect for the current connection. If you are not using either option, SESSION is used.

If the default values are unsuitable, you can set most of these variables using command-line options when mysqld starts up. See section 5.2.1 mysqld Command-line Options. It is also possible to change most variables with the SET statement. See section 7.5.6 SET Syntax.

The output from SHOW VARIABLES resembles that shown in the following list, though the format and numbers may differ somewhat. You can also get this information using the mysqladmin variables command. 

+---------------------------------+------------------------------+
| Variable_name                   | Value                        |
+---------------------------------+------------------------------|
| back_log                        | 50                           |
| basedir                         | /usr/local/mysql             |
| bdb_cache_size                  | 8388572                      |
| bdb_log_buffer_size             | 32768                        |
| bdb_home                        | /usr/local/mysql             |
| bdb_max_lock                    | 10000                        |
| bdb_logdir                      |                              |
| bdb_shared_data                 | OFF                          |
| bdb_tmpdir                      | /tmp/                        |
| bdb_version                     | Sleepycat Software: ...      |
| binlog_cache_size               | 32768                        |
| bulk_insert_buffer_size         | 8388608                      |
| character_set                   | latin1                       |
| character_sets                  | latin1 big5 czech euc_kr     |
| concurrent_insert               | ON                           |
| connect_timeout                 | 5                            |
| convert_character_set           |                              |
| datadir                         | /usr/local/mysql/data/       |
| delay_key_write                 | ON                           |
| delayed_insert_limit            | 100                          |
| delayed_insert_timeout          | 300                          |
| delayed_queue_size              | 1000                         |
| flush                           | OFF                          |
| flush_time                      | 0                            |
| ft_boolean_syntax               | + -><()~*:""&|               |
| ft_min_word_len                 | 4                            |
| ft_max_word_len                 | 84                           |
| ft_query_expansion_limit        | 20                           |
| ft_stopword_file                | (built-in)                   |
| have_bdb                        | YES                          |
| have_innodb                     | YES                          |
| have_isam                       | YES                          |
| have_raid                       | NO                           |
| have_symlink                    | DISABLED                     |
| have_openssl                    | YES                          |
| have_query_cache                | YES                          |
| init_file                       |                              |
| innodb_additional_mem_pool_size | 1048576                      |
| innodb_buffer_pool_size         | 8388608                      |
| innodb_data_file_path           | ibdata1:10M:autoextend       |
| innodb_data_home_dir            |                              |
| innodb_file_io_threads          | 4                            |
| innodb_force_recovery           | 0                            |
| innodb_thread_concurrency       | 8                            |
| innodb_flush_log_at_trx_commit  | 1                            |
| innodb_fast_shutdown            | ON                           |
| innodb_flush_method             |                              |
| innodb_lock_wait_timeout        | 50                           |
| innodb_log_arch_dir             |                              |
| innodb_log_archive              | OFF                          |
| innodb_log_buffer_size          | 1048576                      |
| innodb_log_file_size            | 5242880                      |
| innodb_log_files_in_group       | 2                            |
| innodb_log_group_home_dir       | ./                           |
| innodb_mirrored_log_groups      | 1                            |
| interactive_timeout             | 28800                        |
| join_buffer_size                | 131072                       |
| key_buffer_size                 | 16773120                     |
| key_cache_block_size            | 1024                         |
| key_cache_division_limit        | 100                          |
| key_cache_age_threshold         | 300                          |
| language                        | /usr/local/mysql/share/...   |
| large_files_support             | ON                           |
| local_infile                    | ON                           |
| locked_in_memory                | OFF                          |
| log                             | OFF                          |
| log_update                      | OFF                          |
| log_bin                         | OFF                          |
| log_slave_updates               | OFF                          |
| log_slow_queries                | OFF                          |
| log_warnings                    | OFF                          |
| long_query_time                 | 10                           |
| low_priority_updates            | OFF                          |
| lower_case_table_names          | 0                            |
| max_allowed_packet              | 1047552                      |
| max_binlog_cache_size           | 4294967295                   |
| max_binlog_size                 | 1073741824                   |
| max_connections                 | 100                          |
| max_connect_errors              | 10                           |
| max_delayed_threads             | 20                           |
| max_heap_table_size             | 16777216                     |
| max_join_size                   | 4294967295                   |
| max_relay_log_size              | 0                            |
| max_sort_length                 | 1024                         |
| max_user_connections            | 0                            |
| max_tmp_tables                  | 32                           |
| max_write_lock_count            | 4294967295                   |
| myisam_max_extra_sort_file_size | 268435456                    |
| myisam_repair_threads           | 1                            |
| myisam_max_sort_file_size       | 2147483647                   |
| myisam_recover_options          | force                        |
| myisam_sort_buffer_size         | 8388608                      |
| net_buffer_length               | 16384                        |
| net_read_timeout                | 30                           |
| net_retry_count                 | 10                           |
| net_write_timeout               | 60                           |
| open_files_limit                | 1024                         |
| pid_file                        | /usr/local/mysql/name.pid    |
| port                            | 3306                         |
| protocol_version                | 10                           |
| query_cache_limit               | 1048576                      |
| query_cache_size                | 0                            |
| query_cache_type                | ON                           |
| read_buffer_size                | 131072                       |
| read_rnd_buffer_size            | 262144                       |
| rpl_recovery_rank               | 0                            |
| safe_show_database              | OFF                          |
| server_id                       | 0                            |
| slave_net_timeout               | 3600                         |
| skip_external_locking           | ON                           |
| skip_networking                 | OFF                          |
| skip_show_database              | OFF                          |
| slow_launch_time                | 2                            |
| socket                          | /tmp/mysql.sock              |
| sort_buffer_size                | 2097116                      |
| sql_mode                        |                              |
| table_cache                     | 64                           |
| table_type                      | MYISAM                       |
| thread_cache_size               | 3                            |
| thread_stack                    | 131072                       |
| tx_isolation                    | READ-COMMITTED               |
| timezone                        | EEST                         |
| tmp_table_size                  | 33554432                     |
| tmpdir                          | /tmp/:/mnt/hd2/tmp/          |
| version                         | 4.0.4-beta                   |
| wait_timeout                    | 28800                        |
+---------------------------------+------------------------------+

Each option is described here. Values for buffer sizes, lengths, and stack sizes are given in bytes. You can specify values with a suffix of `K' or `M' to indicate kilobytes or megabytes. For example, 16M indicates 16 megabytes. The case of suffix letters does not matter; 16M and 16m are equivalent:

The manual section that describes tuning MySQL contains some information of how to tune the above variables. See section 7.5.2 Tuning Server Parameters.

13.5.3.5 SHOW [BDB] LOGS

SHOW LOGS shows you status information about existing log files. It currently only displays information about Berkeley DB log files, so an alias for it (available as of MySQL 4.1.1) is SHOW BDB LOGS.

13.5.3.6 SHOW PROCESSLIST

SHOW [FULL] PROCESSLIST shows you which threads are running. You can also get this information using the mysqladmin processlist command. If you have the SUPER privilege, you can see all threads. Otherwise, you can see only your own threads. See section 13.5.4.2 KILL Syntax. If you don't use the FULL option, then only the first 100 characters of each query will be shown.

Starting from 4.0.12, MySQL reports the hostname for TCP/IP connections in hostname:client_port format to make it easier to find out which client is doing what.

This command is very useful if you get the 'too many connections' error message and want to find out what's going on. MySQL reserves one extra connection for a client with the SUPER privilege to ensure that you should always be able to login and check the system (assuming you are not giving this privilege to all your users).

Some states commonly seen in mysqladmin processlist

Most states are very quick operations. If threads last in any of these states for many seconds, there may be a problem around that needs to be investigated.

There are some other states that are not mentioned previously, but most of these are only useful to find bugs in mysqld.

13.5.3.7 SHOW GRANTS

SHOW GRANTS FOR user lists the grant commands that must be issued to duplicate the grants for a user. 

mysql> SHOW GRANTS FOR root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+

To list grants for the current session, you can find out what user the session was authenticated as by selecting the value of the CURRENT_USER() function (new in version 4.0.6). Then use that value in the SHOW GRANTS statement. See section 12.6.4 Miscellaneous Functions.

13.5.3.8 SHOW CREATE TABLE

Shows a CREATE TABLE statement that will create the given table: 

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE t (
  id INT(11) default NULL auto_increment,
  s char(60) default NULL,
  PRIMARY KEY (id)
) TYPE=MyISAM

SHOW CREATE TABLE quotes table and column names according to the value of the SQL_QUOTE_SHOW_CREATE option. section 7.5.6 SET Syntax.

13.5.3.9 SHOW WARNINGS | ERRORS

SHOW WARNINGS [LIMIT row_count]
SHOW ERRORS [LIMIT row_count]

This command is implemented in MySQL 4.1.0.

It shows the errors, warnings and notes that one got for the last command. The errors/warnings are reset for each new command that uses a table.

The MySQL server sends back the total number of warnings and errors you got for the last commend; This can be retrieved by calling mysql_warning_count().

Up to max_error_count messages are stored (Global and thread specific variable).

You can retrieve the number of errors from @error_count and warnings from @warning_count.

SHOW WARNINGS shows all errors, warnings and notes you got for the last command while SHOW ERRORS only shows you the errors. 

mysql> DROP TABLE IF EXISTS no_such_table;
mysql> SHOW WARNINGS;

+-------+------+-------------------------------+
| Level | Code | Message                       |
+-------+------+-------------------------------+
| Note  | 1051 | Unknown table 'no_such_table' |
+-------+------+-------------------------------+

Note that in MySQL 4.1.0 we have just added the frame work for warnings and not many MySQL command do yet generate warnings. 4.1.1 supports all kind of warnings for LOAD DATA INFILE and DML statements such as INSERT, UPDATE and ALTER commands.

For example, here is a simple case which produces conversion warnings for a insert statement. 

mysql> create table t1(a tinyint NOT NULL, b char(4));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(10,'mysql'),(NULL,'test'),(300,'open source');
Query OK, 3 rows affected, 4 warnings (0.15 sec)
Records: 3  Duplicates: 0  Warnings: 4

mysql> show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1263 | Data truncated for column 'b' at row 1                        |
| Warning | 1261 | Data truncated, NULL supplied to NOT NULL column 'a' at row 2 |
| Warning | 1262 | Data truncated, out of range for column 'a' at row 3          |
| Warning | 1263 | Data truncated for column 'b' at row 3                        |
+---------+------+---------------------------------------------------------------+
4 rows in set (0.00 sec)

Maximum number of warnings can be specified using the server variable 'max_error_count', SET max_error_count=[count]; By default it is 64. In case to disable warnings, simply reset this variable to '0'. In case if max_error_count is 0, then still the warning count represents how many warnings have occurred, but none of the messages are stored.

For example, consider the following ALTER table statement for the above example, which returns only one warning message even though total warnings occurred is 3 when you set max_error_count=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> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1263 | Data truncated for column 'b' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql>

13.5.3.10 SHOW TABLE TYPES

SHOW TABLE TYPES

This command is implemented in MySQL 4.1.0.

SHOW TABLE TYPES shows you status information about the table types. This is particulary useful for checking if a table type is supported; or to see what is the default table type is. 

mysql> SHOW TABLE TYPES;

+--------+---------+-----------------------------------------------------------+
| Type   | Support | Comment                                                   |
+--------+---------+-----------------------------------------------------------+
| MyISAM | DEFAULT | Default type from 3.23 with great performance             |
| HEAP   | YES     | Hash based, stored in memory, useful for temporary tables |
| MERGE  | YES     | Collection of identical MyISAM tables                     |
| ISAM   | YES     | Obsolete table type; Is replaced by MyISAM                |
| InnoDB | YES     | Supports transactions, row-level locking and foreign keys |
| BDB    | NO      | Supports transactions and page-level locking              |
+--------+---------+-----------------------------------------------------------+
6 rows in set (0.00 sec)

The 'Support' option DEFAULT indicates whether the particular table type is supported, and which is the default type. If the server is started with --default-table-type=InnoDB, then the InnoDB 'Support' field will have the value DEFAULT.

13.5.3.11 SHOW PRIVILEGES

SHOW PRIVILEGES

This command is implemented in MySQL 4.1.0.

SHOW PRIVILEGES shows the list of system privileges that the underlying MySQL server supports. 

mysql> show privileges;
+------------+-------------------------
-+-------------------------------------------------------+
| Privilege  | Context                  | Comment                                               |
+------------+--------------------------+-------------------------------------------------------+
| Select     | Tables                   | To retrieve rows from table                           |
| Insert     | Tables                   | To insert data into tables                            |
| Update     | Tables                   | To update existing rows                               |
| Delete     | Tables                   | To delete existing rows                               |
| Index      | Tables                   | To create or drop indexes                             |
| Alter      | Tables                   | To alter the table                                    |
| Create     | Databases,Tables,Indexes | To create new databases and tables                    |
| Drop       | Databases,Tables         | To drop databases and tables                          |
| Grant      | Databases,Tables         | To give to other users those privileges you possess   |
| References | Databases,Tables         | To have references on tables                          |
| Reload     | Server Admin             | To reload or refresh tables, logs and privileges      |
| Shutdown   | Server Admin             | To shutdown the server                                |
| Process    | Server Admin             | To view the plain text of currently executing queries |
| File       | File access on server    | To read and write files on the server                 |
+------------+--------------------------+-------------------------------------------------------+
14 rows in set (0.00 sec)

13.5.4 Other Adminstrative Statements

13.5.4.1 FLUSH Syntax

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

You should use the FLUSH command 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:

Option Description
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 ... is blocked. When more than max_connect_errors errors occur in a row for a given host while connection to the MySQL server, MySQL assumes 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 '...' is blocked Error. You can start mysqld with -O 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 will be incremented by one relative to the previous file. If you have used an extension in the file name, MySQL will close and reopen the update log file. See section 5.7.3 The Update Log. This is the same thing as sending the SIGHUP signal to the mysqld server.
PRIVILEGES Reloads the privileges from the grant tables in the mysql database.
QUERY CACHE Defragment the query cache to better utilize its memory. This command will not remove any queries from the cache, unlike RESET QUERY CACHE.
TABLES Closes all open tables and force all tables in use to be closed. This also flushes the query cache.
[TABLE | TABLES] tbl_name [,tbl_name...] Flushes only the given tables.
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, like Veritas, that can take snapshots in time.
STATUS Resets most status variables to zero. This is something one should only use when debugging a query. See section 1.7.1.3 How to Report Bugs or Problems.
USER_RESOURCES Resets all user resources to zero. This will enable blocked users to login again. See section 5.4.6 Limiting user resources.

Before MySQL 4.1.1, FLUSH commands are not written to the binary log. Since MySQL 4.1.1 they are written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) was used, or unless the command contained one of these arguments: LOGS, MASTER, SLAVE, TABLES WITH READ LOCK, because any of these arguments may cause problems if replicated to a slave.

You can also access some of the commands shown above with the mysqladmin utility, using the flush-hosts, flush-logs, flush-privileges, flush-status or flush-tables commands.

Take also a look at the RESET command used with replication. See section 13.5.4.4 RESET Syntax.

13.5.4.2 KILL Syntax

KILL thread_id
KILL CONNECTION thread_id
KILL QUERY thread_id

Each connection to mysqld runs in a separate thread. You can see which threads are running with the SHOW PROCESSLIST command and kill a thread with the KILL thread_id command.

As of MySQL 5.0.0, KILL allows the optional CONNECTION or QUERY modifiers:

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 only see and kill your own threads and statements.

You can also use the mysqladmin processlist and mysqladmin kill commands to examine and kill threads.

Note: You currently cannot use KILL with the Embedded MySQL Server library, because the embedded server merely runs inside the threads of the host application, it does not create connection threads of its own.

When you do a KILL, a thread-specific kill flag is set for the thread.

In most cases it may take some time for the thread to die, as the kill flag is only checked at specific intervals.

13.5.4.3 PURGE MASTER LOGS Syntax

PURGE {MASTER|BINARY} LOGS TO binlog_name
PURGE {MASTER|BINARY} LOGS BEFORE date

This command is used to delete all binary logs strictly prior to the specified binlog or date. See section 13.6.1 SQL Statements for Controlling Master Servers.

PURGE BINARY LOGS is available as a synonym for PURGE MASTER LOGS as of MySQL 4.1.1.

13.5.4.4 RESET Syntax

RESET reset_option [,reset_option] ...

The RESET command is used to clear things. It also acts as an stronger version of the FLUSH command. See section 13.5.4.1 FLUSH Syntax.

To execute RESET, you must have the RELOAD privilege.

Option Description
MASTER Deletes all binary logs listed in the index file, resetting the binlog index file to be empty. Previously named FLUSH MASTER. See section 13.6.1 SQL Statements for Controlling Master Servers.
SLAVE Makes the slave forget its replication position in the master binlogs. Previously named FLUSH SLAVE. See section 13.6.2 SQL Statements for Controlling Slave Servers.
QUERY CACHE Removes all query results from the query cache.

13.6 Replication Statements

This section describes replication-related SQL statements. One group of statements is used for controlling master servers. The other is used for controlling slave servers.

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

PURGE {MASTER|BINARY} LOGS TO 'log_name'
PURGE {MASTER|BINARY} LOGS BEFORE 'date'

Deletes all the binary logs listed in the log index that are strictly prior to the specified log or date. The logs also are removed from this list recorded in the log index file, so that the given log now becomes the first.

Example: 

PURGE MASTER LOGS TO 'mysql-bin.010';
PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26';

The BEFORE variant is available in MySQL 4.1; its date argument can be in 'YYYY-MM-DD hh:mm:ss' format. MASTER and BINARY are synonyms, though BINARY can be used only as of MySQL 4.1.1.

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

You must first check all the slaves with SHOW SLAVE STATUS to see which log they are reading, then do a listing of the logs on the master with SHOW MASTER LOGS, find the earliest log among all the slaves (if all the slaves are up to date, this will be the last log on the list), backup all the logs you are about to delete (optional) and purge up to the target log.

13.6.1.2 RESET MASTER

RESET MASTER

Deletes all binary logs listed in the index file, resetting the binlog index file to be empty.

This statement was named FLUSH MASTER before MySQL 3.23.26.

13.6.1.3 SET SQL_LOG_BIN

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. The statement is ignored if the client does not have that privilege.

13.6.1.4 SHOW BINLOG EVENTS

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 will be displayed.

This statement is available as of MySQL 4.0

13.6.1.5 SHOW MASTER STATUS

SHOW MASTER STATUS

Provides status information on the binlog of the master.

13.6.1.6 SHOW MASTER LOGS

SHOW MASTER LOGS

Lists the binary logs on the master. You should use this command before using PURGE MASTER LOGS to find out how far you should go.

13.6.1.7 SHOW SLAVE HOSTS

SHOW SLAVE HOSTS

Displays a list of slaves currently registered with the master. Note that slaves not started with the --report-host=slave_name option will not be visible in that 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

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. The possible master_def values are shown above.

The relay log options (RELAY_LOG_FILE and RELAY_LOG_POS) are available beginning with MySQL 4.0.

The SSL options (MASTER_SSL, MASTER_SSL_CA, MASTER_SSL_CAPATH, MASTER_SSL_CERT, MASTER_SSL_KEY, and MASTER_SSL_CIPHER) are available beginning with MySQL 4.1.1. You can change these options even on slaves that are compiled without SSL support. They will be saved to the `master.info' file but ignored until you use a server that has SSL support enabled.

For example: 

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;

MASTER_USER, MASTER_PASSWORD, MASTER_SSL, MASTER_SSL_CA, MASTER_SSL_CAPATH, MASTER_SSL_CERT, MASTER_SSL_KEY, and MASTER_SSL_CIPHER are information for the slave to be able to connect to its master. If you don't specify some of these informations, the non-specified informations will keep their old value. For example, if the password to connect to your MySQL master has changed, you just need to issue 

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

to tell the slave about the new password; no need to specify the information which did not change (host, port, user etc).

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

If you specify MASTER_HOST or MASTER_PORT, the slave will assume that the master server is different than before (even if you specify a host or port value value that is the same as the current value.) In this case, the old values of master binlog name and position are considered no longer applicable, so if you do not specify MASTER_LOG_FILE and MASTER_LOG_POS in the command, 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 any of them, you can't specify RELAY_LOG_FILE or RELAY_LOG_POS. If none of MASTER_LOG_FILE and MASTER_LOG_POS was specified, then the last coordinates of the slave SQL thread before CHANGE MASTER was issued, are used. This ensures that replication has no discontinuity, even if the slave SQL thread was late compared to the slave I/O thread, when you just want to change, say, the password to use. This safe behavior was introduced starting from MySQL 4.0.17 and 4.1.1. (Before these versions, the used coordinates were the last coordinates of the slave I/O thread before CHANGE MASTER was issued, which caused the SQL thread to sometimes lose some events from the master, thus breaking replication.)

CHANGE MASTER deletes all relay logs (and starts a new one), unless you specified RELAY_LOG_FILE or RELAY_LOG_POS (in that case relay logs will be kept; since MySQL 4.1.1 the RELAY_LOG_PURGE global variable will silently be set to 0). CHANGE MASTER TO updates `master.info' and `relay-log.info'.

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 on the master that the snapshot corresponds to. You can run CHANGE MASTER TO MASTER_LOG_FILE='log_name_on_master', MASTER_LOG_POS=log_offset_on_master on the slave after restoring the snapshot.

The first example above (CHANGE MASTER TO MASTER_HOST='master2.mycompany.com' etc) changes the master and master's binlog coordinates. This is when you want the slave to replicate the master. The second example, less frequently used, is when the slave has relay logs which, for some reason, you want the slave to execute again; to do this the master needn't be reachable, you just have to do CHANGE MASTER TO and start the SQL thread (START SLAVE SQL_THREAD). You can even use this out of a replication setup, on a standalone, slave-of-nobody server, to recover after a crash. Suppose your server has crashed and you have restored a backup. You want to replay the server's own binlogs (not relay logs, but regular binary logs), supposedly named `myhost-bin.*'. First make a backup copy of these binlogs in some safe place, in case you don't exactly follow the procedure below and accidentally have the server purge the binlogs. If using MySQL 4.1.1 or newer, do SET GLOBAL RELAY_LOG_PURGE=0 for additional safety. Then start the server without log-bin, with a new (different from before) server ID, with relay-log=myhost-bin (to make the server believe that these regular binlogs are relay logs) and skip-slave-start, then 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;

Then the server will read and execute its own binlogs, thus achieving crash recovery. Once the recovery is finished, run STOP SLAVE, shutdown the server, delete `master.info' and `relay-log.info', and restart the server with its original options. For the moment, specifying MASTER_HOST (even with a dummy value) is compulsory to make the server think it is a slave, and giving the server a new, different from before, server ID is also compulsory otherwise the server will see events with its ID and think it is in a circular replication setup and skip the events, which is unwanted. In the future we plan to add options to get rid of these small constraints.

13.6.2.2 LOAD DATA FROM MASTER

LOAD DATA FROM MASTER

Takes a snapshot of the master and copies it to the slave. Updates the values of MASTER_LOG_FILE and MASTER_LOG_POS so that the slave will start replicating from the correct position. Will honor table and database exclusion rules specified with replicate-* options.

Use of this statement is subject to the following conditions:

In the future, it is planned to make this statement work with InnoDB tables and to remove the need for global read lock by using the non-blocking online backup feature.

If you are loading big tables, you may have to increase the values of net_read_timeout and net_write_timeout on both your master and slave. See section 13.5.3.4 SHOW VARIABLES.

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

This statement requires that the replication account that is used to connect to the master have RELOAD and SUPER privileges on the master, SELECT privileges on all master's tables you want to load. All master's tables on which the user has no SELECT privilege will be ignored by LOAD DATA FROM MASTER; this is because the master will hide them to the user: LOAD DATA FROM MASTER calls SHOW DATABASES to know the master databases to load, but SHOW DATABASES returns only databases on which the user has some privilege. See section 13.5.3.1 Retrieving Information about Database, Tables, Columns, and Indexes. On the slave's side, the user which issues LOAD DATA FROM MASTER should have grants to drop and create the involved databases and tables.

13.6.2.3 LOAD TABLE tbl_name FROM MASTER

LOAD TABLE tbl_name FROM MASTER

Downloads a copy of the table from master to the slave. This statement is implemented mainly for debugging of LOAD DATA FROM MASTER. Requires that the account used for connecting to the master server have RELOAD and SUPER privileges on the master, and SELECT on the master table to load. On the slave's side, the user which issues LOAD TABLE FROM MASTER should have grants to drop and create the table. Please read the timeout notes in the description of LOAD DATA FROM MASTER above; they apply here, too. Please also read the limitations of LOAD DATA FROM MASTER above, they apply too (for example, LOAD TABLE FROM MASTER only works for MyISAM tables).

13.6.2.4 MASTER_POS_WAIT()

SELECT MASTER_POS_WAIT('master_log_file', master_log_pos)

This is a function, not a command. It is used to ensure that the slave has reached (read and executed up to) a given position in the master's binlog. See section 12.6.4 Miscellaneous Functions for a full description.

13.6.2.5 RESET SLAVE

RESET SLAVE

Makes the slave forget its replication position in the master's binlogs. 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 had not been totally executed by the slave SQL thread. (This is a condition likely to exist on a replication slave that is highly loaded, or if you have issued a STOP SLAVE statement.) Connection information stored in the `master.info' file is immediately reset to the values specified in the corresponding startup options, if they were specified. This information includes values such as master host, master port, master user, and master password. If the slave SQL thread was in the middle of replicating temporary tables when it was stopped, and RESET SLAVE is issued, these replicated temporary tables are deleted on the slave.

This statement was named FLUSH SLAVE before MySQL 3.23.26.

13.6.2.6 SET GLOBAL SQL_SLAVE_SKIP_COUNTER

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.

Before MySQL 4.0, omit the GLOBAL keyword from the statement.

13.6.2.7 SHOW SLAVE STATUS

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 semicolon to get a more readable vertical layout: 

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
       Slave_IO_State: Waiting for master to send event
          Master_Host: localhost
          Master_User: root
          Master_Port: 3306
        Connect_Retry: 3
      Master_Log_File: gbichot-bin.005
  Read_Master_Log_Pos: 79
       Relay_Log_File: gbichot-relay-bin.005
        Relay_Log_Pos: 548
Relay_Master_Log_File: gbichot-bin.005
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
      Replicate_Do_DB:
  Replicate_Ignore_DB:
           Last_Errno: 0
           Last_Error:
         Skip_Counter: 0
  Exec_Master_Log_Pos: 79
      Relay_Log_Space: 552
      Until_Condition: None
       Until_Log_File:
        Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File:
   Master_SSL_CA_Path:
      Master_SSL_Cert:
    Master_SSL_Cipher:
       Master_SSL_Key:
Seconds_Behind_Master: 8

Depending on your version of MySQL, you may not see all the fields just shown. In particular, several fields are present only as of MySQL 4.1.1.

The fields displayed by SHOW SLAVE STATUS have the following meanings:

Slave_IO_State
A copy of the State column of the output of SHOW PROCESSLIST for the slave I/O thread; will tell you if this thread is trying to connect to the master, waiting for events from the master, reconnecting to the master, etc. Possible states are listed in section 6.3 Replication Implementation Details. Looking at this column is necessary because, for example, the thread can be running but unsuccessfully trying to connect to the master: only this column will make you aware of the connection problem. On the opposite, the state of the SQL thread is not copied, because things are simpler for this thread: if it's running, there is no problem; if it's not, you will find the error in the Last_Error column (described below). This field is present beginning with MySQL 4.1.1.
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 master-connect-retry.
Master_Log_File
The name of the master's binlog file from which the I/O thread is currently reading.
Read_Master_Log_Pos
The position which the I/O thread has read up to in this master's binlog.
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 which the SQL thread has read and executed up to in this relay log.
Relay_Master_Log_File
The name of the master's binlog file that contains the last event executed by the SQL thread.
Slave_IO_Running
Tells whether or not the I/O thread is started.
Slave_SQL_Running
Tells whether or not the SQL thread is started.
Replicate_Do_DB, Replicate_Ignore_DB
The lists of the 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 These fields are present beginning with MySQL 4.1.1.
Last_Errno
The error number returned by the most recently executed query. A value of 0 means ``no error''.
Last_Error
The error message returned by the most recently executed query. 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 if you forgot to copy the table to the slave when setting up replication.) The empty string means ``no error''. If the Last_Error value is not empty, it will also appear as a message in the slave's error log.
Skip_Counter
The last used value for SQL_SLAVE_SKIP_COUNTER.
Exec_Master_Log_Pos
The position in the master's binlog (Relay_Master_Log_File) of the last event executed by the SQL thread. ((Relay_Master_Log_File,Exec_Master_Log_Pos) in the master's binlog 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: Until_Log_File and Until_Log_Pos indicate the log filename and position values that define the point at which the SQL thread will stop executing. These fields are present beginning with MySQL 4.1.1.
Master_SSL_Allowed, Master_SSL_CA_File, Master_SSL_CA_Path, Master_SSL_Cert, Master_SSL_Cipher, Master_SSL_Key
These fields show the The SSL parameters used by the slave to connect to the master, if any. Master_SSL_Allowed has these values: The values of the other fields correspond to the values of the --master-ca, --master-capath, --master-cert, --master-cipher, and --master-key options. These fields are present beginning with MySQL 4.1.1.
Seconds_Behind_Master
The number of seconds that have elapsed since the timestamp of the last master's event executed by the slave SQL thread. Will be NULL when no event has been executed yet, or after CHANGE MASTER and RESET SLAVE. This column can be used to know how ``late'' your slave is. It will work even though your master and slave don't have identical clocks. This field is present beginning with MySQL 4.1.1.

13.6.2.8 START SLAVE

START SLAVE [thread_name [, thread_name] ... ]
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_name = 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. Note that if START SLAVE succeeds in starting the slave threads it will return without any error. But even in that case it might be that slave threads start and then later stop (because they don't manage to connect to the master or read his binlogs or any other problem). START SLAVE will not warn you about this. You must check your slave's error log for error messages generated by the slave threads, or check that these are running fine with SHOW SLAVE STATUS.

START SLAVE requires the SUPER privilege.

As of MySQL 4.0.2, you can add IO_THREAD or SQL_THREAD options to the statement to name which of the threads to start.

As of MySQL 4.1.1, an UNTIL clause may be added to specify that the slave should start until the SQL thread reaches a given point in the master binlogs or in the slave relay logs. When the SQL thread reaches that point, it stops. If the SQL_THREAD option is specified in the statement, it starts only the SQL thread. Otherwise, it starts both slave threads. If the SQL thread is already running, the UNTIL clause is ignored and a warning is issued.

With an UNTIL clause, you must specify both a log filename and position. Do not mix master and relay log options.

Any UNTIL condition is reset by a subsequent STOP SLAVE statement, or 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 replicated 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 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 starts. It's probably best to use this option in an option file rather than on the command line, so that an unexpected server restart does not cause it to be forgotten.

The SHOW SLAVE STATUS statement includes output fields that display the current values of the UNTIL condition.

This command is called SLAVE START before MySQL 4.0.5. For the moment, SLAVE START is still accepted for backward compatibility, but is deprecated.

13.6.2.9 STOP SLAVE

STOP SLAVE [thread_name [, thread_name] ... ]

thread_name = 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 stop.

This command is called SLAVE STOP before MySQL 4.0.5. For the moment, SLAVE STOP is still accepted for backward compatibility, but is deprecated.

13.7 MySQL Full-text Search

MATCH (col1,col2,...) AGAINST (expr [IN BOOLEAN MODE | WITH QUERY EXPANSION] )

As of Version 3.23.23, MySQL has support for full-text indexing and searching. Full-text indexes in MySQL are an index of type FULLTEXT. FULLTEXT indexes are used with MyISAM tables only and can be created from CHAR, VARCHAR, or TEXT columns at CREATE TABLE time or added later with ALTER TABLE or CREATE INDEX. For large datasets, it will be much faster to load your data into a table that has no FULLTEXT index, then create the index with ALTER TABLE (or CREATE INDEX). Loading data into a table that already has a FULLTEXT index could be significantly slower.

Full-text searching is performed with the MATCH() function. 

mysql> CREATE TABLE articles (
    ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->   title VARCHAR(200),
    ->   body TEXT,
    ->   FULLTEXT (title,body)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO articles VALUES
    -> (NULL,'MySQL Tutorial', 'DBMS stands for DataBase ...'),
    -> (NULL,'How To Use MySQL Efficiently', 'After you went through a ...'),
    -> (NULL,'Optimizing MySQL','In this tutorial we will show ...'),
    -> (NULL,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    -> (NULL,'MySQL vs. YourSQL', 'In the following database comparison ...'),
    -> (NULL,'MySQL Security', 'When configured properly, MySQL ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM articles
    ->          WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

The MATCH() function performs a natural language search for a string against a text collection (a set of one or more columns included in a FULLTEXT index). The search string is given as the argument to AGAINST(). The search is performed in case-insensitive fashion. For every row in the table, MATCH() returns a relevance value, that is, a similarity measure between the search string and the text in that row in the columns named in the MATCH() list.

When MATCH() is used in a WHERE clause (see example above) the rows returned are automatically sorted with highest relevance first. Relevance values are non-negative floating-point numbers. Zero relevance means no similarity. Relevance is computed based on the number of words in the row, the number of unique words in that row, the total number of words in the collection, and the number of documents (rows) that contain a particular word.

It is also possible to perform a boolean mode search. This is explained later in the section.

The preceding example is a basic illustration showing how to use the MATCH() function. Rows are returned in order of decreasing relevance.

The next example shows how to retrieve the relevance values explicitly. As neither WHERE nor ORDER BY clauses are present, returned rows are not ordered. 

mysql> SELECT id,MATCH (title,body) AGAINST ('Tutorial') FROM articles;
+----+-----------------------------------------+
| id | MATCH (title,body) AGAINST ('Tutorial') |
+----+-----------------------------------------+
|  1 |                        0.64840710366884 |
|  2 |                                       0 |
|  3 |                        0.66266459031789 |
|  4 |                                       0 |
|  5 |                                       0 |
|  6 |                                       0 |
+----+-----------------------------------------+
6 rows in set (0.00 sec)

The following example is more complex. The query returns the relevance and still sorts the rows in order of decreasing relevance. To achieve this result, you should specify MATCH() twice. This will cause no additional overhead, because the MySQL optimizer will notice that the two MATCH() calls are identical and invoke the full-text search code only once. 

mysql> SELECT id, body, MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root') AS score
    -> FROM articles WHERE MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root');
+----+-------------------------------------+-----------------+
| id | body                                | score           |
+----+-------------------------------------+-----------------+
|  4 | 1. Never run mysqld as root. 2. ... | 1.5055546709332 |
|  6 | When configured properly, MySQL ... |   1.31140957288 |
+----+-------------------------------------+-----------------+
2 rows in set (0.00 sec)

As of Version 4.1.1, full-text search supports query expansion (in particular, its variant ``blind query expansion''). It is generally useful when a search phrase is too short, which often means that the user is relying on implied knowledge that the full-text search engine usually lacks. For example, a user searching for ``database'' may really mean that ``MySQL'', ``Oracle'', ``DB2'', and ``RDBMS'' all are phrases that should match ``databases'' and should be returned, too. This is implied knowledge. Blind query expansion (also known as automatic relevance feedback) works by performing the search twice, where the search phrase for the second search is the original search phrase concatenated with the few top found documents from the first search. Thus, if one of these documents contained the word ``databases'' and the word ``MySQL'', then the second search will find the documents that contain the word ``MySQL'' but not ``database''. Another example could be searching for books by Georges Simenon about Maigret, when a user is not sure how to spell ``Maigret''. Then, searching for ``Megre and the reluctant witnesses'' will find only ``Maigret and the Reluctant Witnesses'' without query expansion, but all books with the word ``Maigret'' on the second pass of a search with query expansion. Note: because blind query expansion tends to increase noise significantly, by returning non-relevant documents, it's only meaningful to use when a search phrase is rather short.

MySQL uses a very simple parser to split text into words. A ``word'' is any sequence of characters consisting of letters, digits, `'', and `_'. Any ``word'' that is present in the stopword list or is just too short is ignored. The default minimum length of words that will be found by full-text searches is four characters. This can be changed as described in section 13.7.2 Fine-tuning MySQL Full-text Search.

Every correct word in the collection and in the query is weighted according to its significance in the query or collection. This way, a word that is present in many documents will have lower weight (and may even have a zero weight), because it has lower semantic value in this particular collection. Otherwise, if the word is rare, it will receive a higher weight. The weights of the words are then combined to compute the relevance of the row.

Such a technique works best with large collections (in fact, it was carefully tuned this way). For very small tables, word distribution does not reflect adequately their semantic value, and this model may sometimes produce bizarre results. 

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL');
Empty set (0.00 sec)

The search for the word MySQL produces no results in the above example, because that word is present in more than half the rows. As such, it is effectively treated as a stopword (that is, a word with zero semantic value). This is the most desirable behavior -- a natural language query should not return every second row from a 1 GB table.

A word that matches half of rows in a table is less likely to locate relevant documents. In fact, it will most likely find plenty of irrelevant documents. We all know this happens far too often when we are trying to find something on the Internet with a search engine. It is with this reasoning that such rows have been assigned a low semantic value in this particular dataset.

As of Version 4.0.1, MySQL can also perform boolean full-text searches using the IN BOOLEAN MODE modifier. 

mysql> SELECT * FROM articles WHERE MATCH (title,body)
    ->     AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
+----+------------------------------+-------------------------------------+
| id | title                        | body                                |
+----+------------------------------+-------------------------------------+
|  1 | MySQL Tutorial               | DBMS stands for DataBase ...        |
|  2 | How To Use MySQL Efficiently | After you went through a ...        |
|  3 | Optimizing MySQL             | In this tutorial we will show ...   |
|  4 | 1001 MySQL Tricks            | 1. Never run mysqld as root. 2. ... |
|  6 | MySQL Security               | When configured properly, MySQL ... |
+----+------------------------------+-------------------------------------+

This query retrieved all the rows that contain the word MySQL (note: the 50% threshold is not used), but that do not contain the word YourSQL. Note that a boolean mode search does not automatically sort rows in order of decreasing relevance. You can see this from result of the preceding query, where the row with the highest relevance (the one that contains MySQL twice) is listed last, not first. A boolean full-text search can also work even without a FULLTEXT index, although it would be slow.

The boolean full-text search capability supports the following operators:

+
A leading plus sign indicates that this word must be present in every row returned.
-
A leading minus sign indicates that this word must not be present in any row returned.
By default (when neither plus nor minus is specified) the word is optional, but the rows that contain it will be rated higher. This mimicks the behavior of MATCH() ... AGAINST() without the IN BOOLEAN MODE modifier.
< >
These two operators are used to change a word's contribution to the relevance value that is assigned to a row. The < operator decreases the contribution and the > operator increases it. See the example below.
( )
Parentheses are used to group words into subexpressions.
~
A leading tilde acts as a negation operator, causing the word's contribution to the row relevance to be negative. It's useful for marking noise words. A row that contains such a word will be rated lower than others, but will not be excluded altogether, as it would be with the - operator.
*
An asterisk is the truncation operator. Unlike the other operators, it should be appended to the word, not prepended.
"
The phrase, that is enclosed in double quotes ", matches only rows that contain this phrase literally, as it was typed.

And here are some examples:

apple banana
find rows that contain at least one of these words.
+apple +juice
... both words.
+apple macintosh
... word ``apple'', but rank it higher if it also contain ``macintosh''.
+apple -macintosh
... word ``apple'' but not ``macintosh''.
+apple +(>turnover <strudel)
... ``apple'' and ``turnover'', or ``apple'' and ``strudel'' (in any order), but rank ``apple pie'' higher than ``apple strudel''.
apple*
... ``apple'', ``apples'', ``applesauce'', and ``applet''.
"some words"
... ``some words of wisdom'', but not ``some noise words''.

13.7.1 Full-text Restrictions

13.7.2 Fine-tuning MySQL Full-text Search

Unfortunately, full-text search has few user-tunable parameters yet, although adding some is very high on the TODO. If you have a MySQL source distribution (see section 2.3 MySQL Installation Using a Source Distribution), you can exert more control over full-text searching behavior.

Note that full-text search was carefully tuned for the best searching effectiveness. Modifying the default behavior will, in most cases, only make the search results worse. Do not alter the MySQL sources unless you know what you are doing!

The full-text variables described in the following list must be set at server startup time. You cannot modify them dynamically while the server is running.

For full-text changes that require you to rebuild your FULLTEXT indexes, the easiest way to do so for a MyISAM table is to use the following statement, which rebuilds the index file: 

mysql> REPAIR TABLE tbl_name QUICK;

13.7.3 Full-text Search TODO

13.8 MySQL Query Cache

From version 4.0.1, MySQL server features a Query Cache. When in use, the query cache stores the text of a SELECT query together with the corresponding result that was sent to the client. If an identical query is later received, the server will retrieve the results from the query cache rather than parsing and executing the same query again.

NOTE: The query cache does not return stale data. When data is modified, any relevant entries in the query cache are flushed.

The query cache is extremely useful in an environment where (some) tables don't change very often and you have a lot of identical queries. This is a typical situation for many web servers that use a lot of dynamic content.

Below is some performance data for the query cache. (These results were generated by running the MySQL benchmark suite on a Linux Alpha 2 x 500 MHz with 2 GB RAM and a 64 MB query cache):

13.8.1 How the Query Cache Operates

Queries are compared before parsing, thus 

SELECT * FROM tbl_name

and 

Select * from tbl_name

are regarded as different queries for query cache, so queries need to be exactly the same (byte for byte) to be seen as identical. In addition, a query may be seen as different if for instance one client is using a new communication protocol format or another character set than another client.

Queries that uses different databases, uses different protocol versions or the uses different default character sets are considered different queries and cached separately.

The cache does work for SELECT SQL_CALC_FOUND_ROWS ... and SELECT FOUND_ROWS() ... type queries because the number of found rows is also stored in the cache.

If query result was returned from query cache then status variable Com_select will not be increased, but Qcache_hits will be. See section 13.8.4 Query Cache Status and Maintenance.

If a table changes (INSERT, UPDATE, DELETE, TRUNCATE, ALTER or DROP TABLE|DATABASE), then all cached queries that used this table (possibly through a MRG_MyISAM table!) become invalid and are removed from the cache.

Transactional InnoDB tables that have been changed will be invalidated when a COMMIT is performed.

In MySQL 4.0, the query cache is disabled inside of transactions (it does not return results). Beginning with MySQL 4.1.1, the query cache will also work inside of transactions when using InnoDB tables (it will use the table version number to detect if the data is still current or not).

Before MySQL 5.0, a query that begins with a leading comment might be cached, but could not be fetched from the cache. This problem is fixed in MySQL 5.0.

A query cannot be cached if it contains one of the functions:
Function Function Function
User-Defined Functions CONNECTION_ID FOUND_ROWS
GET_LOCK RELEASE_LOCK LOAD_FILE
MASTER_POS_WAIT NOW SYSDATE
CURRENT_TIMESTAMP CURDATE CURRENT_DATE
CURTIME CURRENT_TIME DATABASE
ENCRYPT (with one parameter) LAST_INSERT_ID RAND
UNIX_TIMESTAMP (without parameters) USER BENCHMARK

Nor can a query be cached if it contains user variables, references the mysql system database, is of the form SELECT ... IN SHARE MODE, SELECT ... INTO OUTFILE ..., SELECT ... INTO DUMPFILE ... or of the form SELECT * FROM AUTOINCREMENT_FIELD IS NULL (to retrieve last insert ID - ODBC work around).

However, FOUND_ROWS() will return the correct value, even if the preceding query was fetched from the cache.

In case a query does not use any tables, or uses temporary tables, or if the user has a column privilege for any of the involved tables, that query will not be cached.

Before a query is fetched from the query cache, MySQL will check that the user has SELECT privilege to all the involved databases and tables. If this is not the case, the cached result will not be used.

13.8.2 Query Cache Configuration

The query cache adds a few MySQL system variables for mysqld which may be set in a configuration file, on the command-line when starting mysqld.

Inside a thread (connection), the behavior of the query cache can be changed from the default. The syntax is as follows:

QUERY_CACHE_TYPE = OFF | ON | DEMAND QUERY_CACHE_TYPE = 0 | 1 | 2

Option Description
0 or OFF Don't cache or retrieve results.
1 or ON Cache all results except SELECT SQL_NO_CACHE ... queries.
2 or DEMAND Cache only SELECT SQL_CACHE ... queries.

13.8.3 Query Cache Options in SELECT

There are two possible query cache related parameters that may be specified in a SELECT query:

Option Description
SQL_CACHE If QUERY_CACHE_TYPE is DEMAND, allow the query to be cached. If QUERY_CACHE_TYPE is ON, this is the default. If QUERY_CACHE_TYPE is OFF, do nothing.
SQL_NO_CACHE Make this query non-cachable, don't allow this query to be stored in the cache.

13.8.4 Query Cache Status and Maintenance

With the FLUSH QUERY CACHE command you can defragment the query cache to better utilize its memory. This command will not remove any queries from the cache. FLUSH TABLES also flushes the query cache.

The RESET QUERY CACHE command removes all query results from the query cache. You can check whether the query cache is present in your MySQL version: 

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.00 sec)

You can monitor query cache performance in SHOW STATUS:

Variable Description
Qcache_queries_in_cache Number of queries registered in the cache.
Qcache_inserts Number of queries added to the cache.
Qcache_hits Number of cache hits.
Qcache_lowmem_prunes Number of queries that were deleted from cache because of low memory.
Qcache_not_cached Number of non-cached queries (not cachable, or due to QUERY_CACHE_TYPE).
Qcache_free_memory Amount of free memory for query cache.
Qcache_free_blocks Number of free memory blocks in query cache.
Qcache_total_blocks Total number of blocks in query cache.

Total number of queries = Qcache_inserts + Qcache_hits + Qcache_not_cached.

The query cache uses variable length blocks, so Qcache_total_blocks and Qcache_free_blocks may indicate query cache memory fragmentation. After FLUSH QUERY CACHE only a single (big) free block remains.

Note: Every query needs a minimum of two blocks (one for the query text and one or more for the query results). Also, every table that is used by a query needs one block, but if two or more queries use same table only one block needs to be allocated.

You can use the Qcache_lowmem_prunes status variable to tune the query cache size. It counts the number of queries that have been removed from the cache to free up memory for caching new queries. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache.

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