• mysql函数concat与group_concat使用说明


    mysql函数concat与group_concat使用说明
    concat()函数
    <pre>
    mysql> select concat(',',name,',') from `user`;
    +--------------------------+
    | concat(',',fdipzone,',') |
    +--------------------------+
    | ,fdipzone, |
    +--------------------------+
    1 row in set (0.00 sec)
    </pre>


    concat_ws() 函数
    <pre>
    mysql> select concat_ws(',',country_code,phone,region) from `user`;
    +------------------------------------------+
    | concat_ws(',',country_code,phone,region) |
    +------------------------------------------+
    | 86,13794830550,GZ |
    +------------------------------------------+
    1 row in set (0.00 sec)
    </pre>

    group_concat()函数
    <pre>
    mysql> select * from `article_in_category`;
    +----+------------+-------------+
    | id | article_id | category_id |
    +----+------------+-------------+
    | 1 | 1 | 1 |
    | 2 | 1 | 2 |
    | 3 | 1 | 3 |
    | 4 | 2 | 4 |
    | 5 | 2 | 3 |
    | 6 | 2 | 5 |
    | 7 | 3 | 1 |
    | 8 | 3 | 5 |
    | 9 | 3 | 6 |
    | 10 | 4 | 8 |
    +----+------------+-------------+
    </pre>

    <pre>
    mysql> select article_id,group_concat(category_id order by category_id asc) from `article_in_category` group by article_id;
    +------------+----------------------------------------------------+
    | article_id | group_concat(category_id order by category_id asc) |
    +------------+----------------------------------------------------+
    | 1 | 1,2,3 |
    | 2 | 3,4,5 |
    | 3 | 1,5,6 |
    | 4 | 8 |
    +------------+----------------------------------------------------+
    4 rows in set (0.00 sec)
    </pre>

    ps:category_id order by category_id asc 这个是 字段里面的的排序 比方说1,2,3就是升序啦

    注意:group_concat()函数对返回的结果有长度限制,默认为1024字节

    <pre>
    mysql> set global group_concat_max_len=2048;
    Query OK, 0 rows affected (0.03 sec)

    mysql> show global variables like '%group_concat_max_len%';
    +----------------------+-------+
    | Variable_name | Value |
    +----------------------+-------+
    | group_concat_max_len | 2048 |
    +----------------------+-------+
    </pre>

  • 相关阅读:
    [原]音视频播放笔记
    [原]很多时候, 错误源于自己
    [原]找工作之tj
    [原]昨天碰到的一个诡异问题
    [原]硬盘分区规划
    [原]编程手记2008.08.26
    [原]编程手记2008.08.28
    eclipse 某些java文件乱码
    图片垂直居中,兼容ie6
    ul里不能直接嵌套div
  • 原文地址:https://www.cnblogs.com/newmiracle/p/11865373.html
Copyright © 2020-2023  润新知