group_concat() 函数有限制 mysql> show warnings// +---------+------+--------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------+ | Warning | 1260 | 1 line(s) were cut by GROUP_CONCAT() | +---------+------+--------------------------------------+ 1 row in set (0.00 sec) 我一共有190列它只是截取了一部分
用 select max(if()) from ... group by ...
or 用SP来动态生成SQL语句,如select max(if()) from ... group by ...,再执行
你总共有多少行啊?mysql 中查询的总列数,或者varchar()的总长都是有限制的。
190个字段MYSQL支持,不过SQL语句就长了
简单示例: mysql> CREATE PROCEDURE lj() -> BEGIN -> DECLARE done INT DEFAULT 0; -> DECLARE v_a varchar(100); -> DECLARE cur1 CURSOR FOR SELECT f1 FROM tt; -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -> set @sqlstmt=''; -> OPEN cur1; -> FETCH cur1 INTO v_a; -> WHILE done=0 DO -> set @sqlstmt=concat(@sqlstmt,v_a); -> FETCH cur1 INTO v_a; -> END WHILE; -> END;
MySQL交叉表
函数有限制
mysql> show warnings//
+---------+------+--------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------+
| Warning | 1260 | 1 line(s) were cut by GROUP_CONCAT() |
+---------+------+--------------------------------------+
1 row in set (0.00 sec)
我一共有190列它只是截取了一部分
select max(if()) from ... group by ...
用SP来动态生成SQL语句,如select max(if()) from ... group by ...,再执行
mysql> CREATE PROCEDURE lj()
-> BEGIN
-> DECLARE done INT DEFAULT 0;
-> DECLARE v_a varchar(100);
-> DECLARE cur1 CURSOR FOR SELECT f1 FROM tt;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-> set @sqlstmt='';
-> OPEN cur1;
-> FETCH cur1 INTO v_a;
-> WHILE done=0 DO
-> set @sqlstmt=concat(@sqlstmt,v_a);
-> FETCH cur1 INTO v_a;
-> END WHILE;
-> END;
+------------------------+
| @@group_concat_max_len |
+------------------------+
| 1073741824 |
+------------------------+
1 row in set (0.00 sec)
这个默认是1024
我给改成了1024×1024×1024
先想办法上传到某个网站上,然后到CSDN把URL粘过来。