php 使用 MySQL 检查空字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2327029/
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
Checking for an empty field with MySQL
提问by Quassnoi
I've wrote a query to check for users with certain criteria, one being they have an email address.
我写了一个查询来检查具有特定条件的用户，其中一个是他们有一个电子邮件地址。
Our site will allow a user to have or not have an email address.
我们的网站将允许用户拥有或不拥有电子邮件地址。
$aUsers=$this->readToArray('
SELECT `userID`
FROM `users`
WHERE `userID`
IN(SELECT `userID`
FROM `users_indvSettings`
WHERE `indvSettingID`=5 AND `optionID`='.$time.')
AND `email`!=""
');
Is this the best way to check for an empty field in SQL? I've just tried "IS NOT NULL" and this still returned a users record without them having an email address.
这是在 SQL 中检查空字段的最佳方法吗？我刚刚尝试过“IS NOT NULL”，这仍然返回了一个用户记录，而他们没有电子邮件地址。
The query above works but out of curiosity I wondered if I'm doing it the correct way.
上面的查询有效，但出于好奇，我想知道我是否以正确的方式执行此操作。
回答by Quassnoi
An empty field can be either an empty string or a NULL.
空字段可以是空字符串或NULL.
To handle both, use:
要处理两者，请使用：
email > ''
which can benefit from the rangeaccess if you have lots of empty email record (both types) in your table.
range如果您的表中有很多空的电子邮件记录（两种类型），这可以从访问中受益。
回答by Yada
Yes, what you are doing is correct. You are checking to make sure the email field is not an empty string. NULL means the data is missing. An empty string ""is a blank string with the length of 0.
是的，你在做什么是正确的。您正在检查以确保电子邮件字段不是空字符串。NULL 表示数据丢失。空字符串""是长度为 0 的空字符串。
You can add the null check also
您也可以添加空检查
AND (email != "" OR email IS NOT NULL)
回答by Mathieu de Lorimier
You could use
你可以用
IFNULL(email, '') > ''
回答by Leslie
There's a difference between an empty string (email != "") and NULL. NULL is null and an Empty string is something.
空字符串 (email != "") 和 NULL 之间是有区别的。NULL 是空值，空字符串是一些东西。
回答by pdavis
This will work but there is still the possibility of a null record being returned. Though you may be setting the email address to a string of length zero when you insert the record, you may still want to handle the case of a NULL email address getting into the system somehow.
这将起作用，但仍有可能返回空记录。尽管您可能在插入记录时将电子邮件地址设置为长度为零的字符串，但您可能仍希望以某种方式处理 NULL 电子邮件地址进入系统的情况。
$aUsers=$this->readToArray('
SELECT `userID`
FROM `users`
WHERE `userID`
IN(SELECT `userID`
FROM `users_indvSettings`
WHERE `indvSettingID`=5 AND `optionID`='.$time.')
AND `email` != "" AND `email` IS NOT NULL
');
回答by Scott Hildebrand
If you want to find all records that are not NULL, and either empty or have any number of spaces, this will work:
如果要查找所有不为 NULL 的记录，并且为空或有任意数量的空格，这将起作用：
LIKE '%\ '
Make sure that there's a space after the backslash. More info here: http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html
确保反斜杠后有一个空格。更多信息：http: //dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html
回答by user3103831
check this code for the problem:
检查此代码的问题：
$sql = "SELECT * FROM tablename WHERE condition";
$res = mysql_query($sql);
while ($row = mysql_fetch_assoc($res)) {
foreach($row as $key => $field) {
echo "<br>";
if(empty($row[$key])){
echo $key." : empty field :"."<br>";
}else{
echo $key." =" . $field."<br>";
}
}
}
