在mysqli中输出Inserted.row

我有以下sql表:

ID |电子邮件| FBID

当我执行查询

 INSERT INTO users(email,fbid) VALUES('randomvalue','otherrandomvalue')

我想获取插入行的id.为此,我尝试编辑这样的查询:

 INSERT INTO users(email,fbid) VALUES('randomvalue','otherrandomvalue') OUTPUT Inserted.id

但是我得到了:

1064 - 您的SQL语法出错; 检查与MySQL服务器版本对应的手册,以便在第1行的"OUTPUT Inserted.id"附近使用正确的语法

可能是什么问题呢？

我将使用我在下面描述的过程处理与我为个人使用而编写的私人家庭(非企业)应用程序相同的情况.我知道这个问题现在已经有一年了,但批量处理似乎没有足够的答案.我找不到合适的答案.MySQL似乎没有内置设施来处理这类事情.

我担心这个解决方案的可靠性,当放入生产环境中时,多个不同的用户/作业可以同时访问相同的程序来进行插入.我相信我通过在@by变量赋值中添加连接ID来解决这些问题.这样做使得by具有:会话的连接ID和b:执行插入的程序/作业/过程的名称.结合插入的日期和时间,我相信这三个值提供了一个非常安全的密钥来检索正确的插入行集.如果需要绝对确定性,您可以添加GUID类型的第三列(或varchar)生成要插入的GUID变量,然后使用GUID变量以及@by和@now作为键.我认为这对我的目的是不必要的,因为我要使用它的过程是一个在服务器而不是PHP上运行的事件(作业)脚本.所以除非有人要求,否则我不会举例说明.

警告

如果您在PHP中执行此操作,请考虑在流程中使用GUID列而不是CreatedBy.在PHP中执行此操作非常重要,因为在插入记录和尝试检索IDS之间可能会丢失连接,并且连接ID的CreatedBy将变得无用.但是,如果您具有在PHP中创建的GUID,则可以循环直到连接成功或使用保存在文件中某处的GUID进行恢复.为了我的目的,不需要这种级别的连接安全性,所以我不会这样做.

此解决方案的关键是CreatedBy是连接ID与正在执行插入的作业或过程的名称相结合,而CreatedDate是一个CURRENT_TIMESTAMP,它保存在通过以下代码使用的变量中.假设您有一个名为"TestTable"的表.它具有以下结构:

测试"插入"表

CREATE TABLE TestTable (
      TestTableID INT NOT NULL AUTO_INCREMENT
    , Name VARCHAR(100) NOT NULL
    , CreatedBy VARCHAR(50) NOT NULL
    , CreatedDate DATETIME NOT NULL
    , PRIMARY KEY (TestTableID)
);

Temp表用于存储插入的ID

此临时表将保存插入TestTable的行的主键ID.它只有一个字段的简单结构,它既是临时表的主键,也是插入表的主键(TestTable)

DROP TEMPORARY TABLE IF EXISTS tTestTablesInserted;
CREATE TEMPORARY TABLE tTestTablesInserted(
      TestTableID INT NOT NULL
    , PRIMARY KEY (TestTableID)
);

变量

这个很重要.您需要将CreatedBy和CreatedDate存储在变量中.CreatedBy存储用于一致性/编码实践,CreatedDate非常重要,因为您将使用它作为检索插入行的键.

@by的示例:CONID(576)BuildTestTableData

请注意,将连接ID​​封装为指示它的内容非常重要,因为它被用作"复合",并在一个字段中包含其他信息

@now的样子:'2016-03-11 09:51:10'

请注意,使用LEFT(50)封装@by非常重要,以避免在插入CreatedBy VARCHAR(50)列时跳过截断错误.我知道这发生在sql server中,对mysql不太确定.如果mysql在截断数据时没有抛出异常,则在您将截断的值插入字段然后匹配检索失败的情况下,静默错误可能会持续存在,因为您尝试将字符串的非截断版本与截断的匹配字符串的版本.如果mysql在插入时没有截断(即它没有强制类型值限制),那么这不是一个真正的问题.我是从我的SQL服务器经验中的标准练习中完成的.

SET @by = LEFT(CONCAT('CONID(', CONNECTION_ID(), ') BuildTestTableData'), 50);
SET @now = CURRENT_TIMESTAMP;

插入TestTable

插入测试表,指定@by和@now的CreatedBy和CreatedDate

INSERT INTO TestTable (
      Name
    , CreatedBy
    , CreatedDate
)
SELECT Name
     , @by
     , @now
FROM SomeDataSource
WHERE BusinessRulesMatch = 1
;

检索插入的ID

现在,使用@by和@now检索测试表中插入行的ID

INSERT INTO tTestTablesInserted (TestTableID)
SELECT TestTableID
FROM TestTable
WHERE CreatedBy = @by
  AND CreatedDate = @now
;

通过检索信息做任何事情

/*DO SOME STUFF HERE*/
SELECT * 
FROM tTestTablesInserted tti
JOIN TestTable tt
    ON tt.TestTableID = tti.TestTableID
;