批量插入mysql-我可以使用ignore子句吗?有没有限制.批量插入记录? [英] bulk insert mysql - can i use ignore clause? is there a limit to no. of records for bulk insert?

问题描述

我有一堆要插入的数据，因此决定对mysql使用批量插入.

I have a bunch of data that i want to insert and i have decided to use bulk insert for mysql.

插入朋友(请求者，好友)值(值1，值2)，(值2，值1)，(值3，值4)，(值4，值3)，...

insert into friends (requestor, buddy) values (value1, value2), (value2, value1), (value3, value4), (value4, value3), ...

我想了解以下信息:

1)我可以使用忽略吗?例如

1) can i use ignore? eg

将忽略值插入朋友(请求者，好友)值(值1，值2)，(值2，值1)，(值3，值4)，(值4，值3)，...

insert ignore into friends (requestor, buddy) values (value1, value2), (value2, value1), (value3, value4), (value4, value3), ...

如果我重复了会怎样?会不会)不插入所有内容? b)在重复记录之前插入记录，并在此之后停止处理数据? c)忽略重复项并继续处理其余的吗?

what happens if i have duplicate? will it a) not insert everything? b) insert the records before the duplicate record and STOP processing the data after that? c) ignore the duplicate and carry on with the rest?

2)否有限制.可以用于像这样的大容量插入的记录?

2) is there a limit to the no. of records i can use for a bulk insert like this?

谢谢.

推荐答案

是的，您可以使用"ignore"关键字，它只会忽略重复的错误.它将插入它可能的每一行，并跳过那些会导致重复的行(并且不会停止处理数据.)如果您执行以下操作(假设第一列是主键):

Yes, you can use the "ignore" keyword, and it will simply ignore duplicate errors. It will insert every row that it can, skipping those that would lead to duplicates (and it will not stop processing data.) If you do something like this (where we assume that the first column is a primary key):

将忽略插入c值(1,1)，(2,2)，(3,3)，(3,5)，(4,5);

insert ignore into c values (1,1), (2,2), (3,3), (3,5), (4,5);

这意味着将插入(3,3)，而不会插入(3,5).除了(3,5)以外的所有内容都会被插入(假设有一个新表).

Then that means that (3,3) will be inserted and (3,5) will not. Everything but (3,5) will be inserted (assuming a fresh table.)

可能没有硬性限制，但是您可能需要进行测试，以根据需要确定应该划界的位置.

There is probably no hard limit, but you might want to do testing to see where you should draw the line based on your needs.

MySQL似乎确实对SQL查询的大小有可配置的限制，默认值为1MB.更多信息: http://dev.mysql. com/doc/refman/5.0/en/server-system-variables.html#sysvar_query_cache_limit .

It does seem that MySQL has a configurable limit on the size of SQL queries, with the default being 1MB. More info: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_query_cache_limit.

这篇关于批量插入mysql-我可以使用ignore子句吗?有没有限制.批量插入记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！