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

How to Store UUIDs as Numbers in MySQL

Storing UUIDs as numbers offers performance advantages over storing them as strings. Here's a step-by-step guide on how to achieve this in MySQL:

1. Convert UUID to a Binary String:
   Remove the dashes from the UUID and convert it to a single binary string. For example, if your original UUID is "110E8400-E29B-11D4-A716-446655440000," it becomes "110E8400E29B11D4A716446655440000."

2. Create a BINARY Field in MySQL:
   Create a BINARY field in your MySQL table with a length that matches the length of your binary UUID (16 in our example). This can be done using:

   CREATE TABLE my_table (
       uuid BINARY(16) NOT NULL
   );

3. Insert the Binary UUID:
   Insert the binary UUID into the field using the UNHEX() function:

   INSERT INTO my_table (uuid) VALUES (UNHEX('110E8400E29B11D4A716446655440000'));

4. Retrieve and Convert to a UUID:
   When retrieving the UUID from the database, select the BINARY field and use the HEX() function to convert it back to a hyphenated UUID:

   SELECT HEX(uuid) AS original_uuid FROM my_table;

5. Ruby Code Integration:

   Insertion:
   Convert the UUID to a binary string using ActiveSupport::Digest::UUID.binary_pack(uuid). Then use the UNHEX() function to transform it to a hex string before inserting it into the field.

   Retrieval:
   Retrieve the binary UUID from the field and convert it to a hex string using HEX(). You can then use ActiveSupport::Digest::UUID.uuid_unpack() to convert the hex UUID back to its original format.

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