| This section describes the syntax and meaning of each of MySQL's SQL statements. A statement will fail if you do not have the necessary privileges to perform it. For example, USE db_name fails if you have no permission to access the database db_name. ALTER DATABASE ALTER DATABASE db_name action_list This statement changes global database characteristics. The action_list specifies one or more actions separated by commas. However, there is currently only one possible action: [DEFAULT] CHARACTER SET charset charset may be a character set name or DEFAULT to have the database use the current server character set by default. ALTER DATABASE requires the ALTER privilege for the database. This statement was introduced in MySQL 4.1. ALTER TABLE ALTER [IGNORE] TABLE tbl_name action_list ALTER TABLE allows you to rename tables or modify their structure. To use it, specify the table name tbl_name then give the specifications for one or more actions to be performed on the table. The IGNORE keyword comes into play if the action could produce duplicate key values in a unique index in the new table. Without IGNORE, the effect of the ALTER TABLE statement is canceled. With IGNORE, the rows that duplicate values for unique key values are deleted. Except for table renaming operations, ALTER TABLE works by creating from the original table a new one that incorporates the changes to be made. If an error occurs, the new table is discarded and the original remains unchanged. If the operation completes successfully, the original table is discarded and replaced by the new one. During the operation, other clients may read from the original table. Any clients that try to update the table are blocked until the ALTER TABLE statement completes, at which point the updates are applied to the new table. action_list specifies one or more actions separated by commas. Each action is performed in turn. An action may be any of the following: ADD [COLUMN] col_declaration [FIRST | AFTER col_name] Adds a column to the table. col_declaration is the column declaration; it has the same format as that used for the CREATE TABLE statement. The column becomes the first column in the table if the FIRST keyword is given or is placed after the named column if AFTER col_name is given. If the column placement is not specified, the column becomes the last column of the table. ALTER TABLE t ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY; ALTER TABLE t ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; ALTER TABLE t ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY AFTER suffix; ADD [COLUMN] (create_definition,...) Adds columns or indexes to the table. Each create_definition is a column or index definition, in the same format as for CREATE TABLE. This syntax was introduced in MYSQL 3.23.11. ADD [CONSTRAINT name] FOREIGN KEY [index_name] (index_columns)reference_definition Adds a foreign key definition to a table. This is supported only for InnoDB tables. The foreign key is based on the columns named in index_columns, which is a list of one or more columns in the table separated by commas. Any CONSTRAINT or index_name, if given, are ignored. reference_definition defines how the foreign key relates to the parent table. The syntax is as described in the entry for CREATE TABLE.ADD FOREIGN KEY was introduced in MySQL 3.23.50. (There is a corresponding DROP FOREIGN KEY clause, but currently it is just parsed and ignored.) ALTER TABLE child ADD FOREIGN KEY (par_id) REFERENCES parent (par_id) ON DELETE CASCADE; ADD FULLTEXT [KEY | INDEX] [index_name] (index_columns) Adds a FULLTEXT index to a MyISAM table. The index is based on the columns named in index_columns, which is a list of one or more columns in the table separated by commas. ADD FULLTEXT was introduced in MySQL 3.23.23. ALTER TABLE poetry ADD FULLTEXT (author,title,stanza); ADD INDEX [index_name] (index_columns) Adds an index to the table. The index is based on the columns named in index_columns, which is a list of one or more columns in the table separated by commas. For CHAR and VARCHAR columns, you can index a prefix of the column, using col_name(n) syntax to index the first n bytes of column values. For BLOB and TEXT columns, you must specify a prefix value; you cannot index the entire column. If the index name index_name is not specified, a name is chosen automatically based on the name of the first indexed column. ADD PRIMARY KEY (index_columns) Adds a primary key on the given columns. The key is given the name PRIMARY. index_columns is specified as for the ADD INDEX clause. An error occurs if a primary key already exists or if any of the columns are defined to allow NULL values. ALTER TABLE president ADD PRIMARY KEY (last_name, first_name); ADD UNIQUE [index_name] (index_columns) Adds a unique-valued index to tbl_name. index_name and index_columns are specified as for the ADD INDEX clause. ALTER TABLE absence ADD UNIQUE id_date (student_id, date); ALTER [COLUMN] col_name {SET DEFAULT value | DROP DEFAULT} Modifies the given column's default value, either to the specified value or by dropping the current default value. In the latter case, a new default value is assigned, as described in the entry for the CREATE TABLE statement. ALTER TABLE event ALTER type SET DEFAULT 'Q'; ALTER TABLE event ALTER type DROP DEFAULT; CHANGE [COLUMN] col_name col_declaration [FIRST | AFTER col_name] Changes a column's name and definition. col_name is the column's current name, and col_declaration is the declaration to which the column should be changed. col_declaration is in the same format as that used for the CREATE TABLE statement. Note that the declaration must include the new column name, so if you want to leave the name unchanged, it's necessary to specify the same name twice. FIRST or AFTER be used as of MySQL 4.0.1 and have the same effect as for ADD COLUMN. ALTER TABLE student CHANGE name name VARCHAR(40); ALTER TABLE student CHANGE name student_name CHAR(30) NOT NULL; DISABLE KEYS For a MyISAM table, this disables the updating of non-unique indexes that normally occurs when the table is changed. ENABLE KEYS may be used to re-enable index updating. DISABLE KEYS was introduced in MySQL 4.0. ALTER TABLE score DISABLE KEYS; DROP [COLUMN] col_name [RESTRICT | CASCADE] Removes the given column from the table. If the column is part of any indexes, it is removed from those indexes. If all columns from an index are removed, the index is removed as well. ALTER TABLE president DROP suffix; The RESTRICT and CASCADE keywords have no effect. They are parsed for compatibility with code ported from other databases, but ignored. DROP INDEX index_name Removes the given index from the table. ALTER TABLE member DROP INDEX name; DROP PRIMARY KEY Removes the primary key from the table. If a table has no unique index that was created as a PRIMARY KEY but has one or more UNIQUE indexes, the first one of those is dropped. ALTER TABLE president DROP PRIMARY KEY; ENABLE KEYS For a MyISAM table, re-enables updating for non-unique indexes that have been disabled with DISABLE KEYS. ENABLE KEYS was introduced in MySQL 4.0. ALTER TABLE score ENABLE KEYS; MODIFY [COLUMN] col_declaration [FIRST | AFTER col_name] Changes the declaration of a column. The column declaration col_declaration is given, using the same format for column descriptions as is shown in the entry for the CREATE TABLE statement. The declaration begins with a column name, which is how the column that is to be modified is identified. MODIFY was introduced in MySQL 3.22.16. FIRST or AFTER may be used as of MySQL 4.0.1 and have the same effect as for ADD COLUMN. ALTER TABLE student MODIFY name VARCHAR(40) DEFAULT '' NOT NULL; ORDER BY col_list Sorts the rows in the table according to the columns named in col_list, which should be a list of one or more columns in the table separated by columns. The default sort order is ascending. A column name can be followed by ASC or DESC to specify ascending or descending order explicitly. Sorting a table this way may improve performance of subsequent queries that retrieve records in the same order. This is mostly useful for a table that will not be modified afterward, because rows will not remain in order if the table is modified after performing the ORDER BY operation. This option was introduced in MySQL 3.23.28. ALTER TABLE score ORDER BY event_id, student_id; RENAME [TO | AS] new_tbl_name Renames the table tbl_name to new_tbl_name. ALTER TABLE president RENAME TO prez; Prior to MySQL 3.23.17, there is no keyword between RENAME and the new table name. From 3.23.17 on, TO is optional there, and from 3.23.23 on, TO or AS are optional there. table_options Specifies table options of the sort that may be given in the table_options part of a CREATE TABLE statement. ALTER TABLE score TYPE = MYISAM CHECKSUM = 1; ALTER TABLE sayings CHARACTER SET utf8; Any version-specific constraints on the availability of a given table option are as described in the entry for the CREATE TABLE statement. ANALYZE TABLE ANALYZE {TABLE | TABLES} tbl_name [, tbl_name] ... This statement causes MySQL to analyze each of the named tables, storing the distribution of key values present in each table's indexes. It works for MyISAM and BDB tables and requires SELECT and INSERT privileges on each table. After analysis, the Cardinality column of the output from SHOW INDEX indicates the number of distinct values in the indexes. Information from the analysis can be used by the optimizer during subsequent queries to perform certain types of joins more quickly. Analyzing a table requires a read lock, which prevents that table from being updated during the operation. If you run ANALYZE TABLE on a table that has already been analyzed and that has not been changed since, no analysis is performed. ANALYZE TABLE produces output in the format described under the entry for CHECK TABLE. ANALYZE TABLE was introduced in MySQL 3.23.14. BACKUP TABLE BACKUP {TABLE | TABLES} tbl_name [, tbl_name] ... TO 'dir_name' Copies the named table or tables to the directory named by 'dir_name', which should be the full pathname to a directory on the server host where the backup files should be written. BACKUP TABLE works only for MyISAM tables and requires the SELECT and FILE privileges. It copies the table definition and data files (the .frm and .MYD files), which are the minimum required to restore the table. Index files are not copied because they can be re-created as necessary (using RESTORE TABLE) from the definition and data files. Tables are read-locked individually as they are backed up. If you are backing up a set of tables, it's possible that tables named later in the table list will be modified while earlier tables are being backed up, or vice versa. If you want to ensure that all the tables are backed up as a group with the contents they have when BACKUP TABLE begins executing, use LOCK TABLE to lock them first and then unlock them after backing up with UNLOCK TABLE. Of course, this will cause the tables to be unavailable for a longer time to other clients that want to update the tables. The files created by BACKUP TABLE will be owned by the account used to run the server. Any existing backup files for a table are overwritten. BACKUP TABLE was introduced in MySQL 3.23.25. Back up table t by creating files t.frm and t.MYD in the directory /var/mysql/bkup: BACKUP TABLE t TO '/var/mysql/bkup'; BEGIN BEGIN [WORK] Begins a transaction by disabling auto-commit mode until the next COMMIT or ROLLBACK statement. Statements executed while auto-commit mode is disabled will be committed or rolled back as a unit. After the transaction has been committed or rolled back, auto-commit mode is restored to the state it was in prior to BEGIN. To manipulate auto-commit mode explicitly, use SET AUTOCOMMIT. (See the description for the SET statement.) Issuing a BEGIN while a transaction is in progress causes the transaction to be committed implicitly. BEGIN was introduced in MySQL 3.23.17. BEGIN WORK was introduced as a synonym in MySQL 3.23.19. CHANGE MASTER CHANGE MASTER TO master_defs For use on replication slave servers to change the parameters that indicate which master host to use, how to connect to it, or which logs to use. master_defs is a comma-separated list of one or more parameter definitions in param =value format. The allowable definitions are as follows: MASTER_CONNECT_RETRY = n The number of seconds to wait between attempts to connect to the master MASTER_HOST = 'host_name' The host on which the master server is running MASTER_LOG_FILE = 'file_name' The name of the master's binary update log file to use for replication MASTER_LOG_POS = n The position within the master log file from which to begin or resume replication MASTER_PASSWORD = 'pass_val' The password to use for connecting to the master server MASTER_PORT = n The port number to use for connecting to the master server MASTER_USER = 'user_name' The username to use for connecting to the master server RELAY_LOG_FILE = 'file_name' The slave relay log file name RELAY_LOG_POS = n The current position within the slave relay log With the exception of the hostname or port number, only those parameters that you specify explicitly are changed. Changes to the host or port normally indicate that you're switching to a different master server, so in those cases, the binary update log filename and position are reset to the empty string and zero. CHANGE MASTER was introduced in MySQL 3.23.23. The RELAY_LOG_FILE and RELAY_LOG_POS options were introduced in MySQL 4.0.2 (replication relay logs were not instituted until then). CHECK TABLE CHECK {TABLE | TABLES} tbl_name [, tbl_name] ... [options] This statement checks tables for errors. It works with MyISAM tables and also with InnoDB tables as of MySQL 3.23.39. It requires the SELECT privilege on each table. options, if given, is a list naming one or more of the following options (not separated by commas): CHANGED Check only those tables that have been changed since they were last checked or that have not been closed properly. EXTENDED Perform an extended check that attempts to ensure that the table is fully consistent. For example, it verifies that each key in each index points to a data row. This option can be slow. FAST Check only those tables that have not been closed properly. MEDIUM Check the index, scan the data rows for problems, and perform a checksum verification. This is the default if no options are given. QUICK Don't scan the data rows, just the index. CHECK TABLE returns information about the result of the operation for example: mysql> CHECK TABLE t; +--------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------+-------+----------+----------+ | test.t | check | status | OK | +--------+-------+----------+----------+ ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE also return information in this format. Table indicates the table on which the operation was performed. Op indicates the type of operation and has a value of check, analyze, optimize, or repair. The Msg_type and Msg_text columns provide information about the result of the operation. CHECK TABLE was introduced in MySQL 3.23.13, but it does not work under Windows prior to 3.23.25. The QUICK, FAST, and MEDIUM options were added in MySQL 3.23.16, 3.23.23, and 3.23.31, respectively. From 3.23.15 to 3.23.25, only a single option is allowed and TYPE = must precede it; after that, TYPE = is deprecated, and multiple options are allowed. COMMIT COMMIT Commits changes made by statements that are part of the current transaction to record those changes permanently in the database. COMMIT works only for transaction-safe table types. (For non-transactional table types, statements are committed as they are executed.) COMMIT has no effect if auto-commit mode has not been disabled with BEGIN or by setting AUTOCOMMIT to 0. Some statements implicitly end any current transaction, as if a COMMIT had been performed: ALTER TABLE BEGIN CREATE INDEX DROP DATABASE DROP INDEX DROP TABLE LOAD MASTER DATA LOCK TABLES RENAME TABLE SET AUTOCOMMIT = 1 TRUNCATE TABLE UNLOCK TABLES (if tables currently are locked) COMMIT was introduced in MySQL 3.23.14. CREATE DATABASE CREATE DATABASE [IF NOT EXISTS] db_name [[DEFAULT] CHARACTER SET charset] Creates a database with the given name. The statement fails if you don't have the proper privilege to create it. Attempts to create a database with a name that already exists normally result in an error; if the IF NOT EXISTS clause is specified, the database is not created but no error occurs. This clause was introduced in MySQL 3.23.12. As of MySQL 4.1, the DEFAULT CHARACTER SET clause can be used to specify a default character set attribute for the database. charset can be a character set name or DEFAULT to have tables in the database use the current server character set by default. Database attributes are stored in the db.opt file in the database directory. CREATE FUNCTION CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING | REAL | INTEGER} SONAME 'shared_library_name' Specifies a user-defined function (UDF) to be loaded into the func table in the mysql database. function_name is the name by which you want to refer to the function in SQL statements. The keyword following RETURNS indicates the return type of the function. The 'shared_library_name' string names the pathname of the file that contains the executable code for the function. The AGGREGATE keyword, if given, indicates that the function is an aggregate (group) function like SUM() or MAX(). AGGREGATE was introduced in MySQL 3.23.5. CREATE FUNCTION requires that the server be built as a dynamically linked binary (not as a static binary) because the UDF mechanism requires dynamic linking. For instructions on writing user-defined functions, refer to the MySQL Reference Manual. CREATE INDEX CREATE [UNIQUE | FULLTEXT] INDEX index_name ON tbl_name (index_columns) Adds an index named index_name to the table tbl_name. This statement is handled as an ALTER TABLE ADD INDEX, ALTER TABLE ADD UNIQUE, or ALTER TABLE ADD FULLTEXT statement, according to the absence or presence of the UNIQUE or FULLTEXT keywords. See the entry for ALTER TABLE for details. CREATE INDEX cannot be used to create a PRIMARY KEY; use ALTER TABLE instead. If you want to create several indexes on a table, it's preferable to use ALTER TABLE directly; you can add them all with a single statement, which is faster than adding them individually. CREATE INDEX is functional only as of MySQL 3.22. The option of creating FULLTEXT indexes was introduced in MySQL 3.23.23. CREATE TABLE CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [[IGNORE | REPLACE] [AS] select_statement] create_definition: { col_declaration [reference_definition] | [CONSTRAINT symbol] PRIMARY KEY (index_columns) | [CONSTRAINT symbol] UNIQUE [INDEX | KEY] [index_name] (index_columns) | {INDEX | KEY} [index_name] (index_columns) | FULLTEXT [INDEX | KEY] [index_name] (index_columns) | [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_columns) [reference_definition] | [CONSTRAINT symbol] CHECK (expr) } col_declaration: col_name col_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY] [UNIQUE [KEY]] [COMMENT 'string'] reference_definition: REFERENCES tbl_name (index_columns) [ON DELETE reference_action] [ON UPDATE reference_action] [MATCH FULL | MATCH PARTIAL] reference_action: {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT} The CREATE TABLE statement creates a new table named tbl_name in the current database. If the name is specified as db_name.tbl_name, the table is created in the named database. If the TEMPORARY keyword is given, the table exists only until the current client connection ends (either normally or abnormally) or until a DROP TABLE statement is issued. A temporary table is visible only to the client that created it. Normally, attempts to create a table with a name that already exists result in an error. No error occurs under two conditions. First, if the IF NOT EXISTS clause is specified, the table is not created but no error occurs. Second, if TEMPORARY is specified and the original table is not a temporary table, the new temporary table is created, but the original table named tbl_name becomes hidden to the client while the temporary table exists. The original table remains visible to other clients. The original table becomes visible again either at the next client session, if an explicit DROP TABLE is issued for the temporary table, or if the temporary table is renamed to some other name. The create_definition list names the columns and indexes that you want to create. The list is optional if you create the table by means of a trailing SELECT statement. The table_options clause allows you to specify various properties for the table. If a trailing select_statement is specified (in the form of an arbitrary SELECT statement), the table is created using the result set returned by the SELECT statement. These clauses are described more fully in the following sections. The IF NOT EXISTS clause, the table_options clause, and the ability to create a table from the result of a SELECT statement were introduced in MySQL 3.23. TEMPORARY tables were introduced in MySQL 3.23.2. Column and index definitions. A create_definition can be a column or index definition, a FOREIGN KEY clause, or a CHECK clause. CHECK is parsed for compatibility with other database systems, but otherwise ignored. FOREIGN KEY is treated similarly, except for InnoDB tables. A column declaration col_declaration begins with a column name col_name and a type col_type and may be followed by several optional keywords. The column type may be any of the types listed in Appendix B, Column Type Reference. See that appendix for type-specific attributes that apply to the columns you want to declare. Other optional keywords that may follow the column type are as follows: NULL or NOT NULL Specifies that the column may or may not contain NULL values. If neither is specified, NULL is the default. DEFAULT default_value Specifies the default value for the column. This cannot be used for BLOB or TEXT types. A default value must be a constant, specified as a number, a string, or NULL. If no default is specified, a default value is assigned. For columns that may take NULL values, the default is NULL.. For columns that may not be NULL, the default is assigned as follows: For numeric columns, the default is 0, except for AUTO_INCREMENT columns. For AUTO_INCREMENT, the default is the next number in the column sequence. For date and time types other than TIMESTAMP, the default is the "zero" value for the type (for example, '0000-00-00' for DATE). For TIMESTAMP, the default is the current date and time for the first TIMESTAMP column in a table, and the "zero" value for any following TIMESTAMP columns. For string types other than ENUM, the default is the empty string. For ENUM, the default is the first enumeration element. AUTO_INCREMENT This keyword applies only to integer column types. An AUTO_INCREMENT column is special in that when you insert NULL into it, the value actually inserted is the next value in the column sequence. (Typically, this is one greater than the current maximum value in the column.) AUTO_INCREMENT values start at 1 by default. For MyISAM tables (and for HEAP tables as of MySQL 4.1), the first value may be specified explicitly with the AUTO_INCREMENT = n table option. The column must also be specified as a UNIQUE index or PRIMARY KEY and should be NOT NULL. There may be at most one AUTO_INCREMENT column per table. PRIMARY KEY Specifies that the column is a PRIMARY KEY. A PRIMARY KEY must be NOT NULL. UNIQUE [KEY] Specifies that the column is a UNIQUE index. This attribute may be specified as of MySQL 3.23. COMMENT 'string' Specifies a descriptive comment to be associated with the column. Prior to MySQL 4.1, this attribute is parsed but ignored. As of 4.1, it is remembered and displayed by SHOW CREATE TABLE and SHOW FULL COLUMNS. The PRIMARY KEY, UNIQUE, INDEX, KEY, and FULLTEXT clauses specify indexes. PRIMARY KEY and UNIQUE specify indexes that must contain unique values. INDEX and KEY are synonymous; they specify indexes that may contain duplicate values. The index is based on the columns named in index_columns, each of which must be a column in tbl_name. If there are multiple columns, they should be separated by commas. For CHAR and VARCHAR columns, you can index a prefix of the column, using col_name(n) syntax to index the first n bytes of column values. (The exception is that InnoDB tables do not allow index prefixes.) For BLOB and TEXT columns, you must specify a prefix value; you cannot index the entire column. Prefixes for columns named in a FULLTEXT index are ignored if given. If the index name index_name is not specified, a name is chosen automatically based on the name of the first indexed column. FULLTEXT indexes are allowed only for MyISAM tables and only for TEXT columns and non-BINARY CHAR and VARCHAR columns. Indexed columns must be declared NOT NULL for ISAM tables, and for HEAP tables prior to MySQL 4.0.2. PRIMARY KEY columns must always be declared NOT NULL. Table options. The table_options clause is available as of MySQL 3.23 (some of the options appeared later, as indicated in the descriptions). Table options can include one or more of the options in the following list. If multiple options are present, they should not be separated by commas. Each specifier applies to all table types unless otherwise noted. AUTO_INCREMENT = n The first AUTO_INCREMENT value to be generated for the table. This option is effective only for MyISAM tables, and for HEAP tables as of MYSQL 4.1. AVG_ROW_LENGTH = n The approximate average row length of your table. For MyISAM tables, MySQL uses the product of the AVG_ROW_LENGTH and MAX_ROWS values to determine the maximum data file size. The MyISAM handler can use internal row pointers with a table from 1 to 8 bytes wide. The default pointer width is wide enough to allow tables up to 4GB. If you require a larger table (and your operating system supports larger files), the MAX_ROWS and AVG_ROW_LENGTH table options allow the MyISAM handler to adjust the internal pointer width. A large product of these values causes the handler to use wider pointers. (Conversely, a small product allows the handler to use smaller pointers. This won't save you much space if the table is small anyway, but if you have many small tables, the cumulative savings may be significant.) [DEFAULT] CHARACTER SET charset Specifies the table's default character set. charset can be a character set name or DEFAULT to use the database character set if it is defined or the server character set if not. This option determines which character set is used for character columns that are declared without an explicit character set. In the following example, c1 will be assigned the sjis character set and c2 the ujis character set: CREATE TABLE t ( c1 CHAR(50) CHARACTER SET sjis, c2 CHAR(50) ) CHARACTER SET ujis; This option also applies to subsequent table modifications made with ALTER TABLE for character column changes that do not name a character set explicitly. CHARACTER SET was introduced in MySQL 4.1. It can be given in any of several variant synonymous forms. The following are all equivalent: CHARACTER SET charset CHARSET = charset CHARSET charset These synonymous forms can also be used in other places where character sets can be specified, such as in column definitions or in the CREATE DATABASE and ALTER DATABASE statements. CHECKSUM = {0 | 1} If this is set to 1, MySQL maintains a checksum for each table row. There is a slight penalty for updates to the table, but the presence of checksums improves the table checking process. (MyISAM tables only.) COMMENT = 'string' A comment for the table. The maximum length is 60 characters. This comment is shown by SHOW CREATE TABLE and SHOW TABLE STATUS. DATA DIRECTORY = 'dir_name' This option is used for MyISAM tables only, and indicates the directory where the data (.MYD) file should be written. 'dir_name' must be a full pathname. This option was introduced in MySQL 4.0, and works only if the server is started without the --skip-symlink option. On some operating systems, such as Mac OS X, FreeBSD, and BSDI, symlinks are not thread-safe and are disabled by default. DELAY_KEY_WRITE = {0 | 1} If this is set to 1, the index cache is flushed only occasionally for the table, rather than after each insert operation. (MyISAM tables only.) INDEX DIRECTORY = 'dir_name' This option is used for MyISAM tables only and indicates the directory where the index (.MYI) file should be written. 'dir_name' must be a full pathname. This option was introduced in MySQL 4.0, and is subject to the same constraints as the DATA DIRECTORY option. INSERT_METHOD = {NO | FIRST | LAST} This is used for MERGE tables to specify how to insert rows. A value of NO disallows inserts entirely. Values of FIRST or LAST indicate that rows should be inserted into the first or last of the MyISAM tables that make up the MERGE table. This option was introduced in MySQL 4.0. MAX_ROWS = n The maximum number of rows you plan to store in the table. The description of the AVG_ROW_LENGTH option indicates how this value is used. (MyISAM tables only.) MIN_ROWS = n The minimum number of rows you plan to store in the table. This option can be used for HEAP tables to give the HEAP handler a hint about how to optimize memory usage. PACK_KEYS = {0 | 1 | DEFAULT} This option controls index compression for MyISAM and ISAM tables, which allows runs of similar index values to be compressed. The usual effect is an update penalty and an improvement in retrieval performance. A value of 0 specifies no index compression. A value of 1 specifies compression for string (CHAR and VARCHAR) values and (for MyISAM tables) numeric index values. As of MySQL 4.0, a value of DEFAULT can be used, which specifies compression only for long string columns. PASSWORD = 'string' Specifies a password for encrypting the table's description file. This option normally has no effect; it enabled only for certain support contract customers. RAID_TYPE = {1 | STRIPED | RAID0} RAID_CHUNKS = n RAID_CHUNKSIZE =n These options are used together and are available as of MySQL 3.23.12 for use with MyISAM to achieve larger effective table sizes. The options are ineffective unless MySQL was configured with the --with-raid option at build time. The default RAID_TYPE value is STRIPED; the other two types actually are just aliases for STRIPED. RAID_CHUNKS and RAID_CHUNKSIZE control the allocation of space to be used for the table's data. The server creates several directories under the database directory (the number is determined by the RAID_CHUNKS value) and creates a data file named tbl_name.MYD in each. As rows are added to the table, the server writes to the file in the first directory until it fills up and then proceeds to the next directory. The size of the file in each directory is controlled by the value of RAID_CHUNKSIZE, which is measured in MB (1024 bytes). Directories are named using hexadecimal digits in the sequence 00, 01, and so forth. For example, if RAID_CHUNKS is 256 and RAID_CHUNKSIZE is 1000, the server creates 256 directories named 00 through ff, and writes up to 1000MB of data to the file in each directory. ROW_FORMAT = {DEFAULT | FIXED | DYNAMIC | COMPRESSED} This option applies only to MyISAM tables and specifies the row storage type. The option can be used as of MySQL 3.23.6. TYPE = {ISAM | MYISAM | MERGE | HEAP | BDB | INNODB} Specifies the table storage format. The characteristics of these storage formats are described in the "Table Types" section in Chapter 3, "MySQL SQL Syntax and Use." The default format for MySQL as of version 3.23 is MyISAM unless the server has been configured otherwise (either at build time or if the server was started with a --default-table-type option). MRG_MYISAM, BERKELEYDB, and INNOBASE are synonyms for MERGE, BDB, and INNODB, respectively. If you specify a table type that is legal but for which no table handler is available, MySQL uses the default storage format. If you give an invalid value for the option, an error results. This option was introduced in MySQL 3.23. Prior to that, CREATE TABLE always creates tables in ISAM format. UNION = (tbl_list) This option is used for MERGE tables. It specifies the list of MyISAM tables that make up the MERGE table. Trailing SELECT statement. If a select_statement clause is specified (as a trailing SELECT query), the table is created using the contents of the result set returned by the query. Rows that duplicate values on a unique index are either ignored or they replace existing rows according to whether IGNORE or REPLACE is specified. If neither is specified, the statement aborts with an error. Foreign key support. The InnoDB table handler provides foreign key support. A foreign key in a child table is indicated by FOREIGN KEY, an optional index name, a list of the columns that make up the foreign key, and a REFERENCES definition. The index name, if given, is ignored. The REFERENCES definition names the parent table and columns to which the foreign key refers and indicates what to do when a parent table record is deleted. The actions that InnoDB implements are CASCADE (delete the corresponding child table records) and SET NULL (set the foreign key columns in the corresponding child table records to NULL). The RESTRICT, NO ACTION, and SET DEFAULT actions are parsed but ignored. ON UPDATE and MATCH clauses in REFERENCE definitions are parsed but ignored. (If you specify a foreign key definition for a table type other than InnoDB, the entire definition is ignored.) The following statements demonstrate some ways in which CREATE TABLE can be used. Create a table with three columns. The id column is a PRIMARY KEY, and the last_name and first_name columns are indexed together: CREATE TABLE customer ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, last_name CHAR(30) NOT NULL, first_name CHAR(20) NOT NULL, PRIMARY KEY (id), INDEX (last_name, first_name) ); Create a temporary table and make it a HEAP (in-memory) table for greater speed: CREATE TEMPORARY TABLE tmp_table (id MEDIUMINT NOT NULL UNIQUE, name CHAR(40)) TYPE = HEAP; Create a table as a copy of another table: CREATE TABLE prez_copy SELECT * FROM president; Create a table using only part of another table: CREATE TABLE prez_alive SELECT last_name, first_name, birth FROM president WHERE death IS NULL; If creation declarations are specified for a table created and populated by means of a trailing SELECT statement, the declarations are applied after the table contents have been inserted into the table. For example, you can declare that a selected column should be made into a PRIMARY KEY: CREATE TABLE new_tbl (PRIMARY KEY (a)) SELECT a, b, c FROM old_tbl; As of MySQL 4.1, you can specify declarations for the columns in the new table to override the definitions that would be used by default based on the characteristics of the result set: CREATE TABLE new_tbl (a INT NOT NULL AUTO_INCREMENT, b DATE, PRIMARY KEY (a)) SELECT a, b, c FROM old_tbl; DELETE DELETE [LOW_PRIORITY] [QUICK] FROM tbl_name [WHERE where_expr] [ORDER BY ...] [LIMIT n] DELETE [LOW_PRIORITY] [QUICK] tbl_name [, tbl_name] ... FROM tbl_name [, tbl_name] ... [WHERE where_expr] DELETE [LOW_PRIORITY] [QUICK] FROM tbl_name [, tbl_name] ... USING tbl_name [, tbl_name] ... [WHERE where_expr] The first form of the DELETE statement deletes rows from the table tbl_name. The rows deleted are those that match the conditions specified in the WHERE clause: DELETE FROM score WHERE event_id = 14; DELETE FROM member WHERE expiration < CURDATE(); If the WHERE clause is omitted, all records in the table are deleted. Specifying LOW_PRIORITY causes the statement to be deferred until no clients are reading from the table. LOW_PRIORITY was introduced in MySQL 3.22.5. For MyISAM tables, specifying QUICK can make the statement quicker; the MyISAM handler will not perform its usual index tree leaf merging. QUICK was introduced in MySQL 3.23.25. If the LIMIT clause is given, the value n specifies the maximum number of rows that will be deleted. LIMIT was introduced in MySQL 3.22.7. With ORDER BY, rows are deleted in the resulting sort order. Combined with LIMIT, this provides more precise control over which rows are deleted. ORDER BY was introduced in MySQL 4.0.0 and has same syntax as for SELECT. Normally, DELETE returns the number of records deleted. DELETE with no WHERE clause will empty the table, and you may find that, prior to MySQL 4, the server optimizes this special case by dropping and recreating the table from scratch rather than deleting records on a row-by-row basis. This is extremely fast, but a row count of zero may be returned. To obtain a true count, specify a WHERE clause that matches all records for example: DELETE FROM tbl_name WHERE 1; There is a significant performance penalty for row-by-row deletion, however. If you don't need a row count, another way to empty a table is to use TRUNCATE TABLE. The second and third forms of DELETE allow rows to be deleted from multiple tables at once. They also allow you to identify the rows to delete based on joins between tables. These forms are available as of MySQL 4.0.0 and 4.0.2, respectively. Names in the list of tables from which rows are to be deleted can be given as tbl_name or tbl_name.*; the latter form is supported for ODBC compatibility. To delete rows in t1 having id values that match those in t2, use the first multiple-table syntax like this: DELETE t1 FROM t1, t2 WHERE t1.id = t2.id; or the second syntax like this: DELETE FROM t1 USING t1, t2 WHERE t1.id = t2.id; DESCRIBE {DESCRIBE | DESC} tbl_name [col_name | 'pattern'] {DESCRIBE | DESC} select_statement DESCRIBE with a table name produces the same kind of output as SHOW COLUMNS. See the SHOW entry for more information. With this syntax, a trailing column name restricts output to information for the given column. A trailing string is interpreted as a pattern, as for the LIKE operator, and restricts output to those columns having names that match the pattern. Display output for the last_name column of the president table: DESCRIBE president last_name; Display output for both the last_name and first_name columns of the president table: DESCRIBE president '%name'; DESCRIBE with a SELECT statement is a synonym for EXPLAIN. See the EXPLAIN entry for more information. (DESCRIBE and EXPLAIN actually are completely synonymous in MySQL, but DESCRIBE is more often used to obtain table descriptions and EXPLAIN to obtain SELECT statement execution information.) DO DO expr [, expr] ... Evaluates the expressions without returning any results. This makes DO more convenient than SELECT for expression evaluation, because you need not deal with a result set. For example, DO can be used for setting variables or for invoking functions that you are interested in primarily for their side effects rather than for their return values. DO @sidea := 3, @sideb := 4, @sidec := SQRT(@sidea*@sidea+@sideb*@sideb); DO RELEASE_LOCK('mylock'); DO was introduced in MySQL 3.23.47. DROP DATABASE DROP DATABASE [IF EXISTS] db_name Drops (removes) the given database. After you drop a database, it's gone, so be careful. The statement fails if the database does not exist (unless you specify IF EXISTS) or if you don't have the proper privilege. The IF EXISTS clause can be specified to suppress the error message that normally results if the database does not exist. IF EXISTS was introduced in MySQL 3.22.2. A database is represented by a directory under the data directory. If you have put non-table files in that directory, those files are not deleted by the DROP DATABASE statement. In that case, the database directory itself is not removed, either, and its name will continue to be listed by SHOW DATABASES. DROP FUNCTION DROP FUNCTION function_name Removes a user-defined function that was previously loaded with CREATE FUNCTION. DROP INDEX DROP INDEX index_name ON tbl_name Drops the index index_name from the table tbl_name. This statement is handled as an ALTER TABLE DROP INDEX statement. See the entry for ALTER TABLE for details. DROP INDEX cannot be used to drop a PRIMARY KEY; use ALTER TABLE instead. DROP INDEX is functional only as of MySQL 3.22. DROP TABLE DROP TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE] Drops the named table or tables from the database they belong to. If the IF EXISTS clause is given, dropping a non-existent table is not an error. IF EXISTS was introduced in MySQL 3.22.2. The RESTRICT and CASCADE keywords have no effect. They are parsed for compatibility with code ported from other databases, but ignored. These keywords can be used as of MySQL 3.23.29. EXPLAIN EXPLAIN tbl_name [col_name | 'pattern'] EXPLAIN select_statement The first form of this statement is equivalent to DESCRIBE tbl_name. See the description of the DESCRIBE statement for more information. The second form of the EXPLAIN statement provides information about how MySQL would execute the SELECT statement following the EXPLAIN keyword. EXPLAIN SELECT score.* FROM score, event WHERE score.event_id = event.event_id AND event.event_id = 14; Output from EXPLAIN consists of one or more rows containing the following columns: table The table to which the output row refers. type The type of join that MySQL will perform. The possible types are, from best to worst: system, const, eq_ref, ref, range, index, and ALL., The better types are more restrictive, meaning that MySQL has to look at fewer rows from the table when performing the retrieval. possible_keys The indexes that MySQL considers candidates for finding rows in the table named in the table column. A value of NULL means that no indexes were found. key The index that MySQL actually will use for finding rows in the table. A value of NULL indicates that no index will be used. key_len How much of the index will be used. This can be less than the full index row length if MySQL will use a leftmost prefix of the index. ref The values to which MySQL will compare index values. The word const or '???' means the comparison is against a constant; a column name indicates a column-to-column comparison. rows An estimate of the number of rows from the table that MySQL must examine to perform the query. The product of the values in this column is an estimate of the total number of row combinations that must be examined from all tables. Extra Using index indicates that MySQL can retrieve information for the table using only information in the index without examining the data file (this used to appear as Only index). Using where indicates the use of the information in the WHERE clause of the SELECT statement (this used to appear as where used). FLUSH FLUSH option [, option] ... Flushes various internal caches used by the server. Each option value should be one of the following items: DES_KEY_FILE Reload the DES key file used for encryption and decryption by the DES_ENCRYPT() and DES_DECRYPT() functions. This option was introduced in MySQL 4.0.1. HOSTS Flushes the host cache. LOGS Flushes the log files by closing and reopening them. MASTER This has been renamed to RESET MASTER, which should be used instead. PRIVILEGES Reloads the grant tables. If you modify the tables with GRANT or REVOKE, the server reloads its in-memory copies of the tables automatically. If you modify the tables directly using statements such as INSERT or UPDATE, it's necessary to tell the server to reload them explicitly. This statement also resets the resource management limits to zero, like the USER_RESOURCES option. QUERY CACHE Flush the query cache to defragment it, without removing statements from the cache. (To clear the cache entirely, use RESET QUERY CACHE.) This option was introduced in MySQL 4.0.1. SLAVE This has been renamed to RESET SLAVE, which should be used instead. STATUS Reinitializes the status variables. This option was introduced in MySQL 3.22.11. TABLES [tbl_name [,tbl_name] ...] Without any table names, closes any open tables in the table cache. As of MySQL 3.23.23, you can specify an optional comma-separated list of one or more table names to flush specific tables rather than the entire table cache. Also as of that version, FLUSH TABLE is a synonym for FLUSH TABLES. If the query cache is operational, FLUSH TABLES also flushes the query cache. TABLES WITH READ LOCK Flushes all tables in all databases and then places a read lock on them, which is held until you issue an UNLOCK TABLES statement. This statement allows clients to read tables but prohibits any changes from being made, which is useful for getting a backup for your entire server with the guarantee that no tables will change during the backup period. Of course, from the client point of view, this means that the period during which updates are disallowed is greater. This option was introduced in MySQL 3.23.18. USER_RESOURCES Reset the counters for account resource management limits (such as MAX_QUERIES_PER_HOUR). Accounts that have reached their limits will once again be able to proceed in their activities. This option was introduced in MySQL 4.0.2. The FLUSH statement requires the RELOAD privilege. It was introduced in MySQL 3.22.9; some of its options were introduced later, as noted in the preceding descriptions. GRANT GRANT priv_type [(column_list)] [, priv_type [(column_list)] ] ... ON {*.* | * | db_name.* | db_name.tbl_name | tbl_name} TO account [IDENTIFIED BY 'password'] [, account [IDENTIFIED BY 'password'] ] ... [REQUIRE security_options] [WITH grant_or_resource_options] The GRANT statement grants access privileges to one or more MySQL users. The priv_type value specifies the privileges to be granted. It consists of privilege types chosen from the following list. ALL is used by itself. For the other privileges, you can specify one or more of them as a comma-separated list. ALL signifies the combination of all the other privileges, except for GRANT OPTION, which must be granted separately or by adding a WITH GRANT OPTION clause. | Privilege Specifier | Operation Allowed by Privilege | | ALTER | Alter tables and indexes | | CREATE | Create databases and tables | | CREATE TEMPORARY TABLES | Create temporary tables | | DELETE | Delete existing rows from tables | | DROP | Drop (remove) databases and tables | | EXECUTE | Execute stored procedures (reserved for future use) | | FILE | Read and write files on the server host | | GRANT OPTION | Grant the account's privileges to other accounts | | INDEX | Create or drop indexes | | INSERT | Insert new rows into tables | | LOCK TABLES | Explicitly lock tables with LOCK TABLES statements | | PROCESS | View information about the threads executing within the server | | REFERENCES | Unused (reserved for future use) | | RELOAD | Reload the grant tables or flush the logs or caches | | REPLICATION CLIENT | Ask about master and slave server locations | | REPLICATION SLAVE | Act as a replication slave server | | SELECT | Retrieve existing rows from tables | | SHOW DATABASES | Issue SHOW DATABASES statements | | SHUTDOWN | Shut down the server | | SUPER | Kill threads and perform other supervisory operations | | UPDATE | Modify existing table rows | | ALL | All operations (except GRANT); ALL PRIVILEGES is a synonym | | USAGE | A special "no privileges" privilege | The LOCK TABLES privilege can be exercised only over tables for which you also have the SELECT privilege, but it allows you to place any kind of lock, not just read locks. You can always view or kill your own threads. The SUPER privilege allows you to view or kill any threads. The CREATE TEMPORARY TABLES, EXECUTE, LOCK TABLES, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, and SUPER privileges were added in MySQL 4.0.2. Prior to 4.0.2, operations that require SHOW DATABASES, the replication privileges, or SUPER were controlled by the SELECT, FILE, and PROCESS privileges, respectively. The ON clause specifies how widely the privileges should be granted, as shown in the following table: | Privilege Specifier | Level at Which Privileges Apply | | ON *.* | Global privileges; all databases, all tables | | ON * | Global privileges if no default database has been selected, database-level privileges for the current database otherwise | | ON db_name.* | Database-level privileges; all tables in the named database | | ON db_name.tbl_name | Table-level privileges; all columns in the named table | | ON tbl_name | Table-level privileges; all columns in the named table in the default database | When a table is named in the ON clause, privileges can be made column-specific by naming one or more comma-separated columns in the column_list clause. (This applies only for the INSERT, REFERENCES, SELECT, and UPDATE privileges, which are the only ones that can be granted on a column-specific basis.) The TO clause specifies one or more accounts to which the privileges should be granted. Each account name consists of a specifier in 'user_name'@'host_name' format and can be followed by an optional IDENTIFIED BY clause to specify a password. The user_name and host_name parts need not be quoted if they contain no special characters. However, if quoted, they must be quoted separately. (For example, bill@%.com should be quoted as 'bill'@'%.com', not as 'bill@%.com'.) The user_name can be a name or an empty string (''); the latter specifies an anonymous user. host_name can be given as localhost, a hostname, an IP address, or a pattern matching a domain name or network number. The pattern characters are '%' and '_', with the same meaning as for the LIKE operator. A user_name specified alone with no hostname is equivalent to 'user_name'@'%'. As of MySQL 3.23, it's also possible for host_name to be an IP number/netmask pair in n.n.n.n/m.m.m.m notation, where n.n.n.n indicates the IP address and m.m.m.m indicates the netmask to use for the network number. The IDENTIFIED BY clause, if given, assigns a password to the user. The password should be specified in plain text, without using the PASSWORD() function, in contrast to the way passwords are specified for the SET PASSWORD statement. If the account already exists and IDENTIFIED BY is specified, the new password replaces the old one. The existing password remains unchanged otherwise. The REQUIRE clause, if given, allows you to specify that secure connections are to be used and what kinds of information the client is required to supply. The REQUIRE keyword may be followed by: NONE to indicate that secure connections are not required. A generic connection type of SSL to require that connections for the account use SSL. X509 to require that the user supply a valid X509 certificate. In this case, the client can present any X509 certificate; it doesn't matter what its contents are other than that it is valid. One or more of the following options to require that the connection be established with certain characteristics: CIPHER 'str' requires the connection to be established with 'str' as its encryption cipher. ISSUER 'str' requires the client certificate to have 'str' as the certificate issuer value. SUBJECT 'str' requires the client certificate to have 'str' as the certificate subject value. If you give more than one of these options, they can optionally be separated by AND. The order of the options doesn't matter. The WITH clause, if given, can specify that the account is able to grant other accounts the privileges that it holds itself. As of MySQL 4.0.2, it can also be used to place limits on the account's resource consumption. The allowable options are shown next. You can specify more than one option; their order does not matter. GRANT OPTION This account is allowed to grant its own privileges to other accounts, including the right to grant privileges. MAX_CONNECTIONS_PER_HOUR n The account is allowed to make n connections to the server per hour. MAX_QUERIES_PER_HOUR n The account is allowed to issue n queries per hour. MAX_UPDATES_PER_HOUR n The account is allowed to issue n queries that modify data per hour. For the options that set numeric limits, a value of 0 means "no limit." The GRANT statement was introduced in MySQL 3.22.11. The REQUIRE clause was introduced in MySQL 4.0.0; its NONE option was introduced in 4.0.4, at which time the AND option separator between REQUIRE options also was made optional. The resource management options were introduced in 4.0.2. The following statements demonstrate some ways in which the GRANT statement can be used. See Chapter 11, "General MySQL Administration," for other examples. See Chapter 12, "Security," for information on setting up secure connections using SSL. Create an account for paul who can access all tables in the sampdb database from any host. The following two statements are equivalent because a missing hostname part in the account identifier is equivalent to %: GRANT ALL ON sampdb.* TO 'paul' IDENTIFIED BY 'secret'; GRANT ALL ON sampdb.* TO 'paul'@'%' IDENTIFIED BY 'secret'; Create an account with read-only privileges for the tables in the menagerie database. The lookonly user can connect from any host in the xyz.com domain: GRANT SELECT ON menagerie.* TO 'lookonly'@'%.xyz.com' IDENTIFIED BY 'ragweed'; Create an account with full privileges, but only for the member table in the sampdb database. The member_mgr user can connect from a single host: GRANT ALL ON sampdb.member TO 'member_mgr'@'boa.snake.net' IDENTIFIED BY 'doughnut'; Create a superuser who can do anything, including granting privileges to other users, but who must connect from the local host: GRANT ALL ON *.* TO 'superduper'@'localhost' IDENTIFIED BY 'homer' WITH GRANT OPTION; Create an anonymous user of the menagerie database who can connect from the local host with no password: GRANT ALL ON menagerie.* TO ''@'localhost'; Create an account for a remote user who must connect via SSL and present a valid X509 certificate: GRANT ALL ON privatedb.* TO 'paranoid'@'%.mydom.com' IDENTIFIED BY 'keepout' REQUIRE X509; Create an account for a limited-access user who can issue only 100 queries per hour, of which at most 10 can be updates: GRANT ALL ON test.* TO 'caleb'@'localhost' IDENTIFIED BY 'rosepetal' WITH MAX_QUERIES_PER_HOUR 100 MAX_UPDATES_PER_HOUR 10; HANDLER HANDLER tbl_name OPEN [AS alias_name] HANDLER tbl_name READ [FIRST | NEXT] [where_clause] [limit_clause] HANDLER tbl_name READ index_name [FIRST | NEXT | PREV | LAST | [< | <= | = | => | >] (expr_list)] [where_clause] [limit_clause] HANDLER tbl_name CLOSE HANDLER provides a low-level interface to the MyISAM and InnoDB table handlers that bypasses the optimizer and accesses table contents directly. To access a table through the HANDLER interface, first use HANDLER ... OPEN to open it. The table remains available for use until you issue a HANDLER ... CLOSE statement to close it explicitly or until or the connection terminates. While the table is open, use HANDLER ... READ to access the table's contents. HANDLER provides no protection against concurrent updates. It does not lock the table, so it's possible for the table to be modified while HANDLER has it open, and there is no guarantee that the modifications will be reflected in the records that you read from the file. HANDLER was introduced for MyISAM tables in MySQL 4.0.0 and extended to work with InnoDB tables in 4.0.3. INSERT INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(column_list)] VALUES (expr [, expr] ...) [, (...)] ... INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(column_list)] SELECT ... INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name SET col_name=expr [, col_name=expr] ... Inserts rows into an existing table tbl_name and returns the number of rows inserted. The INTO keyword is optional as of MySQL 3.22.5. LOW_PRIORITY causes the statement to be deferred until no clients are reading from the table. LOW_PRIORITY was introduced in MySQL 3.22.5. DELAYED causes the rows to be placed into a queue for later insertion, and the statement returns immediately so that the client can continue on without waiting. However, in this case, LAST_INSERT_ID() will not return the AUTO_INCREMENT value for any AUTO_INCREMENT column in the table. DELAYED inserts were introduced in MySQL 3.22.15; they work only for ISAM and MyISAM tables. If IGNORE is specified, rows that duplicate values for unique keys in existing rows are discarded. If duplicate values occur without IGNORE, an error occurs and no more rows are inserted. IGNORE was introduced in MySQL 3.22.10. The first form of INSERT requires a VALUES() list that specifies all values to be inserted. If no column_list is given, the VALUES() list must specify one value for each column in the table. If a column_list is given consisting of one or more comma-separated column names, one value per column must be specified in the VALUES() list. Columns not named in the column list are set to their default values. As of MySQL 3.22.5, multiple value lists can be specified, allowing multiple rows to be inserted using a single INSERT statement. As of MySQL 3.23.3, the column_list and VALUES() list can be empty, which can be used as follows to create a record for which all columns are set to their default values: INSERT INTO t () VALUES(); As of MySQL 4.0.3, the word DEFAULT can be used in a VALUES() list to set a column to its default value explicitly without knowing what the default value is. The second form of INSERT retrieves records according to the SELECT statement and inserts them into tbl_name. The SELECT statement must select as many columns as are in tbl_name or as many columns as are named in column_list if a column list is specified. When a column list is specified, any columns not named in the list are set to their default values. You cannot select records from the same table into which you are inserting them. The third form of INSERT, available as of MySQL 3.22.10, inserts columns named in the SET clause to the values given by the corresponding expressions. Columns not named are set to their default values. INSERT INTO absence (student_id, date) VALUES(14,'1999-11-03'),(34,NOW()); INSERT INTO absence SET student_id = 14, date = '1999-11-03'; INSERT INTO absence SET student_id = 34, date = NOW(); INSERT INTO score (student_id, score, event_id) SELECT student_id, 100 AS score, 15 AS event_id FROM student; KILL KILL thread_id Kills the server thread with the given thread_id. You must have the SUPER privilege (PROCESS prior to MySQL 4.0.2) to kill the thread unless it is one of your own. The KILL statement allows only a single ID. The mysqladmin kill command performs the same operation but allows multiple thread ID values to be specified on the command line. This statement was introduced in MySQL 3.22.9. LOAD DATA LOAD DATA [LOW_PRIORITY | CONCURRENT ] [LOCAL] INFILE 'file_name' [IGNORE | REPLACE] INTO TABLE tbl_name import_options [IGNORE n LINES] [(column_list)] LOAD DATA reads records from the file file_name and loads them in bulk into the table tbl_name. This is faster than using a set of INSERT statements. LOW_PRIORITY causes the statement to be deferred until no clients are reading from the table. LOW_PRIORITY was introduced in MySQL 3.23.0. CONCURRENT is used only for MyISAM tables. It allows other clients to retrieve from the table while rows are being loaded into it. CONCURRENT was introduced in MySQL 3.23.38. Without the LOCAL keyword, the file is read directly by the server on the server host. In this case, you must have the FILE privilege and the file must either be located in the database directory of the default database or world readable. If LOCAL is specified, the client reads the file on the client host and sends its contents over the network to the server. In this case, the FILE privilege is not required. LOCAL became functional in MySQL 3.22.15. However, as of 3.23.49, LOCAL can be disabled or enabled selectively. If it is disabled on the server side, you cannot use it from the client side. If it is enabled on the server side but disabled by default on the client side, you'll need to enable it explicitly. For example, with the mysql program, you can use the --local-infile flag to enable the LOCAL capability. When LOCAL is not specified in the LOAD DATA statement, the server locates the file as follows: If 'file_name' is an absolute pathname, the server looks for the file starting from the root directory. If 'file_name' is a relative pathname, interpretation depends on whether or not the name contains a single component. If so, the server looks for the file in the database directory of the default database. If the filename contains multiple components, the server looks for the file beginning in the server's data directory. If LOCAL is given, the filename is interpreted as follows: If 'file_name' is an absolute pathname, the client looks for the file starting from the root directory. If 'file_name' is a relative pathname, the client looks for the file beginning with your current directory. For Windows, backslashes in filenames can be written either as slashes ('/') or as doubled backslashes ('\\'). Rows that duplicate values in a unique index are either ignored or replace existing rows according to whether IGNORE or REPLACE is specified. If neither is specified, an error occurs, and any remaining records are ignored. If LOCAL is specified, transmission of the file cannot be interrupted, so the default behavior is like that of IGNORE if neither duplicate-handling option is given. The import_options clause indicates the format of the data. The options available in this clause also apply to the export_options clause for the SELECT ... INTO OUTFILE statement. The syntax for import_options is as follows: [FIELDS [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char' ] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] The 'string' and 'char' values can include the following escape sequences to indicate special characters: | Sequence | Meaning | | \0 | ASCII 0 | | \b | Backspace | | \n | Newline (linefeed) | | \r | Carriage return | | \s | Space | | \t | Tab | | \' | Single quote | | \" | Double quote | | \\ | Backslash | As of MySQL 3.22.10, you can also use hexadecimal constants to indicate arbitrary characters. For example, LINES TERMINATED BY 0x02 indicates that lines are terminated by Ctrl-B (ASCII 2) characters. If FIELDS is given, at least one of the TERMINATED BY, ENCLOSED BY, or ESCAPED BY clauses must be given, but if multiple clauses are present, they can appear in any order. Similarly, if LINES is given, at least one of the STARTING BY or TERMINATED BY clauses must be given, but if both are present, they can appear in any order. If both FIELDS and LINES are given, FIELDS must precede LINES. The parts of the FIELDS clause are used as follows: TERMINATED BY specifies the character or characters that delimit values within a line. ENCLOSED BY specifies a quote character that is stripped from the ends of field values if it is present. This occurs whether or not OPTIONALLY is present. For output (SELECT ... INTO OUTFILE), the ENCLOSED BY character is used to enclose field values in output lines. If OPTIONALLY is given, values are quoted only for CHAR and VARCHAR columns. To include an instance of the ENCLOSED BY character within an input field value, it should either be doubled or preceded by the ESCAPED BY character. Otherwise, it will be interpreted as signifying the end of the field. For output, instances of the ENCLOSED BY character within field values are preceded by the ESCAPED BY character. The ESCAPED BY character is used to specify escaping of special characters. In the following examples, assume that the escape character is backslash ('\'). For input, the unquoted sequence \N (backslash-N) is interpreted as NULL. The \0 sequence (backslash-ASCII '0') is interpreted as a zero-valued byte. For other escaped characters, the escape character is stripped off, and the following character is used literally. For example, \" is interpreted as a double quote, even if field values are enclosed within double quotes. For output, the escape character is used to encode NULL as an unquoted \N sequence, and zero-valued bytes as \0. In addition, instances of the ESCAPED BY and ENCLOSED BY characters are preceded by the escape character, as are the first characters of the field and line termination strings. If the ESCAPED BY character is empty (ESCAPED BY ''), no escaping is done. To specify an escape character of '\', double it (ESCAPED BY '\\'). The parts of the LINES clause are used as follows: If neither FIELDS nor LINES is given, the defaults are as if you had specified them like this: FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\n' In other words, fields within a line are tab-delimited without being quoted, backslash is treated as the escape character, and lines are terminated by newline characters. If the TERMINATED BY and ENCLOSED BY values for the FIELDS clause are both empty, a fixed-width row format is used with no delimiters between fields. Column values are read (or written, for output) using the display widths of the columns. For example, VARCHAR(15) and MEDIUMINT(5) columns are read as 15-character and 5-character fields for input. For output, the columns are written using 15 characters and 5 characters. NULL values are written as strings of spaces. NULL values in an input data file are indicated by the unquoted sequence \N. If the FIELDS ENCLOSED BY character is not empty, all non-NULL input values must be quoted with the enclosed-by character and the unquoted word NULL also will be interpreted as a NULL value. If the IGNORE n LINES clause is given, the first n lines of the input are discarded. For example, if your data file has a row of column headers that you don't want to put into the database table, you can use IGNORE 1 LINES: LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl IGNORE 1 LINES; If no column_list is specified, input lines are assumed to contain one value per column in the table. If a list consisting of one or more comma-separated column names is given, input lines should contain a value for each named column. Columns not named in the list are set to their default values. If an input line is short of the expected number of values, columns for which values are missing are set to their default values. If you have a tab-delimited text file that you created on Windows, you can use the default column separator, but the lines are probably terminated by carriage return/newline pairs. To load the file, specify a different line terminator ('\r' indicates a carriage return, and '\n' indicates a newline): LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl LINES TERMINATED BY '\r\n'; Unfortunately, for files created on Windows, you may end up with a malformed record in the database if the program that created the data file uses the odd MS-DOS convention of putting the Ctrl-Z character at the end of the file to indicate end-of-file. Either write the file using a program that doesn't do this, or delete the record after loading the file. Files in comma-separated values (CSV) format have commas between fields, and fields can be quoted with double quotes. Assuming lines have newlines at the end, the LOAD DATA statement to load such a file looks like this: LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl FIELDS TERMINATED BY ',' ENCLOSED BY '"'; The following statement reads a file for which fields are separated by Ctrl-A (ASCII 1) characters, and lines are terminated by Ctrl-B (ASCII 2) characters: LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl FIELDS TERMINATED BY 0x01 LINES TERMINATED BY 0x02; LOAD ... FROM MASTER LOAD DATA FROM MASTER LOAD TABLE tbl_name FROM MASTER These statements are used on replication slave servers to request data from the master server. LOAD DATA FROM MASTER requests all tables from the master. It also updates the slave's replication coordinates so that the slave will replicate only updates on the master that were made after completion of the LOAD DATA operation. The tables to transfer are subject to any restrictions specified by any --replicate-xxx options with which the slave server may have been started. The statement itself also has a number of constraints that must be satisfied, as detailed in the "Establishing a Master-Slave Replication Relationship" section in Chapter 11. If these conditions are acceptable, this statement provides a convenient way to initialize a slave server. LOAD TABLE ... FROM MASTER transfers a copy of just the named table from the master to the slave. This is used primarily for replication debugging. LOAD TABLE ... FROM MASTER was introduced in MySQL 3.23.19, and LOAD DATA FROM MASTER in MySQL 4.0.0. LOCK TABLE LOCK {TABLE | TABLES} tbl_name [AS alias_name] lock_type [, tbl_name [AS alias_name] lock_type] ... Obtains a lock on the named tables, waiting if necessary until all locks are acquired. Each lock_type value must be one of the following: READ Acquire a read lock. This blocks other clients that want to write to the table, but allows other clients to read the table. READ LOCAL This is a variation on a READ lock, designed for concurrent insert situations. It applies only to MyISAM tables that do not have any holes in them resulting from deleted record. READ LOCAL allows you to lock a table explicitly but still allow concurrent inserts for a MyISAM table that has no holes in it. (If the table does have holes in it, the lock is treated as a regular READ lock.) WRITE Acquire a write lock. This blocks all other clients, whether they want to read from or write to the table. LOW_PRIORITY WRITE This type of lock allows other readers to read the table if the request is waiting for another client that is already reading the table. The lock is not acquired until there are no more readers. LOCK TABLE allows an alias to be specified so that you can lock a table under an alias that you are going to use when referring to the table in a subsequent query. (If you use a table multiple times in a query, you must obtain a lock for each instance of the table, locking aliases as necessary.) LOCK TABLE releases any existing locks that you currently hold. Thus, to lock multiple tables, you must lock them all using a single LOCK TABLE statement. Any locks that are held by a client when it terminates are released automatically. LOW_PRIORITY write locks were introduced in MySQL 3.22.8 and READ LOCAL locks in MySQL 3.23.11. LOCK TABLES student READ, score WRITE, event READ; LOCK TABLE member READ; LOCK TABLES t AS t1 READ, t AS t2 READ; OPTIMIZE TABLE OPTIMIZE {TABLE | TABLES} tbl_name [, tbl_name] ... DELETE, REPLACE, and UPDATE statements can result in areas of unused space in a table, particularly for tables that have variable-length rows. To counter this, OPTIMIZE TABLE performs the following actions: Defragments the table to eliminate wasted space and reduce the table size. Coalesces the contents of variable-rows that have become fragmented into non-contiguous pieces, so that each row is stored contiguously. Sorts the index pages if necessary. Updates the internal table statistics. OPTIMIZE TABLE can be used only with MyISAM and BDB tables (and for BDB tables, it actually maps onto ANALYZE TABLE). It requires SELECT and INSERT privileges on each table. Issuing an OPTIMIZE TABLE statement is like executing myisamchk with the --check-only-changed, --quick, --sort-index, and --analyze options. However, with myisamchk, you must arrange to prevent the server from accessing the table at the same time. With OPTIMIZE TABLE, you let the server do the work, and it takes care of making sure that other clients do not modify a table while it's being optimized. OPTIMIZE TABLE produces output in the format described under the entry for CHECK TABLE. OPTIMIZE TABLE was introduced in MySQL 3.22.7. PURGE MASTER LOGS PURGE MASTER LOGS TO 'log_name' Deletes all the binary update logs on the server that were generated earlier than the named log file and resets the binary update log index file to list only those logs that remain. Normally, you use this after running SHOW SLAVE STATUS on each of the master's slaves to determine which log files are still in use. This statement requires the SUPER privilege (PROCESS prior to MySQL 4.0.2). Remove binlog.001 through binlog.009 (or whichever of them exist) and cause binlog.010 to become the first of the remaining log files: PURGE MASTER LOGS TO 'binlog.010'; PURGE MASTER LOGS was introduced in MySQL 3.23.28. RENAME TABLE RENAME {TABLE | TABLES} tbl_name TO new_tbl_name [, ...] Renames one or more tables. RENAME TABLE is similar to ALTER TABLE ... RENAME, except that it can rename multiple tables at once and locks them all during the rename operation. This is advantageous if you need to prevent any of the tables from being accessed during the operation. RENAME TABLE was introduced in MySQL 3.23.23. REPAIR TABLE REPAIR {TABLE | TABLES} tbl_name [, tbl_name] ... [options] This statement performs table repair operations. It works only for MyISAM tables and requires SELECT and INSERT privileges on each table. options, if given, is a list naming one or more of the following options (not separated by commas): EXTENDED Perform an extended repair that recreates the indexes. This is similar to running myisamchk --safe-recover on the tables, except that the repair is performed by the server rather than by an external utility. QUICK Repair only the index; leave the data file alone. USE_FRM Uses the table's .frm file to figure out how to interpret the contents of the data file and then uses the data file to rebuild the index file. This can be useful if the index has become lost or irrecoverably corrupted. REPAIR TABLE with no options performs a table repair option like that done by myisamchk --recover. REPAIR TABLE produces output in the format described under the entry for CHECK TABLE. REPAIR TABLE was introduced in MySQL 3.23.14. The QUICK and USE_FRM options were added in MySQL 3.23.16 and 4.0.2. From 3.23.14 to 3.23.25, only a single option is allowed and TYPE = must precede it; after that, TYPE = is deprecated, and multiple options are allowed. REPLACE REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(column_list)] VALUES (expr [, expr] ...) [, (...)] ... REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(column_list)] SELECT ... REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name SET col_name=expr [, col_name=expr] ... The REPLACE statement is like INSERT, with the exception that if a row to be inserted has a value for a unique index that duplicates the value in a row already present in the table, the old row is deleted before the new one is inserted. For this reason, there is no IGNORE clause option in the syntax of REPLACE. See the INSERT entry for more information. It's possible for a REPLACE to delete more than one row if the table contains multiple unique indexes. This can happen if a new row matches values in several of the unique indexes, in which case, all the matching rows are deleted before the new row is inserted. REPLACE requires the INSERT and DELETE privileges. Prior to MySQL 4.0.5, it also requires the UPDATE privilege. RESET RESET option [, option] ... The RESET statement is similar to FLUSH in that it affects log or cache information. (In fact, RESET began life as part of the FLUSH statement.) Each option value should be one of the following items: MASTER Delete the existing binary update logs for a replication master server, create a new log file with the numbering sequence set to 001, and reset the binary update log index to name just the new file. QUERY CACHE Clear the query cache and remove any queries currently registered in it. (To defragment the cache without clearing it, use FLUSH QUERY CACHE.) SLAVE If the server is acting as a replication slave, this option tells it to forget its replication coordinates (that is, its current replication binary log filename and position within that file). RESET requires the RELOAD privilege. RESET was introduced in MySQL 3.23.26. The QUERY CACHE option was introduced in MySQL 4.0.1. From 3.23.19 to 3.23.25, RESET MASTER and RESET SLAVE are available as FLUSH MASTER and FLUSH SLAVE. RESTORE TABLE RESTORE {TABLE | TABLES} tbl_name [, tbl_name] ... FROM 'dir_name' Restores the named table or tables using files located in the backup directory that were created with BACKUP TABLE. 'dir_name' should be the full pathname to the directory on the server host that contains the backup files. The tables to be restored must not already exist. RESTORE TABLE works only for MyISAM tables and requires the INSERT and FILE privileges. The restore operation for each table uses only the table definition and data files (the .frm and .MYD files). Indexes are rebuilt using the information contained in those two files. RESTORE TABLE was introduced in MySQL 3.23.25. REVOKE REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...] ON {*.* | * | db_name.* | db_name.tbl_name | tbl_name} FROM account [, account ] ... This statement revokes privileges from the named account or accounts. The priv_type, column_list, and account clauses are specified the same way as for the GRANT statement. The same kind of specifiers as for GRANT are allowed in the ON clause as well. REVOKE does not remove the account from the user grant table. This means that the account can still be used to connect to the MySQL server. To remove the account entirely, you must manually delete its entry from the user table. (However, look for the capability of removing the entry automatically in the future.) The REVOKE statement was introduced in MySQL 3.22.11. Revoke all privileges for superduper@localhost: REVOKE ALL ON *.* FROM 'superduper'@'localhost'; Revoke privileges that allow the member_mgr user to modify the member table in the sampdb database: REVOKE INSERT,DELETE,UPDATE ON sampdb.member FROM 'member_mgr'@'boa.snake.net'; Revoke all privileges for a single table in the menagerie database from the anonymous user on the local host: REVOKE ALL ON menagerie.pet FROM ''@'localhost'; Note that ALL revokes all but the GRANT OPTION privilege. If you want to revoke that privilege as well, you must do so explicitly: REVOKE GRANT OPTION ON menagerie.pet FROM ''@'localhost'; ROLLBACK ROLLBACK Rolls back changes made by statements that are part of the current transaction so that those changes are forgotten. This works only for transaction-safe table types. (For non-transactional table types, statements are committed as they are executed and thus cannot be rolled back.) ROLLBACK does nothing if auto-commit mode has not been disabled with BEGIN or by setting AUTOCOMMIT to 0. ROLLBACK was introduced in MySQL 3.23.14. SELECT SELECT [select_options] select_list [ INTO OUTFILE 'file_name' export_options | INTO DUMPFILE 'file_name' | INTO @var_name [, @var_name ] ... ] [FROM tbl_list [WHERE where_expr] [GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC] , ...] [HAVING where_expr] [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] , ...] [LIMIT [skip_count,] show_count] [PROCEDURE procedure_name(arg_list)] [FOR UPDATE | LOCK IN SHARE MODE] ] SELECT normally is used to retrieve rows from one or more tables. However, because everything in the statement is optional except the SELECT keyword and the select_list clause, it's also possible to write statements that simply evaluate expressions: SELECT 'one plus one =', 1+1; The select_options clause, if present, can contain the following options: ALL DISTINCT DISTINCTROW These keywords control whether or not duplicate rows are returned. ALL causes all rows to be returned, which is the default. DISTINCT and DISTINCTROW specify that duplicate rows should be eliminated from the result set. HIGH_PRIORITY Specifying HIGH_PRIORITY gives the statement a higher priority if it normally would have to wait. If other statements, such as INSERT or UPDATE, are waiting to write to tables named in the SELECT because some other client is reading the tables, HIGH_PRIORITY causes a SELECT statement to be given priority over those write statements. This should be done only for SELECT statements that you know will execute quickly and that must be done immediately because it slows down execution of the write statements. HIGH_PRIORITY was introduced in MySQL 3.22.9. SQL_BUFFER_RESULT Tell the server to buffer the query result in a separate temporary table rather than keeping the table or tables named in the SELECT locked while waiting for the entire query result to be sent to the client. This helps the server release the locks sooner, which gives other clients access to the tables more quickly. (However, using this option also requires more disk space and memory.) SQL_BUFFER_RESULT was introduced in MySQL 3.23.13. SQL_CACHE SQL_NO_CACHE If the query cache is operating in demand mode, SQL_CACHE causes the query result to be cached. SQL_NO_CACHE suppresses any caching of the query result. These options were introduced in MySQL 4.0.1. SQL_CALC_FOUND_ROWS Normally, the row count from a query that includes a LIMIT clause is the number of rows actually returned. SQL_CALC_FOUND_ROWS tells the server to determine how large the query result would be without the LIMIT. This row count can be obtained by issuing a SELECT FOUND_ROWS() statement following the initial SELECT. SQL_SMALL_RESULT SQL_BIG_RESULT These keywords provide a hint that the result set will be small or large, which gives the optimizer information that it can use to process the query more effectively. SQL_SMALL_RESULT and SQL_BIG_RESULT were introduced in MySQL 3.22.12 and 3.23.0, respectively. STRAIGHT_JOIN Forces tables to be joined in the order named in the FROM clause. This option can be useful if you believe that the optimizer is not making the best choice. The select_list clause names the output columns to be returned. Multiple columns should be separated by commas. Columns can be references to table columns or expressions. Any column can be assigned a column alias using the AS alias_name syntax. The alias then becomes the column name in the output and can also be referred to in GROUP BY, ORDER BY, and HAVING clauses. However, you cannot refer to the alias in a WHERE clause. The special notation * means "all columns from the tables named in the FROM clause," and tbl_name.* means "all columns from the named table." The result of a SELECT statement can be written into a file file_name using an INTO OUTFILE 'file_name' clause. The syntax of the export_options clause is the same as for the import_options clause of the LOAD DATA statement. See the LOAD DATA entry for more information. INTO DUMPFILE 'file_name' is similar to INTO OUTFILE but writes only a single row and writes the output entirely without interpretation. That is, it writes raw values without delimiters, quotes, or terminators. This can be useful if you want to write BLOB data to a file, such as an image or other binary data. INTO DUMPFILE was introduced in MySQL 3.23.5. For both INTO OUTFILE and INTO DUMPFILE, the filename is interpreted using the same rules that apply when reading non-LOCAL files with LOAD DATA. You must have the FILE privilege, the output file must not already exist, and the file is created by the server on the server host. Its ownership will be set to the account used to run the server. As of MySQL 4.1, the results of a SELECT can be stored into a set of user-defined variables of the form @var_name. The query must select a single row of values, and must name one variable per output column, separated by commas. The FROM clause names one or more tables from which rows should be selected. MySQL supports the following join types for use in SELECT statements: tbl_list: tbl_name tbl_list, tbl_name tbl_list [CROSS] JOIN tbl_name tbl_list INNER JOIN tbl_name ON conditional_expr tbl_list INNER JOIN tbl_name USING (column_list) tbl_list STRAIGHT_JOIN tbl_name tbl_list LEFT [OUTER] JOIN tbl_name ON conditional_expr tbl_list LEFT [OUTER] JOIN tbl_name USING (column_list) tbl_list NATURAL [LEFT [OUTER]] JOIN tbl_name { OJ tbl_list LEFT OUTER JOIN tbl_name ON conditional_expr } tbl_list RIGHT [OUTER] JOIN tbl_name ON conditional_expr tbl_list RIGHT [OUTER] JOIN tbl_name USING (column_list) tbl_list NATURAL [RIGHT [OUTER]] JOIN tbl_name (tbl_list) Each table name can be accompanied by an alias or index hints. That is, the full syntax for referring to a table actually looks like this: tbl_name [[AS] alias_name] [USE INDEX (index_list) | IGNORE INDEX (index_list)] Tables can be assigned aliases in the FROM clause using either tbl_name alias_name or tbl_name AS alias_name syntax. An alias provides an alternate name by which to refer to the table columns elsewhere in the query. The USE INDEX or IGNORE INDEX clauses can be used as of MySQL 3.23.12 to provide index hints to the optimizer. This can be helpful in cases where the optimizer doesn't make the correct choice about which index to use in a join. (USE KEY and IGNORE KEY are synonyms for USE INDEX and IGNORE INDEX.) index_list should name one or more indexes separated by commas. Each index in index_list should be the name of an index from the table, or the keyword PRIMARY to indicate the table's PRIMARY KEY. The join types select rows from the named tables as indicated in the following descriptions. The rows actually returned to the client may be limited by WHERE, HAVING, or LIMIT clauses. For a single table named by itself, SELECT retrieves rows from that table. If multiple tables are named and separated by commas, SELECT returns all possible combinations of rows from the tables. Using JOIN or CROSS JOIN is equivalent to using commas. STRAIGHT_JOIN is similar, but forces the optimizer to join the tables in the order that the tables are named. It can be used if you believe that the optimizer is not making the best choice. INNER JOIN is like the comma operator but requires an ON or USING() clause to constrain matches between tables, similar to a LEFT JOIN. (However, note that prior to MySQL 3.23.17, INNER JOIN is exactly like the comma operator and does not allow ON or USING() clauses.) LEFT JOIN retrieves rows from the joined tables, but forces a row to be generated for every row in the left table, even if there is no matching row in the right table. When there is no match, columns from the right table are returned as NULL values. Matching rows are determined according to the condition specified in the ON conditional_expr clause or the USING (column_list) clause. conditional_expr is an expression of the form that can be used in the WHERE clause. column_list consists of one or more comma-separated column names, each of which must be a column that occurs in both of the joined tables. LEFT OUTER JOIN is equivalent to LEFT JOIN. So is the syntax that begins with OJ, which is included for ODBC compatibility. (The curly braces shown for the OJ syntax are not metacharacters; they are literal characters that must be present in the statement.) NATURAL LEFT JOIN is equivalent to LEFT JOIN USING (column_list), where column_list names all the columns that are common to both tables. The RIGHT JOIN types are like the corresponding LEFT JOIN types but with the table roles reversed. RIGHT JOIN is allowed as of MySQL 3.23.25. The WHERE clause specifies an expression that is applied to rows selected from the tables named in the FROM clause. (Column aliases cannot be referred to in the WHERE clause.) Rows that do not satisfy the criteria given by the expression are rejected. The result set can be further limited by HAVING and LIMIT clauses. The GROUP BY column_list clause groups rows of the result set according to the columns named in the list. This clause is used when you specify summary functions such as COUNT() or MAX() in the select_list clause. Columns can be referred to by column names, aliases, or by position within select_list. Column positions are numbered beginning with 1. As of 3.23.2, you can use expressions in GROUP BY clauses to group by expression results. In MySQL, GROUP BY not only groups rows, it sorts the results. As of MySQL 3.23.47, you can also use ASC and DESC after GROUP BY column specifiers to specify an explicit grouping order (and thus affect the output order). The output order resulting from GROUP BY is overridden by any ORDER BY clause that is present. The HAVING clause specifies a secondary expression that is used to limit rows after they have satisfied the conditions named by the WHERE clause. Rows that do not satisfy the HAVING condition are rejected. HAVING is useful for expressions involving summary functions that cannot be tested in the WHERE clause. However, if a condition is legal in either the WHERE clause or the HAVING clause, it is preferable to place it in the WHERE clause where it will be subject to analysis by the optimizer. ORDER BY indicates how to sort the result set. Like GROUP BY, columns can be referred to by column names, aliases, or by position at which columns appear in the output column list. Output columns are sorted in ascending order by default. To specify a sort order for a column explicitly, follow the column indicator by ASC (ascending) or DESC (descending). As of 3.23.2, you can use expressions in ORDER BY clauses. For example, ORDER BY RAND() returns rows in random order. However, unlike GROUP BY, expressions in an ORDER BY clause cannot refer to summary (aggregate) functions. The LIMIT clause can be used to select a section of rows from the result set. It takes either one or two arguments, which must be integer constants. LIMIT n returns the first n rows. LIMIT m, n skips the first m rows and then returns the next n rows. The two-argument form also allows LIMIT m, -1 to skip the first m rows and then retrieve all the remaining rows, however many that may be. PROCEDURE names a procedure to which the data in the result set will be sent before a result set is returned to the client. The argument list, arg_list, can be empty or a comma-separated list of arguments to pass to the procedure. As of MySQL 3.23, you can use PROCEDURE ANALYSE() to obtain information about the characteristics of the data in the columns named in the column selection list. The FOR UPDATE and LOCK IN SHARE MODE clauses place locks on the selected rows until the current transaction is committed or rolled back. This can be useful in multiple-statement transactions. If you use FOR UPDATE with a table for which the handler uses page-level or row-level locks (BDB or InnoDB), the selected rows are write-locked for exclusive use. Using LOCK IN SHARE MODE sets read locks on the rows, allowing other clients to read but not modify them. These locking clauses were introduced in MySQL 3.23.35. The following statements demonstrate some ways in which the SELECT statement can be used. See Chapter 1, "Getting Started with MySQL and SQL," and Chapter 3 for many other examples. Select the entire contents of a table: SELECT * FROM president; Select entire contents, but sort by name: SELECT * FROM president ORDER BY last_name, first_name; Select records for presidents born on or after '1900-01-01': SELECT * FROM president WHERE birth >= '1900-01-01'; Do the same, but sort in birth order: SELECT * FROM president WHERE birth >= '1900-01-01' ORDER BY birth; Determine which states are represented by rows in the member table: SELECT DISTINCT state FROM member; Select rows from member table and write columns as comma-separated values into a file: SELECT * INTO OUTFILE '/tmp/member.txt' FIELDS TERMINATED BY ',' FROM member; Select the top five scores for a particular grade event: SELECT * FROM score WHERE event_id = 9 ORDER BY score DESC LIMIT 5; Subselect support. MySQL 4.1 adds subselect support, allowing one SELECT to be nested within another. Several forms of subselect are available: A subselect can be introduced by a comparison operator, in which case it must produce a single value. Select the president record having the earliest birth value: SELECT * FROM president WHERE birth = (SELECT MIN(birth) FROM president); Select scores from a given grade event that are higher than the event's average score: SELECT * FROM score WHERE event_id = 5 AND score > (SELECT AVG(score) FROM score WHERE event_id = 5); A subselect can be introduced by EXISTS or NOT EXISTS. In this case, the inner SELECT may refer to columns from the outer SELECT and thus column references may need to be qualified with table names to avoid ambiguity. Select students who have at least one absence: SELECT student_id, name FROM student WHERE EXISTS (SELECT * FROM absence WHERE absence.student_id = student.student_id); Select students who have no absences: SELECT student_id, name FROM student WHERE NOT EXISTS (SELECT * FROM absence WHERE absence.student_id = student.student_id); SELECT * is used in the subselect because the inner query is evaluated to produce a true or false value, not particular column values. A subselect can be introduced by IN or NOT IN, in which case it should return a single column of values. The preceding EXISTS and NOT EXISTS queries can be rewritten to use IN and NOT IN as follows: SELECT student_id, name FROM student WHERE student_id IN (SELECT student_id FROM absence); SELECT student_id, name FROM student WHERE student_id NOT IN (SELECT student_id FROM absence); SET SET [OPTION] option_setting [, option_setting ] ... The SET statement is used to assign values to a variety of options, user-defined variables, global or session variables, or the transaction isolation level. SET TRANSACTION ISOLATION LEVEL is described in a separate entry, and information about user-defined variables is provided in the "SQL Variables" section later in this appendix. The word OPTION in the SET statement is allowed but deprecated; it may be removed in a future version of MySQL. The syntax for each value assignment is name op val, where name is the SQL option or variable to be assigned a value, op is the assignment operator, and val is the value to assign. Prior to MySQL 3.23.6, only = can be used as the assignment operator; from 3.23.6 on, either = or := can be used. SET can be used to assign values to any of several SQL options: SET SQL_LOG_BIN = 1; SET AUTOCOMMIT = 0; or to set user-defined variables: SET @day = CURDATE(), @time = CURTIME(); In addition, as of MySQL 4.0.3, the server supports several dynamic system variables that can be changed while the server is running, and SET is used to modify these values, too. (Prior to 4.0.3, system variables can be set only at server startup time, necessitating a restart to change values.) Dynamic variables exist at two levels. Global variables are server-wide and affect all clients. Session variables (also called local variables) are specific to a given client connection only. For variables that exist at both levels, the session variables are initialized for each new client connection from the values of the corresponding global variables. Any client can modify its own session variables. To modify a global variable, it is necessary to have the SUPER privilege. To set a global variable (for example, table_type), use a statement having either of the following forms: SET GLOBAL table_type = InnoDB; SET @@GLOBAL.table_type = InnoDB; To set a session variable, substitute the word SESSION for GLOBAL: SET SESSION table_type = InnoDB; SET @@SESSION.table_type = InnoDB; You can also use LOCAL as a synonym for SESSION: SET LOCAL table_type = InnoDB; SET @@LOCAL.table_type = InnoDB; If none of GLOBAL, SESSION, or LOCAL are present, the SET statement modifies the session-level variable: SET table_type = InnoDB; SET @@table_type = InnoDB; To check the value of system variables, use SHOW VARIABLES. The entry in this appendix for that statement lists the variables that are available and indicates which of them can be modified dynamically. The following list describes SQL options that can be controlled with SET. Several of the option descriptions here indicate that you need the SUPER privilege to set the option; prior to MySQL 4.0.2, you need the PROCESS privilege instead. AUTOCOMMIT = {0 | 1} Sets the auto-commit level for transaction processing. Setting the value to 0 disables auto-commit mode so that subsequent statements do not take effect until a commit is performed (either with a COMMIT statement or by setting AUTOCOMMIT to 1). Statements in the transaction can be canceled with ROLLBACK if a commit has not occurred. Setting AUTOCOMMIT to 1 re-enables auto-commit mode (and commits the pending transaction, if any). With auto-commit mode enabled, statements take effect immediately; essentially, each statement is its own transaction. CHARACTER SET [=] {charset | DEFAULT} Specifies the character set used by the client. Strings sent to and from the client are mapped using this character set. The only character set name currently allowable for this option is cp1251_koi8. The character set name DEFAULT restores the default character set. The = is optional as of MySQL 4.0.3; prior to that, it must be omitted. FOREIGN_KEY_CHECKS = {0 | 1} Setting this option to 0 or 1 disables or enables foreign key checking. The default is to perform checking. Disabling key checks can be useful, for example, when restoring a dump file that creates and loads tables in a different order than that required by their foreign key relationships. You can re-enable key checking after loading the tables. This option was introduced in MySQL 3.23.52. It has no effect except for InnoDB tables. INSERT_ID = n Specifies the value to be used by the next INSERT statement when inserting an AUTO_INCREMENT column. This is used for update log processing. LAST_INSERT_ID = n Specifies the value to be returned by LAST_INSERT_ID(). This is used for update log processing. PASSWORD [FOR account] = PASSWORD('pass_val') With no FOR clause, sets the password for the current account to 'pass_val'. With a FOR clause, sets the password for the given account. You must have privileges for modifying the mysql database to be able to set another account's password. account is specified in 'user_name'@'host_name' format using the same types of values for user_name and host_name that are acceptable for the GRANT statement. SET PASSWORD = PASSWORD('secret'); SET PASSWORD FOR 'paul' = PASSWORD('secret'); SET PASSWORD FOR 'paul'@'localhost' = PASSWORD('secret'); SET PASSWORD FOR 'bill'@'%.bigcorp.com' = PASSWORD('old-sneep'); SQL_AUTO_IS_NULL = {0 | 1} If this is set to 1, the most recently generated AUTO_INCREMENT value can be selected using a WHERE clause of the form WHERE col_name IS NULL, where col_name is the name of the AUTO_INCREMENT column. This feature is used by some ODBC programs, such as Access. The default is 1. This option was introduced in MySQL 3.23.5. SQL_BIG_SELECTS = {0 | 1} This option is used in conjunction with MAX_JOIN_SIZE as follows: If SQL_BIG_SELECTS is set to 1 (the default), queries that return result sets of any size are allowed. If SQL_BIG_SELECTS is set to 0, queries that are likely to return a large number of rows are disallowed. In this case, the value of MAX_JOIN_SIZE is used when executing a join. The server makes an estimate of the number of row combinations it will need to examine. If the value exceeds the MAX_JOIN_SIZE value, the server returns an error rather than executing the query. Setting MAX_JOIN_SIZE to a value other than DEFAULT automatically sets SQL_BIG_SELECTS to 0. SQL_BIG_TABLES = {0 | 1} BIG_TABLES = {0 | 1} All internal temporary tables are stored on disk rather than in memory if this is option is set to 1. Performance is slower, but SELECT statements that require large temporary tables will not generate "table full" errors. The default is 0 (hold temporary tables in memory). This option normally is not needed for MySQL 3.23 and up. BIG_TABLES is the preferred name for this option as of MySQL 4. SQL_BUFFER_RESULT = n Setting this option to 1 causes the server to use temporary tables to hold results from SELECT queries. The effect is that the server can more quickly release locks held on the tables from which the results are produced. This option was introduced in MySQL 3.23.13. SQL_LOG_BIN = {0 | 1} Setting this option to 1 enables binary update logging for the current client. Setting the option to 0 turns the log off. The client must have the SUPER privilege for this statement to have any effect. This option was introduced in MySQL 3.23.16. SQL_LOG_OFF = {0 | 1} If this option is set to 1, the current client's queries are not logged in the general log file. If set to 0, logging for the client is enabled. The client must have the SUPER privilege for this statement to have any effect. SQL_LOG_UPDATE = {0 | 1} If this option is set to 1, the current client's queries are logged in the update log file. If set to 0, logging for the client is enabled. The client must have the SUPER privilege for this statement to have any effect. SQL_LOG_UPDATE was introduced in MySQL 3.22.5. SQL_LOW_PRIORITY_UPDATES = {0 | 1} LOW_PRIORITY_UPDATES = {0 | 1} If this option is set to 1, statements that modify table contents (DELETE, INSERT, REPLACE, UPDATE) wait until no SELECT is active or pending for the table. SELECT statements that arrive while another is active begin executing immediately rather than waiting for low-priority modification statements. This option was introduced in MySQL 3.22.5. LOW_PRIORITY_UPDATES is the preferred name as of MySQL 4. SQL_MAX_JOIN_SIZE = {n | DEFAULT} MAX_JOIN_SIZE = {n | DEFAULT} This option is used in combination with SQL_BIG_SELECTS, as discussed in the description for that option. MAX_JOIN_SIZE is the preferred name for this option as of MySQL 4. SQL_QUERY_CACHE_TYPE = {0 | 1 | 2 | OFF | ON | DEMAND} QUERY_CACHE_TYPE = {0 | 1 | 2 | OFF | ON | DEMAND} Sets the query cache mode for the current client. | Mode | Meaning | | 0, OFF | Don't cache | | 1, ON | Cache queries except those that begin with SELECT SQL_NO_CACHE | | 2, DEMAND | Cache on demand only those queries that begin with SELECT SQL_CACHE | This option was introduced in MySQL 4.0.1. QUERY_CACHE_TYPE is the preferred name. SQL_QUOTE_SHOW_CREATE = {0 | 1} This option controls whether to use backticks to quote table, column, and index names in the output from SHOW CREATE TABLE statements. The default is 1 (use quoting). Turning quoting off by setting the option to 0 can be useful when producing CREATE TABLE statements for use with other database servers or with MySQL servers older than version 3.23.6 that do not understand backtick quoting. However, if you turn quoting off, you should make sure that your tables do not use names that are reserved words or that contain special characters. This option was introduced in MySQL 3.23.26. SQL_SAFE_UPDATES = {0 | 1} If this option is set to 1, UPDATE and DELETE statements are allowed only if the records to be modified are identified by key values or if a LIMIT clause is used. This option was introduced in MySQL 3.22.32. SQL_SELECT_LIMIT = {n | DEFAULT} Specifies the maximum number of records to return from a SELECT statement. The presence of an explicit LIMIT clause in a statement takes precedence over this option. The default value is "no limit." A value of DEFAULT restores the default if you have changed it. SQL_SLAVE_SKIP_COUNTER = n Tells a slave server to skip the next n events from the master server. The slave thread must not be running. This option was introduced in MySQL 3.23.33. As of MySQL 4.0.3, it is necessary to use SET GLOBAL rather than just SET to modify this option. SQL_WARNINGS = {0 | 1} If set to 1, MySQL reports warning counts even for single-row inserts. The default is 0, so warning counts normally are reported only for INSERT statements that insert multiple rows. This option was introduced in MySQL 3.22.11. TIMESTAMP = {timestamp_value | DEFAULT} Specifies a TIMESTAMP value. This is used for update log processing. UNIQUE_CHECKS = {0 | 1} Setting this option to 0 or 1 disables or enables uniqueness checks for secondary unique indexes in InnoDB tables. This option was introduced in MySQL 3.23.52. SET TRANSACTION ISOLATION LEVEL SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL level This statement sets the global (server-wide) or session (client-specific) transaction isolation level or the level for just the next transaction within the current session. If neither GLOBAL nor SESSION is specified, the statement sets the level for the next transaction. The SUPER privilege (PROCESS prior to 4.0.2) is required to set the global isolation level. A global change affects clients that connect after the change is made, not those clients that are already connected. The transaction level indicated by level should be one of the following values: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, or SERIALIZABLE. This statement was introduced in MySQL 3.23.36. It has an effect for InnoDB tables only as of MySQL 3.23.50; prior to that, InnoDB always operates in REPEATABLE READ mode. Until MySQL 4.0.5, the other allowable value for InnoDB is SERIALIZABLE, and setting the level to any other value causes REPEATABLE READ to be used. As of MySQL 4.0.5, InnoDB also supports the READ COMMITTED and READ UNCOMMITTED isolation levels. The BDB handler is unaffected by this statement, because BDB always runs at the SERIALIZABLE level. SHOW SHOW BINLOG EVENTS [IN 'file_name'] [FROM n] [LIMIT [skip_count,] show_count] SHOW CHARACTER SET SHOW COLUMN TYPES SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern'] SHOW CREATE DATABASE db_name SHOW CREATE TABLE tbl_name SHOW DATABASES [LIKE 'pattern'] SHOW GRANTS FOR account SHOW INDEX FROM tbl_name [FROM db_name] SHOW INNODB STATUS SHOW LOGS SHOW MASTER LOGS SHOW MASTER STATUS SHOW PRIVILEGES SHOW [FULL] PROCESSLIST SHOW SLAVE HOSTS SHOW SLAVE STATUS SHOW STATUS [LIKE 'pattern'] SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern'] SHOW TABLE TYPES SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern'] SHOW [GLOBAL | SESSION ] VARIABLES [LIKE 'pattern'] The various forms of the SHOW statement provide information about databases, tables, columns, and indexes, or information about server operation. Several of the forms take an optional FROM db_name clause, allowing you to specify the database for which information should be shown. If the clause is not present, the default database is used. (As of MySQL 4.0, IN is a synonym for FROM in each of these statements.) Some forms allow an optional LIKE 'pattern' clause to limit output to values that match the pattern. 'pattern' is interpreted as a SQL pattern and may contain the '%' or '_' wildcard characters. SHOW BINLOG EVENTS This statement is used on replication master servers to display events in a binary update log file. Its output includes the following columns: Log_name The binary log file name Pos The position of the event within the log file Event_type The type of event, such as Query for a statement that is to be executed Server_id The ID of the server that logged the event Orig_log_pos The position of the event in the original log file on the master server Info Event information, such as the statement text for a Query event SHOW BINLOG EVENTS was introduced in MySQL 4.0. SHOW CHARACTER SET Displays a list of the character sets supported by the server. The output from SHOW CHARACTER SET currently includes the following types of information about each character set, although it's likely that this format will undergo some change in the future to include additional information. Name The character set name. Id The internal character set ID number. strx_maxlen A cost factor relating to the amount of memory that must be allocated for internal string conversion operations when sorting values in the character set. mb_maxlen Indicates the length of the "widest" character in the character set, in bytes. For multi-byte character sets, this value will be greater than one. For non-multi-byte sets, all characters take a single byte, so the value is one. SHOW CHARACTER SET was introduced in MySQL 4.1. SHOW COLUMN TYPES This statement lists information about the column types that can be used when creating MySQL tables. The output from SHOW COLUMN TYPES includes the following columns: Type The column type. Size The type's storage size in bytes. Min_Value The minimum value of the type's range. Max_Value The maximum value of the type's range. Prec The type's precision. Scale The type's scale factor. Nullable Whether or not the type allows NULL values. Auto_Increment Whether or not the type can be used for AUTO_INCREMENT sequences. Unsigned Whether or not the type has the UNSIGNED attribute. Zerofill Whether or not the type has the ZEROFILL attribute. Searchable Whether or not the type is searchable. Case_Sensitive Whether or not the type is case sensitive. Default The type's default value. There may be more than one value listed, because the default value may depend on whether the column is declared to allow NULL values. Comment A descriptive comment about the column type. SHOW COLUMN TYPES was introduced in MySQL 4.1. SHOW [FULL] COLUMNS The SHOW COLUMNS statement lists the columns for the given table. SHOW FIELDS is a synonym for SHOW COLUMNS. SHOW COLUMNS FROM president; SHOW FIELDS FROM president; SHOW COLUMNS FROM president FROM sampdb; SHOW FULL COLUMNS FROM tables_priv FROM mysql LIKE '%priv'; The output from SHOW COLUMNS provides the following types of information about each column in the table: Field The column name. Type The column type. This can include type attributes following the type name. Null YES if the column can contain NULL values, blank otherwise. Key Whether or not the column is indexed. Default The column's default value. Extra Extra information about the column. Privileges The privileges that you hold for the column. This information is available only as of MySQL 3.23.0. It is always displayed from 3.23.0 to 3.23.32 and from 3.23.32 on is displayed only if you specify the FULL keyword. Comment The value of any COMMENT clause that was specified in the column declaration. This information is displayed only as of MySQL 4.1, and only if you specify the FULL keyword. SHOW CREATE DATABASE This statement displays the CREATE DATABASE statement necessary to create the named database. SHOW CREATE DATABASE sampdb; This statement was introduced in MySQL 4.1. SHOW CREATE TABLE This statement displays the CREATE TABLE statement that corresponds to the structure of the named table. SHOW CREATE TABLE absence; Table, column, and index names in the statement produced by SHOW CREATE TABLE are quoted by default. Quoting can be controlled by setting the SQL_QUOTE_SHOW_CREATE option. (This option is described in the entry for the SET statement.) SHOW CREATE TABLE was introduced in MySQL 3.23.20. SHOW DATABASES The SHOW DATABASES statement lists the databases available on the server host. SHOW DATABASES; SHOW DATABASES LIKE 'test%'; If you don't have the SHOW DATABASES privilege, you'll see only the databases for which you have some kind of access privilege. SHOW GRANTS The SHOW GRANTS statement displays grant information about the specified user, which should be given in 'user_name'@'host_name' form, using the same types of values for user_name and host_name that are acceptable for the GRANT statement. SHOW GRANTS FOR 'root'@'localhost'; SHOW GRANTS FOR ''@'cobra.snake.net'; The output is in the form of the GRANT statements that would need to be issued to recreate the account privileges. SHOW GRANTS was introduced in MySQL 3.23.4. SHOW INDEX The SHOW INDEX statement displays information about a table's indexes. SHOW KEYS is a synonym for SHOW INDEX. SHOW INDEX FROM score; SHOW KEYS FROM score; SHOW INDEX FROM sampdb.score; SHOW INDEX FROM score FROM sampdb; The output from SHOW INDEX contains the following columns: Table The name of the table containing the index. Non_unique This value is 1 if the index can contain duplicate values and 0 if it cannot. Key_name The index name. Seq_in_index The number of the column within the index. Index columns are numbered beginning with 1. Column_name The column name. Collation The column sorting order within the index. The values may be A (ascending), D (descending), or NULL (not sorted). Descending keys are not yet available, but will be implemented in the future. Cardinality The number of unique values in the index. myisamchk or isamchk update this value for MyISAM or ISAM tables when run with the --analyze option. The ANALYZE TABLE statement updates this value for MyISAM or BDB tables. Sub_part The prefix length in bytes, if only a prefix of the column is indexed. This is NULL if the entire column is indexed. Packed How the key is packed, or NULL if it is not packed. Null YES if the column can contain NULL values, blank otherwise. Index_type The method used to index the column, such as BTREE or FULLTEXT. Comment Reserved for internal comments about the index. The Packed and Comment columns were added in MySQL 3.23.0. The Null and Index_type columns were added in 4.0.2. SHOW INNODB STATUS This statement displays information about the internal operation of the InnoDB table handler. It was introduced in MySQL 3.23.52. SHOW LOGS This statement displays information about the server's log files. Currently, it is used only for the BDB logs. The output from SHOW LOGS includes the following columns: SHOW LOGS was introduced in MySQL 3.23.29. SHOW MASTER LOGS This statement is used on replication master servers. It displays the names of the binary logs currently available on the master. It can be useful before issuing a PURGE MASTER LOGS statement after running SHOW SLAVE STATUS on each of the slaves to determine the binary logs to which they currently are positioned. This statement was introduced in MySQL 3.23.28. SHOW MASTER STATUS This statement is used on replication master servers. It provides information about the status of the master's binary update logs. The output from SHOW MASTER STATUS includes the following columns: File The name of the binary update log file Position The current position at which the server is writing to the file Binlog_do_db A comma-separated list of databases that are explicitly replicated to the binary log with --binlog-do-db options, blank if no such options were given Binlog_ignore_db A comma-separated list of databases that are explicitly excluded from the binary log with --binlog-ignore-db options, blank if no such options were given SHOW MASTER STATUS was introduced in MySQL 3.23.22. SHOW PRIVILEGES SHOW PRIVILEGES displays the privileges that can be granted and information about the purpose of each one. The output from SHOW PRIVILEGES includes the following columns: Privilege The privilege name Context The applicability of the privilege, such as Server Admin (server administration), Databases, or Tables Comment A description of the purpose of the privilege SHOW PRIVILEGES was introduced in MySQL 4.1. SHOW [FULL] PROCESSLIST SHOW PROCESSLIST displays information about the threads executing within the server. The output contains the following columns: Id The thread ID number for the client. User The client name associated with the thread. Host The host from which the client is connected. db The default database for the thread. Command The statement being executed by the thread. Time The amount of time used by the statement currently executing in the thread, in seconds. State Information about what MySQL is doing while processing a SQL statement. The value can be useful for reporting a problem with MySQL or when asking a question on the MySQL mailing list about why a thread stays in some state for a long time. Info The query being executed. As of MySQL 3.23.7, the FULL option can be added to see the full text of queries in the Info field. Without it, only the first 100 characters are displayed. SHOW SLAVE HOSTS This statement is used on replication master servers. It displays information about the slave servers that are currently registered with the master. The output from SHOW SLAVE HOSTS includes the following columns: Server_id The slave server ID Host The slave host User The account name the slave used to connect Password The account password the slave used to connect Port The port to which the slave is connected Rpl_recovery_rank The replication recovery rank Master_id The master server ID The User and Password columns are shown only if the master server was started with the --show-slave-auth-info option. SHOW SLAVE HOSTS was introduced in MySQL 4.0. SHOW SLAVE STATUS This statement is used on slave servers and provides information about the replication status of the server. The output from SHOW SLAVE STATUS includes the following columns: Master_Host The master host name or IP address Master_User The user name for connecting to the master Master_Port The port number for connecting to the master Connect_retry The number of times to attempt connections to the master before giving up Master_Log_File The name of the current master binary update log file Read_Master_Log_Pos The current position within the master binary update log file where the slave I/O thread is reading Relay_Log_File The name of the current relay log file Relay_Log_Pos The current position within the relay log file Relay_Master_Log_File The name of the current master relay log file Slave_IO_Running Whether the slave I/O thread is running Slave_SQL_Running Whether the slave SQL thread is running Replicate_do_db A comma-separated list of databases that are explicitly replicated with --replicate-do-db options, blank if no such options were given Replicate_ignore_db A comma-separated list of databases that are explicitly excluded from replication with --replicate-ignore-db options, blank if no such options were given Last_errno The most recent error number or 0 if none Last_error The most recent error message or blank if none Skip_counter The number of events from the master that the slave should skip Exec_master_log_pos The current position within the master binary update log file where the slave SQL thread is executing Relay_log_space The combined size of the relay log files SHOW SLAVE STATUS was introduced in MySQL 3.23.22. SHOW STATUS The SHOW STATUS statement displays the server's status variables and their values. As of MySQL 3.23.0, a LIKE 'pattern' clause can be added to display only variables having names that match the pattern. The more general variables are listed next. Variables for statement counters, the query cache, and SSL are listed in separate groups after that. Aborted_clients The number of client connections aborted due to clients not closing the connection properly. Aborted_connects The number of failed attempts to connect to the server. Bytes_received The total number of bytes received from all clients. This variable was introduced in MySQL 3.23.7. Bytes_sent The total number of bytes sent to all clients. This variable was introduced in MySQL 3.23.7. Connections The number of attempts to connect to the server (both successful and unsuccessful). If this number is quite high, you may want to look into using persistent connections in your clients if possible. Created_tmp_disk_tables The number of on-disk temporary tables created while processing queries. This variable was introduced in MySQL 3.23.24. Created_tmp_files The number of temporary files created by the server. This variable was introduced in MySQL 3.23.28. Created_tmp_tables The number of in-memory temporary tables created while processing queries. Delayed_errors The number of errors occurring while processing INSERT DELAYED rows. Delayed_insert_threads The current number of INSERT DELAYED handlers. Delayed_writes The number of INSERT DELAYED rows that have been written. Flush_commands The number of FLUSH statements that have been executed. Handler_commit The number of requests to commit a transaction. This variable was introduced in MySQL 4.0.2. Handler_delete The number of requests to delete a row from a table. Handler_read_first The number of requests to read the first row from an index. Handler_read_key The number of requests to read a row based on an index value. Handler_read_next The number of requests to read the next row in index order. Handler_read_prev The number of requests to read the previous row in descending index order. This variable was introduced in MySQL 3.23.6. Handler_read_rnd The number of requests to read a row based on its position. Handler_read_rnd_next The number of requests to read the next row. If this number is high, you are likely performing many queries that require full table scans or that are not using indexes properly. This variable was introduced in MySQL 3.23.6. Handler_rollback The number of requests to roll back a transaction. This variable was introduced in MySQL 4.0.2. Handler_update The number of requests to update a row in a table. Handler_write The number of requests to insert a row in a table. Key_blocks_used The number of blocks in use in the index cache. Key_read_requests The number of requests to read a block from the index cache. Key_reads The number of physical reads of index blocks from disk. Key_write_requests The number of requests to write a block to the index cache. Key_writes The number of physical writes of index blocks to disk. Max_used_connections The maximum number of connections that have been open simultaneously. Not_flushed_delayed_rows The number of rows waiting to be written for INSERT DELAYED queries. Not_flushed_key_blocks The number of blocks in the key cache that have been modified but not yet flushed to disk. Opened_tables The total number of tables that have been opened. If this number is high, it may be a good idea to increase your table cache size. Open_files The number of open files. Open_streams The number of open streams. A stream is a file opened with fopen(); this applies only to log files. Open_tables The number of open tables. Questions The number of queries that have been received by the server (this includes both successful and unsuccessful queries). The ratio of Questions to Update yields the number of queries per second. Rpl_status Failsafe replication status. This variable was introduced in MySQL 4.0.0, but is not yet used. Select_full_join The number of joins performed without using indexes. This variable was introduced in MySQL 3.23.25. Select_full_range_join The number of joins performed using a range search on a reference table. This variable was introduced in MySQL 3.23.25. Select_range The number of joins performed using a range on the first table. This variable was introduced in MySQL 3.23.25. Select_range_check The number of joins performed such that a range search must be used to fetch rows on a secondary table. This variable was introduced in MySQL 3.23.25. Select_scan The number of joins performed that used a full scan of the first table. This variable was introduced in MySQL 3.23.25. Slave_open_temp_tables The number of temporary tables the slave thread has open. This variable was introduced in MySQL 3.23.29. Slave_running Whether this server is acting as a slave that is currently connected to a server. This variable was introduced in MySQL 3.23.16. Slow_launch_threads The number of threads that took longer than slow_launch_time seconds to create. This variable was introduced in MySQL 3.23.15. Slow_queries The number of queries that look longer than long_query_time seconds to execute. Sort_merge_passes The number of merge passes performed by the sort algorithm. This variable was introduced in MySQL 3.23.28. Sort_range The number of sort operations performed using a range. This variable was introduced in MySQL 3.23.25. Sort_rows The number of rows sorted. This variable was introduced in MySQL 3.23.25. Sort_scan The number of sort operations performed using a full table scan. This variable was introduced in MySQL 3.23.25. Table_locks_immediate The number of requests for a table lock that could be satisfied immediately with no waiting. This variable was introduced in MySQL 3.23.33. Table_locks_waited The number of requests for a table lock that could be satisfied only after waiting. If this value is high, it indicates performance problems. This variable was introduced in MySQL 3.23.33. Threads_cached The number of threads currently in the thread cache. This variable was introduced in MySQL 3.23.17. Threads_connected The number of currently open connections. Threads_created The total number of threads that have been created to handle client connections. This variable was introduced in MySQL 3.23.31. Threads_running The number of threads that are not sleeping. Uptime The number of seconds since the server started running. Statement Counter Status Variables As of MySQL 3.23.47, the server maintains a set of status variables that serve as counters to indicate the number of times particular types of statements (commands) have been executed. There are dozens of such variables, and they all have similar names, so they are not listed individually here. Each statement counter variable name begins with Com_, and has a suffix that indicates the type of statement to which the counter corresponds. For example, Com_select and Com_drop_table indicate how many SELECT and DROP TABLE statements the server has executed. Query Cache Status Variables The following variables display information about the operation of the query cache. They were introduced in MySQL 4.0.1, when the query cache itself was added. Qcache_free_blocks The number of free memory blocks in the query cache Qcache_free_memory The amount of free memory for the query cache Qcache_hits The number of hits in the query cache, that is, the number of query requests satisfied by queries held in the cache Qcache_inserts The number of queries that have ever been registered in the query cache Qcache_not_cached The number of queries that were uncacheable or for which caching was suppressed with the SQL_NO_CACHE keyword Qcache_queries_in_cache The number of queries currently registered in the cache Qcache_total_blocks The total number of memory blocks in the query cache Note that SHOW VARIABLES also lists a few query cache-related variables; they all have names that begin with query_cache. SSL Status Variables The following variables provide information about the SSL management code. Many of them reflect the state of the current connection and will be blank unless the connection actually is secure. These variables were introduced in MySQL 4.0, when SSL support was added. However, they are unavailable unless SSL support actually has been built into the server. Ssl_accept_renegotiates The number of start renegotiations in server mode. Ssl_accepts The number of started SSL/TLS handshakes in server mode. Ssl_callback_cache_hits The number of sessions successfully retrieved from the external session cache in server mode. Ssl_cipher The SSL cipher (protocol) for the current connection (blank if no cipher is in effect). You can use this variable to determine whether or not the current connection is encrypted. Ssl_cipher_list The list of available SSL ciphers Ssl_client_connects The number of started SSL/TLS handshakes in client mode Ssl_connect_renegotiates The number of start renegotiations in client mode Ssl_ctx_verify_depth The SSL context verification depth Ssl_ctx_verify_mode The SSL context verification mode Ssl_default_timeout The default SSL session timeout Ssl_finished_accepts The number of successfully established SSL/TLS sessions in server mode Ssl_finished_connects The number of successfully established SSL/TLS sessions in client mode Ssl_session_cache_hits The number of SSL sessions found in the session cache Ssl_session_cache_misses The number of SSL sessions not found in the session cache Ssl_session_cache_mode The type of SSL caching used by the server Ssl_session_cache_overflows The number of sessions removed from the cache because it was full Ssl_session_cache_size The number of sessions that can be stored in the SSL session cache. Ssl_session_cache_timeouts The number of sessions that have timed out Ssl_sessions_reused Whether or not the session was reused from an earlier session Ssl_used_session_cache_entries The number of sessions currently in the session cache Ssl_verify_depth The SSL verification depth Ssl_verify_mode The SSL verification mode Ssl_version The protocol version of the connection SHOW TABLE STATUS The SHOW TABLE STATUS statement displays descriptive information about the tables in a database. SHOW TABLE STATUS; SHOW TABLE STATUS FROM sampdb; SHOW TABLE STATUS FROM mysql LIKE '%priv'; The output from SHOW TABLE STATUS includes the following columns: Name The table name. Type The table type; this is a value like NISAM (ISAM), MyISAM, HEAP, or InnoDB. Row_format The row storage format; this can be Fixed (fixed-length rows), Dynamic (variable-length rows), or Compressed. Rows The number of rows in the table. For some table types, such as BDB and InnoDB, this is only an approximate count. Avg_row_length The average number of bytes used by table rows. Data_length The actual size in bytes of the table data file. Max_data_length The maximum size in bytes that the table data file can grow to. Index_length The actual size in bytes of the index file. Data_free The number of unused bytes in the data file. If this number is very high, it may be a good idea to issue an OPTIMIZE TABLE statement for the table. Auto_increment The next value that will be generated for an AUTO_INCREMENT column. Create_time The time when the table was created. Update_time The time when the table was most recently modified. Check_time For MyISAM tables, the time at which the table was last checked or repaired by myisamchk; the value is NULL if the table has never been checked or repaired. Charset The table's character set. Create_options Extra options that were specified in the table_options clause of the CREATE TABLE statement that created the table. Comment The text of any comment specified when the table was created. For an InnoDB table, this column shows foreign key definitions; it also displays the amount of free space in the InnoDB tablespace. SHOW TABLE STATUS was introduced in MySQL 3.23.0. The Charset column was added in MySQL 4.1. SHOW TABLE TYPES SHOW TABLE TYPES lists the table handlers that the server supports. The output from this statement includes the following columns: Type The table type (MyISAM, InnoDB, and so on) Support The level of support: YES for supported, NO for not supported, DISABLED for supported but disabled at runtime, or DEFAULT to indicate that the table type is the default type Comment Descriptive text about the table type SHOW TABLE TYPES was introduced in MySQL 4.1. SHOW [OPEN] TABLES The SHOW TABLES statement displays the names of the non-TEMPORARY tables in a database. SHOW TABLES; SHOW TABLES FROM sampdb; SHOW TABLES FROM mysql LIKE '%priv'; As of MySQL 3.23.33, SHOW OPEN TABLES can be used, which displays the list of open tables that are registered in the table cache. The FROM and LIKE clauses are ignored for this form of the statement. SHOW OPEN TABLES; SHOW [GLOBAL | SESSION] VARIABLES The SHOW VARIABLES statement displays a list of server variables and their values. SHOW VARIABLES; SHOW VARIABLES LIKE '%thread%'; As of MySQL 4.0.3, the server can display the values of variables at both the global (server-wide) or session (client-specific) level. By default, SHOW displays the session-level value for any given variable or the global value if no session value exists. To display global or session values explicitly, specify a level indicator: SHOW GLOBAL VARIABLES [LIKE pattern']; SHOW SESSION VARIABLES [LIKE pattern']; LOCAL is a synonym for SESSION. It is also possible to retrieve the values of dynamic variables using SELECT: SELECT @@GLOBAL.table_type, @@SESSION.table_type, @@LOCAL.table_type; Using SELECT has the advantage that you can more easily manipulate the query result in certain contexts. SHOW VARIABLES output includes the variables in the following list. Unless otherwise indicated, the variables listed have been present in MySQL at least as far back as MySQL 3.22.0. (Many of the InnoDB variables were introduced in MySQL 3.23.29 under names beginning with innobase_ and renamed in 3.23.37 to begin with innodb_.) Some of the variables are present only under certain configurations. For example, many of those that begin with bdb_ are shown only if the BDB table handler is present. As of MySQL 4.0.3, several server variables can be modified dynamically while the server is running. (The entry for the SET statement describes how to do this.) For variables that can be modified this way, the variable name is followed by the words global or session in parentheses to indicate the levels at which the variable can be modified. (Prior to MySQL 4.0.3, these parenthetical indicators have no meaning.) back_log The maximum number of pending connection requests that can be queued while current connections are being processed. ansi_mode Whether the server was started with the --ansi option. This variable was introduced in MySQL 3.23.6, and removed in 3.23.41 when the sql_mode variable was added. basedir The pathname to the root directory of the MySQL installation. bdb_cache_size The size of the buffer used by the BDB table handler to cache data and index rows. Starting MySQL with --skip-bdb to disable the BDB handler causes this to be set to zero, which reduces memory use. This variable was introduced in MySQL 3.23.14. bdb_home The BDB home directory; normally has the same value as datadir. This variable was introduced in MySQL 3.23.14. bdb_log_buffer_size The size of the buffer used for the BDB transaction log. This variable was introduced in MySQL 3.23.21. bdb_logdir The pathname to the directory in which the BDB handler writes log files. This variable was introduced in MySQL 3.23.14. bdb_max_lock The maximum number of simultaneous locks that can be placed on a BDB table. This variable was introduced in MySQL 3.23.29. bdb_shared_data Indicates whether or not BDB was started in multi-process mode. This variable was introduced in MySQL 3.23.29. bdb_tmpdir The directory where the BDB handler creates temporary files. This variable was introduced in MySQL 3.23.14. bdb_version Version number of the BDB handler. This variable was introduced in MySQL 3.23.31. binlog_cache_size (global) The size of the cache that is used to store SQL statements that are part of a transaction before they are flushed to the binary log. (This occurs only if the transaction is committed. If the transaction is rolled back, the statements are discarded.) This variable was introduced in MySQL 3.23.29. bulk_insert_buffer_size (global, session) The size of the cache used to help optimize bulk inserts into MyISAM tables. This includes LOAD DATA statements, multiple-row INSERT statements, and INSERT INTO ... SELECT statements. Setting the value to zero disables the optimization. This variable was introduced as myisam_bulk_insert_tree_size in MySQL 4.0.0 and renamed to bulk_insert_buffer_size in 4.0.3. character_set The name of the default character set. This variable was introduced in MySQL 3.23.3. character_sets The list of supported character sets. The value is a set of space-separated character set names. This variable was introduced in MySQL 3.23.15. concurrent_insert (global) Whether or not the server allows INSERT queries on MyISAM tables while SELECT queries are active. Enabled by default, but it can be disabled with --skip-concurrent-insert. This variable was introduced in MySQL 3.23.7. connect_timeout (global) The number of seconds that mysqld will wait for packets during the initial connection handshake. convert_character_set (session) The character set to use for mapping strings sent to and from the client. This variable was introduced in MySQL 4.0.3. datadir The pathname to the MySQL data directory. delayed_insert_limit (global) The number of rows from INSERT DELAYED statements that will be inserted into a table before checking whether any new SELECT statements for the table have arrived. If any have, the insert operation is suspended to allow retrievals to execute. delayed_insert_timeout (global) When the handler for INSERT DELAYED operations finishes inserting queued rows, it waits this many seconds to see if any new INSERT DELAYED rows arrive. If so, it handles them; otherwise, it terminates. delayed_queue_size (global) The number of rows that may be queued for INSERT DELAYED statements. If the queue is full, further INSERT DELAYED statements block until there is room in the queue. delay_key_write (global) Whether or not the server respects delayed key writes for MyISAM tables created with the DELAY_KEY_WRITE option. This variable can have three values. ON (the default value) tells the server to honor the DELAY_KEY_WRITE option for tables declared with that option. (Key writes are delayed for tables declared with DELAY_KEY_WRITE=1 but not for tables declared with DELAY_KEY_WRITE=0.) A value of OFF means that key writes are never delayed for any table, no matter how it was declared. A value of ALL forces key writes always to be delayed for every table, no matter how it was declared. This variable was introduced as delayed_key_write in MySQL 3.23.5 and renamed to delay_key_write in 3.23.8. flush (global) Indicates whether or not the server flushes tables after each update. This variable was introduced in MySQL 3.22.9, but was used only on Windows from 3.22.9 to 3.22.11. flush_time (global) If this variable has a non-zero value, tables are closed to flush pending changes to disk every flush_time seconds. The default value is 0 for UNIX and 1800 (30 minutes) for Windows; use the --flush option to change the value. This variable was introduced in MySQL 3.22.18. ft_boolean_syntax The list of operators that are supported for FULLTEXT searches that use IN BOOLEAN MODE. This variable was introduced in MySQL 4.0.1. ft_max_word_len The maximum length of words that can be included in a FULLTEXT index. Longer words are ignored. If you change the value of this variable, you should rebuild any FULLTEXT indexes that are present in tables managed by the server. This variable was introduced in MySQL 4.0.0. ft_max_word_len_for_sort The maximum length of words that are considered short enough for the fast index-creation method used by ALTER TABLE, CREATE INDEX, and REPAIR TABLE to build FULLTEXT indexes. Words that exceed this length are inserted using a slower method. This variable was introduced in MySQL 4.0.0. ft_min_word_len The minimum length of words that can be included in a FULLTEXT index. Shorter words are ignored. If you change the value of this variable, you should rebuild any FULLTEXT indexes that are present in tables managed by the server. This variable was introduced in MySQL 4.0.0. have_bdb YES if the BDB table handler is present and enabled, DISABLED if the handler is present but disabled, NO if the handler is not present. This variable was introduced in MySQL 3.23.30. have_innodb YES if the InnoDB table handler is present and enabled, DISABLED if the handler is present but disabled, NO if the handler is not present. This variable was introduced as have_innobase in MySQL 3.23.30 and renamed to have_innodb in MySQL 3.23.37. have_isam YES if the ISAM table handler is present and enabled, DISABLED if the handler is present but disabled, NO if the handler is not present. This variable was introduced in MySQL 3.23.30. have_openssl YES or NO to indicate whether the server supports encrypted client connections using SSL. This variable was introduced in MySQL 3.23.43 but is not meaningful until 4.0.0 when SSL support was implemented. have_query_cache Whether or not the query cache is available. This variable was introduced in MySQL 4.0.2. have_raid Indicates whether or not the RAID-related CREATE TABLE options are supported. This variable was introduced in MySQL 3.23.30. have_symlink Whether or not symbolic linking support is enabled. This variable was introduced in MySQL 4.0.0. init_file The name of the file containing SQL statements to be executed by the server when it starts (blank if none). This file is specified using the --init-file option. This variable was introduced in MySQL 3.23.2. innodb_additional_mem_pool_size The size of the InnoDB memory pool for storing internal data structures. This variable was introduced in MySQL 3.23.37. innodb_buffer_pool_size The size of the InnoDB cache for buffering table data and indexes. This variable was introduced in MySQL 3.23.37. innodb_data_file_path The specifications for the InnoDB tablespace component files. This variable was introduced in MySQL 3.23.37. innodb_data_home_dir The pathname to the directory under which the InnoDB tablespace components are located. This variable was introduced in MySQL 3.23.37. innodb_fast_shutdown Whether or not InnoDB will use its quicker shutdown method that skips some of the operations that it performs normally. This variable was introduced in MySQL 3.23.44. innodb_file_io_threads The number of file I/O threads used by InnoDB. This variable was introduced in MySQL 3.23.37. innodb_flush_log_at_trx_commit This option controls InnoDB log flushing when transactions are committed. | Value | Meaning | | 0 | Write to log once per second and flush to disk | | 1 | Write to log at each commit and flush to disk | | 2 | Write to log at each commit, but flush to disk only once per second | This variable was introduced in MySQL 3.23.37. innodb_flush_method The method InnoDB uses for log flushing. This variable was introduced in MySQL 3.23.39. innodb_force_recovery Normally 0, but can be set to a value from 1 to 6 to cause the server to start up after a crash even if InnoDB recovery fails. For a description of how this variable is used, see Chapter 13, "Database Backups, Maintenance, and Repair." This variable was introduced in MySQL 3.23.44. innodb_lock_wait_timeout The number of seconds InnoDB waits for a lock for a transaction. If the lock cannot be acquired, InnoDB rolls back the transaction. This variable was introduced in MySQL 3.23.37. innodb_log_arch_dir This variable is unused. It was introduced in MySQL 3.23.37. innodb_log_archive This variable is unused. It was introduced in MySQL 3.23.37. innodb_log_buffer_size The size of the InnoDB transaction log buffer. This variable was introduced in MySQL 3.23.37. innodb_log_files_in_group The number of log files InnoDB maintains. The product of innodb_log_files_in_group and innodb_log_file_size determines the total InnoDB log size. This variable was introduced in MySQL 3.23.37. innodb_log_file_size The size of each InnoDB log file. The product of innodb_log_files_in_group and innodb_log_file_size determines the total InnoDB log size. This variable was introduced in MySQL 3.23.37. innodb_log_group_home_dir The directory in which InnoDB log files are written. This variable was introduced in MySQL 3.23.37. innodb_mirrored_log_groups The number of InnoDB log file groups to maintain. The value should always be 1. This variable was introduced in MySQL 3.23.37. innodb_thread_concurrency The limit on the number of threads that InnoDB tries to maintain. This variable was introduced in MySQL 3.23.44. interactive_timeout (global, session) The number of seconds an interactive client connection can remain idle before the server considers itself free to close it. For non-interactive clients, the value of the wait_timeout variable is used instead. This variable was introduced in MySQL 3.23.7. join_buffer_size (global, session) The size of the full-join buffer (that is, the buffer for joins that are performed without use of indexes). This variable was called join_buffer prior to MySQL 3.23. key_buffer_size (global) The size of the buffer used for index blocks. This buffer is shared among connection-handler threads. This variable was called key_buffer prior to MySQL 3.23. language The language used to display error messages. The value can be either the language name or the pathname of the directory containing the language files. large_files_support Whether or not the server was built with support for handling large files. This variable was introduced in MySQL 3.23.28. local_infile (global) Whether or not LOCAL is allowed for LOAD DATA statements. This variable was introduced in MySQL 4.0.3. locked_in_memory Whether or not the server is locked in memory. This variable was introduced in MySQL 3.23.25. log Whether or not query logging is enabled. log_bin Whether or not the binary update log is enabled. This variable was introduced in MySQL 3.23.14. log_slave_updates Whether or not a replication slave server is logging updates that it receives from the master. Slave update logging can be enabled to allow a slave server to act as a master to another slave in a chained replication configuration. This variable was introduced in MySQL 3.23.17. log_slow_queries Whether or not the slow-query log is enabled. This variable was introduced in MySQL 4.0.2. log_update Whether or not update logging is enabled. This variable was introduced in MySQL 3.22.18. log_warnings (global, session) Whether or not to log non-critical warnings to the error log. This variable was introduced in MySQL 4.0.3. long_query_time (global, session) The number of seconds that defines a "slow" query. Any query taking longer than this causes the Slow_queries counter to be incremented. In addition, if the slow-query log is enabled, the query is written to the log. lower_case_table_names Whether or not to force table names as stored on disk to lowercase (and also database names as of MySQL 4.0.2). This variable was introduced in MySQL 3.23.6. low_priority_updates (global, session) Whether the server was started with the --low-priority-updates option to give updates a lower priority than retrievals. This variable was introduced in MySQL 3.22.5. max_allowed_packet (global, session) The maximum size of the buffer used for communication between the server and the client. The buffer is initially allocated to be net_buffer_length bytes long but can grow up to max_allowed_packet bytes as necessary. The maximum value for max_allowed_packet is 1GB from MySQL 4 and later, 16MB prior to MySQL 4. max_binlog_cache_size (global) The maximum binary log cache size. This restricts the total combined size of the statements that make up a transaction. This variable was introduced in MySQL 3.23.29. max_binlog_size (global) The maximum size of a binary log. If the log reaches this size, the log is rotated. The allowable range of values is 1KB to 1GB. This variable was introduced in MySQL 3.23.33. max_connections (global) The maximum number of simultaneous client connections allowed. max_connect_errors (global) The number of failed connections from a host that are allowed before the host is blocked from further connection attempts. This is done on the basis that someone may be attempting to break in from that host. The FLUSH HOSTS statement or mysqladmin flush-hosts command can be used to clear the host cache to re-enable blocked hosts. max_delayed_threads (global) The maximum number of threads that will be created to handle INSERT DELAYED statements. Any such statements that are received while the maximum number of handlers is already in use will be treated as non-DELAYED statements. This option was introduced as max_delayed_insert_threads in MySQL 3.22.15 and renamed to max_delayed_threads in 3.23.0. max_heap_table_size (global, session) The maximum allowed size of HEAP tables. This variable can be used to help prevent the server from using excessive amounts of memory. It was introduced in MySQL 3.23.0. max_join_size (global, session) When executing a join, the MySQL optimizer estimates how many row combinations it will need to examine. If the estimate exceeds max_join_size rows, an error is returned. This can be used if users tend to write indiscriminate SELECT queries that return an inordinate number of rows. max_sort_length (global, session) BLOB or TEXT values are sorted using the first max_sort_length bytes of each value. max_tmp_tables (global, session) The maximum number of temporary tables a client can have open simultaneously. This variable was introduced in MySQL 3.23.0 but currently is unused. max_user_connections (global) The maximum number of simultaneous client connections allowed to any single account. The default value is zero, which means "no limit." The number of per-account connections is bound in any case by the value of max_connections. This variable was introduced in MySQL 3.23.34. To specify connection limits for specific accounts, use the GRANT statement. max_write_lock_count (global) After this many write locks, the server starts allowing queries that require read locks a higher priority than normal. This variable was introduced in MySQL 3.23.7. myisam_bulk_insert_tree_size See the description for bulk_insert_buffer_size. myisam_max_extra_sort_file_size (global, session) This value is used by the MyISAM handler to help decide when to use a slower but safer key cache index creation method. This variable was introduced in MySQL 3.23.37. The value is measured in bytes as of MySQL 4.0.3 and in MB before that. myisam_max_sort_file_size (global, session) MyISAM table index rebuilding for statements such as REPAIR TABLE, ALTER TABLE, or LOAD DATA can use a temporary file or the key cache. The value of this variable determines which method is used; if the temporary file would be larger than this value, the key cache is used instead. This variable was introduced in MySQL 3.23.37. The value is measured in bytes as of MySQL 4.0.3 and in MB before that. myisam_recover_options The value of the --myisam-recover option that the server was started with to specify the MyISAM auto-checking mode. This variable was introduced in MySQL 3.23.36. myisam_sort_buffer_size (global, session) The size of the buffer that is allocated to sort an index for MyISAM tables during ALTER TABLE, CREATE INDEX, and REPAIR TABLE operations. This variable was introduced in MySQL 3.23.16. named_pipe Whether or not named pipe support is enabled. This is used for Windows NT-based servers. This variable was introduced in MySQL 3.23.50. (Prior to 3.23.50, named pipe support is enabled by default for servers that support named pipes.) net_buffer_length (global, session) The initial size of the buffer used for communication between the server and the client. This buffer can be expanded up to max_allowed_packet bytes long. net_read_timeout (global, session) The number of seconds to wait for data from a client connection before timing out. This variable was introduced in MySQL 3.23.20. net_retry_count (global, session) The number of times to retry an interrupted read. This variable was introduced in MySQL 3.23.7. net_write_timeout (global, session) The number of seconds to wait before timing out while writing a block to a client connection. This variable was introduced in MySQL 3.23.20. open_files_limit If non-zero, this variable is the number of file descriptors the server will attempt to reserve. If zero, the server uses the larger of max_connections*5 and max_connections + table_cache*2 as the number of descriptors to reserve. This variable was introduced in MySQL 3.23.30. pid_file The pathname of the file where the server writes its process ID number. This variable was introduced in MySQL 3.22.23. port The number of the TCP/IP port to which the server listens for client connections. protocol_version The version number of the client/server protocol the server is using. This variable was introduced in MySQL 3.22.18. query_cache_limit (global) The maximum size of cached query results; larger results are not cached. This variable was introduced in MySQL 4.0.1. query_cache_size (global) The amount of memory to use for query result caching. Set this variable to zero to disable the query cache. This variable was introduced in MySQL 4.0.1. query_cache_type (global, session) The mode of operation of the query cache. | Mode | Meaning | | OFF | Don't cache | | ON | Cache queries except those that begin with SELECT SQL_NO_CACHE | | DEMAND | Cache on demand only those queries that begin with SELECT SQL_CACHE | This variable was introduced as query_cache_startup_type in MySQL 4.0.1 and renamed to query_cache_type in 4.0.3. read_buffer_size (global, session) The size of the buffer used by threads that perform sequential table scans. A buffer is allocated as necessary per client. Prior to MySQL 4.0.3, this variable is called record_buffer. read_rnd_buffer_size (global, session) The size of the buffer used for reading rows in order after a sort. A buffer is allocated as necessary per client. This variable was introduced as record_rnd_buffer in MySQL 3.23.41 and renamed to read_rnd_buffer_size in 4.0.3. record_buffer See the description for read_buffer_size. record_rnd_buffer See the description for read_rnd_buffer_size. rpl_recovery_rank (global) The server's replication recovery rank. This is unused currently; its purpose in the future will be to allow a slave server that has lost its master to select a new master from among communicating replication servers. This variable was introduced in MySQL 4.0.0. safe_show_database Whether or not to show databases names unconditionally. If the value is ON, users can see names only for databases for which they have database or table privileges. If OFF, all database names are shown to all users. This variable was introduced in MySQL 3.23.30. As of 4.0.3, you should instead grant the SHOW DATABASES privilege to users that need to see all databases. This variable has been removed as of MySQL 4.0.5. server_id (global) The server's replication ID number. This variable was introduced in MySQL 3.23.26. skip_external_locking Whether or not use of external locking (file system locking) is suppressed. This variable as called skip_locking prior to MySQL 4.0.3. skip_locking See the description for skip_external_locking. skip_networking OFF to allow TCP/IP connections, ON to disable them. In the latter case clients can connect from the local host only, using UNIX socket connections under UNIX or named pipes under Windows. This variable was introduced in MySQL 3.22.23. skip_show_database ON to show database names only to users who have the SHOW DATABASES privilege (PROCESS prior to MySQL 4.0.2). OFF for no such requirement. This variable was introduced in MySQL 3.23.4. slave_net_timeout (global) The number of seconds to wait for data from a master server before timing out. This variable was introduced in MySQL 3.23.40. slow_launch_time (global) The number of seconds that defines "slow" thread creation. Any thread taking longer to create causes the Slow_launch_threads status counter to be incremented. This variable was introduced in MySQL 3.23.15. socket The pathname to the UNIX domain socket, or the name of the named pipe under Windows. sort_buffer_size (global, session) The size of the buffer used by threads for performing sort operations (GROUP BY or ORDER BY). This buffer is allocated as necessary per client. Normally, if you may have many clients that do sorting at the same time, it is unwise to make this value very large (more than 1MB). Prior to MySQL 4.0.3, this variable is called sort_buffer. sql_mode The value of the --sql-mode option. This variable was introduced in MySQL 3.23.41; it replaces ansi_mode. table_cache (global) The maximum number of tables that can be open. This cache is shared between threads. table_type (global, session) The default table type, used for tables that are created without a TYPE =type_name option or with an unsupported type_name value. This variable was introduced in MySQL 3.23.0. thread_cache_size (global) The maximum number of threads to maintain in the thread cache. This variable was introduced in MySQL 3.23.16. thread_concurrency This variable applies only to Solaris. The value is passed to thr_concurrency() to provide a hint to the thread manager about how many threads to run simultaneously. This variable was introduced in MySQL 3.23.7. thread_stack The stack size for each thread. timezone The server's time zone setting. This variable was introduced in MySQL 3.23.15. tmpdir The pathname to the directory where the server creates temporary files. This option was introduced in MySQL 3.22.4. It was called tmp_dir from 3.22.0 to 3.22.3. tmp_table_size (global, session) The maximum number of bytes allowed for temporary tables. Prior to MySQL 3.23, if a table exceeds this size, mysqld returns an error to the client. From 3.23 on, if a table exceeds this size, the server converts that table to a MyISAM table on disk. If you have memory to spare, higher values of this variable allow the server to maintain larger temporary tables. transaction_isolation See the description for tx_isolation. tx_isolation (global, session) The default transaction isolation level. This variable was introduced as transaction_isolation in MySQL 3.23.36 and renamed to tx_isolation in 4.0.3. version The server version. The value consists of a version number, possibly followed by one or more suffixes. The suffix values are listed in the description of the VERSION() function in Appendix C, "Operator and Function Reference." wait_timeout (global, session) The number of seconds a non-interactive client connection can remain idle before the server considers itself free to close it. For interactive clients, the value of the interactive_timeout variable is used instead. SLAVE SLAVE {START | STOP} [slave_options] This statement controls the operation of a replication slave server. SLAVE START initiates a slave thread and SLAVE STOP terminates it. SLAVE was introduced in MySQL 3.23.16. As of MySQL 4.0.2, an optional slave_options clause can be specified. It should consist of one or more of the following options, separated by commas: TRUNCATE TRUNCATE [TABLE] tbl_name TRUNCATE TABLE performs a fast truncation of table contents by dropping and recreating the table. This is much faster than deleting each row individually. This statement is not transaction-safe; an error will occur should you issue a TRUNCATE TABLE statement in the middle of an active transaction or while you are holding any explicit table locks. TRUNCATE TABLE was introduced in MySQL 3.23.28, but prior to 3.23.33, the TABLE keyword must be omitted. UNION select_statement UNION [ALL] select_statement [UNION select_statement] ... UNION isn't really a separate statement, it's a way of combining SELECT statements such that their results are concatenated one after the other. Each SELECT statement must produce the same number of columns in its result set. The names and types of the columns in the final result are determined by the column names in the first SELECT. If the types of corresponding columns do not match, implicit type conversion will take place for rows from the second and following tables. By default, UNION eliminates duplicates. This is like the effect of DISTINCT but extended over all the result sets. UNION ALL preserves duplicates so that all rows are returned. ORDER BY or LIMIT clauses can be applied to an individual SELECT statement within a UNION by placing the statement within parentheses. ORDER BY or LIMIT clauses that occur at the end of the entire UNION and not within parentheses apply to the UNION result as a whole. In this case, any columns named in an ORDER BY should refer to the names of the columns in the first SELECT. UNION was introduced in MySQL 4.0.0. UNLOCK TABLE UNLOCK {TABLE | TABLES} This statement releases any table locks being held by the current client. UPDATE UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name=expr [, col_name=expr ] ... [WHERE where_expr] [ORDER BY ... ] [LIMIT n] UPDATE [LOW_PRIORITY] [IGNORE] tbl_name , tbl_name ... SET col_name=expr [, col_name=expr ] ... [WHERE where_expr] [ORDER BY ... ] [LIMIT n] For the first syntax, UPDATE modifies the contents of existing rows in the table tbl_name. The rows to be modified are those selected by the expression specified in the WHERE clause. For those rows that are selected, each column named in the SET clause is set to value of the corresponding expression. UPDATE member SET expiration = NULL, phone = '197-602-4832' WHERE member_id = 14; If no WHERE clause is given, all records in the table are updated. UPDATE returns the number of rows that were updated. However, a row is not considered as having been updated unless some column value actually changed. Setting a column to the value it already contains is not considered to affect the row. If your application really needs to know how many rows matched the WHERE clause regardless of whether or not the UPDATE actually changed any values, you should specify the CLIENT_FOUND_ROWS flag when you establish a connection to the server. See the entry for the mysql_real_connect() function in Appendix F, "C API Reference." LOW_PRIORITY causes the statement to be deferred until no clients are reading from the table. LOW_PRIORITY was introduced in MySQL 3.22.5. If updating a record would result in a duplicate key value in a unique index, UPDATE will terminate in error. Adding IGNORE causes such records not to be updated and no error occurs. IGNORE was introduced in MySQL 3.23.16. ORDER BY causes rows to be updated according to the resulting sort order. This clause was introduced in MySQL 4.0.0 and has same syntax as for SELECT. If the LIMIT clause is given, the value n specifies the maximum number of rows that will be updated. LIMIT was introduced in MySQL 3.23.3. The second UPDATE syntax is like the first but allows multiple tables to be named to perform a multiple-table update. In this case, the WHERE clause can specify conditions based on a join between tables, and the SET clause can update columns in multiple tables. For example, the following statement updates rows in t1 having id values that match those in t2, copying the quantity values from t2 to t1: UPDATE t, t2 SET t.quantity = t2.quantity WHERE t.id = t2.id; Multiple-table updates were introduced in MySQL 4.0.2. USE USE db_name Selects db_name to make it the current database (the default database for table references that include no explicit database name). The USE statement fails if the database doesn't exist or if you have no privileges for accessing it. | 