(注意:这个问题不是关于转义查询,而是关于转义结果)
我正在使用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是否具有可以让我转义这些字符的功能?(请假定用户名可以包含任何字符,因为这只是示例架构)
如果用户名中还有其他非法字符,则可以使用鲜为人知的语法指定其他分隔符:
...GROUP_CONCAT(name SEPARATOR '|')...
…您要允许管道吗?或任何字符?
转义分隔符,也许用反斜杠,但是在这样做之前,转义自己反斜杠:
group_concat(replace(replace(name, '\\', '\\\\'), '|', '\\|') SEPARATOR '|')
这将:
要获得未转义的结果,请以相反的顺序执行相同的操作:
用分隔符(不带反斜杠)分隔结果。实际上,这有点棘手,您希望将其拆分成没有 奇数个 黑杠的位置。此正则表达式将匹配以下内容: (?<!\\)(?:\\\\)*\|
(?<!\\)(?:\\\\)*\|
用文字替换所有转义的分隔符,即替换\ | 与|