PHP读取SQL文件并导入数据库(示例)

3. /**

4. * 读取 sql 文件并写入数据库
5. * @version 1.01 demo.php
6. * @author xingshaocheng
7. * @edit: bbs.it-home.org
8. */
9. class DBManager
10. {
11. var $dbHost = '';
12. var $dbUser = '';
13. var $dbPassword = '';
14. var $dbSchema = '';
16. function __construct($host,$user,$password,$schema)
17. {
18. $this->dbHost = $host;
19. $this->dbUser = $user;
20. $this->dbPassword = $password;
21. $this->dbSchema = $schema;
22. }
24. function createFromFile($sqlPath,$delimiter = '(;\n)|((;\r\n))|(;\r)',$prefix = '',$commenter = array('#','--'))
25. {
26. //判断文件是否存在
27. if(!file_exists($sqlPath))
28. return false;
30. $handle = fopen($sqlPath,'rb');
32. $sqlStr = fread($handle,filesize($sqlPath));
34. //通过sql语法的语句分割符进行分割
35. $segment = explode(";",trim($sqlStr));
37. //var_dump($segment);
39. //去掉注释和多余的空行
40. foreach($segment as & $statement)
41. {
42. $sentence = explode("\n",$statement);
44. $newStatement = array();
46. foreach($sentence as $subSentence)
47. {
48. if('' != trim($subSentence))
49. {
50. //判断是会否是注释
51. $isComment = false;
52. foreach($commenter as $comer)
53. {
54. if(eregi("^(".$comer.")",trim($subSentence)))
55. {
56. $isComment = true;
57. break;
58. }
59. }
60. //如果不是注释，则认为是sql语句
61. if(!$isComment)
62. $newStatement[] = $subSentence;
63. }
64. }
66. $statement = $newStatement;
67. }
68. //对表名加前缀
69. if('' != $prefix)
70. {
73. //只有表名在第一行出现时才有效 例如 CREATE TABLE talbeName
75. $regxTable = "^[\`\'\"]{0,1}[\_a-zA-Z]+[\_a-zA-Z0-9]*[\`\'\"]{0,1}$";//处理表名的正则表达式
76. $regxLeftWall = "^[\`\'\"]发达";
78. $sqlFlagTree = array(
79. "CREATE" => array(
80. "TABLE" => array(
81. "$regxTable" => 0
82. )
83. ),
84. "INSERT" => array(
85. "INTO" => array(
86. "$regxTable" => 0
87. )
88. )
90. );
92. foreach($segment as & $statement)
93. {
94. $tokens = split(" ",$statement[0]);
96. $tableName = array();
97. $this->findTableName($sqlFlagTree,$tokens,0,$tableName);
99. if(empty($tableName['leftWall']))
100. {
101. $newTableName = $prefix.$tableName['name'];
102. }
103. else{
104. $newTableName = $tableName['leftWall'].$prefix.substr($tableName['name'],1);
105. }
107. $statement[0] = str_replace($tableName['name'],$newTableName,$statement[0]);
108. }
110. }
111. //组合sql语句
112. foreach($segment as & $statement)
113. {
114. $newStmt = '';
115. foreach($statement as $sentence)
116. {
117. $newStmt = $newStmt.trim($sentence)."\n";
118. }
120. $statement = $newStmt;
121. }
123. //用于测试------------------------
124. //var_dump($segment);
125. //writeArrayToFile('data.txt',$segment);
126. //-------------------------------
128. self::saveByQuery($segment);
130. return true;
131. }
133. private function saveByQuery($sqlArray)
134. {
135. $conn = mysql_connect($this->dbHost,$this->dbUser,$this->dbPassword);
137. mysql_select_db($this->dbSchema);
139. foreach($sqlArray as $sql)
140. {
141. mysql_query($sql);
142. }
143. mysql_close($conn);
144. }
146. private function findTableName($sqlFlagTree,$tokens,$tokensKey=0,& $tableName = array())
147. {
148. $regxLeftWall = "^[\`\'\"]发达";
150. if(count($tokens) return false;
152. if('' == trim($tokens[$tokensKey]))
153. {
154. return self::findTableName($sqlFlagTree,$tokens,$tokensKey+1,$tableName);
155. }
156. else
157. {
158. foreach($sqlFlagTree as $flag => $v)
159. {
160. if(eregi($flag,$tokens[$tokensKey]))
161. {
162. if(0==$v)
163. {
164. $tableName['name'] = $tokens[$tokensKey];
166. if(eregi($regxLeftWall,$tableName['name']))
167. {
168. $tableName['leftWall'] = $tableName['name']{0};
169. }
171. return true;
172. }
173. else{
174. return self::findTableName($v,$tokens,$tokensKey+1,& $tableName);
175. }
176. }
177. }
178. }
180. return false;
181. }
182. }

183. function writeArrayToFile($fileName,$dataArray,$delimiter="\r\n")

184. {
185. $handle=fopen($fileName, "wb");
187. $text = '';
189. foreach($dataArray as $data)
190. {
191. $text = $text.$data.$delimiter;
192. }
193. fwrite($handle,$text);
194. }

195. //测试

196. $dbM = new DBManager('localhost','w01f','123456','test');
197. $dbM->createFromFile('data.sql',null,'fff_');
198. ?>

复制代码