mysql insert if not exists, concurrent sessions

I want to insert new user into users table and make sure that user's nick and email are not already in the table (InnoDB).

Here is my logic:

if (SELECT COUNT(*) FROM users开发者_JAVA百科 WHERE nick = :nick) > 0:
    return "name exists";

if (SELECT COUNT(*) FROM users WHERE email = :email) > 0:
    return "email exists";

# OK to insert? Or something bad can happen here?

INSERT INTO users (nick, email) VALUES (:nick, :email)

But now I'm not sure if this is the right way. Suppose that between SELECT and INSERT query some other, concurrent connection creates new record with same nick or email (is this even possible?). Then INSERT will throw an exception and I'm unable to provide any feedback to the front end (beside simple "error occurred, try again).

Another idea is to use INSERT IGNORE and then check LAST_INSERT_ID(), but can I always be sure LAST_INSERT_ID()==0 when insertion is skipped?

Is there any better way to handle this?

Why don't you use a UNIQUE INDEX? Just insert the new value and let the query fail when the unique constraint is violated. A bit of errorhandling will do the trick.

The UNIQUE contraint will also solve your problem with concurrent users.

INSERT INTO users (nick, email)
SELECT :nick, :email
FROM Dual
WHERE NOT EXISTS (
    SELECT 1
    FROM users
    WHERE nick = :nick OR email =  :email
)

most MySql connectors out there have a way to get the rows affected, or you can SELECT ROW_COUNT().

Good question.

unfortinately mysql doesnt support something like "insert into if not exists".

there are several ugly solutions.

mostly the best is to handle it in your application. select before, see if you get anything, only insert if you dont get anything.

then you can put a unique key on the fields to ensure that the database keeps consistent.

you can also directly insert and rely on the unique keys. you will get an error which you have to deal with in your application. you CAN distinguish between the errors so you can display the proper message. duplicate key will be a 1062 if i remember that correclty.

however there ARE means to accomplish this with other teqniques.

one that i know of is called a mutex table.

it works so that you create a second table "mutex" which has the syme key fields as your working table which i now call "table". then you can do something like:

isnert into table (field1,field2) select ("input1","input2") from mutex left outer join table on(mutex.field1 = table.field1 AND mutex.field2 = table.field2) where mutex.field1 != "input1" AND mutex.field2 != "field2"

i did not test this and i hope i remember the tequnique correctly, better look it up!

it is also possible to advance this to mre flexibility so you can for example only allow a desired number of duplicates in one table.

but this does not ensure data consistency as the data table is accessible as well, so i would really recommend the first method: rely on key constraints where possible, and deal with the error number in your app, if that is not possible, deal with it in your application.

Based on the following link click here

mysql> LOCK TABLE t WRITE, t AS t1 READ;
mysql> INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> INSERT INTO t SELECT * FROM t AS t1;

LOCK TABLE users WRITE, users AS t1 READ;
INSERT INTO users (nick, email)
SELECT :nick, :email
FROM Dual
WHERE NOT EXISTS (
    SELECT *
    FROM users AS t1
    WHERE nick = :nick OR email =  :email
)
UNLOCK TABLES;