- 用例是这样的.
- 有一个注册端点，用户将在该端点上提交邮箱和密码
- 必须同时创建4行并生成几个id
- 桌子看起来是这样的
authentication_types
| id | name |
|---|---|
| uuid | varchar |
| primary key | |
| 0aa4d9a9-e024-4792-bc41-36a4f3528d36 | password |
accounts
| id | password | ...few other columns | authentication_type_id | |
|---|---|---|---|---|
| uuid | varchar | varchar | uuid | |
| primary key | unique | foreign key to authentication_types | ||
| 7a9d912a-69ab-4615-9058-e1bb1c4e36c5 | password | ... | ... | 0aa4d9a9-e024-4792-bc41-36a4f3528d36 |
users
| id | enabled |
|---|---|
| uuid | boolean |
| primary key | |
| fc9ca826-63dc-43b8-97b6-2e949ffd8a30 | true |
user_accounts
| id | account_id | user_id |
|---|---|---|
| uuid | uuid | uuid |
| primary key | foreign key to accounts | foreign key to users |
| bd4b338f-1b5a-4b24-9908-e5cfb4080dd4 | 7a9d912a-69ab-4615-9058-e1bb1c4e36c5 | fc9ca826-63dc-43b8-97b6-2e949ffd8a30 |
verification_tokens
| id | expires | token | account_id |
|---|---|---|---|
| uuid | timestamptz | varchar | uuid |
| primary key | unique | foreign key to accounts | |
| 865a6389-67ea-4e38-a48f-9f4b60ffe816 | ... | ... | 7a9d912a-69ab-4615-9058-e1bb1c4e36c5 |
当新注册发生时，我想做以下事情
- 生成uid并将行插入帐户
- 生成uid并将行插入用户
- 生成uuid +从帐户和用户获取插入行的id并插入到user_accounts
- 生成上面生成的uuid + token +帐户ID并插入verification_token
这是我深思熟虑后提出的疑问.获取密码的身份验证类型id，并在每一步中运行带有一些uuid生成的CTE.有没有方法进一步优化它？
WITH account_data(id, email, password, authentication_type_id) AS (
VALUES( gen_random_uuid()
,:email
,:password
,(SELECT id
FROM authentication_types
WHERE name = :authenticationTypeName) ) )
,ins1(user_id) AS (
INSERT INTO users(id, enabled)
VALUES( gen_random_uuid()
,true)
RETURNING id AS user_id )
,ins2(account_id) AS (
INSERT INTO accounts (id, email, password, authentication_type_id)
SELECT id
,email
,password
,authentication_type_id
FROM account_data
RETURNING id AS account_id )
,ins3 AS (
INSERT INTO user_accounts (id, account_id, user_id)
VALUES( gen_random_uuid()
,(SELECT account_id
FROM ins2)
,(SELECT user_id
FROM ins1) ) )
INSERT INTO verification_tokens (id, token, account_id)
VALUES( gen_random_uuid()
,:token
,(SELECT account_id
FROM ins2) )
RETURNING (SELECT account_id FROM ins2) AS id
如果有更快的方法来执行上述查询，我会很高兴听到.提前感谢您的帮助
EDIT 1
这是一个包含实际数据的示例查询
WITH account_data(id, email, password, authentication_type_id) AS (
VALUES( gen_random_uuid()
,'abc@example.com'
,'$2a$12$71qwp8jK0z6EVbpe9FZSIeHQkYh4Wtn2sOxH/Y3xuEhSLP1IjPAEK'
,(SELECT id
FROM authentication_types
WHERE name = 'password') ) )
,ins1(user_id) AS (
INSERT INTO users(id, enabled)
VALUES( gen_random_uuid()
,true)
RETURNING id AS user_id )
,ins2(account_id) AS (
INSERT INTO accounts (id, email, password, authentication_type_id)
SELECT id
,email
,password
,authentication_type_id
FROM account_data
RETURNING id AS account_id )
,ins3 AS (
INSERT INTO user_accounts (id, account_id, user_id)
VALUES( gen_random_uuid()
,(SELECT account_id
FROM ins2)
,(SELECT user_id
FROM ins1) ) )
INSERT INTO verification_tokens (id, token, account_id)
VALUES( gen_random_uuid()
,'$2a$12$VNP/ya5ILSP.yYjSa3anyuZhm8Jxc97Y3p95grDjS/Xe1XjBmX/VK'
,(SELECT account_id
FROM ins2) )
RETURNING (SELECT account_id FROM ins2) AS id
这是视觉查询分析器
这是解释分析结果
