Store UUID v4 in MySQL

2018-07-02 19:42:58

I'm generating UUIDs using PHP, per the function found here: https://stackoverflow.com/a/15875555/339440

Now I want to store that in a MySQL database. What is the best/most efficient MySQL field format for storing UUID v4?

I currently have varchar(256), but I'm pretty sure that's much larger than necessary. I've found lots of almost-answers, but they're generally ambiguous about what form of UUID they're referring to, so I'm asking for the specific format.

Store it as VARCHAR(36) if you're looking to have an exact fit, or VARCHAR(255) which is going to work out with the same storage cost anyway. There's no reason to fuss over bytes here.

Remember VARCHAR fields are variable length, so the storage cost is proportional to how much data is actually in them, not how much data could be in them.

Storing it as BINARY is extremely annoying, the values are unprintable and can show up as garbage when running queries. There's rarely a reason to use the literal binary representation. Human-readable values can be copy-pasted, and worked with easily.

Some other platforms, like Postgres, have a proper UUID column which stores it internally in a more compact format, but displays it as human-readable, so you get the best of both approaches.

If you always have a UUID for each row, you could store it as CHAR(36) and save 1 byte per row over VARCHAR(36) .

uuid CHAR(36) CHARACTER SET ascii

In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes. https://dev.mysql.com/doc/refman/5.7/en/char.html

Though be careful with CHAR , it will always consume the full length defined even if the field is left empty. Also, make sure to use ASCII for character set, as CHAR would otherwise plan for worst case scenario (ie 3 bytes per character in utf8 , 4 in utf8mb4 )

[...] MySQL must reserve four bytes for each character in a CHAR CHARACTER SET utf8mb4 column because that is the maximum possible length. For example, MySQL must reserve 40 bytes for a CHAR(10) CHARACTER SET utf8mb4 column. https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html

Question is about storing an UUID in MySQL.

Since version 8.0 of mySQL you can use binary(16) with automatic conversion via UUID_TO_BIN/BIN_TO_UUID functions: https://mysqlserverteam.com/mysql-8-0-uuid-support/

Be aware that mySQL has also a fast way to generate UUIDs as primary key:

INSERT INTO t VALUES(UUID_TO_BIN(UUID(), true))

链接地址: http://www.djcxy.com/p/91476.html

上一篇: 在Java中生成全局唯一标识符

下一篇: 在MySQL中存储UUID v4