匿名用户没有存储过程授权,
会报错 execute command denied to user ''@'localhost' for routine
需要建立用户并授权
1:建立用户
CREATE USER app IDENTIFIED BY 'abc';
2:授权用户
GRANT ALL PRIVILEGES ON test.* TO app@localhost identified by "abc";
3:可能用到了
grant execute on procedure test.seq to 'app'@'localhost';
匿名用户登陆不让用存储过程
如果你不想使用mysql的自动递增,但又想实现主键序列号的功能,可以使用下面的方法,通过函数用一张表去维护生成多个表的序列号,简单又实用
1.创建生成多个表的序列号的数据维护表
CREATE TABLE seq (
name varchar(20) NOT NULL,
val int(10) UNSIGNED NOT NULL,
PRIMARY KEY (name)
) ENGINE=MyISAM DEFAULT CHARSET=UTF8
2.插入几条初始化数据
INSERT INTO seq VALUES('one',100);
INSERT INTO seq VALUES('two',1000);
3.创建函数以生成序列号
DELIMITER //
CREATE FUNCTION seq(seq_name char (20)) returns int
begin
UPDATE seq SET val=last_insert_id(val+1) WHERE name=seq_name;
RETURN last_insert_id();
end
//
4.测试
- mysql> SELECT seq('one'),seq('two'),seq('one'),seq('one');
- +------------+------------+------------+------------+
- | seq('one') | seq('two') | seq('one') | seq('one') |
- +------------+------------+------------+------------+
- | 102 | 1002 | 103 | 104 |
- +------------+------------+------------+------------+
- 1 row IN SET (0.00 sec)