Random string unique in database table

Question

I need to generate a random string (8 digit alphanumeric code) and save it with my Tournament row into a database.

Problem is that this code needs to be unique in the whole table and random (not incremental) because I don't want it to be predictable (people using it to join tournament).

So I need something to genreate a code but when it already is in table then generate new one. One way is to generate code, then check database existance, then generate new in case of conflict. But this solution have many problems like it can run forever and its is slow.

Why not concatenate the unique (numerical and incrementing) ID with some secret and use a hash of that? — Eugen Rieck, Apr 21 '19 at 17:48
Possible duplicate of [Generating a random & unique 8 character string using MySQL](https://stackoverflow.com/questions/16737910/generating-a-random-unique-8-character-string-using-mysql) — Nikhil S, Apr 21 '19 at 18:08
@DavidBubeník you can use UUID generator, remove the dashes and trim it to the requested size. There are plenty of UUID libraries that you can use to randomly generate UUIDs. — Nir Alfasi, Apr 21 '19 at 18:37
@GordonLinoff chances of collision are relatively low (1/36^8). — Nir Alfasi, Apr 21 '19 at 20:25
Concatenating a hex timestamp would be a good idea to avoid uniqueness problems. But its too long and the length could increase at some point — Rashomon, Apr 21 '19 at 21:24

Peter O. · Answer 1 · 2019-07-31T09:11:48.753

The answer is to use a unique identifier that combines a "unique" part and a "random" part.

The "unique" part can be a monotonically increasing counter (such as the record or row number in a database), or it can be a number generated with a full-period linear congruential generator (which cycles pseudorandomly through all possible values in its period before repeating).
The "random" part is simply a random number generated with a cryptographic random number generator (which Node.js has; check the documentation). In general, the longer it is, the less predictable it will be.

Moreover, for the purposes of generating unique identifiers, there is little reason to limit yourself to 8-character alphanumeric identifiers as you're now doing.

Even if you expect end users to enter a "random" and "unique" ID at some point, there are techniques you can use to ease the task of entering IDs much longer than 8 characters; they include—

adding a "checksum" character to the ID,
dividing the ID into hyphen-separated chunks, and
employing a system similar to Bitcoin's BIP39, in which each ID is convertible to a sequence of memorable words.

score 0 · Answer 2 · answered Jul 03 '22 at 11:33

function GenerateID(length,tabelName,IdName){
        function SelectAllID(table,ID) {
              return new Promise((resolve, reject) => {
                let sql = `SELECT ${ID} FROM ${table} WHERE 1`;
                   connection.query(sql, (err, rows, fields) => {
                      if (err) return reject(err);
                      resolve(rows);
                  })
              });
        }
        function Generate(array) {
            let acceptable = false;
            do {
              let ID = Math.floor(Math.random() * length);
              let exist = array.includes(ID);
              if (!exist) { 
                return ID ;  
                acceptable = true; 
              } else{ acceptable = false; }
            }
            while (acceptable = false);
        }
        async function RetunFunction() {    
            let arrayFromDb = await SelectAllID(tabelName,IdName)
            let ReturnId = await Generate(arrayFromDb)
            console.log(ReturnId)
            return ReturnId
        }
        
        RetunFunction();
    }

Random string unique in database table

2 Answers2

Linked