generate random value in MySQL MySQL中产生随机值

generate random value in MySQL
MySQL里产生随机值

没Oracle好用, 只有有限的几个功能, 除非写函数实现

1. 生成随机数

用rand函数
如, 产生大于等于7,小于12的整数
rand函数产生了一个大于等于0小于1的浮点数

select floor(7+rand()*(12-7));

mysql> select floor(7+rand()*(12-7));
+------------------------+
| floor(7+rand()*(12-7)) |
+------------------------+
|                      9 |
+------------------------+
1 row in set (0.00 sec)

mysql>

2. 产生一个随机字母

用elt(round(rand())+1,'A','a')返回字母'A'或'a', 用ascii函数转换成ascii代码, 即65或97
然后加上用floor(rand()*26)产生的大于等于0小于26的整数, 最后用char函数转换为ascii字符

select char(floor(rand()*26)+ascii(elt(round(rand())+1,'A','a')));

mysql> select char(floor(rand()*26)+ascii(elt(round(rand())+1,'A','a')));
+------------------------------------------------------------+
| char(floor(rand()*26)+ascii(elt(round(rand())+1,'A','a'))) |
+------------------------------------------------------------+
| u                                                          |
+------------------------------------------------------------+
1 row in set (0.00 sec)

或
用floor(1+(rand()*52)产生一个大于等于1小于等于52的数字, 以此用substr函数从大小写英文字符串取出其中一个

select substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',floor(1+(rand()*52)),1);

mysql> select substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',floor(1+(rand()*52)),1);
+---------------------------------------------------------------------------------------+
| substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',floor(1+(rand()*52)),1) |
+---------------------------------------------------------------------------------------+
| y                                                                                     |
+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

或
同前, 用elt函数从后面的若干字符串中取出一个, 每个字符串只有一个字符

select elt(floor(1+(rand()*52)),
'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');

mysql> select elt(floor(1+(rand()*52)),
    -> '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');
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| elt(floor(1+(rand()*52)),
'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') |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| I                                                                                                                                                                                                                                           |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3. 生成小写和数字混合的字符串

用rand函数产生一个大于等于0小于1的的浮点数, 然后用md5函数计算它的MD5值, 返回一个由32个16进制数组成的字符串

select md5(rand());

mysql> select md5(rand());
+----------------------------------+
| md5(rand())                      |
+----------------------------------+
| 1450f3993ca13b8b500fe9275d3ee8fa |
+----------------------------------+
1 row in set (0.00 sec)

4. 生成大写和数字混合的字符串

用rand函数产生一个大于等于0小于1的的浮点数, 与一个很大数相乘后取整(floor函数), 然后用conv函数转换成36进制数
(36进制数包括26个英文字母和10个数字)

select conv(floor(rand() * 99999999999999), 10, 36);

mysql> select conv(floor(rand() * 99999999999999), 10, 36);
+----------------------------------------------+
| conv(floor(rand() * 99999999999999), 10, 36) |
+----------------------------------------------+
| T13IANG02                                    |
+----------------------------------------------+
1 row in set (0.00 sec)

外部链接:
Chapter 11. Functions and Operators



-fin-