[sudo] password for kokaki:
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 8.0.40-0ubuntu0.24.04.1 (Ubuntu)
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set persist local_infile=1;
Query OK, 0 rows affected (0.01 sec)
mysql> select @@local_infile;
+----------------+
| @@local_infile |
+----------------+
| 1 |
+----------------+
1 row in set (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)
mysql> quit
Bye
■unzipインストール
DROP TABLE IF EXISTS M_zip_code;
CREATE TABLE M_zip_code (
jis_code varchar(5) NOT NULL,
zip_code_old varchar(5) NOT NULL,
zip_code varchar(7) NOT NULL,
todofuken_kana varchar(100) NOT NULL,
shikuchoson_kana varchar(500) NOT NULL,
choiki_kana varchar(500) NOT NULL,
todofuken varchar(8) NOT NULL,
shikuchoson varchar(200) NOT NULL,
choiki varchar(200) NOT NULL,
tmp1 varchar(1) NOT NULL,
tmp2 varchar(1) NOT NULL,
tmp3 varchar(1) NOT NULL,
tmp4 varchar(1) NOT NULL,
tmp5 varchar(1) NOT NULL,
tmp6 varchar(1) NOT NULL,
PRIMARY KEY (jis_code, zip_code, todofuken)
);
CREATE INDEX M_zip_code_IDX1 ON M_zip_code (jis_code);
CREATE INDEX M_zip_code_IDX2 ON M_zip_code (zip_code);
CREATE INDEX M_zip_code_IDX3 ON M_zip_code (todofuken);
kokaki@skynew:~/zipcode$ mysql -udev -p dev
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 49
Server version: 8.0.40-0ubuntu0.24.04.1 (Ubuntu)
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> source DDL_M_zip_code.sql;
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected (0.06 sec)
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> quit
Bye
■郵便番号データダウンロード
--2025-01-02 11:52:42-- https://www.post.japanpost.jp/zipcode/dl/kogaki/zip/ken_all.zip
Resolving www.post.japanpost.jp (www.post.japanpost.jp)... 43.253.212.25
Connecting to www.post.japanpost.jp (www.post.japanpost.jp)|43.253.212.25|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1693993 (1.6M) [application/zip]
Saving to: ‘ken_all.zip’
ken_all.zip 100%[===================================================>] 1.62M 9.37MB/s in 0.2s
2025-01-02 11:52:42 (9.37 MB/s) - ‘ken_all.zip’ saved [1693993/1693993]
Archive: ken_all.zip
inflating: KEN_ALL.CSV
zip_code_import_script.sh: 59: mail: not found
zip_code_import_script.sh
1.日本郵便のサイトから全国の郵便番号データをダウンロード：ken_all.zip
2.圧縮ファイルを解凍：ken_all.zip -> KEN_ALL.CSV
3.CSVファイルの文字コードをUTF8に変換：KEN_ALL.CSV -> KEN_ALL_UTF8.CSV
4.MySQLデータベースにCSVファイルを取り込む：KEN_ALL_UTF8.CSV -> M_zip_code
■動作確認
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> CREATE TABLE M_zip_code (
jis_code varchar(5) NOT NULL,
zip_code_old varchar(5) NOT NULL,
zip_code varchar(7) NOT NULL,
todofuken_kana varchar(100) NOT NULL,
shikuchoson_kana varchar(500) NOT NULL,
choiki_kana varchar(500) NOT NULL,
todofuken varchar(8) NOT NULL,
shikuchoson varchar(200) NOT NULL,
choiki varchar(200) NOT NULL,
tmp1 varchar(1) NOT NULL,
tmp2 varchar(1) NOT NULL,
tmp3 varchar(1) NOT NULL,
tmp4 varchar(1) NOT NULL,
tmp5 varchar(1) NOT NULL,
tmp6 varchar(1) NOT NULL--,
--PRIMARY KEY (jis_code, zip_code, todofuken)
);
sqlite> CREATE INDEX M_zip_code_IDX1 ON M_zip_code (jis_code);
CREATE INDEX M_zip_code_IDX2 ON M_zip_code (zip_code);
CREATE INDEX M_zip_code_IDX3 ON M_zip_code (todofuken);
sqlite> .schema M_zip_code
CREATE TABLE M_zip_code (
jis_code varchar(5) NOT NULL,
zip_code_old varchar(5) NOT NULL,
zip_code varchar(7) NOT NULL,
todofuken_kana varchar(100) NOT NULL,
shikuchoson_kana varchar(500) NOT NULL,
choiki_kana varchar(500) NOT NULL,
todofuken varchar(8) NOT NULL,
shikuchoson varchar(200) NOT NULL,
choiki varchar(200) NOT NULL,
tmp1 varchar(1) NOT NULL,
tmp2 varchar(1) NOT NULL,
tmp3 varchar(1) NOT NULL,
tmp4 varchar(1) NOT NULL,
tmp5 varchar(1) NOT NULL,
tmp6 varchar(1) NOT NULL
);
CREATE INDEX M_zip_code_IDX1 ON M_zip_code (jis_code);
CREATE INDEX M_zip_code_IDX2 ON M_zip_code (zip_code);
CREATE INDEX M_zip_code_IDX3 ON M_zip_code (todofuken);
sqlite> .mode csv
sqlite> .import KEN_ALL_UTF8.CSV M_zip_code
※PRIMARY KEYに指定したカラムは、UNIQUE制約が適用されるようだ。
※以下の2ファイルをSQLite3向けに修正
BaseDao.php
ZipCodeDao.php
・パラメータ[db]に"SQLite"を指定するとSQLite3版を実行
$db = $_GET["db"];
if ($db = "SQLite") {
require_once("ZipCodeDao_SQLite.php");
} else {
require_once("ZipCodeDao_MySQL.php");
}
2025 kokaki.jp, Office.