MySQL GROUP_CONCAT escaping

Actually, there are ascii control characters specifically designed for separating database fields and records:

0x1F (31): unit (fields) separator0x1E (30): record separator0x1D (29): group separator

Read more: about ascii characters

You will never have them in usernames and most probably never in any other non-binary data in your database so they can be used safely:

GROUP_CONCAT(foo SEPARATOR 0x1D)

Then split by CHAR(0x1D) in whatever client language you want.

If there's some other character that's illegal in usernames, you can specify a different separator character using a little-known syntax:

...GROUP_CONCAT(name SEPARATOR '|')...

... You want to allow pipes? or any character?

Escape the separator character, perhaps with backslash, but before doing that escape backslashes themselves:

group_concat(replace(replace(name, '\\', '\\\\'), '|', '\\|') SEPARATOR '|')

This will:

1. escape any backslashes with another backslash
2. escape the separator character with a backslash
3. concatenate the results with the separator character

To get the unescaped results, do the same thing in the reverse order:

1. split the results by the separator character where not preceded by a backslash. Actually, it's a little tricky, you want to split it where it isn't preceded by an odd number of blackslashes. This regex will match that:
   (?<!\\)(?:\\\\)*\|
2. replace all escaped separator chars with literals, i.e. replace \| with |
3. replace all double backslashes with singe backslashes, e.g. replace \\ with \

REPLACE()

Example:

... GROUP_CONCAT(REPLACE(name, ',', '\\,')) 

Note you have to use a double-backslash (if you escape the comma with backslash) because backslash itself is magic, and \, becomes simply ,.