项目需求:
传感器大数据量数据表,按照日期自动分区,现根据时间筛选条件进行跨分区查询数据。
表分区查询语句语法
mysql表分区查询记录
SELECT * FROM t PARTITION(p0, p1);
时间筛选格式
$fromTime 2021-01-07 00:32:00 $toTime 2021-01-06 15:32:00
封装函数
/*获取查询分区名称 *$pre,分区前缀; * $fromTime,起始时间; * $toTime,结束时间,默认当前时间; * */ function getPart($pre, $fromTime, $toTime) { //防止结束时间为空; if ($toTime == "") { $toTime = date("Y-m-d"); } //转为标准时间格式2021-01-06; $t1 = date("Y-m-d", strtotime($fromTime)); $t2 = date("Y-m-d", strtotime($toTime)); //判断两个日期之差; $diff = (strtotime($t2) - strtotime($t1)) / 86400; //返回拼接分区名称; if ($diff == "0") { return $pre . date('Ymd', strtotime($fromTime)); } if ($diff == "1") { $p1 = $pre . date('Ymd', strtotime($fromTime)); $p2 = $pre . date('Ymd', strtotime($toTime)); return $p1 . "," . $p2; } if ($diff == "2") { $p1 = $pre . date('Ymd', strtotime($fromTime)); $p2 = $pre . date('Ymd', strtotime("+1 day", strtotime($fromTime))); $p3 = $pre . date('Ymd', strtotime($toTime)); return $p1 . "," . $p2 . "," . $p3; } } echo(getPart('jk_data', '2021-01-03 11:00:00', '2021-01-05 14:05:04')); //jk_data20210103,jk_data20210104,jk_data20210105
Done!