MySQL查询仅显示每列的最大值长度

Question

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

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

+--------+--------+----+
| column | length | id |
+--------+--------+----+
| a      |    123 |  1 |
| b      |    123 |  8 |
| c      |    123 |  6 |
| d      |    123 |  5 |
| e      |    123 |  3 |
+--------+--------+----+

a、b、c 等是列名，length 是该列中的最大长度，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)...(只需要指定“表”)但现在如何运行 _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`);

Answer 1

MySQL 没有任何方法可以在单个查询中执行您想要的操作。在您准备查询时，所有对列的引用都必须固定。

您能做的最好的事情是对 INFORMATION_SCHEMA 使用一个查询来生成一个新的动态 SQL 查询，以获取您想要的信息。

我针对 MySQL 5.5.16 对此进行了测试:

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_SCHEMA, TABLE_NAME) = ('test', 'tt') 
  AND COLUMN_NAME IN ('a', 'b', 'c');

此查询产生以下输出(为了在此处发布，我添加了换行符):

(SELECT 'a' AS `column`, LENGTH(`a`) AS `length`, id 
 FROM `test`.`tt` ORDER BY `length` DESC LIMIT 1) 
UNION ALL
(SELECT 'b' AS `column`, LENGTH(`b`) AS `length`, id 
 FROM `test`.`tt` ORDER BY `length` DESC LIMIT 1)
UNION ALL
(SELECT 'c' AS `column`, LENGTH(`c`) AS `length`, id 
 FROM `test`.`tt` ORDER BY `length` DESC LIMIT 1);

生成的查询在我的测试中返回以下结果:

+--------+--------+----+
| column | length | id |
+--------+--------+----+
| a      |     10 |  2 | 
| b      |      9 |  1 | 
| c      |      6 |  2 | 
+--------+--------+----+

请注意，这不会解决关系。如果不止一行具有相同的最长字符串，它将只报告一个任意选择的 id。

---

关于你的额外问题:

您必须将它们作为两个单独的查询来执行。我在回答的开头说过，在您准备查询时，列引用必须固定在 SQL 字符串中。您无法动态发现列并在同一个查询中查询它们的数据。

---

回复你的评论:

GROUP_CONCAT() 返回的最长字符串默认为 1024，这只够生成此 SQL 查询的 9 列。您可以增加此限制:

SET group_concat_max_len = 1024*1024;

这只会增加当前数据库 session 的限制。您可以使用 SET GLOBAL ... 为所有 session 更改它。如果您希望它在重新启动 MySQL 后仍然存在，请在您的 my.cnf 文件中设置该值(无需在配置文件中使用 SET GLOBAL)。