Release Notes
Product Introduction
- Overview
- Scenarios
- Product Architecture
- Instance Types
- Compatibility Notes
- Usage specification recommendations
Kernel Features
- Kernel Overview
- Kernel Version Release Notes
- Functionality Features
- Performance Features
Billing
Getting Started
- Creating an Instance
- Connect to Instances
User Guide
- Data Migration
- Data Subscription
- Instance Management
- Parameter Configuration
- Account Management
- Security Group
- Backup and Restoration
- Database Auditing
- Tag Management
Use Cases
Developer Guide
- Developer Guide (MySQL Compatibility Mode)
- Developer Guide (HBase Compatibility Mode)
Performance Tuning
- Performance Tuning Overview
- SQL Tuning
- DDL Tuning
Performance White Paper
API Documentation
- History
- Introduction
- API Category
- Making API Requests
- Instance APIs
- Security Group APIs
- Task APIs
- Backup APIs
- Rollback APIs
- Parameter APIs
- Database APIs
- Data Types
- Error Codes
General Reference
- System Architecture
- SQL Reference
- Database Parameter Description
- TPC-H benchmark data model reference
- Error Code Information
Security and Compliance
FAQs
Agreements
Contact Us
Glossary

CREATE TABLE

Focus Mode

Font Size

Last updated: 2026-02-10 11:26:03

Description
Create a table.
Syntax
CREATE TABLE [IF NOT EXISTS] table_name
      (table_definition_list) [table_option_list] [partition_clause] [opt_using_policy] ;
﻿
table_definition_list:
    table_definition [, table_definition ...]
﻿
table_definition:
      column_definition_list
    | [CONSTRAINT [constraint_name]] PRIMARY KEY index_desc
    | [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY}
            [index_name] index_desc
    | [CONSTRAINT [constraint_name]] CHECK(expression) constranit_state
﻿
column_definition_list:
    column_definition [, column_definition ...]
﻿
column_definition:
     column_name data_type
         [DEFAULT const_value] [AUTO_INCREMENT]
         [NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] [COMMENT string_value]
         
index_desc:
   (column_desc_list) [index_option_list]
﻿
index_option_list:
    index_option [ index_option ...]
﻿
index_option:
      KEY_BLOCK_SIZE [=] INT_VALUE
    | COMMENT string_value
﻿
table_option_list:
    table_option [ table_option ...]
﻿
table_option:
      [DEFAULT] {CHARSET | CHARACTER SET} [=] charset_name
    | [DEFAULT] COLLATE [=] collation_name
    | COMMENT string_value
    | ROW_FORMAT [=] REDUNDANT|COMPACT|DYNAMIC|COMPRESSED|DEFAULT
    | SYNC_LEVEL [=] 'NODE(MAJORITY)|NODE(ALL)'
    | DISTRIBUTION [=] 'NODE(DEFAULT)|NODE(ALL)'
﻿
partition_option:
      PARTITION BY HASH(expression)
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY KEY([column_name_list])
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
      [subpartition_option] (range_partition_list)
    | PARTITION BY LIST {(expression) | COLUMNS (column_name_list)}
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY RANGE [COLUMNS]([column_name_list]) (range_partition_list)
﻿
subpartition_option:
      SUBPARTITION BY HASH(expression)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY KEY(column_name_list)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
      (range_subpartition_list)
    | SUBPARTITION BY LIST(expression)
﻿
range_partition_list:
    range_partition [, range_partition ...]
﻿
range_partition:
    PARTITION partition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}
﻿
range_subpartition_list:
    range_subpartition [, range_subpartition ...]
﻿
range_subpartition:
    SUBPARTITION subpartition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}
﻿
expression_list:
    expression [, expression ...]
﻿
column_name_list:
    column_name [, column_name ...]
﻿
partition_name_list:
    partition_name [, partition_name ...]
﻿
partition_count | subpartition_count:
    INT_VALUE
    
opt_using_policy:
    USING DISTRIBUTION POLICY distribution_policy_name
   | USING PARTITION POLICY partition_policy_name
Parameter Description
Parameter
Optional or Not
Description
IF NOT EXISTS
Optional
If IF NOT EXISTS is specified, no error will be reported even if the table to be created already exists. If it is not specified and the table to be created already exists, the system will report an error.
PRIMARY KEY
Optional
Specifies a primary key for the created table. If it is not specified, a hidden primary key is used.
KEY | INDEX
Optional
Specifies a key or index for the created table. If the index name is not specified, the first column of the index reference will be used as the index name. If a duplicate name already exists, the index will be named using the underscore (_) + serial number. (For example, an index created using the c1 column will be named c1_2 if a duplicate name already exists.) You can view the indexes on a table through the SHOW INDEX statement.
ROW_FORMAT
Optional
Specifies whether the table has encoded storage format enabled.
redundant: Does not enable the encoded storage format.
compact: Does not enable the encoded storage format.
dynamic: Encoded storage format.
compressed: Encoded storage format.
default: Equivalent to dynamic mode.
KEY_BLOCK_SIZE
Optional
Specifies the size of the index block (in bytes).
CHARSET | CHARACTER SET
Optional
Specifies the default character set for columns in the table.
COLLATE
Optional
Specifies the default collation for columns in the table.
COMMENT
Optional
Comment. Case-insensitive.
CHECK
Optional
Limits the range of values in columns. 
If a CHECK constraint is defined for a single column, this column-level constraint can be written into the column definition and its name can be specified.
If a CHECK constraint is defined for a table, this constraint applies to multiple columns in the table and is allowed to appear before the column definitions. When you delete a table, the CHECK constraints created in the table will also be deleted.
Constraint information can be viewed in the following ways:
Use the SHOW CREATE TABLE command. 
View the information_schema.TABLE_CONSTRAINTS view.
View the information_schema.CHECK_CONSTRAINTS view. 
constraint_name
Optional
Constraint names can contain up to 64 characters.
Spaces are allowed in the beginning, middle, and end of a constraint name, but "`" is required to identify the beginning and end of the name. 
A constraint name can contain the special character "$".
If a constraint name contains a reserved word, it needs to be marked with "`". Otherwise, an error will be reported.  
Duplicate names of CHECK constraints are not allowed in the same database.
expression
Optional
Constraint expression.
The expression cannot be empty.
The expression result cannot be a non-Boolean type.
The expression cannot contain non-existent columns.
SYNC_LEVEL
Optional
Specify synchronization properties. The values are as follows:
NODE(MAJORITY): default value, indicates that this table is an ordinary table.
NODE(ALL): indicates that this table is a synchronous table. The leader needs to strongly synchronize modifications to all replicas (except log replicas).
Currently, synchronous tables must be used together with broadcast tables.
DISTRIBUTION
Optional
Specify broadcast properties. The values are as follows:
NODE(DEFAULT): default value, indicates that this table is an ordinary table.
NODE(ALL): indicates that this table is a synchronous table, and a replica will be created on every node in the system.
Currently, broadcast tables must be used together with synchronous tables.
distribution_policy_name
Optional
Specify the data distribution policy to apply to the table. This policy must be created beforehand.
Pre-defined Policy:
inner_affinity: An internal affinity policy for subpartitioned tables. When this policy is applied, corresponding subpartitions from different primary partitions are co-located within the same Raft group. For example, p0.sp0 and p1.sp0 would be placed in the same Raft group.
partition_policy_name
Optional
Specify the policy for partition affinity to be bound. Please create the policy for partition affinity in advance. For details, refer to CREATE PARTITION POLICY.
Examples
Create a database table.
tdsql [test]> CREATE TABLE tbl1 (c1 INT PRIMARY KEY, c2 VARCHAR(50));
Query OK, 0 rows affected
Create a table with an index.
tdsql [test]> CREATE TABLE tbl2 (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX i1 (c2));
Query OK, 0 rows affected
Create a hash partitioned table with 8 partitions.
tdsql [test]> CREATE TABLE tbl3 (c1 INT PRIMARY KEY, c2 INT) PARTITION BY HASH(c1) PARTITIONS 8;
Query OK, 0 rows affected
Create a table with the primary partition as the Range partition and the subspartition as the key partition.
tdsql [test]> CREATE TABLE tbl4 (c1 INT, c2 INT, c3 INT) PARTITION BY RANGE(c1)
            SUBPARTITION BY KEY(c2, c3) SUBPARTITIONS 5
            (PARTITION p0 VALUES LESS THAN(0), PARTITION p1 VALUES LESS THAN(100));
﻿
Query OK, 0 rows affected
Create a table with gbk as one column and utf8 as the other.
tdsql [test]> CREATE TABLE tbl5 (
           c1 VARCHAR(10),
           c2 VARCHAR(10) CHARSET GBK COLLATE gbk_bin
       ) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Query OK, 0 rows affected
Use an auto-increment column as the partition key.
tdsql [test]> CREATE TABLE tbl6(inv_id BIGINT NOT NULL AUTO_INCREMENT,c1 BIGINT,
       PRIMARY KEY (inv_id) ) PARTITION BY HASH(inv_id) PARTITIONS 8;
Query OK, 0 rows affected
Create a table with a CHECK constraint and view the constraint information.
tdsql [test]> CREATE TABLE tbl7 (col1 INT, col2 INT, col3 INT, CONSTRAINT equal_check1 CHECK(col1 = col3 * 2));
Query OK, 0 rows affected
﻿
tdsql [test]> SHOW CREATE TABLE tbl7;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                            |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl10 | CREATE TABLE `tbl7` (
  `col1` int DEFAULT NULL,
  `col2` int DEFAULT NULL,
  `col3` int DEFAULT NULL,
  CONSTRAINT `equal_check1` CHECK ((`col1` = (`col3` * 2)))
) ENGINE=ROCKSDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
Create a synchronized table.
tdsql [test]> create table sync_table(c1 int primary key, c2 int) sync_level = node(all) distribution = node(all);
Query OK, 0 rows affected
Create a table bound to a policy for partitioned affinity.
# Create policy for partition affinity
tdsql [test]> create partition policy pp2 partition by hash(int) partitions 4;
Query OK, 0 rows affected
# Create table.
tdsql [test]> create table tbl8(id INT) partition by hash(id) partitions 4 using partition policy pp2;
Query OK, 0 rows affected

Help and Support

Was this page helpful?

You can also Contact sales or Submit a Ticket for help.

Help us improve! Rate your documentation experience in 5 mins.

Feedback

tencent cloud

TDSQL Boundless

CREATE TABLE

Description

Syntax

Parameter Description

Examples

Help and Support

Parameter	Optional or Not	Description
IF NOT EXISTS	Optional	If `IF NOT EXISTS` is specified, no error will be reported even if the table to be created already exists. If it is not specified and the table to be created already exists, the system will report an error.
PRIMARY KEY	Optional	Specifies a primary key for the created table. If it is not specified, a hidden primary key is used.
KEY \| INDEX	Optional	Specifies a key or index for the created table. If the index name is not specified, the first column of the index reference will be used as the index name. If a duplicate name already exists, the index will be named using the underscore (_) + serial number. (For example, an index created using the `c1` column will be named `c1_2` if a duplicate name already exists.) You can view the indexes on a table through the `SHOW INDEX` statement.
ROW_FORMAT	Optional	Specifies whether the table has encoded storage format enabled. redundant: Does not enable the encoded storage format. compact: Does not enable the encoded storage format. dynamic: Encoded storage format. compressed: Encoded storage format. default: Equivalent to dynamic mode.
KEY_BLOCK_SIZE	Optional	Specifies the size of the index block (in bytes).
CHARSET \| CHARACTER SET	Optional	Specifies the default character set for columns in the table.
COLLATE	Optional	Specifies the default collation for columns in the table.
COMMENT	Optional	Comment. Case-insensitive.
CHECK	Optional	Limits the range of values in columns. If a `CHECK` constraint is defined for a single column, this column-level constraint can be written into the column definition and its name can be specified. If a `CHECK` constraint is defined for a table, this constraint applies to multiple columns in the table and is allowed to appear before the column definitions. When you delete a table, the `CHECK` constraints created in the table will also be deleted. Constraint information can be viewed in the following ways: Use the `SHOW CREATE TABLE` command. View the `information_schema.TABLE_CONSTRAINTS` view. View the `information_schema.CHECK_CONSTRAINTS` view.
constraint_name	Optional	Constraint names can contain up to 64 characters. Spaces are allowed in the beginning, middle, and end of a constraint name, but "`" is required to identify the beginning and end of the name. A constraint name can contain the special character "$". If a constraint name contains a reserved word, it needs to be marked with "`". Otherwise, an error will be reported. Duplicate names of `CHECK` constraints are not allowed in the same database.
expression	Optional	Constraint expression. The `expression` cannot be empty. The `expression` result cannot be a non-Boolean type. The `expression` cannot contain non-existent columns.
SYNC_LEVEL	Optional	Specify synchronization properties. The values are as follows: `NODE(MAJORITY)`: default value, indicates that this table is an ordinary table. `NODE(ALL)`: indicates that this table is a synchronous table. The leader needs to strongly synchronize modifications to all replicas (except log replicas). Currently, synchronous tables must be used together with broadcast tables.
DISTRIBUTION	Optional	Specify broadcast properties. The values are as follows: `NODE(DEFAULT)`: default value, indicates that this table is an ordinary table. `NODE(ALL)`: indicates that this table is a synchronous table, and a replica will be created on every node in the system. Currently, broadcast tables must be used together with synchronous tables.
distribution_policy_name	Optional	Specify the data distribution policy to apply to the table. This policy must be created beforehand. Pre-defined Policy: `inner_affinity`: An internal affinity policy for subpartitioned tables. When this policy is applied, corresponding subpartitions from different primary partitions are co-located within the same Raft group. For example, p0.sp0 and p1.sp0 would be placed in the same Raft group.
partition_policy_name	Optional	Specify the policy for partition affinity to be bound. Please create the policy for partition affinity in advance. For details, refer to CREATE PARTITION POLICY.