![]() 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 Simple solution, obviously, but hopefully this will save someone the time that I just lost.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). UPDATE some_table SET some_field=REPLACE(some_field, '-', '') To resolve this, I just split it into two queries: UPDATE some_table SET some_field=(SELECT uuid()) It seems when surrounding the subquery to generate a UUID with REPLACE, it only runs the UUID query once, which probably makes perfect sense as an optimization to much smarter developers than I, but it didn't to me. UPDATE some_table SET some_field=(REPLACE(SELECT uuid(), '-', '')) Doesn't matter how I situated the parentheses, the same thing happens. ![]() Then all the resulting values were the same (not subtly different - I quadruple checked with a GROUP BY some_field query). But when I tried this: UPDATE some_table SET some_field=(REPLACE((SELECT uuid()), '-', '')) I found the answer by Rakesh to be the simplest that worked well, except in cases where you want to strip the dashes.įor reference: UPDATE some_table SET some_field=(SELECT uuid()) Just a minor addition to make as I ended up with a weird result when trying to modify the UUIDs as they were generated. I'm using MySQL Server version: 5.5.40-0+wheezy1 (Debian) MySQL > select city, id from CityPopCountry limit 10 If you want visibly different keys, try this: update CityPopCountry set id = (select md5(UUID())) The approved solution does create unique IDs but on first glance they look identical, only the first few characters differ.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |