今天写的一个COPY一个库的表结构到另外一个表的SP。
DELIMITER $$
DROP PROCEDURE IF EXISTS `test_copy`.`sp_copy_db_schema`$$
CREATE PROCEDURE `test_copy`.`sp_copy_db_schema`(IN db_from varchar(64),IN db_to varchar(64))
BEGIN
declare i int default 0;
declare cnt int default 0;
-- Get the columns number
select count(1) from information_schema.tables where table_schema = db_from into cnt;
-- Loop table name one time
while i < cnt do
-- Danymic query
set @stmt1 = concat('select table_name from information_schema.tables where table_schema = ''',db_from,''' limit ',i,
',1 into @tb_name;');
prepare s1 from @stmt1;
execute s1;
deallocate prepare s1;
set @stmt1 = '';
-- Begin copy schema to new database
set @stmt2 = concat('create table if not exists ',db_to,'.',@tb_name,' select * from ',db_from,'.',@tb_name,' where 1 != 1');
prepare s2 from @stmt2;
execute s2;
deallocate prepare s2;
set @stmt2 = '';
-- Ready for the next table
set i = i + 1;
end while;
END$$
DELIMITER ;测试：
原库：TEST
目标库：TEST_COPY
----------------
mysql> show tables from test \G
*************************** 1. row ***************************
Tables_in_test: a
*************************** 2. row ***************************
Tables_in_test: animals
*************************** 3. row ***************************
Tables_in_test: b
*************************** 4. row ***************************
Tables_in_test: cubeexample
*************************** 5. row ***************************
Tables_in_test: employees
*************************** 6. row ***************************
Tables_in_test: eugene
*************************** 7. row ***************************
Tables_in_test: ewp
*************************** 8. row ***************************
Tables_in_test: info
*************************** 9. row ***************************
Tables_in_test: ip
*************************** 10. row ***************************
Tables_in_test: lk
*************************** 11. row ***************************
Tables_in_test: lk2
*************************** 12. row ***************************
Tables_in_test: lk3
*************************** 13. row ***************************
Tables_in_test: members
*************************** 14. row ***************************
Tables_in_test: mocha_document_content
*************************** 15. row ***************************
Tables_in_test: no_part_tab
*************************** 16. row ***************************
Tables_in_test: os_propertyentry
*************************** 17. row ***************************
Tables_in_test: part_tab
*************************** 18. row ***************************
Tables_in_test: person
*************************** 19. row ***************************
Tables_in_test: rooms
*************************** 20. row ***************************
Tables_in_test: score
*************************** 21. row ***************************
Tables_in_test: shirt
*************************** 22. row ***************************
Tables_in_test: song
*************************** 23. row ***************************
Tables_in_test: song2
*************************** 24. row ***************************
Tables_in_test: t1
*************************** 25. row ***************************
Tables_in_test: t11
*************************** 26. row ***************************
Tables_in_test: t2
*************************** 27. row ***************************
Tables_in_test: table1
*************************** 28. row ***************************
Tables_in_test: table2
*************************** 29. row ***************************
Tables_in_test: table_a
*************************** 30. row ***************************
Tables_in_test: temp
*************************** 31. row ***************************
Tables_in_test: temp2
*************************** 32. row ***************************
Tables_in_test: test1
*************************** 33. row ***************************
Tables_in_test: total_user
33 rows in set (0.01 sec)
---------------------
mysql> show tables from test_copy \G
Empty set (0.00 sec)
-----
mysql> call sp_copy_db_schema('test','test_copy');
Query OK, 0 rows affected (13.97 sec)--------------
mysql> show tables from test_copy \G
*************************** 1. row ***************************
Tables_in_test_copy: a
*************************** 2. row ***************************
Tables_in_test_copy: animals
*************************** 3. row ***************************
Tables_in_test_copy: b
*************************** 4. row ***************************
Tables_in_test_copy: cubeexample
*************************** 5. row ***************************
Tables_in_test_copy: employees
*************************** 6. row ***************************
Tables_in_test_copy: eugene
*************************** 7. row ***************************
Tables_in_test_copy: ewp
*************************** 8. row ***************************
Tables_in_test_copy: info
*************************** 9. row ***************************
Tables_in_test_copy: ip
*************************** 10. row ***************************
Tables_in_test_copy: lk
*************************** 11. row ***************************
Tables_in_test_copy: lk2
*************************** 12. row ***************************
Tables_in_test_copy: lk3
*************************** 13. row ***************************
Tables_in_test_copy: members
*************************** 14. row ***************************
Tables_in_test_copy: mocha_document_content
*************************** 15. row ***************************
Tables_in_test_copy: no_part_tab
*************************** 16. row ***************************
Tables_in_test_copy: os_propertyentry
*************************** 17. row ***************************
Tables_in_test_copy: part_tab
*************************** 18. row ***************************
Tables_in_test_copy: person
*************************** 19. row ***************************
Tables_in_test_copy: rooms
*************************** 20. row ***************************
Tables_in_test_copy: score
*************************** 21. row ***************************
Tables_in_test_copy: shirt
*************************** 22. row ***************************
Tables_in_test_copy: song
*************************** 23. row ***************************
Tables_in_test_copy: song2
*************************** 24. row ***************************
Tables_in_test_copy: t1
*************************** 25. row ***************************
Tables_in_test_copy: t11
*************************** 26. row ***************************
Tables_in_test_copy: t2
*************************** 27. row ***************************
Tables_in_test_copy: table1
*************************** 28. row ***************************
Tables_in_test_copy: table2
*************************** 29. row ***************************
Tables_in_test_copy: table_a
*************************** 30. row ***************************
Tables_in_test_copy: temp
*************************** 31. row ***************************
Tables_in_test_copy: temp2
*************************** 32. row ***************************
Tables_in_test_copy: test1
*************************** 33. row ***************************
Tables_in_test_copy: total_user
33 rows in set (0.00 sec)
