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:

IfNotExists:

TableName:

TableElementListOpt:

TableElement:

PartitionOpt:

ColumnDef:

ColumnName:

Type:

ColumnOptionListOpt:

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.

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.
• 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.
• 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.
• 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.
• FOREIGN KEY constraints are parsed and stored, but not enforced by DML statements. For details, see Constraints.

See also

• Data Types
• DROP TABLE
• CREATE TABLE LIKE
• SHOW CREATE TABLE