获取上个月的Mysql程序 [英] Mysql Procedure for get previous month

问题描述

我有一个存储过程，理论上应该返回上个月，但它返回无效.

I have a Stored Procedure that in theory should return pervious Month, but it returns void.

DROP PROCEDURE  `open_month` ;

CREATE DEFINER =  `root`@`localhost` PROCEDURE  `open_month` ( IN  `data` VARCHAR( 7 ) ) NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY INVOKER 
BEGIN 
DECLARE DATAv VARCHAR( 10 ) DEFAULT "";

SET DATAv = CONCAT( data,  '-01' ) ;

SELECT * 
FROM incom
WHERE MONTH( DATA ) = MONTH( DATAv - INTERVAL 1 
MONTH ) ;

END

我快 24 小时没睡了，无法猜测问题所在.但我可以建议在 phpmyadmin 的查询监视器中，如果您将 DATAv 设置为用户 var (@)，则该查询有效.提前致谢！

I'm near 24h awake and can't guess the problem. but I can advise that in query monitor of phpmyadmin, that query works if you set DATAv as user var (@). thanks ahead!

推荐答案

选项 1:当参数名与列名不匹配时.

Option 1: When parameter name does not match with a column name.

您的 data 参数似乎采用 Y-m 格式，数据类型为 varchar.

Your data parameter seems to be in the format Y-m and data type is varchar.

所以你不能得到有效的输出，只能得到一个 NULL，像这样调用

So you can't get valid output but a NULL, on calling like

month( data )

您需要将data转换成有效的date格式并找到它的month.

You need to convert data to valid date format and find its month.

示例:

SELECT * FROM incom
 WHERE MONTH( str_to_date( DATA, '%Y-%m' ) ) = MONTH( DATAv - INTERVAL 1 MONTH ) ;

选项 2:当参数名称与列名称匹配时:

Option 2: When parameter name matches with a column name:

如果您的表有一个名为 data 的字段，那么您的过程将无法工作，除非您在 data 列上使用表限定符.这是因为要使用的列名和参数名不明确.观察表明，当出现这种歧义时，将优先考虑参数名称.您最好稍微更改参数的名称并在代码中使用.或者使用表别名来限定列.

If your table has a field with name data, then your procedure is not going to work, unless you use table qualifier on data column. This is because of ambiguity on column and parameter names to use. Observations show that, priority will be given to parameter name, when such ambiguity arises. You better change the name of the parameter a bit and use in the code. Or use table alias name to qualify the column.

修改后的程序:

DROP PROCEDURE  if exists `open_month` ;

delimiter // 
CREATE DEFINER =  `root`@`localhost` 
       PROCEDURE  `open_month` ( IN  `data` VARCHAR( 7 ) ) 
         NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY INVOKER 
BEGIN 
  DECLARE DATAv VARCHAR( 10 ) DEFAULT "";

  SET DATAv = CONCAT( data,  '-01' ) ;

  SELECT * 
    FROM incom i
   WHERE MONTH( i.DATA ) = MONTH( DATAv - INTERVAL 1 MONTH ) ;

END;
//

delimiter ;

有了这些更改，您的程序应该可以正常工作了.

With such changes, your procedure should be working.

这篇关于获取上个月的Mysql程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！