CODEWITHSUNDEEP
mysqlsqldatabasemysql-5.6virtual-column
user9920500
user9920500

Reputation: 705

What is the alternative for generated column in MySQL 5.6

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?

Upvotes: 3

Views: 3469

Answers (2)

cbishop
cbishop

Reputation: 63

You can use triggers as a workaround and achieve similar results.

ALTER TABLE `employee`
ADD `employee_name_generator` CHAR(20);

delimiter //
create trigger trig_create_employee_name_generator before insert on `employee`
for each row
begin
   set NEW.`employee_name_generator` = COALESCE(concat(NEW.`employee_name`), '^');
end
//

If you will also be updating the employee_name field after the insert, you'll also need a trigger on update:

delimiter //
create trigger trig_update_employee_name_generator before update on `employee`
for each row
begin
   set NEW.`employee_name_generator` = COALESCE(concat(NEW.`employee_name`), '^');
end
//

Keep in mind that in MySQL 5.6, per table you get only one trigger for before insert and one for update, so if you need more than one generated column, you would chain the set statements into the same trigger between "begin" and "end".

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

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.

Upvotes: 5

Related Questions