MySQL UUID

Posted on Edited on

MySQL UUID Smackdown: UUID vs. INT for Primary Key

可以用 BINARY 存 UUID，然後用以下 function 做 human-readable format 的轉換：

  • UUID_TO_BIN
  • BIN_TO_UUID
  • IS_UUID

這些 function 要 MySQL 8.0 後才有。

Example

把 UUID 當 PK 的 example

create table

1
2
3
4
CREATE TABLE customers (
    id BINARY(16) PRIMARY KEY,
    name VARCHAR(255)
);

insertion

1
2
3
4
INSERT INTO customers(id, name)
VALUES(UUID_TO_BIN(UUID()),'John Doe'),
      (UUID_TO_BIN(UUID()),'Will Smith'),
      (UUID_TO_BIN(UUID()),'Mary Jane');

query

1
2
3
4
5
SELECT 
    BIN_TO_UUID(id) id, 
    name
FROM
    customers;