When doing a MySQL INSERT, if the insert fails because of a duplicate key, is there a way to return that key's id?

So I have a table like this:

create table `test` ( 
`testId` int(11) not null auto_increment, 
`text` varchar(10) not null default '',
primary key(`testId`),
unique(`text`)
) engine=innodb;

My insert would be

1. insert into test (text)开发者_如何学编程 values ('a');
2. insert into test (text) values ('b');
3. insert into test (text) values ('a');

the 3rd insert will fail, but I want it to return the testId for the duplicate (for 'a').

Is this possible without writing a second query?

---

You can't do this in an INSERT query because the INSERT does not return any rows.

When an INSERT fails because of a duplicated key - normally the thing that built the query knows what data it sent, so it could use this.

You may be able to achieve what you want by using 12.2.5.3. INSERT ... ON DUPLICATE KEY UPDATE

If a table contains an AUTO_INCREMENT column and INSERT ... UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful. However, you can work around this by using LAST_INSERT_ID(expr). Suppose that id is the AUTO_INCREMENT column. To make LAST_INSERT_ID() meaningful for updates, insert rows as follows:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

---

There are two ways of doing this. If you just want to replace the existing data, you can use

REPLACE INTO <table_name> VALUES <values>

That is the simplest thing. But if you just want to check whether the data exist, you can first, do this

SELECT COUNT(*) FROM <table> WHERE <field>='<field_value>

If exists, query returns more than 0.

Hope this helps.