最近用mysql導(dǎo)出表數(shù)據(jù)到excel文件，mysql中的datetime類型導(dǎo)出到excel（excel2016）中被excel識別成它自己默認(rèn)的日期格式了，在mysql中的格式形如 yyyy-mm-dd hh:mm:ss，到了excel變成了 yyyy/m/d h:mm，看起來不太習(xí)慣，當(dāng)然可以通過設(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)為它的日期類型，所以可以通過mysql的date_format函數(shù)把datetime轉(zhuǎn)為字符串，以為沒有問題，結(jié)果還是一樣的，猜想是對于這種標(biāo)準(zhǔn)日期的字符串的格式excel也會(huì)識別成日期格式，于是在導(dǎo)出語句中加上一個(gè)字符串的日期，驗(yàn)證了我的猜想。所以就尋思打破這種默認(rèn)的日期格式但使得它看上去又沒什么差別，所以在用date_format轉(zhuǎn)為字符串時(shí)，在前面加上一個(gè)空格，這樣就完美解決了。

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

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

導(dǎo)出excel語句，注意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ì)在把日期識別成日期格式，而變成了文本格式，這樣就能展示自己想要的風(fēng)格的日期了

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

問題

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

分析

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

解決方法

1.導(dǎo)入前，在excel中修改單元格格式，設(shè)置為自定義，類型為yyyy/m/d h:mm:ss，并保存文件。

2.重新將文件導(dǎo)入到Navicate中即可。

以上這篇淺談mysql導(dǎo)出表數(shù)據(jù)到excel關(guān)于datetime的格式問題就是小編分享給大家的全部內(nèi)容了，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評論