地方・都道府県マスタ

2023.05.14

サイトを作るときに必ずと言っていいほど作る都道府県マスタ。
毎回作るのが面倒なので、SQL文を書いておきます。

ご利用上の注意点

IDはゼロ埋めの2ケタにすると、JIS X0401に合うようになってますので、そのままご利用ください。
カラム名、フリガナの有無は環境に合わせてご利用ください。
地方に関しては一般的な八地方区分を使っています。
都道府県のみと地方も利用する2種類を用意していますので、用途によって使い分けてください。

都道府県のみ

CREATE TABLE `prefecture` (
`id` tinyint(3) unsigned NOT NULL,
`name` varchar(255) DEFAULT NULL,
`name_kana` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
);
view raw CreateTable.sql hosted with ❤ by GitHub
INSERT INTO `prefecture` VALUES
(1,'北海道','ホッカイドウ'),
(2,'青森県','アオモリケン'),
(3,'岩手県','イワテケン'),
(4,'宮城県','ミヤギケン'),
(5,'秋田県','アキタケン'),
(6,'山形県','ヤマガタケン'),
(7,'福島県','フクシマケン'),
(8,'茨城県','イバラキケン'),
(9,'栃木県','トチギケン'),
(10,'群馬県','グンマケン'),
(11,'埼玉県','サイタマケン'),
(12,'千葉県','チバケン'),
(13,'東京都','トウキョウト'),
(14,'神奈川県','カナガワケン'),
(15,'新潟県','ニイガタケン'),
(16,'富山県','トヤマケン'),
(17,'石川県','イシカワケン'),
(18,'福井県','フクイケン'),
(19,'山梨県','ヤマナシケン'),
(20,'長野県','ナガノケン'),
(21,'岐阜県','ギフケン'),
(22,'静岡県','シズオカケン'),
(23,'愛知県','アイチケン'),
(24,'三重県','ミエケン'),
(25,'滋賀県','シガケン'),
(26,'京都府','キョウトフ'),
(27,'大阪府','オオサカフ'),
(28,'兵庫県','ヒョウゴケン'),
(29,'奈良県','ナラケン'),
(30,'和歌山県','ワカヤマケン'),
(31,'鳥取県','トットリケン'),
(32,'島根県','シマネケン'),
(33,'岡山県','オカヤマケン'),
(34,'広島県','ヒロシマケン'),
(35,'山口県','ヤマグチケン'),
(36,'徳島県','トクシマケン'),
(37,'香川県','カガワケン'),
(38,'愛媛県','エヒメケン'),
(39,'高知県','コウチケン'),
(40,'福岡県','フクオカケン'),
(41,'佐賀県','サガケン'),
(42,'長崎県','ナガサキケン'),
(43,'熊本県','クマモトケン'),
(44,'大分県','オオイタケン'),
(45,'宮崎県','ミヤザキケン'),
(46,'鹿児島県','カゴシマケン'),
(47,'沖縄県','オキナワケン');
view raw InsertData.sql hosted with ❤ by GitHub

地方+都道府県

CREATE TABLE `region` (
`id` tinyint(3) unsigned NOT NULL,
`name` varchar(255) DEFAULT NULL,
`name_kana` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
);
view raw CreatePrefectureTable.sql hosted with ❤ by GitHub
CREATE TABLE `prefecture` (
`id` tinyint(3) unsigned NOT NULL,
`region_id` tinyint(3) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`name_kana` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
);
view raw CreateRegionTable.sql hosted with ❤ by GitHub
INSERT INTO `prefecture` VALUES
(1,1,'北海道','ホッカイドウ'),
(2,2,'青森県','アオモリケン'),
(3,2,'岩手県','イワテケン'),
(4,2,'宮城県','ミヤギケン'),
(5,2,'秋田県','アキタケン'),
(6,2,'山形県','ヤマガタケン'),
(7,2,'福島県','フクシマケン'),
(8,3,'茨城県','イバラキケン'),
(9,3,'栃木県','トチギケン'),
(10,3,'群馬県','グンマケン'),
(11,3,'埼玉県','サイタマケン'),
(12,3,'千葉県','チバケン'),
(13,3,'東京都','トウキョウト'),
(14,3,'神奈川県','カナガワケン'),
(15,4,'新潟県','ニイガタケン'),
(16,4,'富山県','トヤマケン'),
(17,4,'石川県','イシカワケン'),
(18,4,'福井県','フクイケン'),
(19,4,'山梨県','ヤマナシケン'),
(20,4,'長野県','ナガノケン'),
(21,4,'岐阜県','ギフケン'),
(22,4,'静岡県','シズオカケン'),
(23,4,'愛知県','アイチケン'),
(24,5,'三重県','ミエケン'),
(25,5,'滋賀県','シガケン'),
(26,5,'京都府','キョウトフ'),
(27,5,'大阪府','オオサカフ'),
(28,5,'兵庫県','ヒョウゴケン'),
(29,5,'奈良県','ナラケン'),
(30,5,'和歌山県','ワカヤマケン'),
(31,6,'鳥取県','トットリケン'),
(32,6,'島根県','シマネケン'),
(33,6,'岡山県','オカヤマケン'),
(34,6,'広島県','ヒロシマケン'),
(35,6,'山口県','ヤマグチケン'),
(36,7,'徳島県','トクシマケン'),
(37,7,'香川県','カガワケン'),
(38,7,'愛媛県','エヒメケン'),
(39,7,'高知県','コウチケン'),
(40,8,'福岡県','フクオカケン'),
(41,8,'佐賀県','サガケン'),
(42,8,'長崎県','ナガサキケン'),
(43,8,'熊本県','クマモトケン'),
(44,8,'大分県','オオイタケン'),
(45,8,'宮崎県','ミヤザキケン'),
(46,8,'鹿児島県','カゴシマケン'),
(47,8,'沖縄県','オキナワケン');
view raw InsertPrefectureData.sql hosted with ❤ by GitHub
INSERT INTO `region` VALUES
(1,'北海道地方','ホッカイドウ'),
(2,'東北地方','トウホクチホウ'),
(3,'関東地方','カントウチホウ'),
(4,'中部地方','チュウブチホウ'),
(5,'近畿地方','キンキチホウ'),
(6,'中国地方','チュウゴクチホウ'),
(7,'四国地方','シコクチホウ'),
(8,'九州地方','キュウシュウチホウ');
view raw InsertRegionData.sql hosted with ❤ by GitHub

おまけ：テーブル作成とINSERTをまとめる

都道府県のみ

WITH `prefecture`(id, name, name_kana) AS (
VALUES
(1,'北海道','ホッカイドウ'),
(2,'青森県','アオモリケン'),
(3,'岩手県','イワテケン'),
(4,'宮城県','ミヤギケン'),
(5,'秋田県','アキタケン'),
(6,'山形県','ヤマガタケン'),
(7,'福島県','フクシマケン'),
(8,'茨城県','イバラキケン'),
(9,'栃木県','トチギケン'),
(10,'群馬県','グンマケン'),
(11,'埼玉県','サイタマケン'),
(12,'千葉県','チバケン'),
(13,'東京都','トウキョウト'),
(14,'神奈川県','カナガワケン'),
(15,'新潟県','ニイガタケン'),
(16,'富山県','トヤマケン'),
(17,'石川県','イシカワケン'),
(18,'福井県','フクイケン'),
(19,'山梨県','ヤマナシケン'),
(20,'長野県','ナガノケン'),
(21,'岐阜県','ギフケン'),
(22,'静岡県','シズオカケン'),
(23,'愛知県','アイチケン'),
(24,'三重県','ミエケン'),
(25,'滋賀県','シガケン'),
(26,'京都府','キョウトフ'),
(27,'大阪府','オオサカフ'),
(28,'兵庫県','ヒョウゴケン'),
(29,'奈良県','ナラケン'),
(30,'和歌山県','ワカヤマケン'),
(31,'鳥取県','トットリケン'),
(32,'島根県','シマネケン'),
(33,'岡山県','オカヤマケン'),
(34,'広島県','ヒロシマケン'),
(35,'山口県','ヤマグチケン'),
(36,'徳島県','トクシマケン'),
(37,'香川県','カガワケン'),
(38,'愛媛県','エヒメケン'),
(39,'高知県','コウチケン'),
(40,'福岡県','フクオカケン'),
(41,'佐賀県','サガケン'),
(42,'長崎県','ナガサキケン'),
(43,'熊本県','クマモトケン'),
(44,'大分県','オオイタケン'),
(45,'宮崎県','ミヤザキケン'),
(46,'鹿児島県','カゴシマケン'),
(47,'沖縄県','オキナワケン')
)
)
SELECT
id, name, name_kana
FROM
prefecture
view raw CreatePrefectureAndInsert.sql hosted with ❤ by GitHub

都道府県のみ（BigQuery用）

BigQueryにはVALUES句がありませんので、以下で対応。

WITH `prefecture` AS (
SELECT
*
FROM
UNNEST([
STRUCT(1 AS id,'北海道' AS name,'ホッカイドウ' AS name_kana),
STRUCT(2,'青森県','アオモリケン'),
STRUCT(3,'岩手県','イワテケン'),
STRUCT(4,'宮城県','ミヤギケン'),
STRUCT(5,'秋田県','アキタケン'),
STRUCT(6,'山形県','ヤマガタケン'),
STRUCT(7,'福島県','フクシマケン'),
STRUCT(8,'茨城県','イバラキケン'),
STRUCT(9,'栃木県','トチギケン'),
STRUCT(10,'群馬県','グンマケン'),
STRUCT(11,'埼玉県','サイタマケン'),
STRUCT(12,'千葉県','チバケン'),
STRUCT(13,'東京都','トウキョウト'),
STRUCT(14,'神奈川県','カナガワケン'),
STRUCT(15,'新潟県','ニイガタケン'),
STRUCT(16,'富山県','トヤマケン'),
STRUCT(17,'石川県','イシカワケン'),
STRUCT(18,'福井県','フクイケン'),
STRUCT(19,'山梨県','ヤマナシケン'),
STRUCT(20,'長野県','ナガノケン'),
STRUCT(21,'岐阜県','ギフケン'),
STRUCT(22,'静岡県','シズオカケン'),
STRUCT(23,'愛知県','アイチケン'),
STRUCT(24,'三重県','ミエケン'),
STRUCT(25,'滋賀県','シガケン'),
STRUCT(26,'京都府','キョウトフ'),
STRUCT(27,'大阪府','オオサカフ'),
STRUCT(28,'兵庫県','ヒョウゴケン'),
STRUCT(29,'奈良県','ナラケン'),
STRUCT(30,'和歌山県','ワカヤマケン'),
STRUCT(31,'鳥取県','トットリケン'),
STRUCT(32,'島根県','シマネケン'),
STRUCT(33,'岡山県','オカヤマケン'),
STRUCT(34,'広島県','ヒロシマケン'),
STRUCT(35,'山口県','ヤマグチケン'),
STRUCT(36,'徳島県','トクシマケン'),
STRUCT(37,'香川県','カガワケン'),
STRUCT(38,'愛媛県','エヒメケン'),
STRUCT(39,'高知県','コウチケン'),
STRUCT(40,'福岡県','フクオカケン'),
STRUCT(41,'佐賀県','サガケン'),
STRUCT(42,'長崎県','ナガサキケン'),
STRUCT(43,'熊本県','クマモトケン'),
STRUCT(44,'大分県','オオイタケン'),
STRUCT(45,'宮崎県','ミヤザキケン'),
STRUCT(46,'鹿児島県','カゴシマケン'),
STRUCT(47,'沖縄県','オキナワケン')
])
)
SELECT
id, name, name_kana
FROM
prefecture
view raw CreatePrefectureAndInsertBQ.sql hosted with ❤ by GitHub

参考

全国地方公共団体コード – Wikipedia
日本の地域 – Wikipedia
総務省｜電子自治体｜全国地方公共団体コード

コメント

タイトルとURLをコピーしました