sql - What is the alternative for generated column in MySQL 5.6

Question

I have a MySQL alter statement

ALTER TABLE `employee`
ADD `employee_name_generator` CHAR(20) GENERATED ALWAYS AS
   ?(COALESCE(concat(`employee_name`), '^')) VIRTUAL;

This is needed for adding employee_name_generator in a unique constraint. This works fine in MySQL 5.7 onwards. But the environment where I need this has MySQL 5.6. Is there an alternative for MySQL 5.6?

Answer 1

If your earlier version of MySQL does not support generated columns, then you'll have to compute that column at the time you query. One option would be a view:

CREATE VIEW yourView AS (
    SELECT *, COALESCE(CONCAT(employee_name, '^')) AS employee_name_generator
    FROM employee
)

MySQL does not support materialized views (directly). So, if you really needed the behavior of a materialized view, you would have to do something like create a temporary table using the select logic in the view I gave above.