执行SQL为select next value for MYCATSEQ_TEST_SEQ
获取自增ID时,报了上面的错误,其中创建的函数为
CREATE DEFINER=
root@
%FUNCTION
mycat_seq_currval(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf8 DETERMINISTIC BEGIN DECLARE retval VARCHAR(64); SET retval="-999999999,null"; SELECT concat(CAST(current_value AS CHAR),",",CAST(increment AS CHAR)) INTO retval FROM MYCAT_SEQUENCE WHERE name = seq_name; RETURN retval; END
CREATE DEFINER=`root`@`%` FUNCTION `mycat_seq_nextval`(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf8
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = current_value + increment WHERE name = seq_name;
RETURN mycat_seq_currval(seq_name);
END
CREATE DEFINER=`root`@`%` FUNCTION `mycat_seq_setval`(seq_name VARCHAR(50),value INTEGER) RETURNS varchar(64) CHARSET utf8
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = value
WHERE name = seq_name;
RETURN mycat_seq_currval(seq_name);
END
可以看到,用户都为root,而在mycat的schema.xml配置文件中,配置的datahost节点的用户为up,这两个用户不一致,导致空指针,修改如下即可
在mysql命令行创建函数时,需要先修改分隔符delimiter $$
,然后执行创建函数的sql就行。