mysql创建序列

男娘i 2022-06-03 00:35 236阅读 0赞

提到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中的序列。   
  
先建一个序列表，如下：

CREATE TABLE IF NOT EXISTS \`sequence\` (

\`name\` varchar(50) NOT NULL,    
    
  \`current\_value\` int(11) NOT NULL,    
    
  \`increment\` int(11) NOT NULL DEFAULT '1'    
    
) ENGINE=MyISAM DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY\_KEY\_WRITE=1 ROW\_FORMAT=DYNAMIC COMMENT='序列表，命名s\_\[table\_name\]';    
    
INSERT INTO \`sequence\` (\`name\`, \`current\_value\`, \`increment\`) VALUES    
    
('s\_blog\_account', 0, 1)   
  
  
  
  
DROP FUNCTION IF EXISTS \`currval\`;    
    
DELIMITER //    
    
CREATE  FUNCTION \`currval\`(seq\_name VARCHAR(50)) RETURNS int(11)    
    
    READS SQL DATA    
    
    DETERMINISTIC    
    
BEGIN    
    
DECLARE VALUE INTEGER;    
    
SET VALUE = 0;    
    
SELECT current\_value INTO VALUE FROM sequence WHERE NAME = seq\_name;    
    
RETURN VALUE;    
    
END//    
    
DELIMITER ;  
  
  
  
  
  
  
DROP FUNCTION IF EXISTS \`nextval\`;    
    
DELIMITER //    
    
CREATE  FUNCTION \`nextval\`(seq\_name VARCHAR(50)) RETURNS int(11)    
    
    DETERMINISTIC    
    
BEGIN    
    
UPDATE sequence SET current\_value = current\_value + increment WHERE NAME = seq\_name;    
    
RETURN currval(seq\_name);    
    
END//

DELIMITER ;

最后使用select nextval("s\_blog\_account")即可得到下一个值

第一步：创建--Sequence 管理表

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;   
   
第二步：创建--取当前值的函数  
DROP FUNCTION IF EXISTS currval;   
DELIMITER $   
CREATE FUNCTION currval (seq\_name VARCHAR(50))   
     RETURNS INTEGER  
     LANGUAGE SQL   
     DETERMINISTIC   
     CONTAINS SQL   
     SQL SECURITY DEFINER   
     COMMENT ''  
BEGIN  
     DECLARE value INTEGER;   
     SET value = 0;   
     SELECT current\_value INTO value   
          FROM sequence  
          WHERE name = seq\_name;   
     RETURN value;   
END  
$   
DELIMITER ;

第三步：创建--取下一个值的函数

DROP FUNCTION IF EXISTS nextval;   
DELIMITER $   
CREATE FUNCTION nextval (seq\_name VARCHAR(50))   
     RETURNS INTEGER  
     LANGUAGE SQL   
     DETERMINISTIC   
     CONTAINS SQL   
     SQL SECURITY DEFINER   
     COMMENT ''  
BEGIN  
     UPDATE sequence  
          SET current\_value = current\_value + increment   
          WHERE name = seq\_name;   
     RETURN currval(seq\_name);   
END  
$   
DELIMITER ;   
   
第四步：创建--更新当前值的函数

DROP FUNCTION IF EXISTS setval;   
DELIMITER $   
CREATE FUNCTION setval (seq\_name VARCHAR(50), value INTEGER)   
     RETURNS INTEGER  
     LANGUAGE SQL   
     DETERMINISTIC   
     CONTAINS SQL   
     SQL SECURITY DEFINER   
     COMMENT ''  
BEGIN  
     UPDATE sequence  
          SET current\_value = value   
          WHERE name = seq\_name;   
     RETURN currval(seq\_name);   
END  
$   
DELIMITER ;