mysql - 如何获取包含十个值而不是全部值的逗号分隔列表？

Question

我需要将一组动态行更新为给定值，我在以下查询中想到了::

UPDATE tbl_names n 
SET n.queue_id = (SELECT MAX(id) FROM tbl_queue ORDER BY 1 DESC)
WHERE n.id IN (SELECT nm.id FROM tbl_names nm ORDER BY 1 DESC LIMIT 10)

但是我使用的MySQL版本不允许在子查询中使用LIMIT:

SHOW VARIABLES LIKE "%version%";

+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| innodb_version | 5.7.27 |
| version        | 5.7.27 |
+----------------+--------+

然后我想到创建变量并将子查询的结果分配给它们:

SET @queueId = (SELECT MAX(id) FROM tbl_queue q ORDER BY 1 DESC);
SET @nameIds = (SELECT GROUP_CONCAT(n.id) FROM tbl_names n ORDER BY 1 DESC LIMIT 10);

UPDATE cases c SET c.queue_id=@queueId WHERE c.id IN (@nameIds);

但问题是 GROUP_CONCAT 不遵守 LIMIT 并返回表中的所有 ID。

我从here知道以下内容:

One important thing you need to be aware of when using GROUP_CONCAT() is that the result is truncated to the maximum length that is provided by the group_concat_max_len system variable, which has a default value of 1024.

This variable’s value can be set higher, by using the following syntax:

SET [GLOBAL | SESSION] group_concat_max_len = val; Where val is an unsigned integer.

However, note that the effective maximum length of the return value is itself constrained by the value of max_allowed_packet.

但是这样做:

SET SESSION group_concat_max_len = 10;
SET @nameIds = (SELECT GROUP_CONCAT(n.id) FROM tbl_names n ORDER BY 1 DESC);
SELECT @nameIds;

仅返回一个 ID，而不是我期望的 10 个。我做错了什么？我想我误解了 group_concat_max_len 的工作原理。仅获取 10 个 Id 作为逗号分隔值的正确方法是什么？

Answer 1

使用SUBSTRING_INDEX()以 10 作为 3d 参数:

SELECT SUBSTRING_INDEX(GROUP_CONCAT(n.id), ',', 10) FROM tbl_names n