为什么MySQL中GROUP

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了为什么MySQL中GROUP，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4087字，纯文字阅读大概需要6分钟。

内容图文

为什么 MySQL 中 GROUP_CONCAT 函数返回 BLOB 大对象类型？（Why GROUP_CONCAT returns BLOB?） 太阳火神的美丽人生 (http://blog.csdn.net/opengl_es) 本文遵循“署名-非商业用途-保持一致”创作公用协议 太阳火神的美丽人生 - 本博客专注于 敏捷开发及移动

为什么 MySQL 中 GROUP_CONCAT 函数返回 BLOB 大对象类型？（Why GROUP_CONCAT returns BLOB?）

太阳火神的美丽人生 (http://blog.csdn.net/opengl_es)

本文遵循“署名-非商业用途-保持一致”创作公用协议

太阳火神的美丽人生 - 本博客专注于 敏捷开发及移动和物联设备研究：iOS、Android、Html5、Arduino、pcDuino，

相似的文章列于下方，

我的问题是 CONCAT(字段名, '')，这个用法，就是为了将字段名所代表的数值字段转换成字符串结果。

那么，从以下三楼回复中，我们可以摘出：CAST(user_id AS CHAR)

用 CAST 函数来将某个字段的值转换成字符型。

问题出现的原因是，CONCAT 的函数计算结果以字节形式返回，就造成被误认为是 BLOB 二进制的大对象。

所以，转换成字符串时，慎用该函数，而使用 CAST 进行转换。

使用 WorkBench 这类工具时，会有一个设置选项，可以把 二进制或可变二进制返回结果当成非二进制的字符串来看待，这样这类工具执行类似SQL语句，返回的函数计算字段值就不会被当成 BLOB 看待，而是直接显示其字符串值了。

Why GROUP_CONCAT returns BLOB?

Last week, when using the GROUP_CONCAT() function on a MySQL database, I got an unexpected result.

Indeed, instead of getting my result as VARCHAR types, I got it as BLOB types! For information, a BLOB is a binary large object that can hold a variable amount of data:
http://dev.mysql.com/doc/refman/5.0/en/blob.html
Because BLOB values are treated as binary strings, it is not easy to use. This is why we would prefer to haveVARCHAR values.

So the question is how to get around this frustrating problem?

The answer is, for once, very simple!
You simply need to:

• Open your my.ini or my.cnf file;
• Change the value of the group_concat_max_len system variable to 512 (no ‘k’ suffix);
• Restart the mysql service

To verify if the value has been successfully updated, execute the following command in your mysql client:

Note that you cannot set the value of group_concat_max_len to less than 1Kb using the MySQL Administrator GUI. Which means that the only way to set this system variable to 512 (which is less than 1Kb) is to edit your MySQL configuration file as described above.

blob, group_concat(), group_concat_max_len, MySQL, varchar

This entry was posted on 11 Feb 2010, 19:11 and is filed under MySQL, Tricks. You can follow any responses to this entry through RSS 2.0. You can leave a response, or trackback from your own site.

• COMMENTS (5)
• RELATED POSTS

1. #1 by Gerald Mengisen on 15 Sep 2010 - 20:34

   The solution above doesn’t quite work for shared hosting. However, the solution in the link below worked for me (I’m just adding this here because your entry was one of the first ones in Google about this problem):
   http://stackoverflow.com/questions/2133936/using-group-concat-in-phpmyadmin-will-show-the-result-as-blob-3b

2. #2 by smoreau on 15 Sep 2010 - 22:52

   Thank you very much for sharing this link.
   Indeed, it wouldn’t be possible to change the value of a system variable on a shared hosting.
   It is actually a nice trick to convert the column value to a string !

3. #3 by mart on 13 Apr 2011 - 11:25

   Had the same problem, found answer
   SELECT rec_id, GROUP_CONCAT(CAST(user_id AS CHAR))
   FROM t1
   GROUP BY rec_id

   @ http://stackoverflow.com/questions/2133936/using-group-concat-in-phpmyadmin-will-show-the-result-as-blob-3b

4. #4 by RakonDark on 13 Jul 2012 - 17:46

   Dear mart
   I found a problem with your CAST on MySQL

   when i have INT and i Gdo this
   SELECT rec_id, GROUP_CONCAT(CAST(user_id AS CHAR))
   FROM t1
   GROUP BY rec_id

   then i have always only 1 char space for the int value , this may work for 0-9 but not when your INT grow

   for me it was fine to CAST as CHAR(7) to hold the
   biggest value 9999999 value from my INT:)

   SELECT rec_id, GROUP_CONCAT(CAST(user_id AS CHAR(7)))
   FROM t1
   GROUP BY rec_id

5. 
   

内容总结

以上是互联网集市为您收集整理的为什么MySQL中GROUP全部内容，希望文章能够帮你解决为什么MySQL中GROUP所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端