CODEWITHSUNDEEP
mysqlauto-incrementinsert-updateon-duplicate-key
Gilles Lesire
Gilles Lesire

Reputation: 1237

MySQL ON DUPLICATE KEY UPDATE and auto_increment index

I've been scooping around in the MySQL documentation but haven't found a clear answer on the following problem.

Let's say that I have a table "users". With the following fields:

CREATE TABLE `users` (
  `userId` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(16) NOT NULL,
  `password` varchar(128) NOT NULL,
  `salt` varchar(128) NOT NULL,
  `email` varchar(256) NOT NULL,
  PRIMARY KEY (`userId`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

So I have a primary key "userId" which is an auto_increment value. And a unique field "username". (So each username can only appear once, but the username should be editable).

Now I would like to know how I should use the ON DUPLICATE KEY UPDATE statement in this case. (Don't mind the unsecure way, but this is easier to read in this example.)

INSERT INTO users (`userId`, `username`, `password`, `salt`, `email`)
VALUES('$userId', '$username', '$password', '$salt', '$email')
ON DUPLICATE KEY UPDATE 
   username = VALUES(username), 
   password = VALUES(password), 
   email = VALUES(email);

What I was wondering was how I would have to insert a new user. Should I just pass a "0" or null for the UserId variable and it will create a new record with the appropriate auto incremented id? There is little information about how to use this with an auto_increment.

Because this current statement gives an error "Column count doesn't match value count at row 1".

Also what happens if I alter the username of a user, or if I enter a user with a different id but the same username?

Edit What I want to know is how to use the ON DUPLICATE KEY statement with a primary key that is an auto_increment. The problem is that I can't just pass on the the key in the insert statement, but I also need the key to update if it already exists. So is it possible to use the ON DUPLICATE KEY with an auto_increment primary?

Upvotes: 1

Views: 2467

Answers (2)

user5262341
user5262341

Reputation:

Problem is when you do the insert you do not include the auto_incremented index.

For Example:

INSERT INTO users (`username`, `password`, `salt`, `email`)
VALUES('$username', '$password', '$salt', '$email')

This is the reason why your column count doesn't match the table because it is not expecting your auto_index to be in the insert statement and therefore looking for another column

Upvotes: 1

miralong
miralong

Reputation: 842

inserting old (used) autoincrement id may be security hole in relation tables. For this reason i dont recomended this even if this is possible.

Upvotes: 0

Related Questions