英文:
MySQL Upsert with Insert Only Column Value
问题
在MySQL中，是否可以进行upsert，但仅在插入时设置列值（不在更新时设置列值）。
例如，对于createdBy列，我们只想在插入时设置该值，不希望在更新时覆盖该值（因为我们会失去最初插入该列的人）。
请注意，我们只知道当前登录的用户。因此，updatedBy很简单 - 始终使用已登录用户的值。但createdBy很难。在插入时使用已登录用户的值，但不要在更新时覆盖这个值。
示例架构：
CREATE TABLE `movie` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`name` NVARCHAR(100) NOT NULL,
`createdBy` NVARCHAR(100) NOT NULL,
`updatedBy` NVARCHAR(100) NOT NULL,
UNIQUE INDEX (`name`)
);
标准upsert示例：
INSERT INTO `movie` (`name`, `createdBy`, `updatedBy`)
VALUES ('The Matrix', 'Jill', 'Jill')
ON DUPLICATE KEY UPDATE
`id` = LAST_INSERT_ID(`id`),
`name` = VALUES(`name`),
`createdBy` = VALUES(`createdBy`),
`updatedBy` = VALUES(`updatedBy`)
;
这是我尝试使用IFNULL仅在插入时设置createdBy列的示例。但这不起作用，导致createdBy始终为null。
INSERT INTO `movie` (`name`, `createdBy`, `updatedBy`)
VALUES ('The Matrix', IFNULL(`createdBy`, 'Jill'), 'Jill')
ON DUPLICATE KEY UPDATE
`id` = LAST_INSERT_ID(`id`),
`name` = VALUES(`name`),
`createdBy` = VALUES(`createdBy`),
`updatedBy` = VALUES(`updatedBy`)
;
期望的结果：
情况1：Jill运行一个插入行的upsert。
id = 1
name = 'The Matrix'
createdBy = 'Jill' // 由Jill创建
updatedBy = 'Jill' // 最后由Jill更新
情况2：Bob运行一个更新相同行的upsert。
id = 1
name = 'The Matrix Reloaded'
createdBy = 'Jill' // 由Jill创建（不要在更新时更改值）
updatedBy = 'Bob' // 最后由Bob更新
英文:
In MySQL, is it possible to do an upsert but only set a column value on insert (and not set the column value on update).
For example, for a createdBy column, we only want to set the value on insert, we don't want to override that value on update (because we lose who originally inserted the column).
Note that we only know the currently logged in user. So updatedBy is simple -- always use the value of the logged in user. But createdBy is hard. Use the value of the logged in user but only for an insert -- don't override this on update.
Example schema:
CREATE TABLE `movie` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`name` NVARCHAR(100) NOT NULL,
`createdBy` NVARCHAR(100) NOT NULL,
`updatedBy` NVARCHAR(100) NOT NULL,
UNIQUE INDEX (`name`)
);
Example of a standard upsert:
INSERT INTO `movie` (`name`, `createdBy`, `updatedBy`)
VALUES ('The Matrix', 'Jill', 'Jill')
ON DUPLICATE KEY UPDATE
`id` = LAST_INSERT_ID(`id`),
`name` = VALUES(`name`),
`createdBy` = VALUES(`createdBy`),
`updatedBy` = VALUES(`updatedBy`)
;
Here's my attempt to only set the createdBy column on insert using IFNULL. But this doesn't work and results in createdBy always being null.
INSERT INTO `movie` (`name`, `createdBy`, `updatedBy`)
VALUES ('The Matrix', IFNULL(`createdBy`, 'Jill'), 'Jill')
ON DUPLICATE KEY UPDATE
`id` = LAST_INSERT_ID(`id`),
`name` = VALUES(`name`),
`createdBy` = VALUES(`createdBy`),
`updatedBy` = VALUES(`updatedBy`)
;
Results wanted:
Case 1: Jill runs an upsert that inserts a row.
id = 1
name = 'The Matrix'
createdBy = 'Jill' // Created by Jill
updatedBy = 'Jill' // Last updated by Jill
Case 2: Bob runs an upsert that updates the same row.
id = 1
name = 'The Matrix Reloaded'
createdBy = 'Jill' // Created by Jill (do not change value on update)
updatedBy = 'Bob' // Last updated by Bob
答案1
得分: 1
尝试这个：
INSERT INTO movie (name, createdBy)
VALUES ('The Matrix', 'Jill')
ON DUPLICATE KEY UPDATE name = VALUES(name)
;
<details>
<summary>英文:</summary>
Try this:
INSERT INTO movie (name, createdBy)
VALUES ('The Matrix', 'Jill')
ON DUPLICATE KEY UPDATE name = VALUES(name)
;
</details>
# 答案2
**得分**: 1
我创建了一个猜测“Name”是关键字的小游戏，随时可以在[这里][1]试一试。
这是基本语法：
```sql
INSERT INTO `movie` (`name`, `UpdatedBy`,`CreatedBy`)
VALUES ('Star wars', 'NameA','NameB')
ON DUPLICATE KEY UPDATE `UpdatedBy` = VALUES(`UpdatedBy`)
;
注意：NameA和NameB可以相同，这样在插入时就不会得到空值。
希望对你有帮助 
1: http://sqlfiddle.com/#!9/063e24/1
英文:
I created a fiddle guessing that Name is the Key, feel free to give it a try here.
This is the basic syntax:
INSERT INTO `movie` (`name`, `UpdatedBy`,`CreatedBy`)
VALUES ('Star wars', 'NameA','NameB')
ON DUPLICATE KEY UPDATE `UpdatedBy` = VALUES(`UpdatedBy`)
;
Notice: NameA and NameB can be the same so you dont get nulls on inserts
Hope it helps
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论