Unable to do upsert with MySQL using diesel

[raintears]

I tried on_conflict but seems like it doesn't work for mysql? I'm getting no method named on_conflict when compiling. Any reasons for that?

[weiznich]

on_conflict is a postgresql specific method because it uses postgresql specific syntax.
As far as I'm aware of we do not support anything like this on mysql side.

[raintears]

Thanks for the clarification! So how then if we're using mysql, do an upsert?

We should be able to do something like:

INSERT INTO `item`
(`item_id`, items_in_stock)
VALUES( 'A', 27)
ON DUPLICATE KEY UPDATE
`items_in_stock` = `items_in_stock` + 27

[weiznich]

There are 2 possible ways here:

• Fall back to raw sql
• Implement the according diesel dsl. This could also be done outside of diesel, but this is something we are also interested to have inside of diesel. Basically you need to implement a mysql version of the diesel postgres upsert implementation.

[belsonheng]

I'm using replace_into but you can also use sql_query if you prefer.

[sgrif]

Note that ON DUPLICATE KEY UPDATE is unsafe and can lead to major issues, especially if using replication. For this reason, Diesel doesn't support it and does not intend to.

[orangesoup]

I understand ON DUPLICATE KEY UPDATE won't be supported, but... people who actually need the performance vs REPLACE when using MySQL would still want to use it. I'm trying to convert my app from using the "basic" mysql crate to use diesel, and this one is kind of a blocker atm.

In my case I would like to batch insert or update thousands of rows every second, how can I do that with diesel? The guide only says it's not supported. Well... I would gladly use raw SQL, but even then I can't really see a way to provide it a huge Vec and just run it. The documentation is really lacking tbf. Would any of you be so kind and provide an example how to do this?

An example query is something like this (simplified):

INSERT INTO table (x, y, z) VALUES (?, ?, ?), (?, ?, ?), ...
ON DUPLICATE KEY UPDATE x = x + VALUES(x)