技术分享 | 关于 MySQL 自增 ID 的事儿

当我们使用 MySQL 进行数据存储时，一般会为一张表设置一个自增主键，当有数据行插入时，该主键字段则会根据步长与偏移量增长（默认每次+1）。

站在用户的角度思考问题，与客户深入沟通，找到温县网站设计与温县网站推广的解决方案，凭借多年的经验，让设计与互联网技术结合，创造个性化、用户体验好的作品，建站类型包括：网站设计、成都网站建设、企业官网、英文网站、手机端网站、网站推广、申请域名、虚拟空间、企业邮箱。业务覆盖温县地区。

下文以 Innodb 引擎为主进行介绍，使用自增主键的好处有很多，如：索引空间占比小、范围查询与排序都友好、避免像 UUID 这样随机字符串带来的页分裂问题等...

当我们对该表设置了自增主键之后，则会在该表上产生一个计数器，用于为自增列分配 ID 。

自增的值并不是保存在表结构信息内的，对于不同的版本它们有如下的区别：

计数器的值存储在内存中的，重启后丢弃，下一次将读取最大的一个自增ID往后继续发号。

计数器的值将会持久化到磁盘。在每次发号时都将写入 Redolog ，并在每个 Checkpoint 都进行保存，重启时候使用 Redolog 恢复重启之前的值。

可以预先确定插入行数的语句（像简单 insert 的语句包含多个 value 这种情况也是属于简单插入，因为在进行插入时就已经可以确定行数了）

预先不知道要插入的行数的语句（包括 INSERT ... SELECT, REPLACE ... SELECT 和 LOAD DATA 语句，但不包括 plain INSERT ）

如果一个事务正在向表中插入值，则会产生表级的共享锁，以便当前事务插入的行接收连续的主键值。

当处于[ 传统模式 ]与[ 连续模式 ]时，每次访问计数器时都会加上一个名为 AUTO-INC 的表级锁

传统模式：锁只持有到该语句执行结束，注意是语句结束，不是事务结束

连续模式：批量插入时锁持有到该语句执行结束，简单插入时锁持有到申请完自增ID后即释放，不直到语句完成

通过调整 innodb_autoinc_lock_mode 配置项，可以定义 AUTO-INC 锁的模式，不同的模式对应的策略与锁的粒度也将不同。

当使用基于 Binlog 的复制场景时，对于 statement（SBR）同步模式下只有[ 传统模式 ]与[ 连续模式 ]能保证语句的正确性。

基于 row（RBR）行复制的情况下任何配置模式都可以。

执行语句时加 AUTO-INC 表级锁，执行完毕后释放

针对 Bulk Inserts 时才会采用 AUTO-INC 锁，而针对 Simple Inserts 时，则采用了一种新的轻量级的互斥锁来分配 auto_increment 列的值。

该模式下可以保证同一条 insert 语句中新插入的自增 ID 都是连续的，但如果前一个事务 rollback 丢弃了一部分 ID 的话也会存在后续 ID 出现间隔的情况。

来一个分配一个，不会产生 AUTO-INC 表级锁 ，仅仅会锁住分配 ID 的过程。

由于锁的粒度减少，多条语句在插入时进行锁竞争，自增长的值可能不是连续的。

且当 Binlog 模式为 statement（SBR）时自增 ID 不能保证数据的正确性

不一定，业务也不应该过分依赖 MySQL 自增 ID 的连续性，在以下三种情况下，并不能保证自增 ID 的连续性：

假设已存在数据{1,张三}，且张三所属的字段设置了唯一主键

此时再次插入{null,张三}时候，主键冲突插入失败，但表的计数器已由2变成了3

当下次插入{null,李四}的时候最终入库的会变成{3,李四}

在一个事务里进行数据的插入，但最后并没提交，而是执行了 Rollback 。那么计数器已递增的 ID 是不会返还的，而是被直接丢弃。

发生大量插入时可能会出现自增 ID 并不是连续的情况

当我们为表设置了自增主键后，自增 ID 的范围则与主键的数据类型长度相关。

如果没有一张表里没有设置任何主键，则会自动生成一个隐性的6字节的 row_id 作为主键，它的取值范围为 0 到 2^48-1。

row_id 是由一个全局的 dict_sys.row_id 参数进行维护的，所有没有主键的表都会用上它（并不是每一个表单独占一份 row_id list ）

那么针对这两种主键，则会有以下两种情况发生：

当自增 ID 到达上限后，受到主键数据类型的影响，计数器发放的下一个 ID 也是当前这个 Max ID ，当执行语句时则会提示主键冲突。

建议根据业务合理规划，在进行表设计时就选择适合的数据类型。

当然也可以直接选择 Bigint 类型，它的取值范围是无符号情况下：0到 2^64–1（18446744073709551615）

这里并不是指 bigint 类型一定不会用完，毕竟一个有范围的持续增长的值一定会有溢出的时候，只是说一般场景下它都是足够使用的。

当 row_id 使用完后则又会从 0 开始发放，此时新插入的数据将覆盖回 row_id=0 的数据行。

由于它并不产生错误，还会造成数据的覆盖写。所以我们平时还是尽量给表都设置一个合理的主键才是。

在实际业务场景中，ID 常常需要返回给客户端用来进行相关业务操作。

假如我们有个 userinfo?uid=? 的 API 接口，而用户 ID 是自增的，这时会发生什么？

该接口通过简单的尝试就可以暴露出真实的业务用户总数，可以很方便的使用爬虫从1开始递增获取数据信息。

那么有的同学说，我既想使用自增 ID 带来的好处，也不想承受这种比较常见的问题，那该怎么办呢？

在输出或者获取前对指定字段进行可逆的转义操作

优点：实现起来比较简单，无论单体业务或者分布式应用都无需考虑对数据源的解析，只需在客户端实现自己的转义与解析方法即可；

缺点：业务入侵较大，且需要前后端各个合作方确认统一的标准；如果转义方法有调整，变更影响面也会很大；字符串长度会随ID长度而变化，使用空位填充也会特别明显；

优点：由于采用了时间戳进行 ID 生成，该 ID 是有序的，对范围查询与排序都比较友好；

缺点：需要保证发号节点的高可用性；另外由于生成时依赖时间戳，需要考虑时钟回拨与时钟同步的问题；

维护一份 ID 与 hash 的映射字典，它可以存在于客户端本身，也可以依赖其他如 Redis 、ETCD 之类的组件

优点：hash 长度不会随着 ID 长度或值的变化而变化；可以根据已有的 hash code 来造布隆过滤器；

缺点：业务入侵较大，查询时同样需要先根据 hash key 找到对应的 ID 值；需要考虑选择合适的 hash 算法以及解决 hash 冲突或扩容的问题。

mysql怎么把数据库id设为自增

创建数据库是定义id，例如

id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT

这样就设置id为自增行，并为主键

mysql如何设置ID自增

设置自增列

MYSQL的自增列一定要是有索引的列，设置种子值要在表的后面设置

--mysql

-- 设置自增ID从N开始

CREATE TABLE empautoinc(

ID INT PRIMARY KEY AUTO_INCREMENT

) AUTO_INCREMENT = 100 ; --（设置自增ID从100开始）

insert into empautoinc(id) values(null);

Query OK, 1 row affected (0.00 sec)

mysql select * from empautoinc;

+-----+

| ID |

+-----+

| 100 |

+-----+

1 row in set (0.00 sec)

show table status like 'empautoinc'G;

*************************** 1. row ***************************

Name: empautoinc

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 1

Avg_row_length: 16384

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: 101

Create_time: 2016-10-27 01:50:32

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

设置自增列的步长，可以分为全局级别和会话级别

如果是会话级别，那么当用户新建一个会话的时候，那么步长又回到了全局级别，所以mysql的步长跟sqlserver的步长有很大的不同

mysql不能设置为 表级别 的步长

私信666领取资料

如何在MYSQL插数据 ID自增

如何在MYSQL插数据ID自增的方法。

如下参考：

1.在添加字段之前，第一个应该首先检查当前tb1表的结构，如下图所示。

2.实例字段列添加到表，如下所示。

3.再次看表结构和比较之前和之后的情况添加字段，如下图所示。

4.最后，插入新的数据行看到的样子，最后添加自动增长的字段，如下所示。

注意事项：

MySQL使用的SQL语言是访问数据库最常用的标准语言。MySQL软件采用双重许可政策,分为社区版,商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特性，一般中小网站开发选择MySQL作为数据库。

Mysql数据库自增长如何设置？

在MySQL中可通过字段的AUTO_INCREMENT属性来自动生成。

mysql数据库表主键自增长的sql语句

1、不控制主键的起点

create table emb_t_dictBusType

(

emb_c_busTypeID      int not null auto_increment,

emb_c_busTypeEnName  varchar(255) not null,

emb_c_busTypeZhName  varchar(255) not null,

primary key(emb_c_busTypeID)  

)engine=INNODB  default charset=gbk;

2、控制主键的起点

create table emb_t_dictBusType

(

emb_c_busTypeID      int not null auto_increment,

emb_c_busTypeEnName  varchar(255) not null,

emb_c_busTypeZhName  varchar(255) not null,

primary key(emb_c_busTypeID)  

)engine=INNODB auto_increment=1001 default charset=gbk;

mysql的设置主键自增

列的自增属性，一般用来设置整数列根据一定步长逐步增长的值，类似于其他数据库的序列。不过这里的“序列”是基于特定一张表的。关于自增属性的相关特性如下：

1. 控制自增属性性能的变量：innodb_autoinc_lock_mode

innodb_autoinc_lock_mode=0

代表传统模式，也就是说，在对有自增属性的字段插入记录时，会持续持有一个表级别的自增锁，直到语句执行结束为止。比如说下面两条语句，SQL 1 在执行期间，一直持有对表 f1 的表级自增锁，接下来 SQL 2 执行时锁超时。

innodb_autoinc_lock_mode=1

代表连续模式，和传统模式差不多，不同的点在于对于简单的插入语句，比如 SQL 2，只在分配新的 ID 过程中持有一个轻量级的互斥锁（线程级别，而不是事务级别），而不是直到语句结束才释放的表锁。

代表交错模式。这个模式下放弃了自增表锁，产生的值会不连续。不过这是性能最高的模式，多条插入语句可以并发执行。MySQL 8.0 默认就是交错模式。

那针对复制安全来说，以上三种模式，0 和 1 对语句级别安全，也就是产生的二进制日志复制到任何其他机器都数据都一致；2 对于语句级别不安全；三种模式对二进制日志格式为行的都安全。

2. 控制自增属性的步长以及偏移量

一般用在主主复制架构或者多源复制架构里，主动规避主键冲突。

auto_increment_increment 控制步长

auto_increment_offset 控制偏移量

3. 对于要立刻获取插入值的需求

就是说要获取一张表任何时候的最大值，应该时刻执行以下 SQL 3 ，而不是 SQL 2。SQL 2 里的函数 last_insert_id() 只获取上一条语句最开始的 ID，只适合简单的 INSERT。

4. 自增列溢出现象

自增属性的列如果到了此列数据类型的最大值，会发生值溢出。比如变更表 f1 的自增属性列为 tinyint。

网页名称：mysql自增怎么定义 mysql中怎么设置自增
分享路径：http://kswjz.com/article/hgodjp.html