SELECTしたものとAUTO_INCREMENTの値をINSERTしたい

Question

都道府県・市町村のデータをmysqlのデータベースに登録する事を考えています。
SQL INSERT文を用いて、都道府県・市町村データを手打ちする以外の方法を探しています。

現在の都道府県・市町村データの取り方は、
https://teratail.com/questions/16583
のKosuke_Shibuyaさんの回答を参考しました。
ここに書かれている様に、mst_addressテーブル、mst_address_miniテーブルに取り込みました。

やりたいことは、データを利用するソフトの設計上、
そこからさらに都道府県のみのテーブル/市町村のみのテーブルに分割したいのです。
officialCodeで管理するのではなく、AUTO_INCREMENTなプライマリキーで管理したいのです。

都道府県データは、officialCode順でソートされた都道府県名を、AUTO_INCREMENTなプライマリキーで登録したいのです。
市町村データは、officialCode順でソートされた市町村名を、AUTO_INCREMENTなプライマリキーで登録したいのです。

後もう一歩な感じがするのですが、どなたか教えて頂けますでしょうか。

・都道府県リスト、市町村リスト

CREATE TABLE PrefectureList(    PrefectureID smallint(3) unsigned NOT NULL AUTO_INCREMENT,
                                PrefectureName varchar(32) NOT NULL,
                                PRIMARY KEY (PrefectureID)
                           ) ENGINE=InnoDB;
CREATE TABLE CityList(  CityID int(10) unsigned NOT NULL AUTO_INCREMENT,
                        PrefectureID smallint(3) unsigned NOT NULL,
                        CityName varchar(64) NOT NULL,
                        PRIMARY KEY(CityID),
                        FOREIGN KEY(PrefectureID) REFERENCES PrefectureList(PrefectureID)
                     ) ENGINE=InnoDB;

・試してみたこと

INSERT INTO PrefectureList(PrefectureID,PrefectureName) SELECT 0,pref FROM mst_address_mini GROUP BY pref;

prefの値の順序で並んでいる様です。

mysql>INSERT INTO PrefectureList(PrefectureID,PrefectureName) SELECT 0,pref FROM mst_address_mini GROUP BY pref,officialCode ORDER BY officialCode;
Query OK, 1896 rows affected (0.04 sec)
Records: 1896  Duplicates: 0  Warnings: 0

重複している様でした。

Answer 1

こんな感じでしょうか?

INSERT INTO PrefectureList
(
	  PrefectureName
)
SELECT
	  pref
FROM mst_address_mini
GROUP BY
	  pref
ORDER BY
	  MIN(officialCode)
;

INSERT INTO CityList
(
	  PrefectureID
	, CityName
)
SELECT
	  P.PrefectureID
	, M.city
FROM PrefectureList P
JOIN mst_address_mini M
ON P.PrefectureName = M.pref
GROUP BY
	  P.PrefectureID
	, M.city
ORDER BY
	  MIN(M.officialCode)
;

Comments

[mao999]

ありがとうございます！
解決しました。
JOIN、ON、MIN等について調べてみます。

Answer 2

ななめ読みしかしていませんが

SELECT 0,pref FROM mst_address_mini GROUP BY pref,officialCode

しているのでしたら、pref,officialCodeのユニークな数だけ行数ができますが
出力されるのは0,prefなので当然データがかぶる可能性が大
また集計もしていないのにgroup byを使うのはSQL的にはまずいですね

Comments

[mao999]

ご回答有り難うございました。
自分はSQLの基本がまだ分かっていない様で、少し調べる必要がありそうですね。

Answer 3

もしかして2番目のSQLからPrefectureIDと併せて0を除けばよろしいのでは、自動で継続されます。

Comments

[mao999]

ご回答有り難うございました。