即使没有结果也返回一个值 [英] Returning a value even if no result

问题描述

我有这种简单的查询，它针对给定的id返回一个不为null的整数字段:

I have this kind of simple query that returns a not null integer field for a given id:

SELECT field1 FROM table WHERE id = 123 LIMIT 1;

问题是，如果找不到ID，则结果集为空.我需要查询始终返回一个值，即使没有结果.

The thing is if the id is not found, the resultset is empty. I need the query to always return a value, even if there is no result.

我有这个东西在工作，但是我不喜欢它，因为它运行相同子查询的2倍:

I have this thing working but I don't like it because it runs 2 times the same subquery:

SELECT IF(EXISTS(SELECT 1 FROM table WHERE id = 123) = 1, (SELECT field1 FROM table WHERE id = 123 LIMIT 1), 0);

如果该行存在，则返回field1，否则返回0.有什么改进的方法吗?

It returns either field1 if the row exists, otherwise 0. Any way to improve that?

谢谢！

编辑以下评论和答案:是的，它必须在单个查询语句中，并且我不能使用计数技巧，因为我需要返回仅1个值(仅供参考，我使用Java/Spring方法SimpleJdbcTemplate.queryForLong()运行查询.)

Edit following some comments and answers: yes it has to be in a single query statement and I can not use the count trick because I need to return only 1 value (FYI I run the query with the Java/Spring method SimpleJdbcTemplate.queryForLong()).

推荐答案

如果结果为null，MySQL具有返回值的功能.您可以在整个查询中使用它:

MySQL has a function to return a value if the result is null. You can use it on a whole query:

SELECT IFNULL( (SELECT field1 FROM table WHERE id = 123 LIMIT 1) ,'not found');

这篇关于即使没有结果也返回一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！