MySql table with non-sequential identifier

Question

Is there a way to create a table in MySql that has an automatic ID field, but the identifier is not sequential. For example, a random or pseudo-random identifier. I found solutions offering to create an identifier and try to insert it until an unused identifier ( generating a sequential five-digit alphanumeric identifier ) is found. but nothing that can be done directly in the table definition, or a simpler trick.

Answer 1

MySQL has a built-in function UUID()that will generate a globally unique identifier:

mysql> SELECT UUID();
    -> '6ccd780c-baba-1026-9564-0040f4311e29'

CHAR(36).

INSERT INTO table (`uuid`, `col1`, `col2`) VALUES (UUID(), 'someval', 'someval');

,

UUID() , . , UUID .

Addendum UUID_SHORT() 64- unsigned INT, .

mysql> SELECT UUID_SHORT();
    -> 92395783831158784

Answer 2

, auto_incremented "" , ( 2 ^ 32):

CREATE TABLE AutoIncPrime
(id int unsigned auto_increment primary key
) ;

1 10:

INSERT INTO AutoIncPrime 
VALUES (),(),(),(),(),(),(),(),(),() ;

SELECT * FROM AutoIncPrime ;

:

id
---
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

id :

CREATE VIEW AutoIncPrime_v AS
    SELECT 
        ((id*1798672429 ) & 0xFFFFFFFF) 
          AS FakeUUID
    FROM AutoIncPrime ;

"UUID":

  SELECT * FROM AutoIncPrime_v ;

:

FakeUUID
----------
1798672429
3597344858
1101049991
2899722420
 403427553
2202099982
4000772411
1504477544
3303149973
 806855106

( ):

CREATE VIEW AutoIncPrime_v2 AS
    SELECT
        (   (((id*1798672429 ) & 0x55555555) << 1) 
          | (((id*1798672429 ) & 0xAAAAAAAA) >> 1)
        )
        AS FakeUUID
    FROM AutoIncPrime ;

SELECT * FROM AutoIncPrime_v2 ;

FakeUUID
----------
2537185310
3918991525
2186309707
1558806648
 604496082
1132630541
3719950903
2791064212
3369149034
 808145601

, , . - .

, , FakeUUID INSERT.

Answer 3

? auto_increment. , , .

, , . , , "".

, , last_insert_id() mysql , . , , , .

Answer 4

( MySQL).

You must write a trigger to automate this process, perhaps through the UUID function, as Michael suggested.