MYSQL CONCAT最大长度 [英] MYSQL CONCAT MAX LENGTH
问题描述
关注此帖子:发布关于CONCAT的信息
我的问题是我在一行中有很多行CONCAT.例如，如果我有10行，字符串约50个字符，我的查询将仅显示6-7行或类似的内容.
我在堆栈和Google中进行了搜索，发现可以通过以下命令更改CONCAT最大长度:SET group_concat_max_len := @@max_allowed_packet.我在做什么错了?
Following this post: POST ABOUT CONCAT
My problem is that i have many rows CONCAT into 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?
当我SHOW VARIABLES LIKE 'group_concat_max_len'时，显示1024.
MySQL版本5.0.96-log.表类型:MyISAM.看起来它没有任何限制，我尝试选择具有2000个字符的简单varchar，它看起来还不错.
我有3个表:1st-具有ItemID的项目，2nd-具有ItemID和DescriptionID的Descriptionpack，第3个具有DescriptionID的描述.
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.
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
我认为我发现了问题，我对提供者说了我的问题，他们回答了这个问题:
I think i found the problem, i said my problem to my provider and they answer me this:
我与我们的托管团队一起审查了您的问题.你将无法 更改该变量和其他变量的全局设置.然而， 您应该可以在每个会话的基础上通过以下方式设置该变量: 首先设置它，然后再进行其他查询.希望能有所帮助.
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.
推荐答案
大概是在使用GROUP_CONCAT()，而不是简单的CONCAT().
Presumably you're using GROUP_CONCAT(), not simple CONCAT().
group_concat_max_len的默认值是1024，如果要建立大的长串联，则这是一个很小的限制.
The default value of the group_concat_max_len is 1024, which is a pretty small limit if you're building up big long concatenations.
要更改它，请使用此命令.在此示例中，我将长度设置为100,000.您可以将其设置为所需的任何内容.
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.
SET SESSION group_concat_max_len = 100000;
max_allowed_packet的通常值为1兆字节，可能超出您的需求.
The usual value for max_allowed_packet is one megabyte, which is likely more than you need.
group_concat_max_len本身具有有效的限制大小.它仅受平台的无符号字长限制:在32位平台上为2 ^ 32-1，在64位平台上为2 ^ 64-1.
group_concat_max_len itself 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.
如果这仍不足以满足您的应用程序，那么该接受@eggyal的建议并重新考虑您的方法了.
If that still isn't enough for your application, it's time to take @eggyal's suggestion and rethink your approach.
这篇关于MYSQL CONCAT最大长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
