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

alter table lots_of_columns 
  add column all_columns varchar(255) not null default '',
  add unique index unique_constraint (all_columns);

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

alter table lots_of_columns 
  add column all_columns varchar(255) generated always as (
  concat(c1,'-',c2,'-',c3,'-',c4,'-',c5,'-',c6,'-',c7,'-',c8,'-',c9,'-',c10,'-',
    c11,'-',c12,'-',c13,'-',c14,'-',c15,'-',c16,'-',c17,'-',c18,'-',c19,'-',c20)
  )
  stored not null,
  add unique index unique_constraint (all_columns);

In this example we need the column to be STORED so we can add the unique index on it. The difference between VIRTUAL and 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 INSERT and 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

mysql > insert into lots_of_columns (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20) 
    -> values (1,2,3,4,5,6,7,8,9,10,'a','b','c','d','e','f','g','h','i','j');
Query OK, 1 row affected (0.01 sec)

mysql > insert into lots_of_columns (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20)
    -> values (1,2,3,4,5,6,7,8,9,10,'a','b','c','d','e','f','g','h','i','j');
ERROR 1062 (23000): Duplicate entry '1-2-3-4-5-6-7-8-9-10-a-b-c-d-e-f-g-h-i-j' for key 'unique_constraint'

mysql > insert into lots_of_columns (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20) 
    -> values (1000,2,3,4,5,6,7,8,9,10,'a','b','c','d','e','f','g','h','i','j');
Query OK, 1 row affected (0.00 sec)

mysql > update lots_of_columns set c1 = 1 where c1 = 1000;
ERROR 1062 (23000): Duplicate entry '1-2-3-4-5-6-7-8-9-10-a-b-c-d-e-f-g-h-i-j' for key 'unique_constraint'
mysql > 

Next I’ll try this using MariaDB virtual columns, and I will follow that up with another blog post.