Jak mam przechowywać GUID w tabelach MySQL?


146

Czy używam varchar (36), czy są na to lepsze sposoby?


1
„thaBadDawg” oferuje dobrą odpowiedź. Istnieje równoległy wątek w Stack Overflow, który omawia ten temat. Dodałem kilka komentarzy do tych wątków, które zawierają więcej szczegółów na ten link do zasobów. Oto link do pytania: stackoverflow.com/questions/547118/storing-mysql-guid-uuids - spodziewam się, że ten temat stanie się bardziej powszechny, gdy ludzie zaczną rozważać AWS i Aurorę.
Zack Jannsen

Odpowiedzi:


104

Mój DBA zapytał mnie, kiedy zapytałem o najlepszy sposób przechowywania identyfikatorów GUID dla moich obiektów, dlaczego potrzebowałem przechowywać 16 bajtów, skoro mogłem zrobić to samo w 4 bajtach za pomocą liczby całkowitej. Odkąd rzucił mi to wyzwanie, pomyślałem, że teraz jest dobry moment, aby o tym wspomnieć. Biorąc to pod uwagę ...

Możesz przechowywać guid jako binarny CHAR (16), jeśli chcesz maksymalnie optymalnie wykorzystać przestrzeń dyskową.


176
Ponieważ dzięki 16 bajtom możesz generować rzeczy w różnych bazach danych, na różnych maszynach, w różnym czasie i nadal płynnie łączyć dane ze sobą :)
Billy ONeal

4
potrzebuję odpowiedzi, czym tak naprawdę jest plik binarny char 16? nie char? nie binarne? Nie widzę tego typu w żadnym z narzędzi GUI mysql ani w żadnej dokumentacji w witrynie mysql. @BillyONeal
nawfal

3
@nawfal: Char to typ danych. BINARY to specyfikator typu względem typu. Jedynym efektem jest modyfikacja sposobu sortowania przez MySQL. Więcej informacji można znaleźć pod adresem dev.mysql.com/doc/refman/5.0/en/charset-binary-op.html . Oczywiście możesz po prostu użyć bezpośrednio typu BINARY, jeśli pozwala na to narzędzie do edycji bazy danych. (Starsze narzędzia nie znają typu danych binarnych, ale znają flagę kolumny binarnej)
Billy ONeal

2
pola CHAR i BINARY są zasadniczo takie same. Jeśli chcesz przenieść to na najbardziej podstawowe poziomy, CHAR jest polem binarnym oczekującym wartości od 0 do 255 z zamiarem reprezentowania tej wartości wartością odwzorowaną z tabeli przeglądowej (obecnie w większości przypadków UTF8). Pole BINARY oczekuje tego samego rodzaju wartości bez zamiaru reprezentowania wspomnianych danych z tabeli przeglądowej. Używałem CHAR (16) w 4.x dniach, ponieważ wtedy MySQL nie był tak dobry, jak jest teraz.
thaBadDawg

15
Istnieje kilka dobrych powodów, dla których identyfikator GUID jest znacznie lepszy niż automatyczna inkrementacja. Jeff Atwood wymienia te . Dla mnie największą zaletą korzystania z identyfikatora GUID jest to, że moja aplikacja nie będzie potrzebować połączenia z bazą danych w obie strony, aby poznać klucz jednostki: mógłbym wypełnić go programowo, czego nie byłbym w stanie zrobić, gdybym używał pola automatycznego zwiększania. To uratowało mnie przed kilkoma bólami głowy: dzięki GUID mogę zarządzać jednostką w ten sam sposób, niezależnie od tego, czy jednostka została już utrwalona, ​​czy jest zupełnie nowa.
Arialdo Martini

48

Przechowałbym to jako char (36).


5
Nie rozumiem, dlaczego powinieneś przechowywać -s.
Afshin Mehrabani

2
@AfshinMehrabani To proste, zrozumiałe, czytelne dla człowieka. Nie jest to oczywiście konieczne, ale jeśli przechowywanie tych dodatkowych bajtów nie zaszkodzi, to jest to najlepsze rozwiązanie.
user1717828

2
Przechowywanie myślników może nie być dobrym pomysłem, ponieważ spowoduje większe obciążenie. Jeśli chcesz, aby była czytelna dla człowieka, spraw, aby aplikacja była czytana za pomocą myślników.
Lucca Ferri

@AfshinMehrabani innym zagadnieniem jest analizowanie go z bazy danych. Większość implementacji oczekuje myślników w prawidłowym guidzie.
Ryan Gates

Możesz wstawić łączniki podczas pobierania, aby łatwo przekonwertować znak (32) na znak (36). użyj Insert FN mySql.
joedotnot

33

Dodając do odpowiedzi ThaBadDawg, użyj tych przydatnych funkcji (dzięki mojej mądrzejszej koleżance), aby uzyskać od 36 długości łańcucha z powrotem do tablicy bajtów 16.

DELIMITER $$

CREATE FUNCTION `GuidToBinary`(
    $Data VARCHAR(36)
) RETURNS binary(16)
DETERMINISTIC
NO SQL
BEGIN
    DECLARE $Result BINARY(16) DEFAULT NULL;
    IF $Data IS NOT NULL THEN
        SET $Data = REPLACE($Data,'-','');
        SET $Result =
            CONCAT( UNHEX(SUBSTRING($Data,7,2)), UNHEX(SUBSTRING($Data,5,2)),
                    UNHEX(SUBSTRING($Data,3,2)), UNHEX(SUBSTRING($Data,1,2)),
                    UNHEX(SUBSTRING($Data,11,2)),UNHEX(SUBSTRING($Data,9,2)),
                    UNHEX(SUBSTRING($Data,15,2)),UNHEX(SUBSTRING($Data,13,2)),
                    UNHEX(SUBSTRING($Data,17,16)));
    END IF;
    RETURN $Result;
END

$$

CREATE FUNCTION `ToGuid`(
    $Data BINARY(16)
) RETURNS char(36) CHARSET utf8
DETERMINISTIC
NO SQL
BEGIN
    DECLARE $Result CHAR(36) DEFAULT NULL;
    IF $Data IS NOT NULL THEN
        SET $Result =
            CONCAT(
                HEX(SUBSTRING($Data,4,1)), HEX(SUBSTRING($Data,3,1)),
                HEX(SUBSTRING($Data,2,1)), HEX(SUBSTRING($Data,1,1)), '-', 
                HEX(SUBSTRING($Data,6,1)), HEX(SUBSTRING($Data,5,1)), '-',
                HEX(SUBSTRING($Data,8,1)), HEX(SUBSTRING($Data,7,1)), '-',
                HEX(SUBSTRING($Data,9,2)), '-', HEX(SUBSTRING($Data,11,6)));
    END IF;
    RETURN $Result;
END
$$

CHAR(16)jest właściwie a BINARY(16), wybierz preferowany smak

Aby lepiej postępować zgodnie z kodem, weź przykład z podanym poniżej identyfikatorem GUID uporządkowanym cyframi. (Niedozwolone znaki są używane w celach ilustracyjnych - każde miejsce jest unikalnym znakiem). Funkcje przekształcają kolejność bajtów, aby uzyskać kolejność bitów dla lepszego grupowania indeksów. Ponownie uporządkowany przewodnik jest pokazany poniżej przykładu.

12345678-9ABC-DEFG-HIJK-LMNOPQRSTUVW
78563412-BC9A-FGDE-HIJK-LMNOPQRSTUVW

Usunięte kreski:

123456789ABCDEFGHIJKLMNOPQRSTUVW
78563412BC9AFGDEHIJKLMNOPQRSTUVW

Oto powyższy GuidToBinary bez usuwania myślników z ciągu: CREATE FUNCTION GuidToBinary($ guid char (36)) RETURNS binary (16) RETURN CONCAT (UNHEX (SUBSTRING ($ guid, 7, 2)), UNHEX (SUBSTRING ($ guid, 5, 2)), UNHEX (SUBSTRING ($ guid, 3, 2)), UNHEX (SUBSTRING ($ guid, 1, 2)), UNHEX (SUBSTRING ($ guid, 12, 2)), UNHEX (SUBSTRING ($ guid, 10, 2)), UNHEX (SUBSTRING ($ guid, 17, 2)), UNHEX (SUBSTRING ($ guid, 15, 2)), UNHEX (SUBSTRING ($ guid, 20, 4)), UNHEX (SUBSTRING ($ guid, 25, 12)));
Jonathan Oliver

4
Dla ciekawskich funkcje te są lepsze niż tylko UNHEX (REPLACE (UUID (), '-', '')), ponieważ układa bity w kolejności, która będzie działać lepiej w indeksie klastrowym.
Slashterix

Jest to bardzo pomocne, ale wydaje mi się, że można by to poprawić za pomocą źródła dla CHARi BINARYrównoważności ( dokumentacja wydaje się sugerować, że istnieją ważne różnice i wyjaśnienie, dlaczego wydajność indeksu klastrowego jest lepsza z uporządkowanymi bajtami.
Patrick M

Kiedy używam tego, mój przewodnik się zmienia. Próbowałem wstawić go przy użyciu zarówno unhex (replace (string, '-', '')), jak i funkcji powyżej, a kiedy konwertuję je z powrotem przy użyciu tych samych metod, wybrany guid nie jest tym, który został wstawiony. Co zmienia guid? Wszystko, co zrobiłem, to skopiowanie kodu z góry.
vsdev

@JonathanOliver Czy mógłbyś udostępnić kod funkcji BinaryToGuid ()?
Arun Avanathan

27

char (36) byłby dobrym wyborem. Można również użyć funkcji UUID () MySQL, która zwraca 36-znakowy format tekstowy (szesnastkowo z myślnikami), który może być użyty do pobrania takich identyfikatorów z bazy danych.


19

„Lepsze” zależy od tego, do czego optymalizujesz.

Jak bardzo zależy Ci na rozmiarze / wydajności pamięci masowej w porównaniu z łatwością rozwoju? Co ważniejsze - czy generujesz wystarczającą liczbę identyfikatorów GUID lub pobierasz je wystarczająco często, że ma to znaczenie?

Jeśli odpowiedź brzmi „nie”, char(36)jest więcej niż wystarczająco dobra i sprawia, że ​​przechowywanie / pobieranie identyfikatorów GUID staje się proste. W przeciwnym razie binary(16)jest to rozsądne, ale będziesz musiał oprzeć się na MySQL i / lub wybranym języku programowania, aby konwertować w tę iz powrotem ze zwykłej reprezentacji ciągu.


2
Jeśli udostępniasz oprogramowanie (np. Stronę internetową) i nie sprzedajesz / nie instalujesz w kliencie, zawsze możesz zacząć od znaku (36), aby ułatwić rozwój we wczesnym etapie oprogramowania i zmienić na bardziej kompaktową format, gdy system rośnie w użyciu i zaczyna wymagać optymalizacji.
Xavi Montero

1
Największą wadą znacznie większego znaku (36) jest to, ile miejsca zajmie indeks. Jeśli masz dużą liczbę rekordów w bazie danych, podwajasz rozmiar indeksu.
bpeikes


7

Procedura GuidToBinary wysłana przez KCD powinna zostać zmodyfikowana, aby uwzględnić układ bitów znacznika czasu w ciągu GUID. Jeśli ciąg reprezentuje identyfikator UUID wersji 1, taki jak zwracany przez procedurę uuid () mysql, to składniki czasu są osadzone w literach 1-G, z wyłączeniem D.

12345678-9ABC-DEFG-HIJK-LMNOPQRSTUVW
12345678 = least significant 4 bytes of the timestamp in big endian order
9ABC     = middle 2 timestamp bytes in big endian
D        = 1 to signify a version 1 UUID
EFG      = most significant 12 bits of the timestamp in big endian

Podczas konwersji do formatu binarnego najlepsza kolejność indeksowania będzie następująca: EFG9ABC12345678D + reszta.

Nie chcesz zamienić 12345678 na 78563412, ponieważ big endian już daje najlepszą kolejność bajtów indeksu binarnego. Jednak chcesz, aby najbardziej znaczące bajty zostały przeniesione przed młodsze bajty. Stąd EFG idzie pierwszy, a następnie środkowe bity i niższe bity. Wygeneruj kilkanaście UUID za pomocą uuid () w ciągu minuty i powinieneś zobaczyć, jak to zamówienie daje prawidłową pozycję.

select uuid(), 0
union 
select uuid(), sleep(.001)
union 
select uuid(), sleep(.010)
union 
select uuid(), sleep(.100)
union 
select uuid(), sleep(1)
union 
select uuid(), sleep(10)
union
select uuid(), 0;

/* output */
6eec5eb6-9755-11e4-b981-feb7b39d48d6
6eec5f10-9755-11e4-b981-feb7b39d48d6
6eec8ddc-9755-11e4-b981-feb7b39d48d6
6eee30d0-9755-11e4-b981-feb7b39d48d6
6efda038-9755-11e4-b981-feb7b39d48d6
6f9641bf-9755-11e4-b981-feb7b39d48d6
758c3e3e-9755-11e4-b981-feb7b39d48d6 

Pierwsze dwa identyfikatory UUID zostały wygenerowane najbliżej w czasie. Różnią się one tylko w ostatnich 3 skubaniach pierwszego bloku. Są to najmniej znaczące bity znacznika czasu, co oznacza, że ​​chcemy przesunąć je w prawo, gdy konwertujemy to na indeksowalną tablicę bajtów. Jako przykład licznika, ostatni identyfikator jest najbardziej aktualny, ale algorytm zamiany KCD umieściłby go przed trzecim identyfikatorem (3e przed dc, ostatnie bajty z pierwszego bloku).

Prawidłowa kolejność indeksowania to:

1e497556eec5eb6... 
1e497556eec5f10... 
1e497556eec8ddc... 
1e497556eee30d0... 
1e497556efda038... 
1e497556f9641bf... 
1e49755758c3e3e... 

Dodatkowe informacje można znaleźć w tym artykule: http://mysql.rjweb.org/doc.php/uuid

*** Zwróć uwagę, że nie dzielę skubania wersji od wysokich 12 bitów znacznika czasu. To jest skubać D z twojego przykładu. Po prostu rzucam to przed siebie. Więc moja sekwencja binarna kończy się na DEFG9ABC i tak dalej. Oznacza to, że wszystkie moje indeksowane identyfikatory UUID zaczynają się od tego samego skubacza. Artykuł robi to samo.


czy ma to na celu oszczędność miejsca? lub żeby ich sortowanie było przydatne?
MD004

1
@ MD004. Tworzy lepszy indeks sortowania. Przestrzeń pozostaje taka sama.
bigh_29

5

Dla tych, którzy się na to natkną, jest teraz znacznie lepsza alternatywa, jak wynika z badań przeprowadzonych przez Perconę.

Obejmuje reorganizację fragmentów UUID w celu optymalnego indeksowania, a następnie konwersję na binarną w celu zmniejszenia ilości pamięci.

Przeczytaj cały artykuł tutaj


Czytałem ten artykuł wcześniej. Uważam to za bardzo interesujące, ale jak powinniśmy wykonać zapytanie, jeśli chcemy filtrować według identyfikatora, który jest binarny? Myślę, że musimy ponownie przekląć i zastosować kryteria. Czy to takie wymagające? Po co przechowywać binarny (16) (na pewno jest lepszy niż varchar (36)) zamiast biginta o wielkości 8 bajtów?
Maximus Decimus

2
Jest zaktualizowany artykuł z MariaDB, który powinien odpowiedzieć na twoje pytanie mariadb.com/kb/en/mariadb/guiduuid-performance
sleepycal

fwiw, UUIDv4 jest całkowicie losowy i nie wymaga fragmentacji.
Mahmoud Al-Qudsi,

2

Sugerowałbym użycie poniższych funkcji, ponieważ te wymienione przez @ bigh_29 przekształcają moje guidery w nowe (z powodów, których nie rozumiem). Są też trochę szybsze w testach, które przeprowadziłem na moich stołach. https://gist.github.com/damienb/159151

DELIMITER |

CREATE FUNCTION uuid_from_bin(b BINARY(16))
RETURNS CHAR(36) DETERMINISTIC
BEGIN
  DECLARE hex CHAR(32);
  SET hex = HEX(b);
  RETURN LOWER(CONCAT(LEFT(hex, 8), '-', MID(hex, 9,4), '-', MID(hex, 13,4), '-', MID(hex, 17,4), '-', RIGHT(hex, 12)));
END
|

CREATE FUNCTION uuid_to_bin(s CHAR(36))
RETURNS BINARY(16) DETERMINISTIC
RETURN UNHEX(CONCAT(LEFT(s, 8), MID(s, 10, 4), MID(s, 15, 4), MID(s, 20, 4), RIGHT(s, 12)))
|

DELIMITER ;

-4

jeśli masz wartość char / varchar sformatowaną jako standardowy identyfikator GUID, możesz po prostu zapisać ją jako BINARY (16) za pomocą prostego CAST (MyString AS BINARY16), bez tych wszystkich zadziwiających sekwencji CONCAT + SUBSTR.

Pola BINARY (16) są porównywane / sortowane / indeksowane znacznie szybciej niż łańcuchy, a także zajmują dwa razy mniej miejsca w bazie danych


2
Uruchomienie tego zapytania pokazuje, że CAST konwertuje ciąg znaków UUID na bajty ASCII: set @a = uuid (); select @a, hex (cast (@a AS BINARY (16))); Otrzymuję 16f20d98-9760-11e4-b981-feb7b39d48d6: 3136663230643938 2D 39373630 2D 3131 (spacje dodane do formatowania). 0x31 = ascii 1, 0x36 = ascii 6. Otrzymujemy nawet 0x2D, ​​czyli myślnik. Nie różni się to zbytnio od zwykłego przechowywania guid jako łańcucha, z wyjątkiem tego, że obcinasz ciąg na szesnastym znaku, co odcina część identyfikatora, która jest specyficzna dla komputera.
bigh_29

Tak, to jest po prostu obcięcie. select CAST("hello world, this is as long as uiid" AS BINARY(16));produkujehello world, thi
MD004
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.