mysql高效导入数据

前景

关于数据库导入数据，作为新手，我记得最初学习的时候，当时老师是让我们执行sql脚本的，也就是全都是insert 语句的那种。几万条数据把，插了好久emm，今天又遇到了这个问题，于是就特地去网上查找了方法。
附上一个链接：分析的也挺好的
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，但是如果真到了服务器上，这些都不适用了。所以还是用命令行把


使用命令导入：

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文件也是一个纯文本，用‘,’逗号自动分隔数据的那种

• wps：
  一开始我用的wps，可能是因为我对它不熟悉，在最后保存为csv文件导入的时候，发现mysql数据库查看是畸形的，意思就是表里面出现问题了。比如：
  
  类似于这样的。可能是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);

结果图：

小小想法

我觉得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，加油呢～我起步晚，需要多努力啊～