Jak przechowywać GUID w tabelach MySQL?

Zapisałbym jako char (36).

Dodając do odpowiedzi Thabaddawga, użyj tych przydatnych funkcji (dzięki mądrzejszemu koledze), 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 rzeczywiście BINARY(16), Wybierz preferowany smak

Aby lepiej podążać za kodem, weźmy przykład podany poniżej, uporządkowany cyfrowo identyfikator GUID. (Niedozwolone znaki są używane w celach ilustracyjnych - każde miejsce ma unikalny charakter.) Funkcje przekształcą kolejność bajtów w celu uzyskania kolejności bitowej dla lepsze grupowanie indeksów. Zmiana kolejności guid jest pokazana poniżej przykładu.

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

Usunięte myślniki:

123456789ABCDEFGHIJKLMNOPQRSTUVW
78563412BC9AFGDEHIJKLMNOPQRSTUVW

Char (36) byłby dobrym wyborem. Można również użyć funkcji uuid() MySQL, która zwraca 36-znakowy format tekstu (hex z myślnikami), który może być używany do pobierania takich identyfikatorów z db.

"lepsze" zależy od tego, do czego optymalizujesz.

Jak bardzo zależy ci na wielkości/wydajności pamięci a łatwości rozwoju? Co ważniejsze-czy generujesz wystarczająco dużo GUID, czy pobierasz je na tyle często, że ma to znaczenie?

Jeśli odpowiedź brzmi "Nie", char(36) jest wystarczająco dobra i sprawia, że przechowywanie/pobieranie plików GUID jest martwe-proste. W przeciwnym razie binary(16) jest rozsądne, ale będziesz musiał oprzeć się na MySQL i / lub wybranym języku programowania, aby konwertować tam iz powrotem od zwykłej reprezentacji łańcuchów.

Binary(16) byłoby w porządku, lepsze niż użycie varchar(32).

Procedura guidtobinary opublikowana przez KCD powinna zostać zmodyfikowana, aby uwzględnić układ bitowy znacznika czasu w łańcuchu GUID. Jeśli łańcuch znaków reprezentuje UUID w wersji 1, jak te zwracane przez 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

Po konwersji na binarny, najlepszym porządkiem indeksowania będzie: EFG9ABC12345678D + reszta.

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 uuid zostały wygenerowane najbliżej czasu. Różnią się one tylko w ostatnich 3 nibbles pierwszego bloku. Są to najmniej znaczące bity znacznika czasu, które oznacza to, że chcemy je przesunąć w prawo, gdy przekonwertujemy 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... 

Zobacz ten artykuł, aby uzyskać dodatkowe informacje: http://mysql.rjweb.org/doc.php/uuid

*** zauważ, że nie dzielę wersji z high 12 fragmenty znacznika czasu. To jest D skubanie z twojego przykładu. Rzucam ją z przodu. Więc moja Sekwencja binarna kończy się DEFG9ABC i tak dalej. Oznacza to, że wszystkie moje indeksowane uuid zaczynają się od tego samego skubania. Artykuł robi to samo.

Dla tych, którzy po prostu natykają się na to, istnieje teraz znacznie lepsza alternatywa według badań Percona.

Polega na reorganizacji fragmentów UUID w celu optymalnego indeksowania, a następnie konwersji na binarne w celu zmniejszenia pamięci masowej.

Przeczytaj cały artykuł tutaj

Sugerowałbym użycie poniższych funkcji, ponieważ te wymienione przez @bigh_29 przekształca moje GUID na nowe (z powodów, których nie rozumiem). Ponadto są one nieco szybsze w testach, które zrobił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 ;

Jeśli masz wartość char/varchar sformatowaną jako standardowy GUID, możesz po prostu zapisać ją jako binarną(16) używając prostego CAST(MyString jako BINARY16), bez tych wszystkich zadziwiających sekwencji CONCAT + SUBSTR.

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