盘点MYSQL中创建表的三种方法

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

CREATE TABLE

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

1

2

3

4

5

6

7

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：

1

2

3

4

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 个字符，不允许为空。

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

1

2

3

4

5

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）自动生成一个唯一的数字。

例如：

1

2

3

4

CREATE

TABLE

department

( dept_id    

INTEGER

AUTO_INCREMENT 

PRIMARY

KEY

, dept_name  

VARCHAR

(50) 

NOT

NULL

) ;

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

然后我们再创建两个表：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

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 还提供了复制已有表结构的方法：

1

2

CREATE

TABLE

[IF 

NOT

EXISTS] table_name

{ 

LIKE

old_tbl_name | (

LIKE

old_tbl_name) }

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

例如：

1

2

CREATE

TABLE

emp_copy

LIKE

employee;

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

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 属性，可以使用下面介绍的第三种方法。例如：

1

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 中复制表结构的另一种方法就是利用查询语句的结果定义字段和复制的数据：

1

2

CREATE

TABLE

table_name

[

AS

] 

SELECT

...;

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

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

1

2

3

4

5

6

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 实际上是在已有目标表中增加新的字段。

例如：

1

2

3

4

5

6

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 中的数据：

1

2

3

4

5

6

7

8

9

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 语句之前指定。

例如：

1

2

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 命令创建表时，需要为查询语句中的函数和表达式指定一个别名，否则该命令可能失败或者创建意料之外的字段名：

1

2

3

4

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 属性不会被应用，除非为目标表明确指定这些选项。

转自：微点阅读 https://www.weidianyuedu.com