复制表数据

在本教程中，您将学习如何使用CREATE TABLE和SELECT语句在同一数据库或从一个数据库复制表。

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。 第二个语句将现有existing_table表中的数据插入到new_table中。

MySQL拷贝表示例

以下语句将数据从office表复制到示例数据(yiibaidb)库中指定名为offices_bk的新表。

USE yiibaidb;
CREATE TABLE IF NOT EXISTS offices_bk 
SELECT * FROM
    offices;

可以通过查询office_bk表中的数据来验证副本，如下所示：

SELECT
    *
FROM
    offices_bk;

执行上面查询，得到以下结果 -

+------------+---------------+------------------+--------------------------+--------------+-------+-----------+------------+-----------+
| officeCode | city          | phone            | addressLine1             | addressLine2 | state | country   | postalCode | territory |
+------------+---------------+------------------+--------------------------+--------------+-------+-----------+------------+-----------+
| 1          | San Francisco | [phone]  | 100 Market Street        | Suite 300    | CA    | USA       | 94080      | NA        |
| 2          | Boston        | [phone]  | 1550 Court Place         | Suite 102    | MA    | USA       | 02107      | NA        |
| 3          | NYC           | [phone]  | 523 East 53rd Street     | apt. 5A      | NY    | USA       | 10022      | NA        |
| 4          | Paris         | [phone]  | 43 Rue Jouffroy Dabbans | NULL         | NULL  | France    | 75017      | EMEA      |
| 5          | Beijing       | [phone]  | 4-1 Haidian Area         | NULL         | BJ    | China     | 110000     | NA        |
| 6          | Sydney        | [phone]  | 5-11 Wentworth Avenue    | Floor #2     | NULL  | Australia | NSW 2010   | APAC      |
| 7          | London        | [phone] | 25 Old Broad Street      | Level 7      | NULL  | UK        | EC2N 1HN   | EMEA      |
+------------+---------------+------------------+--------------------------+--------------+-------+-----------+------------+-----------+
7 rows in set

如果我们想仅复制在美国(USA)办公室，可以将WHERE子句添加到SELECT语句中，如下所示：

USE yiibaidb;
CREATE TABLE IF NOT EXISTS offices_usa 
SELECT * 
FROM
    offices
WHERE
    country = 'USA';

验证上面查询执行，得到以下结果 -

SELECT * 
FROM
    offices
WHERE
    country = 'USA';
+------------+---------------+-----------------+----------------------+--------------+-------+---------+------------+-----------+
| officeCode | city          | phone           | addressLine1         | addressLine2 | state | country | postalCode | territory |
+------------+---------------+-----------------+----------------------+--------------+-------+---------+------------+-----------+
| 1          | San Francisco | [phone] | 100 Market Street    | Suite 300    | CA    | USA     | 94080      | NA        |
| 2          | Boston        | [phone] | 1550 Court Place     | Suite 102    | MA    | USA     | 02107      | NA        |
| 3          | NYC           | [phone] | 523 East 53rd Street | apt. 5A      | NY    | USA     | 10022      | NA        |
+------------+---------------+-----------------+----------------------+--------------+-------+---------+------------+-----------+
3 rows in set

假设不仅要复制数据，还要复制与offices表相关联的所有数据库对象，我们使用以下语句：

CREATE TABLE offices_dup LIKE offices;

INSERT office_dup
SELECT * FROM offices;

MySQL复制表到另一个数据库

有时，您要将表复制到其他数据库。 在这种情况下，可使用以下语句：

CREATE TABLE destination_db.new_table 
LIKE source_db.existing_table;

INSERT destination_db.new_table 
SELECT *
FROM source_db.existing_table;

第一个语句通过从源数据库(source_db)复制现有表(existing_table)到目标数据库(destination_db)中创建一个新表new_table。

第二个语句将数据从源数据库中的现有(existing_table)表复制到目标数据库中的新表。

下面来看看看下面的例子。

首先，我们使用以下语句创建一个名为testdb的数据库：

CREATE DATABASE IF NOT EXISTS testdb;

其次，通过将其结构从示例数据库(yiibaidb)中的offices表复制出来，在testdb中创建了offices表。

CREATE TABLE testdb.offices LIKE yiibaidb.offices;

第三，我们将数据从yiibaidb.offices表复制到testdb.offices表中。

INSERT testdb.offices
SELECT *
FROM yiibaidb.offices;

我们来验证testdb.offices表中的数据。

SELECT
    *
FROM
    testdb.offices;

执行上面查询语句，得到以下结果 -

mysql> SELECT
    *
FROM
    testdb.offices;
+------------+---------------+------------------+--------------------------+--------------+-------+-----------+------------+-----------+
| officeCode | city          | phone            | addressLine1             | addressLine2 | state | country   | postalCode | territory |
+------------+---------------+------------------+--------------------------+--------------+-------+-----------+------------+-----------+
| 1          | San Francisco | [phone]  | 100 Market Street        | Suite 300    | CA    | USA       | 94080      | NA        |
| 2          | Boston        | [phone]  | 1550 Court Place         | Suite 102    | MA    | USA       | 02107      | NA        |
| 3          | NYC           | [phone]  | 523 East 53rd Street     | apt. 5A      | NY    | USA       | 10022      | NA        |
| 4          | Paris         | [phone]  | 43 Rue Jouffroy Dabbans | NULL         | NULL  | France    | 75017      | EMEA      |
| 5          | Beijing       | [phone]  | 4-1 Haidian Area         | NULL         | BJ    | China     | 110000     | NA        |
| 6          | Sydney        | [phone]  | 5-11 Wentworth Avenue    | Floor #2     | NULL  | Australia | NSW 2010   | APAC      |
| 7          | London        | [phone] | 25 Old Broad Street      | Level 7      | NULL  | UK        | EC2N 1HN   | EMEA      |
+------------+---------------+------------------+--------------------------+--------------+-------+-----------+------------+-----------+
7 rows in set

在本教程中，我们向您展示了将数据库中的表和从一个数据库复制到另一个数据库的各种技术。