有个上线单，期望将id自增列从10000开始，但执行后面这个DDL后`alter table tt AUTO_INCREMENT=10000;`，再insert发现id并没有从10000开始，这是为什么呢？ * 1、数据库没有重启 * 2、期间这个表无其它DDL执行 * 3、数据库版本：5.6.23 如下是具体复现过程： mysql> CREATE TABLE `tt` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `name` varchar(20) NOT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB ; Query OK, 0 rows affected (0.02 sec) mysql> insert into tt (name)values('a'),('b'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from tt; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec) mysql> alter table tt AUTO_INCREMENT=10000; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table tt\G *************************** 1. row *************************** Table: tt Create Table: CREATE TABLE `tt` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) AUTO_INCREMENT的值改成10000后，不久又变回AUTO_INCREMENT=3了。 后面重新再调整时，发现并没有改，过段时间后又还原了。 mysql> alter table tt AUTO_INCREMENT=10000; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table tt\G *************************** 1. row *************************** Table: tt Create Table: CREATE TABLE `tt` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) .... mysql> select * from tt; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec) mysql> show create table tt\G *************************** 1. row *************************** Table: tt Create Table: CREATE TABLE `tt` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 关于auto_inrement的问题，在8.0以前的版，auto_inrement值并没有持久化，只存储在内存中。如果期间mysql重启，会重新生成auto_inrement值。 如：`select max(id) from table for update` 但是，除了重启，主动alter修改，肯定还有其它因素影响表的auto_increment的重新计算。 首先想到的是表的统计数据相关的，会不会触发了表统计信息更新，同时也重新计算了auto_increment值。于是检查配置： mysql> show variables like '%stats%'; +--------------------------------------+---------------+ | Variable_name | Value | +--------------------------------------+---------------+ | innodb_stats_auto_recalc | ON | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | OFF | | innodb_stats_persistent | ON | | innodb_stats_persistent_sample_pages | 20 | | innodb_stats_sample_pages | 8 | | innodb_stats_transient_sample_pages | 8 | | myisam_stats_method | nulls_unequal | +--------------------------------------+---------------+ innodb_stats_on_metadata 参数是关着的，innodb_stats_persistent=ON并不会自动更新表统计数据。但还是进行了测试。 修改自增后，然后执行```select * from information_schema.tables;``` 。果然，不需要等待，AUTO_INCREMENT就还原了。 mysql> explain select * from information_schema.tables; +----+-------------+--------+------+---------------+------+---------+------+------+----------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+----------------------------------------+ | 1 | SIMPLE | tables | ALL | NULL | NULL | NULL | NULL | NULL | Open_full_table; Scanned all databases | +----+-------------+--------+------+---------------+------+---------+------+------+----------------------------------------+ 1 row in set (0.02 sec) 虽然不会更新索引统计信息，但是执行都会去open\_full\_table，scanned all databases; 可能是重新load的表，导致了auto_increment的值重新设置。 后面在bugs中也找到了这个问题，在5.6.27以前的版本，会存在这个问题。[bug#77743](https://bugs.mysql.com/bug.php?id=77743) Reloading a table that was evicted while empty caused an AUTO_INCREMENT value to be reset 虽然在5.6.27已经修复了这个问题，但重启MySQL，auto_increment还是会重新设置。直到8.0版本才彻底解决。 后记如何成功设置auto_increment： * 1、执行修改auto_increment后，在自增值没有变回前写入一条数据（在有监控的情况下要反映要快，已知promethuos是会查表的meta数据，有可能会导致reload table） * 2、先手动插入或者更新一条记录且id为10000 （较为可控） * 3、升级MySQL版本到5.6.27以上版本（成本较大，不过建议升级到5.7或者8.0的GA版本）
文章最后更新时间： 2020年05月29日 19:27:12
评论
确认删除评论？
将该评论删除后不可恢复，您确认要删除吗？
（提示：如果您只是不想显示该评论内容，可以选择将其屏蔽，而不必删除。）
发表评论
分类文章统计
Python常见错误（3）
Python基础（10）
Django（5）
Flask（1）
Linux基础（4）
shell（11）
linux排障（4）
Consul（3）
MySQL（63）
ProxySQL（7）
SequoiaDB（2）
TiDB（4）
Redis（2）
oracle（10）
常用软件（2）
硬件排障（2）
HTML（1）
JavaScript（1）
windows（1）
总结（1）
我们的作品（18）
按年文章统计
2013（43）
2014（19）
2015（25）
2016（6）
2017（30）
2018（7）
2019（17）
2020（4）
2021（4）
老版入口
亲，扫我吧！
友情链接
飞哥的：imbusy.me/
冰川的：www.mindg.cn
海洋的：hiaero.net
宏斌的：techindeep.com
若水的：nosa.me
段郎的：sixther.me
肥客联邦：fk68.net
Muchas gracias. ?Como puedo iniciar sesion?
write my essays writemypaper.online
We'd like to invite you for an interview http://www.kobify.com/stmap_19zwkycw.html?cialis.symmetrel.septilin.claritin aspirin complex oder ibuprofen But California Governor Jerry Brown intervened on Wednesdayto postpone such a walkout, at least for now, beginning aprocess to potentially impose a 60-day cooling-off period on thebus system. Brown had successfully sought such a period for BARTworkers, but it expired last week and cannot be renewed.
Get a job https://baurzhan.kz/stmap_19zwkycw.html?levitra.atenolol.satibo para que sirve pastillas de escitalopram But this hard won stability still faces challenges. Negotiations with Baghdad over energy rights are ongoing. Tensions in areas that both Kurdistan and the central government of Iraq claim, such as Kirkuk, continue, and violence rages across the border in Syria. While once Iraqis sought safety in Syria, Syria's Kurds now seek safety in Iraq.
I work for a publishers http://www.gregahern.us/stmap_19xorppt.html?viagra.thorazine.cyclogyl.ciloxan methotrexate subcutaneous injection "The government's enthusiastic or energetic pursuit of Bout, a high-priority criminal target, does not demonstrate vindictive, or even inappropriate, government conduct," Circuit Judge Jose Cabranes wrote for a unanimous three-judge panel.
Just over two years http://www.kobify.com/stmap_19zwkycw.html?cialis.symmetrel.septilin.claritin buy zyprexa baikalpharmacy.com Getting his game in shape ahead of the year's last Grand Slam tournament, the top-seeded del Potro solved Isner's big serve and pulled away for a 3-6, 6-1, 6-2 victory in the Citi Open final Sunday, winning his third championship and 14th consecutive match in Washington.
I can't stand football http://www.kobify.com/stmap_19zwkycw.html?levitra.finax.herbolax.decadron doxepin hcl for sleep dosage It says: "We changed with the market, embracing BYOD because we understand that as iOS and Android devices become common in the workplace, businesses still need to manage all of these different platforms seamlessly and securely.
good material thanks https://baurzhan.kz/stmap_54yebbug.html?viagra.avandia.lomefloxacin fougera clindamycin phosphate gel The full-service 27-unit building has a full-time doorman on staff â who should be more than capable of keeping out the rude boys who have been stalking the starâs L.A. compound in recent months.
Yes, I play the guitar http://www.kobify.com/stmap_19zwkycw.html?levitra.glyburide.plendil.duloxetine lamictal sleepiness Japan's handset makers have been struggling to compete withglobal smartphone leaders Apple and Samsung. DoCoMo, the onlyone of Japan's three mobile carriers not to offer Apple'siPhone, remains the Japanese manufacturers' best customer, andwhile industry executives do not expect a DoCoMo-iPhone deal inthe near future, one is expected eventually.
A jiffy bag https://baurzhan.kz/stmap_19zwkycw.html?zestoretic.viagra.sinemet.malegra orlistat hexal 60 mg hartkapseln 84 st preisvergleich "We truly have an increased focus on sustainable lunar activity [but] new lunar exploration missions should aim for a sustained presence on the moon, and not just be a repeat of NASA's Apollo lunar landings," she was cited as saying by Space.com.