创建表

创建表

更新时间：2023-07-19 13:03:07

您可以使用 CREATE TABLE 语句来创建表。本文主要介绍非分区表的创建。

前提条件

当前用户具有用户级或数据库级 CREATE 权限。

创建非分区表

创建非分区表是指创建只有一个分区的表。

创建非分区表的示例语句如下：

示例1：创建 student 表。

CREATE TABLE student(
  id int,
  name varchar(18),
  sex char(1),
  age int,
  address varchar(200),
  email varchar(100),
  date date,
  PRIMARY KEY (id)
);

示例2：创建 staff 表。

CREATE TABLE staff(
  id int not null auto_increment,
  name varchar(18),
  sex char(1) default '0' check(
    sex = '0'
    or sex = '1'
  ),
  age int not null,
  address varchar(200),
  email varchar(100) unique,
  date date,
  wages number(7, 3),
  Entry_time TIMESTAMP(6),
  PRIMARY KEY (id),
  index idx (date)
);

示例中创建了 2 个表，并同时对表中的列定义了一些约束信息，包括在不同列上创建的主键和外键等。

创建表的列时请选择正确的数据类型。关于具体数据类型的使用，请参见《SQL 参考（MySQL 模式）》。

说明

基于性能和后期维护的需要，建议建表时为表设计主键或者唯一键。如果没有合适的字段作为主键，可以在创建表时不指定主键，待表创建成功后系统会为无主键表指定自增列作为隐藏主键。

由于 ALTER TABLE 语句不支持在后期增加主键，故在创建表时就需要设置主键。

复制已有表的数据创建新表

复制表数据

可以使用 CREATE TABLE AS SELECT 语句复制表的数据，但是结构并不完全一致，并且会丢失约束、索引、默认值、分区等信息。

示例

将 staff 表的数据复制到 staff_copy 表。语句如下:

CREATE TABLE staff_copy AS SELECT * FROM staff;

表数据对比

原 staff 表数据：

obclient> select * from staff;
+----+--------+------+-----+-------------------+----------------------+------------+---------+----------------------------+
| id | name   | sex  | age | address           | email                | date       | wages   | Entry_time                 |
+----+--------+------+-----+-------------------+----------------------+------------+---------+----------------------------+
|  1 | 张三   | 0    |  15 | 杭州市XX小区      | 1********@QQ.com     | 2022-02-22 | 100.000 | 2022-02-25 23:59:59.999990 |
|  2 | 李四   | 1    |  15 | 北京市XX小区      | 2********@163.com    | 2022-02-28 | 100.000 | 2022-02-27 23:59:59.999990 |
|  3 | 王五   | 1    |  15 | 上海市XX小区      | 3********@shouhu.com | 2022-02-28 | 100.000 | 2022-04-28 23:59:59.999990 |
+----+--------+------+-----+-------------------+----------------------+------------+---------+----------------------------+
3 rows in set

staff_copy 表数据：

obclient> select * from staff_copy;
+----+--------+------+-----+-------------------+----------------------+------------+---------+----------------------------+
| id | name   | sex  | age | address           | email                | date       | wages   | Entry_time                 |
+----+--------+------+-----+-------------------+----------------------+------------+---------+----------------------------+
|  1 | 张三   | 0    |  15 | 杭州市XX小区      | 1********@QQ.com     | 2022-02-22 | 100.000 | 2022-02-25 23:59:59.999990 |
|  2 | 李四   | 1    |  15 | 北京市XX小区      | 2********@163.com    | 2022-02-28 | 100.000 | 2022-02-27 23:59:59.999990 |
|  3 | 王五   | 1    |  15 | 上海市XX小区      | 3********@shouhu.com | 2022-02-28 | 100.000 | 2022-04-28 23:59:59.999990 |
+----+--------+------+-----+-------------------+----------------------+------------+---------+----------------------------+
3 rows in set

表结构对比

原 staff 表结构：

obclient> desc staff;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| name       | varchar(18)  | YES  |     | NULL    |                |
| sex        | char(1)      | YES  |     | 0       |                |
| age        | int(11)      | NO   |     | NULL    |                |
| address    | varchar(200) | YES  |     | NULL    |                |
| email      | varchar(100) | YES  | UNI | NULL    |                |
| date       | date         | YES  | MUL | NULL    |                |
| wages      | decimal(7,3) | YES  |     | NULL    |                |
| Entry_time | timestamp(6) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
9 rows in set

staff_copy 表结构：

obclient> desc staff_copy;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | NO   |     | NULL    |       |
| name       | varchar(18)  | YES  |     | NULL    |       |
| sex        | char(1)      | YES  |     | NULL    |       |
| age        | int(11)      | NO   |     | NULL    |       |
| address    | varchar(200) | YES  |     | NULL    |       |
| email      | varchar(100) | YES  |     | NULL    |       |
| date       | date         | YES  |     | NULL    |       |
| wages      | decimal(7,3) | YES  |     | NULL    |       |
| Entry_time | timestamp(6) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
9 rows in set

说明

通过复制 staff 表数据示例前后对比可以看出，复制表数据后原 staff 表数据和复制后的 staff_copy 表数据完全一致，但 staff_copy 表结构中的约束、索引、默认值等信息则会丢失。

复制表结构

可以使用 CREATE TABLE LIKE 语句复制表结构，但是不能复制表数据。示例：

将 staff 表的表结构复制到 staff_like 表。

语句如下：

CREATE TABLE staff_like like staff;

表结构对比

原 staff 表结构：

obclient> desc staff;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| name       | varchar(18)  | YES  |     | NULL    |                |
| sex        | char(1)      | YES  |     | 0       |                |
| age        | int(11)      | NO   |     | NULL    |                |
| address    | varchar(200) | YES  |     | NULL    |                |
| email      | varchar(100) | YES  | UNI | NULL    |                |
| date       | date         | YES  | MUL | NULL    |                |
| wages      | decimal(7,3) | YES  |     | NULL    |                |
| Entry_time | timestamp(6) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
9 rows in set

staff_like 表的表结构：

obclient> desc staff_like;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| name       | varchar(18)  | YES  |     | NULL    |                |
| sex        | char(1)      | YES  |     | 0       |                |
| age        | int(11)      | NO   |     | NULL    |                |
| address    | varchar(200) | YES  |     | NULL    |                |
| email      | varchar(100) | YES  | UNI | NULL    |                |
| date       | date         | YES  | MUL | NULL    |                |
| wages      | decimal(7,3) | YES  |     | NULL    |                |
| Entry_time | timestamp(6) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
9 rows in set

表行数对比

原 staff 表行数：

obclient> select count(*) from staff;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set

staff_like 表行数：

obclient> select count(*) from staff_like;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

创建复制表

复制表是 OceanBase 数据库的高级优化手段。

通常 OceanBase 集群是三副本架构，默认每个表的每个分区在 OceanBase 数据库中会有三个副本数据，在角色上分为一个主副本（Leader 副本）和两个备副本（Follower 副本），默认由主副本提供读写服务。

复制表可以在指定租户的每台机器上都有一个备副本，并且主副本与所有备份的数据使用全同步策略保持强同步。这样做的目的是为了让业务有些 SQL 关联查询时能在同一节点内部执行，以获取更好的性能。

复制表的语法是在 CREATE TABLE 语句后增加 DUPLICATE_SCOPE 选项。

示例：

创建 staff_DC 表，该表使用 zstd 压缩算法；开启 Encoding 存储格式；指定宏块保留空间为 0；指定微块大小为 16384；是一张复制表；描述副本在 F@zone1。

CREATE TABLE staff_DC(
  id int not null auto_increment,
  name varchar(18),
  sex char(1) default '0' check(
    sex = '0'
    or sex = '1'
  ),
  age int not null,
  address varchar(200),
  email varchar(100) unique,
  date date,
  wages number(7, 3),
  Entry_time TIMESTAMP(6),
  PRIMARY KEY (id)
) COMPRESSION 'zstd_1.0' FORM ROW_FORMAT DYNAMIC PCTFREE 0 BLOCK_SIZE = 16384 DUPLICATE_SCOPE = 'cluster' locality = 'F@zone1';