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

Générer une clé de 8 caractères

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

Comment générer une chaîne unique et aléatoire pour l'une des colonnes de ma table MySql ?

Answer 2

Les déclencheurs SQL sont complexes et nécessitent beaucoup de ressources. Contre une solution basée sur les "Trigger" MySQL, voici une solution plus simple.

1. Créez un INDEX UNIQUE sur la colonne de la table MySQL qui contiendra la chaîne de la plaque d'immatriculation du véhicule. Cela permettra de s'assurer que seules des valeurs uniques seront saisies.
2. Il suffit de générer la chaîne alphanumérique aléatoire standard en Lua (ou dans tout autre langage de programmation comme ASP, PHP, Java, etc.)
3. Exécutez l'instruction INSERT avec la chaîne de caractères générée, et disposez d'un code de détection d'erreur pour analyser l'échec (en cas de violation de l'UNIQUE INDEX).
4. Si l'INSERT échoue, générez une nouvelle chaîne aléatoire et réinsérez-la. La longueur de 8 caractères en elle-même est assez difficile à répéter, et une fois trouvée dans la table, il sera pratiquement impossible d'en générer une autre.

Cela sera plus léger et plus efficace sur le serveur DB.

Voici un exemple de (pseudo-) code en PHP :

function refercode()
{
    $string = '';
    $characters = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    $max = strlen($characters) - 1;
    for ($i = 0; $i < 8; $i++) {
        $string .= $characters[mt_rand(0, $max)];
    }
    $refer = "select * from vehicles where refer_code = '".$string."' ";
    $coderefertest = mysqli_query($con,$refer);

    if(mysqli_num_rows($coderefertest)>0)
    {
        return refercode();
    }
    else
    {
        return $string;
    }
}
$refer_by = refercode();

Answer 3

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 à chaque fois comme suit

Call GenerateUniqueValue('tableName','columnName')

Answer 4

Un moyen simple de générer un numéro unique

set @i = 0;
update vehicles set plate = CONCAT(@i:=@i+1, ROUND(RAND() * 1000)) 
order by rand();

Answer 5

Je cherchais quelque chose de similaire et j'ai décidé de faire ma propre version où vous pouvez également spécifier une graine différente si vous le souhaitez (liste de caractères) comme paramètre :

CREATE FUNCTION `random_string`(length SMALLINT(3), seed VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
    NO SQL
BEGIN
    SET @output = '';

    IF seed IS NULL OR seed = '' THEN SET seed = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'; END IF;

    SET @rnd_multiplier = LENGTH(seed);

    WHILE LENGTH(@output) < length DO
        # Select random character and add to output
        SET @output = CONCAT(@output, SUBSTRING(seed, RAND() * (@rnd_multiplier + 1), 1));
    END WHILE;

    RETURN @output;
END

Peut être utilisé comme :

SELECT random_string(10, '')

Ce qui utiliserait la graine intégrée de caractères majuscules et minuscules + chiffres. NULL serait également une valeur à la place de ''.

Mais on peut spécifier une graine personnalisée lors de l'appel :

SELECT random_string(10, '1234')