- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
MySQL data types int versus enum?
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)
- Related Articles
- What is MySQL ENUM data type? What are the advantages to use ENUM data type?
- MyISAM versus InnoDB in MySQL?
- How to get int value from enum in C#?
- what are the different attributes of MySQL ENUM data type?
- How can I insert default value in MySQL ENUM data type?
- int(5) vs. int(10) in MySQL?
- Does SQL Server have an equivalent to MySQL's ENUM data type?
- Add a new value to a column of data type enum in MySQL?
- How many DATE data types are supported by MySQL?
- How Are MySQL ENUM values sorted?
- How many groups of data types are supported by MySQL?
- Difference between fundamental data types and derived data types
- MySQL - CAST DECIMAL to INT?
- MySQL ENUM column match for quoted values
- Set ENUM in MySQL for column values

Advertisements