Add a new value to a column of data type enum in MySQL?

MySQLMySQLi Database

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 − 0

Check 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)
raja
Published on 01-Feb-2019 09:50:33
Advertisements