So, imagine a mysql table with a few simple columns, an auto increment, and a hash (varchar, UNIQUE).

Is it possible to give mysql a query that will add a column, and generate a unique hash without multiple queries?

Currently, the only way I can think of to achieve this is with a while, which I worry would become more and more processor intensive the more entries were in the db.

Here's some pseudo-php, obviously untested, but gets the general idea across:

while(!query("INSERT INTO table (hash) VALUES (".generate_hash().");")){
    //found conflict, try again.
}

In the above example, the hash column would be UNIQUE, and so the query would fail. The problem is, say there's 500,000 entries in the db and I'm working off of a base36 hash generator, with 4 characters. The likelyhood of a conflict would be almost 1 in 3, and I definitely can't be running 160,000 queries. In fact, any more than 5 I would consider unacceptable.

So, can I do this with pure SQL? I would need to generate a base62, 6 char string (like: "j8Du7X", chars a-z, A-Z, and 0-9), and either update the last_insert_id with it, or even better, generate it during the insert.

I can handle basic CRUD with MySQL, but even JOINs are a little outside of my MySQL comfort zone, so excuse my ignorance if this is cake.

Any ideas? I'd prefer to use either pure MySQL or PHP & MySQL, but hell, if another language can get this done cleanly, I'd build a script and AJAX it too.

Thanks!