How to generate random IDs that are not individually unique, but unique across 2 fields (columns) in a MySQL database table?

There already are answered questions on how to generate unique, random alphanumeric strings in MySQL. That is not the question.

I am trying to generate a 4 character, random alphanumeric string in MySQL such that the COMBINATION of that field and another are unique in MySQL. In other words, given an existing field in the table (userID), I need a 4 character, random alphanumeric string for another field in the same table (commentUID), such that the combined userID+commentUID is unique. Note that commentUID by itself is not necessarily unique - 2 or more users with different userIDs can have the SAME commentUID value.

Background: I have a users table, and a comments table. I need a unique combination for: the user's username (or ID) and the Unique ID of the comment they leave, so that every comment in the system can be uniquely identified in the URL, while also keeping the comment UIDs (and therefore URLs) as short as possible.

The unique ID for the comment should be 4 alphanumeric characters, because each user is not likely to leave more than 36^4 = 1.6M comments (36 denotes all possible values from a-z0-9, lowercase only). An example of this is actually dev.to. See https://dev.to/mindplay/comment/dm5i

How can I do this as a pure MySQL-only solution, such that the commentID is generated when a new record is inserted into the comments table? If you know how to do this, your thoughts are appreciated.

Comments

[Zohar Peled]

One easy way to do it is to generate the entire range of possibilities and store in a table, and then, when a user creates a new comment, pick one random row from that table that this specific use didn't already use.
Personally, I'm a SQL Server guy so I don't know the exact specific details on how to do it in MySql, but in SQL Server you can use the same trick as shown here:

answer re: Guarantee random inserting

Nov 1 '18

8

If you add an identity column to the table, and use order by newid() when inserting the records (that will be slow but it's a one time thing that's being done offline from what I understand) then you can use order by on the identity column to select the records…

Open Full Answer

• and I'm guessing something like this can also be used in MySql. The benefit of this approach is that you can simply select for each user the first row from the strings pool table where it's id is larger then the last row they used - and you don't need to sort randomly at runtime.

You might also want to read this:

Random strings. Generate on the fly, or select from a pre-populated table?

Zohar Peled ・ Sep 15 ・ 3 min read

#sql #random

[KP]

@peledzohar thank you, while I'm more of a MySQL guy (I understand that SQL server can be quite different) I read your comment and the article you wrote here

Random strings. Generate on the fly, or select from a pre-populated table?

Zohar Peled ・ Sep 15 ・ 3 min read

#sql #random

Instead of pre-generating a new table, I would like to generate the commentID field (such that userID + commentID combined field is unique) dynamically at runtime. Is this at all possible in MySQL?

[Zohar Peled]

Yes, I'm guessing it's probably possible - as far as I know, MySql does support unique indexes on multiple columns - so all you really have to do is to create such an index, and then generate a random string and just attempt to insert. If success, great. If failed, generate a new string and repeat until success.
With 1.6M options, I don't think the Birthday problem is really a factor to consider here.

[KP]

@peledzohar good points, thanks for sharing. Instead of trying to reinsert on failure, I just thought of this to avoid collisions (even though the birthday problem isnt a pressing issue at the moment). Let me know what you think of this.

What if:

• I dropped the idea of a unique userID+commentID, and ONLY made the commentID unique?
• Instead of randomly generated commentID strings, what if I made the commentID the primary key, and incremented it sequentially, starting with 4 characters, and when we run out of 4 characters, move to 5 and then 6? Is there a downside to doing this? I dont think I necessarily need the commentIDs to be randomly generated since all comments are public so security isnt really a concern here.
• Taking this page dev.to/peledzohar/comment/fmnb as an example, it would only load if I get both the userID "peledzohar" and commentID "fmnb" correct. Otherwise it would show a 404 page.

Thoughts on this approach? In this case, would commentID be a primary key? How would I get it to auto-increment and go from a default of 4 characters to 5, and so on? Am I still overcomplicating or over-thinking this?

[Zohar Peled]

Then why not simply use an integer?

[KP]

Also a possibility

[Valentin Silvestre]

Hey, take a look at Unique index on these links :
mysqltutorial.org/mysql-unique/
stackoverflow.com/questions/635937...

This solution works, afaik the only downside is coming from null value.

[KP]

@vasilvestre thank you for your response. Adding a unique constraint to a combination of columns in MySQL is useful. But how do you generate a unique ID for one of those fields (commentID in this case) during insert, such that userId + commentID is unique? Is there a way to do this in MySQL?