发现自己之前写的php导出sql数据为Excel文件在导出一些数据的时候出现了精度的问题，比如导出身份证号的时候会把后面变成0000。暂时先把这个问题留下，有空去看看到底是什么问题。
写了一个导出sql的工具。稍微加了下界面，把功能也做多了点，简单来说，就是三个功能：
1.直接在浏览器上显示sql文件内容。
2.直接导出到服务器上，要求目录要可写。
3.直接下载到本地，脱库时感觉还不错。
好了直接贴上代码：
sqldump.php
<?
php
if
(
isset
(
$_REQUEST
['option'
])) {
define
('DOWNLOAD',
$_REQUEST
['option'
]);
}
else
{
define
('DOWNLOAD',0);
//
0代表直接显示,1代表下载,2代表导出在本地
}
if
(DOWNLOAD != 1
) {
header
("Content-type:text/html;charset=utf-8"
);
}
$cfg_dbhost
='localhost'
;
$cfg_dbname
='mysql'
;
$cfg_dbuser
='root'
;
$cfg_dbpwd
=''
;
$cfg_db_language
='utf8'
;
//
配置信息
$cfg_dbhost
=
isset
(
$_REQUEST
['dbhost'])?
$_REQUEST
['dbhost']:
$cfg_dbhost
;
$cfg_dbname
=
isset
(
$_REQUEST
['dbname'])?
$_REQUEST
['dbname']:
$cfg_dbname
;
$cfg_dbuser
=
isset
(
$_REQUEST
['dbuser'])?
$_REQUEST
['dbuser']:
$cfg_dbuser
;
$cfg_dbpwd
=
isset
(
$_REQUEST
['dbpwd'])?
$_REQUEST
['dbpwd']:
$cfg_dbpwd
;
$cfg_db_language
=
isset
(
$_REQUEST
['dbc'])?
$_REQUEST
['dbc']:
$cfg_db_language
;
$to_file_name
=
isset
(
$_REQUEST
['dbtable'])?
$_REQUEST
['dbtable'].".sql":
$cfg_dbname
.".sql"
;
if
(DOWNLOAD==2
) {
$to_file_name
=
isset
(
$_REQUEST
['dbtable'])?
dirname
(
__FILE__
).DIRECTORY_SEPARATOR.
$_REQUEST
['dbtable'].".sql":
dirname
(
__FILE__
).DIRECTORY_SEPARATOR.
$cfg_dbname
.".sql"
;
}
//
END 配置
//链接数据库
$link
= @
mysql_connect
(
$cfg_dbhost
,
$cfg_dbuser
,
$cfg_dbpwd
);
$link
==
null
?
die
('mysql connect error'):''
;
@
mysql_select_db
(
$cfg_dbname
);
//
选择编码
@
mysql_query
("set names ".
$cfg_db_language
);
//
数据库中有哪些表
$tabList
=
isset
(
$_REQUEST
['dbtable'])?
array
("{
$_REQUEST
['dbtable']}"):list_tables(
$cfg_dbname
);
$tabList
==
null
?
die
('no tables found'):''
;
if
(DOWNLOAD==1
) {
Header
("Content-type: application/octet-stream"
);
Header
("Accept-Ranges: bytes"
);
Header
("Content-Disposition: attachment; filename=".
$to_file_name
);
}
if
(DOWNLOAD==2
) {
echo
"正在导出...<hr/>"
;
}
$info
= "-- ----------------------------\r\n"
;
$info
.= "-- 备份日期：".
date
("Y-m-d H:i:s",
time
())."\r\n"
;
$info
.= "-- ----------------------------\r\n\r\n"
;
if
(DOWNLOAD==2
) {
file_put_contents
(
$to_file_name
,
$info
,
FILE_APPEND);
}
else
{
echo
$info
;
}
//
将每个表的表结构导出到文件
foreach
(
$tabList
as
$val
){
$sql
= "show create table ".
$val
;
$res
= @
mysql_query
(
$sql
,
$link
);
if
(
$res
==
null
) {
die
('table `'.
$val
.'` not EXISTS'
);
}
$row
= @
mysql_fetch_array
(
$res
);
$info
= "-- ----------------------------\r\n"
;
$info
.= "-- Table structure for `".
$val
."`\r\n"
;
$info
.= "-- ----------------------------\r\n"
;
$info
.= "DROP TABLE IF EXISTS `".
$val
."`;\r\n"
;
$sqlStr
=
$info
.
$row
[1].";\r\n\r\n"
;
if
(DOWNLOAD==2
) {
//
追加到文件
file_put_contents
(
$to_file_name
,
$sqlStr
,
FILE_APPEND);
}
else
{
echo
$sqlStr
;
}
//
释放资源
@
mysql_free_result
(
$res
);
}
//
将每个表的数据导出到文件
foreach
(
$tabList
as
$val
){
if
(DOWNLOAD==2
){
echo
"正在导出表`".
$val
."`...<br>"
;
}
$sql
= "select * from ".
$val
;
$res
= @
mysql_query
(
$sql
,
$link
);
//
如果表中没有数据，则继续下一张表
if
(@
mysql_num_rows
(
$res
)<1)
continue
;
//
$info
= "-- ----------------------------\r\n"
;
$info
.= "-- Records for `".
$val
."`\r\n"
;
$info
.= "-- ----------------------------\r\n"
;
if
(DOWNLOAD==2
) {
file_put_contents
(
$to_file_name
,
$info
,
FILE_APPEND);
}
else
{
echo
$info
;
}
//
读取数据
while
(
$row
= @
mysql_fetch_row
(
$res
)){
$sqlStr
= "INSERT INTO `".
$val
."` VALUES ("
;
foreach
(
$row
as
$zd
){
$sqlStr
.= "'".
$zd
."', "
;
}
//
去掉最后一个逗号和空格
$sqlStr
=
substr
(
$sqlStr
,0,
strlen
(
$sqlStr
)-2
);
$sqlStr
.= ");\r\n"
;
if
(DOWNLOAD==2
) {
file_put_contents
(
$to_file_name
,
$sqlStr
,
FILE_APPEND);
}
else
{
echo
$sqlStr
;
}
}
//
释放资源
@
mysql_free_result
(
$res
);
if
(DOWNLOAD==2
) {
file_put_contents
(
$to_file_name
,"\r\n",
FILE_APPEND);
}
else
{
echo
"\r\n"
;
}
}
if
(DOWNLOAD==2
){
echo
"<hr/>导出成功。"
;
}
//
echo "End!";
function
list_tables(
$database
)
{
$sql
='SHOW TABLES FROM '.
$database
;
$rs
=
mysql_query
(
$sql
);
$tables
=
array
();
while
(
$row
=
mysql_fetch_row
(
$rs
)) {
$tables
[] =
$row
[0
];
}
mysql_free_result
(
$rs
);
return
$tables
;
}
?>
然后是界面的东西了，为的是不让自己去记这个烦人的参数。
sqldumpclient.html
<
html
>
<
head
>
<
title
>
sqldump客户端
</
title
>
<
meta
charset
='utf-8'
>
<
script
type
="text/javascript"
>
function
setaction () {
$(
'
cform
'
).action
=
$(
'
act
'
).value;
inputs
=
document.getElementsByTagName(
'
input
'
);
for
(
var
i
=
1
;i
<
inputs.length
-
4
;i
++
){
if
(inputs[i].value
!=
''
) {
inputs[i].name
=
inputs[i].id;
}
}
}
function
$(id){
return
document.getElementById(id);
}
</
script
>
</
head
>
<
body
>
<
center
>
<
h1
align
="center"
>
PHP sql dump
</
h1
>
<
h3
align
="right"
>
Author:Medici.Yan@gmail.com
</
h3
>
<
form
method
="post"
id
='cform'
onsubmit
="setaction()"
>
<
fieldset
>
<
table
>
<
legend
>
配置
</
legend
>
<
tr
>
<
td
><
label
for
="act"
>
脚本地址:
</
label
></
td
>
<
td
><
input
type
="text"
id
='act'
placeholder
="http://www.example.com/sqldump.php"
/></
td
>
<
td
rowspan
="7"
width
="100"
>
使用方法：把sqldump.php传到服务器上,然后在这里填写相关参数，也可以直接访问脚本
</
td
>
</
tr
>
<
tr
>
<
td
><
label
for
="dbhost"
>
数据库地址:
</
label
></
td
>
<
td
><
input
type
="text"
id
="dbhost"
placeholder
="默认:localhost"
/></
td
>
</
tr
>
<
tr
>
<
td
><
label
for
="dbname"
>
数据库名:
</
label
></
td
>
<
td
><
input
type
="text"
id
="dbname"
placeholder
="默认:mysql"
/></
td
>
</
tr
>
<
tr
>
<
td
><
label
for
="dbuser"
>
用户名:
</
label
></
td
>
<
td
><
input
type
="text"
id
="dbuser"
placeholder
="默认:root"
/></
td
>
</
tr
>
<
tr
>
<
td
><
label
for
="dbpwd"
>
密码:
</
label
></
td
>
<
td
><
input
type
="text"
id
="dbpwd"
placeholder
="默认:<empty>"
/></
td
>
</
tr
>
<
tr
>
<
td
><
label
for
="dbc"
>
字符集:
</
label
></
td
>
<
td
>
<
input
list
="charlist"
id
="dbc"
placeholder
="默认:utf8"
/>
<
datalist
id
="charlist"
>
<
option
value
="utf8"
/>
<
option
value
="gbk"
/>
<
option
value
="gb2312"
/>
</
datalist
>
</
td
>
</
tr
>
<
tr
>
<
td
><
label
for
="dbtable"
>
表名:
</
label
></
td
>
<
td
><
input
type
="text"
id
="dbtable"
placeholder
="为空代表所有表"
/></
td
>
</
tr
>
<
tr
>
<
td
><
label
for
="option"
>
导出方式:
</
label
></
td
>
<
td
>
<
input
type
="radio"
id
="option"
name
="option"
value
="0"
checked
="checked"
>
浏览器显示
<
input
type
="radio"
id
="option"
name
="option"
value
="1"
>
下载到本地
<
input
type
="radio"
id
="option"
name
="option"
value
="2"
>
导出至服务器
</
td
>
</
tr
>
<
tr
>
<
td
><
input
type
="submit"
value
="开始"
/></
td
>
</
tr
>
</
table
>
</
fieldset
>
</
form
>
</
center
>
</
body
>
</
html
>
用法也很简单，把sqldump.php传到服务器上，然后在本地运行sqldumpclient.html，写上相关参数就OK。
