mysql - 根据 WHERE 子句中变量的值更新记录

Question

我正在尝试执行此查询并收到错误代码:1175 use are using safe mode and you tried to update table without where clause.

这是查询

set @email = 'foo@bar.com';
UPDATE user SET date_of_birth = '1985-10-01' 
WHERE email_id = @email;

我不想禁用安全模式。

Answer 1

由于您不想在 MySQL Workbench 中禁用安全模式，您可能需要调整查询以便可以在 where 子句中使用主键。

set @email = 'foo@bar.com';
set @id =
(
    SELECT u.id
    FROM user u
    WHERE u.email_id = @email
);

UPDATE user SET date_of_birth = '1985-10-01' 
WHERE id = @id;

或直接:

SET @id = 42;

UPDATE user SET date_of_birth = '1985-10-01' 
WHERE id = @id;

或者，暂时禁用安全模式(这是我会选择的选项):

set @email = 'foo@bar.com';

SET SQL_SAFE_UPDATES = 0;

UPDATE user SET date_of_birth = '1985-10-01' 
WHERE email_id = @email;

SET SQL_SAFE_UPDATES = 1;