How do I add more members to my ENUM - type column in MySQL?

MySQLMySQLi Database

You can use alter command. The syntax is as follows −

ALTER TABLE yourTableName
MODIFY COLUMN yourColumNam
enum(yourOldValue1,yourOldValue2,....N,yourNewValue1,yourNewValue2,....N);

To understand the above syntax, let us first create a table. The query to create a table is as follows −

mysql> create table EnumDemo
   -> (
   -> AllColors enum('RED','GREEN')
   -> );
Query OK, 0 rows affected (0.66 sec)

Now you have two members in AllColors of enum type. If you want to add more members to your enum type, use the following query −

mysql> alter table EnumDemo
-> modify column AllColors enum('RED','GREEN','YELLOW','BLUE');
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0

Let us check the structure of the table whether it is updated with new members or not. The query is as follows −

mysql> SHOW CREATE TABLE EnumDemo;

Output

+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EnumDemo | CREATE TABLE `enumdemo` (`AllColors` enum('RED','GREEN','YELLOW','BLUE') DEFAULT NULL ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci      |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Yes, we have two new members now i.e. YELLOW and BLUE.

raja
Published on 10-Jan-2019 16:18:09
Advertisements