MySQL笔记（三）由txt文件导入数据

时间：2023-03-08 23:39:26
MySQL笔记（三）由txt文件导入数据

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

索引

  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 -p

mysql > 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_name

FIELDS TERMINATED BY 'xxx'

LINES TERMINATED BY 'xxxx'

示范如下：

mysql> USE orderdb

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> 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 的设定为 utf8

SET NAMES 'utf8';

SOURCE /root/sql-exercise/create.sql

LOAD DATA INFILE '/var/lib/mysql-files/customer.txt' INTO TABLE customer

FIELDS TERMINATED BY ','

LINES TERMINATED BY '\n';

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

MySQL笔记（三）由txt文件导入数据

以及：

CREATE TABLE xxxxx (

)    ENGINE=INNODB DEFAULT CHARSET=utf8;

才不容易出现乱码。

最终脚本

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

populate.sql

LOAD DATA INFILE '/var/lib/mysql-files/customer.txt' INTO TABLE customer

FIELDS TERMINATED BY ','

LINES TERMINATED BY '\n';

LOAD DATA INFILE '/var/lib/mysql-files/employee.txt' INTO TABLE employee

FIELDS TERMINATED BY ','

LINES TERMINATED BY '\n';

LOAD DATA INFILE '/var/lib/mysql-files/ordermaster.txt' INTO TABLE order_master

FIELDS TERMINATED BY ','

LINES TERMINATED BY '\n';

LOAD DATA INFILE '/var/lib/mysql-files/product.txt' INTO TABLE product

FIELDS TERMINATED BY ','

LINES TERMINATED BY '\n';

LOAD DATA INFILE '/var/lib/mysql-files/orderdetail.txt' INTO TABLE order_detail

FIELDS 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.sql

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

相关文章