如何使用slick/plainSQL检索自动递增的ID? [英] How to retrieve the auto incremented ID using slick / plainSQL?

问题描述

是否没有一个灵巧/plainSQL本机解决方案来检索当前INSERT的自动递增的ID?

Isn't there a slick/plainSQL native solution to retrieve the auto incremented id of the current INSERT?

userId是我的mySQL表中的一个自动增量字段.

userId is an auto incremental field in my mySQL table.

sql"""
   INSERT INTO `table`(`email`) 
   OUTPUT INSERTED.userId 
   VALUES ("[e-mail]")
""".as[Int].firstOption

我们将不胜感激.

欢呼 奥利弗(Oliver)

Cheers Oliver

推荐答案

感谢您 cvogt 在此讨论中提供了帮助. 我认为提交PR会有所帮助，因为它是非常常见且有用的功能，在

Thank you cvogt for helping out in this discussion. I think it would be helpful to submit a PR, inasmuch as it is a very common and useful functionality which should not be missing in slick's plainSQL queries.

最后，我找到了一种解决方法来替换缺少的本机函数，如下所示.

Finally, i found a work-around to replace the missing native function as following.

在同一会话中，我解决了两个查询.第一个是INSERT语句，第二个语句是SELECT LAST_INSERT_ID()，它返回由最近执行的INSERT(1)为AUTO_INCREMENT列设置的最新自动生成的值.此处有更多详细信息: MySQL参考-LAST_INSERT_ID()

Within the same session I settle two queries. The first one is the INSERT statement, the second statement is SELECT LAST_INSERT_ID() which returns the newest automatically generated value that was set for the AUTO_INCREMENT column by the recently executed INSERT(1). More details here: MySQL Reference - LAST_INSERT_ID()

Database.forDataSource(dataSource).withDynSession {
  sqlu"""INSERT INTO `users`(`email`) VALUES ("[e-mail]")
  """.firstOption match {
    case Some(num) if num == 1 => sql"SELECT LAST_INSERT_ID()".as[Long].firstOption()
    case None => None
  }
}

这现在对我有效.如果有任何改进，请随时发布您的解决方案.

This works for me right now. If there are any improvements, do not hesitate to post your solution.

这篇关于如何使用slick/plainSQL检索自动递增的ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！