MySQL GROUP_CONCAT转义

(注意:这个问题不是关于转义查询,而是关于转义结果)

我正在使用GROUP_CONCAT将多行组合成逗号分隔的列表.例如,假设我有两个(示例)表:

CREATE TABLE IF NOT EXISTS `Comment` (
`id` int(11) unsigned NOT NULL auto_increment,
`post_id` int(11) unsigned NOT NULL,
`name` varchar(255) collate utf8_unicode_ci NOT NULL,
`comment` varchar(255) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY  (`id`),
KEY `post_id` (`post_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6 ;

INSERT INTO `Comment` (`id`, `post_id`, `name`, `comment`) VALUES
(1, 1, 'bill', 'some comment'),
(2, 1, 'john', 'another comment'),
(3, 2, 'bill', 'blah'),
(4, 3, 'john', 'asdf'),
(5, 4, 'x', 'asdf');


CREATE TABLE IF NOT EXISTS `Post` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(255) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;

INSERT INTO `Post` (`id`, `title`) VALUES
(1, 'first post'),
(2, 'second post'),
(3, 'third post'),
(4, 'fourth post'),
(5, 'fifth post'),
(6, 'sixth post');

我想列出所有帖子以及评论帖子的每个用户名列表:

SELECT
Post.id as post_id, Post.title as title, GROUP_CONCAT(name) 
FROM Post 
LEFT JOIN Comment on Comment.post_id = Post.id
GROUP BY Post.id

给我:

id  title   GROUP_CONCAT( name )
1   first post  bill,john
2   second post     bill
3   third post  john
4   fourth post     x
5   fifth post  NULL
6   sixth post  NULL

这很好用,除非用户名包含逗号,否则会破坏用户列表.MySQL有没有让我逃脱这些角色的功能？(请假设用户名可以包含任何字符,因为这只是一个示例模式)

实际上,ascii control characters专门设计用于分离数据库字段和记录:

0x1F (31): unit (fields) separator

0x1E (30): record separator

0x1D (29): group separator

阅读更多:关于ascii字符

你永远不会在用户名中拥有它们,并且很可能永远不会在non-binary data数据库中的任何其他用户名中,因此可以安全地使用它们:

GROUP_CONCAT(foo SEPARATOR 0x1D)

然后用CHAR(0x1D)你想要的任何客户语言分开.

如果某些其他字符在用户名中是非法的,则可以使用鲜为人知的语法指定不同的分隔符:

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

你想要管道吗？还是任何角色？

转义分隔符,可能使用反斜杠,但在执行转义反斜杠之前:

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

这将:

用另一个反斜杠转义任何反斜杠

使用反斜杠转义分隔符

将结果与分隔符字符连接起来

要获得未转义的结果,请按相反的顺序执行相同的操作:

将结果拆分为不带反斜杠的分隔符.实际上,它有点棘手,你想把它分开,前面没有奇数个黑色斜线.这个正则表达式将匹配:
(?

用文字替换所有转义的分隔符字符,即替换\ | 与|

用单个反斜杠替换所有双反斜杠,例如将\替换为\