插入不知道或不在其中插入 [英] INSERT IGNORE or INSERT WHERE NOT IN
问题描述
我有一个900万行的表，由于其巨大的规模，我正努力处理所有这些数据.
I have a 9 million rows table and I'm struggling to handle all this data because of its sheer size.
我想做的是将CSV导入到表中，而不会覆盖数据.
What I want to do is add IMPORT a CSV to the table without overwriting data.
在我做这样的事情之前；如果不在，则插入(从tblName中选择电子邮件，其中source ="number"，然后电子邮件！="email")INTO(电子邮件...)值("email" ...)
Before I would of done something like this; INSERT if not in(select email from tblName where source = "number" and email != "email") INTO (email...) VALUES ("email"...)
但是我担心我会再次使服务器崩溃.我希望能够在表中插入10,000行，但前提是它不在源="number"的表中.
But I'm worried that I'll crash the server again. I want to be able to insert 10,000s of rows into a table but only if its not in the table with source = "number".
否则，我会在电子邮件"列上使用唯一".
Otherwise I would of used unique on the email column.
简而言之，我想通过检查两件事尽快插入，而不会在表中引入重复项.如果email！=电子邮件" AND源！=数字"，则插入表中，否则不执行任何操作.而且我也不希望错误报告.
In short, I want to INSERT as quickly as possible without introducing duplicates to the table by checking two things. If email != "email" AND source != "number" then insert into table otherwise do nothing. And I dont want errors reports either.
很抱歉我的措辞不好，这个问题听起来有点愚蠢.
I'm sorry for my bad wording and the question sounding a little silly.
我很难通过无法下载备份和上载错误来证明无法在数据上对其进行测试.我讨厌大型数据集:)
I'm just having a hard time adabting to not been able to test it out on the data by downloading backups and uploading if it goes wrong. I hate large datasets :)
谢谢大家的时间 -BigThings
Thank-you all for your time -BigThings
推荐答案
在email和source列上设置UNIQUE约束.
然后做:
INSERT INTO table_name(email, source, ...) VALUES ('email', 'source', ...)
ON DUPLICATE KEY UPDATE email = email;
INSERT IGNORE不会将任何类型的错误通知您.我不推荐它.我也不推荐INSERT ... WHERE NOT IN. MySQL已经为此进行了很好的优化功能.这就是为什么INSERT ... ON DUPLICATE KEY UPDATE在这里的原因.
INSERT IGNORE will not notify you of any kind of error. I would not recommend it. Neither would I recommend INSERT ... WHERE NOT IN. MySQL has an already well optimized functionality for that. That's why INSERT ... ON DUPLICATE KEY UPDATE is there.
这篇关于插入不知道或不在其中插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
