英文:
Efficient way to insert record or ignore record when columns values are same, except dateTime column
问题
我有一张带有 DateTime 列的表。我想在插入时避免表中出现重复行。我已经尝试过 INSERT IGNORE 和 REPLACE。然而，由于每次插入时 DateTime 都会变化，记录正在重复。
如何高效地检查如果其他值相同则不插入
我的查询如下：
REPLACE INTO mytable (msisdn, tokenized_card_no, createddate, scheduletype, payment_for) VALUES ('11', '11', NOW(), '12', '11');
INSERT IGNORE INTO ESB_AUTOPAYMENT (msisdn, tokenized_card_no, createddate, scheduletype, payment_for) VALUES ('11', '11', NOW(), '12', '11');
其中一种方法是，首先查询记录是否存在，然后不执行插入查询。是否有更好的解决方案？
我正在使用 JDBC。
英文:
I have a table with a DateTime column. I want to avoid duplicate rows in my table on insertion. I have tried INSERT IGNORE AND REPLACE. However, since DateTime varies on every insertion, the records are duplicating.
How to check efficiently if other values are same then don't insert
My queries are:
REPLACE into mytable (msisdn, tokenized_card_no, createddate, scheduletype, payment_for) values ('11', '11', now(), '12', '11');
insert IGNORE into ESB_AUTOPAYMENT (msisdn, tokenized_card_no, createddate, scheduletype, payment_for) values ('11', '11', now(), '12', '11');
One way to do this, is to first select the record if exist then don't run the insert query. Is there any better solution?
I am using JDBC
答案1
得分: 1
在SQL术语中，这被称为“MERGE”或“UPSERT”（这是“UPDATE或INSERT”的混合词）。
MySQL不支持它。你会发现MySQL不支持很多东西，而且一开始就不应该使用它，它...根本不是一个好的数据库引擎。
无论如何，你仍然可以这样做：首先，在除了“createddate”之外的所有字段上建立一个唯一索引，然后你可以使用“INSERT ... ON DUPLICATE KEY UPDATE id=id”。不要使用IGNORE；它会忽略所有错误，而不仅仅是主键问题。
*）在搜索“如何在MySQL中进行UPSERT”时，使用IGNORE是非常常见的建议，但是MySQL的一个问题是社区似乎对于正确的做法非常粗心。不要使用IGNORE。
英文:
In SQL terms, this is called MERGE or UPSERT (which is a portmanteau of 'UPDATE or INSERT').
MySQL doesn't support it. You'll find that mysql doesn't support many things and really shouldn't be used in the first place, it's... not a good DB engine at all.
At any rate, you can still sort of do this: First make a unique index on everything except createddate, and then you can use INSERT ... ON DUPLICATE KEY UPDATE id=id). Don't use IGNORE; that ignores all errors, not just primary key issues.
*) Using IGNORE is very common advice when searching the web for 'How do I UPSERT in MySQL', but, one of the problems of MySQL is that the community seems to be very careless about proper practices. Do not use IGNORE.
答案2
得分: 1
你需要定义什么使你的行唯一，并将其定义为唯一（你还可以设置多列）。设置完成后，忽略/替换将起作用。
英文:
You need to define what makes you row unique and define it as unique (you can also set multi columns).
After you will set it, the ignore / replace will work.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论