MYSQL 序列使用及实现

﹏ヽ暗。殇╰゛Y 2022-05-14 07:08 170阅读 0赞

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（）函数完成并发控制。

[MySQL]: http://lib.csdn.net/base/mysql