AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Mysql uuid char or varchar9/16/2023 This applies when the UUID is your primary key or if you do a lot of range queries on it. UUID v4, which is randomized) will hurt severely. In other words, I would choose one of these types.įor performance, the randomness of random UUIDs (i.e. If this is a primary key, I would definitely not waste any space on it, as it becomes part of every secondary index as well. The latter might give you headaches because of having to map between one value and two columns. The former might give you headaches because manual queries do not (in a straightforward way) give you readable/copyable values. The most space-efficient would be BINARY(16) or two BIGINT UNSIGNED. Return strlen($data) = 32 ? hex2bin($data) : false Įdit: If you only need the column pretty when reading the database, a statement like the following is sufficient: ALTER TABLE test ADD uuid_pretty CHAR(36) GENERATED ALWAYS AS (CONCAT_WS('-', LEFT(HEX(uuid_ugly), 8), SUBSTR(HEX(uuid_ugly), 9, 4), SUBSTR(HEX(uuid_ugly), 13, 4), SUBSTR(HEX(uuid_ugly), 17, 4), RIGHT(HEX(uuid_ugly), 12))) VIRTUAL $data = $native ? random_bytes(16) : openssl_random_pseudo_bytes(16) Static $native = function_exists('random_bytes') I would separate the UUID generation logic from the display logic to ensure that existing data are never changed and errors are detectable: function guidv4($prettify = false) You could probably even do it in the database as virtual columns and stored procedures so the app never bothers with the raw data. It is trivial to write a function in your app – like the one you referenced – to deal with this for you. You shouldn't need to be looking up UUIDs manually in the db if you have to, HEX() and x'deadbeef01' literals are your friends. Storing raw is really not as painful as others suggest because any decent db admin tool will display/dump the octets as hexadecimal, rather than literal bytes of "text". If your data is small enough that storing as them as text doesn't hurt performance, you probably don't need UUIDs over boring integer keys. Most efficient is definitely BINARY(16), storing the human-readable characters uses over double the storage space, and means bigger indices and slower lookup. For example, MySQL must reserve 40 bytes for a CHAR(10) MySQL must reserve four bytes for each character in a CHARĬHARACTER SET utf8mb4 column because that is the maximum possible 3 bytes per character in utf8, 4 in utf8mb4) Also, make sure to use ASCII for character set, as CHAR would otherwise plan for worst case scenario (i.e. Though be careful with CHAR, it will always consume the full length defined even if the field is left empty. More than 255 bytes, two length bytes if values may require more than A column uses one length byte if values require no The length prefix indicates the number ofīytes in the value. In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte 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).
0 Comments
Read More
Leave a Reply. |