2

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 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?

Improve this question
2
  • use table lock before you start do the checking - dev.mysql.com/doc/refman/5.0/en/lock-tables.html Commented Dec 11, 2010 at 17:19
  • I was told not to use table locks. Because code that unlocks the table may fail/never execute. Commented Dec 11, 2010 at 17:39

4 Answers 4

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

both fields are UNIQUE, but if INSERT query fails how do I know which field is duplicated?
@spajak, requery the database with the first two selects.
Do some proper errorhandling, the error message contains the name of the constraint that is violated. Now you know what field is duplicated because you know what field belongs to this constraint.
So regex match on error msg.. a little bit tricky. I've expected a cleaner method, but I think I have no better choice.
hi @frank-heikens, if the storage engine of the table is innodb, and 2 concurrent connections insert same data to table with auto-commit enabled, any idea if 1 or both insert will failed?
1 
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().

Improve this answer

1 Comment

doesn't work for me - for height concurrent requests creates a lot of deadlocks. See: percona.com/blog/2006/07/12/…
0

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.

Improve this answer

2 Comments

I relay on constraints so I know records won't be duplicated inside this table. In that case, are both SELECT queries useless? Should I only check INSERT query error code?
yes, i would recommend that. because there may be a delay between your select and insert statements. so you cant really rely on your isnert statements, but you can rely on the error code.
0

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;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.