在MySQL中生成唯一的10个字符的字母数字哈希值

Question

我有一个简单的表格，其中有一个名为"hash" VARCHAR 10 UNIQUE FIELD的字段。

现在我想运行一个查询并自动生成字段内的哈希值。

问题在于哈希值必须是字母数字组合，长度为10个字符，并且唯一。

表格结构如下：

CREATE TABLE `vouchers` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `hash` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash` (`hash`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

我需要将哈希值插入到哈希字段中，它们应该看起来像随机的字母数字哈希值，也就是说用户不应该能够通过查看一个哈希值来获取下一个或前一个哈希值，此外它们必须是10个字符长且唯一的。

有人知道如何实现吗？

- itsme

---

MySQL仅支持自动递增的整数，并且对varchar列的选择操作始终比int列慢。我猜你一定有非常充分的理由想要这样做？ - Raymond Nijland

@RaymondNijland 是的，为了避免从应用程序端创建脚本 :) - itsme

Answer 1

-- most elegant, has adjustable length 1-32 and probably has best performance
SELECT SUBSTR(REPLACE(UUID(),'-',''),1,10) as randomStringUUID
;

-- generate 10 character [a-z0-9] string, has adjustable letter/nr ratio
SELECT CONCAT(
  CASE WHEN RAND()>=0.5 THEN char(round(RAND()*9+48)) ELSE char(round(RAND()*25+97)) END
  ,CASE WHEN RAND()>=0.5 THEN char(round(RAND()*9+48)) ELSE char(round(RAND()*25+97)) END
  ,CASE WHEN RAND()>=0.5 THEN char(round(RAND()*9+48)) ELSE char(round(RAND()*25+97)) END
  ,CASE WHEN RAND()>=0.5 THEN char(round(RAND()*9+48)) ELSE char(round(RAND()*25+97)) END
  ,CASE WHEN RAND()>=0.5 THEN char(round(RAND()*9+48)) ELSE char(round(RAND()*25+97)) END
  ,CASE WHEN RAND()>=0.5 THEN char(round(RAND()*9+48)) ELSE char(round(RAND()*25+97)) END
  ,CASE WHEN RAND()>=0.5 THEN char(round(RAND()*9+48)) ELSE char(round(RAND()*25+97)) END
  ,CASE WHEN RAND()>=0.5 THEN char(round(RAND()*9+48)) ELSE char(round(RAND()*25+97)) END
  ,CASE WHEN RAND()>=0.5 THEN char(round(RAND()*9+48)) ELSE char(round(RAND()*25+97)) END
  ,CASE WHEN RAND()>=0.5 THEN char(round(RAND()*9+48)) ELSE char(round(RAND()*25+97)) END
  ) as randomString
;

-- as bonus: generate a variable size letter only string, best for emulating names/words
SELECT SUBSTR(CONCAT(char(RAND()*25+55),char(RAND()*25+97),char(RAND()*25+97),char(RAND()*25+97),char(RAND()*25+97),char(RAND()*25+97),char(RAND()*25+97),char(RAND()*25+97),char(RAND()*25+97),char(RAND()*25+97),char(RAND()*25+97),char(RAND()*25+97)),1,RAND()*9+4) as RandomName

Test at http://sqlfiddle.com/#!8/d41d8/586

- Stefan Rogin

Answer 2

这是将Gordon上面的答案封装成一个函数的代码（感谢Gordon） -

delimiter |
create function hash10() returns varchar(10)
begin
declare chars varchar(36);
set chars = '0123456789abcdefghijklmnopqrstuvwxyz';
return concat(substring(chars, floor(rand()*36) + 1, 1),
              substring(chars, floor(rand()*36) + 1, 1),
              substring(chars, floor(rand()*36) + 1, 1),
              substring(chars, floor(rand()*36) + 1, 1),
              substring(chars, floor(rand()*36) + 1, 1),
              substring(chars, floor(rand()*36) + 1, 1),
              substring(chars, floor(rand()*36) + 1, 1),
              substring(chars, floor(rand()*36) + 1, 1),
              substring(chars, floor(rand()*36) + 1, 1),
              substring(chars, floor(rand()*36) + 1, 1)
             );
end|
delimiter ;

然后，您可以使用...

insert into x (hash) values (hash10()),(hash10()),(hash10());

- zevra0

---

声明字符变量为 varchar(36)； - wieczorek1990

Answer 3

如果您想为此字段创建独特的值，您可以使用自增长方法，只要是基于36进制即可。下面是一个例子，可以生成数亿个不同的值：

update t cross join (select @i := 0, @chars = '0123456789abcdefghijklmnopqrstuvwxyz') const
    set hash = concat(substring(@chars, ((@i := @i + 1) %36)+1, 1),
                      substring(@chars, floor(@i/pow(36, 1))%36 + 1, 1),
                      substring(@chars, floor(@i/pow(36, 2))%36 + 1, 1),
                      substring(@chars, floor(@i/pow(36, 3))%36 + 1, 1),
                      substring(@chars, floor(@i/pow(36, 4))%36 + 1, 1),
                      substring(@chars, floor(@i/pow(36, 5))%36 + 1, 1),
                      '0000'
                     );

编辑：（基于修改后的问题）

你的表上有一个唯一约束条件。我会采取以下措施：

insert into vouchers(hash)
    select concat(substring(@chars, floor(rand()*36) + 1, 1),
                  substring(@chars, floor(rand()*36) + 1, 1),
                  substring(@chars, floor(rand()*36) + 1, 1),
                  substring(@chars, floor(rand()*36) + 1, 1),
                  substring(@chars, floor(rand()*36) + 1, 1),
                  substring(@chars, floor(rand()*36) + 1, 1),
                  substring(@chars, floor(rand()*36) + 1, 1),
                  substring(@chars, floor(rand()*36) + 1, 1),
                  substring(@chars, floor(rand()*36) + 1, 1),
                  substring(@chars, floor(rand()*36) + 1, 1)
                 );

只需在循环中重复执行此操作（或根据需要执行），即可填充表格。极不可能出现重复的情况，如果出现，则该插入操作将失败。

- Gordon Linoff

---

谢谢，不错，我正在测试它，但是返回错误：[查询2中的错误]每个派生表都必须有自己的别名。 - itsme

你可以将其封装在 MySQL 函数中以获得更简便的结果。 - zevra0

@sbaaaang . . . 我测试了逻辑，但是在 select 语句中，而不是更新语句中。我只是将 @i 的赋值添加了 const 并将 chars 移到了相同的子查询中。这消除了单独的 set 命令。 - Gordon Linoff

@GordonLinoff 现在没有错误，但是我无法插入哈希值，表格是空的 :P - itsme

@sbaaaang . . . 或许我误解了问题。您是想更新现有表格还是将新行插入到新表格中？ - Gordon Linoff

Answer 4

我认为最好从应用程序逻辑处理这个问题。
如果你想使用sql处理它，可以尝试使用mysql函数UUID()（但生成的uuid长度为36个字符）。

- manuskc

---

确实，你应该从应用程序中处理它，但要做到这一点会更加困难。 - Raymond Nijland

@Manu，我同意，只是我有点懒，想知道是否有什么方法可以避免编写应用程序脚本。 - itsme

Answer 5

只需使用循环：

DROP FUNCTION hash10;
DELIMITER |
CREATE FUNCTION hash10() RETURNS VARCHAR(10)
BEGIN
  DECLARE chars VARCHAR(36);
  DECLARE result VARCHAR(10);
  DECLARE i INT;
  SET chars = '0123456789abcdefghijklmnopqrstuvwxyz';
  SET result = '';
  SET i = 0;
  label: LOOP
    SET result = CONCAT(result, SUBSTRING(chars, FLOOR(RAND()*36) + 1, 1));
    SET i = i + 1;
    IF i = 10 THEN
      LEAVE label;
    END IF;
  END LOOP label;
  RETURN result;
END|
DELIMITER ;

要生成不同长度，只需将所有的10替换为不同的数字即可。

- wieczorek1990