MySQL UUID alternative - more randomness

Question

I have a table with thousands of rows, this table has a column of guid. This is normally generated in the code, and that works fine.

However, this time, a batch import was performed and does not contain UUID values. I need to now update this table so that it does properly have UUID values.

The problem I'm running into is that when I execute the following in a loop:

(select uuid())

It's not random enough. Meaning that internally, the machine (name?) and timestamp are used to generate the uuid, which in tern results in hundreds of thousands of rows receiving super similar UUID's.

I can't generate this at the software stack, unfortunately, so I'm wondering if anyone has any tips or good solutions for this.

Edit For those close votes:

When using (select uuid()) in an update command on thousands of rows, it relies on the machine (node) and the timestamp. Because thousands upon thousands of rows are updated within microseconds of each other, this produces an unfortunately similar string across all rows, for example:

1a5e308e-5530-11e7-a853-932d273ec009
1a5e3160-5530-11e7-a853-932d273ec009
1a5e32a0-5530-11e7-a853-932d273ec009

Would be the first 3 uuid's generated and applied to the first 3 rows in the update query. This is problematic because these UUID's are used in a greater part of a concatenated string that is visible to the frontend. Because of this, the end result is that these become guessable, and therefore represent a possible angle for a vector attack.

My purpose in asking this question is if there is another way to generate a 16 character, 128 byte string that follows the same pattern as a UUID does, but perhaps has more uniqueness from one iteration to the next.

I hope I was clear enough in this explanation.

Answer 1

This function generates completely random UUIDs in MySQL (tested with MySQL 5.6)

select LOWER(CONCAT(
    LPAD(HEX(ROUND(rand()*POW(2,32))), 8, '0'), '-',
    LPAD(HEX(ROUND(rand()*POW(2,16))), 4, '0'), '-',
    LPAD(HEX(ROUND(rand()*POW(2,16))), 4, '0'), '-',
    LPAD(HEX(ROUND(rand()*POW(2,16))), 4, '0'), '-',
    LPAD(HEX(ROUND(rand()*POW(2,48))), 12, '0')
))

Note that it's not a UUIDv4 because it doesn't have bits that indicate version 4 or variant (See UUIDv4 on Wikipedia) - instead it's completely random (or, more precisely, as random as MySQL's RAND() function can generate)