GROUP_CONCAT - wwq0912/mysql GitHub Wiki

在MySQL数据库中，用GROUP_CONCAT连接字符串是有长度限制的，默认是1024个字节。

mysql> select @@group_concat_max_len;
+------------------------+
| @@group_concat_max_len |
+------------------------+
|                   1024 |
+------------------------+
1 row in set (0.00 sec)

How to edit?

• SET GLOBAL group_concat_max_len = 102400;
• SET SESSION group_concat_max_len = 102400;

mysql> SET session group_concat_max_len = 10;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@group_concat_max_len;
+------------------------+
| @@group_concat_max_len |
+------------------------+
|                     10 |
+------------------------+
1 row in set (0.00 sec)

CREATE TABLE IF NOT EXISTS `student` (
	  `ID` int(11) NOT NULL AUTO_INCREMENT,
	  `CLASS_ID` int(11) NOT NULL,
	  `STUDENT_AGE` int(11) NOT NULL,
	  `STUDENT_NAME` varchar(45) DEFAULT NULL,
	  PRIMARY KEY (`ID`),
	  KEY `FK_CLASS_ID` (`CLASS_ID`)
	) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

INSERT INTO `student` (`ID`, `CLASS_ID`, `STUDENT_AGE`, `STUDENT_NAME`) VALUES ('1', '1', '1', 'student1');
INSERT INTO `student` (`CLASS_ID`, `STUDENT_AGE`, `STUDENT_NAME`) VALUES ('1', '1', 'student2');
INSERT INTO `student` (`CLASS_ID`, `STUDENT_AGE`, `STUDENT_NAME`) VALUES ('1', '1', 'student3');
INSERT INTO `student` (`CLASS_ID`, `STUDENT_AGE`, `STUDENT_NAME`) VALUES ('1', '1', 'student4');

mysql> select * from student;
+----+----------+-------------+--------------+
| ID | CLASS_ID | STUDENT_AGE | STUDENT_NAME |
+----+----------+-------------+--------------+
|  1 |        1 |           1 | student1     |
|  2 |        1 |           1 | student2     |
|  3 |        1 |           1 | student3     |
|  4 |        1 |           1 | student4     |
+----+----------+-------------+--------------+

mysql> SELECT group_concat(STUDENT_NAME) FROM student group by CLASS_ID;
+----------------------------+
| group_concat(STUDENT_NAME) |
+----------------------------+
| student1,s                 |
+----------------------------+
1 row in set, 1 warning (0.00 sec)

UTF-8编码中，一个英文字符等于一个字节，一个中文（含繁体）等于三个字节。

2： export by using excel

ERROR Controller - Exception: 
java.lang.IllegalArgumentException: The maximum length of cell contents (text) is 32,767 characters
	at org.apache.poi.xssf.streaming.SXSSFCell.setCellValue(SXSSFCell.java:309) ~[poi-ooxml-4.0.1.jar!/:4.0.1]