minusculas - ¿Cómo debo almacenar GUID en tablas MySQL?

sql no distinguir mayusculas

Question

¿Uso varchar (36) o hay alguna forma mejor de hacerlo?

Answer 1

"Mejor" depende de para qué estás optimizando.

¿Cuánto te preocupa el tamaño / rendimiento de almacenamiento frente a la facilidad de desarrollo? Más importante aún: ¿está generando suficientes GUID o buscándolos con la frecuencia suficiente como para que importe?

Si la respuesta es "no", char(36) es más que suficiente, y hace que almacenar / buscar GUID sea muy simple. De lo contrario, binary(16) es razonable, pero tendrá que apoyarse en MySQL y / o en su lenguaje de programación de elección para convertir hacia adelante y hacia atrás desde la representación de cadena habitual.

Answer 2

Agregando a la respuesta de ThaBadDawg, use estas útiles funciones (gracias a un colega mío más sabio) para pasar de una cadena de 36 longitudes a una matriz de bytes 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) es en realidad BINARY(16) , elija su sabor preferido

Para seguir mejor el código, tome el ejemplo dado el GUID ordenado por dígitos a continuación. (Los caracteres ilegales se utilizan con fines ilustrativos; cada uno coloca un carácter único). Las funciones transformarán el orden de bytes para lograr un orden de bits para una agrupación de índices superior. El guid reordenado se muestra debajo del ejemplo.

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

Guiones eliminados:

123456789ABCDEFGHIJKLMNOPQRSTUVW
78563412BC9AFGDEHIJKLMNOPQRSTUVW

Answer 3

Binario (16) estaría bien, mejor que el uso de varchar (32).

Answer 4

La rutina GuidToBinary publicada por KCD debe ajustarse para tener en cuenta el diseño de bits de la marca de tiempo en la cadena GUID. Si la cadena representa un UUID de versión 1, como los devueltos por la rutina uuid () mysql, los componentes de hora se incrustan en las letras 1-G, excluyendo el 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

Cuando convierte a binario, el mejor orden para la indexación sería: EFG9ABC12345678D + el resto.

No desea cambiar 12345678 a 78563412 porque big endian ya produce el mejor orden de bytes de índice binario. Sin embargo, desea que los bytes más significativos se muevan delante de los bytes inferiores. Por lo tanto, EFG va primero, seguido de los bits medios y los bits más bajos. Genere una docena de UUID con uuid () en el transcurso de un minuto y debería ver cómo este orden arroja el rango correcto.

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 

Los dos primeros UUID se generaron más cerca en el tiempo. Solo varían en los últimos 3 nibbles del primer bloque. Estos son los bits menos significativos de la marca de tiempo, lo que significa que queremos empujarlos hacia la derecha cuando convertimos esto en una matriz de bytes indexable. Como ejemplo de contador, el último ID es el más reciente, pero el algoritmo de intercambio del KCD lo pondría antes del tercer ID (3e antes del DC, últimos bytes del primer bloque).

El orden correcto para la indexación sería:

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

Consulte este artículo para obtener información de respaldo: http://mysql.rjweb.org/doc.php/uuid

*** tenga en cuenta que no divido el nibble de versión de los 12 bits altos de la marca de tiempo. Este es el nudillo D de tu ejemplo. Solo lo tiro al frente. Entonces mi secuencia binaria termina siendo DEFG9ABC y así sucesivamente. Esto implica que todos mis UUID indexados comienzan con el mismo mordisco. El artículo hace lo mismo.

Answer 5

Lo almacenaría como un char (36).

Answer 6

Mi DBA me preguntó cuando le pregunté sobre la mejor manera de almacenar GUID para mis objetos por qué necesitaba almacenar 16 bytes cuando podía hacer lo mismo en 4 bytes con un Entero. Desde que él me presentó ese desafío, pensé que era un buen momento para mencionarlo. Habiendo dicho eso...

Puede almacenar un guid como un binario CHAR (16) si desea aprovechar al máximo el espacio de almacenamiento.

Answer 7

Para aquellos que están tropezando con esto, ahora hay una alternativa mucho mejor según Percona.

Consiste en reorganizar los fragmentos de UUID para una indexación óptima y luego convertirlos en binarios para reducir el almacenamiento.

Lea el artículo completo here

Answer 8

Sugeriría usar las funciones siguientes, ya que las que menciona @ bigh_29 transforman mis guías en nuevas (por razones que no entiendo). Además, estos son un poco más rápidos en las pruebas que hice en mis tablas. 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 ;

Answer 9

char (36) sería una buena elección. También se puede utilizar la función UUID () de MySQL, que devuelve un formato de texto de 36 caracteres (hexadecimal con guiones) que se puede utilizar para la recuperación de dichos ID de la base de datos.

Answer 10

si tiene un valor char / varchar formateado como el GUID estándar, puede simplemente almacenarlo como BINARY (16) usando el simple CAST (MyString AS BINARY16), sin todas esas secuencias alucinantes de CONCAT + SUBSTR.

Los campos BINARY (16) se comparan / ordenan / indexan mucho más rápido que las cadenas, y también ocupan dos veces menos espacio en la base de datos