問題描述
我在 MySQL 查詢中使用 GROUP_CONCAT() 將多行轉換為單個字符串.但是，這個函數的結果的最大長度是1024個字符.
I'm using GROUP_CONCAT() in a MySQL query to convert multiple rows into a single string.
However, the maximum length of the result of this function is 1024 characters.
我非常清楚我可以更改參數 group_concat_max_len 以增加此限制:
I'm very well aware that I can change the param group_concat_max_len to increase this limit:
SET SESSION group_concat_max_len = 1000000;
但是，在我使用的服務器上，我無法更改任何參數.不是通過使用前面的查詢，也不是通過編輯任何配置文件.
However, on the server I'm using, I can't change any param. Not by using the preceding query and not by editing any configuration file.
所以我的問題是:有沒有其他方法可以將多行查詢的輸出轉換為單個字符串?
So my question is: Is there any other way to get the output of a multiple row query into a single string?
推薦答案
CREATE TABLE some_table (
field1 int(11) NOT NULL AUTO_INCREMENT,
field2 varchar(10) NOT NULL,
field3 varchar(10) NOT NULL,
PRIMARY KEY (`field1`)
);
INSERT INTO `some_table` (field1, field2, field3) VALUES
(1, 'text one', 'foo'),
(2, 'text two', 'bar'),
(3, 'text three', 'data'),
(4, 'text four', 'magic');
這個查詢有點奇怪但是它不需要另一個查詢來初始化變量；它可以嵌入到更復雜的查詢中.它返回所有以分號分隔的field2".
This query is a bit strange but it does not need another query to initialize the variable; and it can be embedded in a more complex query. It returns all the 'field2's separated by a semicolon.
SELECT result
FROM (SELECT @result := '',
(SELECT result
FROM (SELECT @result := CONCAT_WS(';', @result, field2) AS result,
LENGTH(@result) AS blength
FROM some_table
ORDER BY blength DESC
LIMIT 1) AS sub1) AS result) AS sub2;
這篇關于MySQL 和 GROUP_CONCAT() 最大長度的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！