PHP导出sql文件

发现自己之前写的php导出sql数据为Excel文件在导出一些数据的时候出现了精度的问题，比如导出身份证号的时候会把后面变成0000。暂时先把这个问题留下，有空去看看到底是什么问题。 写了一个导出sql的工具。稍微加了下界面，把功能也做多了点，简单来说，就是

发现自己之前写的php导出sql数据为Excel文件在导出一些数据的时候出现了精度的问题，比如导出身份证号的时候会把后面变成0000。暂时先把这个问题留下，有空去看看到底是什么问题。

写了一个导出sql的工具。稍微加了下界面，把功能也做多了点，简单来说，就是三个功能：

　　1.直接在浏览器上显示sql文件内容。

　　2.直接导出到服务器上，要求目录要可写。

　　3.直接下载到本地，脱库时感觉还不错。

好了直接贴上代码：

 sqldump.php

<span>php
    </span><span>if</span> (<span>isset</span>(<span>$_REQUEST</span>['option'<span>])) {
        </span><span>define</span>('DOWNLOAD', <span>$_REQUEST</span>['option'<span>]);
    }</span><span>else</span><span>{
        </span><span>define</span>('DOWNLOAD',0);<span>//</span><span>0代表直接显示,1代表下载,2代表导出在本地</span>
<span>    }
    </span><span>if</span> (DOWNLOAD != 1<span>) {
        </span><span>header</span>("Content-type:text/html;charset=utf-8"<span>);
    }
    </span><span>$cfg_dbhost</span> ='localhost'<span>;
    </span><span>$cfg_dbname</span> ='mysql'<span>;
    </span><span>$cfg_dbuser</span> ='root'<span>;
    </span><span>$cfg_dbpwd</span> =''<span>;
    </span><span>$cfg_db_language</span> ='utf8'<span>;

    </span><span>//</span><span>配置信息</span>
    <span>$cfg_dbhost</span> = <span>isset</span>(<span>$_REQUEST</span>['dbhost'])?<span>$_REQUEST</span>['dbhost']:<span>$cfg_dbhost</span><span>;
    </span><span>$cfg_dbname</span> = <span>isset</span>(<span>$_REQUEST</span>['dbname'])?<span>$_REQUEST</span>['dbname']:<span>$cfg_dbname</span><span>;
    </span><span>$cfg_dbuser</span> = <span>isset</span>(<span>$_REQUEST</span>['dbuser'])?<span>$_REQUEST</span>['dbuser']:<span>$cfg_dbuser</span><span>;
    </span><span>$cfg_dbpwd</span> = <span>isset</span>(<span>$_REQUEST</span>['dbpwd'])?<span>$_REQUEST</span>['dbpwd']:<span>$cfg_dbpwd</span><span>;
    </span><span>$cfg_db_language</span> = <span>isset</span>(<span>$_REQUEST</span>['dbc'])?<span>$_REQUEST</span>['dbc']:<span>$cfg_db_language</span><span>;
    
    </span><span>$to_file_name</span> =<span>isset</span>(<span>$_REQUEST</span>['dbtable'])?<span>$_REQUEST</span>['dbtable'].".sql":<span>$cfg_dbname</span>.".sql"<span>;

    </span><span>if</span> (DOWNLOAD==2<span>) {
        </span><span>$to_file_name</span> =<span>isset</span>(<span>$_REQUEST</span>['dbtable'])?<span>dirname</span>(<span>__FILE__</span>).DIRECTORY_SEPARATOR.<span>$_REQUEST</span>['dbtable'].".sql":<span>dirname</span>(<span>__FILE__</span>).DIRECTORY_SEPARATOR.<span>$cfg_dbname</span>.".sql"<span>;
    }
    </span><span>//</span><span> END 配置

    //链接数据库</span>
    <span>$link</span> = @<span>mysql_connect</span>(<span>$cfg_dbhost</span>,<span>$cfg_dbuser</span>,<span>$cfg_dbpwd</span><span>);
    </span><span>$link</span>==<span>null</span>?<span>die</span>('mysql connect error'):''<span>;
    @</span><span>mysql_select_db</span>(<span>$cfg_dbname</span><span>);
    </span><span>//</span><span>选择编码</span>
    @<span>mysql_query</span>("set names ".<span>$cfg_db_language</span><span>);
    </span><span>//</span><span>数据库中有哪些表</span>
    <span>$tabList</span> = <span>isset</span>(<span>$_REQUEST</span>['dbtable'])?<span>array</span>("{<span>$_REQUEST</span>['dbtable']}"):list_tables(<span>$cfg_dbname</span><span>);
    </span><span>$tabList</span>==<span>null</span>?<span>die</span>('no tables found'):''<span>;
    </span><span>if</span> (DOWNLOAD==1<span>) {
        </span><span>Header</span>("Content-type: application/octet-stream"<span>);
        </span><span>Header</span>("Accept-Ranges: bytes"<span>);
        </span><span>Header</span>("Content-Disposition: attachment; filename=".<span>$to_file_name</span><span>);
    }
    </span><span>if</span> (DOWNLOAD==2<span>) {
        </span><span>echo</span> "正在导出...<hr>"<span>;
    }
    </span><span>$info</span> = "-- ----------------------------\r\n"<span>;
    </span><span>$info</span> .= "-- 备份日期：".<span>date</span>("Y-m-d H:i:s",<span>time</span>())."\r\n"<span>;
    </span><span>$info</span> .= "-- ----------------------------\r\n\r\n"<span>;
    </span><span>if</span> (DOWNLOAD==2<span>) {
        </span><span>file_put_contents</span>(<span>$to_file_name</span>,<span>$info</span>,<span>FILE_APPEND);
    }</span><span>else</span><span>{
        </span><span>echo</span> <span>$info</span><span>;
    }
    </span><span>//</span><span>将每个表的表结构导出到文件</span>
    <span>foreach</span>(<span>$tabList</span> <span>as</span> <span>$val</span><span>){
        </span><span>$sql</span> = "show create table ".<span>$val</span><span>;
        </span><span>$res</span> = @<span>mysql_query</span>(<span>$sql</span>,<span>$link</span><span>);
        </span><span>if</span> (<span>$res</span>==<span>null</span><span>) {
            </span><span>die</span>('table `'.<span>$val</span>.'` not EXISTS'<span>);
        }
        </span><span>$row</span> = @<span>mysql_fetch_array</span>(<span>$res</span><span>);
        </span><span>$info</span> = "-- ----------------------------\r\n"<span>;
        </span><span>$info</span> .= "-- Table structure for `".<span>$val</span>."`\r\n"<span>;
        </span><span>$info</span> .= "-- ----------------------------\r\n"<span>;
        </span><span>$info</span> .= "DROP TABLE IF EXISTS `".<span>$val</span>."`;\r\n"<span>;
        </span><span>$sqlStr</span> = <span>$info</span>.<span>$row</span>[1].";\r\n\r\n"<span>;
        </span><span>if</span> (DOWNLOAD==2<span>) {
            </span><span>//</span><span>追加到文件</span>
            <span>file_put_contents</span>(<span>$to_file_name</span>,<span>$sqlStr</span>,<span>FILE_APPEND);
        }</span><span>else</span><span>{
            </span><span>echo</span> <span>$sqlStr</span><span>;
        }
        </span><span>//</span><span>释放资源</span>
        @<span>mysql_free_result</span>(<span>$res</span><span>);
    }

    </span><span>//</span><span>将每个表的数据导出到文件</span>
    <span>foreach</span>(<span>$tabList</span> <span>as</span> <span>$val</span><span>){
        </span><span>if</span>(DOWNLOAD==2<span>){
            </span><span>echo</span> "正在导出表`".<span>$val</span>."`...<br>"<span>;
        }
        </span><span>$sql</span> = "select * from ".<span>$val</span><span>;
        </span><span>$res</span> = @<span>mysql_query</span>(<span>$sql</span>,<span>$link</span><span>);
        </span><span>//</span><span>如果表中没有数据，则继续下一张表</span>
        <span>if</span>(@<span>mysql_num_rows</span>(<span>$res</span>)continue<span>;
        </span><span>//
</span>        <span>$info</span> = "-- ----------------------------\r\n"<span>;
        </span><span>$info</span> .= "-- Records for `".<span>$val</span>."`\r\n"<span>;
        </span><span>$info</span> .= "-- ----------------------------\r\n"<span>;
        </span><span>if</span> (DOWNLOAD==2<span>) {
            </span><span>file_put_contents</span>(<span>$to_file_name</span>,<span>$info</span>,<span>FILE_APPEND);
        }</span><span>else</span><span>{
            </span><span>echo</span> <span>$info</span><span>;
        }
        </span><span>//</span><span>读取数据</span>
        <span>while</span>(<span>$row</span> = @<span>mysql_fetch_row</span>(<span>$res</span><span>)){
            </span><span>$sqlStr</span> = "INSERT INTO `".<span>$val</span>."` VALUES ("<span>;
            </span><span>foreach</span>(<span>$row</span> <span>as</span> <span>$zd</span><span>){
                </span><span>$sqlStr</span> .= "'".<span>$zd</span>."', "<span>;
            }
            </span><span>//</span><span>去掉最后一个逗号和空格</span>
            <span>$sqlStr</span> = <span>substr</span>(<span>$sqlStr</span>,0,<span>strlen</span>(<span>$sqlStr</span>)-2<span>);
            </span><span>$sqlStr</span> .= ");\r\n"<span>;
            </span><span>if</span> (DOWNLOAD==2<span>) {
                </span><span>file_put_contents</span>(<span>$to_file_name</span>,<span>$sqlStr</span>,<span>FILE_APPEND);
            }</span><span>else</span><span>{
                </span><span>echo</span> <span>$sqlStr</span><span>;
            }
        }
        </span><span>//</span><span>释放资源</span>
        @<span>mysql_free_result</span>(<span>$res</span><span>);
        </span><span>if</span> (DOWNLOAD==2<span>) {
            </span><span>file_put_contents</span>(<span>$to_file_name</span>,"\r\n",<span>FILE_APPEND);
        }</span><span>else</span><span>{
            </span><span>echo</span> "\r\n"<span>;
        }
    }
    </span><span>if</span>(DOWNLOAD==2<span>){
        </span><span>echo</span> "<hr>导出成功。"<span>;
    }
</span><span>//</span><span>    echo "End!";</span>
    <span>function</span> list_tables(<span>$database</span><span>)
    {
        </span><span>$sql</span>='SHOW TABLES FROM '.<span>$database</span><span>;
        </span><span>$rs</span> = <span>mysql_query</span>(<span>$sql</span><span>);
        </span><span>$tables</span> = <span>array</span><span>();
        </span><span>while</span> (<span>$row</span> = <span>mysql_fetch_row</span>(<span>$rs</span><span>)) {
            </span><span>$tables</span>[] = <span>$row</span>[0<span>];
        }
        </span><span>mysql_free_result</span>(<span>$rs</span><span>);
        </span><span>return</span> <span>$tables</span><span>;
    }

</span>?>

然后是界面的东西了，为的是不让自己去记这个烦人的参数。

sqldumpclient.html

<span><span>html</span><span>></span>
<span><span>head</span><span>></span>
<span><span>title</span><span>></span>sqldump客户端<span></span><span>title</span><span>></span>
<span><span>meta </span><span>charset</span><span>='utf-8'</span><span>></span>
<span><span>script </span><span>type</span><span>="text/javascript"</span><span>></span>
    <span>function</span><span> setaction () {
        $(</span><span>'</span><span>cform</span><span>'</span><span>).action</span><span>=</span><span>$(</span><span>'</span><span>act</span><span>'</span><span>).value;
        inputs</span><span>=</span><span>document.getElementsByTagName(</span><span>'</span><span>input</span><span>'</span><span>);
        </span><span>for</span><span>(</span><span>var</span><span> i </span><span>=</span><span>1</span><span>;i</span><span><span>inputs.length</span><span>-</span><span>4</span><span>;i</span><span>++</span><span>){
            </span><span>if</span><span> (inputs[i].value</span><span>!=</span><span>''</span><span>) {
                inputs[i].name</span><span>=</span><span>inputs[i].id;
            }
        }

    }
    </span><span>function</span><span> $(id){
        </span><span>return</span><span> document.getElementById(id);
    }
</span><span></span><span>script</span><span>></span>
<span></span><span>head</span><span>></span>
<span><span>body</span><span>></span>
<span><span>center</span><span>></span>    
<span><span>h1 </span><span>align</span><span>="center"</span><span>></span>PHP sql dump<span></span><span>h1</span><span>></span>
<span><span>h3 </span><span>align</span><span>="right"</span><span>></span>Author:Medici.Yan@gmail.com<span></span><span>h3</span><span>></span>
    <span><span>form </span><span>method</span><span>="post"</span><span> id</span><span>='cform' </span><span>onsubmit</span><span>="setaction()"</span><span>></span>
            <span><span>fieldset</span><span>></span>
            <span><span>table</span><span>></span>
                <span><span>legend</span><span>></span>配置<span></span><span>legend</span><span>></span>
                <span><span>tr</span><span>></span>
                <span><span>td</span><span>><span>label </span><span>for</span><span>="act"</span><span>></span>脚本地址:<span></span><span>label</span><span>></span><span>td</span><span>></span>
                <span><span>td</span><span>><span>input </span><span>type</span><span>="text"</span><span> id</span><span>='act' </span><span>placeholder</span><span>="http://www.example.com/sqldump.php"</span><span>/></span><span>td</span><span>></span>
                <span><span>td </span><span>rowspan</span><span>="7"</span><span> width</span><span>="100"</span><span>></span>使用方法：把sqldump.php传到服务器上,然后在这里填写相关参数，也可以直接访问脚本<span></span><span>td</span><span>></span>
                <span></span><span>tr</span><span>></span>
                <span><span>tr</span><span>></span>
                <span><span>td</span><span>><span>label </span><span>for</span><span>="dbhost"</span><span>></span>数据库地址:<span></span><span>label</span><span>></span><span>td</span><span>></span>
                <span><span>td</span><span>><span>input </span><span>type</span><span>="text"</span><span> id</span><span>="dbhost"</span><span> placeholder</span><span>="默认:localhost"</span><span>/></span><span>td</span><span>></span>
                <span></span><span>tr</span><span>></span>
                <span><span>tr</span><span>></span>
                <span><span>td</span><span>><span>label </span><span>for</span><span>="dbname"</span><span>></span>数据库名:<span></span><span>label</span><span>></span><span>td</span><span>></span>
                <span><span>td</span><span>><span>input </span><span>type</span><span>="text"</span><span> id</span><span>="dbname"</span><span> placeholder</span><span>="默认:mysql"</span><span>/></span><span>td</span><span>></span>
                <span></span><span>tr</span><span>></span>
                <span><span>tr</span><span>></span>
                <span><span>td</span><span>><span>label </span><span>for</span><span>="dbuser"</span><span>></span>用户名:<span></span><span>label</span><span>></span><span>td</span><span>></span>
                <span><span>td</span><span>><span>input </span><span>type</span><span>="text"</span><span> id</span><span>="dbuser"</span><span> placeholder</span><span>="默认:root"</span><span>/></span><span>td</span><span>></span>
                <span></span><span>tr</span><span>></span>
                <span><span>tr</span><span>></span>
                <span><span>td</span><span>><span>label </span><span>for</span><span>="dbpwd"</span><span>></span>密码:<span></span><span>label</span><span>></span><span>td</span><span>></span>
                <span><span>td</span><span>><span>input </span><span>type</span><span>="text"</span><span> id</span><span>="dbpwd"</span><span> placeholder</span><span>="默认:<empty>"</empty></span><span>/></span><span>td</span><span>></span>
                <span></span><span>tr</span><span>></span>
                <span><span>tr</span><span>></span>
                <span><span>td</span><span>><span>label </span><span>for</span><span>="dbc"</span><span>></span>字符集:<span></span><span>label</span><span>></span><span>td</span><span>></span>
                    <span><span>td</span><span>></span>
                    <span><span>input </span><span>list</span><span>="charlist"</span><span> id</span><span>="dbc"</span><span> placeholder</span><span>="默认:utf8"</span> <span>/></span>
                        <span><span>datalist </span><span>id</span><span>="charlist"</span><span>></span>
                            <span><span>option </span><span>value</span><span>="utf8"</span> <span>/></span>
                            <span><span>option </span><span>value</span><span>="gbk"</span> <span>/></span>
                            <span><span>option </span><span>value</span><span>="gb2312"</span> <span>/></span>
                        <span></span><span>datalist</span><span>></span>    
                    <span></span><span>td</span><span>></span>
                <span></span><span>tr</span><span>></span>
                <span><span>tr</span><span>></span>
                <span><span>td</span><span>><span>label </span><span>for</span><span>="dbtable"</span><span>></span>表名:<span></span><span>label</span><span>></span><span>td</span><span>></span>
                <span><span>td</span><span>><span>input </span><span>type</span><span>="text"</span><span> id</span><span>="dbtable"</span><span> placeholder</span><span>="为空代表所有表"</span><span>/></span><span>td</span><span>></span>
                <span></span><span>tr</span><span>></span>
                <span><span>tr</span><span>></span>
                <span><span>td</span><span>><span>label </span><span>for</span><span>="option"</span><span>></span>导出方式:<span></span><span>label</span><span>></span><span>td</span><span>></span>
                <span><span>td</span><span>></span>
                <span><span>input </span><span>type</span><span>="radio"</span><span> id</span><span>="option"</span><span> name</span><span>="option"</span><span> value</span><span>="0"</span><span> checked</span><span>="checked"</span><span>></span><span>浏览器显示
                </span><span><span>input </span><span>type</span><span>="radio"</span><span> id</span><span>="option"</span><span> name</span><span>="option"</span><span> value</span><span>="1"</span><span>></span><span>下载到本地
                </span><span><span>input </span><span>type</span><span>="radio"</span><span> id</span><span>="option"</span><span> name</span><span>="option"</span><span> value</span><span>="2"</span><span>></span><span>导出至服务器
                </span><span></span><span>td</span><span>></span>
                <span></span><span>tr</span><span>></span>
                <span><span>tr</span><span>></span>
                <span><span>td</span><span>><span>input </span><span>type</span><span>="submit"</span><span> value</span><span>="开始"</span> <span>/></span><span>td</span><span>></span>
                <span></span><span>tr</span><span>></span>
                <span></span><span>table</span><span>></span>
            <span></span><span>fieldset</span><span>></span>
    <span></span><span>form</span><span>></span>
<span></span><span>center</span><span>></span>
<span></span><span>body</span><span>></span>
<span></span><span>html</span><span>></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

用法也很简单，把sqldump.php传到服务器上，然后在本地运行sqldumpclient.html，写上相关参数就OK。