On conflict do nothing for MySQL

[tp971]

Description

OnConflict::do_nothing() generates a syntactically incorrect query for MySQL/MariaDB:

something::Entity::insert(...)
    .on_conflict(OnConflict::new()
        .do_nothing()
        .to_owned()
    )
    .build(DbBackend::MySql)

returns something like:

INSERT INTO `something` (...) VALUES (...) ON DUPLICATE KEY DO NOTHING

which is not correct in MySQL/MariaDB. The correct syntax would be:

INSERT IGNORE INTO `something` (...) VALUES (...)

(cf. https://dev.mysql.com/doc/refman/8.1/en/insert.html and https://mariadb.com/kb/en/insert/)

Workarounds

Update the unique key on conflict:

something::Entity::insert(...)
    .on_conflict(OnConflict::new()
        .update_column(something::Column::Id)
        .to_owned()
    )
    .build(DbBackend::MySql)

Versions

0.12.1

[tyt2y3]

I don't think INSERT IGNORE should be used, as semantically it is different from ON DUPLICATE KEY. So ON DUPLICATE KEY UPDATE id=id is the way to go indeed.

Since SeaORM do know the PK of the entities, we are looking for ways to make it transparent.

[tyt2y3]

Now it's released in sea-query 0.31.0-rc.5, this can now be implemented in SeaORM