创建，删除
DROP PROCEDURE IF EXISTS `proc_time`;
DELIMITER ;;
CREATE PROCEDURE proc_time()
BEGIN
set @currentDay = date_format(curdate(),'%Y%m%d');
set @currentDay1 = date_format(curdate()+1,'%Y%m%d');
set @currentMonth = date_format(curdate(),'%Y%m');
set @currentYear = date_format(curdate(),'%Y');
set @firstDayOfWeek = date_format(date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 0 DAY),'%Y%m%d');
set @fiftyDaysBefore = date_format(date_sub(curdate(),interval 3 day),'%Y%m%d');
set @fieldSql=concat("(id bigint NOT NULL AUTO_INCREMENT,
dayid int(11) NOT NULL DEFAULT 0,
maxTemp varchar(50) DEFAULT NULL,
minTemp varchar(50) DEFAULT NULL,
avgTemp varchar(50) DEFAULT NULL,
maxHumidity varchar(50) DEFAULT NULL,
minHumidity varchar(50) DEFAULT NULL,
avgHumidity varchar(50) DEFAULT NULL,
maxPm25 varchar(8) DEFAULT 0,
minPm25 varchar(8) DEFAULT 0,
avgPm25 varchar(8) DEFAULT 0,
maxVoc varchar(8) DEFAULT 0,
minVoc varchar(8) DEFAULT 0,
avgVoc varchar(8) DEFAULT 0,
maxAirquality varchar(50) DEFAULT NULL,
minAirquality varchar(50) DEFAULT NULL,
avgAirquality varchar(50) DEFAULT NULL,
comfortLevel varchar(50) DEFAULT NULL,
duration varchar(50) DEFAULT NULL,
consumption varchar(50) DEFAULT NULL,
ts varchar(50) DEFAULT NULL,
iid varchar(50) DEFAULT NULL,
rank varchar(50) DEFAULT NULL,
percent varchar(50) DEFAULT NULL,
deviceName varchar(50) DEFAULT NULL,
deviceType varchar(20) DEFAULT NULL,
KEY idx_ac_bd_hour_ts (ts),
KEY idx_ac_bd_hour_iid (iid),
PRIMARY KEY (id,dayid))
ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY RANGE (dayid)
(PARTITION p2000 VALUES LESS THAN (2000))");
set @createTableName=concat('ac_',@firstDayOfWeek,"W");
set @weeksql = concat("create table if not exists ",@createTableName,@fieldSql);
PREPARE create_weekTable from @weeksql;
EXECUTE create_weekTable;
DEALLOCATE PREPARE create_weekTable;
set @iidArrayStr=null;
set @preIID='0';
set @nextIID='0';
PREPARE iidQuerySql from "select distinct iid into @nextIID from ac_bd_hour WHERE dayid=? and iid>? order by iid limit 10000,1";
EXECUTE iidQuerySql using @currentDay,@preIID;
while @nextIID!=@preIID do
set @iidArrayStr=concat_ws(",",@iidArrayStr,@nextIID);
set @preIID=@nextIID;
EXECUTE iidQuerySql using @currentDay,@preIID;
end while;
DEALLOCATE PREPARE iidQuerySql;
set @iidArrayStr=concat_ws(",",@iidArrayStr,'z');
/*分区查询*/
set @patiCount=0;
set @patiQuerysql = "select count(1) into @patiCount from information_schema.partitions
where table_schema ='acbigdata' and table_name=? and partition_name=concat('p',?)";
PREPARE patiQuerysql from @patiQuerysql;
EXECUTE patiQuerysql using @createTableName,@currentDay;
if @patiCount=0 THEN
set @addParWeekDateSql = concat("ALTER TABLE ac_", @firstDayOfWeek, "W", " ADD PARTITION (PARTITION p", @currentDay," VALUES LESS THAN (",@currentDay1,"))");
SELECT concat("addParWeekDateSql is ",@addParWeekDateSql);
PREPARE addParWeekDate from @addParWeekDateSql;
EXECUTE addParWeekDate;
DEALLOCATE PREPARE addParWeekDate;
END IF;
set @deleteWeekDateSql = concat("ALTER TABLE ac_", @firstDayOfWeek, "W", " TRUNCATE PARTITION p", @currentDay);
SELECT concat("deleteWeekDateSql is ",@deleteWeekDateSql);
PREPARE deleteWeekDate from @deleteWeekDateSql;
EXECUTE deleteWeekDate;
DEALLOCATE PREPARE deleteWeekDate;
set @addWeekDatesql = concat("insert into ac_", @firstDayOfWeek, "W",
" (maxTemp, minTemp, avgTemp, maxHumidity, minHumidity,
avgHumidity, maxAirquality,minAirquality, avgAirquality,comfortLevel,
duration, consumption, ts, dayid, iid, rank, percent, deviceName, deviceType)",
" select MAX(maxTemp), MIN(minTemp), ROUND(AVG(avgTemp)),MAX(maxHumidity), MIN(minHumidity),
AVG(avgHumidity), MAX(maxAirquality), MIN(minAirquality),AVG(avgAirquality), ROUND(AVG(comfortLevel)),
SUM(duration),SUM(consumption),",@currentDay,",",@currentDay,",iid, '','','',''",
" from ac_bd_hour WHERE dayid =", @currentDay, " and iid >= ? and iid < ? group by iid");
PREPARE addWeekDate from @addWeekDatesql;
set @preIID='0';
set @iidIndex=1;
set @rowCount=0;
REPEAT
set @nextIID=substring_index(substring_index(@iidArrayStr,',',@iidIndex),',',-1);
EXECUTE addWeekDate using @preIID,@nextIID;
set @rowCount=@rowCount+ROW_COUNT();
set @preIID=@nextIID;
set @iidIndex=@iidIndex+1;
UNTIL @nextIID='z' END REPEAT;
DEALLOCATE PREPARE addWeekDate;
PREPARE logWrite from "INSERT INTO ac_log (dataType, isSuccess) VALUES (?,?)";
set @kind = 'W';
EXECUTE logWrite using @kind,@rowCount;
DEALLOCATE PREPARE logWrite;
END;;
DELIMITER ;
常用语法总结
-
DELIMITER ;; 换行符，使";"失效。
-
CREATE PROCEDURE 创建存储过程，后面接名称。
-
BEGIN 定义开始
-
set 设置变量
-
PARTITION BY RANGE (dayid)(PARTITION p2000 VALUES LESS THAN (2000))")
定义分区表，并说明初始分区。
-
concat("","") 链接函数
-
PREPARE from 编译sql
-
EXECUTE using 执行sql 并使用变量
-
while do end while;当满足某条件时，执行
-
select count(1) into @patiCount from information_schema.partitions
where table_schema ='acbigdata' and table_name=? and partition_name=concat('p',?)
查询某个表的某个分区是否存在。
-
if THEN END IF; 满足某条件执行动作。
-
REPEAT UNTIL END REPEAT; 重复执行，知道某个条件终止。
-
END;; 结束标志。
-
SELECT concat 存储过程执行时打印变量。
-
CALL XXXX(); 执行某个存储过程。
关注点
- 分区表插入的数据必须要满足分区条件，否则无法插入
新增分区:ALTERTABLEt1ADDPARTITION(PARTITIONp3VALUESLESSTHAN(2002));
分区使用详见官方文档:
https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html
- DROP PARTITION和TRUNCATE PARTITION区别
DROP PARTITION 将分区和分区数据一并删除
TRUNCATE PARTITION 只删除分区数据，保留分区
效率值 TRUNCATE<DROP,但是在只需要清除某个分区数据的场景下，建议使用TRUNCATE，这样能避免频繁创建分区带来的资源浪费。
- limit用法
(1)、SELECT*FROMtablename LIMIT [offset,]rows|rowsOFFSET offset
(2)、SELECT id FROM tablename WHERE id >= (SELECT MAX(id) FROM (SELECT id FROM tablename ORDER BY id LIMIT 10001) AS tmp) LIMIT 100;
0.14s
(3)、SELECT id FROM tablename WHERE id >= (SELECT MAX(id) FROM (SELECT id FROM tablename ORDER BY id LIMIT 10000,1) AS tmp) LIMIT 100;
0.01s
从上面的结果可以看出，LIMIT起到性能优化的作用，可以避免全表扫描。因为，如果第2种方法是利用全表扫描，那么就是从头开始的到大于10000的100条，时间会和第一种差不多，现在相差14倍，所以，LIMIT可以提高海量数据查询的性能。