Chinaunix

标题: [代码]MySQL序列解决方案 [打印本页]
作者: mocow    时间: 2015-05-21 13:27
标题: [代码]MySQL序列解决方案
  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;

  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 ;

  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 ;

  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 ;

  52. -- 初始化数据
  53. INSERT INTO sequence VALUES ('SAMPLE', 1, 1);

  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');
复制代码




欢迎光临 Chinaunix (http://bbs.chinaunix.net/) Powered by Discuz! X3.2