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?

Answer 1

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.