Comment dois-je stocker GUID dans les tables MySQL?

Je le stockerais comme un char (36).

En ajoutant à la réponse de ThaBadDawg, utilisez ces fonctions pratiques (grâce à un collègue plus sage) pour revenir de la chaîne de longueur 36 à un tableau d'octets de 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) est en fait un BINARY(16), choisissez votre saveur préférée

Pour mieux suivre le code, prenez l'exemple donné par le GUID ordonné par chiffres ci-dessous. (Caractères illégaux sont utilisés à des fins d'illustration - chaque lieu un caractère unique.) Les fonctions transformeront l'ordre des octets pour obtenir un ordre de bits pour indice supérieur de clustering. Le guid réorganisé est montré ci-dessous l'exemple.

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

Tirets supprimés:

123456789ABCDEFGHIJKLMNOPQRSTUVW
78563412BC9AFGDEHIJKLMNOPQRSTUVW

Char(36) serait un bon choix. La fonction UUID() de MySQL peut également être utilisée pour renvoyer un format de texte de 36 caractères (hexadécimal avec tirets) qui peut être utilisé pour récupérer de tels identifiants à partir de la base de données.

"mieux" dépend de ce que vous optimisez.

Combien vous souciez-vous de la taille/performance du stockage par rapport à la facilité de développement? Plus important encore-générez-vous suffisamment de GUID, ou récupérez-les assez fréquemment, que cela compte?

Si la réponse est "non", char(36) est plus que suffisant, et cela rend le stockage / la récupération des GUID simples. Sinon, binary(16) est raisonnable, mais vous devrez vous appuyer sur MySQL et / ou votre langage de programmation de choix pour convertir en arrière de la représentation de chaîne habituelle.

Binary(16) serait bien, mieux que l'utilisation de varchar(32).

La routine GuidToBinary publiée par KCD doit être modifiée pour tenir compte de la disposition des bits de l'horodatage dans la chaîne GUID. Si la chaîne représente un UUID version 1, comme ceux renvoyés par la routine mysql UUID (), alors les composants de temps sont incorporés dans les lettres 1-G, à l'exclusion du 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

Lorsque vous convertissez en binaire, le meilleur ordre d'indexation serait: EFG9ABC12345678D + le reste.

Vous ne voulez pas échanger 12345678 à 78563412 car big endian cède déjà le meilleur ordre d'octet d'index binaire. Cependant, vous voulez que les octets les plus significatifs soient déplacés devant les octets inférieurs. Par conséquent, EFG va en premier, suivi des bits du milieu et des bits inférieurs. Générez une douzaine D'UUID avec uuid () au cours d'une minute et vous devriez voir comment cet ordre donne le bon rang.

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 

Les deux premiers UUID ont été générés le plus près dans le temps. Ils ne varient que dans les 3 dernières grignotines du premier bloc. Ce sont les bits les moins significatifs de l'horodatage, qui signifie que nous voulons pousser vers la droite lorsque nous convertir à un indexables tableau d'octets. Comme contre-exemple, le dernier ID est le plus courant, mais l'algorithme d'échange du KCD le placerait avant le 3ème ID (3e avant dc, derniers octets du premier bloc).

L'ordre correct pour l'indexation serait:

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

Voir cet article pour les informations à l'appui: http://mysql.rjweb.org/doc.php/uuid

* * * notez que je ne divise pas la version grignoter du haut 12 les bits de l'horodatage. C'est le d grignoter de votre exemple. Je viens de jeter en avant. Donc, ma séquence binaire finit par être DEFG9ABC et ainsi de suite. Cela implique que tous mes UUID indexés commencent par le même grignotage. L'article fait la même chose.

Pour ceux qui trébuchent sur cela, il existe maintenant une bien meilleure alternative selon les recherches de Percona.

Il consiste à réorganiser les morceaux D'UUID pour une indexation optimale, puis à les convertir en binaire pour un stockage réduit.

Lire l'article complet ici

Je suggère d'utiliser les fonctions ci-dessous puisque celles mentionnées par @bigh_29 transforment mes GUID en nouveaux (pour des raisons que je ne comprends pas). En outre, ceux-ci sont un peu plus rapides dans les tests que j'ai faits sur mes tables. 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 ;

Si vous avez une valeur char/varchar formatée en tant que GUID standard, vous pouvez simplement la stocker en tant que binaire (16) en utilisant le CAST simple(MyString as BINARY16), sans toutes ces séquences époustouflantes de CONCAT + SUBSTR.

Les champs binaires(16) sont comparés / triés / indexés beaucoup plus rapidement que les chaînes, et prennent également deux fois moins d'espace dans la base de données