MySQL select where column is falsey

Question

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

So far I have:

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

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?

Edit:

column data type is char(32)

What PHP considers falsey is clearly documented: http://php.net/manual/en/language.types.boolean.php. a string with any non-zero number of spaces is NOT false. — Marc B, Oct 21 '16 at 17:04
hmmm thanks for the comments, right, based on the docs I just need to add in a check for FALSE, but of course, the column datatype isn't boolean, it's char(32) so don't need to check that. What about a string of '0.00' or '00000000000000'. Guess I will keep investigating for now. — Andrew, Oct 21 '16 at 17:23

score 1 · Answer 1 · answered Oct 21 '16 at 17:05

Spaces will count as something in this case, and all of the special characters such as newline will as well. Those are not considered falsey. The real question is why you have chosen to store things in the manner within MySQL. The normal way to store a boolean value in MySQL is to use a 0 or 1. This ensures consistency and means you wouldn't have to do something like this for all queries.

Your query will work, however firstname = NULL is valid as well.

Andrew · Accepted Answer · 2017-02-16T17:54:33.597

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.

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.

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:

Considered 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

Considered 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()');

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).

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: Your only realistic option would be to select everything, and check in your script if it is false or not.

MySQL select where column is falsey

2 Answers2

Linked