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

php中文网
发布: 2016-07-25 08:55:00
原创
1677人浏览过
  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. ?>
复制代码


PHP速学教程(入门到精通)
PHP速学教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载
来源:php中文网
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
最新问题
开源免费商场系统广告
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责申明 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号 技术交流群
PHP中文网订阅号
每天精选资源文章推送
PHP中文网APP
随时随地碎片化学习
PHP中文网抖音号
发现有趣的

Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号