Duplicate Key in SQL

Discussion in 'Spigot Plugin Development' started by _xXProDudeXx_, May 3, 2017.

  1. Hello Spigotiers!

    I am currently having errors while trying to insert players into a database.
    I tried to add a 'ON DUPLICATE KEY' check but I can't figure out how it properly works.

    This is the statement I tried:
    Code (Text):
    sql.query(String.format("INSERT INTO stats(uuid, name, combatpoints, ip) VALUES ('%s', '%s', %s, '%s') ON DUPLICATE KEY 1=1;", uuid, name, tokens, IP));
    Regards,

    ~Wouter
     
  2. With 'ON DUPLICATE KEY' you can specify the behavior when an entry with an already existing primary key (in your case probably uuid [and maybe name]) is inserted into the table. The syntax is basically the same as for UPDATE but without the selecting part:
    Normal UPDATE statement:
    Code (Text):
    UPDATE stats SET combatpoints=combatpoints+1 WHERE uuid=<uuid>;
    Remove the redundant stuff: UPDATE stats SET combatpoints=combatpoints+1 WHERE uuid=<uuid>

    Combined with INSERT and ON DUPLICATE KEY:
    Code (Text):
    INSERT INTO stats(uuid, name, combatpoints, ip) VALUES ('%s', '%s', %s, '%s')
      ON DUPLICATE KEY UPDATE combatpoints=combatpoints+1
     
  3. @Coww and if I want to keep the combatpoints the same, then I can just enter combatpoints=combatpoints
    ?
     
  4. Yep, that would be one way to do it.

    Alternatively you could
    1. before inserting, check if the player already exists in the database
    2. handle the error that MySql returns (#1062 - Duplicate entry '...' for key 'PRIMARY') when trying to insert with a duplicate key
    3. use INSERT IGNORE to suppress the error
    4. (as you said) do something trivial with ON DUPLICATE KEY; e.g. ON DUPLICATE KEY UPDATE uuid=uuid
     
    • Agree Agree x 1
  5. Just felt like commenting that you could always do a check for if the table contains a value and use either UPDATE or INSERT based on that. Pretty sure it's been mentioned, but why not say it again? ;)
     
  6. Frankly, the only efficient way of doing this would either be ON DUPLICATE KEY UPDATE or INSERT IGNORE (both INSERT + error check and SELECT + INSERT/UPDATE might require 2 queries, which makes it an inefficient approach - on top of the race condition SELECt + INSERT/UPDATE introduces).

    As for INSERT IGNORE, it is not a recommended approach since it'll silence any error that occurs, not just duplicate entry errors.

    Code (Text):
    Server A: SELECT * FROM table WHERE id = 1
    Server B: SELECT * FROM table WHERE id = 1
    // Both servers get no results
    Server A: INSERT INTO table VALUES('bar')
    Server B: INSERT INTO table VALUES('bar')
    // Server B now receives a duplicate entry error
     
    • Like Like x 1