my-sql编写技巧
1. 日期时间计算
2.存储过程
DELIMITER //
CREATE PROCEDURE `AddMonthlyPartition`()
BEGIN
DECLARE tomorrow DATE;
DECLARE partition_name VARCHAR(20);
-- 计算明天的日期
SET tomorrow = DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH , '%Y-%m');
SET partition_name = CONCAT('p', DATE_FORMAT(tomorrow, '%Y%m'));
-- 构建ALTER TABLE语句来添加分区
SET @sql = CONCAT('ALTER TABLE mqtt_received_message ',
'ADD PARTITION (PARTITION ', partition_name,
' VALUES LESS THAN (TO_DAYS(\'', tomorrow, '\')))');
-- 执行ALTER TABLE语句
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;