Mysql模拟数据

模拟SQL

模拟序号

模拟1到10000的1w条序号记录

--  :=是赋值符号,@row := 0用来初始化变量,@row := @row + 1为每次+1并赋值给变量@row
SELECT @row := @row + 1 as number 
FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
     (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
     (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c,
     (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d,
     (SELECT @row := 0) r;
	 
-- 等同于
set @row = 0;
SELECT @row := @row + 1 as number 
FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
     (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
     (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c,
     (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d
		 order by number asc;

或者

SELECT (t4.i + t3.i * 10 + t2.i * 100 + t1.i * 1000 + 1) AS number
FROM
    (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS t4,
    (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS t3,
    (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS t2,
    (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS t1
-- WHERE (t4.i + t3.i * 10 + t2.i * 100 + t1.i * 1000 + 1) <= 10000
ORDER BY number;

日期模拟(1w上限)

根据日期的起止时间查询出所有日期,通过对起始日期进行DATE_ADD

-- 初始化日期范围和计数器
SET @start_date = '2024-01-01';
SET @end_date = '2024-07-31';

-- 生成日期序列
SELECT 
    DATE_ADD(@start_date, INTERVAL n DAY) AS date
FROM (
select @row := @row + 1 as n FROM (
	(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
	(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
	(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c,
	(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d,
	(SELECT @row := -1) r)
) numbers 
WHERE 
    DATE_ADD(@start_date, INTERVAL n DAY) <= @end_date;

id字符串转集合(1w上限)

将逗号分隔的字符串显示为独立的行

-- 初始化卡号字符串和变量
SET @id_list = '17366752804,05874382354,12926556270,09816968344,04957446360';
-- SUBSTRING_INDEX(SUBSTRING_INDEX(@id_list, ',', n), ',', -1)通过截取字符串方式获得独立的id,通过where条件 n <= LENGTH(@id_list) - LENGTH(REPLACE(@id_list, ',', '')) + 1;判断id的个数
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@id_list, ',', n), ',', -1) AS id
FROM (
select @row := @row + 1 as n FROM (
	(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
	(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
	(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c,
	(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d,
	(SELECT @row := 0) r)
) numbers 
WHERE n <= LENGTH(@id_list) - LENGTH(REPLACE(@id_list, ',', '')) + 1;

随机数

SET @min_val = 1.3;
SET @max_val = 1.5;

select ROUND(@min_val + (RAND() * (@max_val - @min_val)), 3) AS val;

案例

模拟设备数据(每个设备每天一条记录)

CREATE TABLE `device_energy_report` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` tinyint(4) DEFAULT '1' COMMENT '类型 1:day 2:month 3:year',
  `day` varchar(36) NOT NULL COMMENT '日',
  `deviceid` varchar(14) NOT NULL COMMENT '设备id',
  `qu` float(11,3) DEFAULT NULL COMMENT '电量值',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_kh_tm` (`deviceid`,`day`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=894103 DEFAULT CHARSET=utf8 COMMENT='报表汇总';

-- 删除现有临时表（如果存在）,临时表仅存在于本次会话,关闭连接时会删除并释放临时表
DROP TEMPORARY TABLE IF EXISTS temp_device_energy_report;
-- 根据原表创建临时表用于生成模拟数据,表需要deviceid和day组成唯一约束
CREATE TEMPORARY TABLE temp_device_energy_report SELECT * FROM device_energy_report WHERE false;

-- 日期范围,支持1w天的跨度
SET @start_date = '2024-01-01';
SET @end_date = '2024-07-03';
-- 初始化设备id字符串,支持1w个设备
SET @id_list = '17366752804,05874382354,12926556270,09816968344,04957446360';
-- 随机数范围
SET @min_val = 1.3;
SET @max_val = 1.5;

-- 1.将生成的数据insert到临时表中去,(包括deviceid,day和随机数)
INSERT INTO temp_device_energy_report (`day`, deviceid, qu,type,create_time) 
select date_numbers.`date` as day,id_numbers.id as deviceid,
	ROUND(@min_val + (RAND() * (@max_val - @min_val)), 3) AS val ,
	null as type,
  DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS create_time
from 
(
	SELECT 
			DATE_ADD(@start_date, INTERVAL n DAY) AS `date`
	FROM (
	select @row1 := @row1 + 1 as n FROM 
	(
		(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
		(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
		(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c,
		(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d,
		(SELECT @row1 := -1) r)
	) row_generator 
	WHERE 
  DATE_ADD(@start_date, INTERVAL n DAY) <= @end_date
) date_numbers,
(
	SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@id_list, ',', n), ',', -1) AS id
	FROM (
	select @row2 := @row2 + 1 as n FROM (
		(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
		(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
		(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c,
		(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d,
		(SELECT @row2 := 0) r)
	) row_generator 
	WHERE n <= LENGTH(@id_list) - LENGTH(REPLACE(@id_list, ',', '')) + 1
) id_numbers;

-- 2.查询生成的数据
select * from temp_device_energy_report;

-- 3.将临时表的数据插入到目标表中,ON DUPLICATE KEY UPDATE 在主键重复时更新哪些字段
INSERT INTO `device_energy_report` (`day`, deviceid, qu,type,create_time)
SELECT `day`, deviceid, qu,type,create_time FROM temp_device_energy_report 
    ON DUPLICATE KEY UPDATE 
    qu = VALUES(qu),
    create_time = VALUES(create_time);

-- 删除临时表
DROP TEMPORARY TABLE temp_device_energy_report;

模拟报表数据(每日一条记录)

-- 删除现有临时表（如果存在）
DROP TEMPORARY TABLE IF EXISTS temp_citypower_dates;

-- 创建临时表用于生成模拟数据
CREATE TEMPORARY TABLE temp_citypower_dates (
    city_id INT,
    daytime DATE,
    type INT,
    power DECIMAL(10, 2),
    elecfee DECIMAL(10, 2),
    create_time DATETIME
);

-- 定义变量
SET @city_id = 65; -- 统计的城市id
SET @start_date = '2023-01-01';
SET @end_date = '2023-12-31';
SET @min_power = 10.0;
SET @max_power = 50.0;

-- 插入指定月份的每一天数据，带有随机的power值,每次只能插入100条数据(即每次三个月)
INSERT INTO temp_citypower_dates (city_id, daytime, type, power, elecfee, unit_price, create_time)
SELECT
    @city_id AS city_id,
    DATE_FORMAT(DATE_ADD(@start_date, INTERVAL (t4.i * 10 + t3.i) DAY), '%Y-%m-%d') AS daytime,
    0 AS type,
    ROUND(@min_power + (RAND() * (@max_power - @min_power)), 2) AS power, -- 生成 @min_power 到 @max_power 之间的随机浮动值
    NULL AS elecfee,
    DATE_FORMAT(DATE_ADD(@start_date, INTERVAL (t4.i * 10 + t3.i) DAY), '%Y-%m-%d 12:00:00') AS create_time
FROM 
    (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t4,
    (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3
WHERE 
    DATE_ADD(@start_date, INTERVAL (t4.i * 10 + t3.i) DAY) <= @end_date;

-- 将临时表的数据插入到目标表中,ON DUPLICATE KEY UPDATE 在主键重复时
INSERT INTO `city_power_data` (`city_id`, `daytime`,  `power`,  `create_time`)
SELECT `city_id`, `daytime`,  `power`,  `create_time` FROM temp_citypower_dates ON DUPLICATE KEY UPDATE 
    power = VALUES(power),
    create_time = VALUES(create_time);

select * from temp_citypower_dates;
-- 删除临时表
DROP TEMPORARY TABLE temp_citypower_dates;

posted @ 2024-07-01 11:37 秋夜雨巷阅读(7) 评论(0) 编辑收藏举报

秋夜雨巷

GitHub地址: https://autkevin.github.io/
本博客是为了记录工作和学习中的总结，可以交流学习，请勿在不理解的情况下直接复制粘贴

Mysql模拟数据

模拟SQL

模拟序号

日期模拟(1w上限)

id字符串转集合(1w上限)

随机数

案例

模拟设备数据(每个设备每天一条记录)

模拟报表数据(每日一条记录)

公告

秋夜雨巷

GitHub地址: https://autkevin.github.io/ 本博客是为了记录工作和学习中的总结，可以交流学习，请勿在不理解的情况下直接复制粘贴

Mysql模拟数据

模拟SQL

模拟序号

日期模拟(1w上限)

id字符串转集合(1w上限)

随机数

案例

模拟设备数据(每个设备每天一条记录)

模拟报表数据(每日一条记录)

公告

GitHub地址: https://autkevin.github.io/
本博客是为了记录工作和学习中的总结，可以交流学习，请勿在不理解的情况下直接复制粘贴