CREATE TABLE - Table options

These options are used to set the maximum and minimum rows of a MySQL table, respectively. Use the SHOW CREATE TABLE statement to see the results.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table 
(column, ..., index type[(width)] [ASC|DESC]
[TABLESPACE tablespace_name STORAGE DISK] 
   {ENGINE|TYPE} [=] {BDB|HEAP|ISAM|INNODB|MERGE|MRG_MYISAM|MYISAM} | 
AUTO_INCREMENT [=] value | 
AVG_ROW_LENGTH [=] value | 
[DEFAULT] CHARACTER SET character_set |
CHECKSUM [=] {0|1} | 
CONNECTION [=] 'string' | 
COLLATE collation |
COMMENT [=] 'string' | 
DATA DIRECTORY [=] '/path' |
DELAY_KEY_WRITE [=] {0|1} | 
ENGINE [=] engine |
INDEX DIRECTORY [=] '/path' |
INSERT_METHOD [=] {NO|FIRST|LAST } | 
KEY_BLOCK_SIZE [=] value | 
MAX_ROWS [=] value | 
MIN_ROWS [=] value | 
PACK_KEYS [=] {0|1|DEFAULT} |  
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} | 
RAID_TYPE = {1|STRIPED|RAID0} | 
UNION [=] (table[,...])

Explanation

This documentation page lists all of the table options that can be set with the CREATE TABLE statement. The options are given after the closing parenthesis for the column definitions. To see the values for an existing table, use the SHOW TABLE STATUS statement. To change the values of any options after a table has been created, use the ALTER TABLE statement. Each option is explained in the following paragraphs in alphabetical order, as shown in the preceding syntax. Examples of each are given as well.

AUTO_INCREMENT

This parameter causes MySQL to assign a unique identification number automatically to the column in each row added to the table. By default, the starting number is 1. To set it to a different starting number when creating a table, you can use the AUTO_INCREMENT table option. Here's an example using this option:

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY, 
client_name VARCHAR(75), 
telephone CHAR(15))
AUTO_INCREMENT=1000;

This statement sets the initial value of the primary key column to 1000 so that the next row inserted will be 1001. There is usually no reason to set a starting number explicitly, because the key is used merely to distinguish different columns.

AVG_ROW_LENGTH

For large tables, you may want to set the average row length for better table optimization by using the AVG_ROW_LENGTH option.

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY, 
client_name VARCHAR(75), 
telephone CHAR(15))
AVG_ROW_LENGTH = 12638;

CHARACTER SET

This option sets the character set used for character data in the table. The DEFAULT flag is not required. This option is typically used along with the COLLATE option. These options do not affect columns for which the character sets and collation are explicitly specified. Use the SHOW CHARACTER SET and SHOW COLLATION statements to see the character sets and collations available.

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY, 
client_name VARCHAR(75), 
telephone CHAR(15))
DEFAULT CHARACTER SET 'latin2'
COLLATE 'latin2_general_ci';

CHECKSUM

This option enables or disables a checksum for a table. Set the value to 0 to disable the checksum or to 1 to enable a checksum on a table. If you are upgrading a table that uses a checksum and was created prior to version 4.1 of MySQL, the table may be corrupted in the process. Try using REPAIR TABLE to recalculate the checksum for the table.

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY, 
client_name VARCHAR(75), 
telephone CHAR(15))
CHECKSUM = 0;

COLLATE

This option sets the collaction (alphabetizing order) to use with character data in the table. This option is typically used along with the CHARACTER SET option. These options do not affect columns for which the collation and character sets are explicitly specified. Use the SHOW CREATE TABLE statement to see the collation and character set for the table and its columns.

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY, 
client_name VARCHAR(75), 
telephone CHAR(15))
COLLATE 'latin2_general_ci'
DEFAULT CHARACTER SET 'latin2';

COMMENT

With this option you can add notes for yourself or other table adminstrators regarding a table.

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY, 
client_name VARCHAR(75), 
telephone CHAR(15))
COMMENT = 'This table lists basic information on clients.';

CONNECTION

This option is provided for tables that use the FEDERATED storage engine. Previously you would use the COMMENT option to specify this option. The syntax for this option is:

CONNECTION='mysql://username:password@hostname:port/database/tablename'

The password and port are optional.

If you want to federate an existing table with a remote table, you can alter the table on your system to specify the connection to the remote table like this:

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY, 
client_name VARCHAR(75), 
telephone CHAR(15))
ENGINE = FEDERATED
CONNECTION='mysql://russell:rover123@santa_clara_svr:9306/federated/clients';

DATA DIRECTORY

This option is theoretically used to see the data directory path for the table. However, MySQL currently ignores the option. Filesystem privileges for the path given are required to specify the option.

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY, 
client_name VARCHAR(75), 
telephone CHAR(15))
DATA DIRECTORY = '/data/mysql/clients';

DELAY_KEY_WRITE

This option delays index updates until the table is closed. It's enabled with a value of 1 and disabled with a value of 0.

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY, 
client_name VARCHAR(75), 
telephone CHAR(15))
DELAY_KEY_WRITE = 1;

ENGINE

Use this option to change the storage engine(formerly known as the table type) for the table given. Be careful using this option as it may cause problems with data. Make a backup of your table and data before using it. As of version 5.1.11 of MySQL, this option cannot be used to change a table to the BLACKHOLE or MERGE storage engines.

CREATE TABLE clients
(client_id INT AUTO_INCREMENT
client_name VARCHAR(75), 
telephone CHAR(15))
ENGINE = MyISAM;

INDEX DIRECTORY

This

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY, 
client_name VARCHAR(75), 
telephone CHAR(15))
INDEX DIRECTORY = '/data/mysql/clients_index';

INSERT_METHOD

To insert data into a MERGE table, you need to specify the insert method it will use. To set or change this method, use the INSERT_METHOD option with the CREATE TABLE or ALTER TABLE statements. A value of FIRST indicates that the first table should be used; LAST indicates the last table should be used; NO disables insertions.

CREATE TABLE sales_national
(order_id INT, sales_total INT) 
ENGINE = MERGE
UNION = (sales_east, sales_west) 
INSERT_METHOD = LAST;

This SQL statement created the table sales_national based on two other tables while specifying that insertions use the last table in the list of tables given.

KEY_BLOCK_SIZE

This option gives the storage engine a hint of the size of index key blocks. Set the value to 0 to instruct the engine to use the default. This option was added in version 5.1.10 of MySQL.

CREATE TABLE c
(client_id INT AUTO_
client_name VARCHAR(75), 
telephone CHAR(15))
KEY_BLOCK_SIZE = 1024;

MAX_ROWS; MIN_ROWS

These options are used to set the maximum and minimum rows of a table, respectively. Use the SHOW CREATE TABLE statement to see the results.

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY, 
client_name VARCHAR(75), 
telephone CHAR(15))
MIN_ROWS = 100, 
MAX_ROWS = 1000;

PACK_KEYS

For small MyISAM tables in which users pri

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY, 
cli
telephone CHAR(15))
PACK_KEYS = 0;

ROW_FORMAT

This option tells the storage engine how to store rows of data. With MyISAM, a va

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR
telephone CHAR(15))
ROW_FORMAT = DYNAMIC;

RAID_TYPE

This option specifies the type of RAID to be used. However, support for RAID has been removed from MySQL as of version 5.0. This SQL statement also used to permit the options RAID_CHUNKS and RAID_CHUNKSIZE, but they have been deprecated as well.

UNION

To change the tables that make up a MERGE table, specify the full list of ta

CREATE TABLE sales_national
(order_id INT, sales_total INT) 
ENGINE = MERGE
UNION = (sales_north, sales_south, sales_east, sales_west);