MySQL[批量]插入数据


=Start=

搜索关键字:
  • mysql insert many data
  • mysql 批量 插入 数据
  • mysql insert data speed
  • mysql insert ignore

参考解答:

先说解决方案:
INSERT INTO tbl_name
    (a,b,c)
VALUES
    (1,2,3),
    (4,5,6),
    (7,8,9);

INSERT IGNORE INTO tbl_name
    (a,b,c)
VALUES
    (1,2,3),
    (4,5,6),
    (7,8,9);

load data infile '/path/to/file.csv'
    into table tbl_name
    fields terminated by ',' optionally enclosed by '"'
    lines terminated by '\n'
    ignore 1 lines
    (field1, field2, field3,..., fieldN);

load data local infile '/path/to/file.csv'
    into table tbl_name
    fields terminated by ',' optionally enclosed by '"'
    lines terminated by '\n'
    ignore 1 lines
    (field1, field2, field3,..., fieldN);
再说在实际过程中碰到的问题:
0.影响MySQL插入语句的速度的因素Speed of INSERT Statements

为了优化插入速度,可以将多个小的插入操作合并成一个大的插入操作来达到目的。理想情况是:在一个连接建立后,一次性发送所有的数据、延迟所有的索引更新操作直到批量插入完成。

下面是MySQL中插入一行数据的各阶段操作的所需时间占比情况:

  • Connecting——建立连接: (3)
  • Sending query to server——向服务器发送请求: (2)
  • Parsing query——服务器解析请求: (2)
  • Inserting row——插入数据: (1 × size of row)
  • Inserting indexes——插入索引: (1 × number of indexes)
  • Closing——关闭连接: (1)

说明:以上内容并没有把打开表操作的时间算进去,因为那在每个并发执行的查询操作中只会发生一次。当使用B-tree结构作索引时,插入索引操作的速度与表的大小成反比(比值约为log N)。

1.insert vs. bulk insert

常规的每个insert语句插入一条记录效率太低,若将多个记录放在一个insert语句中进行插入,效率会有较大提高(参考上面的说明);当记录可能存在重复时使用ignore关键字进行忽略(是否使用需要视具体情况而定);

2.bulk_insert_buffer_size

在一个insert语句中进行大量记录的插入时可能需要关注一下「bulk_insert_buffer_size」变量的大小;

3.load data

个人感觉用「load data」的方式速度是最快的,但是坑也比较多(除非用于加载的文件内容格式全都准确无误,一般情况下这个比较难以保证,比如文件中多了个空行什么的);

4.用「load data」的方式时最常碰到的权限问题

ERROR 1045 (28000): Access denied for user ‘xxx’@’xxx’ (using password: YES)

原因:这个一般是因为非root用户没有FILE Privilege,可以通过show grants查看当前登陆用户的权限,也可以通过select mysql.user查看某用户的权限,一般情况下,normal user是无FILE权限的。

三种解决办法:

1)给「load data」命令加local参数,用「load data local infile ‘filename’ into table xxx」来导数据(推荐)

2)给normal user开通FILE Privilege,注意:FILE权限与SELECE/DELETE/UPDATE等不同,后者是可以具体指定到某个db的某个表的,而FILE则是全局的,即只能通过:

GRANT FILE ON *.* TO user@xxx;
/* 或 */
UPDATE mysql.user SET File_priv = 'Y' WHERE Host = 'xxx' AND User = 'user';

FLUSH PRIVILEGES;

才能使FILE权限对所有db的所有tables生效。通过:

GRANT ALL ON dbName.* TO 'someuser'@'somehost';

不能使指定的user在指定的db上具有FILE权限。根据最小权限的原则,这个方法并不安全,故不推荐使用。

3)修改 ~/.my.cnf 中的配置,如下:

[mysql]
local-infile

参考链接:

5.「load data」的语法

将指定column字段的部分放在最后,参考:

=EOF=


《“MySQL[批量]插入数据”》 有 9 条评论

  1. MySQL 当记录不存在时插入,当记录存在时更新
    http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html
    http://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists
    http://blog.sae.sina.com.cn/archives/3491
    http://fenley.iteye.com/blog/626355
    https://segmentfault.com/a/1190000002527333
    `
    INSERT INTO table (id, name, age) VALUES(1, “A”, 19) ON DUPLICATE KEY UPDATE name=”A”, age=19 — 推荐
    or
    REPLACE INTO table (id, name, age) VALUES(1, “A”, 19)
    `

  2. MySQL中如何进行正则表达式替换? (mysql regex replace string)
    https://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql
    `
    — 在 MySQL 8.0+ 中支持原生的正则替换 REGEXP_REPLACE 功能
    SELECT REGEXP_REPLACE(‘Stackoverflow’,'[A-Zf]’,’-‘,1,0,’c’);
    — Output:
    -tackover-low

    — 语法解析
    REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])

    REGEXP_REPLACE(‘原始字符串’, ‘要匹配的正则表达式’, ‘做替换的字符串'[, 从第几个字符开始[, 进行几次替换[, 匹配模式]]])

    match_type有几种取值:
    c #大小写敏感
    i #大小写不敏感
    m #多行模式
    `
    https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html
    https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-replace
    https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-like

  3. MySQL “replace into” 的坑
    https://blog.xupeng.me/2013/10/11/mysql-replace-into-trap/

    MySQL的Replace into 与Insert into on duplicate key update真正的不同之处
    https://www.jb51.net/article/47090.htm
    `
    总结从上面的测试结果看出,相同之处:
    (1)没有key的时候,replace与insert .. on deplicate udpate相同。
    (2)有key的时候,都保留主键值,并且auto_increment自动+1。

    不同之处:有key的时候,replace是delete老记录,而录入新的记录,所以原有的所有记录会被清除,这个时候,如果replace语句的字段不全的话,有些原有的比如例子中c字段的值会被自动填充为默认值。
    而insert .. deplicate update则只执行update标记之后的sql,从表象上来看相当于一个简单的update语句。
    但是实际上,根据我推测,如果是简单的update语句,auto_increment不会+1,应该也是先delete,再insert的操作,只是在insert的过程中保留除update后面字段以外的所有字段的值。

    所以两者的区别只有一个,insert .. on deplicate udpate保留了所有字段的旧值,再覆盖然后一起insert进去,而replace没有保留旧值,直接删除再insert新值。

    从底层执行效率上来讲,replace要比insert .. on deplicate update效率要高,但是在写replace的时候,字段要写全,防止老的字段数据被删除。
    `
    MySql避免重复插入记录方法(ignore,Replace,ON DUPLICATE KEY UPDATE)
    http://www.111cn.net/database/mysql/50135.htm

  4. “INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”
    https://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update
    https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

    如何在单MySQL查询中一次插入多条记录,且在出现重复key时进行update操作(MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query)
    https://stackoverflow.com/questions/2714587/mysql-on-duplicate-key-update-for-multiple-rows-insert-in-single-query
    `
    INSERT INTO beautiful (name, age)
    VALUES
    (‘Helen’, 24),
    (‘Katrina’, 21),
    (‘Samia’, 22),
    (‘Hui Ling’, 25),
    (‘Yumie’, 29)
    ON DUPLICATE KEY UPDATE
    age = VALUES(age),

    `

回复 hi 取消回复

您的电子邮箱地址不会被公开。 必填项已用*标注