导入测试数据集

导入测试数据集

安装完GreatSQL数据库后，如果是用于教学或测试场景，可以导入几个通用的测试数据集，主要有：

Employees sample database，一个模拟
world sample database
Sakila sample database

1. 下载测试数据集压缩包

上述三个测试数据集下载地址分别是：

<https://github.com/datacharmer/test_db>
<https://downloads.mysql.com/docs/world-db.tar.gz>
<https://downloads.mysql.com/docs/sakila-db.tar.gz>

分别下载到本地，并解压缩到 /tmp/testdb 目录下：

1. $ ls -l /tmp/testdb

3. drwxr-xr-x 2  500  500      100 Aug  1 06:06 sakila-db  #<--sakila 测试数据集
4. -rw------- 1 root root   732290 Aug  1 06:06 sakila-db.tar.gz
5. drwx------ 4 root root      460 Aug 29 15:33 test_db    #<--employees 测试数据集
6. -rw------- 1 root root 35607473 Aug 29 15:32 test_db-1.0.7.tar.gz
7. drwxr-xr-x 2  500  500       60 Aug  1 06:06 world-db   #<--world 测试数据集
8. -rw------- 1 root root    92916 Aug  1 06:06 world-db.tar.gz

2. 导入测试数据集

2.1 导入employees数据集

employees测试数据文件包中提供了导入方法说明，基本上照着做就行：

1. $ cd /tmp/testdb/test_db
2. $ cat README.md
3. ...

开始导入（在这里，采用具有最高权限的root账户导入，所以略过创建测试账户这个环节，下同）：

1. $ mysql -f < employees.sql
2. INFO
3. CREATING DATABASE STRUCTURE
4. INFO
5. storage engine: InnoDB
6. INFO
7. LOADING departments
8. INFO
9. LOADING employees
10. INFO
11. LOADING dept_emp
12. INFO
13. LOADING dept_manager
14. INFO
15. LOADING titles
16. INFO
17. LOADING salaries
18. data_load_time_diff
19. 00:02:28

导入完成后，还可以执行校验程序，确认导入的结果无误：

1. greatsql> source /tmp/testdb/test_db/test_employees_md5.sql;
2. +----------------------+
3. | INFO                 |
4. +----------------------+
5. | TESTING INSTALLATION |
6. +----------------------+
7. ...
8. +--------------+------------------+----------------------------------+
9. | table_name   | expected_records | expected_crc                     |
10. +--------------+------------------+----------------------------------+
11. | departments  |                9 | d1af5e170d2d1591d776d5638d71fc5f |
12. | dept_emp     |           331603 | ccf6fe516f990bdaa49713fc478701b7 |
13. | dept_manager |               24 | 8720e2f0853ac9096b689c14664f847e |
14. | employees    |           300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
15. | salaries     |          2844047 | fd220654e95aea1b169624ffe3fca934 |
16. | titles       |           443308 | bfa016c472df68e70a03facafa1bc0a8 |
17. +--------------+------------------+----------------------------------+
18. ...
19. +--------------+------------------+----------------------------------+
20. | table_name   | found_records    | found_crc                        |
21. +--------------+------------------+----------------------------------+
22. | departments  |                9 | d1af5e170d2d1591d776d5638d71fc5f |
23. | dept_emp     |           331603 | ccf6fe516f990bdaa49713fc478701b7 |
24. | dept_manager |               24 | 8720e2f0853ac9096b689c14664f847e |
25. | employees    |           300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
26. | salaries     |          2844047 | fd220654e95aea1b169624ffe3fca934 |
27. | titles       |           443308 | bfa016c472df68e70a03facafa1bc0a8 |
28. +--------------+------------------+----------------------------------+
29. ...
30. +--------------+---------------+-----------+
31. | table_name   | records_match | crc_match |
32. +--------------+---------------+-----------+
33. | departments  | OK            | ok        |
34. | dept_emp     | OK            | ok        |
35. | dept_manager | OK            | ok        |
36. | employees    | OK            | ok        |
37. | salaries     | OK            | ok        |
38. | titles       | OK            | ok        |
39. +--------------+---------------+-----------+
40. ..
41. +------------------+
42. | computation_time |
43. +------------------+
44. | 00:02:15         |
45. +------------------+
46. ...
47. +---------+--------+
48. | summary | result |
49. +---------+--------+
50. | CRC     | OK     |
51. | count   | OK     |
52. +---------+--------+

看起来验证无误。

2.2 导入world数据集

执行下面的命令导入world数据集：

1. greatsql> source /tmp/testdb/world-db/world.sql;
2. ...
3. greatsql> select count(*) from city;
4. +----------+
5. | count(*) |
6. +----------+
7. |     4079 |
8. +----------+

10. greatsql> select count(*) from country;
11. +----------+
12. | count(*) |
13. +----------+
14. |      239 |
15. +----------+

17. greatsql> select count(*) from countrylanguage;
18. +----------+
19. | count(*) |
20. +----------+
21. |      984 |
22. +----------+

导入完毕。

2.3 导入Sakila数据集

执行下面的命令初始化Sakila测试数据库：

1. greatsql> source /tmp/testdb/sakila-db/sakila-schema.sql;
2. greatsql> source /tmp/testdb/sakila-db/sakila-data.sql;
3. greatsql> select TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_ROWS
4. from information_schema.tables where table_schema = 'sakila';
5. +--------------+----------------------------+------------+--------+------------+
6. | TABLE_SCHEMA | TABLE_NAME                 | TABLE_TYPE | ENGINE | TABLE_ROWS |
7. +--------------+----------------------------+------------+--------+------------+
8. | sakila       | actor                      | BASE TABLE | InnoDB |        200 |
9. | sakila       | actor_info                 | VIEW       | NULL   |       NULL |
10. | sakila       | address                    | BASE TABLE | InnoDB |        603 |
11. | sakila       | category                   | BASE TABLE | InnoDB |         16 |
12. | sakila       | city                       | BASE TABLE | InnoDB |        600 |
13. | sakila       | country                    | BASE TABLE | InnoDB |        109 |
14. | sakila       | customer                   | BASE TABLE | InnoDB |        599 |
15. | sakila       | customer_list              | VIEW       | NULL   |       NULL |
16. | sakila       | film                       | BASE TABLE | InnoDB |       1000 |
17. | sakila       | film_actor                 | BASE TABLE | InnoDB |       5462 |
18. | sakila       | film_category              | BASE TABLE | InnoDB |       1000 |
19. | sakila       | film_list                  | VIEW       | NULL   |       NULL |
20. | sakila       | film_text                  | BASE TABLE | InnoDB |       1000 |
21. | sakila       | inventory                  | BASE TABLE | InnoDB |       4581 |
22. | sakila       | language                   | BASE TABLE | InnoDB |          6 |
23. | sakila       | nicer_but_slower_film_list | VIEW       | NULL   |       NULL |
24. | sakila       | payment                    | BASE TABLE | InnoDB |      16500 |
25. | sakila       | rental                     | BASE TABLE | InnoDB |      16010 |
26. | sakila       | sales_by_film_category     | VIEW       | NULL   |       NULL |
27. | sakila       | sales_by_store             | VIEW       | NULL   |       NULL |
28. | sakila       | staff                      | BASE TABLE | InnoDB |          2 |
29. | sakila       | staff_list                 | VIEW       | NULL   |       NULL |
30. | sakila       | store                      | BASE TABLE | InnoDB |          2 |
31. +--------------+----------------------------+------------+--------+------------+

导入完毕。

接下来就可以运行查询和测试了。

关于测试数据库的更多详情参考 Other MySQL Documentation (opens new window)。

问题反馈

问题反馈 gitee (opens new window)

联系我们

扫码关注微信公众号