试试看：

`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;`

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

扩展此答案时，可以使用存储过程：

CALL duplicate_table('tableName');

这将导致名为tableName_20181022235959的重复表如果在

SELECT NOW();

结果：

2018-10-22 23:59:59

实施

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 ;

除了上面的解决方案之外，您可以使用AS将其集成到一条线上。

CREATE TABLE tbl_new AS SELECT * FROM tbl_old;

要复制表及其结构，而不从其他数据库复制数据，请使用此选项。关于新的数据库sql类型

创建表格currentdatabase.tablename与旧数据库.tablename相似

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

CREATE TABLE new_table LIKE old_table; 
INSERT INTO new_table SELECT * FROM old_table;

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

CREATE TABLE new_table AS SELECT * FROM old_table;

我以前问过这个问题：

复制包含索引的MySQL表

MySQL方式：

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