我想在一列"email_new"中写入一个值,但前提是该值不存在于名为"email"的第二列中。这意味着电子邮件列中的任何条目都不应包含新的邮件地址。
这是用于更改电子邮件地址的程序,其中必须验证电子邮件地址,并且"电子邮件"列仅允许UNIQUE值。
"
UPDATE `table`
SET email_new = '".mysql_real_escape_string($newmail)."'
WHERE
user = '".mysql_real_escape_string($user)."'
&& email NOT CONTAINS('".mysql_real_escape_string($newmail)."')
"
这样的事情可能发生吗?
您没有检查新邮件是否真的是唯一的:
在查询中附加另一个AND条件,类似于:AND 0=(从表中选择count(*),其中email=mysql_real_sescape_string($newmail))
$q = "
UPDATE
`table`
SET
`email_new` = '" . mysql_real_escape_string($newmail) . "'
WHERE
`user` = '" . mysql_real_escape_string($newmail) . "'
AND NOT EXISTS (
SELECT
`email`
FROM
`table`
WHERE
`email` = '" . mysql_real_escape_string($newmail) . "'
)
";
UPDATE table
SET email_new = ?
WHERE
user = ?
AND (SELECT COUNT(*) = 0 FROM table WHERE email = ?)
用实际值替换?
符号。
或者简称
UPDATE `table`
SET email_new = IF (email IS NULL, ?, ?)
-- this line is if you want to update email if email is not null
, email = IF (email IS NOT NULL, ?, ?)
WHERE ...