导入测试数据集


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

  • Employees sample database,一个模拟
  • world sample database
  • Sakila sample database

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

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

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

  1. $ ls -l /tmp/testdb
  2. drwxr-xr-x 2 500 500 100 Aug 1 06:06 sakila-db #<--sakila 测试数据集
  3. -rw------- 1 root root 732290 Aug 1 06:06 sakila-db.tar.gz
  4. drwx------ 4 root root 460 Aug 29 15:33 test_db #<--employees 测试数据集
  5. -rw------- 1 root root 35607473 Aug 29 15:32 test_db-1.0.7.tar.gz
  6. drwxr-xr-x 2 500 500 60 Aug 1 06:06 world-db #<--world 测试数据集
  7. -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. +----------+
  9. greatsql> select count(*) from country;
  10. +----------+
  11. | count(*) |
  12. +----------+
  13. | 239 |
  14. +----------+
  15. greatsql> select count(*) from countrylanguage;
  16. +----------+
  17. | count(*) |
  18. +----------+
  19. | 984 |
  20. +----------+

导入完毕。

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导入测试数据集 - 图1 (opens new window)

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx