xnscu

Appearance

如何一个事务内插入和返回数据(包含关联数据) ​

代码 ​

sql
WITH
  doc AS (
    INSERT INTO
      doc (usr_id, name)
    VALUES
      (1, 'doc1')
    RETURNING
      *
  )
SELECT
  T.name,
  T.usr_id,
  T1.name AS usr_id__name
FROM
  doc T
  LEFT JOIN usr T1 ON (T.usr_id = T1.id)

这条SQL语句的设计巧妙之处在于它 在一个SQL查询中完成了数据插入并立即返回新插入的数据以及关联数据，极大地提高了效率和代码的简洁性。 我们来逐层分析：

1. 使用 CTE (Common Table Expression) WITH doc AS (...):

  • 模块化和可读性: CTE doc 将插入操作封装起来，使其成为一个独立的逻辑单元。这提高了整个SQL语句的可读性和结构性。我们可以先理解 doc CTE 的作用，再看后面的 SELECT 语句如何使用它。
  • 临时结果集: doc CTE 创建了一个临时的结果集，这个结果集在后续的 SELECT 语句中可以像一个普通的表一样被引用。

2. INSERT INTO doc ( usr_id, name ) VALUES ( 1, 'doc1' ) RETURNING *:

  • 数据插入: INSERT INTO doc ( usr_id, name ) VALUES ( 1, 'doc1' ) 这部分是标准的 INSERT 语句，向 doc 表中插入一条新的记录，usr_id 为 1，name 为 'doc1'。
  • RETURNING * 的关键作用: RETURNING * 是 PostgreSQL (以及一些其他数据库如 Oracle, SQL Server) 的一个非常强大的特性。它允许 INSERT, UPDATE, DELETE 语句返回受影响的行的数据。
    • 避免额外的查询: 如果没有 RETURNING *，通常在插入数据后，如果需要获取新插入行的信息（例如自增ID或其他默认值），就需要执行一个额外的 SELECT 查询。RETURNING * 一步到位，直接返回新插入的行的所有列的数据。
    • 原子性: INSERT 和 RETURNING 操作在同一个语句中完成，保证了操作的原子性。要么插入成功并返回数据，要么插入失败，不会出现插入成功但无法立即获取数据的情况。
    • 效率提升: 减少了数据库交互次数，提高了效率，尤其是在需要立即使用新插入数据的场景下。

3. SELECT T.name, T.usr_id, T1.xm AS usr_id__xm FROM doc T LEFT JOIN usr T1 ON (T.usr_id = T1.id):

  • 利用 CTE 结果集: FROM doc T 这里 doc 就是前面 CTE 定义的临时结果集。 T 是 doc 的别名。 这意味着 SELECT 语句的数据来源是刚刚插入到 doc 表并由 RETURNING * 返回的数据。
  • LEFT JOIN usr T1 ON (T.usr_id = T1.id):
    • 关联查询: 使用 LEFT JOIN 将 doc 表（别名 T）和 usr 表（别名 T1）关联起来，关联条件是 doc.usr_id = usr.id。这表明 doc 表的 usr_id 字段很可能是 usr 表的 id 字段的外键，表示文档属于某个用户。
    • LEFT JOIN 的选择: 使用 LEFT JOIN 而不是 INNER JOIN 意味着即使在 usr 表中找不到 usr_id 为 1 的用户记录，查询仍然会返回 doc 表的数据，只是 T1.xm (用户姓名) 会是 NULL。这在某些场景下可能更健壮，例如允许文档关联的用户信息暂时缺失。
  • SELECT T.name, T.usr_id, T1.xm AS usr_id__xm:
    • 选择需要的列: 选择了 doc 表的 name 和 usr_id 列，以及 usr 表的 xm 列（并将其别名为 usr_id__xm，可能表示用户姓名）。
    • 数据整合: 最终的 SELECT 语句将新插入的文档信息和关联的用户信息整合在一起返回。

总结巧妙之处:

  1. 原子性操作: 在一个SQL语句中完成数据插入和关联查询，保证了操作的原子性。
  2. 高效性: 使用 RETURNING * 避免了额外的 SELECT 查询，减少了数据库交互次数，提高了效率。
  3. 简洁性: 代码简洁明了，逻辑清晰，易于理解和维护。
  4. 可读性: 使用 CTE 将插入操作封装，提高了 SQL 语句的可读性和结构性。
  5. 数据整合: 一步到位地返回新插入的数据以及关联数据，方便后续程序直接使用。

应用场景:

这种设计模式非常适合以下场景：

  • 需要立即获取新插入数据并进行后续操作的场景: 例如，创建文档后需要立即显示文档信息和作者信息。
  • 需要保证数据操作原子性的场景: 例如，在事务中需要插入数据并立即获取其信息。
  • 需要减少数据库交互次数，提高性能的场景: 例如，在高并发环境下，减少数据库 round-trip 可以显著提升性能。

总而言之，这条 SQL 语句巧妙地利用了 CTE 和 RETURNING * 特性，以简洁高效的方式完成了数据插入和关联查询，体现了现代 SQL 数据库的强大功能和灵活应用。

完整代码 ​

sql
create table
  usr (id serial primary key, name text);

create table
  doc (
    id serial primary key,
    usr_id int references usr (id),
    name text
  );

insert into usr(name) values ('foo');

WITH
  doc AS (
    INSERT INTO
      doc (usr_id, name)
    VALUES
      (1, 'doc1')
    RETURNING
      *
  )
SELECT
  T.name,
  T.usr_id,
  T1.name AS usr_id__name
FROM
  doc T
  LEFT JOIN usr T1 ON (T.usr_id = T1.id)

version 0.2.0