复制表数据

PreviousUUID 和主键 Next变量

Last updated 5 years ago

Was this helpful?

复制表数据

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

MySQL将表复制到新表

将数据从现有表复制到新的数据，在某些情况下非常有用，例如备份数据和复制生产数据进行测试。

要将数据从表复制到新表，请使用和语句，如下所示：

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

首先，MySQL使用语句中指定的名称创建一个新表。新表的结构由SELECT语句的结果集定义。然后，MySQL将来自SELECT语句的数据填充到新表中。

要将部分数据从现有表复制到新表中，请在SELECT语句中使用子句，如下所示：

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;

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 | +1 650 219 4782  | 100 Market Street        | Suite 300    | CA    | USA       | 94080      | NA        |
| 2          | Boston        | +1 215 837 0825  | 1550 Court Place         | Suite 102    | MA    | USA       | 02107      | NA        |
| 3          | NYC           | +1 212 555 3000  | 523 East 53rd Street     | apt. 5A      | NY    | USA       | 10022      | NA        |
| 4          | Paris         | +33 14 723 4404  | 43 Rue Jouffroy Dabbans | NULL         | NULL  | France    | 75017      | EMEA      |
| 5          | Beijing       | +86 33 224 5000  | 4-1 Haidian Area         | NULL         | BJ    | China     | 110000     | NA        |
| 6          | Sydney        | +61 2 9264 2451  | 5-11 Wentworth Avenue    | Floor #2     | NULL  | Australia | NSW 2010   | APAC      |
| 7          | London        | +44 20 7877 2041 | 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 | +1 650 219 4782 | 100 Market Street    | Suite 300    | CA    | USA     | 94080      | NA        |
| 2          | Boston        | +1 215 837 0825 | 1550 Court Place     | Suite 102    | MA    | USA     | 02107      | NA        |
| 3          | NYC           | +1 212 555 3000 | 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 | +1 650 219 4782  | 100 Market Street        | Suite 300    | CA    | USA       | 94080      | NA        |
| 2          | Boston        | +1 215 837 0825  | 1550 Court Place         | Suite 102    | MA    | USA       | 02107      | NA        |
| 3          | NYC           | +1 212 555 3000  | 523 East 53rd Street     | apt. 5A      | NY    | USA       | 10022      | NA        |
| 4          | Paris         | +33 14 723 4404  | 43 Rue Jouffroy Dabbans | NULL         | NULL  | France    | 75017      | EMEA      |
| 5          | Beijing       | +86 33 224 5000  | 4-1 Haidian Area         | NULL         | BJ    | China     | 110000     | NA        |
| 6          | Sydney        | +61 2 9264 2451  | 5-11 Wentworth Avenue    | Floor #2     | NULL  | Australia | NSW 2010   | APAC      |
| 7          | London        | +44 20 7877 2041 | 25 Old Broad Street      | Level 7      | NULL  | UK        | EC2N 1HN   | EMEA      |
+------------+---------------+------------------+--------------------------+--------------+-------+-----------+------------+-----------+
7 rows in set

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