TINYINT vs ENUM(0, 1) for boolean values in MySQL

Which one is better, Tinyint with 0 and 1 values or开发者_JAVA技巧 ENUM 0,1 in MyISAM tables and MySQL 5.1?

---

You can use BIT(1) as mentioned in mysql 5.1 reference. i will not recommend enum or tinyint(1) as bit(1) needs only 1 bit for storing boolean value while tinyint(1) needs 8 bits.

---

My research shows that BIT(1) is a synonym for TINYINT(1) for versions of MySQL before 5.0.3.

MySQL versions after 5.0.3 change how the BIT datatype works. It is no longer a synonym for TINYINT and is the only data type that allows you to store anything in less than one byte.

This datatype may be preferrable to using TINYINT or ENUM. I plan on testing to see which is fastest and the space usage of the three on my blog. There is a link at the bottom if you care to see the size and speed results. Testbed: crummy consumer grade Pentium III box running OpenBSD and MySQL. (With a slower DB dev box, you can really feel the effects of bad code. Also, differences between test queries are more discernible. Alternatively, try using a VM with barely enough resources allocated.)

The MySQL Official Documentation.

• v5.1 - http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html
• v5.6 - http://dev.mysql.com/doc/refman/5.6/en/numeric-type-overview.html

Baron Schwartz has this to say about it.

http://www.xaprb.com/blog/2006/04/11/bit-values-in-mysql/

---

I'd suggest the ENUM is preferable because it makes clear what is expected; if it detracts from performance in any measurable way I would be very surprised. To make a tinyint do this work would require CHECK a constraint on the column; none of the MySQL storage engines currently support this.

---

Enum, in a way gives a "hint" for developers or programmers. But usually, it's better to handle it programmatically. So whether it is ENUM(0,1), BIT(1) AND TINYINT(1), all using 1 byte, it would be better, in most cases, handled on the client side, rather than sending 2 in bit(1) or enum(0,1) to the server and then the server would return an error that you will have to handle anyways - uses more resources (network + server CPU + client CPU x 2)

0 usually means false, 1 true.

---

For the best performance and space requirements you should collect your boolean values and save them in the same TINYINT. Eg. Save up to 8 boolean values in a TINYINT. 16 boolean values in a SMALLINT etc. Both BIT(1) and ENUM uses at least 1 byte BIT(M) - approximately (M+7)/8 bytes see: https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html. So if you are storing 1 boolean value I would use TINYINT as it has the same overhead as BIT and ENUM but gives you the option to store 7 more boolean values later if you need.