Skip to content

Here's the English translation of the CREATE TABLE statement documentation:

CREATE TABLE

Syntax Explanation

The CREATE TABLE statement is used tocreate a new table in the currently selected database.

Syntax Structure

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

create_definition: {
    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY
      [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (col_name,...)
      reference_definition
}

column_definition: {
    data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [reference_definition]
  | data_type
      [[PRIMARY] KEY]
      [COMMENT 'string']
      [reference_definition]
}

reference_definition:
    REFERENCES tbl_name (key_part,...)
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

table_options:
    table_option [[,] table_option] ...

table_option: {
  | AUTO_INCREMENT [=] value
  | COMMENT [=] 'string'
  | START TRANSACTION
}

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)}
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
 PARTITION partition_name
     [VALUES
         {LESS THAN {(expr | value_list) | MAXVALUE}
         |
         IN (value_list)}]
     [COMMENT [=] 'string' ]

Syntax Breakdown

This section explains the various parameters and options available when creating a table, including table creation, column definitions, constraints, options, and partitioning.

  • CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name: This is thebasic syntax for creating a table. The TEMPORARY keyword indicates the creation of a temporary table, IF NOT EXISTS means to create the table only if it doesn't already exist, and tbl_name is the name of the table to be created.

  • (create_definition,...): This is thecolumn definition part, used to define each column of the table and its associated attributes.

  • [table_options]: These aretable-level options, which can set parameters like the table's storage engine or character set.

  • [partition_options]: These areoptions for partitioned tables, used to define the partitioning method and partition key.

The create_definition section is used to define the attributes of each column and can include:

  • col_name column_definition: Defines the specific column name and column attributes, includingdata type, nullability, default value, etc.

  • [CONSTRAINT [symbol]] PRIMARY KEY: Defines aprimary key constraint, allowing you to set the constraint name and the columns forming the primary key.

  • [CONSTRAINT [symbol]] FOREIGN KEY: Defines aforeign key constraint, allowing you to set the constraint name, the foreign key columns, and the referenced table.

The column_definition section is used for specific column definitions and can include:

  • data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]: Defines the column'sdata type, whether it allows nulls, and its default value.

  • [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]: Setsauto-increment, unique, and primary key constraints.

  • [COMMENT 'string']: Sets acomment for the column.

  • [reference_definition]: An optional reference definition, used to defineforeign key constraints.

The reference_definition section is used to define foreign key references, including:

  • REFERENCES tbl_name (key_part,...): Specifies thereferenced table and columns for the foreign key.

  • [ON DELETE reference_option]: Sets theforeign key action upon deletion.

  • [ON UPDATE reference_option]: Sets theforeign key action upon update.

reference_option represents theforeign key action options, which can be RESTRICT, CASCADE, SET NULL, or NO ACTION.

The table_options section is used to set table-level options, including theinitial value for auto-increment columns andtable comments.

The partition_options section is used to define options for partitioned tables, including thepartitioning method, partition key, and number of partitions.

More detailed parameter syntax explanations are provided below.

TEMPORARY

When creating a table, you can use theTEMPORARY keyword to create a temporary table. A TEMPORARY table is only visible within the current session and is automatically deleted when the session closes. This means two different sessions can use the same temporary table name without conflicting with each other or with an existing non-temporary table of the same name. (Existing tables are hidden until the temporary table is dropped.)

Deleting a database automatically deletes all TEMPORARY tables created within that database.

The session that creates the table can perform any operation on it, such as DROP TABLE, INSERT, UPDATE, or SELECT.

COMMENT

You can use theCOMMENT option to specify comments for columns or the entire table:

  • COMMENT = "comment of table" in CREATE TABLE [IF NOT EXISTS] [db.]table_name [comment = "comment of table"]; refers to thecomment for the entire table, with a maximum length of 2049 characters.
  • comment 'comment of column' in (name1 type1 [comment 'comment of column'],...) refers to thecomment for the specified column, with a maximum length of 1024 characters.

Use the SHOW CREATE TABLE and SHOW FULL COLUMNS statements to display comment content. Comment content is also shown in the INFORMATION_SCHEMA.COLUMN_COMMENT column.

AUTO_INCREMENT

AUTO_INCREMENT: Theinitial value of the table, which defaults to 1 and increments by 1 for each new record. The values in the data column must also be unique.

  • Columns set with AUTO_INCREMENT must be of aninteger or floating-point data type.
  • Auto-increment columns must be set as NOT NULL, otherwise NULL will be stored directly. When you insert a NULL (recommended) or 0 value into an indexed AUTO_INCREMENT column, the column will be set to the next sequence value. This is typically value+1, where value is the current maximum value for that column in the table.
  • Each table can only haveone AUTO_INCREMENT column, which must be indexable and cannot have a default value set. AUTO_INCREMENT columns need to contain positive values. If a negative number is inserted, it will be treated as inserting a very large positive number. This is done to avoid precision issues with numbers and ensure that AUTO_INCREMENT columns do not accidentally contain 0.

You can use the AUTO_INCREMENT attribute to define thestarting value for an auto-increment column. To set the starting value of an auto-increment column to 10, you can use the AUTO_INCREMENT keyword when creating the table and specify the starting value after it.

For example, to create a table and define the starting value of an auto-increment column as 10, you can use the following SQL statement:

-- Set
create table t1(id int auto_increment primary key) auto_increment = 10;

In this example, the id column is an auto-increment column with a starting value of 10. When new records are inserted into the table, the id column's value will start from 10 and automatically increment by 1 each time. If no AUTO_INCREMENT starting value is specified, the default starting value is 1, and it automatically increments by 1 each time.

Note

  1. MatrixOne currently only supports a default increment step of 1. Regardless of the initial value of the auto-increment column, it will always increment by 1. Setting the increment step size is not currently supported.
  2. MatrixOne only syntactically supports using the system variable set @@auto_increment_offset=n to set the auto-increment column's initial value; it does not actually take effect.

PRIMARY KEY

PRIMARY KEY is aprimary key constraint, used to uniquely identify each row in a table. A primary key must contain UNIQUE values and cannot contain NULL values. In the current version, a table can only have one primary key, which can consist of a single column or multiple columns.

*Creating a primary key during table creation

The following SQL statement creates a primary key on the ID column when creating the Persons table:

> CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

Note

In the example above, there is only one primary key, and it includes only one column (ID).

Whereas the following SQL statement creates a primary key on the ID and LastName columns when creating the Students table:

> CREATE TABLE Students (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID,LastName)
);

FOREIGN KEY

AFOREIGN KEY constraint, or foreign key, is a special field in a table, often used in conjunction with primary key constraints. Foreign key constraints are used to prevent actions that would break links between tables. For two tables with an associated relationship, the table containing the primary key in the associated field is theparent table, and the table containing the foreign key is thechild table. A foreign key is used to establish the relationship between the parent and child tables, connecting data in the two tables and ensuring data consistency and integrity.

A FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, as it must be one of the values in the table it points to.

When defining a foreign key, the following rules must be observed:

  • The parent table mustalready exist in the database, or be the table currently being created. In the latter case, if the parent and child tables are the same, such a table is called a self-referencing table, and this structure is called self-referencing integrity.
  • Aprimary key must be defined for the parent table.
  • Specify the column name or combination of column names after the parent table name. This column or combination of columns must be aprimary key or candidate key of the parent table.
  • Thenumber of columns in the foreign key must be the same as the number of columns in the parent table's primary key.
  • Thedata types of the columns in the foreign key must be the same as the data types of the corresponding columns in the parent table's primary key.

Let's illustrate with an example of associating parent and child tables via FOREIGN KEY and PRIMARY KEY:

First, create a parent table with field a as the primary key:

create table t1(a int primary key,b varchar(5));
insert into t1 values(101,'abc'),(102,'def');
mysql> select * from t1;
+------+------+
| a    | b    |
+------+------+
|  101 | abc  |
|  102 | def  |
+------+------+
2 rows in set (0.00 sec)

Then, create a child table with field c as a foreign key, referencing parent table field a:

create table t2(a int ,b varchar(5),c int, foreign key(c) references t1(a));
insert into t2 values(1,'zs1',101),(2,'zs2',102);
insert into t2 values(3,'xyz',null);
mysql> select * from t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | zs1  |  101 |
|    2 | zs2  |  102 |
|    3 | xyz  | NULL |
+------+------+------+
3 rows in set (0.00 sec)

Additionally, [ON DELETE reference_option] and [ON UPDATE reference_option] are used when defining foreign key relationships to specify the action to be performed when records in the parent table are deleted or updated. These parameters are primarily used to maintain data integrity and consistency:

  • ON DELETE reference_option: This parameter specifies how associated foreign key data should be handled when data in the referenced table is deleted. Common options include:

    • RESTRICT: If related foreign key data exists in the referencing table, deletion of data in the referenced table is not allowed. This can prevent accidental deletion of associated data, maintaining data consistency.

    • CASCADE: When data in the referenced table is deleted, associated foreign key data is also deleted. This can be used for cascading deletions of associated data, ensuring data integrity.

    • SET NULL: When data in the referenced table is deleted, the foreign key column's value is set to NULL. This can be used to retain foreign key data while disconnecting the association with the referenced data when deleting the referenced data.

    • NO ACTION: Indicates no action is taken, only checking if associated data exists. This is similar to RESTRICT, but may have minor differences in some databases.

  • ON UPDATE reference_option: This parameter specifies how associated foreign key data should be handled when data in the referenced table is updated. Common options are similar to ON DELETE reference_option, and their usage is similar, but they apply to data update operations.

See the example below:

Assume there are two tables, Orders and Customers. The Orders table has a foreign key column customer_id referencing the id column in the Customers table. If a customer in the Customers table is deleted and you also want to delete the associated order data, you can use ON DELETE CASCADE.

CREATE TABLE Customers (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE Orders (
    id INT PRIMARY KEY,
    order_number VARCHAR(10),
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES Customers(id) ON DELETE CASCADE
);

In the example above, when a customer in the Customers table is deleted, the associated order data will also be cascaded and deleted, maintaining data integrity. Similarly, the ON UPDATE parameter can be used in a similar way to handle update operations.

Cluster by

Cluster by is a command used to optimize the physical arrangement of a table. When used in CREATE TABLE for tables without a primary key, Cluster by allows the table to be physically sorted according to the specified columns, rearranging data rows to match the order of the values in those columns. Using Cluster by can improve query performance.

  • Single-column syntax: create table() cluster by col;
  • Multi-column syntax: create table() cluster by (col1, col2);

Note: Cluster bycannot exist simultaneously with a primary key, otherwise a syntax error will occur. Cluster by can only be specified during table creation and does not support dynamic creation.

Table PARTITION and PARTITIONS

partition_options:
 PARTITION BY
     { [LINEAR] HASH(expr)
     | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
 [PARTITIONS num]
 [(partition_definition [, partition_definition] ...)]

partition_definition:
 PARTITION partition_name
     [VALUES
         {LESS THAN {(expr | value_list) | MAXVALUE}
         |
         IN (value_list)}]
     [COMMENT [=] 'string' ]

Partitions can be modified, merged, added to, or removed from a table.

  • Can storemore data compared to a single disk or file system partition. *Optimizes queries.
    • When the WHERE clause includes partition conditions, only necessary partitions can be scanned.
    • Queries involving aggregate functions can be easily processed in parallel on each partition, with results eventually summarized.
  • For expired or unnecessary data, data can be quickly deleted bydropping partitions associated with that data. *Distributes data queries across multiple disks for greater query throughput.

*PARTITION BY

Partitioning syntax begins with PARTITION BY. This clause contains the function used to determine the partition, which returns an integer value from 1 to num, where num is the number of partitions.

*HASH(expr)

In practical work, large tables like membership tables often lack obvious characteristic fields for partitioning. To distribute such data, MatrixOne providesHASH partitioning. Based on a given number of partitions, data is assigned to different partitions. HASH partitioning can only hash integers; for non-integer fields, an expression converts them to integers.

  • HASH partitioning distributes data to different partitions based on the given number of partitions.
  • Expr is an expression that uses one or more table columns.

Examples are as follows:

CREATE TABLE t1 (col1 INT, col2 CHAR(5))
    PARTITION BY HASH(col1);

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATETIME)
    PARTITION BY HASH ( YEAR(col3) );

*KEY(column_list)

*KEY partitioning divides data by taking the remainder of a field. The partitioning object must be a column, not an expression based on a column, and multiple columns are allowed. KEY partition columns can be unspecified; by default, they are primary key columns or unique key columns. If there is no primary key or unique key, the columns must be explicitly specified.

Similar to HASH. The column_list parameter is simply a list containing 1 or more table columns (maximum: 16). The following example is a simple table partitioned by KEY, with 4 partitions:

CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY KEY(col3)
    PARTITIONS 4;

For tables partitioned by KEY, you can use LINEAR KEY forlinear partitioning. This has the same effect as tables using HASH partitioning. The following example uses LINEAR KEY to distribute data across 5 partitions for linear partitioning:

CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY LINEAR KEY(col3)
    PARTITIONS 5;

*RANGE(expr)

RANGE partitioning: Based on a given continuous range, data (or rather, multiple rows) are assigned to different partitions. The most common use case is based on time fields. It is best if the partitioning column is an integer; if it's a date type, a function can be used to convert it to an integer.

In this case, expr uses a series of values shown with VALUES LESS THAN operators. When using range partitioning, you must use VALUES LESS THAN to define at least one partition, and you cannot use VALUES IN with range partitioning.

VALUES LESS THAN MAXVALUE is used to specify "remaining" values that are less than the specified maximum value.

The clauses are arranged such that each successive upper limit specified in VALUES LESS THAN is greater than the previous one, with the one referencing MAXVALUE appearing last in the list.

*RANGE COLUMNS(column_list)

RANGE COLUMNS(column_list) is another form of RANGE, commonly used for partition pruning queries with range conditions on multiple columns (i.e., conditions such as WHERE a = 1 AND b < 10 or WHERE a = 1 AND b = 10 AND c < 10). It allows you to specify value ranges across multiple columns by using a list of columns in the COLUMNS clause and a set of column values in each PARTITION ... VALUES LESS THAN (value_list) partition definition clause. (In the simplest case, this set consists of a single column.) The maximum number of columns that can be referenced in column_list and value_list is 16.

column_list usage:

  1. column_list can only contain column names.
  2. Each column in the list must be of an integer type, string type, time, or date column type.
  3. BLOB, TEXT, SET, ENUM, BIT, or spatial data types are not allowed; nor are floating-point column types. Functions or arithmetic expressions are also not allowed in the COLUMNS clause.

Partition definition notes:

  1. In the partition definition, the list of values used for each VALUES LESS THAN clause must contain the same number of values as the columns listed in the COLUMNS clause.
  2. NULL cannot appear in any value in VALUES LESS THAN. MAXVALUE can be used multiple times for a given column except for the first column, as shown in the example below:
CREATE TABLE rc (
    a INT NOT NULL,
    b INT NOT NULL
)
PARTITION BY RANGE COLUMNS(a,b) (
    PARTITION p0 VALUES LESS THAN (10,5),
    PARTITION p1 VALUES LESS THAN (20,10),
    PARTITION p2 VALUES LESS THAN (50,MAXVALUE),
    PARTITION p3 VALUES LESS THAN (65,MAXVALUE),
    PARTITION p4 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

*LIST(expr)

LIST partitioning is similar to RANGE partitioning, the difference being that LIST is a collection of enumerated value lists, while RANGE is a collection of continuous interval values. The two are syntactically very similar.

Partition usage:

  1. LIST partition columns are non-null columns. Otherwise, inserting a null value will fail if the enumerated list does not contain a null value. This differs from other partitioning types: RANGE partitioning stores it as the smallest partition value, and HASH or KEY partitioning converts it to 0. Because LIST partitioning only supports integers, non-integer fields need to be converted to integers via a function.
  2. When using LIST partitioning, you must use VALUES IN to define at least one partition, and you cannot use VALUES LESS THAN with PARTITION BY LIST.

Examples are as follows:

CREATE TABLE client_firms (
    id   INT,
    name VARCHAR(35)
)
PARTITION BY LIST (id) (
    PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21),
    PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22),
    PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23),
    PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24)
);

*LIST COLUMNS(column_list)

LIST COLUMNS(column_list) is another way to write LIST, used for partition pruning queries with comparison conditions on multiple columns (i.e., conditions such as WHERE a = 5 AND b = 5 or WHERE a = 1 AND b = 10 AND c = 5). It specifies values in multiple columns by using a list of columns in the COLUMNS clause and a set of column values in each PARTITION ... VALUES IN (value_list) partition definition clause.

The data type rules for the column list used in LIST COLUMNS(column_list) and the value list used in VALUES IN(value_list) are the same as those for RANGE COLUMNS(column_list) and the value list used in VALUES LESS THAN(value_list). However, in the VALUES IN clause, MAXVALUE is not allowed, but NULL can be used.

There is one important difference between the VALUES IN value list used with PARTITION BY LIST COLUMNS and when it's used with PARTITION BY LIST. When used with PARTITION BY LIST COLUMNS, each element in the VALUES IN clause must be a set of column values; the number of values in each set must be the same as the number of columns used in the COLUMNS clause, and these values' data types must match the columns' data types (and appear in the same order). In the simplest case, the set consists of a single column. The maximum number of columns that can be used in column_list and in the elements comprising value_list is 16.

Examples are as follows:

CREATE TABLE lc (
    a INT NULL,
    b INT NULL
)
PARTITION BY LIST COLUMNS(a,b) (
    PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ),
    PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ),
    PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ),
    PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) )
);

*PARTITIONS num

You can optionally use thePARTITIONS num clause to specify the number of partitions, where num is the number of partitions. If this clause is used along with other PARTITION clauses, num must equal the total number of partitions declared using the PARTITION clause.

Examples

  • Example 1: Creating a regular table
CREATE TABLE test(a int, b varchar(10));
INSERT INTO test values(123, 'abc');

mysql> SELECT * FROM test;
+------+---------+
|   a  |    b    |
+------+---------+
|  123 |   abc   |
+------+---------+
  • Example 2: Creating a table with a comment
create table t2 (a int, b int) comment = "事实表";

mysql> show create table t2;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table                                                                          |
+-------+---------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
`a` INT DEFAULT NULL,
`b` INT DEFAULT NULL
) COMMENT='事实表',    |
+-------+---------------------------------------------------------------------------------------+

Here are more examples and a "Limitations" section for the CREATE TABLE statement.

Examples (Continued)

*Example 3: Adding a comment to a column during table creation

```sql
create table t3 (a int comment '列的注释', b int) comment = "table";

mysql> SHOW CREATE TABLE t3;
+-------+----------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
`a` INT DEFAULT NULL COMMENT '列的注释',
`b` INT DEFAULT NULL
) COMMENT='table',     |
+-------+----------------------------------------------------------------------------------------------------------+
```

*Example 4: Creating various types of partitioned tables

```sql
-- Create a basic partitioned table with KEY partitioning and specify 4 partitions
CREATE TABLE tp1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col3) PARTITIONS 4;

mysql> SHOW CREATE TABLE tp1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| tp1   | CREATE TABLE `tp1` (
`col1` INT DEFAULT NULL,
`col2` CHAR(5) DEFAULT NULL,
`col3` DATE DEFAULT NULL
) partition by key algorithm = 2 (col3) partitions 4 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- Create a partitioned table without specifying the number of partitions (default will be used)
CREATE TABLE tp2 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col3);

mysql> SHOW CREATE TABLE tp2;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| tp2   | CREATE TABLE `tp2` (
`col1` INT DEFAULT NULL,
`col2` CHAR(5) DEFAULT NULL,
`col3` DATE DEFAULT NULL
) partition by key algorithm = 2 (col3) |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- Create a partitioned table and specify the partition algorithm
CREATE TABLE tp3
(
    col1 INT,
    col2 CHAR(5),
    col3 DATE
) PARTITION BY KEY ALGORITHM = 1 (col3);


mysql> show create table tp3;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| tp3   | CREATE TABLE `tp3` (
`col1` INT DEFAULT NULL,
`col2` CHAR(5) DEFAULT NULL,
`col3` DATE DEFAULT NULL
) partition by key algorithm = 1 (col3) |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- Create a partitioned table with LINEAR KEY partitioning, specify algorithm and number of partitions
CREATE TABLE tp4 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR KEY ALGORITHM = 1 (col3) PARTITIONS 5;

mysql> SHOW CREATE TABLE tp4;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tp4   | CREATE TABLE `tp4` (
`col1` INT DEFAULT NULL,
`col2` CHAR(5) DEFAULT NULL,
`col3` DATE DEFAULT NULL
) partition by linear key algorithm = 1 (col3) partitions 5 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

-- Create a multi-column partitioned table
CREATE TABLE tp5
(
    col1 INT,
    col2 CHAR(5),
    col3 DATE
) PARTITION BY KEY(col1, col2) PARTITIONS 4;

mysql> SHOW CREATE TABLE tp5;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                   |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tp5   | CREATE TABLE `tp5` (
`col1` INT DEFAULT NULL,
`col2` CHAR(5) DEFAULT NULL,
`col3` DATE DEFAULT NULL
) partition by key algorithm = 2 (col1, col2) partitions 4 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

-- Create a partitioned table using a primary key column for partitioning
CREATE TABLE tp6
(
    col1 INT  NOT NULL PRIMARY KEY,
    col2 DATE NOT NULL,
    col3 INT  NOT NULL,
    col4 INT  NOT NULL
) PARTITION BY KEY(col1) PARTITIONS 4;

mysql> SHOW CREATE TABLE tp6;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                        |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tp6   | CREATE TABLE `tp6` (
`col1` INT NOT NULL,
`col2` DATE NOT NULL,
`col3` INT NOT NULL,
`col4` INT NOT NULL,
PRIMARY KEY (`col1`)
) partition by key algorithm = 2 (col1) partitions 4 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

-- Create a HASH partitioned table
CREATE TABLE tp7
(
    col1 INT,
    col2 CHAR(5)
) PARTITION BY HASH(col1);

mysql> SHOW CREATE TABLE tp7;
+-------+------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                         |
+-------+------------------------------------------------------------------------------------------------------+
| tp7   | CREATE TABLE `tp7` (
`col1` INT DEFAULT NULL,
`col2` CHAR(5) DEFAULT NULL
) partition by hash (col1) |
+-------+------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

-- Create a HASH partitioned table and specify the number of partitions
CREATE TABLE tp8
(
    col1 INT,
    col2 CHAR(5)
) PARTITION BY HASH(col1) PARTITIONS 4;

mysql> SHOW CREATE TABLE tp8;
+-------+-------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------+
| tp8   | CREATE TABLE `tp8` (
`col1` INT DEFAULT NULL,
`col2` CHAR(5) DEFAULT NULL
) partition by hash (col1) partitions 4 |
+-------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- Create a partitioned table and specify the partition granularity (using a function)
CREATE TABLE tp9
(
    col1 INT,
    col2 CHAR(5),
    col3 DATETIME
) PARTITION BY HASH (YEAR(col3));

mysql> SHOW CREATE TABLE tp9;
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                             |
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
| tp9   | CREATE TABLE `tp9` (
`col1` INT DEFAULT NULL,
`col2` CHAR(5) DEFAULT NULL,
`col3` DATETIME DEFAULT NULL
) partition by hash (year(col3)) |
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- Create a partitioned table and specify partition granularity and number of partitions
CREATE TABLE tp10
(
    col1 INT,
    col2 CHAR(5),
    col3 DATE
) PARTITION BY LINEAR HASH( YEAR(col3)) PARTITIONS 6;

mysql> SHOW CREATE TABLE tp10;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                              |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| tp10  | CREATE TABLE `tp10` (
`col1` INT DEFAULT NULL,
`col2` CHAR(5) DEFAULT NULL,
`col3` DATE DEFAULT NULL
) partition by linear hash (year(col3)) partitions 6 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- Create a partitioned table using a primary key column for HASH partitioning
CREATE TABLE tp12 (col1 INT NOT NULL PRIMARY KEY, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL) PARTITION BY HASH(col1) PARTITIONS 4;

mysql> SHOW CREATE TABLE tp12;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tp12  | CREATE TABLE `tp12` (
`col1` INT NOT NULL,
`col2` DATE NOT NULL,
`col3` INT NOT NULL,
`col4` INT NOT NULL,
PRIMARY KEY (`col1`)
) partition by hash (col1) partitions 4 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

-- Create a RANGE partitioned table and define partition ranges
CREATE TABLE tp13 (id INT NOT NULL PRIMARY KEY, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL) PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN (21));

mysql> SHOW CREATE TABLE tp13;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                         |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tp13  | CREATE TABLE `tp13` (
`id` INT NOT NULL,
`fname` VARCHAR(30) DEFAULT NULL,
`lname` VARCHAR(30) DEFAULT NULL,
`hired` DATE DEFAULT '1970-01-01',
`separated` DATE DEFAULT '9999-12-31',
`job_code` INT NOT NULL,
`store_id` INT NOT NULL,
PRIMARY KEY (`id`)
) partition by range(id) (partition p0 values less than (6), partition p1 values less than (11), partition p2 values less than (16), partition p3 values less than (21)) |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

CREATE TABLE tp14 (id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT) PARTITION BY RANGE ( YEAR(separated) ) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1996), PARTITION p2 VALUES LESS THAN (2001), PARTITION p3 VALUES LESS THAN MAXVALUE);

mysql> SHOW CREATE TABLE tp14;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tp14  | CREATE TABLE `tp14` (
`id` INT NOT NULL,
`fname` VARCHAR(30) DEFAULT NULL,
`lname` VARCHAR(30) DEFAULT NULL,
`hired` DATE DEFAULT '1970-01-01',
`separated` DATE DEFAULT '9999-12-31',
`job_code` INT DEFAULT NULL,
`store_id` INT DEFAULT NULL
) partition by range(year(separated)) (partition p0 values less than (1991), partition p1 values less than (1996), partition p2 values less than (2001), partition p3 values less than (MAXVALUE)) |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- Use multiple columns for RANGE partitioning and specify partition ranges
CREATE TABLE tp15 (a INT NOT NULL, b INT NOT NULL) PARTITION BY RANGE COLUMNS(a,b) PARTITIONS 4 (PARTITION p0 VALUES LESS THAN (10,5), PARTITION p1 VALUES LESS THAN (20,10), PARTITION p2 VALUES LESS THAN (50,20), PARTITION p3 VALUES LESS THAN (65,30));

mysql> SHOW CREATE TABLE tp15;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tp15  | CREATE TABLE `tp15` (
`a` INT NOT NULL,
`b` INT NOT NULL
) partition by range columns (a, b) partitions 4 (partition p0 values less than (10, 5), partition p1 values less than (20, 10), partition p2 values less than (50, 20), partition p3 values less than (65, 30)) |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- Create a LIST partitioned table
CREATE TABLE tp16 (id   INT PRIMARY KEY, name VARCHAR(35), age INT unsigned) PARTITION BY LIST (id) (PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21), PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22), PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23), PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24));

mysql> SHOW CREATE TABLE tp16;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                          |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tp16  | CREATE TABLE `tp16` (
`id` INT DEFAULT NULL,
`name` VARCHAR(35) DEFAULT NULL,
`age` INT UNSIGNED DEFAULT NULL,
PRIMARY KEY (`id`)
) partition by list(id) (partition r0 values in (1, 5, 9, 13, 17, 21), partition r1 values in (2, 6, 10, 14, 18, 22), partition r2 values in (3, 7, 11, 15, 19, 23), partition r3 values in (4, 8, 12, 16, 20, 24)) |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

CREATE TABLE tp17 (id   INT, name VARCHAR(35), age INT unsigned) PARTITION BY LIST (id) (PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21), PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22), PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23), PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24));

mysql> SHOW CREATE TABLE tp17;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                      |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tp17  | CREATE TABLE `tp17` (
`id` INT DEFAULT NULL,
`name` VARCHAR(35) DEFAULT NULL,
`age` INT UNSIGNED DEFAULT NULL
) partition by list(id) (partition r0 values in (1, 5, 9, 13, 17, 21), partition r1 values in (2, 6, 10, 14, 18, 22), partition r2 values in (3, 7, 11, 15, 19, 23), partition r3 values in (4, 8, 12, 16, 20, 24)) |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

-- Use multiple columns for LIST partitioning
CREATE TABLE tp18 (a INT NULL,b INT NULL) PARTITION BY LIST COLUMNS(a,b) (PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ), PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ), PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ), PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) ));

mysql> SHOW CREATE TABLE tp18;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tp18  | CREATE TABLE `tp18` (
`a` INT DEFAULT NULL,
`b` INT DEFAULT NULL
) partition by list columns (a, b) (partition p0 values in ((0, 0), (null, null)), partition p1 values in ((0, 1), (0, 2), (0, 3), (1, 1), (1, 2)), partition p2 values in ((1, 0), (2, 0), (2, 1), (3, 0), (3, 1)), partition p3 values in ((1, 3), (2, 2), (2, 3), (3, 2), (3, 3))) |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```

*Example 5: Auto-incrementing primary key behavior

```sql
drop table if exists t1;
create table t1(a bigint primary key auto_increment, b varchar(10));
insert into t1(b) values ('bbb');
insert into t1 values (3, 'ccc');
insert into t1(b) values ('bbb1111');

mysql> select * from t1 order by a;
+------+---------+
| a    | b       |
+------+---------+
|    1 | bbb     |
|    3 | ccc     |
|    4 | bbb1111 |
+------+---------+
3 rows in set (0.01 sec)

insert into t1 values (2, 'aaaa1111');

mysql> select * from t1 order by a;
+------+----------+
| a    | b        |
+------+----------+
|    1 | bbb      |
|    2 | aaaa1111 |
|    3 | ccc      |
|    4 | bbb1111  |
+------+----------+
4 rows in set (0.00 sec)

insert into t1(b) values ('aaaa1111');

mysql> select * from t1 order by a;
+------+----------+
| a    | b        |
+------+----------+
|    1 | bbb      |
|    2 | aaaa1111 |
|    3 | ccc      |
|    4 | bbb1111  |
|    5 | aaaa1111 |
+------+----------+
5 rows in set (0.01 sec)

insert into t1 values (100, 'xxxx');
insert into t1(b) values ('xxxx');

mysql> select * from t1 order by a;
+------+----------+
| a    | b        |
+------+----------+
|    1 | bbb      |
|    2 | aaaa1111 |
|    3 | ccc      |
|    4 | bbb1111  |
|    5 | aaaa1111 |
|  100 | xxxx     |
|  101 | xxxx     |
+------+----------+
7 rows in set (0.00 sec)
```

Limitations

  1. The ALTER TABLE table_name DROP PRIMARY KEY statement isnot supported for dropping primary keys from a table.
  2. The ALTER TABLE table_name AUTO_INCREMENT = n; statement isnot supported for modifying the initial value of an auto-increment column after table creation.
  3. In MatrixOne, setting the increment step for auto-increment columns using the system variable set @@auto_increment_increment=n isonly syntactically supported but does not actually take effect. Similarly, setting the default initial value of auto-increment columns using set @@auto_increment_offset=n isonly syntactically supported but does not actually take effect. Currently, you can set theinitial value of an auto-increment column using AUTO_INCREMENT=n during CREATE TABLE, but theincrement step remains at its default value of 1.