Schema optimization and indexing

MySQL has a lot of types that use a single bit to store data. Regardless of the underlying storage format and operations, all of these types are technically string types.

BIT

In the version before 5.0, bit is just equivalent to tinyint. But after 5.0, it's already a very different type of data with some features. What we're talking about here is the newly added feature.

You can use the bit column to store one or more true/false values. The bit (1) defines a field containing 1 bits, and the bit (2) stores 2 bit. So on.

The maximum bit length is 64bits.

With different storage engines, bit characteristics are different. MyISAM to store these columns as a whole. So 17 separate bit columns will only store 17bits (assuming that there are no null values for each column). MyISAM requires about 3 bytes to store them. Other storage engines, such as memory and InnoDB. Stores each column with the smallest integer type sufficient to store these bits. So you can't save the storage space you use.

MySQL regards bit as String type. is not a numeric type. When you get the value of a bit (1), the result is a string, but the string is binary 0 or 1, remember not to be 0 or 1 of ASCII. However, if you get a number, the result is converted. Be sure to remember this if you want to make a comparison. For example, if you store B ' 00111001 ' (this equivalent to 57) to bit (8) and then get it. You will get a string containing a character code of 57. ASCII character code is 9, but in a digital environment, you get a value of 57.

mysql> CREATE TABLE bittest(a bit(8));

mysql> INSERT INTO bittest VALUES(b'00111001');

mysql> SELECT a, a + 0 FROM bittest;

+------+-------+

| a | a + 0 |

+------+-------+

| 9 | 57 |

+------+-------+

This is confusing, so we recommend that you use the bit type carefully. For most applications, avoid using this type as much as possible.

If you want to store true/false with a bit. Another option is to use a nullable char (0) column. This column can store null, or it can store an empty string.

SET

If you want to store a lot of true/false. Consider using the set data type to consolidate many columns into one. It packs a series of bit bits. and is very efficient for storage. And there's MySQL. There are some such as find_in_set () and field () functions that can easily use the SET type. The main drawback is the need to change the table definition. You need to use ALTER TABLE, which is much more than a large table. In general, you cannot use indexes to find set columns.

Bitwise operations on a whole series

Another principle for set is to use an integral type. As an example, you can pack 8bits into a tinyint and manipulate them using bitwise operations. Defining constants for each bit in the application code can make these operations simpler.

The main advantage of this approach is that you can change the enumeration values in the field without using alter TABLE. The disadvantage is that the written statement is difficult to understand. Some people are very accustomed to bit arithmetic while others are not. So it's a personal hobby to use the technology.

One of the following examples is about permission control. The values for each bit or set element are Can_read, Can_write, or Can_delete. If you use a set type, then MySQL stores a bit and a value mapping. If you're storing the plastic, you're going to store the mapping in the application code.

Here is an example of set

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('CAN_READ', perms);
+---------------------+
| perms |
+---------------------+
| CAN_READ,CAN_DELETE |
+---------------------+

If you use an orthopedic, you may write the following code

mysql> SET @CAN_READ := 1 << 0,
-> @CAN_WRITE := 1 << 1,
-> @CAN_DELETE := 1 << 2;
mysql> CREATE TABLE acl (
-> perms TINYINT UNSIGNED NOT NULL DEFAULT 0
-> );
mysql> INSERT INTO acl(perms) VALUES(@CAN_READ + @CAN_DELETE);
mysql> SELECT perms FROM acl WHERE perms & @CAN_READ;
+-------+
| perms |
+-------+
| 5 |
+-------+

We use variables, but you can replace them with the code in your application.