从MySQL官网下载测试库employees_db-full-1.0.6.tar.bz2，导入时出现两个错误

错误1：ERROR 1193 (HY000) at line 38: Unknown system variable 'storage_engine'

[root@maomao employees_db]# mysql -t < employees.sql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)[root@maomao employees_db]# mysql -uroot -p123456 -t < employees.sqlmysql: [Warning] Using a password on the command line interface can be insecure.+-----------------------------+| INFO                        |+-----------------------------+| CREATING DATABASE STRUCTURE |+-----------------------------+ERROR 1193 (HY000) at line 38: Unknown system variable 'storage_engine'

查看自己数据库存储引擎

(root@localhost:mysql.sock)[(none)]>show variables like '%engine%';+----------------------------------+--------+| Variable_name                    | Value  |+----------------------------------+--------+| default_storage_engine           | InnoDB || default_tmp_storage_engine       | InnoDB || disabled_storage_engines         |        || internal_tmp_disk_storage_engine | InnoDB |+----------------------------------+--------+4 rows in set (0.00 sec)

打开脚本编辑，将下面代码段中标记为红色的“storage_engine”修改为“default_storage_engine”

DROP DATABASE IF EXISTS employees;CREATE DATABASE IF NOT EXISTS employees;USE employees;SELECT 'CREATING DATABASE STRUCTURE' as 'INFO';DROP TABLE IF EXISTS dept_emp,                     dept_manager,                     titles,                     salaries,                     employees,                     departments;   set storage_engine = InnoDB;-- set storage_engine = MyISAM;-- set storage_engine = Falcon;-- set storage_engine = PBXT;-- set storage_engine = Maria;select CONCAT('storage engine: ', @@storage_engine) as INFO;

再次导入，成功！

[root@maomao employees_db]# mysql -uroot -p123456 -t < employees.sql mysql: [Warning] Using a password on the command line interface can be insecure.+-----------------------------+| INFO                        |+-----------------------------+| CREATING DATABASE STRUCTURE |+-----------------------------++------------------------+| INFO                   |+------------------------+| storage engine: InnoDB |+------------------------++---------------------+| INFO                |+---------------------+| LOADING departments |+---------------------++-------------------+| INFO              |+-------------------+| LOADING employees |+-------------------++------------------+| INFO             |+------------------+| LOADING dept_emp |+------------------++----------------------+| INFO                 |+----------------------+| LOADING dept_manager |+----------------------++----------------+| INFO           |+----------------+| LOADING titles |+----------------++------------------+| INFO             |+------------------+| LOADING salaries |+------------------+

错误2：导入成功后进入数据库查看，发现所有表数据为空

(root@localhost:mysql.sock)[employees]>show tables;+---------------------+| Tables_in_employees |+---------------------+| departments         || dept_emp            || dept_manager        || employees           || salaries            || titles              |+---------------------+6 rows in set (0.00 sec)(root@localhost:mysql.sock)[employees]>select count(*) from departments;+----------+| count(*) |+----------+|        0 |+----------+1 row in set (0.00 sec)(root@localhost:mysql.sock)[employees]>select count(*) from titles;+----------+| count(*) |+----------+|        0 |+----------+1 row in set (0.00 sec)

解决方法：在文件所在目录下登录数据库employees，手动执行脚本employees.sql最后标记为红色的语句。

[root@maomao employees_db]# lsChangelog                   employees.sql           load_employees.dump  READMEemployees_partitioned2.sql  load_departments.dump   load_salaries.dump   test_employees_md5.sqlemployees_partitioned3.sql  load_dept_emp.dump      load_titles.dump     test_employees_sha.sqlemployees_partitioned.sql   load_dept_manager.dump  objects.sqlcat employees.sqlCREATE TABLE salaries (    emp_no      INT             NOT NULL,    salary      INT             NOT NULL,    from_date   DATE            NOT NULL,    to_date     DATE            NOT NULL,    KEY         (emp_no),    FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,    PRIMARY KEY (emp_no, from_date)); SELECT 'LOADING departments' as 'INFO';source load_departments.dump ;SELECT 'LOADING employees' as 'INFO';source load_employees.dump ;SELECT 'LOADING dept_emp' as 'INFO';source load_dept_emp.dump ;SELECT 'LOADING dept_manager' as 'INFO';source load_dept_manager.dump ;SELECT 'LOADING titles' as 'INFO';source load_titles.dump ;SELECT 'LOADING salaries' as 'INFO';source load_salaries.dump ;

具体操作如下

[root@maomao employees_db]# mysql -uroot -p123456mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 425Server version: 5.7.22-log MySQL Community Server (GPL)Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.(root@localhost:mysql.sock)[(none)]>use employees;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changed(root@localhost:mysql.sock)[employees]>SELECT 'LOADING departments' as 'INFO';(root@localhost:mysql.sock)[employees]>source load_departments.dump ;(root@localhost:mysql.sock)[employees]>SELECT 'LOADING employees' as 'INFO';......

执行结束后查看，发现数据已经导入成功

(root@localhost:mysql.sock)[employees]>select count(*) from employees;+----------+| count(*) |+----------+|   300024 |+----------+1 row in set (0.06 sec)