List:MySQL and Java« Previous MessageNext Message »
From:Pål Arne Hoff Date:April 22 2002 1:20pm
Subject:Re: boolean values
View as plain text  
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

boolean valuesRobert I Graham22 Apr
  • Re: boolean valuesPål Arne Hoff22 Apr