mysql 数据库自增id 的总结

mysql数据库使用auto_increment 字段来辅助为自增列赋值。

 

SHOW VARIABLES LIKE 'auto_incre%';

 

 输出

 

auto_increment_increment 1
auto_increment_offset 1

 

auto_increment_increment  = 1 ，每次插入数据，id++；auto_increment_offset =1 ，说明从1开始。

一般情况下auto_increment_increment=1,auto_increment_offset=1.是默认初始值。

我们可以在my.cnf文件中进行重新指定。auto_increment满足一个数学当纳法推导公

式:

value=auto_increment_increment*N+auto_increment_offset

 

表中有auto_increment列，innodb用一种锁策略来保正这个auto_increment列值。

如果我们创建一个表，指定了auto_increment表。那么innodb会维护一个auto_increment

计数器。

 

当访问这个计数器，innodb会使用表级锁来锁定这访问过程，这个发生在分析auto_increment

值的过程中，而不是在一个执行事务中。也就是说：这个访问发生在事务执行前面。所以和这个锁

不是发生整个事务过程中，仅仅是分析语句判断出auto_increment这一过程中.

 

auto_increment计数器，一直维护在内存中，当server重启或是停止后重启，innodb会为每个表

初始化这个计数器.

 

 

对一个含有自增列（通常为id）的表执行

 

mysql> SHOW CREATE TABLE table_name;

 

 可以得到类似输出：

 

CREATE TABLE `news` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`url` varchar(255) NOT NULL,
`pub_date` datetime NOT NULL,
`site` tinyint(4) NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime DEFAULT NULL,
`org` varchar(255) DEFAULT NULL,
`author` varchar(255) DEFAULT NULL,
`trend` tinyint(4) DEFAULT '-1',
`md5url` char(32) NOT NULL,
`content` longtext NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `md5url` (`md5url`),
KEY `pub_date` (`pub_date`),
KEY `created_at` (`created_at`),
KEY `title` (`title`)
) ENGINE=InnoDB AUTO_INCREMENT=704677 DEFAULT CHARSET=utf8

 

 

其中该状态下，表中AUTO_INCREMENT 字段值为704677 ，说明下一个插入的id 为704677 ，同时插入成功后，AUTO_INCREMENT=704678.

 

mysql 数据库中的id设为自增，容易产生id不连续的问题。有时将一个表中的所有数据清除，但是在插入数据时，id还是在未清除前的基础上累加。

要解决的问题：

1. 删除数据，同时要求再次插入数据时，id从1 开始累计

（1） TRUNCATE TABLE table_name;

（2） DELETE FROM table_name;    

 ALTER TABLE table_name auto_increment =1 ;

对上面两种方法进行一下分析。Truncate table 表名 速度快，而且效率高，因为：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

　　TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

2. 查看当前状态下一个表的auto_increment 值：

 

SHOW TABLE STATUS LIKE 'table_name'

输出：

Name: ddx
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 2449
Avg_row_length: 93
Data_length: 229376
Max_data_length: 0
Index_length: 114688
Data_free: 0
Auto_increment: 2353
Create_time: 2012-05-02 12:54:55
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:

  可以查看该表当前的Auto_increment的值。

 

3. 对有些失败插入操作，如果不是语法错误，比如重复的唯一键值，也会造成自增id的不连续。

4. 5.1.22之前，innodb使用一个表锁解决自增字段的一致性问题（内部是用一个计数器维护，每次自增时要加表锁），如果一行一行的插入数据则没有什么问题，但是如果大量的并发插入就废了，表锁会引起SQL堵塞，不但影响效率，而且可能会瞬间达到max_connections而崩溃。在 5.1.22之后，innodb使用新的方式解决自增字段一致性问题，对于可以预判行数的insert语句，innodb使用一个轻量级的互斥量。如：某一insert语句1执行前，表的AUTO_INCREMENT=1，语句1的插入行数已知为3，innodb在语句1的实际插入操作执行前就预分配给该语句三个自增值，当有一个新的insert语句2要执行时，读取的AUTO_INCREMENT=4，这样虽然语句1可能还没有执行完，语句2就可直接执行无需等待语句2。这种方式对于可预判插入行数的插入语句有效，如：insert和replace。对于无法提前获知插入行数的语句，如：insert...select...、replace...select...和load

data则innodb还是使用表锁。

insert语句中有时会显示的设置自增字段的值，对于这种情况innodb还是会预分配给语句总行数的自增值而不是只有实际使用系统自增的行。因而有可能会造成自增字段的值不连续。如：

 

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

 c1 为自增字段，实际的AUTO_INCREMENT 会大2。

设置新自增互斥方式：通过配置选项：innodb_autoinc_lock_mode，它是专门用来在使用auto_increment的情况下调整锁策略的，目前有三种选择：
innodb_autoinc_lock_mode = 0 (“traditional” lock mode：全部使用表锁)
innodb_autoinc_lock_mode = 1 (默认)(“consecutive” lock
mode：可预判行数时使用新方式，不可时使用表锁) 
innodb_autoinc_lock_mode = 2 (“interleaved” lock
mode：全部使用新方式，不安全，不适合replication)