GROUP_CONCAT and truncation / field serialization in MySQL

I wish there were a true serialize() grouping method in MySQL, as I frequently deal with data that must be viewed as groups of tables or groups of groups of tables, and sometimes even deeper. One hacky way I occasionally use (only when there is really no other alternative, because this sucks) to accomplish this without having to resort to recursion is

GROUP_CONCAT(items, '|||')

Most datasets I work with are sane enough to safely assume I won’t find 3x consecutive pipe characters in a given field. So, I simply explode that field out into an array.

$data = array();
while ($row = $result->fetch()) {
   $row['items'] = explode('|||', $row['items']);
   // or, to eliminate null columns / empty string values:
   //$row['items'] = preg_split('/\|\|\|/', $row['items'], -1, PREG_SPLIT_NO_EMPTY);
   $data[] = $row;

One of the problems you will run into, though, as your datasets start to get a little larger, is that this is an insufficient solution since GROUP_CONCAT() truncates each row by default (on most systems) by 1024 characters. Not to worry — there is a very simple fix to this.. simply increase the value of group_concat_max_len.

DB::mysql()->exec("SET SESSION group_concat_max_len = " . PHP_INT_MAX);

Maybe someday there will be a more elegant solution to what should be an unusual problem, as it seems I come up with a nasty solution like this on a weekly basis these days.

Next on the list should be a MODE() aggregate function. Like, for real.. tell me that’s harder to implement than STDDEV_POP().

  1. No comments yet.

  1. No trackbacks yet.