场景
刚刚在项目中用到了删除包含某个关键字内容,需要检索整个数据库的所有表、所有字段的值。这里记录一下PHP的方法。
思路
- 分析数据库结构,然后将所有
varchar
类型的字段取出来 - 用
or
语句拼接所有可用字段,并搜索关键词 - 打印出对应的表的对应字段
PHP代码实例
function search_all_db( $search_key ){ $tables = db_sql_query("show tables"); foreach($tables as $t){ sort($t); $all_tables[] = $t[0]; } foreach($all_tables as $t){ $columns = db_sql_query("show columns from $t"); $columns_arr = array(); foreach($columns as $c){ if(substr($c['Type'], 0, 7) != 'varchar'){continue;} $columns_arr[] = '`' . $c['Field'] . '`' . " like '%$search_key%'"; } if(empty($columns_arr)){continue;} $sql = "SELECT * from $t where " . implode(' or ', $columns_arr); $rs = db_sql_query($sql); //echo $sql . "</br>"; if($rs){ $and_arr = array(); foreach($columns_arr as $c){ $sql = "SELECT * from $t where $c"; $s = db_sql_query($sql); if($s){ $and_arr[] = str_replace(' like ', ' not like ', $c); //echo $sql . "</br>"; } } if(!empty($and_arr)){ $sql = "SELECT * from $t where " . implode(' and ', $and_arr); $s = db_sql_query($sql); if(!empty($s)){ echo str_replace('SELECT *', 'DELETE', $sql) . ";</br>"; } } //echo $sql . "</br>"; }else{ //echo $t . "</br>"; } } //print_r($all_tables); } search_all_db();
db_sql_query
为查询数据库的方法类,这里不做详细说明。使用时请自行修改代码。