Mysql Generated Column Compued By Conditions On Other Columns

Question

Is it possible to create a generated MySQL column that runs a condition on other columns?

For example: column "a" - type boolean column "b" - type date generate column "c" that implements the logic:

if (a == false) || (a == true && b < NOW()) {
    return true;
} else {
    return false;
}

Have you read https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html ? — Bill Karwin, Jan 16 '18 at 21:58

score 2 · Answer 1 · answered Jan 16 '18 at 21:51

You can use IF/ELSE (https://dev.mysql.com/doc/refman/5.7/en/if.html) or CASE/WHEN (https://dev.mysql.com/doc/refman/5.7/en/case.html) statements in MySQL.

If you're writing a procedure, you can save the output of a query to a variable and check against that. If you aren't doing a procedure, you could do the validation inside of the statement and that should work.

Good luck!

score 0 · Answer 2 · answered Jan 16 '18 at 21:15

You can write scripts for databases. I've done it in Ruby but I'm sure there are plenty of others. You would write a script to create the table without the conditional column and then you would write logic to add a new column with values based on your conditions.

Mysql Generated Column Compued By Conditions On Other Columns

2 Answers2