modify enum values in migrations scripts

Question

Is there a correct and safe way to modify enum column type values? Add new or remove old.

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?

Answer 1

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

More examples are at tutorialspoint.com

EDIT

If you want it all sql then you need to write a procedure to do it which you call from your script. This can fetch the enum value from the information_schema.

I added a column test just for testing type enum with values 'a','b','c','d' to one of my tables.

Here's a function to demo the concept. To check what is returned by the select statement. Replace the TABLE_SCHEMA, TABLE_NAME and COLUMN_NAME values to suit.

CREATE DEFINER=`root`@`localhost` 
FUNCTION `Get_Def`(`New_Value` VARCHAR(40)) RETURNS LONGTEXT 
CHARSET utf8mb4 NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER
return (select COLUMN_TYPE 
        from information_schema.`COLUMNS` 
        where TABLE_SCHEMA = 'aklcity_directory' 
        and TABLE_NAME = 'entries' 
        and COLUMN_NAME = 'Test')

This returns

enum('a','b','c','d')

In your procedure you can get this value as a string (more accurately longtext). You can check if the new value exists. If not, you can add it in.

To add the value 'e' to it requires

ALTER TABLE `entries` CHANGE `Test` `Test` 
ENUM('a','b','c','d','e') 
CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;

Please alter to suit.