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

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

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

Creating a Table

PDF

포커스 모드

폰트 크기

마지막 업데이트 시간: 2026-03-06 18:48:24

Overview
In TDSQL Boundless, CREATE TABLE not only defines the logical structure of data (columns, indexes, constraints), but also affects the storage and distribution policy of data in the distributed cluster to some extent.
Table Types Overview
When data volume, access patterns, and distribution requirements are considered, TDSQL Boundless supports the following three table types. Before creating a table, be sure to select the correct type based on your business scenario.
Table Type
Introduction
Scenarios
Single table
Data is not split and is stored entirely on a single storage node.
Tables with small data volumes and low write concurrency requirements.
Partition Table
Data is scattered based on the partition key and distributed across multiple storage nodes, enabling horizontal scaling.
Business tables with large data volumes, rapid growth, and requiring high-concurrency writes (such as orders and transaction records).
Synchronized Table/Broadcast Table
A table type that fully replicates data to all storage nodes to enable local joins and enforces strong synchronous writes for all replicas (Note: Currently, synchronized tables must be used together with broadcast tables).
Suitable for scenarios with small data volumes (e.g., dictionaries/configurations), frequent reads and infrequent writes, and frequent join queries with large tables.
Naming Tables and Basic Syntax
The first step in creating a table is determining the table name. Table names are fundamental to database governance; please avoid using meaningless names, as this will cause significant difficulties for subsequent development and maintenance.
In TDSQL Boundless, it is recommended to follow the naming conventions below:
1. Table names should clearly express business meanings (such as users and order_details).
2. Moderate length: It is recommended to keep it within 32 characters.
CREATE TABLE The statement typically takes the following form:
CREATE TABLE <table_name> (
    <create_definitions>
) {table_options};
Parameter Description
<table_name>: Required. The unique identifier of the table.
<create_definitions> : Required. Includes column definitions, primary key constraints, and so on.
{table_options} : Optional. Common options include COMMENT='...', CHARSET=utf8mb4, and possible custom syntax.
Column Definition and Type Specifications
Columns organize row data into attributes with specific business meanings through strict data type definitions.
The general syntax for defining columns is as follows:
<column_name> <data_type> {column_qualification}
Parameter Description
<column_name>: The identifier of the column.
<data_type>: Specifies the type of data that can be stored in this column (such as integers, characters, time, and so on).
{column_qualification}: Optional column-level constraints (such as NOT NULL, DEFAULT) or generation policy.
Example: Build Inventory Details Table
CREATE TABLE `warehouse`.`inventory_items` (
  `sku_id` bigint NOT NULL,
  `item_name` varchar(150),
  `storage_zone` enum('Cold_Chain', 'Dry_Goods', 'Hazardous', 'General'),
  `entry_time` datetime,
  `quantity` int,
  `unit_cost` decimal(18,4),
  PRIMARY KEY (`sku_id`)
COMMENT='Product Inventory Core Table';
Field Parameter Analysis
1. sku_id (bigint)
Meaning: The unique identifier for the Stock Keeping Unit (SKU).
Explanation: Using bigint (8 bytes) instead of int is to handle the potential billions of product categories in large warehouses.
2. item_name (varchar)
Meaning: Product name.
Explanation: varchar(150) is a variable-length character string. If a product name is only 10 characters long, it occupies approximately 10 bytes of space (plus a length prefix), without forced space padding like char, making it ideal for storing text of varying lengths.
3. storage_zone (enum)
Meaning: Storage Area (Cold Chain, Dry Goods, Hazardous Chemicals, General Area).
Explanation: Warehouse partitions are typically fixed. Using enum enforces data compliance with predefined specifications, preventing inconsistencies like "ColdChain" and "cold_chain".
4. entry_time (datetime)
Meaning: Inbound Scan Timestamp.
Explanation: Used to record the exact timestamp when products enter the warehouse.
5. quantity (int)
Meaning: Current Stock Quantity.
Explanation: A standard 4-byte integer. Selecting this type requires estimating the maximum value; int supports up to approximately 2.1 billion, which is typically more than sufficient for individual item inventory.
6. unit_cost (decimal)
Meaning: Procurement cost per item.
Explanation: Defined here as decimal(18,4). Precision 18: supports extremely large total amounts. Scale 4: retains 4 decimal places (such as 12.5678).
Select the Primary Key
The primary key requires specifying several columns, and the value combined from all primary key columns serves as the unique identifier for a data row. The primary key constraint requires that all constrained columns contain only non-NULL values.
A table may lack a primary key, or the primary key may be of a non-integer type. In such cases, TDSQL Boundless creates a Hidden Primary Key. Since RocksDB is based on the LSM-Tree structure and data is ordered by the primary key, hidden primary keys typically exhibit monotonically increasing characteristics. In distributed scenarios, this causes all newly written data to land on the same data shard, creating a (Write Hotspot) that fails to utilize the overall throughput capacity of the distributed cluster. Therefore, it is recommended to use a business-generated random unique ID as the primary key.
Recommendations for Selecting a Primary Key
1. Prefer business columns with "high uniqueness" as the primary key: Prioritize using columns that have business meaning and evenly distributed values (such as user ID, order number) as the primary key.
2. Strictly control table width and row size: For performance considerations, avoid storing excessively wide tables whenever possible. The number of table fields should not exceed 60, and it is recommended that the total data size per row does not exceed 64K.
3. Prefer simple, standard types (such as INT, BIGINT, DECIMAL, VARCHAR) to minimize parsing overhead and maximize storage efficiency.
Specify Column Constraint
In addition to primary key constraints, TDSQL Boundless supports standard SQL column constraints, such as: NOT NULL constraint, UNIQUE KEY constraint and DEFAULT value, and so on.
1. Populate default value (DEFAULT)
If you need to set a default value on a column, use the DEFAULT constraint. This way, when inserting data, you don't need to specify values for every column, and the database will automatically populate them.
The most common scenario is: automatically record the stock-in time when products enter the warehouse.
Example SQL:
 CREATE TABLE `warehouse`.`inventory_items` (
  `sku_id` bigint NOT NULL,
  `item_name` varchar(150),
  `storage_zone` enum('Cold_Chain', 'Dry_Goods', 'Hazardous', 'General'),
  -- Use DEFAULT CURRENT_TIMESTAMP to automatically populate the current timestamp
  `entry_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `quantity` int,
  `unit_cost` decimal(18,4),
  PRIMARY KEY (`sku_id`)
COMMENT='Product Inventory Core Table';
2. Prevent duplication (UNIQUE)
If you need to prevent duplicate values in a column, you can use the UNIQUE constraint.
For example, although sku_id is the primary key, you may also require that the product name (item_name) must be unique across the entire warehouse. This can be rewritten as:
CREATE TABLE `warehouse`.`inventory_items` (
  `sku_id` bigint NOT NULL,
  -- Add a UNIQUE constraint; inserting duplicate product names will result in an error
  `item_name` varchar(150) UNIQUE,
  `storage_zone` enum('Cold_Chain', 'Dry_Goods', 'Hazardous', 'General'),
  `entry_time` datetime,
  `quantity` int,
  `unit_cost` decimal(18,4),
  PRIMARY KEY (`sku_id`)
COMMENT='Product Inventory Core Table';
3. Prevent null values (NOT NULL)
If you need to prevent null values in columns, you can use the NOT NULL constraint.
For example, the product name must not only be unique but also (cannot be null), and the inventory quantity quantity cannot be NULL either (it can default to 0). This can be rewritten as:
CREATE TABLE `warehouse`.`inventory_items` (
  `sku_id` bigint NOT NULL,
  -- No duplicates or nulls
  `item_name` varchar(150) UNIQUE NOT NULL,
  `storage_zone` enum('Cold_Chain', 'Dry_Goods', 'Hazardous', 'General'),
  `entry_time` datetime DEFAULT CURRENT_TIMESTAMP,
  -- Cannot be NULL, and the default value is 0
  `quantity` int NOT NULL DEFAULT 0,
  `unit_cost` decimal(18,4),
  PRIMARY KEY (`sku_id`)
COMMENT='Product Inventory Core Table';
Frequently Asked Questions (FAQ)
What Should I Do If the Single Table I Created Grows Large in the Future?
A: TDSQL Boundless supports smooth evolution. If the data volume of a single table grows beyond expectations, you can use the ALTER TABLE ... PARTITION BY ...  statement to convert it to a partitioned table online. The data will be automatically redistributed to other nodes in the cluster without downtime migration.
Do Single Tables Support Distributed Transactions?
Yes, it is supported. A single table can be involved in cross-node distributed transactions (for example: a transaction modifying both single table A and partitioned table B), whereas TDSQL Boundless automatically ensures global consistency.
Q: Why Is It Not Recommended to Design All Tables as Standalone Tables?
A: The data and computational pressure of a single table are concentrated on a single storage node. If core high-frequency services use a single table, this node can easily become a performance bottleneck (hotspot) for the entire cluster and cannot leverage the parallel computing capabilities of multiple nodes.
Next
If your table is expected to have a large data volume or require high-concurrency writes, please read Creating Partitioned Tables.
If you need to create rarely modified dictionary tables to optimize cross-database queries, please read Synced Tables.

도움말 및 지원

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

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

피드백

Table Type	Introduction	Scenarios
Single table	Data is not split and is stored entirely on a single storage node.	Tables with small data volumes and low write concurrency requirements.
Partition Table	Data is scattered based on the partition key and distributed across multiple storage nodes, enabling horizontal scaling.	Business tables with large data volumes, rapid growth, and requiring high-concurrency writes (such as orders and transaction records).
Synchronized Table/Broadcast Table	A table type that fully replicates data to all storage nodes to enable local joins and enforces strong synchronous writes for all replicas (Note: Currently, synchronized tables must be used together with broadcast tables).	Suitable for scenarios with small data volumes (e.g., dictionaries/configurations), frequent reads and infrequent writes, and frequent join queries with large tables.

tencent cloud

TDSQL Boundless

Creating a Table

Overview

Table Types Overview

Naming Tables and Basic Syntax

Column Definition and Type Specifications

Example: Build Inventory Details Table

Select the Primary Key

Recommendations for Selecting a Primary Key

Specify Column Constraint

Frequently Asked Questions (FAQ)

What Should I Do If the Single Table I Created Grows Large in the Future?

Do Single Tables Support Distributed Transactions?

Q: Why Is It Not Recommended to Design All Tables as Standalone Tables?

Next

도움말 및 지원