CREATE PROCEDURE genid ()
BEGIN DECLARE st VARCHAR(100);
START TRANSACTION;
IF NOT EXISTS (SELECT 1 FROM order_key) THEN
SET @sqlstr=CONCAT('insert into order_key select substring(`key`,8) from order', DATE_FORMAT(CURDATE(), '%Y%m'), ' order by id desc limit 1');
PREPARE st FROM @sqlstr;
EXECUTE st;
ELSE
UPDATE order_key SET id=id+1;
END IF;
SELECT id FROM order_key;
COMMIT;
END
有一个专门生成id的表order_key
初始化的时候是从order201208 就是order+当前月中取key字段的中8个以后的数字 插入表order_key中order201208
id key data
1 x2012081 ds
2 x2012082 24
3 x2012083 14也就是说现在取到的是3 3会被插入到order_key的id字段中。然后被跟新为4上面的存储过程我执行的时候有问题,苦于找不到问题来求助!
详细错误:
查询:create procedure genid () begin START TRANSACTION错误代码: 1064
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 '' at line 3执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0 sec
---------------------------------------------------查询: DECLARE st VARCHAR(1000)错误代码: 1064
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 'DECLARE st VARCHAR(1000)' at line 1执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0 sec
---------------------------------------------------查询: if not exists (select 1 from order_key) then set @sqlstr=concat('insert into order_key select substring(`key`,8) from order', d...错误代码: 1064
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 'if not exists (select 1 from order_key) then
set @sqlstr=concat('insert ' at line 1执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0 sec
---------------------------------------------------共 0 行受到影响执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0 sec
---------------------------------------------------共 0 行受到影响执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0 sec
---------------------------------------------------查询: else update order_key set id=id+1错误代码: 1064
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 'else
update order_key set id=id+1' at line 1执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0 sec
---------------------------------------------------查询: end if错误代码: 1064
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 'end if' at line 1执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0 sec
---------------------------------------------------返回了 0 行
我在mysql5.1下能编译成功,但是在5.5下就不行,还没找到问题原因!
CREATE PROCEDURE genid ()
BEGIN DECLARE st VARCHAR(100);
START TRANSACTION;
IF NOT EXISTS (SELECT 1 FROM order_key) THEN
SET @sqlstr=CONCAT('insert into order_key select substring(`key`,8) from order', DATE_FORMAT(CURDATE(), '%Y%m'), ' order by id desc limit 1');
PREPARE st FROM @sqlstr;
EXECUTE st;
ELSE
UPDATE order_key SET id=id+1;
END IF;
SELECT id FROM order_key;
COMMIT;
END
//
汗 好了
意思是说delimiter是用于要执行大椴sql的标志吗??还是???
CREATE PROCEDURE genid ()
BEGIN DECLARE st VARCHAR(100);
START TRANSACTION;
IF NOT EXISTS (SELECT 1 FROM order_key) THEN
SET @sqlstr=CONCAT('insert into order_key select substring(`key`,8) from order', DATE_FORMAT(CURDATE(), '%Y%m'), ' order by id desc limit 1');
PREPARE st FROM @sqlstr;
EXECUTE st;
ELSE
UPDATE order_key SET id=id+1;
END IF;
SELECT id FROM order_key;
COMMIT;
END
//