php读取txt文件组成SQL并插入数据库的方法

3. /**

4. * $splitChar 字段分隔符
5. * $file 数据文件文件名
6. * $table 数据库表名
7. * $conn 数据库连接
8. * $fields 数据对应的列名
9. * $insertType 插入操作类型，包括INSERT,REPLACE
10. * 搜集整理：bbs.it-home.org
11. */
12. function loadTxtDataIntoDatabase($splitChar,$file,$table,$conn,$fields=array(),$insertType='INSERT'){
13. if(empty($fields)) $head = "{$insertType} INTO `{$table}` VALUES('";
14. else $head = "{$insertType} INTO `{$table}`(`".implode('`,`',$fields)."`) VALUES('"; //数据头
15. $end = "')";
16. $sqldata = trim(file_get_contents($file));
17. if(preg_replace('/\s*/i','',$splitChar) == '') {
18. $splitChar = '/(\w+)(\s+)/i';
19. $replace = "$1','";
20. $specialFunc = 'preg_replace';
21. }else {
22. $splitChar = $splitChar;
23. $replace = "','";
24. $specialFunc = 'str_replace';
25. }
26. //处理数据体，二者顺序不可换，否则空格或Tab分隔符时出错
27. $sqldata = preg_replace('/(\s*)(\n+)(\s*)/i','\'),(\'',$sqldata); //替换换行
28. $sqldata = $specialFunc($splitChar,$replace,$sqldata); //替换分隔符
29. $query = $head.$sqldata.$end; //数据拼接
30. if(mysql_query($query,$conn)) return array(true);
31. else {
32. return array(false,mysql_error($conn),mysql_errno($conn));
33. }
34. }
35. //调用示例1
36. require 'db.php';
37. $splitChar = '|'; //竖线
38. $file = 'sqldata1.txt';
39. $fields = array('id','parentid','name');
40. $table = 'cengji';
41. $result = loadTxtDataIntoDatabase($splitChar,$file,$table,$conn,$fields);
42. if (array_shift($result)){
43. echo 'Success!
    ';
44. }else {
45. echo 'Failed!--Error:'.array_shift($result).'
    ';
46. }
47. /*sqlda ta1.txt
48. |0|A
49. |1|B
50. |1|C
51. |2|D
52. -- cengji
53. CREATE TABLE `cengji` (
54. `id` int(11) NOT NULL AUTO_INCREMENT,
55. `parentid` int(11) NOT NULL,
56. `name` varchar(255) DEFAULT NULL,
57. PRIMARY KEY (`id`),
58. UNIQUE KEY `parentid_name_unique` (`parentid`,`name`) USING BTREE
59. ) ENGINE=InnoDB AUTO_INCREMENT=1602 DEFAULT CHARSET=utf8
60. */
61. //调用示例2
62. require 'db.php';
63. $splitChar = ' '; //空格
64. $file = 'sqldata2.txt';
65. $fields = array('id','make','model','year');
66. $table = 'cars';
67. $result = loadTxtDataIntoDatabase($splitChar,$file,$table,$conn,$fields);
68. if (array_shift($result)){
69. echo 'Success!
    ';
70. }else {
71. echo 'Failed!--Error:'.array_shift($result).'
    ';
72. }
73. /* sqldata2.txt
74. Aston DB19 2009
75. Aston DB29 2009
76. Aston DB39 2009
77. -- cars
78. CREATE TABLE `cars` (
79. `id` int(11) NOT NULL AUTO_INCREMENT,
80. `make` varchar(16) NOT NULL,
81. `model` varchar(16) DEFAULT NULL,
82. `year` varchar(16) DEFAULT NULL,
83. PRIMARY KEY (`id`)
84. ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
85. */
86. //调用示例3
87. require 'db.php';
88. $splitChar = ' '; //Tab
89. $file = 'sqldata3.txt';
90. $fields = array('id','make','model','year');
91. $table = 'cars';
92. $insertType = 'REPLACE';
93. $result = loadTxtDataIntoDatabase($splitChar,$file,$table,$conn,$fields,$insertType);
94. if (array_shift($result)){
95. echo 'Success!
    ';
96. }else {
97. echo 'Failed!--Error:'.array_shift($result).'
    ';
98. }
99. /* sqldata3.txt
100. Aston DB19 2009
101. Aston DB29 2009
102. Aston DB39 2009
103. */
104. //调用示例3
105. require 'db.php';
106. $splitChar = ' '; //Tab
107. $file = 'sqldata3.txt';
108. $fields = array('id','value');
109. $table = 'notExist'; //不存在表
110. $result = loadTxtDataIntoDatabase($splitChar,$file,$table,$conn,$fields);
111. if (array_shift($result)){
112. echo 'Success!
     ';
113. }else {
114. echo 'Failed!--Error:'.array_shift($result).'
     ';
115. }

116. //附：db.php

117. /* //注释这一行可全部释放
118. ?>
119. static $connect = null;
120. static $table = 'jilian';
121. if(!isset($connect)) {
122. $connect = mysql_connect("localhost","root","");
123. if(!$connect) {
124. $connect = mysql_connect("localhost","Zjmainstay","");
125. }
126. if(!$connect) {
127. die('Can not connect to database.Fatal error handle by /test/db.php');
128. }
129. mysql_select_db("test",$connect);
130. mysql_query("SET NAMES utf8",$connect);
131. $conn = &$connect;
132. $db = &$connect;
133. }
134. ?>

代码如下:

3. //*/

4. 数据表结构

5. -- 数据表结构：
6. -- 100000_insert,1000000_insert
7. CREATE TABLE `100000_insert` (
8. `id` int(11) NOT NULL AUTO_INCREMENT,
9. `parentid` int(11) NOT NULL,
10. `name` varchar(255) DEFAULT NULL,
11. PRIMARY KEY (`id`)
12. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
13. 100000 （10万）行插入：Insert 100000_line_data use 2.5534288883209 seconds
14. 1000000（100万）行插入：Insert 1000000_line_data use 19.677318811417 seconds
15. //可能报错：MySQL server has gone away
16. //解决：修改my.ini/my.cnf max_allowed_packet=20M

作者：Zjmainstay