modify enum values in migrations scripts

Question

Is there a correct and safe way to modify enum column type?

E.g.: I have ENUM ("apple", "banana")

I have 2 tasks that need to add value to the ENUM. 1 needs to add orange and second needs to add peach.

If I get migrations scripts, I will have:

ALTER TABLE example MODIFY COLUMN fruit ENUM("apple", "banana", "orange) NOT NULL

ALTER TABLE example MODIFY COLUMN fruit ENUM("apple", "banana", "peach) NOT NULL

I will end up only with values from the last executed SQL. Is there a way to just add value to existing values?

Rohit Gupta · Answer

You can use the show or description command.

show create table dl_stats

produces this on my system if I use print_r to show the row fetched from the database.

Array
(
    [Table] => dl_stats
    [Create Table] => CREATE TABLE `dl_stats` (
  `Ref` bigint(20) NOT NULL AUTO_INCREMENT,
  `Area` varchar(10) NOT NULL,
  `Name` varchar(80) NOT NULL,
  `WIN` bigint(20) NOT NULL DEFAULT 0,
  `AND` bigint(20) NOT NULL DEFAULT 0,
  `LNX` bigint(20) NOT NULL DEFAULT 0,
  `IOS` bigint(20) NOT NULL DEFAULT 0,
  `MOS` bigint(20) NOT NULL DEFAULT 0,
  `MSC` bigint(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (`Ref`),
  UNIQUE KEY `By_Name` (`Area`,`Name`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4
)

Once you have this in a variable in your language, you can parse it.

13.7.7.10 SHOW CREATE TABLE Statement

SHOW CREATE TABLE tbl_name

Shows the CREATE TABLE statement that creates the named table. To use this statement, you must have some privilege for the table. This statement also works with views.

From dev.mysql.com

modify enum values in migrations scripts

1 Answers