mysql-插入后忽略获取主键 [英] mysql - after insert ignore get primary key

问题描述

我正在使用Python在mysql insert ignore into........中运行查询

i am running a query in mysql insert ignore into........ using Python

运行查询后，我想知道该行的主键.我知道有查询

after running the query I want to know the primary key of the row. I know there is the query

SELECT LAST_INSERT_ID();

但是我不确定它是否可以与插入忽略一起使用

but i'm not sure if it will work with insert ignore

什么是最好的方法?

推荐答案

如果使用INSERT IGNORE并且忽略该行，则AUTO_INCREMENT计数器不会增加，并且LAST_INSERT_ID()返回0，这表明没有插入行.

If you use INSERT IGNORE and the row is ignored, the AUTO_INCREMENT counter is not incremented and LAST_INSERT_ID() returns 0, which reflects that no row was inserted.

知道这一点后，您可以将其分为多个步骤:

Knowing this, you can make this a multi-step process:

• INSERT IGNORE
• 如果LAST_INSERT_ID()，则完成(已插入新行)
• 否则从您的表WHERE中选择your_primary键(您插入的数据的UNIQUE约束)

具有美国各州的示例:

id  | abbrev | other_data
 1  | AL     | ...
 2  | AK     |

UNIQUE KEY abbr (abbrev)

现在，插入新行:

INSERT IGNORE INTO `states` (`abbrev`,`other_data`) VALUES ('AZ','foo bar');
> OK
SELECT LAST_INSERT_ID();
> "3"
// we have the ID, we're done

插入将被忽略的行:

INSERT IGNORE INTO `states` (`abbrev`,`other_data`) VALUES ('AK','duplicate!');
> OK
SELECT LAST_INSERT_ID();
> "0"
// oops, it already exists!
SELECT id FROM `states` WHERE `abbrev` = 'AK'; // our UNIQUE constraint here
> "2"
// there we go!

或者，有可能一种解决方法，可以一步完成此操作-使用REPLACE INTO而不是INSERT IGNORE INTO-

Alternately, there is a possible workaround to do this in one step - use REPLACE INTO instead of INSERT IGNORE INTO - the syntax is very similar. Note however that there are side effects with this approach - these may or may not be important to you:

• REPLACE删除并重新创建行
  • 所以DELETE触发器是被触发的
  • 此外，即使该行存在，主ID也将递增
  • INSERT IGNORE保留旧的行数据，REPLACE替换为新的行数据
  • REPLACE deletes+recreates the row
    • so DELETE triggers are, um, triggered
    • also, the primary ID will be incremented even if the row exists
    • INSERT IGNORE keeps the old row data, REPLACE replaces it with new row data

    这篇关于mysql-插入后忽略获取主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！