How to insert ignore with 100+ input values

Question

i'm learning Java hibernate, found an example of "insert ignore",

Query query = getSession().createSQLQuery("INSERT IGNORE INTO TABLA (ID, VAR) VALUES (:id, :var)");
UUID id = UUID.randomUUID();
query.setParameter("id", id);
String var = "abcde";
query.setParameter("var", var);
query.executeUpdate();

This will "insert ignore" 1 entry. I may have 100+ var as input, but only 1 or 2 will not be in the existing table, i don't know which ones.

If i set Parameters of 100 of values with a loop, it may work. But is there a better may ? given i may just need to insert 1 or 2 of them really.

Or i should ask this: given a list of 100 var string, find out the a few not in existing table, without using loop.

Take a look at: https://stackoverflow.com/a/25480573/5066625 — Miroslav Glamuzina, Mar 09 '19 at 01:07
Just to be clear, you use the loop to build a single query, right? You don't execute 100 queries? — Strawberry, Mar 09 '19 at 08:50
@Strawberry yes i expect 1 query if it's possible, how to do it is my question — user3552178, Mar 09 '19 at 21:02

score 0 · Answer 1 · answered Mar 09 '19 at 02:22

0

You could use the MySQL ON DUPLICATE KEY UPDATE feature.

For example:

INSERT INTO TABLA (ID, VAR) VALUES (1, 1), (2, 2), ... ON DUPLICATE KEY UPDATE ID=ID;

I'm not familiar with java or hibernate, hence my answer being a raw query. If you need help figuring out the exact syntax, this question might be a good resource for it.

Also, generally speaking, ON DUPLICATE KEY UPDATE is much safer than INSERT IGNORE because the latter will ignore all errors, while the former will only ignore duplicate inserts.

Hope this helps!

answered Mar 09 '19 at 02:22

Lord Elrond

13,430
7
40
80

my concern is, the INSERT string is so long if i have 100 vars and uuid, won't this hit some max string length ? – user3552178 Mar 09 '19 at 02:45
I don't think so. I run a similar query that is roughly 13000 bytes long, so I don't think it will matter. – Lord Elrond Mar 09 '19 at 03:03
I think I'd try to find a way to use the values option with this solution – Strawberry Mar 10 '19 at 02:17
@Strawberry what do you mean? – Lord Elrond Mar 10 '19 at 03:06

How to insert ignore with 100+ input values

1 Answers1