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

叁歲伎倆 2024-03-23 22:04 35阅读 0赞

![2ed1e68fbb5c21ac3b08ac050dc39de2.jpeg][]

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

### CREATE TABLE ###

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

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 </td> 
 <td> <code>CREATE</code> <code>TABLE</code> <code>[IF </code><code>NOT</code> <code>EXISTS] table_name</code> <code>(</code> <code>&nbsp;&nbsp;</code><code>column1 data_type column_constraint,</code> <code>&nbsp;&nbsp;</code><code>column2 data_type,</code> <code>&nbsp;&nbsp;</code><code>...,</code> <code>&nbsp;&nbsp;</code><code>table_constraint</code> <code>);</code> </td> 
 </tr> 
 </tbody> 
</table>

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

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

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

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

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 </td> 
 <td> <code>CREATE</code> <code>TABLE</code> <code>department</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>( dept_id&nbsp;&nbsp;&nbsp; </code><code>INTEGER</code> <code>NOT</code> <code>NULL</code> <code>PRIMARY</code> <code>KEY</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>, dept_name&nbsp; </code><code>VARCHAR</code><code>(50) </code><code>NOT</code> <code>NULL</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>) ;</code> </td> 
 </tr> 
 </tbody> 
</table>

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

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

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

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 </td> 
 <td> <code>CREATE</code> <code>TABLE</code> <code>department</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>( dept_id&nbsp;&nbsp;&nbsp; </code><code>INTEGER</code> <code>NOT</code> <code>NULL</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>, dept_name&nbsp; </code><code>VARCHAR</code><code>(50) </code><code>NOT</code> <code>NULL</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>, </code><code>CONSTRAINT</code> <code>pk_department </code><code>PRIMARY</code> <code>KEY</code> <code>(dept_id)</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>) ;</code> </td> 
 </tr> 
 </tbody> 
</table>

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

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

例如：

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 </td> 
 <td> <code>CREATE</code> <code>TABLE</code> <code>department</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>( dept_id&nbsp;&nbsp;&nbsp; </code><code>INTEGER</code> <code>AUTO_INCREMENT </code><code>PRIMARY</code> <code>KEY</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>, dept_name&nbsp; </code><code>VARCHAR</code><code>(50) </code><code>NOT</code> <code>NULL</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>) ;</code> </td> 
 </tr> 
 </tbody> 
</table>

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

然后我们再创建两个表：

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 </td> 
 <td> <code>CREATE</code> <code>TABLE</code> <code>job</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>( job_id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code><code>INTEGER</code> <code>NOT</code> <code>NULL</code> <code>PRIMARY</code> <code>KEY</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>, job_title&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code><code>VARCHAR</code><code>(50) </code><code>NOT</code> <code>NULL</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>) ;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code>&nbsp; <code>CREATE</code> <code>TABLE</code> <code>employee</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>( emp_id&nbsp;&nbsp;&nbsp; </code><code>INTEGER</code> <code>NOT</code> <code>NULL</code> <code>PRIMARY</code> <code>KEY</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>, emp_name&nbsp; </code><code>VARCHAR</code><code>(50) </code><code>NOT</code> <code>NULL</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>, sex&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </code><code>VARCHAR</code><code>(10) </code><code>NOT</code> <code>NULL</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>, dept_id&nbsp;&nbsp; </code><code>INTEGER</code> <code>NOT</code> <code>NULL</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>, manager&nbsp;&nbsp; </code><code>INTEGER</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>, hire_date </code><code>DATE</code> <code>NOT</code> <code>NULL</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>, job_id&nbsp;&nbsp;&nbsp; </code><code>INTEGER</code> <code>NOT</code> <code>NULL</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>, salary&nbsp;&nbsp;&nbsp; </code><code>NUMERIC</code><code>(8,2) </code><code>NOT</code> <code>NULL</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>, bonus&nbsp;&nbsp;&nbsp;&nbsp; </code><code>NUMERIC</code><code>(8,2)</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>, email&nbsp;&nbsp;&nbsp;&nbsp; </code><code>VARCHAR</code><code>(100) </code><code>NOT</code> <code>NULL</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>, </code><code>CONSTRAINT</code> <code>ck_emp_sex </code><code>CHECK</code> <code>(sex </code><code>IN</code> <code>(</code><code>'男'</code><code>, </code><code>'女'</code><code>))</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>, </code><code>CONSTRAINT</code> <code>ck_emp_salary </code><code>CHECK</code> <code>(salary &gt; 0)</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>, </code><code>CONSTRAINT</code> <code>uk_emp_email </code><code>UNIQUE</code> <code>(email)</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>, </code><code>CONSTRAINT</code> <code>fk_emp_dept </code><code>FOREIGN</code> <code>KEY</code> <code>(dept_id) </code><code>REFERENCES</code> <code>department(dept_id)</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>, </code><code>CONSTRAINT</code> <code>fk_emp_job </code><code>FOREIGN</code> <code>KEY</code> <code>(job_id) </code><code>REFERENCES</code> <code>job(job_id)</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>, </code><code>CONSTRAINT</code> <code>fk_emp_manager </code><code>FOREIGN</code> <code>KEY</code> <code>(manager) </code><code>REFERENCES</code> <code>employee(emp_id)</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>) ;</code> </td> 
 </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 </td> 
 <td> <code>CREATE</code> <code>TABLE</code> <code>[IF </code><code>NOT</code> <code>EXISTS] table_name</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>{ </code><code>LIKE</code> <code>old_tbl_name | (</code><code>LIKE</code> <code>old_tbl_name) }</code> </td> 
 </tr> 
 </tbody> 
</table>

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

例如：

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 </td> 
 <td> <code>CREATE</code> <code>TABLE</code> <code>emp_copy</code> <code>&nbsp;&nbsp;</code><code>LIKE</code> <code>employee;</code> </td> 
 </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 </td> 
 <td> <code>mysql&gt; show </code><code>create</code> <code>table</code> <code>emp_copy \G</code> <code>*************************** 1. row ***************************</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Table</code><code>: emp_copy</code> <code>Create</code> <code>Table</code><code>: </code><code>CREATE</code> <code>TABLE</code> <code>`emp_copy` (</code> <code>&nbsp;&nbsp;</code><code>`emp_id` </code><code>int</code> <code>NOT</code> <code>NULL</code><code>,</code> <code>&nbsp;&nbsp;</code><code>`emp_name` </code><code>varchar</code><code>(50) </code><code>NOT</code> <code>NULL</code><code>,</code> <code>&nbsp;&nbsp;</code><code>`sex` </code><code>varchar</code><code>(10) </code><code>NOT</code> <code>NULL</code><code>,</code> <code>&nbsp;&nbsp;</code><code>`dept_id` </code><code>int</code> <code>NOT</code> <code>NULL</code><code>,</code> <code>&nbsp;&nbsp;</code><code>`manager` </code><code>int</code> <code>DEFAULT</code> <code>NULL</code><code>,</code> <code>&nbsp;&nbsp;</code><code>`hire_date` </code><code>date</code> <code>NOT</code> <code>NULL</code><code>,</code> <code>&nbsp;&nbsp;</code><code>`job_id` </code><code>int</code> <code>NOT</code> <code>NULL</code><code>,</code> <code>&nbsp;&nbsp;</code><code>`salary` </code><code>decimal</code><code>(8,2) </code><code>NOT</code> <code>NULL</code><code>,</code> <code>&nbsp;&nbsp;</code><code>`bonus` </code><code>decimal</code><code>(8,2) </code><code>DEFAULT</code> <code>NULL</code><code>,</code> <code>&nbsp;&nbsp;</code><code>`email` </code><code>varchar</code><code>(100) </code><code>NOT</code> <code>NULL</code><code>,</code> <code>&nbsp;&nbsp;</code><code>PRIMARY</code> <code>KEY</code> <code>(`emp_id`),</code> <code>&nbsp;&nbsp;</code><code>UNIQUE</code> <code>KEY</code> <code>`uk_emp_email` (`email`),</code> <code>&nbsp;&nbsp;</code><code>KEY</code> <code>`fk_emp_dept` (`dept_id`),</code> <code>&nbsp;&nbsp;</code><code>KEY</code> <code>`fk_emp_job` (`job_id`),</code> <code>&nbsp;&nbsp;</code><code>KEY</code> <code>`fk_emp_manager` (`manager`),</code> <code>&nbsp;&nbsp;</code><code>CONSTRAINT</code> <code>`emp_copy_chk_1` </code><code>CHECK</code> <code>((`salary` &gt; 0)),</code> <code>&nbsp;&nbsp;</code><code>CONSTRAINT</code> <code>`emp_copy_chk_2` </code><code>CHECK</code> <code>((`sex` </code><code>in</code> <code>(_utf8mb4</code><code>'男'</code><code>,_utf8mb4</code><code>'女'</code><code>)))</code> <code>) ENGINE=InnoDB </code><code>DEFAULT</code> <code>CHARSET=utf8mb4 </code><code>COLLATE</code><code>=utf8mb4_0900_ai_ci</code> <code>1 row </code><code>in</code> <code>set</code> <code>(0.01 sec)</code> </td> 
 </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
 <tr> 
 <td> 1 </td> 
 <td> <code>CREATE</code> <code>TABLE</code> <code>new_tbl </code><code>SELECT</code> <code>* </code><code>FROM</code> <code>orig_tbl LIMIT 0;</code> </td> 
 </tr> 
 </tbody> 
</table>

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

CREATE TABLE … LIKE 使用原始表的所有 ENGINE\_ATTRIBUTE 和 SECONDARY\_ENGINE\_ATTRIBUTE 值创建目标表。

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

### CREATE TABLE … SELECT ###

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

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 </td> 
 <td> <code>CREATE</code> <code>TABLE</code> <code>table_name</code> <code>&nbsp;&nbsp;</code><code>[</code><code>AS</code><code>] </code><code>SELECT</code> <code>...;</code> </td> 
 </tr> 
 </tbody> 
</table>

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

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

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 </td> 
 <td> <code>CREATE</code> <code>TABLE</code> <code>emp_devp</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>AS</code> <code>SELECT</code> <code>e.*</code> <code>&nbsp;&nbsp;</code><code>FROM</code> <code>employee e</code> <code>&nbsp;&nbsp;</code><code>JOIN</code> <code>department d</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>ON</code> <code>(d.dept_id = e.dept_id </code><code>AND</code> <code>d.dept_name = </code><code>'研发部'</code><code>);</code> </td> 
 </tr> 
 </tbody> 
</table>

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

例如：

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 </td> 
 <td> <code>CREATE</code> <code>TABLE</code> <code>t1(col1 </code><code>INTEGER</code><code>, col2 </code><code>INTEGER</code><code>);</code> <code>INSERT</code> <code>INTO</code> <code>t1(col1, col2) </code><code>VALUES</code><code>(1, 1), (2, 4);</code> <code>CREATE</code> <code>TABLE</code> <code>t2(id </code><code>INTEGER</code> <code>AUTO_INCREMENT </code><code>PRIMARY</code> <code>KEY</code><code>)</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>ENGINE=InnoDB</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>AS</code> <code>SELECT</code> <code>col1, col2 </code><code>FROM</code> <code>t1;</code> </td> 
 </tr> 
 </tbody> 
</table>

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

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

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

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

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

例如：

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 </td> 
 <td> <code>CREATE</code> <code>TABLE</code> <code>t3(id </code><code>INTEGER</code> <code>PRIMARY</code> <code>KEY</code><code>)</code> <code>AS</code> <code>SELECT</code> <code>col1 </code><code>as</code> <code>id, col2 </code><code>FROM</code> <code>t1;</code> </td> 
 </tr> 
 </tbody> 
</table>

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

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

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 </td> 
 <td> <code>CREATE</code> <code>TABLE</code> <code>artists_and_works</code> <code>SELECT</code> <code>artist.</code><code>name</code><code>, </code><code>COUNT</code><code>(</code><code>work</code><code>.artist_id) </code><code>AS</code> <code>number_of_works</code> <code>FROM</code> <code>artist </code><code>LEFT</code> <code>JOIN</code> <code>work</code> <code>ON</code> <code>artist.id = </code><code>work</code><code>.artist_id</code> <code>GROUP</code> <code>BY</code> <code>artist.id;</code> </td> 
 </tr> 
 </tbody> 
</table>

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

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

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

转自：[微点阅读][Link 1] [https://www.weidianyuedu.com][Link 1]

[2ed1e68fbb5c21ac3b08ac050dc39de2.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/c21e95b0d2e246e98a28f03728c4c4fe.png
[Link 1]: https://www.weidianyuedu.com