Comments on: SQL trick: overcoming GROUP_CONCAT limitation in special cases https://shlomi-noach.github.io/blog/mysql/sql-trick-overcoming-group_concat-limitation-in-special-cases Blog by Shlomi Noach Mon, 07 Nov 2011 14:22:06 +0000 hourly 1 https://wordpress.org/?v=5.3.3 By: marcin https://shlomi-noach.github.io/blog/mysql/sql-trick-overcoming-group_concat-limitation-in-special-cases/comment-page-1#comment-57926 Mon, 07 Nov 2011 14:22:06 +0000 https://shlomi-noach.github.io/blog/?p=2580#comment-57926 you’re right

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/sql-trick-overcoming-group_concat-limitation-in-special-cases/comment-page-1#comment-57913 Mon, 07 Nov 2011 12:27:16 +0000 https://shlomi-noach.github.io/blog/?p=2580#comment-57913 @marcin,
I wrote: “…I will follow the path where I assume I cannot control group_concat_max_len, not even in session scope…”

Yes, if you have control, just change group_concat_max_len. This is not always the case. For example, I provide a schema with views which I expect you to install on your server. I cannot change your session variable since I am not the one invoking queries; it is YOU who is invoking queries using my own views. There’s a hack around this, as well, and an ugly one as well.

]]> By: marcin https://shlomi-noach.github.io/blog/mysql/sql-trick-overcoming-group_concat-limitation-in-special-cases/comment-page-1#comment-57909 Mon, 07 Nov 2011 11:54:55 +0000 https://shlomi-noach.github.io/blog/?p=2580#comment-57909 before executing query try execute SET SESSION group_concat_max_len = 20000

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/sql-trick-overcoming-group_concat-limitation-in-special-cases/comment-page-1#comment-16004 Thu, 22 Jul 2010 06:12:45 +0000 https://shlomi-noach.github.io/blog/?p=2580#comment-16004 @strcmp: for completeness, the trick of CONCAT still makes it more robust, since otherwise you must add you own “,” separators between the GROUP_CONCAT parts; should any of them be empty, the manually added separators be invalid.

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/sql-trick-overcoming-group_concat-limitation-in-special-cases/comment-page-1#comment-16003 Thu, 22 Jul 2010 06:11:13 +0000 https://shlomi-noach.github.io/blog/?p=2580#comment-16003 @strcmp: wow! Thanks for this

]]> By: strcmp https://shlomi-noach.github.io/blog/mysql/sql-trick-overcoming-group_concat-limitation-in-special-cases/comment-page-1#comment-16001 Thu, 22 Jul 2010 05:45:00 +0000 https://shlomi-noach.github.io/blog/?p=2580#comment-16001 You can use GROUP_CONCAT(IF(actor_id BETWEEN 1 AND 100, last_name, NULL)) instead of GROUP_CONCAT(IF(actor_id BETWEEN 1 AND 100, last_name, ”)) to get rid of the extra separators. ” is a normal value that will just be concatenated, NULL will be skipped.

]]>