MySQL 関連の検証をしたり，データベース未経験者に SQL を教えたりするときに，よく MySQL 公式の「world データベース」を使っている．「国と都市と言語」を対象にしたデータセットとなり，とても使いやすいと思う．例えば「Redash ハンズオン
Example Databases
MySQL 公式のデータセットは他にもある．個人的に簡単に試すなら worldを使って，ある程度の規模を必要とするなら employee と sakila を使っている．
- employee data（従業員データ）
- world database（国データ）
- world_x database（国データ）
- sakila database（DVD レンタルショップデータ）
- menagerie database（ペットデータ）
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 で良さそう．なお，テーブル情報の概要は以下に載っている．
- MySQL :: Setting Up the world Database :: 2 Installation
- MySQL :: Setting Up the world_x Database :: 2 Installation
テーブル
まず，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 も残しておく．
-- world SELECT COUNT(*) FROM world.city; SELECT COUNT(*) FROM world.country; SELECT COUNT(*) FROM world.countrylanguage; -- world_x 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()) と ->> の話は昨日の記事にまとめてある．
まとめ
MySQL 公式のデータセット world と world_x の差を把握するために検証環境を構築して調べた．今後も SQL を教えたりする簡単な場面では world を使おうと思う．もし「JSON 型」などを検証する必要があったら world_x を使おうと思う．差を把握できて良かった！
