[代码]MySQL序列解决方案

mocow

1. -- 定义序列表
   
2. DROP TABLE IF EXISTS sequence;
   
3. CREATE TABLE sequence (
   
4.     name VARCHAR(50) NOT NULL,
   
5.     current_value INT NOT NULL,
   
6.     increment INT NOT NULL DEFAULT 1,
   
7.     PRIMARY KEY (name)
   
8. ) ENGINE=InnoDB;
   
9. 
   
10. -- 获取当前序列号
    
11. DROP FUNCTION IF EXISTS currval;
    
12. DELIMITER $
    
13. CREATE FUNCTION currval (seq_name VARCHAR(50))
    
14. RETURNS INTEGER
    
15. CONTAINS SQL
    
16. BEGIN
    
17.     DECLARE value INTEGER;
    
18.     SET value = 0;
    
19.     SELECT current_value INTO value
    
20.     FROM sequence
    
21.     WHERE name = seq_name;
    
22.     RETURN value;
    
23. END$
    
24. DELIMITER ;
    
25. 
    
26. -- 获取下一个序列号
    
27. DROP FUNCTION IF EXISTS nextval;
    
28. DELIMITER $
    
29. CREATE FUNCTION nextval (seq_name VARCHAR(50))
    
30. RETURNS INTEGER
    
31. CONTAINS SQL
    
32. BEGIN
    
33.     UPDATE sequence SET current_value = current_value + increment
    
34.     WHERE name = seq_name;
    
35.     RETURN currval(seq_name);
    
36. END$
    
37. DELIMITER ;
    
38. 
    
39. -- 重设序列号
    
40. DROP FUNCTION IF EXISTS setval;
    
41. DELIMITER $
    
42. CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER)
    
43. RETURNS INTEGER
    
44. CONTAINS SQL
    
45. BEGIN
    
46.     UPDATE sequence SET current_value = value
    
47.     WHERE name = seq_name;
    
48.     RETURN currval(seq_name);
    
49.     END$
    
50. DELIMITER ;
    
51. 
    
52. -- 初始化数据
    
53. INSERT INTO sequence VALUES ('SAMPLE', 1, 1);
    
54. 
    
55. -- 测试
    
56. SELECT currval('SAMPLE');
    
57. SELECT nextval('SAMPLE');
    
58. SELECT nextval('SAMPLE');
    
59. SELECT setval('SAMPLE',150);
    
60. SELECT currval('SAMPLE');
    
61. SELECT nextval('SAMPLE');
    
62. SELECT nextval('SAMPLE');

复制代码