14 SQL Statement Syntax

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

14.1 Data Manipulation Statements

14.1.1 DELETE Syntax

Single-table syntax: 

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

Multiple-table syntax: 

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
       tbl_name[.*] [, tbl_name[.*] ...]
       FROM table_references
       [WHERE where_definition]

Or: 

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
       FROM tbl_name[.*] [, tbl_name[.*] ...]
       USING table_references
       [WHERE where_definition]

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

If you issue a DELETE statement with no WHERE clause, all rows are deleted. A faster way to do this, when you don't want to know the number of deleted rows, is to use TRUNCATE TABLE. See section 14.1.9 TRUNCATE Syntax.

In MySQL 3.23, DELETE without a WHERE clause returns zero as the number of affected records.

In MySQL 3.23, if you really want to know how many records are deleted when you are deleting all rows, and are willing to suffer a speed penalty, you can use a DELETE statement that includes a WHERE clause with an expression that is true for every row. For example: 

mysql> DELETE FROM tbl_name WHERE 1>0;

This is much slower than TRUNCATE tbl_name, because it deletes rows one at a time.

If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value will be reused for an ISAM or BDB table, but not for a MyISAM or InnoDB table. If you delete all rows in the table with DELETE FROM tbl_name (without a WHERE) in AUTOCOMMIT mode, the sequence starts over for all table types except for InnoDB and (as of MySQL 4.0) MyISAM. There are some exceptions to this behavior for InnoDB tables, discussed in section 16.7.3 How an AUTO_INCREMENT Column Works in InnoDB.

For MyISAM and BDB tables, you can specify an AUTO_INCREMENT secondary column in a multiple-column key. In this case, reuse of values deleted from the top of the sequence occurs even for MyISAM tables. See section 3.6.9 Using AUTO_INCREMENT.

The DELETE statement supports the following modifiers:

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

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

The MySQL-specific LIMIT row_count option to DELETE tells the server the maximum number of rows to be deleted before control is returned to the client. This can be used to ensure that a specific DELETE statement doesn't take too much time. You can simply repeat the DELETE statement until the number of affected rows is less than the LIMIT value.

If the DELETE statement includes an ORDER BY clause, the rows are deleted in the order specified by the clause. This is really useful only in conjunction with LIMIT. For example, the following statement finds rows matching the WHERE clause, sorts them in timestamp order, and deletes the first (oldest) one: 

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

ORDER BY can be used with DELETE beginning with MySQL 4.0.0.

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

The first multiple-table DELETE syntax is supported starting from MySQL 4.0.0. The second is supported starting from MySQL 4.0.2. The table_references part lists the tables involved in the join. Its syntax is described in section 14.1.7.1 JOIN Syntax.

For the first syntax, only matching rows from the tables listed before the FROM clause are deleted. For the second syntax, only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted. The effect is that you can delete rows from many tables at the same time and also have additional tables that are used for searching: 

DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id;

Or: 

DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id;

These statements use all three files when searching for rows to delete, but delete matching rows only from tables t1 and t2.

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

The syntax allows .* after the table names for compatibility with Access.

If you use a multiple-table DELETE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, delete from a single table and rely on the ON DELETE capabilities that InnoDB provides to cause the other tables to be modified accordingly.

Note: In MySQL 4.0, you should refer to the table names to be deleted with the true table name. In MySQL 4.1, you must use the alias (if one was given) when referring to a table name:

In MySQL 4.0: 

DELETE test FROM test AS t1, test2 WHERE ...

In MySQL 4.1: 

DELETE t1 FROM test AS t1, test2 WHERE ...

The reason we didn't make this change in 4.0 is that we didn't want to break any old 4.0 applications that were using the old syntax.

14.1.2 DO Syntax

DO expr [, expr] ...

DO executes the expressions but doesn't return any results. This is shorthand for SELECT expr, ..., but has the advantage that it's slightly faster when you don't care about the result.

DO is useful mainly with functions that have side effects, such as RELEASE_LOCK().

DO was added in MySQL 3.23.47.

14.1.3 HANDLER Syntax

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

The HANDLER statement provides direct access to table storage engine interfaces. It is available for MyISAM tables as MySQL 4.0.0 and InnoDB tables as of MySQL 4.0.3.

The HANDLER ... OPEN statement opens a table, making it accessible via subsequent HANDLER ... READ statements. This table object is not shared by other threads and is not closed until the thread calls HANDLER ... CLOSE or the thread terminates. If you open the table using an alias, further references to the table with other HANDLER statements must use the alias rather than the table name.

The first HANDLER ... READ syntax fetches a row where the index specified satisfies the given values and the WHERE condition is met. If you have a multiple-column index, specify the index column values as a comma-separated list. Either specify values for all the columns in the index, or specify values for a leftmost prefix of the index columns. Suppose that an index includes three columns named col_a, col_b, and col_c, in that order. The HANDLER statement can specify values for all three columns in the index, or for the columns in a leftmost prefix. For example: 

HANDLER ... index_name = (col_a_val,col_b_val,col_c_val) ...
HANDLER ... index_name = (col_a_val,col_b_val) ...
HANDLER ... index_name = (col_a_val) ...

The second HANDLER ... READ syntax fetches a row from the table in index order that that matches WHERE condition.

The third HANDLER ... READ syntax fetches a row from the table in natural row order that matches the WHERE condition. It is faster than HANDLER tbl_name READ index_name when a full table scan is desired. Natural row order is the order in which rows are stored in a MyISAM table data file. This statement works for InnoDB tables as well, but there is no such concept because there is no separate data file.

Without a LIMIT clause, all forms of HANDLER ... READ fetch a single row if one is available. To return a specific number of rows, include a LIMIT clause. It has the same syntax as for the SELECT statement. See section 14.1.7 SELECT Syntax.

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

Note: To use the HANDLER interface to refer to a table's PRIMARY KEY, use the quoted identifier `PRIMARY`: 

HANDLER tbl_name READ `PRIMARY` > (...);

HANDLER is a somewhat low-level statement. For example, it does not provide consistency. That is, HANDLER ... OPEN does not take a snapshot of the table, and does not lock the table. This means that after a HANDLER ... OPEN statement is issued, table data can be modified (by this or any other thread) and these modifications might appear only partially in HANDLER ... NEXT or HANDLER ... PREV scans.

There are several reasons to use the HANDLER interface instead of normal SELECT statements:

14.1.4 INSERT Syntax

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

Or: 

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

Or: 

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...

INSERT inserts new rows into an existing table. The INSERT ... VALUES and INSERT ... SET forms of the statement insert rows based on explicitly specified values. The INSERT ... SELECT form inserts rows selected from another table or tables. The INSERT ... VALUES form with multiple value lists is supported in MySQL 3.22.5 or later. The INSERT ... SET syntax is supported in MySQL 3.22.10 or later. INSERT ... SELECT is discussed further in See section 14.1.4.1 INSERT ... SELECT Syntax.

tbl_name is the table into which rows should be inserted. The columns for which the statement provides values can be specified as follows:

Column values can be given in several ways:

The INSERT statement supports the following modifiers:

If you specify the ON DUPLICATE KEY UPDATE clause (new in MySQL 4.1.0), and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and already contains the value 1, the following two statements have identical effect: 

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)
    -> ON DUPLICATE KEY UPDATE c=c+1;

mysql> UPDATE table SET c=c+1 WHERE a=1;

Note: If column b is unique too, the INSERT would be equivalent to this UPDATE statement instead: 

mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

If a=1 OR b=2 matches several rows, only one row is updated! In general, you should try to avoid using the ON DUPLICATE KEY clause on tables with multiple UNIQUE keys.

As of MySQL 4.1.1, you can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT part of the INSERT ... UPDATE statement. In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted if no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The VALUES() function is meaningful only in INSERT ... UPDATE statements and returns NULL otherwise.

Example: 

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
    -> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

That statement is identical to the following two statements: 

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)
    -> ON DUPLICATE KEY UPDATE c=3;
mysql> INSERT INTO table (a,b,c) VALUES (4,5,6)
    -> ON DUPLICATE KEY UPDATE c=9;

When you use ON DUPLICATE KEY UPDATE, the DELAYED option is ignored.

You can find the value used for an AUTO_INCREMENT column by using the LAST_INSERT_ID() function. From within the C API, use the mysql_insert_id() function. However, note that the two functions do not behave quite identically under all circumstances. The behavior of INSERT statements with respect to AUTO_INCREMENT columns is discussed further in section 13.8.3 Information Functions and section 21.2.3.32 mysql_insert_id().

If you use an INSERT ... VALUES statement with multiple value lists or INSERT ... SELECT, the statement returns an information string in this format: 

Records: 100 Duplicates: 0 Warnings: 0

Records indicates the number of rows processed by the statement. (This is not necessarily the number of rows actually inserted. Duplicates can be non-zero.) Duplicates indicates the number of rows that couldn't be inserted because they would duplicate some existing unique index value. Warnings indicates the number of attempts to insert column values that were problematic in some way. Warnings can occur under any of the following conditions:

If you are using the C API, the information string can be obtained by invoking the mysql_info() function. See section 21.2.3.30 mysql_info().

14.1.4.1 INSERT ... SELECT Syntax

INSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(column_list)]
    SELECT ...

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

For example: 

INSERT INTO tbl_temp2 (fld_id)
    SELECT tbl_temp1.fld_order_id
    FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

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

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

14.1.4.2 INSERT DELAYED Syntax

INSERT DELAYED ...

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

When a client uses INSERT DELAYED, it gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread.

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

There are some constraints on the use of DELAYED:

Note that currently the queued rows are held only in memory until they are inserted into the table. This means that if you terminate mysqld forcibly (for example, with kill -9) or if mysqld dies unexpectedly, any queued rows that have not been written to disk are lost!

The following describes in detail what happens when you use the DELAYED option to INSERT or REPLACE. In this description, the ``thread'' is the thread that received an INSERT DELAYED statement and ``handler'' is the thread that handles all INSERT DELAYED statements for a particular table.

Note that INSERT DELAYED is slower than a normal INSERT if the table is not in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows. This means that you should use INSERT DELAYED only when you are really sure that you need it!

14.1.5 LOAD DATA INFILE Syntax

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

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. For more information about the efficiency of INSERT versus LOAD DATA INFILE and speeding up LOAD DATA INFILE, section 7.2.12 Speed of INSERT Statements.

You can also load data files by using the mysqlimport utility; it operates by sending a LOAD DATA INFILE statement to the server. The --local option causes mysqlimport to read data files from the client host. You can specify the --compress option to get better performance over slow networks if the client and server support the compressed protocol. See section 8.10 The mysqlimport Data Import Program.

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

If you specify the CONCURRENT keyword with a MyISAM table that satisfies the condition for concurrent inserts (that is, it contains no free blocks in the middle), then other threads can retrieve data from the table while LOAD DATA is executing. Using this option affects the performance of LOAD DATA a bit, even if no other thread is using the table at the same time.

If the LOCAL keyword is specified, it is interpreted with respect to the client end of the connection:

LOCAL is available in MySQL 3.22.6 or later.

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

Using LOCAL is 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.

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

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

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

If you are using a version of MySQL older than 3.23.25, you can use this technique only with LOAD DATA LOCAL INFILE.

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

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

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

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

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

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

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

If you want to ignore foreign key constraints during the load operation, you can issue a SET FOREIGN_KEY_CHECKS=0 statement before executing LOAD DATA.

If you use LOAD DATA INFILE on an empty MyISAM table, all non-unique indexes are created in a separate batch (as for REPAIR TABLE). This normally makes LOAD DATA INFILE much faster when you have many indexes. Normally this is very fast, but in some extreme cases, you can create the indexes even faster by turning them off with ALTER TABLE .. DISABLE KEYS before loading the file into the table and using ALTER TABLE .. ENABLE KEYS to re-create the indexes after loading the file. See section 7.2.12 Speed of INSERT Statements.

LOAD DATA INFILE is the complement of SELECT ... INTO OUTFILE. See section 14.1.7 SELECT Syntax. To write data from a table to a file, use SELECT ... INTO OUTFILE. To read the file back into a table, use LOAD DATA INFILE. The syntax of the FIELDS and LINES clauses is the same for both statements. 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' STARTING BY ''

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

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

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

Note: If you have generated the text file on a Windows system, you might have to use LINES TERMINATED BY '\r\n' to read the file properly, because Windows programs typically use two characters as a line terminator. Some programs, such as WordPad, might use \r as a line terminator when writing files. To read such files, use LINES TERMINATED BY '\r'.

If all the lines you want to read in have a common prefix that you want to ignore, you can use LINES STARTING BY 'prefix_string' to skip over the prefix (and anything before it). If a line doesn't include the prefix, the entire line is skipped. Note that prefix_string may be in the middle of the line!

Example: 

mysql> LOAD DATA INFILE '/tmp/test.txt'
    -> INTO TABLE test LINES STARTING BY "xxx";

With this you can read in a file that contains something like: 

xxx"Row",1
something xxx"Row",2

And just get the data ("row",1) and ("row",2).

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

mysql> LOAD DATA INFILE '/tmp/test.txt'
    -> INTO TABLE test IGNORE 1 LINES;

When you use SELECT ... INTO OUTFILE in tandem with LOAD DATA INFILE to write data from a database into a file and then read the file back into the database later, the field- and line-handling options for both statements must match. Otherwise, LOAD DATA INFILE will not interpret the contents of the file properly. Suppose that you use SELECT ... INTO OUTFILE to write a file with fields delimited by commas: 

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

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

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

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

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

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

LOAD DATA INFILE can be used to read files obtained from external sources, too. For example, a file in dBASE format will have fields separated by commas and enclosed within double quotes. If lines in the file are terminated by newlines, the statement shown here illustrates the field- and line-handling options you would use to load the file: 

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
    ->           FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    ->           LINES TERMINATED BY '\n';

Any of the field- or line-handling options can 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, LINES STARTING BY, and LINES TERMINATED BY values can be more than one character. For example, to write lines that are terminated by carriage return/linefeed pairs, or to read a file containing such lines, specify a LINES TERMINATED BY '\r\n' clause.

To read a file containing jokes that are separated by lines consisting of of %%, you can do this 

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

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

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

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

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

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

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

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

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

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

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

If the FIELDS ESCAPED BY character is empty, no characters are escaped and NULL is output as NULL, not \N. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the list just given.

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

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

In certain cases, field- and line-handling options interact:

Handling of NULL values varies according to the FIELDS and LINES options in use:

Some cases are not supported by LOAD DATA INFILE:

The following example loads all columns of the persondata table: 

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

By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list: 

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

You must also specify a column 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 an input line has too many fields, the extra fields are ignored and the number of warnings is incremented.

If an input line has too few fields, the table columns for which input fields are missing are set to their default values. Default value assignment is described in section 14.2.5 CREATE TABLE Syntax.

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

These are the same values that result if you assign an empty string explicitly to a string, numeric, or date or time type explicitly in an INSERT or UPDATE statement.

TIMESTAMP columns are 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.

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!

When the LOAD DATA INFILE statement finishes, it returns an information string in the following format: 

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

If you are using the C API, you can get information about the statement by calling the mysql_info() function. See section 21.2.3.30 mysql_info().

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

From MySQL 4.1.1 on, you can use SHOW WARNINGS to get a list of the first max_error_count warnings as information about what went wrong. See section 14.5.3.20 SHOW WARNINGS Syntax.

Before MySQL 4.1.1, only a warning count is available to indicate that something went wrong. If you get warnings and want to know exactly why you got them, one way to do this is to dump the table into another file using SELECT ... INTO OUTFILE and compare the file to your original input file.

14.1.6 REPLACE Syntax

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [(col_name,...)]
    VALUES ({expr | DEFAULT},...),(...),...

Or: 

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

Or: 

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...

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

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

Values for all columns are taken from the values specified in the REPLACE statement. Any missing columns are set to their default values, just as happens for INSERT. You can't refer to values from the old row and use them in the new row. It appeared that you could do this in some old MySQL versions, but that was a bug that has been corrected.

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

The REPLACE statement returns a count to indicate the number of rows affected. This is the sum of the rows deleted and inserted. If the count is 1 for a single-row REPLACE, a row was inserted and no rows were deleted. If the count is greater than 1, one or more old rows were deleted before the new row was inserted. It is possible for a single row to replace more than one old row if the table contains multiple unique indexes and the new row duplicates values for different old rows in different unique indexes.

The affected-rows count makes it easy to determine whether REPLACE only added a row or whether it also replaced any rows: Check whether the count is 1 (added) or greater (replaced).

If you are using the C API, the affected-rows count can be obtained using the mysql_affected_rows() function.

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

  1. Try to insert the new row into the table
  2. While the insertion fails because a duplicate-key error occurs for a primary or unique key:
    1. Delete from the table the conflicting row that has the duplicate key value
    2. Try again to insert the new row into the table

14.1.7 SELECT Syntax

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr, ...
    [INTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name']
    [FROM table_references
      [WHERE where_definition]
      [GROUP BY {col_name | expr | position}
        [ASC | DESC], ... [WITH ROLLUP]]
      [HAVING where_definition]
      [ORDER BY {col_name | expr | position}
        [ASC | DESC] , ...]
      [LIMIT {[offset,] row_count | row_count OFFSET offset}]
      [PROCEDURE procedure_name(argument_list)]
      [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT is used to retrieve rows selected from one or more tables. Support for UNION statements and subqueries is available as of MySQL 4.0 and 4.1, respectively. See section 14.1.7.2 UNION Syntax and section 14.1.8 Subquery Syntax.

SELECT can also be used to retrieve rows computed without reference to any table. For example: 

mysql> SELECT 1 + 1;
        -> 2

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

Following the SELECT keyword, you can give a number of options that affect the operation of the statement.

The ALL, DISTINCT, and DISTINCTROW options specify whether duplicate rows should be returned. If none of these options are given, the default is ALL (all matching rows are returned). DISTINCT and DISTINCTROW are synonyms and specify that duplicate rows in the result set should be removed.

HIGH_PRIORITY, STRAIGHT_JOIN, and options beginning with SQL_ are MySQL extensions to standard SQL.

14.1.7.1 JOIN Syntax

MySQL supports the following JOIN syntaxes for the table_references part of SELECT statements and multiple-table DELETE and UPDATE statements: 

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

table_reference is defined as: 

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

join_condition is defined as: 

ON conditional_expr | USING (column_list)

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

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

The { OJ ... LEFT OUTER JOIN ...} syntax shown in the preceding list exists only for compatibility with ODBC.

As of MySQL 3.23.12, you can give hints about which index MySQL should use when retrieving information from a table. By specifying USE INDEX (key_list), you can tell MySQL to use only one of the possible indexes to find rows in the table. The alternative syntax IGNORE INDEX (key_list) can be used to tell MySQL to not use some particular index. These hints are useful if EXPLAIN shows that MySQL is using the wrong index from the list of possible indexes.

From MySQL 4.0.9 on, you can also use FORCE INDEX. This acts likes USE INDEX (key_list) but with the addition that a table scan is assumed to be very expensive. In other words, a table scan will only be used if there is no way to use one of the given indexes to find rows in the table.

USE KEY, IGNORE KEY, and FORCE KEY are synonyms for USE INDEX, IGNORE INDEX, and FORCE INDEX.

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

Some join examples: 

mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id);
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
    ->          LEFT JOIN table3 ON table2.id=table3.id;
mysql> SELECT * FROM table1 USE INDEX (key1,key2)
    ->          WHERE key1=1 AND key2=2 AND key3=3;
mysql> SELECT * FROM table1 IGNORE INDEX (key3)
    ->          WHERE key1=1 AND key2=2 AND key3=3;

See section 7.2.8 How MySQL Optimizes LEFT JOIN and RIGHT JOIN.

14.1.7.2 UNION Syntax

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

UNION is used to combine the result from many SELECT statements into one result set. UNION is available from MySQL 4.0.0 on.

Selected columns listed in corresponding positions of each SELECT statement should have the same type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.) The column names used in the first SELECT statement are used as the column names for the results returned.

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

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

The DISTINCT keyword is an optional word (introduced in MySQL 4.0.17). It does nothing, but is allowed in the syntax as required by the SQL standard.

Before MySQL 4.1.2, you cannot mix UNION ALL and UNION DISTINCT in the same query. If you use ALL for one UNION, it is used for all of them. As of MySQL 4.1.2, mixed UNION types are treated such that a DISTINCT union overrides any ALL union to its left. A DISTINCT union can be produced explicitly by using UNION DISTINCT or implicitly by using UNION with no following DISTINCT or ALL keyword.

If you want to use an ORDER BY to sort the entire UNION result, you should use parentheses: 

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

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

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

That limitation has been removed as of MySQL 4.1.1: 

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

14.1.8 Subquery Syntax

A subquery is a SELECT statement inside another statement.

Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific.

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

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

Here is an example of a subquery: 

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

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

The main advantages of subqueries are:

Here is an example statement that shows the major points about subquery syntax as specified by the SQL standard and supported in MySQL: 

DELETE FROM t1
WHERE s11 > ANY
 (SELECT COUNT(*) /* no hint */ FROM t2
 WHERE NOT EXISTS
  (SELECT * FROM t3
   WHERE ROW(5*t2.s1,77)=
    (SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM
     (SELECT * FROM t5) AS t5)));

14.1.8.1 The Subquery as Scalar Operand

In its simplest form (the scalar subquery as opposed to the row or table subqueries that are discussed later), a subquery is a simple operand. Thus, you can use it wherever a column value or literal is legal, and you can expect it to have those characteristics that all operands have: a data type, a length, an indication whether it can be NULL, and so on. For example: 

CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);
SELECT (SELECT s2 FROM t1);

The subquery in this SELECT has a data type of CHAR, a length of 5, a character set and collation equal to the defaults in effect at CREATE TABLE time, and an indication that the value in the column can be NULL. In fact, almost all subqueries can be NULL, because if the table is empty, as in the example, the value of the subquery will be NULL. There are few restrictions.

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

For example, suppose that we make two tables: 

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

Then perform a SELECT: 

SELECT (SELECT s1 FROM t2) FROM t1;

The result will be 2 because there is a row in t2 containing a column s1 that has a value of 2.

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

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

14.1.8.2 Comparisons Using Subqueries

The most common use of a subquery is in the form: 

non_subquery_operand comparison_operator (subquery)

Where comparison_operator is one of: 

=  >  <  >=  <=  <>

For example: 

... 'a' = (SELECT column1 FROM t1)

At one time the only legal place for a subquery was on the right side of a comparison, and you might still find some old DBMSs that insist on this.

Here is an example of a common-form subquery comparison that you cannot do with a join. It finds all the values in table t1 that are equal to a maximum value in table t2: 

SELECT column1 FROM t1
       WHERE column1 = (SELECT MAX(column2) FROM t2);

Here is another example, which again is impossible with a join because it involves aggregating for one of the tables. It finds all rows in table t1 containing a value that occurs twice: 

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

14.1.8.3 Subqueries with ANY, IN, and SOME

Syntax: 

operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)

The ANY keyword, which must follow a comparison operator, means ``return TRUE if the comparison is TRUE for ANY of the rows that the subquery returns.'' For example: 

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

Suppose that there is a row in table t1 containing (10). The expression is TRUE if table t2 contains (21,14,7) because there is a value 7 in t2 that is less than 10. The expression is FALSE if table t2 contains (20,10), or if table t2 is empty. The expression is UNKNOWN if table t2 contains (NULL,NULL,NULL).

The word IN is an alias for = ANY. Thus these two statements are the same: 

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN    (SELECT s1 FROM t2);

However, NOT IN is not an alias for <> ANY, but for <> ALL. See section 14.1.8.4 Subqueries with ALL.

The word SOME is an alias for ANY. Thus these two statements are the same: 

SELECT s1 FROM t1 WHERE s1 <> ANY  (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);

Use of the word SOME is rare, but this example shows why it might be useful. To most people's ears, the English phrase ``a is not equal to any b'' means ``there is no b which is equal to a,'' but that isn't what is meant by the SQL syntax. Using <> SOME instead helps ensure that everyone understands the true meaning of the query.

14.1.8.4 Subqueries with ALL

Syntax: 

operand comparison_operator ALL (subquery)

The word ALL, which must follow a comparison operator, means ``return TRUE if the comparison is TRUE for ALL of the rows that the subquery returns.'' For example: 

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

Suppose that there is a row in table t1 containing (10). The expression is TRUE if table t2 contains (-5,0,+5) because 10 is greater than all three values in t2. The expression is FALSE if table t2 contains (12,6,NULL,-100) because there is a single value 12 in table t2 that is greater than 10. The expression is UNKNOWN if table t2 contains (0,NULL,1).

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

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

But this statement is UNKNOWN when table t2 is empty: 

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

In addition, the following statement is UNKNOWN when table t2 is empty: 

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

In general, tables with NULL values and empty tables are edge cases. When writing subquery code, always consider whether you have taken those two possibilities into account.

NOT IN is an alias for <> ALL. Thus these two statements are the same: 

SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

14.1.8.5 Correlated Subqueries

A correlated subquery is a subquery that contains a reference to a table that also appears in the outer query. For example: 

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

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

Suppose that table t1 contains a row where column1 = 5 and column2 = 6; meanwhile, table t2 contains a row where column1 = 5 and column2 = 7. The simple expression ... WHERE column1 = ANY (SELECT column1 FROM t2) would be TRUE, but in this example, the WHERE clause within the subquery is FALSE (because 7 is not equal to 5), so the subquery as a whole is FALSE.

Scoping rule: MySQL evaluates from inside to outside. For example: 

SELECT column1 FROM t1 AS x
  WHERE x.column1 = (SELECT column1 FROM t2 AS x
    WHERE x.column1 = (SELECT column1 FROM t3
      WHERE x.column2 = t3.column1));

In this statement, x.column2 must be a column in table t2 because SELECT column1 FROM t2 AS x ... renames t2. It is not a column in table t1 because SELECT column1 FROM t1 ... is an outer query that is farther out.

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

For certain cases, a correlated subquery is optimized. For example: 

val IN (SELECT key_val FROM tbl_name WHERE correlated_condition)

Otherwise, they are inefficient and likely to be slow. Rewriting the query as a join might improve performance.

14.1.8.6 EXISTS and NOT EXISTS

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

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

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

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

The last example is a double-nested NOT EXISTS query. That is, it has a NOT EXISTS clause within a NOT EXISTS clause. Formally, it answers the question ``does a city exist with a store that is not in Stores?'' But it's easier to say that a nested NOT EXISTS answers the question ``is x TRUE for all y?''

14.1.8.7 Row Subqueries

The discussion to this point has been of column (or scalar) subqueries: subqueries that return a single column value. A row subquery is a subquery variant that returns a single row value and can thus return more than one column value. Here are two examples: 

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

The queries here are both TRUE if table t2 has a row where column1 = 1 and column2 = 2.

The expressions (1,2) and ROW(1,2) are sometimes called row constructors. The two are equivalent. They are legal in other contexts, too. For example, the following two statements are semantically equivalent (although currently only the second one can be optimized): 

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

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

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

14.1.8.8 Subqueries in the FROM clause

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

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

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

For illustration, assume that you have this table: 

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

Here's how to use a subquery in the FROM clause, using the example table: 

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

Result: 2, '2', 4.0.

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

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

But this query will provide the desired information: 

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

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

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

Subquery in the FROM clause will be executed (that is, derived temporary tables will be built) even for the EXPLAIN statement, because upper level queries need information about all tables during optimization phase.

14.1.8.9 Subquery Errors

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

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

14.1.8.10 Optimizing Subqueries

Development is ongoing, so no optimization tip is reliable for the long term. Some interesting tricks that you might want to play with are:

These tricks might cause programs to go faster or slower. Using MySQL facilities like the BENCHMARK() function, you can get an idea about what helps in your own situation. Don't worry too much about transforming to joins except for compatibility with older versions of MySQL before 4.1 that do not support subqueries.

Some optimizations that MySQL itself makes are:

There is a chapter titled ``How MySQL Transforms Subqueries'' in the MySQL Internals Manual. You can obtain this document by downloading the MySQL source package and looking for a file named `internals.texi' in the `Docs' directory.

14.1.8.11 Rewriting Subqueries as Joins for Earlier MySQL Versions

Before MySQL 4.1, only nested queries of the form INSERT ... SELECT ... and REPLACE ... SELECT ... are supported. The IN() construct can be used in other contexts to test membership in a set of values.

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

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

This can be rewritten as: 

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

The queries: 

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

Can be rewritten as: 

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

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

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

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

14.1.9 TRUNCATE Syntax

TRUNCATE TABLE tbl_name

TRUNCATE TABLE empties a table completely. Logically, this is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.

For InnoDB, TRUNCATE TABLE is mapped to DELETE, so there is no difference. For other storage engines, TRUNCATE TABLE differs from DELETE FROM ... in the following ways from MySQL 4.0 and up:

In MySQL 3.23, TRUNCATE TABLE is mapped to COMMIT; DELETE FROM tbl_name, so it behaves like DELETE. See section 14.1.1 DELETE Syntax.

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

14.1.10 UPDATE Syntax

Single-table syntax: 

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

Multiple-table syntax: 

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

The UPDATE statement 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. The LIMIT clause places a limit on the number of rows that can be updated.

The UPDATE statement supports the following modifiers:

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

mysql> UPDATE persondata SET age=age+1;

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

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

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

If you update a column that has been declared NOT NULL by setting to NULL, the column is set to the default value appropriate for the column type and the warning count is incremented. The default value is 0 for numeric types, the empty string ('') for string types, and the ``zero'' value for date and time types.

UPDATE returns the number of rows that were actually changed. In MySQL 3.22 or later, the mysql_info() C API function returns the number of rows that were matched and updated and the number of warnings that occurred during the UPDATE.

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

If an UPDATE statement includes an ORDER BY clause, the rows are updated in the order specified by the clause. ORDER BY can be used from MySQL 4.0.0.

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

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

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

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

Before MySQL 4.0.18, you need the UPDATE privilege for all tables used in a multiple-table UPDATE, even if they were not updated. As of MySQL 4.0.18, you need only the SELECT privilege for any columns that are read but not modified.

If you use a multiple-table UPDATE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement will fail and roll back. Instead, update a single table and rely on the ON UPDATE capabilities that InnoDB provides to cause the other tables to be modified accordingly.

14.2 Data Definition Statements

14.2.1 ALTER DATABASE Syntax

ALTER DATABASE db_name
    alter_specification [, alter_specification] ...

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

ALTER DATABASE allows you to change the overall characteristics of a database. 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 section 11 Character Set Support.

ALTER DATABASE was added in MySQL 4.1.1.

14.2.2 ALTER TABLE Syntax

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 section 14.2.5 CREATE TABLE Syntax.

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

ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, then the original table is deleted and the new one is renamed. 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 section 14.5.3.1 SET Syntax.

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

See section A.7.1 Problems with ALTER TABLE.

14.2.3 CREATE DATABASE Syntax

CREATE DATABASE [IF NOT EXISTS] db_name
    [create_specification [, create_specification] ...]

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

CREATE DATABASE creates a database with the given name. To use CREATE DATABASE, you need the CREATE privilege on the database.

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

As of MySQL 4.1.1, create_specification options 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 section 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).

You can also use the mysqladmin program to create databases. See section 8.4 mysqladmin, Administering a MySQL Server.

14.2.4 CREATE INDEX Syntax

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 section 14.2.2 ALTER TABLE Syntax. 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 section 14.2.5 CREATE TABLE Syntax. CREATE INDEX allows you to add indexes to existing tables.

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

For CHAR and VARCHAR columns, indexes can be created that use only part of a column, using col_name(length) syntax to index 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 section 7.4.5 How MySQL Uses Indexes.

FULLTEXT indexes can index only CHAR, VARCHAR, and TEXT columns, and only in MyISAM tables. FULLTEXT indexes are available in MySQL 3.23.23 or later. section 13.6 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 section 19 Spatial Extensions in MySQL.

14.2.5 CREATE TABLE Syntax

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

Or: 

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

create_definition:
    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 section 10.2 Database, Table, Index, Column, and Alias Names. By default, the table is created in the current database. An error occurs if the table already exists, if there is no current database, or if the database does not exist.

In MySQL 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:

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

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

For general information on the properties of the various column types, see section 12 Column Types. For information about spatial column types, see section 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:

Storage Engine Description
BDB Transaction-safe tables with page locking. See section 15.4 The BDB (BerkeleyDB) Storage Engine.
BerkeleyDB An alias for BDB.
HEAP The data for this table is stored only in memory. See section 15.3 The MEMORY (HEAP) Storage Engine.
ISAM The original MySQL storage engine. See section 15.5 The ISAM Storage Engine.
InnoDB Transaction-safe tables with row locking and foreign keys. See section 16 The InnoDB Storage Engine.
MEMORY An alias for HEAP. (Actually, as of MySQL 4.1, MEMORY is the preferred term.)
MERGE A collection of MyISAM tables used as one table. See section 15.2 The MERGE Storage Engine.
MRG_MyISAM An alias for MERGE.
MyISAM The binary portable storage engine that is the improved replacement for ISAM. See section 15.1 The MyISAM Storage Engine.

See section 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:

AUTO_INCREMENT
The initial AUTO_INCREMENT value for the table. This works for MyISAM only. To set the first auto-increment value for an InnoDB table, insert a dummy row with a value one less than the desired value after creating the table, and then 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. When you create a MyISAM table, MySQL uses the product of the MAX_ROWS and AVG_ROW_LENGTH options to decide how big the resulting table will be. If you don't specify either option, the maximum size for a table will be 4GB (or 2GB if your operating system only supports 2GB tables). The reason for this is just to keep down the pointer sizes to make the index smaller and faster if you don't really need big files. If you want all your tables to be able to grow above the 4GB limit and are willing to have your smaller tables slightly slower and larger than necessary, you may increase the default pointer size by setting the myisam_data_pointer_size system variable, which was added in MySQL 4.1.2. See section 5.2.3 Server System Variables.
CHECKSUM
Set this to 1 if you want MySQL to maintain a live checksum for all rows (that is, a checksum that MySQL updates automatically as the table changes). This makes the table a little slower to update, but also makes it easier to find corrupted tables. The CHECKSUM TABLE statement reports the checksum. (MyISAM only.)
COMMENT
A comment for your table, up to 60 characters long.
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 option to 1 if you want to have smaller indexes. This usually makes updates slower and reads faster. Setting the option to 0 disables all packing of keys. Setting it to DEFAULT (MySQL 4.0) tells the storage engine to only pack long CHAR/VARCHAR columns. (MyISAM and ISAM only.) If you don't use PACK_KEYS, the default is to only pack strings, not numbers. If you use PACK_KEYS=1, numbers will be packed as well. When packing binary number keys, MySQL uses prefix compression: This means that if you have many equal keys on two consecutive rows, all following ``same'' keys will usually only take two bytes (including the pointer to the row). Compare this to the ordinary case where the following keys will take storage_size_for_key + pointer_size (where the pointer size is usually 4). Conversely, you will get a big benefit from prefix compression only if you have many numbers that are the same. If all keys are totally different, you will use one byte more per key, if the key isn't a key that can have NULL values. (In this case, the packed key length will be stored in the same byte that is used to mark if a key is NULL.)
PASSWORD
Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version.
DELAY_KEY_WRITE
Set this to 1 if you want to delay key updates for the table until the table is closed. (MyISAM only.)
ROW_FORMAT
Defines how the rows should be stored. Currently this option works only with MyISAM tables. The option value can FIXED or DYNAMIC for static or variable-length row format. myisampack sets the type to COMPRESSED. See section 15.1.3 MyISAM Table Storage Formats.
RAID_TYPE
The RAID_TYPE option can help you to exceed the 2GB/4GB limit for the MyISAM data file (not the index file) on operating systems that don't support big files. This option is unnecessary and not recommended for filesystems that support big files. You can get more speed from the I/O bottleneck by putting RAID directories on different physical disks. For now, the only allowed RAID_TYPE is STRIPED. 1 and RAID0 are aliases for STRIPED. If you specify the RAID_TYPE option for a MyISAM table, specify the RAID_CHUNKS and RAID_CHUNKSIZE options as well. The maximum RAID_CHUNKS value is 255. MyISAM will create RAID_CHUNKS subdirectories named `00', `01', `02', ... `09', `0a', `0b', ... in the database directory. In each of these directories, MyISAM will create a file `tbl_name.MYD'. When writing data to the data file, 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. RAID_TYPE works on any operating system, as long as you have built MySQL with the --with-raid option to configure. To determine whether a server supports RAID tables, use SHOW VARIABLES LIKE 'have_raid' to see whether the variable value is YES.
UNION
UNION is used when you want to use a collection of identical tables as one. This works only with MERGE tables. See section 15.2 The MERGE Storage Engine. For the moment, you must have SELECT, UPDATE, and DELETE privileges for the tables you map to a MERGE table. Originally, all used tables had to be in the same database as the MERGE table itself. This restriction has been lifted as of MySQL 4.1.1.
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. This option was introduced in MySQL 4.0.0. See section 15.2 The MERGE Storage Engine.
DATA DIRECTORY
INDEX DIRECTORY
By using DATA DIRECTORY='directory' or INDEX DIRECTORY='directory' you can specify where the MyISAM storage engine should put a table's data file and index file. Note that the directory should be a full path to the directory (not a relative path). These options work only for MyISAM tables from MySQL 4.0 on, when you are not using the --skip-symbolic-links option. Your operating system must also have a working, thread-safe realpath() call. See section 7.6.1.2 Using Symbolic Links for Tables on Unix.

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.

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.

14.2.5.1 Silent Column Specification Changes

In some cases, MySQL silently changes column specifications from those given in a CREATE TABLE or ALTER TABLE statement:

To see whether MySQL used a column type other than the one you specified, issue a DESCRIBE or SHOW CREATE TABLE statement after creating or altering your table.

Certain other column type changes can occur if you compress a table using myisampack. See section 15.1 The MyISAM Storage Engine.

See section 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:

AUTO_INCREMENT
The initial AUTO_INCREMENT value for the table. This works for MyISAM only. To set the first auto-increment value for an InnoDB table, insert a dummy row with a value one less than the desired value after creating the table, and then 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. When you create a MyISAM table, MySQL uses the product of the MAX_ROWS and AVG_ROW_LENGTH options to decide how big the resulting table will be. If you don't specify either option, the maximum size for a table will be 4GB (or 2GB if your operating system only supports 2GB tables). The reason for this is just to keep down the pointer sizes to make the index smaller and faster if you don't really need big files. If you want all your tables to be able to grow above the 4GB limit and are willing to have your smaller tables slightly slower and larger than necessary, you may increase the default pointer size by setting the myisam_data_pointer_size system variable, which was added in MySQL 4.1.2. See section 5.2.3 Server System Variables.
CHECKSUM
Set this to 1 if you want MySQL to maintain a live checksum for all rows (that is, a checksum that MySQL updates automatically as the table changes). This makes the table a little slower to update, but also makes it easier to find corrupted tables. The CHECKSUM TABLE statement reports the checksum. (MyISAM only.)
COMMENT
A comment for your table, up to 60 characters long.
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 option to 1 if you want to have smaller indexes. This usually makes updates slower and reads faster. Setting the option to 0 disables all packing of keys. Setting it to DEFAULT (MySQL 4.0) tells the storage engine to only pack long CHAR/VARCHAR columns. (MyISAM and ISAM only.) If you don't use PACK_KEYS, the default is to only pack strings, not numbers. If you use PACK_KEYS=1, numbers will be packed as well. When packing binary number keys, MySQL uses prefix compression: This means that if you have many equal keys on two consecutive rows, all following ``same'' keys will usually only take two bytes (including the pointer to the row). Compare this to the ordinary case where the following keys will take storage_size_for_key + pointer_size (where the pointer size is usually 4). Conversely, you will get a big benefit from prefix compression only if you have many numbers that are the same. If all keys are totally different, you will use one byte more per key, if the key isn't a key that can have NULL values. (In this case, the packed key length will be stored in the same byte that is used to mark if a key is NULL.)
PASSWORD
Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version.
DELAY_KEY_WRITE
Set this to 1 if you want to delay key updates for the table until the table is closed. (MyISAM only.)
ROW_FORMAT
Defines how the rows should be stored. Currently this option works only with MyISAM tables. The option value can FIXED or DYNAMIC for static or variable-length row format. myisampack sets the type to COMPRESSED. See section 15.1.3 MyISAM Table Storage Formats.
RAID_TYPE
The RAID_TYPE option can help you to exceed the 2GB/4GB limit for the MyISAM data file (not the index file) on operating systems that don't support big files. This option is unnecessary and not recommended for filesystems that support big files. You can get more speed from the I/O bottleneck by putting RAID directories on different physical disks. For now, the only allowed RAID_TYPE is STRIPED. 1 and RAID0 are aliases for STRIPED. If you specify the RAID_TYPE option for a MyISAM table, specify the RAID_CHUNKS and RAID_CHUNKSIZE options as well. The maximum RAID_CHUNKS value is 255. MyISAM will create RAID_CHUNKS subdirectories named `00', `01', `02', ... `09', `0a', `0b', ... in the database directory. In each of these directories, MyISAM will create a file `tbl_name.MYD'. When writing data to the data file, 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. RAID_TYPE works on any operating system, as long as you have built MySQL with the --with-raid option to configure. To determine whether a server supports RAID tables, use SHOW VARIABLES LIKE 'have_raid' to see whether the variable value is YES.
UNION
UNION is used when you want to use a collection of identical tables as one. This works only with MERGE tables. See section 15.2 The MERGE Storage Engine. For the moment, you must have SELECT, UPDATE, and DELETE privileges for the tables you map to a MERGE table. Originally, all used tables had to be in the same database as the MERGE table itself. This restriction has been lifted as of MySQL 4.1.1.
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. This option was introduced in MySQL 4.0.0. See section 15.2 The MERGE Storage Engine.
DATA DIRECTORY
INDEX DIRECTORY
By using DATA DIRECTORY='directory' or INDEX DIRECTORY='directory' you can specify where the MyISAM storage engine should put a table's data file and index file. Note that the directory should be a full path to the directory (not a relative path). These options work only for MyISAM tables from MySQL 4.0 on, when you are not using the --skip-symbolic-links option. Your operating system must also have a working, thread-safe realpath() call. See section 7.6.1.2 Using Symbolic Links for Tables on Unix.

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.

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.

14.2.5.1 Silent Column Specification Changes

In some cases, MySQL silently changes column specifications from those given in a CREATE TABLE or ALTER TABLE statement:

To see whether MySQL used a column type other than the one you specified, issue a DESCRIBE or SHOW CREATE TABLE statement after creating or altering your table.

Certain other column type changes can occur if you compress a table using myisampack. See section 15.1 The MyISAM Storage Engine.

See section 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:

AUTO_INCREMENT
The initial AUTO_INCREMENT value for the table. This works for MyISAM only. To set the first auto-increment value for an InnoDB table, insert a dummy row with a value one less than the desired value after creating the table, and then 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. When you create a MyISAM table, MySQL uses the product of the MAX_ROWS and AVG_ROW_LENGTH options to decide how big the resulting table will be. If you don't specify either option, the maximum size for a table will be 4GB (or 2GB if your operating system only supports 2GB tables). The reason for this is just to keep down the pointer sizes to make the index smaller and faster if you don't really need big files. If you want all your tables to be able to grow above the 4GB limit and are willing to have your smaller tables slightly slower and larger than necessary, you may increase the default pointer size by setting the myisam_data_pointer_size system variable, which was added in MySQL 4.1.2. See section 5.2.3 Server System Variables.
CHECKSUM
Set this to 1 if you want MySQL to maintain a live checksum for all rows (that is, a checksum that MySQL updates automatically as the table changes). This makes the table a little slower to update, but also makes it easier to find corrupted tables. The CHECKSUM TABLE statement reports the checksum. (MyISAM only.)
COMMENT
A comment for your table, up to 60 characters long.
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 option to 1 if you want to have smaller indexes. This usually makes updates slower and reads faster. Setting the option to 0 disables all packing of keys. Setting it to DEFAULT (MySQL 4.0) tells the storage engine to only pack long CHAR/VARCHAR columns. (MyISAM and ISAM only.) If you don't use PACK_KEYS, the default is to only pack strings, not numbers. If you use PACK_KEYS=1, numbers will be packed as well. When packing binary number keys, MySQL uses prefix compression: This means that if you have many equal keys on two consecutive rows, all following ``same'' keys will usually only take two bytes (including the pointer to the row). Compare this to the ordinary case where the following keys will take storage_size_for_key + pointer_size (where the pointer size is usually 4). Conversely, you will get a big benefit from prefix compression only if you have many numbers that are the same. If all keys are totally different, you will use one byte more per key, if the key isn't a key that can have NULL values. (In this case, the packed key length will be stored in the same byte that is used to mark if a key is NULL.)
PASSWORD
Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version.
DELAY_KEY_WRITE
Set this to 1 if you want to delay key updates for the table until the table is closed. (MyISAM only.)
ROW_FORMAT
Defines how the rows should be stored. Currently this option works only with MyISAM tables. The option value can FIXED or DYNAMIC for static or variable-length row format. myisampack sets the type to COMPRESSED. See section 15.1.3 MyISAM Table Storage Formats.
RAID_TYPE
The RAID_TYPE option can help you to exceed the 2GB/4GB limit for the MyISAM data file (not the index file) on operating systems that don't support big files. This option is unnecessary and not recommended for filesystems that support big files. You can get more speed from the I/O bottleneck by putting RAID directories on different physical disks. For now, the only allowed RAID_TYPE is STRIPED. 1 and RAID0 are aliases for STRIPED. If you specify the RAID_TYPE option for a MyISAM table, specify the RAID_CHUNKS and RAID_CHUNKSIZE options as well. The maximum RAID_CHUNKS value is 255. MyISAM will create RAID_CHUNKS subdirectories named `00', `01', `02', ... `09', `0a', `0b', ... in the database directory. In each of these directories, MyISAM will create a file `tbl_name.MYD'. When writing data to the data file, 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. RAID_TYPE works on any operating system, as long as you have built MySQL with the --with-raid option to configure. To determine whether a server supports RAID tables, use SHOW VARIABLES LIKE 'have_raid' to see whether the variable value is YES.
UNION
UNION is used when you want to use a collection of identical tables as one. This works only with MERGE tables. See section 15.2 The MERGE Storage Engine. For the moment, you must have SELECT, UPDATE, and DELETE privileges for the tables you map to a MERGE table. Originally, all used tables had to be in the same database as the MERGE table itself. This restriction has been lifted as of MySQL 4.1.1.
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. This option was introduced in MySQL 4.0.0. See section 15.2 The MERGE Storage Engine.
DATA DIRECTORY
INDEX DIRECTORY
By using DATA DIRECTORY='directory' or INDEX DIRECTORY='directory' you can specify where the MyISAM storage engine should put a table's data file and index file. Note that the directory should be a full path to the directory (not a relative path). These options work only for MyISAM tables from MySQL 4.0 on, when you are not using the --skip-symbolic-links option. Your operating system must also have a working, thread-safe realpath() call. See section 7.6.1.2 Using Symbolic Links for Tables on Unix.

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.

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.

14.2.5.1 Silent Column Specification Changes

In some cases, MySQL silently changes column specifications from those given in a CREATE TABLE or ALTER TABLE statement:

To see whether MySQL used a column type other than the one you specified, issue a DESCRIBE or SHOW CREATE TABLE statement after creating or altering your table.

Certain other column type changes can occur if you compress a table using myisampack. See section 15.1 The MyISAM Storage Engine.

See section 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:

AUTO_INCREMENT
The initial AUTO_INCREMENT value for the table. This works for MyISAM only. To set the first auto-increment value for an InnoDB table, insert a dummy row with a value one less than the desired value after creating the table, and then 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. When you create a MyISAM table, MySQL uses the product of the MAX_ROWS and AVG_ROW_LENGTH options to decide how big the resulting table will be. If you don't specify either option, the maximum size for a table will be 4GB (or 2GB if your operating system only supports 2GB tables). The reason for this is just to keep down the pointer sizes to make the index smaller and faster if you don't really need big files. If you want all your tables to be able to grow above the 4GB limit and are willing to have your smaller tables slightly slower and larger than necessary, you may increase the default pointer size by setting the myisam_data_pointer_size system variable, which was added in MySQL 4.1.2. See section 5.2.3 Server System Variables.
CHECKSUM
Set this to 1 if you want MySQL to maintain a live checksum for all rows (that is, a checksum that MySQL updates automatically as the table changes). This makes the table a little slower to update, but also makes it easier to find corrupted tables. The CHECKSUM TABLE statement reports the checksum. (MyISAM only.)
COMMENT
A comment for your table, up to 60 characters long.
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 option to 1 if you want to have smaller indexes. This usually makes updates slower and reads faster. Setting the option to 0 disables all packing of keys. Setting it to DEFAULT (MySQL 4.0) tells the storage engine to only pack long CHAR/VARCHAR columns. (MyISAM and ISAM only.) If you don't use PACK_KEYS, the default is to only pack strings, not numbers. If you use PACK_KEYS=1, numbers will be packed as well. When packing binary number keys, MySQL uses prefix compression: This means that if you have many equal keys on two consecutive rows, all following ``same'' keys will usually only take two bytes (including the pointer to the row). Compare this to the ordinary case where the following keys will take storage_size_for_key + pointer_size (where the pointer size is usually 4). Conversely, you will get a big benefit from prefix compression only if you have many numbers that are the same. If all keys are totally different, you will use one byte more per key, if the key isn't a key that can have NULL values. (In this case, the packed key length will be stored in the same byte that is used to mark if a key is NULL.)
PASSWORD
Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version.
DELAY_KEY_WRITE
Set this to 1 if you want to delay key updates for the table until the table is closed. (MyISAM only.)
ROW_FORMAT
Defines how the rows should be stored. Currently this option works only with MyISAM tables. The option value can FIXED or DYNAMIC for static or variable-length row format. myisampack sets the type to COMPRESSED. See section 15.1.3 MyISAM Table Storage Formats.
RAID_TYPE
The RAID_TYPE option can help you to exceed the 2GB/4GB limit for the MyISAM data file (not the index file) on operating systems that don't support big files. This option is unnecessary and not recommended for filesystems that support big files. You can get more speed from the I/O bottleneck by putting RAID directories on different physical disks. For now, the only allowed RAID_TYPE is STRIPED. 1 and RAID0 are aliases for STRIPED. If you specify the RAID_TYPE option for a MyISAM table, specify the RAID_CHUNKS and RAID_CHUNKSIZE options as well. The maximum RAID_CHUNKS value is 255. MyISAM will create RAID_CHUNKS subdirectories named `00', `01', `02', ... `09', `0a', `0b', ... in the database directory. In each of these directories, MyISAM will create a file `tbl_name.MYD'. When writing data to the data file, 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. RAID_TYPE works on any operating system, as long as you have built MySQL with the --with-raid option to configure. To determine whether a server supports RAID tables, use SHOW VARIABLES LIKE 'have_raid' to see whether the variable value is YES.
UNION
UNION is used when you want to use a collection of identical tables as one. This works only with MERGE tables. See section 15.2 The MERGE Storage Engine. For the moment, you must have SELECT, UPDATE, and DELETE privileges for the tables you map to a MERGE table. Originally, all used tables had to be in the same database as the MERGE table itself. This restriction has been lifted as of MySQL 4.1.1.
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. This option was introduced in MySQL 4.0.0. See section 15.2 The MERGE Storage Engine.
DATA DIRECTORY
INDEX DIRECTORY
By using DATA DIRECTORY='directory' or INDEX DIRECTORY='directory' you can specify where the MyISAM storage engine should put a table's data file and index file. Note that the directory should be a full path to the directory (not a relative path). These options work only for MyISAM tables from MySQL 4.0 on, when you are not using the --skip-symbolic-links option. Your operating system must also have a working, thread-safe realpath() call. See section 7.6.1.2 Using Symbolic Links for Tables on Unix.

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.

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.

14.2.5.1 Silent Column Specification Changes

In some cases, MySQL silently changes column specifications from those given in a CREATE TABLE or ALTER TABLE statement:

To see whether MySQL used a column type other than the one you specified, issue a DESCRIBE or SHOW CREATE TABLE statement after creating or altering your table.

Certain other column type changes can occur if you compress a table using myisampack. See section 15.1 The MyISAM Storage Engine.

See section 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:

AUTO_INCREMENT
The initial AUTO_INCREMENT value for the table. This works for MyISAM only. To set the first auto-increment value for an InnoDB table, insert a dummy row with a value one less than the desired value after creating the table, and then 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. When you create a MyISAM table, MySQL uses the product of the MAX_ROWS and AVG_ROW_LENGTH options to decide how big the resulting table will be. If you don't specify either option, the maximum size for a table will be 4GB (or 2GB if your operating system only supports 2GB tables). The reason for this is just to keep down the pointer sizes to make the index smaller and faster if you don't really need big files. If you want all your tables to be able to grow above the 4GB limit and are willing to have your smaller tables slightly slower and larger than necessary, you may increase the default pointer size by setting the myisam_data_pointer_size system variable, which was added in MySQL 4.1.2. See section 5.2.3 Server System Variables.
CHECKSUM
Set this to 1 if you want MySQL to maintain a live checksum for all rows (that is, a checksum that MySQL updates automatically as the table changes). This makes the table a little slower to update, but also makes it easier to find corrupted tables. The CHECKSUM TABLE statement reports the checksum. (MyISAM only.)
COMMENT
A comment for your table, up to 60 characters long.
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 option to 1 if you want to have smaller indexes. This usually makes updates slower and reads faster. Setting the option to 0 disables all packing of keys. Setting it to DEFAULT (MySQL 4.0) tells the storage engine to only pack long CHAR/VARCHAR columns. (MyISAM and ISAM only.) If you don't use PACK_KEYS, the default is to only pack strings, not numbers. If you use PACK_KEYS=1, numbers will be packed as well. When packing binary number keys, MySQL uses prefix compression: This means that if you have many equal keys on two consecutive rows, all following ``same'' keys will usually only take two bytes (including the pointer to the row). Compare this to the ordinary case where the following keys will take storage_size_for_key + pointer_size (where the pointer size is usually 4). Conversely, you will get a big benefit from prefix compression only if you have many numbers that are the same. If all keys are totally different, you will use one byte more per key, if the key isn't a key that can have NULL values. (In this case, the packed key length will be stored in the same byte that is used to mark if a key is NULL.)
PASSWORD
Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version.
DELAY_KEY_WRITE
Set this to 1 if you want to delay key updates for the table until the table is closed. (MyISAM only.)
ROW_FORMAT
Defines how the rows should be stored. Currently this option works only with MyISAM tables. The option value can FIXED or DYNAMIC for static or variable-length row format. myisampack sets the type to COMPRESSED. See section 15.1.3 MyISAM Table Storage Formats.
RAID_TYPE
The RAID_TYPE option can help you to exceed the 2GB/4GB limit for the MyISAM data file (not the index file) on operating systems that don't support big files. This option is unnecessary and not recommended for filesystems that support big files. You can get more speed from the I/O bottleneck by putting RAID directories on different physical disks. For now, the only allowed RAID_TYPE is STRIPED. 1 and RAID0 are aliases for STRIPED. If you specify the RAID_TYPE option for a MyISAM table, specify the RAID_CHUNKS and RAID_CHUNKSIZE options as well. The maximum RAID_CHUNKS value is 255. MyISAM will create RAID_CHUNKS subdirectories named `00', `01', `02', ... `09', `0a', `0b', ... in the database directory. In each of these directories, MyISAM will create a file `tbl_name.MYD'. When writing data to the data file, 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. RAID_TYPE works on any operating system, as long as you have built MySQL with the --with-raid option to configure. To determine whether a server supports RAID tables, use SHOW VARIABLES LIKE 'have_raid' to see whether the variable value is YES.
UNION
UNION is used when you want to use a collection of identical tables as one. This works only with MERGE tables. See section 15.2 The MERGE Storage Engine. For the moment, you must have SELECT, UPDATE, and DELETE privileges for the tables you map to a MERGE table. Originally, all used tables had to be in the same database as the MERGE table itself. This restriction has been lifted as of MySQL 4.1.1.
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. This option was introduced in MySQL 4.0.0. See section 15.2 The MERGE Storage Engine.
DATA DIRECTORY
INDEX DIRECTORY
By using DATA DIRECTORY='directory' or INDEX DIRECTORY='directory' you can specify where the MyISAM storage engine should put a table's data file and index file. Note that the directory should be a full path to the directory (not a relative path). These options work only for MyISAM tables from MySQL 4.0 on, when you are not using the --skip-symbolic-links option. Your operating system must also have a working, thread-safe realpath() call. See section 7.6.1.2 Using Symbolic Links for Tables on Unix.

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.

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.

14.2.5.1 Silent Column Specification Changes

In some cases, MySQL silently changes column specifications from those given in a CREATE TABLE or ALTER TABLE statement:

To see whether MySQL used a column type other than the one you specified, issue a DESCRIBE or SHOW CREATE TABLE statement after creating or altering your table.

Certain other column type changes can occur if you compress a table using myisampack. See section 15.1 The MyISAM Storage Engine.

See section 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:

AUTO_INCREMENT
The initial AUTO_INCREMENT value for the table. This works for MyISAM only. To set the first auto-increment value for an InnoDB table, insert a dummy row with a value one less than the desired value after creating the table, and then 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. When you create a MyISAM table, MySQL uses the product of the MAX_ROWS and AVG_ROW_LENGTH options to decide how big the resulting table will be. If you don't specify either option, the maximum size for a table will be 4GB (or 2GB if your operating system only supports 2GB tables). The reason for this is just to keep down the pointer sizes to make the index smaller and faster if you don't really need big files. If you want all your tables to be able to grow above the 4GB limit and are willing to have your smaller tables slightly slower and larger than necessary, you may increase the default pointer size by setting the myisam_data_pointer_size system variable, which was added in MySQL 4.1.2. See section 5.2.3 Server System Variables.
CHECKSUM
Set this to 1 if you want MySQL to maintain a live checksum for all rows (that is, a checksum that MySQL updates automatically as the table changes). This makes the table a little slower to update, but also makes it easier to find corrupted tables. The CHECKSUM TABLE statement reports the checksum. (MyISAM only.)
COMMENT
A comment for your table, up to 60 characters long.
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 option to 1 if you want to have smaller indexes. This usually makes updates slower and reads faster. Setting the option to 0 disables all packing of keys. Setting it to DEFAULT (MySQL 4.0) tells the storage engine to only pack long CHAR/VARCHAR columns. (MyISAM and ISAM only.) If you don't use PACK_KEYS, the default is to only pack strings, not numbers. If you use PACK_KEYS=1, numbers will be packed as well. When packing binary number keys, MySQL uses prefix compression: This means that if you have many equal keys on two consecutive rows, all following ``same'' keys will usually only take two bytes (including the pointer to the row). Compare this to the ordinary case where the following keys will take storage_size_for_key + pointer_size (where the pointer size is usually 4). Conversely, you will get a big benefit from prefix compression only if you have many numbers that are the same. If all keys are totally different, you will use one byte more per key, if the key isn't a key that can have NULL values. (In this case, the packed key length will be stored in the same byte that is used to mark if a key is NULL.)
PASSWORD
Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version.
DELAY_KEY_WRITE
Set this to 1 if you want to delay key updates for the table until the table is closed. (MyISAM only.)
ROW_FORMAT
Defines how the rows should be stored. Currently this option works only with MyISAM tables. The option value can FIXED or DYNAMIC for static or variable-length row format. myisampack sets the type to COMPRESSED. See section 15.1.3 MyISAM Table Storage Formats.
RAID_TYPE
The RAID_TYPE option can help you to exceed the 2GB/4GB limit for the MyISAM data file (not the index file) on operating systems that don't support big files. This option is unnecessary and not recommended for filesystems that support big files. You can get more speed from the I/O bottleneck by putting RAID directories on different physical disks. For now, the only allowed RAID_TYPE is STRIPED. 1 and RAID0 are aliases for STRIPED. If you specify the RAID_TYPE option for a MyISAM table, specify the RAID_CHUNKS and RAID_CHUNKSIZE options as well. The maximum RAID_CHUNKS value is 255. MyISAM will create RAID_CHUNKS subdirectories named `00', `01', `02', ... `09', `0a', `0b', ... in the database directory. In each of these directories, MyISAM will create a file `tbl_name.MYD'. When writing data to the data file, 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. RAID_TYPE works on any operating system, as long as you have built MySQL with the --with-raid option to configure. To determine whether a server supports RAID tables, use SHOW VARIABLES LIKE 'have_raid' to see whether the variable value is YES.
UNION
UNION is used when you want to use a collection of identical tables as one. This works only with MERGE tables. See section 15.2 The MERGE Storage Engine. For the moment, you must have SELECT, UPDATE, and DELETE privileges for the tables you map to a MERGE table. Originally, all used tables had to be in the same database as the MERGE table itself. This restriction has been lifted as of MySQL 4.1.1.
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. This option was introduced in MySQL 4.0.0. See section 15.2 The MERGE Storage Engine.
DATA DIRECTORY
INDEX DIRECTORY
By using DATA DIRECTORY='directory' or INDEX DIRECTORY='directory' you can specify where the MyISAM storage engine should put a table's data file and index file. Note that the directory should be a full path to the directory (not a relative path). These options work only for MyISAM tables from MySQL 4.0 on, when you are not using the --skip-symbolic-links option. Your operating system must also have a working, thread-safe realpath() call. See section 7.6.1.2 Using Symbolic Links for Tables on Unix.

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.

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.

14.2.5.1 Silent Column Specification Changes

In some cases, MySQL silently changes column specifications from those given in a CREATE TABLE or ALTER TABLE statement:

To see whether MySQL used a column type other than the one you specified, issue a DESCRIBE or SHOW CREATE TABLE statement after creating or altering your table.

Certain other column type changes can occur if you compress a table using myisampack. See section 15.1 The MyISAM Storage Engine.

See section 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:

AUTO_INCREMENT
The initial AUTO_INCREMENT value for the table. This works for MyISAM only. To set the first auto-increment value for an InnoDB table, insert a dummy row with a value one less than the desired value after creating the table, and then 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. When you create a MyISAM table, MySQL uses the product of the MAX_ROWS and AVG_ROW_LENGTH options to decide how big the resulting table will be. If you don't specify either option, the maximum size for a table will be 4GB (or 2GB if your operating system only supports 2GB tables). The reason for this is just to keep down the pointer sizes to make the index smaller and faster if you don't really need big files. If you want all your tables to be able to grow above the 4GB limit and are willing to have your smaller tables slightly slower and larger than necessary, you may increase the default pointer size by setting the myisam_data_pointer_size system variable, which was added in MySQL 4.1.2. See section 5.2.3 Server System Variables.
CHECKSUM
Set this to 1 if you want MySQL to maintain a live checksum for all rows (that is, a checksum that MySQL updates automatically as the table changes). This makes the table a little slower to update, but also makes it easier to find corrupted tables. The CHECKSUM TABLE statement reports the checksum. (MyISAM only.)
COMMENT
A comment for your table, up to 60 characters long.
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 option to 1 if you want to have smaller indexes. This usually makes updates slower and reads faster. Setting the option to 0 disables all packing of keys. Setting it to DEFAULT (MySQL 4.0) tells the storage engine to only pack long CHAR/VARCHAR columns. (MyISAM and ISAM only.) If you don't use PACK_KEYS, the default is to only pack strings, not numbers. If you use PACK_KEYS=1, numbers will be packed as well. When packing binary number keys, MySQL uses prefix compression: This means that if you have many equal keys on two consecutive rows, all following ``same'' keys will usually only take two bytes (including the pointer to the row). Compare this to the ordinary case where the following keys will take storage_size_for_key + pointer_size (where the pointer size is usually 4). Conversely, you will get a big benefit from prefix compression only if you have many numbers that are the same. If all keys are totally different, you will use one byte more per key, if the key isn't a key that can have NULL values. (In this case, the packed key length will be stored in the same byte that is used to mark if a key is NULL.)
PASSWORD
Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version.
DELAY_KEY_WRITE
Set this to 1 if you want to delay key updates for the table until the table is closed. (MyISAM only.)
ROW_FORMAT
Defines how the rows should be stored. Currently this option works only with MyISAM tables. The option value can FIXED or DYNAMIC for static or variable-length row format. myisampack sets the type to COMPRESSED. See section 15.1.3 MyISAM Table Storage Formats.
RAID_TYPE
The RAID_TYPE option can help you to exceed the 2GB/4GB limit for the MyISAM data file (not the index file) on operating systems that don't support big files. This option is unnecessary and not recommended for filesystems that support big files. You can get more speed from the I/O bottleneck by putting RAID directories on different physical disks. For now, the only allowed RAID_TYPE is STRIPED. 1 and RAID0 are aliases for STRIPED. If you specify the RAID_TYPE option for a MyISAM table, specify the RAID_CHUNKS and RAID_CHUNKSIZE options as well. The maximum RAID_CHUNKS value is 255. MyISAM will create RAID_CHUNKS subdirectories named `00', `01', `02', ... `09', `0a', `0b', ... in the database directory. In each of these directories, MyISAM will create a file `tbl_name.MYD'. When writing data to the data file, 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. RAID_TYPE works on any operating system, as long as you have built MySQL with the --with-raid option to configure. To determine whether a server supports RAID tables, use SHOW VARIABLES LIKE 'have_raid' to see whether the variable value is YES.
UNION
UNION is used when you want to use a collection of identical tables as one. This works only with MERGE tables. See section 15.2 The MERGE Storage Engine. For the moment, you must have SELECT, UPDATE, and DELETE privileges for the tables you map to a MERGE table. Originally, all used tables had to be in the same database as the MERGE table itself. This restriction has been lifted as of MySQL 4.1.1.
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. This option was introduced in MySQL 4.0.0. See section 15.2 The MERGE Storage Engine.
DATA DIRECTORY
INDEX DIRECTORY
By using DATA DIRECTORY='directory' or INDEX DIRECTORY='directory' you can specify where the MyISAM storage engine should put a table's data file and index file. Note that the directory should be a full path to the directory (not a relative path). These options work only for MyISAM tables from MySQL 4.0 on, when you are not using the --skip-symbolic-links option. Your operating system must also have a working, thread-safe realpath() call. See section 7.6.1.2 Using Symbolic Links for Tables on Unix.

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.

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.

14.2.5.1 Silent Column Specification Changes

In some cases, MySQL silently changes column specifications from those given in a CREATE TABLE or ALTER TABLE statement:

To see whether MySQL used a column type other than the one you specified, issue a DESCRIBE or SHOW CREATE TABLE statement after creating or altering your table.

Certain other column type changes can occur if you compress a table using myisampack. See section 15.1 The MyISAM Storage Engine.

See section 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:

AUTO_INCREMENT
The initial AUTO_INCREMENT value for the table. This works for MyISAM only. To set the first auto-increment value for an InnoDB table, insert a dummy row with a value one less than the desired value after creating the table, and then 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. When you create a MyISAM table, MySQL uses the product of the MAX_ROWS and AVG_ROW_LENGTH options to decide how big the resulting table will be. If you don't specify either option, the maximum size for a table will be 4GB (or 2GB if your operating system only supports 2GB tables). The reason for this is just to keep down the pointer sizes to make the index smaller and faster if you don't really need big files. If you want all your tables to be able to grow above the 4GB limit and are willing to have your smaller tables slightly slower and larger than necessary, you may increase the default pointer size by setting the myisam_data_pointer_size system variable, which was added in MySQL 4.1.2. See section 5.2.3 Server System Variables.
CHECKSUM
Set this to 1 if you want MySQL to maintain a live checksum for all rows (that is, a checksum that MySQL updates automatically as the table changes). This makes the table a little slower to update, but also makes it easier to find corrupted tables. The CHECKSUM TABLE statement reports the checksum. (MyISAM only.)
COMMENT
A comment for your table, up to 60 characters long.
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 option to 1 if you want to have smaller indexes. This usually makes updates slower and reads faster. Setting the option to 0 disables all packing of keys. Setting it to DEFAULT (MySQL 4.0) tells the storage engine to only pack long CHAR/VARCHAR columns. (MyISAM and ISAM only.) If you don't use PACK_KEYS, the default is to only pack strings, not numbers. If you use PACK_KEYS=1, numbers will be packed as well. When packing binary number keys, MySQL uses prefix compression: This means that if you have many equal keys on two consecutive rows, all following ``same'' keys will usually only take two bytes (including the pointer to the row). Compare this to the ordinary case where the following keys will take storage_size_for_key + pointer_size (where the pointer size is usually 4). Conversely, you will get a big benefit from prefix compression only if you have many numbers that are the same. If all keys are totally different, you will use one byte more per key, if the key isn't a key that can have NULL values. (In this case, the packed key length will be stored in the same byte that is used to mark if a key is NULL.)
PASSWORD
Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version.
DELAY_KEY_WRITE
Set this to 1 if you want to delay key updates for the table until the table is closed. (MyISAM only.)
ROW_FORMAT
Defines how the rows should be stored. Currently this option works only with MyISAM tables. The option value can FIXED or DYNAMIC for static or variable-length row format. myisampack sets the type to COMPRESSED. See section 15.1.3 MyISAM Table Storage Formats.
RAID_TYPE
The RAID_TYPE option can help you to exceed the 2GB/4GB limit for the MyISAM data file (not the index file) on operating systems that don't support big files. This option is unnecessary and not recommended for filesystems that support big files. You can get more speed from the I/O bottleneck by putting RAID directories on different physical disks. For now, the only allowed RAID_TYPE is STRIPED. 1 and RAID0 are aliases for STRIPED. If you specify the RAID_TYPE option for a MyISAM table, specify the RAID_CHUNKS and RAID_CHUNKSIZE options as well. The maximum RAID_CHUNKS value is 255. MyISAM will create RAID_CHUNKS subdirectories named `00', `01', `02', ... `09', `0a', `0b', ... in the database directory. In each of these directories, MyISAM will create a file `tbl_name.MYD'. When writing data to the data file, 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. RAID_TYPE works on any operating system, as long as you have built MySQL with the --with-raid option to configure. To determine whether a server supports RAID tables, use SHOW VARIABLES LIKE 'have_raid' to see whether the variable value is YES.
UNION
UNION is used when you want to use a collection of identical tables as one. This works only with MERGE tables. See section 15.2 The MERGE Storage Engine. For the moment, you must have SELECT, UPDATE, and DELETE privileges for the tables you map to a MERGE table. Originally, all used tables had to be in the same database as the MERGE table itself. This restriction has been lifted as of MySQL 4.1.1.
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. This option was introduced in MySQL 4.0.0. See section 15.2 The MERGE Storage Engine.
DATA DIRECTORY
INDEX DIRECTORY
By using DATA DIRECTORY='directory' or INDEX DIRECTORY='directory' you can specify where the MyISAM storage engine should put a table's data file and index file. Note that the directory should be a full path to the directory (not a relative path). These options work only for MyISAM tables from MySQL 4.0 on, when you are not using the --skip-symbolic-links option. Your operating system must also have a working, thread-safe realpath() call. See section 7.6.1.2 Using Symbolic Links for Tables on Unix.

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.

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.

14.2.5.1 Silent Column Specification Changes

In some cases, MySQL silently changes column specifications from those given in a CREATE TABLE or ALTER TABLE statement:

To see whether MySQL used a column type other than the one you specified, issue a DESCRIBE or SHOW CREATE TABLE statement after creating or altering your table.

Certain other column type changes can occur if you compress a table using myisampack. See section 15.1 The MyISAM Storage Engine.

See section 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:

AUTO_INCREMENT
The initial AUTO_INCREMENT value for the table. This works for MyISAM only. To set the first auto-increment value for an InnoDB table, insert a dummy row with a value one less than the desired value after creating the table, and then 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. When you create a MyISAM table, MySQL uses the product of the MAX_ROWS and AVG_ROW_LENGTH options to decide how big the resulting table will be. If you don't specify either option, the maximum size for a table will be 4GB (or 2GB if your operating system only supports 2GB tables). The reason for this is just to keep down the pointer sizes to make the index smaller and faster if you don't really need big files. If you want all your tables to be able to grow above the 4GB limit and are willing to have your smaller tables slightly slower and larger than necessary, you may increase the default pointer size by setting the myisam_data_pointer_size system variable, which was added in MySQL 4.1.2. See section 5.2.3 Server System Variables.
CHECKSUM
Set this to 1 if you want MySQL to maintain a live checksum for all rows (that is, a checksum that MySQL updates automatically as the table changes). This makes the table a little slower to update, but also makes it easier to find corrupted tables. The CHECKSUM TABLE statement reports the checksum. (MyISAM only.)
COMMENT
A comment for your table, up to 60 characters long.
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 option to 1 if you want to have smaller indexes. This usually makes updates slower and reads faster. Setting the option to 0 disables all packing of keys. Setting it to DEFAULT (MySQL 4.0) tells the storage engine to only pack long CHAR/VARCHAR columns. (MyISAM and ISAM only.) If you don't use PACK_KEYS, the default is to only pack strings, not numbers. If you use PACK_KEYS=1, numbers will be packed as well. When packing binary number keys, MySQL uses prefix compression: This means that if you have many equal keys on two consecutive rows, all following ``same'' keys will usually only take two bytes (including the pointer to the row). Compare this to the ordinary case where the following keys will take storage_size_for_key + pointer_size (where the pointer size is usually 4). Conversely, you will get a big benefit from prefix compression only if you have many numbers that are the same. If all keys are totally different, you will use one byte more per key, if the key isn't a key that can have NULL values. (In this case, the packed key length will be stored in the same byte that is used to mark if a key is NULL.)
PASSWORD
Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version.
DELAY_KEY_WRITE
Set this to 1 if you want to delay key updates for the table until the table is closed. (MyISAM only.)
ROW_FORMAT
Defines how the rows should be stored. Currently this option works only with MyISAM tables. The option value can FIXED or DYNAMIC for static or variable-length row format. myisampack sets the type to COMPRESSED. See section 15.1.3 MyISAM Table Storage Formats.
RAID_TYPE
The RAID_TYPE option can help you to exceed the 2GB/4GB limit for the MyISAM data file (not the index file) on operating systems that don't support big files. This option is unnecessary and not recommended for filesystems that support big files. You can get more speed from the I/O bottleneck by putting RAID directories on different physical disks. For now, the only allowed RAID_TYPE is STRIPED. 1 and RAID0 are aliases for STRIPED. If you specify the RAID_TYPE option for a MyISAM table, specify the RAID_CHUNKS and RAID_CHUNKSIZE options as well. The maximum RAID_CHUNKS value is 255. MyISAM will create RAID_CHUNKS subdirectories named `00', `01', `02', ... `09', `0a', `0b', ... in the database directory. In each of these directories, MyISAM will create a file `tbl_name.MYD'. When writing data to the data file, 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. RAID_TYPE works on any operating system, as long as you have built MySQL with the --with-raid option to configure. To determine whether a server supports RAID tables, use SHOW VARIABLES LIKE 'have_raid' to see whether the variable value is YES.
UNION
UNION is used when you want to use a collection of identical tables as one. This works only with MERGE tables. See section 15.2 The MERGE Storage Engine. For the moment, you must have SELECT, UPDATE, and DELETE privileges for the tables you map to a MERGE table. Originally, all used tables had to be in the same database as the MERGE table itself. This restriction has been lifted as of MySQL 4.1.1.
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. This option was introduced in MySQL 4.0.0. See section 15.2 The MERGE Storage Engine.
DATA DIRECTORY
INDEX DIRECTORY
By using DATA DIRECTORY='directory' or INDEX DIRECTORY='directory' you can specify where the MyISAM storage engine should put a table's data file and index file. Note that the directory should be a full path to the directory (not a relative path). These options work only for MyISAM tables from MySQL 4.0 on, when you are not using the --skip-symbolic-links option. Your operating system must also have a working, thread-safe realpath() call. See section 7.6.1.2 Using Symbolic Links for Tables on Unix.

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.

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.

14.2.5.1 Silent Column Specification Changes

In some cases, MySQL silently changes column specifications from those given in a CREATE TABLE or ALTER TABLE statement:

To see whether MySQL used a column type other than the one you specified, issue a DESCRIBE or SHOW CREATE TABLE statement after creating or altering your table.

Certain other column type changes can occur if you compress a table using myisampack. See section 15.1 The MyISAM Storage Engine.

See section 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:

AUTO_INCREMENT
The initial AUTO_INCREMENT value for the table. This works for MyISAM only. To set the first auto-increment value for an InnoDB table, insert a dummy row with a value one less than the desired value after creating the table, and then 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. When you create a MyISAM table, MySQL uses the product of the MAX_ROWS and AVG_ROW_LENGTH options to decide how big the resulting table will be. If you don't specify either option, the maximum size for a table will be 4GB (or 2GB if your operating system only supports 2GB tables). The reason for this is just to keep down the pointer sizes to make the index smaller and faster if you don't really need big files. If you want all your tables to be able to grow above the 4GB limit and are willing to have your smaller tables slightly slower and larger than necessary, you may increase the default pointer size by setting the myisam_data_pointer_size system variable, which was added in MySQL 4.1.2. See section 5.2.3 Server System Variables.
CHECKSUM
Set this to 1 if you want MySQL to maintain a live checksum for all rows (that is, a checksum that MySQL updates automatically as the table changes). This makes the table a little slower to update, but also makes it easier to find corrupted tables. The CHECKSUM TABLE statement reports the checksum. (MyISAM only.)
COMMENT
A comment for your table, up to 60 characters long.
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 option to 1 if you want to have smaller indexes. This usually makes updates slower and reads faster. Setting the option to 0 disables all packing of keys. Setting it to DEFAULT (MySQL 4.0) tells the storage engine to only pack long CHAR/VARCHAR columns. (MyISAM and ISAM only.) If you don't use PACK_KEYS, the default is to only pack strings, not numbers. If you use PACK_KEYS=1, numbers will be packed as well. When packing binary number keys, MySQL uses prefix compression: This means that if you have many equal keys on two consecutive rows, all following ``same'' keys will usually only take two bytes (including the pointer to the row). Compare this to the ordinary case where the following keys will take storage_size_for_key + pointer_size (where the pointer size is usually 4). Conversely, you will get a big benefit from prefix compression only if you have many numbers that are the same. If all keys are totally different, you will use one byte more per key, if the key isn't a key that can have NULL values. (In this case, the packed key length will be stored in the same byte that is used to mark if a key is NULL.)
PASSWORD
Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version.
DELAY_KEY_WRITE
Set this to 1 if you want to delay key updates for the table until the table is closed. (MyISAM only.)
ROW_FORMAT
Defines how the rows should be stored. Currently this option works only with MyISAM tables. The option value can FIXED or DYNAMIC for static or variable-length row format. myisampack sets the type to COMPRESSED. See section 15.1.3 MyISAM Table Storage Formats.
RAID_TYPE
The RAID_TYPE option can help you to exceed the 2GB/4GB limit for the MyISAM data file (not the index file) on operating systems that don't support big files. This option is unnecessary and not recommended for filesystems that support big files. You can get more speed from the I/O bottleneck by putting RAID directories on different physical disks. For now, the only allowed RAID_TYPE is STRIPED. 1 and RAID0 are aliases for STRIPED. If you specify the RAID_TYPE option for a MyISAM table, specify the RAID_CHUNKS and RAID_CHUNKSIZE options as well. The maximum RAID_CHUNKS value is 255. MyISAM will create RAID_CHUNKS subdirectories named `00', `01', `02', ... `09', `0a', `0b', ... in the database directory. In each of these directories, MyISAM will create a file `tbl_name.MYD'. When writing data to the data file, 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. RAID_TYPE works on any operating system, as long as you have built MySQL with the --with-raid option to configure. To determine whether a server supports RAID tables, use SHOW VARIABLES LIKE 'have_raid' to see whether the variable value is YES.
UNION
UNION is used when you want to use a collection of identical tables as one. This works only with MERGE tables. See section 15.2 The MERGE Storage Engine. For the moment, you must have SELECT, UPDATE, and DELETE privileges for the tables you map to a MERGE table. Originally, all used tables had to be in the same database as the MERGE table itself. This restriction has been lifted as of MySQL 4.1.1.
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. This option was introduced in MySQL 4.0.0. See section 15.2 The MERGE Storage Engine.
DATA DIRECTORY
INDEX DIRECTORY
By using DATA DIRECTORY='directory' or INDEX DIRECTORY='directory' you can specify where the MyISAM storage engine should put a table's data file and index file. Note that the directory should be a full path to the directory (not a relative path). These options work only for MyISAM tables from MySQL 4.0 on, when you are not using the --skip-symbolic-links option. Your operating system must also have a working, thread-safe realpath() call. See section 7.6.1.2 Using Symbolic Links for Tables on Unix.

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.

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.

14.2.5.1 Silent Column Specification Changes

In some cases, MySQL silently changes column specifications from those given in a CREATE TABLE or ALTER TABLE statement:

To see whether MySQL used a column type other than the one you specified, issue a DESCRIBE or SHOW CREATE TABLE statement after creating or altering your table.

Certain other column type changes can occur if you compress a table using myisampack. See section 15.1 The MyISAM Storage Engine.

See section 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:

AUTO_INCREMENT
The initial AUTO_INCREMENT value for the table. This works for MyISAM only. To set the first auto-increment value for an InnoDB table, insert a dummy row with a value one less than the desired value after creating the table, and then 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. When you create a MyISAM table, MySQL uses the product of the MAX_ROWS and AVG_ROW_LENGTH options to decide how big the resulting table will be. If you don't specify either option, the maximum size for a table will be 4GB (or 2GB if your operating system only supports 2GB tables). The reason for this is just to keep down the pointer sizes to make the index smaller and faster if you don't really need big files. If you want all your tables to be able to grow above the 4GB limit and are willing to have your smaller tables slightly slower and larger than necessary, you may increase the default pointer size by setting the myisam_data_pointer_size system variable, which was added in MySQL 4.1.2. See section 5.2.3 Server System Variables.
CHECKSUM
Set this to 1 if you want MySQL to maintain a live checksum for all rows (that is, a checksum that MySQL updates automatically as the table changes). This makes the table a little slower to update, but also makes it easier to find corrupted tables. The CHECKSUM TABLE statement reports the checksum. (MyISAM only.)
COMMENT
A comment for your table, up to 60 characters long.
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 option to 1 if you want to have smaller indexes. This usually makes updates slower and reads faster. Setting the option to 0 disables all packing of keys. Setting it to DEFAULT (MySQL 4.0) tells the storage engine to only pack long CHAR/VARCHAR columns. (MyISAM and ISAM only.) If you don't use PACK_KEYS, the default is to only pack strings, not numbers. If you use PACK_KEYS=1, numbers will be packed as well. When packing binary number keys, MySQL uses prefix compression: This means that if you have many equal keys on two consecutive rows, all following ``same'' keys will usually only take two bytes (including the pointer to the row). Compare this to the ordinary case where the following keys will take storage_size_for_key + pointer_size (where the pointer size is usually 4). Conversely, you will get a big benefit from prefix compression only if you have many numbers that are the same. If all keys are totally different, you will use one byte more per key, if the key isn't a key that can have NULL values. (In this case, the packed key length will be stored in the same byte that is used to mark if a key is NULL.)
PASSWORD
Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version.
DELAY_KEY_WRITE
Set this to 1 if you want to delay key updates for the table until the table is closed. (MyISAM only.)
ROW_FORMAT
Defines how the rows should be stored. Currently this option works only with MyISAM tables. The option value can FIXED or DYNAMIC for static or variable-length row format. myisampack sets the type to COMPRESSED. See section 15.1.3 MyISAM Table Storage Formats.
RAID_TYPE
The RAID_TYPE option can help you to exceed the 2GB/4GB limit for the MyISAM data file (not the index file) on operating systems that don't support big files. This option is unnecessary and not recommended for filesystems that support big files. You can get more speed from the I/O bottleneck by putting RAID directories on different physical disks. For now, the only allowed RAID_TYPE is STRIPED. 1 and RAID0 are aliases for STRIPED. If you specify the RAID_TYPE option for a MyISAM table, specify the RAID_CHUNKS and RAID_CHUNKSIZE options as well. The maximum RAID_CHUNKS value is 255. MyISAM will create RAID_CHUNKS subdirectories named `00', `01', `02', ... `09', `0a', `0b', ... in the database directory. In each of these directories, MyISAM will create a file `tbl_name.MYD'. When writing data to the data file, 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. RAID_TYPE works on any operating system, as long as you have built MySQL with the --with-raid option to configure. To determine whether a server supports RAID tables, use SHOW VARIABLES LIKE 'have_raid' to see whether the variable value is YES.
UNION
UNION is used when you want to use a collection of identical tables as one. This works only with MERGE tables. See section 15.2 The MERGE Storage Engine. For the moment, you must have SELECT, UPDATE, and DELETE privileges for the tables you map to a MERGE table. Originally, all used tables had to be in the same database as the MERGE table itself. This restriction has been lifted as of MySQL 4.1.1.
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. This option was introduced in MySQL 4.0.0. See section 15.2 The MERGE Storage Engine.
DATA DIRECTORY
INDEX DIRECTORY
By using DATA DIRECTORY='directory' or INDEX DIRECTORY='directory' you can specify where the MyISAM storage engine should put a table's data file and index file. Note that the directory should be a full path to the directory (not a relative path). These options work only for MyISAM tables from MySQL 4.0 on, when you are not using the --skip-symbolic-links option. Your operating system must also have a working, thread-safe realpath() call. See section 7.6.1.2 Using Symbolic Links for Tables on Unix.

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.

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.

14.2.5.1 Silent Column Specification Changes

In some cases, MySQL silently changes column specifications from those given in a CREATE TABLE or ALTER TABLE statement:

To see whether MySQL used a column type other than the one you specified, issue a DESCRIBE or SHOW CREATE TABLE statement after creating or altering your table.

Certain other column type changes can occur if you compress a table using myisampack. See section 15.1 The MyISAM Storage Engine.

See section 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:

AUTO_INCREMENT
The initial AUTO_INCREMENT value for the table. This works for MyISAM only. To set the first auto-increment value for an InnoDB table, insert a dummy row with a value one less than the desired value after creating the table, and then 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. When you create a MyISAM table, MySQL uses the product of the MAX_ROWS and AVG_ROW_LENGTH options to decide how big the resulting table will be. If you don't specify either option, the maximum size for a table will be 4GB (or 2GB if your operating system only supports 2GB tables). The reason for this is just to keep down the pointer sizes to make the index smaller and faster if you don't really need big files. If you want all your tables to be able to grow above the 4GB limit and are willing to have your smaller tables slightly slower and larger than necessary, you may increase the default pointer size by setting the myisam_data_pointer_size system variable, which was added in MySQL 4.1.2. See section 5.2.3 Server System Variables.
CHECKSUM
Set this to 1 if you want MySQL to maintain a live checksum for all rows (that is, a checksum that MySQL updates automatically as the table changes). This makes the table a little slower to update, but also makes it easier to find corrupted tables. The CHECKSUM TABLE statement reports the checksum. (MyISAM only.)
COMMENT
A comment for your table, up to 60 characters long.
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 option to 1 if you want to have smaller indexes. This usually makes updates slower and reads faster. Setting the option to 0 disables all packing of keys. Setting it to DEFAULT (MySQL 4.0) tells the storage engine to only pack long CHAR/VARCHAR columns. (MyISAM and ISAM only.) If you don't use PACK_KEYS, the default is to only pack strings, not numbers. If you use PACK_KEYS=1, numbers will be packed as well. When packing binary number keys, MySQL uses prefix compression: This means that if you have many equal keys on two consecutive rows, all following ``same'' keys will usually only take two bytes (including the pointer to the row). Compare this to the ordinary case where the following keys will take storage_size_for_key + pointer_size (where the pointer size is usually 4). Conversely, you will get a big benefit from prefix compression only if you have many numbers that are the same. If all keys are totally different, you will use one byte more per key, if the key isn't a key that can have NULL values. (In this case, the packed key length will be stored in the same byte that is used to mark if a key is NULL.)
PASSWORD
Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version.
DELAY_KEY_WRITE
Set this to 1 if you want to delay key updates for the table until the table is closed. (MyISAM only.)
ROW_FORMAT
Defines how the rows should be stored. Currently this option works only with MyISAM tables. The option value can FIXED or DYNAMIC for static or variable-length row format. myisampack sets the type to COMPRESSED. See section 15.1.3 MyISAM Table Storage Formats.
RAID_TYPE
The RAID_TYPE option can help you to exceed the 2GB/4GB limit for the MyISAM data file (not the index file) on operating systems that don't support big files. This option is unnecessary and not recommended for filesystems that support big files. You can get more speed from the I/O bottleneck by putting RAID directories on different physical disks. For now, the only allowed RAID_TYPE is STRIPED. 1 and RAID0 are aliases for STRIPED. If you specify the RAID_TYPE option for a MyISAM table, specify the RAID_CHUNKS and RAID_CHUNKSIZE options as well. The maximum RAID_CHUNKS value is 255. MyISAM will create RAID_CHUNKS subdirectories named `00', `01', `02', ... `09', `0a', `0b', ... in the database directory. In each of these directories, MyISAM will create a file `tbl_name.MYD'. When writing data to the data file, 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. RAID_TYPE works on any operating system, as long as you have built MySQL with the --with-raid option to configure. To determine whether a server supports RAID tables, use SHOW VARIABLES LIKE 'have_raid' to see whether the variable value is YES.
UNION
UNION is used when you want to use a collection of identical tables as one. This works only with MERGE tables. See section 15.2 The MERGE Storage Engine. For the moment, you must have SELECT, UPDATE, and DELETE privileges for the tables you map to a MERGE table. Originally, all used tables had to be in the same database as the MERGE table itself. This restriction has been lifted as of MySQL 4.1.1.
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. This option was introduced in MySQL 4.0.0. See section 15.2 The MERGE Storage Engine.
DATA DIRECTORY
INDEX DIRECTORY
By using DATA DIRECTORY='directory' or INDEX DIRECTORY='directory' you can specify where the MyISAM storage engine should put a table's data file and index file. Note that the directory should be a full path to the directory (not a relative path). These options work only for MyISAM tables from MySQL 4.0 on, when you are not using the --skip-symbolic-links option. Your operating system must also have a working, thread-safe realpath() call. See section 7.6.1.2 Using Symbolic Links for Tables on Unix.

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.

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.

14.2.5.1 Silent Column Specification Changes

In some cases, MySQL silently changes column specifications from those given in a CREATE TABLE or ALTER TABLE statement:

To see whether MySQL used a column type other than the one you specified, issue a DESCRIBE or SHOW CREATE TABLE statement after creating or altering your table.

Certain other column type changes can occur if you compress a table using myisampack. See section 15.1 The MyISAM Storage Engine.

See section 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:

AUTO_INCREMENT
The initial AUTO_INCREMENT value for the table. This works for MyISAM only. To set the first auto-increment value for an InnoDB table, insert a dummy row with a value one less than the desired value after creating the table, and then 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. When you create a MyISAM table, MySQL uses the product of the MAX_ROWS and AVG_ROW_LENGTH options to decide how big the resulting table will be. If you don't specify either option, the maximum size for a table will be 4GB (or 2GB if your operating system only supports 2GB tables). The reason for this is just to keep down the pointer sizes to make the index smaller and faster if you don't really need big files. If you want all your tables to be able to grow above the 4GB limit and are willing to have your smaller tables slightly slower and larger than necessary, you may increase the default pointer size by setting the myisam_data_pointer_size system variable, which was added in MySQL 4.1.2. See section 5.2.3 Server System Variables.
CHECKSUM
Set this to 1 if you want MySQL to maintain a live checksum for all rows (that is, a checksum that MySQL updates automatically as the table changes). This makes the table a little slower to update, but also makes it easier to find corrupted tables. The CHECKSUM TABLE statement reports the checksum. (MyISAM only.)
COMMENT
A comment for your table, up to 60 characters long.
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 option to 1 if you want to have smaller indexes. This usually makes updates slower and reads faster. Setting the option to 0 disables all packing of keys. Setting it to DEFAULT (MySQL 4.0) tells the storage engine to only pack long CHAR/VARCHAR columns. (MyISAM and ISAM only.) If you don't use PACK_KEYS, the default is to only pack strings, not numbers. If you use PACK_KEYS=1, numbers will be packed as well. When