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

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

ALTER DATABASE allows you to change the overall characteristics of a database. These characteristics are stored in the db.opt file in the database directory. To use ALTER DATABASE, you need the ALTER privilege on the database. The CHARACTER SET clause changes the default database character set. The COLLATE clause changes the default database collation. Character set and collation names are discussed in Chapter 11, Character Set Support.

ALTER DATABASE was added in MySQL 4.1.1. ALTER SCHEMA can be used as of MySQL 5.0.2.

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

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

ALTER TABLE allows you to change the structure of an existing table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. You can also change the comment for the table and type of the table. The syntax for many of the allowable alterations is similar to clauses of the CREATE TABLE statement. See CREATE TABLE.

Some operations may result in warnings if attempted on a table for which the storage engine does not support the operation. In MySQL 4.1 and up, these warnings can be displayed with SHOW WARNINGS. See SHOW WARNINGS.

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

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

Note that if you use any other option to ALTER TABLE than RENAME, MySQL always creates a temporary table, even if the data wouldn't strictly need to be copied (such as when you change the name of a column). We plan to fix this in the future, but because ALTER TABLE is not a statement that is normally used frequently, this isn't high on our TODO list. For MyISAM tables, you can speed up the index re-creation operation (which is the slowest part of the alteration process) by setting the myisam_sort_buffer_size system variable to a high value.

Here are some examples that show uses of ALTER TABLE. Begin with a table t1 that is created as shown here:

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

To rename the table from t1 to t2:

mysql> ALTER TABLE t1 RENAME t2;

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

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

To add a new TIMESTAMP column named d:

mysql> ALTER TABLE t2 ADD d TIMESTAMP;

To add indexes on column d and on column a:

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

To remove column c:

mysql> ALTER TABLE t2 DROP COLUMN c;

To add a new AUTO_INCREMENT integer column named c:

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

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

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

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

See ALTER TABLE problems.

ALTER VIEW view_name [(column_list)] AS select_statement

This statement changes the definition of an existing view. select_statement is as for CREATE VIEW. See CREATE VIEW. This statement was added in MySQL 5.0.1.

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

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

CREATE DATABASE creates a database with the given name. To use CREATE DATABASE, you need the CREATE privilege on the database. Rules for allowable database names are given in the section called “Database, Table, Index, Column, and Alias Names”. An error occurs if the database already exists and you didn't specify IF NOT EXISTS.

As of MySQL 4.1.1, create_specification options can be given to specify database characteristics. Database characteristics are stored in the db.opt file in the database directory. The CHARACTER SET clause specifies the default database character set. The COLLATE clause specifies the default database collation. Character set and collation names are discussed in Chapter 11, Character Set Support.

Databases in MySQL are implemented as directories containing files that correspond to tables in the database. Because there are no tables in a database when it is initially created, the CREATE DATABASE statement only creates a directory under the MySQL data directory (and the db.opt file, for MySQL 4.1.1 and up).

CREATE SCHEMA can be used as of MySQL 5.0.2.

You can also use the mysqladmin program to create databases. See mysqladmin.

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

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

In MySQL 3.22 or later, CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. See ALTER TABLE. The CREATE INDEX statement doesn't do anything prior to MySQL 3.22. Normally, you create all indexes on a table at the time the table itself is created with CREATE TABLE. See CREATE TABLE. CREATE INDEX allows you to add indexes to existing tables.

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

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

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

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

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

Prefixes can be up to 255 bytes long (or 1000 bytes for MyISAM and InnoDB tables as of MySQL 4.1.2). Note that prefix limits are measured in bytes, whereas the prefix length in CREATE INDEX statements is interpreted as number of characters. Take this into account when specifying a prefix length for a column that uses a multi-byte character set.

Note that you can add an index on a column that can have NULL values only if you are using MySQL 3.23.2 or newer and are using the MyISAM, InnoDB, or BDB table type. You can only add an index on a BLOB or TEXT column if you are using MySQL 3.23.2 or newer and are using the MyISAM or BDB table type, or MySQL 4.0.14 or newer and the InnoDB table type.

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

For more information about how MySQL uses indexes, see MySQL indexes.

FULLTEXT indexes can index only CHAR, VARCHAR, and TEXT columns, and only in MyISAM tables. FULLTEXT indexes are available in MySQL 3.23.23 or later. the section called “Full-Text Search Functions”.

SPATIAL indexes can index only spatial columns, and only in MyISAM tables. SPATIAL indexes are available in MySQL 4.1 or later. Spatial column types are described in Chapter 19, Spatial Extensions in MySQL.

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

Or:

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

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

column_definition:
    col_name type [NOT NULL | NULL] [DEFAULT default_value]
        [AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'string']
        [reference_definition]

type:
    TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  | INT[(length)] [UNSIGNED] [ZEROFILL]
  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
  | NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
  | DATE
  | TIME
  | TIMESTAMP
  | DATETIME
  | CHAR(length) [BINARY | ASCII | UNICODE]
  | VARCHAR(length) [BINARY]
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT
  | TEXT
  | MEDIUMTEXT
  | LONGTEXT
  | ENUM(value1,value2,value3,...)
  | SET(value1,value2,value3,...)
  | spatial_type

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

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

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

table_options: table_option [table_option] ...

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

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

CREATE TABLE creates a table with the given name. You must have the CREATE privilege for the table.

Rules for allowable table names are given in the section called “Database, Table, Index, Column, and Alias Names”. By default, the table is created in the current database. An error occurs if the table already exists, if there is no current database, or if the database does not exist. In MySQL 3.22 or later, the table name can be specified as db_name.tbl_name to create the table in a specific database. This works whether or not there is a current database. If you use quoted identifiers, quote the database and table names separately. For example, `mydb`.`mytbl` is legal, but `mydb.mytbl` is not.

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

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

MySQL represents each table by an .frm table format (definition) file in the database directory. The storage engine for the table might create other files as well. In the case of MyISAM tables, the storage engine creates three files for a table named tbl_name:

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

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

The table_options part of the CREATE TABLE syntax can be used in MySQL 3.23 and above.

The ENGINE and TYPE options specify the storage engine for the table. ENGINE was added in MySQL 4.0.18 (for 4.0) and 4.1.2 (for 4.1). It is the preferred option name as of those versions, and TYPE has become deprecated. TYPE will be supported throughout the 4.x series, but likely will be removed in MySQL 5.1.

The ENGINE and TYPE options take the following values:

See Chapter 15, MySQL Storage Engines and Table Types.

If a storage engine is specified that is not available, MySQL uses MyISAM instead. For example, if a table definition includes the ENGINE=BDB option but the MySQL server does not support BDB tables, the table is created as a MyISAM table. This makes it possible to have a replication setup where you have transactional tables on the master but tables created on the slave are non-transactional (to get more speed). In MySQL 4.1.1, a warning occurs if the storage engine specification is not honored.

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

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

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

MySQL will create new column for all elements in the SELECT. For example:

mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
    ->        PRIMARY KEY (a), KEY(b))
    ->        TYPE=MyISAM SELECT b,c FROM test2;

This creates a MyISAM table with three columns, a, b, and c. Notice that the columns from the SELECT statement are appended to the right side of the table, not overlapped onto it. Take the following example:

mysql> SELECT * FROM foo;
+---+
| n |
+---+
| 1 |
+---+

mysql> CREATE TABLE bar (m INT) SELECT n FROM foo;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM bar;
+------+---+
| m    | n |
+------+---+
| NULL | 1 |
+------+---+
1 row in set (0.00 sec)

For each row in table foo, a row is inserted in bar with the values from foo and default values for the new columns.

If any errors occur while copying the data to the table, it is automatically dropped and not created.

CREATE TABLE ... SELECT will not automatically create any indexes for you. This is done intentionally to make the statement as flexible as possible. If you want to have indexes in the created table, you should specify these before the SELECT statement:

mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;

Some conversion of column types might occur. For example, the AUTO_INCREMENT attribute is not preserved, and VARCHAR columns can become CHAR columns.

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

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

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

CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;

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

CREATE TABLE new_tbl LIKE orig_tbl;

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

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

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

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

This statement creates a new view, or replaces an existing one if the OR REPLACE clause is given. The select_statement is a SELECT statement that provides the definition of the view. The optional column list can be given to define explicit names for the view columns.

WITH CHECK OPTION, if given, is parsed and ignored. A view can be created from many kinds of SELECT statements. For example, the SELECT can refer to a single table, a join of multiple tables, or a UNION. The SELECT need not even refer to any tables. The following example defines a view that selects two columns from another table, as well as an expression calculated from those columns:

mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    3 |    50 |   150 |
+------+-------+-------+

By default, the view is in the current database. To create the view explicitly in a given database, specify the name as db_name.view_name when you create it.

mysql> CREATE VIEW test.v AS SELECT * FROM t;

This statement was added in MySQL 5.0.1.

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

DROP DATABASE drops all tables in the database and deletes the database. Be very careful with this statement! To use DROP DATABASE, you need the DROP privilege on the database.

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

DROP SCHEMA can be used as of MySQL 5.0.2. If you use DROP DATABASE on a symbolically linked database, both the link and the original database are deleted.

As of MySQL 4.1.2, DROP DATABASE returns the number of tables that were removed. This corresponds to the number of .frm files removed.

The DROP DATABASE statement removes from the given database directory those files and directories that MySQL itself may create during normal operation:

If other files or directories remain in the database directory after MySQL removes those just listed, the database directory cannot be removed. In this case, you must remove any remaining files or directories manually and issue the DROP DATABASE statement again.

You can also drop databases with mysqladmin. See mysqladmin.

DROP INDEX index_name ON tbl_name

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

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

DROP TABLE removes one or more tables. You must have the DROP privilege for each table. All table data and the table definition are removed, so be careful with this statement!

In MySQL 3.22 or later, you can use the keywords IF EXISTS to prevent an error from occurring for tables that don't exist. As of MySQL 4.1, a NOTE is generated for each non-existent table when using IF EXISTS. See SHOW WARNINGS.

RESTRICT and CASCADE are allowed to make porting easier. For the moment, they do nothing.

Note: DROP TABLE automatically commits the current active transaction, unless you are using MySQL 4.1 or higher and the TEMPORARY keyword. The TEMPORARY keyword is ignored in MySQL 4.0. As of 4.1, it has the following effect:

Using TEMPORARY is a good way to ensure that you don't accidentally drop a non-TEMPORARY table.

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

DROP VIEW removes one or more views. You must have the DROP privilege for each view.

You can use the keywords IF EXISTS to prevent an error from occurring for views that don't exist. When this clause is given, a NOTE is generated for each non-existent view. See SHOW WARNINGS.

RESTRICT and CASCADE, if given, are parsed and ignored. This statement was added in MySQL 5.0.1.

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

This statement renames one or more tables. It was added in MySQL 3.23.23.

The rename operation is done atomically, which means that no other thread can access any of the tables while the rename is running. For example, if you have an existing table old_table, you can create another table new_table that has the same structure but is empty, and then replace the existing table with the empty one as follows:

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

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

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

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

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

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

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