重复键上插入...使用MySQL更新任何东西 [英] Insert ... on duplicate key update nothing using MySQL

问题描述

我的问题是我在桌子上有多个唯一的键。

1. 插入忽略不是一个选项，因为它会抑制错误。


2. MySQL不支持声明之外的任何类型的条件（例如if（cond）然后插入else不插入）


3. 存储程序不是一个选项（唯一可以使用if / else语句的地方）


4. 重复键可能会更新一个带有新值的键，但是我想要唯一的键不会改变以防万一失败了唯一的约束。

所以唯一的选择是重复，只是不要更新任何东西。有什么办法可以实现吗？还是有其他选择？

解决方案

如果你想要 ON DUPLICATE KEY UPDATE ，只需将列值设置为现有值。与使用 IGNORE 关键字的其他冲突（如外键限制）将会起泡，但不会在冲突中改变任何值。

INSERT INTO表（value1，value2）VALUES（'1'，'2'）
ON DUPLICATE KEY UPDATE value1 = value1;

如果您想确保在发生冲突时没有有效的数据更改，您可以添加列，其中包含任意数据到表中，并将其用于 UPDATE 语句。

第三个选项if您希望在应用程序中保留所有逻辑，而不是在数据库中运行 SELECT 语句，以便在运行 INSERT / UDPATE之前检查潜在的冲突语句

尽管排除了您的场景，存储过程也可以在单个数据库调用中提供此逻辑。 p>

My problem is that I have multiple unique keys on a table.

1. Insert ignore is not an option because it suppresses the errors.
2. MySQL has no support for any type of conditionals outside a statement (ex. if (cond) then insert else don't insert)
3. Stored procedures are not an option (the only place I can use the if/else statements)
4. On duplicate key might update a key with a new value, but I want the unique keys not to change in case one fails the unique constraint.

So the only option would be on duplicate just don't update anything. Is there any way I can achieve this? Or are there any other options?

解决方案

If you want ON DUPLICATE KEY UPDATE to not actually do anything, just set a column value to the existing value. Other conflicts such as foreign key constraints will bubble up, unlike using the IGNORE keyword, but no values will change on conflict.

INSERT INTO table (value1, value2) VALUES ('1', '2')
ON DUPLICATE KEY UPDATE value1 = value1;

If you want to ensure that no valid data changes in the event of a conflict, you can add a column with arbitrary data in it to the table, and use that for the UPDATE statement.

A third option if you wish to keep all logic in your application and not in the database is to run a SELECT statement first to inspect potential conflicts before running your INSERT/UDPATE statement.

Although ruled out for your scenario, a stored procedure would also be able to provide this logic in a single database call.

这篇关于重复键上插入...使用MySQL更新任何东西的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！