How to generate a unique random number when insert in MySQL?

Question

I have a database for articles and may want to generate a unique random integer for each articles so that they can be visited through URL like https://blablabla.com/articles/8373734 etc.

I could achieve that in python backend, but how do we achieve this in MySQL sentences?

For example, a new article was done, and inserted into database:

INSERT into article_table (title, date, url_id) VALUES ('asdf', '11/11/1111', 8373734)

the url_id here is the unique random integer (1000~10000000) that automatically generated.

I believe The primary key ID and auto-increasment are good way to solve this. But my question is:

1. In practical scenario, do they (companies) literally use primary ID or auto-increasment? This may expose how piece of data you (ever) have in database. Take this https://www.zhihu.com/question/41490222 for example, I tried hundreds of number around 41490222, all returns 404 not found. it seems that the number are recorded very sparsely, not very possible achieved by auto-increasement.

2. Are there any efficient way to generate such random number without checking duplication for every loop?

Answer 1

You can use UUID(), or if it has to be numeric UUID_SHORT() for that.

Answer 2

albeit my sql skills are a bit rusty, I think you might want to create a function using the RAND function.

CREATE PROCEDURE GetRandomValue()
BEGIN

  DECLARE newUrlId INT DEFAULT 0;

  WHILE (
    newUrlId = 0 
    OR IF EXISTS(SELECT 1 FROM yourTable WHERE url_id = newUrlId)
  )
  DO
    SET newUrlId = SELECT FLOOR(RAND() * 999999)
  END WHILE

  RETURN newUrlId
END

Then again, why creating such a fuss while you could use other ways to create "bigger random numbers"

for example:

function createBiggerNumber(id) {
  return (id * constants.MySecretMultiplyValue) + constants.MySecretAddedValue;
}

function extractIdFromBiggerNumber(number) {
  return (number - constants.MySecretAddedValue) / constants.MySecretMultiplyValue
}