mysql查所有值的长度_MySQL查询仅显示每列的最大值长度

我有一个有n列和x行的表格。

我想要查询显示一个包含所有列的表以及该列的最大长度值(以及具有最大值长度的那一行的行ID)。

+--------+--------+----+

| column | length | id |

+--------+--------+----+

| a | 123 | 1 |

| b | 123 | 8 |

| c | 123 | 6 |

| d | 123 | 5 |

| e | 123 | 3 |

+--------+--------+----+a，b，c等是列名，长度是该列中的最大长度，id是具有该列的最大长度的行。

像这样？但后来动态地为所有列：

SELECT MAX(LENGTH(`column_a`)) AS `length`, `id`

FROM `table` GROUP BY LENGTH(`column_a`)

ORDER BY LENGTH(`column_a`) DESC LIMIT 1像添加了两列的下一个查询(最大长度和行ID)

SHOW COLUMNS FROM `table`也许也可以使用此查询来获取特定表的列名称：

SELECT `column_name` AS `column`

FROM `information_schema`.`columns`

WHERE `table_name` = 'table'

AND `column_name` != 'id'

ORDER BY `ordinal_position`几乎在那里(感谢Bill)...(只需指定'table')，但现在如何运行_SQL al同样的运行...

SELECT CONCAT(GROUP_CONCAT(CONCAT('(SELECT \'', `column_name`,'\' AS `column`, LENGTH(`', `column_name`,'`) AS `length`, id ', 'FROM `', `table_schema`,'`.`', `table_name`,'` ORDER BY `length` DESC LIMIT 1)') SEPARATOR ' UNION ALL '), ';') AS _SQL

FROM `information_schema`.`columns`

WHERE `table_name` = 'table'

AND `column_name` IN (

SELECT `column_name`

FROM `information_schema`.`columns`

WHERE `table_name` = 'table'

AND `column_name` != 'id'

ORDER BY `ordinal_position`);