TINYINT vs ENUM: in a situation where there are only a few possible integer values?

Question

MySQL

There is a field "Speed ​​limit" in the table. Possible field values: 20, 30, 40, 50, 60, 70, 90, 100, 110, 120, 130.
What is the best type to choose for the "Speed ​​limit" field: TINYINT UNSIGNED or ENUM(20, 30, 40, 50, 60, 70, 90, 100, 110, 120, 130)?

Answer 1

Enum with numeric fields works in the way described in the manual, but this way ... Let's just say, it sometimes surprises.

mysql> create temporary table speedlimit (i int, lim ENUM('20', '30', '40', '50', '60'));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into speedlimit values (1, 60);
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> insert into speedlimit values (2, 5);
Query OK, 1 row affected (0.04 sec)

mysql> insert into speedlimit values (3, '60');
Query OK, 1 row affected (0.04 sec)

mysql> select * from speedlimit;
+------+------+
| i    | lim  |
+------+------+
|    1 |      |
|    2 | 60   |
|    3 | 60   |
+------+------+
4 rows in set (0.00 sec)

mysql> select * from speedlimit where lim > 20;
Empty set (0.00 sec)

mysql> select * from speedlimit where lim > '20';
+------+------+
| i    | lim  |
+------+------+
|    2 | 60   |
|    3 | 60   |
+------+------+
2 rows in set (0.00 sec)

If you understand why the behavior is the way it is, and know that this behavior won't surprise other developers, you can use it. An enum of up to 255 values ​​takes 1 byte, just like tinyint. But I don't recommend using enum with numeric values.

Answer 2

And if spaceships are added later? TINYINT might not be enough.
ENUM such in basis I too would not describe. Take the regular UNSIGNED INT.
Validation (imho) should be at the DTO/VO level.

Answer 3

Reflections aside - if the base size / memory size is critical, then, of course, the smaller the dimension, the better.
If we talk about performance, it turns out that a 64-bit processor in combination with a 64-bit OS works best with, SUDDENLY ... 64-bit integers. Reducing the bit depth to 32, 16 or 8 bits will either not give a performance increase at all, or even worsen performance.