MYSQL CONCAT 最大长度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20910166/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL CONCAT MAX LENGTH
提问by Crackeraki
Following this post: POST ABOUT CONCATMy problem is that i have many rows CONCATinto one row. For example if i have 10 rows with string around 50 chars, my query will show me only 6-7 of that rows or something like that.
I searech in stack and google and i found that i can change CONCAT max length by command: SET group_concat_max_len := @@max_allowed_packet. What i am doing wrong?
在这篇文章之后：发布关于 CONCAT我的问题是我将多行CONCAT合并为一行。例如，如果我有 10 行大约 50 个字符的字符串，我的查询将只显示 6-7 行或类似的内容。我searech在堆栈和谷歌和我发现我可以通过命令改变CONCAT最大长度：SET group_concat_max_len := @@max_allowed_packet。我在做什么错？
EDIT:When i SHOW VARIABLES LIKE 'group_concat_max_len'it's shows me 1024.
Mysql version 5.0.96-log. Tables type: MyISAM. Looks like it dont have any limits, i try to select simple varchar with 2000 chars, and it looks fine.
I have 3 tables: 1st - Item with ItemID, 2nd - Descriptionpack with ItemID and DescriptionID, 3rd Description with DescriptionID.
编辑：当我SHOW VARIABLES LIKE 'group_concat_max_len'显示 1024 时。Mysql 版本 5.0.96-log。表类型：MyISAM。看起来它没有任何限制，我尝试选择带有 2000 个字符的简单 varchar，它看起来不错。我有 3 个表：第一个 - 带有 ItemID 的项目，第二个 - 带有 ItemID 和 DescriptionID 的描述包，带有 DescriptionID 的第三个描述。
Select
DISTINCT Item.ItemID as item
,GROUP_CONCAT(Description.DescriptionID) AS description
From Item
LEFT OUTER JOIN descriptionpack
on Item.ItemID=descriptionpack.ItemID
LEFT OUTER JOIN description
on descriptionpack.descriptionID=description.descriptionID
GROUP BY item
EDIT2:I think i found the problem, i said my problem to my provider and they answer me this:
EDIT2：我想我发现了问题，我向我的供应商说了我的问题，他们回答我：
I reviewed your question with our hosting team. You wouldn't be able to change the global settings for that and other variables. However, you should be able to set that variable on a per session basis by setting it first, before other queries. Hope that helps.
我与我们的托管团队一起了您的问题。您将无法更改该变量和其他变量的全局设置。但是，您应该能够在每个会话的基础上设置该变量，方法是在其他查询之前先设置它。希望有帮助。
So now the problem is, how to do that.
所以现在的问题是，如何做到这一点。
回答by O. Jones
Presumably you're using GROUP_CONCAT(), not simple CONCAT().
大概你正在使用GROUP_CONCAT()，而不是简单的CONCAT()。
The default value of the group_concat_max_lenis 1024, which is a pretty small limit if you're building up big long concatenations.
的默认值group_concat_max_len是 1024，如果您正在构建大的长连接，这是一个非常小的限制。
To change it, use this command. I've set the length in this example to 100,000. You could set it to anything you need.
要更改它，请使用此命令。我在本例中将长度设置为 100,000。您可以将其设置为您需要的任何内容。
SET SESSION group_concat_max_len = 100000;
The usual value for max_allowed_packet is one megabyte, which is likely more than you need.
max_allowed_packet 的通常值为 1 兆字节，这可能超出您的需要。
group_concat_max_lenitself has an effectively unlimited size. It's limited only by the unsigned word length of the platform: 2^32-1 on a 32-bit platform and 2^64-1 on a 64-bit platform.
group_concat_max_len本身具有有效的无限大小。它仅受平台无符号字长的限制：32 位平台上为 2^32-1，64 位平台上为 2^64-1。
If that still isn't enough for your application, it's time to take @eggyal's suggestion and rethink your approach.
如果这对您的应用程序来说仍然不够，那么是时候采纳 @eggyal 的建议并重新考虑您的方法了。
回答by Reena Mori
You need change group_concat_max_len default value in the bellow config file
**my.cnf file(Linux) and my.ini file(windows)**
[mysqld]//Add this line group_concat_max_len=15000 under mysqld section
group_concat_max_len=15000
Note: After change is done You need to restart your MySQL server.
my.cnf file path in linux :
1. /etc/my.cnf
2./etc/mysql/my.cnf
3.$MYSQL_HOME/my.cnf
4.[datadir]/my.cnf
5.~/.my.cnf
