Generating a random & unique 8 character string using MySQL

by

I woudn’t bother with the likelihood of collision. Just generate a random string and check if it exists. If it does, try again and you shouldn’t need to do it more that a couple of times unless you have a huge number of plates already assigned.

Another solution for generating an 8-character long pseudo-random string in pure (My)SQL:

SELECT LEFT(UUID(), 8);

You can try the following (pseudo-code):

DO 
    SELECT LEFT(UUID(), 8) INTO @plate;
    INSERT INTO plates (@plate);
WHILE there_is_a_unique_constraint_violation
-- @plate is your newly assigned plate number

Since this post has received a unexpected level of attention, let me highlight ADTC’s comment : the above piece of code is quite dumb and produces sequential digits.

For slightly less stupid randomness try something like this instead :

SELECT LEFT(MD5(RAND()), 8)

And for true (cryptograpically secure) randomness, use RANDOM_BYTES() rather than RAND() (but then I would consider moving this logic up to the application layer).

More Related Contents:

  1. In SQL Server how to get the count of child if both parent and child are available [closed]
  2. Delete the entire content of all table in a Database [closed]
  3. MySQL select 10 random rows from 600K rows fast
  4. Delete all Duplicate Rows except for One in MySQL? [duplicate]
  5. Get a list of dates between two dates
  6. Calculate a running total in MySQL
  7. Simulate lag function in MySQL
  8. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  9. Detect if value is number in MySQL
  10. Difference between two dates in MySQL
  11. How do I create a sequence in MySQL?
  12. MySQL search and replace some text in a field
  13. How to get the next auto-increment id in mysql
  14. MySQL dynamic-pivot
  15. Can I reuse a calculated field in a SELECT query?
  16. Group mysql query by 15 min intervals
  17. Error Code: 1406. Data too long for column – MySQL
  18. Deleting duplicate rows from a table
  19. Using Count to find the number of occurrences
  20. Is there any reason to worry about the column order in a table?
  21. Checking a table for time overlap?
  22. MySQL INSERT INTO … VALUES and SELECT
  23. MySQL LIKE query with underscore
  24. MYSQL select DISTINCT values in two columns
  25. Compare only day and month with date field in mysql
  26. What are practical differences between `REPLACE` and `INSERT … ON DUPLICATE KEY UPDATE` in MySQL?
  27. What does it mean by select 1 from table?
  28. mysql select distinct rows into a comma delimited list column
  29. Select from same table as an Insert or Update
  30. How do I limit the number of rows per field value in SQL?

Leave a Comment