3.Mysql数据库——向表内导入文件数据

小鱼儿 2023-06-19 05:23 4阅读 0赞

## Mysql数据库——向表内导入文件数据 ##

1.创建一个表student：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70][]

2.查看local\_infile服务是否开启：

对于服务器来说，默认开启，对于客户端，默认关闭。

show variables like 'local_infile';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 1][]

3.如果关闭状态，打开此服务。

set global local_infile=on;

![20191204162223317.png][]

4.再次检查此服务状态

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 2][]

5.载入数据

(1)win7利用语法直接导入csv文件即可。

load data local infile "C:/Users/wwb/Desktop/学生表.csv" into table student
    fields terminated by ',' lines terminated by '\n';

![20191204163627831.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 3][]

(2)对于win10系统，由于安全权限问题，登陆mysql服务时需要如下登陆才能导入数据文件。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 4][]

再次执行导入语法即可正确导入。

load data local infile "C:/Users/wwb/Desktop/学生表.csv" into table student
    fields terminated by ',' lines terminated by '\n';

![20191204172158616.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 5][]

6.如果数据存在表头，需要忽略第一行表头。

load data local infile "C:/Users/wwb/Desktop/学生表.csv" into table student
    fields terminated by ',' lines terminated by '\n' ignore 1 lines;

\->>>关键字:ignore num lines【忽略指定的行数】

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 6][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191204161823784.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191204162046693.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[20191204162223317.png]: https://img-blog.csdnimg.cn/20191204162223317.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191204162346400.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[20191204163627831.png]: https://img-blog.csdnimg.cn/20191204163627831.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/2019120416365589.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20191204163845843.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[20191204172158616.png]: https://img-blog.csdnimg.cn/20191204172158616.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20191204172220377.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20191204172454170.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70