Comment dois-je stocker GUID dans les tables MySQL?

Mon DBA m'a demandé quand je les ai interrogés sur la meilleure façon de conserver les Guid pour mes objets pourquoi j'avais besoin de stocker 16 octets quand je pourrais faire la même chose dans les 4 octets avec un Entier. Depuis qu'il a mis ce défi là pour moi, j'ai pensé que le moment était le bon moment de le mentionner. Cela étant dit...

Vous pouvez stocker un guid comme un CHAR(16) binaire si vous voulez faire le plus de l'utilisation optimale de l'espace de stockage.

Ajoutant à la réponse par ThaBadDawg, l'utilisation de ces fonctions pratiques (grâce à une sage collègue de la mine) pour obtenir de 36 longueur de la chaîne à 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

À suivre le code mieux, prendre l'exemple donné les chiffres-commandé GUID ci-dessous. (Caractères illégaux sont utilisés à des fins d'illustration - chaque endroit un caractère unique.) Les fonctions de transformer l'octet de commande pour obtenir un peu d'ordre supérieur de l'indice de clustering. La refonte guid est indiqué ci-dessous l'exemple.

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

Tirets supprimée:

123456789ABCDEFGHIJKLMNOPQRSTUVW
78563412BC9AFGDEHIJKLMNOPQRSTUVW

"Mieux" dépend de ce que vous êtes à l'optimisation pour les.

Combien vous vous souciez de la taille de stockage/performance contre, la facilité de développement? Plus important encore, êtes - vous générer suffisamment de Guid, ou de les chercher assez fréquemment, que c'est important?

Si la réponse est "non", char(36) est plus assez bon, et il fait de stockage/récupération de Guid morts-simple. Sinon, binary(16) est raisonnable, mais vous aurez à appuyer sur MySQL et/ou de votre langage de programmation de choix pour convertir en arrière à partir de l'habituelle représentation sous forme de chaîne.

La GuidToBinary routine posté par KCD devrait être modifié pour tenir compte de la structure de bits de l'horodatage dans la chaîne GUID. Si la chaîne de caractères représente une version 1 UUID, comme ceux retournés par l'uuid() de mysql routine, le temps que les composants sont intégrés dans les lettres 1-G, à l'exclusion de la 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

Lors de la conversion en binaire, le meilleur ordre pour l'indexation serait: EFG9ABC12345678D + le reste.

Vous ne voulez pas de swap 12345678 à 78563412 parce que big endian déjà produit les meilleurs index binaire d'ordre des octets. Cependant, vous voulez le plus important octets déplacé en face de la basse-octets. Par conséquent, EFG y aller en premier, suivie par le moyen de bits et bits de poids faible. Générer une douzaine Uuid avec l'uuid() au cours d'une minute et vous devriez voir comment cet ordre donne le rang correct.

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 généré le plus proche dans le temps. Ils ne varient dans le dernier 3 unités du premier bloc. Ce sont les bits de poids faible de l'horodatage, ce qui signifie que nous voulons pour les pousser vers la droite lorsque nous convertir à un indexables tableau d'octets. Comme un contre-exemple, le dernier ID est le plus courant, mais le KCD de l'algorithme de permutation serait de le placer avant le 3ème ID (3e avant dc, derniers octets à partir du premier bloc).

L'ordre correct pour l'indexation serait:

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

Voir cet article pour plus de renseignements à l'appui: http://mysql.rjweb.org/doc.php/uuid

*** notez que je n'ai pas diviser la version grignoter de la haute 12 bits de l'horodatage. C'est le D grignoter à partir de votre exemple. Je viens de jeter en avant. Donc, mon séquence binaire finit par être DEFG9ABC et ainsi de suite. Cela implique que tous mes indexé Uuid de démarrer avec le même grignoter. L'article fait la même chose.

Je vous suggérons d'utiliser les fonctions ci-dessous depuis que celles mentionnées par @bigh_29 transforme mon guid dans les nouveaux (pour des raisons que je ne comprends pas). Aussi, ce sont un peu plus vite dans les tests que j'ai fait 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 ;