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
描述:
- 创建表statistics
- 创建一个存储过程
- 定义一个变量 i 类型为INT 默认值设置为0
- 查询connection_histories中ip小于100的数据并赋值到i
- 插入一条记录到表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需要执行一下操作, 否则会报错
- 把SQL语句用CONCAT拼接起来,赋值给@SQL,@SQL是系统内置的函数。
- 预执行SQL
- 执行SQL
- 释放预执行SQL
SET @SQL = CONCAT('ALTER TABLE connection_histories DROP PARTITION ', ch_min_partition_name);
PREPARE s1 FROM @SQL;
EXECUTE s1;
DEALLOCATE PREPARE s1;