MySQL in a Nutshell, 2nd Edition

Name

CREATE TABLE

Synopsis

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table 
{[(definition)][options]|[[AS] SELECT...]|[LIKE table]}

Use this statement to create a new table within a database. This statement has many clauses and options; however, when creating a basic table, you can omit most of them. The TEMPORARY keyword is used to create a temporary table that can be accessed only by the current connection thread and is not accessible by other users. The IF NOT EXISTS flag is used to suppress error messages caused by attempting to create a table by the same name as an existing one. After the table name is given, either the table definition is given (i.e., a list of columns and their data types) along with table options or properties, or a table can be created based on another table. The subsections that follow describe how to:

Set column properties regarding NULL and default values (see the CREATE TABLE: Column flags” subsection)
Create an index for a table based on one or more columns (see the CREATE TABLE: Index and key definitions” subsection)
Reference a foreign key constraint (see the CREATE TABLE: Foreign key references” subsection)
Specify various table options (see the CREATE TABLE: Table options” subsection)
Create a table exactly like another table (see the CREATE TABLE: Based on an existing table” subsection)
Create a table with filesystem partitions (see the three subsections on partitioning: CREATE TABLE: Partitioning,” CREATE TABLE: Partition definitions,” and CREATE TABLE: Subpartition definitions”)

Here is a simple example of how you can use the CREATE TABLE statement:

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

This creates a table with three columns. The first column is called client_id and may contain integers. It will be incremented automatically as records are created. It will also be the primary key field for records, which means that no duplicates are allowed and the rows will be indexed based on this column. The second column, client_name, is a variable-width, character-type column with a maximum width of 75 characters. The third column is called telephone and is a fixed-width, character-type column with a minimum and maximum width of 15 characters. To see the results of this statement, you can use the DESCRIBE statement. There are many column data types. They’re all listed and described in Appendix A.

CREATE TABLE: Column flags

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table 
(column type[(width)] [ASC|DESC] [NOT NULL|NULL] [DEFAULT value]
   [AUTO_INCREMENT] [[PRIMARY] KEY]|[[UNIQUE] KEY] 
   [COMMENT 'string']
   [REFERENCES table [(column,...)]
      [MATCH FULL|MATCH PARTIAL|MATCH SIMPLE]
      [ON DELETE [RESTRICT|CASCADE|SET NULL|NO ACTION]]
      [ON UPDATE [RESTRICT|CASCADE|SET NULL|NO ACTION]] [,...]
   ]
[,...]) [options]

This is the syntax for the CREATE TABLE statement again, but detailing the column flags portion of the column definition. For some column types, you may need to specify the size of the column within parentheses after the column name and column type.

If a column is indexed, the keyword ASC or DESC may be given next to indicate whether indexes should be stored in ascending or descending order, respectively. By default, they are stored in ascending order. For older versions of MySQL, these flags are ignored. Adding the NOT NULL flag indicates the column may not be NULL. The NULL flag may be given to state that a NULL value is allowed. Some data types are NULL by default. For some, you don’t have a choice whether a column may be NULL or not. To set a default value for a column, you can use the DEFAULT keyword. For some data types (e.g., TIMESTAMP), a default value is not allowed. The AUTO_INCREMENT option tells MySQL to assign a unique identification number automatically to a column. It must be designated as a PRIMARY or UNIQUE key column, and you cannot have more than one AUTO_INCREMENT column in a table. If a column is to be the basis of an index, either PRIMARY KEY, UNIQUE KEY, UNIQUE, or just KEY can be given. Just KEY indicates the column is a primary key.

To document what you’re doing for an administrator or a developer, a comment regarding a column may be given. The results of a SELECT statement won’t show it, but a SHOW FULL COLUMNS statement will reveal it. To add a comment, use the COMMENT keyword followed by a string within quotes. Here is an example using some of the flags and clauses mentioned here:

CREATE TABLE clients
(client_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
client_name VARCHAR(75),
client_city VARCHAR(50) DEFAULT 'New Orleans',
telephone CHAR(15) COMMENT 'Format: ###-###-####');

In this example, the client_id column is a primary key. The NOT NULL option is included for completeness, even though it’s not necessary, because a primary key must be unique and non-NULL. For the client_city column, the DEFAULT clause is used to provide the default value of the column. The default will be used during inserts when no value is given, although you can override the default by specifying an explicit blank value for the column. This statement also includes a comment regarding the typical format for entering telephone numbers in the telephone column. Again, this will be displayed only with the SHOW FULL COLUMNS statement.

For information on the REFERENCES column flag, see the CREATE TABLE: Foreign key references” subsection later in this section.

CREATE TABLE: Index and key definitions

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table 
(column, ..., index type[(width)] [ASC|DESC] | 
[CONSTRAINT [symbol]] PRIMARY KEY [type] (column,...) 
   [KEY_BLOCK_SIZE value|type|WITH PARSER parser] | 
INDEX|[PRIMARY] KEY [index] [type] (column,...) 
   [KEY_BLOCK_SIZE value|type|WITH PARSER parser] |
[CONSTRAINT [symbol]] UNIQUE [INDEX] [index] [type] (column,...) 
   [KEY_BLOCK_SIZE value|type|WITH PARSER parser] |
[FULLTEXT|SPATIAL] [INDEX] [index] (column,...) 
   [KEY_BLOCK_SIZE value|type|WITH PARSER parser] |
[CONSTRAINT [symbol]] FOREIGN KEY [index] (column,...)
    [reference_definition] | 
CHECK (expression)]
[,...]) [options]

You can use one or more columns for an index, and a table can contain multiple indexes. Indexes can greatly increase the speed of data retrieval from a table. You can define an index involving multiple columns with this statement, or later with the ALTER TABLE statement or the CREATE INDEX statement. With the CREATE TABLE statement, though, indexes can be given after the definition of the columns they index.

A KEY (also called a PRIMARY KEY) is a particular kind of index obeying certain constraints. It must be unique, for instance. It is often combined in MySQL with the AUTO_INCREMENT keyword, and used for identifiers that appear as columns in tables. The general format is to specify the type of index, such as KEY, INDEX, or UNIQUE. This is followed by the index name. Optionally, the index type may be specified with the USING keyword. For most tables, there is only one type of index, so this is unnecessary.

Before version 5 of MySQL, BTREE is the only type for MyISAM tables. Beginning with version 5, the RTREE index type is also available, so you may want to specify the index type. After the index type, one or more columns on which the index is based are listed within parentheses, separated by commas. Before explaining the various possibilities, let’s look at an example:

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
name_last VARCHAR(50), name_first VARCHAR(50),
telephone CHAR(15),
INDEX names USING BTREE (name_last(5), name_first(5) DESC));

The client_id column here is a PRIMARY KEY, although that clause has been abbreviated to just KEY. This abbreviation is available as of version 4.1 of MySQL. There can be only one PRIMARY KEY but any number of other indexes. The table contains a second index using the first five characters of the two name columns. To specify a combination, the index definition is generally given at the end of the table’s column definitions with the INDEX keyword. The index is named names in the example.

After the index name, the USING clause specifies the type of index to be used. Currently, this is unnecessary because BTREE is the default type for a MyISAM table.

Next, the two columns to index appear within parentheses. The name columns are variable-width columns and 50 characters in length, so to speed up indexing, only the first five characters of each column are used. The name_first column is supposed to be used in descending order per the DESC flag. However, this will be ignored for the current version of MySQL.

The syntax structures for the index clauses listed here vary depending on the type of table index to be created: PRIMARY KEY, INDEX, UNIQUE, FULLTEXT (or BLOB column types), or SPATIAL.

To create constraints on tables based on columns in another table, use the FOREIGN KEY index syntax structures. Foreign keys are used only to link columns in InnoDB tables. The CHECK clause is not used in MySQL but is available for porting to other database systems. Here is an example of how you can use foreign keys to create a table:

CREATE TABLE employees
(emp_id INT NOT NULL PRIMARY KEY,
name_last VARCHAR(25), name_first VARCHAR(25))
TYPE = INNODB;

CREATE TABLE programmers
(prog_id INT, emp_id INT,
INDEX (emp_id),
FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
ON DELETE CASCADE)
TYPE=INNODB;

The first CREATE TABLE statement creates a table of basic employee information. The second CREATE TABLE statement creates a simple table of programmers. In the employees table, the key column emp_id will be used to identify employees and will be the foreign key for the programmers table. The programmers table sets up an index based on emp_id, which will be tied to the emp_id column in the employees table. The FOREIGN KEY clause establishes this connection using the REFERENCES keyword to indicate the employees table and the key column to use in that table. Additionally, the ON DELETE CASCADE clause instructs MySQL to delete the row in the programmers table whenever an employee record for a programmer is deleted from the employees table.

The next subsection, CREATE TABLE: Foreign key references,” gives the syntax for references to foreign keys and the meaning of each component.

At the end of both of these SQL statements, the storage engine is set to InnoDB with the TYPE clause. The ENGINE keyword could be used instead and would have the same effect.

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

The WITH PARSER clause may be used to give a parser plugin for an index. This is used only with FULLTEXT indexes.

CREATE TABLE: Foreign key references

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table 
(column, ..., index type[(width)] [ASC|DESC]
[CONSTRAINT [symbol]] FOREIGN KEY [index] (column,...)
REFERENCES table [(column,...)]
   [MATCH FULL|MATCH PARTIAL|MATCH SIMPLE]
   [ON DELETE [RESTRICT|CASCADE|SET NULL|NO ACTION]]
   [ON UPDATE [RESTRICT|CASCADE|SET NULL|NO ACTION]]
[,...]) [options]

This subsection describes the REFERENCES options to the FOREIGN KEY clause, which creates a relationship between an index and another table. This information also applies to the REFERENCES column flag (see the earlier subsection CREATE TABLE: Column flags”).

The MATCH FULL clause requires that the reference match on the full width of each column indexed. In contrast, MATCH PARTIAL allows the use of partial columns. Partial columns can accelerate indexing when the first few characters of a column determine that a row is unique.

The ON DELETE clause instructs MySQL to react to deletions of matching rows from the foreign table according to the option that follows. The ON UPDATE clause causes MySQL to respond to updates made to the referenced table according to the options that follow it. You can use both clauses in the same CREATE TABLE statement.

The RESTRICT keyword option instructs MySQL not to allow the deletion or update (depending on the clause in which it’s used) of the rows in the foreign table if rows in the current table are linked to them. The CASCADE keyword says that when deleting or updating the rows that are referenced in the parent table, delete or update the related rows in the child table accordingly (as in the last example of the previous subsection).

SET NULL causes MySQL to change the data contained in the related columns to a NULL value. For this to work, the column in the child table must allow NULL values. The NO ACTION setting has MySQL not react to deletions or updates with regard to the referencing table.

CREATE TABLE: Table options

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[,...])

This subsection 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 also given.

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 first 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 collation (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 administrators regarding a table. Comments are shown only when the SHOW CREATE TABLE statement is executed:

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. As of version 5.1.23 of MySQL, this option is ignored for table partitions. 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 KEY, 
client_name VARCHAR(75), 
telephone CHAR(15))
ENGINE = MyISAM;

INDEX DIRECTORY

This option is theoretically used to see the directory path for the table indexes. As of version 5.1.23 of MySQL, this option is ignored for table partitions. 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))
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 creates 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 clients
(client_id INT AUTO_INCREMENT KEY, 
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 primarily read the data and rarely update it, you can use the PACK_KEYS option to pack the indexes. This will make reads faster but updates slower. Set the value of this option to 1 to enable packing and 0 to disable it. A value of DEFAULT instructs the storage engine to pack CHAR or VARCHAR data type columns only:

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

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.

ROW_FORMAT

This option tells the storage engine how to store rows of data. With MyISAM, a value of DYNAMIC (i.e., variable-length) or FIXED may be given. If you run the myisampack utility on a MyISAM table, the format will be set to a value of COMPRESSED. You can uncompress a compressed MyISAM table by giving a value of REDUNDANT. This value is deprecated, though. InnoDB tables use the COMPACT method, but offer a REDUNDANT method to be compatible with a more wasteful format used in older versions of InnoDB:

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

UNION

To change the tables that make up a MERGE table, specify the full list of tables using this option:

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

CREATE TABLE: Partitioning

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table
PARTITION BY
  [LINEAR] HASH(expression) | 
  [LINEAR] KEY(columns) | 
  RANGE(expression) | 
  LIST(expression)
  [PARTITIONS number]
    [SUBPARTITION BY
      [LINEAR] HASH(expression) | 
      [LINEAR] KEY(columns)
      [SUBPARTITIONS number]
    ]
  [PARTITION partition
        [VALUES {LESS THAN (expression)|MAXVALUE|IN (values)}]
        [[STORAGE] ENGINE [=] engine]
        [COMMENT [=] 'text' ]
        [DATA DIRECTORY [=] '/path']
        [INDEX DIRECTORY [=] '/path']
        [MAX_ROWS [=] number]
        [MIN_ROWS [=] number]
        [TABLESPACE [=] (tablespace)]
        [NODEGROUP [=] value]

        [(SUBPARTITION logical_name
           [[STORAGE] ENGINE [=] engine]
           [COMMENT [=] 'text' ]
           [DATA DIRECTORY [=] '/path']
           [INDEX DIRECTORY [=] '/path']
           [MAX_ROWS [=] number]
           [MIN_ROWS [=] number]
           [TABLESPACE [=] (tablespace)]
           [NODEGROUP [=] value]
        [, SUBPARTITION...])]
  [, PARTITION...]]
  ]

These table partition clauses may be used in CREATE TABLE to create a table using partitions—that is, to organize data into separate files on the filesystem. This capability was added as of version 5.1.6 of MySQL. To add or alter partitions on an existing table, see the ALTER TABLE statement explanation earlier in this chapter. See that section also for comments on partitions in general. This subsection includes several examples of creating a MyISAM table with partitions.

The PARTITION BY clause is required when partitioning in order to explain how data is split and distributed among partitions. A table cannot have more than 1,024 partitions and subpartitions. The subclauses of PARTITION BY are explained in this subsection, whereas the PARTITION and SUBPARTITION clauses are explained in the next two subsections that cover this statement (CREATE TABLE: Partition definitions” and CREATE TABLE: Subpartition definitions”):

HASH

This subclause creates a key/value pair that controls which partition is used for saving rows of data and for indexing data. The value of the hash consists of the specified columns. If a table has a primary key, that column must be used by the hash. Functions that return a numerical value (not a string) may be used within a hash specification:

CREATE TABLE sales_figures
(emp_id INT, 
sales_date DATE,
amount INT)
PARTITION BY HASH(MONTH(sales_date))
PARTITIONS 12;

This creates 12 partitions, one for each month extracted from the sales_data.

By default, the HASH method and the KEY method (described next) use the modulus of the hash function’s given value. The keyword LINEAR may be added in front of HASH or KEY to change the algorithm to a linear powers-of-two algorithm. For extremely large tables of data, the linear hash has higher performance results in processing data, but does not evenly spread data among partitions.

KEY

This subclause functions the same as HASH except that it accepts only a comma-separated list of columns for indexing and distributing data among partitions. The LINEAR flag may be given to change the algorithm method used. See the previous description for HASH:

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY, 
client_name VARCHAR(75), 
telephone CHAR(15))
PARTITION BY KEY (client_id)
PARTITIONS 4;

LIST

This subclause can be used to give specific values for distributing data across partitions. The column and values must all be numeric, not strings:

CREATE TABLE sales_figures
(region_id INT, sales_date DATE, amount INT)
PARTITION BY LIST (region_id) (
   PARTITION US_DATA VALUES IN(100,200,300),
   PARTITION EU_DATA VALUES IN(400,500));

In this example, data is distributed between two partitions: one for the sales in the United States, which is composed of three regions, and a second partition for data for the two European regions. Notice that the names for the partitions given aren’t in the usual naming convention (e.g., p0). Any name will do. It’s a matter of preference.

RANGE

To instruct MySQL to distribute data among the partitions based on a range of values, use the RANGE subclause. Use the VALUES LESS THAN subclause to set limits for each range. Use VALUES LESS THAN MAXVALUE to set the limit of the final partition:

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY, 
client_name VARCHAR(75), 
telephone CHAR(15))
PARTITION BY RANGE (client_id) (
   PARTITION p0 VALUES LESS THAN (500),
   PARTITION p1 VALUES LESS THAN (1000),
   PARTITION p3 VALUES LESS THAN MAXVALUE);

In this example, the data is distributed among the partitions based on the client_id values. The first partition will contain rows with a client identification number less than 500; the second will contain rows of values ranging from 501 to 1000; and the last partition will contain values of 1001 and higher. Values given for partitions must be in ascending order.

See the ALTER TABLE explanation for more information on table partitioning, especially modifying or removing partitioning.

CREATE TABLE: Partition definitions

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table
    PARTITION partition
        [VALUES {LESS THAN (expression) | MAXVALUE | IN (value_list)}]
        [[STORAGE] ENGINE [=] engine]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] '/path']
        [INDEX DIRECTORY [=] '/path']
        [MAX_ROWS [=] number]
        [MIN_ROWS [=] number]
        [TABLESPACE [=] (tablespace)]
        [NODEGROUP [=] number]
        [(subpartition_definition[, subpartition_definition] ...)]

The subclauses described in this subsection define general parameters of partitions, such as their sizes and locations in the filesystems:

COMMENT

Use this subclause if you want to add a comment to a partition. The text must be contained within single quotes. Comments can be viewed only with the SHOW CREATE TABLE statement:

CREATE TABLE sales_figures
(region_id INT, sales_date DATE, amount INT)
PARTITION BY LIST (region_id) (
   PARTITION US_DATA VALUES IN(100,200,300)
   COMMENT = 'U.S. Data',
   PARTITION EU_DATA VALUES IN(400,500)
   COMMENT = 'Europe Data');

DATA DIRECTORY, INDEX DIRECTORY

With these subclauses, you can specify file pathnames in order to fix the locations of partitions. The directories given must exist and you must have access privileges to the given directories:

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
PARTITION BY RANGE (client_id) (
   PARTITION p0 VALUES LESS THAN (500)
   DATA DIRECTORY = '/data/mysql/old_clients/data'
   INDEX DIRECTORY = '/data/mysql/old_clients/index',
   PARTITION p1 VALUES LESS THAN MAXVALUE
   DATA DIRECTORY = '/data/mysql/new_clients/data'
   INDEX DIRECTORY = '/data/mysql/new_clients/index');

ENGINE

This subclause specifies an alternative storage engine to use for the partition. However, at this time all partitions must use the same storage engine:

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
PARTITION BY RANGE (client_id) (
   PARTITION p0 VALUES LESS THAN (500)
   ENGINE = InnoDB,
   PARTITION p1 VALUES LESS THAN MAXVALUE
   ENGINE = InnoDB);

MAX_ROWS, MIN_ROWS

These subclauses suggest the maximum and minimum number of rows in a table partition, respectively. MySQL may deviate from these limits, though:

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
PARTITION BY RANGE (client_id) (
   PARTITION p0 VALUES LESS THAN (500)
   MIN_ROWS = 10 MAX_ROWS = 1000,
   PARTITION p3 VALUES LESS THAN MAXVALUE
   MIN_ROWS = 10 MAX_ROWS = 500);

NODEGROUP

This subclause can be used only with MySQL Cluster, and places a partition in the given node group. (MySQL clusters are divided into different node groups in order to let certain nodes manage the data nodes.)

TABLESPACE

This subclause can be used only with MySQL Cluster, and specifies the tablespace to use with the partition.

VALUES

This subclause specifies a range of values or a list of specific values for indexing and determining the disbursal of data among partitions. These are described earlier in the CREATE TABLE: Partitioning” subsection.

CREATE TABLE: Subpartition definitions

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table
    SUBPARTITION partition
        [[STORAGE] ENGINE [=] engine]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] '/path']
        [INDEX DIRECTORY [=] '/path']
        [MAX_ROWS [=] number]
        [MIN_ROWS [=] number]
        [TABLESPACE [=] (tablespace)]
        [NODEGROUP [=] number]

Only partitions distributed by the RANGE or LIST methods can be subpartitioned. The subpartitions can use only the HASH or KEY methods. The definitions for subpartitions are the same as for partitions, described earlier in the CREATE TABLE: Partitioning” subsection. Here are some examples of subpartitioning:

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );

CREATE TABLE sales_figures
(emp_id INT, sales_date DATE, amount INT)
PARTITION BY RANGE(YEAR(sales_date))
SUBPARTITION BY HASH(MONTH(sales_date))
SUBPARTITIONS 4 (
   PARTITION QTR1 VALUES LESS THAN (4),
   PARTITION QTR2 VALUES LESS THAN (7),
   PARTITION QTR3 VALUES LESS THAN (10),
   PARTITION QTR4 VALUES LESS THAN MAXVALUE);

Notice that although the subpartition uses HASH, the subpartitions are specified in ranges of values because it’s a subpartition of a partition that uses the RANGE method.

CREATE TABLE: Based on an existing table

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table
LIKE table |
[IGNORE|REPLACE] [AS] SELECT...

These two syntaxes for the CREATE TABLE statement allow a new table to be created based on an existing table. With the LIKE clause, a table is created based on the structure of the existing table given. For example, suppose a database has a table called employees that contains information on full-time and part-time employees. Suppose further that it has been decided that information on part-time employees should be stored in a separate table. You could execute the following statement to create a new table for part-time employees with the same structure as the existing employees table:

CREATE TABLE part_time_employees
LIKE employees;

This statement results in a new table with the same structure but without any data. If the table that was copied has a primary key or any indexes, they won’t be copied. You can use the CREATE INDEX statement to create an index. You would first have to do the following to copy the data over:

INSERT INTO part_time_employees 
SELECT * FROM employees 
WHERE part_time = 'Y';

To create a new table based on the structure of an existing table, and to copy some data from the old table to the new one, you can enter something like the following statement:

CREATE TABLE part_time_employees
SELECT *
FROM employees
WHERE part_time = 'Y';

CREATE INDEX emp_id ON part_time_employees(emp_id);

In this example, the table structure is copied and the data is copied for rows where the part_time column has a value of Y, meaning yes. You could follow this statement with a DELETE statement to delete the rows for part-time employees from the employees table. The second SQL statement in this example restores the index on emp_id. However, it doesn’t make the column a primary key or an AUTO_INCREMENT one. For that, you would need to use ALTER TABLE instead.

You can use the IGNORE keyword before the SELECT statement to instruct MySQL to ignore any error messages regarding duplicate rows, to not insert them, or to proceed with the remaining rows of the SELECT statement. Use the REPLACE keyword instead if duplicate rows are to be replaced in the new table.

Table of Contents for MySQL in a Nutshell, 2nd Edition