MySQL:郵便番号情報をDBに格納
※ken_all.zipファイルをダウンロードした後、①utf-8に変換、②半角カタカナを全角カタカナに変換(ここでは秀丸利用)。
※そのままインポートすると文字化けになる。
CREATE TABLE `mt_zipcode` ( `id` int(11) NOT NULL AUTO_INCREMENT, `jiscode` char(5) COLLATE utf8_bin NOT NULL, `zipcode_old` char(5) COLLATE utf8_bin NOT NULL, `zipcode` char(7) COLLATE utf8_bin NOT NULL, `pref_kana` varchar(20) COLLATE utf8_bin NOT NULL, `city_kana` varchar(100) COLLATE utf8_bin NOT NULL, `street_kana` varchar(200) COLLATE utf8_bin NOT NULL, `pref` varchar(20) COLLATE utf8_bin NOT NULL, `city` varchar(100) COLLATE utf8_bin NOT NULL , `street` varchar(200) COLLATE utf8_bin NOT NULL , `flag1` tinyint(1) NOT NULL , `flag2` tinyint(1) NOT NULL , `flag3` tinyint(1) NOT NULL , `flag4` tinyint(1) NOT NULL , `flag5` tinyint(1) NOT NULL , `flag6` tinyint(1) NOT NULL , PRIMARY KEY (`id`), KEY `zipcode` (`zipcode`), KEY `street` (`street`) ) ENGINE=MyISAM AUTO_INCREMENT=123910 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; mysql> LOAD DATA LOCAL INFILE 'C:\\util\\zipcode\\ken_all\\ken_all_utf8_zenkaku.csv' -> INTO TABLE mt_zipcode2 -> FIELDS TERMINATED BY ',' -> OPTIONALLY ENCLOSED BY '"' -> ESCAPED BY '' -> LINES STARTING BY '' -> TERMINATED BY '\r\n' -> (jiscode,zipcode_old,zipcode,pref_kana,city_kana,street_kana,pref,city,street,flag1,flag2,flag3,flag4,flag5,flag6); Query OK, 123909 rows affected, 1 warning (1.17 sec) Records: 123909 Deleted: 0 Skipped: 0 Warnings: 1