如何在PHP中获取MySQL表的刚刚插入的行？

Question

I got a table in MySQL which looks like this:

CREATE TABLE IF NOT EXISTS `play` (
  `identifier` varchar(255) NOT NULL DEFAULT '',
    ... ,
  PRIMARY KEY (`identifier`),
  UNIQUE KEY `identifier` (`identifier`)
)

When I insert a row in this table the value of the column identifier may be changed by a trigger I wrote. Now I try to insert a row in this table from PHP which works perfectly but I need to retrieve the content of the identifier column in the just inserted row.

I know that the function mysql_insert_id() exists but it does only work if the table has a column with auto increment which does not work with my structure.

Is there any way to receive the just inserted row in PHP or maybe SQL?

Answer 1

The short answer is no, there's no builtin functionality for the behavior you describe.

It would be possible, however, to set a user-defined variable within a BEFORE INSERT or AFTER INSERT trigger, e.g.

SET @new_mytable_identifier_val = NEW.identifier ;

Immediately following the insert, you could then run a separate query to retrieve the current value of the user-defined variable:

SELECT @new_mytable_identifier_val

This would have to be within the same session that fires your trigger (that is, the session that performed the INSERT.) User-defined variables are in scope and persist within the current session. Note that the user-defined variables are not protected in anyway, except being available only within the session. Some other statement within the session could change the currently assigned value, you need to be careful not to "step on" the value assigned by the trigger.

You could use a pattern like this:

SET @new_mytable_identifier_val = '';
INSERT INTO mytable (col) VALUES (val);
SELECT @new_mytable_identifier_val;
SET @new_mytable_identifier_val = '';

Note that with this approach, with a multi-row insert statement, only the last assigned value is going to be available. (You could also work it so that the trigger concatenate a comma and the new identifier value into the existing value of the user-defined variable, so you could get back a comma separated list, but that gets pretty ugly.)

---

As an alternative design, rather than performing the derivation/assignment within a trigger, you could write a function that derives the value. Call that function separately, passing in whattever values are needed (if any), for example:

SELECT get_mytable_new_identifier('fee','fi','fo') AS newid;

newid
-----
42

Then use the return from the function and use it in the INSERT

INSERT INTO mytable (identifier,b,c,d,...) VALUES (42,'fee','fi','fo',...)