Hi

I have a table with about 17000 users and I need to give each of them a unique random 16-character identifier - for various reasons I can't use their existing user id.

Does MySQL have a function that would enable me to populate all the rows with a unique id just using a single UPDATE query ?

like : UPDATE clients SET identifier=(?????)

and would it be possible to set this up so that the string is automatically generated and inserted by MySQL when a new row is created ?

I could do it with php but it would be longer to set up and anyway I'm always on the lookout to learn something 🙂

thanks

    Well, you could use a prepared statement, start a transaction, update each user individually with the "unique random 16-character identifier" generated via PHP, then commit.

      First thing you'll want to do is to make sure you've got a UNIQUE index on this new column, of course.

      Next, you'll need to define how these 16 random characters should be generated. For example, what types of characters? How about line break characters? Or backspace characters? Null characters?

      steamPunk;11000406 wrote:

      Does MySQL have a function that would enable me to populate all the rows with a unique id just using a single UPDATE query ?

      like : UPDATE clients SET identifier=(?????)

      You might be able to do execute a query like:

      UPDATE clients
SET identifier = RIGHT( [i]example_func[/i](), 16 )
WHERE LENGTH( identifier ) != 16

      repeatedly until all rows have an identifier, but I don't know if that would work (what with the UNIQUE constraint and all - depending upon how likely example_func/i is of generating duplicate values). Note that example_func/i is, of course, just a placeholder - you'll need to use whatever function(s) you desire to generate the sequence of characters. MySQL's UUID() comes to mind.


      steamPunk;11000406 wrote:

      would it be possible to set this up so that the string is automatically generated and inserted by MySQL when a new row is created ?


      See the section entitled 17.3 Using Triggers in the MySQL manual for one way of doing this at the DB rather than in your application(s).

        thanks for your replies

        I've come across a MySQL thing that does 1 part of what I need : updating all the current rows with an identifier

        UPDATE `client` SET identifier = substring(MD5(RAND()), -16)

        which puts a different string like "bf7c5e2e6b9519a1" in each row

        now I'm going to check out the Triggers thing in MySQL - and if I can't do that I guess I'll just change the insert client function

        thanks

          I would include the primary key in that MD5 call, as it should (pretty much) guarantee a unique random string entry. Just using RAND() would product quite a few collisions.

            Ashley Sheridan;11000431 wrote:

            I would include the primary key in that MD5 call, as it should (pretty much) guarantee a unique random string entry. Just using RAND() would product quite a few collisions.

            I'm not really sure what you mean, there - I aready have a primary key on the existing user id (which is just an incrementing number) - is it possible to have 2 primary keys or should I use a unique field thingy ?

              steamPunk wrote:

              I'm not really sure what you mean, there - I aready have a primary key on the existing user id (which is just an incrementing number) - is it possible to have 2 primary keys or should I use a unique field thingy ?

              Your code in post #4 basically relies solely on RAND, for which there is no guarantee of uniqueness. Ashley Sheridan's idea is to just take the MD5 of the primary key instead, which would be unique. (The MD5 hashes of two different values can be identical, but it is rather unlikely.)

                so are you saying that I'd need to write it as

                UPDATE `client` SET identifier = substring(MD5(incrementingUserIdFieldName), -16)

                ?

                  Up to you. For example, the UUID idea is workable (whether in the SQL or from PHP).

                    excellent - I went with the UUID() and this works fine :

                    UPDATE `client` SET identifier = substring(MD5(UUID()), -32)

                    thanks for your help 😃

                      Write a Reply...