mysql infile 将字符串转换为时间格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14364065/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql infile convert string to time format
提问by Plummer
I have a TSV that I'm trying to infile into a mySQL table. A couple columns are time formats, but they don't match the standard time format of HH:MM:SS, instead they look like HH:MM AM/PM
我有一个 TSV，我正试图将其归档到 mySQL 表中。几列是时间格式，但它们与 的标准时间格式不匹配HH:MM:SS，而是看起来像HH:MM AM/PM
I've seen examples to do this with PHP, but I was hoping there was a way to do with with mysql str_to_date
我已经看到用 PHP 来做这件事的例子，但我希望有一种方法可以用 mysql str_to_date
Here's what I've worked up so far.
这是我到目前为止所做的工作。
LOAD DATA LOCAL INFILE
'C:\SINGLE_PROP\open_houses.txt'
REPLACE INTO TABLE singleprop.jos_openhouse
IGNORE 1 LINES
SET OHSSTARTTM = STR_TO_DATE('%g:%i %a', '%g:%i:%s');
I keep getting an incorrect time format error. Here's how the table looks.
我不断收到不正确的时间格式错误。这是桌子的外观。
CREATE TABLE `jos_openhouse` (
`OHSSTARTDT` DATE NOT NULL,
`OHSHOSTBID` varchar(14) NOT NULL,
`OHSMLSNO` int(7) NOT NULL,
`OHSSTARTTM` TIME NOT NULL,
`OHSENDTM'` TIME NOT NULL,
`OHSREMARK` TEXT,
`OHSTYPE` TEXT,
`OHSUPDTDT` TIMESTAMP,
PRIMARY KEY (`OHSMLSNO`))
ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
Should the column be created as 'TEXT' first so infile can put the data there and then it's converted to time format after it's been imported?
该列是否应该首先创建为“文本”，以便 infile 可以将数据放在那里，然后在导入后将其转换为时间格式？
回答by Saharsh Shah
Try this:
尝试这个：
SELECT STR_TO_DATE('11:15 PM', '%h:%i %p') ;
OUTPUT
输出
TIME
--------
23:15:00
Try this:
尝试这个：
LOAD DATA LOCAL INFILE
'C:\SINGLE_PROP\open_houses.txt'
REPLACE INTO TABLE singleprop.jos_openhouse (col1, col2, ..., @OHSSTARTTM)
IGNORE 1 LINES
SET OHSSTARTTM = STR_TO_DATE(@OHSSTARTTM, '%h:%i %p');
