利用mysql和mysqli取得mysql的所有数据库和库中的所有表

时间:2022-08-11 08:26:53

功能:利用mysql和mysqli两种连接数据库的方法,取得Mysql服务器上的所有数据库和库中的所有表。

环境:自己用分开安装apache mysql php搭建的环境

操作系统是:windows 10

代码如下:

<?php
/**
* mysqli  op mysql demo
*/
/**
* 公用函数
*/

/**
* 功能:列出一个数据库的所有表
* @param $dbname
* @param $con
*/
function list_tables($dbname,$con)
   {
       mysql_select_db($dbname,$con) or die("选择数据库失败!");
       $res = mysql_query("SHOW TABLES");

       $tables = array();

       while($row = mysql_fetch_array($res))
       {
           echo  $row[0] .'<br>';
       }
       mysql_free_result($res);


//    return $tables;

   }

/**
* 利用msyql方法取得所有库的名字
*/
$mysqlCon = mysql_connect('localhost','root','2015');
$set = mysql_query('SHOW DATABASES;');
$dbs = array();
while($db = mysql_fetch_row($set))
   $dbs[] = $db[0];

//var_dump($dbs);
echo '<h1>这个是mysql操作输出的结果:</h1>';
//print_r($dbs);

/**
* 这个用来显示数据库名称
*/
foreach($dbs as $key=>$value){
  echo '<b>'.$key . "=>". $value .'</b><br>';
   list_tables($value,$mysqlCon);

}


echo '<h1>这个是msyqli操作输出的结果:</h1>';
$mysqliCon = mysqli_connect('localhost','root','2015');
$result = mysqli_query($mysqliCon,'show databases');
$dbs2 =array();
while($db =mysqli_fetch_row($result))
       $dbs2[] = $db[0];
print_r($dbs2);

结果如下:

这个是mysql操作输出的结果:

0=>information_schema
CHARACTER_SETS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
COLUMN_PRIVILEGES
ENGINES
EVENTS
FILES
GLOBAL_STATUS
GLOBAL_VARIABLES
KEY_COLUMN_USAGE
PARAMETERS
PARTITIONS
PLUGINS
PROCESSLIST
PROFILING
REFERENTIAL_CONSTRAINTS
ROUTINES
SCHEMATA
SCHEMA_PRIVILEGES
SESSION_STATUS
SESSION_VARIABLES
STATISTICS
TABLES
TABLESPACES
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TRIGGERS
USER_PRIVILEGES
VIEWS
INNODB_BUFFER_PAGE
INNODB_TRX
INNODB_BUFFER_POOL_STATS
INNODB_LOCK_WAITS
INNODB_CMPMEM
INNODB_CMP
INNODB_LOCKS
INNODB_CMPMEM_RESET
INNODB_CMP_RESET
INNODB_BUFFER_PAGE_LRU
1=>access_control
collection
collection2permission
permission
signup
user
user2collection
2=>db_bcty365
tb_bb
tb_bbqb
tb_bbs
tb_bccd
tb_bccdjj
tb_city
tb_cjwt
tb_dd
tb_leaveword
tb_reply
tb_sjxz
tb_soft
tb_tell
tb_type
tb_type_big
tb_type_small
tb_user
tb_xlh
3=>db_nethard
tb_member
tb_upfile
tb_uptype
4=>db_reglog
tb_member
5=>mysql
columns_priv
db
event
func
general_log
help_category
help_keyword
help_relation
help_topic
host
ndb_binlog_index
plugin
proc
procs_priv
proxies_priv
servers
slow_log
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user
6=>performance_schema
cond_instances
events_waits_current
events_waits_history
events_waits_history_long
events_waits_summary_by_instance
events_waits_summary_by_thread_by_event_name
events_waits_summary_global_by_event_name
file_instances
file_summary_by_event_name
file_summary_by_instance
mutex_instances
performance_timers
rwlock_instances
setup_consumers
setup_instruments
setup_timers
threads
7=>php_study
8=>phpbook
addressbook
9=>regi
member
10=>test
11=>world
city
country
countrylanguage

这个是msyqli操作输出的结果:

Array(    [0] => information_schema    [1] => access_control    [2] => db_bcty365    [3] => db_nethard    [4] => db_reglog    [5] => mysql    [6] => performance_schema    [7] => php_study    [8] => phpbook    [9] => regi    [10] => test    [11] => world)