MySQL - What's the best way to store a static array size of TINYINT in a column

Question

I need to store an array of unsigned TINYINT in a MySQL table for each user. The array length is constant and I don't need to do any search or sorting in it. Only its values are changed over time. My goal is to have the values stored in a way that the data size remains as close as N x TINYINT for each line and hopefully readable. I was considering 2 solutions:

Solution 1:

| user_id | TINYINT_1 | TINYINT_... | TINYINT_N |

Solution 2:

| user_id | JSON array [TINYINT_1, TINYINT_..., TINYINT_N] |

The second seems cleaner as I don't need to give N useless names, but from what I understand I have have no control on the type of value used to store data in a JSON array and I'm afraid that it increase the final memory size way more than N x TINYINT per line. Is there way to control the type of values or some other smarter ways to do it?

Thanks for your advises.

Answer 1

One TINYINT takes one byte. The only way to ensure the storage of N TINYINTs is N x TINYINT bytes is to store them as a BINARY(N) up to N of 255, and BLOB if it's longer. That is, each TINYINT gets one byte in a binary string. That's not readable at all, but it is the most compact way to store it.

Because you would be responsible for interpreting this string byte-by-byte, there is no way it could be misinterpreted, or the elements treated as some other data type. That's entirely up to your code that unpacks the string.

MySQL does not have an array type (like for example PostgreSQL does). If you want the array of TINYINT to be stored in a readable fashion, you could store it as a string of hex digits, with two digits per TINYINT. This takes exactly twice the space of the BINARY(N) solution.

You can also store the numbers as a string of comma-separated text numbers in base 10, which is more readable, but takes more space.

You can also use JSON, which allows for an array of digits, but it takes even more space because it stores numbers in base 10, and there need to be [ ] array delimiters. And you already thought of the possibility that JSON allows arbitrary types for the array elements. MySQL supports JSON schema, but not automatically. You'd have to write a CHECK constraint. There's an example here: https://dev.mysql.com/doc/refman/8.0/en/json-validation-functions.html