Using MySQL 5.7 Generated Columns to Avoid ERROR 1070
When I first blogged about a potential workaround for MySQL error 1070, I received a suggestion in the comments that my workaround might be a useful trick with MariaDB virtual columns or MySQL 5.7 generated columns. Although I’ve already blogged about a simplified version of my original workaround, the arrival of the MySQL 5.7.7 release candidate inspired me to try an even simpler implementation using generated columns.
Read on for my findings.
In my original posts I created a table with 20 columns and added a new column to store the concatenation of all 20 columns so I could add a unique constraint on those columns. Here’s the DDL for that column:
1 2 3
The extra work involved with my previous implementations was setting that concatenated value, which could be done in application code, via triggers, or as part of a
LOAD DATA INFILE statement. In today’s example I’ll let the server do all of the work using a generated column, like this:
1 2 3 4 5 6 7
In this example we need the column to be
STORED so we can add the unique index on it. The difference between
STORED generated columns is described in the manual like this:
The VIRTUAL or STORED keyword indicates how column values are stored, which has implications for column use:
VIRTUAL: Column values are not stored, but are evaluated when rows are read, immediately after any BEFORE triggers. A virtual column takes no storage and consequently cannot be indexed.
STORED: Column values are evaluated and stored when rows are inserted or updated. A stored column does require storage space and can be indexed.
I ran the same
UPDATE tests on this table that I did in my original post, and they all worked properly, so this seems like a promising approach:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Next I’ll try this using MariaDB virtual columns, and I will follow that up with another blog post.