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?

A bit. I would prefer option that works with SQL only. But it looks no one else will react so I accept your answer. Thank you. — tttpapi, Oct 18 '22 at 14:42

Rohit Gupta · Accepted Answer · 2022-10-19T04:55:28.997

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

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.

How does this answer the question *Is there a correct and safe way to modify enum column type?* or *Is there a way to just add value to existing values?*? — Ken White, Sep 25 '22 at 02:53
This allows you to get the current values, so you can tailor the script to suit. — Rohit Gupta, Sep 25 '22 at 04:45
The OP knows the current values - they've been included in the question, which was about how to *add new values*. — Ken White, Sep 25 '22 at 04:49
I am reading it differently, 2 people are running scripts, so the script needs to know the value at that time. And that is not the starting value. — Rohit Gupta, Sep 25 '22 at 06:18
The two statements that I included in my first comment are pretty clear. I don't know how you can read something that is very specific (and that I quoted) differently. — Ken White, Sep 25 '22 at 06:26
"One task wants to add orange", "Another wants to add peach". And the last one wins. The safe way to update them is to know the current status. I have shown how to get the current status. Now the OP is able to modify the script by reading the current value and updating to have the missing one either orange/peach. Do you really want me to delete my answer? — Rohit Gupta, Sep 25 '22 at 07:10

modify enum values in migrations scripts

1 Answers1

EDIT