MySQL data types int versus enum?

Nishtha Thakur

Answered 9h 42m ago

If you already know that only some of the values is needed then the best solution is to use ENUM data types. ENUM is more restrictive.

If you do not know about the vaulues, then you need to use TINYINT UNSIGNED data type. TINYINT UNSIGNED is less restrictive.

Let us implement the ENUM data type, if you want to store only 10,20,30. Following is the query −

mysql> create table DemoTable
   (
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Number ENUM('10','20','30')
   );
Query OK, 0 rows affected (0.24 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable(Number) values('10');
Query OK, 1 row affected (0.07 sec)

mysql> insert into DemoTable(Number) values('20');
Query OK, 1 row affected (0.06 sec)

mysql> insert into DemoTable(Number) values('30');
Query OK, 1 row affected (0.06 sec)

mysql> insert into DemoTable(Number) values('50');
ERROR 1265 (01000): Data truncated for column 'Number' at row 1

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+----+--------+
| Id | Number |
+----+--------+
|  1 | 10     |
|  2 | 20     |
|  3 | 30     |
+----+--------+
3 rows in set (0.00 sec)

Here is the implementation of TINYINT UNSIGNED. Let us create a new table −

mysql> create table DemoTable
   (
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Number tinyint unsigned
   );
Query OK, 0 rows affected (0.23 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable(Number) values(100);
Query OK, 1 row affected (0.07 sec)

mysql> insert into DemoTable(Number) values(50);
Query OK, 1 row affected (0.17 sec)

mysql> insert into DemoTable(Number) values(60);
Query OK, 1 row affected (0.06 sec)

mysql> insert into DemoTable(Number) values(70);
Query OK, 1 row affected (0.07 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+----+--------+
| Id | Number |
+----+--------+
|  1 |    100 |
|  2 |     50 |
|  3 |     60 |
|  4 |     70 |
+----+--------+
4 rows in set (0.00 sec)