时间戳

首页 > 解决方案 > 如何将控制流添加到 mysql 函数中？

mysql - 如何将控制流添加到 mysql 函数中？

问题描述

我想优化一个从表中读取设置的 mysql 5.7 函数。如果解析的日期在“第 1 学期”或“第 2 学期”，则该函数返回 1 或 2。第 1 和第 2 学期的日期每年都在变化。我们已经确认 dateIn 是一个有效的日期。

功能是：

DELIMITER //
CREATE  function getSemester (dateIN date)
RETURNS INT DETERMINISTIC
BEGIN
    DECLARE sem int;
    select if( dateIN < a.mindate,1,2) into sem
    from (SELECT min(date(value)) mindate FROM `settings` WHERE name = CONCAT(‘sem2_‘,year(dateIN),‘_start’) ) a;
    return sem;
END//
DELIMITER ;

设置定义为：

CREATE TABLE `settings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`value` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

设置数据为：

INSERT INTO `mdl_sap_settings` (`id`, `name`, `value`)
VALUES
    (4, 'sem2_2012_start', '2012/7/16'),
    (15, 'sem2_2013_start', '2013/7/1'),
    (25, 'sem2_2014_start', '2014/6/30'),
    (29, 'sem2_2015_start', '2015/6/29'),
    (37, 'sem2_2016_start', '2016/6/27'),
    (42, 'sem2_2011_start', '2011/7/16'),
    (50, 'sem2_2017_start', '2017/6/26'),
    (56, 'sem2_2018_start', '2018/6/25'),
    (63, 'sem2_2019_start', '2019/6/24');

问题是在 20,000 行上调用该函数时速度很慢。我想通过在函数中进行某种流控制来优化它，例如：

if (year(dateIN) = 2012)
{
   return dateIN < '2012-07-16' ? 1 : 2;
}
if (year(dateIN) = 2013)
{
   return dateIN < '2013-07-01' ? 1 : 2;
}

... etc.

我们需要保持选择原样查询，因为如果不维护代码，我们希望它返回正确的值。

我只是想知道这种控制流在 mysql 函数中是否可能，或者是否有其他方法来优化函数？

标签： mysql

解决方案

我没有 20k 条记录可供测试，但如果我是你，我会像这样重写你的函数，看看是否有帮助。

DELIMITER //
CREATE  function getSemester (dateIN date)
RETURNS INT DETERMINISTIC
BEGIN
    DECLARE sem int;
    DECLARE nameByDate varchar(255);
    SET nameByDate = (SELECT CONCAT('sem2_',year(dateIN),'_start') );
    select if( dateIN < a.mindate,1,2) into sem
    from (SELECT min(date(value)) mindate FROM `test`.`settings` WHERE name = nameByDate ) a;
    return sem;
END//
DELIMITER ;

我会避免在 where 子句中添加 concat，因为大型数据集会影响性能。所以我将它从 where 子句中移出并分配给一个变量一次。如果这没有帮助，您还可以尝试使用 explain 进行查询，看看是否提供任何提示。

EXPLAIN select getSemester('2019/6/24');

祝你好运。

推荐阅读