Curtis Maurand wrote:
:-)<SNIP>
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.
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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
