需求
A項(xiàng)目昧廷,需要獲取B堪嫂、C項(xiàng)目的數(shù)據(jù)表的字段結(jié)構(gòu),用于實(shí)現(xiàn)自定義列表木柬。
解決方法
- 在A項(xiàng)目配置B項(xiàng)目數(shù)據(jù)庫的賬號(hào)密碼皆串,用于連接B數(shù)據(jù)庫。
- 在.env文件中
DB_CONNECTION=mysql_sea
DB_PORT=3306
DB_HOST_B=127.0.0.1
DB_DATABASE_B=information_schema
DB_USERNAME_B=root
DB_PASSWORD_B=
注意這里連接的是information_schema這個(gè)庫眉枕《窀矗【INFORMATION_SCHEMA 數(shù)據(jù)庫】 是MySQL自帶的,它提供了訪問數(shù)據(jù)庫 元數(shù)據(jù) 的方式速挑。什么是 元數(shù)據(jù) 呢谤牡?元數(shù)據(jù)是關(guān)于數(shù)據(jù)的數(shù)據(jù),如數(shù)據(jù)庫名或表名姥宝,列的數(shù)據(jù)類型翅萤,或訪問權(quán)限等。不清楚的腊满,可以打開這個(gè)庫的column表看看断序,就懂了流纹。
- 在config/database.php中
'mysql_B' => [
'driver' => 'mysql',
'host' => env('DB_HOST_B', 'localhost'),
'port' => env('DB_PORT_B', '3306'),
'database' => env('DB_DATABASE_B', 'forge'),
'username' => env('DB_USERNAME_B', 'forge'),
'password' => env('DB_PASSWORD_B', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
'engine' => null,
'prefix' => ''
],
- 下面是獲取代碼
//獲取其他數(shù)據(jù)庫連接
$databases = array_where(config('database')['connections'], function ($value,$key) {
return $value && strstr($key,'mysql_') ;
});
$data = [];
foreach($databases as $connectName => $value){
//這里是把需要獲取的B數(shù)據(jù)庫名寫在連接名后面,即mysql_B
$base = last(explode('_',$connectName));
$tmp = \DB::connection( $connectName)->select("
select TABLE_SCHEMA,COLUMN_NAME,TABLE_NAME,COLUMN_COMMENT
from information_schema.COLUMNS
where TABLE_SCHEMA = '{$base}' "
);
$collection = collect( $tmp);
$database = $collection->first()->TABLE_SCHEMA;
$data[$database] = $collection->groupBy('TABLE_NAME')->toArray();
}
return $data;