Python的mysql检查重复插入之前 [英] Python mysql check for duplicate before insert

问题描述

这里是表

CREATE TABLE IF NOT EXISTS kompas_url
(
    id  BIGINT(20) NOT NULL AUTO_INCREMENT,
    url VARCHAR(1000),
    created_date datetime,
    modified_date datetime,
    PRIMARY KEY(id)
)

我只想在不存在url的情况下尝试对kompas_url表执行INSERT

I am trying to do INSERT to kompas_url table only if url is not exist yet

任何想法？

谢谢

推荐答案

通过 SELECT ing by url ，或者您可以使 url 字段唯一：

You can either find out whether it's in there first, by SELECTing by url, or you can make the url field unique:

CREATE TABLE IF NOT EXISTS kompas_url
    ...
    url VARCHAR(1000) UNIQUE,
    ...
)

这将阻止MySQL插入重复行，但是当您尝试插入时也会报告错误。这不是很好 - 虽然我们可以处理错误，但可能会伪装他人。为了解决这个问题，我们使用 ON DUPLICATE KEY UPDATE 语法：

This will stop MySQL from inserting a duplicate row, but it will also report an error when you try and insert. This isn't good—although we can handle the error, it might disguise others. To get around this, we use the ON DUPLICATE KEY UPDATE syntax:

INSERT INTO kompas_url (url, created_date, modified_date)
VALUES ('http://example.com', NOW(), NOW())
ON DUPLICATE KEY UPDATE modified_date = NOW()

这样，我们可以在...中提供一个 UPDATE 在唯一字段中重复值的情况（这可以包括您的主键）。在这种情况下，我们可能想要更新当前日期的 modified_date 字段。

This allows us to provide an UPDATE statement in the case of a duplicate value in a unique field (this can include your primary key). In this case, we probably want to update the modified_date field with the current date.

编辑： 如〜unutbu 所建议的，如果您不想更改重复的任何内容，可以使用 INSERT IGNORE 语法。这个工作方式如下：

As suggested by ~unutbu, if you don't want to change anything on a duplicate, you can use the INSERT IGNORE syntax. This simply works as follows:

INSERT IGNORE INTO kompas_url (url, created_date, modified_date)
VALUES ('http://example.com', NOW(), NOW())

这只是转一些错误进入警告 - 最有用的地方，错误指出将有一个重复的唯一条目。如果将关键字 IGNORE 放在您的语句中，则不会收到错误 - 查询将被简单地删除。在复杂的查询中，这也可能会隐藏可能有用的其他错误，所以最好是确保你的代码是正确的，如果你想使用它。

This simply turns certain kinds of errors into warnings—most usefully, the error that states there will be a duplicate unique entry. If you place the keyword IGNORE into your statement, you won't get an error—the query will simply be dropped. In complex queries, this may also hide other errors that might be useful though, so it's best to make doubly sure your code is correct if you want to use it.

这篇关于Python的mysql检查重复插入之前的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！