是否可以检查表中的num行是否为0,然后在ONE sql语句中执行插入?
这是我尝试过的查询,但它说我有语法错误:
$query =
"IF (SELECT COUNT(ID) FROM votes WHERE userid = $userid AND itemid = $itemid AND itemtype=1) = 0
INSERT INTO votes (itemtype, itemid, userid) VALUES (1, $itemid, $userid)
SELECT 1 AS result
ELSE
SELECT 0 AS result
END IF";
我知道SELECT COUNT位本身可以成功工作。
如果这是解决这个问题的最佳方法,没有任何想法,但它会起作用。基本上,如果条件为false,它只会导致错误,因此它会阻止插入:
-- make itemtype not nullable then simply insert
INSERT INTO votes SELECT
CASE
WHEN
(SELECT COUNT(ID)
FROM votes
WHERE userid = $userid AND itemid = $itemid AND itemtype=1) = 0 THEN 1
ELSE NULL
END CASE,
$itemid, $userid;
我现在没有访问MySQL来测试它,但它能工作吗?
INSERT INTO votes (itemtype, itemid, userid)
(SELECT 1,$itemid, $userid
WHERE NOT EXISTS (
SELECT *
FROM votes
WHERE itemtype=1
AND itemid=$itemid
AND userid=$userid))