复制 MySQL 表、索引和数据

Duplicating a MySQL table, indices, and data

提问by xkcd150

How do I copy or clone or duplicate the data, structure, and indices of a MySQL table to a new one?

如何将 MySQL 表的数据、结构和索引复制或克隆或复制到新表？

This is what I've found so far.

这是我迄今为止发现的。

This will copy the data and the structure, but not the indices:

这将复制数据和结构，但不会复制索引：

create table {new_table} select * from {old_table};

This will copy the structure and indices, but not the data:

这将复制结构和索引，但不会复制数据：

create table {new_table} like {old_table};

回答by Haim Evgi

To copy with indexes and triggers do these 2 queries:

要使用索引和触发器进行复制，请执行以下 2 个查询：

CREATE TABLE newtable LIKE oldtable; 
INSERT INTO newtable SELECT * FROM oldtable;

To copy just structure and data use this one:

要仅复制结构和数据，请使用以下命令：

CREATE TABLE tbl_new AS SELECT * FROM tbl_old;

I've asked this before:

我以前问过这个：

Copy a MySQL table including indexes

复制包含索引的 MySQL 表

回答by theDistantStar

Apart from the solution above, you can use ASto make it in one line.

除了上面的解决方案，您还可以使用AS将其放在一行中。

CREATE TABLE tbl_new AS SELECT * FROM tbl_old;

回答by Krishneil

MySQL way:

MySQL方式：

CREATE TABLE recipes_new LIKE production.recipes;
INSERT recipes_new SELECT * FROM production.recipes;

回答by spuvi86

Go to phpMyAdminand select your original table then select "Operations" tab in the "Copy table to (database.table)" area. Select the database where you want to copy and add a name for your new table.

转到phpMyAdmin并选择您的原始表，然后在“将表复制到 (database.table)”区域中选择“操作”选项卡。选择要复制到的数据库并为新表添加名称。

回答by Jai

I found the same situation and the approach which I took was as follows:

我发现了同样的情况，我采取的方法如下：

1. Execute SHOW CREATE TABLE <table name to clone>: This will give you the Create Tablesyntax for the table which you want to clone
2. Run the CREATE TABLEquery by changing the table name to clone the table.

1. 执行SHOW CREATE TABLE <table name to clone>：这将为Create Table您提供要克隆的表的语法
2. CREATE TABLE通过更改表名来克隆表来运行查询。

This will create exact replica of the table which you want to clone along with indexes. The only thing which you then need is to rename the indexes (if required).

这将创建要与索引一起克隆的表的精确副本。然后您唯一需要的是重命名索引（如果需要）。

回答by Alessandro

The better way to duplicate a table is using only DDLstatement. In this way, independently from the number of records in the table, you can perform the duplication instantly.

复制表的更好方法是使用 onlyDDL语句。这样，独立于表中的记录数，您可以立即执行复制。

My purpose is:

我的目的是：

DROP TABLE IF EXISTS table_name_OLD;
CREATE TABLE table_name_NEW LIKE table_name;
RENAME TABLE table_name TO table_name_OLD;
RENAME TABLE table_name _NEW TO table_name;

This avoids the INSERT AS SELECTstatement that, in case of table with a lot of records can take time to be executed.

这避免了INSERT AS SELECT在表有很多记录的情况下执行需要时间的语句。

I suggest also to create a PLSQL procedure as the following example:

我还建议创建一个 PLSQL 过程，如下例所示：

DELIMITER //
CREATE PROCEDURE backup_table(tbl_name varchar(255))
BEGIN
  -- DROP TABLE IF EXISTS GLS_DEVICES_OLD;
  SET @query = concat('DROP TABLE IF EXISTS ',tbl_name,'_OLD');
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

  -- CREATE TABLE GLS_DEVICES_NEW LIKE GLS_DEVICES;
  SET @query = concat('CREATE TABLE ',tbl_name,'_NEW LIKE ',tbl_name);
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

  -- RENAME TABLE GLS_DEVICES TO GLS_DEVICES_OLD;
  SET @query = concat('RENAME TABLE ',tbl_name,' TO ',tbl_name,'_OLD');
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;  

  --  RENAME TABLE GLS_DEVICES_NEW TO GLS_DEVICES;
  SET @query = concat('RENAME TABLE ',tbl_name,'_NEW TO ',tbl_name);
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt; 
END//
DELIMITER ;

Have a nice day! Alex

祝你今天过得愉快！亚历克斯

回答by Nae

Expanding on this answerone could use a stored procedure:

扩展这个答案可以使用存储过程：

CALL duplicate_table('tableName');

Which will result in a duplicate table called tableName_20181022235959If called when

这将导致一个重复的表，称为tableName_20181022235959If 调用时

SELECT NOW();

results:

结果：

2018-10-22 23:59:59

---

---

Implementation

执行

DELIMITER $$
CREATE PROCEDURE duplicate_table(IN tableName VARCHAR(255))
  BEGIN
    DECLARE schemaName VARCHAR(255) DEFAULT SCHEMA();
    DECLARE today VARCHAR(14) DEFAULT REPLACE(REPLACE(REPLACE(NOW(), '-', ''), ' ', ''), ':', ''); -- update @ year 10000
    DECLARE backupTableName VARCHAR(255) DEFAULT CONCAT(tableName, '_', today);

    IF fn_table_exists(schemaName, tableName)
      THEN
        CALL statement(CONCAT('CREATE TABLE IF NOT EXISTS ', backupTableName,' LIKE ', tableName));
        CALL statement(CONCAT('INSERT INTO ', backupTableName,' SELECT * FROM ', tableName));
        CALL statement(CONCAT('CHECKSUM TABLE ', backupTableName,', ', tableName));
      ELSE
        SELECT CONCAT('ERROR: Table "', tableName, '" does not exist in the schema "', schemaName, '".') AS ErrorMessage;
      END IF;
  END $$
DELIMITER ;

---

---

DELIMITER $$
CREATE FUNCTION fn_table_exists(schemaName VARCHAR(255), tableName VARCHAR(255))
  RETURNS TINYINT(1)
  BEGIN
    DECLARE totalTablesCount INT DEFAULT (
      SELECT COUNT(*)
      FROM information_schema.TABLES
      WHERE (TABLE_SCHEMA COLLATE utf8_general_ci = schemaName COLLATE utf8_general_ci)
        AND (TABLE_NAME COLLATE utf8_general_ci = tableName COLLATE utf8_general_ci)
    );
    RETURN IF(
      totalTablesCount > 0,
      TRUE,
      FALSE
    );
  END $$
DELIMITER ;

---

---

DELIMITER $$
CREATE PROCEDURE statement(IN dynamic_statement TEXT)
  BEGIN
      SET @dynamic_statement := dynamic_statement;
      PREPARE prepared_statement FROM @dynamic_statement;
      EXECUTE prepared_statement;
      DEALLOCATE PREPARE prepared_statement;
  END $$
DELIMITER ;

回答by V? Minh

After I tried the solution above, I come up with my own way.

在我尝试了上面的解决方案之后，我想出了我自己的方法。

My solution a little manual and needs DBMS.

我的解决方案有点手册，需要 DBMS。

First, export the data.

首先，导出数据。

Second, open the export data.

二、打开导出数据。

Third, replace old table name with new table name.

第三，用新表名替换旧表名。

Fourth, change all the trigger name in the data (I use MySQL and it show error when I don't change trigger name).

第四，修改数据中的所有触发器名称（我使用MySQL，不更改触发器名称时会显示错误）。

Fifth, import your edited SQL data to the database.

第五，将您编辑的 SQL 数据导入数据库。

回答by Crazy_DT0

Try this :

尝试这个 ：

`CREATE TABLE new-table (id INT(11) auto_increment primary key) SELECT old-table.name, old-table.group, old-table.floor, old-table.age from old-table;`

I selected 4 columns from old-table and made a new table.

我从旧表中选择了 4 列并制作了一个新表。

回答by Krishneil

FOR MySQL

对于 MySQL

CREATE TABLE newtable LIKE oldtable ; 
INSERT newtable SELECT * FROM oldtable ;

FOR MSSQL Use MyDatabase:

对于 MSSQL 使用MyDatabase：

Select * into newCustomersTable  from oldCustomersTable;

This SQL is used for copying tables, here the contents of oldCustomersTable will be copied to newCustomersTable.
Make sure the newCustomersTabledoes notexist in the database.

此 SQL 用于复制表，这里将 oldCustomersTable 的内容复制到newCustomersTable.
确保数据库newCustomersTable中不存在 。