

Which is better? NULL vs 0 for Boolean Columns in MySQL - simonhamp
http://forrst.com/posts/NULL_vs_0_for_Boolean_Columns_in_MySQL-TkT

======
cd34
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.

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

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

