Sign InTry Free

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
CREATEOptTemporaryTABLEIfNotExistsTableNameTableElementListOptCreateTableOptionListOptPartitionOptDuplicateOptAsOptCreateTableSelectOptLikeTableWithOrWithoutParenOnCommitOpt
OptTemporary
TEMPORARYGLOBALTEMPORARY
IfNotExists
IFNOTEXISTS
TableName
Identifier.Identifier
TableElementListOpt
(TableElementList)
TableElementList
TableElement,
TableElement
ColumnDefConstraint
ColumnDef
ColumnNameTypeSERIALColumnOptionListOpt
ColumnOptionListOpt
ColumnOption
ColumnOptionList
ColumnOption
ColumnOption
NOTNULLAUTO_INCREMENTPrimaryOptKEYUNIQUEKEYDEFAULTDefaultValueExprSERIALDEFAULTVALUEONUPDATENowSymOptionFractionCOMMENTstringLitConstraintKeywordOptCHECK(Expression)EnforcedOrNotOrNotNullOptGeneratedAlwaysAS(Expression)VirtualOrStoredReferDefCOLLATECollationNameCOLUMN_FORMATColumnFormatSTORAGEStorageMediaAUTO_RANDOMOptFieldLen
CreateTableOptionListOpt
TableOptionList
PartitionOpt
PARTITIONBYPartitionMethodPartitionNumOptSubPartitionOptPartitionDefinitionListOpt
DuplicateOpt
IGNOREREPLACE
TableOptionList
TableOption,
TableOption
PartDefOptionDefaultKwdOptCharsetKwEqOptCharsetNameCOLLATEEqOptCollationNameAUTO_INCREMENTAUTO_ID_CACHEAUTO_RANDOM_BASEAVG_ROW_LENGTHCHECKSUMTABLE_CHECKSUMKEY_BLOCK_SIZEDELAY_KEY_WRITESHARD_ROW_ID_BITSPRE_SPLIT_REGIONSEqOptLengthNumCONNECTIONPASSWORDCOMPRESSIONEqOptstringLitRowFormatSTATS_PERSISTENTPACK_KEYSEqOptStatsPersistentValSTATS_AUTO_RECALCSTATS_SAMPLE_PAGESEqOptLengthNumDEFAULTSTORAGEMEMORYDISKSECONDARY_ENGINEEqOptNULLStringNameUNIONEqOpt(TableNameListOpt)ENCRYPTIONEqOptEncryptionOptPlacementPolicyOption
OnCommitOpt
ONCOMMITDELETEROWS
PlacementPolicyOption
PLACEMENTPOLICYEqOptPolicyNamePLACEMENTPOLICYEqOptSETDEFAULT

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
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'

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

See also

Download PDF
Playground
New
One-stop & interactive experience of TiDB's capabilities WITHOUT registration.
Products
TiDB
TiDB Dedicated
TiDB Serverless
Pricing
Get Demo
Get Started
© 2024 PingCAP. All Rights Reserved.
Privacy Policy.