快捷導(dǎo)航

淺談mysql導(dǎo)出表數(shù)據(jù)到excel關(guān)于datetime的格式問(wèn)題

更新時(shí)間：2020年07月14日 10:38:46 作者：Listening_傾聽

這篇文章主要介紹了淺談mysql導(dǎo)出表數(shù)據(jù)到excel關(guān)于datetime的格式問(wèn)題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧

最近用mysql導(dǎo)出表數(shù)據(jù)到excel文件，mysql中的datetime類型導(dǎo)出到excel（excel2016）中被excel識(shí)別成它自己默認(rèn)的日期格式了，在mysql中的格式形如 yyyy-mm-dd hh:mm:ss，到了excel變成了 yyyy/m/d h:mm，看起來(lái)不太習(xí)慣，當(dāng)然可以通過(guò)設(shè)置excel單元格格式改成自定義格式 yyyy-mm-dd hh:mm:ss，但是這樣多了一個(gè)步驟，能不能直接從mysql導(dǎo)出到excel的就是mysql顯示的樣式呢？當(dāng)然可以。

開始猜想是由于mysql中該字段是datetime類型的，導(dǎo)出到excel后excel自動(dòng)轉(zhuǎn)為它的日期類型，所以可以通過(guò)mysql的date_format函數(shù)把datetime轉(zhuǎn)為字符串，以為沒有問(wèn)題，結(jié)果還是一樣的，猜想是對(duì)于這種標(biāo)準(zhǔn)日期的字符串的格式excel也會(huì)識(shí)別成日期格式，于是在導(dǎo)出語(yǔ)句中加上一個(gè)字符串的日期，驗(yàn)證了我的猜想。所以就尋思打破這種默認(rèn)的日期格式但使得它看上去又沒什么差別，所以在用date_format轉(zhuǎn)為字符串時(shí)，在前面加上一個(gè)空格，這樣就完美解決了。

測(cè)試用表及測(cè)試數(shù)據(jù)

CREATE TABLE `users` (
 `username` varchar(255) NOT NULL,
 `create_time` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
# 插入測(cè)試數(shù)據(jù)
INSERT INTO `users`(`username`, `create_time`) VALUES 
 ('李四', '2018-10-11 15:54:23'),
 ('張三', '2018-10-12 15:54:14');

導(dǎo)出excel語(yǔ)句，注意DATE_FORMAT函數(shù)里面的格式前面加了個(gè)空格（由于表的格式是utf8的，要保證excel打開后不亂碼，需要把格式轉(zhuǎn)換為gbk）

SELECT
 username,
 DATE_FORMAT( create_time, ' %Y-%m-%d %H:%i:%s' ) 
FROM users 
 INTO OUTFILE '/tmp/user_info.xls' 
 CHARACTER SET gbk;

大功告成，這樣導(dǎo)出的excel，不會(huì)在把日期識(shí)別成日期格式，而變成了文本格式，這樣就能展示自己想要的風(fēng)格的日期了

補(bǔ)充知識(shí)：將Excel文件導(dǎo)入到Navicat Premium中日期變?yōu)?000-00-00的解決方案

問(wèn)題

在某些場(chǎng)景下，需要將本地文件導(dǎo)入到Navicat中。筆者今天遇到的問(wèn)題是，將excel文件成功導(dǎo)入后，一個(gè)在excel中原本正確的日期字段在Navicate中卻變成了“0000-00-00 00:00:00”，實(shí)在是匪夷所思。

分析

經(jīng)過(guò)觀察發(fā)現(xiàn)，該日期字段在excel中似乎沒有全部顯示，比如本來(lái)是2018/10/1 0:01:42，excel中顯示為01:42.0，于是嘗試在excel中修改了該列的單元格格式，重新導(dǎo)入Navicate中，問(wèn)題得到解決。詳細(xì)解決方法請(qǐng)見下方。