转自:http://foolraty.iteye.com/blog/777215
http://www.blogjava.net/Skynet/archive/2011/03/23/301847.html
http://sulong.me/2011/11/14/spring_mysql_sequence
MySQL This function has none of DETERMINISTIC, NO SQL...错误1418 的原因分析及解决方法
http://blog.csdn.net/ty_soft/article/details/6940190
一、 单独的数据表+函数:
如果你不想使用mysql的自动递增,但又想实现主键序列号的功能,可以使用下面的方法,通过函数用一张表去维护生成多个表的序列号,简单又实用
1.创建生成多个表的序列号的数据维护表
CREATE TABLE seq (
name varchar(20) NOT NULL,
val int(10) UNSIGNED NOT NULL,
PRIMARY KEY (name)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
2.插入几条初始化数据
INSERT INTO seq VALUES('one',100);
INSERT INTO seq VALUES('two',1000);
3.创建函数以生成序列号
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
)
二、触发器
原理是在建立一个触发器TRIGGERtri_NewBH 在table插入时执行序列计算
mysql> CREATE TABLE tb(BH CHAR(16),content VARCHAR(20),`date` DATETIME,val INT);
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql>
mysql> DELIMITER $$
mysql> DROP TRIGGER IF EXISTS tri_NewBH $$
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> CREATE TRIGGER tri_NewBH BEFORE INSERT ON tb
-> FOR EACH ROW
-> BEGIN
-> DECLARE dt CHAR(8);
-> DECLARE bh_id CHAR(16);
-> DECLARE number INT;
-> DECLARE new_bh VARCHAR(16);
->
-> SET dt = DATE_FORMAT(CURDATE(),'%Y%m%d');
->
-> SELECT
-> MAX(BH) INTO bh_id
-> FROM tb
-> WHERE BH LIKE CONCAT(dt,'%');
->
-> IF bh_id = '' OR bh_id IS NULL THEN
-> SET new_bh = CONCAT(dt,'00000001');
-> ELSE
-> SET number = RIGHT(bh_id,8) + 1;
-> SET new_bh = RIGHT(CONCAT('00000000',number),8);
-> SET new_bh=CONCAT(dt,new_bh);
-> END IF;
->
-> SET NEW.BH = new_bh;
-> END$$
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql> DELIMITER ;
mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO tb(content,`date`,val) VALUES('LiangCK','2009-05-11',20);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tb;
+------------------+---------+---------------------+------+
| BH | content | date | val |
+------------------+---------+---------------------+------+
| 2009051100000001 | LiangCK | 2009-05-11 00:00:00 | 20 |
| 2009051100000002 | LiangCK | 2009-05-11 00:00:00 | 20 |
| 2009051100000003 | LiangCK | 2009-05-11 00:00:00 | 20 |
| 2009051100000004 | LiangCK | 2009-05-11 00:00:00 | 20 |
| 2011051200000001 | LiangCK | 2009-05-11 00:00:00 | 20 |
| 2011051200000002 | LiangCK | 2009-05-11 00:00:00 | 20 |
| 2011051200000003 | LiangCK | 2009-05-11 00:00:00 | 20 |
+------------------+---------+---------------------+------+
7 rows in set (0.00 sec)
三、下面就是另外一个的实现方案:
原理是创建一个专门记录序列的表sequence,记录有当前序列号,序列的间隔如+1
Sql代码
DROPTABLEIF EXISTSsequence;/*创建记录当前序列的表*/
CREATETABLEsequence(
nameVARCHAR(50)NOTNULL,
current_value INTNOTNULL,
increment INTNOTNULLDEFAULT1,
PRIMARYKEY(name)
) ENGINE=InnoDB;
INSERTINTOsequenceVALUES('MovieSeq',3,5);
DROPFUNCTIONIF EXISTS currval;
DELIMITER $/*创建一个获取当前序列的function*/
CREATEFUNCTIONcurrval (seq_nameVARCHAR(50))
RETURNSINTEGER
CONTAINSSQL
BEGIN
DECLAREvalueINTEGER;
SETvalue = 0;
SELECTcurrent_valueINTOvalue
FROMsequence
WHEREname= seq_name;
RETURNvalue;
END$
DELIMITER ;
测试一下结果:
Sql代码
mysql>SELECTcurrval('MovieSeq');
+---------------------+
| currval('MovieSeq') |
+---------------------+
| 3 |
+---------------------+
1 row inset(0.00 sec)
mysql> SELECTcurrval('x');
+--------------+
| currval('x') |
+--------------+
| 0 |
+--------------+
1 row inset, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------+
| Level| Code | Message |
+---------+------+------------------+
| Warning | 1329 | NodatatoFETCH|
+---------+------+------------------+
1 row inset(0.00 sec)
nextval
//获取下一个数值..先在
sequence里面调用update当前最大数值+1然后再调用
currval获得当前数值
Sql代码
DROPFUNCTIONIF EXISTS nextval;
DELIMITER $
CREATEFUNCTIONnextval (seq_nameVARCHAR(50))
RETURNSINTEGER
CONTAINSSQL
BEGIN
UPDATEsequence
SETcurrent_value = current_value + increment
WHEREname= seq_name;
RETURNcurrval(seq_name);
END$
DELIMITER ;
mysql> select nextval('MovieSeq');
Sql代码
+---------------------+
| nextval('MovieSeq') |
+---------------------+
| 15 |
+---------------------+
1 row inset(0.09 sec)
mysql> selectnextval('MovieSeq');
+---------------------+
| nextval('MovieSeq') |
+---------------------+
| 20 |
+---------------------+
1 row inset(0.01 sec)
mysql> selectnextval('MovieSeq');
+---------------------+
| nextval('MovieSeq') |
+---------------------+
| 25 |
+---------------------+
1 row inset(0.00 sec)
setval
Sql代码
DROPFUNCTIONIF EXISTS setval;
DELIMITER $
CREATEFUNCTIONsetval (seq_nameVARCHAR(50), valueINTEGER)
RETURNSINTEGER
CONTAINSSQL
BEGIN
UPDATEsequence
SETcurrent_value = value
WHEREname= seq_name;
RETURNcurrval(seq_name);
END$
DELIMITER ;
mysql> select setval('MovieSeq',150);
Sql代码
+------------------------+
| setval('MovieSeq',150) |
+------------------------+
| 150 |
+------------------------+
1 row inset(0.06 sec)
mysql> selectcurval('MovieSeq');
+---------------------+
| currval('MovieSeq') |
+---------------------+
| 150 |
+---------------------+
1 row inset(0.00 sec)
mysql> selectnextval('MovieSeq');
+---------------------+
| nextval('MovieSeq') |
+---------------------+
| 155 |
+---------------------+
1 row inset(0.00 sec)