获取某数据库中含有某字段的所有表的脚本

最新推荐文章于 2024-07-18 02:07:56 发布

weixin_34117211

最新推荐文章于 2024-07-18 02:07:56 发布

阅读量212

点赞数

文章标签：数据库 php python

原文链接：https://my.oschina.net/u/3412738/blog/1548751

版权

2019独角兽企业重金招聘Python工程师标准>>>

背景

在某些情况下，比如业务重构时，需要对涉及到某字段的代码进行修改，则需要获取到某数据库中所有含有该字段的数据表．

代码

<?php

$dbhost = 'ip';
$dbusername = 'username';
$dbpass = 'password';
$dbname = 'databasename';

$db_connect = new mysqli($dbhost, $dbusername, $dbpass, $dbname);

// 获取数据库中各个表的基础信息
$sql = 'SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_SCHEMA="'.$dbname.'"';

$result = $db_connect->query($sql);

while ($row = mysqli_fetch_assoc($result)) {
	$tables[] = $row;
}

//取出所有表的名称
$tables = array_column($tables, 'TABLE_NAME');

//要查询的字段名
$name = 'abc';
//含有该字段名的表名称数组
$needs = array();
// 对各个表做遍历，获取含有该字段的表名称
foreach ($tables as $table) {
    $sql = 'select '.$name.' from '.$table;
    if ($result = $db_connect->query($sql)) {
    	$needs[] = $table;
    }
}

return $needs;

测试

以某数据库和某字段为样例进行测试，返回结果如下

/opt/wwwroot/test/dbcolume.php:34:
array(32) {
  [0] =>
  string(20) "account_organization"
  [1] =>
  string(21) "app_recruit_interview"
  [2] =>
  string(26) "campus_recruit_interviewer"
  [3] =>
  string(18) "campus_recruit_log"
  [4] =>
  string(35) "campus_recruit_site_address_element"
  [5] =>
  string(33) "campus_recruit_site_group_element"
  [6] =>
  string(24) "campus_recruit_site_plan"
  [7] =>
  string(32) "campus_recruit_site_time_element"
  [8] =>
  string(8) "employee"
  [9] =>
  string(34) "interviewer_remark_template_config"
  [10] =>
  string(18) "operation_integral"
  [11] =>
  string(17) "position_projects"
  [12] =>
  string(11) "preemployee"
  [13] =>
  string(7) "recruit"
  [14] =>
  string(19) "recruit_config_lock"
  [15] =>
  string(17) "recruit_interview"
  [16] =>
  string(25) "recruit_interview_history"
  [17] =>
  string(24) "recruit_interview_status"
  [18] =>
  string(12) "recruit_lock"
  [19] =>
  string(13) "recruit_offer"
  [20] =>
  string(15) "recruit_process"
  [21] =>
  string(21) "recruit_process_alias"
  [22] =>
  string(14) "recruit_rounds"
  [23] =>
  string(16) "recruitflow_step"
  [24] =>
  string(24) "recruitflow_step_history"
  [25] =>
  string(23) "resume_background_check"
  [26] =>
  string(23) "resume_candidate_upload"
  [27] =>
  string(13) "resume_hiring"
  [28] =>
  string(25) "resume_remuneration_proof"
  [29] =>
  string(10) "statistics"
  [30] =>
  string(19) "statistics_archives"
  [31] =>
  string(12) "vanke_reward"
}

转载于:https://my.oschina.net/u/3412738/blog/1548751