在与插入相同的重复密钥更新上

我四处寻找，但没有找到，如果它是可能的。

我有这个 MySQL 查询:

INSERT INTO table (id,a,b,c,d,e,f,g) VALUES (1,2,3,4,5,6,7,8)

字段 id 有一个“惟一索引”，所以不能有两个字段。现在，如果相同的 id 已经出现在数据库中，我想要更新它。但是我真的需要再次指定所有这些字段吗，比如:

INSERT INTO table (id,a,b,c,d,e,f,g) VALUES (1,2,3,4,5,6,7,8)
ON DUPLICATE KEY UPDATE a=2,b=3,c=4,d=5,e=6,f=7,g=8

或者:

INSERT INTO table (id,a,b,c,d,e,f,g) VALUES (1,2,3,4,5,6,7,8)
ON DUPLICATE KEY UPDATE a=VALUES(a),b=VALUES(b),c=VALUES(c),d=VALUES(d),e=VALUES(e),f=VALUES(f),g=VALUES(g)

我已经详细说明了插入的内容。

一个额外的说明，我想使用周围的工作得到的 ID！

id=LAST_INSERT_ID(id)

我希望有人能告诉我最有效的方法是什么。

346851 次浏览
小开

给出 UPDATE语句是为了将旧字段更新为新值。如果您的旧值与新值相同，那么为什么在任何情况下都需要更新它呢？

例如，如果您的列 a到 g已经设置为 2到 8，则不需要重新更新它。

此外，你亦可使用:

INSERT INTO table (id,a,b,c,d,e,f,g)
VALUES (1,2,3,4,5,6,7,8)
ON DUPLICATE KEY
UPDATE a=a, b=b, c=c, d=d, e=e, f=f, g=g;

要从 LAST_INSERT_ID获取 id，需要指定正在使用的后端应用程序。

对于 LuaSQL，conn:getlastautoid()获取值。

小开

有一个 MySQL 特定的 SQL 扩展，可能是你想要的-REPLACE INTO

然而，它的工作原理与“ ON DUPLICATE UPDATE”不完全一样

  1. 它删除与新行冲突的旧行，然后插入新行。只要表上没有主键就可以，但是如果有，那么如果其他表引用该主键

  2. 无法引用旧行中的值，因此不能执行

    INSERT INTO mytable (id, a, b, c) values ( 1, 2, 3, 4)
ON DUPLICATE KEY UPDATE
id=1, a=2, b=3, c=c + 1;

I'd like to use the work around to get the ID to!

That should work — last_insert_id() should have the correct value so long as your primary key is auto-incrementing.

However as I said, if you actually use that primary key in other tables, REPLACE INTO probably won't be acceptable to you, as it deletes the old row that clashed via the unique key.

Someone else suggested before you can reduce some typing by doing:

INSERT INTO `tableName` (`a`,`b`,`c`) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE `a`=VALUES(`a`), `b`=VALUES(`b`), `c`=VALUES(`c`);
小开
最佳答案

没有其他办法，我必须指定所有内容两次。第一次是插入，第二次是更新。

小开
对于这种情况，您可以使用插入忽略，如果得到重复的记录，它将忽略 插入无视 ... ... 没有打开重复的钥匙
小开

您可能需要考虑使用 REPLACE INTO语法，但是请注意，在重复 PRIMARY/UNIQUE 键时，它 删除行，插入行是新的。

您不需要重新指定所有字段。但是，您应该考虑可能的性能降低(取决于您的表设计)。

警告:

  • 如果您有 AUTO _ INCREMENT 主键，它将得到一个新的主键
  • 索引可能需要更新
小开

这里有一个解决你问题的办法:

我试图解决像你这样的问题，我想建议从简单的方面测试。

遵循以下步骤: 从简单的解决方案中学习。

步骤1: 使用此 SQL 查询的 创建表架构:

CREATE TABLE IF NOT EXISTS `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(30) NOT NULL,
`password` varchar(32) NOT NULL,
`status` tinyint(1) DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `no_duplicate` (`username`,`password`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

A table <code>user</code> with data

步骤2: 创建两列的索引，使用以下 SQL 查询防止重复数据:

ALTER TABLE `user` ADD INDEX no_duplicate (`username`, `password`);
< p > 或，从 GUI 创建两列的索引如下: Create index from GUI Select columns to create index Indexes of table <code>user</code>

步骤3: 使用以下查询的 如果存在则更新，如果不存在则插入:

INSERT INTO `user`(`username`, `password`) VALUES ('ersks','Nepal') ON DUPLICATE KEY UPDATE `username`='master',`password`='Nepal';


INSERT INTO `user`(`username`, `password`) VALUES ('master','Nepal') ON DUPLICATE KEY UPDATE `username`='ersks',`password`='Nepal';

Table <code>user</code> after running above query

小开

如果你能够利用一个脚本语言来准备你的 SQL 查询，你可以通过使用 SET而不是 (a,b,c) VALUES(a,b,c)来重用字段 = 值对。

PHP 的一个例子:

$pairs = "a=$a,b=$b,c=$c";
$query = "INSERT INTO $table SET $pairs ON DUPLICATE KEY UPDATE $pairs";

示例表:

CREATE TABLE IF NOT EXISTS `tester` (
`a` int(11) NOT NULL,
`b` varchar(50) NOT NULL,
`c` text NOT NULL,
UNIQUE KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
小开

我知道现在很晚了，但我希望有人能得到这个答案

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

你可以在这里阅读以下教程:

https://mariadb.com/kb/en/library/insert-on-duplicate-key-update/

http://www.mysqltutorial.org/mysql-insert-or-update-on-duplicate-key-update/

小开
使用 MySQL v8.0.19及以上版本，你可以这样做: Mysql 文档 

INSERT INTO mytable(fielda, fieldb, fieldc)
VALUES("2022-01-01", 97, "hello")
AS NEW(newfielda, newfieldb, newfieldc)
ON DUPLICATE KEY UPDATE
fielda=newfielda,
fieldb=newfieldb,
fieldc=newfieldc;

也如果你想要一个 在关于重复键更新部分有一个条件在 MySQL 中有一个扭曲。如果您更新 fieldb 作为第一个参数，并将其包含在 fieldb 的 IF 子句中，那么它将已经被更新为新值！移动到结束或类似的。假设 fielda 是一个类似于示例中的日期，并且只有当该日期比前一个日期更新时才需要更新:

INSERT INTO mytable(fielda, fieldb)
VALUES("2022-01-01", 97)
AS NEW(newfielda, newfieldb, newfieldc)
ON DUPLICATE KEY UPDATE
fielda=IF(fielda<STR_TO_DATE(newfielda,'%Y-%m-%d %H:%i:%s'),newfielda,fielda),
fieldb=IF(fielda<STR_TO_DATE(newfielda,'%Y-%m-%d %H:%i:%s'),newfieldb,fieldb);

在这种情况下，由于 < ! 你需要移动 更新它下面的字段或检查 < = 或 = ... ！

INSERT INTO mytable(fielda, fieldb)
VALUES("2022-01-01", 97)
AS NEW(newfielda, newfieldb, newfieldc)
ON DUPLICATE KEY UPDATE
fielda=IF(fielda<STR_TO_DATE(newfielda,'%Y-%m-%d %H:%i:%s'),newfielda,fielda),
fieldb=IF(fielda=STR_TO_DATE(newfielda,'%Y-%m-%d %H:%i:%s'),newfieldb,fieldb);

在到达 fieldb 的 if 子句之前，Using = 因为 fielda 已经更新了的新值和预期的一样工作... 我个人最喜欢 < = 在这种情况下，如果你重新排列语句..。


提交答案