mysql高效导入数据

爱被打了一巴掌 2022-05-31 00:48 236阅读 0赞

*  前景
    
     *  问题如何快速将数据导入mysql
        
         *  初探
         *  提升
         *  尝试
            
             *  纯文本导入
             *  excel导入
         *  小小想法
         *  总结

# 前景 #

关于数据库导入数据，作为新手，我记得最初学习的时候，当时老师是让我们执行sql脚本的，也就是全都是insert 语句的那种。几万条数据把，插了好久emm，今天又遇到了这个问题，于是就特地去网上查找了方法。  
附上一个链接：分析的也挺好的  
[http://blog.csdn.net/caoxiaohong1005/article/details/72570147][http_blog.csdn.net_caoxiaohong1005_article_details_72570147]

## 问题：如何快速将数据导入mysql ##

### 初探 ###

一开始看见网上的建议是，可以用一条insert语句连续查几个值，这样能提高速度。就是insert into table xxx values( )填入多组数据的值,但是如果这样的话，写脚本会是一个比较烦的问题不是吗？及时能够通过编程简化这个过程但是还是不太好。速度上提升了一些。

--------------------

### 提升 ###

之后我找到了一种直接导入文件的方法：在mysql中可以使用：

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char' ]
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...)]

LOAD DATA INFILE语句用于高速地从一个**文本文件中**读取行，并装入一个表中。

*  关键字理解：(摘录)
 *  LOW\_PRIORITY & CONCURRENT
    
     *  LOW\_PRIORITY: LOAD DATA语句的执行被延迟，直到没有其它的客户端从表中读取为止.
     *  CONCURRENT :如果一个MyISAM表满足同时插入的条件（即该表在中间有空闲块），LOADDATA正在执行时，其它线程会从表中重新获取数据。即使没有其它线程在同时使用本表格使用本选项也会略微影响LOAD DATA的性能。
 *  LOCAL :
    
     *  如果指定了LOCAL，则被认为与连接的客户端有关,如果指定了LOCAL，则文件会被客户主机上的客户端读取，并被发送到服务器。文件会被给予一个完整的路径名称，以指定确切的位置。如果给定的是一个相对的路径名称，则此名称会被理解为相对于启动客户端时所在的目录,使用了LOCAL 参数，其实会比直接操作数据库文件慢，毕竟每次都会通过mysql客户端来处理文件，再发送给sever 处理。
     *  如果LOCAL没有被指定，则文件必须位于服务器主机上，并且被服务器直接读取。当在服务器主机上为文件定位时，服务器使用以下规则：
        
        > 1.  如果给定了一个绝对的路径名称，则服务器使用此路径名称。
        > 2.  如果给定了带有一个或多个引导组件的相对路径名称，则服务器会搜索相对于服务器数据目录的文件。
        > 3.  如果给定了一个不带引导组件的文件名称，则服务器会在默认数据库的数据库目录中寻找文件。
 *  REPLACE & IGNORE
    
     *  如果您指定了REPLACE，则输入行会替换原有行（换句话说，与原有行一样，对一个主索引或唯一索引具有相同值的行）
     *  如果您指定IGNORE，则把原有行复制到唯一关键字值的输入行被跳过
     *  如果您这两个选项都不指定，则运行情况根据LOCAL关键词是否被指定而定。不使用LOCAL时，当出现重复关键字值时，会发生错误，并且剩下的文本文件被忽略。使用LOCAL时，默认的运行情况和IGNORE被指定时的情况相同；这是因为在运行中间，服务器没有办法中止文件的传输。如果您希望在载入运行过程中忽略外键的限制，您可以在执行LOAD DATA前发送一个  
        SET FOREIGN\_KEY\_CHECKS=0语句
 *  LOAD DATA INFILE &SELECT…INTO OUTFILE
    
     *  要从一个表中把数据写入一个文件中，应使用SELECT…INTO OUTFILE。
     *  要读取文件，放回到表中，应使用LOAD DATA INFILE。
 *  FIELDS（控制数据格式）和LINES（控制行格式）
 *  子句的语法对于两个语句是一样的。两个子句都是自选的，但是如果两个都被指定了，FIELDS必须位于LINES的前面。两条语句需要选择其中一条，两条默认执行语句如下：
    
     *  `FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'`
     *  `LINES TERMINATED BY '\n' STARTING BY '`

例子：

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test LINES STARTING BY "xxx";

> xxx”row”,1  
> something xxx”row”,2  
> 得到数据(“row”,1)和(“row”,2)。

--------------------

### 尝试 ###

#### 纯文本导入 ####

介于上面说的是纯文本格式嘛，我就自己手动写了一个txt文件，然后导入进去，效果如下：总想着用mysql workbench，但是如果真到了服务器上，这些都不适用了。所以还是用命令行把  
![这里写图片描述][SouthEast]  
![这里写图片描述][SouthEast 1]  
使用命令导入：

load data local infile '/home/dream/桌面/111.txt' ignore into table employee fields terminated by ',' enclosed by '\'' lines terminated by '\n' (id, city, address1);

可以看出这是完全可以的，一个高效的方法。但是我想着似乎平时里面大家用的更多的应该是excel表格把。于是我就想着把excel表格里面的内容导进去。

--------------------

#### excel导入 ####

PS:CSV文件也是一个纯文本，用‘,’逗号自动分隔数据的那种  
![这里写图片描述][SouthEast 2]

*  wps：  
    一开始我用的wps，可能是因为我对它不熟悉，在最后保存为csv文件导入的时候，发现mysql数据库查看是畸形的，意思就是表里面出现问题了。比如：  
    ![这里写图片描述][SouthEast 3]  
    类似于这样的。可能是wps的问题把。不太清楚
 *  ubuntu自带的excel工具  
    于是我又尝试了ubuntu自带的excel工具，成功了。如下：

load data local infile '/home/dream/桌面/333.csv' into table employee fields terminated by ',' lines terminated by '\n' (id, city , address1);

结果图：  
![这里写图片描述][SouthEast 4]

### 小小想法 ###

我觉得excel表格来写数据是不错的选择因为，方便把。我自己写了一个小python脚本来进行操作，来写txt文件的：刚学- -勿喷。。。

f = open(r'/home/dream/桌面/111.txt', 'w')
    while True:
        ID = input('输入你的id:\n')
        if ID == '0':
            break
        city = input('输入你的city:\n')
        address = input('输入你的address：\n')
        f.write("'{0}','{1}','{2}'\n".format(ID, city, address))
    f.close()

代码很简单。就是手动输入那么值，其余的txt自动会写，算是一个很low的自动化把。哈哈哈贻笑大方了。今天刚学了点文件，这么看来，似乎编程就是一个工具啦～

### 总结 ###

今天主要是学习了，数据库快速导入数据的知识，只因为自己好奇心，然后就玩了一天QAQ，这应该是DBA大哥干的事情，不过作为想干后端的我，对数据库的一些用法还是不能少的啊～一不小心就入了mysql的坑，等再学学，就要去干flask 开发咯～这块的小知识就这么记下来把～以后绝对有用啊！理论+实践才是王道呢～最近还要补TCP HTTP，加油呢～我起步晚，需要多努力啊～

[http_blog.csdn.net_caoxiaohong1005_article_details_72570147]: http://blog.csdn.net/caoxiaohong1005/article/details/72570147
[SouthEast]: /images/20220531/7e06584da7d44cd0b7071a1472957600.png
[SouthEast 1]: /images/20220531/5158c2bd2e244738ac950f6a7f41ae72.png
[SouthEast 2]: /images/20220531/76c5b2f7b50f4cb5b4f98e0e44cc92b0.png
[SouthEast 3]: /images/20220531/91ffa78958214c9393368ce029423e19.png
[SouthEast 4]: /images/20220531/b77c66830d9f403280e6f9c30a2ad812.png