MySQL数据库 Load Data 多种用法

目录
  • MySQL Load Data 的多种用法
    • 一、LOAD 基本背景
    • 二、LOAD 基础参数
    • 三、LOAD 示例数据及示例表结构
    • 四、LOAD 场景示例
    • 五、LOAD 总结

MySQL Load Data 的多种用法

一、LOAD 基本背景

我们在数据库运维过程中难免会涉及到需要对文本数据进行处理,并导入到数据库中,本文整理了一些导入导出时常见的场景进行示例演示。

二、LOAD 基础参数

文章后续示例均使用以下命令导出的 csv 格式样例数据(以 , 逗号做分隔符,以 " 双引号作为界定符)

-- 导出基础参数
select * into outfile '/data/mysql/3306/tmp/employees.txt'
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
from employees.employees limit 10;

-- 导入基础参数
load data infile '/data/mysql/3306/tmp/employees.txt'
replace into table demo.emp
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
...

三、LOAD 示例数据及示例表结构

以下为示例数据,表结构及对应关系信息

-- 导出的文件数据内容
[root@10-186-61-162 tmp]# cat employees.txt
"10001","1953-09-02","Georgi","Facello","M","1986-06-26"
"10002","1964-06-02","Bezalel","Simmel","F","1985-11-21"
"10003","1959-12-03","Parto","Bamford","M","1986-08-28"
"10004","1954-05-01","Chirstian","Koblick","M","1986-12-01"
"10005","1955-01-21","Kyoichi","Maliniak","M","1989-09-12"
"10006","1953-04-20","Anneke","Preusig","F","1989-06-02"
"10007","1957-05-23","Tzvetan","Zielinski","F","1989-02-10"
"10008","1958-02-19","Saniya","Kalloufi","M","1994-09-15"
"10009","1952-04-19","Sumant","Peac","F","1985-02-18"
"10010","1963-06-01","Duangkaew","Piveteau","F","1989-08-24"

-- 示例表结构
SQL > desc demo.emp;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| emp_no      | int           | NO   | PRI | NULL    |       |
| birth_date  | date          | NO   |     | NULL    |       |
| first_name  | varchar(16)   | NO   |     | NULL    |       |
| last_name   | varchar(16)   | NO   |     | NULL    |       |
| fullname    | varchar(32)   | YES  |     | NULL    |       | -- 表新增字段,导出数据文件中不存在
| gender      | enum('M','F') | NO   |     | NULL    |       |
| hire_date   | date          | NO   |     | NULL    |       |
| modify_date | datetime      | YES  |     | NULL    |       | -- 表新增字段,导出数据文件中不存在
| delete_flag | char(1)       | YES  |     | NULL    |       | -- 表新增字段,导出数据文件中不存在
+-------------+---------------+------+-----+---------+-------+

-- 导出的数据与字段对应关系
emp_no  birth_date    first_name   last_name    gender  hire_date
"10001"  "1953-09-02"  "Georgi"     "Facello"    "M"    "1986-06-26"
"10002"  "1964-06-02"  "Bezalel"    "Simmel"     "F"    "1985-11-21"
"10003"  "1959-12-03"  "Parto"      "Bamford"    "M"    "1986-08-28"
"10004"  "1954-05-01"  "Chirstian"  "Koblick"    "M"    "1986-12-01"
"10005"  "1955-01-21"  "Kyoichi"    "Maliniak"   "M"    "1989-09-12"
"10006"  "1953-04-20"  "Anneke"     "Preusig"    "F"    "1989-06-02"
"10007"  "1957-05-23"  "Tzvetan"    "Zielinski"  "F"    "1989-02-10"
"10008"  "1958-02-19"  "Saniya"     "Kalloufi"   "M"    "1994-09-15"
"10009"  "1952-04-19"  "Sumant"     "Peac"       "F"    "1985-02-18"
"10010"  "1963-06-01"  "Duangkaew"  "Piveteau"   "F"    "1989-08-24"

四、LOAD 场景示例

场景1. LOAD 文件中的字段比数据表中的字段多

只需要文本文件中部分数据导入到数据表中

-- 临时创建2个字段的表结构
SQL > create table emp_tmp select emp_no,hire_date from emp;
SQL > desc emp_tmp;
+-----------+------+------+-----+---------+-------+
| Field     | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| emp_no    | int  | NO   |     | NULL    |       |
| hire_date | date | NO   |     | NULL    |       |
+-----------+------+------+-----+---------+-------+

-- 导入数据语句
load data infile '/data/mysql/3306/tmp/employees.txt'
replace into table demo.emp_tmp
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(@C1,@C2,@C3,@C4,@C5,@C6) -- 该部分对应employees.txt文件中6列数据
-- 只对导出数据中指定的2个列与表中字段做匹配,mapping关系指定的顺序不影响导入结果
set hire_date=@C6,
    emp_no=@C1; 

-- 导入数据结果示例
SQL > select * from emp_tmp;
+--------+------------+
| emp_no | hire_date  |
+--------+------------+
|  10001 | 1986-06-26 |
|  10002 | 1985-11-21 |
|  10003 | 1986-08-28 |
|  10004 | 1986-12-01 |
|  10005 | 1989-09-12 |
|  10006 | 1989-06-02 |
|  10007 | 1989-02-10 |
|  10008 | 1994-09-15 |
|  10009 | 1985-02-18 |
|  10010 | 1989-08-24 |
+--------+------------+
10 rows in set (0.0016 sec)

场景 2. LOAD 文件中的字段比数据表中的字段少

表字段不仅包含文本文件中所有数据,还包含了额外的字段

-- 导入数据语句
load data infile '/data/mysql/3306/tmp/employees.txt'
replace into table demo.emp
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(@C1,@C2,@C3,@C4,@C5,@C6) -- 该部分对应employees.txt文件中6列数据
-- 将文件中的字段与表中字段做mapping对应,表中多出的字段不做处理
set emp_no=@C1,
   birth_date=@C2,
   first_name=@C3,
   last_name=@C4,
   gender=@C5,
   hire_date=@C6;

 

场景3. LOAD 生成自定义字段数据

从场景 2 的验证可以看到,emp 表中新增的字段 fullname,modify_date,delete_flag 字段在导入时并未做处理,被置为了 NULL 值,如果需要对其进行处理,可在 LOAD 时通过 MySQL支持的函数 或给定 固定值 自行定义数据,对于文件中存在的字段也可做函数处理,结合导入导出,实现简单的 ETL 功能,如下所示:

-- 导入数据语句
load data infile '/data/mysql/3306/tmp/employees.txt'
replace into table demo.emp
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(@C1,@C2,@C3,@C4,@C5,@C6)              -- 该部分对应employees.txt文件中6列数据

-- 以下部分明确对表中字段与数据文件中的字段做Mapping关系,不存在的数据通过函数处理生成(也可设置为固定值)
set emp_no=@C1,
   birth_date=@C2,
   first_name=upper(@C3),              -- 将导入的数据转为大写
   last_name=lower(@C4),               -- 将导入的数据转为小写
   fullname=concat(first_name,' ',last_name),    -- 对first_name和last_name做拼接
   gender=@C5,
   hire_date=@C6 ,
   modify_date=now(),                 -- 生成当前时间数据
   delete_flag=if(hire_date<'1988-01-01','Y','N'); -- 对需要生成的值基于某一列做条件运算

场景4. LOAD 定长数据

定长数据的特点如下所示,可以使用函数取出字符串中固定长度来生成指定列数据

SQL > select
    c1 as sample_data,
    substr(c1,1,3)  as c1,
    substr(c1,4,3)  as c2,
    substr(c1,7,2)  as c3,
    substr(c1,9,5)  as c4,
    substr(c1,14,3) as c5,
    substr(c1,17,3) as c6 from t1

*************************** 1. row ***************************
sample_data: ABC余振兴CDMySQLEFG数据库
         c1: ABC
         c2: 余振兴
         c3: CD
         c4: MySQL
         c5: EFG
         c6: 数据库

定长数据导入需要明确每列数据占用的字符个数,以下直接使用 rpad 对现有的表数据填充空格的方式生成定长数据用作示例使用

-- 生成定长数据
SQL > select
    concat(rpad(emp_no,10,' '),
          rpad(birth_date,19,' '),
          rpad(first_name,14,' '),
          rpad(last_name,16,' '),
          rpad(gender,2,' '),
          rpad(hire_date,19,' ')) as fixed_length_data
      from employees.employees limit 10;

+----------------------------------------------------------------------------------+
| fixed_length_data                                                                |
+----------------------------------------------------------------------------------+
| 10001     1953-09-02         Georgi        Facello         M 1986-06-26          |
| 10002     1964-06-02         Bezalel       Simmel          F 1985-11-21          |
| 10003     1959-12-03         Parto         Bamford         M 1986-08-28          |
| 10004     1954-05-01         Chirstian     Koblick         M 1986-12-01          |
| 10005     1955-01-21         Kyoichi       Maliniak        M 1989-09-12          |
| 10006     1953-04-20         Anneke        Preusig         F 1989-06-02          |
| 10007     1957-05-23         Tzvetan       Zielinski       F 1989-02-10          |
| 10008     1958-02-19         Saniya        Kalloufi        M 1994-09-15          |
| 10009     1952-04-19         Sumant        Peac            F 1985-02-18          |
| 10010     1963-06-01         Duangkaew     Piveteau        F 1989-08-24          |
+----------------------------------------------------------------------------------+

-- 导出定长数据
select
    concat(rpad(emp_no,10,' '),
          rpad(birth_date,19,' '),
          rpad(first_name,14,' '),
          rpad(last_name,16,' '),
          rpad(gender,2,' '),
          rpad(hire_date,19,' ')) as fixed_length_data
into outfile '/data/mysql/3306/tmp/employees_fixed.txt'
character set utf8mb4
lines terminated by '\n'
from employees.employees limit 10;

-- 导出数据示例
[root@10-186-61-162 tmp]# cat employees_fixed.txt
10001     1953-09-02         Georgi        Facello         M 1986-06-26
10002     1964-06-02         Bezalel       Simmel          F 1985-11-21
10003     1959-12-03         Parto         Bamford         M 1986-08-28
10004     1954-05-01         Chirstian     Koblick         M 1986-12-01
10005     1955-01-21         Kyoichi       Maliniak        M 1989-09-12
10006     1953-04-20         Anneke        Preusig         F 1989-06-02
10007     1957-05-23         Tzvetan       Zielinski       F 1989-02-10
10008     1958-02-19         Saniya        Kalloufi        M 1994-09-15
10009     1952-04-19         Sumant        Peac            F 1985-02-18
10010     1963-06-01         Duangkaew     Piveteau        F 1989-08-24

-- 导入定长数据
load data infile '/data/mysql/3306/tmp/employees_fixed.txt'
replace into table demo.emp
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(@row)  -- 对一行数据定义为一个整体
set emp_no   = trim(substr(@row,1,10)),-- 使用substr取前10个字符,并去除头尾空格数据
   birth_date = trim(substr(@row,11,19)),-- 后续字段以此类推
   first_name = trim(substr(@row,30,14)),
   last_name  = trim(substr(@row,44,16)),
   fullname  = concat(first_name,' ',last_name),  -- 对first_name和last_name做拼接
   gender   = trim(substr(@row,60,2)),
   hire_date  = trim(substr(@row,62,19)),
   modify_date = now(),
   delete_flag = if(hire_date<'1988-01-01','Y','N'); -- 对需要生成的值基于某一列做条件运算

五、LOAD 总结

1.默认情况下导入的顺序以文本文件 列-从左到右,行-从上到下 的顺序导入

2.如果表结构和文本数据不一致,建议将文本文件中的各列依次顺序编号并与表中字段建立 mapping 关系,以防数据导入到错误的字段

3.对于待导入的文本文件较大的场景,建议将文件 按行拆分 为多个小文件,如用 split 拆分

4.对文件导入后建议执行以下语句验证导入的数据是否有 Warning,ERROR 以及导入的数据量

  • GET DIAGNOSTICS @p1=NUMBER,@p2=ROW_COUNT;
  • select @p1 AS ERROR_COUNT,@p2 as ROW_COUNT;

5.文本文件数据与表结构存在过大的差异或数据需要做清洗转换,建议还是用专业的 ETL 工具或先粗略导入 MySQL 中再进行加工转换处理

以上就是 MySQL Load Data 数据的多种用法的详细内容,更多关于MySQL Load Data 的用法的资料请关注我们其它相关文章!,希望大家以后多多支持我们!

(0)

相关推荐

  • mysql load data infile 的用法(40w数据 用了3-5秒导进mysql)

    如果是导入有中文的数据,我的mysql 设置的utf8 字符集,所以你要导入的xxx.txt 文件也要保存utf-8的字符集,命令 load data infile "d:/Websites/Sxxxx/test1.txt" ignore into table `names` fields terminated by ',' enclosed by '"'; 不知道用replace 这个关键字的话,还是会乱码..不同.等高手回答. 在详细的介绍,推荐大家去看mysql手册去吧

  • Java利用MYSQL LOAD DATA LOCAL INFILE实现大批量导入数据到MySQL

    Mysql load data的使用 数据库中,最常见的写入数据方式是通过SQL INSERT来写入,另外就是通过备份文件恢复数据库,这种备份文件在MySQL中是SQL脚本,实际上执行的还是在批量INSERT语句. 在实际中,常常会遇到两类问题:一类是数据导入,比如从word.excel表格或者txt文档导入数据(这些数据一般来自于非技术人员通过OFFICE工具录入的文档):一类数据交换,比如从MySQL.Oracle.DB2数据库之间的数据交换. 这其中就面临一个问题:数据库SQL脚本有差异,

  • mysql中Load Data记录换行问题的解决方法

    问题是这样的: 表persons有两个字段: id和name文本文档persons.txt中内容(其中每行字段之间用tab分割):1    Bush2    Carter3    Bush 在mysql命令行下使用 load data local infile "persons.txt" into table persons 导入数据到persons表中. 导入后查看persons表的数据,与persons.txt的内容一致.但是使用语句select distinct name fro

  • mysql 的load data infile

    LOAD DATA INFILE语句从一个文本文件中以很高的速度读入一个表中.如果指定LOCAL关键词,从客户主机读文件.如果LOCAL没指定,文件必须位于服务器上.(LOCAL在MySQL3.22.6或以后版本中可用.) 为了安全原因,当读取位于服务器上的文本文件时,文件必须处于数据库目录或可被所有人读取.另外,为了对服务器上文件使用LOAD DATA INFILE,在服务器主机上你必须有file的权限. 头一回用load data infile,以为只是把插入语句写到一个文件里,然后用loa

  • mysql Load Data InFile 的用法

    首先创建一个表 Use Test; Create Table TableTest( `ID` mediumint(8) default '0', `Name` varchar(100) default '' ) TYPE=MyISAM; 向数据表导入数据 Load Data InFile 'C:/Data.txt' Into Table `TableTest` 常用如下: Load Data InFile 'C:/Data.txt' Into Table `TableTest` Lines Te

  • mysql中的Load data的使用方法

    测试把txt文件导入至mysql数据库中: table: txt文件:D:/data.txt (txt文件下载) txt中使用 '\N' 描述null值. 导入数据: 复制代码 代码如下: load data local infile 'D:/data.txt' into table pet lines terminated by '\r\n' ignore 1 lines; 应用mysql版本:

  • mysql遇到load data导入文件数据出现1290错误的解决方案

    错误出现情景 在cmd中使用mysql命令,学生信息表添加数据.使用load data方式简单批量导入数据. 准备好文本数据: xueshengxinxi.txt 文件  数据之间以tab键进行分割 执行 "load data infile 文本数据路径  into table tab_load_data"  弹出错误. #load data (载入数据)语法,要载入数据如下: 1 张三 男 江西 1 2 李四 男 四川 2 3 王五 男 上海 1 4 赵六 女 湖北 3 5 孙七 女

  • MySQL中由load data语句引起死锁的解决案例

    一个线上项目报的死锁,简要说明一下产生原因.处理方案和相关的一些点. 1.背景 这是一个类似数据分析的项目,数据完全通过LOAD DATA语句导入一个InnoDB表中.为方便描述,表结构简化为如下: Create table tb(id int primary key auto_increment, c int not null) engine=innodb; 导入数据的语句对应为 Load data infile 'data1.csv' into table tb; Load data inf

  • MySQL数据库 Load Data 多种用法

    目录 MySQL Load Data 的多种用法 一.LOAD 基本背景 二.LOAD 基础参数 三.LOAD 示例数据及示例表结构 四.LOAD 场景示例 五.LOAD 总结 MySQL Load Data 的多种用法 一.LOAD 基本背景 我们在数据库运维过程中难免会涉及到需要对文本数据进行处理,并导入到数据库中,本文整理了一些导入导出时常见的场景进行示例演示. 二.LOAD 基础参数 文章后续示例均使用以下命令导出的 csv 格式样例数据(以 , 逗号做分隔符,以 " 双引号作为界定符)

  • Navicat修改MySQL数据库密码的多种方法

    方法1: 用SET PASSWORD命令 首先登录MySQL. 格式:mysql> set password for 用户名@localhost = password('新密码'); 例子:mysql> set password for root@localhost = password('123'); 方法2:用mysqladmin 格式:mysqladmin -u用户名 -p旧密码 password 新密码 例子:mysqladmin -uroot -p123456 password 12

  • mysql 数据库备份的多种实现方式总结

    本文实例讲述了mysql 数据库备份的多种实现方式.分享给大家供大家参考,具体如下: 一.使用mysqldump进行备份 1.完整备份所有数据库 mysqldump -u root -p --all-databases > E:/all.sql 在mysql8之前,存储过程和事件存储在mysql.proc和mysql.event表中. 从mysql8开始,相应对象的定义存储在数据字典中,这些表不会被备份. 要将存储过程和事件也包含,请使用如下语句: mysqldump -u root -p --

  • MySQL数据库迁移data文件夹位置详细步骤

    由于yum安装mysql的时候,数据库的data目录默认是在/var/lib下,出于数据安全性的考虑需要把它挪到/data分区.步骤如下:一.关闭apache和mysql. 复制代码 代码如下: service httpd stopservice mysqld stop 二.将/var/lib下的mysql目录mv(移动)到data目录.为什么要用mv命令,而不用cp命令呢?应为linux文件系统特殊性,mv命令能保留文件的所有属性和权限,尤其是selinux属性.如果用cp命令,就需要回头再去

  • PHP封装mysqli基于面向对象的mysql数据库操作类与用法示例

    本文实例讲述了PHP封装mysqli基于面向对象的mysql数据库操作与用法.分享给大家供大家参考,具体如下: 首先封装好mysql类 mysql.php <?php class Mysql{ private static $host="localhost"; private static $user="root"; private static $password="123456"; private static $dbName=&quo

  • phpnow重新安装mysql数据库的方法

    1.phpnow下重装Mysql数据库 如果之前重装过系统或卸载过服务,原先的Mysql文件夹完全保留原状,只需要将mysql安装成服务,可以按以下步骤操作:在bin目录下msyql\bin>mysqld-nt install 服务名 --defaults-file="my.ini文件的位置" //安装mysql服务以指定的服务名安装成服务,服务运行时,在指定位置读取my.ini文件如果my.ini的位置放置正确,即在默认的mysql目录结构下放置,可以不用加--defaults

  • MySQL数据库Shell import_table数据导入

    目录 MySQL Shell import_table数据导入 1. import_table介绍 2. Load Data 与 import table功能示例 2.1 用Load Data方式导入数据 2.2 用import_table方式导入数据 3. import_table特定功能 3.1 多文件导入(模糊匹配) 3.2 并发导入 3.3 导入速率控制 3.4 自定义chunk大小 4. Load Data vs import_table性能对比 MySQL Shell import_

随机推荐