duplicating mysql primary key as MD5 equivalent

For each record in my tables, there is a ID, then Public_ID.

The ID is an auto-incremented int. The Public_ID is an MD5 hash of the INT in ID.

To generate this Public_ID, I typically get the insert id which matches is the value of ID, then I generate the Public_ID and update the record where ID matches the Insert ID.

Is ther开发者_如何学Pythone a more efficient way of generating the Public_ID without having to count, or update after inserting?

Thanks, Peter

---

Sure there is. Hash anything other than the ID to get the Public_ID. You can use the current timestamp, other columns from the table, environment data, or any combination of these.

---

Generall the ID of the inserted row isn't available until AFTER the insert has completed, which means you can't do:

insert into table (pk, hash) values (null, last_insert_id());

You'll just get the ID of whatever insert was performed before this one, not the ID of the current insert. As well, you can't use a trigger to catch it and update the table, as a trigger can't change the new row.

So, looks like you're stuck with the insert/fetch/update cycle.

---

If you are going to use MD5 to hash the ID, you might as well generate a GUID.