转自: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 )
二、触发器
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
- DROP TABLE IF EXISTS sequence;/*创建记录当前序列的表*/
- CREATE TABLE sequence (
- name VARCHAR(50) NOT NULL,
- current_value INT NOT NULL,
- increment INT NOT NULL DEFAULT 1,
- PRIMARY KEY (name)
- ) ENGINE=InnoDB;
- INSERT INTO sequence VALUES ('MovieSeq',3,5);
- DROP FUNCTION IF EXISTS currval;
- DELIMITER $/*创建一个获取当前序列的function*/
- CREATE FUNCTION currval (seq_name VARCHAR(50))
- RETURNS INTEGER
- CONTAINS SQL
- BEGIN
- DECLARE value INTEGER;
- SET value = 0;
- SELECT current_value INTO value
- FROM sequence
- WHERE name = seq_name;
- RETURN value;
- END$
- DELIMITER ;
测试一下结果:
- mysql> SELECT currval('MovieSeq');
- +---------------------+
- | currval('MovieSeq') |
- +---------------------+
- | 3 |
- +---------------------+
- 1 row in set (0.00 sec)
- mysql> SELECT currval('x');
- +--------------+
- | currval('x') |
- +--------------+
- | 0 |
- +--------------+
- 1 row in set, 1 warning (0.00 sec)
- mysql> show warnings;
- +---------+------+------------------+
- | Level | Code | Message |
- +---------+------+------------------+
- | Warning | 1329 | No data to FETCH |
- +---------+------+------------------+
- 1 row in set (0.00 sec)
nextval
- +------------------------+
- | setval('MovieSeq',150) |
- +------------------------+
- | 150 |
- +------------------------+
- 1 row in set (0.06 sec)
- mysql> select curval('MovieSeq');
- +---------------------+
- | currval('MovieSeq') |
- +---------------------+
- | 150 |
- +---------------------+
- 1 row in set (0.00 sec)
- mysql> select nextval('MovieSeq');
- +---------------------+
- | nextval('MovieSeq') |
- +---------------------+
- | 155 |
- +---------------------+
- 1 row in set (0.00 sec)