Mysql数据库，将存储在文本中的日期(dd/mm/yy)转换为日期时间的问题

Question

我收到了一个包含客户数据的 Mysql 表，该表的结构非常糟糕，几乎所有字段都是文本。为了防止丢失数据，我创建了另一个表，我试图以正确和有用的方式导入列。将 Full_name 拆分为名称和姓氏列效果很好，但是当我尝试将包含 dd/mm/yy 日期的字段 created_time 转换为 DATETIME 时，它显示错误的数据，例如。 10/10/18 至 2010-10-18。

我已求助于创建另一个字段 created_time_old，将文本数据复制到那里，然后按照其他建议的答案通过 STR_TO_DATE 将其转换。但是，我只能将字符串放在那里，当我放整列时，它只会给我:

1411 - 日期时间值不正确:函数 str_to_date 的“”。

我假设有另一种方法/函数可以做到这一点，但我对 SQL 不是很有经验。此外，如果您对我的代码有任何其他建议，请发布:)

我的代码如下

TRUNCATE TABLE Facetel_bazaPGS;
INSERT INTO Facetel_bazaPGS (id,created_time_old,campaign_name,email,Imię,Nazwisko,`phone_number`,platform,Time_added)

SELECT `id`,created_time,`campaign_name`,`email`,substring_index(`full_name`, ' ',1 ),substring(`full_name` from INSTR(`full_name`, ' ') + 1),`phone_number`,`platform`,Time_added

FROM `Facetel_bazaPGS_input`;
UPDATE Facetel_bazaPGS

SET platform = Replace(REPLACE(platform, 'fb', 'Facebook') , 'ig', 'Instagram');

UPDATE Facetel_bazaPGS
SET created_time = STR_TO_DATE(`created_time_old`, '%d/%m/%y');

编辑#1:添加示例数据(由于 GDPR 无法提供真实数据)

+------------------+--------------+---------------+--------------------+--------------+---------------------+----------+---------------------+
|        id        | created_time | campaign_name |       email        |  full_name   |    phone_number     | platform |     Time_added      |
+------------------+--------------+---------------+--------------------+--------------+---------------------+----------+---------------------+
| 1010334092505681 | 10/10/18     | leady         | samplemail@mail.eu | Name         | your_typical_number | ig       | 2018-10-11 08:29:45 |
| 1010457652493325 | 10/10/18     | leady         | samplemail@mail.eu | Name Surname | your_typical_number | ig       | 2018-10-11 08:29:45 |
| 1010470612492029 | 10/10/18     | leady         | samplemail@mail.eu | Name Surname | your_typical_number | fb       | 2018-10-11 08:29:45 |
+------------------+--------------+---------------+--------------------+--------------+---------------------+----------+---------------------+

Answer 1

这个答案是一个推测，但它试图找出您的 1411 错误的根本原因来自对 STR_TO_DATE 的调用。我们可以尝试运行以下查询来检测 Facetel_bazaPGS_input 源表中格式错误的日期字符串:

SELECT *
FROM Facetel_bazaPGS_input
WHERE created_time NOT REGEXP '^[0-9]{2}/[0-9]{2}/[0-9]{2}$';

这将以您期望的格式返回任何不具有created_time 的记录。如果您没有看到空结果集，则可以修复日期字符串。

注意:刚才我可以看到您的错误消息似乎是说某些 created_time 值是空字符串或 NULL。无论哪种情况，上述查询都应清除此类记录。