Sometimes we need to save a lot of data, which is time-sensitive, such as some logs. After a period of time, the existence of these data is not significant, and in addition to taking up a lot of hard disk space, it may make data queries slow.This data needs to be cleaned up regularly.We can cope with this situation by dividing the tables horizontally (or by dividing them into libraries), but dividing the tables horizontally is more appropriate when you have a large number of queries or need to save data for a long time.In some cases, Mysql partitioned tables are better suited when the query volume of our data is not large and the table data meets the system's needs on a single machine.
Create a partitioned table to create a stored procedure and name it: create_log_partition
BEGIN select replace(partition_name,'device_log_','') into @PName from INFORMATION_SCHEMA. PARTITIONS where table_name = 'device_log' order by partition_ordinal_position desc limit 1; IF isnull(@PName) THEN select min(days) into @minDay from device_log; select max(days) into @maxDays from device_log; SET @partStr=''; WHILE @minDay <= @maxDays DO SET @partStr = CONCAT( @partStr, 'PARTITION device_log_' ,@minDay, ' VALUES in (' ,@minDay, ') ,' ); SET @minDay = @minDay + 1; END WHILE; SET @sql = CONCAT('ALTER TABLE device_log PARTITION by list (days)(',@partStr,'PARTITION device_log_' ,@maxDays+1 ,' VALUES in (' ,@maxDays+1,') )'); PREPARE stmt FROM @sql ; EXECUTE stmt ; DEALLOCATE PREPARE stmt ; ELSE SET @minDay = CAST(@PName AS UNSIGNED INTEGER) + 1; select (DATEDIFF(now(),'1970-1-1') + 1) into @days; WHILE @minDay <= @days DO SET @sql = CONCAT('ALTER TABLE device_log add PARTITION (PARTITION device_log_' ,@minDay,' VALUES in (' ,@minDay,') )'); PREPARE stmt FROM @sql ; EXECUTE stmt ; DEALLOCATE PREPARE stmt ; SET @minDay = @minDay + 1; END WHILE; END IF; END
Create a partitioned table, delete the stored procedure, and name it reduce_log_partition
BEGIN select DATEDIFF(now(),'1970-1-1') into @days; SET @find = 1; WHILE @find > 0 DO select replace(partition_name,'device_log_','') into @minDay from INFORMATION_SCHEMA. PARTITIONS where table_name = 'device_log' order by partition_ordinal_position asc limit 1; IF ISNULL(@minDay) THEN SET @find = 0; ELSE SET @minDay = CAST(@minDay AS UNSIGNED INTEGER); IF @minDay + 15 <= @days THEN SET @sql = CONCAT('alter table device_log drop partition device_log_',@minDay); PREPARE stmt FROM @sql ; EXECUTE stmt ; DEALLOCATE PREPARE stmt ; ELSE SET @find = 0; END IF; END IF; END WHILE; END
Two stored procedures are created here to be called individually at some point in the future.
Create a Mysql event to periodically execute the above two stored procedures: device_log_event
BEGIN DECLARE r_code CHAR(5) DEFAULT '00000'; DECLARE r_msg TEXT; DECLARE v_starttime DATETIME DEFAULT NOW(); BEGIN BEGIN DECLARE CONTINUE HANDLER FOR SQLEXCEPTION GET DIAGNOSTICS CONDITION 1 r_code = RETURNED_SQLSTATE , r_msg = MESSAGE_TEXT; END; call create_log_partition(); call reduce_log_partition(); END; insert into event_log (event_name,start_time,end_time,state,msg)values ('device_log_event',v_starttime,now(),r_code,r_msg); END
Set execution cycle
Here's an event_The log table, which records the execution of events, is defined as follows
CREATE TABLE `event_log` ( `event_log_id` int(11) NOT NULL AUTO_INCREMENT, `event_name` varchar(255) NOT NULL, `start_time` datetime NOT NULL, `end_time` datetime NOT NULL, `state` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '0', `msg` text CHARACTER SET utf8 COLLATE utf8_general_ci, PRIMARY KEY (`event_log_id`) ) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8;
Device_The log is the name of the partitioned table and can be changed based on your table name.