How to store uuid as number?

by

If I understand correctly, you’re using UUIDs in your primary column? People will say that a regular (integer) primary key will be faster , but there’s another way using MySQL’s dark side. In fact, MySQL is faster using binary than anything else when indexes are required.

Since UUID is 128 bits and is written as hexadecimal, it’s very easy to speed up and store the UUID.

First, in your programming language remove the dashes

From 110E8400-E29B-11D4-A716-446655440000 to 110E8400E29B11D4A716446655440000.

Now it’s 32 chars (like an MD5 hash, which this also works with).

Since a single BINARY in MySQL is 8 bits in size, BINARY(16) is the size of a UUID (8*16 = 128).

You can insert using:

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

and query using:

SELECT HEX(FieldBin) AS FieldBin FROM Table

Now in your programming language, re-insert the dashes at the positions 9, 14, 19 and 24 to match your original UUID. If the positions are always different you could store that info in a second field.

Full example :

CREATE TABLE  `test_table` (
    `field_binary` BINARY( 16 ) NULL ,
    PRIMARY KEY (  `field_binary` )
) ENGINE = INNODB ;

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

SELECT HEX(field_binary) AS field_binary FROM `test_table`

If you want to use this technique with any hex string, always do length / 2 for the field length. So for a sha512, the field would be BINARY (64) since a sha512 encoding is 128 characters long.

More Related Contents:

  1. How should I store GUID in MySQL tables?
  2. UUID performance in MySQL?
  3. How to generate a UUIDv4 in MySQL?
  4. Get records with max value for each group of grouped SQL results
  5. How do I escape reserved words used as column names? MySQL/Create Table
  6. How to store multiple options in a single table?
  7. How to populate a table with a range of dates?
  8. Get the new record primary key ID from MySQL insert query?
  9. What is the opposite of GROUP_CONCAT in MySQL?
  10. How to Perform an UPSERT so that I can use both new and old values in update part
  11. brew install mysql on macOS
  12. TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT maximum storage sizes
  13. How do I check to see if a value is an integer in MySQL?
  14. Add 2 hours to current time in MySQL?
  15. Store image to database blob; retrieve from db into Picturebox
  16. MySQL/MariaDB – order by inside subquery
  17. Can MySQL replace multiple characters?
  18. My MySQL trigger doesn’t work, simple syntax, not complicated
  19. Removing MySQL 5.7 Completely [closed]
  20. script to convert mysql dump sql file into format that can be imported into sqlite3 db
  21. How can VBA connect to MySQL database in Excel?
  22. Sum results of a few queries and then find top 5 in SQL
  23. ERROR 1148: The used command is not allowed with this MySQL version
  24. ERROR 1356 (HY000): View ‘mysql.user’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
  25. Too many auto increments with ON DUPLICATE KEY UPDATE
  26. Equivalent of MSSQL IDENTITY Column in MySQL
  27. #1060 – Duplicate column name ‘id’
  28. Update multiple rows with multiple ‘where’ clauses for each individual row
  29. WHERE datetime older than some time (eg. 15 minutes)
  30. Is there a way to use ON DUPLICATE KEY to Update all that I wanted to insert?

Leave a Comment