MySQL MySQL选择其中列不为空的地方
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1869264/
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
MySQL select where column is not empty
提问by mike
In MySQL, can I select columns only where something exists?
在 MySQL 中，我可以只选择存在某些内容的列吗？
For example, I have the following query:
例如，我有以下查询：
select phone, phone2
from jewishyellow.users
where phone like '813%'
and phone2
I'm trying to select only the rows where phone starts with 813 and phone2 has something in it.
我正在尝试仅选择 phone 以 813 开头且 phone2 中包含某些内容的行。
回答by Ivan Nevostruev
Compare value of phone2with empty string:
将 的值phone2与空字符串进行比较：
select phone, phone2
from jewishyellow.users
where phone like '813%' and phone2<>''
Note that NULLvalue is interpreted as false.
请注意，NULLvalue 被解释为false.
回答by Stanislav Stoyanov
To check if field is NULL use IS NULL, IS NOT NULLoperators.
要检查字段是否为 NULL，请使用IS NULL,IS NOT NULL运算符。
MySql reference http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html
MySql 参考http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html
回答by davek
Check for NULLand empty string values:
检查NULL和空字符串值：
select phone
, phone2
from users
where phone like '813%'
and trim(coalesce(phone2, '')) <>''
N.B. I think COALESCE() is SQL standard(-ish), whereas ISNULL() is not.
注意我认为 COALESCE() 是 SQL 标准（-ish），而 ISNULL() 不是。
回答by Brian Leishman
An answer that I've been using that has been working for me quite well that I didn't already see here (this question is very old, so it may have not worked then) is actually
我一直在使用的一个答案对我来说效果很好，我在这里还没有看到（这个问题很老，所以当时可能没有用）实际上是
SELECT t.phone,
t.phone2
FROM jewishyellow.users t
WHERE t.phone LIKE '813%'
AND t.phone2 > ''
Notice the > ''part, which will check if the value is not null, and if the value isn't just whitespace or blank.
注意这> ''部分，它将检查值是否不为空，以及值是否只是空白或空白。
Basically, if the field has something in it other than whitespace or NULL, it is true. It's also super short, so it's easy to write, and another plus over the COALESCE()and IFNULL()functions is that this is index friendly, since you're not comparing the output of a function on a field to anything.
基本上，如果该字段中有除空格或 之外的其他内容NULL，则为 true。它也非常短，因此很容易编写，并且COALESCE()和IFNULL()函数的另一个优点是它对索引友好，因为您不会将字段上的函数输出与任何内容进行比较。
Test cases:
测试用例：
SELECT if(NULL > '','true','false');-- false
SELECT if('' > '','true','false');-- false
SELECT if(' ' > '','true','false');-- false
SELECT if('\n' > '','true','false');-- false
SELECT if('\t' > '','true','false');-- false
SELECT if('Yeet' > '','true','false');-- true
UPDATEThere is a caveat to this that I didn't expect, but numerical values that are zero or below are notgreater than a blank string, so if you're dealing with numbers that can be zero or negative then DO NOT DO THIS, it bit me very recently and was very difficult to debug :(
更新对此有一个警告，我没想到，但零或以下的数值不大于空白字符串，因此如果您正在处理可能为零或负数的数字，则不要这样做，它最近咬了我，很难调试:(
If you're using strings (char, varchar, text, etc.), then this will be perfectly be fine, just be careful with numerics.
如果您使用字符串（char、varchar、text 等），那么这完全没问题，只是要小心数字。
回答by micahwittman
If there are spaces in the phone2 field from inadvertant data entry, you can ignore those records with the IFNULL and TRIM functions:
如果 phone2 字段中有来自无意数据输入的空格，您可以使用 IFNULL 和 TRIM 函数忽略这些记录：
SELECT phone, phone2
FROM jewishyellow.users
WHERE phone LIKE '813%'
AND TRIM(IFNULL(phone2,'')) <> '';
回答by Klaus Byskov Pedersen
select phone, phone2 from jewishyellow.users
where phone like '813%' and phone2 is not null
回答by Satanicpuppy
SELECT phone, phone2
FROM jewishyellow.users
WHERE phone like '813%' and (phone2 <> "");
May need some tweakage depending on what your default value is. If you allowed Null fill, then you can do "Not NULL" instead, which is obviously better.
根据您的默认值，可能需要进行一些调整。如果您允许 Null 填充，那么您可以改为使用“Not NULL”，这显然更好。
回答by Atul
We can use CASE for setting blank value to some char or String. I am using NA as Default string.
我们可以使用 CASE 将空白值设置为某些字符或字符串。我使用 NA 作为默认字符串。
SELECT phone,
CASE WHEN phone2 = '' THEN 'NA' END AS phone2 ELSE ISNULL(phone2,0)
FROM jewishyellow.users WHERE phone LIKE '813%'
回答by fyrye
Another alternative is to look specifically at the CHAR_LENGTHof the column values. (not to be confused with LENGTH)
另一种选择是专门查看CHAR_LENGTH列值的 。（不要与 混淆LENGTH）
Using a criteria where the character length is greater than 0, will avoid false positives when the column values can be falsey, such as in the event of an integer column with a value of 0or NULL. Behaving more consistently across varying data-types.
使用字符长度大于 0 的标准将避免在列值可能为假时出现误报，例如在整数列的值为0或 的情况下NULL。在不同的数据类型中表现得更一致。
Which results in any value that is at least 1 character long, or otherwise not empty.
这会导致任何长度至少为 1 个字符的值，否则不为空。
Examplehttps://www.db-fiddle.com/f/iQvEhY1SH6wfruAvnmWdj5/1
示例https://www.db-fiddle.com/f/iQvEhY1SH6wfruAvnmWdj5/1
SELECT phone, phone2
FROM users
WHERE phone LIKE '813%'
AND CHAR_LENGTH(phone2) > 0
Table Data
表格数据
users
phone (varchar 12) | phone2 (int 10)
"813-123-4567" | NULL
"813-123-4567" | 1
"813-123-4567" | 0
users2
phone (varchar 12) | phone2 (varchar 12)
"813-123-4567" | NULL
"813-123-4567" | "1"
"813-123-4567" | "0"
"813-123-4567" | ""
CHAR_LENGTH(phone2) > 0Results(same)
CHAR_LENGTH(phone2) > 0结果（相同）
users
813-123-4567 | 1
813-123-4567 | 0
users2
813-123-4567 | 1
813-123-4567 | 0
Alternatives
备择方案
phone2 <> ''Results(different)
phone2 <> ''结果（不同）
users
813-123-4567 | 1
users2
813-123-4567 | 1
813-123-4567 | 0
phone2 > ''Results(different)
phone2 > ''结果（不同）
users
813-123-4567 | 1
users2
813-123-4567 | 1
813-123-4567 | 0
COALESCE(phone2, '') <> ''Results(same)
Note: the results differ from phone2 IS NOT NULL AND phone2 <> ''which is not expected
COALESCE(phone2, '') <> ''结果（相同）
注：结果与phone2 IS NOT NULL AND phone2 <> ''预期不同
users
813-123-4567 | 1
813-123-4567 | 0
users2
813-123-4567 | 1
813-123-4567 | 0
phone2 IS NOT NULL AND phone2 <> ''Results(different)
phone2 IS NOT NULL AND phone2 <> ''结果（不同）
users
813-123-4567 | 1
users2
813-123-4567 | 1
813-123-4567 | 0
回答by OMG Ponies
Use:
用：
SELECT t.phone,
t.phone2
FROM jewishyellow.users t
WHERE t.phone LIKE '813%'
AND t.phone2 IS NOT NULL
