How to generate unique id in MySQL

Question

I m programming a script using PHP and MySQL and I want to get a  unique id  consisting of a string  capitals and small  letters with numbers  like  gHYtUUi5b  I found many functions in PHP that can generate such numbers but I m afraid about how to ensure the id is unique   UPDATE  uuid is long  I mean such id like   P5Dc  an 11 alphanumeric char

User · Answer

If you use MySQL with version higher than 5.7.4, you can use the newly added RANDOM_BYTES function:

 SELECT TO_BASE64(RANDOM_BYTES(16));

This will result in a random string such as GgwEvafNLWQ3+ockEST00A==.

User · Answer

USE IT      info   random bytes 16        info 6    chr ord  info 6    amp  0x0f   0x40         info 8    chr ord  info 8    amp  0x3f   0x80         result  vsprintf   s s- s- s- s- s s s   str split bin2hex  info   4        return  result

User · Answer

crypt   as suggested and store salt in some configuration file  Start salt from 1 and if you find duplicate move to next value 2  You can use 2 chars  but that will give you enough combination for salt   You can generate string from openssl random pseudo bytes 8   So this should give random and short string  11 char  when run with crypt     Remove salt from result and there will be only 11 chars that should be enough random for 100  millions if you change salt on every fail of random

User · Answer

How you generate the unique ids is a useful question - but you seem to be making a counter productive assumption about when you generate them   My point is that you do not need to generate these unique id s at the time of creating your rows  because they are essentially independent of the data being inserted   What I do is pre-generate unique id s for future use  that way I can take my own sweet time and absolutely guarantee they are unique  and there s no processing to be done at the time of the insert   For example I have an orders table with order id in it   This id is generated on the fly when the user enters the order  incrementally 1 2 3 etc forever   The user does not need to see this internal id   Then I have another table - unique ids with  order id  unique id    I have a routine that runs every night which pre-loads this table with enough unique id rows to more than cover the orders that might be inserted in the next 24 hours    If I ever get 10000 orders in one day I ll have a problem - but that would be a good problem to have    This approach guarantees uniqueness and takes any processing load away from the insert transaction and into the batch routine  where it does not affect the user

User · Answer

lt  php      hostname conn    localhost        database conn    user id        username conn    root        password conn             conn   mysql pconnect  hostname conn   username conn     password conn  or trigger error mysql error   E USER ERROR       mysql select db  database conn  conn         run an endless loop           while 1                randomNumber   rand 1  999999     generate unique random number                     query    SELECT   FROM tbl rand WHERE the number    mysql real escape string   randomNumber           check if it exists in database         res  mysql query  query  conn               rowCount   mysql num rows  res           if not found in the db  it is unique   then insert the unique number into data base and break out of the loop     if  rowCount  lt  1         con   mysql connect   localhost   root              mysql select db  user id    con               sql    insert into tbl rand the number  values     randomNumber                 mysql query   sql  con               mysql close   con       break               echo  inserted unique number into Data base  use it as ID        gt

User · Answer

This generates random ids   CREATE TABLE Persons       ID Integer PRIMARY KEY AUTOINCREMENT      LastName varchar 255  NOT NULL      FirstName varchar 255       Age int

User · Answer

DELIMITER     USE  temp      DROP PROCEDURE IF EXISTS  GenerateUniqueValue     CREATE PROCEDURE  GenerateUniqueValue  IN tableName VARCHAR 255  IN columnName VARCHAR 255    BEGIN     DECLARE uniqueValue VARCHAR 8  DEFAULT         DECLARE newUniqueValue VARCHAR 8  DEFAULT         WHILE LENGTH uniqueValue    0 DO         SELECT CONCAT SUBSTRING  ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789   RAND   34 1  1                   SUBSTRING  ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789   RAND   34 1  1                   SUBSTRING  ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789   RAND   34 1  1                   SUBSTRING  ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789   RAND   34 1  1                   SUBSTRING  ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789   RAND   34 1  1                   SUBSTRING  ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789   RAND   34 1  1                   SUBSTRING  ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789   RAND   34 1  1                   SUBSTRING  ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789   RAND   34 1  1                    INTO  newUniqueValue          SET  rcount   -1          SET  query CONCAT  SELECT COUNT    INTO  rcount FROM    tableName   WHERE   columnName    like     newUniqueValue                PREPARE stmt FROM   query          EXECUTE stmt          DEALLOCATE PREPARE stmt      IF  rcount   0 THEN             SET uniqueValue    newUniqueValue           END IF       END WHILE       SELECT uniqueValue      END    DELIMITER     And call the stored procedure as GenerateUniqueValue  tableName   columnName    This will give you a 8 digit unique character everytime

User · Answer

You could use Twitter s snowflake   In short  it generates a unique id based on time  server id and a sequence  It generates a 64-bit value so it is pretty small and it fits in an INT64    It also allows for sorting values correctly   https   developer twitter com en docs basics twitter-ids  In sum  it allows multiple servers  highly concurrency  sorting value and all of them in 64 bits   Here it is the implementation for MySQL  https   github com EFTEC snowflake-mysql  It consists of a function and a table

User · Answer

You may like the way that we do it   I wanted a reversible unique code that looked  random  -a fairly common problem    We take an input number such as 1 942    Left pad it into a string   0000001942  Put the last two digits onto the front   4200000019  Convert that into a number  4 200 000 019   We now have a number that varies wildly between calls and is guaranteed to be less than 10 000 000 000   Not a bad start    Convert that number to a Base 34 string   2oevc0b  Replace any zeros with  y  and any ones with  z    2oevcyb  Upshift   2OEVCYB    The reason for choosing base 34 is so that we don t worry about 0 O and 1 l collisions   Now you have a short random-looking key that you can use to look up a LONG database identifier

User · Answer

For uniqueness what I do is I take the Unix timestamp and append a random string to it and use that

User · Answer

I use UUID   to create a unique value   example   insert into Companies  CompanyID  CompanyName  Values UUID     TestUUID

User · Answer

Use UUID function    I don t know the source of your procedures in PHP that generates unique values  If it is library function they should guarantee that your value is really unique  Check in documentation  You should  hovewer  use this function all the time  If you  for example  use PHP function to generate unique value  and then you decide to use MySQL function  you can generate value that already exist  In this case putting UNIQUE INDEX on the column is also a good idea

User · Answer

You might also consider using crypt    to generate a  nearly-guaranteed  unique ID inside your contraints

User · Answer

Below is just for reference of numeric unique random id     it may help you      query mysql query  select   from collectors repair     row mysql num rows  query    ind 0  if  row gt 0    while  rowids mysql fetch array  query        already exists  ind   rowids  collector repair reportid        else      already exists 0   nothing          break  false       while  break   false           rand mt rand 10000 999999          if array search  rand  alredy exists    false              break  stop          else                   echo  random number is      echo    and you can add char with the code like -       rand mt rand 10000 999999    randomchar     assume  radomchar contains char

User · Answer

A programmatic way can be to    add a UNIQUE INDEX to the field generate a random string in PHP loop in PHP   while    DO THE INSERT       generate another string    Note    This can be dirty  but has the advantage to be DBMS-agnostic Even if you choose to use a DBMS specific unique ID generator function  UUID  etc  it is a best practice to assure the field HAS to be UNIQUE  using the index the loop is statistically not executed at all  it is entered only on insert failure

User · Answer

To get unique and random looking tokens you could just encrypt your primary key i e   SELECT HEX AES ENCRYPT your pk  your password    AS  token  FROM your table  This is good enough plus its reversable so you d not have to store that token in your table but to generate it instead  Another advantage is once you decode your PK from that token you do not have to do heavy full text searches over your table but simple and quick PK search  Theres one small problem though  MySql supports different block encryption modes which if changed will completely change your token space making old tokens useless    To overcome this one could set that variable before token generated i e   SET block encryption mode    aes-256-cbc   However that a bit waste    The solution for this is to attach an encryption mode used marker to the token  SELECT CONCAT CONV CRC32   GLOBAL block encryption mode  10 35   Z  HEX AES ENCRYPT your pk  your password     AS  token  FROM your table   Another problem may come up if you wish to persist that token in your table on INSERT because to generate it you need to know primary key for the record which was not inserted yet    Ofcourse you might just INSERT and then UPDATE with LAST INSERT ID   but again - theres a better solution  INSERT INTO your table   token   SELECT CONCAT CONV CRC32   GLOBAL block encryption mode  10 35   Z  HEX AES ENCRYPT your pk  your password     AS  token  FROM information schema TABLES  WHERE  TABLE SCHEMA   DATABASE   AND TABLE NAME    quot your table quot    One last but not least advantage of this solution is you can easily replicate it in php  python  js or any other language you might use

[php] How to generate unique id in MySQL?

Examples related to php

Examples related to sql

Examples related to mysql