MySQL的STR_TO_DATE函数无法正常工作

Question

我有一张表，其中有一个名为birthday的varchar列。

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `birthday` varchar(30) COLLATE utf16_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf16 COLLATE=utf16_unicode_ci AUTO_INCREMENT=5 ;

INSERT INTO `test` (`id`, `birthday`) 
VALUES (1, '20041225'),
       (2, '2004-12-25'),
       (3, '19941225'),
       (4, '19941201');

我尝试运行这个查询：

SELECT str_to_date(birthday,"%Y%m%d") 
FROM `test` 
WHERE 1

但它总是返回带有空值的行。

如果我运行以下查询：

SELECT str_to_date('20141225',"%Y%m%d") 
FROM `test` 
WHERE 1

它将返回2014-12-25

那么我的查询有什么问题吗？

- Thinh Phan

Answer 1

留给更简单的函数。 DATE() 返回字符串的日期部分，格式为YYYY-MM-DD：

SELECT DATE(birthday) FROM `test`

结果：

2004-12-25      
2004-12-25      
1994-12-25      
1994-12-01      

---

你的代码无法正常工作的原因是STR_TO_DATE()需要相同的输入和输出格式，例如STR_TO_DATE('2014-08-29', '%Y-%m-%d')。请参考文档中的示例。该函数主要用于将日期或时间从一种格式转换为另一种格式，其中原始格式来自MySQL之外，你想将数据导入MySQL的日期格式，例如在这种情况下，你将知道原始日期格式。

示例：

SELECT STR_TO_DATE('20041225', '%Y-%m-$d');   -- null - formats don't match
SELECT STR_TO_DATE('2004-12-25', '%Y-%m-%d'); -- 2004-12-25 - formats match

- scrowler

Answer 2

您创建表时遇到了排序规则问题。如果使用utf8_unicode_ci，则没有任何问题。请参考此示例。关于排序规则的更多信息，请阅读此帖子。

- Sadikhasan

---

对于这个问题，+1，但是对于不符合格式的值（例如第2行中的YYYY-MM-DD），它仍然返回null。如果您根本没有指定COLLATE，它也可以正常工作。 - scrowler

@scrowler 你说得对，如果你没有指定 COLLATE，那么它会采用默认的 utf8_unicode_ci，这就是为什么它能够工作的原因。 - Sadikhasan

这仍然不是解决此问题的正确方法，因为输入数据格式的不一致意味着选择了错误的函数。 - scrowler