MySql table with not sequential id

Is there a way to create a table in MySql that it has an automatic ID field, but the ID is not s开发者_StackOverflow社区equential. For example, a random or pseudo random ID. I have found solutions that suggest generating an ID and try to insert it until an unused ID is found (generating an sequential five digit alphanumerical ID). but nothing that can be done directly in the table definition, or a simpler trick.

MySQL has a native function UUID() which will generate a globally unique identifier:

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

You can store its output in a CHAR(36) column.

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

According to the documentation though,

Although UUID() values are intended to be unique, they are not necessarily unguessable or unpredictable. If unpredictability is required, UUID values should be generated some other way.

Addendum Another option is UUID_SHORT() for a 64-bit unsigned INT rather than a character field.

mysql> SELECT UUID_SHORT();
    -> 92395783831158784

Since you asked for a trick, you could use a common auto_incremented id and "fake" it by multiplying with a big prime (and then modulo 2^32):

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

Insert values, from 1 to 10:

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

SELECT * FROM AutoIncPrime ;

Output:

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

Fake the id, with a View:

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

Lets see our "UUIDs":

  SELECT * FROM AutoIncPrime_v ;

Output:

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

You could even make it look more random with (more complicated bit mixing):

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

The trick is that you still have a sequential id in the table - which you can use to join to other tables. You just don't show it to the users - but only show the fake one.

If the table is to get big and the calculations slow, you can add another column in the table and store the FakeUUID value there with an INSERT trigger.

Would a composite key work? A regular standard auto_increment field. You insert your new record, retrieve its new ID, then hash that ID with a salt, and update the record with that hash value.

If you do this all within a transaction, the in-progress version of the record without the hash will never be visible until the hash is generated. And assuming you've done proper salting, the resulting hash value will be for all intents and purposes 'random'.

Note that you can't do this in a single step, as the value of last_insert_id() in mysql is not updated with the new id until the record is actually written. The value retrieved during the actual insert parseing stage would be whatever id was inserted BEFORE this one.

The only automatically generated default in the table definition allowed would be autoincrement (MySQL Guide).

You should be able to write a trigger to automate this process though, maybe through the UUID function as Michael suggested.