根据字段关键字mobile 和 phone 查询所有带关键字的表
$sql="SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like ('%mobile%') or COLUMN_NAME like ('%phone%')
AND TABLE_SCHEMA='hawx'";
根据表先查询一条记录,然后循环匹配phone和mobile字段。知道表明和字段名,就可以查询自己想要的数据了。
完整代码如下
<?php
//获取库里所有带有mobile和phone字段的所有表
$sql="SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like ('%mobile%') or COLUMN_NAME like ('%phone%')
AND TABLE_SCHEMA='hawx'";
$res_data=getsql($sql);
$data=[];
//循环所有表
foreach($res_data as $v){
$table =$v['TABLE_NAME'];
$sql = "select * from $table limit 1";
$info=getsql($sql);
$sql = "select count(*) as count from $table";
$count=getsql($sql);
if($count){
$count=$count[0]['count'];
}else{
$count=0;
}
if(!$info){
continue;
}
$info=array_keys($info[0]);
foreach($info as $val){
if( stripos($val,'mobile') !== false || stripos($val,'phone') !== false ){
$row=[];
$filed=$val;
$row['count']=$count;
$row['table']=$table;
$row['filed']=$filed;
$sql = "select $filed from $table where $filed > 1 limit 1";
$res=getsql($sql);
if($res){
$row['val']=$res[0][$filed];
}else{
$row['val']='';
}
$sql="select COLUMN_NAME,COLUMN_COMMENT from information_schema.COLUMNS where table_name =" . "'" . $table . "' and COLUMN_NAME ='" . $filed . "'";
$res=getsql($sql);
$row['zhushi']=$res[0]['COLUMN_COMMENT'];
$data[]=$row;
}
}
}
$html='';
foreach($data as $k=>$v){
$html.="<tr><td >".$k."</td>
<td>".$v['table']."</td>
<td >".$v['filed']."</td>
<td>".$v['zhushi']."</td>
<td>".$v['val']."</td>
<td>".$v['count']."</td>
</tr>";
}
$html=<<<EOF
<!DOCTYPE html>
<head>
<style>
body {
font-size: 16px;
color: #000;
background-color: #fff;
text-align: center;
}
.bg_blue {
background-color: #98d7fa;
}
</style>
</head>
<body>
<div style="width:100%;">
<div style="width:100%;margin-top:20px">
<table border="1" style="width:100%;border-collapse: collapse;table-layout: fixed;">
<tr >
<td colspan="6" style="width:100%;overflow: hidden;border: 1px;">
hwx
</td>
</tr>
<tr >
<td class="bg_blue">序号</td>
<td class="bg_blue">表名</td>
<td class="bg_blue">字段名</td>
<td class="bg_blue">字段注释</td>
<td class="bg_blue">字段存储数据情况</td>
<td class="bg_blue">表数据总量</td>
</tr>
$html
</table>
</div>
</body>
</html>
EOF;
echo $html;
function getsql($sql){
if($sql){
$serve='';
$username='';
$password='';
$dbname='';
$link=mysqli_connect($serve,$username,$password,$dbname);
$result=mysqli_query($link,$sql);
$res_data=mysqli_fetch_all($result, MYSQLI_ASSOC );
return $res_data;
}else{
return [];
}
}
执行结果如下