Here's the English translation of the `CREATE TABLE` statement documentation: --- # **CREATE TABLE** ## **Syntax Explanation** The `CREATE TABLE` statement is used to**create a new table in the currently selected database**. ## **Syntax Structure** ```sql 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 the **basic 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 the **column definition part**, used to define each column of the table and its associated attributes. * `[table_options]`: These are**table-level options**, which can set parameters like the table's storage engine or character set. * `[partition_options]`: These are**options 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, including**data type, nullability, default value**, etc. * `[CONSTRAINT [symbol]] PRIMARY KEY`: Defines a**primary key constraint**, allowing you to set the constraint name and the columns forming the primary key. * `[CONSTRAINT [symbol]] FOREIGN KEY`: Defines a**foreign 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's**data type, whether it allows nulls, and its default value**. * `[AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]`: Sets**auto-increment, unique, and primary key constraints**. * `[COMMENT 'string']`: Sets a**comment for the column**. * `[reference_definition]`: An optional reference definition, used to define**foreign key constraints**. The `reference_definition` section is used to define foreign key references, including: * `REFERENCES tbl_name (key_part,...)`: Specifies the **referenced table and columns** for the foreign key. * `[ON DELETE reference_option]`: Sets the **foreign key action upon deletion**. * `[ON UPDATE reference_option]`: Sets the **foreign key action upon update**. `reference_option` represents the **foreign 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 the **initial value for auto-increment columns** and**table comments**. The `partition_options` section is used to define options for partitioned tables, including the **partitioning method, partition key, and number of partitions**. More detailed parameter syntax explanations are provided below. --- #### TEMPORARY When creating a table, you can use the **`TEMPORARY` 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 the **`COMMENT` 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 the **comment for the entire table**, with a maximum length of 2049 characters. * `comment 'comment of column'` in `(name1 type1 [comment 'comment of column'],...)` refers to the **comment 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`: The**initial 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 an**integer 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 have**one `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 the **starting 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: ```sql -- 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 a**primary 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: ```sql CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (ID) ); ``` :::{note} Attention to distinction 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: ```sql CREATE TABLE Students ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (ID,LastName) ); ``` --- #### FOREIGN KEY A**FOREIGN 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 the **parent table**, and the table containing the foreign key is the **child 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 must**already 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. * A**primary 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 a**primary key or candidate key of the parent table**. * The**number of columns in the foreign key must be the same** as the number of columns in the parent table's primary key. * The**data 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: ```text 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`: ```text 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`. ```sql 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 by`**cannot 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 ```sql 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 store**more 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 by**dropping 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 provides**`HASH` 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: ```sql 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: ```sql 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` for **linear 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: ```sql 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)** MatrixOne 4.2.5 uses a single partition-key column for `RANGE COLUMNS` and `LIST COLUMNS`. The examples below use one column. Check column-count and type limits for other instance versions. Use a column name as the partition key and values of that column as partition bounds. A function expression cannot be used as a column name in `COLUMNS`. See [Partition support and limitations](../../Limitations/mo-partition-support.md). ```sql CREATE TABLE rc (a INT NOT NULL, b INT) PARTITION BY RANGE COLUMNS(a) ( PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20), PARTITION pmax VALUES LESS THAN (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: ```sql 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)** Define partitions using enumerated values from a single column. Do not use `MAXVALUE` in `VALUES IN`. ```sql CREATE TABLE lc (a INT, b INT) PARTITION BY LIST COLUMNS(a) ( PARTITION p0 VALUES IN (1, 2), PARTITION p1 VALUES IN (3, 4) ); ``` - **PARTITIONS num** You can optionally use the **`PARTITIONS 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 ```text 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 ```text create table t2 (a int, b int) comment = "fact table"; mysql> show create table t2; +-------+---------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------+ | t2 | CREATE TABLE `t2` ( `a` INT DEFAULT NULL, `b` INT DEFAULT NULL ) COMMENT='fact table', | +-------+---------------------------------------------------------------------------------------+ ``` 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** ```text create table t3 (a int comment 'column comment', b int) comment = "table"; mysql> SHOW CREATE TABLE t3; +-------+----------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------+ | t3 | CREATE TABLE `t3` ( `a` INT DEFAULT NULL COMMENT 'column comment', `b` INT DEFAULT NULL ) COMMENT='table', | +-------+----------------------------------------------------------------------------------------------------------+ ``` - **Example 4: Creating various types of partitioned tables** ```text -- 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 one column for RANGE COLUMNS partitioning CREATE TABLE tp15 (a INT NOT NULL, b INT) PARTITION BY RANGE COLUMNS(a) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20), PARTITION pmax VALUES LESS THAN (MAXVALUE)); SHOW CREATE TABLE tp15; -- 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 one column for LIST COLUMNS partitioning CREATE TABLE tp18 (a INT, b INT) PARTITION BY LIST COLUMNS(a) (PARTITION p0 VALUES IN (1,2), PARTITION p1 VALUES IN (3,4)); SHOW CREATE TABLE tp18; ``` - **Example 5: Auto-incrementing primary key behavior** ```text 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 is **not supported** for dropping primary keys from a table. 2. The `ALTER TABLE table_name AUTO_INCREMENT = n;` statement is **not 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` is **only syntactically supported but does not actually take effect**. Similarly, setting the default initial value of auto-increment columns using `set @@auto_increment_offset=n` is **only syntactically supported but does not actually take effect**. Currently, you can set the **initial value** of an auto-increment column using `AUTO_INCREMENT=n` during `CREATE TABLE`, but the **increment step remains at its default value of 1**.