MySql中GROUP_CONCAT()函数使用详解
发布:smiling 来源: PHP粉丝网 添加日期:2014-10-03 14:09:53 浏览: 评论:0
GROUP_CONCAT()函数在mysql中可以说是连接字段了,但是我们在使用GROUP_CONCAT()函数时发现此函数并没那么简单了,下面我来介绍关于GROUP_CONCAT()用法.
语法,代码如下:
- GROUP_CONCAT([DISTINCT] expr [,expr ...]
- [ORDER BY {unsigned_integer | col_name | expr}
- [ASC | DESC] [,col_name ...]]
- [SEPARATOR str_val])
1.例如,代码如下:
- SELECT student_id, GROUP_CONCAT(courses_id) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id;
- +————+———+
- | student_id | courses |
- +————+———+
- | 2 | 3,4,5 |
- +————+———+
这就不需要用php循环了,代码如下:
- $row = $pdo->query("SELECT student_id, GROUP_CONCAT(courses_id) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id");
- $result = explode(',', $row['courses']);
2.当然分隔符还可以自定义,默认是以“,”作为分隔符,若要改为“|||”,则使用SEPARATOR来指定,代码如下:
- SELECT student_id, GROUP_CONCAT(courses_id SEPARATOR '|||') AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id;
- +————+———+
- | student_id | courses |
- +————+———+
- | 2 | 3|||4|||5 |
- +————+———+
3.除此之外,还可以对这个组的值来进行排序再连接成字符串,例如按courses_id降序来排,代码如下:
- SELECT student_id, GROUP_CONCAT(courses_id ORDER BY courses_id DESC) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id;
- +————+———+
- | student_id | courses |
- +————+———+
- | 2 | 5,4,3 |
- +————+———+
PHP代码如下:
- $row = $pdo->query("SELECT student_id, GROUP_CONCAT(courses_id) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id");
- $result = explode(',', $row['courses']);
分隔符还可以自定义,默认是以“,”作为分隔符,若要改为“|||”,则使用SEPARATOR来指定.
4.需要注意的:
a.int字段的连接陷阱
当你用group_concat的时候请注意,连接起来的字段如果是int型,一定要转换成char再拼起来,否则在你执行后(ExecuteScalar或者其它任何执行SQL返回结果的方法)返回的将不是一个逗号隔开的串,而是byte[]。
该问题当你在SQLyog等一些工具中是体现不出来的,所以很难发现,代码如下:
select group_concat(ipaddress) from t_ip 返回逗号隔开的串
select group_concat(id) from t_ip 返回byte[]
select group_concat(CAST(id as char)) from t_dep 返回逗号隔开的串
select group_concat(Convert(id , char)) from t_dep 返回逗号隔开的串
下面演示一下这个函数,先建立一个学生选课表student_courses,并填充一些测试数据,SQL代码如下:
- CREATE TABLE student_courses (
- student_id INT UNSIGNED NOT NULL,
- courses_id INT UNSIGNED NOT NULL,
- KEY(student_id)
- );
- INSERT INTO student_courses VALUES (1, 1), (1, 2), (2, 3), (2, 4), (2, 5);
若要查找学生ID为2所选的课程,则使用下面这条SQL,SQL代码如下:
- mysql> SELECT student_id, courses_id FROM student_courses WHERE student_id=2; --phpfensi.com
- +------------+------------+
- | student_id | courses_id |
- +------------+------------+
- | 2 | 3 |
- | 2 | 4 |
- | 2 | 5 |
- +------------+------------+
- 3 rows IN SET (0.00 sec)
输出结果有3条记录,说明学生ID为2的学生选了3、4、5这3门课程,放在PHP里,必须用一个循环才能取到这3条记录,如下所示:
- foreach ($pdo->query("SELECT student_id, courses_id FROM student_courses WHERE student_id=2") as $row) {
- $result[] = $row['courses_id'];
- }
而如果采用GROUP_CONCAT()函数和GROUP BY语句就显得非常简单了,如下所示:
- mysql> SELECT student_id, GROUP_CONCAT(courses_id) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id;
- +------------+---------+
- | student_id | courses |
- +------------+---------+
- | 2 | 3,4,5 |
- +------------+---------+
- 1 row IN SET (0.00 sec)
这样php里处理就简单了,代码如下:
- $row = $pdo->query("SELECT student_id, GROUP_CONCAT(courses_id) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id");
- $result = explode(',', $row['courses']);
- //附Cast,convert的用法:
- CAST(expr AS type), CONVERT(expr,type) , CONVERT(expr USING transcoding_name)
CAST() 和CONVERT() 函数可用来获取一个类型的值,并产生另一个类型的值,这个类型 可以是以下值其中的一个,代码如下:
- BINARY[(N)]
- CHAR[(N)]
- DATE
- DATETIME
- DECIMAL
- SIGNED [INTEGER]
- TIME
- UNSIGNED [INTEGER]
b.长度陷阱
用了group_concat后,select里如果使用了limit是不起作用的,用group_concat连接字段的时候是有长度限制的,并不是有多少连多少,但你可以设置一下.
使用group_concat_max_len系统变量,你可以设置允许的最大长度,程序中进行这项操作的语法如下,其中 val 是一个无符号整数:
SET [SESSION | GLOBAL] group_concat_max_len = val;
若已经设置了最大长度,则结果被截至这个最大长度,在SQLyog中执行 SET GLOBAL group_concat_max_len = 10 后,重新打开SQLyog,设置就会生效.
参数设置与限制说明
1.查看服务器中设置,代码如下:
- mysql> show variables like '%group_concat%';
- +----------------------+-------+
- | Variable_name | Value |
- +----------------------+-------+
- | group_concat_max_len | 1024 |
- +----------------------+-------+
- 1 row in set (0.00 sec)
以上设置的值说明当前是默认长度1KB.
2.改变参数值
方法一:修改配置文件中参数,新增 group_concat_max_len = 10240
方法二:在会话中实现,全局或当前session中,代码如下:
SET GLOBAL group_concat_max_len=10240;
SET SESSION group_concat_max_len=10240;
Tags: GROUP_CONCAT MySql函数
相关文章
- ·mysql函数group_concat使用方法(2014-10-01)
- ·mysql group_concat函数使用方法(2014-10-08)
- ·MySql中GROUP_CONCAT函数把多条数据显示成一条数据(2014-10-17)
- ·mysql自定义函数实现中文首字母拼音查询(2014-09-23)
- ·mysql字符串处理函数整理(2014-09-24)
- ·mysql用户函数区别详解(2014-09-25)
- ·mysql中count函数使用方法详解(2014-09-27)
- ·mysql你不知道字符串操作函数(2014-10-02)
- ·MySQL存储过程的函数与基本用法(2014-10-05)
- ·mysql过程去除html标签的函数例子(2015-04-17)
推荐文章
热门文章
最新评论文章
- 写给考虑创业的年轻程序员(10)
- PHP新手上路(一)(7)
- 惹恼程序员的十件事(5)
- PHP邮件发送例子,已测试成功(5)
- 致初学者:PHP比ASP优秀的七个理由(4)
- PHP会被淘汰吗?(4)
- PHP新手上路(四)(4)
- 如何去学习PHP?(2)
- 简单入门级php分页代码(2)
- php中邮箱email 电话等格式的验证(2)