mysql - 如果选择部分匹配该行的列之一，则触发对该行的列的更新

Question

更清楚地说:

表thetable (id int，用户名varchar(30)，密码varchar(30)，last_successful_login时间戳，last_unsuccessful_login时间戳，another_variable varchar(30))具有下一行: (1, "tgh", "pass", 0, 0, "另一个")

1) 用户/密码对错误，但有一行包含用户名

我想要 select id from the table where username="tgh"and password="wrongpass"and another_variable="another"; 更新所有的 last_unsuccessful_login 列包含 username="tgh" AND another_variable="another" 的行(这是唯一的，不能有两行包含 ("tgh", "another") 对.不过，可以有 ("tgh", "another2")。) 到 CURRENT_TIMESTAMP。

因此，示例行将是 (1, "tgh", "pass", 0, CURRENT_TIMESTAMP, "another")，在“select”之后> 查询不完全匹配。

更清楚地说，我试图避免仅使用 username="tgh" 和 another_variable="another" 运行额外的更新，即根据选择的结果更新表集last_unsuccessful_login=CURRENT_TIMESTAMP，其中 username="tgh"和 another_variable="another";。

2) 正确的用户/密码对

此外，如果所有三个用户名和密码以及another_variable都匹配，这次我想设置last_successful_login 到 CURRENT_TIMESTAMP。

这将使示例行 `(1, "tgh", "pass", CURRENT_TIMESTAMP, 0, "another")

最有效的方法是什么？

Answer 1

对您的问题的简短回答是否定的，SELECT 语句不可能引起或触发更新。 (这里需要注意的是，SELECT 语句可以调用可以执行 UPDATE 的 FUNCTION(MySQL 存储程序)。)

你无法回避发出 UPDATE 语句；必须从某个地方发出 UPDATE 语句，而 SELECT 语句无法“触发”它。

可以使用单个 UPDATE 语句根据密码列中的当前值检查提供的密码，并设置 last_successful_login 和 last_unsuccessful_login 列，例如:

UPDATE thetable 
   SET last_successful_login = 
       IF(IFNULL(password,'')='wrongpass',CURRENT_TIMESTAMP,0)
     , last_unsuccessful_login = 
       IF(IFNULL(password,'')='wrongpass',0,CURRENT_TIMESTAMP)
 WHERE username='tgh' 
   AND another_variable='another'

因此，您可以先发出 UPDATE 语句；然后发出 SELECT 语句。

如果您想最大限度地减少到数据库的“往返”次数，但代价是增加复杂性(使其他人更难弄清楚发生了什么)，您可以将 UPDATE 语句放入存储程序中。如果将其放入函数中，则可以设置返回值来指示登录是否成功。

SELECT udf_login('username','wrongpass','another')

因此，从您的应用程序来看，您似乎正在执行登录检查，但被调用的函数可以执行更新。

CREATE FUNCTION `udf_login`
( as_username         VARCHAR(30)
, as_password         VARCHAR(30)
, as_another_variable VARCHAR(30) 
) RETURNS INT
READS SQL DATA
BEGIN
   UPDATE `thetable`
      SET `last_successful_login` = 
          IF(IFNULL(`password`,'')=IFNULL(as_password,''),CURRENT_TIMESTAMP,0)
        , `last_unsuccessful_login` = 
          IF(IFNULL(`password`,'')=IFNULL(as_password,''),0,CURRENT_TIMESTAMP)
    WHERE `username` = as_username
      AND `another_variable` = as_another_variable;

   -- then perform whatever checks you need to (e.g)
   --     SELECT IFNULL(t.password,'')=IFNULL(as_password,'') AS password_match
   --       FROM `thetable` t
   --      WHERE t.username = as_username
   --        AND t.another_variable = as_another_variable
   -- and conditionally return a 0 or 1
   RETURN 0;
END$$