MySQL选择列是falsey的地方 [英] MySQL select where column is falsey

问题描述

如何选择与 PHP 的 falsey 等效的列(例如，当在 php 中 !$myVar 求值为 true 时).

How to select where column is equivalent to PHP's falsey (eg, when in php !$myVar evaluates to true).

到目前为止我有:

SELECT * from users 
WHERE firstname = '0' 
   OR firstname = ''
   OR firstname IS NULL;

我认为这涵盖了 php 的大部分错误，但剩下的主要问题是……任何数量的空格呢?例如，我现在是否需要对 \s+ 进行某种正则表达式检查.以及上面的制表符或换行符.或者有没有更简单的方法来处理所有这些?

I think that covers most of php's falsey, but the main issue left then is... what about any number of spaces? Do I need to now do some kind of a regex check for \s+ for example. And what above tab or new line characters. Or is there easier way to handle all of this?

列数据类型为char(32)

推荐答案

做了更多调查.http://php.net/manual/en/language.types.boolean.php 是一个很好的起点.检查您所知道的数据中可能存在哪些被视为错误的内容.

Did some more investigating. http://php.net/manual/en/language.types.boolean.php is a good place to start. Check for what you know might be in the data that counts as false.

但是，我希望这仅用于数据清理目的.构建一些东西来要求您查询 PHP 认为是 false 的内容似乎是一个非常糟糕的主意……看看这些测试结果.

However, I would hope this is just for data cleanup purposes. Architecting something to require you to query for what PHP considers false seems like a really bad idea... Just look at these test results.

我将以下内容插入到 MySQL 表中，然后使用 PHP 查询数据库并检查结果是否被认为是错误的:

I inserted into a MySQL table the following, and then queried the DB with PHP and checked if the result were considered false or not:

考虑FALSE

INSERT into confirm (data) VALUES ('0');
INSERT into confirm (data) VALUES (00);
INSERT into confirm (data) VALUES (000);
INSERT into confirm (data) VALUES ('');
INSERT into confirm (data) VALUES (null);
INSERT into confirm (data) VALUES ('  ');
INSERT into confirm (data) VALUES ('   ');
INSERT into confirm (data) VALUES ('    '); #tab character

认为 TRUE

INSERT into confirm (data) VALUES ('0.00');
INSERT into confirm (data) VALUES ('0.000');
INSERT into confirm (data) VALUES (0.0);
INSERT into confirm (data) VALUES (0.00);
INSERT into confirm (data) VALUES (0.000);
INSERT into confirm (data) VALUES ('00');
INSERT into confirm (data) VALUES ('false');
INSERT into confirm (data) VALUES ('null');
INSERT into confirm (data) VALUES ('[]');
INSERT into confirm (data) VALUES ('array');
INSERT into confirm (data) VALUES ('array()');

注意，在 MySQL 中插入多个空格时，会将其转换为空的 '' 字符串.制表符仍然存在(但看起来像空格).

Note that when inserting multiple blank spaces into MySQL, it converts it into an empty '' string. The tab character remains (but looks like white space).

您可能有制表符或其他被 PHP 认为是错误的其他空白字符这一事实告诉我，很难创建一个涵盖所有可能性的查询.

The very fact you might have tab characters or other miscellaneous white space characters that are considered false by PHP tells me it would be a difficult to create a query that covers all possibilities.

tldr:您唯一现实的选择是选择所有内容，并检查您的脚本是否为假.

这篇关于MySQL选择列是falsey的地方的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！