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 the basic syntax for creating a table. TheTEMPORARYkeyword indicates the creation of a temporary table,IF NOT EXISTSmeans to create the table only if it doesn’t already exist, andtbl_nameis 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 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 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 andtable 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"inCREATE 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: 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_INCREMENTmust be of aninteger or floating-point data type.Auto-increment columns must be set as
NOT NULL, otherwiseNULLwill be stored directly. When you insert aNULL(recommended) or 0 value into an indexedAUTO_INCREMENTcolumn, 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_INCREMENTcolumn, which must be indexable and cannot have a default value set.AUTO_INCREMENTcolumns 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 thatAUTO_INCREMENTcolumns 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:
-- 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
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.
MatrixOne only syntactically supports using the system variable
set @@auto_increment_offset=nto 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
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:
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 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 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 toNULL. 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 toRESTRICT, 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 toON 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
WHEREclause 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 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.
HASHpartitioning distributes data to different partitions based on the given number of partitions.Expris 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 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:
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.
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:
LISTpartition 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:RANGEpartitioning stores it as the smallest partition value, andHASHorKEYpartitioning converts it to 0. BecauseLISTpartitioning only supports integers, non-integer fields need to be converted to integers via a function.When using
LISTpartitioning, you must useVALUES INto define at least one partition, and you cannot useVALUES LESS THANwithPARTITION 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)
Define partitions using enumerated values from a single column. Do not use MAXVALUE in VALUES IN.
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
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 = "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
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
-- 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
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¶
The
ALTER TABLE table_name DROP PRIMARY KEYstatement is not supported for dropping primary keys from a table.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.In MatrixOne, setting the increment step for auto-increment columns using the system variable
set @@auto_increment_increment=nis only syntactically supported but does not actually take effect. Similarly, setting the default initial value of auto-increment columns usingset @@auto_increment_offset=nis only syntactically supported but does not actually take effect. Currently, you can set the initial value of an auto-increment column usingAUTO_INCREMENT=nduringCREATE TABLE, but the increment step remains at its default value of 1.