Mysql Insert on duplicated key with last_insert_id not working

create table test1 ( 
   id int not null auto_increment primary key, 
   a varchar(16), b varchar(16) 
);


INSERT INTO test1 (a,b) VALUES ('a1','b3') ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), b='3';

The ab开发者_开发问答ove line should insert an entry since the table is empty.

INSERT INTO test1 (a,b) VALUES ('a1','b3') ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), b='3';

Run the line again, it should replace b with "3" since a1, b3 already exist. But mysql adds another line for me. I have searched awhile and can not find a solution.

Latest update: Thanks for all your help. I figured one of the column must be unique.

Alter table test1 add unique (a)

solve the problem.

---

The ON DUPLICATE is only triggered if the column has a UNIQUE KEY. Try adding a UNIQUE INDEX on your table on the a or b columns and it should work as intended.

---

There is no duplicate key in your case. Only unique column in your table is primary key id

Try

INSERT INTO test1 (id,a,b) VALUES (1,'a1','b3') ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), b='3';

twice. First line of documentation says.

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.

See the full documentation.