MySQL 如果未找到行，则返回默认值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15319264/
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
Return a default value if no rows found
提问by Fibericon
I have the following select statement, to grab the next scheduled item for a stream. If there is no matching row, I want it to return a default value. Here's the line I'm using:
我有以下选择语句，用于获取流的下一个计划项目。如果没有匹配的行，我希望它返回一个默认值。这是我正在使用的线路：
SELECT `file` FROM `show`, `schedule`
WHERE `channel` = 1 AND `start_time` <= UNIX_TIMESTAMP()
AND `start_time` > UNIX_TIMESTAMP()-1800 AND `show`.`id` = `schedule`.`file`
ORDER BY `start_time` DESC LIMIT 1
That should grab the most recently scheduled item, but not if it's older than 30 minutes before the query. However, if the user doesn't schedule anything, I want a default value, so that something actually plays on the stream. I've tried the following:
这应该抓取最近安排的项目，但如果它早于查询前 30 分钟，则不会。但是，如果用户没有安排任何事情，我想要一个默认值，以便在流中实际播放某些内容。我尝试了以下方法：
SELECT COALESCE(`file`, 'default.webm') FROM `show`, `schedule`...
And:
和：
SELECT IFNULL(`file`, 'default.webm') FROM `show`, `schedule`
However, it always returns an empty result if no rows are found. How can I return a default value instead?
但是，如果没有找到行，它总是返回一个空结果。我怎样才能返回一个默认值呢？
回答by peterm
One way to do it
一种方法
SELECT IFNULL(MIN(`file`), 'default.webm') `file`
FROM `show`, `schedule`
WHERE `channel` = 1 AND `start_time` <= UNIX_TIMESTAMP()
AND `start_time` > UNIX_TIMESTAMP()-1800 AND `show`.`id` = `schedule`.`file`
ORDER BY `start_time` DESC LIMIT 1
Since you return only one row, you can use an aggregate function, in that case MIN(), that ensures that you'll get NULLif no records selected. Then IFNULL()or COALESCE()will do its job.
由于您只返回一行，因此您可以使用聚合函数，在这种情况下MIN()，可确保NULL在未选择任何记录时获得。然后IFNULL()或COALESCE()将完成其工作。
回答by mickmackusa
For cases where only one row of results is expected, just use UNIONwith hardcoded value(s) in a second SELECTclause that has the same number of columns as the original SELECT.
对于只需要一行结果的情况，只需UNION在SELECT与原始 SELECT 具有相同列数的第二个子句中使用硬编码值。
For the OP:
对于 OP：
(SELECT `file` FROM `show`, `schedule`
WHERE `channel` = 1 AND `start_time` <= UNIX_TIMESTAMP()
AND `start_time` > UNIX_TIMESTAMP()-1800 AND `show`.`id` = `schedule`.`file`
ORDER BY `start_time` DESC LIMIT 1) UNION (SELECT 'default.webm') LIMIT 1;
Barring any syntax errors, the result set will serve up one row with one column called file.
除非有任何语法错误，否则结果集将提供一行和一列名为file.
As a more general example:
作为一个更一般的例子：
(SELECT Col1,Col2,Col3 FROM ExampleTable WHERE ID='1234')
UNION (SELECT 'Def Val','none','') LIMIT 1;
In either case:
在任一情况下：
If there are no rows found in the first SELECT, the result set will be filled with the values from the second SELECT.
If one row is found in the first SELECT, the first SELECT values are offered in the result set and the second SELECT values are omitted.
如果在第一个 SELECT 中没有找到任何行，结果集将使用第二个 SELECT 中的值填充。
如果在第一个 SELECT 中找到一行，则在结果集中提供第一个 SELECT 值，并省略第二个 SELECT 值。
*You don't have to assign a column name/alias to the second SELECT values if you want to use the same columns (in the same order) from the first SELECT.
*如果要使用第一个 SELECT 中的相同列（按相同顺序），则不必为第二个 SELECT 值分配列名/别名。
*UNION doesn't require the column names from the two united queries to be identical, in fact you can assign different column names to the second SELECT query if it helps your case. (Your result set processing code will need to accommodate for these column naming differences.)
*UNION 不要求来自两个联合查询的列名相同，事实上，如果对您的情况有帮助，您可以将不同的列名分配给第二个 SELECT 查询。（您的结果集处理代码需要适应这些列命名差异。）
(SELECT Col1,Col2,Col3 FROM ExampleTable WHERE ID='1234')
UNION (SELECT 'Def Val' AS `Fallback1`,'none' AS `Fallback2`,'' AS `Fallback3`) LIMIT 1;
My opinion is that this is very easy to read and doesn't seem like a taxing query.
我的观点是，这很容易阅读，而且看起来不像是一个繁重的查询。
回答by ExStackChanger
To handle a wider variety of cases, you'll need some conditional logic. This is only available in stored procedures in MySQL so you'll need to wrap this code in a procedure and call it:
为了处理更广泛的情况，您需要一些条件逻辑。这仅在 MySQL 的存储过程中可用，因此您需要将此代码包装在一个过程中并调用它：
if exists (
SELECT `file` FROM `show`, `schedule`
WHERE `channel` = 1 AND `start_time` <= UNIX_TIMESTAMP()
AND `start_time` > UNIX_TIMESTAMP()-1800 AND `show`.`id` = `schedule`.`file`
) then
SELECT `file` FROM `show`, `schedule`
WHERE `channel` = 1 AND `start_time` <= UNIX_TIMESTAMP()
AND `start_time` > UNIX_TIMESTAMP()-1800 AND `show`.`id` = `schedule`.`file`
ORDER BY `start_time` DESC LIMIT 1
; else
select `DefaultValue` as `file`
; end if
