如何使用Navicat导入Excel数据到数据库(Mysql为例)

ゝ一纸荒年。 2022-12-30 13:56 889阅读 0赞

### 一、 背景 ###

有时候我们需要将Excel中的数据导入到数据库表里，从而更好地使用SQL进行复杂查询以满足我们的统计需求

### 二、环境 ###

这里以MySQL为例，其他数据库也差不多，使用的环境是：

*  测试的平台：windows
 *  Navicat版本：Premium 12.1.12 (64-bit)
 *  数据库：mysql，5.7

PS：以上这些应该是不管是win还是mac，Navicat的版本应该跟我有差异，或者mysql版本跟我有差异，或者数据库类型不同，应该也是可以成功导入的。这里列出详细的版本仅仅是为了尽到告知。

### 三、如何用Navicat导入Excel到Mysql？ ###

##### 1、 提前建好表 #####

提前按照Excel的数据，建好表。注意字符类型的字段的编码，保证是utf8或者utf8mb4。**需要注意**的是，表可能是utf8或者utf8mb4编码，但字段可能不是，要确保字段也是这个编码。

> 一般情况下字段的字符集会继承表默认的字符集，而表默认的字符集继承数据库默认的字符集。如果数据库默认的字符集一开始就是正确的，那后续就会正确。如果一开始库的字符集不正确，建的表的字符集就错误，导致字段的字符集错误，如果此时修改表的字符集是没有用的，因为字段还是错的字符集。

注意：如果是非utf8或者utf8mb4编码，例如latin1编码会导致导入内容含有中文时失败，如果是其他非utf8和utf8mb4编码可能能够导入但是出现中文乱码。

##### 2、最好确保Excel有表头 #####

比如最好第一行是表头，而不是直接就是数据(最佳实践)

##### 3、开始导入 #####

*  选择要到入的表，点击下一步

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70][]

*  选择类型 Excel 文件，点下一步  
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 1][]
 *  选择Excel文件的位置，点下一步
    
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 2][]
 *  勾选要导入数据的Sheet才能下一步(如果Excel文件有多个sheet会出现多个让你选择)，点下一步
    
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 3][]
    
    下一步后见到如下这个图  
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 4][]
 *  调整数据格式等，调整好后点下一步
    
    如果导入的字段有日期信息，按照要求调整，例如日期是按照年月日时分秒排列的，选择 YMD，日期分隔符根据实际选择`/` 或者 `-`，所导入的Excel数据的第一行一般为表头
    
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 5][]
 *  选择要导入的表，之后下一步
    
    目标表就是说我们要导入到什么表，my\_billing是我们提前就建好的，所以 “新建表” 复选框是不选中的状态并且也无法被选中  
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 6][]
    
    (下图，展示了，如果将目标表改名，改一个库中不存在的名字，则 “新建表” 会自动勾选上了并且也无法取消勾选，应该是会在导入的时候自动创建表，**但是不推荐自动创建表的方式，原因是字段类型可能无法控制**)  
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 7][]
 *  配置Excel中的字段和库表字段的映射关系，配置好后点击下一步
    
    点击 “目标字段” 会出现下拉框，提供了目标表的字段，可以配置Excel中的表字段要导入到目标表的哪个字段中  
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 8][]
    
    (下图，出现了候选字段)  
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 9][]
    
    (下图，配置好所有字段的映射关系后即可点击下一步)  
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 10][]
 *  选择导入的模式，点击下一步
    
    导入的模式有追加数据和清除数据全新导入，按自己需求选择(\*\*PS：\*\*这里不知道为什么有些选项是灰色的，我遇到过没有置灰的情况)
    
    (下图，有些选项置灰，不知道为什么)  
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 11][]
    
    (下图，某些时候又全部选项可以选择)  
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 12][]
 *  点击开始
    
    (先出现一切ready的界面，等待你点击 “开始”)  
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 13][]
    
    (下图，点击 “开始”，可以查看到报告，处理数，新增数，更新数，删除数，失败数(Errors))  
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 14][]

### 可能遇到的问题 ###

##### 1、导入失败，或乱码 #####

前面的 "提前建好表"已经提到了，是编码的问题。尤其要注意检查字段的编码，不光是 “我表的编码都对了为何还是导入失败”(因为可能你刚开始建的表是Latin1编码的，后来将表改成utf8或utf8mb4，但是改了之后字段还是Latin1)

(下图，就是因为编码问题导致的错误，`Incorrent string value:'\xE4xB8\xAD\xE6\x96\x87'`)  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 15][]

### 补充 ###

*  自动映射字段
    
    如果Excel的表头的值，和数据库字段的值一样，**能够自动设置好字段映射关系**，如下图  
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 16][]
 *  建表时的字段选择
    
    以mysql为例，浮点数最好用decimal类型，用float和double可能会丢失精度，或者varchar字串类型也行(就是处理数字的时候不太方便)，比如decimal(30, 2)表示 `允许2个小数点，整数部分可以是 30-2=28位`

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70]: /images/20221120/61c54c669dc84d659d54b5b103e7d776.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201226093157342.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20201226093405811.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20201226093552258.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/2020122609374212.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20201226094105748.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20201226094211195.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20201226094630455.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20201226094838958.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20201226094907271.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/20201226095010814.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 11]: https://img-blog.csdnimg.cn/20201226095309731.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 12]: https://img-blog.csdnimg.cn/20201226095421585.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 13]: https://img-blog.csdnimg.cn/20201226095600198.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 14]: https://img-blog.csdnimg.cn/20201226095723857.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 15]: https://img-blog.csdnimg.cn/20201226100506777.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg_size_16_color_FFFFFF_t_70 16]: https://img-blog.csdnimg.cn/20201226100748814.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c4eTU2Zg==,size_16,color_FFFFFF,t_70