MYSQL 序列使用及实现

MySQL序列是一组整数：1, 2, 3, …，由于一张数据表只能有一个字段自增主键， 如果你想实现其他字段也实现自动增加，就可以使用MySQL序列来实现。

使用AUTO_INCREMENT

CREATE TABLE   test
(
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(15) NOT NULL
) AUTO_INCREMENT = 100;

可在建表时可用“AUTO_INCREMENT=n”选项来指定一个自增的初始值。

或者使用：alter table tbname auto_increment = n ;
设置表tbname的唯一auto_increment字段起始值从x开始，如果此表数据量很多的话，这样执行起来会很慢.

 

AUTO_INCREMENT说明：

（1）如果把一个NULL插入到一个AUTO_INCREMENT数据列里去，MySQL将自动生成下一个序列编号。编号从1开始，并1为基数递增。

（2）把0插入AUTO_INCREMENT数据列的效果与插入NULL值一样。但不建议这样做，还是以插入NULL值为好。

（3）当插入记录时，没有为AUTO_INCREMENT明确指定值，则等同插入NULL值。

（4）当插入记录时，如果为AUTO_INCREMENT数据列明确指定了一个数值，则会出现两种情况，情况一，如果插入的值与已有的编号重复，则会出现出错信息，因为AUTO_INCREMENT数据列的值必须是唯一的；情况二，如果插入的值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递增。也就是说，可以跳过一些编号。

（5）如果用UPDATE命令更新自增列，如果列值与已有的值重复，则会出错。如果大于已有值，则下一个编号从该值开始递增。

获取AUTO_INCREMENT值

select last_insert_id();   获取的当前最大的id值

 

重置序列

如果你删除了数据表中的多条记录，并希望对剩下数据的AUTO_INCREMENT列进行重新排列，那么你可以通过删除自增的列，然后重新添加来实现。 不过该操作要非常小心，如果在删除的同时又有新记录添加，有可能会出现数据混乱。大概有三种方式可以做到：

1、直接重置autoIncrement的值

语法格式：ALTER TABLE table_name AUTO_INCREMENT = 1;

2、通过truncate table 完成

TRUNCATE TABLE table_name;

truncate table 不仅是删除表里面的数据，而且还会清空表里面主键的标识。也就是说使用过truncate table 的表在重新写入数据的时候，标识符会从0或1重新开始（看你设置的种子号）。

delete * from就是仅仅能删除数据，不能清空标识。不过delete * from可以后面加Where truncate table却不能加Where。。

3、删除表，然后重建表

DROP TABLE table_name;

CREATE TABLE table_name { ... };

4、删除主键，再添加主键和序列

 ALTER  TABLE  table_name  MODIFY  id  int; //去掉auto_increment

 ALTER  TABLE  table_name  DROP  PRIMARY   KEY ;

 

ALTER  TABLE  table_name  ADD  PRIMARY  KEY(id); //添加主键

ALTER  TABLE  table_name  MODIFY  id   int  AUTO_INCREMENT;//加入自动增加

或者直接删除主键id字段

alter table insect drop id;

alter table insect  add id int unsigned not null auto_increment first add primary key(id);

truncate table 和delete * from的区别

1、DROP删表，表结构将删了，当然数据也不存在了
2、TRUNCATE和DELETE删数据，表结构还在
3、DELETE可以带条件删除，TRUNCATE是全部删除
4、DELETE删除会写日志，TRUNCATE不写
5、DELETE效率低，数据可以恢复，TRUNCATE效率高，数据不可恢复

mysql 实现序列

1、oracle和mysql相比真的是一个天一个地，mysql没有序列，只有主键自增，如果需要自定义主键含义则需要实现序列。

2、思路很简单，创建函数 通过查询出当前值+步长跟新到原来的当前值，实现每一次查询都有自增后的值。

3、如果考虑并发脏数据，需要进行加锁控制

并发生成方式：

sequence的并发生成方式同非并发生成方式都需要建立sequence表，如下：

一：创建SEQUENCE表：

DROP TABLE
    IF EXISTS sequence;
CREATE TABLE
    sequence
    (
        name VARCHAR(50) NOT NULL,
        current_value BIGINT NOT NULL,
        increment INT NOT NULL DEFAULT 1,
        PRIMARY KEY (name)
    )
    ENGINE=InnoDB;

插入定义的序列：

INSERT INTO sequence VALUES ('SEQ_TRZ_MEMBER_NO',10000000000,1);

二：自定义函数实现：

DROP FUNCTION IF EXISTS seq;  
DELIMITER $$  
CREATE FUNCTION seq(seq_name char (20)) returns BIGINT
BEGIN
 UPDATE sequence SET current_value=last_insert_id(current_value+increment) WHERE name=seq_name;
 RETURN last_insert_id();
END $$
DELIMITER;

验证方法SELECT NEXTVAL('SEQ_TRZ_MEMBER_NO');

 

函数内部调用了MySQL内部提供的last_insert_id（）函数完成并发控制。