如何为 MySql 表列之一生成唯一的随机字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39257391/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I generate a unique, random string for one of my MySql table columns?
提问by Dave
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。根据上表中的某些条件（例如，WHERE COLUMN1 = 0），我有许多要更新的行。如何为我的 CODE 列生成随机的、唯一的 6 字符代码（最好是字母和数字），以便它们不会违反我表中的唯一约束？请注意，表中不符合条件的列（例如，Where COLUMN1 <> 0）已经具有 CODE 列的唯一值。
Edit:This is different than this question -- Generating a random & unique 8 character string using MySQLbecause 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 是数字。我的 ID 是 32 个字符的字符串。此外，他们的解决方案没有考虑到在运行我想运行的语句之前表中可能存在值的事实，这些值将为相关列生成唯一值。
回答by Paul Spiegel
BEFORE UPDATE trigger solution:
BEFORE UPDATE 触发器解决方案：
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 UPDATEtrigger.
为了不创建已经存在的字符串，您可以使用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 CODEcolumn to NULLin an UPDATEstatement, the trigger will create a new random string in a loop until no match has been found in the table.
每次在语句中将CODE列设置为NULL时UPDATE，触发器将在循环中创建一个新的随机字符串，直到在表中找不到匹配项。
Now you can replace all NULL values with:
现在您可以将所有 NULL 值替换为：
update unique_codes set CODE = NULL where code is NULL;
In the SQLFiddle demo herei use a one character random string to demonstrate that no value is duplicated.
在此处的SQLFiddle 演示中，我使用一个字符随机字符串来证明没有重复值。
You can also use the same code in a BEFORE INSERTtrigger. This way you can just insert new rows with CODE=NULLand the trigger will set it to a new unique random string. And you will never need to update it again.
您还可以在BEFORE INSERT触发器中使用相同的代码。这样您就可以插入新行，CODE=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 个字符的字母数字大写随机字符串。将其中四个连接起来得到 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 ;-)
那么唯一性呢？好吧 - 尝试生成重复项 ;-)
回答by Rick James
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'
回答by krunal nerikar
Try this for code
试试这个代码
SELECT LEFT(MD5(NOW()), 6) AS CODE;
LEFT(MD5(NOW()), 6)this will return unique code with 6 characters.
LEFT(MD5(NOW()), 6)这将返回具有 6 个字符的唯一代码。
Try another way like this
试试这样的另一种方式
SELECT LEFT(UUID(), 6);
LEFT(UUID(), 6)This will also return unique code
LEFT(UUID(), 6)这也将返回唯一的代码
回答by Hariramprasath Nandhagopalan
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 个字符的字符串。
回答by Antares
Simple and efficient solution to get a random 10 characters string with uppercase and lowercase letters and digits, without custom function (works with MySQL >= 5.6) :
简单有效的解决方案，用于获取带有大写和小写字母和数字的随机 10 个字符的字符串，无需自定义函数（适用于 MySQL >= 5.6）：
select substring(base64_encode(md5(rand())) from 1+rand()*4 for 10);
回答by Cristian Gon?alves
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.
SELECT COUNT(1) INTO @count FROM demo.codes;Then, foreach row (using
WHILEloop):Get the id of the next record to be uptdated
PREPARE stmt FROM "SELECT @id := id, @itemCode := code FROM demo.codes p LIMIT ?, 1;";EXECUTE stmt USING @i;Get a new code for the record:
SET @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: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;Finally,
CALLthe createdPROCEDUREin order to populate the fields fromcodecolumn that areNULL.CALL generateCodes();
计算将使用 6 个字符的新随机代码更新的所有记录。
SELECT COUNT(1) INTO @count FROM demo.codes;然后，foreach 行（使用
WHILE循环）：获取下一条要更新的记录的id
PREPARE stmt FROM "SELECT @id := id, @itemCode := code FROM demo.codes p LIMIT ?, 1;";EXECUTE stmt USING @i;获取记录的新代码：
SET @code = getRandomAlphaNumeric();最后，验证表中是否不存在新代码，以及当前字段列是否没有值 (is
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而创建的.PROCEDUREcodeNULLCALL generateCodes();
