mysql使用group_concat顯示資料不完整解決！

8563084發表於2020-12-24
MySql
select group_concat(uid)  from  user  where uid = 509 or pid like '%,509,%'
 #資料顯示不完整509,520,625,688,766,767,852,1069,1178,1200,1225,1293,1408,1433,1558,1575,1661,1663,1672,1677,1707,1740,1754,1756,1759,1761,1774,1785,1792,1804,1811,1826,1831,1859,1884,1976,2016,2020,2041,2130,2133,2149,2166,2174,2185,2188,2222,2229,2292,2357,2364,2385,2409,2520,2521,2531,2533,2536,2537,2538,2539,2543,2555,2580,2615,2773,2794,2800,2858,2889,2971,3161,3267,3390,3511,3520,3531,3584,3586,3587,3588,3591,3598,3600,3601,3607,3610,3617,3636,3639,3643,3644,3660,3671,3726,3767,3768,3781,3794,3806,3812,3824,3827,3838,3871,3881,3911,3915,3918,3923,3936,3942,3944,3970,3979,4046,4081,4110,4115,4124,4150,4166,4227,4228,4240,4248,4286,4538,4541,4663,4686,4709,4724,4729,4756,4768,4941,5081,5120,5335,5341,5343,5346,5352,5354,5375,5388,5415,5439,5452,5501,5511,5529,5540,5541,5545,5546,5567,5569,5570,5574,5626,5634,5636,5640,5649,5655,5661,5667,5673,5682,5700,5733,5737,5748,5756,5758,5774,5813,5819,5824,5828,5840,5948,5958,5987,6007,6008,6013,6025,6031,6036,6044,6055,6116,6153,6307,6748,6790,6811,6814,6830,6838,6842,6843,6846,6
重啟服務後設定失效
//檢視當前資料庫的groupconcat長度SHOW VARIABLES LIKE 'group_concat_max_len'; //執行以下語句修改成自己想要的長度SET GLOBAL group_concat_max_len = 10240000;  SET SESSION group_concat_max_len = 10240000;
在mysql配置檔案裡修改my.cnf長期有效
#找到group_concat_max_len 若填-1表示最大值，表示2^32 -1 = 4294967295
#若沒有該項 直接新增即可 
group_concat_max_len = 10240000
阿里雲資料庫在引數設定裡設定，輸入範圍： [4-1844674407370954752],輸入-1並不能提交成功！
SHOW VARIABLES LIKE 'group_concat_max_len'; #修改完畢查詢還是1024
SET SESSION group_concat_max_len = 10240000;#修改session中的值

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/69989885/viewspace-2745149/，如需轉載，請註明出處，否則將追究法律責任。

相關文章