问题描述
如果包含特定值的行存在，我想插入行，如果不更新，则要插入行.
i want to insert rows IF a row containing the specific values exists, and if not update it.
具体:
数据库中存在列user_id=5, user_zip=12345, distance=600.
如果我尝试插入user_id=5, user_zip=12345, distance=700，则应该只更新distance
If i try to insert user_id=5, user_zip=12345, distance=700 it should just update the distance
但是我尝试插入user_id=5, user_zip=67890, distance=800，它应该插入新行.
but i try to insert user_id=5, user_zip=67890, distance=800 it should insert a new row.
我无法定义列user_zip和distance唯一，因此我可以使用重复键更新.
I can't define the columns user_zip and distance unique, so that i can use the on duplicate key update.
推荐答案
我认为您误解了ON DUPLICATE KEY UPDATE的工作方式.在(user_id, user_zip)上有唯一的约束，这应该可以工作:
I think you are misunderstanding how ON DUPLICATE KEY UPDATE works. With a unique constraint on (user_id, user_zip), this should work:
INSERT INTO yourTable (user_id, user_zip, distance) VALUES (5,12345,600)
ON DUPLICATE KEY UPDATE distance=600;
您不必定义user_zip唯一(这就是我理解您的问题的方式)，只需定义user_id和user_zip的组合即可.因此，您仍然可以具有2个或更多具有相同user_id的行，只是user_zip在这些行上不匹配.
You don't have to define user_zip unique (that's how I understand your question), just the combination of user_id and user_zip. So you still can have 2 or more rows with the same user_id, just the user_zip can't match on those rows.
这篇关于MySQL:通过检查多个而不是唯一的列来插入IGNORE或对重复键进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！