MySQL插入位置查询 [英] MySQL Insert Where query

问题描述

此查询出了什么问题:

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.

这篇关于MySQL插入位置查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！