Génération d’une chaîne de 8 caractères aléatoire et unique en utilisant MySQL

Je travaille sur un jeu qui implique des véhicules à un moment donné. J’ai une table MySQL nommée “vehicles” contenant les données sur les véhicules, y compris la colonne “plate” qui stocke les plaques d’immasortingculation pour les véhicules.

Maintenant, voici la partie avec laquelle j’ai des problèmes. Je dois trouver une plaque d’immasortingculation inutilisée avant de créer un nouveau véhicule. Il doit s’agir d’une chaîne alphanumérique de 8 caractères. Comment j’ai réalisé ceci a été d’utiliser une boucle while dans Lua, qui est le langage dans lequel je programme, pour générer des chaînes et interroger la firebase database pour voir si elle est utilisée. Cependant, à mesure que le nombre de véhicules augmente, je m’attends à ce que cela devienne encore plus inefficace. Par conséquent, j’ai décidé d’essayer de résoudre ce problème en utilisant une requête MySQL.

La requête dont j’ai besoin doit simplement générer une chaîne alphanumérique de 8 caractères qui ne figure pas déjà dans la table. J’ai repensé à l’approche de la boucle de génération et de vérification, mais je ne limiterai pas cette question à celle-ci au cas où celle-ci serait plus efficace. J’ai été en mesure de générer des chaînes en définissant une chaîne contenant tous les caractères autorisés et en la sous-chaîne de manière aléatoire, et rien de plus.

Toute aide est appréciée.

Ce problème consiste en deux sous-problèmes très différents:

  • la chaîne doit être apparemment aléatoire
  • la chaîne doit être unique

Bien que le caractère aléatoire soit assez facile à atteindre, l’unicité sans boucle de nouvelle tentative ne l’est pas. Cela nous amène à nous concentrer sur le caractère unique en premier. L’unicité non aléatoire peut être sortingvialement obtenue avec AUTO_INCREMENT . Donc, en utilisant une transformation pseudo-aléatoire préservant l’unicité serait bien:

  • Hash a été suggéré par @paul
  • AES-encrypt s’adapte également
  • Mais il y en a une belle: RAND(N) elle-même!

Une séquence de nombres aléatoires créés par la même graine est garantie d’être

  • reproductible
  • différent pour les 8 premières itérations
  • si la graine est une INT32

Nous utilisons donc l’approche de @ AndreyVolk ou @ GordonLinoff, mais avec un RAND :

Par exemple, l’ id Assumin est une colonne AUTO_INCREMENT:

 INSERT INTO vehicles VALUES (blah); -- leaving out the number plate SELECT @lid:=LAST_INSERT_ID(); UPDATE vehicles SET numberplate=concat( subssortingng('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@lid)*4294967296))*36+1, 1), subssortingng('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1), subssortingng('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1), subssortingng('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1), subssortingng('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1), subssortingng('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1), subssortingng('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1), subssortingng('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed)*36+1, 1) ) WHERE id=@lid; 

Comme je l’ai dit dans mon commentaire, je ne me soucierais pas de la probabilité d’une collision. Générez simplement une chaîne aléatoire et vérifiez si elle existe. Si c’est le cas, réessayez et vous ne devriez pas le faire plus souvent que si vous avez déjà un grand nombre d’assiettes.

Une autre solution pour générer une chaîne pseudo-aléatoire de 8 caractères en pure (My) SQL:

 SELECT LEFT(UUID(), 8); 

Vous pouvez essayer ce qui suit (pseudo-code):

 DO SELECT LEFT(UUID(), 8) INTO @plate; INSERT INTO plates (@plate); WHILE there_is_a_unique_constraint_violation -- @plate is your newly assigned plate number 

Qu’en est-il du calcul du hachage MD5 (ou autre) d’entiers séquentiels, en prenant alors les 8 premiers caractères.

c’est à dire

 MD5(1) = c4ca4238a0b923820dcc509a6f75849b => c4ca4238 MD5(2) = c81e728d9d4c2f636f067f89cc14862c => c81e728d MD5(3) = eccbc87e4b5ce2fe28308fd9f2a7baf3 => eccbc87e 

etc.

mise en garde: Je ne sais pas combien vous pourriez allouer avant une collision (mais ce serait une valeur connue et constante).

edit: C’est maintenant une vieille réponse, mais je l’ai revu avec du temps sur mes mains, donc, de l’observation …

Chances de tous les nombres = 2,35%

Chances de toutes les lettres = 0,05%

Première collision lorsque MD5 (82945) = “7b763dcb …” (même résultat que MD5 (25302))

Voici un moyen, en utilisant des chiffres alpha comme caractères valides:

 select concat(subssortingng('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1), subssortingng('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1), subssortingng('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1), subssortingng('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1), subssortingng('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1), subssortingng('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1), subssortingng('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1), subssortingng('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1) ) as LicensePlaceNumber; 

Notez qu’il n’y a aucune garantie d’unicité. Vous devrez vérifier cela séparément.

Créer une chaîne aléatoire

Voici une fonction MySQL pour créer une chaîne aléatoire d’une longueur donnée.

 DELIMITER $$ CREATE DEFINER=`root`@`%` FUNCTION `RandSsortingng`(length SMALLINT(3)) RETURNS varchar(100) CHARSET utf8 begin SET @returnStr = ''; SET @allowedChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; SET @i = 0; WHILE (@i < length) DO SET @returnStr = CONCAT(@returnStr, substring(@allowedChars, FLOOR(RAND() * LENGTH(@allowedChars) + 1), 1)); SET @i = @i + 1; END WHILE; RETURN @returnStr; END 

Utilisation SELECT RANDSTRING(8) pour renvoyer une chaîne de 8 caractères.

Vous pouvez personnaliser les @allowedChars .

L'unicité n'est pas garantie - comme vous le verrez dans les commentaires sur d'autres solutions, ce n'est tout simplement pas possible. Au lieu de cela, vous devrez générer une chaîne, vérifier si elle est déjà utilisée et réessayer si c'est le cas.


Vérifiez si la chaîne aléatoire est déjà utilisée

Si nous voulons garder le code de vérification de collision de l'application, nous pouvons créer un déclencheur:

 DELIMITER $$ CREATE TRIGGER Vehicle_beforeInsert BEFORE INSERT ON `Vehicle` FOR EACH ROW BEGIN SET @vehicleId = 1; WHILE (@vehicleId IS NOT NULL) DO SET NEW.plate = RANDSTRING(8); SET @vehicleId = (SELECT id FROM `Vehicle` WHERE `plate` = NEW.plate); END WHILE; END;$$ DELIMITER ; 

Vous pouvez utiliser la fonction rand () et char () de MySQL:

 select concat( char(round(rand()*25)+97), char(round(rand()*25)+97), char(round(rand()*25)+97), char(round(rand()*25)+97), char(round(rand()*25)+97), char(round(rand()*25)+97), char(round(rand()*25)+97), char(round(rand()*25)+97) ) as name; 

Vous pouvez générer une chaîne alphanumérique aléatoire avec:

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

Vous pouvez l’utiliser dans un déclencheur BEFORE INSERT et rechercher une copie dans une boucle while:

 CREATE TABLE `vehicles` ( `plate` CHAR(8) NULL DEFAULT NULL, `data` VARCHAR(50) NOT NULL, UNIQUE INDEX `plate` (`plate`) ); DELIMITER // CREATE TRIGGER `vehicles_before_insert` BEFORE INSERT ON `vehicles` FOR EACH ROW BEGIN declare str_len int default 8; declare ready int default 0; declare rnd_str text; while not ready do set rnd_str := lpad(conv(floor(rand()*pow(36,str_len)), 10, 36), str_len, 0); if not exists (select * from vehicles where plate = rnd_str) then set new.plate = rnd_str; set ready := 1; end if; end while; END// DELIMITER ; 

Maintenant, insérez simplement vos données comme

 insert into vehicles(col1, col2) values ('value1', 'value2'); 

Et le déclencheur générera une valeur pour la colonne de la plate .

( démo de sqlfiddle )

Cela fonctionne de cette façon si la colonne autorise les valeurs NULL. Si vous voulez qu’il ne soit pas NULL, vous devez définir une valeur par défaut

 `plate` CHAR(8) NOT NULL DEFAULT 'default', 

Vous pouvez également utiliser tout autre algorithme générant des chaînes aléatoires dans le déclencheur si les caractères alphanumériques majuscules ne sont pas ce que vous voulez. Mais la gâchette prendra soin de l’unicité.

Voici une autre méthode pour générer une chaîne aléatoire:

SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 8) AS myrandomssortingng

J’utilise des données provenant d’une autre colonne pour générer un “hash” ou une chaîne unique

 UPDATE table_name SET column_name = Right( MD5(another_column_with_data), 8 ) 

Pour générer une chaîne aléatoire, vous pouvez utiliser:

SUBSTRING(MD5(RAND()) FROM 1 FOR 8)

Vous recevez des smth comme ça:

353E50CC

Si vous êtes d’accord avec des plaques d’immasortingculation “aléatoires” mais totalement prévisibles, vous pouvez utiliser un registre à décalage à retour linéaire pour choisir le numéro de plaque suivant. Cependant, sans quelques calculs complexes, vous ne pourrez pas parcourir chaque chaîne alphanumérique de 8 caractères (vous obtiendrez 2 ^ 41 des 36 ^ 8 (78%) des plaques possibles). Pour que cela remplisse mieux votre espace, vous pouvez exclure une lettre des plaques (peut-être O), vous donnant 97%.

Compte tenu du nombre total de caractères requirejs, vous auriez très peu de chance de générer deux plaques d’immasortingculation identiques. Ainsi, vous pourriez probablement vous en sortir en générant les nombres dans LUA.

Vous avez 36 ^ 8 différentes plaques de numérotation uniques (2 821 109 907 456, c’est beaucoup), même si vous aviez déjà un million de plaques numérotées, vous auriez très peu de chances d’en générer une, environ 0,000035%

Bien sûr, tout dépend du nombre de plaques que vous allez créer.

8 lettres de l’alphabet – Toutes les majuscules:

 UPDATE `tablename` SET `tablename`.`randomssortingng`= concat(CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25)))CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25)))); 

Pour une chaîne composée de 8 nombres aléatoires et de lettres majuscules et minuscules, voici ma solution:

 LPAD(LEFT(REPLACE(REPLACE(REPLACE(TO_BASE64(UNHEX(MD5(RAND()))), "/", ""), "+", ""), "=", ""), 8), 8, 0) 

Expliqué de l’intérieur:

  1. RAND génère un nombre aléatoire compris entre 0 et 1
  2. MD5 calcule la sum MD5 de (1), 32 caractères de af et 0-9
  3. UNHEX traduit (2) en 16 octets avec des valeurs de 00 à FF
  4. TO_BASE64 code (3) en base64, 22 caractères az et AZ et 0-9 plus “/” et “+”, suivis de deux “=”
  5. les trois REPLACE suppriment les caractères “/”, “+” et “=” de (4)
  6. LEFT prend les 8 premiers caractères de (5), changez 8 en quelque chose si vous avez besoin de plus ou moins de caractères dans votre chaîne aléatoire
  7. LPAD insère des zéros au début de (6) s’il fait moins de 8 caractères; encore une fois, modifiez 8 si nécessaire

Si vous n’avez pas d’id ou de graine, comme c’est son pour une liste de valeurs dans insert:

 REPLACE(RAND(), '.', '') 
 DELIMITER $$ USE `temp` $$ 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 ; 

Utilisez cette procédure stockée et utilisez-la à tout moment comme

 Call GenerateUniqueValue('tableName','columnName')