如何检查变量是否为 NULL，然后使用 MySQL 存储过程设置它？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18499747/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check if a variable is NULL, then set it with a MySQL stored procedure?
提问by Jaylen
I have a MySQL stored procedure where I find the max value from a table.
我有一个 MySQL 存储过程，我可以在其中找到表中的最大值。
If there is no value I want to set the variable to yesterday's date.
如果没有值，我想将变量设置为昨天的日期。
DECLARE current_procedure_name CHAR(60) DEFAULT 'accounts_general';
DECLARE last_run_time datetime DEFAULT NULL;
DECLARE current_run_time datetime DEFAULT NOW();
-- Define the last run time
SET last_run_time := (SELECT MAX(runtime)
FROM dynamo.runtimes WHERE procedure_name = @current_procedure_name);
-- if there is no last run time found then use yesterday as starting point
IF(@last_run_time IS NULL) THEN
SET last_run_time := DATE_SUB( NOW(), INTERVAL 1 DAY);
END IF;
SELECT @last_run_time;
The problem is that @last_run_timeis always NULL.
问题是它@last_run_time总是NULL。
The following code is not being executed for some reason
以下代码由于某种原因没有被执行
IF(last_run_time IS NULL) THEN
SET last_run_time := DATE_SUB( NOW(), INTERVAL 1 DAY);
END IF;
How can I set the variable @last_run_timecorrectly?
如何@last_run_time正确设置变量？
回答by wchiquito
@last_run_timeis a 9.4. User-Defined Variablesand last_run_time datetimeone 13.6.4.1. Local Variable DECLARE Syntax, are different variables.
@last_run_time是9.4。用户定义的变量和last_run_time datetime一个13.6.4.1。局部变量 DECLARE 语法，是不同的变量。
Try: SELECT last_run_time;
尝试： SELECT last_run_time;
UPDATE
更新
Example:
例子：
/* CODE FOR DEMONSTRATION PURPOSES */
DELIMITER $$
CREATE PROCEDURE `sp_test`()
BEGIN
DECLARE current_procedure_name CHAR(60) DEFAULT 'accounts_general';
DECLARE last_run_time DATETIME DEFAULT NULL;
DECLARE current_run_time DATETIME DEFAULT NOW();
-- Define the last run time
SET last_run_time := (SELECT MAX(runtime) FROM dynamo.runtimes WHERE procedure_name = current_procedure_name);
-- if there is no last run time found then use yesterday as starting point
IF(last_run_time IS NULL) THEN
SET last_run_time := DATE_SUB(NOW(), INTERVAL 1 DAY);
END IF;
SELECT last_run_time;
-- Insert variables in table2
INSERT INTO table2 (col0, col1, col2) VALUES (current_procedure_name, last_run_time, current_run_time);
END$$
DELIMITER ;
