MySQL生成随机字符串

怼烎@ 2022-12-13 01:50 298阅读 0赞

编程中经常遇到生成随机字符串的需求场景，比如验证码、初始密码等，一般情况下通过服务器端程序完成这个需求。笔者前面写的[Java中生成一个随机字符串][Java]就是针对这种需求的。

但是，你可能还会遇到给数据库中所有数据新增一个字段，并且给一个随机的初始值的需求（是的，我遇到过），所以，有必要整理一些通过MySQL内置函数生成随机字符串的方法。

### 生成随机数字 ###

#### 随机函数 ####

MySQL中也有一个生成随机数的函数`RAND()`。生成的是0到1之间的随机浮点数。

mysql> select RAND();
    +--------------------+
    | RAND()             |
    +--------------------+
    | 0.6373184441005052 |
    +--------------------+
    1 row in set (0.04 sec)

#### 借助字符串截取函数 ####

实际需求中需要的大部分是一个连续的数字字符串，所以，可以使用`SUBSTRING()`函数截取浮点数的一部分。比如，我们需要一个6位的数字字符串：

mysql> select substring(rand(),3,6);
    +-----------------------+
    | substring(rand(),3,6) |
    +-----------------------+
    | 504347                |
    +-----------------------+
    1 row in set (0.00 sec)

`SUBSTRING()`是一个重载函数（MySQL里应该也叫重载函数，没有考证过），`SUBSTRING(str, n)`表示从str的第n个字符开始截取，直到str的结尾；`SUBSTRING(str, n, m)`表示从str的第n个字符开始截取，共截取m个字符。他们还可以表示为：`SUBSTRING(str from n)`和`SUBSTRING(str from n for m)`。截取起始位置的字符计数从1开始。

mysql> select SUBSTRING('123456789', 3),SUBSTRING('123456789' from 3);
    +---------------------------+-------------------------------+
    | SUBSTRING('123456789', 3) | SUBSTRING('123456789' from 3) |
    +---------------------------+-------------------------------+
    | 3456789                   | 3456789                       |
    +---------------------------+-------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select SUBSTRING('123456789', 3, 2),SUBSTRING('123456789' from 3 for 2);
    +------------------------------+-------------------------------------+
    | SUBSTRING('123456789', 3, 2) | SUBSTRING('123456789' from 3 for 2) |
    +------------------------------+-------------------------------------+
    | 34                           | 34                                  |
    +------------------------------+-------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select SUBSTRING('123456789', 9, 2),SUBSTRING('123456789' from 9 for 2);
    +------------------------------+-------------------------------------+
    | SUBSTRING('123456789', 9, 2) | SUBSTRING('123456789' from 9 for 2) |
    +------------------------------+-------------------------------------+
    | 9                            | 9                                   |
    +------------------------------+-------------------------------------+
    1 row in set (0.00 sec)

#### 借助取整函数 ####

mysql> SELECT CEILING(RAND()*500000+500000);
    +-------------------------------+
    | CEILING(RAND()*500000+500000) |
    +-------------------------------+
    |                        977906 |
    +-------------------------------+
    1 row in set (0.03 sec)
    
    mysql> SELECT FLOOR(RAND()*500000 + 500000);
    +-------------------------------+
    | FLOOR(RAND()*500000 + 500000) |
    +-------------------------------+
    |                        940636 |
    +-------------------------------+
    1 row in set (0.03 sec)

`FLOOR(n)`返回小于等于n的最大整数。

mysql> select floor(5.0),floor(4.5),floor(-1.5);
    +------------+------------+-------------+
    | floor(5.0) | floor(4.5) | floor(-1.5) |
    +------------+------------+-------------+
    |          5 |          4 |          -2 |
    +------------+------------+-------------+
    1 row in set (0.04 sec)

`CEILING(n)`返回大于等于n的最小整数，可简写为`CEIL(n)`。

mysql> select ceil(5.0),ceiling(5.0);
    +-----------+--------------+
    | ceil(5.0) | ceiling(5.0) |
    +-----------+--------------+
    |         5 |            5 |
    +-----------+--------------+
    1 row in set (0.00 sec)
    
    mysql> select ceil(5.1),ceil(4.9);
    +-----------+-----------+
    | ceil(5.1) | ceil(4.9) |
    +-----------+-----------+
    |         6 |         5 |
    +-----------+-----------+
    1 row in set (0.00 sec)

### 生成随机数字和字母的组合 ###

如果需要生成的随机字符串中既有数字又有字母，可以使用MD5将随机数加密，然后再截取。

mysql> select substring(MD5(RAND()),1,6);
    +----------------------------+
    | substring(MD5(RAND()),1,6) |
    +----------------------------+
    | 6b63ac                     |
    +----------------------------+
    1 row in set (0.03 sec)

### 实际案例 ###

假设现在有一个用户表，里面记录了用户名，但是现在有一个为了提需求而提出需求：给用户表增加一个密码列，并给每个用户设置一个随机密码。

mysql> select id,pwd from t_user;
    +----+------+
    | id | pwd  |
    +----+------+
    |  1 | NULL |
    |  2 | NULL |
    |  3 | NULL |
    |  4 | NULL |
    |  5 | NULL |
    |  6 | NULL |
    +----+------+
    6 rows in set (0.00 sec)

mysql> update t_user set pwd = substring(MD5(RAND()),1,6);
    Query OK, 6 rows affected (0.00 sec)
    Rows matched: 6  Changed: 6  Warnings: 0
    
    mysql> select id,pwd from t_user;
    +----+--------+
    | id | pwd    |
    +----+--------+
    |  1 | 3aaa34 |
    |  2 | 7e0ef6 |
    |  3 | cc59c1 |
    |  4 | f9d2c0 |
    |  5 | 0b95b9 |
    |  6 | 04ee59 |
    +----+--------+
    6 rows in set (0.00 sec)

[Java]: https://blog.csdn.net/qq_38234015/article/details/108798958