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

An integer column may have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value. Typically this is value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1. Section 9.1.3.31, “mysql_insert_id()”.

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 table_name (without a WHERE) in AUTOCOMMIT mode, the sequence starts over for all table types except InnoDB. Section 7.5.12.5, “How an AUTO_INCREMENT Column Works in InnoDB ”.

Note: there can be only one AUTO_INCREMENT column per table, it must be indexed and it can't have a DEFAULT value. In MySQL Version 3.23, an AUTO_INCREMENT column will work properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers "wrap" over from positive to negative and also to ensure that one doesn't accidentally get an AUTO_INCREMENT column that contains 0.

In MyISAM and BDB tables you can specify AUTO_INCREMENT secondary column in a multiple-column key. Section 3.6.9, “Using AUTO_INCREMENT”.

To make MySQL compatible with some ODBC applications, you can find the AUTO_INCREMENT value for the last inserted row with the following query:

SELECT * FROM tbl_name WHERE auto_col IS NULL

NULL values are handled differently for TIMESTAMP columns than for other column types. You cannot store a literal NULL in a TIMESTAMP column; setting the column to NULL sets it to the current date and time. Because TIMESTAMP columns behave this way, the NULL and NOT NULL attributes do not apply in the normal way and are ignored if you specify them.

On the other hand, to make it easier for MySQL clients to use TIMESTAMP columns, the server reports that such columns may be assigned NULL values (which is true), even though TIMESTAMP never actually will contain a NULL value. You can see this when you use DESCRIBE tbl_name to get a description of your table.

Note that setting a TIMESTAMP column to 0 is not the same as setting it to NULL, because 0 is a valid TIMESTAMP value.

A DEFAULT value has to be a constant, it cannot be a function or an expression.

If no DEFAULT value is specified for a column, MySQL automatically assigns one, as follows.

If the column may take NULL as a value, the default value is NULL.

If the column is declared as NOT NULL, the default value depends on the column type:

Default values must be constants. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE.

A comment for a column may be specified with the COMMENT option. The comment is displayed by the SHOW CREATE TABLE statement, and by SHOW FULL COLUMNS. This option is available as of MySQL 4.1. (It is allowed but ignored in earlier versions.)

KEY is normally a synonym for INDEX. From version 4.1, the key attribute PRIMARY KEY may also be specified as just KEY. This was implemented for compatibility with other databases.

In MySQL, a UNIQUE key can have only distinct values. An error occurs if you try to add a new row with a key that matches an existing row.

A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, it will be done implicitly (and quietly). In MySQL the key is named PRIMARY. A table can have only one PRIMARY KEY. If you don't have a PRIMARY KEY and some applications ask for the PRIMARY KEY in your tables, MySQL will return the first UNIQUE key, which doesn't have any NULL columns, as the PRIMARY KEY.

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

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

If the PRIMARY or UNIQUE key consists of only one column and this is of type integer, you can also refer to it as _rowid (new in Version 3.23.11).

If you don't assign a name to an index that is not a PRIMARY KEY, the index will be assigned the same name as the first index_col_name, with an optional suffix (_2, _3, ...) to make it unique. You can see index names for a table using SHOW INDEX FROM tbl_name. Section 4.5.7.1, “Retrieving information about Database, Tables, Columns, and Indexes ”.

Only the MyISAM, InnoDB, and BDB table types support indexes on columns that can have NULL values. In other cases you must declare such columns NOT NULL or an error results.

With col_name(length) syntax in an index specification, you can create an index that uses only the first length bytes of a CHAR or VARCHAR column. This can make the index file much smaller. Section 5.4.4, “Column Indexes ”.

Only the MyISAM and (as of MySQL 4.0.14) InnoDB table types support indexing on BLOB and TEXT columns. When putting an index on a BLOB or TEXT column you MUST always specify the length of the index, up to 255 bytes. For example:

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

An index_col_name specification may end with ASC or DESC. These keywords are allowed for compatibility with other database systems where ascending or descending index value storage can be specified. In MySQL, the keywords are parsed but ignoredd; index values are always stored in ascending order.

When you use ORDER BY or GROUP BY with a TEXT or BLOB column, the server sorts values using only the initial number of bytes indicated by the max_sort_length server variable. Section 6.2.3.2, “The BLOB and TEXT Types ”.

In MySQL Version 3.23.23 or later, you can also create special FULLTEXT indexes. They are used for full-text search. Only the MyISAM table type supports FULLTEXT indexes. They can be created only from CHAR, VARCHAR, and TEXT columns. Indexing always happens over the entire column; partial indexing is not supported. See Section 6.8, “MySQL Full-text Search ” for details of operation.

In MySQL Version 3.23.44 or later, InnoDB tables support checking of foreign key constraints. Section 7.5, “InnoDB Tables ”. Note that the FOREIGN KEY syntax in InnoDB is more restrictive than the syntax presented above: The columns of the referenced table must always be explicitly named. InnoDB supports both ON DELETE and ON UPDATE actions on foreign keys as of MySQL 3.23.50 and 4.0.8, respectively. See the InnoDB manual section for the precise syntax. Section 7.5.5.2, “FOREIGN KEY Constraints ”. For other table types, MySQL Server does parse the FOREIGN KEY, CHECK, and REFERENCES syntax in CREATE TABLE commands, but without further action being taken. Section 1.7.4.5, “Foreign Keys ”.

For MyISAM and ISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte. The maximum record length in bytes can be calculated as follows:

row length = 1
             + (sum of column lengths)
             + (number of NULL columns + delete_flag + 7)/8
             + (number of variable-length columns)

delete_flag is 1 for tables with static record format. Static tables use a bit in the row record for a flag that indicates whether the row has been deleted. delete_flag is 0 for dynamic tables because the flag is stored in the dynamic row header.

These calculations do not apply for InnoDB tables, for which storage size is not different for NULL columns compared to NOT NULL columns.

The table_options and SELECT options are only implemented in MySQL Version 3.23 and above.

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

Chapter 7, MySQL Table Types .

If a table type is specified, and that particular type is not available, MySQL will use MyISAM instead. For example, if a table definition includes the TYPE=BDB option but the MySQL server does not support BDB tables, the table will be 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 you get a warning if the specified table type is not honored.

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

When you use a MyISAM table, MySQL uses the product of MAX_ROWS * AVG_ROW_LENGTH to decide how big the resulting table will be. If you don't specify any of the above options, the maximum size for a table will be 4G (or 2G if your operating systems only supports 2G 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 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 will use prefix compression. This means that you will only get a big benefit from this if you have many numbers that are the same. Prefix compression means that every key needs one extra byte to indicate how many bytes of the previous key are the same for the next key (note that the pointer to the row is stored in high-byte-first order directly after the key, to improve compression). This means that if you have many equal keys on two consecutive rows, all following "same" keys will usually only take 2 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 (usually 4). On the other hand, if all keys are totally different, you will use 1 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.)

As of MySQL 3.23, if you specify a SELECT after the CREATE statement, MySQL will create new fields 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 will create 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.

CREATE TABLE ... SELECT will not automatically create any indexes for you. This is done intentionally to make the command 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;

If any errors occur while copying the data to the table, it will automatically be deleted.

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

The RAID_TYPE option will help you to exceed the 2G/4G limit for the MyISAM datafile (not the index file) on operating systems that don't support big files. Note that this option is not recommended for filesystem that supports big files!

You can get more speed from the I/O bottleneck by putting RAID directories on different physical disks. RAID_TYPE will work on any OS, as long as you have configured MySQL with -with-raid. For now the only allowed RAID_TYPE is STRIPED (1 and RAID0 are aliases for this).

If you specify RAID_TYPE=STRIPED for a MyISAM table, MyISAM will create RAID_CHUNKS subdirectories named 00, 01, 02 in the database directory. In each of these directories MyISAM will create a table_name.MYD. When writing data to the datafile, the RAID handler will map the first RAID_CHUNKSIZE *1024 bytes to the first file, the next RAID_CHUNKSIZE *1024 bytes to the next file and so on.

UNION is used when you want to use a collection of identical tables as one. This only works with MERGE tables. Section 7.2, “MERGE Tables ”.

For the moment you need to have SELECT, UPDATE, and DELETE privileges on the tables you map to a MERGE table. All mapped tables must be in the same database as the MERGE table.

If you want to insert data in a MERGE table, you have to specify with INSERT_METHOD into with table the row should be inserted. INSERT_METHOD is an option useful for MERGE tables only. Section 7.2, “MERGE Tables ”. This option was introduced in MySQL 4.0.0.

In the created table the PRIMARY key will be placed first, followed by all UNIQUE keys and then the normal keys. This helps the MySQL optimiser to prioritise which key to use and also more quickly detect duplicated UNIQUE keys.

By using DATA DIRECTORY="directory" or INDEX DIRECTORY="directory" you can specify where the storage engine should put it's table and index files. Note that the directory should be a full path to the directory (not relative path).

This only works for MyISAM tables in MySQL 4.0, when you are not using the -skip-symlink option. Section 5.6.1.2, “Using Symbolic Links for Tables ”.

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

IGNORE is a MySQL extension to SQL-92. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table. If IGNORE isn't specified, the copy is aborted and rolled back. If IGNORE is specified, then for rows with duplicates on a unique key, only the first row is used; the others are deleted.

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

CHANGE col_name, DROP col_name, and DROP INDEX are MySQL extensions to SQL-92.

MODIFY is an Oracle extension to ALTER TABLE.

The optional word COLUMN is a pure noise word and can be omitted.

If you use ALTER TABLE tbl_name RENAME TO new_name without any other options, MySQL simply renames the files that correspond to the table tbl_name. There is no need to create the temporary table. Section 6.5.5, “RENAME TABLE Syntax ”.

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

You can rename a column using a CHANGE old_col_name create_definition clause. To do so, specify the old and new column names and the type that the column currently has. For example, to rename an INTEGER column from a to b, you can do this:

mysql ALTER TABLE t1 CHANGE a b INTEGER;

If you want to change a column's type but not the name, CHANGE syntax still requires an old and new column name, even if they are the same. For example:

mysql ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;

However, as of MySQL Version 3.22.16a, you can also use MODIFY to change a column's type without renaming it:

mysql ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

If you use CHANGE or MODIFY to shorten a column for which an index exists on part of the column (for instance, if you have an index on the first 10 characters of a VARCHAR column), you cannot make the column shorter than the number of characters that are indexed.

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

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

ALTER COLUMN specifies a new default value for a column or removes the old default value. If the old default is removed and the column can be NULL, the new default is NULL. If the column cannot be NULL, MySQL assigns a default value, as described in Section 6.5.3, “CREATE TABLE Syntax ”.

DROP INDEX removes an index. This is a MySQL extension to SQL-92. Section 6.5.8, “DROP INDEX Syntax ”.

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

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

DROP PRIMARY KEY drops the primary index. If no such index exists, it drops the first UNIQUE index in the table. (MySQL marks the first UNIQUE key as the PRIMARY KEY if no PRIMARY KEY was specified explicitly.)

If you add a UNIQUE INDEX or PRIMARY KEY to a table, this is stored before any not UNIQUE index so that MySQL can detect duplicate keys as early as possible.

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

If you use ALTER TABLE on a MyISAM table, all non-unique indexes are created in a separate batch (like in REPAIR). This should make ALTER TABLE much faster when you have many indexes.

Since MySQL 4.0 the above feature can be activated explicitly. ALTER TABLE ... DISABLE KEYS makes MySQL to stop updating non-unique indexes for MyISAM table. ALTER TABLE ... ENABLE KEYS then should be used to recreate missing indexes. As MySQL does it with a special algorithm which is much faster then inserting keys one by one, disabling keys could give a considerable speedup on bulk inserts.

With the C API function mysql_info(), you can find out how many records were copied, and (when IGNORE is used) how many records were deleted due to duplication of unique key values.

The FOREIGN KEY, CHECK, and REFERENCES clauses don't actually do anything, except for InnoDB type tables which support ... ADD [CONSTRAINT symbol] FOREIGN KEY (...) REFERENCES ... (...) and ... DROP FOREIGN KEY .... Section 7.5.5.2, “FOREIGN KEY Constraints ”. The syntax for other table types is provided only for compatibility, to make it easier to port code from other SQL servers and to run applications that create tables with references. Section 1.7.4, “MySQL Differences Compared To SQL-92 ”.

Only drops temporary tables.

It doesn't end a running transactions.

No access rights is checked.