MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Curtis Maurand Date:March 3 2004 10:41pm
Subject:Re: PgSQL vs MySQL
View as plain text  
I know, I get it, I was trying for humor.


On Wed, 3 Mar 2004, Michael Stassen wrote:

> 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

Curtis Maurand

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