【译】str_to_date Function

原文链接：https://blog.mclaughlinsoftware.com/2022/02/11/str_to_date-function/
原文作者：MacLochlainns

正如许多人所知，我采用了Alan Beaulieu的《Learning SQL》作为我的数据库类的核心参考。本书第7章主要讨论数据生成、操作和转换。

我检查他们是否阅读了这一章并使用了所讨论的函数的最后一个练习问题是:
使用一个或多个时间函数来编写一个查询，将’ 29-FEB-2024 '字符串值转换为默认的MySQL日期格式,结果应该显示:

+--------------------+
| mysql_default_date |
+--------------------+
| 2024-02-29         |
+--------------------+
1 row in set, 1 warning (0.00 sec)

如果您不熟悉MySQL函数的行为，这看起来可能是一个很难解决的问题。如果你倾向于冒险，你可能会尝试STR_TO_DATE函数，但如果你不倾向于冒险，%m说明符的描述可能会建议你没有内置SQL来解决问题。

我用这个问题来教学生如何解决SQL查询中的问题。第一步需要将’ 29-FEB-2024 '基本字符串值放入mystringstrings表中，比如:

DROP TABLE IF EXISTS strings;
CREATE TABLE strings
(mystring  VARCHAR(11));
 
SELECT 'Insert' AS statement;
INSERT INTO strings
(mystring)
VALUES
('29-FEB-2024');

下一步需要创建一个查询:

• 公共表表达式(CTE)中的参数列表
• CASE语句用于筛选select列表中的结果
• 字符串表和参数CTE之间的交叉连接

查询看起来像这样:通过不区分大小写的比较来解析CASE语句中的比较:

SELECT 'Query' AS statement;
WITH params AS
(SELECT 'January' AS full_month
 UNION ALL
 SELECT 'February' AS full_month)
SELECT s.mystring
,      p.full_month
,      CASE
         WHEN SUBSTR(s.mystring,4,3) = SUBSTR(p.full_month,1,3) THEN
           STR_TO_DATE(REPLACE(s.mystring,SUBSTR(s.mystring,4,3),p.full_month),'%d-%M-%Y') 
       END AS converted_date
FROM   strings s CROSS JOIN params p;

结果返回：

+-------------+------------+----------------+
| mystring    | full_month | converted_date |
+-------------+------------+----------------+
| 29-FEB-2024 | January    | NULL           |
| 29-FEB-2024 | February   | 2024-02-29     |
+-------------+------------+----------------+
2 rows in set (0.00 sec)

结果集或驱动表的问题CROSS JOIN，CROSS JOIN将一个表中的每一行与先前连接的另一个表或驱动表中的每一行进行匹配。这意味着你需要在WHERE子句中添加一个过滤器，以确保你只获得字符串和参数之间的匹配，就像修改后的查询:
WITH params AS
(SELECT ‘January’ AS full_month
UNION ALL
SELECT ‘February’ AS full_month)
SELECT s.mystring
, p.full_month
, CASE
WHEN SUBSTR(s.mystring,4,3) = SUBSTR(p.full_month,1,3) THEN
STR_TO_DATE(REPLACE(s.mystring,SUBSTR(s.mystring,4,3),p.full_month),’%d-%M-%Y’)
END AS converted_date
FROM strings s CROSS JOIN params p
WHERE SUBSTR(s.mystring,4,3) = SUBSTR(p.full_month,1,3);

它返回一行，比如:
±------------±-----------±---------------+
| mystring | full_month | converted_date |
±------------±-----------±---------------+
| 29-FEB-2024 | February | 2024-02-29 |
±------------±-----------±---------------+
1 row in set (0.00 sec)

然而，这些都不是必须的，因为查询可以这样写:

SELECT STR_TO_DATE('29-FEB-2024','%d-%M-%Y') AS mysql_date;

它返回：

+------------+
| mysql_date |
+------------+
| 2024-02-29 |
+------------+
1 row in set (0.00 sec)

这是因为带有%M说明符的STR_TO_DATE()函数解析所有包含三个或更多字符的月份。需要三个字符，因为Mar和May, June和July都只能被三个字符限定。如果提供的月份小于3个字符，函数将返回空值。
下面是一个完整的测试用例，让你发现所有的空值，可能出现的两个字符:

/* Conditionally drop the table. */
DROP TABLE IF EXISTS month, param;

/* Create a table. */
CREATE TABLE month
( month_name VARCHAR(9));

/* Insert into the month table. */
INSERT INTO month
( month_name )
VALUES
('January')
,('February')
,('March')
,('April')
,('May')
,('June')
,('July')
,('August')
,('September')
,('October')
,('November')
,('December');

/* Create a table. */
CREATE TABLE param
( month VARCHAR(9)
, needle VARCHAR(9));

/* Conditionally drop the procedure. */
DROP PROCEDURE IF EXISTS read_string;
DROP PROCEDURE IF EXISTS test_month_name;

/* Reset the execution delimiter to create a stored program. */
DELIMITER $$

/* Create a procedure. */
CREATE PROCEDURE read_string(month_name VARCHAR(9))
BEGIN

/* Declare a handler variable. */
DECLARE display VARCHAR(17);
DECLARE evaluate VARCHAR(17);
DECLARE iterator INT DEFAULT 1;
DECLARE partial VARCHAR(9);

/* Read the list of characters. */
character_loop:LOOP

/* Print the character list. */
IF iterator > LENGTH(month_name) THEN
LEAVE character_loop;
END IF;

/* Assign substring of month name. */
SELECT SUBSTR(month_name,1,iterator) INTO partial;
SELECT CONCAT('01-',partial,'-2024') INTO evaluate;

/* Print only the strings too short to identify as the month. */
IF STR_TO_DATE(evaluate,'%d-%M-%Y') IS NULL THEN
INSERT INTO param
( month, needle )
VALUES
( month_name, partial );
END IF;

/* Increment the counter. */
SET iterator = iterator + 1;

END LOOP;
END;
$$

/* Create a procedure. */
CREATE PROCEDURE test_month_name()
BEGIN

/* Declare a handler variable. */
DECLARE month_name VARCHAR(9);

/* Declare a handler variable. */
DECLARE fetched INT DEFAULT 0;

/* Cursors must come after variables and before event handlers. */
DECLARE month_cursor CURSOR FOR
SELECT m.month_name
FROM month m;

/* Declare a not found record handler to close a cursor loop. */
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;

/* Open cursor and start simple loop. */
OPEN month_cursor;
cursor_loop:LOOP

/* Fetch a record from the cursor. */
FETCH month_cursor
INTO month_name;

/* Place the catch handler for no more rows found
immediately after the fetch operations. */
IF fetched = 1 THEN
/* Fetch the partial strings that fail to find a month. */
SELECT * FROM param;

/* Leave the loop. */
LEAVE cursor_loop;
END IF;

/* Call the subfunction because stored procedures do not
support nested loops. */
CALL read_string(month_name);
END LOOP;
END;
$$

/* Reset the delimter. */
DELIMITER ;

CALL test_month_name();

它返回无法解析英语月份的字符片段列表:

+---------+--------+
| month   | needle |
+---------+--------+
| January | J      |
| March   | M      |
| March   | Ma     |
| April   | A      |
| May     | M      |
| May     | Ma     |
| June    | J      |
| June    | Ju     |
| July    | J      |
| July    | Ju     |
| August  | A      |
+---------+--------+
11 rows in set (0.02 sec)

这里有两个过程，因为MySQL不支持嵌套循环，而是使用单通路解析器。因此，第一个read_string过程是内部循环，第二个test_month_name过程是外部循环
我在这篇文章后面写了一篇，因为有一个读者问我关于没有清理测试用例的问题。在另一篇文章中，你会发现一个drop_table过程，它可以让你动态删除为存储内部循环过程的结果而创建的参数表。