Doc Menu
Important

You are viewing the documentation of an older version of the TiDB database (TiDB v2.1).

It is recommended that you use the latest stable version of the TiDB database.

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:

CreateTableStmt

IfNotExists:

IfNotExists

TableName:

TableName

TableElementListOpt:

TableElementListOpt

TableElement:

TableElement

PartitionOpt:

PartitionOpt

ColumnDef:

ColumnDef

ColumnName:

ColumnName

Type:

Type

ColumnOptionListOpt:

ColumnOptionListOpt

TableOptionListOpt:

TableOptionListOpt

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
CHARACTER SETTo specify the character set for the tableCHARACTER SET = 'utf8mb4'
COMMENTThe comment informationCOMMENT = 'comment info'

Note:

In TiDB 2.1 versions, the three features SHARD_ROW_ID_BITS, PRE_SPLIT_REGIONS and COLLATE are supported starting from the 2.1.13 version (including 2.1.13).

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

  • TiDB does not support the syntax CREATE TEMPORARY TABLE.
  • All of the data types except spatial types are supported.
  • FULLTEXT, HASH and SPATIAL indexes are not supported.
  • The [ASC | DESC] in index_col_name is currently parsed but ignored (MySQL 5.7 compatible behavior).
  • The COMMENT attribute supports a maximum of 1024 characters and does not support the WITH PARSER option.
  • TiDB supports at most 512 columns in a single table. The corresponding number limit in InnoDB is 1017, and the hard limit in MySQL is 4096.
  • CHECK constraints are parsed but ignored (MySQL 5.7 compatible behavior). For details, see Constraints.
  • FOREIGN KEY constraints are parsed and stored, but not enforced by DML statements. For details, see Constraints.

See also