• 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>

  • 相关阅读:
    CentOS7上Mongodb安装、卸载
    CentOS7上Redis安装与配置
    vmware centos7系统虚拟机复制到其他电脑后不能联网问题解决
    流程项目点水笔记
    CentOS7图形界面启动报错unable to connect to X server
    本地Chrome测试JS代码报错:XMLHttpRequest cannot load
    iptables相关操作以及简单理解端口和服务之间关系
    git revert 和 git reset的区别
    Linux中_ALIGN宏背后的原理——内存对齐
    SPI协议及其工作原理浅析
  • 原文地址:https://www.cnblogs.com/newmiracle/p/11865373.html
Copyright © 2020-2023  润新知