使用 MySQL 检查空字段

2022-01-06 00:00:00 sql null php mysql

我写了一个查询来检查符合特定条件的用户，其中一个是他们有电子邮件地址.

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`!=""
 ');

这是在 SQL 中检查空字段的最佳方法吗?我刚刚尝试了IS NOT NULL"，这仍然返回了一个用户记录，而他们没有电子邮件地址.

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.

上面的查询有效，但出于好奇，我想知道我是否以正确的方式进行操作.

The query above works but out of curiosity I wondered if I'm doing it the correct way.

推荐答案

空字段可以是空字符串或 NULL.

An empty field can be either an empty string or a NULL.

要同时处理这两种情况，请使用:

To handle both, use:

email > ''

如果您的表格中有大量空电子邮件记录(两种类型)，则可以从 range 访问中受益.

which can benefit from the range access if you have lots of empty email record (both types) in your table.

相关文章