Use identity column in a string concat during an insert operation

Question

Let's say I have a table that looks like this:

CREATE TABLE Foo (
    fooSn INT NOT NULL AUTO_INCREMENT,
    fooId VARCHAR(64) NOT NULL,
    PRIMARY KEY (fooSn)
)
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC;

And I want to do something like this:

CREATE PROCEDURE insertWithAutoId (
    p_myPrefix VARCHAR(32) NOT NULL,
)
BEGIN
    INSERT INTO Foo
        (fooSn, fooId)
    VALUES
        (DEFAULT, CONCAT(p_myPrefix, DEFAULT));
END

Is this possible?

If you really need this here is one way to do it http://stackoverflow.com/a/17894239/1920232 — peterm, Sep 08 '16 at 04:08
It's complicated, but it works! Thanks for the heads up but I don't think I'll go down that route. — Isen Ng, Sep 09 '16 at 06:14
I'd examine the requirement for storing `fooId` column as the concatenation of a string prefix and the value returned from an AUTO_INCRMENT. As an alternative, consider storing the prefix in a separate column. And if you need the two columns combined (concatenated), use an expression in the SELECT list of the query that needs it. BUT... we wouldn't want to use a CONCAT() expression in a predicate (i.e. in a condition in a WHERE clause.) — spencer7593, Sep 09 '16 at 15:17
@spencer7593, the issue is that `fooId`, could be an auto id, or an explicitly set id. It's a unique naturalId to the sequentialid of the identity column. — Isen Ng, Sep 15 '16 at 09:21

score 2 · Accepted Answer · answered Sep 08 '16 at 03:57

No. It's not possible to reference the value assigned to the AUTO_INCREMENT column within the INSERT statement that adds the row. The value is not available within the statement. It's also not available in a BEFORE INSERT trigger.

You might be tempted to create an AFTER INSERT trigger, to assign a value to the fooId column, based on the value assigned to the fooSn AUTO_INCREMENT column. But that would require an UPDATE of the row that was just inserted, and you will find that if you attempt to do that within an AFTER INSERT trigger, MySQL will raise error 1442.

Use identity column in a string concat during an insert operation

1 Answers1