php将数据库中的表,PHP-将表计数添加到数据库列表中

我想列出所有自定义数据库：

SELECT `SCHEMA_NAME` AS 'Database',

`DEFAULT_CHARACTER_SET_NAME` AS 'Character Set',

`DEFAULT_COLLATION_NAME` AS 'Collation'

FROM `information_schema`.`SCHEMATA`

WHERE `SCHEMA_NAME` NOT IN ('information_schema','mysql','performance_schema')

ORDER BY `SCHEMA_NAME` ASC

但是我想在每个数据库中包括表的数量：

SELECT `TABLE_SCHEMA`, COUNT(*)

FROM `information_schema`.`TABLES`

WHERE `TABLE_SCHEMA` NOT IN ('information_schema','mysql','performance_schema')

GROUP BY `TABLE_SCHEMA`

…在一个表格中：

[

0 => [ 'Database'=>'customDb1', ..., 'Table Count'=>3 ],

1 => [ 'Database'=>'customDb2', ..., 'Table Count'=>8 ],

2 => [ 'Database'=>'customDb3', ..., 'Table Count'=>0 ]

]

我知道运行第一个查询,遍历PHP中的每个数据库以及运行第二个查询的效率要比使用表JOIN的效率低得多,但是我只是不掌握JOIN语法. Lynda tutorial我正在通过语言的这一相当(可能)复杂的部分进行学习,而我尝试的一切都会引发错误或不良结果.

我确信这是开发人员创建MySQL数据库接口的常见查询.如何仅使用MySQL查询将表计数添加到数据库列表中？

解决方法:

尝试这个：

SELECT s.`SCHEMA_NAME` AS 'Database',

s.`DEFAULT_CHARACTER_SET_NAME` AS 'Character Set',

s.`DEFAULT_COLLATION_NAME` AS 'Collation',

COALESCE(COUNT(t.`TABLE_SCHEMA`),0) as table_cnt

FROM `information_schema`.`SCHEMATA` s

LEFT JOIN `information_schema`.`TABLES` t

ON(s.`SCHEMA_NAME` = t.`TABLE_SCHEMA`)

WHERE s.`SCHEMA_NAME` NOT IN ('information_schema','mysql','performance_schema')

GROUP BY s.`SCHEMA_NAME`

ORDER BY s.`SCHEMA_NAME` ASC

标签：join,mysql,php

来源： https://codeday.me/bug/20191118/2030204.html