如何为MySql表列之一生成唯一的随机字符串? [英] How do I generate a unique, random string for one of my MySql table columns?

问题描述

我正在使用MySql 5.5.37.我有一个包含以下各列的表

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

+------------------+------------------+------+-----+---------+-------+
| 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个字符的代码(最好是字母和数字)，以免它们违反表中的唯一约束?请注意，表格中不符合条件的列(例如，其中COLUMN1 <>> 0的列)已经具有CODE列的唯一值.

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.

这与此问题不同– 生成随机&使用MySQL唯一的8个字符串，因为该链接处理的ID是数字.我的ID是32个字符的字符串.同样，他们的解决方案没有考虑到以下事实:在运行我要运行的语句之前，表中可能存在一些值，这些值将为所讨论的列生成唯一的值.

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.

推荐答案

更新前触发解决方案:

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

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

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

为了不创建已经存在的字符串，可以使用BEFORE UPDATE触发器.

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

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时，触发器都会在循环中创建一个新的随机字符串，直到在表中找不到匹配项为止.

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.

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

Now you can replace all NULL values with:

update unique_codes set CODE = NULL where code is NULL;

在此处的SQLFiddle演示中，我使用一个字符随机字符串来证明没有值重复.

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

您还可以在BEFORE INSERT触发器中使用相同的代码.这样，您只需使用CODE=NULL插入新行，触发器就会将其设置为新的唯一随机字符串.而且您将不需要再次更新.

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.

原始答案(32个字符串):

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

-- output example: 3AHX44TF

将生成一个8个字符的字母数字大写随机字符串.将其中的四个连接起来可获得32个字符:

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

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

那么唯一性呢?好吧-尝试生成重复项;-)

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

这篇关于如何为MySql表列之一生成唯一的随机字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！