MySQL导入txt文件（Navicat 8 for MySQL）

水深无声 2022-05-27 06:29 339阅读 0赞

我们为了方便，经常会把数据保存成txt文本文件，为了更好的维护数据，txt文本文件需要保存到数据库中。本例以MySQL数据库为例，通过Navicat 8 for MySQL实现导入TXT文件，当然以可以直接用MySQL命令实现。

## 工具/原料    ##

*  电脑
 *  MySQL,Navicat 8 for MySQL,TXT

## 方法/步骤    ##

1.  1
    
    安装好MySQL和Navicat 8 for MySQL，本例省略。
    
    通过Navicat 8 for MySQL创建数据库test。
    
    [![MySQL导入txt文件（Navicat 8 for MySQL）][MySQL_txt_Navicat 8 for MySQL]][MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL]
2.  2
    
    在数据库test上创建测试数据表student(主键ID，姓名，年龄，学号):
    
    create table student ( id int not null primary key, name varchar(50) not null, age int, xuehao varchar(20))
    
    [![MySQL导入txt文件（Navicat 8 for MySQL）][MySQL_txt_Navicat 8 for MySQL 1]][MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 1]
3.  3
    
    创建测试数据txt文件student.txt。id name age xuehao1 fengqingyang 88 20141223012 linghuchong 25 20141223023 renyingying 23 20141223034 renwoxing 86 20141223045 xiangwentian 54 2014122305
    
    注意：数据间用tab键分隔
    
    [![MySQL导入txt文件（Navicat 8 for MySQL）][MySQL_txt_Navicat 8 for MySQL 2]][MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 2]
4.  4
    
    右键test下面的表，点击“导入向导”
    
    [![MySQL导入txt文件（Navicat 8 for MySQL）][MySQL_txt_Navicat 8 for MySQL 3]][MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 3]
5.  弹窗中选择默认的“文字文件”，点击“下一步”
    
    [![MySQL导入txt文件（Navicat 8 for MySQL）][MySQL_txt_Navicat 8 for MySQL 4]][MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 4]
6.  弹窗中在“导入从”中选择student.txt文件，点击“下一步”
    
    [![MySQL导入txt文件（Navicat 8 for MySQL）][MySQL_txt_Navicat 8 for MySQL 5]][MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 5]
    
    [![MySQL导入txt文件（Navicat 8 for MySQL）][MySQL_txt_Navicat 8 for MySQL 6]][MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 6]
7.  按默认值，点击“下一步”
    
    [![MySQL导入txt文件（Navicat 8 for MySQL）][MySQL_txt_Navicat 8 for MySQL 7]][MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 7]
8.  弹窗中，栏位名默认填1，表示第一行是字段，第一个数据列填2，表示数据从第二行开始，最后一个数据列默认为空即可。点击“下一步”。
    
    [![MySQL导入txt文件（Navicat 8 for MySQL）][MySQL_txt_Navicat 8 for MySQL 8]][MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 8]
9.  按默认值不变（目标表可以自行下拉选择或手动设置），下一步
    
    [![MySQL导入txt文件（Navicat 8 for MySQL）][MySQL_txt_Navicat 8 for MySQL 9]][MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 9]
10. 默认不变，下一步
    
    [![MySQL导入txt文件（Navicat 8 for MySQL）][MySQL_txt_Navicat 8 for MySQL 10]][MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 10]
11. 默认不变，下一步
    
    [![MySQL导入txt文件（Navicat 8 for MySQL）][MySQL_txt_Navicat 8 for MySQL 11]][MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 11]
12. 点击“开始”
    
    [![MySQL导入txt文件（Navicat 8 for MySQL）][MySQL_txt_Navicat 8 for MySQL 12]][MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 12]
13. 数据导入成功
    
    [![MySQL导入txt文件（Navicat 8 for MySQL）][MySQL_txt_Navicat 8 for MySQL 13]][MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 13]
14. 查看数据表数据，是否成功
    
    [![MySQL导入txt文件（Navicat 8 for MySQL）][MySQL_txt_Navicat 8 for MySQL 14]][MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 14]
15.

[MySQL_txt_Navicat 8 for MySQL]: https://imgsa.baidu.com/exp/w=500/sign=0e779441a486c91708035239f93c70c6/962bd40735fae6cde034dbd80cb30f2443a70f82.jpg
[MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL]: /images/20220527/f32a3ed9c2fe4c9f9ab32ca2416103b1.png
[MySQL_txt_Navicat 8 for MySQL 1]: https://imgsa.baidu.com/exp/w=500/sign=ed6fe5bd5a82b2b7a79f39c401accb0a/95eef01f3a292df5acd5d1acbf315c6034a87370.jpg
[MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 1]: /images/20220527/374ae11247ce4879aac1fbd831840492.png
[MySQL_txt_Navicat 8 for MySQL 2]: https://imgsa.baidu.com/exp/w=500/sign=e01a110bb1b7d0a27bc9049dfbee760d/f11f3a292df5e0fed40bf4b85f6034a85edf7273.jpg
[MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 2]: /images/20220527/b24e64445bb04a5dbc332afb098ddfff.png
[MySQL_txt_Navicat 8 for MySQL 3]: https://imgsa.baidu.com/exp/w=500/sign=542c25ab4c4a20a4311e3cc7a0539847/342ac65c10385343ecc8fa989013b07eca808807.jpg
[MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 3]: /images/20220527/f2f67244801f4c68b2ad8bf8a9b7a5c5.png
[MySQL_txt_Navicat 8 for MySQL 4]: https://imgsa.baidu.com/exp/w=500/sign=fa7b3e4fa4efce1bea2bc8ca9f50f3e8/a9d3fd1f4134970a5ada6d9696cad1c8a6865dd9.jpg
[MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 4]: /images/20220527/e8e4bfd63ef74847a55c6e3d66627909.png
[MySQL_txt_Navicat 8 for MySQL 5]: https://imgsa.baidu.com/exp/w=500/sign=1943e1658913632715edc233a18ea056/6159252dd42a28343c074bb258b5c9ea14cebfe1.jpg
[MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 5]: /images/20220527/3635aab8d3d84fc998e0a029d911eeb3.png
[MySQL_txt_Navicat 8 for MySQL 6]: https://imgsa.baidu.com/exp/w=500/sign=1bf2954d52da81cb4ee683cd6267d0a4/42a98226cffc1e173c1ef1884990f603728de9e2.jpg
[MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 6]: /images/20220527/e4c8cab72a214b6da2cca51158d0a4e0.png
[MySQL_txt_Navicat 8 for MySQL 7]: https://imgsa.baidu.com/exp/w=500/sign=99f33f804836acaf59e096fc4cd88d03/5d6034a85edf8db1990f00dd0a23dd54564e7461.jpg
[MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 7]: /images/20220527/1ac86ccfe84d48df99efbb43741c2e3c.png
[MySQL_txt_Navicat 8 for MySQL 8]: https://imgsa.baidu.com/exp/w=500/sign=56b7682d452309f7e76fad12420f0c39/11385343fbf2b2118aeef8f7c98065380dd78e42.jpg
[MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 8]: /images/20220527/bc35cd7917e947688d5761a42be726b2.png
[MySQL_txt_Navicat 8 for MySQL 9]: https://imgsa.baidu.com/exp/w=500/sign=12203a04aa773912c4268561c8188675/f603918fa0ec08fab51e6f1e5aee3d6d55fbda69.jpg
[MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 9]: /images/20220527/f32a3ed9c2fe4c9f9ab32ca2416103b1.png0
[MySQL_txt_Navicat 8 for MySQL 10]: https://imgsa.baidu.com/exp/w=500/sign=5583fa579f82d158bb8259b1b00b19d5/9345d688d43f879420483497d11b0ef41ad53ad7.jpg
[MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 10]: /images/20220527/f32a3ed9c2fe4c9f9ab32ca2416103b1.png1
[MySQL_txt_Navicat 8 for MySQL 11]: https://imgsa.baidu.com/exp/w=500/sign=42b4ce88a28b87d65042ab1f37092860/21a4462309f79052698a3b870ff3d7ca7acbd5f6.jpg
[MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 11]: /images/20220527/f32a3ed9c2fe4c9f9ab32ca2416103b1.png2
[MySQL_txt_Navicat 8 for MySQL 12]: https://imgsa.baidu.com/exp/w=500/sign=4c921326a70f4bfb8cd09e54334e788f/9f2f070828381f3027b4f110aa014c086f06f0de.jpg
[MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 12]: /images/20220527/f32a3ed9c2fe4c9f9ab32ca2416103b1.png3
[MySQL_txt_Navicat 8 for MySQL 13]: https://imgsa.baidu.com/exp/w=500/sign=85b43b870ff3d7ca0cf63f76c21ebe3c/b17eca8065380cd78cb5de54a244ad34588281db.jpg
[MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 13]: /images/20220527/f32a3ed9c2fe4c9f9ab32ca2416103b1.png4
[MySQL_txt_Navicat 8 for MySQL 14]: https://imgsa.baidu.com/exp/w=500/sign=0631a0d9362ac65c67056673cbf3b21d/4ec2d5628535e5dddba7d96675c6a7efcf1b62fb.jpg
[MySQL_txt_Navicat 8 for MySQL_MySQL_txt_Navicat 8 for MySQL 14]: /images/20220527/f32a3ed9c2fe4c9f9ab32ca2416103b1.png5