MySQL如何生成随机数据 RAND函数与测试数据生成技巧

SELECT FLOOR(RAND() * 100); -- 生成0到99之间的随机整数

SELECT RAND() * 100; -- 生成0到100之间的随机浮点数

SELECT FLOOR(RAND() * (max - min + 1)) + min; -- 生成min到max之间的随机整数
-- 例如，生成10到20之间的随机整数：
SELECT FLOOR(RAND() * (20 - 10 + 1)) + 10;

SELECT MD5(RAND()); -- 生成32位的随机字符串
SELECT SUBSTRING(MD5(RAND()), 1, 10); -- 生成10位的随机字符串

DELIMITER //
CREATE PROCEDURE generate_test_data(IN num_rows INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < num_rows DO
    INSERT INTO your_table (column1, column2) VALUES (FLOOR(RAND() * 100), SUBSTRING(MD5(RAND()), 1, 10));
    SET i = i + 1;
  END WHILE;
END //
DELIMITER ;

CALL generate_test_data(100); -- 生成100条测试数据

CREATE TEMPORARY TABLE temp_data AS
SELECT
  FLOOR(RAND() * 100) AS column1,
  SUBSTRING(MD5(RAND()), 1, 10) AS column2
FROM
  (SELECT 1 AS n UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) AS numbers -- 生成5行数据，可以根据需要增加UNION ALL的数量
LIMIT 1000; -- 控制生成的数据量

INSERT INTO your_table (column1, column2) SELECT column1, column2 FROM temp_data;

DROP TEMPORARY TABLE IF EXISTS temp_data;

INSERT INTO your_table (column1, column2) VALUES
(FLOOR(RAND() * 100), SUBSTRING(MD5(RAND()), 1, 10)),
(FLOOR(RAND() * 100), SUBSTRING(MD5(RAND()), 1, 10)),
(FLOOR(RAND() * 100), SUBSTRING(MD5(RAND()), 1, 10));
--  ...  更多行

SET @prefix = '138'; -- 假设号段是138
SET @random_number = FLOOR(RAND() * 100000000); -- 生成8位随机数
SELECT CONCAT(@prefix, LPAD(@random_number, 8, '0')); -- 拼接成11位手机号码

SET @username = SUBSTRING(MD5(RAND()), 1, 8); -- 随机用户名
SET @domain = 'example.com'; -- 域名
SELECT CONCAT(@username, '@', @domain); -- 拼接成邮箱地址

SET @start_date = '2023-01-01';
SET @end_date = '2023-12-31';
SET @days = TIMESTAMPDIFF(DAY, @start_date, @end_date); -- 计算日期差
SELECT DATE_ADD(@start_date, INTERVAL FLOOR(RAND() * @days) DAY); -- 生成随机日期

SET @enum_values = 'value1,value2,value3'; -- 枚举值列表
SET @num_values = LENGTH(@enum_values) - LENGTH(REPLACE(@enum_values, ',', '')) + 1; -- 计算枚举值个数
SET @random_index = FLOOR(RAND() * @num_values) + 1; -- 随机索引
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@enum_values, ',', @random_index), ',', -1); -- 获取随机枚举值

SELECT id FROM related_table ORDER BY RAND() LIMIT 1; -- 随机选择一个ID

SELECT COUNT(DISTINCT column1) FROM your_table;
SELECT COUNT(*) FROM your_table;
-- 如果两个COUNT值相等，则说明column1是唯一的

SELECT MIN(column1), MAX(column1) FROM your_table;

SELECT * FROM your_table WHERE column2 NOT REGEXP '^[a-zA-Z0-9]+$'; -- 查找不符合格式的数据

SELECT column3, COUNT(*) FROM your_table GROUP BY column3;