mysql 导入txt文件及问题解决

目录

• 设置
• 命令
• 编码错误处理

设置

登录时需采用如下命令，不然可能报错

LOAD DATA LOCAL INFILE file request rejected due to restrictions on access

采用的登录命令为

mysql -u root -p --local-infile

同时登录后输入命令

set global local_infile = 1;

命令

即可采用如下命令进行导入txt文件，注意先选择数据库，以及在相应目录下运行mysql，同时注意windows txt文件换行符为"\r\n",linux为"\n"

load data local infile "1.txt" into table Student 
fields terminated by ' ' #属性分隔符  
lines terminated by "\r\n"; #行分隔符

编码错误处理

如遇到报错，Invalid utf8mb4 character string: ‘’，记得查看导入的txt文件是否为utf-8编码，还未解决可采用如下命令进行导入

load data local infile "1.txt" into table Student 
CHARACTER SET utf8mb4 
fields terminated by ' ' #属性分隔符  
lines terminated by "\r\n"; #行分隔符