MySQL GROUP_CONCAT转义 [英] MySQL GROUP_CONCAT escaping

查看:420
本文介绍了MySQL GROUP_CONCAT转义的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

(NOTE: This question is not about escaping queries, it's about escaping results)

我使用 GROUP_CONCAT 将多行结合成逗号分隔列表。例如,假设我有两个(示例)表:

I'm using GROUP_CONCAT to combine multiple rows into a comma delimited list. For example, assume I have the two (example) tables:

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');

我想列出所有帖子以及每个用户名的列表,该列表对该帖子发表了评论: / p>

And I want to list all posts along with a list of each username who commented on the 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有一个让我逃避这些角色的功能吗? (请假设用户名可以包含任何字符,因为这只是一个示例模式)

This works great, except that if a username contains a comma it will ruin the list of users. Does MySQL have a function that will let me escape these characters? (Please assume usernames can contain any characters, since this is only an example schema)

推荐答案

如果还有一些其他角色是非法的您可以使用一些不明确的语法来指定其他分隔符:

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 '|')

这将:


  1. 使用其他反斜杠转义任何反斜杠

  2. 使用反斜杠
  3. 转换分隔符字符
  4. 将结果与分隔符相连

  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. 将结果拆分为前面没有反斜杠的分隔符。实际上,这有点棘手,你想把它拆分出来,之前没有一个黑色的奇数。这个正则表达式将匹配:

    (?<!\\)(?: \\\\)* \ |

  2. 将所有转义的分隔符字符替换为文字,即替换\ |用|

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

  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 \

这篇关于MySQL GROUP_CONCAT转义的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆