MySQL lOAD DATA详解

官方文档
https://dev.mysql.com/doc/refman/8.0/en/load-data.html

样例

LOAD DATA
[LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var
[, col_name_or_user_var] ...)]
[SET col_name={expr | DEFAULT}
[, col_name={expr | DEFAULT}] ...]

LOCAL:是否导入本地电脑文本文件，

导入本地电脑文件：一定要启用 local_infile 参数，否则会报错。
导入非本地电脑文件：用户一定要 FILE 权限，secure_file_priv参数值如果不为空，则文件一定要在这个目录中，如果为空，则该文件只需服务器可读。

我本地使用mysql8.0。23客户端，在一切条件符合的情况下，LOAD DATA数据报错.
mysql> load data local infile '/Users/1.csv' into table ceshi.t1 ;
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

排错一圈，才发现踩了mysql8的一个bug，
https://bugs.mysql.com/bug.php?id=91872

  • 解决方法：
    在client端配置文件中加入
[client]
loose-local-infile = 1
[mysqld]
local_infile=1

然后再在使用mysql命令行时，指定 loose-local-infile = 1 连接数据库

mysql --local-infile=1 -uroot -p123456 -P3306 -h1.1.1.1

[REPLACE | IGNORE]：如遇到唯一冲突重复处理机制

REPLACE：覆盖写。
IGNORE：忽略。
如果没有指定REPLACE， IGNORE或者LOCAL，当发生错误时，会报错，并且文本余下部分不会被执行。

示例：

mysql> load data infile '/root/1.csv' into table ceshi.t1 ;
ERROR 1265 (01000): Data truncated for column 'id' at row 2

提示：如果要在加载数据中忽略外键约束，需要在Load data 数据之前执行SET foreign_key_checks = 0

如果没有指定 FIELDS 或 LINES 子句，则默认值如下
FIELDS TERMINATED BY ‘\t’ ENCLOSED BY ‘’ ESCAPED BY ‘\’
LINES TERMINATED BY ‘\n’ STARTING BY ‘’
提示：在 WINDOWS 系统中，想要正确的读文件需要配置 LINES TERMINATED BY ‘\r\n’，因为WINDOWS系统通常使用两个字符做为终止符。

CHARACTER SET charset_name

设置导入内容的字符集,默认采用character_set_database系统变量值字符集导入内容。
提示：
这里我踩了一个坑，我本地使用CRT连接数据库，不知为何客户端字符集是latain1了，文本中包含中文，如果以默认方式导入会出现乱码。一般情况下，不需要指定CHARACTER SET

示例：

root# cat 1.csv
1,chai
2,测试

mysql> show variables like '%character%'
-> ;
+--------------------------+------------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql57/share/charsets/ |
+--------------------------+------------------------------------+
8 rows in set (0.07 sec)

mysql> load data local infile '/Users/1.csv' into table ceshi.t1 FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\';
Query OK, 2 rows affected (0.15 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | chai |
| 2 | ?? |
+----+------+
2 rows in set (0.06 sec)

set names utf8;
再查询就正常了
mysql> select * from t1;
+----+----------------+
| id | name |
+----+----------------+
| 1 | chai |
| 2 | 测试 |

FIELDS TERMINATED BY：指定两列之间分隔符，

默认是\t ，也就是跳格，但大多时候生成的文本文件都是’,'逗号，所以在导入数据时，需要显式指定。

示例：

root# cat 1.csv
1,chai
2,测试

mysql> load data local infile '/Users/1.csv' into table ceshi.t1 FIELDS TERMINATED BY ',';
Query OK, 2 rows affected (0.20 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
+----+--------+
| id | name |
+----+--------+
| 1 | chai |
| 2 | 测试 |
+----+--------+
2 rows in set (0.09 sec)

ENCLOSED BY：去掉字符串中包裹的符号

示例：

root #cat 1.csv
1,chai
2,测试
3,""chayicha"
4,"chayige"

如果以之前的参数导入，则结果如下,里边的引号也会写入进去。

mysql> load data local infile '/Users/1.csv' into table ceshi.t1 FIELDS TERMINATED BY ',';
Query OK, 4 rows affected (0.16 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
+----+-------------+
| id | name |
+----+-------------+
| 1 | chai |
| 2 | 测试 |
| 3 | ""chayicha" |
| 4 | "chayige" |
+----+-------------+
4 rows in set (0.05 sec)

##加入 ENCLOSED BY ‘"’ 参数后，在导入时字符左右两则的双引号被删掉了。

mysql> load data local infile '/Users/1.csv' into table ceshi.t1 
FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Query OK, 4 rows affected (0.13 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
+----+-----------+
| id | name |
+----+-----------+
| 1 | chai |
| 2 | 测试 |
| 3 | "chayicha |
| 4 | chayige |
+----+-----------+

ESCAPED BY:设置转义字符，默认为\ 。

示例：

root#cat 1.csv
1,chai
2,测试
3,"\tchayicha"
4,wo\\a\\b\\c\tchayige

mysql> load data local infile '/Users/1.csv' into table ceshi.t1 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\';
Query OK, 4 rows affected (0.13 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
+----+------------------+
| id | name |
+----+------------------+
| 1 | chai |
| 2 | 测试 |
| 3 | chayicha |
| 4 | wo\a\b\c chayige |
+----+------------------+

LINES STARTING BY：忽略一个公共前缀,

如示例，只有以 cha 开头的记录正确写入到了数据库，这个参数应该不常用

示例：

root#cat 1.csv
cha1,chai
2,测试
cha3,"yicha"
4,chayige

mysql> load data local infile '/Users/1.csv' into table ceshi.t1 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
ESCAPED BY '\\' LINES STARTING BY 'cha';
Query OK, 3 rows affected, 2 warnings (0.14 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 2

mysql> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 1 | chai |
| 3 | yicha |
| 0 | NULL |
+----+-------+

LINES TERMINATED BY ‘string’：分行符，

一般情况下遇到回行即分行 (\r\n)

示例：

演示一次遇到句号(。)即换行符
root#cat 2.csv
a,chai。2,测试。3,chayicha。

mysql> load data local infile '/Users/2.csv' into table ceshi.t1 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
ESCAPED BY '\\' LINES TERMINATED BY '。';
Query OK, 4 rows affected, 4 warnings (0.15 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 4

mysql> select * from t1;
+----+----------+
| id | name |
+----+----------+
| 0 | chai |
| 2 | 测试 |
| 3 | chayicha |
| 0 | NULL |
+----+----------+
4 rows in set (0.07 sec)

IGNORE number {LINES | ROWS}:跳过开始的多少行才进行导入，

如果文本中有字段名，可以跳过第一行.

示例：

root# cat 1.csv
1,chai
2,测试
3,"yicha"
4,chayige

mysql> load data local infile '/Users/1.csv' into table ceshi.t1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' IGNORE
1 LINES;
Query OK, 3 rows affected (0.13 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
+----+---------+
| id | name |
+----+---------+
| 2 | 测试 |
| 3 | yicha |
| 4 | chayige |
+----+---------+

[(col_name_or_user_var [, col_name_or_user_var] …)]:手动指定要插入的列

示例：

root# cat 1.csv
1,chai
2,测试
3,"yicha"
4,chayige

mysql> load data local infile '/Users/1.csv' into table ceshi.t1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' IGNORE
1 LINES(id,name);
Query OK, 3 rows affected (0.16 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 2 | 测试 | NULL |
| 3 | yicha | NULL |
| 4 | chayige | NULL |
+----+---------+------+
3 rows in set (0.09 sec)

[SET col_name={expr | DEFAULT} [, col_name={expr | DEFAULT}] …]:在加载数据时做一些计算或更新一些其它字段值。

示例：

root# cat 1.csv
1,chai
2,测试
3,"yicha"
4,chayige

#在写入数据时，更新age字段列

mysql> load data local infile '/Users/1.csv' into table ceshi.t1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' IGNORE
-> 1 LINES(id,name) set age=10;
Query OK, 3 rows affected (0.13 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 2 | 测试 | 10 |
| 3 | yicha | 10 |
| 4 | chayige | 10 |
+----+---------+------+
3 rows in set (0.07 sec)

#在写入数据时对数据做二次逻辑处理

mysql> load data local infile '/Users/1.csv' into table ceshi.t1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' IGNORE
-> 1 LINES(id,@name) set name=concat(@name,1);
Query OK, 3 rows affected (0.14 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 2 | 测试1 | NULL |
| 3 | yicha1 | NULL |
| 4 | chayige1 | NULL |
+----+----------+------+
3 rows in set (0.07 sec)

最后贴一个成功案例

load data local infile 'D:\\34178517.csv' 
into table `finance_new`            
fields terminated by ','  
 enclosed by '"'        
lines terminated by '\n'
ignore 1 lines
(@id,`uuid`, `buyer_type`, `buyer_account`, `vendor_code`, `vendor_name`, `vendor_short_name`, `policy_no`, `order_id`, `biz_id`, `parent_biz_id`, `biz_line`, `biz_type`);
# 忽略第一行和id字段不赋值，因为表头已建好

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.mfbz.cn/a/12648.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

Linux搭建SVN服务器详细教程

Linux搭建SVN服务器详细教程

前言 本文讲解 Linux 系统下如何搭建 SVN 服务器，详细说明各配置项的功能，最终实现可管控多个项目的复杂配置。 SVN 是 subversion 的缩写，是一个开放源代码的版本控制系统，通过采用分支管理系统的高效管理，实现最终集…
阅读更多...
HANA SDA连接外部数据库到BW的步骤

HANA SDA连接外部数据库到BW的步骤

咱都知道，我们不能直接从BW连接到外部数据库。第一步得从HANA database通过SDA去建一个到外部DB的连接。 数据库连接好了，那么接下来别忘了，还得建一个源系统。 也就是说第一步，我们要用HANA SDA通过Linux ODBC driver去连接外部…
阅读更多...
PHP快速入门05-时间日期与时区，附30个常用案例

PHP快速入门05-时间日期与时区，附30个常用案例

文章目录 前言一、时间日期与时区1.1 时间与日期1.2 时区 二、 30个日期时间函数的用法示例2.1 获取当前的时间戳2.2 将时间戳格式化为日期时间2.3 获取当前的日期2.4 获取当前的时间2.5 获取当前年份2.6 获取当前月份2.7 获取当前日期的第几天2.8 计算两个日期之间的天数差2.9…
阅读更多...
【生活工作经验 十】ChatGPT模型对话初探

【生活工作经验 十】ChatGPT模型对话初探

最近探索了下全球大火的ChatGPT，想对此做个初步了解 一篇博客 当今社会，自然语言处理技术得到了迅速的发展，人工智能技术也越来越受到关注。其中，基于深度学习的大型语言模型，如GPT（Generative Pre-train…
阅读更多...
Java：MybatisPlus--条件构造器

Java：MybatisPlus--条件构造器

1、条件构造器类别 ①wrapper：抽象类，条件类的顶层，提供了一些获取和判断相关的方法。 ②AbstractWrapper：抽象类，Wrapper的子类，提供了所有的条件相关方法。 ③AbstractLambdaWrapper：抽象类…
阅读更多...
Tinymce富文本编辑器在vue项目中的使用；引入第三方插件和上传视频、图片等

Tinymce富文本编辑器在vue项目中的使用；引入第三方插件和上传视频、图片等

先放张效果图 第一步：安装依赖 npm install tinymce5.0.12 第二步：在项目中的public文件夹中新建tinymce文件夹（因为我的项目是脚手架创建的，所以公共文件夹是public）；在node_modules中找到skins文件夹复制…
阅读更多...
Java day11

Java day11

第11章 在用户界面上排列组件 11.1 基本的界面布局11.1.1 布置界面11.1.2 顺序布局11.1.3 方框布局11.1.4 网格布局11.1.5 边框布局 11.2 使用多个布局管理器11.3 卡片布局11.3.1 在应用程序中使用卡片布局11.3.2 单元格内边距和面板内边距 11.1 基本的界面布局 11.1.1 布置界…
阅读更多...
社科院与杜兰大学中外合作办学金融管理硕士项目——比起过往，前路更值得期待

社科院与杜兰大学中外合作办学金融管理硕士项目——比起过往，前路更值得期待

当结束一天工作陷入沉思时，你有没有特别遗憾的事情呢，人生有太多的不确定性，比起过往，未知的人生更值得我们期待。与其懊恼没完成的遗憾，不如珍惜当下，努力创造未来。人生没有太晚的开始，在职读…
阅读更多...
macOS设置环境变量和别名

macOS设置环境变量和别名

因为我的mac所用shell是bash，所以本文中涉及的环境变量和别名配置均在~/.zshrc文件中,且在每次配置完成后，需要执行source ~/.zshrc命令使配置文件生效 环境变量 通过配置环境变量，我们可以将某个路径暴露到全局，这样可以在全局…
阅读更多...
【C语言学习3——基本的C语言语法知识2】

【C语言学习3——基本的C语言语法知识2】

C语言学习3——基本的C语言语法知识 标识符关键词什么是字面常量？printf函数printf函数更多用法 #include命令 标识符 在前面的代码中，由我们自己命名，用于指代某一个实体的名称，例如:add，result，函数的参…
阅读更多...
android studio ImageView和ImageButton和Button

android studio ImageView和ImageButton和Button

1.ImageView 1.1代码显示 ImageView img findViewById(R.id.img); img.setImageResource(R.drawable.apple); 1.2XML <ImageViewandroid:layout_width"match_parent"android:layout_height"match_parent"android:id"id/img"android:src&qu…
阅读更多...
连接云服务器

连接云服务器

前言&#xff1a;相信看到这篇文章的小伙伴都或多或少有一些编程基础&#xff0c;懂得一些linux的基本命令了吧&#xff0c;本篇文章将带领大家服务器如何部署一个使用django框架开发的一个网站进行云服务器端的部署。 文章使用到的的工具 Python&#xff1a;一种编程语言&…
阅读更多...
实现开机动画和自定义Toolbar的高级写法

实现开机动画和自定义Toolbar的高级写法

需求是自定义一个Toolbar和全屏展示一个第一次激活App的开机动画 1自定义Toolbar的使用 1仍然是先将工程的theme.xml中设置成NoActionBar <resources xmlns:tools"http://schemas.android.com/tools"><!-- Base application theme. --><style name&…
阅读更多...
Spring原理学习（五）：一篇讲清楚动态代理（jdk和cglib）的使用、原理和源码

Spring原理学习（五）：一篇讲清楚动态代理（jdk和cglib）的使用、原理和源码

目录 一、jdk动态代理的基本使用 二、cglib动态代理的基本使用 2.1 方法一&#xff1a;method.invoke() 方法反射调用 2.2 方法二&#xff08;spring使用的这个方法&#xff09;&#xff1a; methodProxy.invoke() 2.3 方法三&#xff1a;methodProxy.invokeSuper() 三、…
阅读更多...
ChatGPT API接口使用+fine tune微调+prompt介绍

ChatGPT API接口使用+fine tune微调+prompt介绍

目录 1 接口调用1.1 生成key1.2 接口功能1.2.1 图片生成 (image generation)1.2.2 对话(chat)1.2.3 中文纠错 (Chinese Spelling Correct)1.2.4 关键词提取 &#xff08;keyword extract)1.2.5 抽取文本向量 (Embedding)1.2.6 微调 (fine tune) 2 如何写好prompt2.1分类任务2.2…
阅读更多...
Baumer工业相机堡盟工业相机如何联合BGAPISDK和OpenCVSharp实现图像的拉普拉斯算法增强（C#）

Baumer工业相机堡盟工业相机如何联合BGAPISDK和OpenCVSharp实现图像的拉普拉斯算法增强（C#）

Baumer工业相机堡盟工业相机如何联合BGAPISDK和OpenCVSharp实现图像的拉普拉斯算法增强&#xff08;C#&#xff09; Baumer工业相机Baumer工业相机使用图像算法增加图像的技术背景Baumer工业相机通过BGAPI SDK联合OpenCV使用图像增强算法1.引用合适的类文件2.BGAPI SDK在图像回…
阅读更多...
【C++】从string开始了解STL

【C++】从string开始了解STL

文章目录 1.初识STL1.什么是STL2.STL的版本3.STL的六大组件 2.string1.string类模板2.string类的构造函数3.string内部数据访问4.string的遍历5.string类的迭代器6.string的Capacity相关接口7.string的修改相关接口8.其他接口 1.初识STL 1.什么是STL STL(standard template l…
阅读更多...
openpnp - 顶部相机 - 辅助光(环形灯)的电路原理图

openpnp - 顶部相机 - 辅助光(环形灯)的电路原理图

文章目录 openpnp - 顶部相机 - 辅助光(环形灯)的电路原理图概述END openpnp - 顶部相机 - 辅助光(环形灯)的电路原理图 概述 同学帮我做的简易灯板设计不太合理, 发热量极大. 想看看商用的环形灯电路啥样的, 如果有可能, 自己做块灯板, 塞进商用环形灯外壳中. 拆解了一个环形…
阅读更多...
从TOP25榜单，看半导体之变

从TOP25榜单，看半导体之变

据SIA报告显示&#xff0c;2022年全球半导体销售额创历史新高达到5740亿美元。尽管2022年下半年&#xff0c;半导体市场出现了周期性的低迷&#xff0c;但其全年的销售额相较2021年增长了3.3%。 近日&#xff0c;市调机构Gartner发布了全球以及中国大陆TOP25名半导体厂商的排名…
阅读更多...
【软考数据库】第二章 程序语言基础知识

【软考数据库】第二章 程序语言基础知识

目录 2.1 程序设计语言的基本概念2.2 程序设计语言的基本成分2.3 编译程序基本原理 前言&#xff1a; 笔记来自《文老师软考数据库》教材精讲&#xff0c;精讲视频在b站&#xff0c;某宝都可以找到&#xff0c;个人感觉通俗易懂。 2.1 程序设计语言的基本概念 程序设计语言是…
阅读更多...
最新文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发 尧图建网站