Mysql——》用load data命令把txt导入到Mysql

最新推荐文章于 2023-10-14 11:07:32 发布

小仙。

最新推荐文章于 2023-10-14 11:07:32 发布

阅读量586

点赞数

分类专栏： Mysql 文章标签： mysql load data txt

本文链接：https://blog.csdn.net/weixin_43453386/article/details/128232747

版权

Mysql 专栏收录该内容

105 篇文章 5 订阅

订阅专栏

一、语法

load data local infile "txt文件路径"
into table 表名 
fields terminated by "," 
lines terminated by "\n";

参数	描述
load data local infile “txt文件路径”	绝对路径（双/单引号包起来）
fields terminated by “,”	字段分隔符
lines terminated by “\n”;	行分隔符

1、表字段数>txt字段数

表字段	id,name,age
txt字段	id,name,address,age

load data local infile "c:/Users/xiaox/Desktop/test.txt" 
into table test
fields terminated by ","
lines terminated by "\n" 
(id,name,@abc,age);

@abc表示变量，使用变量作为占位符，导入时等于丢掉这列数据。

2、表字段数<txt字段数

表字段	id,name,age
txt字段	name,age

load data local infile "c:/Users/xiaox/Desktop/test.txt" 
into table test
fields terminated by ","
lines terminated by "\n" 
(name,age);

二、示例

C:\Users\xiaox>mysql -h localhost -u root -p xiaoxian --local-infile
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> load data local infile "c:/Users/xiaox/Desktop/test.txt" into table test (id,area);
Query OK, 2 rows affected, 4 warnings (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 4

mysql> load data local infile "c:/Users/xiaox/Desktop/test.txt" into table test fields terminated by ",";
Query OK, 0 rows affected, 2 warnings (0.01 sec)
Records: 2  Deleted: 0  Skipped: 2  Warnings: 2

mysql> load data local infile "c:/Users/xiaox/Desktop/test.txt" into table test fields terminated by "," (area,id);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0