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)
分享到:
相关推荐
mysql 数据库自增id 的总结.docx
小A正在balabala写代码呢,DBA小B突然发来了一条消息,“快看看你的用户特定信息表T,里面的主键,也就是自增id,都到16亿了,这才多久,在这样下去过不了多久主键就要超出范围了,插入就会失败,balabala……” ...
MySQL的自增ID(主键) 用完了,怎么办? 如果用 int unsigned (int,4个字节 ), 我们可以算下最大当前声明的自增ID最大是多少,由于这里定义的是 int unsigned,所以最大可以达到2的32幂次方 – 1 = 4294967295。 ...
在mysql中很多朋友都认为字段为AUTO_INCREMENT类型自增ID值是无法修改,其实这样理解是错误的,下面介绍mysql自增ID的起始值修改与设置方法。通常的设置自增字段的方法:创建表格时添加: 代码如下:create table ...
介绍了Java获取最后插入MySQL记录的自增ID值的3种方法,有需要的朋友可以参考需要
由于之前处理过sql server数据库的迁移工作,尝试过其自增列值的变更,但是通过SQL 语句修改自增列值,是严格不允许的,直接报错(无法更新标识列 ‘自增列名称‘)。sql server我测试是2008、2012和2014,都不允许...
在单表/数据库数据量过大,更新量不断飙涨时,MySQL DBA往往会对业务系统提出sharding的方案。既然要sharding,那么不可避免的要讨论到sharding key问题,在有些业务系统中,必须保证sharding key全局唯一,比如存放...
mysql雪花算法生成唯一整型ID主键的实现方法,整型ID作为主键好处有很多,比如节省存储空间、插入和查询排序快、具有一定规律性(时间顺序)等。
项目中经常会用到自增id,比如uid,最简单的方法就是用直接用数据库提供的AUTO_INCREMENT,但是如果用户量非常大,几千万,几亿然后需要分表存储的时候呢,这种方案就搞不定了,所以最好有一个全局的自增ID的生成器...
mysql用触发器实现char类型主键自增长
数据库的Id自增越来越大,要让自增重新从1开始:那么就用下面的方法吧
应尽量避免使用自增IP来做为主键,为数据库分表操作带来极大的不便。 在postgreSQL、oracle、db2数据库中有一个特殊的特性—sequence。 任何时候数据库可以根据当前表中的记录数大小和步长来获取到该表下一条...
在将数据库从MSSQL迁移到MySQL的过程中,基于业务逻辑的要求,需要在MySQL的自增列插入0值。在MSSQL中是这样完成的: 代码如下: string sql;sql = ” set identity_insert dbo.AppUsers on ” + ” insert dbo....
1. MySQL数据库方式Redis Incr方式分配自增ID数据库方式3和Redis Incr分配自增ID的性能对比1. MySQL数据库方式数据库方式优点、
自增字段,一定要设置为primary key. 以指定从1000开始为例。 1 创建表的时候就设置: CREATE TABLE `Test` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `NAME` varchar(50) NOT NULL, `SEX` varchar(2) NOT NULL...
最近老是要为现在这个项目初始化数据,搞的很头疼,而且数据库的Id自增越来越大,要让自增重新从1开始:那么就用下面的方法吧: 方法一:如果曾经的数据都不需要的话,可以直接清空所有数据,并将自增字段恢复从1...
使用此方法得到的是 id最大的值,确为最后一个值,但当多链接线程时,这个最大的id并不一定是我们插入数据的自增id值,因此不适用于多线程。 二,使用函数:msyql_insert_id(); 在PHP中,经常需要把插入数据库中的id...
####Statement和PreparedStatement - PreparedStatement预编译的SQL执行对象 1. 可以避免SQL注入 因为在编译的时候已经把SQL的逻辑固定,不会因为替换进去...- 元数据指 数据库和表的相关信息 - 代码参见 Demo10.java