MySQL高级应用--自增长序列

MySQL中不常用但却能在关键时刻帮上大忙的应用有：序列，视图，存储过程等


自增长系列
MYSQL通过AUTO_INCREMENT自增长字段实现插入1条记录进行自动增加1,而这个功能在Oracle中是通过序列(sequence)完成的。
MYSQL自增长“序列”和序列是两回事，MYSQL本身不提供序列机制，它使用AUTO_INCREMENT设置起始值，也能通过修改系统变量auto_increment_increment设置步长，这是一个全局设置。MYSQL一个表只能有一个自增长字段，且只能分配给固定字段，不能被多个表共用，并且只能是数字型，另外自增主键往往是没意义的。


可能需要使用序列的场景：
业务复杂：需要定制和控制主键时（序列可以按需求变化如按照年/月/日生成主键），
希望手工维护自增长，方便数据迁移时，
当事务跨多表，期望事务可靠性时，
需要一个业务上有意义的主键时，比如单据号（若只是一个流水号则自增长更方便），
主键很明确地需要和其他表关联时，
期望主键是唯一的不需要重复利用时
当然序列也有缺点，主要是程序处理麻烦，Oracle自增有专门的缓存与之对应，不用担心效率问题，而MySQL只能通过触发器模拟，会有一些性能损失。


MySQL中的序列：
DROP TABLE IF EXISTS 'sequence';
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)
name是序列名(可理解为维护表的序列名),current_value是序列当前值，increment是每次增长的步长。
写两个函数取当前值和取下一个值：
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;
这样在需要插入时只需用nextval("s_blog_account")的返回值作为主键插入到对应的列表中如INSERT INTO 's_blog_account' (id,name) VALUES (nextval('s_blog_account'),'denglitong');


当对数据进行归档删除后序列可重置，而自增主键是不能重新归零的从而会显得比较离散不够连续，同时between代替limit优化中使用序列就没有自增主键不连续的问题了，即使存在序列不连续也可以进行更新。MYSQL使用自增主键更方便，但由于序列更高的可定制性和可控性仍然推荐使用序列。不过，在Log表和无意义字段中仍推荐使用自增，因为这些字段没有实际意义只作为索引。需要注意的是，不要在一个表的字段上同时使用自增和"序列"。最后一点，对于InnoDB引擎，如使用序列则不推荐使用char等非number型数据做主键，因为这样会明显影响InnoDB的插入性能，应该保证序列是有序的number.