以下のような内容のCSV(カンマ区切り、UTF-8)があります。
(カラムの内容はそれぞれ ID,コード,氏名,氏名カナ(半角),ポジション,作成日,更新日,削除日)
(IDが数値型、作成日、更新日、削除日が日付(datetime)型、それ以外は文字列型)
挿入先のテーブルの設定は以下の通りです。
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| code | varchar(4) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| kana_name | varchar(255) | YES | | NULL | |
| position | varchar(100) | YES | | NULL | |
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
| deleted_at | datetime | YES | | NULL | |
+------------+------------------+------+-----+---------+----------------+
テーブルのCREATE文
MySQL
1| member | CREATE TABLE `member` ( 2 `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 3 `code` varchar(4) DEFAULT NULL, 4 `name` varchar(255) DEFAULT NULL, 5 `kana_name` varchar(255) DEFAULT NULL, 6 `position` varchar(100) DEFAULT NULL, 7 `created_at` datetime DEFAULT NULL, 8 `updated_at` datetime DEFAULT NULL, 9 `deleted_at` datetime DEFAULT NULL, 10 PRIMARY KEY (`id`), 11 KEY `id` (`id`,`name`,`created_at`,`updated_at`,`deleted_at`) 12) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 |
このCSVを以下のコマンドでMySQLのテーブルに挿入しようとしたところ、
タイトルにもあるIncorrect datetime value: '' for column 'created_at' at row 1
が発生してしまいました。
MySQL
1mysql> LOAD DATA INFILE '/home/meikun.csv' INTO TABLE dokaben.member FIELDS TERMINATED BY ',' SET created_at = nullif(created_at, null), updated_at = nullif(updated_at, null), deleted_at = nullif(deleted_at, null); 2ERROR 1292 (22007): Incorrect datetime value: '' for column 'created_at' at row 1
日付のカラムが文字列として認識されてしまっている('')のが原因であるとは思うのですが、
これをどのように回避すればよいのか見当がつきません。
(CSVの作り方が悪いのか、MySQL側の設定(my.cnf等)の問題なのか)
created_at
に挿入する値をNULL
や\N
、素直に日付(YYYY-MM-DD hh:mm:ss)に変更してみたりしたのですが、
何れも'NULL'
のように文字列と認識されてしまっているようでした。
一応通常のinsert
の結果も載せておきます。
MySQL
1mysql> INSERT INTO member (id,code,name,kana_name,position,created_at,updated_at,deleted_at) VALUES (4,'0004','山田太郎4','ヤマダタロウ','キャッチャー',null,null,null); 2Query OK, 1 row affected (0.08 sec)
皆様のお知恵をお借りしたく存じます…。
追記
yukihisa様のアドバイスのおかげで無事、解決することができました。
回答内容のSQL文自体では解消できなかったのですが、いただいたコメントにより解消することができたので、
ベストアンサーとさせていただきました。
具体的な手法といたしましては、
CSVファイルの作成日、更新日、削除日の内容を\N
に変更し、
挿入時のSQLのSET
以降をごっそり削った結果、エラーなく挿入することができました。
CSV
1//変更前 24,0004,山田太郎,ヤマダタロウ,キャッチャー,,, 3 4//変更後 54,0004,山田太郎,ヤマダタロウ,キャッチャー,\N,\N,\N
MySQL
1//変更前 2mysql> LOAD DATA INFILE '/home/meikun.csv' INTO TABLE dokaben.member FIELDS TERMINATED BY ',' SET created_at = nullif(created_at, null), updated_at = nullif(updated_at, null), deleted_at = nullif(deleted_at, null); 3 4//変更後 5mysql> LOAD DATA INFILE '/home/meikun.csv' INTO TABLE dokaben.member FIELDS TERMINATED BY ',';
回答2件
あなたの回答
tips
プレビュー