Help Center/ Distributed Database Middleware/ User Guide (Ally Region)/ SQL Syntax/ DDL/ Creating a Table
Updated on 2026-01-26 GMT+08:00
View PDF
Share

Creating a Table

  • Do not create tables whose names start with _ddm. DDM manages such tables as internal tables by default
  • Sharded tables do not support globally unique indexes. If the unique key is different from the sharding key, data uniqueness cannot be ensured.
  • The auto-increment key should be of the BIGINT data type. To avoid duplicate values, do not use TINYINT, SMALLINT, MEDIUMINT, INTEGER, or INT as the auto-increment key.
  • You need to specify a character set and collation when creating a table to prevent inconsistent collation between tables caused by the difference between the collation of the schema and the default collation of the DN.

Database and Table Sharding

The following is an example statement when HASH is used for database sharding and MOD_HASH for table sharding:

CREATE TABLE tbpartition_tbl (
id bigint NOT NULL AUTO_INCREMENT COMMENT 'Primary key id',
name varchar(128),
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
DBPARTITION BY HASH(id)
TBPARTITION BY mod_hash(name) tbpartitions 8;

Database Sharding

The following is an example statement when HASH is used:

CREATE TABLE dbpartition_tbl (
id bigint NOT NULL AUTO_INCREMENT COMMENT 'Primary key id',
name varchar(128),
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
DBPARTITION BY HASH(id);

Creating a Broadcast Table

The following is an example statement:

CREATE TABLE broadcast_tbl (
id bigint NOT NULL AUTO_INCREMENT COMMENT 'Primary key id',
name varchar(128),
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
BROADCAST;

Creating a Table When Sharding Is not Used

A global sequence can also be specified for an unsharded table, but this function is always ignored. An unsharded table provides auto-increment using auto-increment values of corresponding physical tables.

The following is an example statement:

CREATE TABLE single(
id bigint NOT NULL AUTO_INCREMENT COMMENT 'Primary key id',
name varchar(128),
PRIMARY KEY(id)
) 
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Parent topic: DDL