Databases Reference
In-Depth Information
+------+-------+
| a | a + 0 |
+------+-------+
| 9 | 57 |
+------+-------+
This can be very confusing, so we recommend that you use
BIT
with caution. For
most applications, we think it is a better idea to avoid this type.
If you want to store a true/false value in a single bit of storage space, another option
is to create a nullable
CHAR(0)
column. This column is capable of storing either the
absence of a value
(NULL)
or a zero-length value (the empty string).
SET
If you need to store many true/false values, consider combining many columns into
one with MySQL's native
SET
data type, which MySQL represents internally as a
packed set of bits. It uses storage efficiently, and MySQL has functions such as
FIND_IN_SET()
and
FIELD()
that make it easy to use in queries. The major drawback
is the cost of changing the column's definition: this requires an
ALTER TABLE
, which
is very expensive on large tables (but see the workaround later in this chapter). In
general, you also can't use indexes for lookups on
SET
columns.
Bitwise operations on integer columns
An alternative to
SET
is to use an integer as a packed set of bits. For example, you
can pack eight bits in a
TINYINT
and manipulate them with bitwise operators. You
can make this easier by defining named constants for each bit in your application
code.
The major advantage of this approach over
SET
is that you can change the “enu-
meration” the field represents without an
ALTER TABLE
. The drawback is that your
queries are harder to write and understand (what does it mean when bit 5 is set?).
Some people are comfortable with bitwise manipulations and some aren't, so
whether you'll want to try this technique is largely a matter of taste.
An example application for packed bits is an access control list (ACL) that stores per-
missions. Each bit or
SET
element represents a value such as
CAN_READ
,
CAN_WRITE
, or
CAN_DELETE
. If you use a
SET
column, you'll let MySQL store the bit-to-value mapping
in the column definition; if you use an integer column, you'll store the mapping in your
application code. Here's what the queries would look like with a
SET
column:
mysql>
CREATE TABLE acl (
->
perms SET('CAN_READ', 'CAN_WRITE', 'CAN_DELETE') NOT NULL
->
);
mysql>
INSERT INTO acl(perms) VALUES ('CAN_READ,CAN_DELETE');
mysql>
SELECT perms FROM acl WHERE FIND_IN_SET('AN_READ', perms);
+---------------------+
| perms |
+---------------------+
| CAN_READ,CAN_DELETE |
+---------------------+
If you used an integer, you could write that example as follows:
