V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
okwork
V2EX  ›  MySQL

MySQL 主键自增,会出现跳键,是什么情况?

  •  
  •   okwork · 2018-10-16 17:29:44 +08:00 · 7466 次点击
    这是一个创建于 1990 天前的主题,其中的信息可能已经有所发展或是发生改变。
    一台服务器上的 mysql,就是简单的自增主键,1, 2, 3, 4, 5, 6, ... 这样没有其他的特别设计。结果发现某些地方会跳掉了,比如 1, 2, 3, 4, 6, ... 发现 5 没了,虽然出现的频率不高,几十万条的记录里面,跳了 2~3 条的样子,这是什么情况?
    26 条回复    2019-02-22 18:37:22 +08:00
    realkenshinji
        1
    realkenshinji  
       2018-10-16 17:31:55 +08:00 via iPhone
    被删了?
    eslizn
        2
    eslizn  
       2018-10-16 17:31:56 +08:00   ❤️ 8
    是不是业务里有事务插入后没提交回滚了
    fy1993
        3
    fy1993  
       2018-10-16 17:32:48 +08:00
    赞同 2 楼 事务回滚,主键会跳增
    glacer
        4
    glacer  
       2018-10-16 17:38:55 +08:00
    replace, insert into ... on duplicate key 这类语句都会删行
    xiaoxinshiwo
        5
    xiaoxinshiwo  
       2018-10-16 17:41:25 +08:00
    看了下 mysql 官方文档,还挺复杂
    The exception is for “ mixed-mode inserts ”, where the user provides explicit values for an AUTO_INCREMENT column for some, but not all, rows in a multiple-row “ simple insert ”. For such inserts, InnoDB allocates more auto-increment values than the number of rows to be inserted. However, all values automatically assigned are consecutively generated (and thus higher than) the auto-increment value generated by the most recently executed previous statement. “ Excess ” numbers are lost.
    参考: https://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html
    xiaoxinshiwo
        6
    xiaoxinshiwo  
       2018-10-16 17:43:23 +08:00
    “ 丢失 ”自动递增值和序列间隙

    在所有锁定模式( 0,1 和 2 )中,如果生成自动增量值的事务回滚,则这些自动增量值将“ 丢失 ”。一旦为自动增量列生成了值,就无法回滚它,无论 “ INSERT-like ” 语句是否完成,以及是否回滚包含的事务。这些丢失的值不会被重复使用。因此,存储在 AUTO_INCREMENT 表的列中的值可能存在间隙 。
    est
        7
    est  
       2018-10-16 17:53:32 +08:00
    AUTO_INCREMENT 有预分配的。没用完很正常。
    dengtongcai
        8
    dengtongcai  
       2018-10-16 17:55:55 +08:00
    @glacer replace into 会删行,inster into 不会的吧
    qiyuey
        9
    qiyuey  
       2018-10-16 17:57:02 +08:00
    这个是先申请,后使用,申请了没使用就跳了。
    像事务、insert or update 都会导致。
    gaius
        10
    gaius  
       2018-10-16 18:02:09 +08:00
    我这有个表用的 select on duplicate key,前段时间都把 int 用完了,应用插不进去了. 控制台还能插最后一个是最骚的
    opengps
        11
    opengps  
       2018-10-16 18:04:44 +08:00 via Android
    添加失败呗,比如某些约束不满足,返回错误,但是 id 还是占用的
    bobuick
        12
    bobuick  
       2018-10-16 18:04:50 +08:00
    间隙锁会导致主键出现这种情况。
    gaius
        13
    gaius  
       2018-10-16 18:05:10 +08:00
    insert..
    Leigg
        14
    Leigg  
       2018-10-16 18:29:16 +08:00 via iPhone
    肯定有骚操作
    kiddult
        15
    kiddult  
       2018-10-16 18:42:58 +08:00
    @dengtongcai insert 会,不管成功失败都会自增
    littlewing
        16
    littlewing  
       2018-10-16 19:57:37 +08:00
    okwork
        17
    okwork  
    OP
       2018-10-16 20:45:41 +08:00
    @qiyuey update 也会跳键?这个是主键,说不通嘛
    alcarl
        18
    alcarl  
       2018-10-16 22:48:38 +08:00 via Android
    @okwork mysql 某 update 语法是先删后插。。。。。慎用
    okwork
        19
    okwork  
    OP
       2018-10-16 23:28:57 +08:00 via Android
    @alcarl 更新主键列,真是先删后插呀
    alcarl
        20
    alcarl  
       2018-10-17 00:11:08 +08:00 via Android
    @okwork 不是,看 4 楼的语句
    Raymon111111
        21
    Raymon111111  
       2018-10-17 00:42:33 +08:00
    on duplicate 就会这样

    其实是先删了一条再插的
    qiyuey
        22
    qiyuey  
       2018-10-17 10:16:51 +08:00 via Android
    @okwork update 不会,insert or update 会,因为有可能要 insert 所以先申请了
    q397064399
        23
    q397064399  
       2018-10-18 10:09:17 +08:00
    要连续干嘛,主键
    tinyhill
        24
    tinyhill  
       2018-10-18 10:59:58 +08:00
    一般是事务回滚导致,也有可能是插入被物理删除导致。但自增 ID 连续没啥用,不要有洁癖...
    qianbi6
        25
    qianbi6  
       2018-12-16 16:32:36 +08:00 via Android
    引擎用了 innodb 了吧 innodb 会出现这问题
    c4f36e5766583218
        26
    c4f36e5766583218  
       2019-02-22 18:37:22 +08:00
    既然知道原因了,如果强迫症的话,了解一下: https://www.v2ex.com/t/529758
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5408 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 07:54 · PVG 15:54 · LAX 00:54 · JFK 03:54
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.