在MySQL中重置AUTO_INCREMENT需要很长时间 [英] Resetting AUTO_INCREMENT is taking a long time in MySQL

问题描述

ALTER TABLE tablename AUTO_INCREMENT = 10000000

此查询需要很长时间才能更新.为什么?我需要优化此查询.

This query is taking long time to update. Why? I need to optimize this query.

推荐答案

ALTER TABLE会导致整个表的重建-如果您的表包含许多行，则可能需要很长时间.

ALTER TABLE causes a rebuild of the entire table - if your table contains many rows,this can take ages.

如果只需要增加auto_increment值的值，最快的方法是插入一个虚拟行(然后在需要时删除该行).这只需要几分之一秒的时间，而大型表的ALTER TABLE可能要花几天的时间.

If you just need to bump up the value of the auto_increment value, the quickest way is to insert a dummy row (and then delete that roow if need be). This will only take a fraction of a second, whereas ALTER TABLE can take days for a large table.

例如，假设我有一个表，其中包含一个auto_increment ID列和其他列col1，col2 ...:

For example, suppose I have a table with an auto_increment ID column and other columns col1, col2...:

insert into autoinc_table set ID = 10000000;
delete from autoinc_table where ID = 10000000;

这篇关于在MySQL中重置AUTO_INCREMENT需要很长时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！