复制 MySQL 表、索引和数据

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

This is what I've found so far.

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

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

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

create table {new_table} like {old_table};

To copy with indexes and triggers do these 2 queries:

CREATE TABLE newtable LIKE oldtable; 
INSERT 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

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

CREATE TABLE tbl_new AS SELECT * FROM tbl_old;

FOR MySQL

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

FOR MSSQL Use 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 newCustomersTable does not exist in the database.

I found the same situation and the approach which I took was as follows: 1. Execute SHOW CREATE TABLE : This will give you the Create Table syntax for the table which you want to clone 2. Run the CREATE TABLE query by changing the table name to clone the 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).

MySQL Way

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

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

My solution a little manually and need 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)

fifth import your edited sql data to the database.

Go to phpMyAdmin and 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.

Expanding on this answer one could use a stored procedure:

CALL duplicate_table('tableName');

Which will result in a duplicate table called tableName_20181022235959 If called when

SELECT NOW();

results:

2018-10-22 23:59:59

Implementation

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
;

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
;

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
;