Comment dois-je stocker le GUID dans les tables MySQL?

Dois-je utiliser varchar (36) ou existe-t-il de meilleurs moyens de le faire?

Mon DBA m’a demandé quand je lui demandais quelle était la meilleure façon de stocker les GUID pour mes objects. Je devais stocker 16 octets alors que je pouvais faire la même chose en 4 octets avec un entier. Comme il m’a lancé ce défi, j’ai pensé que c’était le bon moment pour le mentionner. Cela étant dit…

Vous pouvez stocker un guid en tant que binary CHAR (16) si vous souhaitez utiliser au mieux l’espace de stockage.

Je le stockerais comme un char (36).

En ajoutant à la réponse de ThaBadDawg, utilisez ces fonctions pratiques (grâce à un collègue plus avisé) pour passer 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é ci-dessous avec le GUID ordonné par un chiffre. (Les caractères illégaux sont utilisés à des fins d’illustration – chaque emplacement est un caractère unique.) Les fonctions transformeront l’ordre des octets pour obtenir un ordre binary pour un regroupement d’index supérieur. Le guide de réorganisation est indiqué ci-dessous.

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

Tirets supprimés:

 123456789ABCDEFGHIJKLMNOPQRSTUVW 78563412BC9AFGDEHIJKLMNOPQRSTUVW 

char (36) serait un bon choix. De même, la fonction UUID () de MySQL peut être utilisée et renvoie un format de texte de 36 caractères (hexadécimal avec des tirets) qui peut être utilisé pour récupérer ces identifiants à partir de la firebase database.

“Mieux” dépend de ce que vous optimisez.

Quelle importance accordez-vous à la taille / performance du stockage par rapport à la facilité de développement? Plus important encore: générez-vous suffisamment de GUID, ou les récupérez-vous assez souvent, que cela compte?

Si la réponse est “non”, le caractère char(36) est plus que suffisant, ce qui rend le stockage / la récupération des GUID simples. Sinon, binary(16) est raisonnable, mais vous devrez vous appuyer sur MySQL et / ou sur votre langage de programmation de prédilection pour effectuer des conversions à partir de la représentation de chaîne habituelle.

Binary (16) serait bien, mieux que d’utiliser varchar (32).

La routine GuidToBinary envoyée par KCD doit être ajusté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 de version 1, comme ceux retournés par la routine mysql uuid (), les composants de temps sont incorporés dans les lettres 1-G, à l’exclusion de 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 binary, le meilleur ordre pour l’indexation serait: EFG9ABC12345678D + le rest.

Vous ne voulez pas permuter 12345678 à 78563412 car big endian fournit déjà le meilleur ordre d’octet d’index binary. Cependant, vous voulez que les octets les plus significatifs soient déplacés devant les octets inférieurs. Par conséquent, EFG passe en premier, suivi des bits du milieu et des bits inférieurs. Générez une dizaine d’UUIDs avec uuid () au cours d’une minute et vous devriez voir comment cette commande donne le bon classement.

 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 les plus proches dans le temps. Ils ne varient que dans les 3 dernières minutes du premier bloc. Ce sont les bits les moins significatifs de l’horodatage, ce qui signifie que nous voulons les pousser à droite lorsque nous les convertissons en un tableau d’octets indexables. Par exemple, le dernier identifiant est le plus récent, mais l’algorithme de permutation du KCD le placerait avant le 3ème identifiant (3e avant dc, dernier octet du premier bloc).

L’ordre correct pour l’indexation serait:

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

Voir cet article pour plus d’informations: http://mysql.rjweb.org/doc.php/uuid

*** notez que je ne partage pas la version grignotée des 12 bits supérieurs de l’horodatage. C’est le grignotage de votre exemple. Je le jette juste devant. Donc, ma séquence binary finit par être DEFG9ABC et ainsi de suite. Cela implique que tous mes UUID indexés commencent par le même quartet. L’article fait la même chose.

Pour ceux qui ne font que trébucher, il existe désormais une alternative bien meilleure selon les recherches de Percona.

Il consiste à réorganiser les blocs UUID pour une indexation optimale, puis à les convertir en fichiers binarys pour un stockage réduit.

Lire l’article complet ici

Je suggère d’utiliser les fonctions ci-dessous, car celles mentionnées par @ bigh_29 transforment mes règles en nouvelles (pour des raisons que je ne comprends pas). En outre, ils sont un peu plus rapides dans les tests que j’ai effectués 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 BINARY (16) en utilisant le simple CAST (MySsortingng AS BINARY16), sans toutes les séquences ahurissantes de CONCAT + SUBSTR.

Les champs BINARY (16) sont comparés / sortingés / indexés beaucoup plus rapidement que les chaînes, et prennent également deux fois moins d’espace dans la firebase database