mysql - 来自 mysql 函数的 Srtange 结果

Question

我创建了一个返回值的函数，该值是在周期表中选择的，如下所示:

 CREATE TABLE `periods` (
  `period` DATE NOT NULL,
  `threshold` DOUBLE NOT NULL,
   PRIMARY KEY (`period`)
 )
 COLLATE='latin1_swedish_ci'
 ENGINE=InnoDB;

+------------------------+
|        periods         |
+------------+-----------+
|   period   | threshold |
+------------+-----------+
| 2013-11-01 |     5     |
+------------+-----------+
| 2013-12-01 |     1     |
+------------+-----------+
| 2014-01-01 |     5     |
+------------+-----------+
| 2014-02-01 |     5     |
+------------+-----------+

和函数创建:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `GET_THRESHOLD`(`PERIOD` VARCHAR(10))
 RETURNS double
 LANGUAGE SQL
 NOT DETERMINISTIC
 READS SQL DATA
 SQL SECURITY DEFINER
 COMMENT 'RETURN THRESHOLD'
 BEGIN
   DECLARE RESULT DOUBLE;
   SELECT threshold INTO RESULT FROM periods WHERE period = PERIOD LIMIT 1;
   RETURN RESULT;
 END
 $$
 DELIMITER ;

但是函数返回了一个错误的值

mysql>SELECT GET_THRESHOLD('2013-12-01')
-> 5

有人可以帮助我吗？

Answer 1

您的参数与列同名。那是不行的。试试这个:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `GET_THRESHOLD`(`V_PERIOD` VARCHAR(10))
 RETURNS double
 LANGUAGE SQL
 NOT DETERMINISTIC
 READS SQL DATA
 SQL SECURITY DEFINER
 COMMENT 'RETURN THRESHOLD'
 BEGIN
   DECLARE RESULT DOUBLE;
   SELECT threshold INTO RESULT FROM periods WHERE period = V_PERIOD LIMIT 1;
   RETURN RESULT;
 END
 $$
 DELIMITER 

声明:

WHERE period = PERIOD

正在将列值与其自身进行比较。因此，它选择具有非 NULL 列值的所有行。不是很有趣。

最好始终为变量和参数添加前缀以将它们与表中的列区分开来。