MySQL から Excel にテーブルデータをエクスポートする際の日時形式に関する簡単な説明

最近、MySQL を使用してテーブルデータを Excel ファイルにエクスポートしました。MySQL の datetime タイプが Excel (Excel 2016) にエクスポートされ、Excel によって独自の既定の日付形式として認識されました。MySQL の形式は yyyy-mm-dd hh:mm:ss のようなものです。しかし、Excel では yyyy/m/dh:mm になり、見慣れません。もちろん、Excel セル形式を設定することで、カスタム形式 yyyy-mm-dd hh:mm:ss に変更することもできますが、これには余分な手順が追加されます。MySQL で表示されるスタイルで、MySQL から Excel に直接エクスポートできますか?もちろん。

最初は、MySQL のフィールドが datetime 型なので、Excel にエクスポートした後、Excel が自動的に日付型に変換するので、MySQL の date_format 関数を使用して datetime を文字列に変換でき、問題はないだろうと考えました。結果は同じで、Excel もこの標準の日付文字列形式を日付形式として認識すると推測したため、エクスポートステートメントに文字列の日付を追加して、推測が正しいことを確認しました。そこで、このデフォルトの日付形式を破って、見た目を同じにすることを考えました。date_format を使用して文字列に変換するときに、先頭にスペースを追加したところ、問題は完全に解決しました。

テストテーブルとテストデータ

テーブル「users」を作成します（
 `username` varchar(255) NOT NULL,
 `create_time` 日時 NOT NULL
)ENGINE=InnoDB デフォルト文字セット=utf8;
 
# テストデータを挿入 INSERT INTO `users`(`username`, `create_time`) VALUES 
 ('李思', '2018-10-11 15:54:23'),
 ('張三'、'2018-10-12 15:54:14');

Excel ステートメントをエクスポートします。DATE_FORMAT 関数の形式の前にスペースが追加されていることに注意してください (テーブル形式は utf8 であるため、Excel を開いた後にコードが文字化けしないようにするには、形式を gbk に変換する必要があります)

選択
 ユーザー名、
 DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s') 
ユーザーから 
 出力ファイル '/tmp/user_info.xls' へ 
 文字セット gbk;

これで完了です。エクスポートされた Excel では日付が日付形式として認識されなくなり、テキスト形式になるため、希望するスタイルで日付を表示できます。

補足知識: Excel ファイルを Navicat Premium にインポートすると日付が 0000-00-00 になる問題の解決方法

質問

シナリオによっては、ローカルファイルを Navicat にインポートする必要があります。今日遭遇した問題は、Excel ファイルを正常にインポートした後、Excel では元々正しかった日付フィールドが Navicate では「0000-00-00 00:00:00」になってしまったことです。これは本当に信じられません。

分析する

観察してみると、Excel では日付フィールドが完全に表示されないようだということがわかりました。例えば、本来は 2018/10/1 0:01:42 だったのですが、Excel では 01:42.0 と表示されていました。そこで、Excel で列のセルの書式を変更して Navicate に再インポートしてみたところ、問題は解決しました。詳細な解決策については以下を参照してください。