• 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 '
    ';
    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 '
    ';

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

    以及:

    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 '
    ';
    
    LOAD DATA INFILE '/var/lib/mysql-files/employee.txt' INTO TABLE employee
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '
    ';
    
    LOAD DATA INFILE '/var/lib/mysql-files/ordermaster.txt' INTO TABLE order_master
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '
    ';
    
    LOAD DATA INFILE '/var/lib/mysql-files/product.txt' INTO TABLE product
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '
    ';
    
    LOAD DATA INFILE '/var/lib/mysql-files/orderdetail.txt' INTO TABLE order_detail
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '
    ';

    最终脚本.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
  • 相关阅读:
    SPRING IN ACTION 第4版笔记-第八章Advanced Spring MVC-001- 配置SpringFlow(flow-executor、flow-registry、FlowHandlerMapping、FlowHandlerAdapter)
    SPRING IN ACTION 第4版笔记-第七章Advanced Spring MVC-006- 如何保持重定向的request数据(用model、占位符、RedirectAttributes、model.addFlashAttribute("spitter", spitter);)
    SPRING IN ACTION 第4版笔记-第七章Advanced Spring MVC-005- 异常处理@ResponseStatus、@ExceptionHandler、@ControllerAdvice
    SPRING IN ACTION 第4版笔记-第七章Advanced Spring MVC-004- 处理上传文件
    SPRING IN ACTION 第4版笔记-第七章Advanced Spring MVC-003- 上传文件multipart,配置StandardServletMultipartResolver、CommonsMultipartResolver
    SPRING IN ACTION 第4版笔记-第七章Advanced Spring MVC-002- 在xml中引用Java配置文件,声明DispatcherServlet、ContextLoaderListener
    SPRING IN ACTION 第4版笔记-第七章Advanced Spring MVC-001- DispatcherServlet的高级配置(ServletRegistration.Dynamic、WebApplicationInitializer)
    Swift
    Swift
    用POP动画引擎实现弹簧动画(POPSpringAnimation)
  • 原文地址:https://www.cnblogs.com/xkxf/p/8746962.html
Copyright © 2020-2023  润新知