Insert player into MySQL if he does not exist there

Discussion in 'Plugin Development' started by Max8801, Jun 8, 2016.

Thread Status:
Not open for further replies.
  1. Offline

    Max8801

    Hey guys,
    I am currently working on a plugin in which I need to insert a player into the MySQL database if he is not already in it. My table consists of 1 String column called Name (the player's name) and 2 Integer colums (they should both be set to 0 when the player is inserted). I want to do all of that in only one qery but I really have no idea how I can do it.
    Regards, Max
     
  2. Offline

    CoolDude53

    @Max8801 use an INSERT IGNORE query
     
  3. Offline

    bennie3211

    Why do you want this in one query? Just create a function called tableContainsPlayer(UUID uuid, String tableName) and check with that if the given tableName contains the uuid (or something that you use to store from a specific player, please don't use String names because players can change their names). The tableContainsPlayer gets from the specific tableName the info about a specific player and if it can get that value, the player is in the database/table(Name). If not, then use INSERT INTO statement to put them into the table.
     
  4. Offline

    Axanite

    The way I do it is using a ResultSet to select everything from the table where a certain factor is the same as something (e.g. a player's UUID) and use rs.next() to determine whether they are in the table or not. If rs.next() returns true then it means they're in the table and you can do whatever / nothing, depending on what the query is being used for. If it however returns false, you can just use an INSERT statement to add the player to the table.
     
  5. Offline

    CoolDude53

    @bennie3211 @Axanite Both of your suggested solutions are inefficient and completely unnecessary. SQL is robust enough to have built in query commands that account for problems like this. There are several different commands that you can use in a single query that check if there is a duplicate key, INSERT IGNORE tries to perform a regular insert command, but if it encounters a duplicate key, it simply does nothing. In other instances, you can use an ON DUPLICATE KEY command in an insert query to perform other actions such as updating when it encounters a duplicate key.
     
  6. Offline

    mythbusterma

    @CoolDude53

    There's no reason not to check first, in fact I would argue it's better. Queries that use INSERT IGNORE or ON DUPLICATE KEY are using MySQL specific syntax, making supporting something like SQLite much more difficult. If you perform the check first, you can use the queries with both types of server. Also, another query to the database isn't going to kill you, efficiency on such low volume data doesn't really matter that much.

    So no, it's not "completely unnecessary," it's a platform-independent way of accomplishing the same task.
     
  7. Offline

    CoolDude53

    @mythbusterma

    I didn't consider other db support, so that is a fair point that should be considered when making this sort of decision. Considering we know he is using MySQL and we don't know the frequency of his application, I stand that using MySQL specific queries like INSERT IGNORE or ON DUPLICATE KEY is still best. On a small scale, you are correct that the effects on efficiency won't be noticeable, but if it is something that is going to be repeatedly called (especially if you want to query several players at the same time), it would be noticeable.
     
Thread Status:
Not open for further replies.

Share This Page