组长说想对比数据库A和B,B里面有我们新增的一些数据项,想让我把B中的所有变化整合到A当中去,(数据库A和B里面表结构神马都是一样的)所以我想第一个游标循环是这样的,找出所有共有的表DECLARE cur_Param CURSOR FOR select t.table_name from information_schema.TABLES t where t.table_schema = 'mmo_db' or t.table_schema = 'mmo_db_107g' group by t.table_name having count(1) = 2;然后再在这个循环中用每个表的表名找到该表中的所有主键 然后拼接sql字符串来执行,其中嵌套的另一个游标循环是这样的DECLARE cur_Param2 CURSOR FOR SELECT distinct t.TABLE_NAME, c.COLUMN_NAME, c.ORDINAL_POSITION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t, INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c WHERE t.TABLE_NAME = c.TABLE_NAME and t.table_name = 'items' AND t.TABLE_SCHEMA = 'mmo_db' AND t.CONSTRAINT_TYPE = 'PRIMARY KEY'; 但是好像mysql不让这样做 求更好的方法
把 DECLARE cur_Param2 放在前面紧跟DECLARE cur_Param CURS 就行了。
以前用的oracle,对mysql不熟,弱弱的问一下,第一个循环用的退出条件:DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;应该是只截取当前的游标状态吧,这样的话,当第二个退出的时候,第一个和第二个循环的退出就无法分辨了,是这样么?
DELIMITER $$ CREATE PROCEDURE ca() BEGIN DECLARE done INT DEFAULT 0; DECLARE cur_Param CURSOR FOR SELECT t.table_name FROM information_schema.TABLES t WHERE t.table_schema = 'mmo_db' OR t.table_schema = 'mmo_db_107g' GROUP BY t.table_name HAVING COUNT(1) = 2; DECLARE cur_Param2 CURSOR FOR SELECT DISTINCT t.TABLE_NAME, c.COLUMN_NAME, c.ORDINAL_POSITION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t, INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c WHERE t.TABLE_NAME = c.TABLE_NAME AND t.table_name = 'items' AND t.TABLE_SCHEMA = 'mmo_db' AND t.CONSTRAINT_TYPE = 'PRIMARY KEY'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;你的语句,注意在每个WHERE有的SQL语句后加上 SET done = 0 在第2个游标结束时,也要加上SET done = 0END$$ DELIMITER ;
是不是DECLARE语句都要写在最上面和最前面呢,我的第二个游标循环用的where参数里面有一个必须是第一个游标里面拿出来的内容,所以必须写在第一个循环开始使用之后,但是就报错了,好像第二个游标的声明有问题,全部代码在这里: DELIMITER $$ DROP PROCEDURE IF EXISTS Pro_Merage_Data$$ CREATE PROCEDURE Pro_Merage_Data(IN v_DB1 VARCHAR(45), IN v_DB2 VARCHAR(45), IN v_DB3 VARCHAR(45), IN nOffset INT) BEGIN DECLARE v_KEYS VARCHAR(80); DECLARE v_sql VARCHAR(1000); DECLARE v_tmp VARCHAR(100); DECLARE n_flag,nNumKeys,n_Index INT DEFAULT 0; DECLARE done INT DEFAULT 0; DECLARE v_Table VARCHAR(45); DECLARE cur_Param CURSOR FOR SELECT t.table_name FROM information_schema.TABLES t WHERE t.table_schema = v_DB1 OR t.table_schema = v_DB2 OR t.table_schema = v_DB3 GROUP BY t.table_name HAVING COUNT(1) = 3; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur_Param; FETCH cur_Param INTO v_Table; WHILE done=0 DO SET @v_sql = CONCAT('SELECT 1 into @aa FROM information_schema.TABLES where table_schema = \'',v_DB1,'\' and table_name = \'',v_Table,'_Back\''); PREPARE stml FROM @v_sql; EXECUTE stml; DECLARE cur_pkey CURSOR FOR SELECT DISTINCT c.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t, INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c WHERE t.TABLE_NAME = c.TABLE_NAME AND t.table_name = v_Table AND t.TABLE_SCHEMA = v_DB1 AND t.CONSTRAINT_TYPE = 'PRIMARY KEY'; OPEN cur_pkey; FETCH cur_pkey INTO v_KEYS; SET n_Index = 1; REPEAT IF n_Index = 1 THEN SET @v_tmp = CONCAT('A.',v_KEYS,' = B.',v_KEYS); ELSE SET @v_tmp = CONCAT(v_tmp,',A.',v_KEYS,' = B.',v_KEYS); n_Index = n_Index + 1; UNTIL done = 1 END REPEAT; CLOSE cur_pkey; SET done = 0; IF @aa IS NOT NULL THEN SET @v_sql = CONCAT('TRUNCATE TABLE ',v_DB1,'.',v_Table,'_Back'); PREPARE stml FROM @v_sql; EXECUTE stml; SET @v_sql =CONCAT('INSERT INTO ',v_DB1,'.',v_Table,'_Back SELECT * FROM ',v_DB1,'.',v_Table, ' A LEFT JOIN ',v_DB1,'.',v_Table,' B on ',v_tmp,' WHERE b.',v_KEYS,' IS NULL;'); PREPARE stml FROM @v_sql; EXECUTE stml; COMMIT; ELSE SET @v_sql =CONCAT( 'CREATE TABLE ',v_DB1,'.',v_Table,'_Back SELECT A.* FROM ',v_DB1,'.',v_Table, ' A LEFT JOIN ',v_DB2,'.',v_Table,' B ON ',v_tmp,' WHERE B.',v_KEYS,' IS NULL;'); PREPARE stml FROM @v_sql; EXECUTE stml; COMMIT; END IF; SET @v_sql =CONCAT( 'UPDATE ',v_DB1,'.',v_Table,' A SET ',v_KEYS,' = ',v_KEYS,' + ',V_OFFSET,' WHERE A.',v_KEYS, ' IN (SELECT ',v_KEYS,' FROM ',v_DB1,'.',v_Table,'_Back) AND A.',v_KEYS,' < ',V_OFFSET); PREPARE stml FROM @v_sql; EXECUTE stml; COMMIT; SET @v_sql =CONCAT( 'DROP TABLE ',v_DB1,'.',v_Table,'_Back'); PREPARE stml FROM @v_sql; EXECUTE stml; COMMIT; END WHILE; CLOSE cur_Param; END$$ DELIMITER ;
是不是DECLARE语句都要写在最上面和最前面呢,: 是 可以用间接方法。声明第2 个游标 select * from tt动态生成这个TT表
组长说想对比数据库A和B,B里面有我们新增的一些数据项,想让我把B中的所有变化整合到A当中去,(数据库A和B里面表结构神马都是一样的)所以我想第一个游标循环是这样的,找出所有共有的表DECLARE cur_Param CURSOR FOR select t.table_name from information_schema.TABLES t where t.table_schema = 'mmo_db' or t.table_schema = 'mmo_db_107g' group by t.table_name having count(1) = 2;然后再在这个循环中用每个表的表名找到该表中的所有主键 然后拼接sql字符串来执行,其中嵌套的另一个游标循环是这样的DECLARE cur_Param2 CURSOR FOR SELECT distinct
t.TABLE_NAME,
c.COLUMN_NAME,
c.ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c
WHERE
t.TABLE_NAME = c.TABLE_NAME
and t.table_name = 'items'
AND t.TABLE_SCHEMA = 'mmo_db'
AND t.CONSTRAINT_TYPE = 'PRIMARY KEY';
但是好像mysql不让这样做 求更好的方法
以前用的oracle,对mysql不熟,弱弱的问一下,第一个循环用的退出条件:DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;应该是只截取当前的游标状态吧,这样的话,当第二个退出的时候,第一个和第二个循环的退出就无法分辨了,是这样么?
CREATE PROCEDURE ca()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE cur_Param CURSOR FOR SELECT t.table_name FROM information_schema.TABLES t WHERE t.table_schema = 'mmo_db' OR t.table_schema = 'mmo_db_107g' GROUP BY t.table_name HAVING COUNT(1) = 2;
DECLARE cur_Param2 CURSOR FOR SELECT DISTINCT
t.TABLE_NAME,
c.COLUMN_NAME,
c.ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c
WHERE
t.TABLE_NAME = c.TABLE_NAME
AND t.table_name = 'items'
AND t.TABLE_SCHEMA = 'mmo_db'
AND t.CONSTRAINT_TYPE = 'PRIMARY KEY';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;你的语句,注意在每个WHERE有的SQL语句后加上
SET done = 0
在第2个游标结束时,也要加上SET done = 0END$$
DELIMITER ;
是不是DECLARE语句都要写在最上面和最前面呢,我的第二个游标循环用的where参数里面有一个必须是第一个游标里面拿出来的内容,所以必须写在第一个循环开始使用之后,但是就报错了,好像第二个游标的声明有问题,全部代码在这里:
DELIMITER $$
DROP PROCEDURE IF EXISTS Pro_Merage_Data$$
CREATE PROCEDURE Pro_Merage_Data(IN v_DB1 VARCHAR(45),
IN v_DB2 VARCHAR(45),
IN v_DB3 VARCHAR(45),
IN nOffset INT)
BEGIN
DECLARE v_KEYS VARCHAR(80);
DECLARE v_sql VARCHAR(1000);
DECLARE v_tmp VARCHAR(100);
DECLARE n_flag,nNumKeys,n_Index INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE v_Table VARCHAR(45);
DECLARE cur_Param CURSOR FOR
SELECT t.table_name FROM information_schema.TABLES t
WHERE t.table_schema = v_DB1 OR t.table_schema = v_DB2 OR t.table_schema = v_DB3
GROUP BY t.table_name HAVING COUNT(1) = 3;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur_Param;
FETCH cur_Param INTO v_Table;
WHILE done=0 DO
SET @v_sql = CONCAT('SELECT 1 into @aa FROM information_schema.TABLES where table_schema = \'',v_DB1,'\' and table_name = \'',v_Table,'_Back\'');
PREPARE stml FROM @v_sql;
EXECUTE stml;
DECLARE cur_pkey CURSOR FOR SELECT DISTINCT
c.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c
WHERE
t.TABLE_NAME = c.TABLE_NAME
AND t.table_name = v_Table
AND t.TABLE_SCHEMA = v_DB1
AND t.CONSTRAINT_TYPE = 'PRIMARY KEY';
OPEN cur_pkey;
FETCH cur_pkey INTO v_KEYS;
SET n_Index = 1;
REPEAT
IF n_Index = 1 THEN
SET @v_tmp = CONCAT('A.',v_KEYS,' = B.',v_KEYS);
ELSE
SET @v_tmp = CONCAT(v_tmp,',A.',v_KEYS,' = B.',v_KEYS);
n_Index = n_Index + 1;
UNTIL done = 1
END REPEAT;
CLOSE cur_pkey;
SET done = 0;
IF @aa IS NOT NULL THEN
SET @v_sql = CONCAT('TRUNCATE TABLE ',v_DB1,'.',v_Table,'_Back');
PREPARE stml FROM @v_sql;
EXECUTE stml;
SET @v_sql =CONCAT('INSERT INTO ',v_DB1,'.',v_Table,'_Back SELECT * FROM ',v_DB1,'.',v_Table,
' A LEFT JOIN ',v_DB1,'.',v_Table,' B on ',v_tmp,' WHERE b.',v_KEYS,' IS NULL;');
PREPARE stml FROM @v_sql;
EXECUTE stml;
COMMIT;
ELSE
SET @v_sql =CONCAT( 'CREATE TABLE ',v_DB1,'.',v_Table,'_Back SELECT A.* FROM ',v_DB1,'.',v_Table,
' A LEFT JOIN ',v_DB2,'.',v_Table,' B ON ',v_tmp,' WHERE B.',v_KEYS,' IS NULL;');
PREPARE stml FROM @v_sql;
EXECUTE stml;
COMMIT;
END IF;
SET @v_sql =CONCAT( 'UPDATE ',v_DB1,'.',v_Table,' A SET ',v_KEYS,' = ',v_KEYS,' + ',V_OFFSET,' WHERE A.',v_KEYS,
' IN (SELECT ',v_KEYS,' FROM ',v_DB1,'.',v_Table,'_Back) AND A.',v_KEYS,' < ',V_OFFSET);
PREPARE stml FROM @v_sql;
EXECUTE stml;
COMMIT;
SET @v_sql =CONCAT( 'DROP TABLE ',v_DB1,'.',v_Table,'_Back');
PREPARE stml FROM @v_sql;
EXECUTE stml;
COMMIT;
END WHILE;
CLOSE cur_Param;
END$$
DELIMITER ;
是
可以用间接方法。声明第2 个游标
select * from tt动态生成这个TT表