java复制mysql表数据_复制MySQL表，索引和数据

扩展this answer可以使用存储过程：

CALL duplicate_table('tableName');

这将导致重复的表名为 tableName_20181022235959 如果调用时

SELECT NOW();

结果：

2018-10-22 23:59:59

实施

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

;