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