如何使 phpMyAdmin 从 csv 正确导入日期时间?
我收到了一个 csv 文件，其中包含客户数据库表的导出.其中两列是日期，在文件中它们的格式为 mm/dd/yyyy.
I've been provided a csv file which contains an export of a client's database table. Two of the columns are dates, and in the file they're formatted as mm/dd/yyyy.
ID | ActivateDate
-----------------
1 | 05/22/2010
2 | 10/01/2010
我需要将它们导入的 mySQL 数据库将这些列定义为日期时间，默认值为 null.当我在phpMyAdmin中使用导入功能时，它会将导入记录中的所有日期列设置为0000-00-00 00:00:00，而不管导入文件中是否有任何值.
Our mySQL database that I need to import them into has those columns defined as datetime, with a default value of null. When I use the import function in phpMyAdmin, it's setting all the date columns in the imported records to 0000-00-00 00:00:00, regardless of whether there's any value in the import file.
谁能告诉我需要做什么才能将数据库中的 ActivateDate 列设置为 2010-05-22 00:00:00 而不是 0000-00-00 00:00:00?
Can anyone tell me what I need to do to get the ActivateDate column in the database to be set to 2010-05-22 00:00:00 instead of 0000-00-00 00:00:00?
推荐答案
如果可能，我会先将这些值导入到 varchar 列 fake_column 中，然后将它们推送到真正的列 real_column 使用 STR_TO_DATE.
If at all possible, I'd import those values into a varchar column fake_column first, and then push them over into the real column real_columnusing STR_TO_DATE.
UPDATE tablename SET real_column = STR_TO_DATE(fake_column, '%m/%d/%Y');
参考构建格式字符串
相关文章