logo标签列表

如何为我的 MySql 表列生成一个唯一的随机字符串？

mysqlrandomsql-updateconstraintsunique
18
我将使用MySql 5.5.37。 我有一个带有以下列的表格。
+------------------+------------------+------+-----+---------+-------+
| Field            | Type             | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| ID               | varchar(32)      | NO   | PRI | NULL    |       |
| CODE             | varchar(6)       | NO   | UNI | NULL    |       |

代码列是唯一的，我的ID列是GUID。我有许多行需要更新，但要满足上表中的某些条件（例如 WHERE COLUMN1 = 0）。如何为我的CODE列生成随机且唯一的6位字符代码（最好是字母和数字），使它们不违反表中的唯一约束？请注意，表中不符合条件的列（例如 Where COLUMN1 <> 0）已经具有CODE列的唯一值。
编辑：这与此问题不同--使用MySQL生成随机且唯一的8个字符字符串，因为该链接处理的ID是数字。我的ID是32个字符的字符串。此外，他们的解决方案没有考虑到在运行我想运行的语句之前可能存在表中的值，这些值将为所讨论的列生成唯一值。
请看我的编辑。你列出的链接涉及到一个具有数字ID的表格，而我的表格没有这个。 - Dave
1
值必须是随机的吗？为什么？"唯一"不足以吗？ - Bohemian
3
如果数值不是随机的，人们可以破解算法并猜测它们。这段代码用于授予应用程序的某个部分访问权限。 - Dave
6个回答
25

BEFORE UPDATE触发器解决方案：

您可以使用以下方法创建一个6个字符的随机字母数字大写字符串：

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

为了避免创建已存在的字符串，您可以使用一个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 ;

每次您在 UPDATE 语句中将 CODE 列设置为 NULL 时，触发器会在循环中创建一个新的随机字符串，直到在表中找不到匹配项为止。
现在您可以用以下内容替换所有的 NULL 值：
update unique_codes set CODE = NULL where code is NULL;

在这个SQLFiddle演示中，我使用一个字符的随机字符串来演示没有值被重复的情况。
你也可以在一个BEFORE INSERT触发器中使用相同的代码。这样，你只需要插入新行时将CODE=NULL，触发器就会将其设置为新的唯一随机字符串。你将永远不需要再更新它了。 原始答案（32个字符的字符串）： 
select lpad(conv(floor(rand()*pow(36,8)), 10, 36), 8, 0) as rnd_str_8;

-- output example: 3AHX44TF

将生成一个包含8个大写字母和数字的随机字符串。将四个字符串拼接在一起以获得32个字符：

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

那么唯一性呢？嗯，尝试生成重复数据吧 ;-)

您正在生成32个字符的字符串，但我的唯一列只有6个字符宽。我不知道如何从您的答案中得出我需要插入到我的列中的内容。 - Dave
抱歉，我以为你需要一个32个字符的字符串。对于6个字符，我可能会创建一个触发器，在循环中检查重复项。或者预先计算一个包含唯一字符串的表格。 - Paul Spiegel
我所询问的是，我有一组现有行需要唯一值（它们目前都设置为NULL），我想找出如何填充这些值。据我所知，触发器只适用于添加操作，而不是更新操作。 - Dave
你正在将CODE列设置为NULL，但该列有NOT NULL约束。 - Dave
@Dave - 你怎么能在同一列中同时拥有NOT NULL约束和NULL值呢？不过，尝试执行update unique_codes set ID = ID where code is NULL;，那也应该可以解决问题。 - Paul Spiegel
哈哈，没错。我移除了我的 NOT NULL 约束，现在一切都正常了。 - Dave
1

这个有点棘手，但我认为我已经找到了一个不错的解决方案：

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();

首先，我创建了一个函数，它返回一个由6个字符组成的随机字符串，然后用于生成所需的代码：
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
$$

然后我创建了一个过程，负责使用随机唯一码更新表格。 该过程包括：

  • 计算所有将使用新的随机6个字符代码更新的记录数。

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

  • 然后，对于每一行（使用WHILE循环）：

    • 获取下一个要更新的记录的ID

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

    • 为该记录获取一个新代码：

      SET @code = getRandomAlphaNumeric();

    • 最后，验证新代码在表中不存在，并且当前字段列没有值（是NULL），如果不是，则使用随机代码更新当前记录：

      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;

    • 最后，CALL创建的PROCEDURE以填充code列中为NULL的字段。

      CALL generateCodes();

嘿，我正在尝试在运行之前弄清楚发生了什么...这会更新代码表中的所有行吗？还是只会更新那些没有代码（值为NULL）的行？ - Dave
嘿，戴夫。我没有注意到你的请求中有这个要求，但我编辑了我的回复，现在当前脚本只会为当前没有值的字段生成随机代码，即它们是“NULL”。 - Cristian Gonçalves
谢谢你的更新。你的函数需要多长时间？我有大约30,000行需要更新，我一直在运行你的代码，但已经半个小时了，还在运行中。我觉得我可能陷入了一个无限循环中。 - Dave
哇，30k行...好吧，速度会变得相当慢...此时脚本已经成功运行了吗？ - Cristian Gonçalves
我不得不在几个小时后停止它，因为这个进程正在耗尽我的电池。我会尝试找出另一种解决方法来排除故障，并确定它是否正在取得进展或因某种原因挂起。 - Dave
1
CONV(CONV(( SELECT MAX(CODE) FROM tbl ), 36, 10) + 1, 10, 36)

这将会获取下一个使用基数36（数字和大写字母）编码的“数字”。

例如：

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

获取包含大写字母、小写字母和数字的随机10个字符字符串的简单高效解决方案，无需自定义函数（适用于MySQL >= 5.6）：

select substring(base64_encode(md5(rand())) from 1+rand()*4 for 10);
0
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 ;

像这样调用存储过程

Call GenerateUniqueValue('tableName','columnName')

这将每次为您提供一个独特的8个字符的字符串。

这是非常好的方法，但是在23000条记录之后，它开始重复相同的值。 - jesantana
0

尝试使用这段代码

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

LEFT(MD5(NOW()), 6) 这将返回一个具有 6 个字符的唯一代码。

尝试另一种方式，如下所示：

SELECT LEFT(UUID(), 6);

LEFT(UUID(), 6) 这将返回一个唯一的代码

如果我在一个大型更新语句中一次性更新多行数据，那么 NOW() 函数对于这些行来说不就是相同的吗？ - Dave
这不会生成唯一的ID。SELECT LEFT(MD5(NOW()), 6) AS CODE union all SELECT LEFT(MD5(NOW()), 6) AS CODE 输出 -> 9fd589 9fd5891 - TheTechGuy
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接