How to Store UUIDs as Numbers in MySQL for Enhanced Performance?

Storing UUID as a Number in MySQL

To improve MySQL performance, it is recommended to store UUIDs as numbers instead of strings. This optimization leverages MySQL's faster binary processing capabilities.

Conversion Process:

1. Remove Dashes: Strip the dashes from the UUID. For example, convert "110E8400-E29B-11D4-A716-446655440000" to "110E8400E29B11D4A716446655440000".
2. Calculate Length: Divide the resulting string length by 2. For UUIDs, which are 32 characters long, this yields 16.
3. Create Binary Field: In MySQL, create a BINARY field with a length equal to the calculated value. For UUIDs, this would be BINARY(16).
4. Insert UUID: Use the UNHEX() function to insert the binary representation of the UUID into the field. For example:

INSERT INTO Table (FieldBin) VALUES (UNHEX("110E8400E29B11D4A716446655440000"))

Querying and Re-formatting:

1. Query UUID: Retrieve the hexadecimal representation of the UUID using the HEX() function.
2. Re-insert Dashes: In your programming language, re-insert dashes at positions 9, 14, 19, and 24 of the hexadecimal string to obtain the original UUID format.

Example:

CREATE TABLE test_table (
    field_binary BINARY(16) NULL,
    PRIMARY KEY (field_binary)
);

INSERT INTO test_table (field_binary) VALUES (UNHEX('110E8400E29B11D4A716446655440000'));

SELECT HEX(field_binary) AS field_binary FROM test_table;

The above is the detailed content of How to Store UUIDs as Numbers in MySQL for Enhanced Performance?. For more information, please follow other related articles on the PHP Chinese website!