I have a column set to the tinyint or bit type which must allow NULL values because the insert/updates may not contain an explicit value for the column. This means that when I query the column, I can't simply do something like "where columnname != '1' beause it will ignore the Null values, so I have to do: ...where columnname is null OR columnname ='0'
This can't be the right way to do it.
Problems with booleans
Moderator: General Moderators
- mecha_godzilla
- Forum Contributor
- Posts: 375
- Joined: Wed Apr 14, 2010 4:45 pm
- Location: UK
Re: Problems with booleans
Hi,
Your query is actually testing two different conditions
1. Is the value NULL?
2. If it isn't NULL, does the value equal 1?
MySQL won't evaluate NULLs numerically so it doesn't even try - at least, that's my understanding of it - but you could do something like this instead:
In a boolean test, both NULL and 0 evaluate to FALSE according to MySQL's rules, so this should hopefully work for you.
HTH,
Mecha Godzilla
Your query is actually testing two different conditions
1. Is the value NULL?
2. If it isn't NULL, does the value equal 1?
MySQL won't evaluate NULLs numerically so it doesn't even try - at least, that's my understanding of it - but you could do something like this instead:
Code: Select all
SELECT columnname
FROM tablename
WHERE columnname IS NOT TRUEHTH,
Mecha Godzilla
Re: Problems with booleans
Are you allowing NULL because that's actually a legitimate use for the column? The column may contain true, false, or neither? Because if your reason is that the column won't always be specified during an INSERT then you can simply set a default value for the column and leave it as a pure boolean field.
Re: Problems with booleans
M.G., you are right, using IS NOT TRUE instead of IS FALSE does return correctly.
Requinix, ever since I switched to PDO, inserts/updates that don't contain values in a column that does not allow NULL fails. I have set the defaults to '0', but if the column allows NULL, they can still be NULL, and not '0'.
Requinix, ever since I switched to PDO, inserts/updates that don't contain values in a column that does not allow NULL fails. I have set the defaults to '0', but if the column allows NULL, they can still be NULL, and not '0'.
Re: Problems with booleans
Unless you specify otherwise, a NOT NULL column will (generally) still have NULL as the default. Since that's invalid it forces you to specify a value. If yourhecker wrote:Requinix, ever since I switched to PDO, inserts/updates that don't contain values in a column that does not allow NULL fails. I have set the defaults to '0', but if the column allows NULL, they can still be NULL, and not '0'.
Code: Select all
field TINYINT(1) NOT NULL DEFAULT 0