問題描述
我很難形成條件插入
我有 x_table 列(實例、用戶、項目)，其中實例 ID 是唯一的.只有當用戶已經沒有給定的項目時，我才想插入一個新行.
I have x_table with columns (instance, user, item) where instance ID is unique. I want to insert a new row only if the user already does not have a given item.
例如嘗試插入 instance=919191 user=123 item=456
For example trying to insert instance=919191 user=123 item=456
Insert into x_table (instance, user, item) values (919191, 123, 456)
ONLY IF there are no rows where user=123 and item=456
在正確方向上的任何幫助或指導將不勝感激.
Any help or guidance in the right direction would be much appreciated.
推薦答案
如果您的 DBMS 沒有對您在執行插入時選擇的表施加限制，請嘗試:
If your DBMS does not impose limitations on which table you select from when you execute an insert, try:
INSERT INTO x_table(instance, user, item)
SELECT 919191, 123, 456
FROM dual
WHERE NOT EXISTS (SELECT * FROM x_table
WHERE user = 123
AND item = 456)
在這里，dual 是一個只有一行的表(最初在 Oracle 中發現，現在也在 mysql 中).邏輯是 SELECT 語句生成具有所需值的單行數據，但僅在尚未找到值時才生成.
In this, dual is a table with one row only (found originally in Oracle, now in mysql too). The logic is that the SELECT statement generates a single row of data with the required values, but only when the values are not already found.
或者，查看 MERGE 語句.
Alternatively, look at the MERGE statement.
這篇關于MySQL 條件插入的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！