MySQL 表数据拷贝实战讲解 - MySQL 系列教程

卡拉先生
发布于 2020年09月24日 | 上次编辑:2020年09月26日

header

在 MySQL 的日常使用中,我们有时候会需要把一些表结构进行复制操作。在本篇教程中,我们会一步一步教你如何使用 CREATE TABLESELECT 语句完成复制表的操作。

如果你对 MySQL 还不了解,可以从我们的初学者教程开始看起,或访问我们的技术社区查看更多 MySQL 教程。

接下来,我们从最基本的操作入手,讲解表数据拷贝。

如何拷到一个新表里

把数据从一个已有的表拷贝到一个新表,是一个经常需要用到的操作。比如有时候你要备份数据,或者你要把生产环境的数据拷贝下来做测试等等。

要从一个表拷贝数据出来放到一个新表里,你要使用 CREATE TABLE 和 SELECT 语句,来看一段代码:

CREATE TABLE new_table 
SELECT col, col2, col3 
FROM
    existing_table;

首先,MySQL 会创建一个 CREATE TABLE 语句中你命名的表格。而新表的结构主要取决于你后面 SELECT 语句所选出来的结果集。然后,MySQL 会把你 SELECT 拿到的数据塞到新表里去。

如果只想拷贝一部分数据到新表里去,那你就需要在 SELECT 语句中使用 WHERE 语句,就像下面的代码:

CREATE TABLE new_table 
SELECT col1, col2, col3 
FROM
    existing_table
WHERE
    conditions;

另外同样重要的是,你在创建新表之前,应该检查这个表是不是已经存在了。想要实现这一步,你要在 CREATE TABLE 语句里使用 IF NOT EXIST 语句。所以完整的拷贝到一个新表的操作代码应该是:

CREATE TABLE new_table 
SELECT col1, col2, col3 
FROM
    existing_table
WHERE
    conditions;

注意,上面的操作仅仅拷贝了表和表里的数据。它并不会拷贝其他数据库对象,例如索引,主键约束,外键约束,触发器等和表相关的东西。

如果你不但要拷数据,你还想把这些表相关的对象统统拷贝过去,你应该这样写代码:

CREATE TABLE IF NOT EXISTS new_table LIKE existing_table;

INSERT new_table
SELECT * FROM existing_table;

我们执行了两个语句。第一个语句通过复制 existing_table 创建了一个新表格 new_table。第二个语句从已有表格中向 new_table 中 插入数据。

实战案例

来看一个例子,下面的语句会在样例数据库 classicmodels 中把数据从offices 表中拷贝到一个叫 offices_bk 的表格里。

CREATE TABLE IF NOT EXISTS offices_bk 
SELECT * FROM
    offices;

我们可以查询一下 office_bk里数据看看是不是生效了:

SELECT
    *
FROM
    offices_bk;

table1

如果我们只想拷贝上海办公室的数据,我们可以在 SELECT 语句后面添加 WHERE 语句:

CREATE TABLE IF NOT EXISTS offices_shanghai 
SELECT * 
FROM
    offices
WHERE
    province = '上海'

然后我们从 offices_shanghai 里把数据捞出来看看:

table2

再假设,我们不但要拷贝数据,还要把所有 offices 相关的数据对象都拷贝出来,那我们就要这么写:

CREATE TABLE offices_dup LIKE offices;

INSERT office_dup
SELECT * FROM offices;

如何把表拷到一个新数据库里

有时候,你甚至要把一个表拷到一个不同的数据库里,那么你就要这么写代码了:

CREATE TABLE destination_db.new_table 
LIKE source_db.existing_table;

INSERT destination_db.new_table 
SELECT *
FROM source_db.existing_table;

在第一句代码里,我们通过从源数据库(sourcedb)里复制已经有的表(existingtable),并且在目标数据库(destinatiodb)里创建了一个新表叫 `newtable`。

在第二句代码里,我们把源数据库里已有表的数据拷贝到了目标数据库的新表里。

来看个例子好了:

首先我们要创建一个名叫 testdb 的数据库:

CREATE DATABASE IF NOT EXISTS testdb;

然后我们要把刚才 classicmodels 数据库里的 offices 表复制到这个 testdb 里的一个 offices 表中。

CREATE TABLE testdb.offices LIKE classicmodels.offices;

最后我们把数据从 classicmodels.offices 拷贝到 testdb.offices 表里去。

INSERT testdb.offices
SELECT *
FROM classicmodels.offices;

我们来验证一下 testdb.offices 表里的数据:

SELECT
    *
FROM
    testdb.offices;

table1

到这里,你已经学会了在数据库内拷贝一张表,以及拷贝表到新的数据库,这两种数据库表拷贝技术的操作方式了。

接下来,在我们的 MySQL 系列教程中你还可以学习到

同时也欢迎使用我们的数据库搜索工具「卡拉搜索」。

一行代码快速部署「站内搜索」,app、小程序皆可使用。精准智能排序,毫秒级搜索体验。卡拉搜索 Demo,欢迎体验。

友情链接更新日志© 2020, 卡拉搜索, Built with ❤️ in San Francisco + Beijing

京ICP备15049164号-3