Help Center/ Distributed Database Middleware/ User Guide/ SQL Syntax/ DDL/ Creating a Table
Updated on 2022-09-16 GMT+08:00
View PDF

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.

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)
);
Parent topic: DDL