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

小灰灰 2024-03-31 16:20 21阅读 0赞

## 一、语法 ##

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

<table> 
 <thead> 
  <tr> 
   <th align="left">参数</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">load data local infile “txt文件路径”</td> 
   <td align="left">绝对路径（双/单引号包起来）</td> 
  </tr> 
  <tr> 
   <td align="left">fields terminated by “,”</td> 
   <td align="left">字段分隔符</td> 
  </tr> 
  <tr> 
   <td align="left">lines terminated by “\n”;</td> 
   <td align="left">行分隔符</td> 
  </tr> 
 </tbody> 
</table>

### 1、表字段数>txt字段数 ###

<table> 
 <thead> 
  <tr> 
   <th>表字段</th> 
   <th>id,name,age</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>txt字段</td> 
   <td>id,name,address,age</td> 
  </tr> 
 </tbody> 
</table>

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字段数 ###

<table> 
 <thead> 
  <tr> 
   <th>表字段</th> 
   <th>id,name,age</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>txt字段</td> 
   <td>name,age</td> 
  </tr> 
 </tbody> 
</table>

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

## 三、错误 ##

### 1、ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version ###

[解决——》ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version][ERROR 1064 _42000_ You have an error in your SQL syntax_ check the manual that corresponds to your MySQL server version]

### 2、ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides ###

[解决——》ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides][ERROR 3948 _42000_ Loading local data is disabled_ this must be enabled on both the client and server sides]

### 3、ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access. ###

[解决——》ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.][ERROR 2068 _HY000_ LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.]

[ERROR 1064 _42000_ You have an error in your SQL syntax_ check the manual that corresponds to your MySQL server version]: https://blog.csdn.net/weixin_43453386/article/details/128232877
[ERROR 3948 _42000_ Loading local data is disabled_ this must be enabled on both the client and server sides]: https://blog.csdn.net/weixin_43453386/article/details/128232800
[ERROR 2068 _HY000_ LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.]: https://blog.csdn.net/weixin_43453386/article/details/128232950