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
abcbuzhiming
V2EX  ›  MySQL

求解 MySQL 那个批量插入语句的正确打开方式是什么?

  •  
  •   abcbuzhiming · 2016-12-23 21:32:33 +08:00 · 2948 次点击
    这是一个创建于 2891 天前的主题,其中的信息可能已经有所发展或是发生改变。
    MySQL 有一个所谓的扩展插入语句,就是
    insert tablename 列 1,列 2,列 3 values (value1,value2,value3),(value1,value2,value3),(value1,value2,value3)........

    这条语句能一次性插入 n 条记录,只受限 mysql 设置的数据包大小,但是它有个奇怪的毛病,就是你这张表如果是个自增的主键,而且你在插入的时候没有主键值而让它自动生成的话,就会产生所谓的“主键空洞”,就是你插入 5 条记录进去,本来这个时候你觉得主键记录应该到 6 了,你一看记录,会发现它停在 11 (甚至更大)的位置上,你再插入的时候你会发现果然是从 11 开始的, 5-11 中间的主键就这么失踪了。
    我翻了很久的 MySQL 文档,文档说这是因为 MySQL 不能判断这次会有多少条记录进入导致的,它就会尽可能的把需要的主键估计值估计的很高甚至翻倍导致的。而且我没有找到应对这个问题的办法。唯一的办法就是你在批量插入的时候自己加上主键记录的值,就没这个问题了。难道这是这个扩展插入语句必须的?
    4 条回复    2016-12-27 20:43:22 +08:00
    Infernalzero
        1
    Infernalzero  
       2016-12-23 22:08:30 +08:00
    原因你已经知道了, mysql 之所以默认这么做是为了性能,所以你要保证主键连续的话要么就把事务级别设置成序列化或者每次插入的时候显示锁表
    klgd
        2
    klgd  
       2016-12-24 11:49:46 +08:00
    ```
    CREATE TABLE `test` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `val` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    ```

    ```
    insert into test (name,val) values ('123','222'),('qaz','qqq'),('1233','2221'),('q2azd','qqdaq'),('1dcwd23','22awd2'),('qaawd2z','qqv3dq');
    ```
    执行了 2 次,从 1 自增到 12 ,没有发现你说的主键丢失问题,是我的操作方式不对吗?
    abcbuzhiming
        3
    abcbuzhiming  
    OP
       2016-12-27 10:11:25 +08:00
    @klgd 我刚刚在 5.7.x 版本上测试确实发现这个 bug 没有了,很奇怪我记得 5.5 的时候我还测试过这个问题是存在的,难道真是 bug 。你的 mysql 是什么版本
    klgd
        4
    klgd  
       2016-12-27 20:43:22 +08:00
    @abcbuzhiming 是 5.5.53 的
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2149 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 00:47 · PVG 08:47 · LAX 16:47 · JFK 19:47
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.