我要合并的数据库中有两千多张表,要将它们合并成一张,我的想法是写存储过程,循环从各表中取得数据插入大表,首先取得各个表名,并从中获取数据插入大表
现在我已经能够取得表名了,并且准备使用游标逐个从表中读入(但我现在不知道怎么写这句),因为我获得的只是表名table_name,如果直接select * from table_name会报错"table_name不存在"代码及问题如下:
DELIMITER $$DROP PROCEDURE IF EXISTS `combinetest`.`combine` $$
CREATE PROCEDURE `combinetest`.`combine` ()
BEGIN
DECLARE tname VARCHAR(100);
DECLARE ptname CURSOR
FOR
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema='combinetest' AND table_name LIKE 'ct_ipv4_log_2013_08_17_%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @done=TRUE;
DROP TABLE IF EXISTS combt;
CREATE TABLE combt
(id INT(10) UNSIGNED NOT NULL,
date_time_destroy DATETIME DEFAULT "0000-00-00 00:00:00",
attr_orig_ipv4_dst INT(10) UNSIGNED NOT NULL,
attr_orig_port_dst SMALLINT(5) UNSIGNED NOT NULL);
SET @done=FALSE;
OPEN ptname;
REPEAT
FETCH ptname INTO tname;
INSERT INTO combt SELECT id,date_time_destroy,attr_orig_ipv4_dst,attr_orig_port_dst
FROM tname;
UNTIL @done
END REPEAT;
CLOSE ptname;END $$DELIMITER ;运行时会提示错误Table 'combinetest.tname' doesn't exist。而且我不知道游标的那个限制条件这么写对不对@done不加@会报错。
现在我已经能够取得表名了,并且准备使用游标逐个从表中读入(但我现在不知道怎么写这句),因为我获得的只是表名table_name,如果直接select * from table_name会报错"table_name不存在"代码及问题如下:
DELIMITER $$DROP PROCEDURE IF EXISTS `combinetest`.`combine` $$
CREATE PROCEDURE `combinetest`.`combine` ()
BEGIN
DECLARE tname VARCHAR(100);
DECLARE ptname CURSOR
FOR
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema='combinetest' AND table_name LIKE 'ct_ipv4_log_2013_08_17_%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @done=TRUE;
DROP TABLE IF EXISTS combt;
CREATE TABLE combt
(id INT(10) UNSIGNED NOT NULL,
date_time_destroy DATETIME DEFAULT "0000-00-00 00:00:00",
attr_orig_ipv4_dst INT(10) UNSIGNED NOT NULL,
attr_orig_port_dst SMALLINT(5) UNSIGNED NOT NULL);
SET @done=FALSE;
OPEN ptname;
REPEAT
FETCH ptname INTO tname;
INSERT INTO combt SELECT id,date_time_destroy,attr_orig_ipv4_dst,attr_orig_port_dst
FROM tname;
UNTIL @done
END REPEAT;
CLOSE ptname;END $$DELIMITER ;运行时会提示错误Table 'combinetest.tname' doesn't exist。而且我不知道游标的那个限制条件这么写对不对@done不加@会报错。
mysql -s -L -e "select concat('INSERT INTO combt SELECT id,date_time_destroy,attr_orig_ipv4_dst,attr_orig_port_dst from ',table_name,';') from information_schema.tables WHERE table_schema='combinetest' AND table_name LIKE 'ct_ipv4_log_2013_08_17_%';" > xxxx.sql
chmod +x xxx.sql./xxx.sql
是在DOS下直接执行,不是在 mysql 中