mysqlrandom

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


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?


Solution

  • 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
    }