RT,这个存储过程的目的是,用数据库DB1的数据来对比数据库DB2的数据(里面的表结构什么的都一样),把多出来的数据条数找出来,然后给主键(只有一个INT类型的主键)加上一个偏移量,然后把这些数据添加到数据库DB3中(表也是一样的)。现在编译通过了 但是运行的时候遇到一个错误
“You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1”不解DELIMITER $$
DROP PROCEDURE IF EXISTS Pro_Compare_Tab$$
CREATE PROCEDURE Pro_Compare_Tab(IN v_DB1 VARCHAR(45), #一号数据库
IN v_DB2 VARCHAR(45), #二号数据库
IN v_DB3 VARCHAR(45), #三号数据库
IN v_Table VARCHAR(45), #3个数据库中都有的表名
IN nOffset INT) #主键偏移量
BEGIN
DECLARE v_KEYS VARCHAR(80);
DECLARE v_sql VARCHAR(1000);
DECLARE v_tmp VARCHAR(100);
DECLARE v_tmp2 VARCHAR(100);
DECLARE v_SPACE VARCHAR(45);
DECLARE v_PKeyExist INT DEFAULT 0;
DECLARE n_flag,nNumKeys,n_Index INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE cur_pkey CURSOR FOR SELECT
c.TABLE_NAME,MAX(c.COLUMN_NAME) 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_SCHEMA = v_DB1
AND t.TABLE_NAME = v_Table
AND t.CONSTRAINT_TYPE = 'PRIMARY KEY'
GROUP BY c.TABLE_NAME
HAVING MAX(c.ORDINAL_POSITION) = 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur_pkey;
SET n_Index = 1;
LOOP_FLAG:LOOP
FETCH cur_pkey INTO v_SPACE,v_KEYS;
IF done = 1 THEN LEAVE LOOP_FLAG;
END IF;
SET v_PKeyExist = 1;
IF n_Index = 1 THEN
SET @v_tmp = CONCAT('A.',v_KEYS,' = B.',v_KEYS);
SET @v_tmp2 = CONCAT('A.',v_KEYS,' <> B.',v_KEYS);
ELSE
SET @v_tmp = CONCAT(v_tmp,'AND A.',v_KEYS,' = B.',v_KEYS);
SET @v_tmp2 = CONCAT(v_tmp2,' OR A.',v_KEYS,' <> B.',v_KEYS);
END IF;
SET n_Index = n_Index + 1;
END LOOP;
CLOSE cur_pkey;
IF v_PKeyExist <> 0 THEN
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;
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_DB2,'.',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,'_Back A SET ',v_KEYS,' = ',v_KEYS,' + ',V_OFFSET,' WHERE A.',v_KEYS,' < ',V_OFFSET);
PREPARE stml FROM @v_sql;
EXECUTE stml;
COMMIT;
SET @v_sql =CONCAT( 'INSERT INTO ',v_DB3,'.',v_Table,'A SELECT * FROM ',v_DB1,'.',v_Table,'_Back B WHERE ',v_tmp2);
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 IF;
END$$
DELIMITER ;
“You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1”不解DELIMITER $$
DROP PROCEDURE IF EXISTS Pro_Compare_Tab$$
CREATE PROCEDURE Pro_Compare_Tab(IN v_DB1 VARCHAR(45), #一号数据库
IN v_DB2 VARCHAR(45), #二号数据库
IN v_DB3 VARCHAR(45), #三号数据库
IN v_Table VARCHAR(45), #3个数据库中都有的表名
IN nOffset INT) #主键偏移量
BEGIN
DECLARE v_KEYS VARCHAR(80);
DECLARE v_sql VARCHAR(1000);
DECLARE v_tmp VARCHAR(100);
DECLARE v_tmp2 VARCHAR(100);
DECLARE v_SPACE VARCHAR(45);
DECLARE v_PKeyExist INT DEFAULT 0;
DECLARE n_flag,nNumKeys,n_Index INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE cur_pkey CURSOR FOR SELECT
c.TABLE_NAME,MAX(c.COLUMN_NAME) 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_SCHEMA = v_DB1
AND t.TABLE_NAME = v_Table
AND t.CONSTRAINT_TYPE = 'PRIMARY KEY'
GROUP BY c.TABLE_NAME
HAVING MAX(c.ORDINAL_POSITION) = 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur_pkey;
SET n_Index = 1;
LOOP_FLAG:LOOP
FETCH cur_pkey INTO v_SPACE,v_KEYS;
IF done = 1 THEN LEAVE LOOP_FLAG;
END IF;
SET v_PKeyExist = 1;
IF n_Index = 1 THEN
SET @v_tmp = CONCAT('A.',v_KEYS,' = B.',v_KEYS);
SET @v_tmp2 = CONCAT('A.',v_KEYS,' <> B.',v_KEYS);
ELSE
SET @v_tmp = CONCAT(v_tmp,'AND A.',v_KEYS,' = B.',v_KEYS);
SET @v_tmp2 = CONCAT(v_tmp2,' OR A.',v_KEYS,' <> B.',v_KEYS);
END IF;
SET n_Index = n_Index + 1;
END LOOP;
CLOSE cur_pkey;
IF v_PKeyExist <> 0 THEN
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;
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_DB2,'.',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,'_Back A SET ',v_KEYS,' = ',v_KEYS,' + ',V_OFFSET,' WHERE A.',v_KEYS,' < ',V_OFFSET);
PREPARE stml FROM @v_sql;
EXECUTE stml;
COMMIT;
SET @v_sql =CONCAT( 'INSERT INTO ',v_DB3,'.',v_Table,'A SELECT * FROM ',v_DB1,'.',v_Table,'_Back B WHERE ',v_tmp2);
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 IF;
END$$
DELIMITER ;
解决方案 »
- 这个存储过程到底哪里错啦?
- READS SQL DATA 是什么意思??
- 请教几个与MySQL相关驱动有关的问题
- 已经有sql文件,如何在mysql中生成数据库?
- 检索数据排列问题
- 坛上所有的中文问题的贴子都看完了,还是不解决问题:(((
- MS sql server7.0 完整企业版 和 sql server2000 简体中文版
- You have an error in your SQL syntax; check the manual that corresponds to y
- 设置sql_mode无效果
- ERROR 1410 (42000): You are not allowed to create a user with GRANT
- [mysql]求个sql语句
- 初学sql,在mysql里写sql发现没有代码对齐跟自动补全的功能
SELECT 变量名
OR
将中间结果插入到临时表中保存,检查结果
嗯 好像发现问题了,但是不解为什么会这样:SET @v_tmp = CONCAT('A.',v_KEYS,' = B.',v_KEYS);
SET @v_tmp2 = CONCAT('A.',v_KEYS,' <> B.',v_KEYS);我插入到临时表 发现 v_tmp和v_tmp2都是为null,v_KEYS也不为空呀,
这怎么回事呢?
select @v_sql;看一下实际执行的语句是什么,应该是你生成的SQL语句不正确。