如何为我的 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 字符代码(最好是字母和数字)，这样它们就不会违反我的表中的唯一约束?请注意，表中不符合条件的列(例如 Where 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.

这与这个问题不同——Generating a random &使用 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.

推荐答案

BEFORE UPDATE 触发器解决方案:

您可以使用以下命令创建 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屋！