时间戳

首页 > 解决方案 > MariaDB/MySQL 中的 CTE 中类似 PostgreSQL 的返回修改语句管道/使用

mysql - MariaDB/MySQL 中的 CTE 中类似 PostgreSQL 的返回修改语句管道/使用

问题描述

试图insert .. returning ..在 MariaDB/MySQL 中找到一种不复杂的方式来管道/链接返回修改语句（例如）。这是有效的 PostgreSQL 代码：

创建 2 个表：

create table test1(
    id serial primary key,
    value int
);

create table test2(
    ref_id int,
    value varchar
);

使用with，将 的输出通过管道传输insert .. returning ..到另一个insert：

with
    cte as (
        insert into test1(value)
            select floor(random()*10) as value
        returning *
    )
insert into test2(ref_id, value)
    select
            id,
            concat('Inserted value is ', value::varchar) as value
        from cte
;

编辑：为简单起见，该示例仅插入一行，但一般用例将插入多行（通常至少数千行）。要模拟真实世界的用例，请添加from generate_series(1,1000000)到select.

test1测试从（由公平掷骰子选择）返回 (1, 4)和 (1, 'Inserted value is 4') 从test2(注意 1 intest2是预先由 's 序列/自动增量动态生成的test1，不能指定为文字）。

这在需要修改以某种方式逻辑连接的单独表中的数据时非常有用（例如，插入一个值，返回一个串行/自动增量 id，然后将该 id 作为引用插入到其他表中）。我通常会在 PostgreSQL 中以这种方式链接十几个插入和更新：

with
    cte1 as (insert .. returning ..),
    cte2 as (update .. from cte1 .. returning ..),
    cte3 as (delete .. from cte2 .. returning ..)
...

但是，with似乎仅限select于 MariaDB 中的 CTE，因此基本上一次仅限于一个修改语句，并且无法通过管道insert .. returning ..进入进一步的语句（不，子查询也不起作用）。
有没有办法在没有外部工具的情况下完成此操作（例如通过 PHP 存储返回值，然后发出进一步的语句，如果查询正在处理数千或数百万行，这可能会产生可怕的性能）？

请记住，如果插入的行没有严格的唯一键，则可能无法保证以后正确选择插入的行（尤其是不断写入表），因此insert .. returning ..在我的用例。

迄今为止研究的潜在解决方案

标签： mysqlsqlpostgresqlmariadbsql-insert

解决方案

MySQL/MariaDB 不支持returning或类似的，所以你必须在这里运行多个语句。

但是，您可以使用last_insert_id()来恢复最后一个自动生成的值，因此您可以执行以下操作：

insert into test1(value) 
values (floor(rand() * 10));

insert into test2 (ref_id, value) 
select id, value
from test1
where id = last_insert_id();

推荐阅读