带条件的 SQL 多条语句 [英] SQL multiple statements with conditions

问题描述

我正在尝试整理一个查询中的 SQL 语句以获得身份验证序列.

I'm trying to sort out SQL statements in one query for an authentication sequence.

用户

╔════╦══════════════╦══════════╗
║ id ║ emailAddress ║ password ║
╠════╬══════════════╬══════════╣
║  1 ║ test1        ║ pass1    ║
║  2 ║ test2        ║ pass2    ║
║  3 ║ test3        ║ pass3    ║
╚════╩══════════════╩══════════╝

user_token

╔═══════╦═════════╗
║ token ║ user_id ║
╠═══════╬═════════╣
║ t1    ║       1 ║
║ t2    ║       2 ║
║ t3    ║       3 ║
╚═══════╩═════════╝

1. 检查是否存在针对电子邮件地址和密码的记录
2. 如果找到匹配项，则将带有 user_id 的令牌插入 user_token 表中
3. 返回结果集中的用户记录

我的尝试(部分)请注意，我可以组合多个由 ;

My Attempts (partial) please note i can combine several sql statements delimited by ;

1. 条件插入

1. Conditional Insert

INSERT INTO user_token(token, user_id)
SELECT ?, ? FROM DUAL
WHERE EXISTS (SELECT * FROM user WHERE emailAddress = ? AND password = ?);

["t1", 1, "test1", "pass1"];

设置某种变量，尝试与其他尝试相结合.

Setting some sort of variable, an attempt to combined with other attempts.

SELECT @id:=id, emailAddress, password FROM user WHERE emailAddress = "test1" AND password = "pass1";

我想我已经接近了，但在从子查询外部访问 @id 时遇到问题.

I think I'm close but having issues with access @id from outside the subquery.

serviceRequest.sql = "INSERT INTO user_token(token, user_id)\
    SELECT ?, @id FROM DUAL WHERE EXISTS (
      SELECT @id:=id, emailAddress, password FROM user WHERE emailAddress = ? AND password = ?
    );
    SELECT @id, ? FROM DUAL";
serviceRequest.values = ["t1", "test1", "pass1", "t1"];

目标:

借助IF子句和SQL中用户定义的变量等，在单个事务/执行中结合使用逻辑的多个SQL语句来实现身份验证过程.

To combine multiple SQL statements employing logic in a single transaction/execution to achieve the authentication process, with the help of IF clause and user defined variables in SQL etc.

推荐答案

终于解决了，但我需要改进查询 #2，使用 IF 而不是在 中执行另一个 SELECT 语句哪里存在有人吗?

Cracked it down finally, but I need improvements for query #2, using IF instead of executing another SELECT statement inside WHERE EXISTS anyone?

serviceRequest.sql = "SET @emailAddress = ?, @password = ?;\
    SELECT @authToken:= ? AS authToken, @id:=id AS id FROM user WHERE emailAddress = @emailAddress AND password = @password;\
    INSERT INTO user_token(authToken, user_id) \
        SELECT @authToken, @id FROM DUAL WHERE EXISTS (SELECT id FROM user WHERE emailAddress = @emailAddress AND password = @password);";

serviceRequest.values = [request.body.credentials.emailAddress, request.body.credentials.password, "t3"];

查询 #1.检索到第一个用户记录并设置变量(@token、@id)并作为结果集返回.

query #1. First user record was retrieved and variables (@token, @id) are set and are returned as result set.

查询 #2.如果记录存在，则 user_token 插入将执行

query #2. user_token insert will execute if record exists

感谢是否有人可以改进这个答案并进一步优化查询，尤其是查询 #2，其中我有另一个选择语句，我相信 WHERE EXISTS 可以利用检查是否 @id 不为 null 插入发生，我不必在 values 数组中传递用户名/密码两次.

Appreciate if someone can improve this answer and optimize the queries further, especially query #2 where I've another select statement going on, I believe the WHERE EXISTS could utilize checking if @id is not null for the insert to happen, and I don't have to pass username/password twice in values array.

这篇关于带条件的 SQL 多条语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！