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 を使おうと思う.差を把握できて良かった!