mysql函数

创建使用分区表

创建分区表

动态地给分区时间

分区一般按时间分区，这里就以时间分区为例：

库名：liwinal_dev

表名：liwinal_temp

USE liwinal_dev;
​
-- 计算日期值
SET @date_3_days_ago = CONVERT(CURDATE() - INTERVAL 3 DAY USING utf8);
SET @date_2_days_ago = CONVERT(CURDATE() - INTERVAL 2 DAY USING utf8);
SET @date_1_day_ago = CONVERT(CURDATE() - INTERVAL 1 DAY USING utf8);
SET @date_today = CONVERT(CURDATE() USING utf8);
​
-- 创建表
SET @sql = CONCAT(
    'CREATE TABLE `liwinal_temp` (',
    '`mt_id` varchar(128) NOT NULL COMMENT ''id'',',
    '`mt_open_id` varchar(100) DEFAULT NULL COMMENT ''openid'',',
    '`mt_prize_id` varchar(128) DEFAULT NULL COMMENT ''奖ID'',',
    '`create_time` datetime NOT NULL COMMENT ''创建时间'',',
    '`update_time` datetime DEFAULT NULL COMMENT ''修改时间'',',
    'PRIMARY KEY (`mt_id`, `create_time`)',
    ') ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''临时表''',
    'PARTITION BY RANGE (TO_DAYS(create_time)) (',
    'PARTITION p0 VALUES LESS THAN (TO_DAYS(''', @date_3_days_ago, ''')),',
    'PARTITION p1 VALUES LESS THAN (TO_DAYS(''', @date_2_days_ago, ''')),',
    'PARTITION p2 VALUES LESS THAN (TO_DAYS(''', @date_1_day_ago, ''')),',
    'PARTITION p3 VALUES LESS THAN (TO_DAYS(''', @date_today, '''))',
    ')'
);
​
-- 先删除表（如果有）
SET @drop_sql = 'DROP TABLE IF EXISTS `liwinal_temp`';
PREPARE stmt FROM @drop_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
​
-- 创建新表
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

事件是否开启

-- 查看事件调度器状态
SHOW VARIABLES LIKE 'event_scheduler';
​
-- 开启事件调度器
SET GLOBAL event_scheduler = ON;
-- 关闭事件调度器
SET GLOBAL event_scheduler = OFF;

用事件定时更新分区

新增新的分区p4，删除旧分区p0数据，然后重新排序

USE liwinal_dev;
​
DELIMITER //
-- 删除事件
DROP EVENT IF EXISTS update_partitions;
​
-- 创建事件
CREATE EVENT IF NOT EXISTS update_partitions
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP(CURDATE() + INTERVAL 1 DAY, '02:00:00')
DO
BEGIN
​
    -- 定义变量,需要放在开始事务之前，因为MySQL 的事务管理机制要求在事务开始之前，所有的变量定义都已经完成，事务开始后，你不能定义新的用户变量。
    SET @date_1_day_ago = CURDATE() + INTERVAL 1 DAY;
    
    -- 开始事务
    START TRANSACTION;
​
    ALTER TABLE `liwinal_temp` DROP PARTITION p0;
​
    -- 动态生成 SQL 语句
    SET @sql = CONCAT(
        'ALTER TABLE `liwinal_temp` ADD PARTITION (',
        'PARTITION p4 VALUES LESS THAN (TO_DAYS(''', @date_1_day_ago, '''))',
        ');'
    );
​
    -- 执行动态 SQL
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
​
​
    -- 查询分区 p1 的 VALUES LESS THAN 时间
    SELECT PARTITION_DESCRIPTION INTO @p1_less_than
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_NAME = 'liwinal_temp'
      AND TABLE_SCHEMA = DATABASE()
      AND PARTITION_NAME = 'p1';
​
    -- 查询分区 p2 的 VALUES LESS THAN 时间
    SELECT PARTITION_DESCRIPTION INTO @p2_less_than
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_NAME = 'liwinal_temp'
      AND TABLE_SCHEMA = DATABASE()
      AND PARTITION_NAME = 'p2';
​
    -- 查询分区 p3 的 VALUES LESS THAN 时间
    SELECT PARTITION_DESCRIPTION INTO @p3_less_than
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_NAME = 'liwinal_temp'
      AND TABLE_SCHEMA = DATABASE()
      AND PARTITION_NAME = 'p3';
​
    -- 动态生成重新编号分区的 SQL 语句
    SET @sql_reorganize_p1 = CONCAT(
        'ALTER TABLE `liwinal_temp` REORGANIZE PARTITION p1 INTO (',
        'PARTITION p0 VALUES LESS THAN (', @p1_less_than, ')',
        ');'
    );
    SET @sql_reorganize_p2 = CONCAT(
        'ALTER TABLE `liwinal_temp` REORGANIZE PARTITION p2 INTO (',
        'PARTITION p1 VALUES LESS THAN (', @p2_less_than, ')',
        ');'
    );
    SET @sql_reorganize_p3 = CONCAT(
        'ALTER TABLE `liwinal_temp` REORGANIZE PARTITION p3 INTO (',
        'PARTITION p2 VALUES LESS THAN (', @p3_less_than, ')',
        ');'
    );
​
    SET @sql_reorganize_p4 = CONCAT(
        'ALTER TABLE `liwinal_temp` REORGANIZE PARTITION p4 INTO (',
        'PARTITION p3 VALUES LESS THAN (TO_DAYS(''', @date_1_day_ago, '''))',
        ');'
    );
​
    -- 执行动态 SQL 重新编号分区
    PREPARE stmt FROM @sql_reorganize_p1;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
​
    PREPARE stmt FROM @sql_reorganize_p2;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
​
    PREPARE stmt FROM @sql_reorganize_p3;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
​
    PREPARE stmt FROM @sql_reorganize_p4;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
​
    -- 提交事务
    COMMIT;
END //
​
DELIMITER ;

 

查询分区

SELECT 
    TABLE_NAME, 
    PARTITION_NAME, 
    PARTITION_ORDINAL_POSITION, 
    PARTITION_METHOD, 
    PARTITION_EXPRESSION, 
    PARTITION_DESCRIPTION,
    TABLE_SCHEMA
FROM 
    INFORMATION_SCHEMA.PARTITIONS 
WHERE 
    TABLE_NAME = 'liwinal_temp' 
    AND TABLE_SCHEMA = 'liwinal_dev';

只查某个分区的数据

1. 使用 PARTITION 关键字

   SELECT * FROM liwinal_temp PARTITION (p2);

2. 使用 EXPLAIN PARTITIONS

   在执行查询之前，可以使用 EXPLAIN PARTITIONS 来查看 MySQL 是否会自动选择特定的分区。这有助于优化查询，确保 MySQL 只扫描相关的分区。
   EXPLAIN PARTITIONS SELECT * FROM liwinal_temp WHERE create_time >= '2025-08-03' AND create_time < '2025-08-04';

    

3. 优化查询条件

   确保查询条件能够匹配分区的范围，这样 MySQL 可以自动选择相关的分区。例如，如果你的分区是按日期范围划分的，查询条件应该包含日期范围。
   ​
   SELECT * FROM liwinal_temp WHERE create_time >= '2025-08-03' AND create_time < '2025-08-04';

    

查看事务

USE liwinal_dev;
SHOW EVENTS;

 

过程函数

1. 有一些需要动态创建的sql可以利用过程函数完成

2. 可以重复执行的sql也可以用过程函数

语法

删除表的过程函数

DROP PROCEDURE IF EXISTS 过程函数名;

创建表的过程函数

注意过程函数名后要加括号

DELIMITER //
CREATE PROCEDURE 过程函数名()
BEGIN
    过程函数要执行的sql
END //
DELIMITER ;

使用过程函数

注意过程函数名后要加括号

CALL 过程函数名();

利用过程函数批量新增数据

少量数据

USE liwinal_dev
-- 删除表的过程函数
DROP PROCEDURE IF EXISTS GenerateData;
​
-- 创建表的过程函数
DELIMITER //
CREATE PROCEDURE GenerateData()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 100 DO
        INSERT INTO `liwinal_temp` 
        (`lt_id`, `lt_open_id`, `lt_prize_id`, `create_time`, `update_time`) 
        VALUES (
            CONCAT('User', i), 
            CONCAT('oMnE95GXA', i), 
            'jp1000000206', 
            '2025-08-01 15:12:23', 
            '2025-08-01 15:12:23'
        );
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;
​
-- 执行表的过程函数
CALL GenerateData();

大量数据

USE liwinal_dev;
-- 删除表的过程函数
DROP PROCEDURE IF EXISTS GenerateData;
​
-- 创建表的过程函数
DELIMITER //
CREATE PROCEDURE GenerateData()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE batch_size INT DEFAULT 100;  -- 每次插入 100 条数据
    DECLARE values_list TEXT DEFAULT '';
​
    WHILE i <= 400000 DO
        -- 这里双引号和\'任选一个编写都行
        SET values_list = CONCAT(values_list, '("F', i, '", \'oMnE95GXA\', \'jp1000000206\', \'2025-08-03 11:12:23\', \'2025-08-03 11:12:23\'),');
        SET i = i + 1;
​
        -- 每当积累到 batch_size 条数据时，执行一次批量插入
        IF i % batch_size = 0 OR i > 400000 THEN
            SET values_list = SUBSTRING(values_list, 1, LENGTH(values_list) - 1);  -- 去掉最后一个逗号
            SET @sql = CONCAT('INSERT INTO liwinal_temp (lt_id, lt_open_id, lt_prize_id, create_time, update_time) VALUES ', values_list);
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            SET values_list = '';  -- 重置 values_list
        END IF;
    END WHILE;
END //
DELIMITER ;
​
-- 执行表的过程函数
CALL GenerateData();