我有一个过程的 DATE 输入参数，例如:IN p_date DATE。我想在过程中验证此输入 DATE 参数格式，该格式应为 YYYY-MM-DD 格式。如果输入参数有字符或日期格式错误，它应该通过使用 SIGNAL 的异常。

请找出下面我写的代码

CREATE PROCEDURE `validation_check`(IN pdate_time DATE)
BEGIN


DECLARE InputValidation CONDITION FOR SQLSTATE '45000';
DECLARE dateValidation CONDITION FOR SQLSTATE '45000';

/*  Doing NULL validation  */
IF pdate_time IS NULL THEN
SIGNAL InputValidation
SET MESSAGE_TEXT='pdate_time should not be empty.';
END IF;

/*  Doing Date format validation
IF STR_TO_DATE(pdate_time,'%Y-%m-%d') != pdate_time THEN
SIGNAL dateValidation
SET MESSAGE_TEXT='Input Date format should be in YYYY-MM-DD.';
END IF;
*/
/*  Doing Date format validation  */
IF pdate_time NOT REGEXP '/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$/' THEN
SIGNAL dateValidation
SET MESSAGE_TEXT='Input Date format should be in YYYY-MM-DD.';
END IF;

SELECT pdate_time;

END

谢谢， 萨加尔

最佳答案

长话短说

正则表达式在这种情况下会失败，因为它们可能只检查日期格式，但如果它是有效日期则不会(例如，'2014-02-30' 具有正确的格式但日期数据无效)

使用字符串函数

概念

解决方案是 - 是的，使用 string functions .然而，正则表达式也很有用——要检查格式是否正确，您仍然需要检查日期本身。由于日期验证是一个单独的问题 - 您应该为它创建一个函数。该函数将接受字符串并将结果作为 bool 值返回 - 因此日期是否正确。这将是可重用的，因此更加灵活。

代码

这里我们使用函数:

CREATE FUNCTION VALIDATE_DATE(d VARCHAR(255))
RETURNS INT
BEGIN
   DECLARE date_year  VARCHAR(255) DEFAULT '';
   DECLARE date_month VARCHAR(255) DEFAULT '';
   DECLARE date_day   VARCHAR(255) DEFAULT '';
   DECLARE ym_delim   INT DEFAULT 0;
   DECLARE md_delim   INT DEFAULT 0;
   -- First, if it's just not xxx-yyy-zzz format:
   SET ym_delim = LOCATE('-', d);
   SET md_delim = LOCATE('-', d, ym_delim+1);
   IF !ym_delim || !md_delim THEN

      RETURN FALSE;
   END IF;
   -- Second, if resulted members are not YYYY, MM or DD:
   SET date_year  = SUBSTR(d, 1, ym_delim-1);
   SET date_month = SUBSTR(d, ym_delim+1, md_delim-ym_delim-1);
   SET date_day   = SUBSTR(d, md_delim+1);
   IF  date_year  NOT REGEXP '^[0-9]{4}$'
    || date_month NOT REGEXP '^[0-9]{2}$'
    || date_day   NOT REGEXP '^[0-9]{2}$' THEN
      RETURN FALSE;
   END IF;
   -- Finally, check if date itself is ok, like 2014-02-30 isn't ok:
   IF DATE(CONCAT(date_year, '-', date_month, '-', date_day)) IS NULL THEN
      RETURN FALSE;
   END IF;
   RETURN TRUE;
END//
DELIMITER ;

如您所见，当日期验证失败时，我们有三种情况:

• 首先，如果没有合适的分隔符(-)。然后就找不到年月日了
• 其次，如果提取的年、月和日部分是错误的(例如，日期是 foo-bar-baz)。这就是为什么我们不能使用日期函数来提取这些部分，所以我们必须使用字符串函数。
• 第三 - 最后，如果我们的日期部分看起来不错，由于组合无效(例如 2014-13-01 有错误的月份)，仍然可能有错误的结果。

似乎是一个解决方案

但是，STR_TO_DATE() 函数可能看起来像解决方案。不幸的是，它将传递不符合相应格式的日期部分(例如 2014-1-1)——因此，它不能用于直接格式验证。这就是为什么我改用单独的存储函数。

将通过什么

所有 YYYY-MM-DD 日期，在 MySQL 方面都是正确的，将被传递。也就是说，较早的日期，例如 '0001-01-01' 是正确的:

mysql> SELECT VALIDATE_DATE('0001-01-01');
+-----------------------------+
| VALIDATE_DATE('0001-01-01') |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set (0.00 sec)

而且，事实上，它们应该是正确的，因为它们对 MySQL 有效。然而，像 001-01-01 这样的东西不会被传递，尽管事实上这样的字符串对于 MySQL 日期也是正确的:

mysql> SELECT VALIDATE_DATE('001-01-01'), DATE('001-01-01');
+----------------------------+-------------------+
| VALIDATE_DATE('001-01-01') | DATE('001-01-01') |
+----------------------------+-------------------+
|                          0 | 0001-01-01        |
+----------------------------+-------------------+
1 row in set (0.00 sec)

这源自您的格式期望 - 您应该过滤所有不具有 YYYY-MM-DD 格式的内容，因此，您将有这样的结果。

关于mysql - 如何在 MySQL 过程中验证 YYYY-MM-DD 格式的输入日期，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/24526782/