mysql unique number generation

by

While it seems somewhat awkward, this is what can be done to achieve the goal:

SELECT FLOOR(10000 + RAND() * 89999) AS random_number
FROM table
WHERE random_number NOT IN (SELECT unique_id FROM table)
LIMIT 1

Simply put, it generates N random numbers, where N is the count of table rows, filters out those already present in the table, and limits the remaining set to one.

It could be somewhat slow on large tables. To speed things up, you could create a view from these unique ids, and use it instead of nested select statement.

EDIT: removed quotes

More Related Contents:

  1. MySQL select 10 random rows from 600K rows fast
  2. quick selection of a random row from a large table in mysql
  3. Simple Random Samples from a Sql database
  4. How to select one row randomly taking into account a weight?
  5. quick selection of a random row from a large table in mysql
  6. Simple Random Samples from a MySQL Sql database
  7. Insert/ Update random date in MySQL
  8. MYSQL Select One Random record from each Category
  9. get first occurrence of last changed value of a column [closed]
  10. MySQL – Rows to Columns
  11. ROW_NUMBER() in MySQL
  12. multiple query same table but in different columns mysql
  13. How can I make SQL case sensitive string comparison on MySQL?
  14. How can I temporarily disable a foreign key constraint in MySQL?
  15. How to do a batch insert in MySQL
  16. Ordering by the order of values in a SQL IN() clause
  17. Creating a “Numbers Table” in MySQL
  18. MySQL Group by SUM
  19. In SQL, how to select the top 2 rows for each group
  20. Mysql query to dynamically convert rows to columns on the basis of two columns
  21. mysql trigger stored trigger is already used by statement which invoked stored trigger
  22. Error Dropping Database (Can’t rmdir ‘.test\’, errno: 17)
  23. MySQL: How do I join same table multiple times?
  24. mysql update query with sub query
  25. What is the difference between the backtick and the square bracket in SQL statements?
  26. How to insert a file in MySQL database?
  27. What does SQL Select symbol || mean?
  28. What is wrong with my SQL here? #1089 – Incorrect prefix key
  29. How to speed up sql queries ? Indexes?
  30. mySQL SELECT upcoming birthdays

Leave a Comment