MySQL Insert 查询不适用于 WHERE 子句

本文介绍了MySQL Insert 查询不适用于 WHERE 子句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

这个查询有什么问题:

INSERT INTO Users( weight, desiredWeight ) VALUES ( 160, 145 ) WHERE id = 1;

它可以在没有 WHERE 子句的情况下工作.我似乎忘记了我的 SQL.

It works without the WHERE clause. I've seemed to have forgot my SQL.

推荐答案

MySQL INSERT 语法 不支持 WHERE 子句，因此您的查询将失败.假设您的 id 列是唯一的或主键:

MySQL INSERT Syntax does not support the WHERE clause so your query as it stands will fail. Assuming your id column is unique or primary key:

如果您尝试插入 ID 为 1 的新行，您应该使用:

If you're trying to insert a new row with ID 1 you should be using:

INSERT INTO Users(id, weight, desiredWeight) VALUES(1, 160, 145);

如果您尝试更改 ID 为 1 的现有行的 weight/desiredWeight 值，您应该使用:

If you're trying to change the weight/desiredWeight values for an existing row with ID 1 you should be using:

UPDATE Users SET weight = 160, desiredWeight = 145 WHERE id = 1;

如果你愿意，你也可以像这样使用 INSERT .. ON DUPLICATE KEY 语法:

If you want you can also use INSERT .. ON DUPLICATE KEY syntax like so:

INSERT INTO Users (id, weight, desiredWeight) VALUES(1, 160, 145) ON DUPLICATE KEY UPDATE weight=160, desiredWeight=145

或者甚至像这样:

INSERT INTO Users SET id=1, weight=160, desiredWeight=145 ON DUPLICATE KEY UPDATE weight=160, desiredWeight=145

同样重要的是要注意，如果你的 id 列是一个自动增量列，那么你最好从你的 INSERT 中省略它，让 mysql 像往常一样增加它.

It's also important to note that if your id column is an autoincrement column then you might as well omit it from your INSERT all together and let mysql increment it as normal.