Storing and retrieving boolean values. MySQL

Question

MySQL

There are 4 switches (on/off), they will be sampled, for example, the 1st is on, the 2nd is off, the 3rd is not important, the 4th is on.
How best to organize data storage, the decision of 4 TINYINT fields immediately came to mind, but as I understand there are more competent solutions? for example, storing four values ​​\u200b\u200bin one field (1101), the question is how to choose, through like? how more efficient?
What is the best solution to choose, poke your nose please where to read?

Answer 1

I would try the following: store all 4 bits together in one indexed field (let's call it bits) of type TINYINT UNSIGNED (either BIT(4) or SET).
And requests would be in the form:

SELECT * FROM table WHERE bits IN (...)

Where, instead of an ellipsis, those values ​​​​that satisfy the mask should be listed. For the mask you specified in the example, the request will take the form:

SELECT * FROM table WHERE bits IN (b'1001', b'1011')

In my opinion, with this approach, both space is saved and performance will be the best.

Answer 2

I am sure that the SET data type is suitable for the described task

Answer 3

If there are not many rows, then I would probably do this:
CREATE TABLE `test`.`switches` (
`id` INTEGER NOT NULL AUTO_INCREMENT,
`switches` CHAR(4) NOT NULL,
PRIMARY KEY (`id` )
);
Then your example (1 - enabled, 2 - disabled, 3 - don't care, 4 - enabled) turns into a query:
SELECT * FROM switches WHERE switches LIKE '10_1';

Answer 4

If I were you, I would use the BOOLEAN type (this is a synonym for TINYINT but with values ​​0 or 1 only).
You can build an index on them and the search will be carried out much faster than on a string with LIKE.
It will also be possible to easily expand the system by removing or adding new switches.

Answer 5

Is performance important? BOOLEAN
Is space saving important? SET

Answer 6

You are confusing. There are no space savings. In both my solution and SET, everything is stored in 1 byte. There is nowhere more compact. But the solution with 4 BOOLEAN fields takes 4 bytes.
I'm convinced that the solution I've given is also the most productive one I've come up with.
The speed advantage is achieved by using the index with the WHERE IN (...) clause. Exactly the same result should be if you use this construction with a field of type SET. I even initially wrote that the solution is acceptable for types, I quote:

TINYINT UNSIGNED (either BIT(4) or SET)

This is because they are all coercible to an integer type, and using each of them will actually produce the same value per byte.