MySQL数据库错误:列太多

Question

我正在运行以下查询，以使表中的数据枢轴化：

SELECT 
time_stamp,
SUM(CASE WHEN sensor_id=000001 THEN temp ELSE 0 END) AS 'temp_000001',
SUM(CASE WHEN sensor_id=000002 THEN temp ELSE 0 END) AS 'temp_000002',

..。

SUM(CASE WHEN sensor_id=003372 THEN temp ELSE 0 END) AS 'temp_003372'

FROM sensor_data
GROUP BY time_stamp

查询应该返回一个包含3372列和8928行(不同time_stamps数)的结果集。我得到了一个“太多的列”错误，这是奇怪的，因为该查询以前工作过。sensor_data包含8928个时点的3372个传感器的测量数据。如果我在一个表上运行相同的查询，该表包含288个时点的3372个传感器的测量结果，它可以工作。所以列的数量不可能是真正的问题。

Answer 1

MySQL中的最大列数是欺骗性的。

它可以是4096 (这是源代码中记录的MAX_FIELDS )，但也可以低得多，这取决于列的名称！

血淋淋的细节在这里：理解MySQL表中的最大列数

您以前也可能做过查询工作，因为它没有创建临时表。也许时间戳列以前是索引的，现在不是了？

Answer 2

您似乎遇到了计算GROUP BY所需的临时表的行长问题。列数的硬限制为4096，但行长度限制为64K (没有blobs)。有很多事情你可以试着避开这个问题：

• 将temp替换为cast(temp as signed)，或使用其他强制转换技巧，强制表达式的类型尽可能减少存储量。
• 将查询拆分为由两个union查询组成的group by
• 用time_stamp列手动创建一个临时结果表，所有结果列都是最小的存储类型，并使用insert into ... select ... on duplicate key update ...手动填充它--然后从该表读取结果--实际上是用您自己的双手执行一个糟糕的group by。
• 将温度类型更改为数据允许的最小值。通过执行MySQL，您可以从select temp from sensor_data procedure analyse()获得推荐