【MySQL 使用秘籍】克隆数据表、保存查询数据至数据表以及创建临时表

文章目录

• • 1. 引言
  • 2. 克隆一张表
  • • 问题
    • 解决方案
    • 讨论
  • 3. 保存查询数据至数据表
  • • 问题
    • 解决方案
    • 讨论
  • 4. 创建临时表
  • • 问题
    • 解决方案
    • 讨论

1. 引言

本文后续的介绍将基于名为 mail 的数据表，建表和向表中插入数据的语句如下：

DROP TABLE IF EXISTS mail;
#@ _CREATE_TABLE_
CREATE TABLE mail
(
  t       DATETIME,    # when message was sent
  srcuser VARCHAR(8),  # sender (source user and host)
  srchost VARCHAR(20),
  dstuser VARCHAR(8),  # recipient (destination user and host)
  dsthost VARCHAR(20),
  size    BIGINT,      # message size in bytes
  INDEX (t)
);
#@ _CREATE_TABLE_

INSERT INTO mail (t,srchost,srcuser,dsthost,dstuser,size)
  VALUES
    ('2014-05-11 10:15:08','saturn','barb','mars','tricia',58274),
    ('2014-05-12 12:48:13','mars','tricia','venus','gene',194925),
    ('2014-05-12 15:02:49','mars','phil','saturn','phil',1048),
    ('2014-05-12 18:59:18','saturn','barb','venus','tricia',271),
    ('2014-05-14 09:31:37','venus','gene','mars','barb',2291),
    ('2014-05-14 11:52:17','mars','phil','saturn','tricia',5781),
    ('2014-05-14 14:42:21','venus','barb','venus','barb',98151),
    ('2014-05-14 17:03:01','saturn','tricia','venus','phil',2394482),
    ('2014-05-15 07:17:48','mars','gene','saturn','gene',3824),
    ('2014-05-15 08:50:57','venus','phil','venus','phil',978),
    ('2014-05-15 10:25:52','mars','gene','saturn','tricia',998532),
    ('2014-05-15 17:35:31','saturn','gene','mars','gene',3856),
    ('2014-05-16 09:00:28','venus','gene','mars','barb',613),
    ('2014-05-16 23:04:19','venus','phil','venus','barb',10294),
    ('2014-05-19 12:49:23','mars','phil','saturn','tricia',873),
    ('2014-05-19 22:21:51','saturn','gene','venus','gene',23992)
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

2. 克隆一张表

问题

你希望创建一张数据表，且保证该表和某已有数据表具有相同表结构。

解决方案

使用语句 CREATE TABLE ... LIKE 来克隆某张数据表的表结构。如果希望将原表中全部或部分记录拷贝到克隆的表中，可以使用 INSERT INTO ... SELECT 这样的语句。

讨论

为了创建和一张已有数据表的表结构一模一样的表，可以使用下列语句：

CREATE TABLE new_table LIKE original_table;
1

使用上述语句克隆出来的新表的表结构和原始表一样，存在的部分区别是：

• 上述语句并不会克隆原表中外键的定义；
• 上述语句不会拷贝任何 DATA DIRECTORY 或 INDEX DIRECTORY 表选项（如果原表确有使用的话）。

新克隆出来的表是空的，如果你同时还希望新表中的数据也和原表中的一样，可以使用下列语句：

INSERT INTO new_table SELECT * FROM original_table;
1

如果仅需要从原表中拷贝部分数据至新表，可以在拷贝时加上 WHERE 限制条件，例如：

CREATE TABLE mail2 LIKE mail;
INSERT INTO mail2 SELECT * FROM mail WHERE srcuser = 'barb';
1
2

3. 保存查询数据至数据表

问题

你希望将通过 SELECT 语句查询出来的结果保存到一张数据表中，而不是在终端上进行展示。

解决方案

如果数据表存在，那么可以使用 INSERT INTO ... SELECT 实现需求。如果数据表不存在，那么可以使用 CREATE TABLE ... SELECT 来实现需求。

讨论

通常，MySQL 服务端会将 SELECT 语句的结果返回给执行该语句的客户端。将 SELECT 语句的查询结果保存到一张数据表中，可能有以下好处：

• 可以简单地拷贝出一张数据表的全部或部分数据。例如：假设你正在开发的应用涉及到修改数据表，那么你可以先通过上述给出的方式将数据表拷贝一份，然后在拷贝出的表上进行操作，这样就不会因错误的操作而影响原表数据。如果原表数据量很大，那么你可以先通过上述方式仅拷贝出数据表的一部分，因为对于部分数据的查询需要的时间更短；
• 有些应用程序可能会同时维护一个大的仓储表以及一个更小的生产表，应用程序主要向后者插入新的数据，后者会定期将其中的数据导入前者进行归档，然后清理前者，以此避免因向不断增大的数据表中插入数据而引起的性能下降问题；
• 针对大的数据表进行统计操作一般比较耗时，为了降低重复执行这类操作的必要性，可以将执行结果保存在另一张表中，以供后续使用。

下面的示例中会用到两张表，其中 src_tbl 表示原表，数据通过 SELECT 语句从中查询得到，dst_tbl 表示目标表，从前者中查询得到的数据会直接插入其中。

如果目标表存在，那么使用 INSERT ... SELECT 语句可以将查询结果直接拷贝至其中。例如，如果 dst_tbl 中包含一个整型字段 i 和一个字符串类型字段 s ，那么下列语句实现的功能就是将 src_tbl 的 val 字段和 name 字段分别拷贝至 dst_tbl 的 i 和 s 字段：

INSERT INTO dst_tbl (i, s) SELECT val, name FROM src_tbl;
1

需要注意的是，从 src_tbl 表中查出的字段数目一定要和向 dst_tbl 表中插入的字段数目要一致，而且两表中的字段关系仅仅基于在语句中的位置对应起来，而和字段名称无关。如果二者具有相同数量的字段，且相同位置的字段类型也一样，那么在拷贝所有记录的所有字段时可以这么写：

INSERT INTO dst_tbl SELECT * FROM src_tbl;
1

如果只希望拷贝部分记录，则可以在 SELECT 语句后加上 WHERE 限制条件：

INSERT INTO dst_tbl SELECT * FROM src_tbl
WHERE val > 100 AND name LIKE 'A%';
1
2

实际上， SELECT 语句还可以更加复杂。例如，下列的语句先统计了 src_tbl 表中 name 字段各个取值的出现频次，然后将频次和字段名保存在了 dst_tbl 中：

INSERT INTO dst_tbl (i, s) SELECT COUNT(*), name
FROM src_tbl GROUP BY name;
1
2

如果目标表不存在，你当然可以先用 CREATE TABLE 语句将表先建出来，然后再使用 INSERT ... SELECT 语句；另一种更加简洁的方式是使用 CREATE TABLE ... SELECT 将两个步骤合在一起。例如：

CREATE TABLE dst_tbl SELECT * FROM src_tbl;
1

在上述语句中，MySQL 会基于 src_tbl 的字段名称、数量以及类型创建 dst_tbl 表，然后将前者中的数据都查询出来后插入后者。如果只想拷贝前者中的部分数据，可以加上合适的 WHERE 条件。如果只想创建一个空的表，可以使用类型下列语句：

CREATE TABLE dst_tbl SELECT * FROM src_tbl WHERE FALSE;
1

如果只想拷贝 src_tbl 表中部分字段，可以在 SELECT 语句中进行指定。例如，如果 src_tbl 包含字段 a ，b ，c 和 d ，如果只想在创建 dst_tbl 的同时拷贝字段 b 和 d ，那么可以使用下列语句：

CREATE TABLE dst_tbl SELECT b, d FROM src_tbl;
1

如果除了希望在 dst_tbl 中创建从 src_tbl 中 SELECT 出的字段外，还希望新增别的字段，那么需要在 CREATE TABLE 语句的部分增加对应的语句。例如，下面的语句，除了在 dst_tbl 表中创建了 src_tbl 中的字段 a ， b 和 c 以外，还新增了一个具有 AUTO_INCREMENT 属性的 id 字段：

CREATE TABLE dst_tbl
(
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
)
SELECT a, b, c FROM src_tbl;
1
2
3
4
5
6

如果新建的 dst_tbl 表中的值来自 src_tbl 表中的表达式，那么在新建的 dst_tbl 表中，对应的字段名就是表达式的名称，这会使得字段名不那么直观。对此，可以通过为字段起别名的方式来改进。例如，src_tbl 中包含了收据信息，收据信息中是商品的数量和单价，那么，下面的语句实现了这样的功能，先统计每张收据中的物品总价，然后将收据编号和总价插入新建的 dst_tbl 表中：

CREATE TABLE dst_tbl
SELECT inv_no, SUM(unit_cost*quantity) AS total_cost
FROM src_tbl GROUP BY inv_no;
1
2
3

如上所述，CREATE TABLE ... SELECT 语句的功能很方便，但是新建表时可以指定的信息却没有直接使用 CREATE TABLE 来得全面。例如：MySQL 并不知道是否需要在新建表时对某个字段添加索引，或是否需要指定默认值。对此，可以通过下列方式来改善：

• 可以使用前述介绍的克隆一张表的方式，来创建一张和原表一模一样的表；

• 在创建目标表的同时显式指定所需要的约束。例如，如果 src_tbl 在 id 字段有 PRIMARY KEY 约束，同时在 state 和 city 字段上有联合索引，那么就可以使用下列语句：

  CREATE TABLE dst_tbl (PRIMARY KEY (id), INDEX(state,city))
  SELECT * FROM src_tbl;
  1
  2

• 字段的属性例如 AUTO_INCREMENT 以及默认值并不会被拷贝至目标表，如果的确需要，可以先使用 CREATE TABLE ... SELECT 语句，然后再使用 ALTER TABLE 语句进行相应的修改。例如：

  CREATE TABLE dst_tbl (PRIMARY KEY (id)) SELECT * FROM src_tbl;
  ALTER TABLE dst_tbl MODIFY id INT UNSIGNED NOT NULL AUTO_INCREMENT;
  1
  2

4. 创建临时表

问题

你需要一张仅短时间存在的数据表，该表需要在被使用完之后自动消失。

解决方案

使用 TEMPORARY 关键字来创建一张临时表，此后 MySQL 本身会来管理其生命周期。

讨论

有一些操作可能仅要求数据表暂时存在，且要求当该表不再会被用到时自动消失。当然，在你的确不需要某张表后，你完全可以使用 DROP TABLE 语句来显式地删除一张表。

另外一种更加优雅和安全的做法是使用 CREATE TEMPORARY TABLE 语句。该语句和 CREATE TABLE 功能类似，只是前者创建的是一张临时表，在当前客户端和服务端结束会话后，如果你没有显式地删除这张表，那么 MySQL 会自动帮你删除这张临时表。

• 通过显式定义表字段的方式创建临时表：

CREATE TEMPORARY TABLE tbl_name (...column definitions...);
1

• 基于已有表创建临时表：

CREATE TEMPORARY TABLE new_table LIKE original_table;
1

• 基于已有表创建临时表同时插入已有表中数据：

CREATE TEMPORARY TABLE tbl_name SELECT ... ;
1

需要注意的是，由于临时表只和某个客户端会话相关，所以多个客户端可以创建具有相同名字的临时表，同时这些临时表还不会相互影响。这对于使用临时表的应用来说很方便，因为你不需要确保临时表具有唯一的名字。

一张临时表可以和永久表具有相同的名字，这种情况下，在临时表存续期间，同名永久表是“不可见的”，这样的好处是，你可以拷贝出一张临时表，然后对其进行修改而不影响原始表。例如，下面的 DELETE 语句只会从名为 mail 的临时表中删除数据，而不影响原始表中的数据：

mysql> CREATE TEMPORARY TABLE mail SELECT * FROM mail;
Query OK, 16 rows affected (0.01 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*) FROM mail;
+----------+
| COUNT(*) |
+----------+
|       16 |
+----------+
1 row in set (0.01 sec)

mysql> DELETE FROM mail;
Query OK, 16 rows affected (0.01 sec)

mysql> SELECT COUNT(*) FROM mail;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.03 sec)

mysql> DROP TEMPORARY TABLE mail;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT COUNT(*) FROM mail;
+----------+
| COUNT(*) |
+----------+
|       16 |
+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

需要注意的是，虽然使用 CREATE TEMPORARY TABLE 语句创建的临时表具有很多有点，但还有以下几点需要牢记：

• 在同一个会话中，创建第二个同名的临时表会报错，因此正确的做法是先显式地将第一个临时表删除，然后再创建第二个临时表；

• 如果你的应用期望修改的是临时表而不是被隐藏的同名永久原表，那么如果你的程序接口具有重新建立连接的功能，那么请务必确保你的程序会检测连接因预期外的情况断开的情形。如果探测到连接因异常断开后会自动重连，那么重连之后，对同名数据表的修改将会是针对永久原表，而非临时表，因为此时临时表已经因为会话断开而被自动删除了；

• 有一些 API 支持持久化连接或者连接池。在这种情况下，当你的脚本运行结束后，临时表可能不会像你希望的那样会被自动删除，因为连接会因为被其他脚本使用而保持建立的状态，在这种情况下，创建临时表前建议先执行下列语句：

  DROP TEMPORARY TABLE IF EXISTS tbl_name
  1