将Excel导入MySQL，并解决乱码问题_xsl文件导入mysql中文乱码

前文

  工作中经常需要将业务方的数据导入数据库，而这两者通常就是Excel和MySQL。MySQL本身对文件导入导出支持还是比较充分的，但碰到乱码、导入格式不对就比较糟心！这里就提供解决思路和方案！

从Excel导入MySQL

  首先确定要导入的Excel各个字段，那么我们定义demo.xlsx这么一个Excel表，里面的测试数据如下：

  导入MySQL步骤如下：

1. 将Excel另存为txt格式：demo.txt
2. 将txt格式里的列名删除，只保存数据
3. 根据Excel 列名创建数据表，假如命名为demo；
4. 执行：load data local infile “/Users/yourname/Desktop/demo.txt” into table demo fields terminated by “\t” lines terminated by “\r\n”;
5. 此时Excel就导入成为了新的表，接下来根据需求可以这样操作：
   1.如果是插入另一张表，则：insert into target_table select * from demo;
   2.如果需要导出给其他数据库，则：mysqldump -uroot -p --database test --tables demo > /Users/yourname/Desktop/20190716.sql

提示：上面的路径要注意是你存放文件的路径！
  那按照上面的操作一步步开始，先另存为txt格式，mac下有4中txt格式，如图，每一种都命名的比较清晰易懂，那因为数据库一般是utf8的，所以我们就先取第一种UTF-16的，这两个都属于unicode字符集，区别是utf8是1-4字节，utf16是2或4个字节编码。
  这里也尝试了其他三种，发现存下来的中文都是乱码。

  ok，保存后我们打开txt文件，这里我已经把字段名删了，可以提前在Excel文件里删除！

  接着创建demo表，满足三个字段：

create database test charset utf8;  #创建库
use test
create table demo(name varchar(32),age int,gender varchar(8));
1
2
3

  这样我们就创建了一个在test下的demo表，肯定有人会觉得还要建表好麻烦，不过实际工作中一般都是有表的情况下录入数据的，所以这时候可以采用这条语句copy表结构：

create table demo1 like demo;  #这样就照着demo的表结构复制到demo1
1

  接着我们把数据导入MySQL，demo.txt我放在桌面，语句如下：

load data local infile "/Users/yourname/Desktop/demo.txt" into table demo fields terminated by "\t" lines terminated by "\r\n";
1

结果如下：

可以看到只有1条录入了，然后有3条警告，警告内容：

如图出现了乱码：’\xFF\xFE\x0F\xA2~’，导致数据录入错误！查出的结果也是不知甚解：

解决导入乱码问题

  在解决问题前，我们得先搞懂为什么？亦或是，MySQL是如何处理字符字节编码的？可以看我之前写的这篇：MySQL进阶必备知识(一)：CS架构、配置文件、字符集等等。截图部分：

  按照上面说的，我查的我MySQL三个参数都是utf8，并且Mac也是类unix系统，所以操作系统默认是utf8，但仍旧编码错误！但如果你是window系统，你就要好好看下set_client是否是gbk，因为window默认操作系统是gbk！如果你是其他的类unix系统，就需要看下这三个参数是否保持一致！
  所以问题就出在了我们保存的txt格式上，我们保存的是utf16，这里可以用命令file来再次确认！
  看到文件是utf-16编码，所以我们把其转换为utf-8即可。

find *.txt -exec sh -c "iconv -f UTF-16 -t UTF8 {} > {}.txt" \;  （回车键）
1

然后删除demo.txt并把demo.txt.txt重新命名为demo.txt！接着删除数据表的乱码值，并重新导入！

delete from demo;
load data local infile "/Users/yourname/Desktop/demo.txt" into table demo fields terminated by "\t" lines terminated by "\r\n";
1
2

  可以看到成功导入！随后可以插入其他表也可以将sql语句导出！

总结

  当我们理解了MySQL的字符集原理后，出现的问题也就随之而解了，所以要多探究一些深层的原理！