MySQL中创建表的三种方法汇总

MySQL
332
0
0
2023-03-26
MySQL数据库的索引原理与慢SQL优化的5大原则
上一篇
MySQL索引的一些常见面试题大全(2022年)
下一篇
目录
  • CREATE TABLE
  • CREATE TABLE … LIKE
  • CREATE TABLE … SELECT
  • 总结

SQL 标准使用 CREATE TABLE 语句创建数据表；MySQL 则实现了三种创建表的方法，支持自定义表结构或者通过复制已有的表结构来创建新表，本文给大家分别介绍一下这些方法的使用和注意事项。

CREATE TABLE

CREATE TABLE 语句的基本语法如下：

CREATE TABLE [IF NOT EXISTS] table_name
(
  column1 data_type column_constraint,
  column2 data_type,
  ...,
  table_constraint
);

使用该语句时，我们需要手动定义表的结构。以上包含的内容如下：

  • IF NOT EXISTS 表示当该表不存在时创建表，当表已经存在时不执行该语句。
  • table_name 指定了表的名称。
  • 括号内是字段的定义；columnN 是字段的名称，data_type 是它的数据类型；column_constraint 是可选的字段约束；多个字段使用逗号进行分隔。
  • table_constraint 是可选的表级约束。

其中，常见的约束包括主键、外键、唯一、非空、检查约束以及默认值。

举例来说，以下语句用于创建一个新表 department：

CREATE TABLE department
    ( dept_id    INTEGER NOT NULL PRIMARY KEY
    , dept_name  VARCHAR(50) NOT NULL
    ) ;

部门表 department 包含两个字段，部门编号（dept_id）是一个整数类型（INTEGER），不可以为空（NOT NULL），同时它还是这个表的主键（PRIMARY KEY）。

部门名称（dept_name）是一个可变长度的字符串，最长 50 个字符，不允许为空。

如果我们想要创建一个自定义名称的主键约束，可以使用表级约束：

CREATE TABLE department
    ( dept_id    INTEGER NOT NULL
    , dept_name  VARCHAR(50) NOT NULL
    , CONSTRAINT pk_department PRIMARY KEY (dept_id)
    ) ;

表级约束在所有字段之后定义，其中 pk_dept 是自定义的主键名称。

对于数字类型的主键字段，我们可以通过自增长列（auto increment）自动生成一个唯一的数字。

例如：

CREATE TABLE department
    ( dept_id    INTEGER AUTO_INCREMENT PRIMARY KEY
    , dept_name  VARCHAR(50) NOT NULL
    ) ;

此时，我们在插入数据时不再需要为 dept_id 字段提供数据，MySQL 默认会产生一个从 1 开始，每次递增 1 的数字序列。

然后我们再创建两个表：

CREATE TABLE job
    ( job_id         INTEGER NOT NULL PRIMARY KEY
    , job_title      VARCHAR(50) NOT NULL
    ) ;
    
CREATE TABLE employee
    ( emp_id    INTEGER NOT NULL PRIMARY KEY
    , emp_name  VARCHAR(50) NOT NULL
    , sex       VARCHAR(10) NOT NULL
    , dept_id   INTEGER NOT NULL
    , manager   INTEGER
    , hire_date DATE NOT NULL
    , job_id    INTEGER NOT NULL
    , salary    NUMERIC(8,2) NOT NULL
    , bonus     NUMERIC(8,2)
    , email     VARCHAR(100) NOT NULL
    , CONSTRAINT ck_emp_sex CHECK (sex IN ('男', '女'))
    , CONSTRAINT ck_emp_salary CHECK (salary > 0)
    , CONSTRAINT uk_emp_email UNIQUE (email)
    , CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES department(dept_id)
    , CONSTRAINT fk_emp_job FOREIGN KEY (job_id) REFERENCES job(job_id)
    , CONSTRAINT fk_emp_manager FOREIGN KEY (manager) REFERENCES employee(emp_id)
    ) ;

job 表用于存储职位信息，和部门表相似，比较简单。

employee 表用于存储员工信息，包含的字段和约束如下：

  • 员工编号（emp_id）是一个整数类型（INTEGER），不可以为空（NOT NULL），同时它还是这个表的主键（PRIMARY KEY）。
  • 员工姓名（emp_name）是一个可变长度的字符串，最长 50 个字符，不允许为空。
  • 性别（sex）是一个可变长度的字符串，最长 10 个字符，不允许为空；另外，我们通过表级约束 ck_emp_sex 限制了性别的取值只能为“男”或者“女”。
  • 部门编号（dept_id）代表了员工所在的部门，因此通过外键约束 fk_emp_dept 引用了部门表的主键字段。
  • 经理编号（manager）代表了员工的直接上级，可能为空。外键约束 fk_emp_manager 表示经理也属于员工。
  • 入职日期（hire_date）是一个 DATE 类型的字段，不能为空。
  • 职位编号（job_id）代表了员工的职位，因此通过外键 fk_emp_job 引用了职位表的主键字段。
  • 月薪（salary）是一个支持两位小数的数字，不能为空。检查约束 ck_emp_salary 要求月薪必须大于零。
  • 奖金（bonus）是一个可选的数字字段。
  • 电子邮箱（email）是一个可变长度的字符串，最长100 个字符，不允许为空。检查约束 uk_emp_email 要求每个员工的电子邮箱都不相同。

CREATE TABLE … LIKE

除了手动定义表的结构之外，MySQL 还提供了复制已有表结构的方法：

CREATE TABLE [IF NOT EXISTS] table_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

MySQL 的 LIKE 语法只复制表结构，包括字段的属性和索引，但是不复制数据。

例如：

CREATE TABLE emp_copy
  LIKE employee;

以上语句基于 employee 表的结构复制生成一个新的表 emp_copy。

mysql> show create table emp_copy \G
*************************** 1. row ***************************
       Table: emp_copy
Create Table: CREATE TABLE `emp_copy` (
  `emp_id` int NOT NULL,
  `emp_name` varchar(50) NOT NULL,
  `sex` varchar(10) NOT NULL,
  `dept_id` int NOT NULL,
  `manager` int DEFAULT NULL,
  `hire_date` date NOT NULL,
  `job_id` int NOT NULL,
  `salary` decimal(8,2) NOT NULL,
  `bonus` decimal(8,2) DEFAULT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`emp_id`),
  UNIQUE KEY `uk_emp_email` (`email`),
  KEY `fk_emp_dept` (`dept_id`),
  KEY `fk_emp_job` (`job_id`),
  KEY `fk_emp_manager` (`manager`),
  CONSTRAINT `emp_copy_chk_1` CHECK ((`salary` > 0)),
  CONSTRAINT `emp_copy_chk_2` CHECK ((`sex` in (_utf8mb4'男',_utf8mb4'女')))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

对于 CREATE TABLE … LIKE 命令，目标表会保留原始表中的主键、唯一约束、非空约束、表达式默认值、检查约束（自动生成约束名），同时还会保留原始表中的计算列定义。

CREATE TABLE … LIKE 命令不会保留外键约束（但是会保留外键索引），以及任何 DATA DIRECTORY 或者 INDEX DIRECTORY 表属性选项。

如果原始表是一个 TEMPORARY 表，CREATE TABLE … LIKE 不会保留 TEMPORARY 关键字。如果想要创建一个临时表，可以使用 CREATE TEMPORARY TABLE … LIKE。

使用 mysql 表空间、InnoDB 系统表空间（innodb_system）或者通用表空间创建的表包含一个 TABLESPACE 属性，表示该表所在的表空间。目前，无论 innodb_file_per_table 设置为什么参数，CREATE TABLE … LIKE 都会保留 TABLESPACE 属性。为了避免复制新表时使用原始表的 TABLESPACE 属性，可以使用下面介绍的第三种方法。例如：

CREATE TABLE new_tbl SELECT * FROM orig_tbl LIMIT 0;

以上语句会基于 orig_tbl 创建一个新的空表 new_tbl，具体参考下一节内容。

CREATE TABLE … LIKE 使用原始表的所有 ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 值创建目标表。

另外，LIKE 只能基于表进行复制，而不能复制视图。

CREATE TABLE … SELECT

在 MySQL 中复制表结构的另一种方法就是利用查询语句的结果定义字段和复制的数据：

CREATE TABLE table_name
  [AS] SELECT ...;

其中的 SELECT 语句定义了新表的结构和数据。

以下示例基于查询的结果创建了一个新表：emp_devp，表中包含了研发部的所有员工。

CREATE TABLE emp_devp
    AS
SELECT e.*
  FROM employee e
  JOIN department d
    ON (d.dept_id = e.dept_id AND d.dept_name = '研发部');

对于这种语法，MySQL 实际上是在已有目标表中增加新的字段。

例如：

CREATE TABLE t1(col1 INTEGER, col2 INTEGER);
INSERT INTO t1(col1, col2) VALUES(1, 1), (2, 4);

CREATE TABLE t2(id INTEGER AUTO_INCREMENT PRIMARY KEY)
       ENGINE=InnoDB
    AS SELECT col1, col2 FROM t1;

我们首先为 t2 指定了一个自增 id，然后将 t1 的查询结果添加到该字段的后面。其中，ENGINE 选项属于 CREATE TABLE 语句，因此需要位于 SELECT 语句之前。

查询 t2 可以看到以上语句不仅复制了表结构，同时还复制了 t1 中的数据：

TABLE t2;

+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    4 |
+----+------+------+
2 rows in set (0.00 sec)

如果只想复制结构，不需要复制数据，可以在查询语句中增加 LIMIT 0 或者 WHERE 1=0 条件。

如果在 SELECT 语句前面增加 IGNORE 或者 REPLACE 关键字，可以处理复制数据时导致唯一键冲突的数据行。对于 IGNORE，源数据中和目标表重复的数据行就会被丢弃；对于 REPLACE，使用新数据行替换目标表中的已有数据行。如果没有指定任何选项，唯一键冲突将会返回错误。

CREATE TABLE … SELECT 命令不会自动创建任何索引，这样可以使得该语句尽量灵活。如果想要创建索引，可以在 SELECT 语句之前指定。

例如：

CREATE TABLE t3(id INTEGER PRIMARY KEY)
AS SELECT col1 as id, col2 FROM t1;

CREATE TABLE … SELECT 命令不会保留计算列的定义，也不会保留默认值定义。同时某些数据类型可能产生转换。例如，AUTO_INCREMENT 属性不会被保留，VARCHAR 类型被转换为 CHAR 类型。保留的属性包括 NULL（NOT NULL）以及 CHARACTER SET、COLLATION、COMMENT 和 DEFAULT 子句。

使用 CREATE TABLE … SELECT 命令创建表时，需要为查询语句中的函数和表达式指定一个别名，否则该命令可能失败或者创建意料之外的字段名：

CREATE TABLE artists_and_works
SELECT artist.name, COUNT(work.artist_id) AS number_of_works
FROM artist LEFT JOIN work ON artist.id = work.artist_id
GROUP BY artist.id;

对于 CREATE TABLE … SELECT 命令，如果我们指定了 IF NOT EXISTS 并且目标表已经存在，不会将数据复制到目标表，同时该语句不会写入日志文件。

CREATE TABLE … SELECT 命令不支持 FOR UPDATE 选项。

CREATE TABLE … SELECT 命令只会应用字段的 ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 属性。表和索引的 ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 属性不会被应用，除非为目标表明确指定这些选项。

MySQL数据库的索引原理与慢SQL优化的5大原则
上一篇
MySQL索引的一些常见面试题大全(2022年)
下一篇