MySQL 関連の検証をしたり，データベース未経験者に SQL を教えたりするときに，よく MySQL 公式の「world データベース」を使っている．「国と都市と言語」を対象にしたデータセットとなり，とても使いやすいと思う．例えば「Redash ハンズオン
」でも使っている．
Example Databases
MySQL 公式のデータセットは他にもある．個人的に簡単に試すなら worldを使って，ある程度の規模を必要とするなら employee と sakila を使っている．
- employee data（従業員データ）
- world database（国データ）
- world_x database（国データ）
- sakila database（DVD レンタルショップデータ）
- menagerie database（ペットデータ）
dev.mysql.com
world と world_x の差は？
今まで「world_x データベース」を使ったことがなく，そもそも world と world_x の差を把握できていなかった．今回は world_x を調べた結果をまとめる．なお，MySQL 8.0.19 を検証環境にした．
結論から整理すると，データサイズに差はなく，スキーマに差がある．具体的には world_x データベースでは一部のデータが「JSON 型」で，Document Store として使えるようになっている．よって，MySQL の「X DevAPI」を試せる環境となり，データベース名も world_x になっていると推測できる．やはり，ハンズオンなど簡単に使うなら今後も world で良さそう．なお，テーブル情報の概要は以下に載っている．
テーブル
まず，world と world_x のテーブルを確認する．world は「計3テーブル」となり，world_x テーブルは「計4テーブル」となる．とは言え，どちらも「国」と「都市」と「言語」のデータを持っている．
mysql> SHOW TABLES FROM world;
+
| Tables_in_world |
+
| city |
| country |
| countrylanguage |
+
3 rows in set (0.01 sec)
mysql> SHOW TABLES FROM world_x;
+
| Tables_in_world_x |
+
| city |
| country |
| countryinfo |
| countrylanguage |
+
4 rows in set (0.01 sec)
レコード
次に，world と world_x のレコード数を確認する．既に書いた通り，データサイズに差はなかった．
- world データベース
city : 4079 レコードcountry : 239 レコードcountrylanguage : 984 レコード
- world_x データベース
city : 4079 レコードcountry : 239 レコードcountryinfo : 239 レコードcountrylanguage : 984 レコード
SQL も残しておく．
SELECT COUNT(*) FROM world.city;
SELECT COUNT(*) FROM world.country;
SELECT COUNT(*) FROM world.countrylanguage;
SELECT COUNT(*) FROM world_x.city;
SELECT COUNT(*) FROM world_x.country;
SELECT COUNT(*) FROM world_x.countryinfo;
SELECT COUNT(*) FROM world_x.countrylanguage;
スキーマ
SHOW COLUMNS を使って world と world_x のスキーマを確認する．
world は一般的なスキーマとなり，city.CountryCode と countrylanguage.CountryCode が country.Code の外部キーになっている．個人的にも使い慣れたスキーマと言える．
world スキーマ
mysql> SHOW COLUMNS FROM world.city;
+
| Field | Type | Null | Key | Default | Extra |
+
| ID | int | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int | NO | | 0 | |
+
5 rows in set (0.01 sec)
mysql> SHOW COLUMNS FROM world.country;
+
| Field | Type | Null | Key | Default | Extra |
+
| Code | char(3) | NO | PRI | | |
| Name | char(52) | NO | | | |
| Continent | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO | | Asia | |
| Region | char(26) | NO | | | |
| SurfaceArea | decimal(10,2) | NO | | 0.00 | |
| IndepYear | smallint | YES | | NULL | |
| Population | int | NO | | 0 | |
| LifeExpectancy | decimal(3,1) | YES | | NULL | |
| GNP | decimal(10,2) | YES | | NULL | |
| GNPOld | decimal(10,2) | YES | | NULL | |
| LocalName | char(45) | NO | | | |
| GovernmentForm | char(45) | NO | | | |
| HeadOfState | char(60) | YES | | NULL | |
| Capital | int | YES | | NULL | |
| Code2 | char(2) | NO | | | |
+
15 rows in set (0.01 sec)
mysql> SHOW COLUMNS FROM world.countrylanguage;
+
| Field | Type | Null | Key | Default | Extra |
+
| CountryCode | char(3) | NO | PRI | | |
| Language | char(30) | NO | PRI | | |
| IsOfficial | enum('T','F') | NO | | F | |
| Percentage | decimal(4,1) | NO | | 0.0 | |
+
4 rows in set (0.01 sec)
MySQL Workbench で生成した ER も載せておく．
world_x スキーマ
world_x は city.Info や countryinfo.doc など，「JSON 型」のカラムを持ったテーブルがある．MySQL を Document Store として使う検証環境として最適だと思う．
mysql> SHOW COLUMNS FROM world_x.city;
+
| Field | Type | Null | Key | Default | Extra |
+
| ID | int | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | | | |
| District | char(20) | NO | | | |
| Info | json | YES | | NULL | |
+
5 rows in set (0.01 sec)
mysql> SHOW COLUMNS FROM world_x.country;
+
| Field | Type | Null | Key | Default | Extra |
+
| Code | char(3) | NO | PRI | | |
| Name | char(52) | NO | | | |
| Capital | int | YES | | NULL | |
| Code2 | char(2) | NO | | | |
+
4 rows in set (0.01 sec)
mysql> SHOW COLUMNS FROM world_x.countryinfo;
+
| Field | Type | Null | Key | Default | Extra |
+
| doc | json | YES | | NULL | |
| _id | varbinary(32) | NO | PRI | NULL | STORED GENERATED |
| _json_schema | json | YES | | NULL | VIRTUAL GENERATED |
+
3 rows in set (0.01 sec)
mysql> SHOW COLUMNS FROM world_x.countrylanguage;
+
| Field | Type | Null | Key | Default | Extra |
+
| CountryCode | char(3) | NO | PRI | | |
| Language | char(30) | NO | PRI | | |
| IsOfficial | enum('T','F') | NO | | F | |
| Percentage | decimal(4,1) | NO | | 0.0 | |
+
4 rows in set (0.01 sec)
MySQL Workbench で生成した ER も載せておく．テーブル定義を確認すると world_x.city には外部キーの指定がないこともわかる．
なお，「JSON 型」 だとデータ構造を確認しにくいため，実際のデータを JSON_PRETTY() で載せておく．city.Info はシンプルに Population（人口） のみとなる．countryinfo.doc は Name（国名） や government（政府情報） や geography（地理情報） など，様々な情報が JSON に含まれている．
mysql> SELECT JSON_PRETTY(Info) FROM world_x.city WHERE CountryCode = 'JPN' AND NAME = 'Tokyo';
+
| JSON_PRETTY(Info) |
+
| {
"Population": 7980230
} |
+
1 row in set (0.01 sec)
mysql> SELECT JSON_PRETTY(doc) FROM world_x.countryinfo WHERE JSON_EXTRACT(doc, '$.Code') = 'JPN' LIMIT 1;
+
| JSON_PRETTY(doc) |
+
| {
"GNP": 3787042,
"_id": "00005de917d8000000000000006d",
"Code": "JPN",
"Name": "Japan",
"IndepYear": -660,
"geography": {
"Region": "Eastern Asia",
"Continent": "Asia",
"SurfaceArea": 377829
},
"government": {
"HeadOfState": "Akihito",
"GovernmentForm": "Constitutional Monarchy"
},
"demographics": {
"Population": 126714000,
"LifeExpectancy": 80.69999694824219
}
} |
+
1 row in set (0.01 sec)
world_x クエリ : 東京都の Population（人口） を取得する
最後にクエリサンプルを載せておく．
まず，city テーブルから東京都の Population（人口） を取得する．今回は -> オペレータを使う．
mysql> SELECT Info->'$.Population' FROM world_x.city WHERE CountryCode = 'JPN' AND Name = 'Tokyo';
+
| Info->'$.Population' |
+
| 7980230 |
+
1 row in set (0.01 sec)
world_x クエリ : 日本の Region（地域） と Population（人口） を取得する
今度は日本の Region（地域） と Population（人口） を取得する．world_x の場合はテーブルが分割されているため，country テーブルと countryinfo テーブルを JOIN する必要がある．今回は -> オペレータと ->> オペレータを使う．通常の char と JSON の中にある文字列を ON で紐付けた．
mysql> SELECT ci.doc->>'$.Code', ci.doc->'$.demographics.Population', c.Capital FROM world_x.country c INNER JOIN world_x.countryinfo ci ON c.Code = ci.doc->>'$.Code' WHERE c.Name = 'Japan';
+
| JSON_UNQUOTE(JSON_EXTRACT(c2.doc, '$.Code')) | JSON_EXTRACT(c2.doc, '$.demographics.Population') | Capital |
+
| JPN | 126714000 | 1532 |
+
1 row in set (0.01 sec)
なお，JSON_EXTRACT() と -> や JSON_UNQUOTE(JSON_EXTRACT()) と ->> の話は昨日の記事にまとめてある．
kakakakakku.hatenablog.com
まとめ
MySQL 公式のデータセット world と world_x の差を把握するために検証環境を構築して調べた．今後も SQL を教えたりする簡単な場面では world を使おうと思う．もし「JSON 型」などを検証する必要があったら world_x を使おうと思う．差を把握できて良かった！
