<bdo id='ykUAM'></bdo><ul id='ykUAM'></ul>

      1. <i id='ykUAM'><tr id='ykUAM'><dt id='ykUAM'><q id='ykUAM'><span id='ykUAM'><b id='ykUAM'><form id='ykUAM'><ins id='ykUAM'></ins><ul id='ykUAM'></ul><sub id='ykUAM'></sub></form><legend id='ykUAM'></legend><bdo id='ykUAM'><pre id='ykUAM'><center id='ykUAM'></center></pre></bdo></b><th id='ykUAM'></th></span></q></dt></tr></i><div id='ykUAM'><tfoot id='ykUAM'></tfoot><dl id='ykUAM'><fieldset id='ykUAM'></fieldset></dl></div>
      2. <tfoot id='ykUAM'></tfoot>
        <legend id='ykUAM'><style id='ykUAM'><dir id='ykUAM'><q id='ykUAM'></q></dir></style></legend>

        <small id='ykUAM'></small><noframes id='ykUAM'>

      3. 为什么 MySQL 自动增量会在插入失败时增加?

        时间:2023-05-24
        <legend id='v0BWn'><style id='v0BWn'><dir id='v0BWn'><q id='v0BWn'></q></dir></style></legend>
        <i id='v0BWn'><tr id='v0BWn'><dt id='v0BWn'><q id='v0BWn'><span id='v0BWn'><b id='v0BWn'><form id='v0BWn'><ins id='v0BWn'></ins><ul id='v0BWn'></ul><sub id='v0BWn'></sub></form><legend id='v0BWn'></legend><bdo id='v0BWn'><pre id='v0BWn'><center id='v0BWn'></center></pre></bdo></b><th id='v0BWn'></th></span></q></dt></tr></i><div id='v0BWn'><tfoot id='v0BWn'></tfoot><dl id='v0BWn'><fieldset id='v0BWn'></fieldset></dl></div>

            <bdo id='v0BWn'></bdo><ul id='v0BWn'></ul>
          • <tfoot id='v0BWn'></tfoot>

            <small id='v0BWn'></small><noframes id='v0BWn'>

                    <tbody id='v0BWn'></tbody>
                  本文介绍了为什么 MySQL 自动增量会在插入失败时增加?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  限时送ChatGPT账号..

                  一位同事刚刚让我意识到 MySQL 的一个非常奇怪的行为.

                  A co-worker just made me aware of a very strange MySQL behavior.

                  假设您有一个带有 auto_increment 字段和另一个设置为唯一字段的表(例如用户名字段).当尝试使用表中已有的用户名插入一行时,插入失败,正如预期的那样.然而,当您在多次尝试失败后插入有效的新条目时,可以看出 auto_increment 值会增加.

                  Assuming you have a table with an auto_increment field and another field that is set to unique (e.g. a username-field). When trying to insert a row with a username thats already in the table the insert fails, as expected. Yet the auto_increment value is increased as can be seen when you insert a valid new entry after several failed attempts.

                  例如,当我们的最后一个条目看起来像这样......

                  For example, when our last entry looks like this...

                  ID: 10
                  Username: myname
                  

                  ...我们在下一次插入时尝试五个具有相同用户名值的新条目,我们将创建一个新行,如下所示:

                  ...and we try five new entries with the same username value on our next insert we will have created a new row like so:

                  ID: 16
                  Username: mynewname
                  

                  虽然这本身并不是什么大问题,但正如 MySQL 参考手册所述:

                  While this is not a big problem in itself it seems like a very silly attack vector to kill a table by flooding it with failed insert requests, as the MySQL Reference Manual states:

                  如果 [...] 值变得大于可以存储在指定整数类型中的最大整数,则未定义自动递增机制的行为."

                  "The behavior of the auto-increment mechanism is not defined if [...] the value becomes bigger than the maximum integer that can be stored in the specified integer type."

                  这是预期的行为吗?

                  推荐答案

                  InnoDB 是一个事务引擎.

                  这意味着在以下场景中:

                  This means that in the following scenario:

                  1. Session A 插入记录 1
                  2. Session B 插入记录 2
                  3. Session A 回滚
                  1. Session A inserts record 1
                  2. Session B inserts record 2
                  3. Session A rolls back

                  ,要么存在间隙,要么 session B 会锁定,直到 session A 提交或回滚.

                  , there is either a possibility of a gap or session B would lock until the session A committed or rolled back.

                  InnoDB 设计者(与大多数其他事务引擎设计者一样)选择允许间隙.

                  InnoDB designers (as most of the other transactional engine designers) chose to allow gaps.

                  来自文档:

                  当访问自增计数器时,InnoDB 使用一个特殊的表级AUTO-INC 锁,它一直保持到当前SQL 语句,不会到事务结束.引入了特殊的锁释放策略以提高插入到包含 AUTO_INCREMENT

                  When accessing the auto-increment counter, InnoDB uses a special table-level AUTO-INC lock that it keeps to the end of the current SQL statement, not to the end of the transaction. The special lock release strategy was introduced to improve concurrency for inserts into a table containing an AUTO_INCREMENT column

                  InnoDB 就会使用内存中自动递增计数器.当服务器停止并重新启动时,InnoDB 为第一个 INSERT 表重新初始化每个表的计数器,如前所述.

                  InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT to the table, as described earlier.

                  如果您害怕 id 列环绕,请将其设为 BIGINT(8 字节长).

                  If you are afraid of the id column wrapping around, make it BIGINT (8-byte long).

                  这篇关于为什么 MySQL 自动增量会在插入失败时增加?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                1. <legend id='IvHys'><style id='IvHys'><dir id='IvHys'><q id='IvHys'></q></dir></style></legend>
                2. <i id='IvHys'><tr id='IvHys'><dt id='IvHys'><q id='IvHys'><span id='IvHys'><b id='IvHys'><form id='IvHys'><ins id='IvHys'></ins><ul id='IvHys'></ul><sub id='IvHys'></sub></form><legend id='IvHys'></legend><bdo id='IvHys'><pre id='IvHys'><center id='IvHys'></center></pre></bdo></b><th id='IvHys'></th></span></q></dt></tr></i><div id='IvHys'><tfoot id='IvHys'></tfoot><dl id='IvHys'><fieldset id='IvHys'></fieldset></dl></div>
                    <tbody id='IvHys'></tbody>

                    • <bdo id='IvHys'></bdo><ul id='IvHys'></ul>

                    • <tfoot id='IvHys'></tfoot>
                    • <small id='IvHys'></small><noframes id='IvHys'>