mysql创建序列

提到mysql,我顺便讲讲序列。用过oracle的人都知道,orale没有类似mysql的AUTO_INCREMENT这样的自增长字段,实现插入一条记录,自动增加1.oracle是通过sequence(序列)来完成的。这样看起来,似乎mysql的自增长要比oracle序列的实现更好更方便。那我为什么还要提序列呢?有必要吗?
(1)首先mysql的自增长“序列”和序列是两回事,mysql本身不提供序列机制。

(2)mysql的AUTO_INCREMENT可以设置起始值,但是不能设置步长,其步长默认就是1.

(3)mysql一个表只能有一个自增长字段。自增长只能被分配给固定表的固定的某一字段,不能被多个表共用。并且只能是数字型。

(4)在历史表和数据迁移时,经常会遇到自增主键重复的问题。

(5)自增主键往往是没意义的。
在下列情况下可能你需要使用序列:
(1)业务复杂,你需要高度定制和控制主键(自增主键只能是按数字递增的,但是序列可以随心所欲的变化,比如按照年月日生成主键。。。)
(2)你希望手工维护自增长,方便数据迁移;
(3)当事务跨多表时,期望事务可靠性;

(4)当你需要一个业务上有意义的主键时,比如单据号等(若你需要的只是一个流水号,那么用自增更方便);

(5)当你的主键很明确地需要和其他表关联时(若你的表不与其他表关联时,如日志表,自增更方便)

(6)你期望这个主键是唯一的,不需要重复利用的。
    当然,序列也有缺点,主要就是程序处理麻烦,不如自增方便。oracle的自增有缓存,不用担心效率问题,而mysql只能通过触发器模拟,可能会有性能损失。

下面,我们就来实现一下mysql中的序列。

一般使用序列(Sequence)来处理主键字段,在MySQL中是没有序列的,但是MySQL有提供了自增长(increment)来实现类似的目的,但也只是自增,而不能设置步长、开始索引、是否循环等,最重要的是一张表只能由一个字段使用自增,但有的时候我们需要两个或两个以上的字段实现自增(单表多字段自增),MySQL本身是实现不了的,但我们可以用创建一个序列表,使用函数来获取序列的值

 

1. 新建序列表

  1. drop table if exists sequence;
  2. create table sequence (
  3. seq_name        VARCHAR(50) NOT NULL— 序列名称       
  4. current_val     INT         NOT NULL— 当前值       
  5. increment_val   INT         NOT NULL    DEFAULT 1, — 步长(跨度)       
  6. PRIMARY KEY (seq_name)   );

 

  1. INSERT INTO sequence VALUES (‘seq_test1_num1’‘0’‘1’);
  2. INSERT INTO sequence VALUES (‘seq_test1_num2’‘0’‘2’);

3. 创建 函数 用于获取序列当前值(v_seq_name 参数值 代表序列名称)

  1. create function currval(v_seq_name VARCHAR(50))
  2. returns integer
  3. begin
  4.     declare value integer;
  5.     set value = 0;
  6.     select current_val into value  from sequence where seq_name = v_seq_name;
  7.    return value;
  8. end;

4. 查询当前值

  1. select currval(‘seq_test1_num1’);

5. 创建 函数 用于获取序列下一个值(v_seq_name 参数值 代表序列名称)

  1. create function nextval (v_seq_name VARCHAR(50))
  2.     returns integer
  3. begin
  4.     update sequence set current_val = current_val + increment_val  where seq_name = v_seq_name;
  5.     return currval(v_seq_name);
  6. end;

6. 查询下一个值

  1. select nextval(‘seq_test1_num1’);

7. 新建表          用于测试的表

  1. DROP TABLE IF EXISTS `test1`;
  2. CREATE TABLE `test1` (
  3.   `namevarchar(255) NOT NULL,
  4.   `value` double(255,0) DEFAULT NULL,
  5.   `num1` int(11) DEFAULT NULL,
  6.   `num2` int(11) DEFAULT NULL,
  7.   PRIMARY KEY (`name`)
  8. );

8. 新建触发器 插入新纪录前给自增字段赋值实现字段自增效果

  1. CREATE TRIGGER `TRI_test1_num1` BEFORE INSERT ON `test1` FOR EACH ROW BEGIN
  2. set NEW.num1 = nextval(‘seq_test1_num1’);
  3. set NEW.num2 = nextval(‘seq_test1_num2’);
  4. END

9. 最后测试自增效果

  1. INSERT INTO test1 (name, value) VALUES (‘1’‘111’);
  2. INSERT INTO test1 (name, value) VALUES (‘2’‘222’);
  3. INSERT INTO test1 (name, value) VALUES (‘3’‘333’);
  4. INSERT INTO test1 (name, value) VALUES (‘4’‘444’);

 

10. 结果展示

  1. SELECT * FROM test1;

 

未经允许不得转载:A66网 » mysql创建序列

赞 (0)
分享到:更多 ()

评论 0