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.