改编自学校实验，涉及一些字符集相关的问题。

索引

1. 建库
2. 导入数据
3. 最终脚本

下载数据 点击这里

建库

create.sql

DROP DATABASE IF EXISTS orderdb;CREATE DATABASE orderdb;USE orderdb;CREATE TABLE employee ( employee_no VARCHAR(8), employee_name VARCHAR(10), sex CHAR(1), birthday DATE, address VARCHAR(50), telephone VARCHAR(20), hiredate DATE COMMENT ‘聘用日期‘, department VARCHAR(30), headship VARCHAR(10) COMMENT ‘职务‘, salary DECIMAL(8,2), PRIMARY KEY(employee_no)) ENGINE=INNODB DEFAULT CHARSET=utf8;CREATE TABLE customer ( customer_no VARCHAR(9), customer_name VARCHAR(40), telephone VARCHAR(20), address VARCHAR(40), zip VARCHAR(6), PRIMARY KEY(customer_no)) ENGINE=INNODB DEFAULT CHARSET=utf8;CREATE TABLE product ( product_no VARCHAR(9), product_name VARCHAR(40), product_class VARCHAR(20), product_price DECIMAL(7,2),) ENGINE=INNODB DEFAULT CHARSET=utf8; order_no VARCHAR(12), customer_no VARCHAR(9), saler_no VARCHAR(8), order_date DATE, order_sum DECIMAL(9,2), invoiceno CHAR(10) COMMENT ‘发票号码‘, PRIMARY KEY (order_no), FOREIGN KEY (customer_no) REFERENCES customer(customer_no)) ENGINE=INNODB DEFAULT CHARSET=utf8;CREATE TABLE order_detail ( order_no VARCHAR(12), product_no VARCHAR(9), quantity INT, price DECIMAL(7,2), PRIMARY KEY (order_no, product_no), FOREIGN KEY (order_no) REFERENCES order_master(order_no), FOREIGN KEY (product_no) REFERENCES product(product_no)) ENGINE=INNODB DEFAULT CHARSET=utf8;

Linux 环境下运行脚本：

root@xkfx:~/sql-exercise# readlink -f create.sql /root/sql-exercise/create.sql # 复制这个路径root@xkfx:~/sql-exercise# mysql -uroot -pmysql > SOURCE /root/sql-exercise/create.sql # 运行脚本

导入数据

在 Linux 环境下或许需要修改 txt 的编码格式：

root@xkfx:~/sql-exercise# iconv -f gb18030 -t utf8 customer.txt -o customer.txt

并且待导入的 txt 文件必须放在特定目录下，查看特定目录：

mysql> show variables like ‘%secure%‘; +------------------+-----------------------+| Variable_name | Value |+------------------+-----------------------+| secure_auth | OFF || secure_file_priv | /var/lib/mysql-files/ |+------------------+-----------------------+

如上所示 /var/lib/mysql-files/ 就是特定的目录。导入数据：

LOAD DATA INFILE ‘file_path‘ INTO TABLE table_nameFIELDS TERMINATED BY ‘xxx‘LINES TERMINATED BY ‘xxxx‘

示范如下：

mysql> USE orderdbReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> LOAD DATA INFILE ‘/var/lib/mysql-files/customer.txt‘ INTO TABLE customer -> FIELDS TERMINATED BY ‘,‘ -> LINES TERMINATED BY ‘\n‘;Query OK, 10 rows affected, 10 warnings (0.00 sec)Records: 10 Deleted: 0 Skipped: 0 Warnings: 10

 这个时候试着 SELECT 一下 customer 中的数据，有很大概率是乱码的，这个时候不要着急，你需要重新设定字符集再来一遍：

SHOW VARIABLES LIKE "%char%"; # 显示字符集的设定情况SET character_set_database=utf8; # 像这样，把不是 utf8 的设定为 utf8SET NAMES ‘utf8‘;SOURCE /root/sql-exercise/create.sqlLOAD DATA INFILE ‘/var/lib/mysql-files/customer.txt‘ INTO TABLE customerFIELDS TERMINATED BY ‘,‘LINES TERMINATED BY ‘\n‘;

补充：平时创建表的时候要保证 ↓

以及：

CREATE TABLE xxxxx ( ) ENGINE=INNODB DEFAULT CHARSET=utf8;

才不容易出现乱码。

最终脚本

为了方便起见，只需要把相应文件放在 特定位置，修改一下脚本中的路径，最后运行最终脚本就可以了：

 populate.sql 

LOAD DATA INFILE ‘/var/lib/mysql-files/customer.txt‘ INTO TABLE customerFIELDS TERMINATED BY ‘,‘LINES TERMINATED BY ‘\n‘;LOAD DATA INFILE ‘/var/lib/mysql-files/employee.txt‘ INTO TABLE employeeFIELDS TERMINATED BY ‘,‘LINES TERMINATED BY ‘\n‘;LOAD DATA INFILE ‘/var/lib/mysql-files/ordermaster.txt‘ INTO TABLE order_masterFIELDS TERMINATED BY ‘,‘LINES TERMINATED BY ‘\n‘;LOAD DATA INFILE ‘/var/lib/mysql-files/product.txt‘ INTO TABLE productFIELDS TERMINATED BY ‘,‘LINES TERMINATED BY ‘\n‘;LOAD DATA INFILE ‘/var/lib/mysql-files/orderdetail.txt‘ INTO TABLE order_detailFIELDS TERMINATED BY ‘,‘LINES TERMINATED BY ‘\n‘;

最终脚本.sql

SET character_set_database = utf8; SET character_set_server = utf8;SET NAMES ‘utf8‘;SHOW VARIABLES LIKE "%char%"; SOURCE /root/sql-exercise/create.sqlSOURCE /root/sql-exercise/populate.sql

mysql> SOURCE /root/sql-exercise/orderdb.sql