自动替换行中的MySQL REPLACE [英] MySQL REPLACE in an auto incremented row

问题描述

假设我有一个MySQL表，该表包含三列:id，a和b，而名为id的列是一个AUTO INCREMENT字段.如果我将类似以下的查询传递给MySQL，它将正常工作:

Let say I have a MySQL table which contains three columns: id, a and b and the column named id is an AUTO INCREMENT field. If I pass a query like the following to MySQL, it will works fine:

REPLACE INTO `table` (`id`, `a`, `b`) VALUES (1, 'A', 'B')

但是，如果我跳过字段id，它将不再起作用，这是预期的.

But if I skip the field id it will no longer works, which is expected.

我想知道是否有一种方法可以在REPLACE查询中使用ignore某些字段.因此，上面的查询可能是这样的:

I want to know if there is a way to ignore some fields in the REPLACE query. So the above query could be something like this:

REPLACE INTO `table` (`a`, `b`) VALUES ('A', 'B')

我为什么需要这样的东西?

有时，我需要使用SELECT查询来检查数据库，以查看是否存在行.如果存在，那么我需要UPDATE现有行，否则我需要INSERT新行.我想知道是否可以通过单个REPLACE查询获得相似的结果(但不相同).

Sometimes I need to check a database with a SELECT query to see if a row exists or not. If it is exists then I need to UPDATE the existing row, otherwise I need to INSERT a new row. I'm wondering if I could achieve a similar result (but not same) with a single REPLACE query.

为什么不能是相同的结果?仅仅因为REPLACE将DELETE现有行并INSERT新行，这将丢失当前的primary key并增加自动递增的值.相反，在UPDATE查询中，primary key和AI字段将保持不变.

Why it couldn't be the same result? Simply because REPLACE will DELETE the existing row and will INSERT a new row, which will lose the current primary key and will increase the auto incremented values. In contrast, in an UPDATE query, primary key and the AI fields will be untouched.

MySQL 替换.

推荐答案

• 那不是您应该使用replace的方式.
• 仅当您知道primary key值时才使用替换.
• That's not how you're supposed to use replace.
• use replace only when you know primary key values.

手册:

请注意，除非表具有PRIMARY KEY或UNIQUE索引，否则使用 REPLACE语句没有任何意义.它等效于INSERT， 因为没有索引可用于确定是否有新行 复制另一个.

Note that unless the table has a PRIMARY KEY or UNIQUE index, using a REPLACE statement makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.

这篇关于自动替换行中的MySQL REPLACE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！