CREATE TABLE

This statement creates a new table in the currently selected database. It behaves similarly to the CREATE TABLE statement in MySQL.

Synopsis

CreateTableStmt

CREATE TABLE - 图1

OptTemporary

CREATE TABLE - 图2

IfNotExists

CREATE TABLE - 图3

TableName

CREATE TABLE - 图4

TableElementListOpt

CREATE TABLE - 图5

TableElementList

CREATE TABLE - 图6

TableElement

CREATE TABLE - 图7

ColumnDef

CREATE TABLE - 图8

ColumnOptionListOpt

CREATE TABLE - 图9

ColumnOptionList

CREATE TABLE - 图10

ColumnOption

CREATE TABLE - 图11

Constraint

CREATE TABLE - 图12

IndexDef

CREATE TABLE - 图13

KeyPartList

CREATE TABLE - 图14

KeyPart

CREATE TABLE - 图15

IndexOption

CREATE TABLE - 图16

ForeignKeyDef

CREATE TABLE - 图17

ReferenceOption

CREATE TABLE - 图18

CreateTableOptionListOpt

CREATE TABLE - 图19

PartitionOpt

CREATE TABLE - 图20

DuplicateOpt

CREATE TABLE - 图21

TableOptionList

CREATE TABLE - 图22

TableOption

CREATE TABLE - 图23

OnCommitOpt

CREATE TABLE - 图24

PlacementPolicyOption

CREATE TABLE - 图25

  1. CreateTableStmt ::=
  2. 'CREATE' OptTemporary 'TABLE' IfNotExists TableName ( TableElementListOpt CreateTableOptionListOpt PartitionOpt DuplicateOpt AsOpt CreateTableSelectOpt | LikeTableWithOrWithoutParen ) OnCommitOpt
  3. OptTemporary ::=
  4. ( 'TEMPORARY' | ('GLOBAL' 'TEMPORARY') )?
  5. IfNotExists ::=
  6. ('IF' 'NOT' 'EXISTS')?
  7. TableName ::=
  8. Identifier ('.' Identifier)?
  9. TableElementListOpt ::=
  10. ( '(' TableElementList ')' )?
  11. TableElementList ::=
  12. TableElement ( ',' TableElement )*
  13. TableElement ::=
  14. ColumnDef
  15. | Constraint
  16. ColumnDef ::=
  17. ColumnName ( Type | 'SERIAL' ) ColumnOptionListOpt
  18. ColumnOptionListOpt ::=
  19. ColumnOption*
  20. ColumnOptionList ::=
  21. ColumnOption*
  22. ColumnOption ::=
  23. 'NOT'? 'NULL'
  24. | 'AUTO_INCREMENT'
  25. | PrimaryOpt 'KEY'
  26. | 'UNIQUE' 'KEY'?
  27. | 'DEFAULT' DefaultValueExpr
  28. | 'SERIAL' 'DEFAULT' 'VALUE'
  29. | 'ON' 'UPDATE' NowSymOptionFraction
  30. | 'COMMENT' stringLit
  31. | ConstraintKeywordOpt 'CHECK' '(' Expression ')' EnforcedOrNotOrNotNullOpt
  32. | GeneratedAlways 'AS' '(' Expression ')' VirtualOrStored
  33. | ReferDef
  34. | 'COLLATE' CollationName
  35. | 'COLUMN_FORMAT' ColumnFormat
  36. | 'STORAGE' StorageMedia
  37. | 'AUTO_RANDOM' OptFieldLen
  38. Constraint ::=
  39. IndexDef
  40. | ForeignKeyDef
  41. IndexDef ::=
  42. ( 'INDEX' | 'KEY' ) IndexName? '(' KeyPartList ')' IndexOption?
  43. KeyPartList ::=
  44. KeyPart ( ',' KeyPart )*
  45. KeyPart ::=
  46. ColumnName ( '(' Length ')')? ( 'ASC' | 'DESC' )?
  47. | '(' Expression ')' ( 'ASC' | 'DESC' )?
  48. IndexOption ::=
  49. 'COMMENT' String
  50. | ( 'VISIBLE' | 'INVISIBLE' )
  51. ForeignKeyDef
  52. ::= ( 'CONSTRAINT' Identifier )? 'FOREIGN' 'KEY'
  53. Identifier? '(' ColumnName ( ',' ColumnName )* ')'
  54. 'REFERENCES' TableName '(' ColumnName ( ',' ColumnName )* ')'
  55. ( 'ON' 'DELETE' ReferenceOption )?
  56. ( 'ON' 'UPDATE' ReferenceOption )?
  57. ReferenceOption
  58. ::= 'RESTRICT'
  59. | 'CASCADE'
  60. | 'SET' 'NULL'
  61. | 'SET' 'DEFAULT'
  62. | 'NO' 'ACTION'
  63. CreateTableOptionListOpt ::=
  64. TableOptionList?
  65. PartitionOpt ::=
  66. ( 'PARTITION' 'BY' PartitionMethod PartitionNumOpt SubPartitionOpt PartitionDefinitionListOpt )?
  67. DuplicateOpt ::=
  68. ( 'IGNORE' | 'REPLACE' )?
  69. TableOptionList ::=
  70. TableOption ( ','? TableOption )*
  71. TableOption ::=
  72. PartDefOption
  73. | DefaultKwdOpt ( CharsetKw EqOpt CharsetName | 'COLLATE' EqOpt CollationName )
  74. | ( 'AUTO_INCREMENT' | 'AUTO_ID_CACHE' | 'AUTO_RANDOM_BASE' | 'AVG_ROW_LENGTH' | 'CHECKSUM' | 'TABLE_CHECKSUM' | 'KEY_BLOCK_SIZE' | 'DELAY_KEY_WRITE' | 'SHARD_ROW_ID_BITS' | 'PRE_SPLIT_REGIONS' ) EqOpt LengthNum
  75. | ( 'CONNECTION' | 'PASSWORD' | 'COMPRESSION' ) EqOpt stringLit
  76. | RowFormat
  77. | ( 'STATS_PERSISTENT' | 'PACK_KEYS' ) EqOpt StatsPersistentVal
  78. | ( 'STATS_AUTO_RECALC' | 'STATS_SAMPLE_PAGES' ) EqOpt ( LengthNum | 'DEFAULT' )
  79. | 'STORAGE' ( 'MEMORY' | 'DISK' )
  80. | 'SECONDARY_ENGINE' EqOpt ( 'NULL' | StringName )
  81. | 'UNION' EqOpt '(' TableNameListOpt ')'
  82. | 'ENCRYPTION' EqOpt EncryptionOpt
  83. | 'TTL' EqOpt TimeColumnName '+' 'INTERVAL' Expression TimeUnit (TTLEnable EqOpt ( 'ON' | 'OFF' ))? (TTLJobInterval EqOpt stringLit)?
  84. | PlacementPolicyOption
  85. OnCommitOpt ::=
  86. ('ON' 'COMMIT' 'DELETE' 'ROWS')?
  87. PlacementPolicyOption ::=
  88. "PLACEMENT" "POLICY" EqOpt PolicyName
  89. | "PLACEMENT" "POLICY" (EqOpt | "SET") "DEFAULT"

The following table_options are supported. Other options such as AVG_ROW_LENGTH, CHECKSUM, COMPRESSION, CONNECTION, DELAY_KEY_WRITE, ENGINE, KEY_BLOCK_SIZE, MAX_ROWS, MIN_ROWS, ROW_FORMAT and STATS_PERSISTENT are parsed but ignored.

OptionsDescriptionExample
AUTO_INCREMENTThe initial value of the increment fieldAUTO_INCREMENT = 5
SHARD_ROW_ID_BITSTo set the number of bits for the implicit _tidb_rowid shardsSHARD_ROW_ID_BITS = 4
PRE_SPLIT_REGIONSTo pre-split 2^(PRE_SPLIT_REGIONS) Regions when creating a tablePRE_SPLIT_REGIONS = 4
AUTO_ID_CACHETo set the auto ID cache size in a TiDB instance. By default, TiDB automatically changes this size according to allocation speed of auto IDAUTO_ID_CACHE = 200. Note that this option is not available on TiDB Serverless clusters.
AUTO_RANDOM_BASETo set the initial incremental part value of auto_random. This option can be considered as a part of the internal interface. Users can ignore this parameterAUTO_RANDOM_BASE = 0
CHARACTER SETTo specify the character set for the tableCHARACTER SET = ‘utf8mb4’
COMMENTThe comment informationCOMMENT = ‘comment info’

CREATE TABLE - 图26

Note

The split-table configuration option is enabled by default. When it is enabled, a separate Region is created for each newly created table. For details, see TiDB configuration file.

CREATE TABLE - 图27

Note

TiDB creates a separate Region for each newly created table.

Examples

Creating a simple table and inserting one row:

  1. CREATE TABLE t1 (a int);
  2. DESC t1;
  3. SHOW CREATE TABLE t1\G
  4. INSERT INTO t1 (a) VALUES (1);
  5. SELECT * FROM t1;
  1. mysql> drop table if exists t1;
  2. Query OK, 0 rows affected (0.23 sec)
  3. mysql> CREATE TABLE t1 (a int);
  4. Query OK, 0 rows affected (0.09 sec)
  5. mysql> DESC t1;
  6. +-------+---------+------+------+---------+-------+
  7. | Field | Type | Null | Key | Default | Extra |
  8. +-------+---------+------+------+---------+-------+
  9. | a | int(11) | YES | | NULL | |
  10. +-------+---------+------+------+---------+-------+
  11. 1 row in set (0.00 sec)
  12. mysql> SHOW CREATE TABLE t1\G
  13. *************************** 1. row ***************************
  14. Table: t1
  15. Create Table: CREATE TABLE `t1` (
  16. `a` int(11) DEFAULT NULL
  17. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
  18. 1 row in set (0.00 sec)
  19. mysql> INSERT INTO t1 (a) VALUES (1);
  20. Query OK, 1 row affected (0.03 sec)
  21. mysql> SELECT * FROM t1;
  22. +------+
  23. | a |
  24. +------+
  25. | 1 |
  26. +------+
  27. 1 row in set (0.00 sec)

Dropping a table if it exists, and conditionally creating a table if it does not exist:

  1. DROP TABLE IF EXISTS t1;
  2. CREATE TABLE IF NOT EXISTS t1 (
  3. id BIGINT NOT NULL PRIMARY KEY auto_increment,
  4. b VARCHAR(200) NOT NULL
  5. );
  6. DESC t1;
  1. mysql> DROP TABLE IF EXISTS t1;
  2. Query OK, 0 rows affected (0.22 sec)
  3. mysql> CREATE TABLE IF NOT EXISTS t1 (
  4. id BIGINT NOT NULL PRIMARY KEY auto_increment,
  5. b VARCHAR(200) NOT NULL
  6. );
  7. Query OK, 0 rows affected (0.08 sec)
  8. mysql> DESC t1;
  9. +-------+--------------+------+------+---------+----------------+
  10. | Field | Type | Null | Key | Default | Extra |
  11. +-------+--------------+------+------+---------+----------------+
  12. | id | bigint(20) | NO | PRI | NULL | auto_increment |
  13. | b | varchar(200) | NO | | NULL | |
  14. +-------+--------------+------+------+---------+----------------+
  15. 2 rows in set (0.00 sec)

MySQL compatibility

  • All of the data types except spatial types are supported.
  • FULLTEXT, HASH and SPATIAL indexes are not supported.

  • For compatibility, the index_col_name attribute supports the length option with a maximum length limit of 3072 bytes by default. The length limit can be changed through the max-index-length configuration option. For details, see TiDB configuration file.

  • For compatibility, the index_col_name attribute supports the length option with a maximum length limit of 3072 bytes.

  • The [ASC | DESC] in index_col_name is currently parsed but ignored (MySQL 5.7 compatible behavior).

  • The COMMENT attribute does not support the WITH PARSER option.
  • TiDB supports 1017 columns in a single table by default and 4096 columns at most. The corresponding number limit in InnoDB is 1017 columns, and the hard limit in MySQL is 4096 columns. For details, see TiDB Limitations.
  • For partitioned tables, only Range, Hash and Range Columns (single column) are supported. For details, see partitioned table.
  • CHECK constraints are parsed but ignored (MySQL 5.7 compatible behavior). For details, see Constraints.

See also