Génération d'une chaîne de 8 caractères unique et aléatoire à l'aide de MySQL

Question

Je travaille sur un jeu qui implique des véhicules à un moment donné. J'ai une table MySQL nommée "véhicules" contenant les données sur les véhicules, y compris la colonne "plaque" qui stocke les plaques d'immatriculation des véhicules.

Maintenant, voici la partie avec laquelle j'ai des problèmes. Je dois trouver une plaque d'immatriculation inutilisée avant de créer un nouveau véhicule - il doit s'agir d'une chaîne alphanumérique aléatoire de 8 caractères. Pour y parvenir, j'ai utilisé une boucle while en Lua, qui est le langage dans lequel je programme, pour générer des chaînes et interroger la base de données 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 que maintenant. J'ai donc 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 n'est pas déjà dans la table. J'ai de nouveau pensé à l'approche de la boucle generate&check, mais je ne limite pas cette question à celle-ci, au cas où il en existerait une plus efficace. J'ai réussi à générer des chaînes de caractères en définissant une chaîne contenant tous les caractères autorisés et en la soustrayant de manière aléatoire, mais rien de plus.

Toute aide est appréciée.

Answer 1

Je ne m'embêterais pas avec la probabilité d'une collision. Il suffit de générer une chaîne aléatoire et de vérifier si elle existe. Si c'est le cas, essayez à nouveau et vous ne devriez pas avoir besoin de le faire plus de deux fois, à moins que vous n'ayez déjà un grand nombre de plaques assignées.

Une autre solution pour générer une chaîne pseudo-aléatoire de 8 caractères en pur (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

---

Puisque ce post a reçu un niveau d'attention inattendu, permettez-moi de souligner Commentaire de l'ADTC le morceau de code ci-dessus est assez stupide et produit des chiffres séquentiels.

Pour un hasard un peu moins stupide, essayez plutôt quelque chose comme ceci :

SELECT LEFT(MD5(RAND()), 8)

Et pour un véritable caractère aléatoire (sécurisé sur le plan cryptographique), utilisez RANDOM_BYTES() plutôt que RAND() (mais alors j'envisagerais de déplacer cette logique vers la couche application).

Answer 2

Ce problème se compose de deux sous-problèmes très différents :

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

Si le caractère aléatoire est assez facile à obtenir, l'unicité sans boucle de relance ne l'est pas. Cela nous amène à nous concentrer d'abord sur l'unicité. L'unicité non aléatoire peut être obtenue de façon triviale avec AUTO_INCREMENT . L'utilisation d'une transformation pseudo-aléatoire préservant l'unicité serait donc parfaite :

• Le hachage a été suggéré par @paul
• AES-encrypt s'adapte aussi
• Mais il y en a une belle : RAND(N) lui-même !

Une séquence de nombres aléatoires créée par la même graine est garantie comme étant

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

Nous utilisons donc l'approche de @AndreyVolk ou de @GordonLinoff, mais avec une semé RAND :

Par exemple, Assumin id est un AUTO_INCREMENT colonne :

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

Answer 3

Que diriez-vous de calculer le hachage MD5 (ou autre) d'entiers séquentiels, puis de prendre les 8 premiers caractères.

i.e

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

etc.

Attention : je n'ai aucune idée du nombre de personnes que 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 vu à nouveau avec du temps sur mes mains, donc, de l'observation ...

Chance de tous les chiffres = 2,35%.

Chance d'avoir toutes les lettres = 0,05%.

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

Answer 4

Créer une chaîne aléatoire

Voici une fonction MySQL permettant de créer une chaîne aléatoire d'une longueur donnée.

DELIMITER $$

CREATE DEFINER=`root`@`%` FUNCTION `RandString`(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 retourner une chaîne de 8 caractères.

Vous pouvez personnaliser le @allowedChars .

L'unicité n'est pas garantie - comme vous le verrez dans les commentaires des 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érifier si la chaîne aléatoire est déjà utilisée

Si nous voulons garder le code de vérification des collisions en dehors 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 ;

Answer 5

Voici une façon de procéder, en utilisant les alphanumériques comme caractères valides :

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

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