Mysql一快速导入数据 load data infile

一、测试准备

1.1 测试环境

如果直接使用这种格式进行数据的导入和导出会报错 MySQL server is running with the —secure-file-priv option … …
先去修改一下mysql的配置文件 my.ini , 加入这一行 secure_file_priv=”/“ 表示在任意路径均可以使用。需要注意的是这个只能去修改 my.ini的配置
数据库版本 Mysql8.0.12

1.2 准备测试数据一可参考简书中的文档

1.2.1 修改my.ini的设置，修改后重启服务

-- 查看默认配置:
SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log%';
SHOW GLOBAL VARIABLES LIKE 'max_heap_table_size%';
SHOW GLOBAL VARIABLES LIKE 'tmp_table_size%';
-- 配置 innodb_flush_log_at_trx_commit 级别
SET  GLOBAL innodb_flush_log_at_trx_commit = 0;
-- 打开my.ini 到 [mysqld] 下面添加 
tmp_table_size = 256M
max_heap_table_size = 256M

1.2.2 数据表

-- vote_record_memory 表
CREATE TABLE `vote_record_memory` (
    `id` INT (11) NOT NULL AUTO_INCREMENT,
    `user_id` VARCHAR (20) NOT NULL,
    `vote_id` INT (11) NOT NULL,
    `group_id` INT (11) NOT NULL,
    `create_time` datetime NOT NULL,
    PRIMARY KEY (`id`),
    KEY `index_id` (`user_id`) USING HASH
) ENGINE = MEMORY AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8

-- vote_record 表
CREATE TABLE `vote_record` (
    `id` INT (11) NOT NULL AUTO_INCREMENT,
    `user_id` VARCHAR (20) NOT NULL,
    `vote_id` INT (11) NOT NULL,
    `group_id` INT (11) NOT NULL,
    `create_time` datetime NOT NULL,
    PRIMARY KEY (`id`),
    KEY `index_user_id` (`user_id`) USING HASH
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8

1.2.3 创建函数

CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET latin1
BEGIN 
    DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; 
    DECLARE return_str varchar(255) DEFAULT '' ;
    DECLARE i INT DEFAULT 0; 
WHILE i < n DO 
    SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1)); 
    SET i = i +1; 
END WHILE; 
    RETURN return_str; 
END

1.2.4 创建存储过程

CREATE  PROCEDURE `add_vote_memory`(IN n int)
BEGIN  
  DECLARE i INT DEFAULT 1;
    WHILE (i <= n ) DO
      INSERT into vote_record_memory  (user_id,vote_id,group_id,create_time ) VALUEs (rand_string(20),FLOOR(RAND() * 1000),FLOOR(RAND() * 100) ,now() );
            set i=i+1;
    END WHILE;
END

1.2.5 数据插入

-- 调用方法插入数据到表中，数据任意设置。
CALL add_vote_memory(1000000);

-- 插入目标表从内存表中
INSERT into vote_record SELECT * from  vote_record_memory

二、测试实例

2.1 测试csv文件的插入

2.1.1 准备CSV 文件

用其它工具已经存在了测件 ‘d:/Users/Shang_Xiang/Desktop/test_schedule/vote_record.csv’

2.1.2 sql执行

load data infile 'd:/Users/Shang_Xiang/Desktop/test_schedule/vote_record.csv' 
into table `vote_record_memory` character set utf8 
fields terminated by ','
OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
ignore 1 lines
(@id,user_id, vote_id, group_id, create_time);

2.1.3 执行结果

三、load data infile 分析