如何在现有数据库中生成唯一ID？

I have a mysql table named Food. I have 560 rows in it and the ID column is set to null for all rows. I want to generate random 10 length unique strings for the ID-s. How can I do that?

我有一个名为Food的mysql表。我有560行,所有行的ID列都设置为null。我想为ID-s生成随机10个长度的唯一字符串。我怎样才能做到这一点?

1 个解决方案

#1

Simplest is to just create a function to generate a random char and a unique index to guarantee uniqueness, if the insert fails due to duplicates, just try again.

最简单的方法是创建一个函数来生成随机字符和唯一索引以保证唯一性,如果插入因重复而失败,请再试一次。

CREATE FUNCTION random_char() RETURNS CHAR(1)
RETURN ELT(FLOOR(1 + (RAND() * 62)), 
    'a','b','c','d','e','f','g','h','i','j','k','l','m',
    'n','o','p','q','r','s','t','u','v','w','x','y','z',
    'A','B','C','D','E','F','G','H','I','J','K','L','M',
    'N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
    '0','1','2','3','4','5','6','7','8','9');

CREATE UNIQUE INDEX id_ux ON test (id);

UPDATE test 
SET id=CONCAT(random_char(), random_char(), random_char(), random_char(),
              random_char(), random_char(), random_char(), random_char(),
              random_char(), random_char())
WHERE id IS NULL;

DROP INDEX id_ux on test;