Re: boolean values

[Pål Arne Hoff]

Robert I Graham wrote:
> Hi guys,
> 
> What is the best way to represent boolean values in a MySQL database?  At the 
> moment I have been using the enum datatype in MySQL.  An exmaple column in one 
> of my tables would be as follows:
> 
> Is_Available  ENUM('Y', 'N') DEFAULT 'N' NOT NULL
> 
> I can then retrieve the value in a prepared statement using 
> isAvailable = ResultSet.getBoolean(int);
> isAvailable will be true if Is_Available == 'Y' and false if Is_Available == 
> 'N'.
> 
> This strategy works well, but my question is "is this a portable solution?"  Is 
> the enum datatype a standard used by all databases such as Oracle, Sybase etc or 
> is it exclusive to MySQL.  I can seem to find information on enum anywhere other 
> than in the MySQL documentation. The O'Reilly book "Database Programming with 
> JDBC and Java" recommends using the BIT datatype to represent a Java boolean 
> value, however MySQL does not seem to directly support BIT.  When I use BIT in 
> my script to create a table, MySQL translates it to a TINYINT - corresponding to 
> a byte in Java.  As a byte can have 255 possible values, what values should be 
> used to represent true and false?  Can I use ResultSet.setBoolean(int, boolean) 
> when addressing a TINYINT?
> 
A TINYINT is the usual type to use when storing a boolean in MySQL. You can use 
the ResultSet.get/setBoolean(int, boolean) methods when using TINYINT. Values in 
the database will be false=0 and true=1. I don't know if this is portable to 
e.g. Oracle, but I would think so (as MySQL seems to change BIT to TINYINT 
automatically, you can just leave BIT in your creation script. That way a BIT 
type will be created on databaseservers that support it).

An ENUM type also uses 1 byte of storage space when storing just 'N' or 'Y' so I 
would say TINYINT is the better choice no matter how you look at it.


-- 
Pål Arne Hoff
http://www.hoff.ol.no/