Abs wrote:
hi i know this has been discussed before, storing bools in the right column type. in the case of enum as: enum("N","Y") or "n","y", etc. it might be easy to read when u're looking at the database table itself, but if u had to communicate this properly to other programmers designing a front-end, they wouldn't be checking for BOOLS, rather for: if ($value=="Y") { } else { } (add to this, the case of string or char, which would matter more when using a C program or any other language that doesn't type cast as per the convenience of the statement)
is there any combination that would facilitate using it as just: if ($value) { //true } else { //false } ?
Yes. I declare the column as tinyint, then use 0 for false and 1 for true. This works well because mysql, perl, php, etc. treat 0 as false and 1 (or any non-zero value) as true. For example:
mysql> describe testa; +-------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+----------------+ | a | int(11) | | PRI | NULL | auto_increment | | name | char(10) | YES | | NULL | | | flag | tinyint(4) | YES | | NULL | | +-------+------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
mysql> select * from testa; +---+----------+------+ | a | name | flag | +---+----------+------+ | 1 | Smith | 1 | | 2 | Jones | 0 | | 5 | Williams | 0 | | 6 | Simpson | 1 | +---+----------+------+ 4 rows in set (0.00 sec)
mysql> select * from testa where flag; +---+---------+------+ | a | name | flag | +---+---------+------+ | 1 | Smith | 1 | | 6 | Simpson | 1 | +---+---------+------+ 2 rows in set (0.00 sec)
mysql> select * from testa where not flag; +---+----------+------+ | a | name | flag | +---+----------+------+ | 2 | Jones | 0 | | 5 | Williams | 0 | +---+----------+------+ 2 rows in set (0.00 sec)
i think enum(NULL, "") is the only option since a NULL would imply false or 'not true' in most languages. the second value could be "1" for clarity (as opposed to empty strings). but i was looking for a better option. checking for isnull, etc. is almost the same problem as the first case, which is easier to read if we had to.
thanks abs
I don't think this is a good idea. In SQL, NULL does not mean false, it means "unknown" or "no value". NULL is treated as neither true nor false. For example:
mysql> update testa set flag=null where a=5; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from testa; +---+----------+------+ | a | name | flag | +---+----------+------+ | 1 | Smith | 1 | | 2 | Jones | 0 | | 5 | Williams | NULL | | 6 | Simpson | 1 | +---+----------+------+ 4 rows in set (0.00 sec)
mysql> select * from testa where flag; +---+---------+------+ | a | name | flag | +---+---------+------+ | 1 | Smith | 1 | | 6 | Simpson | 1 | +---+---------+------+ 2 rows in set (0.00 sec)
mysql> select * from testa where not flag; +---+-------+------+ | a | name | flag | +---+-------+------+ | 2 | Jones | 0 | +---+-------+------+ 1 row in set (0.00 sec)
As you can see, the row with flag=NULL was not returned in either case. (This is why I usually use "tinyint not null" for boolean, unless I'm really sure I want to allow this.)
Of course, how this is handled in your external programming language is no doubt dependent on the language and the method of interfacing with mysql.
In any case, it seems to me that the value of using an enum column for boolean is to give the values human-readable names, at the cost of a little extra testing. Once you drop the human-readable values, I see no advantage of using enum over using tinyint.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
