kakakakakku blog

Weekly Tech Blog: Keep on Learning!

MySQL のサンプルデータセット "world" データベース と "world_x" データベースの差とは?

MySQL 関連の検証をしたり,データベース未経験者に SQL を教えたりするときに,よく MySQL 公式の「world データベース」を使っている.「国と都市と言語」を対象にしたデータセットとなり,とても使いやすいと思う.例えばRedash ハンズオンでも使っている.

Example Databases

MySQL 公式のデータセットは他にもある.個人的に簡単に試すなら worldを使って,ある程度の規模を必要とするなら employeesakila を使っている.

  • employee data(従業員データ)
  • world database(国データ)
  • world_x database(国データ)
  • sakila database(DVD レンタルショップデータ)
  • menagerie database(ペットデータ)

dev.mysql.com

world と world_x の差は?

今まで「world_x データベース」を使ったことがなく,そもそも worldworld_x の差を把握できていなかった.今回は world_x を調べた結果をまとめる.なお,MySQL 8.0.19 を検証環境にした.

結論から整理すると,データサイズに差はなく,スキーマに差がある.具体的には world_x データベースでは一部のデータが「JSON 型」で,Document Store として使えるようになっている.よって,MySQL の「X DevAPI」を試せる環境となり,データベース名も world_x になっていると推測できる.やはり,ハンズオンなど簡単に使うなら今後も world で良さそう.なお,テーブル情報の概要は以下に載っている.

テーブル

まず,worldworld_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)

レコード

次に,worldworld_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 を使って worldworld_x のスキーマを確認する.

world は一般的なスキーマとなり,city.CountryCodecountrylanguage.CountryCodecountry.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 も載せておく.

f:id:kakku22:20200217102133p:plain

world_x スキーマ

world_xcity.Infocountryinfo.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 には外部キーの指定がないこともわかる.

f:id:kakku22:20200217102237p:plain

なお,「JSON 型」 だとデータ構造を確認しにくいため,実際のデータを JSON_PRETTY() で載せておく.city.Info はシンプルに Population(人口) のみとなる.countryinfo.docName(国名)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 公式のデータセット worldworld_x の差を把握するために検証環境を構築して調べた.今後も SQL を教えたりする簡単な場面では world を使おうと思う.もし「JSON 型」などを検証する必要があったら world_x を使おうと思う.差を把握できて良かった!