如何为MySql表列生成唯一的、随机的字符串?

I’m using MySql 5.5.37. I have a table with the following columns

我使用MySql 5.5.37。我有一个包含以下列的表

+------------------+------------------+------+-----+---------+-------+
| Field            | Type             | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| ID               | varchar(32)      | NO   | PRI | NULL    |       |
| CODE             | varchar(6)       | NO   | UNI | NULL    |       |

The code column is unique and my ID column is a GUID. I have a number of rows that I would like to update, subject to some criteria in the above table (e.g. WHERE COLUMN1 = 0). How do I generate random, unique 6-character codes (ideally letters and numbers) for my CODE column such that they don’t violate the unique constraint in my table? Note that the columns in the table that do not meet the criteria (e.g. Where COLUMN1 <> 0) already have unique values for the CODE column.

代码列是唯一的，我的ID列是GUID。我有一个我想要更新的行数,根据某些标准在上面的表(例如COLUMN1 = 0)。我怎么生成随机的,独特的六个码(理想情况下字母和数字)我的代码列,这样他们不违反唯一约束在我的桌子上吗?注意，表中的列不符合标准(例如，COLUMN1 <> 0)已经为代码列设置了惟一的值。

Edit: This is different than this question -- Generating a random & unique 8 character string using MySQL because that link deals with IDs taht are numeric. My IDs are 32-character strings. Also their solution does not take into account the fact that there may values in the table prior to running the statements I want to run that will generate a unique values for the column in question.

编辑:这与这个问题不同——使用MySQL生成一个随机和唯一的8个字符串，因为处理id taht的链接是数字的。我的id是32个字符的字符串。此外，他们的解决方案没有考虑到，在运行我想要运行的语句之前，表中可能存在值，这些值将为相关列生成惟一值。

5 个解决方案

#1

BEFORE UPDATE trigger solution:

在更新之前触发的解决方案:

You can create a 6 character random alphanumeric uppercase string with:

您可以使用以下命令创建一个6字符的随机字母数字大写字符串:

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

In order to not create an already existing string you can use a BEFORE UPDATE trigger.

为了不创建已经存在的字符串，可以使用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 ;

Every time you set your CODE column to NULL in an UPDATE statement, the trigger will create a new random string in a loop until no match has been found in the table.

每次在UPDATE语句中将代码列设置为NULL时，触发器将在循环中创建一个新的随机字符串，直到在表中没有找到匹配为止。

Now you can replace all NULL values with:

现在可以将所有空值替换为:

update unique_codes set CODE = NULL where code is NULL;

In the SQLFiddle demo here i use a one character random string to demonstrate that no value is duplicated.

在这里的sql小提琴演示中，我使用一个单字符随机字符串来演示没有重复的值。

You can also use the same code in a BEFORE INSERT trigger. This way you can just insert new rows with CODE=NULL and the trigger will set it to a new unique random string. And you will never need to update it again.

您还可以在插入触发器前使用相同的代码。这样，只需插入代码为NULL的新行，触发器就会将其设置为一个新的惟一随机字符串。你再也不需要更新它了。

Original answer (32 character strings):

原答(32字串):

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

-- output example: 3AHX44TF

will generate an 8-character alphanumeric uppercase random string. Concatenate four of them to get 32 characters:

将生成一个8字符的字母数字大写随机字符串。连接4个字符，得到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

http://sqlfiddle.com/ ! 9/9eecb7d / 76933

So what about uniqness? Well - try to generate duplicates ;-)

那么uniqness呢?尝试产生重复;-)

#2

CONV(CONV(( SELECT MAX(CODE) FROM tbl ), 36, 10) + 1, 10, 36)

will get you the next 'number' encoded in base-36 (digits & capital letters).

将得到下一个用base-36(数字和大写字母)编码的“数字”。

For example:

例如:

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

#3

This one is tricky but I think I've reached a nice solution:

这是一个棘手的问题，但我认为我已经找到了一个很好的解决方案:

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

First, I created a function that returns a random string of 6 chars that it's used following to generates the desired codes:

首先，我创建了一个函数，它返回一个由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
$$

Then I created a procedure that is responsible to update the table with random unique codes. The procedure consists in:

然后我创建了一个过程，负责用随机惟一代码更新表。这个过程包括:

• Count all the records that will be updated with a fresh and random code of 6 characters.

  计算所有的记录，用一个新的随机的6个字符的代码更新。

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

  选择COUNT(1)到demo.codes的@count;

• Then, foreach row (using WHILE loop):

  然后，foreach行(使用WHILE循环):

  • Get the id of the next record to be uptdated

    获取下一个要更新的记录的id

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

    从“选择@id:= id， @itemCode:= demo代码”准备stmt。编码p极限?，1;";执行使用@i支撑;

  • Get a new code for the record:

    获取新的记录代码:

    SET @code = getRandomAlphaNumeric();

    设置@code = getRandomAlphaNumeric();

  • For last, verify if the new code do not already exists on table and if currently the field column has no value (is NULL), if it's not, update the current record with the random code:

    最后，验证新代码是否已经存在于表中，如果当前字段列没有值(为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;

    从demo中选择COUNT(1)到@countRowsWithCode。代码= @code;如果@countRowsWithCode = 0， @itemCode为空，则更新demo。码集码= @码，其中id = @id;如果;

  • Finally, CALL the created PROCEDURE in order to populate the fields from code column that are NULL.

    最后，调用创建的过程，以便从NULL的代码列填充字段。

    CALL generateCodes();

    调用generateCodes();

#4

Try this for code

试试这个代码

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

LEFT(MD5(NOW()), 6) this will return unique code with 6 characters.

左边(MD5(NOW())， 6)返回6个字符的唯一代码。

Try another way like this

试试别的方法

SELECT LEFT(UUID(), 6);

LEFT(UUID(), 6) This will also return unique code

左边(UUID()， 6)也将返回唯一的代码

#5

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 this stored procedure like

调用这个存储过程

Call GenerateUniqueValue('tableName','columnName')

This will give you a unique 8 character string everytime.

这将给你一个唯一的8字串每次。