尽快进行大量更新插入
我的应用程序（使用 MySQL）正在执行大量后续更新插入。现在我的 SQL 看起来像这样：
INSERT IGNORE INTO customer (name,customer_number,social_security_number,phone) VALUES ('VICTOR H KINDELL','123','123','123')
INSERT IGNORE INTO customer (name,customer_number,social_security_number,phone) VALUES ('VICTOR H KINDELL','123','123','123')
INSERT IGNORE INTO customer (name,customer_number,social_security_number,phone) VALUES ('VICTOR H KINDELL OR','123','123','123')
INSERT IGNORE INTO customer (name,customer_number,social_security_number,phone) VALUES ('TRACY L WALTER PERSONAL REP FOR','123','123','123')
INSERT IGNORE INTO customer (name,customer_number,social_security_number,phone) VALUES ('TRACY L WALTER PERSONAL REP FOR','123','123','123')
到目前为止，我发现 INSERT IGNORE 是实现更新插入的最快方法。选择一条记录来查看它是否存在，然后更新它或插入新记录太慢了。即使这也没有我想要的那么快，因为我需要为每条记录执行单独的语句。有时我会连续有大约 50,000 个这样的语句。
有没有一种方法可以在一个语句中处理所有这些问题，而不删除任何现有记录？
My app (which uses MySQL) is doing a large number of subsequent upserts. Right now my SQL looks like this:
INSERT IGNORE INTO customer (name,customer_number,social_security_number,phone) VALUES ('VICTOR H KINDELL','123','123','123')
INSERT IGNORE INTO customer (name,customer_number,social_security_number,phone) VALUES ('VICTOR H KINDELL','123','123','123')
INSERT IGNORE INTO customer (name,customer_number,social_security_number,phone) VALUES ('VICTOR H KINDELL OR','123','123','123')
INSERT IGNORE INTO customer (name,customer_number,social_security_number,phone) VALUES ('TRACY L WALTER PERSONAL REP FOR','123','123','123')
INSERT IGNORE INTO customer (name,customer_number,social_security_number,phone) VALUES ('TRACY L WALTER PERSONAL REP FOR','123','123','123')
So far I've found INSERT IGNORE to be the fastest way to achieve upserts. Selecting a record to see if it exists and then either updating it or inserting a new one is too slow. Even this is not as fast as I'd like because I need to do a separate statement for each record. Sometimes I'll have around 50,000 of these statements in a row.
Is there a way to take care of all of these in just one statement, without deleting any existing records?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可以将所有内容放入 1 个插入件中
INSERT IGNORE INTO table_1 (field1, field2) VALUES ('val1', 'val2'), ('val3', 'val4'), 等等。如果您需要更新程序或插入记录，您可能还需要检查INSERT ... ON DUPLICATE KEY UPDATE。You can put everything in 1 insert
INSERT IGNORE INTO table_1 (field1, field2) VALUES ('val1', 'val2'), ('val3', 'val4'), etc. You may also want to checkINSERT ... ON DUPLICATE KEY UPDATEif you need to either updater or insert a record.