Team LiB
Previous Section Next Section

SQL Statement Syntax

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 permissions for accessing the database db_name.

ALTER DATABASE

ALTER DATABASE [db_name] db_attr [, db_attr] ...

This statement changes database attributes. The db_attr database attribute values that may be specified are the same as those listed in the entry for CREATE DATABASE.

ALTER DATABASE requires the ALTER privilege for the database.

The database name, db_name, is mandatory before MySQL 4.1.8. As of MySQL 4.1.8, the database name can be omitted, in which case the statement applies to the default database. If there is no default database, an error occurs.

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, and then specify 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 altered 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 alteration actions separated by commas. Each action is performed in turn. An action may be any of the following:

  • ADD [COLUMN] col_definition [FIRST | AFTER col_name]

    Adds a column to the table. col_definition is the column definition; 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.

  • 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, is ignored. reference_definition defines how the foreign key relates to the parent table. The syntax is as described in the entry for CREATE TABLE.

    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 non-binary string columns in the table separated by commas.

    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 BINARY, VARBINARY, CHAR, and VARCHAR columns, you can index a prefix of the column, using col_name(n) syntax to specify a prefix length of n (the first n bytes for binary data types and the first n characters for non-binary data types). For BLOB and TEXT columns, you must specify a prefix value. If the index name index_name is not specified, MySQL chooses a name 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 action. Each column must be defined as NOT NULL. An error occurs if a primary key already exists.

    ALTER TABLE president ADD PRIMARY KEY (last_name, first_name);
    

  • ADD SPATIAL [KEY | INDEX] [index_name] (index_columns)

    Adds a SPATIAL index to a MyISAM table. The index is based on the columns named in index_columns, which is a list of one or more spatial columns in the table separated by commas. Each column must be defined as NOT NULL.

    ALTER TABLE coordinates ADD SPATIAL (x,y);
    

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

    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 implicit default value might assigned, as described in the discussion of default values in the entry for the CREATE TABLE statement.

    ALTER TABLE grade_event ALTER category SET DEFAULT 'Q';
    ALTER TABLE grade_event ALTER category DROP DEFAULT;
    

  • CHANGE [COLUMN] col_name col_definition [FIRST | AFTER col_name]

    Changes a column's name and definition. col_name is the column's current name, and col_definition is the definition to which the column should be changed. col_definition is in the same format as that used for the CREATE TABLE statement, including any column attributes such as NULL, NOT NULL, and DEFAULT. Note that the definition 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 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;
    

  • CONVERT TO CHARACTER SET {charset | DEFAULT} [COLLATE collation]

    Converts all non-binary character columns in the table to the given character set. The COLLATE clause may be given to specify a collation as well. If COLLATE is omitted, the default collation for the character set is used. This action was introduced in MySQL 4.1.2.

  • DISABLE KEYS

    For a MyISAM table, this action disables the updating of non-unique indexes that normally occurs when the table is changed. ENABLE KEYS can be used to re-enable index updating.

    ALTER TABLE score DISABLE KEYS;
    

  • DISCARD TABLESPACE

    This action applies to InnoDB tables that use individual tablespaces. For such a table, it removes the tbl_name.ibd file that stores the table contents. This action cannot be used in conjunction with other actions. It was introduced in MySQL 4.1.1.

  • 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 are parsed but ignored and have no effect.

  • DROP FOREIGN KEY index_name

    Drops the foreign key definition that has the given name.

  • DROP {INDEX | KEY} 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. Before MySQL 4.1.2, 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.

    ALTER TABLE score ENABLE KEYS;
    

  • IMPORT TABLESPACE

    This action applies to InnoDB tables that use individual tablespaces. For such a table, it associates the tbl_name.ibd file in the table's database directory with the table. (Presumably, the table's former .ibd file previously had been removed with DISCARD TABLESPACE.) This action cannot be used in conjunction with other actions. It was introduced in MySQL 4.1.1.

  • MODIFY [COLUMN] col_definition [FIRST | AFTER col_name]

    Changes the definition of a column. The column definition col_definition is given, using the same format for column definitions as is shown in the entry for the CREATE TABLE statement, including any column attributes such as NULL, NOT NULL, and DEFAULT. The definition begins with a column name, which is how you identify which column to modify. FIRST and AFTER 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 names or one or more columns in the table separated by commas. The default sort order is ascending. A column name may 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.

    ALTER TABLE score ORDER BY event_id, student_id;
    

  • RENAME [TO | AS] new_tbl_name

    Renames the table tbl_name to new_tbl_name. If you rename an InnoDB table on which other tables depend for foreign key relationships, InnoDB adjusts the dependencies to point to the renamed table.

    ALTER TABLE president RENAME TO prez;
    

  • table_options

    Specifies table options of the kind that may be given in the table_options part of a CREATE TABLE statement.

    ALTER TABLE score ENGINE = MYISAM CHECKSUM = 1;
    ALTER TABLE sayings CHARACTER SET utf8;
    

    Any version-specific or storage engine-specific constraints on the availability of a given table option are as described in the entry for the CREATE TABLE statement.

    Note: The CHARACTER SET table option changes the default table character set without converting the columns to that character set, except that before MySQL 4.1.3 it also converts the columns unless you use DEFAULT CHARACTER SET.

ALTER VIEW

ALTER
  [ALGORITHM = { UNDEFINED | MERGE | TEMPTABLE}]
  VIEW view_name
  [(col_name [, col_name] ...)] AS
  select_stmt
  [WITH [CASCADED | LOCAL] CHECK OPTION]

Alters an existing view to have the given definition. The meaning of the ALGORITHM clause and the clauses following the view name are described in the entry for CREATE VIEW.

ANALYZE TABLE

ANALYZE
  [LOCAL | NO_WRITE_TO_BINLOG]
  {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, InnoDB, 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.

If binary logging is enabled, MySQL writes the ANALYZE TABLE statement to the binary log unless the LOCAL or NO_WRITE_TO_BINLOG option is given.

BACKUP TABLE

BACKUP {TABLE | TABLES} tbl_name [, tbl_name] ... TO 'dir_name'

BACKUP TABLE creates table backups that can be restored using RESTORE TABLE. It 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 FILE privilege as well as the SELECT privilege for the tables to be copied. It copies the table format and data files (the .frm and .MYD files), which are the minimum required to restore the table. The files must not already exist. Index files are not copied, because RESTORE TABLE can re-create them as necessary from the format 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. This will of course cause each table to be unavailable for a longer time to other clients that want to update the tables.

Ownership for the files created by BACKUP TABLE is assigned to the account that is used to run the server.

The following statement backs up table t by creating files t.frm and t.MYD in the directory /var/mysql/bkup:

BACKUP TABLE t TO '/var/mysql/bkup';

The mysqlhotcopy program can be used as an alternative to the BACKUP TABLE statement.

BEGIN

BEGIN [WORK]

This statement is a synonym for START TRANSACTION; see the entry for that statement.

CACHE INDEX

CACHE INDEX
  tbl_name [[INDEX | KEY] (index_name [, index_name] ...)]
  [, tbl_name [[INDEX | KEY] (index_name [, index_name] ...)]]
  IN cache_name

Sets up an association between one or more MyISAM tables and the named key cache, which must already exist. You must have the INDEX privilege for each table named in the statement. The default key cache is named default. The table indexes can be loaded into the cache later with LOAD INDEX. Currently, the statement associates all indexes in each table with the cache, even though the syntax allows for designating only certain indexes. Individual-index cache association remains for future implementation.

The following statement caches indexes for the member statement in the key cache named member_cache:

CACHE INDEX member IN member_cache;

CACHE INDEX produces output in the format described under the entry for CHECK TABLE.

MyISAM key cache management is discussed further in Chapter 11, "General MySQL Administration."

This statement was introduced in MySQL 4.1.1.

CHANGE MASTER

CHANGE MASTER TO master_defs

Changes replication parameters for a slave server, to indicate which master host to use, how to connect to it, or which logs to use. The parameters are saved in the slave's master.info and relay-log.info files, which are used for subsequent slave restarts.

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 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 TCP/IP port number to use for connecting to the master server.

  • MASTER_SSL = {0 | 1}

    Disallow (0) or allow (1) use of SSL for connecting to the master server.

  • MASTER_SSL_CA = 'file_name'

    The pathname to the certificate authority file for SSL connections to the master.

  • MASTER_SSL_CAPATH = 'dir_name'

    The pathname to a directory of trusted certificates to be used for certificate verification for SSL connections to the master.

  • MASTER_SSL_CERT = 'file_name'

    The pathname to the certificate file for SSL connections to the master.

  • MASTER_SSL_CIPHER = 'str'

    A string listing the SSL ciphers that may be used to encrypt traffic sent over SSL connections to the master. The value should name one or more cipher types separated by commas.

  • MASTER_SSL_KEY = 'file_name'

    The pathname to the key file for SSL connections to the master.

  • MASTER_USER = 'user_name'

    The username of the account to use for connecting to the master server.

  • RELAY_LOG_FILE = 'file_name'

    The slave relay log filename.

  • RELAY_LOG_POS = n

    The current position within the slave relay log.

Parameters that are not specified in the statement maintain their current values, with the following exception: Changes to MASTER_HOST or MASTER_PORT normally indicate that you're switching to a different master server, so in those cases, the MASTER_LOG_FILE and MASTER_LOG_POS values are set to the beginning of the master's first binary log file.

You should not mix the MASTER_LOG_FILE and MASTER_LOG_POS options with the RELAY_LOG_FILE and RELAY_LOG_POS options in the same statement.

The CHANGE MASTER statement deletes any existing relay log files and begins a new one unless the RELAY_LOG_FILE or RELAY_LOG_POS options are specified.

CHECK TABLE

CHECK {TABLE | TABLES} tbl_name [, tbl_name] ... [options]

This statement checks tables for errors. It works with MyISAM and InnoDB tables, and requires the SELECT privilege on each table. As of MySQL 5.0.2, CHECK TABLE also can be used to check views for problems.

options, if given, is a list naming one or more of the following options (not separated by commas). These options apply to MyISAM tables and are ignored for InnoDB tables and views.

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

If you don't specify one of QUICK, MEDIUM, or EXTENDED when checking a MyISAM table, CHECK TABLE defaults to MEDIUM if the table has variable-length rows. If it has fixed-length rows, the default is QUICK if you specify CHANGED or FAST, and MEDIUM otherwise.

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, CACHE INDEX, LOAD INDEX INTO CACHE, 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 carried out by the statement. The Msg_type and Msg_text columns provide information about the result of the operation.

CHECKSUM TABLE

CHECKSUM {TABLE | TABLES} tbl_name [, tbl_name] ...
  [QUICK | EXTENDED]

Reports a table checksum. By default, the statement reports the live checksum if the storage engine supports it. (A live checksum is one that is updated each time the table is modified.) For MyISAM tables, you can turn on live checksumming for a table by using the CHECKSUM = 1 option with CREATE TABLE or ALTER TABLE.

With the QUICK option, the statement reports the live checksum if there is one and NULL otherwise. With the EXTENDED option, a checksum is calculated by reading the entire table and then reported. This operation becomes slower as the table size increases.

CHECKSUM TABLE was introduced in MySQL 4.1.1.

COMMIT

COMMIT

Commits changes made by statements in the current transaction, to record those changes permanently in the database. COMMIT works only for transaction-safe storage engines. (For non-transactional storage engines, statements are committed as they are executed.)

COMMIT has no effect if autocommit mode has not been disabled with START trANSACTION or by setting the autocommit variable 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
START TRANSACTION
TRUNCATE TABLE
UNLOCK TABLES  (if tables currently are locked)

CREATE DATABASE

CREATE DATABASE [IF NOT EXISTS] db_name [db_attr] ...

db_attr:
    [[DEFAULT] CHARACTER SET charset]
  | [[DEFAULT] COLLATE collation]

Creates a database with the given name. The statement fails if you don't have the CREATE privilege for the database. 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.

The optional CHARACTER SET and COLLATE attributes may be given after the database name to specify a default character set and collation for the database. These attributes are used for tables for which no character set or collation is given explicitly. charset can be a character set name, or DEFAULT to use the current server character set. collation can be a collation name, or DEFAULT to use the current server collation.

If neither attribute is given, the server character set and collation are used. If CHARACTER SET is given without COLLATE, the default collation for the character set is used. If COLLATE is given without CHARACTER SET, the character set is determined from the collation. If both CHARACTER SET and COLLATE are used, the collation must be compatible with the character set.

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. CREATE FUNCTION also can be used to create a stored function. See "Stored Routine Syntax."

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

On many systems, the UDF mechanism requires dynamic linking. In such cases, CREATE FUNCTION requires that the server be built as a dynamically linked binary (not as a static binary). For instructions on writing user-defined functions, refer to the MySQL Reference Manual.

CREATE INDEX

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
  [USING = index_type]
  ON tbl_name (index_columns)

Adds an index named index_name to the table tbl_name. This statement is handled internally as an ALTER TABLE statement. See the entry for ALTER TABLE for details.

The UNIQUE, FULLTEXT, or SPATIAL keywords can be given to indicate a specific kind of index. If none are given, a non-unique index is created. CREATE INDEX cannot be used to create a PRIMARY KEY; use ALTER TABLE instead.

The USING clause can be given to specify the indexing algorithm. The types are described in the entry for CREATE TABLE. TYPE is a synonym for USING.

If you want to create several indexes on a table, it's preferable to use ALTER TABLE; you can add all the indexes with a single statement, which is faster than adding them individually.

CREATE TABLE

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
  {
      (create_definition,...) [table_options] [trailing_select]
    | [table_options] trailing_select
    | LIKE tbl_name2
    | (LIKE tbl_name2)
  }

table_options: (see following discussion)

trailing_select:
  [IGNORE | REPLACE] [AS] select_stmt

create_definition:
  {   col_definition [reference_definition]
    | [CONSTRAINT [constraint_name]] PRIMARY KEY
        [index_name] [USING = index_type] (index_columns)
    | [CONSTRAINT [constraint_name]] UNIQUE [INDEX | KEY]
        [index_name] [USING = index_type] (index_columns)
    | {INDEX | KEY} [index_name] [USING = index_type] (index_columns)
    | FULLTEXT [INDEX | KEY] [index_name] (index_columns)
    | SPATIAL [INDEX | KEY] [index_name] (index_columns)
    | [CONSTRAINT [constraint_name]] FOREIGN KEY [index_name] (index_columns)
        [reference_definition]
    | CONSTRAINT [constraint_name] [CHECK (expr)]
    | [CONSTRAINT [constraint_name]] CHECK (expr)
  }

col_definition:
  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 | MATCH SIMPLE]

reference_action:
  {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}

The CREATE TABLE statement creates a new table named tbl_name in the default database. If the name is specified as db_name.tbl_name, the table is created in the named database. The statement fails if you don't have the CREATE privilege for the table.

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. During its existence, it hides from that client any non-TEMPORARY table that has the same name.

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 and no error occurs. Second, if TEMPORARY is specified and the original table is not a temporary table, the new temporary table is created, and 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 to the current client 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_stmt is specified (in the form of an arbitrary SELECT statement), the table is created using the result set that it returns. These clauses are described more fully in the following sections.

Column and index definitions. A create_definition may be a column or index definition, a FOREIGN KEY clause, or a CHECK clause. CHECK is parsed but ignored. FOREIGN KEY is treated similarly, except for InnoDB tables.

A column definition col_definition 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 data types listed in Appendix B, "Data Type Reference." See that appendix for type-specific attributes that apply to the columns you want to define. Other optional keywords that may follow the data type are as follows:

  • NULL, 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, spatial types, or columns with the AUTO_INCREMENT attribute. Except for TIMESTAMP, a default value must be a constant, specified as a number, a string, or NULL. For the rules that MySQL uses for assigning a default value if you include no DEFAULT clause, see "Specifying Column Default Values," in Chapter 3, "Working with Data in MySQL."

  • AUTO_INCREMENT

    This keyword applies only to integer data 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. (Some storage engines allow the initial value to be specified with an AUTO_INCREMENT table option. See the discussion of table options that follows.) The column must also be indexed and should be NOT NULL. There can 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. MySQL adds NOT NULL to the column definition if you omit it.

  • UNIQUE [KEY]

    Specifies that the column is a UNIQUE index.

  • 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, FULLTEXT, and SPATIAL 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. If the index name index_name is not specified, MySQL chooses a name automatically based on the name of the first indexed column. The index is created for the columns named in index_columns, each of which must be a column in tbl_name. If multiple columns are named in the index definition, they should be separated by commas. For CHAR, VARCHAR, BINARY, VARBINARY, TEXT, and BLOB columns, you can index a prefix of the column, using col_name(n) syntax to specify a prefix length of n, where n is from 1 to 255. (1 to 1000 for MyISAM and InnoDB as of MySQL 4.1.2.) The prefix length is in bytes for binary string columns and characters for non-binary string columns. For BLOB and TEXT columns, you must specify a prefix value. Prefixes for columns named in a FULLTEXT index are ignored if given.

FULLTEXT and SPATIAL indexes are allowed only for MyISAM tables. FULLTEXT indexes are allowed only for non-binary string columns (CHAR, VARCHAR, TEXT).

PRIMARY KEY columns must always be defined NOT NULL; MySQL adds NOT NULL to the definition of such columns if you omit it.

For index definitions that allow a USING clause, the index_type value specifies an indexing algorithm. This can be BTREE for MyISAM and InnoDB tables, and either HASH or BTREE for MEMORY tables. TYPE is a synonym for USING.

Table options. The table_options clause specifies additional characteristics of the table. It may include one or more of the options in the following list. Each specifier applies to all storage engines unless otherwise noted. Before MySQL 4.1, the = in each option setting is mandatory and settings should be separated by whitespace. As of MySQL 4.1, the = is optional and settings can be separated by whitespace or commas.

  • AUTO_INCREMENT = n

    The first AUTO_INCREMENT value to be generated for the table. This option is effective only for MyISAM and MEMORY tables, and for InnoDB tables as of MySQL 5.0.3. However, for InnoDB tables, the effect is canceled if you restart the server before generating any AUTO_INCREMENT values.

  • 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 storage engine can use internal row pointers from 2 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 provide information that allows the MyISAM storage engine to adjust the internal pointer width. A large product of these values causes the engine to use wider pointers. (Conversely, a small product allows the engine to use smaller pointers. This won't save you much space for a single small table, but the cumulative savings may be significant if you have many of them.)

    As of MySQL 4.1.2, to size the data pointers directly, you can set the myisam_data_pointer_size system variable before creating the table.

  • [DEFAULT] CHARACTER SET {charset | DEFAULT}

    Specifies the table's default character set. charset may be a character set name, or DEFAULT to use the database character set. This option determines which character set to use for character columns that are defined 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 table option also applies to subsequent table modifications made with ALTER TABLE for character column changes that do not name a character set explicitly.

  • CHECKSUM = {0 | 1}

    If this option is set to 1, MySQL maintains a live checksum for the table that is updated whenever the table is modified. There is a slight penalty for updates to the table, but the presence of checksums improves the table checking process. (MyISAM tables only.)

  • [DEFAULT] COLLATE = {collation | DEFAULT}

    Specifies the table's default character set collation. collation may be a collation name, or DEFAULT to use the default collation of the table character set.

  • 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 only for MyISAM tables and only on Unix. It indicates the directory where the data (.MYD) file should be written. 'dir_name' must be a full pathname. This option works only if the server is started without the --skip-symbolic-links option. On some Unix variants, symlinks are not thread-safe and are disabled by default.

  • DELAY_KEY_WRITE = {0 | 1}

    If this is set to 1, the key cache is flushed only occasionally for the table, rather than after each insert operation (MyISAM tables only). This improves performance but may require that the table be repaired if a crash occurs.

  • ENGINE = engine_name

    Specifies the storage engine to use for the table. The various storage engines are described in the "Storage Engine Characteristics," in Chapter 2, "MySQL SQL Syntax and Use." The default engine is MyISAM unless the server has been configured otherwise. You can start the server with a different default engine by using the --default-storage-engine option. The known engine names can be displayed with the SHOW ENGINES statement. If you name a storage engine that is legal but unavailable, MySQL uses the default engine as of version 4.1.2, and MyISAM before that. If you name an unknown engine, an error results.

  • INDEX DIRECTORY = 'dir_name'

    This option is used only for MyISAM tables and only on Unix. It indicates the directory where the index (.MYI) file should be written. 'dir_name' must be a full pathname. This option 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.

  • MAX_ROWS = n

    The maximum number of rows you plan to store in the table. The table will be created to allow at least this many rows. The description of the AVG_ROW_LENGTH option indicates how this value is used.

  • MIN_ROWS = n

    The minimum number of rows you plan to store in the table. This option can be used for MEMORY tables to give the MEMORY storage engine 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. 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 format file. This option normally has no effect; it is enabled only for certain support contract customers.

  • RAID_TYPE = {1 | STRIPED | RAID0}
    RAID_CHUNKS = n
    RAID_CHUNKSIZE = n
    

    These options are specified together for use with MyISAM tables to achieve larger effective table sizes. They 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 KB (1024 bytes). Directories are named using hexadecimal digits in the sequence 00, 01, and so forth. For example, if RAID_CHUNKS is 64 and RAID_CHUNKSIZE is 1000, the server creates 64 directories named 00 tHRough 3f, and writes up to 1000KB of data to the file in each directory. The maximum RAID_CHUNKS value is 255.

  • ROW_FORMAT = {DEFAULT | DYNAMIC | FIXED | COMPRESSED}

    This option applies only to MyISAM tables and specifies the row storage type. A value of DYNAMIC causes CHAR and BINARY columns to be created as VARCHAR and VARBINARY. The value cannot be FIXED if the table contains BLOB or TEXT columns. Otherwise, a value of FIXED causes VARCHAR and VARBINARY columns to be created as CHAR and BINARY. A value of COMPRESSED can be set only by the myisampack program and indicates that the table is compressed and read-only.

  • TYPE = engine_name

    This is a deprecated synonym for the ENGINE table option. If you use it, MySQL recognizes it but generates a warning.

  • UNION = (tbl_list)

    This option is used for MERGE tables. It specifies the list of MyISAM tables that make up the MERGE table.

Trailing LIKE clause. If a trailing LIKE tbl_name2 clause is given, the table is created as an empty copy of tbl_name2. The copy will include the same column definitions, index definitions, and table options, with these exceptions: The DATA DIRECTORY and INDEX DIRECTORY table options are not copied, nor are foreign key definitions.

Trailing SELECT statement. If a select_stmt clause is specified (as a trailing SELECT statement), the table is created using the contents of the result set returned by the statement. 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, the statement aborts with an error.

Foreign key support. The InnoDB storage engine 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 default actions are to prevent deletes or updates to the parent or child tables that would compromise referential integrity. The RESTRICT and NO ACTION actions are the same as specifying no action. The ON DEFAULT and ON UPDATE clauses may be given to specify explicit actions. The actions that InnoDB implements are CASCADE (delete or update the corresponding child table records) and SET NULL (set the foreign key columns in the corresponding child table records to NULL). The SET DEFAULT action is not implemented and InnoDB issues an error.

MATCH clauses in REFERENCE definitions are parsed but ignored. If you specify a foreign key definition for a storage engine other than InnoDB, the entire definition is ignored.

Further discussion of foreign keys is given in "Foreign Keys and Referential Integrity," in Chapter 2.

Examples: 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 MEMORY table for greater speed:

CREATE TEMPORARY TABLE tmp_table
    (id MEDIUMINT NOT NULL UNIQUE, name CHAR(40))
    ENGINE = MEMORY;

Create a table as an empty copy of another table:

CREATE TABLE prez_copy LIKE president;

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 column definitions are specified for a table created and populated by means of a trailing SELECT statement, the definitions are applied after the table contents have been inserted into the table. For example, you can define 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;

You can specify definitions 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 UNSIGNED NOT NULL AUTO_INCREMENT, b DATE, PRIMARY KEY (a))
    SELECT a, b, c FROM old_tbl;

CREATE TRIGGER

CREATE TRIGGER trigger_name action event
  ON tbl_name
  FOR EACH ROW trigger_stmt

Associates a trigger with a table, causing the given action to be activated when a given event occurs for the table. When this happens, the triggered statement is executed. The trigger name can be given in tbl_name format to name a table in the current database, or db_name.tbl_name format to name a table in a specific database.

The trigger action is either BEFORE or AFTER, indicating that the triggered statement should be executed before or after each row processed by the statement that caused the trigger to be activated.

The trigger event should be INSERT, UPDATE, or DELETE to indicate what kind of statement causes trigger activation.

The triggered statement, trigger_stmt, can be a simple SQL statement such as SET. You also can use the BEGINEND compound statement construct, in which case the triggered statement can be written using the statements that are available within the body of a stored function. (See "Stored Routine Syntax.")

The syntax OLD.col_name can be used to refer to columns in the old row to be deleted or updated in a DELETE or UPDATE trigger. Similarly, NEW.col_name can be used to refer to columns in the new row to be inserted or updated in an INSERT or UPDATE trigger. OLD and NEW are not case sensitive.

In a BEFORE trigger, you can change the values in the new row by using a SET statement:

SET NEW.col_name = value

To access a column with NEW, you must have the SELECT privilege. To change the value, you must have the UPDATE privilege.

Current limitations on triggers in MySQL: You cannot create a trigger on a TEMPOARY table or a view. In the body of a trigger, you cannot use statements that refer to tables, you cannot use the CALL statement, and you cannot begin or end transactions.

CREATE TRIGGER was introduced in MySQL 5.0.2. It requires the SUPER privilege.

CREATE USER

CREATE USER account [IDENTIFIED BY [PASSWORD] 'password']
  [, account [IDENTIFIED BY [PASSWORD] 'password'] ] ...

Creates one or more MySQL accounts. For each account, a record is created in the mysql.user table with no privileges. It is an error if the account already exists. Specify each account name in the same 'user_name'@'host_name' format that is used for the GRANT statement. An optional password may be given for each account.

This statement was introduced in MySQL 5.0.2. It requires the GRANT OPTION privilege for the mysql database.

CREATE VIEW

CREATE [OR REPLACE]
  [ALGORITHM = { MERGE | TEMPTABLE | UNDEFINED}]
  VIEW view_name [(col_list)] AS select_stmt
  [WITH [CASCADED | LOCAL] CHECK OPTION]

Creates a view. If a view with the same name already exists, an error occurs if the OR REPLACE clause is given (in which case, the new view replaces the old one).

The ALGORITHM clause determines how the view is processed. For MERGE, when you issue a statement that references the view, the view definition is merged into the statement. The resulting statement is executed. For TEMPTABLE, temporary tables are used during the course of executing the view. For UNDEFINED, the server chooses which algorithm to use. The default is UNDEFINED.

column_list, if present, provides names for the columns returned by the view. There must be one name for each column. If no column_list is given, the column names come from the SELECT statement in the view definition.

select_stmt is a SELECT statement that defines the view. It can refer to tables or other views.

The WITH CHECK OPTION clause applies to updatable views. It allows use of the view to insert or update only those rows in the underlying table for which the WHERE clause in the SELECT statement is true. The CASCADED and LOCAL keywords apply in the case that the view definition refers to other views. With CASCADED, checks cascade to underlying views. With LOCAL, checks are restricted to the current view. The default is CASCADED if neither is given.

The CREATE VIEW statement was introduced in MySQL 5.0.1. The WITH CHECK OPTION clause was introduced in MySQL 5.0.2.

DEALLOCATE PREPARE

{DEALLOCATE | DROP} PREPARE stmt_name

Deallocates a prepared statement named stmt_name that previously was prepared with PREPARE. After the statement has been deallocated, you should not attempt to execute it again.

DEALLOCATE PREPARE was introduced in MySQL 4.1.3.

DELETE

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
  [WHERE where_expr] [ORDER BY ...] [LIMIT n]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name [, tbl_name] ...
  FROM tbl_name [, tbl_name] ...
  [WHERE where_expr]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] 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 second and third forms can delete rows from multiple tables, or delete rows based on conditions that involve multiple tables.

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.

For MyISAM tables, specifying QUICK may make the statement quicker; the MyISAM storage engine will not perform its usual index tree leaf merging.

If the IGNORE modifier is given, errors that occur while records are being deleted are ignored. These errors generate warnings instead. IGNORE was introduced in MySQL 4.1.1.

If the LIMIT clause is given, the value n specifies the maximum number of rows that will be deleted.

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 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 re-creating 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. Names in the list of tables from which rows are to be deleted may 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;

Multiple-table DELETE statements do not allow ORDER BY or LIMIT clauses. Also, the WHERE clause cannot include a subquery that selects rows from a table from which rows are deleted.

DESCRIBE

{DESCRIBE | DESC} tbl_name [col_name | 'pattern']

{DESCRIBE | DESC} select_stmt

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

DROP DATABASE

DROP DATABASE [IF EXISTS] db_name

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 DROP privilege for it. The IF EXISTS clause may be specified to suppress the error that normally results if the database does not exist. In this case, a warning is generated instead.

A database is represented by a directory under the data directory. The server deletes only files and directories that it can identify as having been created by itself (for example, .frm files or RAID directories). It does not delete other files and directories. If you have put non-table files in that directory, those files are not deleted by the DROP DATABASE statement. This results in failure to remove the database directory and DROP DATABASE fails. In that case, the database will continue to be listed by SHOW DATABASES. To correct this problem, you can manually remove the database directory and any files within it.

A successful DROP DATABASE returns a row count that indicates the number of tables dropped. (This actually is the number of .frm files removed, which amounts to the same thing.)

DROP FUNCTION

DROP FUNCTION function_name

Removes a user-defined function that was previously loaded with CREATE FUNCTION. DROP FUNCTION also can be used to remove a stored function. See "Stored Routine Syntax."

DROP INDEX

DROP INDEX index_name ON tbl_name

Removes the index index_name from the table tbl_name. This statement is handled internally as an ALTER TABLE DROP INDEX statement. See the entry for ALTER TABLE for details. To use DROP INDEX to drop a PRIMARY KEY, the index name is PRIMARY, which must be named as a delimited identifier:

DROP INDEX `PRIMARY` ON tbl_name;

DROP TABLE

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

Removes the named table or tables from the database they belong to. If the TEMPORARY keyword is given, drops only TEMPORARY tables.

The IF EXISTS clause may be specified to suppress the error that normally results if the table does not exist. In this case, a warning is generated instead.

The RESTRICT and CASCADE keywords are parsed but ignored and have no effect.

DROP TRIGGER

DROP TRIGGER tbl_name.trigger_name

Removes a trigger that is associated with the named table. It is necessary to include the table name.

DROP TRIGGER was introduced in MySQL 5.0.2. It requires the SUPER privilege.

DROP USER

DROP USER account [, account] ...

Prior to MySQL 5.0.2, the DROP USER statement removes accounts from which all privileges have been revoked. Such an account exists in the grant tables only as a record in the user table. This statement removes that record.

From MySQL 5.0.2 on, DROP USER revokes the account's privileges and removes the account record.

Specify each account name in the same 'user_name'@'host_name ' format that is used for the GRANT statement. It is an error if the account does not exist.

DROP USER was introduced in MySQL 4.1.1. It requires the GRANT OPTION privilege for the mysql database.

DROP VIEW

DROP VIEW [IF EXISTS] view_name [, view_name] ...
  [RESTRICT | CASCADE]

Removes the named view or views from the database to which they belong. You must have the DROP privilege for the view.

The IF EXISTS clause may be specified to suppress the error that normally results if the view does not exist. In this case, a warning is generated instead.

The RESTRICT and CASCADE keywords are parsed but ignored and have no effect.

DROP VIEW was introduced in MySQL 5.0.1.

EXECUTE

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

Executes a prepared statement named stmt_name that was previously prepared with PREPARE. The USING clause must be given if the prepared statement contains any placeholder markers. The clause should provide a comma-separated list of user variables that contains the values for successive placeholders.

EXECUTE was introduced in MySQL 4.1.3.

EXPLAIN

EXPLAIN tbl_name [col_name | 'pattern']

EXPLAIN select_stmt

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, grade_event
WHERE score.event_id = grade_event.event_id AND grade_event.event_id = 14;

Output from EXPLAIN consists of one or more rows containing the following columns:

  • id

    The ID number for the SELECT to which this output row applies. There can be more than one SELECT if the statement includes subqueries or is a UNION.

  • select_type

    The type of the SELECT to which this output row applies:

    Type

    Meaning

    SIMPLE

    A SELECT with no UNION or subquery parts

    PRIMARY

    The outermost or leftmost SELECT

    UNION

    The second or later SELECT in a UNION

    DEPENDENT UNION

    Like UNION, but dependent on an outer query

    UNION RESULT

    The result of a UNION

    SUBQUERY

    The first SELECT in a subquery

    DEPENDENT SUBQUERY

    Like SUBQUERY, but dependent on an outer query

    DERIVED

    A subquery in the FROM clause


  • 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, ref_or_null, index_merge, unique_subquery, index_subquery, 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. (There might be several keys listed here if MySQL uses an index_merge join type, because that optimization uses several indexes to process the query.) A value of NULL indicates that no index will be used.

  • key_len

    How much of the index will be used. This will 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

    Other information about the execution plan. The value can be blank or contain one or more values such as those following:

    • Using filesort: Index values need to be written to a file and sorted so that the associated rows can be retrieved in sorted order.

    • Using index: MySQL can retrieve information for the table using only information in the index without examining the data file.

    • Using temporary: A temporary table must be created.

    • Using where: Information in the WHERE clause of the SELECT statement is used for index processing.

    Other values might appear in this field that are not listed here; see the MySQL Reference Manual for the current set of Extra values.

If you use EXPLAIN with a statement that includes a subquery, the subquery has to be executed. This occurs because the optimizer must know what the subquery returns so that it can determine the execution plan for the outer query.

FLUSH

FLUSH [LOCAL | NO_WRITE_TO_BINLOG] 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.

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

  • SLAVE

    This has been renamed to RESET SLAVE, which should be used instead.

  • STATUS

    Reinitializes the server status variables.

  • {TABLE | TABLES} [tbl_name [, tbl_name] ...]

    Without any table names, closes all open tables in the table cache. You can specify an optional comma-separated list of one or more table names to flush specific tables rather than the entire table cache.

    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. The disadvantage of doing this, from the client point of view, is that the period during which updates are disallowed is greater.

  • USER_RESOURCES

    Reset the counters for account resource management limits (such as MAX_QUERIES_PER_HOUR). Accounts that have reached their limits can once again proceed in their activities.

If binary logging is enabled, MySQL writes the FLUSH statement to the binary log unless the LOCAL or NO_WRITE_TO_BINLOG option is given.

The FLUSH statement requires the RELOAD privilege.

GRANT

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ] ...
  ON {*.* | * | db_name.* | db_name.tbl_name | tbl_name}
  TO account [IDENTIFIED BY [PASSWORD] 'password']
    [, account [IDENTIFIED BY [PASSWORD] 'password'] ] ...
  [REQUIRE security_options]
  [WITH grant_or_resource_options]

The GRANT statement grants access privileges to one or more MySQL users. To use this statement, you must have the GRANT OPTION privilege and you must possess the privileges that you are trying to grant.

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

ALTER ROUTINE

Alter or drop stored procedures and functions

CREATE

Create databases and tables

CREATE ROUTINE

Create stored procedures and functions

CREATE TEMPORARY TABLES

Create temporary tables using the TEMPORARY keyword

CREATE VIEW

Create views

DELETE

Delete rows from tables

DROP

Drop (remove) databases and tables

EXECUTE

Execute stored procedures and functions

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 rows from tables

SHOW DATABASES

See all database names with SHOW DATABASES

SHOW VIEW

See view definitions with SHOW CREATE VIEW

SHUTDOWN

Shut down the server

SUPER

Kill threads and perform other supervisory operations

UPDATE

Modify table rows

ALL, ALL PRIVILEGES

All operations (except GRANT)

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 PROCESS and SUPER privileges allow you to view and kill, respectively, threads that belong to any account, not just your own.

The CREATE VIEW and SHOW VIEW privileges were introduced in MySQL 5.0.1. ALTER ROUTINE and CREATE ROUTINE were introduced in MySQL 5.0.3; they apply only to stored routines, not to user-defined functions (UDFs). MySQL 5.0.3 also is the version in which the EXECUTE privilege became operational.

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 you use ALL as a privilege specifier, it grants only those privileges that are available at the level for which you are granting privileges. For example, RELOAD is only available as a global privilege, so it would be granted by GRANT ALL if you specify ON *.*, but not if you specify ON db_name.*. In the latter case, only these privileges that apply to databases would be granted. ALL also can be used only when granting global, database, or table privileges. For column privileges, you must list each privilege to be granted.

When a table is named in the ON clause, privileges may 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 may be granted on a column-specific basis.)

To grant table or column privileges, the table must already exist.

USAGE means "no privileges." It should be used only at the global level.

GRANT OPTION applies to all privileges granted at a given level. For example, you cannot grant an account SELECT and INSERT for a given database, but make just one of them grantable by that account.

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 may 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 are values that can be used as unquoted identifiers. 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 may be a name or an empty string (''); the latter specifies an anonymous user. host_name may 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. 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.

A user_name specified alone with no hostname is equivalent to 'user_name'@'%'.

Database, table, and column names, if quoted, must be quoted using identifier quoting characters. Usernames and hostnames can be quoted using identifier or string quoting characters. For example:

GRANT INSERT (`mycol`) ON `test`.`t` TO 'myuser'@'localhost';

The IDENTIFIED BY clause, if given, assigns a password to the user. Normally, you omit the PASSWORD keyword and specify the literal value of the password in plain text. Do not use the PASSWORD() function, in contrast to the way passwords are specified for the SET PASSWORD statement. In the special case that you want to specify the password hash value in the format returned by PASSWORD(), precede the value with the keyword PASSWORD. (This might be the case if you are using the output of SHOW GRANTS to re-create the privileges for an account. SHOW GRANTS displays the hashed password value, not the literal password.) 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 may 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. It can also be used to place limits on the account's resource consumption. The allowable options are shown in the following list. You may 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 statements per hour.

  • MAX_UPDATES_PER_HOUR n

    The account is allowed to issue n statements that modify data per hour.

  • MAX_USER_CONNECTIONS n

    The account is allowed to make a maximum of n simultaneous connections to the server. This limit was introduced in MySQL 5.0.3.

For MAX_CONNECTIONS_PER_HOUR, MAX_QUERIES_PER_HOUR, and MAX_UPDATES_PER_HOUR, a value of 0 means "no limit." For MAX_USER_CONNECTIONS, a value of 0 means that the value of the max_user_connections system variable applies.

The following statements demonstrate some ways in which the GRANT statement can be used. See Chapter 11 for other examples. See Chapter 12, "MySQL and 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 omitting the hostname part of an account identifier is equivalent to specifying % as the hostname:

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 statements per hour, of which at most 10 may 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 storage engines that bypasses the optimizer and accesses table contents directly. To access a table through the HANDLER interface, first use HANDLEROPEN to open it. The table remains available for use until you issue a HANDLERCLOSE statement to close it explicitly or until or the connection terminates. While the table is open, use HANDLERREAD 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 table.

INSERT

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

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

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

Inserts rows into an existing table tbl_name and returns the number of rows inserted. The INTO keyword is optional. The DELAYED, LOW_PRIORITY, and HIGH_PRIORITY options affect execution scheduling:

  • DELAYED causes the rows to be placed into a queue for later insertion, and the statement returns immediately so that the client may 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 work for MyISAM and MEMORY tables.

  • LOW_PRIORITY causes the statement to be deferred until no clients are reading from the table.

  • HIGH_PRIORITY causes the effect of the --low-priority-updates server option to be canceled for a single statement. (It the server is started with this option, it lowers the priority of INSERT and other update statements.) HIGH_PRIORITY also prevents the INSERT from being performed concurrently with SELECT statements if it otherwise would be.

If inserting a record would result in a duplicate key value in a unique index, INSERT terminates in error and no more rows are inserted. Adding IGNORE causes such records not to be inserted and no error occurs. In strict mode, IGNORE also causes data conversion errors that otherwise would terminate the statement to be treated as non-fatal warnings. Columns are set to the nearest legal value in this case.

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. Multiple value lists may be specified, allowing multiple rows to be inserted using a single INSERT statement. The column_list and VALUES() list may 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();

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 using a subquery from the same table into which you are inserting them.

The third form of INSERT 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;

The word DEFAULT may be used in a VALUES() list or SET clause to set a column to its default value explicitly without knowing what the default value is. More generally, to refer to a column's default value in expressions, you can use DEFAULT(col_name). The following statement sets the column i to 0 if its default value is NULL and to 1 otherwise:

INSERT INTO t SET i = IF(DEFAULT(i) IS NULL,1,0);

In strict mode, it is an error to omit a column or to specify its value by using DEFAULT if the column has no DEFAULT clause in its definition.

The ON DUPLICATE KEY UPDATE clause applies for rows that would result in a duplicate-key violation for a unique-valued index. With this clause, the INSERT is converted to an UPDATE that modifies the column of the existing row using the column assignments following the UPDATE keyword. If an update did occur, the rows-affected count returned by INSERT is 2 rather than 1.

KILL

KILL [CONNECTION | QUERY] thread_id

Kills the server thread with the given thread_id. You must have the SUPER privilege 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.

The CONNECTION and QUERY options were introduced in MySQL 5.0.0. CONNECTION has the same effect as no option: The thread with the given ID is terminated. QUERY terminates any statement that the thread is executing, but not the thread itself.

LOAD DATA

LOAD DATA [LOW_PRIORITY | CONCURRENT ] [LOCAL] INFILE 'file_name'
  [IGNORE | REPLACE]
  INTO TABLE tbl_name
  [field_options] [line_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.

LOAD DATA returns an information string that has the following format:

Records: n  Deleted: n  Skipped: n  Warnings: n

If the warning count is non-zero, use the SHOW WARNINGS statement to see what the problems were.

LOW_PRIORITY causes the statement to be deferred until no clients are reading from the table.

CONCURRENT is used only for MyISAM tables. If the table has no holes in the middle, new rows are loaded at the end of the table. In this case, other clients can retrieve from the table concurrently while rows are being loaded.

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 be 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 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 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, filename interpretation is 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 may 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 field_options and line_options clauses indicates the format of the data. (The options available in these clauses also apply to the corresponding clauses of the SELECTINTO OUTFILE statement.) The syntax for the two clauses is as follows:

field_options:
  [FIELDS
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char' ] ]
line_options:
  [LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string'] ]

The 'string' and 'char' values may include the escape sequences in the following table to indicate special characters. The sequences should be given in the lettercase shown.

Sequence

Meaning

\0

NUL (zero-valued byte)

\b

Backspace

\n

Newline (linefeed)

\r

Carriage return

\s

Space

\t

Tab

\'

Single quote

\"

Double quote

\\

Backslash


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. If multiple clauses are present, they may 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 may appear in any order. FIELDS must precede LINES if both are given.

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

  • The STARTING BY value specifies a character or characters that begin lines.

  • The TERMINATED BY value specifies a character or characters that signify the ends of lines.

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 read 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 may 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 '"';

Hexadecimal notation is useful for specifying arbitrary control characters. 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 replicates 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 the slave server may have been started with. The statement itself also has a number of constraints that must be satisfied:

  • All tables to be replicated must be MyISAM tables.

  • You must connect to the slave server using an account that has the SUPER privilege.

  • The account that the slave server uses to connect to the master server must have the REPLICATION SLAVE privilege so that it can connect to the master as a slave, and it also should have the RELOAD and SUPER privileges to be able to perform the transfer. To grant these privileges when you set up the account, you can use a statement like this:

    GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.*
    TO 'slave_user'@'slave_host'
    IDENTIFIED BY 'slave_pass';
    

    Note that this account must be created on the master server; it differs from the one just mentioned in the previous item, which is an account on the slave.

  • The operation performed by LOAD DATA FROM MASTER acquires a global read lock. This blocks all updates on the master server for the duration of the transfer to the slave.

  • LOAD DATA FROM MASTER does not copy the tables in the mysql database. If you want the slave server to begin with the same set of MySQL accounts as the master, you must copy the tables in this database to the slave by some other means.

If the preceding conditions are acceptable, this statement provides a convenient way to initialize a slave server.

LOAD TABLEFROM MASTER transfers a copy of the single named table from the master to the slave. This is used primarily for replication debugging.

LOAD INDEX INTO CACHE

LOAD INDEX INTO CACHE
  tbl_name [[INDEX | KEY] (index_name [, index_name] ...)
    [IGNORE LEAVES]]
  [, tbl_name [[INDEX | KEY] (index_name [, index_name] ...)]
    [IGNORE LEAVES]]

Loads indexes from each named MyISAM table into the key cache to which the table is assigned. This is the default key cache unless the table has been assigned to another cache with the CACHE INDEX statement. By default, all index blocks are loaded. The IGNORE LEAVES clause causes only non-leaf blocks in the index tree to be loaded.

As with the CACHE INDEX statement, the syntax for LOAD INDEX INTO CACHE allows individual indexes to be specified, but the current implementation is such that all indexes for a table are loaded.

You must have the INDEX privilege for each table named in the statement.

LOAD INDEX INTO CACHE produces output in the format described under the entry for CHECK TABLE.

MyISAM key cache management is discussed further in Chapter 11.

The LOAD INDEX INTO statement was introduced in MySQL 4.1.1.

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 the middle resulting from deleted or updated records. READ LOCAL allows you to lock a table explicitly but still allow other clients to perform concurrent inserts. (If the table does have holes in it, the lock is treated as a regular READ lock.)

  • WRITE

    Acquires 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. A request for this type of lock is not granted until there are no more readers.

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. While you have acquired locks with LOCK TABLE, you cannot refer to any not-locked tables.

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 refer to a table multiple times in a query, you must obtain a lock for each instance of the table, locking aliases as necessary. You must request all the locks in the same statement.

LOCK TABLES student READ, score WRITE, grade_event READ;
LOCK TABLE member READ;
LOCK TABLES t AS t1 READ, t AS t2 READ;

Table locks acquired with LOCK TABLE are released implicitly if you start a transaction with START TRANSACTION.

OPTIMIZE TABLE

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG]
  {TABLE | TABLES} tbl_name [, tbl_name] ...

DELETE, REPLACE, and UPDATE statements may 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 for MyISAM tables:

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

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, the server does the work, and takes care of making sure that other clients do not modify the table while it's being optimized.

For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE to rebuild the index as of MySQL 4.1.3, and to ANALYZE TABLE before that.

For BDB tables, OPTIMIZE TABLE is mapped to ANALYZE TABLE.

OPTIMIZE TABLE requires SELECT and INSERT privileges on each table.

If binary logging is enabled, MySQL writes the OPTIMIZE TABLE statement to the binary log unless the LOCAL or NO_WRITE_TO_BINLOG option is given.

OPTIMIZE TABLE produces output in the format described under the entry for CHECK TABLE.

PREPARE

PREPARE stmt_name FROM {'string' | @var_name}

Prepares a statement and assigns it the name stmt_name. The statement can be executed later with EXECUTE and deallocated with DEALLOCATE PREPARE. If there was already a previously prepared statement that has the given name, the previous statement is deallocated before the new statement is prepared. Statement names are not case sensitive.

The statement to be prepared can be given either as a string literal or a user variable. Allowable statements to be used with PREPARE are CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most variations of SHOW. Other statements cannot be prepared, including PREPARE, EXECUTE, and DEALLOCATE PREPARE.

The prepared statement can contain '?' characters that serve as placeholder markers. Later when you execute the statement, you supply data values to be bound to the placeholders. This allows you to parameterize the statement so that you can use the same prepared statement but execute it with different values from execution to execution.

PREPARE was introduced in MySQL 4.1.3.

PREPARE, EXECUTE, and DEALLOCATE provide an SQL-level interface to prepared statements. They are not the same as or as efficient as the binary API for prepared statements that is discussed in Chapter 6, "Writing MySQL Programs Using C," and Appendix G, "C API Reference."

PURGE MASTER LOGS

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

Deletes all the binary logs on the server that were generated earlier than the named log file or before the given date, and resets the binary 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.

The following statement removes binlog.000001 through binlog.000009 (or whichever of them exist), and causes binlog.000010 to become the first of the remaining log files:

PURGE MASTER LOGS TO 'binlog.000010';

RENAME TABLE

RENAME {TABLE | TABLES} tbl_name TO new_tbl_name
  [, tbl_name TO new_tbl_name] ...

Renames one or more tables. RENAME TABLE is similar to ALTER TABLERENAME, 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.

If you rename an InnoDB table on which other tables depend for foreign key relationships, InnoDB adjusts the dependencies to point to the renamed table.

If you rename a MyISAM table that is part of a MERGE table, you must redefine the MERGE table accordingly.

RENAME TABLE cannot be used for TEMPORARY tables.

RENAME USER

RENAME USER from_account TO to_account
  [, from_account TO to_account] ...

Renames one or more MySQL accounts. Each from_account is renamed to the corresponding to_account. An error occurs if from_account does not exist or if to_account already exists. Specify each account name in the same 'user_name'@'host_name' format that is used for the GRANT statement.

This statement was introduced in MySQL 5.0.2. It requires the GRANT OPTION privilege for the mysql database.

REPAIR TABLE

REPAIR [LOCAL | NO_WRITE_TO_BINLOG]
  {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 re-creates 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

    Use the table's .frm file to determine how to interpret the contents of the data file, and then use 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.

If binary logging is enabled, MySQL writes the REPAIR TABLE statement to the binary log unless the LOCAL or NO_WRITE_TO_BINLOG option is given.

REPAIR TABLE produces output in the format described under the entry for CHECK TABLE.

REPLACE

REPLACE [LOW_PRIORITY | DELAYED] [INTO]
  tbl_name [(column_list)]
  VALUES (expr [, expr] ...) [, (...)] ...

REPLACE [LOW_PRIORITY | DELAYED] [INTO]
  tbl_name [(column_list)]
  {SELECT ... | (SELECT ...)}

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

The basic action of REPLACE statement is like that of 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. Also, REPLACE has no support for ON DUPLICATE KEY UPDATE.

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.

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 logs for a replication master server, create a new log file with the numbering sequence set to 000001, and reset the binary 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 remove any existing relay log files and begin a new relay log, and to forget its replication coordinates (that is, its current replication binary log filename and position within that file).

RESET requires the RELOAD privilege.

RESTORE TABLE

RESTORE {TABLE | TABLES} tbl_name [, tbl_name] ... FROM 'dir_name'

RESTORE TABLE is the complement to BACKUP TABLE. It 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 FILE privilege as well as the INSERT privilege for the tables to be restored. The restore operation for each table uses only the table format and data files (the .frm and .MYD files). Indexes are rebuilt using the information contained in those two files.

REVOKE

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

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

The REVOKE statement revokes privileges from the named account or accounts.

In the first syntax, 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. To use this statement, you must have the GRANT OPTION privilege and you must possess the privileges that you are trying to revoke.

The second syntax has a fixed privilege list and no ON clause. It revokes all privileges held by each of the named accounts. The second syntax was introduced in MySQL 4.1.2. It requires the GRANT OPTION privilege for the mysql database.

REVOKE does not remove an account's record from the user grant table. This means that the account still can be used to connect to the MySQL server even when all its privileges have been revoked. To remove the account entirely, use the DROP USER statement (or manually delete the account record from the user table).

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. To revoke that privilege as well, you must do so explicitly:

REVOKE GRANT OPTION ON menagerie.pet FROM ''@'localhost';

Revoke all privileges held at all levels by superduper@localhost:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'superduper'@'localhost';

ROLLBACK

ROLLBACK [TO SAVEPOINT savepoint_name]

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 storage engines. (For non-transactional storage engines, statements are committed as they are executed and thus cannot be rolled back.)

If the TO SAVEPOINT clause is given, the statement rolls back the current transaction only to the named savepoint. This clause was introduced in MySQL 4.1.1 and works for InnoDB transactions.

ROLLBACK does nothing if autocommit mode has not been disabled with START TRANSACTION or by setting the autocommit variable to 0.

SAVEPOINT

SAVEPOINT savepoint_name

Creates a transaction savepoint with the given name. Any savepoint that exists with the given name is deleted. Statements executed later within the current transaction can be rolled back to the savepoint with the ROLLBACK TO SAVEPOINT statement.

SAVEPOINT was introduced in MySQL 4.1.1.

SELECT

SELECT
  [select_options]
  select_expr [, select_expr] ...
  [
      INTO OUTFILE 'file_name' [field_options] [line_options]
    | INTO DUMPFILE 'file_name'
    | INTO var_name [, var_name ] ...
  ]
  [FROM tbl_list
  [WHERE where_expr]
  [GROUP BY {col_name | position | expr} [ASC | DESC], ... [WITH ROLLUP]]
  [HAVING where_expr]
  [ORDER BY {col_name | position | expr} [ASC | DESC], ...]
  [LIMIT [skip_count,] show_count]
  [PROCEDURE procedure_name([param_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 at least one select_expr, it's also possible to write statements that simply evaluate expressions:

SELECT 'one plus one =', 1+1;

For compatibility with database systems that require a FROM clause, MySQL recognizes the DUAL pseudo-table:

SELECT 'one plus one =', 1+1 FROM DUAL;

The select_options clause, if present, consists of one or more of the following options separated by whitespace:

  • ALL
    DISTINCT
    DISTINCTROW
    

    These keywords control whether 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.

  • SQL_BUFFER_RESULT

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

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

  • STRAIGHT_JOIN

    Forces tables to be joined in the order named in the FROM clause. This option may be useful if you believe that the optimizer is not making the best choice.

The select_expr expressions list the output columns to be returned. Multiple columns should be separated by commas. Columns may be references to table columns or expressions (including scalar subqueries). Any column may be assigned a column alias using AS alias_name syntax. The alias then becomes the column name in the output and may also be referred to in GROUP BY, ORDER BY, and HAVING clauses. However, you cannot refer to column aliases 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 may be written into a file file_name using an INTO OUTFILE 'file_name' clause. The syntax of the field_options and line_options clauses is the same as for the corresponding clauses 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 such as an image or other binary data to a file.

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 with a world-accessible mode. Its ownership will be set to the account used to run the server.

The results of a SELECT may be stored into a set of variables. Each variable can be either a user-defined variables of the form @var_name, or, within a stored routine, a local variable for the routine. 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 | INNER JOIN} tbl_name
  tbl_list {[CROSS] JOIN | INNER JOIN} tbl_name ON conditional_expr
  tbl_list {[CROSS] JOIN | 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 may 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 | IGNORE | FORCE} INDEX (index_list)]

Tables may be assigned aliases in the FROM clause using either tbl_name alias_name or tbl_name AS alias_name syntax. An alias provides an alternative name by which to refer to the table columns elsewhere in the query.

A table reference in the FROM clause also can be a subquery within parentheses, as long as you provide a table alias:

SELECT * FROM (SELECT 1) AS t;

The USE INDEX, IGNORE INDEX, and FORCE INDEX clauses provide index hints to the optimizer. This may be helpful in cases where the optimizer doesn't make the correct choice about which index to use in a join. FORCE INDEX forces the optimizer to use the index. (The word KEY can be used as a synonym for INDEX in these clauses.) 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, CROSS JOIN, or INNER JOIN is equivalent to using a comma if there is no ON or USING clause. STRAIGHT_JOIN is similar, but forces the optimizer to join the tables in the order that the tables are named. It may be used if you believe that the optimizer is not making the best choice.

  • Unlike the comma operator or STRAIGHT_JOIN, joins performed with JOIN, CROSS JOIN, or INNER JOIN can be specified with an ON or USING() clause to constrain matches between tables. 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 may 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 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. The ON or USING() clause following the table names is given as for JOIN, CROSS JOIN, or INNER JOIN. 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.

The WHERE clause specifies an expression that is applied to rows selected from the tables named in the FROM clause. Rows that do not satisfy the criteria given by the expression are rejected. The result set may be further limited by HAVING and LIMIT clauses. Column aliases may not be referred to in the WHERE clause.

The GROUP BY and ORDER BY clauses have similar syntax. GROUP BY column_list is used to group rows of the result set based on the columns named in the list. This clause is used when you specify summary functions such as COUNT() or MAX() in a select_expr. ORDER BY column_list indicates that the result set should be sorted based on the named columns. In either clause, columns may be referred to by column names or aliases, or by position within the list of select_expr expressions. Column positions are unsigned integers beginning with 1. (However, use of column positions is deprecated.) You can also use expressions to group or sort by expression results. For example, ORDER BY RAND() sorts rows in random order. Expressions can refer to summary (aggregate) functions in GROUP BY clauses but not in ORDER BY clauses. (As a workaround, select the function in the output column list, provide an alias for it, and refer to the alias in the ORDER BY clause.)

In a GROUP BY or ORDER BY clause, you can follow any column in the column list with ASC or DESC to indicate that the column should be sorted in ascending or descending order. The default for each column is ascending if neither keyword is present. Sort order indicators are allowed in GROUP BY clauses because, in MySQL, GROUP BY not only groups rows, it sorts the results. The output order resulting from GROUP BY is overridden by any ORDER BY clause that is present. To prevent the implicit ordering that results from GROUP BY (and thus not incur the sorting overhead), use ORDER BY NULL.

WITH ROLLUP can be used at the end of a GROUP BY clause. It causes the output to include summary rows for higher level combinations of the grouped columns, plus an overall summary at the end.

The HAVING clause specifies a secondary expression that is used to limit rows after they have satisfied the conditions named by the WHERE clause and after they have been grouped according to any GROUP BY 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.

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 show_count returns the first show_count rows. LIMIT skip_count, show_count skips the first skip_count rows, and then returns the next show_count rows.

An alternative LIMIT syntax is LIMIT show_count OFFSET skip_count.

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 optional parameter list, param_list, is a comma-separated list of values to pass to the procedure. 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 rows that are examined during query execution. The locks remain in force until the current transaction is committed or rolled back. These locking clauses can be useful in multiple-statement transactions. If you use FOR UPDATE with a table for which the storage engine uses page-level or row-level locks (BDB or InnoDB), the examined 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. Note that if the query optimizer finds no index to use for examining rows, it must scan (and thus lock) all rows in the table.

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

Subquery support. A subquery is one SELECT nested within another. Several forms of subquery are available; examples can be found in Chapter 2. Subqueries can also be used in the WHERE clause of DELETE and UPDATE statements or with INSERT and REPLACE statements. However, currently you cannot use a subquery to select from a table that you are modifying.

SET

SET [OPTION] assignment [, assignment ] ...

assignment: var_name = expr

The SET statement is used to assign values to system variables, user-defined variables, or local variables for stored routines. Information about system and user-defined variables is provided in Appendix D, "System, Status, and User Variable Reference." Declaration syntax for stored routine local variables is described in "Stored Routine Syntax." SET also is used for a few miscellaneous settings that are described later in this entry. The OPTION keyword in the SET statement is allowed but unnecessary.

Other statements that begin with SET (SET PASSWORD and SET TRANSACTION) are described in separate sections later in this appendix.

When SET is used to make variable assignments, var_name in each assignment is the variable to be assigned a value and expr is the expression that indicates the value to assign to the variable. The assignment operator in a SET statement can be either = or :=.

SET can be used to assign values to user-defined variables, which are named using @var_name syntax:

SET @day = CURDATE(), @time = CURTIME();

SET also can assign values to system variables, many of which are dynamic and can be changed while the server is running. Dynamic system variables exist at two levels. Global system variables are server-wide and affect all clients. Session system variables (also called local system variables) are specific to a given client connection only. For variables that exist at both levels, a given client's session variables are initialized from the values of the corresponding global variables when the client connects. It is necessary to have the SUPER privilege to modify a global variable, but any client can modify its own session variables.

The syntax for setting system variables has several forms. To set a global variable (for example, the global sql_mode value), use a statement having either of the following forms:

SET GLOBAL sql_mode = 'ANSI_QUOTES';
SET @@GLOBAL.sql_mode = 'ANSI_QUOTES';

To set a session variable, substitute the word SESSION for GLOBAL:

SET SESSION sql_mode = 'ANSI_QUOTES';
SET @@SESSION.sql_mode = 'ANSI_QUOTES';

You can also use LOCAL as a synonym for SESSION:

SET LOCAL sql_mode = 'ANSI_QUOTES';
SET @@LOCAL.sql_mode = 'ANSI_QUOTES';

If none of GLOBAL, SESSION, or LOCAL are present, the SET statement modifies the session-level variable:

SET sql_mode = 'ANSI_QUOTES';
SET @@sql_mode = 'ANSI_QUOTES';

To check the value of system variables, use the SHOW VARIABLES statement. You can also retrieve individual system variable values by using SELECT:

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode, @@LOCAL.sql_mode;

The use of system variables is discussed further in "Setting and Checking System Variable Values," in Chapter 11.

The following list describes miscellaneous settings that can be controlled with SET.

  • SET CHARACTER SET {charset | DEFAULT}

    Sets the character_set_client and character_set_results session variables to the named character set, and sets the character_set_connection session variable to the value of character_set_database. These variables affect conversion of character data sent to and from the server. Currently, a charset value of ucs2 does not work.

    SET CHARACTER SET DEFAULT restores the default character set mapping.

  • SET NAMES {charset | 'charset' | DEFAULT}

    Sets the character_set_client, character_set_connection, and character_set_results session variables to the named character set. These variables affect conversion of character data sent to and from the server. Currently, a charset value of ucs2 does not work. collation_connection is set to the default collation for character_set_connection.

    SET NAMES DEFAULT restores the default character set mapping.

SET PASSWORD

SET PASSWORD [FOR account] = PASSWORD('pass_val')
SET PASSWORD [FOR account] = OLD_PASSWORD('pass_val')
SET PASSWORD [FOR account] = 'encrypted_pass_val'

SET PASSWORD changes the password for a MySQL account. You can always change your own password, unless you have connected as an anonymous user. To change the password for another account, you must have the UPDATE privilege for the mysql database.

With no FOR clause, the statement sets the password for the current account. With a FOR clause, it sets the password for the named account, which should be given in the same 'user_name'@'host_name' format that is used for the GRANT statement.

The password value, 'pass_val' should be encrypted using PASSWORD() for standard encryption or OLD_PASSWORD() for the older (pre-MySQL 4.1) encryption. If you use neither function, 'encrypted_pass_val' should be given as an already-encrypted password string.

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

SET TRANSACTION

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL level

This statement sets the isolation level for transaction processing:

  • With the GLOBAL option, it sets the global (server-wide) isolation level, which becomes the default level for all clients that connect thereafter.

  • With the SESSION option, it sets the session (client-specific) isolation level, which becomes the level for subsequent transactions within the current session.

  • With neither option, it sets the isolation level only for the next transaction within the current session.

The SUPER privilege is required to set the global isolation level. Any client can change its own session or next-transaction isolation level.

The transaction level indicated by level should be one of the following values:

  • READ UNCOMMITTED

    A transaction can see row modifications made by other transactions even if they have not been committed.

  • READ COMMITTED

    A transaction can see row modifications made by other transactions only if they were committed before the transaction began.

  • REPEATABLE READ

    If a transaction performs a given SELECT twice, the result is repeatable. That is, it gets the same result each time, even if other transactions have changed or inserted rows in the meantime.

  • SERIALIZABLE

    This isolation level is similar to REPEATABLE READ but isolates transactions more completely: Rows selected by one transaction cannot be modified by other transactions until the first transaction completes.

The SET TRANSACTION statement applies only to the InnoDB storage engine, which by default runs at the REPEATABLE READ level. The BDB storage engine always runs at the SERIALIZABLE level. Non-transactional storage engines do not have isolation levels.

"Transaction Isolation," in Chapter 2, further discusses transaction isolation and isolation levels.

SHOW

SHOW BINLOG EVENTS
SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMN TYPES
SHOW COLUMNS FROM tbl_name
SHOW CREATE DATABASE db_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE VIEW view_name
SHOW DATABASES
SHOW ENGINE engine_name {LOGS | STATUS}
SHOW ENGINES
SHOW ERRORS
SHOW FUNCTION STATUS
SHOW GRANTS
SHOW INDEX FROM tbl_name
SHOW INNODB STATUS
SHOW LOGS
SHOW {MASTER | BINARY} LOGS
SHOW MASTER STATUS
SHOW OPEN TABLES
SHOW PRIVILEGES
SHOW PROCEDURE STATUS
SHOW PROCESSLIST
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW VARIABLES
SHOW WARNINGS

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. In each of these statements where FROM is used to specify a table or database name, IN can be used as a synonym.

Some forms allow an optional LIKE 'pattern' clause to limit output to values that match the pattern. 'pattern' is interpreted as an SQL pattern and may contain the '%' or '_' wildcard characters.

The following sections describe most of the SHOW statements in the preceding list. Descriptions for the SHOW CREATE FUNCTION, SHOW CREATE PROCEDURE, SHOW FUNCTION STATUS, and SHOW PROCEDURE STATUS statements can be found later in the chapter in "Stored Routine Syntax."

As of MySQL 5.0.2, INFORMATION_SCHEMA provides another way to obtain database metadata. In addition, several SHOW statements have been extended to allow a WHERE clause. See "Getting Information About Databases and Tables," in Chapter 2.

SHOW BINLOG EVENTS

SHOW BINLOG EVENTS [IN 'file_name'] [FROM position]
  [LIMIT [skip_count,] show_count]

This statement is used on replication master servers to display events in a binary log file. Events correspond roughly to SQL statements.

The output from this statement includes the following columns:

  • Log_name

    The binary log filename.

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

  • End_log_pos

    The position of the next byte after the event in the log file. (Before MySQL 5.0, this column is called Orig_log_pos and is the position of the beginning of the event.)

  • Info

    Event information, such as the statement text for a Query event.

This statement requires the REPLICATION SLAVE privilege.

SHOW CHARACTER SET

SHOW CHARACTER SET [LIKE 'pattern']

Displays a list of the character sets supported by the server. The LIKE clause may be included to display information only for character sets with names that match the given pattern.

The output from SHOW CHARACTER SET includes the following columns:

  • Charset

    The short character set name. This is the name that can be used in SQL statements.

  • Description

    A descriptive character set name.

  • Default collation

    The name of the default collation for the character set.

  • Maxlen

    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 COLLATION

SHOW COLLATION [LIKE 'pattern']

Displays a list of available collations for each character set. The LIKE clause may be included to display information only for collations with names that match the given pattern.

The output from SHOW COLLATION includes the following columns:

  • Collation

    The collation name.

  • Charset

    The name of the character set with this the collation is associated.

  • Id

    The collation ID number.

  • Default

    Yes if the collation is the default collation for its character set, blank otherwise.

  • Compiled

    Yes if the collation is compiled into the server, blank otherwise.

  • Sortlen

    A cost factor relating to the amount of memory that must be allocated for internal string conversion operations when the collation is used to sort values.

SHOW COLUMN TYPES

SHOW COLUMN TYPES

Displays information about the data types that may be used when creating MySQL tables.

The output from SHOW COLUMN TYPES includes the following columns:

  • Type

    The data 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 the type allows NULL values.

  • Auto_Increment

    Whether the type may be used for AUTO_INCREMENT sequences.

  • Unsigned

    Whether the type has the UNSIGNED attribute.

  • Zerofill

    Whether the type has the ZEROFILL attribute.

  • Searchable

    Whether the type is searchable.

  • Case_Sensitive

    Whether the type is case sensitive.

  • Default

    The type's default value. There may be more than one value listed, because the default value might depend on whether the column is defined to allow NULL values.

  • Comment

    A descriptive comment about the data type.

SHOW COLUMNS

SHOW [FULL] COLUMNS {FROM | IN} tbl_name
  [{FROM | IN} db_name] [LIKE 'pattern']

Displays the columns for the given table. SHOW FIELDS is a synonym for SHOW COLUMNS. With the FULL keyword, the statement displays the Collation, Privilege, and Comment output fields. The LIKE clause may be included to display information only for columns with names that match the given pattern.

To specify the database that contains the table, use a FROM db_name clause or write the table name in db_name.tbl_name format:

SHOW COLUMNS FROM president;
SHOW COLUMNS FROM president FROM sampdb;
SHOW COLUMNS FROM sampdb.president;

The output from SHOW COLUMNS provides the following types of information about each column in the table:

  • Field

    The column name.

  • Type

    The column data type. This may include type attributes following the type name.

  • Collation

    The character set collation name for columns that have a character set, NULL for other columns. This information is displayed only if you specify the FULL keyword.

  • Null

    YES if the column can contain NULL values. Otherwise, the column is NO as of MySQL 5.0.3 and blank before that.

  • Key

    Whether the column is indexed.

  • Default

    The column's default value.

  • Extra

    Extra information about the column. auto_increment is shown here for columns that have the AUTO_INCREMENT attribute, otherwise the value is blank.

  • Privileges

    The privileges that you hold for the column. This information is displayed only if you specify the FULL keyword.

  • Comment

    The value of any COMMENT attribute in the column definition. This information is displayed only if you specify the FULL keyword.

SHOW CREATE DATABASE

SHOW CREATE DATABASE [IF NOT EXISTS] db_name

Displays the CREATE DATABASE statement that creates the named database. If the SHOW statement includes an IF NOT EXISTS clause, the output statement does as well. As of MySQL 4.1.2, the value of the sql_quote_show_create session variable controls quoting of identifiers in the output statement.

SHOW CREATE TABLE

SHOW CREATE TABLE tbl_name

Displays the CREATE TABLE statement that creates the named table. The value of the sql_quote_show_create session variable controls quoting of identifiers in the output statement.

SHOW CREATE VIEW

SHOW CREATE VIEW view_name

Displays the CREATE VIEW statement that creates the named view. The value of the sql_quote_show_create session variable controls quoting of identifiers in the output statement.

SHOW CREATE VIEW was introduced in MySQL 5.0.1.

SHOW DATABASES

SHOW DATABASES [LIKE 'pattern']

Displays the databases available on the server host. The LIKE clause may be included to display information only for databases with names that match the given pattern.

If you don't have the SHOW DATABASES privilege, you'll see only the databases for which you have some kind of access privilege. If the server was started with the --skip-show-database option, you'll see all databases if you have the SHOW DATABASES privilege and none otherwise.

SHOW ENGINE ... { LOGS | STATUS}

SHOW ENGINE engine_name {LOGS | STATUS}

Displays information about the given storage engine's logs or status.

SHOW ENGINE BDB LOGS
SHOW ENGINE INNODB STATUS;

This statement is a generalized-syntax form that is intended to replace other storage engine-specific statements such as SHOW INNODB STATUS and SHOW BDB LOGS. This statement was introduced in MySQL 4.1.2.

SHOW ENGINES

SHOW [STORAGE] ENGINES

Displays the storage engines that the server knows about. For each engine, the output indicates the support level and provides a brief description of the engine characteristics.

The output from this statement includes the following columns:

  • Engine

    The storage engine name (MyISAM, InnoDB, and so forth).

  • 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 storage engine is the default engine. The default engine is always enabled.

  • Comment

    Descriptive text about the storage engine.

This statement was introduced in MySQL 4.1.0 as SHOW TABLE TYPES. In 4.1.2, the SHOW ENGINES syntax was introduced, which now is the preferred form.

SHOW ERRORS

SHOW ERRORS [LIMIT [skip_count,] show_count]
SHOW COUNT(*) ERRORS

SHOW ERRORS is like SHOW WARNINGS but displays only messages that have error severity. SHOW COUNT(*) ERRORS is like SHOW COUNT(*) WARNINGS but displays the value of the error_count variable rather than the value of warning_count. See the entry for SHOW WARNINGS for more information.

SHOW GRANTS

SHOW GRANTS [FOR account]

Displays grant information about the specified account, which should be given in the same 'user_name'@'host_name' format that is used for the GRANT statement.

SHOW GRANTS FOR 'root'@'localhost';
SHOW GRANTS FOR ''@'cobra.snake.net';

As of MySQL 4.1.2, you can use any of the following statements to display the privileges for the account that you are connected to the server as:

SHOW GRANTS FOR CURRENT_USER();
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS;

SHOW INDEX

SHOW {INDEX | KEY} {FROM | IN} tbl_name [{FROM | IN} db_name]

Displays information about a table's indexes. To specify the database that contains the table, use a FROM db_name clause or write the table name in db_name.tbl_name format:

SHOW INDEX FROM score;
SHOW INDEX FROM score FROM sampdb;
SHOW INDEX FROM sampdb.score;

The output from SHOW INDEX includes the following columns:

  • Table

    The name of the table containing the index.

  • Non_unique

    1 if the index can contain duplicate values, 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 name of the table column in the index to which the current output row apples.

  • Collation

    The column sort order within the index. The values may be A (ascending), D (descending), or NULL (not sorted). Descending keys are not yet available, but may be implemented in the future.

  • Cardinality

    The number of unique values in the index. myisamchk updates this value for MyISAM tables when run with the --analyze option. The ANALYZE TABLE statement updates this value for MyISAM, InnoDB, and 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 algorithm used to index the column, such as BTREE, FULLTEXT, or HASH.

  • Comment

    Reserved for internal comments about the index.

SHOW INNODB STATUS

SHOW INNODB STATUS

Displays information about the internal operation of the InnoDB storage engine. It requires the SUPER privilege.

SHOW LOGS

SHOW [BDB] LOGS

Displays information about the server's BDB log files. It requires the FILE privilege.

The output from SHOW LOGS includes the following columns:

  • File

    The log filename.

  • Type

    The log type.

  • Status

    The log status (for example, IN USE).

SHOW MASTER LOGS

SHOW {MASTER | BINARY} LOGS

This statement is used on replication master servers. It displays the names of the binary log files 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.

SHOW MASTER STATUS

SHOW MASTER STATUS

This statement is used on replication master servers. It displays information about the status of the master's binary logs.

The output from SHOW MASTER STATUS includes the following columns:

  • File

    The binary log filename.

  • 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 OPEN TABLES

SHOW OPEN TABLES

Displays the list of open non-TEMPORARY tables that are registered in the table cache.

The output from SHOW OPEN TABLES includes the following columns:

  • Database

    The database that contains the table.

  • Table

    The name of the table.

  • In_use

    The number of times the table currently is in use.

  • Name_locked

    Indicates whether the table has a name lock such as is required to use the table without accessing its contents (for example, for RENAME TABLE).

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 PROCESSLIST

SHOW [FULL] PROCESSLIST

Displays information about the threads executing within the server. If you have the PROCESS privilege, the statement displays all threads. Otherwise, it displays only your own threads.

The output includes the following columns:

  • Id

    The thread ID number for the client.

  • User

    The username for the account 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 an SQL statement. The value may 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 statement being executed. By default, the first 100 characters are displayed. If the FULL keyword is given, the entire statement is displayed.

SHOW SLAVE HOSTS

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. A slave is not registered unless it is started with the --report-host option. Even for a registered slave, other conditions apply for display of certain columns. The Port column value is blank unless the slave is started with the --report-port option. The User and Password column values are blank unless the slave is started with the --report-user and --report-password options and the master is started with the --show-slave-auth-info option.

The output from SHOW SLAVE HOSTS includes the following columns:

  • Server_id

    The slave server ID.

  • Host

    The slave host.

  • User

    The username for the account that the slave used to connect.

  • Password

    The password for the account that 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.

SHOW SLAVE STATUS

SHOW SLAVE STATUS

This statement is used on slave servers and displays information about the replication status of the server.

The output from SHOW SLAVE STATUS includes the following columns:

  • Slave_IO_State

    The state of the slave I/O thread. This is the same value that SHOW PROCESSLIST will display for the thread.

  • Master_Host

    The master hostname or IP address.

  • Master_User

    The username of the account used 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 log file.

  • Read_Master_Log_Pos

    The current position within the master binary 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 master binary log file that contains the event most recently executed by the SQL thread.

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

  • Replicate_Do_Table

    A comma-separated list of tables that are explicitly replicated with --replicate-do-table options, blank if no such options were given.

  • Replicate_Ignore_Table

    A comma-separated list of tables that are explicitly excluded from replication with --replicate-ignore-table options, blank if no such options were given.

  • Replicate_Wild_Do_Table

    A comma-separated list of table patterns that are explicitly replicated with --replicate-wild-do-table options, blank if no such options were given.

  • Replicate_Wild_Ignore_Table

    A comma-separated list of table patterns that are explicitly excluded from replication with --replicate-wild-ignore-table 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. (You can skip events by setting the sql_slave_skip_counter global system variable.)

  • Exec_Master_Log_Pos

    The current position within the master binary log file where the slave SQL thread is executing.

  • Relay_Log_Space

    The combined size of the relay log files.

  • Until_Condition

    The condition specified in an UNTIL clause of a START SLAVE statement to indicate when the SQL thread should stop reading and executing events:

    • None

      No UNTIL clause was specified.

    • Master

      The slave is reading until its SQL thread reaches a specific position in the master binary log.

    • Relay

      The slave is reading until its SQL thread reaches a specific position in its relay log.

    If the Until_Condition value is Master or Relay, the Until_Log_File and Until_Log_Pos column values indicate the filename and position at which the SQL thread will stop executing.

  • Until_Log_File

    See the description of Until_Condition.

  • Until_Log_Pos

    See the description of Until_Condition.

  • Master_SSL_Allowed

    Whether SSL is used to connect to the master server: Yes if SSL connections can be used, No if they cannot, and Ignored if SSL connections are allowed, but the slave server was not built with SSL support enabled.

  • Master_SSL_CA_File

    The pathname to the certificate authority file for SSL connections to the master, blank if none has been specified.

  • Master_SSL_CA_Path

    The pathname to a directory of trusted certificates to be used for certificate verification for SSL connections to the master, blank if none has been specified.

  • Master_SSL_Cert

    The pathname to the certificate file for SSL connections to the master, blank if none has been specified.

  • Master_SSL_Cipher

    A string listing the SSL ciphers that may be used to encrypt traffic sent over SSL connections to the master, blank if none has been specified.

  • Master_SSL_Key

    The pathname to the key file for SSL connections to the master, blank if none has been specified.

  • Seconds_Behind_Master

    The difference in seconds between the current time and the timestamp recorded in the master event most recently executed by the slave SQL thread. This value is zero if the SQL thread has caught up with the I/O thread and is idle, and NULL if no event has been executed or the slave parameters have been changed with a CHANGE MASTER or RESET SLAVE statement.

SHOW STATUS

SHOW STATUS [LIKE 'pattern']

Displays the server's status variables and their values. These variables provide information about the server's operational state. The LIKE clause may be included to display information only for variables with names that match the given pattern.

Appendix D describes each of the status variables.

SHOW TABLE STATUS

SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern']

Displays descriptive information about the tables in a database. The LIKE clause may be included to display information only for tables with names that match the given pattern. As of MySQL 5.0.1, this statement also displays the views in a database, but all columns are NULL except that Name is the view name and Comment is view.

The output from SHOW TABLE STATUS includes the following columns:

  • Name

    The table name.

  • Engine

    The storage engine (MyISAM, InnoDB, and so forth). This column is named Type before MySQL 4.1.2.

  • Version

    The version number of the .frm file for the table. This column was introduced in MySQL 4.1.3.

  • Row_format

    The row storage format for MyISAM tables; this can be Fixed (fixed-length rows), Dynamic (variable-length rows), or Compressed (compressed and read-only).

  • Rows

    The number of rows in the table. For some storage engines like BDB and InnoDB, this is 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 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 might be a good idea to issue an OPTIMIZE TABLE statement for the table to defragment it.

  • 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, CHECK TABLE, or REPAIR TABLE. The value is NULL if the table has never been checked or repaired.

  • Collation

    The table's character set collation. (The character set is implicit in the first part of the collation name.) This column was introduced in MySQL 4.1.1.

  • Checksum

    The table checksum value, NULL if one has not been calculated. This column was introduced in MySQL 4.1.1.

  • 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, the Comment value shows foreign key definitions; it also displays the amount of free space in the InnoDB tablespace in which the table is stored. (The table might be in the shared tablespace or have its own tablespace.)

In MySQL 4.1.0, the output included a Charset column. This was changed to Collation in 4.1.1.

SHOW TABLE TYPES

SHOW TABLE TYPES

SHOW TABLE TYPES was the original syntax for the SHOW ENGINES statement. It is still recognized but is deprecated and its use produces a warning. See the entry for SHOW ENGINES for a description of the output.

SHOW TABLES

SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE 'pattern']

Displays the names of the non-TEMPORARY tables in a database. The LIKE clause may be included to display information only for tables with names that match the given pattern.

Beginning with MySQL 5.0.1, this statement also displays view names. The FULL keyword may be given as of MySQL 5.0.2 to display for each row whether the name refers to a table or a view.

The output from this statement includes the following columns:

  • Tables_in_db_name

    The table or view name.

  • Table_type

    Whether the name refers to a table or a view. The value is BASE_TABLE or VIEW.

    This column is displayed only if the FULL keyword is given.

SHOW VARIABLES

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

Displays a list of system variables and their values. These variables provide information about the server's configuration and capabilities. The LIKE clause may be included to display information only for variables with names that match the given pattern. Appendix D describes each of the system variables.

The server can display the values of system variables at 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 individual dynamic variables with SELECT:

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode, @@LOCAL.sql_mode;

Using SELECT has the advantage that you can more easily manipulate the query result in certain contexts.

See the entry for the SET statement for a discussion of the syntax used to assign values to system variables.

SHOW WARNINGS

SHOW WARNINGS [LIMIT [skip_count,] show_count]
SHOW COUNT(*) WARNINGS

SHOW WARNINGS displays error, warnings, and notes generated by the most recent statement that generates such messages. If that statement executed successfully, SHOW WARNINGS returns an empty set.

SHOW COUNT(*) WARNINGS displays the value of the warning_count system variable that counts the number of messages. (A related variable, error_count, counts only errors.) It is possible for the value of warning_count to be larger than the number of messages displayed by SHOW WARNINGS. The max_error_count system variable limits the number of messages that can be stored for display by SHOW WARNINGS, but warning_count counts all messages regardless of whether they are stored.

The LIMIT clause can be used to restrict the number of rows returned by SHOW WARNINGS. Its syntax is the same as the LIMIT clause for SELECT.

START SLAVE

START SLAVE [slave_options]

START SLAVE [SQL_THREAD] UNTIL
  MASTER_LOG_FILE = 'file_name', MASTER_LOG_POS = position

START SLAVE [SQL_THREAD] UNTIL
  RELAY_LOG_FILE = 'file_name', RELAY_LOG_POS = position

This statement, together with STOP SLAVE, controls the operation of replication threads on a slave server. START SLAVE initiates the slave I/O and SQL threads and STOP SLAVE terminates them.

The optional slave_options clause may be specified to indicate which of the threads to start or stop. It should consist of one or more of the following options, separated by commas:

  • IO_THREAD

    Start or stop the I/O thread that reads events from the master server and stores them in the relay log.

  • SQL_THREAD

    Start or stop the SQL thread that reads events from the relay log and executes them.

If no thread or SQL_THREAD is named, an UNTIL clause can be used as of MySQL 4.1.1. Depending on which pair of log file and position options are named in the clause, the slave runs until its SQL thread reaches the given position in the master binary logs or slave relay logs. If the SQL thread is already running, the server ignores the UNTIL clause and generates a warning. If the clause includes the SQL_THREAD option, the server starts only the SQL thread; otherwise, it starts both threads.

START TRANSACTION

START TRANSACTION [WITH CONSISTENT SNAPSHOT]

Begins a transaction by disabling autocommit mode until the next COMMIT or ROLLBACK statement. Statements executed while autocommit mode is disabled thus will be committed or rolled back as a unit.

After the transaction has been committed or rolled back, autocommit mode is restored to the state it was in prior to START TRANSACTION. To manipulate autocommit mode explicitly, use SET autocommit. The autocommit variable is described in Appendix D.

As of MySQL 4.1.8, the WITH CONSISTENT SNAPSHOT clause can be used to cause the transaction to begin with a consistent read. Currently, this applies only to InnoDB tables.

START TRANSACTION implicitly releases any table locks that the client has acquired with LOCK TABLE but has not yet released. Executing START TRANSACTION while a transaction is in progress causes that transaction to be committed implicitly.

STOP SLAVE

STOP SLAVE [slave_options]

This statement, together with START SLAVE, controls the operation of replication threads on a slave server. See the description of START SLAVE for details.

TRUNCATE

TRUNCATE [TABLE] tbl_name

trUNCATE TABLE performs a fast truncation of table contents by dropping and re-creating the table. This is much faster than deleting each row individually.

For InnoDB, this statement is implemented as DELETE FROM tbl_name before MySQL 5.0.3. As of 5.0.3, InnoDB implements fast truncation directly.

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.

UNION

select_stmt
  UNION [DISTINCT | ALL] select_stmt
  [UNION [DISTINCT | ALL] select_stmt] ...
  [ORDER BY column_list] [LIMIT [skip_count,] show_count]

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 of the columns in the final result are determined by the column names in the first SELECT. The data types of the columns are determined taking into account all values from the corresponding columns of the selected tables.

The UNION keyword can be followed by DISTINCT to eliminate duplicate rows or by ALL to preserve duplicates and return all selected rows. The implicit default is to eliminate duplicates if neither DISTINCT nor ALL is given. Any DISTINCT union operation (either explicit or implicit) takes precedence over any ALL union operations to its left:

mysql> SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 1;
+---+
| 1 |
+---+
| 1 |
| 2 |
| 1 |
+---+
mysql> SELECT 1 UNION ALL SELECT 2 UNION SELECT 1;
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+

To use ORDER BY or LIMIT clauses with any individual SELECT, enclose each SELECT within parentheses. To apply ORDER BY or LIMIT to the UNION as a whole, enclose each SELECT within parentheses and add ORDER BY or LIMIT following the final closing parenthesis. In this case, any columns named in an ORDER BY should refer to the names of the columns in the first SELECT.

UNLOCK TABLE

UNLOCK {TABLE | TABLES}

This statement releases any table locks being held by the current client.

If a client connection terminates while the client holds table locks, the server releases them when it closes the connection.

If a client begins a transaction while holding table locks, the server implicitly releases those locks.

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 second UPDATE syntax is like the first, but allows multiple tables to be named to perform a multiple-table update.

The rows to be updated 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 the 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.

The WHERE clause can include subqueries, but they cannot select from a table that is being 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 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 G.

LOW_PRIORITY causes the statement to be deferred until no clients are reading from the table.

If updating a record would result in a duplicate key value in a unique index, UPDATE terminates in error and no more records are updated. Adding IGNORE causes such records not to be updated and no error occurs. In strict mode, IGNORE also causes data conversion errors that otherwise would terminate the statement to be treated as non-fatal warnings. Columns are updated to the nearest legal value in this case.

ORDER BY causes rows to be updated according to the resulting sort order. This clause has the same syntax as for SELECT.

If the LIMIT clause is given, the value n specifies the maximum number of rows to update.

For a multiple-table UPDATE, 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;

USE

USE db_name

Selects db_name to make it the current database (the default database for table, view, and stored routine references that include no explicit database name). After a successful USE statement, the server sets the session character_set_database and collation_database system variables to the database character set and collation.

The USE statement fails if the database doesn't exist or if you have no privileges for accessing it.

    Team LiB
    Previous Section Next Section