MySQL 替换成有 where 子句吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3003244/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does replace into have a where clause?
提问by Lajos Arpad
I'm writing an application and I'm using MySQL as DBMS, we are downloading property offers and there were some performance issues. The old architecture looked like this: A property is updated. If the number of affected rows is not 1, then the update is not considered successful, elseway the update query solves our problem. If the update was not successful, and the number of affected rows is more than 1, we have duplicates and we delete all of them. After we deleted duplicates if needed if the update was not successful, an insert happens. This architecture was working well, but there were some speed issues, because properties are deleted if they were not updated for 15 days. Theoretically the main problem is deleting properties, because some properties are alive for months and the indexes are very far from each other (we are talking about 500, 000+ properties).
我正在编写一个应用程序，我使用 MySQL 作为 DBMS，我们正在下载属性报价，但存在一些性能问题。旧架构看起来像这样：更新了一个属性。如果受影响的行数不是 1，则认为更新不成功，否则更新查询解决了我们的问题。如果更新不成功，并且受影响的行数超过 1，则我们有重复项并删除所有这些行。如果更新不成功，我们根据需要删除重复项后，会发生插入。此架构运行良好，但存在一些速度问题，因为如果属性在 15 天内未更新，则会被删除。理论上主要的问题是删除属性，
Our host told me to use replace into instead of deleting properties and all deprecated properties should be considered as DEAD. I've done this, but problems started to occur because of syntax error and I couldn't find anywhere an example of replace into with a where clause (I'd like to replace a DEAD property with the new property instead of deleting the old property and insert a new to assure optimization). My query looked like this:
我们的主持人告诉我使用替换而不是删除属性，并且所有不推荐使用的属性都应该被视为 DEAD。我已经这样做了，但是由于语法错误开始出现问题，而且我在任何地方都找不到用 where 子句替换的示例（我想用新属性替换死属性而不是删除旧属性属性并插入一个新的以确保优化）。我的查询如下所示：
replace into table_name(column1, ..., columnn) values(value1, ..., valuen) where ID = idValue
Of course, I've calculated idValue and handled everything but I had a syntax error. I would like to know if I'm wrong and there is a where clause for replace into.
当然，我已经计算了 idValue 并处理了所有内容，但出现了语法错误。我想知道我是否错了，是否有替换成的 where 子句。
I've found an alternative solution, which is even better than replace into (using simply an update query) because deletes are happening behind the curtains if I use replace into, but I would like to know if I'm wrong when I say that replace into doesn't have a where clause. For more reference, see this link:
我找到了一个替代解决方案，它甚至比替换到（使用简单的更新查询）更好，因为如果我使用替换到，删除就会发生在幕后，但我想知道我说的是否错了replace into 没有 where 子句。如需更多参考，请参阅此链接：
http://dev.mysql.com/doc/refman/5.0/en/replace.html
http://dev.mysql.com/doc/refman/5.0/en/replace.html
Thank you for your answers in advance, Lajos árpád
感谢您提前回答，Lajos árpád
回答by Ivar Bonsaksen
I can see that you have solved your problem, but to answer your original question:
我可以看到你已经解决了你的问题，但要回答你原来的问题：
REPLACE INTOdoes nothave a WHEREclause.
REPLACE INTO并不能有一个WHERE条款。
The REPLACE INTOsyntax works exactlylike INSERT INTOexcept that any old rows with the same primary or unique key is automaticly deleted before the new row is inserted.
该REPLACE INTO语法的工作原理究竟喜欢INSERT INTO，只是任何旧行具有相同主键或唯一键插入新行之前全自动删除。
This means that instead of a WHEREclause, you should add the primary key to the values beeing replaced to limit your update.
这意味着WHERE您应该将主键添加到被替换的值中以限制您的更新，而不是子句。
REPLACE INTO myTable (
myPrimaryKey,
myColumn1,
myColumn2
) VALUES (
100,
'value1',
'value2'
);
...will provide the same result as...
...将提供与...相同的结果
UPDATE myTable
SET myColumn1 = 'value1', myColumn2 = 'value2'
WHERE myPrimaryKey = 100;
...or more exactly:
...或更确切地说：
DELETE FROM myTable WHERE myPrimaryKey = 100;
INSERT INTO myTable(
myPrimaryKey,
myColumn1,
myColumn2
) VALUES (
100,
'value1',
'value2'
);
回答by msw
In your documentation link, they show three alternate forms of the replacecommand. Even though elided, the only one that can accept a where clause is the third form with the trailing select.
在您的文档链接中，它们显示了replace命令的三种替代形式。即使被省略，唯一可以接受 where 子句的是带有尾随的第三种形式select。
replaceseems like overkill relative to updateif I am understanding your task properly.
replace相对于update我是否正确理解您的任务而言，这似乎有点矫枉过正。
