MySQL:从INSERT SELECT获取返回结果 [英] MySQL: Get a return result from an INSERT SELECT

问题描述

我有疑问要插入显式值和SELECTd内容.我也在做基本的增量操作.

I have query that INSERTS both explicit values and SELECTd content. I'm also doing basic incrementing.

INSERT INTO `table` (`myID`, `myVal1`, `myVal2`) SELECT `myID` + 1, 'explValHere', 'otherValThere')
FROM `table` ORDER BY `myID` DESC LIMIT 0,1

我正在执行此操作，因为该表具有多个ID，并且在特定列中递增.因此，正如您首先要说的那样，我不能使用自动递增和insert_id.

I am doing this as the table has multiple id's and incrementing within a specific column. So I can't, as you would first say, use auto incrementing and insert_id.

当然的问题是插入不返回选择，但是可以吗?有没有一种方法可以运行此插入查询并返回任何结果?

The problem of course is the insert doesn't return the select, but can it? Is there a way of running this insert query and returning any of the result?

推荐答案

由于查询中包含LIMIT 1，因此您可以将结果"存储在

Since your query has a LIMIT 1 you could store the "result" in a session/user-defined variable. Still two queries but reentrant; each connection is its own session.

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
setup($pdo);

$query = "
    INSERT INTO tbl1 (myID, myVal1, myVal2)
    SELECT @foo:=myID+1, 'val1', 'val2' FROM tbl2 WHERE x=0 LIMIT 1
";
$pdo->exec($query);
foreach( $pdo->query('SELECT @foo as foo') as $row ) {
    echo $row['foo'];
}


function setup($pdo) {
    $pdo->exec('CREATE TEMPORARY TABLE tbl1 (myID int, myVal1 varchar(16), myVal2 varchar(16))');
    $pdo->exec('CREATE TEMPORARY TABLE tbl2 (myID int, x int)');
    $pdo->exec('INSERT INTO tbl2 (myID, x) VALUES (1,1),(2,1),(3,0),(4,0),(5,1)');
}

tbl2中x = 0的第一"记录为(myID = 3，x = 0)，脚本打印4.

the "first" record in tbl2 having x=0 is (myID=3,x=0) and the script prints 4.

除了那个和存储过程等之外，(据我所知)没有像SQLServer的 OUTPUT 或Postgresql的 RETURNING 子句.

Other than that and stored procedures et al there's (to my knowledge) nothing like SQLServer's OUTPUT or postgresql's RETURNING clause for MySQL.

这篇关于MySQL:从INSERT SELECT获取返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！