Mysql load data的使用
数据库中,最常见的写入数据方式是通过SQL INSERT来写入,另外就是通过备份文件恢复数据库,这种备份文件在MySQL中是SQL脚本,实际上执行的还是在批量INSERT语句。
在实际中,常常会遇到两类问题:一类是数据导入,比如从word、excel表格或者txt文档导入数据(这些数据一般来自于非技术人员通过OFFICE工具录入的文档);一类数据交换,比如从MySQL、Oracle、DB2数据库之间的数据交换。
这其中就面临一个问题:数据库SQL脚本有差异,SQL交换比较麻烦。但是几乎所有的数据库都支持文本数据导入(LOAD)导出(EXPORT)功能。利用这一点,就可以解决上面所提到的数据交换和导入问题。
MySQL的LOAD DATAINFILE语句用于高速地从一个文本文件中读取行,并装入一个表中。文件名称必须为一个文字字符串。下面以MySQL5为例说明,说明如何使用MySQL的LOADDATA命令实现文本数据的导入。
注意:这里所说的文本是有一定格式的文本,比如说,文本分行,每行中用相同的符号隔开文本等等。等等,获取这样的文本方法也非常的多,比如可以把word、excel表格保存成文本,或者是一个csv文件。
在项目中,使用的环境是快速上传一个csv文件,原系统中是使用的db2数据库,然后调用了与mysql的loaddata相似的一个函数sysproc.db2load。但是loaddata在mysql的存储过程是不能使用的。采取的方法时在java代码中调用此方法。
实现的例子:
准备测试表
SQL如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
USE test; CREATE TABLE `test` ( `id` int (10) UNSIGNED NOT NULL AUTO_INCREMENT, `a` int (11) NOT NULL , `b` bigint (20) UNSIGNED NOT NULL , `c` bigint (20) UNSIGNED NOT NULL , `d` int (10) UNSIGNED NOT NULL , `e` int (10) UNSIGNED NOT NULL , `f` int (10) UNSIGNED NOT NULL , PRIMARY KEY (`id`), KEY `a_b` (`a`, `b`) ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARSET = utf8 |
Java代码如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
|
package com.seven.dbTools.DBTools; import org.apache.log4j.Logger; import org.springframework.jdbc.core.JdbcTemplate; import java.io.ByteArrayInputStream; import java.io.InputStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import javax.sql.DataSource; /** * @author seven * @since 07.03.2013 */ public class BulkLoadData2MySQL { private static final Logger logger = Logger.getLogger(BulkLoadData2MySQL. class ); private JdbcTemplate jdbcTemplate; private Connection conn = null ; public void setDataSource(DataSource dataSource) { this .jdbcTemplate = new JdbcTemplate(dataSource); } public static InputStream getTestDataInputStream() { StringBuilder builder = new StringBuilder(); for ( int i = 1 ; i <= 10 ; i++) { for ( int j = 0 ; j <= 10000 ; j++) { builder.append( 4 ); builder.append( "\t" ); builder.append( 4 + 1 ); builder.append( "\t" ); builder.append( 4 + 2 ); builder.append( "\t" ); builder.append( 4 + 3 ); builder.append( "\t" ); builder.append( 4 + 4 ); builder.append( "\t" ); builder.append( 4 + 5 ); builder.append( "\n" ); } } byte [] bytes = builder.toString().getBytes(); InputStream is = new ByteArrayInputStream(bytes); return is; } /** * * load bulk data from InputStream to MySQL */ public int bulkLoadFromInputStream(String loadDataSql, InputStream dataStream) throws SQLException { if (dataStream == null ) { logger.info( "InputStream is null ,No data is imported" ); return 0 ; } conn = jdbcTemplate.getDataSource().getConnection(); PreparedStatement statement = conn.prepareStatement(loadDataSql); int result = 0 ; if (statement.isWrapperFor(com.mysql.jdbc.Statement. class )) { com.mysql.jdbc.PreparedStatement mysqlStatement = statement.unwrap(com.mysql.jdbc.PreparedStatement. class ); mysqlStatement.setLocalInfileInputStream(dataStream); result = mysqlStatement.executeUpdate(); } return result; } public static void main(String[] args) { String testSql = "LOAD DATA LOCAL INFILE 'sql.csv' IGNORE INTO TABLE test.test (a,b,c,d,e,f)" ; InputStream dataStream = getTestDataInputStream(); BulkLoadData2MySQL dao = new BulkLoadData2MySQL(); try { long beginTime = System.currentTimeMillis(); int rows = dao.bulkLoadFromInputStream(testSql, dataStream); long endTime = System.currentTimeMillis(); logger.info( "importing " + rows + " rows data into mysql and cost " + (endTime - beginTime) + " ms!" ); } catch (SQLException e) { e.printStackTrace(); } System.exit( 1 ); } } |
提示:
例子中的代码使用setLocalInfileInputStream方法,会直接忽略掉文件名称,而直接将IO流导入到数据库中。在实际的实现中也可以把文件上传到服务器,然后读文件再导入文件,此时load data的local参数应该去掉,并且文件名应该是完整的绝对路径的名字。
最后附上LOAD DATA INFILE语法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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,...]] |
总结
LOADDATA是一个很有用的命令,从文件中导入数据比insert语句要快,MySQL文档上说要快20倍左右。但是命令的选项很多,然而大多都用不到,如果真的需要,用的时候看看官方文档即可。