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.