0

I have this database at MySQL that has, among others, the following columns: Country, Level and Branch. I would like three new columns to this database indicating wether there's a comma (1) or not (0) on each of this three columns. Like this: example

I want these columns to be automatic, so new observations can be added to the table without the need of running the same script again and again to update the values shown there. I know how to do this in excel with IF functions, but I got stuck on MySQL. I have tried this:

alter table countries
add bol_coutnry int GENERATED ALWAYS AS (if(`COUNTRY` LIKE '%{$,}%',1));

among other variations of the same code but didn't even run. Any ideas? Thanks

Jn Coe
  • 75
  • 6
  • 1
    ```ADD bol_country INT GENERATED ALWAYS AS (LOCATE(',', `country`) > 0);```. PS. Storing multi-valued CSV is not good idea, this makes problematic the effective data processing. – Akina Sep 29 '20 at 05:30
  • 1
    Perhaps see https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Strawberry Sep 29 '20 at 07:10
  • Yes I'm aware of how badly designed this is. I have very little autonomy on the changes I can make to this database due to compatibility with previous (similarly badly designed) systems that use it. My hope is to properly fix this and other issues in the future – Jn Coe Sep 30 '20 at 05:08

1 Answers1

1

You can simply write a trigger to do the job.

CREATE DEFINER=`root`@`localhost` TRIGGER `countries_before_insert` BEFORE INSERT ON `countries` FOR EACH ROW BEGIN
    SET NEW.bot_country = IF(POSITION(',' IN NEW.Country) = 0, 0, 1);
    SET NEW.bot_level = IF(POSITION(',' IN NEW.Level) = 0, 0, 1);
    SET NEW.bot_branch = IF(POSITION(',' IN NEW.Branch) = 0, 0, 1);
END

It will auto populate these columns in your table. Make sure you do also write BEFORE UPDATE trigger too, so when changing a value should also update these 3 columns.

You can also use INSTR at place of POSITION

Dark Knight
  • 6,116
  • 1
  • 15
  • 37