UUID
UUID()
Returns a universally unique identifier (UUID version 1) in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx. Each call gives a different value.
Examples
Examples with the customers, products and orders tables use the sample database from the SQL basics series.
SELECT CHAR_LENGTH(UUID()) AS len, UUID() = UUID() AS same, UUID() REGEXP '^[0-9a-f]{8}-[0-9a-f]{4}-1[0-9a-f]{3}-[0-9a-f]{4}-[0-9a-f]{12}$' AS shape;
+-----+------+-------+ | len | same | shape | +-----+------+-------+ | 36 | 0 | 1 | +-----+------+-------+
A public order identifier that cannot be guessed by increasing a number:
ALTER TABLE orders ADD public_id CHAR(36) NULL; UPDATE orders SET public_id = UUID(); SELECT COUNT(DISTINCT public_id) AS different, COUNT(*) AS total_orders FROM orders;
+-----------+--------------+ | different | total_orders | +-----------+--------------+ | 7 | 7 | +-----------+--------------+
Things to watch out for
- A version 1 UUID contains the time and the server identification, so it is not random. It is not suitable for secret tokens (password reset) – create those in PHP with random_bytes().
- As text, it takes 36 characters. MySQL 8 has UUID_TO_BIN() and BIN_TO_UUID() for storing it in BINARY(16); MariaDB has its own UUID data type since version 10.7.
- As a primary key, a UUID is slower than AUTO_INCREMENT – the values are not sequential and the index fragments more. A numeric key with a UUID as the public ID is a common combination.