Несколько операторов SQL с условиями

Я пытаюсь отсортировать операторы SQL в одном запросе для последовательности проверки подлинности.

пользователь

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

токен_пользователя

╔═══════╦═════════╗
║ token ║ user_id ║
╠═══════╬═════════╣
║ t1    ║       1 ║
║ t2    ║       2 ║
║ t3    ║       3 ║
╚═══════╩═════════╝
  1. Проверьте, существует ли запись, а также адрес электронной почты и пароль.
  2. Если совпадение найдено, вставьте токен с user_id в таблицу user_token.
  3. отправить обратно запись пользователя в наборе результатов

Мои попытки (частичные) обратите внимание, что я могу объединить несколько операторов sql, разделенных ;

  1. Условная вставка

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

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

  2. Установка какой-то переменной, попытка совмещена с другими попытками.

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

  3. Я думаю, что я близок, но у меня проблемы с доступом к @id из-за пределов подзапроса.

    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"];

Цель:

Чтобы объединить несколько операторов SQL, использующих логику, в одной транзакции/выполнении для достижения процесса аутентификации с помощью предложения IF и пользовательских переменных в SQL и т. д.


mysql sql node-mysql
person user2727195    schedule 20.02.2016    source источник
comment
В MySQL insert не возвращает запись в результирующем наборе.   -  person Gordon Linoff    schedule 20.02.2016
comment
Привет, Гордон, правда, но у меня может быть другой оператор SELECT, за которым следует или которому предшествует этот, который вернет набор результатов, библиотека mysql поддерживает несколько состояний, я также открыт для использования пользовательских переменных @id=id; посмотри мою попытку №2   -  person user2727195    schedule 20.02.2016

Ответы (2)


arrow_upward
0
arrow_downward

Возможно, вы ищете last_insert_id() и row_count(). На всякий случай я бы присвоил их переменным:

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

SELECT @row_count := ROW_COUNT(), @last_insert_id = LAST_INSERT_ID();

SELECT u.*
FROM user
WHERE @row_count > 0 AND emailaddress = ? and password = ?;

Я не уверен, что это именно то, что вам нужно, но это показывает использование двух функций, которые вам, вероятно, понадобятся.

person Gordon Linoff    schedule 20.02.2016
comment
позвольте мне объяснить бизнес-логику, таблица пользователей уже заполнена несколькими записями, a) мы проверяем их учетные данные (адрес электронной почты/пароль) - b) если запись существует, вставляем токен в user_token(token, id), токен предоставляется мной и id пользователь извлекается из a, c) возвращает токен с идентификатором в качестве набора результатов (обратите внимание, что токен не является идентификатором автоматического увеличения, это случайная строка) - person user2727195; 21.02.2016
comment
Я думаю, что мне нужны эти 3 или более комбинированных оператора sql в контексте бизнес-логики, описанной выше. - person user2727195; 21.02.2016
comment
Думаю, я близок, не могли бы вы просмотреть мою попытку № 3, в основном @id недоступен из-за пределов подзапроса. Error: ER_BAD_NULL_ERROR: Column 'user_id' cannot be null - person user2727195; 21.02.2016
comment
Не могли бы вы взглянуть на мой ответ, не уверен, что это что-то новое для сообщества здесь, но я называю их "Intelligent compound SQL statements", что экономит нам много кода программирования, я мог бы заменить всю логику несколькими комбинированными операторами sql, до этого была длинная реализация класса в моем коде. - person user2727195; 21.02.2016

arrow_upward
0
arrow_downward

Наконец-то взломал его, но мне нужны улучшения для запроса № 2, используя IF вместо выполнения другого оператора SELECT внутри WHERE EXISTS кто-нибудь?

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) установлены и возвращаются в виде набора результатов.

запрос №2. Вставка user_token будет выполняться, если запись существует

Ценю, если кто-то может улучшить этот ответ и оптимизировать запросы, особенно запрос № 2, где у меня есть еще один оператор выбора, я считаю, что WHERE EXISTS может использовать проверку, если @id не является нулевым для вставки, и я не необходимо дважды передать имя пользователя/пароль в массиве values.

person user2727195    schedule 20.02.2016