Release Notes

Product Introduction

Overview

Scenarios

Product Architecture

Instance Types

Compatibility Notes

Kernel Features

Kernel Overview

Kernel Version Release Notes

Functionality Features

Performance Features

Billing

Billing Overview

Purchase Method

Pricing Details

Renewal

Overdue Payments

Refund

Getting Started

Creating an Instance

Connect to Instances

User Guide

Data Migration

Data Subscription

Instance Management

Configuration Change

Parameter Configuration

Account Management

Security Group

Backup and Restoration

Database Auditing

Tag Management

Use Cases

Technical Evolution and Usage Practices of Online DDL

Lock Mechanism Analysis and Troubleshooting Practices

Data Intelligent Scheduling and Related Practices for Performance Optimization

TDSQL Boundless Selection Guide and Practical Tutorial

Developer Guide

Developer Guide (MySQL Compatibility Mode)

Developer Guide (HBase Compatibility Mode)

Performance Tuning

Performance Tuning Overview

SQL Tuning

DDL Tuning

Performance White Paper

Performance Overview

TPC-C Test

Sysbench Test

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

Service Level Agreement

Data Processing And Security Agreement

Glossary

CREATE TABLE

PDF

포커스 모드

폰트 크기

마지막 업데이트 시간: 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

도움말 및 지원

문제 해결에 도움이 되었나요?

더 자세한 내용은 문의하기 또는 티겟 제출 을 통해 문의할 수 있습니다.

피드백

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.

tencent cloud

TDSQL Boundless

CREATE TABLE

Description

Syntax

Parameter Description

Examples

도움말 및 지원