8 回答

• 23

  这是一个迟到的答案 . 从2018年6月4日发布的SQLIte 3.24.0开始，最终在PostgreSQL语法之后支持UPSERT子句 .

  INSERT INTO players (user_name, age)
    VALUES('steven', 32) 
    ON CONFLICT(user_name) 
    DO UPDATE SET age=excluded.age;
  

  回复于 2024-04-29T08:45:49+08:00
• 22

  问答风格

  好吧，经过数小时研究和解决问题，我发现有两种方法可以实现这一点，具体取决于您的表的结构以及是否激活了外键限制以保持完整性 . 我想以干净的格式分享这个，以节省一些可能在我的情况下的人 .

  选项1：您可以负担得起删除该行

  换句话说，您没有外键，或者如果您有外键，则配置SQLite引擎，以便不存在完整性异常 . 要走的路是INSERT OR REPLACE . 如果您尝试插入/更新其ID已存在的播放器，则SQLite引擎将删除该行并插入您提供的数据 . 现在的问题是：如何保持旧ID相关联？

  假设我们想要使用数据user_name = 'steven'和age = 32进行UPSERT .

  看看这段代码：

  INSERT INTO players (id, name, age)
  
  VALUES (
      coalesce((select id from players where user_name='steven'),
               (select max(id) from drawings) + 1),
      32)
  

  诀窍是合并 . 它返回用户'steven'的id（如果有的话），否则返回一个新的id .

  选项2：您无法承担删除该行的费用

  在使用之前的解决方案进行调整之后，我意识到在我的情况下最终可能会破坏数据，因为此ID可用作其他表的外键 . 此外，我使用ON DELETE CASCADE子句创建了表，这意味着它将以静默方式删除数据 . 危险的 .

  所以，我首先想到了一个IF子句，但SQLite只有CASE . 如果EXISTS（从user_name = 'steven'的玩家中选择id），则不能使用此CASE（或者至少我没有管理它）执行一次UPDATE查询，如果没有则INSERT . 不行 .

  然后，最后我使用了蛮力，并取得了成功 . 逻辑是，对于您要执行的每个UPSERT，首先执行INSERT或IGNORE以确保我们的用户有一行，然后执行UPDATE查询，其中包含您尝试插入的完全相同的数据 .

  与以前相同的数据：user_name ='steven'，年龄= 32 .

  -- make sure it exists
  INSERT OR IGNORE INTO players (user_name, age) VALUES ('steven', 32); 
  
  -- make sure it has the right data
  UPDATE players SET user_name='steven', age=32 WHERE user_name='steven';
  

  就这样！

  编辑

  正如Andy所评论的那样，尝试先插入然后更新可能会导致触发触发的频率高于预期 . 这不是我认为的数据安全问题，但是发起不必要的事件确实没有意义 . 因此，改进的解决方案是：

  -- Try to update any existing row
  UPDATE players SET user_name='steven', age=32 WHERE user_name='steven';
  
  -- Make sure it exists
  INSERT OR IGNORE INTO players (user_name, age) VALUES ('steven', 32);
  

  回复于 2024-04-29T08:45:49+08:00
• 97

  这里's an approach that doesn' t需要蛮力'ignore'，这只有在发生密钥违规时才有效 . 这种方式基于您在更新中指定的任何条件 .

  试试这个...

  -- Try to update any existing row
  UPDATE players
  SET age=32
  WHERE user_name='steven';
  
  -- If no update happened (i.e. the row didn't exist) then insert one
  INSERT INTO players (user_name, age)
  SELECT 'steven', 32
  WHERE (Select Changes() = 0);
  

  工作原理

  这里的'magic'是使用 Where (Select Changes() = 0) 子句来确定插入是否有任何行，并且由于这是基于您自己的 Where 子句，因此它可以用于您定义的任何行，而不仅仅是键冲突 .

  在上面的示例中，如果更新没有更改（即记录不存在），则 Changes() = 0，因此 Insert 语句中的 Where 子句返回true，并插入带有指定数据的新行 .

  如果 Update 确实更新了现有行，那么 Changes() = 1，那么 Insert 中的'Where'子句现在将为false，因此不会进行插入 .

  不需要蛮力 .

  回复于 2024-04-29T08:45:49+08:00
• 52

  所有提出的答案的问题完全没有考虑到触发器（可能还有其他副作用） . 解决方案如

  INSERT OR IGNORE ...
  UPDATE ...
  

  当行不存在时，导致执行两个触发器（用于插入，然后用于更新） .

  适当的解决方案是

  UPDATE OR IGNORE ...
  INSERT OR IGNORE ...
  

  在这种情况下，只执行一个语句（当行存在或不存在时） .

  回复于 2024-04-29T08:45:49+08:00
• 1

  要有一个没有孔的纯粹UPSERT（对于程序员），它不会在唯一键和其他键上进行中继：

  UPDATE players SET user_name="gil", age=32 WHERE user_name='george'; 
  SELECT changes();
  

  SELECT changes（）将返回上次查询中完成的更新次数 . 然后检查changes（）的返回值是否为0，如果是，则执行：

  INSERT INTO players (user_name, age) VALUES ('gil', 32);
  

  回复于 2024-04-29T08:45:49+08:00
• 4

  选项1：插入 - >更新

  如果你想避免两者 changes()=0 和 INSERT OR IGNORE 即使你负担不起删除行 - 你可以使用这个逻辑;

  首先， insert （如果不存在），然后通过使用唯一键过滤 update .

  示例

  -- Table structure
  CREATE TABLE players (
      id        INTEGER       PRIMARY KEY AUTOINCREMENT,
      user_name VARCHAR (255) NOT NULL
                              UNIQUE,
      age       INTEGER       NOT NULL
  );
  
  -- Insert if NOT exists
  INSERT INTO players (user_name, age)
  SELECT 'johnny', 20
  WHERE NOT EXISTS (SELECT 1 FROM players WHERE user_name='johnny' AND age=20);
  
  -- Update (will affect row, only if found)
  -- no point to update user_name to 'johnny' since it's unique, and we filter by it as well
  UPDATE players 
  SET age=20 
  WHERE user_name='johnny';
  

  关于触发器

  注意：我没有测试它以查看正在调用哪些触发器，但我 assume 以下内容：

  如果行不存在

  • 插入之前

  • 使用INSTEAD OF INSERT

  • 插入后

  • 在更新之前

  • 使用INSTEAD OF更新

  • 更新后

  如果确实存在行

  • 在更新之前

  • 使用INSTEAD OF更新

  • 更新后

  选项2：插入或替换 - 保留您自己的ID

  通过这种方式，您可以拥有一个SQL命令

  -- Table structure
  CREATE TABLE players (
      id        INTEGER       PRIMARY KEY AUTOINCREMENT,
      user_name VARCHAR (255) NOT NULL
                              UNIQUE,
      age       INTEGER       NOT NULL
  );
  
  -- Single command to insert or update
  INSERT OR REPLACE INTO players 
  (id, user_name, age) 
  VALUES ((SELECT id from players WHERE user_name='johnny' AND age=20),
          'johnny',
          20);
  

  编辑：添加选项2 .

  回复于 2024-04-29T08:45:49+08:00
• 1

  您也可以只在您的user_name唯一约束中添加ON CONFLICT REPLACE子句，然后只需INSERT，将其留给SQLite以确定在发生冲突时该怎么做 . 见：https://sqlite.org/lang_conflict.html .

  还要注意有关删除触发器的句子：当REPLACE冲突解决策略删除行以满足约束时，当且仅当启用了递归触发器时，删除触发器才会触发 .

  回复于 2024-04-29T08:45:49+08:00
• -3

  Accepted answer is not correct
  因为它的2个查询
  它的复杂只有!!

  this is simple 2 query :

  $check=query('select id from players where user_name="steven";');
  
  if(empty($check))
  {
     query('insert into players (user_name,age) values ("steven",32);');
  }
  else
  {
     query('update players set age=13 where id='.$check['id'].';');
  }
  

  例如，

  • query是一个函数

  回复于 2024-04-29T08:45:49+08:00