Use current auto_increment value as basis for another field in the same INSERT query - is this possible in MySQL?

Question

In my table (MySQL) I have these fields:

id - primary, auto_increment

hash - base 36 representation of id

I'd like to populate both fields at once, using a stored procedure to compute the base 36 equivalent of the value id receives on INSERT. Something like this:

INSERT into `urls` (`id`,`hash`) VALUES (NULL,base36(`id`));

Obviously this doesn't work, but I hope it illustrates what I'm trying to achieve. Is this possible, and if so, how?

Note: I want to use only one query, so last_insert_id isn't helpful (at least as I understand).

Answer 1

This question is essentially a restatement of this other one. In a nutshell, the advantage of an auto increment field is that the value is guaranteed unique, the disadvantage is that the value doesn't exist until the INSERT has already been executed.

This is necessarily the case. You can, for example, retrieve for a given table what the next auto increment value will be, but because of concurrency concerns, you can't be sure that the query you execute next will be the one that gets that ID. Someone else might take it first. The assignment of the ID field has to be, therefore, an atomic get-and-increment operation that happens as the record is being inserted.

In other words, though you may want to do this in a single query, you're simply not going to. There's a way you could theoretically pretend that you're only executing one query by attaching a trigger on INSERT that adds your alternate representation of the ID field after the fact. Alternately, you can use a non-auto-increment ID field based on something known ahead of time.

Answer 2

Although you can't do that in a single query, you can do this:

BEGIN TRANSACTION;
INSERT into `urls` (`id`) VALUES (NULL);
UPDATE `urls` SET `hash`=BASE36(`id`) WHERE `id`=LAST_INSERT_ID();
COMMIT;

Answer 3

You could do this using a trigger, something like:

CREATE TRIGGER `calc_id_hash` AFTER INSERT ON `urls`
FOR EACH ROW SET new.`hash`= BASE36(new.`id`);