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
- OptTemporary
- IfNotExists
- TableName
- TableElementListOpt
- TableElementList
- TableElement
- ColumnDef
- ColumnOptionListOpt
- ColumnOptionList
- ColumnOption
- Constraint
- IndexDef
- KeyPartList
- KeyPart
- IndexOption
- ForeignKeyDef
- ReferenceOption
- CreateTableOptionListOpt
- PartitionOpt
- DuplicateOpt
- TableOptionList
- TableOption
- OnCommitOpt
- PlacementPolicyOption
CreateTableStmt ::=
'CREATE' OptTemporary 'TABLE' IfNotExists TableName ( TableElementListOpt CreateTableOptionListOpt PartitionOpt DuplicateOpt AsOpt CreateTableSelectOpt | LikeTableWithOrWithoutParen ) OnCommitOpt
OptTemporary ::=
( 'TEMPORARY' | ('GLOBAL' 'TEMPORARY') )?
IfNotExists ::=
('IF' 'NOT' 'EXISTS')?
TableName ::=
Identifier ('.' Identifier)?
TableElementListOpt ::=
( '(' TableElementList ')' )?
TableElementList ::=
TableElement ( ',' TableElement )*
TableElement ::=
ColumnDef
| Constraint
ColumnDef ::=
ColumnName ( Type | 'SERIAL' ) ColumnOptionListOpt
ColumnOptionListOpt ::=
ColumnOption*
ColumnOptionList ::=
ColumnOption*
ColumnOption ::=
'NOT'? 'NULL'
| 'AUTO_INCREMENT'
| PrimaryOpt 'KEY'
| 'UNIQUE' 'KEY'?
| 'DEFAULT' DefaultValueExpr
| 'SERIAL' 'DEFAULT' 'VALUE'
| 'ON' 'UPDATE' NowSymOptionFraction
| 'COMMENT' stringLit
| ConstraintKeywordOpt 'CHECK' '(' Expression ')' EnforcedOrNotOrNotNullOpt
| GeneratedAlways 'AS' '(' Expression ')' VirtualOrStored
| ReferDef
| 'COLLATE' CollationName
| 'COLUMN_FORMAT' ColumnFormat
| 'STORAGE' StorageMedia
| 'AUTO_RANDOM' OptFieldLen
Constraint ::=
IndexDef
| ForeignKeyDef
IndexDef ::=
( 'INDEX' | 'KEY' ) IndexName? '(' KeyPartList ')' IndexOption?
KeyPartList ::=
KeyPart ( ',' KeyPart )*
KeyPart ::=
ColumnName ( '(' Length ')')? ( 'ASC' | 'DESC' )?
| '(' Expression ')' ( 'ASC' | 'DESC' )?
IndexOption ::=
'COMMENT' String
| ( 'VISIBLE' | 'INVISIBLE' )
ForeignKeyDef
::= ( 'CONSTRAINT' Identifier )? 'FOREIGN' 'KEY'
Identifier? '(' ColumnName ( ',' ColumnName )* ')'
'REFERENCES' TableName '(' ColumnName ( ',' ColumnName )* ')'
( 'ON' 'DELETE' ReferenceOption )?
( 'ON' 'UPDATE' ReferenceOption )?
ReferenceOption
::= 'RESTRICT'
| 'CASCADE'
| 'SET' 'NULL'
| 'SET' 'DEFAULT'
| 'NO' 'ACTION'
CreateTableOptionListOpt ::=
TableOptionList?
PartitionOpt ::=
( 'PARTITION' 'BY' PartitionMethod PartitionNumOpt SubPartitionOpt PartitionDefinitionListOpt )?
DuplicateOpt ::=
( 'IGNORE' | 'REPLACE' )?
TableOptionList ::=
TableOption ( ','? TableOption )*
TableOption ::=
PartDefOption
| DefaultKwdOpt ( CharsetKw EqOpt CharsetName | 'COLLATE' EqOpt CollationName )
| ( '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
| ( 'CONNECTION' | 'PASSWORD' | 'COMPRESSION' ) EqOpt stringLit
| RowFormat
| ( 'STATS_PERSISTENT' | 'PACK_KEYS' ) EqOpt StatsPersistentVal
| ( 'STATS_AUTO_RECALC' | 'STATS_SAMPLE_PAGES' ) EqOpt ( LengthNum | 'DEFAULT' )
| 'STORAGE' ( 'MEMORY' | 'DISK' )
| 'SECONDARY_ENGINE' EqOpt ( 'NULL' | StringName )
| 'UNION' EqOpt '(' TableNameListOpt ')'
| 'ENCRYPTION' EqOpt EncryptionOpt
| 'TTL' EqOpt TimeColumnName '+' 'INTERVAL' Expression TimeUnit (TTLEnable EqOpt ( 'ON' | 'OFF' ))? (TTLJobInterval EqOpt stringLit)?
| PlacementPolicyOption
OnCommitOpt ::=
('ON' 'COMMIT' 'DELETE' 'ROWS')?
PlacementPolicyOption ::=
"PLACEMENT" "POLICY" EqOpt PolicyName
| "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.
Options | Description | Example |
---|---|---|
AUTO_INCREMENT | The initial value of the increment field | AUTO_INCREMENT = 5 |
SHARD_ROW_ID_BITS | To set the number of bits for the implicit _tidb_rowid shards | SHARD_ROW_ID_BITS = 4 |
PRE_SPLIT_REGIONS | To pre-split 2^(PRE_SPLIT_REGIONS) Regions when creating a table | PRE_SPLIT_REGIONS = 4 |
AUTO_ID_CACHE | To set the auto ID cache size in a TiDB instance. By default, TiDB automatically changes this size according to allocation speed of auto ID | AUTO_ID_CACHE = 200. Note that this option is not available on TiDB Serverless clusters. |
AUTO_RANDOM_BASE | To 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 parameter | AUTO_RANDOM_BASE = 0 |
CHARACTER SET | To specify the character set for the table | CHARACTER SET = 'utf8mb4' |
COMMENT | The comment information | COMMENT = 'comment info' |
Examples
Creating a simple table and inserting one row:
CREATE TABLE t1 (a int);
DESC t1;
SHOW CREATE TABLE t1\G
INSERT INTO t1 (a) VALUES (1);
SELECT * FROM t1;
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.23 sec)
mysql> CREATE TABLE t1 (a int);
Query OK, 0 rows affected (0.09 sec)
mysql> DESC t1;
+-------+---------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+------+---------+-------+
| a | int(11) | YES | | NULL | |
+-------+---------+------+------+---------+-------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
mysql> INSERT INTO t1 (a) VALUES (1);
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * FROM t1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Dropping a table if it exists, and conditionally creating a table if it does not exist:
DROP TABLE IF EXISTS t1;
CREATE TABLE IF NOT EXISTS t1 (
id BIGINT NOT NULL PRIMARY KEY auto_increment,
b VARCHAR(200) NOT NULL
);
DESC t1;
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.22 sec)
mysql> CREATE TABLE IF NOT EXISTS t1 (
id BIGINT NOT NULL PRIMARY KEY auto_increment,
b VARCHAR(200) NOT NULL
);
Query OK, 0 rows affected (0.08 sec)
mysql> DESC t1;
+-------+--------------+------+------+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+------+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| b | varchar(200) | NO | | NULL | |
+-------+--------------+------+------+---------+----------------+
2 rows in set (0.00 sec)
MySQL compatibility
- All of the data types except spatial types are supported.
FULLTEXT
,HASH
andSPATIAL
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 themax-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]
inindex_col_name
is currently parsed but ignored (MySQL 5.7 compatible behavior). - The
COMMENT
attribute does not support theWITH 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.
- TiDB supports
HASH
,RANGE
,LIST
, andKEY
partitioning types. For an unsupported partition type, TiDB returnsWarning: Unsupported partition type %s, treat as normal table
, where%s
is the specific unsupported partition type. CHECK
constraints are parsed but ignored (MySQL 5.7 compatible behavior). For details, see Constraints.