Previous Section  < Day Day Up >  Next Section

A.1 SQL Statements

A.1.1 ALTER TABLE







ALTER [IGNORE] TABLE table_name alter_spec [, alter_spec ...]



alter_spec:

    ADD [COLUMN] create_definition [FIRST | AFTER column_name ]

  | ADD [COLUMN] (create_definition, create_definition,...)

  | ADD INDEX [index_name] (index_column_name,...)

  | ADD PRIMARY KEY (index_column_name,...)

  | ADD UNIQUE [index_name] (index_column_name,...)

  | ADD FULLTEXT [index_name] (index_column_name,...)

  | ALTER [COLUMN] column_name {SET DEFAULT literal | DROP DEFAULT}

  | CHANGE [COLUMN] old_column_name create_definition

            [FIRST | AFTER column_name]

  | MODIFY [COLUMN] create_definition

            [FIRST | AFTER column_name]

  | DROP [COLUMN] column_name

  | DROP PRIMARY KEY

  | DROP INDEX index_name

  | RENAME [TO] new_table_name

  | ORDER BY col

  | table_options

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

  • To use ALTER TABLE, you need ALTER, INSERT, and CREATE privileges on the table.

  • You can issue multiple ADD, ALTER, DROP, and CHANGE clauses in a single ALTER TABLE statement. This is a MySQL extension to ANSI SQL-92, which allows only one of each clause per ALTER TABLE statement.

  • CHANGE column_name, DROP column_name, and DROP INDEX are MySQL extensions to ANSI SQL-92.

  • MODIFY is an Oracle extension to ALTER TABLE.

  • The word COLUMN is optional and can be omitted.

  • create_definition clauses use the same syntax for ADD and CHANGE as for CREATE TABLE. Note that this syntax includes the column name, not just the column type.

  • You can rename a column using a CHANGE old_column_name create_definition clause. To do so, specify the old and new column names and the type that the column currently has.

  • If you want to change a column's type but not the name, CHANGE syntax still requires an old and new column name, even if they are the same. However, you can also use MODIFY to change a column's type without renaming it.

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

  • You can use FIRST or ADD … AFTER column_name to add a column at a specific position within a table row. The default is to add the column last. From MySQL Version 4.0.1, you can also use the FIRST and AFTER keywords in CHANGE or MODIFY.

  • ALTER COLUMN specifies a new default value for a column or removes the old default value. If the old default is removed and the column can be NULL, the new default is NULL. If the column cannot be NULL, MySQL assigns a default value.

  • DROP INDEX removes an index. This is a MySQL extension to ANSI SQL-92.

  • If columns are dropped from a table, the columns are also removed from any index of which they are a part. If all columns that make up an index are dropped, the index is dropped as well.

  • If a table contains only one column, the column cannot be dropped. If what you intend is to remove the table, use DROP TABLE instead.

  • DROP PRIMARY KEY drops the primary index. If no such index exists, it drops the first UNIQUE index in the table. (MySQL marks the first UNIQUE key as the PRIMARY KEY if no PRIMARY KEY was specified explicitly.) If you add a UNIQUE INDEX or PRIMARY KEY to a table, this is stored before any not UNIQUE index so that MySQL can detect duplicate keys as early as possible.

  • ORDER BY enables you to create the new table with the rows in a specific order. Note that the table will not remain in this order after inserts and deletes. In some cases, it might make sorting easier for MySQL if the table is in order by the column that you want to order it by later. This option is mainly useful when you know that you are mostly going to query the rows in a certain order; by using this option after big changes to the table, you might be able to get higher performance.

A.1.2 ANALYZE TABLE







ANALYZE TABLE table_name[,table_name...]

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

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

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

The command returns a table with the following columns:

  • Table: Table name

  • Op: Always analyze

  • Msg_type: One of status, error, info, or warning

  • Msg_text: The message

You can check the stored key distribution with the SHOW INDEX command.

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

A.1.3 BACKUP TABLE







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

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

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

A.1.4 BEGIN







BEGIN [WORK]

BEGIN [WORK] is a synonym for START TRANSACTION. See section A.1.46, "START TRANSACTION," in this appendix.

When you issue a BEGIN statement (or BEGIN WORK, which is a synonym), the current transaction autocommit mode is suspended, and autocommit is disabled. The following statements form a single transaction. To submit (or confirm) the transaction, issue a COMMIT statement. To cancel the transaction, use ROLLBACK instead. When the transaction ends, the autocommit mode reverts to its state prior to the BEGIN statement.

Transactions are implicitly committed if you issue certain other statements, such as CREATE TABLE. That behavior is covered in detail in Chapter 15, "InnoDB Tables."

The autocommit mode can be enabled or disabled explicitly by setting the AUTOCOMMIT server variable to 1 or 0. See section A.1.31, "SET AUTOCOMMIT," for details. When autocommit is disabled, it is unnecessary to use BEGIN to start a transaction. Just terminate each transaction with COMMIT or ROLLBACK. See sections A.1.6, "COMMIT," and A.1.28, "ROLLBACK," for details.

A.1.5 CHECK TABLE







CHECK TABLE table_name[,table_name...] [check_type [check_type...]]



check_type = QUICK | FAST | MEDIUM | EXTENDED | CHANGED

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

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

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

  • Table: Table name

  • Op: Always check

  • Msg_type: One of status, error, info, or warning

  • Msg_text: The message

Note that the statement might produce many rows of information for each checked table. The last row will have a Msg_type of status and should normally have a Msg_text of OK. If you don't get OK or Table is already up to date, you should normally run a repair of the table. Table is already up to date means that the storage manager for the table indicated that there was no need to check the table.

check_type indicates the type of table-checking operation to perform. The different check types are as follows:

  • QUICK: Don't scan the rows to check for incorrect links.

  • FAST: Only check tables that haven't been closed properly.

  • CHANGED: Only check tables that have been changed since the last check or that haven't been closed properly.

  • MEDIUM: Scan rows to verify that deleted links are okay. This also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys.

  • EXTENDED: Do a full key lookup for all keys for each row. This ensures that the table is 100% consistent, but will take a long time.

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

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







CHECK TABLE test_table FAST QUICK;

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

If a table is corrupted, it's most likely that the problem is in the indexes and not in the data part. All the preceding check types check the indexes thoroughly and should thus find most errors.

If you just want to check a table that you assume is okay, you should use no check options or the QUICK option. The latter should be used when you're in a hurry and can take the very small risk that QUICK didn't find an error in the datafile. (In most cases MySQL should find, under normal usage, any error in the data file. If this happens then the table will be marked as 'corrupted,' in which case the table can't be used until it's repaired.)

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

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

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

Found row where the auto_increment column has the value 0. This means that you have a row in the table where the AUTO_INCREMENT index column contains the value 0. (It's possible to create a row where the AUTO_INCREMENT column is 0 by explicitly setting the column to 0 with an UPDATE statement.) This isn't an error in itself, but could cause trouble if you decide to dump the table and restore it or do an ALTER TABLE on the table. In this case, the AUTO_ INCREMENT column will change value according to the rules of AUTO_INCREMENT columns, which could cause problems such as a duplicate-key error. To get rid of the warning, just execute an UPDATE statement to set the column to some value other than 0.

A.1.6 COMMIT

By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute an update, MySQL will store the update on disk.

If you're using transaction-safe tables (like InnoDB, BDB), you can disable autocommit mode with the following statement:







SET AUTOCOMMIT=0

After this, you must use COMMIT to store your changes to disk or ROLLBACK if you want to ignore the changes you've made since the beginning of your transaction.

If you want to enable autocommit mode for one series of statements, you can use the START TRANSACTION, BEGIN, or BEGIN WORK statement:







START TRANSACTION;

SELECT @A:=SUM(salary) FROM table1 WHERE type=1;

UPDATE table2 SET summary=@A WHERE type=1;

COMMIT;

START TRANSACTION was added to MySQL 4.0.11. This is the recommended way to start an ad-hoc transaction because this is SQL-99 syntax.

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

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

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

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

  • ALTER TABLE

  • BEGIN

  • CREATE INDEX

  • DROP DATABASE

  • DROP INDEX

  • DROP TABLE

  • LOAD MASTER DATA

  • LOCK TABLES

  • RENAME TABLE

  • SET AUTOCOMMIT=1

  • TRUNCATE

UNLOCK TABLES also ends a transaction if any tables currently are locked.

You can change the isolation level for transactions with SET TRANSACTION ISOLATION LEVEL.

A.1.7 CREATE DATABASE







CREATE DATABASE [IF NOT EXISTS] db_name

CREATE DATABASE creates a database with the given name. An error occurs if the database already exists and you didn't specify IF NOT EXISTS.

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

A.1.8 CREATE INDEX







CREATE [UNIQUE|FULLTEXT] INDEX index_name

       ON table_name (column_name[(length)],... )

CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. Normally, you create all indexes on a table at the time the table itself is created with CREATE TABLE. CREATE INDEX enables you to add indexes to existing tables.

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

For CHAR and VARCHAR columns, indexes can be created that use only part of a column, using column_name(length) syntax to index the first length bytes of each column value. (For BLOB and TEXT columns, a prefix length is required; length may be a value up to 255.)

FULLTEXT indexes can index only CHAR, VARCHAR, and TEXT columns, and only in MyISAM tables.

A.1.9 CREATE TABLE







CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name

    [(create_definition,...)]

    [table_options]



create_definition:

    column_name type

      [NOT NULL | NULL] [DEFAULT default_value]

      [AUTO_INCREMENT] [PRIMARY KEY]

  | PRIMARY KEY (index_column_name,...)

  | KEY [index_name] (index_column_name,...)

  | INDEX [index_name] (index_column_name,...)

  | UNIQUE [INDEX] [index_name]  (index_column_name,...)

  | FULLTEXT [INDEX] [index_name] (index_column_name,...)



type:

    TINYINT[(length)] [UNSIGNED] [ZEROFILL]

  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]

  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]

  | INT[(length)] [UNSIGNED] [ZEROFILL]

  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]

  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]

  | REAL[(length, decimals)] [UNSIGNED] [ZEROFILL]

  | DOUBLE[(length, decimals)] [UNSIGNED] [ZEROFILL]

  | FLOAT[(length, decimals)] [UNSIGNED] [ZEROFILL]

  | DECIMAL(length, decimals) [UNSIGNED] [ZEROFILL]

  | NUMERIC(length, decimals) [UNSIGNED] [ZEROFILL]

  | CHAR(length) [BINARY]

  | VARCHAR(length) [BINARY]

  | DATE

  | TIME

  | TIMESTAMP

  | DATETIME

  | TINYBLOB

  | BLOB

  | MEDIUMBLOB

  | LONGBLOB

  | TINYTEXT

  | TEXT

  | MEDIUMTEXT

  | LONGTEXT

  | ENUM(value1, value2, value3,...)

  | SET(value1, value2, value3,...)



index_column_name:

        column_name [(length)]



table_options:

    TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM }

  | AUTO_INCREMENT = #

  | AVG_ROW_LENGTH = #

  | CHECKSUM = {0 | 1}

  | COMMENT = 'string'

  | MAX_ROWS = #

  | MIN_ROWS = #

  | PACK_KEYS = {0 | 1 | DEFAULT}

  | DELAY_KEY_WRITE = {0 | 1}

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

  | RAID_TYPE = { 1 | STRIPED | RAID0 } RAID_CHUNKS=#  RAID_CHUNKSIZE=#

  | UNION = (table_name,[table_name...])

  | INSERT_METHOD = { NO | FIRST | LAST }

  | DATA DIRECTORY = 'absolute path to directory'

  | INDEX DIRECTORY = 'absolute path to directory'

CREATE TABLE creates a table with the given name in the current database. An error occurs if there is no current database or if the table already exists. The table name can be specified as db_name.table_name to create the table in a specific database. You can use the keywords IF NOT EXISTS so that an error does not occur if the table already exists. Note that there is no verification that the existing table has a structure identical to that indicated by the CREATE TABLE statement.

Keep the following considerations in mind when declaring columns and indexes:

  • If neither NULL nor NOT NULL is specified, the column is treated as though NULL had been specified.

  • An integer column may have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value. Typically this is value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1. If you delete all rows in the table with DELETE FROM table_name (without a WHERE), the sequence starts over from 1 for all table types except InnoDB. The InnoDB table handler guarantees that auto-generated values will be unique for a table, even when all rows have been deleted.

  • NULL values are handled differently for TIMESTAMP columns than for other column types. You cannot store a literal NULL in a TIMESTAMP column; setting the column to NULL sets it to the current date and time. Because TIMESTAMP columns behave this way, the NULL and NOT NULL attributes do not apply in the normal way and are ignored if you specify them. On the other hand, to make it easier for MySQL clients to use TIMESTAMP columns, the server reports that such columns may be assigned NULL values (which is true), even though TIMESTAMP never actually will contain a NULL value. Note that setting a TIMESTAMP column to 0 is not the same as setting it to NULL, because 0 is a valid TIMESTAMP value.

  • A DEFAULT value must be a constant; it cannot be a function or an expression. If no DEFAULT value is specified for a column, MySQL automatically assigns one, as follows. If the column may take NULL as a value, the default value is NULL. If the column is declared as NOT NULL, the default value depends on the column type:

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

    • For date and time types other than TIMESTAMP, the default is the appropriate zero value for the type. For the first TIMESTAMP column in a table, the default value is the current date and time.

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

  • A PRIMARY KEY is a unique key with the extra constraint that all key columns must be defined as NOT NULL. In MySQL, the key is named PRIMARY. A table can have only one PRIMARY KEY. If you don't have a PRIMARY KEY and some applications ask for the PRIMARY KEY in your tables, MySQL will return the first UNIQUE key that doesn't have any NULL columns as the PRIMARY KEY.

  • A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attibute in a column specification. Doing so will mark only that single column as primary. You must use a separate PRIMARY KEY(index_column_name, …) clause.

  • A UNIQUE index is one in which all values in the index must be distinct. The exception to this is that if a column in the index is allowed to contain NULL values, it may contain multiple NULL values. This exception does not apply to BDB tables, which allow only a single NULL.

  • If you don't assign a name to an index that is not a PRIMARY KEY, the index will be assigned the same name as the first index_column_name, with an optional suffix (_2, _3, …) to make it unique.

  • With column_name(length) syntax in an index specification, you can create an index that uses only the first length bytes of a CHAR or VARCHAR column. This can make the index file much smaller.

  • Only the MyISAM table type supports indexing on BLOB and TEXT columns. When putting an index on a BLOB or TEXT column, you must always specify the length of the index, up to 255 bytes.

  • When you use ORDER BY or GROUP BY with a TEXT or BLOB column, the server sorts values using only the initial number of bytes indicated by the max_sort_length server variable.

  • You can also create special FULLTEXT indexes. They are used for full-text search. Only the MyISAM table type supports FULLTEXT indexes. They can be created only from CHAR, VARCHAR, and TEXT columns. Indexing always happens over the entire column; partial indexing is not supported.

  • Each NULL column takes one bit extra, rounded up to the nearest byte.

The TYPE option for specifying the table type takes the following values:

  • BDB or BerkeleyDB: Transaction-safe tables with page locking.

  • HEAP: The data for this table is only stored in memory.

  • ISAM: The original storage engine.

  • InnoDB: Transaction-safe tables with row locking.

  • MERGE: A collection of MyISAM tables used as one table.

  • MRG_MyISAM: An alias for MERGE tables.

  • MyISAM: The binary portable storage engine that is the replacement for ISAM.

If a storage engine is specified and that particular engine is not available, MySQL uses MyISAM instead. For example, if a table definition includes the TYPE=BDB option but the MySQL server does not support BDB tables, the table is created as a MyISAM table, and no warning is issued.

The other table options are used to optimize the behavior of the table. In most cases, you don't have to specify any of them. The options work for all table types unless otherwise indicated:

  • AUTO_INCREMENT: The next AUTO_INCREMENT value you want to set for your table (MyISAM only; to set the first auto-increment value for an InnoDB table, insert a dummy row with a value one less, and delete the dummy row).

  • AVG_ROW_LENGTH: An approximation of the average row length for your table. You need to set this only for large tables with variable size records.

  • CHECKSUM: Set this to 1 if you want MySQL to maintain a checksum for all rows. It makes the table a little slower to update, but it also makes it easier to find corrupted tables. (MyISAM only).

  • COMMENT: A comment for your table (60 characters maximum).

  • MAX_ROWS: The maximum number of rows you plan to store in the table.

  • MIN_ROWS: The minimum number of rows you plan to store in the table.

  • PACK_KEYS: Set this to 1 if you want to have a smaller index. This usually makes updates slower and reads faster (MyISAM and ISAM only). Setting this to 0 disables all packing of keys. Setting this to DEFAULT (MySQL 4.0) tells the storage engine to pack only long CHAR/VARCHAR columns.

  • DELAY_KEY_WRITE: Set this to 1 if you want to delay key table updates until the table is closed (MyISAM only).

  • ROW_FORMAT: Defines how the rows should be stored. Currently, this option only works with MyISAM tables, which support the DYNAMIC and FIXED row formats.

  • RAID_TYPE: Helps to exceed the 2GB or 4GB limit for the MyISAM datafile (not the index file) on operating systems that don't support big files. Note that this option is not recommended for a filesystem that supports big files. You can get more speed from the I/O bottleneck by putting RAID directories on different physical disks. RAID_TYPE will work on any operating system, as long as you have configured MySQL with --with-raid. For now, the only allowed RAID_TYPE is STRIPED (1 and RAID0 are aliases for this). If you specify RAID_TYPE=STRIPED for a MyISAM table, MyISAM creates RAID_CHUNKS subdirectories named 00, 01, 02 in the database directory. In each of these directories, MyISAM creates a table_name.MYD. When writing data to the datafile, the RAID handler maps the first RAID_CHUNKSIZE *1024 bytes to the first file, the next RAID_CHUNKSIZE *1024 bytes to the next file and so on.

  • UNION: Used when you want to use a collection of identical tables as one. This works only with MERGE tables. For the moment, you need to have SELECT, UPDATE, and DELETE privileges on the tables you map to a MERGE table. All mapped tables must be in the same database as the MERGE table.

  • INSERT_METHOD: If you want to insert data in a MERGE table, you have to specify with INSERT_METHOD into which table the row should be inserted. INSERT_METHOD is an option useful for MERGE tables only. In the created table, the PRIMARY key will be placed first, followed by all UNIQUE keys, and then the normal keys. This helps the MySQL optimizer to prioritize which key to use and also more quickly to detect duplicated UNIQUE keys.

  • DATA DIRECTORY/INDEX DIRECTORY: By using DATA DIRECTORY='directory' or INDEX DIRECTORY='directory' you can specify where the storage engine should put its datafile and index file. Note that the directory should be a full path to the directory (not a relative path). This only works for MyISAM tables when you aren't using the --skip-symlink option.

A.1.10 DELETE







DELETE FROM table_name

       [WHERE where_definition]

       [ORDER BY ...]

       [LIMIT rows]

or (as of MySQL 4.0.0)

DELETE table_name[.*] [, table_name[.*] ...]

       FROM table-references

       [WHERE where_definition]

or (as of MySQL 4.0.2)

DELETE FROM table_name[.*] [, table_name[.*] ...]

       USING table-references

       [WHERE where_definition]

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

If you issue a DELETE with no WHERE clause, all rows are deleted.

If an ORDER BY clause is used (available as of MySQL 4.0), the rows will be deleted in that order. This is really useful only in conjunction with LIMIT. The MySQL-specific LIMIT rows option to DELETE tells the server the maximum number of rows to be deleted before control is returned to the client. This can be used to ensure that a specific DELETE command doesn't take too much time. You can simply repeat the DELETE command until the number of affected rows is less than the LIMIT value.

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

A.1.11 DESCRIBE







{DESCRIBE | DESC} table_name {column_name | wild}

DESCRIBE is a shortcut for SHOW COLUMNS FROM. It provides information about a table's columns. column_name may be a column name or a string containing the SQL % and _ wildcard characters to obtain output only for the columns with names matching the string. DESCRIBE is provided for Oracle compatibility.

A.1.12 DROP DATABASE







DROP DATABASE [IF EXISTS] db_name

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

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

You can use the keywords IF EXISTS to prevent an error from occurring if the database doesn't exist.

A.1.13 DROP INDEX







DROP INDEX index_name ON table_name

DROP INDEX drops the index named index_name from the table table_name. DROP INDEX is mapped to an ALTER TABLE statement to drop the index (see section A.1.1, "ALTER TABLE").

A.1.14 DROP TABLE







DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name,...]

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

You can use the keywords IF EXISTS to prevent an error from occurring for tables that don't exist.

A.1.15 EXPLAIN







    EXPLAIN table_name

or  EXPLAIN SELECT select_options

EXPLAIN table_name is a synonym for DESCRIBE table_name or SHOW COLUMNS FROM table_name.

When you precede a SELECT statement with the keyword EXPLAIN, MySQL explains how it would process the SELECT, providing information about how tables are joined and in which order.

With the help of EXPLAIN, you can see when you must add indexes to tables to get a faster SELECT that uses indexes to find the records.

For details regarding the output of EXPLAIN, for details about that command, and for examples of its use please refer to the MySQL Reference Manual.

A.1.16 FLUSH TABLES







FLUSH TABLES

This command forces all tables to be closed and reopened.

A.1.17 GRANT







GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]

    ON {table_name | * | *.* | db_name.*}

    TO user_name [IDENTIFIED BY [PASSWORD] 'password']

        [, user_name [IDENTIFIED BY [PASSWORD] 'password'] ...]

    [REQUIRE

        NONE |

        [{SSL| X509}]

        [CIPHER cipher [AND]]

        [ISSUER issuer [AND]]

        [SUBJECT subject]]

    [WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |

                          MAX_UPDATES_PER_HOUR # |

                          MAX_CONNECTIONS_PER_HOUR #]]

The GRANT command enables database administrators to create users and grant rights to MySQL users at four privilege levels:

  • Global level: Global privileges apply to all databases on a given server. These privileges are stored in the mysql.user table.

  • Database level: Database privileges apply to all tables in a given database. These privileges are stored in the mysql.db and mysql.host tables.

  • Table level: Table privileges apply to all columns in a given table. These privileges are stored in the mysql.tables_priv table.

  • Column level: Column privileges apply to single columns in a given table. These privileges are stored in the mysql.columns_priv table.

For details, particularly on the privileges you can grant, see the MySQL Reference Manual.

A.1.18 INSERT







    INSERT [LOW_PRIORITY | DELAYED] [IGNORE]

        [INTO] table_name

        [(column_name,...)]

       VALUES ((expression | DEFAULT),...),(...),...

or  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]

        [INTO] table_name

        [(column_name,...)]

        SELECT ...

or  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]

        [INTO] table_name

        SET column_name=(expression | DEFAULT), ...

INSERT inserts new rows into an existing table. The INSERT … VALUES form of the statement inserts rows based on explicitly specified values.

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

  • If you specify no column list for INSERT … VALUES, values for all columns in the table must be provided in the VALUES() list or by the SELECT.

  • Any column not explicitly given a value is set to its default value. For example, if you specify a column list that doesn't name all the columns in the table, unnamed columns are set to their default values.

  • An expression may refer to any column that was set earlier in a value list.

  • If you specify the keyword DELAYED, the server puts the row or rows to be inserted into a buffer, and the client issuing the INSERT DELAYED statement then may continue on. If the table is busy, the server holds the rows. When the table becomes available for inserts, the server begins inserting rows, checking periodically to see whether there are new read requests for the table. If there are, the delayed row queue is suspended until the table becomes free again.

  • If you specify the keyword LOW_PRIORITY, execution of the INSERT is delayed until no other clients are reading from the table. This includes other clients that began reading while existing clients are reading, and while the INSERT LOW_PRIORITY statement is waiting. It is possible, therefore, for a client that issues an INSERT LOW_PRIORITY statement to wait for a very long time (or even forever) in a read-heavy environment. (This is in contrast to INSERT DELAYED, which lets the client continue at once.) Note that LOW_PRIORITY should normally not be used with MyISAM tables as this disables concurrent inserts.

  • If you specify the keyword IGNORE in an INSERT with many rows, any rows that duplicate an existing PRIMARY or UNIQUE key in the table are ignored (are not inserted). If you do not specify IGNORE, the insert is aborted if there is any row that duplicates an existing key value.

With INSERT … SELECT, you can quickly insert many rows into a table from one or many tables. The following conditions hold true for an INSERT … SELECT statement:

  • Prior to MySQL 4.0.14, the target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query.

  • AUTO_INCREMENT columns work as usual.

  • To ensure that the binary log can be used to re-create the original tables, MySQL will not allow concurrent inserts during INSERT … SELECT.

You can also use REPLACE instead of INSERT to overwrite old rows. REPLACE is the counterpart to INSERT IGNORE in the treatment of new rows that contain unique key values that duplicate old rows: The new rows are used to replace the old rows rather than being discarded.

A.1.19 JOIN

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







table_reference, table_reference

table_reference [CROSS] JOIN table_reference

table_reference INNER JOIN table_reference join_condition

table_reference STRAIGHT_JOIN table_reference

table_reference LEFT [OUTER] JOIN table_reference join_condition

table_reference LEFT [OUTER] JOIN table_reference

table_reference NATURAL [LEFT [OUTER]] JOIN table_reference

{ OJ table_reference LEFT OUTER JOIN table_reference ON conditional_expr }

table_reference RIGHT [OUTER] JOIN table_reference join_condition

table_reference RIGHT [OUTER] JOIN table_reference

table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

table_reference is defined as:







table_name [[AS] alias]

join_condition is defined as:







ON conditional_expr | USING (column_list)

You should generally not have any conditions in the ON part that are used to restrict which rows you have in the result set (there are exceptions to this rule). If you want to restrict which rows should be in the result, you have to do this in the WHERE clause.

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

  • A table reference may be aliased using table_name AS alias_name or table_name alias_name.

  • The ON conditional is any conditional of the form that may be used in a WHERE clause.

  • If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table.

  • The USING (column_list) clause names a list of columns that must exist in both tables.

  • The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables.

  • INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition. Both produce a Cartesian product of the tables used. Normally, you specify how the tables should be linked in the WHERE condition.

  • RIGHT JOIN works analogously to LEFT JOIN. To keep code portable across databases, it's recommended to use LEFT JOIN instead of RIGHT JOIN.

  • STRAIGHT_JOIN is identical to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases in which the join optimizer puts the tables in the wrong order.

A.1.20 LOAD DATA INFILE







LOAD DATA [LOCAL] INFILE 'file_name'

    [REPLACE | IGNORE]

    INTO TABLE table_name

    [FIELDS

        [TERMINATED BY '\t']

        [[OPTIONALLY] ENCLOSED BY '']

        [ESCAPED BY '\\' ]

    ]

    [LINES TERMINATED BY '\n']

    [IGNORE number LINES]

    [(column_name,...)]

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

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

Using LOCAL will be a bit slower than letting the server access the files directly because the contents of the file must be sent over the connection by the client to the server. On the other hand, you do not need the FILE privilege to load local files.

You can also load datafiles by using the mysqlimport utility; it operates by sending a LOAD DATA INFILE command to the server. The --local option causes mysqlimport to read datafiles from the client host.

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

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

  • If a relative pathname with one or more leading components is given, the server searches for the file relative to the server's data directory.

  • If a filename with no leading components is given, the server looks for the file in the database directory of the current database.

Note that these rules mean a file named as ./myfile.txt is read from the server's data directory, whereas the same file named as myfile.txt is read from the database directory of the current database.

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

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

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

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







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

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







LINES TERMINATED BY '\n'

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

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

Any of the field or line handling options may specify an empty string (''). If not empty, the FIELDS [OPTIONALLY] ENCLOSED BY and FIELDS ESCAPED BY values must be a single character. The FIELDS TERMINATED BY and LINES TERMINATED BY values may be more than one character.

FIELDS [OPTIONALLY] ENCLOSED BY controls quoting of fields. For output (SELECT … INTO OUTFILE), if you omit the word OPTIONALLY, all fields are enclosed by the ENCLOSED BY character. If you specify OPTIONALLY, the ENCLOSED BY character is used only to enclose CHAR and VARCHAR fields.

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

  • The FIELDS ESCAPED BY character

  • The FIELDS [OPTIONALLY] ENCLOSED BY character

  • The first character of the FIELDS TERMINATED BY and LINES TERMINATED BY values

  • ASCII 0 (what is actually written following the escape character is ASCII '0', not a zero-valued byte)

If the FIELDS ESCAPED BY character is empty, no characters are escaped. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the previous list (such as ASCII 0).

For input, if the FIELDS ESCAPED BY character is not empty, occurrences of that character are stripped and the following character is taken literally as part of a field value. The exceptions are an escaped 0 or N (for example, \0 or \N if the escape character is \). These sequences are interpreted as ASCII NUL (a zero-valued byte) and NULL.

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

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

If a row has too few fields, the columns for which no input field is present are set to their default values.

TIMESTAMP columns are set to the current date and time only if there is a NULL value for the column (that is, \N), or (for the first TIMESTAMP column only) if the TIMESTAMP column is omitted from the field list when a field list is specified.

If an input row has too many fields, the extra fields are ignored and the number of warnings is incremented.

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

Warnings occur under the same circumstances as when values are inserted via the INSERT statement, except that LOAD DATA INFILE also generates warnings when there are too few or too many fields in the input row.

A.1.21 LOCK TABLES







LOCK TABLES table_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}

            [, table_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...

To use LOCK TABLES, you need the global LOCK TABLES privilege and a SELECT privilege on the involved tables.

The main reasons to use LOCK TABLES are for emulating transactions or getting more speed when updating tables.

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

The difference between READ LOCAL and READ is that READ LOCAL allows nonconflicting INSERT statements to execute while the lock is held. However, this can't be used if you're going to manipulate the database files outside MySQL while you hold the lock.

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

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

LOCK TABLES works as follows:

  1. Sort all tables to be locked in an internally defined order (from the user standpoint, the order is undefined).

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

  3. Lock one table at a time until the thread gets all locks.

This policy ensures that table locking is deadlock free. There are, however, other things you need to be aware of with this schema:

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

You can safely kill a thread that is waiting for a table lock with KILL.

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

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

  • If you're going to run many operations on multiple tables, it's much faster to lock the tables you are going to use. The downside is that no other thread can update a READ-locked table and no other thread can read a WRITE-locked table.

    The reason some things are faster under LOCK TABLES is that MySQL will not flush the key cache for the locked tables until UNLOCK TABLES is called (normally the key cache is flushed after each SQL statement). This speeds up inserting, updating, and deleting on MyISAM tables.

  • If you're using a storage engine in MySQL that doesn't support transactions, you must use LOCK TABLES if you want to ensure that no other thread comes between a SELECT and an UPDATE. The example shown here requires LOCK TABLES to execute safely:

    




    
mysql> LOCK TABLES trans READ, customer WRITE;

mysql> SELECT SUM(value) FROM trans WHERE customer_id=some_id;

mysql> UPDATE customer SET total_value=sum_from_previous_statement

    ->        WHERE customer_id=some_id;

mysql> UNLOCK TABLES;

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

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

You can also solve some cases by using the user-level lock functions GET_LOCK() and RELEASE_LOCK().

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

Note that LOCK TABLES is not transaction-safe and will automatically commit any active transactions before attempting to lock the tables.

The counterpart to the LOCK TABLES command is UNLOCK TABLES (section A.1.49, "UNLOCK TABLES").

A.1.22 OPTIMIZE TABLE







OPTIMIZE TABLE table_name[,table_name]...

At the moment, OPTIMIZE TABLE works only on MyISAM and BDB tables. For BDB tables, OPTIMIZE TABLE is currently mapped to ANALYZE TABLE (see section A.1.2, "ANALYZE TABLE"). You can get OPTIMIZE TABLE to work on other table types by starting mysqld with --skip-new or --safe-mode, but in this case OPTIMIZE TABLE is just mapped to ALTER TABLE (see section A.1.1, "ALTER TABLE").

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

OPTIMIZE TABLE works the following way:

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

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

  • If the statistics are not up-to-date (and the repair couldn't be done by sorting the index), update them.

OPTIMIZE TABLE for a MyISAM table is equivalent to running myisamchk --quick --check-only-changed --sort-index --analyze on the table.

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

A.1.23 RENAME TABLE







RENAME TABLE table_name TO new_table_name[, table_name2 TO new_table_name2,...]

Assigns new_table_name to the table table_name.

A.1.24 REPAIR TABLE







REPAIR TABLE table_name[,table_name...] [QUICK] [EXTENDED] [USE_FRM]

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

Normally, you should never have to run this command, but if disaster strikes, you're very likely to get back all your data from a MyISAM table with REPAIR TABLE. If your tables get corrupted often, you should try to find the reason for it to eliminate the need to use REPAIR TABLE.

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

  • Table: Table name

  • Op: Always repair

  • Msg_type: One of status, error, info, or warning

  • Msg_text: The message

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

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

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

Use USE_FRM mode for REPAIR if the .MYI file is missing or if its header is corrupted. In this mode, MySQL will re-create the table using information from the .frm file. This kind of repair cannot be done with myisamchk.

A.1.25 REPLACE







    REPLACE [LOW_PRIORITY | DELAYED]

            [INTO] table_name

            [(column_name,...)]

            VALUES (expression,...),(...),...

or  REPLACE [LOW_PRIORITY | DELAYED]

            [INTO] table_name

            SET column_name=expression, column_name=expression,...

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

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

When you use a REPLACE statement, the affected-rows count is 2 if the new row replaced an old row. This is because one row was inserted after the duplicate was deleted. This fact makes it easy to determine whether REPLACE added or replaced a row: check whether the affected-rows value is 1 (added) or 2 (replaced).

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

A.1.26 RESTORE TABLE







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

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

A.1.27 REVOKE







REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]

    ON {table_name | * | *.* | db_name.*}

    FROM user_name [, user_name ...]

Revokes privileges granted to a user by using the GRANT command. When using REVOKE to revoke privileges on the column level, you must specify the same columns that were granted.

See also section A.1.17, "GRANT."

A.1.28 ROLLBACK







ROLLBACK

After disabling autocommit mode by setting the AUTOCOMMIT variable to zero, you must use COMMIT to store your changes to disk or ROLLBACK if you want to ignore the changes you've made since the beginning of your transaction. See section A.1.6, "COMMIT," for details.

A.1.29 SELECT







SELECT [STRAIGHT_JOIN]

       [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

       [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]

       [DISTINCT | DISTINCTROW]

    select_expression,...

    [INTO OUTFILE 'file_name' export_options

    | INTO DUMPFILE 'file_name']

    [FROM table_references

      [WHERE where_definition]

      [GROUP BY {unsigned_integer | column_name | formula} [ASC | DESC], ...]

      [HAVING where_definition]

      [ORDER BY {unsigned_integer | column_name | formula} [ASC | DESC] ,...]

      [LIMIT [offset,] row_count]

      [PROCEDURE procedure_name]

      [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT is used to retrieve rows selected from one or more tables. A select_expression indicates a column you want to retrieve. SELECT may also be used to retrieve rows computed without reference to any table.

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

  • A SELECT expression may be given an alias using AS alias_name. The alias is used as the expression's column name and can be used with ORDER BY or HAVING clauses. The AS keyword is optional when aliasing a SELECT expression.

  • You aren't allowed to use a column alias in a WHERE clause because the column value might not yet be determined when the WHERE clause is executed.

  • The FROM table_references clause indicates the tables from which to retrieve rows. If you name more than one table, you're performing a join. Joins are discussed further in the "Core Study Guide."

  • A table reference may be aliased using table_name AS alias_name. The AS keyword is optional.

  • Columns selected for output may be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or column positions. Column positions begin with 1. To sort in reverse order, add the DESC (descending) keyword to the name of the column in the ORDER BY clause that you're sorting by. The default is ascending order; this may be specified explicitly using the ASC keyword.

  • In the WHERE clause, you can use any of the functions that MySQL supports, except for aggregate (summary) functions.

  • The HAVING clause can refer to any column or alias named in the select_expression. It is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.) Don't use HAVING for items that should be in the WHERE clause.

  • The DISTINCT and DISTINCTROW options specify that duplicate rows in the result set should be removed.

  • STRAIGHT_JOIN, HIGH_PRIORITY, and options beginning with SQL, are MySQL extensions to SQL-99.

    • HIGH_PRIORITY will give the SELECT higher priority than a statement that updates a table. You should use this only for queries that are very fast and must be done at once. A SELECT HIGH_PRIORITY query will run if the table is locked for reading even if there is an update statement that is waiting for the table to be free.

    • SQL_BIG_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set will have many rows. In this case, MySQL will directly use disk-based temporary tables if needed. MySQL will also, in this case, prefer sorting to doing a temporary table with a key on the GROUP BY elements.

    • SQL_BUFFER_RESULT forces the result to be put into a temporary table. This helps MySQL free the table locks early and helps in cases where it takes a long time to send the result set to the client.

    • SQL_SMALL_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set will be small. In this case, MySQL uses fast temporary tables to store the resulting table instead of using sorting.

    • SQL_CALC_FOUND_ROWS tells MySQL to calculate how many rows there would be in the result, disregarding any LIMIT clause. The number of rows then can be obtained with SELECT FOUND_ROWS().

    • SQL_CACHE tells MySQL to store the query result in the query cache if you're using QUERY_CACHE_TYPE=2 (DEMAND).

    • SQL_NO_CACHE tells MySQL not to store the query result in the query cache.

    • STRAIGHT_JOIN forces the optimizer to join the tables in the order in which they are listed in the FROM clause. You can use this to speed up a query if the optimizer joins the tables in non-optimal order.

  • If you use GROUP BY, the output rows will be sorted according to the GROUP BY as if you had an ORDER BY over all the fields in the GROUP BY. MySQL has extended the GROUP BY clause so that you can also specify ASC and DESC after columns named in the clause.

  • The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must be integer constants. With one argument, the value specifies the number of rows to return from the beginning of the result set. With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1).

  • The SELECT … INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host and cannot already exist (among other things, this prevents database tables and files such as /etc/passwd from being destroyed). You must have the FILE privilege on the server host to use this form of SELECT. The SELECT … INTO OUTFILE statement is intended primarily to let you very quickly dump a table on the server machine. If you want to create the resulting file on some other host than the server host, you can't use SELECT … INTO OUTFILE. In that case, you should instead use a client program such as mysqldump to generate the file. SELECT … INTO OUTFILE is the complement of LOAD DATA INFILE; the syntax for the export_options part of the statement consists of the same FIELDS and LINES clauses that are used with the LOAD DATA INFILE statement. Note that any file created by INTO OUTFILE will be writable by all users on the server host. The reason is that the MySQL server can't create a file that is owned by anyone else than the user it's running as (you should never run mysqld as root). The file thus must be world-writable so that you can manipulate its contents.

  • If you use INTO DUMPFILE instead of INTO OUTFILE, MySQL will only write one row into the file, without any column or line terminations and without performing any escape processing. This is useful if you want to store a BLOB value in a file.

  • If you use FOR UPDATE on a table handler with page or row locks, the examined rows are write-locked until the end of the current transaction.

A.1.30 SET







SET [GLOBAL | SESSION] sql_variable=expression

    [, [GLOBAL | SESSION] sql_variable=expression] ...

SET sets various options that affect the operation of the server or your client.

The following examples show the different syntaxes one can use to set variables:

LOCAL can be used as a synonym for SESSION.

If you set several variables on the same command line, the last used GLOBAL | SESSION mode is used.

The @@variable_name syntax is supported to make MySQL syntax compatible with some other databases.

Some of the system variables you can set are described in the system variable section of this appendix. See section A.4, "Server System Variables."

If you're using SESSION (the default), the option you set remains in effect until the current session ends or until you set the option to a different value. If you use GLOBAL, which requires the SUPER privilege, the option is remembered and used for new connections until the server restarts. If you want to make an option permanent, you should set it in one of the MySQL option files.

To avoid incorrect usage, MySQL will produce an error if you use SET GLOBAL with a variable that can only be used with SET SESSION, or if you attempt to set a a global variable without specifying GLOBAL.

If you want to set a SESSION variable to the GLOBAL value or a GLOBAL value to the MySQL default value, you can set it to DEFAULT.

See also the SHOW VARIABLES section in this appendix (A.1.45, "SHOW VARIABLES").

A.1.31 SET AUTOCOMMIT







SET AUTOCOMMIT = {0 | 1}

If you're using transaction-safe tables (such as InnoDB or BDB), you can disable autocommit mode with the following statement:







SET AUTOCOMMIT=0

To enable autocommit mode, use the following statement:







SET AUTOCOMMIT=1

By default, autocommit mode is enabled.

A.1.32 SET PASSWORD







SET PASSWORD FOR 'user'[@'host']=PASSWORD('new_password')

Sets the password for the specified user. Note that the password needs to be encrypted using the PASSWORD() function.

A.1.33 SET TRANSACTION ISOLATION LEVEL







SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL

{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

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

The default behavior is to set the isolation level for the next (not started) transaction. If you use the GLOBAL keyword, the statement sets the default transaction level globally for all new connections created from that point on (but not existing connections). You need the SUPER privilege to do this. Using the SESSION keyword sets the default transaction level for all future transactions performed on the current connection.

InnoDB supports each of these levels from MySQL 4.0.5 on. The default level is REPEATABLE READ.

You can set the default global isolation level for mysqld with --transaction-isolation.

A.1.34 SHOW







  SHOW COLUMNS FROM table_name [FROM db_name] [LIKE 'pattern']

| SHOW CREATE TABLE table_name

| SHOW DATABASES [LIKE 'pattern']

| SHOW INDEX FROM table_name [FROM db_name]

| SHOW INNODB STATUS

| SHOW [FULL] PROCESSLIST

| SHOW STATUS [LIKE 'pattern']

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

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

| SHOW VARIABLES [LIKE 'pattern']

SHOW provides information about databases, tables, columns, or status information about the server. If the LIKE 'pattern' part is used, the 'pattern' string can contain the SQL % and _ wildcard characters.

A.1.35 SHOW COLUMNS







SHOW COLUMNS FROM table_name [FROM db_name] [LIKE 'pattern']

Lists the columns in a given table. SHOW FIELDS is a synonym for SHOW COLUMNS.

A.1.36 SHOW CREATE TABLE







SHOW CREATE TABLE table_name

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

A.1.37 SHOW DATABASES







SHOW DATABASES [LIKE 'pattern']

Lists the databases on the MySQL server host. If you don't have the global SHOW DATABASES privilege, you'll see only those databases for which you have some kind of privilege.

A.1.38 SHOW FIELDS

SHOW FIELDS is a synonym for SHOW COLUMNS. See section A.1.35, "SHOW COLUMNS."

A.1.39 SHOW INDEX







SHOW INDEX FROM table_name [FROM db_name]

Returns index information about a table. Here are some of the columns that are returned:

  • Non_unique: 0 if the index can't contain duplicates, 1 if it can

  • Key_name: Name of the index

  • Column_name: Column name

  • Sub_part: Number of indexed characters if the column is only partly indexed; NULL if the entire key is indexed

  • Null: Contains YES if the column may contain NULL

A.1.40 SHOW INNODB STATUS







SHOW INNODB STATUS

The InnoDB storage engine includes InnoDB Monitors that print information about the InnoDB internal state. The SHOW INNODB STATUS statement fetches the output of the standard InnoDB Monitor to the SQL client. The information is useful in performance tuning. If you're using the mysql interactive SQL client, the output is more readable if you replace the usual semicolon statement terminator by \G:







SHOW INNODB STATUS\G

A.1.41 SHOW PROCESSLIST







SHOW [FULL] PROCESSLIST

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

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

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

A.1.42 SHOW STATUS







SHOW STATUS [like 'pattern']

SHOW STATUS provides server status information (like mysqladmin extended-status). For the variables and their values that this command displays, see the MySQL Reference Manual.

A.1.43 SHOW TABLE STATUS







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

This statement has syntax similar to SHOW TABLES, but provides a lot of information about each table.

A.1.44 SHOW TABLES







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

Lists the tables in a given database.

A.1.45 SHOW VARIABLES







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

SHOW VARIABLES shows the values of some MySQL system variables. With GLOBAL, you'll get the values that will be used for new connections to MySQL. With SESSION, you'll get the values that are in effect for the current connection. If you don't specify either option, SESSION is used.

You can also get this information using the mysqladmin variables command.

If the default variable values are unsuitable, you can set most of them using command-line options when mysqld starts. It's also possible to change most variables with the SET statement.

For some of the variables and their values that this statement displays, see the system variables section in this appendix (A.4, "Server System Variables"). For a full list, see the MySQL Reference Manual.

A.1.46 START TRANSACTION







START TRANSACTION

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







START TRANSACTION;

SELECT @A:=SUM(salary) FROM table1 WHERE type=1;

UPDATE table2 SET summary=@A WHERE type=1;

COMMIT;

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

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

A.1.47 TRUNCATE TABLE







TRUNCATE TABLE table_name

TRUNCATE TABLE deletes all rows in a table. It differs from DELETE FROM … in the following ways:

  • Truncate operations drop and re-create the table, which is much faster than deleting rows one by one.

  • Truncate operations are not transaction-safe; you'll get an error if you have an active transaction or an active table lock.

  • The number of deleted rows is not returned.

  • As long as the table format file table_name.frm is valid, the table can be re-created this way, even if the data or index files have become corrupted.

TRUNCATE TABLE is an Oracle SQL extension.

A.1.48 UNION







SELECT ... UNION [ALL] SELECT ... [UNION SELECT ...]

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

The columns listed in the select_expression portion of the SELECT (see section A.1.29, "SELECT") should have the same type. The column names used in the first SELECT query will be used as the column names for the results returned.

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

A.1.49 UNLOCK TABLES







UNLOCK TABLES

UNLOCK TABLES releases any locks held by the current thread. See also the LOCK TABLES section in this appendix (A.1.21, "LOCK TABLES").

A.1.50 UPDATE







    UPDATE table_name

        SET column_name1=expr1 [, column_name2=expr2 ...]

        [WHERE where_definition]

        [ORDER BY ...]

        [LIMIT row_count]

or  UPDATE table_name [, table_name ...]

        SET column_name1=expr1 [, column_name2=expr2 ...]

        [WHERE where_definition]

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

If you access a column from table_name in an expression, UPDATE uses the current value of the column (for example, for calculations with the column value).

UPDATE returns the number of rows that were actually changed. If you set a column to the value it currently has, MySQL notices this and doesn't update it.

You can use LIMIT row_count to ensure that only a given number of rows are changed.

Starting with MySQL Version 4.0.4, you can also perform UPDATE operations that cover multiple tables. Note: You cannot use ORDER BY or LIMIT with multi-table UPDATE.

    Previous Section  < Day Day Up >  Next Section