multiple - ¿Cómo genero una cadena aleatoria única para una de mis columnas de la tabla MySql?

sql server unique constraint multiple columns

Question

Estoy usando MySql 5.5.37. Tengo una tabla con las siguientes columnas.

+------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| ID | varchar(32) | NO | PRI | NULL | |
| CODE | varchar(6) | NO | UNI | NULL | |

La columna de código es única y mi columna de ID es un GUID. Tengo una serie de filas que me gustaría actualizar, sujeto a algunos criterios en la tabla anterior (por ejemplo, DONDE COLUMNA1 = 0). ¿Cómo genero códigos de 6 caracteres aleatorios y únicos (idealmente letras y números) para mi columna CÓDIGO de manera que no violen la restricción única en mi tabla? Tenga en cuenta que las columnas de la tabla que no cumplen con los criterios (por ejemplo, donde COLUMN1 <> 0) ya tienen valores únicos para la columna CÓDIGO.

Edición: esto es diferente a esta pregunta: generar una cadena de 8 caracteres al azar y única usando MySQL porque ese enlace trata con los ID que son numéricos. Mis identificaciones son cadenas de 32 caracteres. Además, su solución no tiene en cuenta el hecho de que puede haber valores en la tabla antes de ejecutar las sentencias que deseo ejecutar que generarán valores únicos para la columna en cuestión.

Answer 1

Este es complicado, pero creo que he llegado a una buena solución:

DROP FUNCTION IF EXISTS getRandomAlphaNumeric;
DELIMITER $$
CREATE FUNCTION getRandomAlphaNumeric() RETURNS CHAR(6)
DETERMINISTIC
BEGIN
SELECT
CONCAT (
CHAR(FLOOR(RAND()*10)+48), CHAR(FLOOR(RAND()*26)+65), CHAR(FLOOR(RAND()*26)+97),
CHAR(FLOOR(RAND()*10)+48), CHAR(FLOOR(RAND()*26)+65), CHAR(FLOOR(RAND()*26)+97)
) INTO @code
;
RETURN @code;
END
$$
DELIMITER ;
DROP PROCEDURE IF EXISTS generateCodes;
DELIMITER $$
CREATE PROCEDURE generateCodes()
BEGIN
SET @count = 0;
SELECT COUNT(1) INTO @count FROM demo.codes;
SET @i = 0;
WHILE @i < @count DO
PREPARE stmt FROM "SELECT @id := id, @itemCode := code FROM demo.codes p LIMIT ?, 1;";
EXECUTE stmt USING @i;
SET @code = getRandomAlphaNumeric();
SELECT COUNT(1) INTO @countRowsWithCode FROM demo.codes WHERE code = @code;
IF @countRowsWithCode = 0 AND @itemCode IS NULL THEN
UPDATE demo.codes SET code = @code WHERE id = @id;
END IF;
SET @i := @i + 1;
END WHILE;
END
$$
DELIMITER ;
CALL generateCodes();

Primero, creé una función que devuelve una cadena aleatoria de 6 caracteres que se usa a continuación para generar los códigos deseados:

DROP FUNCTION IF EXISTS getRandomAlphaNumeric;
DELIMITER $$
CREATE FUNCTION getRandomAlphaNumeric() RETURNS CHAR(6)
DETERMINISTIC
BEGIN
SELECT
CONCAT (
CHAR(FLOOR(RAND()*10)+48), CHAR(FLOOR(RAND()*26)+65), CHAR(FLOOR(RAND()*26)+97),
CHAR(FLOOR(RAND()*10)+48), CHAR(FLOOR(RAND()*26)+65), CHAR(FLOOR(RAND()*26)+97)
) INTO @code
;
RETURN @code;
END
$$

Luego creé un procedimiento que es responsable de actualizar la tabla con códigos únicos aleatorios. El procedimiento consiste en:

• Cuente todos los registros que se actualizarán con un código nuevo y aleatorio de 6 caracteres.

  SELECT COUNT(1) INTO @count FROM demo.codes;

• Luego, para cada fila (usando el bucle WHILE ):

  • Obtenga la identificación del próximo registro que se actualizará

    PREPARE stmt FROM "SELECT @id := id, @itemCode := code FROM demo.codes p LIMIT ?, 1;"; EXECUTE stmt USING @i;

  • Obtenga un nuevo código para el registro:

    SET @code = getRandomAlphaNumeric();

  • Por último, verifique si el nuevo código no existe en la tabla y si actualmente la columna del campo no tiene valor (es NULL ) , si no lo está, actualice el registro actual con el código aleatorio:

    SELECT COUNT(1) INTO @countRowsWithCode FROM demo.codes WHERE code = @code; IF @countRowsWithCode = 0 AND @itemCode IS NULL THEN UPDATE demo.codes SET code = @code WHERE id = @id; END IF;

  • Finalmente, CALL al PROCEDURE creado para llenar los campos de la columna de code que son NULL .

    CALL generateCodes();

Answer 2

Intenta esto para el código

SELECT LEFT(MD5(NOW()), 6) AS CODE;

LEFT(MD5(NOW()), 6) devolverá un código único con 6 caracteres.

Prueba de otra manera como esta

SELECT LEFT(UUID(), 6);

LEFT(UUID(), 6) Esto también devolverá un código único

Answer 3

ANTES DE ACTUALIZAR la solución de disparo:

Puede crear una cadena mayúscula alfanumérica aleatoria de 6 caracteres con:

lpad(conv(floor(rand()*pow(36,6)), 10, 36), 6, 0);

Para no crear una cadena ya existente, puede usar un activador BEFORE UPDATE .

DELIMITER //
CREATE TRIGGER `unique_codes_before_update`
BEFORE UPDATE ON `unique_codes` FOR EACH ROW
BEGIN
declare ready int default 0;
declare rnd_str text;
if new.CODE is null then
while not ready do
set rnd_str := lpad(conv(floor(rand()*pow(36,6)), 10, 36), 6, 0);
if not exists (select * from unique_codes where CODE = rnd_str) then
set new.CODE = rnd_str;
set ready := 1;
end if;
end while;
end if;
END//
DELIMITER ;

Cada vez que establezca su columna CODE en NULL en una instrucción UPDATE , el activador creará una nueva cadena aleatoria en un bucle hasta que no se haya encontrado ninguna coincidencia en la tabla.

Ahora puedes reemplazar todos los valores NULL con:

update unique_codes set CODE = NULL where code is NULL;

En la demostración de SQLFiddle aquí, uso una cadena aleatoria de un carácter para demostrar que no se duplica ningún valor.

También puede usar el mismo código en un disparador BEFORE INSERT . De esta manera, solo puede insertar nuevas filas con CODE=NULL y el activador lo establecerá en una nueva cadena aleatoria única. Y nunca más necesitarás actualizarlo.

Respuesta original (32 cadenas de caracteres):

select lpad(conv(floor(rand()*pow(36,8)), 10, 36), 8, 0) as rnd_str_8;
-- output example: 3AHX44TF

generará una cadena aleatoria mayúscula alfanumérica de 8 caracteres. Concatena a cuatro de ellos para obtener 32 caracteres:

select concat(
lpad(conv(floor(rand()*pow(36,8)), 10, 36), 8, 0),
lpad(conv(floor(rand()*pow(36,8)), 10, 36), 8, 0),
lpad(conv(floor(rand()*pow(36,8)), 10, 36), 8, 0),
lpad(conv(floor(rand()*pow(36,8)), 10, 36), 8, 0)
) as rnd_str_32;
-- output example: KGC8A8EGKE7E4MGD4M09U9YWXVF6VDDS

http://sqlfiddle.com/#!9/9eecb7d/76933

Entonces, ¿qué pasa con la unicidad? Bueno - intenta generar duplicados ;-)

Answer 4

CONV(CONV(( SELECT MAX(CODE) FROM tbl ), 36, 10) + 1, 10, 36)

obtendrá el siguiente ''número'' codificado en base-36 (dígitos y letras mayúsculas).

Por ejemplo:

SELECT CONV(CONV(( ''A1B2C'' ), 36, 10) + 1, 10, 36); --> ''A1B2D''

Answer 5

DELIMITER $$
USE `db` $$
DROP PROCEDURE IF EXISTS `GenerateUniqueValue`$$
CREATE PROCEDURE `GenerateUniqueValue`(IN tableName VARCHAR(255),IN columnName VARCHAR(255))
BEGIN
DECLARE uniqueValue VARCHAR(8) DEFAULT "";
WHILE LENGTH(uniqueValue) = 0 DO
SELECT CONCAT(SUBSTRING(''ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789'', RAND()*34+1, 1),
SUBSTRING(''ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789'', RAND()*34+1, 1),
SUBSTRING(''ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789'', RAND()*34+1, 1),
SUBSTRING(''ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789'', RAND()*34+1, 1),
SUBSTRING(''ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789'', RAND()*34+1, 1),
SUBSTRING(''ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789'', RAND()*34+1, 1),
SUBSTRING(''ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789'', RAND()*34+1, 1),
SUBSTRING(''ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789'', RAND()*34+1, 1)
) INTO @newUniqueValue;
SET @rcount = -1;
SET @query=CONCAT(''SELECT COUNT(*) INTO @rcount FROM '',tableName,'' WHERE '',columnName,'' like '''''',@newUniqueValue,'''''''');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
IF @rcount = 0 THEN
SET uniqueValue = @newUniqueValue ;
END IF ;
END WHILE ;
SELECT uniqueValue;
END$$
DELIMITER ;

Llame a este procedimiento almacenado como

Call GenerateUniqueValue(''tableName'',''columnName'')

Esto te dará una cadena única de 8 caracteres cada vez.