問(wèn)題描述

我在 MySQL 查詢中使用 GROUP_CONCAT() 將多行轉(zhuǎn)換為單個(gè)字符串.但是，這個(gè)函數(shù)的結(jié)果的最大長(zhǎng)度是1024個(gè)字符.

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.

我非常清楚我可以更改參數(shù) 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;

但是，在我使用的服務(wù)器上，我無(wú)法更改任何參數(shù).不是通過(guò)使用前面的查詢，也不是通過(guò)編輯任何配置文件.

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.

所以我的問(wèn)題是:有沒(méi)有其他方法可以將多行查詢的輸出轉(zhuǎn)換為單個(gè)字符串?

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');

這個(gè)查詢有點(diǎn)奇怪但是它不需要另一個(gè)查詢來(lái)初始化變量；它可以嵌入到更復(fù)雜的查詢中.它返回所有以分號(hào)分隔的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; 

這篇關(guān)于MySQL 和 GROUP_CONCAT() 最大長(zhǎng)度的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！