Insert Into Table If Two Columns Don't Exist Already

Question

Here is the code I'm using right now that gives a MySQL syntax error:

UUID uuid = player.getUniqueId();
    long uuid_m = uuid.getMostSignificantBits(), uuid_l = uuid.getLeastSignificantBits();
    String query = String.format(
            "INSERT INTO players (uuid_m, uuid_l, name, clan, xp, rank)" +
                    "SELECT * FROM SELECT CONCAT('%d', '%d', '%s', '%s', '%d', '%d') AS tmp" +
                    "WHERE NOT EXISTS (SELECT CONCAT(uuid_m, uuid_l) FROM players WHERE uuid_m = '%d' AND uuid_l = '%d')" +
                    "LIMIT 1;", uuid_m, uuid_l, player.getName(), "TestClan", 0, 0, uuid_m, uuid_l);

I wrote the code in Java, but I tried making it as readable as possible and I don't think there's really anything specific to Java itself in the code. The table in my database is called "players" and it has columns (uuid_m long, uuid_l long, varchar(50) name, varchar(50) clan, xp int, rank int). I've considered just using varchar(32) instead of splitting the UUID into two, but I did some calculations and that'll take 4 times as much space. I'm fairly new to MySQL, but I want to try and keep everything efficient.

Answer 1

First of all, SELECT * FROM SELECT is redundant, even if it works, which I doubt. Secondly you are trying to insert n columns into table but are only passing 1 - CONCAT will join the values into 1 column. You need to drop concat and provide full list of values to be inserted in both SELECTs (where the player exists and where he doesn't).

Answer 2

You have several issues

1. You are missing spaces at the end of each broken "line". For instance

   "INSERT INTO players (uuid_m, uuid_l, name, clan, xp, rank)" +
                                                              ^
   "SELECT CONCAT('%d', '%d', '%s', '%s', '%d', '%d') AS tmp" +
                                                            ^
   "WHERE NOT EXISTS (SELECT CONCAT(uuid_m, uuid_l) FROM players WHERE uuid_m = '%d' AND uuid_l = '%d')" +
                                                                                                       ^
   "LIMIT 1;"
   

   In all these cases the next "line" starts immediately, so you get, for example, ...AS TMPWHERE... You need to include the space.

2. You have the WHERE clause before the FROM

3. You are building the query dynamically and inserting potentially tainted values, leaving yourself wide open to SQL Injection attacks. Use PreparedStatement with substitution points instead.

There may be more, but you should fix these before proceeding.

When diagnosing SQL syntax errors, always print out the final statement as built by your program to see what you're passing to the driver.

Answer 3

This approach isn't necessary and in fact is probably harmful. The simplest solution is to designate that UUID as the PRIMARY KEY and be done with it:

INSERT INTO players (uuid, name, clan, xp, rank)
  VALUES (?, ?, ?, ?, ?)

This is by the book and it works. If you have a duplicate record the insert will fail. If you want to update an existing record you can either compose an UPDATE ... WHERE uuid=? or use the MySQL ON DUPLICATE KEY feature, an "upsert" in other databases:

INSERT INTO players (uuid, name, clan, xp, rank)
  VALUES (?, ?, ?, ?, ?)
  ON DUPLICATE KEY SET name=VALUES(name), clan=VALUES(clan), xp=VALUES(xp), rank=VALUES(rank)

This way you don't even have to bother testing if the entry already exists. If it exists it'll get updated with whatever new data you have on hand.

Regarding the UUID field:

How many billions of rows are you dealing with where the marginal cost of 32-characters vs. 8 characters is going to bankrupt you storage wise? Don't be too clever by half and store UUIDs as binary data, especially when you put it in two different columns. This is how you earn a bad reputation on a team for making these ridiculously baroque data structures that everyone hates to use. Store it as hex-encoded text with the dashes present like everyone else. If your database platform supports a native UUID type, like Postgres does, use that instead, but only if.

Also a UUID is 128 bits, so you'll need two BIGINT values, minimum, or in other words 16 bytes. I don't think you realize how utterly inconsequential an extra 16 bytes per row is versus the immediate, easy readability and simple indexing benefits a singular column provides.

We live in an age where a drive that has mere gigabytes of storage is considered tiny and where memory is equally abundant. We don't need to squeeze every bit out of our data structures. This is a classic case of premature optimization. Just do it the traditional way until you have concrete prove that way isn't working in your particular situation, and then measure the impact of the problem so when you try alternatives you can see how they perform versus that baseline.