Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
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
Add a new value to a column of data type enum in MySQL?
You can add a new value to a column of data type enum using ALTER MODIFY command.
If you want the existing value of enum, then you need to manually write the existing enum value at the time of adding a new value to column of data type enum.
The syntax is as follows −
ALTER TABLE yourTableName MODIFY COLUMN yourEnumColumnName
ENUM('yourExistingValue1','yourExistingValue2',........N,'yourNewValue1',’yourNewValue2’,.....N);To understand the above syntax, let us create a table. The query to create a table is as follows −
mysql> create table AddingNewValueToEnumColumn
-> (
-> Id int NOT NULL AUTO_INCREMENT,
-> Colors ENUM('RED','GREEN','BLUE'),
-> PRIMARY KEY(Id)
-> );
Query OK, 0 rows affected (0.64 sec)To check the DDL of the above table, use the show create command. The query is as follows −
mysql> show create table AddingNewValueToEnumColumn\G
The following is the output −
*************************** 1. row ***************************
Table − AddingNewValueToEnumColumn
Create Table − CREATE TABLE `addingnewvaluetoenumcolumn` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Colors` enum('RED','GREEN','BLUE') DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci
1 row in set (0.00 sec)Look at the enum column ‘Colors’ with the value 'RED','GREEN','BLUE' and if you want another value, then use the ALTER command as discussed above.
Now, let us add new values i.e. 'YELLOW','ORANGE','PINK'. The following is the query to add a new value to enum column ‘Colors’ −
mysql> ALTER TABLE addingnewvaluetoenumcolumn MODIFY COLUMN Colors
-> ENUM('RED','GREEN','BLUE','YELLOW','ORANGE','PINK');
Query OK, 0 rows affected (0.17 sec)
Records − 0 Duplicates − 0 Warnings − 0Check the description of the table to ensure that the values are added or not. The syntax is as follows −
DESC yourTableName;
Check the description of the table ‘addingnewvaluetoenumcolumn’ using DESC −
mysql> desc AddingNewValueToEnumColumn;
The following is the output −
+--------+-----------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------------------------------------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| Colors | enum('RED','GREEN','BLUE','YELLOW','ORANGE','PINK') | YES | | NULL | |
+--------+-----------------------------------------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
17K+ Views
