如何将一个表中的行值用作一个查询中另一个表的名称?
问题描述:
我甚至不知道我的头衔是否有意义,所以希望我的解释能够弥补我的标题清晰度。如何将一个表中的行值用作一个查询中另一个表的名称?
我想创建一个返回关于我的表的一般信息,行数,平均长度,数据长度单个查询,分(ID)和max(ID)值等
我知道如何查询INFORMATION_SCHEMA.PARTITIONS
以获取“大小”信息。例如:
mysql> SELECT TABLE_NAME AS Name,
-> TABLE_ROWS as 'Row Count',
-> AVG_ROW_LENGTH AS 'Avg Row Len',
-> DATA_LENGTH,
-> ROUND(DATA_LENGTH/1024/1024) AS 'DATA_LENGTH(M)'
->
-> FROM INFORMATION_SCHEMA.PARTITIONS
->
-> WHERE (TABLE_NAME = 'event_log'
-> OR TABLE_NAME = 'event_log_policy_entries'
-> OR TABLE_NAME = 'event_log_policy_status');
+--------------------------+-----------+-------------+-------------+----------------+
| Name | Row Count | Avg Row Len | DATA_LENGTH | DATA_LENGTH(M) |
+--------------------------+-----------+-------------+-------------+----------------+
| event_log | 10000089 | 182 | 1822425088 | 1738 |
| event_log_policy_entries | 10000137 | 171 | 1710227456 | 1631 |
| event_log_policy_status | 10000094 | 244 | 2449473536 | 2336 |
+--------------------------+-----------+-------------+-------------+----------------+
3 rows in set (0.57 sec)
mysql>
而且,我知道如何使用在每个表的基础上MIN()
和MAX()
函数来获得他们的最小/最大ID值。 (我所有的三个表都有一个名为id的列)。
mysql> SELECT MIN(id) AS 'Min', MAX(id) AS 'Max' from event_log;
+------+---------+
| Min | Max |
+------+---------+
| 0 | 9999999 |
+------+---------+
1 row in set (0.00 sec)
mysql>
但是我不知道如何使这个一个查询。我的目标是将有一个表,如下所示:
+--------------------------+-----------+-------------+-------------+----------------+-----+-----+
| Name | Row Count | Avg Row Len | DATA_LENGTH | DATA_LENGTH(M) | Min | Max |
+--------------------------+-----------+-------------+-------------+----------------+-----+-----+
| event_log | 10000089 | 182 | 1822425088 | 1738 | # | # |
| event_log_policy_entries | 10000137 | 171 | 1710227456 | 1631 | # | # |
| event_log_policy_status | 10000094 | 244 | 2449473536 | 2336 | # | # |
+--------------------------+-----------+-------------+-------------+----------------+-----+-----+
我一直在阅读有关表连接和子查询,但我一直没能想出解决办法,但。 (我假设这可以通过一个查询完成)。
答
一位同事谁不是一个堆栈溢出用户提到我作为一个例子,我则推断为以下:
mysql> SELECT TABLE_NAME AS Name,
-> AVG_ROW_LENGTH AS 'Avg_Row_Size',
-> DATA_LENGTH AS 'Table_Size',
-> INDEX_LENGTH AS 'Index_Size',
-> elog.Min,
-> elog.Max
->
-> FROM INFORMATION_SCHEMA.PARTITIONS AS isp
->
-> LEFT JOIN
-> (SELECT 'event_log' AS 'Name',
-> MIN(id) AS 'Min',
-> MAX(id) AS 'Max'
-> FROM event_log
-> UNION
-> (SELECT 'event_log_policy_entries' AS 'Name',
-> MIN(id) AS 'Min',
-> MAX(id) AS 'Max'
-> FROM event_log_policy_entries)
-> UNION
-> (SELECT 'event_log_policy_status' AS 'Name',
-> MIN(id) AS 'Min',
-> MAX(id) AS 'Max'
-> FROM event_log_policy_status)) AS elog ON (elog.Name = isp.TABLE_NAME)
->
-> WHERE (isp.TABLE_NAME = 'event_log'
-> OR isp.TABLE_NAME = 'event_log_policy_entries'
-> OR isp.TABLE_NAME = 'event_log_policy_status');
+--------------------------+--------------+------------+------------+------+---------+
| Name | Avg_Row_Size | Table_Size | Index_Size | Min | Max |
+--------------------------+--------------+------------+------------+------+---------+
| event_log | 182 | 1822425088 | 0 | 11 | 9999999 |
| event_log_policy_entries | 170 | 1709178880 | 137019392 | 33 | 9999999 |
| event_log_policy_status | 244 | 2448392192 | 137019392 | 132 | 9999999 |
+--------------------------+--------------+------------+------------+------+---------+
3 rows in set (0.56 sec)
mysql>
这似乎是一样快,因为这并没有原始查询有最小值/最大值。我使用了SELECT
和UNION
来创建一个带有名称和最小/最大值的子表,然后将它加入到INFORMATION_SCHEMA.PARTITION TABLE_NAME的结果中。
有没有更好的方法来做到这一点?这是有效的,我觉得搞清楚这一点很好。但是有更好的方法吗?
答
试试这个:
mysql> SELECT TABLE_NAME AS Name,
-> TABLE_ROWS as 'Row Count',
-> AVG_ROW_LENGTH AS 'Avg Row Len',
-> DATA_LENGTH,
-> ROUND(DATA_LENGTH/1024/1024) AS 'DATA_LENGTH(M)',
-> MIN(id) AS 'Min', MAX(id) AS 'Max'
->
-> FROM INFORMATION_SCHEMA.PARTITIONS, event_log
->
-> WHERE (TABLE_NAME = 'event_log'
-> OR TABLE_NAME = 'event_log_policy_entries'
-> OR TABLE_NAME = 'event_log_policy_status');
它将从INFORMATION_SCHEMA.PARTITIONS返回过滤的结果,所有从EVENT_LOG结果。
我有一个问题,是否有event_log表中的一个字段可以与INFORMATION_SCHEMA.PARTITIONS表相关联,以便您可以将event_log的每一行绑定到INFORMATION_SCHEMA.PARTITIONS表中的一行?
不幸的是,这只适用于event_log,返回一行而不是全部三行。我尝试通过将'FROM'行更改为[INFORMATION_SCHEMA.PARTITIONS,event_log,event_log_policy_entries,event_log_policy_status]来扩展它,但是失败并显示消息“ERROR 1052(23000):字段列表中的列'id'含糊不清” –
哦! INFORMATION_SCHEMA.PARTITIONS和INFORMATION_SCHEMA中的TABLE_NAME列以外的event_log表之间没有关系.PARTITIONS是我试图获取MAX(id)值的表的名称。 –