模拟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;
![[ 数据库 ] MySQL 入门到放弃(五) --- MySQL 函数](https://ucc.alicdn.com/pic/developer-ecology/f43a903b70ed41448b87498db06143bb.png?x-oss-process=image/resize,h_160,m_lfit)
