MySQL 生成随机测试数据

1. 随机生成各种类型的数据

1.1. 生成[a,b)范围内的浮点数

RAND()生成[0,1)范围内的浮点数

SELECT RAND()

RAND()*n生成[0,n)范围内的浮点数

SELECT RAND() * 100

RAND()*(b-a)+a生成[a,b)范围内的浮点数

SELECT RAND() * (100-60) + 60

1.2. 生成[a,b)范围内的整数

FLOOR(RAND()*n)生成[0,n)范围内的整数

SELECT RAND() * 100

FLOOR(RAND()*(b-a))+a生成[a,b)范围内的整数

SELECT FLOOR(RAND()*(100-60)) + 60

SELECT FLOOR(RAND()*3)

1.3. 生成随机字符串

SELECT MD5(RAND()) 生成32位随机的数字0~9和小写字母a~z的组合

mysql> SELECT MD5(RAND());
+----------------------------------+
| MD5(RAND())                      |
+----------------------------------+
| d799e1b276adebb6e572ba6964731806 |
+----------------------------------+

SELECT SHA(RAND()) 或者 SELECT SHA1(RAND()) 生成40位随机的数字0~9和小写字母a~z的组合。目前MySQL的SHA就是采用SHA-1

mysql> SELECT SHA(RAND());
+------------------------------------------+
| SHA(RAND())                              |
+------------------------------------------+
| 0ba19012421eaf2c325f1c6371187f5aa8a5c9ee |
+------------------------------------------+

1.4. 生成随机日期

MAKEDATE(year, dayOfYear) 指定年份，以及该年的第几天，得到对应日期

mysql> SELECT MAKEDATE(2018,1);
+------------------+
| MAKEDATE(2018,1) |
+------------------+
| 2018-01-01       |
+------------------+

mysql> SELECT MAKEDATE(2018,300);
+--------------------+
| MAKEDATE(2018,300) |
+--------------------+
| 2018-10-27         |
+--------------------+

mysql> SELECT MAKEDATE(2018,3660);  -- 可以跨年
+---------------------+
| MAKEDATE(2018,3660) |
+---------------------+
| 2028-01-08          |
+---------------------+
1 row in set (0.05 sec)

mysql> SELECT MAKEDATE(2018,-1);    -- 不可以为负数
+-------------------+
| MAKEDATE(2018,-1) |
+-------------------+
| NULL              |
+-------------------+

注意年份可以用2位或4位数字来表示，2位数，70~99对应1970~1999，00~69对应2000~2069

mysql> SELECT MAKEDATE(70,2);
+----------------+
| MAKEDATE(70,2) |
+----------------+
| 1970-01-02     |
+----------------+
mysql> SELECT MAKEDATE(69,2);
+----------------+
| MAKEDATE(69,2) |
+----------------+
| 2069-01-02     |
+----------------+

指定年份，随机生成日期

SELECT MAKEDATE(2018,FLOOR(RAND()*365))

生成随机日期

SELECT MAKEDATE(FLOOR(RAND()*20)+2000,FLOOR(RAND()*365))

1.5. 生成随机时间

MAKETIME(hour, minute, second) 生成时分秒

mysql> SELECT MAKETIME(1,1,1);
+-----------------+
| MAKETIME(1,1,1) |
+-----------------+
| 01:01:01        |
+-----------------+
mysql> SELECT MAKETIME(10,20,30);
+--------------------+
| MAKETIME(10,20,30) |
+--------------------+
| 10:20:30           |
+--------------------+

生成随机时间

SELECT MAKETIME(FLOOR(RAND()*24),FLOOR(RAND()*60),FLOOR(RAND()*60));

生成随机日期和时间

SELECT CONCAT(
    MAKEDATE(FLOOR(RAND()*20)+2000, FLOOR(RAND()*365)), ' ',
    MAKETIME(FLOOR(RAND()*24),FLOOR(RAND()*60),FLOOR(RAND()*60)))

2. 随机生成记录

随机生成N条记录

-- 删除存储过程
DROP PROCEDURE IF EXISTS generate_random_record;
-- 临时修改SQL语句结束符，防止与存储过程的语句结束符冲突
DELIMITER $$    
-- 创建存储过程，用 "IN <形参名> 数据类型" 定义一个形参 
CREATE PROCEDURE generate_random_record(IN n INT)
BEGIN
    DECLARE age INT DEFAULT 0;
    DECLARE name VARCHAR(40) DEFAULT '';
    DECLARE birthday DATETIME;
        
    -- 声明一个整数变量
    DECLARE i INT DEFAULT 0;

    -- 开启事务    
    SET AUTOCOMMIT = 0;

    -- 循环N次，生成N条记录
    WHILE i < n DO
        SET age = FLOOR(RAND()*90) + 10;
        SET name = MD5(RAND());
        SET birthday = MAKEDATE(FLOOR(RAND()*20+2000),FLOOR(RAND()*365));
        INSERT INTO person (age, name, birthday) VALUES (age, name, birthday);
        SET i = i + 1;
    END WHILE;

    -- 提交事务
    SET AUTOCOMMIT = 1;
END $$
-- 恢复SQL语句的默认结束符（分号）
DELIMITER ; 

-- 调用存储过程
CALL generate_random_record(20);

特别注意，设置开启事务和提交事务，实现批量插入的效果，能大幅提高效率