在MySQL中生成唯一字符串的最简单方法是什么?[重复]

这项工作形成我,生成6位数字,并在MySQL中更新:

生成:

SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 6)

更新:

UPDATE table_name 
SET column_name = SUBSTRING(MD5(RAND()) FROM 1 FOR 6) 
WHERE id = x12

SQL触发器非常复杂,而且占用大量资源。针对基于MySQL“触发器”的解决方案,这里有一个更简单的解决方案。

1. 在MySQL表列上创建一个唯一的索引,它将保存车辆牌照字符串。这将确保只输入唯一的值。
2. 只需在Lua(或任何其他编程语言,如ASP、PHP、Java等)中生成标准字母数字随机字符串即可
3. 使用生成的字符串执行INSERT语句,并使用错误捕获代码来解析失败(在唯一索引冲突的情况下)
4. 如果插入失败,则生成一个新的随机字符串并重新插入。8个字符的长度本身很难重复,一旦在表中找到,生成另一个字符几乎不可能是另一个重复。

这将在DB服务器上更轻、更高效。

下面是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();

我在寻找类似的东西,我决定制作自己的版本,如果需要,也可以指定不同的种子(字符列表)作为参数:

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

可以用作:

SELECT random_string(10, '')

它将使用内置的大小写字符+数字种子。 NULL也将是值,而不是“”。

但可以在调用时指定自定义种子:

SELECT random_string(10, '1234')

生成唯一数字的简单方法

set @i = 0;
update vehicles set plate = CONCAT(@i:=@i+1, ROUND(RAND() * 1000)) 
order by 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 ;

请使用此存储过程,并随时使用它

Call GenerateUniqueValue('tableName','columnName')

UPPER(HEX(UUID_SHORT()))

为您提供一个唯一的16个字符的字母数字字符串。它有一些不太可能的警告,明白吗 https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_uuid-short

“下一个”值通常是可预测的:

mysql> SELECT UPPER(HEX(UUID_SHORT()));
+--------------------------+
| UPPER(HEX(UUID_SHORT())) |
+--------------------------+
| 161AA3FA5000006          |
+--------------------------+

mysql> SELECT UPPER(HEX(UUID_SHORT()));
+--------------------------+
| UPPER(HEX(UUID_SHORT())) |
+--------------------------+
| 161AA3FA5000007          |
+--------------------------+

转换为BASE64可以将字符串减少到11个字符:

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_to-base64

mysql> SELECT TO_BASE64(UNHEX(HEX(UUID_SHORT())));
+-------------------------------------+
| TO_BASE64(UNHEX(HEX(UUID_SHORT()))) |
+-------------------------------------+
| AWGqP6UAABA=                        |
+-------------------------------------+

这是12个字符,去掉“=”会得到11个字符。

这些可能会使它不适合你的使用:“下一个”盘子在某种程度上是可以预测的。可能有一些标点符号( + , / )在绳子上。小写字母可能会包括在内。

生成8个字符的密钥

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

How do I generate a unique, random string for one of my MySql table columns?

此函数根据输入长度和允许的字符生成一个随机字符串,如下所示:

SELECT str_rand(8, '23456789abcdefghijkmnpqrstuvwxyz');

功能代码:

DROP FUNCTION IF EXISTS str_rand;

DELIMITER //

CREATE FUNCTION str_rand(
    u_count INT UNSIGNED,
    v_chars TEXT
)
RETURNS TEXT
NOT DETERMINISTIC
NO SQL
SQL SECURITY INVOKER
COMMENT ''
BEGIN
    DECLARE v_retval TEXT DEFAULT '';
    DECLARE u_pos    INT UNSIGNED;
    DECLARE u        INT UNSIGNED;

    SET u = LENGTH(v_chars);
    WHILE u_count > 0
    DO
      SET u_pos = 1 + FLOOR(RAND() * u);
      SET v_retval = CONCAT(v_retval, MID(v_chars, u_pos, 1));
      SET u_count = u_count - 1;
    END WHILE;

    RETURN v_retval;
END;
//
DELIMITER ;

此代码基于 shuffle string function 由“罗斯·史密斯二世”发送

考虑到所需的字符总数,生成两个完全相似的车牌的可能性很小。因此,您可能不需要在LUA中生成数字。

你有36^8个不同的唯一数字板(282110907456,太多了),即使你已经有了一百万个数字板,你也有很小的机会生成一个你已经有的数字板,大约0.000035%

当然,这完全取决于你最终会创造多少个数字板。

如果你对“随机”但完全可预测的车牌没问题,你可以使用 linear-feedback shift register 选择下一个车牌号——保证在重复之前先检查每个车牌号。然而,如果没有一些复杂的数学运算,你将无法遍历每个8个字符的字母数字字符串(在36^8(78%)个可能的图版中,你将得到2^41个)。为了让它更好地填满你的空间,你可以从盘子里剔除一个字母(可能是O),这样你就可以得到97%。

简单高效的解决方案,可获得随机的10个字符的字符串,包括大小写字母和数字:

select substring(base64_encode(md5(rand())) from 1+rand()*4 for 10);

创建一个随机的 10位字母数字 ,不包括相貌相似的字符01oOlI:

LPAD(LEFT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TO_BASE64(UNHEX(MD5(RAND()))), "/", ""), "+", ""), "=", ""), "O", ""), "l", ""), "I", ""), "1", ""), "0", ""), "o", ""), 10), 10, 0)

这正是我创建一个 凭证代码 。在凭证代码表单中键入时,会删除令人困惑的字符,以减少错误。

希望这对某人有所帮助,基于 Jan Uhlig's brilliant answer .

请参阅Jan的答案,了解该代码的工作原理。

如果您没有id或seed,例如insert中的值列表:

REPLACE(RAND(), '.', '')

字母表中的8个字母-所有大写字母:

UPDATE `tablename` SET `tablename`.`randomstring`= 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))));

我使用另一列中的数据生成“哈希”或唯一字符串

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

要生成随机字符串,可以使用:

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

你收到这样的短信:

353E50CC

对于由8个随机数和大小写字母组成的字符串,我的解决方案如下:

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

由内而外解释:

1. RAND 生成一个介于0和1之间的随机数
2. MD5 计算MD5(1)、a-f和0-9中的32个字符之和
3. UNHEX 将(2)转换为16字节,值从00到FF
4. TO_BASE64 将(3)编码为base64,从a-z和a-z中提取22个字符,0-9加“/”和“+”,后跟两个“=”
5. 三个 REPLACE s从(4)中删除“/”、“+”和“=”字符
6. LEFT 从(5)中提取前8个字符,如果随机字符串中需要更多或更少的字符,请将8更改为其他字符
7. LPAD 如果长度小于8个字符,则在(6)开头插入零;同样,如果需要的话,把8换成别的

您可以使用以下命令生成随机字母数字字符串:

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

你可以把它用在 BEFORE INSERT 触发并检查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 ;

现在只需像这样插入数据

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

触发器将为 plate 柱

( sqlfiddle demo )

如果列允许空值,则这种方式有效。如果希望它不为NULL,则需要定义一个默认值

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

如果不需要大写字母数字,也可以在触发器中使用任何其他随机字符串生成算法。但触发器将考虑独特性。

你可以使用MySQL的 rand() 和 char() 功能:

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;

下面是生成随机字符串的另一种方法:

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