MySQL Duplicate Values

Discussion in 'Spigot Plugin Development' started by tarpers, Apr 28, 2017.

  1. Hello,
    Most of my code works fine, however when a player joins and I try and load his/her data the table adds a new row (Even if one exists for the user).
    Iv'e tried creating a "Name" row and comparing that rather than a UUID, but that also does not seem to work..
    Code:
    Code (Text):

    //Creating DB code
    statement.executeUpdate("CREATE TABLE IF NOT EXISTS PlayerT(UUID varchar(36), time int, b2 int,b3 int,b4 int)");

    //Insert statement
    private static final String INSERT = "INSERT INTO PlayerTVALUES(?,?,?,?,?) ON DUPLICATE KEY UPDATE uuid=?";

    //Insert code
    PreparedStatement insert = connection.prepareStatement(INSERT);


    insert.setString(1, p.getUniqueId().toString());
    insert.setInt(2, 0);
    insert.setInt(3, 0);
    insert.setInt(4, 0);
    insert.setInt(5, 0);
    insert.setString(6, p.getUniqueId().toString());
    insert.execute();
     

    Thank you,
     
  2. ON DUPLICATE KEY UPDATE will only trigger if an insert of a UNIQUE key will cause a duplicant. Make your UUID column UNIQUE.

    Also,
    • It's always 36 chars, so the CHAR type fits the column better
    • UUID is a MySQL function, so it's advised to use a different column name (f.e. unique_id)
     
    • Agree Agree x 1
  3. Okay thankyou, I have set it as 36 CHAR and renamed the UUID field to unique_id
    As for making the unique_id column unique, is there a way to do this when creating the Table? Or after creating the table doing something like this work?:
    Code (Text):

    ALTER TABLE PlayerT ADD CONSTRAINT unique_id UNIQUE(unique_id
     
    Thanks a lot for the reply!
     
    #3 tarpers, Apr 28, 2017
    Last edited: Apr 28, 2017