迹忆客 专注技术分享

本文讨论如何在 PostgreSQL 查询中转义单引号。

在 PostgreSQL 中转义单引号

考虑一个记录用户评论的评论表。该表有 5 个字段：id、userid、postid、comments、commentdate，如下所示：

|id | userid  | postid  | comments                       | commentdate
|---|-------- |---------|--------------------------------|---------------------
|1  | 1       |  1      | The post is great              | 07-02-2022 11:03:05
|2  | 2       |  1      | We've found the right post     | 07-02-2022 01:17:02
|3  | 3       |  3      | I'm working on a related post  | 08-02-2022 09:12:17
|4  | 4       |  3      | Excellent post                 | 08-02-2022 12:04:01
|5  | 5       |  4      | The post's title is impressive | 09-02-2022 16:23:09

我们将在上面的示例中创建表。这是评论表的 CREATE 语句：

CREATE TABLE comments
(
    id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
    userid INT NOT NULL,
    postid INT NOT NULL,
    comments TEXT NOT NULL,
    commentdate TIMESTAMP NOT NULL,
    CONSTRAINT comment_pkey PRIMARY KEY (id)
)

创建表后，我们将在上面示例的第一行中插入值。下面是第一行的 INSERT 语句：

INSERT INTO comments (userid, postid, comments, commentdate)
VALUES (1, 1, 'The post is great', '07-02-2022 11:03:05');

此查询插入成功。

接下来，让我们在第二行中插入值。下面是 INSERT 语句：

INSERT INTO comments (userid, postid, comments, commentdate)
VALUES (2, 1, 'We've found the right post', '07-02-2022 01:17:02');

当我们尝试执行上面的语句时，会抛出一个语法错误，如下所示：

ERROR:  syntax error at or near "ve"
LINE 1: ... postid, comments, commentdate) VALUES (2, 1, 'We've found t...

PostgreSQL 无法理解 We 之后的单词，因为它假定 We 后的单引号表示字符串的结尾。第 3 行和第 5 行将给出类似的错误，因为它们在 comments 字段中都有单引号。

下面是插入示例中所有行的语句：

INSERT INTO comments (userid, postid, comments, commentdate)
VALUES
    (1, 1, 'The post is great', '07-02-2022 11:03:05'),
    (2, 1, 'We've found the right post', '07-02-2022 01:17:02'),
    (3, 3, 'I'm working on a related post', '08-02-2022 09:12:17'),
    (4, 3, 'Excellent post', '08-02-2022 12:04:01'),
    (5, 4, 'The post's title is impressive', '09-02-2022 16:23:09');

上面的语句将给出与仅插入第二行时的错误相同的错误。

解决此问题的一种方法是转义单引号，这可以通过以下方式完成：

1. 另一个单引号
2. 反斜杠
3. 美元引号

在 PostgreSQL 中使用另一个单引号转义一个单引号

可以通过编写单引号后跟要转义的单引号以转义形式指定单引号。此解决方案显示在这里：

INSERT INTO comments (userid, postid, comments, commentdate)
VALUES (2, 1, 'We''ve found the right post', '07-02-2022 01:17:02');

将上述语句中的所有单引号转义的语句如下所示：

INSERT INTO comments (userid, postid, comments, commentdate)
VALUES
    (1, 1, 'The post is great', '07-02-2022 11:03:05'),
    (2, 1, 'We''ve found the right post', '07-02-2022 01:17:02'),
    (3, 3, 'I''m working on a related post', '08-02-2022 09:12:17'),
    (4, 3, 'Excellent post', '08-02-2022 12:04:01'),
    (5, 4, 'The post''s title is impressive', '09-02-2022 16:23:09');

输出：

|id | userid  | postid  | comments                       | commentdate
|---|-------- |---------|--------------------------------|---------------------
|1  | 1       |  1      | The post is great              | 07-02-2022 11:03:05
|2  | 2       |  1      | We've found the right post     | 07-02-2022 01:17:02
|3  | 3       |  3      | I'm working on a related post  | 08-02-2022 09:12:17
|4  | 4       |  3      | Excellent post                 | 08-02-2022 12:04:01
|5  | 5       |  4      | The post's title is impressive | 09-02-2022 16:23:09

在 PostgreSQL 中使用反斜杠转义单引号

要使用反斜杠转义单引号，你必须在字符串之前放置 E 符号，这是我们示例中的注释，并在要转义的单引号之前放置反斜杠，如下所示：

INSERT INTO comments (userid, postid, comments, commentdate)
VALUES
    (1, 1, 'The post is great', '07-02-2022 11:03:05'),
    (2, 1, E'We\'ve found the right post', '07-02-2022 01:17:02'),
    (3, 3, E'I\'m working on a related post', '08-02-2022 09:12:17'),
    (4, 3, 'Excellent post', '08-02-2022 12:04:01'),
    (5, 4, E'The post\'s title is impressive', '09-02-2022 16:23:09');

输出：

|id | userid  | postid  | comments                       | commentdate
|---|-------- |---------|--------------------------------|---------------------
|1  | 1       |  1      | The post is great              | 07-02-2022 11:03:05
|2  | 2       |  1      | We've found the right post     | 07-02-2022 01:17:02
|3  | 3       |  3      | I'm working on a related post  | 08-02-2022 09:12:17
|4  | 4       |  3      | Excellent post                 | 08-02-2022 12:04:01
|5  | 5       |  4      | The post's title is impressive | 09-02-2022 16:23:09

在 PostgreSQL 中通过美元引号转义单引号

如果你想要一个更具可读性的解决方案，特别是当存在多个单引号时，可以使用美元引号。

如果字符串中有更多单引号，则美元引号可以使解决方案可读。美元引用使用美元符号、可选标记、字符串（在本例中为注释），然后是另一个美元符号、可选标记和结束美元符号。

单引号可以在用美元引用的字符串中使用，而不会被转义。可以使用美元引用插入一行，如下所示：

INSERT INTO comments (userid, postid, comments, commentdate)
VALUES (6, 5, $$'I've shared the post. It's quite impressive'$$, '09-02-2022 16:34:17')

这是官方文档以了解有关 PostgreSQL 字符串常量及其转义的更多信息。