List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:March 3 2004 7:31pm
Subject:Re: PgSQL vs MySQL
View as plain text  
Curtis Maurand wrote:
> :-)
> 
> someflag enum('TRUE','FALSE');
> 
> Not quite boolean, but it works.
> 
> Curtis
> 
> On Wed, 3 Mar 2004, Mark Warner wrote:
> 
> 
>>The thing which bothers me most about MySQL is the lack of a proper 
>>boolean. I don't like having to abstract a tinyint(1) into true or 
>>false. As much of my work involves building applications with Yes or No 
>>questions, I think I am switching to PostgreSQL.
<SNIP>

To my mind, a boolean is something that can be used in a boolean 
context.  That is, if flag is a boolean, the following should work as 
expected:

   SELECT * FROM mytable WHERE flag;     # rows with flag = TRUE
   SELECT * FROM mytable WHERE NOT flag; # rows with flag = FALSE

If you have to compare the column's value to something, it isn't really 
a boolean value.

MySQL, like most programming languages, treats 0 as FALSE and any other 
number as TRUE.  That means that boolean expressions are evaluated 
numerically and compared to 0.

In a numeric context, ENUM columns return the value's position in the 
list of allowed values, starting with 1.  This means that with the 
definition

   someflag enum('TRUE','FALSE')

someflag evaluates as 1 when it is 'TRUE' and 2 when it is 'FALSE', both 
of which are TRUE in boolean context.  Hence, you cannot use someflag in 
boolean context and get the expected results.  In other words, someflag 
looks like a boolean if you view the data, but doesn't behave as a 
boolean in queries.

I don't quite know what Mark Warner means by "abstract a tinyint(1) into 
true or false," but I personally find using tinyint for boolean to be a 
simple solution.  I define "someflag TINYINT", then set it to 0 for 
FALSE and 1 (or any other number) for true.  Of course, if you're not a 
programmer it won't look like a boolean when viewing the data (is that 
what you don't like, Mark?), but it will behave as one.

With tinyint you get some added flexibility, which may be an advantage, 
depending on your application.  For example, if I store the number of 
children a person has in the tinyint kids, I can use kids in boolean 
context:

   SELECT * FROM persontable WHERE kids;      #people who have children
   SELECT * FROM persontable WHERE NOT kids;  #people who don't

One more advantage of tinyints is that, with common programming 
languages, they continue to function as booleans in your application.

Another option is to take advantage of the special error value in every 
ENUM.  	If you insert a wrong value, mysql replaces it with '', which is 
always 0 in numeric context.  So, you could define

   someflag ENUM ('TRUE', 'T');

If you insert 'FALSE' or 'F' (or 'false' or 'f') into someflag, it will 
get the error value, 0 or ''.  When you view the data, you will see 
'TRUE' or 'T' for TRUE values and blanks for FALSE, and someflag will 
behave as boolean in queries.

If your values are 'Y' and 'N', then, strictly speaking, you don't have 
a boolean unless you've defined which is TRUE and which is FALSE. 
Assuming you want to treat 'Y' as TRUE, you could define

   yesflag ENUM ('YES', 'Y');

Inserting 'YES' or 'Y' (or 'yes' or 'y') will get you the corresponding 
entries, which are TRUE in boolean context.  Inserting 'NO' or 'N' (or 
'no' or 'n') will get you blanks, which are FALSE in boolean context.

Michael

Thread
PgSQL vs MySQLMark Warner3 Mar
Re: PgSQL vs MySQLdavid.best3 Mar
  • Re: PgSQL vs MySQLMark Warner3 Mar
    • Re: PgSQL vs MySQLCurtis Maurand3 Mar
      • Re: PgSQL vs MySQLRay3 Mar
      • Re: PgSQL vs MySQLMichael Stassen3 Mar
        • Re: PgSQL vs MySQLKevin Williams3 Mar
        • Re: PgSQL vs MySQLCurtis Maurand3 Mar
  • Re: PgSQL vs MySQLDavid Griffiths3 Mar
    • RE: PgSQL vs MySQLJames Kelty3 Mar
    • RE: PgSQL vs MySQLJeremy Smith3 Mar
      • Re: PgSQL vs MySQLKaarel13 Mar
  • Re: PgSQL vs MySQLKaren Abgarian3 Mar
  • Re: PgSQL vs MySQLDavid Griffiths3 Mar
    • Re: PgSQL vs MySQLMark Warner3 Mar
Re: PgSQL vs MySQLEric @ Zomething3 Mar
  • Re: PgSQL vs MySQLMartijn Tonies3 Mar
Re: PgSQL vs MySQLDavid Griffiths3 Mar
Re: PgSQL vs MySQLJeremy Zawodny3 Mar