guoadou
Don't cry because it came to an end.Smile because it happened.
博客园
首页
社区
新随笔
联系
订阅
管理
随笔-37 评论-68 文章-0 trackbacks-0
2008年12月8日
导出MySQL数据库结构的PHP脚本
SQLYog 每次只能导出一个DB的结构，一台服务器上DB太多，操作起来麻烦，花一小时写了这个脚本。
Code
<?
php
$host_name
=
'
localhost
'
;
$user_name
=
'
user_name
'
;
$password
=
'
password
'
;
$mysqli
=
new
mysqli(
$host_name
,
$user_name
,
$password
);
/*
check connection
*/
if
(
mysqli_connect_errno
()) {
printf
(
"
Connect failed: %s\n
"
,
mysqli_connect_error
());
exit
();
}
$arr_databases
=
get_databases();
echo
'
<pre>
'
;
foreach
(
$arr_databases
as
$db_name
) {
echo
"
/* {
$db_name
} */\n\n
"
;
echo
"
create database if not exists `{
$db_name
}` ;\n\n
"
;
$arr_tables
=
get_tables(
$db_name
);
foreach
(
$arr_tables
as
$tb_name
) {
$arr_table_info
=
get_table_create_info(
$db_name
,
$tb_name
);
$create_table_info
=
$arr_table_info
[
1
];
$create_table_info
=
str_replace
(
"
`{
$tb_name
}`
"
,
"
`{
$db_name
}`.`{
$tb_name
}`
"
,
$create_table_info
);
echo
"
/* {
$tb_name
} */\n\n
"
;
echo
"
{
$create_table_info
};\n\n
"
;
}
}
/*
close connection
*/
$mysqli
->
close();
function
get_databases(){
global
$mysqli
;
$query
=
"
SHOW DATABASES;
"
;
$result
=
$mysqli
->
query(
$query
);
$arr_databases
=
array
();
$arr_sys_db
=
array
(
'
information_schema
'
,
'
mysql
'
,
'
test
'
);
while
(
$row
=
$result
->
fetch_array(MYSQLI_NUM)) {
$db_name
=
$row
[
0
];
if
(
!
in_array
(
$db_name
,
$arr_sys_db
)) {
$arr_databases
[]
=
$db_name
;
}
}
$result
->
close();
return
$arr_databases
;
}
function
get_tables(
$db_name
){
global
$mysqli
;
$query
=
"
SHOW TABLES FROM {
$db_name
};
"
;
$result
=
$mysqli
->
query(
$query
);
$arr_tables
=
array
();
while
(
$row
=
$result
->
fetch_array(MYSQLI_NUM)) {
$tb_name
=
$row
[
0
];
$arr_tables
[]
=
$tb_name
;
}
$result
->
close();
return
$arr_tables
;
}
function
get_table_create_info(
$db_name
,
$tb_name
){
global
$mysqli
;
$query
=
"
SHOW CREATE TABLE `{
$db_name
}`.`{
$tb_name
}`;
"
;
$result
=
$mysqli
->
query(
$query
);
$arr_table_info
=
array
();
if
(
$row
=
$result
->
fetch_array(MYSQLI_NUM)) {
$arr_table_info
=
$row
;
}
$result
->
close();
return
$arr_table_info
;
}
?>
posted @ 2008-12-08 12:00 guoadou 阅读(161) 评论(0)
编辑
2008年12月8日
Code
