4 ms·

Which is better? NULL vs 0 for Boolean Columns in MySQL

1 points by simonhamp 15y ago

manuscreationis 15y ago
Booleans have 2 states - true and false. A 3 state equivalent is a Trivalent (Someone correct me if i'm wrong here). Unless you plan on making use of the third state to indicate something in particular, just set it to 0 and call it a day. Space is cheap, and unless you want to constantly write NULL checks to handle it without the need for a 3rd case, in which case you'd most likely assume NULL is 0, you're only causing problems for yourself down the road Just my opinion, I'm sure others may disagree Edit: Potential Caveat Say it's a table to hold answers from a questionnaire, you may see some value for adding it as a NULLable so you can say "Oh, this guy skipped that question", if such a thing were possible in the application. So for that situation, i'd say the choice is more of a case by case thing
gregjor 15y ago
NULL means "don't know" or "no value." Whether a column should allow NULLs at all depends on data and application. As for storage requirements, it depends on the storage engine. For MyISAM tables NULLs don't take up any more space. InnoDB tables there may be some small savings in space. Each storage engine implements NULLable columns and NULL values differently. Worrying about the space occupied by a NULL is a micro-optimization unlikely to affect real disk space used or database performance.
cd34 15y ago
How does the language you're using handle datatypes? Will your equivalency test assume Null == False or will it do some internal conversion. libmysqlclient does some unique things regarding numeric fields - converting to the ascii representation, requiring the language to convert it back to a numeric representation (if the programming language you use supports typing). It does the same with boolean versus enum versus tinyint fields. If you only have one tinyint/boolean field and are using MyISAM, you're sending a byte anyhow. You won't save 7 bits being sent across the wire because you used a 1 bit field. Each is transmitted as a single byte in mysqlclient (at least in 16) and the bit savings are reflected on disk, not communicating that result across the wire. The overhead you might save is based on how the programming language you use interprets and converts the data from libmysqlclient. The other aspect to consider is the guy that needs to maintain the code after you. Deleted (0|1) or (Null|1) might yield some confusion. Does 0 mean the record is deleted and needs to be removed or does 1 mean it is deleted? Null might mean the field is never set. True/False or Y/N might make more sense for maintenance down the line.
spitfire 15y ago
Just to be absolutely clear, NULL /= 0 (zero). This goes beyond mysql. On some IBM mainframes NULL is actually a distinct representation from zero. On most pc's, null is overloaded with 0. Pr1me for example used 017777600000 as null. Data General MV encoded the ring level in the top bits of a null value. A similar case s NaN /= 0. Except more people know and understand that rule.

1 points by simonhamp 15y ago

Booleans have 2 states - true and false. A 3 state equivalent is a Trivalent (Someone correct me if i'm wrong here). Unless you plan on making use of the third state to indicate something in particular, just set it to 0 and call it a day. Space is cheap, and unless you want to constantly write NULL checks to handle it without the need for a 3rd case, in which case you'd most likely assume NULL is 0, you're only causing problems for yourself down the road Just my opinion, I'm sure others may disagree Edit: Potential Caveat Say it's a table to hold answers from a questionnaire, you may see some value for adding it as a NULLable so you can say "Oh, this guy skipped that question", if such a thing were possible in the application. So for that situation, i'd say the choice is more of a case by case thing

gregjor 15y ago

NULL means "don't know" or "no value." Whether a column should allow NULLs at all depends on data and application. As for storage requirements, it depends on the storage engine. For MyISAM tables NULLs don't take up any more space. InnoDB tables there may be some small savings in space. Each storage engine implements NULLable columns and NULL values differently. Worrying about the space occupied by a NULL is a micro-optimization unlikely to affect real disk space used or database performance.

cd34 15y ago

How does the language you're using handle datatypes? Will your equivalency test assume Null == False or will it do some internal conversion. libmysqlclient does some unique things regarding numeric fields - converting to the ascii representation, requiring the language to convert it back to a numeric representation (if the programming language you use supports typing). It does the same with boolean versus enum versus tinyint fields. If you only have one tinyint/boolean field and are using MyISAM, you're sending a byte anyhow. You won't save 7 bits being sent across the wire because you used a 1 bit field. Each is transmitted as a single byte in mysqlclient (at least in 16) and the bit savings are reflected on disk, not communicating that result across the wire. The overhead you might save is based on how the programming language you use interprets and converts the data from libmysqlclient. The other aspect to consider is the guy that needs to maintain the code after you. Deleted (0|1) or (Null|1) might yield some confusion. Does 0 mean the record is deleted and needs to be removed or does 1 mean it is deleted? Null might mean the field is never set. True/False or Y/N might make more sense for maintenance down the line.

spitfire 15y ago

Just to be absolutely clear, NULL /= 0 (zero). This goes beyond mysql. On some IBM mainframes NULL is actually a distinct representation from zero. On most pc's, null is overloaded with 0. Pr1me for example used 017777600000 as null. Data General MV encoded the ring level in the top bits of a null value. A similar case s NaN /= 0. Except more people know and understand that rule.