Heim > Backend-Entwicklung > PHP-Tutorial > PHP读取SQL文件并导入数据库(示例)

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

WBOY
Freigeben: 2016-07-25 08:55:00
Original
1486 Leute haben es durchsucht
  1. /**

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

  149. {
  150. $handle=fopen($fileName, "wb");
  151. $text = '';
  152. foreach($dataArray as $data)
  153. {
  154. $text = $text.$data.$delimiter;
  155. }
  156. fwrite($handle,$text);
  157. }
  158. //测试

  159. $dbM = new DBManager('localhost','w01f','123456','test');
  160. $dbM->createFromFile('data.sql',null,'fff_');
  161. ?>
复制代码


Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage