如何使phpMyAdmin从csv正确导入datetime? [英] how to make phpMyAdmin import datetime correctly from 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数据库中,这些列定义为datetime,默认值为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_column
using STR_TO_DATE
.
UPDATE tablename SET real_column = STR_TO_DATE(fake_column, '%m/%d/%Y');
这篇关于如何使phpMyAdmin从csv正确导入datetime?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!