Re: UPSERT with non-unique index

On 02.08.2020 18:55, Stanimir Stamenkov wrote:
> 
> I need to implement concurrent-safe UPSERT
...
> Traditional INSERT ... ON DUPLICATE KEY [1] doesn't work for me
...
> There's an index:
> 
>     CREATE INDEX ON table (col3, col4, col5)
> 
> It is non-unique as there are legacy data that does not allow me to declare
> it unique.

That is the point where I would solve it.

1. add a new column `is_legacy_data`, i.e. TINYINT DEFAULT 0 that will be 0
for all new columns

2. for legacy columns with (col3, col4, col5) being not unique, set the new
column to a value that makes (col3, col4, col5, `is_legacy_data`) unique

3. add the unique index on (col3, col4, col5, `is_legacy_data`)

4. be happy with INSERT ... ON DUPLICATE KEY ...