CREATE TABLE

Description

Create a new table.

Syntax

  1. > CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
  2.     (create_definition,...)
  3.     [table_options]
  4.     [partition_options]
  6. create_definition: {
  7.     col_name column_definition
  8.   | [CONSTRAINT [symbol]] PRIMARY KEY
  9.       [index_type] (key_part,...)
  10.       [index_option] ...
  11.   | [CONSTRAINT [symbol]] FOREIGN KEY
  12.       [index_name] (col_name,...)
  13.       reference_definition
  14. }
  16. column_definition: {
  17.     data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
  18.       [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
  19.       [COMMENT 'string']
  20.       [reference_definition]
  21.   | data_type
  22.       [[PRIMARY] KEY]
  23.       [COMMENT 'string']
  24.       [reference_definition]
  25. }
  27. reference_definition:
  28.     REFERENCES tbl_name (key_part,...)
  29.       [ON DELETE reference_option]
  30.       [ON UPDATE reference_option]
  32. reference_option:
  33.     RESTRICT | CASCADE | SET NULL | NO ACTION
  35. table_options:
  36.     table_option [[,] table_option] ...
  38. table_option: {
  39.   | AUTO_INCREMENT [=] value
  40.   | COMMENT [=] 'string'
  41.   | START TRANSACTION
  42. }
  44. partition_options:
  45.     PARTITION BY
  46.         { [LINEAR] HASH(expr)
  47.         | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)}
  48.     [PARTITIONS num]
  49.     [(partition_definition [, partition_definition] ...)]
  51. partition_definition:
  52.  PARTITION partition_name
  53.      [VALUES
  54.          {LESS THAN {(expr | value_list) | MAXVALUE}
  55.          |
  56.          IN (value_list)}]
  57.      [COMMENT [=] 'string' ]

Explanations

Various parameters and options that can be used when creating a table, including table creation, column definition, constraints, options, and partitioning, are explained below:

  • CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name: This is the primary table syntax. The TEMPORARY keyword indicates creating a temporary table, IF NOT EXISTS ensures creation only if the table doesn’t exist, and tbl_name is the name of the table to be created.

  • (create_definition,...): This is the section for column definitions, used to define the table’s columns and their attributes.

  • [table_options]: This is for table-level options where you can set parameters like storage engine, character set, etc.

  • [partition_options]: This is used for partitioned tables and defining partitioning methods and keys.

The create_definition section is used to define attributes for each column, and it can contain the following:

  • col_name column_definition: This defines the column name and its attributes, including data type, whether it can be null, default value, etc.

  • [CONSTRAINT [symbol]] PRIMARY KEY: This defines a primary key constraint and can set a constraint name and the columns that make up the primary key.

  • [CONSTRAINT [symbol]] FOREIGN KEY: This defines a foreign key constraint and can set a constraint name, columns for the foreign key, and the referenced table.

The column_definition section is used to define attributes for specific columns and can include the following:

  • data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]: This defines the data type of the column, whether it can be null, and its default value.

  • [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]: This sets options like auto-increment, uniqueness, and primary key constraint.

  • [COMMENT 'string']: This sets a comment for the column.

  • [reference_definition]: This is an optional reference definition used to define foreign key constraints.

The reference_definition section is used to define references for foreign keys and includes the following:

  • REFERENCES tbl_name (key_part,...): This specifies the referenced table and columns for the foreign key.

  • [ON DELETE reference_option]: This sets the action to be taken when a referenced row is deleted.

  • [ON UPDATE reference_option]: This sets the action to be taken when a referenced row is updated.

reference_option represents the options for foreign key actions, including RESTRICT, CASCADE, SET NULL, and NO ACTION.

The table_options section sets table-level options, including initial auto-increment value, table comments, etc.

The partition_options section defines options for partitioned tables, including partitioning methods, partition keys, and the number of partitions.

For more detailed syntax explanations, see the following content.

Temporary Tables

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only within the current session, and is dropped automatically when the session is closed. This means that 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. (The existing table is hidden until the temporary table is dropped.)

Dropping a database does automatically drop any TEMPORARY tables created within that database.

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

COMMENT

A comment for a column or a table can be specified with the COMMENT option.

  • Up to 1024 characters long. The comment is displayed by the SHOW CREATE TABLE and SHOW FULL COLUMNS statements. It is also shown in the COLUMN_COMMENT column of the INFORMATION_SCHEMA.COLUMNS table.

AUTO_INCREMENT

The initial AUTO_INCREMENT value for the table.

An integer column can have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value. Typically this is value+1, where the value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1 default.

There can be only one AUTO_INCREMENT column per table, which must be indexed and cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0.

You can use the AUTO_INCREMENT attribute to define the starting value of an auto-increment column. If you want to set the starting value of the auto-increment column to 10, you can use the AUTO_INCREMENT keyword when creating the table and specify the starting value later.

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

  1. -- set up
  2. create table t1(a int auto_increment primary key) auto_increment = 10;

In this example, the id column is an auto-incrementing column with a starting value 10. When a new record is inserted into the table, the value of the id column will start from 10 and increment by 1 each time. If the starting value of AUTO_INCREMENT is not specified, the default starting value is 1, which is automatically incremented by 1 each time.

Note

  1. MatrixOne currently only supports the default increment step size of 1; regardless of the initial value of the auto-increment column, each auto-increment is 1. Temporarily does not support setting the incremental step size.
  2. MatrixOne only syntax supports using the system variable set @@auto_increment_offset=n to set the initial value of the auto-increment column, but it does not take effect.

PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each record in a table.

Primary keys must contain UNIQUE values, and cannot contain NULL values.

A table can have only ONE primary key; and in the table, this primary key can consist of single column (field).

  • SQL PRIMARY KEY on CREATE TABLE

The following SQL creates a PRIMARY KEY on the “ID” column when the “Persons” table is created:

  1. > CREATE TABLE Persons (
  2.     ID int NOT NULL,
  3.     LastName varchar(255) NOT NULL,
  4.     FirstName varchar(255),
  5.     Age int,
  6.     PRIMARY KEY (ID)
  7. );

FOREIGN KEY

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.

The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the parent table.

When defining FOREIGN KEY, the following rules need to be followed:

  • The parent table must already exist in the database or be a table currently being created. In the latter case, the parent table and the slave table are the same table, such a table is called a self-referential table, and this structure is called self-referential integrity.

  • A primary key must be defined for the parent table.

  • Specify the column name or combination of column names after the table name of the parent table. This column or combination of columns must be the primary or candidate key of the primary table.

  • The number of columns in the foreign key must be the same as the number of columns in the primary key of the parent table.

  • The data type of the column in the foreign key must be the same as the data type of the corresponding column in the primary key of the parent table.

The following is an example to illustrate the association of parent and child tables through FOREIGN KEY and PRIMARY KEY:

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

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

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

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

In addition, [ON DELETE reference_option] and [ON UPDATE reference_option] are used when defining a foreign key relationship to specify actions to be taken when records in the parent table are deleted or updated. These two parameters are primarily used to maintain data integrity and consistency:

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

    • RESTRICT: If related foreign key data exists in the referenced table, deletion of data in the table is not allowed. This prevents accidental deletion of related data, ensuring data consistency.

    • CASCADE: When data in the referenced table is deleted, associated foreign key data is also deleted. This is used for cascading deletion of related data to maintain data integrity.

    • SET NULL: When data in the referenced table is deleted, the value of the foreign key column is set to NULL. This is used to retain foreign key data while disconnecting it from the referenced data upon deletion.

    • NO ACTION: Indicates no action is taken; it only checks for the existence of associated data. This is similar to RESTRICT but may have minor differences in some databases.

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

See the example below:

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

  1. CREATE TABLE Customers (
  2.     id INT PRIMARY KEY,
  3.     name VARCHAR(50)
  4. );
  6. CREATE TABLE Orders (
  7.     id INT PRIMARY KEY,
  8.     order_number VARCHAR(10),
  9.     customer_id INT,
  10.     FOREIGN KEY (customer_id) REFERENCES Customers(id) ON DELETE CASCADE
  11. );

In the above example, when a customer is deleted from the Customers table, the associated order data will also be deleted through cascading, maintaining data integrity. Similarly, the ON UPDATE parameter can handle update operations.

For more information on data integrity constraints, see Data Integrity Constraints Overview.

Cluster by

Cluster by is a command used to optimize the physical arrangement of a table. When creating a table, the Cluster by command can physically sort the table based on a specified column for tables without a primary key. It will rearrange the data rows to match the order of values in that column. Using Cluster by improves query performance.

  • The syntax for a single column is: create table() cluster by col;
  • The syntax for multiple columns is: create table() cluster by (col1, col2);

Note: Cluster by cannot coexist with a primary key, or a syntax error will occur. Cluster by can only be specified when creating a table and does not support dynamic creation.

For more information on using Cluster by for performing tuning, see Using Cluster by for performance tuning.

Table PARTITION and PARTITIONS

  1. partition_options:
  2.  PARTITION BY
  3.      { [LINEAR] HASH(expr)
  4.      | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
  5.  [PARTITIONS num]
  6.  [(partition_definition [, partition_definition] ...)]
  8. partition_definition:
  9.  PARTITION partition_name
  10.      [VALUES
  11.          {LESS THAN {(expr | value_list) | MAXVALUE}
  12.          |
  13.          IN (value_list)}]
  14.      [COMMENT [=] 'string' ]

Partitions can be modified, merged, added to tables, and dropped from tables.

  • PARTITION BY

If used, a partition_options clause begins with PARTITION BY. This clause contains the function that is used to determine the partition; the function returns an integer value ranging from 1 to num, where num is the number of partitions.

  • HASH(expr)

Hashes one or more columns to create a key for placing and locating rows. expr is an expression using one or more table columns. For example, these are both valid CREATE TABLE statements using PARTITION BY HASH:

  1. CREATE TABLE t1 (col1 INT, col2 CHAR(5))
  2.     PARTITION BY HASH(col1);
  4. CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATETIME)
  5.     PARTITION BY HASH ( YEAR(col3) );
  • KEY(column_list)

This is similar to HASH. The column_list argument is simply a list of 1 or more table columns (maximum: 16). This example shows a simple table partitioned by key, with 4 partitions:

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

For tables that are partitioned by key, you can employ linear partitioning by using the LINEAR keyword. This has the same effect as with tables that are partitioned by HASH. This example uses linear partitioning by key to distribute data between 5 partitions:

  1. CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)
  2.     PARTITION BY LINEAR KEY(col3)
  3.     PARTITIONS 5;
  • RANGE(expr)

In this case, expr shows a range of values using a set of VALUES LESS THAN operators. When using range partitioning, you must define at least one partition using VALUES LESS THAN. You cannot use VALUES IN with range partitioning.

PARTITION ... VALUES LESS THAN ... statements work in a consecutive fashion. VALUES LESS THAN MAXVALUE works to specify “leftover” values that are greater than the maximum value otherwise specified.

The clauses must be arranged in such a way that the upper limit specified in each successive VALUES LESS THAN is greater than that of the previous one, with the one referencing MAXVALUE coming last of all in the list.

  • PARTITIONS num

The number of partitions may optionally be specified with a PARTITIONS num clause, where num is the number of partitions. If both this clause and any PARTITION clauses are used, num must be equal to the total number of any partitions that are declared using PARTITION clauses.

Examples

  • Example 1: Create a common table
  1. CREATE TABLE test(a int, b varchar(10));
  2. INSERT INTO test values(123, 'abc');
  4. mysql> SELECT * FROM test;
  5. +------+---------+
  6. |   a  |    b    |
  7. +------+---------+
  8. |  123 |   abc   |
  9. +------+---------+
  • Example 2: Add comments when creating a table
  1. create table t2 (a int, b int) comment = "fact table";
  3. mysql> show create table t2;
  4. +-------+---------------------------------------------------------------------------------------+
  5. | Table | Create Table                                                                          |
  6. +-------+---------------------------------------------------------------------------------------+
  7. | t2    | CREATE TABLE `t2` (
  8. `a` INT DEFAULT NULL,
  9. `b` INT DEFAULT NULL
  10. ) COMMENT='fact table',    |
  11. +-------+---------------------------------------------------------------------------------------+
  • Example 3: Add comments to columns when creating tables
  1. create table t3 (a int comment 'Column comment', b int) comment = "table";
  3. mysql> SHOW CREATE TABLE t3;
  4. +-------+----------------------------------------------------------------------------------------------------------+
  5. | Table | Create Table                                                                                             |
  6. +-------+----------------------------------------------------------------------------------------------------------+
  7. | t3    | CREATE TABLE `t3` (
  8. `a` INT DEFAULT NULL COMMENT 'Column comment',
  9. `b` INT DEFAULT NULL
  10. ) COMMENT='table',     |
  11. +-------+----------------------------------------------------------------------------------------------------------+
  • Example 4: Create a common partitioned table
  1. CREATE TABLE tp1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col3) PARTITIONS 4;
  3. mysql> SHOW CREATE TABLE tp1;
  4. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | Table | Create Table                                                                                                                                             |
  6. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
  7. | tp1   | CREATE TABLE `tp1` (
  8. `col1` INT DEFAULT NULL,
  9. `col2` CHAR(5) DEFAULT NULL,
  10. `col3` DATE DEFAULT NULL
  11. ) partition by key algorithm = 2 (col3) partitions 4 |
  12. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
  13. 1 row in set (0.00 sec)
  15. -- do not specify the number of partitions
  16. CREATE TABLE tp2 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col3);
  18. mysql> SHOW CREATE TABLE tp2;
  19. +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
  20. | Table | Create Table                                                                                                                                |
  21. +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
  22. | tp2   | CREATE TABLE `tp2` (
  23. `col1` INT DEFAULT NULL,
  24. `col2` CHAR(5) DEFAULT NULL,
  25. `col3` DATE DEFAULT NULL
  26. ) partition by key algorithm = 2 (col3) |
  27. +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
  28. 1 row in set (0.00 sec)
  30. -- Specify partition algorithm
  31. CREATE TABLE tp3
  32. (
  33.     col1 INT,
  34.     col2 CHAR(5),
  35.     col3 DATE
  36. ) PARTITION BY KEY ALGORITHM = 1 (col3);
  39. mysql> show create table tp3;
  40. +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
  41. | Table | Create Table                                                                                                                                |
  42. +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
  43. | tp3   | CREATE TABLE `tp3` (
  44. `col1` INT DEFAULT NULL,
  45. `col2` CHAR(5) DEFAULT NULL,
  46. `col3` DATE DEFAULT NULL
  47. ) partition by key algorithm = 1 (col3) |
  48. +-------+---------------------------------------------------------------------------------------------------------------------------------------------+
  49. 1 row in set (0.00 sec)
  51. -- Specify partition algorithm and the number of partitions
  52. CREATE TABLE tp4 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR KEY ALGORITHM = 1 (col3) PARTITIONS 5;
  54. mysql> SHOW CREATE TABLE tp4;
  55. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
  56. | Table | Create Table                                                                                                                                                    |
  57. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
  58. | tp4   | CREATE TABLE `tp4` (
  59. `col1` INT DEFAULT NULL,
  60. `col2` CHAR(5) DEFAULT NULL,
  61. `col3` DATE DEFAULT NULL
  62. ) partition by linear key algorithm = 1 (col3) partitions 5 |
  63. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
  64. 1 row in set (0.01 sec)
  66. -- Multi-column partition
  67. CREATE TABLE tp5
  68. (
  69.     col1 INT,
  70.     col2 CHAR(5),
  71.     col3 DATE
  72. ) PARTITION BY KEY(col1, col2) PARTITIONS 4;
  74. mysql> SHOW CREATE TABLE tp5;
  75. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
  76. | Table | Create Table                                                                                                                                                   |
  77. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
  78. | tp5   | CREATE TABLE `tp5` (
  79. `col1` INT DEFAULT NULL,
  80. `col2` CHAR(5) DEFAULT NULL,
  81. `col3` DATE DEFAULT NULL
  82. ) partition by key algorithm = 2 (col1, col2) partitions 4 |
  83. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
  84. 1 row in set (0.01 sec)
  86. -- Create a primary key column partition
  87. CREATE TABLE tp6
  88. (
  89.     col1 INT  NOT NULL PRIMARY KEY,
  90.     col2 DATE NOT NULL,
  91.     col3 INT  NOT NULL,
  92.     col4 INT  NOT NULL
  93. ) PARTITION BY KEY(col1) PARTITIONS 4;
  95. mysql> SHOW CREATE TABLE tp6;
  96. +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  97. | Table | Create Table                                                                                                                                                                        |
  98. +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  99. | tp6   | CREATE TABLE `tp6` (
  100. `col1` INT NOT NULL,
  101. `col2` DATE NOT NULL,
  102. `col3` INT NOT NULL,
  103. `col4` INT NOT NULL,
  104. PRIMARY KEY (`col1`)
  105. ) partition by key algorithm = 2 (col1) partitions 4 |
  106. +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  107. 1 row in set (0.01 sec)
  109. -- Create HASH partition
  110. CREATE TABLE tp7
  111. (
  112.     col1 INT,
  113.     col2 CHAR(5)
  114. ) PARTITION BY HASH(col1);
  116. mysql> SHOW CREATE TABLE tp7;
  117. +-------+------------------------------------------------------------------------------------------------------+
  118. | Table | Create Table                                                                                         |
  119. +-------+------------------------------------------------------------------------------------------------------+
  120. | tp7   | CREATE TABLE `tp7` (
  121. `col1` INT DEFAULT NULL,
  122. `col2` CHAR(5) DEFAULT NULL
  123. ) partition by hash (col1) |
  124. +-------+------------------------------------------------------------------------------------------------------+
  125. 1 row in set (0.01 sec)
  127. -- Specifies the number of HASH partitions when creating hash partition
  128. CREATE TABLE tp8
  129. (
  130.     col1 INT,
  131.     col2 CHAR(5)
  132. ) PARTITION BY HASH(col1) PARTITIONS 4;
  134. mysql> SHOW CREATE TABLE tp8;
  135. +-------+-------------------------------------------------------------------------------------------------------------------+
  136. | Table | Create Table                                                                                                      |
  137. +-------+-------------------------------------------------------------------------------------------------------------------+
  138. | tp8   | CREATE TABLE `tp8` (
  139. `col1` INT DEFAULT NULL,
  140. `col2` CHAR(5) DEFAULT NULL
  141. ) partition by hash (col1) partitions 4 |
  142. +-------+-------------------------------------------------------------------------------------------------------------------+
  143. 1 row in set (0.00 sec)
  145. -- specify the partition granularity when creating a partition
  146. CREATE TABLE tp9
  147. (
  148.     col1 INT,
  149.     col2 CHAR(5),
  150.     col3 DATETIME
  151. ) PARTITION BY HASH (YEAR(col3));
  153. mysql> SHOW CREATE TABLE tp9;
  154. +-------+------------------------------------------------------------------------------------------------------------------------------------------+
  155. | Table | Create Table                                                                                                                             |
  156. +-------+------------------------------------------------------------------------------------------------------------------------------------------+
  157. | tp9   | CREATE TABLE `tp9` (
  158. `col1` INT DEFAULT NULL,
  159. `col2` CHAR(5) DEFAULT NULL,
  160. `col3` DATETIME DEFAULT NULL
  161. ) partition by hash (year(col3)) |
  162. +-------+------------------------------------------------------------------------------------------------------------------------------------------+
  163. 1 row in set (0.00 sec)
  165. -- specify the partition granularity and number of partitions when creating a partition
  166. CREATE TABLE tp10
  167. (
  168.     col1 INT,
  169.     col2 CHAR(5),
  170.     col3 DATE
  171. ) PARTITION BY LINEAR HASH( YEAR(col3)) PARTITIONS 6;
  173. mysql> SHOW CREATE TABLE tp10;
  174. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
  175. | Table | Create Table                                                                                                                                              |
  176. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
  177. | tp10  | CREATE TABLE `tp10` (
  178. `col1` INT DEFAULT NULL,
  179. `col2` CHAR(5) DEFAULT NULL,
  180. `col3` DATE DEFAULT NULL
  181. ) partition by linear hash (year(col3)) partitions 6 |
  182. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
  183. 1 row in set (0.00 sec)
  185. -- Use the primary key column as the HASH partition when creating a partition
  186. 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;
  188. mysql> SHOW CREATE TABLE tp12;
  189. +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  190. | Table | Create Table                                                                                                                                                            |
  191. +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  192. | tp12  | CREATE TABLE `tp12` (
  193. `col1` INT NOT NULL,
  194. `col2` DATE NOT NULL,
  195. `col3` INT NOT NULL,
  196. `col4` INT NOT NULL,
  197. PRIMARY KEY (`col1`)
  198. ) partition by hash (col1) partitions 4 |
  199. +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  200. 1 row in set (0.01 sec)
  • Example 5: Primary key auto increment
  1. drop table if exists t1;
  2. create table t1(a bigint primary key auto_increment,
  3.     b varchar(10));
  4. insert into t1(b) values ('bbb');
  5. insert into t1 values (3, 'ccc');
  6. insert into t1(b) values ('bbb1111');
  8. mysql> select * from t1 order by a;
  9. +------+---------+
  10. | a    | b       |
  11. +------+---------+
  12. |    1 | bbb     |
  13. |    3 | ccc     |
  14. |    4 | bbb1111 |
  15. +------+---------+
  16. 3 rows in set (0.01 sec)
  18. insert into t1 values (2, 'aaaa1111');
  20. mysql> select * from t1 order by a;
  21. +------+----------+
  22. | a    | b        |
  23. +------+----------+
  24. |    1 | bbb      |
  25. |    2 | aaaa1111 |
  26. |    3 | ccc      |
  27. |    4 | bbb1111  |
  28. +------+----------+
  29. 4 rows in set (0.00 sec)
  31. insert into t1(b) values ('aaaa1111');
  33. mysql> select * from t1 order by a;
  34. +------+----------+
  35. | a    | b        |
  36. +------+----------+
  37. |    1 | bbb      |
  38. |    2 | aaaa1111 |
  39. |    3 | ccc      |
  40. |    4 | bbb1111  |
  41. |    5 | aaaa1111 |
  42. +------+----------+
  43. 5 rows in set (0.01 sec)
  45. insert into t1 values (100, 'xxxx');
  46. insert into t1(b) values ('xxxx');
  48. mysql> select * from t1 order by a;
  49. +------+----------+
  50. | a    | b        |
  51. +------+----------+
  52. |    1 | bbb      |
  53. |    2 | aaaa1111 |
  54. |    3 | ccc      |
  55. |    4 | bbb1111  |
  56. |    5 | aaaa1111 |
  57. |  100 | xxxx     |
  58. |  101 | xxxx     |
  59. +------+----------+
  60. 7 rows in set (0.00 sec)

Constraints

  1. Currently, it is not supported to use the ALTER TABLE table_name DROP PRIMARY KEY statement to drop the primary key from a table.
  2. The ALTER TABLE table_name AUTO_INCREMENT = n; statement is not supported to modify the initial value of the auto-increment column.
  3. In MatrixOne, only syntax supports using the system variable set @@auto_increment_increment=n to set the incremental step size, and only syntax supports using the system variable set @@auto_increment_offset=n to set the default auto-increment column initial value, but it does not take effect; currently supports setting the initial value AUTO_INCREMENT=n of the auto-increment column, but the step size is still 1 by default.