MySQL 分区 根据字段值进行自动分区 mysql 分区函数

MySql的分区、事件、函数

分区

创建分区

示例 1：创建一个九个分区的表

CREATE TABLE `connection_histories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ip` int(10) unsigned NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime DEFAULT NULL,
  `recv_data` int(10) unsigned NOT NULL DEFAULT '0',
  `send_data` int(10) unsigned NOT NULL DEFAULT '0',
  `exit_flag` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_connection_histories__exit_flag` (`exit_flag`)
) ENGINE=InnoDB AUTO_INCREMENT=1611249400 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (id)
(PARTITION P0359 VALUES LESS THAN (1590000000) ENGINE = InnoDB,
 PARTITION P0360 VALUES LESS THAN (1600000000) ENGINE = InnoDB,
 PARTITION P0361 VALUES LESS THAN (1610000000) ENGINE = InnoDB,
 PARTITION P0362 VALUES LESS THAN (1620000000) ENGINE = InnoDB,
 PARTITION P0363 VALUES LESS THAN (1630000000) ENGINE = InnoDB,
 PARTITION P0364 VALUES LESS THAN (1640000000) ENGINE = InnoDB,
 PARTITION P0365 VALUES LESS THAN (1650000000) ENGINE = InnoDB,
 PARTITION P0366 VALUES LESS THAN (1660000000) ENGINE = InnoDB,
 PARTITION P0367 VALUES LESS THAN (1670000000) ENGINE = InnoDB) */;

示例 2：新增分区

ALTER TABLE connection_histories ADD PARTITION (PARTITION P0200 VALUES LESS THAN (200000000));

ALTER TABLE 表名 ADD PARTITION (PARTITION PARTITION_NAME值 VALUES LESS THAN ( PARTITION_DESCRIPTION值 ));

示例3： 删除分区

ALTER TABLE connection_histories DROP PARTITION p0090;

ALTER TABLE 表名 DROP PARTITION PARTITION_NAME值;

示例4： 查询分区

SELECT *
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'connection_histories' 
AND TABLE_SCHEMA = 'zixue';

SELECT *
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 表名
AND TABLE_SCHEMA = 数据库名

遇到问题

查询分区时如果不定义数据库名，会查出同服务下的所有数据库中同名表的分区。

分区详细请参考此链接分区详情请参考此链接

事件

示例 1：创建一个事件每隔五秒向表中插入一条数据

CREATE EVENT IF NOT EXISTS sj
ON SCHEDULE EVERY 5 SECOND
ON COMPLETION PRESERVE
COMMENT '每隔五秒像表中新增一条数据'
DO INSERT INTO connection_histories
	(ip, start_time, exit_flag)
	VALUES
	(ROUND(RAND() * 10000), NOW(), ROUND(RAND() * 10))

参数说明：
IF NOT EXISTS： 可选填，判断 sj(事件名) 是否存在
ON SCHEDULE EVERY 5 SECOND : 每隔五秒执行一次
ON COMPLETION PRESERVE： 用于定义事件是否循环执行，默认为执行一次， 即 NOT PRESERVE
COMMENT： 描述
DO后是要执行的SQL

示例 2：每隔五秒执行一次gc（gc存储过程在函数示例中）

CREATE EVENT IF NOT EXISTS sjgc
ON SCHEDULE EVERY 5 SECOND
ON COMPLETION PRESERVE ENABLE
DO CALL gc();

描述：创建一个名叫sjgc的事件每隔五秒执行一次名叫gc的存储过程一次，执行存储过程需要添加关键字ENABLE和CALL

示例3： 删除事件

DROP EVENT IF EXISTS sj;

描述：删除名为 sj 的事件

常用的时间间隔设置

间隔设置	描述
ON SCHEDULE EVERY 5 SECOND	每隔5秒钟执行
ON SCHEDULE EVERY 1 MINUTE	每隔1分钟执行
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)	每天凌晨1点执行
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY),INTERVAL 1 MONTH),INTERVAL 1 HOUR)	每个月的第一天凌晨1点执行
ON SCHEDULE EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + 1 WEEK	每 3 个月，从现在起一周后开始
ON SCHEDULE EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK	每十二个小时，从现在起三十分钟后开始，并于现在起四个星期后结束

定时器状态（事件）

查看定时器是否开启：off是关闭on是开启

SELECT @@event_scheduler;

开启定时器

SET GLOBAL event_scheduler = 1;

问题： 因为mysql定时器是默认关闭的 所以重启mysql服务会导致定时器关闭
解决：在mysql配置文件my.ini的[mysqld]部分加上event_scheduler=ON 就行了

函数

函数分为俩种： 函数和存储过程

示例 1： 创建函数方法

CREATE FUNCTION `ride`(i INT(10), j INT(10)) RETURNS int(10)
BEGIN
DECLARE o INT(10);
SET o = i * j;
SELECT i * j INTO o;
RETURN o;
END

描述： 创建一个名为ride的函数方法，定义入参为俩个类型是INT的值，相乘之后返回

示例 2 ： 创建存储过程

CREATE TABLE `statistics` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `range_num` varchar(255) DEFAULT NULL COMMENT '范围',
  `num` int(11) DEFAULT NULL COMMENT '数量',
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4077 DEFAULT CHARSET=utf8;

CREATE PROCEDURE `gc`()
BEGIN
	DECLARE i INT DEFAULT 0;
	SELECT COUNT(0) INTO i FROM connection_histories WHERE ip < 100;
	INSERT INTO statistics(range_num, num, create_date) VALUES('小于100', i , NOW());
END

描述：

1. 创建表statistics
2. 创建一个存储过程
3. 定义一个变量 i 类型为INT 默认值设置为0
4. 查询connection_histories中ip小于100的数据并赋值到i
5. 插入一条记录到表statistics

综合示例

每天凌晨一点， 把connection_histories表的第一个分区删除， 并创建一个新分区， 用来定时删除历史数据

创建存储过程

CREATE PROCEDURE `del_history_data`()
BEGIN
	DECLARE ch_min_partition_name, ch_max_partition_name VARCHAR(64);
	DECLARE ch_data_free_num INT DEFAULT 0;
	DECLARE ch_max_partition_num LONGTEXT;
	SELECT COUNT(0) INTO ch_data_free_num FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'connection_histories' AND DATA_FREE = 0 AND TABLE_SCHEMA = 'zixue';
	IF ch_data_free_num < 3 THEN
		SELECT MIN(PARTITION_NAME), IF(LENGTH(SUBSTRING(MAX(PARTITION_NAME), 2) + 1) < 4, CONCAT('P0',SUBSTRING(MAX(PARTITION_NAME), 2) + 1), CONCAT('P',SUBSTRING(MAX(PARTITION_NAME), 2) + 1)), (MAX(PARTITION_DESCRIPTION) + 10000000) INTO ch_min_partition_name, ch_max_partition_name, ch_max_partition_num FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'connection_histories' AND TABLE_SCHEMA = 'zixue';
		SET @SQL = CONCAT('ALTER TABLE connection_histories DROP PARTITION ', ch_min_partition_name);
		PREPARE s1 FROM @SQL;
		EXECUTE s1;
		DEALLOCATE PREPARE s1;
		SET @SQL = CONCAT('ALTER TABLE connection_histories ADD PARTITION (PARTITION ', ch_max_partition_name, ' VALUES LESS THAN (', ch_max_partition_num, ' ))');
		PREPARE s2 FROM @SQL;
		EXECUTE s2;
		DEALLOCATE PREPARE s2;
	END IF;
END

创建事件

CREATE EVENT `day_del_history_data` 
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
ON COMPLETION PRESERVE ENABLE 
COMMENT '每天凌晨一点执行存储过程更新历史数据' 
DO CALL del_history_data()

总结

存储过程中

定义变量： DECLARE 变量名, 变量名… 变量类型 DEFAULT 默认值;
赋值变量： SET 变量名 = 值, 变量名 = 值…
使用变量：SELECT 列名， 列名 INTO 变量名 FROM 表名

分支条件

IF 条件 THEN
内容
ELSEIF 条件 THEN
内容
END IF

CASE 条件
WHEN 值
THEN 内容
WHEN 值
THEN 内容
ELSE 内容
END

循环

CREATE PROCEDURE `del`()
BEGIN
	DECLARE num INT DEFAULT 0;
	WHILE num < 10 DO
		SELECT num;
		SET num = num + 1;
	END WHILE;
END

CREATE PROCEDURE `del`()
BEGIN
	DECLARE num INT DEFAULT 0;
	WHILE num < 10 DO
		SELECT num;
		SET num = num + 1;
	END WHILE;
END

ITERATE 为结束本次循环， LEAVE 结束循环

CREATE PROCEDURE `del`()
BEGIN
	DECLARE i INT DEFAULT 0;
	loop1: LOOP
		SET i = i + 1;
		IF i < 3 THEN
			ITERATE loop1;
		END IF;
		IF i > 5 THEN
			LEAVE loop1;
		END IF;
		SELECT i;
	END LOOP loop1;
END

使用动态SQL

在存储过程中使用动态SQL需要执行一下操作， 否则会报错

1. 把SQL语句用CONCAT拼接起来，赋值给@SQL,@SQL是系统内置的函数。
2. 预执行SQL
3. 执行SQL
4. 释放预执行SQL

SET @SQL = CONCAT('ALTER TABLE connection_histories DROP PARTITION ', ch_min_partition_name);
PREPARE s1 FROM @SQL;
EXECUTE s1;
DEALLOCATE PREPARE s1;