Mysql GROUP_CONCAT(expr) 介绍

2023-11-02

1、MYSQL版本

\Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 86693
Server version: 5.7.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \

2、需求

把一行的数据用分隔符隔开，放在一个字段下面；

3、实现GROUP_CONCAT(expr )

3.1 测试sql，用逗号分割

select GROUP_CONCAT(id separator  '\,') from crawler_device;

3.2 测试结果

mysql> select GROUP_CONCAT(id separator  '\,') from crawler_device;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GROUP_CONCAT(id separator  '\,')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 18795,18796,18797,22760,22292,23305,23306,23307,19680,21693,23294,23308,19396,15400,16187,23103,23104,23105,23106,23107,23108,23109,23110,23111,23112,23113,23114,23115,13486,18917,13487,15401,15402,21216,22423,21694,18798,18853,18799,16188,21695,21696,16189,16190,16191,16747,16748,16749,16192,16193,22424,13488,13489,20412,22967,22968,22969,22970,22971,13490,19397,13491,13928,16750,18800,21217,15469,13492,13493,22641,16751,15403,15404,22425,16752,18801,20413,13901,16753,22064,19398,19681,16194,13494,16754,16569,16570,16571,16572,16573,22340,21024,13011,21025,13012,21601,21602,21603,21604,22341,16339,16340,16574,18712,13013,16575,16872,16576,23276,15199,15200,23407,19113,22342,23277,18340,21605,21606,21607,21608,21609,21610,23278,13014,22861,23537,23538,23539,23540,23541,16577,16578,16579,22263,15201,15202,15203,13015,13016,15956,19315,19316,21026,13017,22343,13018,13019,13020,16580,13021,22748,15957,18341,18342,18343,18344,13022,22862,17156,15204,13023,18928,13024,13025,13026,13027,18713,13028,13902,16581,1302 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

经过统计：发现每次获取的结果都是1024字节；查看官网：https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat，发现默认截取1024个字节的字符串；

查看环境变量：

mysql> show global variables where variable_name like '%group_concat_max_len%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 1024  |
+----------------------+-------+
1 row in set (0.01 sec)

mysql> show global variables where variable_name like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 41943040   |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)

确实只截取1024个字节；

3.3 解决方式

SET [GLOBAL | SESSION] group_concat_max_len = val;

本数据库设置如下：
SET GLOBAL group_concat_max_len = 1048576; #设置1Mb  这个设置好像不好使；结果没变；可能是客户端通过session 访问mysql server，所以session的设置才有用吧;--搞完es回来再研究！
SET SESSION group_concat_max_len = 1048576; #设置1Mb 好用；

3.4 重复执行，查看获取的结果的长度；

...1,14322,19417,21268,13595,13596,19418,13597,16249,16250,18822,21269,21270,21271,16350,21272,21273,21274,21275,21276,21277,13598,21278,16251,21734,21735,13599,23009,13600,13601,13602,23010,13603,20441,16813,16814,18506,18507,18508,18543,18509,18510,18511,18512,18513,20442,20443,22783,22784,13604,13605,21736,21737,22476,20444,23029,22793,21279,13606,20445,20446,21280,21738,21739,16815,16816,16817,16818,16819,21281,21282,21283,21284,16820,20447,13607,20726,21757,21740,21741,23011,23012,23013,13608,16252,21742,21743,21285,13609,16821,16253,16254,13610,22517,20448,21286,20449,20450,22785,13611,13761,20451,22477,22478,22479,22480,22481,22786,22787,20452,23014,23015,23016,23017,23018,23019,23020,15445,16255,17790,18514,18515,18516,18517,18518,18519,18520,18521,21287,13612,13613,23325,21288,21289,17389,17463,17390,17391,17478,22482,21290,21317,17471,16256,17392,17393,17394,17395,17396,17397,21744,13614,13789,13615,13616,13617,13618,21745,13619,21291,16257,18823,22788,16258,16259,13620,21292,17791,13621,21293,17826,17792,13622,16260,23021,13623,16955,23326,23327,14323,16261,17398,13783,21746,21747,21748,21749,23022,21294,17399,22806,22789,21295,19419,18824,18825,18826,18827,18828,18829,18830,17400,21296,21297,19420,13624,22483,22484,22485,22486,13777,13625,18616,17401,17402,17403,13626,13627,13628,22790,22796,18831,22791,20453,20454,20455,13629,13630,13631,19689,17404,23023,15446,16262,16263,16264,13632,21298,22487,13633,20456,16265,15447,20457,20458,20459,20460,22792,19421,15448,20461,21299,21300,15449,23024,21301,13634,15450,15451,23328,21342,16822,15452,20462,20463,20464,20465,20466,17405,20467,16266,16267,16268,16304,16269,20468,21302,20469,23025,21750,23026,22243,16823,16824,23329,23027,21751,18832,13635,16825,16826,16827,13636,20470,20471,20472,16270,13637,13638,13639,16271,21303,13640,23028,22502,22488,16828,16829,16830,13641,13495,13496,21218,21219,13497,16195,16755,16756,16757,21220,16346,18802,21221,21222,22972,21223,13498,16196,23309,22761,21697,21698,21699,22973,16758,22642,16197,22974,13499,22426,22975,22643,22644,13500,15405,16759,18023,18024,22762,22763,16198,23876,16199,18803,18804,16760,18040,19733,17374,16761,22976,16200,16351,16201,22427,22428,16762,13501,21224,18032,16202,22291,22429,22430,20414,20415,20416,13502,13503,18805,15406,18806,17784,17785,16203,14319,20417,19399,16763,22764,23449,13504,16764,16337,22431,22977,21753,22765,16765,16949,22978,18025,13505,19682,16204,23399,21338,22979,16766,22980,22981,22432,21225,18026,15407,15408,16205,13506,23400,18483,22982,23310,21700,15409,13758,13507,18807,18808,21701,21702,16767,16768,16769,21703,14320,22433,22645,15410,23412,16312,16206,13508,21704,21705,21706,21707,21708,18027,13509,23311,13510,19445,21226,22434,13511,13512,16207,22435,13513,23312,13514,22983,22984,16770,16771,13515,16832,21227,21709,16208,22646,16772,21228,16773,16774,16956,19683,22436,15411,23413,22985,19400,21229,22647,13516,23313,15412,22437,20418,20419,19401,22438,22439,23043,16898,22440,16209,13517,22766,22767,19450,13518,16210,16775,22441,13519,21710,21711,21712,21713,21230,16776,21231,15413,19402,13520,19458,19403,18484,18809,18810,22240,18485,18486,18487,18488,18489,13521,13522,22986,17475,15414,15415,15416,13523,18490,18491,21232,16211,21313,13524,22987,22988,22989,13525,16777,23314,13368,13788,13369,13930,21233,13526,13527,13528,13529,22768,19404,18028,16778,16779,16780,16781,13530,13531,16782,18029,18492,21326,18918,18919,18920,18921,18922,18923,16212,16302,13532,13533,18493,18494,20420,13534,23315,22442,22443,22507,22508,22444,18495,22445,22990,16213,22991,13535,13536,20727,21234,20421,20422,20423,21235,18811,16214,16215,16216,15417,13537,13538,18496,18497,18498,16217,13539,20424,21236,15418,21237,13540,16218,15788,21714,13541,13542,16219,15419,22241,22992,21715,15420,22993,18499,18500,15421,15422,15423,20425,20426,20427,20428,20429,22994,16783,16784,16785,19405,22995,23316,18812,18813,13543,16220,15424,15425,15426,15427,15428,15429,15430,15431,15432,16786,16221,22769,19406,16787,16788,21238,13544,16880,16789,16790,16426,16791,16792,16793,23450,21716,21717,21718,19407,18030,18031,13545,23451,23448,23572,23573,17786,13546,19732,19408,16338,21239,21240,16222,13547,13548,22996,18814,18815,21719,22446,22997,22447,13549,16223,19446,13550,19684,16274,22448,23317,23318,19409,20430,22449,13551,13552,13553,22450,16224,15433,13554,13555,22998,16225,15434,13556,16794,13557,22451,22452,22453,22454,23319,16795,16796,16891,16797,13558,21720,13559,22455,13560,23320,22999,20431,16226,16227,22456,22457,15435,15436,21241,21242,19410,22458,16798,21721,16799,16431,16864,16228,13561,20432,20433,20375,22459,22460,22509,21243,18501,18502,18503,23321,18933,23322,19685,19686,13562,18504,18505,19089,19090,19125,19091,19092,19093,19094,19111,19095,19096,19112,19097,13563,22461,21722,21723,21724,21725,21726,21727,21728,21729,22501,13485,23560,23561,23562,23563,23574,23564,23565,23557,23566,23575,23567,23568,23569,23570,23571,19394,19395,16182,16183,16184,16739,16740,16741,16742,16743,16744,16273,16185,16186,16745,16746,21214,22966,18575,18477,18478,18479,18480,18481,18482,18264,21215,19797,19677,19678,19679 |

总之，很长 ^ V ^，完美解决；

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

Mysql GROUP_CONCAT(expr) 介绍的相关文章

显示标准化数据

跟进问题添加 2 个不同表的总和 https stackoverflow com questions 39717541 adding sum from 2 different tables 我创建了3个表 members videos v
海量记录的bulk_create最佳实践

I use bulk create将 1 mio 记录插入到新表中需要 80 秒 Django 只使用一个 CPU 核心大约 25 CPU 但没有一个核心达到 100 我相信有改进的潜力这是代码 class Stock models
MySQL - 从临时表插入

这看起来非常简单但我坚持使用简单的插入语句见下文 begin work CREATE TEMPORARY TABLE IF NOT EXISTS insert table AS select r resource id fr file
连接 Netbeans 和 MySQL 但出现大整数错误

所以我正在尝试向我的 Netbeans 数据库即 MySQL 添加新连接但我遇到了大整数转换错误有人可以帮助我吗详细地我右键单击现有的MySQL 服务器位于 localhost 3306 root 已断开连接 gt gt 选择co
如何优化这个MySQL慢（非常慢）查询？

我有一个 2 GB 的 mysql 表包含 500k 行我在没有负载的系统上运行以下查询 select from mytable where name in n1 n2 n3 n4 bunch more order by salary
MySQL 和 Hibernate 之间的主键自增由谁负责？

MySQL CREATE TABLE role id role INT 11 unsigned NOT NULL AUTO INCREMENT PRIMARY KEY id role AUTO INCREMENT 1 休眠 Entity p
MySQL 通过 current_timestamp 选择上个月的数据

直到今天当我使用 MySQL 并需要对日期时间执行操作时我使用带有 unix 时间戳的 int 列没有出现任何问题但今天在阅读了一些指南后我决定默认使用 current timestamp 测试时间戳列所以我感兴趣如何按列选择
在 MySQL 中对整数字段运行带引号的数字（字符串）查询时会发生哪些复杂情况

在 SQL 中不应引用整数因为如果引用它将是一个字符串但我很好奇如果我这样做会出现什么问题并发症例如 SELECT FROM table WHERE id 1 正确的 vs SELECT FROM table WHERE id
只获取倒数第二条记录 - mysql-query

我有一个如下表记录 my table id rating description 1 0 0 bed 2 1 0 good 3 0 0 bed 4 1 0 good 5 0 0 bed 6 0 0 bed 7 0 0 bed 现在我通过评级
什么时候应该使用 C++ 而不是 SQL？

我是一名 C 程序员偶尔使用 MySQL 来处理数据库但我的 SQL 知识相当有限但我肯定愿意改变这一点目前我正在尝试仅使用 SQL 查询对数据库中的数据进行分析但我准备放弃了转而将数据导入到C 中用C 代码进行分析我和同
如果 Row1 = 值 1，则更新其他行

我有一个小的 php 脚本用于访问 mySql 数据库我想在数据库中插入新记录之前查看该数字值 1 是否等于数据库中的记录这也在第 1 行所以我想查看传入的电话号码是否等于数据库中的电话号码如果是这样则必须保持电话号码相同的
错误代码：1305。函数或过程不存在

因此我在 MySQL 中创建一个函数然后尝试向用户授予使用该函数的权限但我无法这样做这就是我正在做的 DELIMITER USE rxhelp36 scbn DROP FUNCTION IF EXISTS businessDayDi
慢速自动增量重置

我有很多表由于某些原因我需要在应用程序启动时调整这些表的自动增量值我尝试这样做 mysql gt select max id from item max id 97972232 1 row in set 0 05 sec mysql
MySQL中如何声明变量？

如何在mysql中声明一个变量以便我的第二个查询可以使用它我想写一些类似的东西 SET start 1 SET finish 10 SELECT FROM places WHERE place BETWEEN start AND fin
在 jQuery AJAX 成功中从 MySql 获取特定响应

好吧我有这个 ajax 代码它将在 Success 块中返回 MySql 的结果 ajax type POST url index php success function data alert data My Query sql SE
让登录更安全

我已使用此代码进行管理员登录仅当用户输入正确的用户名和密码时才应打开loginhome php 但后来我意识到这根本不安全任何人都可以直接访问 mywebsite loginhome php 而无需登录注销后可以使用后退按钮打开 l
Mysql用in语句限制

我正在写一个查询 SELECT user bookmarks id as user bookmark id bookmark id user bookmarks user id bookmark url bookmark website b
MYSQL - 使用逗号分隔字符串作为变量输入的存储过程

我希望有人能够提供帮助我已经创建了我的第一个存储过程没什么花哨的但是我遇到了问题我想给它一个字符串输入例如 1 2 3 4 5 然后它执行一个简单的操作SELECT FROM TABLE WHERE EAN IN VAR 所以存储
随机组合 MySQL 数据库中的两个单词

我有一个包含名词和形容词的数据库例如 id type word 1 noun apple 2 noun ball 3 adj clammy 4 noun keyboard 5 adj bloody ect 我想创建一个查询它将抓取 10
post php mysql 的拆分关键字

我有一个表存储帖子 ID 它的标签如下 Post id Tags 1 keyword1 keyword2 keyword3 我想循环遍历该表中的每一行并执行以下操作将关键字1 关键字2 关键字3放入新表中 word id word val

随机推荐

线程进程协程的实现代码

单线程 import time def run print hello world time sleep 1 if name main for i in range 5 run 多线程 import threading import tim
规范：前端代码开发规范

一前端静态代码检查工具 1 1 ESLint ESLint 是一个插件化的 JavaScript 代码检查工具可以使用规则插件或者自定义规则对代码进行静态检查 1 2 JSLint JSLint 是由 Douglas Crockford
JAVA-static关键字多态

static Java中的static关键字主要用于内存管理我们可以应用static关键字在变量方法块和嵌套类中 static关键字属于类而不是类的实例被static修饰的数据在内存中只会存在一份数据所以这个数据适合用来共享数据
TypeError: write() argument must be str, not dict

在写入文件的时候出现 TypeError write argument must be str not dict 报错可以使用json 格式写入 import requests import re import json def get
python bokeh_使用Bokeh在Python中进行交互式数据可视化

python bokeh Bokeh prides itself on being a library for interactive data visualization Bokeh以成为交互式数据可视化的库而自豪 Unlike popu
8051介绍--DW8051

Overview synopsy公司设计的可综合内核IP 可嵌入到IC内部它可以相容803x 805x单片机 1个机器周期 4个时钟周期一条指令周期大概是2 9个机器周期指令周期长度可变因此可以分别访问高速和低速设备 MCU的clo
Call to undefined function mcrypt_get_block_size

http stackoverflow com questions 17109818 install php mcrypt on centos 6 问题 Call to undefined function mcrypt get block
【经验总结】tcp_tw_recycle参数引发的故障

故障描述 2010年9月7日新上线的手机游戏论坛有部分地区用户反应登陆游戏时出现不能登陆或登陆超时等情况观察用户同时在线数量开始下降情况排错过程一初步检查是否有变更导致的故障 1 联系同事检查网络是否有问题或有对该机房网络是否有进
Anaconda修改虚拟环境的安装位置

1 首先试着新建一个环境查看环境的安装路径发现当前安装路径在C盘我们的目标是改到D盘 2 输入conda info 查看当前的环境默认路径有哪几个发现一共有三个其中第一个是默认路径现在的目标是将默认路径改到D盘 3 输入cond
徐小明：上午涨的快，跌的也…

我不喜欢炒股今年股市估计少人欢喜多人愁吧老婆1万块都亏了4千多了还不如到唐狮官方网站去买衣服tonlion hao9go com 哎原文地址徐小明上午涨的快跌的也快作者徐小明徐小明上午涨的快跌的也快今天上午低开之
优惠券秒杀(三)

L1296686146 冗谪 2023 07 28 13 24 发表于陕西收录于合集 redis7个优惠券秒杀一人一单优惠券的目的是为了引流但是目前的情况是一个人可以无限制的抢这个优惠券因此代码中应该添加一个用户只能下一单的逻辑
vue 监听浏览器页面关闭_监听浏览器关闭事件的解决方案

在web项目开发中关于浏览器关闭事件有两个很常见的问题为什么我没有监听浏览器关闭事件我监听到了这个事件但写在事件里的异步请求为什么发送不成功原因分析这两个问题无外乎两个原因浏览器关闭事件未被触发和异步请求发送失败原因1
Centos7 离线安装 nginx

背景某些项目处于私有云中并不能连接互联网此时安装各种组件都是相当的麻烦本文记录在某项目中离线安装部署Nginx 首先是安装依赖包附件地址上传到csdn 免积分下载 nginx 1 21 6及Nginx依赖的rpm安装包 Linux
华为数字化转型之道平台篇第十一章统一的数据底座

平台篇第十一章统一的数据底座在从传统信息化向数字化转型的过程中企业积累了海量数据并且还在爆发式增长数据很多但真正能产生价值的数据却很少数据普遍存在分三年不拉通的问题缺乏统一的定义和架构找到想要的能用的数据越来越难如
C语言结构体与共用体（1）

结构体有时需要将不同类型的数据组合成一个人有机的整体以便于引用如一个学生有学号姓名性别年龄地址等属性 int num char name 20 char sex int age char addr 30 定义一个结构体的一般
php和tp5学习笔记

之前看过php的基础教学视频了解了一下自己没有上手实践现在为了项目需要需要扎实学习一下所以做一下笔记吧 php学习笔记 1 环境 1 用phpstudy搭建php环境 2 搭建thinkphp环境 2 知识点 2 1 URL 2
python判断一个集合是否为另一个集合的子集

a 1 2 3 4 b set 1 2 b issubset a
FusionAD：用于自动驾驶预测和规划任务的多模态融合

论文背景自动驾驶 AD 任务通常分为感知预测和规划在传统范式中 AD中的每个学习模块分别使用自己的主干独立地学习任务以前基于端到端学习的方法通常基于透视视图相机和激光雷达信息直接输出控制命令或轨迹论文提出了基于BEV多模态的多
new JSONObject(true)

构造容量为16个单位的HashMap作为JSONObject JSONObject jsonObject new JSONObject 构造容量为16个单位JSONObject 可选择器容器是否有序 LinkedHashMap true H
Mysql GROUP_CONCAT(expr) 介绍

1 MYSQL版本 Welcome to the MySQL monitor Commands end with or g Your MySQL connection id is 86693 Server version 5 7 23 My

Mysql GROUP_CONCAT(expr) 介绍

Mysql GROUP_CONCAT(expr) 介绍 的相关文章

随机推荐

热门标签

Mysql GROUP_CONCAT(expr) 介绍的相关文章