很多时候,我们需要把选出来的记录连接起来,比如这样:

id name

1 xling

2 snow

3 xlingfiary

 

其实需要的是:

xling,snow,xlingfairy

这样的数据.

 

放在以前,我会用存储过程,放一个游标,放一个 CONTINUE HANDLER 这些东东来循环读取,然后用 CONCAT_WS / CONCAT 这样的函数来重成一个字符串,然后返回. 如:

 

现在不必了, 有函数: GROUP_CONCAT , 其实原来就有这个函数,只是我没有看到而以...

 

SELECT GROUP_CONCAT(company) FROM jos_wbfmember;

SELECT GROUP_CONCAT(company ORDER BY company) FROM jos_wbfmember;

SELECT GROUP_CONCAT(company ORDER BY LENGTH(company)) FROM jos_wbfmember;

SELECT GROUP_CONCAT(company ORDER BY LENGTH(company) SEPARATOR ' | ') FROM jos_wbfmember;

 

以下是 MYSQL Manual 里关于 GROUP_CONCAT的介绍:

 

GROUP_CONCAT([DISTINCT] expr [,expr ...]

             [ORDER BY {unsigned_integer | col_name | expr}

                 [ASC | DESC] [,col_name ...]]

             [SEPARATOR str_val])

 

 

In MySQL, you can get the concatenated values of expression combinations. You can eliminate duplicate values by using DISTINCT. If you want to sort values in the result, you should use ORDER BY clause. To sort in reverse order, add the DESC (descending) keyword to the name of the column you are sorting by in the ORDER BY clause. The default is ascending order; this may be specified explicitly using the ASC keyword. SEPARATOR is followed by the string value that should be inserted between values of result. The default is a comma (“,”). You can eliminate the separator altogether by specifying SEPARATOR ''. 

 

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer: 

 

SET [SESSION | GLOBAL] group_concat_max_len = val;

 

The type returned by GROUP_CONCAT() is always VARCHAR unless group_concat_max_len is greater than 512, in which case, it returns a BLOB.