在 MySql 中将表和数据从一种模式复制到另一种模式的脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19173431/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Script to copy tables and data from one schema to another in MySql
提问by kasdega
I'm sure there's a way to do this but I haven't been able to put the pieces together.
我确信有一种方法可以做到这一点，但我无法将这些碎片拼凑在一起。
I'd like to:
我想：
select table_name from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'myschema';
and then....
进而....
for each table_name in that list
BEGIN
CREATE table myschemacopy.table_name like myschema.table_name;
INSERT INTO myschemacopy.table_nameSELECT * FROM myschema.table_name;
END LOOP
How can this be done?
如何才能做到这一点？
采纳答案by Luv
Just copy the below Query result and Run it, or you could build dynamic Query.
只需复制下面的查询结果并运行它，或者您可以构建动态查询。
select 'select * into [newschema]. ' + table_name + ' from [myschema].' +table_name
from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = 'myschema'
回答by Krishnamoorthy
If it's on the same server just a different database, you can use
如果它在同一台服务器上只是一个不同的数据库，你可以使用
SOLUTION 1 :
解决方案1：
CREATE TABLE newdb.mynewtable LIKE olddb.myoldtable;
You can then insert data into the new table from the old using
然后，您可以使用旧表将数据插入新表中
INSERT newdb.mynewtable SELECT * FROM olddb.myoldtable;
[or]
SOLUTION 2
解决方案 2
ALTER table olddb.myoldtable rename newdb.mynewtable
回答by A C
It's a little late but having just had to solve the same problem, here's the stored procedure that I came up with, in case it helps someone else:
有点晚了，但刚刚不得不解决同样的问题，这是我想出的存储过程，以防它对其他人有帮助：
CREATE DEFINER=`whoever`@`wherever` PROCEDURE `copy_schema`(
IN in_oldDB varchar(256),
IN in_newDB varchar(256),
IN in_copyData int(1)
)
BEGIN
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_tname varchar(100) DEFAULT "";
DECLARE curTable CURSOR FOR
SELECT `table_name`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = in_oldDB
and TABLE_TYPE='BASE TABLE'
;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
set @result=NULL;
# Create new schema if it doesn't exist
SET @sql = CONCAT('CREATE SCHEMA IF NOT EXISTS ',in_newDB,';');
PREPARE create_schema FROM @sql;
EXECUTE create_schema;
DEALLOCATE PREPARE create_schema;
# Loop over tables in old schema
OPEN curTable;
clone_tables: LOOP
# get next table name
FETCH curTable INTO v_tname;
# Quit if we're done
IF v_finished = 1 THEN LEAVE clone_tables; END IF;
# Clone the table
SET @sql = CONCAT("CREATE TABLE `", in_newDB, "`.`", v_tname, "` LIKE `", in_oldDB, "`.`", v_tname, "`;");
PREPARE clone_table FROM @sql;
EXECUTE clone_table;
DEALLOCATE PREPARE clone_table;
# Optionally copy data
#select v_tname; # This just gives some feedback in workbench for long-running copies
IF (in_copyData > 0) THEN
SET @sql = CONCAT("INSERT INTO `", in_newDB, "`.`", v_tname, "` SELECT * FROM `", in_oldDB, "`.`", v_tname, "`;");
PREPARE clone_data FROM @sql;
EXECUTE clone_data;
DEALLOCATE PREPARE clone_data;
END IF;
# Result message
SET @result = IFNULL(CONCAT(@result,',',v_tname),v_tname);
END LOOP clone_tables;
# Close cursor
CLOSE curTable;
# Print result message
SELECT CONCAT("Copied the following tables from ", in_oldDB, " to ", in_newDB, ": ", @result);
END
