enum or tinyint?

[Arthur Radulescu]

What is recommanded for a large database to use enum or tinyint? Should I
store active inactive or tinyint (1) with 0 and 1?

Thanks,
Arthur

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=myo...@freebsd.csie.nctu.edu.tw

[Michael Stassen]

Arthur Radulescu wrote:

> What is recommended for a large database to use enum or tinyint? Should I

> store active inactive or tinyint (1) with 0 and 1?
>
> Thanks,
> Arthur

What matters to you, space, speed, or ease of use? A tinyint and an enum
with just 2 values both take up one byte, so the space question is moot.
Internally, enum values are stored as ints (tinyints, in this case), so the
speed question is nearly moot. That is, while "WHERE status='active' may
look like a string comparison, 'active' is actually converted to an int
(position in the enum list) which is used for the comparison. So, the enum
has a miniscule amount of overhead (look up the position) then integer
comparisons just like the tinyint. I'd be surprised if you noticed a
difference, regardless of table size.

That leaves ease of use, which is a matter of preference and not, I think, a
table size issue. Personally, I like to use tinyints for boolean flags like
this. To me, they are simpler and self-documenting. With values 'active'
and 'inactive', I would probably define the column as "active TINYINT", then
use 1 for active and 0 for inactive. That way, I can simply write queries like

SELECT * FROM mytable WHERE active;

or

SELECT * FROM mytable WHERE NOT active;

On the other hand, there is nothing in the column definition to stop someone
from setting active to 3 (or 5, or 18...) in an insert or update, and those
wrong values are indistinguishable from 1 in my WHERE clauses above, so I
have to properly handle this possibility in my code.

The alternative would be to define the column as something like:

status ENUM('active', 'inactive')

In this case, active is 1 and inactive is 2, both of which are true, so you
have to explicitly compare them to values:

SELECT * FROM mytable WHERE status='active';

or

SELECT * FROM mytable WHERE status='inactive';

You are somewhat protected from bad values, however. Attempts to insert or
update status to values other than those defined won't raise an error, but
they will result in an empty string (position 0) being stored, which won't
match either of the above WHERE clauses. Another advantage is flexibility.
Should you ever decide you need a 3rd status, it would be easy to add it
to your ENUM.

Michael

[Harald Fuchs]

In article <408C1B57...@verizon.net>,
Michael Stassen <Michael...@verizon.net> writes:

> Arthur Radulescu wrote:

>> What is recommended for a large database to use enum or tinyint? Should I
>> store active inactive or tinyint (1) with 0 and 1?
>> Thanks,

> That leaves ease of use, which is a matter of preference and not, I

> think, a table size issue. Personally, I like to use tinyints for
> boolean flags like this. To me, they are simpler and
> self-documenting. With values 'active' and 'inactive', I would
> probably define the column as "active TINYINT", then use 1 for active
> and 0 for inactive. That way, I can simply write queries like

> SELECT * FROM mytable WHERE active;

> or

> SELECT * FROM mytable WHERE NOT active;

Even better, in this case you can use BOOL as the column type.
Although that's just a synonym of TINYINT, it makes the intended usage
clearer.

[Michael Stassen]

Harald Fuchs wrote:

<snip>

>
> Even better, in this case you can use BOOL as the column type.
> Although that's just a synonym of TINYINT, it makes the intended usage
> clearer.

I suppose, except that mysql (4.0.17, anyway) doesn't remember that you used
BOOL.

mysql> CREATE TABLE bt (flag BOOL);
Query OK, 0 rows affected (0.00 sec)

mysql> DESCRIBE bt;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| flag | tinyint(1) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE bt;
+-------+----------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------+
| bt | CREATE TABLE `bt` (
`flag` tinyint(1) default NULL
) TYPE=MyISAM |
+-------+----------------------------------------------------+
1 row in set (0.00 sec)

Which is probably just as well, I think. Otherwise, you might be surprised
to find that 13, for example, is a legal BOOL value.

Michael