QA: mysql group_concat长度限制

 SQL查询修改

-- 查找当前数据库长度
show variables like 'group_concat_max_len' 
-- 设置当前session的group_concat长度，其他session连接不受影响
SET SESSION group_concat_max_len = 10240;
-- 设置全局group_concat长度
SET GLOBAL group_concat_max_len = 10240

[Err] 1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation
当前mysql用户不允许，请联系DBA。DBA可能告诉你，长度太长了，性能差，还可能挂。

Java执行修改

stmt = conn.createStatement(); // 当前的JDBC连接

stmt.execute("set session group_concat_max_len = 150000");

 注意：group_concat长度不够会导致结果丢弃。

比如下面的存储过程：

CREATE DEFINER=`jlslyt`@`%` PROCEDURE `batchGetNsjgTreeList`(
    dwCode VARCHAR (20),
    rootId VARCHAR (1000),
    isParent INT,
    haveSelf INT
)
BEGIN
    # isParent    0：查询子节点 1:查询父节点 2:返回经过节点的父节点以及子节点。
    #    haveSelf    0: 不包含自身 1:包含自身a
DECLARE nsjgIds_all TEXT ;
DECLARE tableName VARCHAR (100) ;
DECLARE fieldName VARCHAR (100) ;
DECLARE whereFieldName VARCHAR (100) ; # 记录所有的ID。
SET nsjgIds_all =
IF (
    haveSelf = 1,
    CAST(rootId AS CHAR),
    ""
) ; loop_start :
LOOP

SET fieldName =
IF (
    isParent = 0,
    'NSJGID',
    'PARID'
) ;
SET tableName = CONCAT(
    "XH_NSJG_TB",

IF (LENGTH(dwCode) > 0, "_", ""),
 dwCode
) ;
SET whereFieldName =
IF (
    isParent > 0,
    'NSJGID',
    'PARID'
) ;
SET @nsjgIds = CAST(rootId AS CHAR) ;
SET @curSql = CONCAT(
    "SELECT GROUP_CONCAT(",
    fieldName,
    ") INTO @nsjgIds FROM ",
    tableName,
    " WHERE FIND_IN_SET (",
    whereFieldName,
    ",?)"
) ; # 循环查询所有节点
WHILE LENGTH(@nsjgIds) > 0 DO
    PREPARE stmt
FROM
    @curSql ; EXECUTE stmt USING @nsjgIds ; DEALLOCATE PREPARE stmt ;
IF @nsjgIds IS NOT NULL THEN

SET nsjgIds_all = CONCAT(
    nsjgIds_all,

IF (LENGTH(nsjgIds_all), ",", "") ,@nsjgIds
) ;
END
IF ;
END
WHILE ;
SET isParent = isParent - 2 ;
IF isParent < 0 THEN
    LEAVE loop_start ;
END
IF ;
END
LOOP
    ;
SET @curSql = CONCAT(
    "SELECT * FROM ",
    tableName,
    " WHERE NSJGID IN (",
    nsjgIds_all,
    ")"
) ; PREPARE stmt
FROM
    @curSql ; EXECUTE stmt ; DEALLOCATE PREPARE stmt ;
END


-----------------------------------------------------------------------------------------------------------


DELIMITER $$

USE `xht_ywp`$$

DROP PROCEDURE IF EXISTS `batchGetNsjgTreeList`$$

CREATE DEFINER=`dlwy`@`%` PROCEDURE `batchGetNsjgTreeList`(dwCode VARCHAR(20),rootId VARCHAR(1000),isParent INT, haveSelf INT)
BEGIN 

    # isParent    0：查询子节点 1:查询父节点 2:返回经过节点的父节点以及子节点。

    #    haveSelf    0: 不包含自身 1:包含自身a

    DECLARE nsjgIds_all TEXT;

    DECLARE    tableName VARCHAR(100);

    DECLARE    fieldName VARCHAR(100);

    DECLARE    whereFieldName VARCHAR(100);

    # 记录所有的ID。

    SET nsjgIds_all = IF(haveSelf=1,CAST(rootId AS CHAR),"");

    loop_start : LOOP

        SET fieldName = IF(isParent=0,'NSJGID','PARID');

        SET tableName = CONCAT("XH_NSJG_TB",IF(LENGTH(dwCode)>0,"_",""),dwCode);

        SET whereFieldName = IF(isParent>0,'NSJGID','PARID');

        SET @nsjgIds = CAST(rootId AS CHAR);

        SET @curSql = CONCAT("SELECT GROUP_CONCAT(",fieldName,") INTO @nsjgIds FROM ",tableName," WHERE FIND_IN_SET (",whereFieldName,",?)");

        # 循环查询所有节点

        WHILE LENGTH(@nsjgIds) > 0 DO 

            PREPARE stmt FROM @curSql;

            EXECUTE stmt USING @nsjgIds;

            DEALLOCATE PREPARE stmt; 

            IF @nsjgIds IS NOT NULL THEN

                SET nsjgIds_all = CONCAT(nsjgIds_all,IF(LENGTH(nsjgIds_all),",",""),@nsjgIds);
                

            END IF;

        END WHILE;  

        SET isParent = isParent - 2;

        IF isParent < 0 THEN  

            LEAVE loop_start;

        END IF;  

    END LOOP;  

    SET @curSql = CONCAT("SELECT * FROM ",tableName," WHERE NSJGID IN (",nsjgIds_all,")");

    PREPARE stmt FROM @curSql;

    EXECUTE stmt ;

    DEALLOCATE PREPARE stmt;

END$$

DELIMITER ;