I am inserting say 500 rows using INSERT IGNORE, if the row already exists (based on a unique field) then it simply does nothing, if not it inserts the row.
我使用INSERT IGNORE插入500行，如果该行已经存在（基于唯一字段），那么它根本不执行任何操作，如果不是，则插入该行。
My problem is that say only 10 do NOT exist the auto increment still increases for every insert so it goes up by 500 so I end up with gaps in my id's. How do I stop this?
我的问题是说只有10个不存在，每个插入的自动增量仍然增加，所以它上升了500，所以我最终得到我的id的空白。我怎么阻止这个？
I have tried:
我努力了：
SET global innodb_autoinc_lock_mode = 0;
But I get the error:
但我得到错误：
1238 - Variable 'innodb_autoinc_lock_mode' is a read only variable
1238 - 变量'innodb_autoinc_lock_mode'是只读变量
How do I change this?
我该如何改变？
1 个解决方案
#1
10
According to MySQL's documentation, the innodb_autoinc_lock_mode is not a dynamic variable and, thus cannot be changed while MySQL is running.
根据MySQL的文档，innodb_autoinc_lock_mode不是动态变量，因此在MySQL运行时无法更改。
You'll either have to set it in the command line
您必须在命令行中设置它
--innodb_autoinc_lock_mode=0
or in the configuration file (mysql.ini)
或者在配置文件（mysql.ini）中
innodb_autoinc_lock_mode=0
It should be noted that you should not (read: never) rely on a continuous id sequence. Rollbacked or failed transactions may result in gaps.
应该注意的是，你不应该（读：永远）依赖于连续的id序列。回滚或失败的交易可能会导致差距。
#1
10
According to MySQL's documentation, the innodb_autoinc_lock_mode is not a dynamic variable and, thus cannot be changed while MySQL is running.
根据MySQL的文档，innodb_autoinc_lock_mode不是动态变量，因此在MySQL运行时无法更改。
You'll either have to set it in the command line
您必须在命令行中设置它
--innodb_autoinc_lock_mode=0
or in the configuration file (mysql.ini)
或者在配置文件（mysql.ini）中
innodb_autoinc_lock_mode=0
It should be noted that you should not (read: never) rely on a continuous id sequence. Rollbacked or failed transactions may result in gaps.
应该注意的是，你不应该（读：永远）依赖于连续的id序列。回滚或失败的交易可能会导致差距。