mysql插入如果行不存在于没有唯一字段的表中

发布于 2024-08-16 22:12:03 字数 58 浏览 6 评论 0原文

现在已经在寻找如何实现这一目标一段时间了。

似乎所有解决方案都需要带有索引的唯一字段。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

魂牵梦绕锁你心扉 2024-08-23 22:12:03

INSERT 中没有 IF NOT EXISTS 语法，但您可以使用
ON DUPLICATE KEY 机制。假设您创建了唯一索引
名字，姓氏，您的更新可能会显示：

INSERT INTO tb (firstname, lastname) 
VALUES ('Jack', 'Doe') 
ON DUPLICATE KEY UPDATE lastname = lastname;

这使得插入中性。

there is no IF NOT EXISTS syntax in INSERT, but you could make use of the
ON DUPLICATE KEY mechanism. Assuming you create a unique index on
firstname, lastname, your update might read:

INSERT INTO tb (firstname, lastname) 
VALUES ('Jack', 'Doe') 
ON DUPLICATE KEY UPDATE lastname = lastname;

which renders the insert neutral.

回复收藏 0 原文

玩世 2024-08-23 22:12:03

您是否已经考虑过 mysql 的 INSERT OR UPDATE 语法，或者您是否希望“取消”最新的插入而不是恢复为更新？

编辑：反射后，ON DUPLICATE KEY 的确切语法（如 afftee 所示）仅由主键或至少唯一列上的重复值触发。

一个可能的解决方案是使用类似于此的 BEFORE INSERT 触发器：

CREATE TRIGGER block_dup_insert BEFORE INSERT ON my_table
FOR EACH ROW BEGIN
  -- check condition and stop if it would be duplicated according to your logic
END

如果您想停止触发器并阻止实际插入，似乎您可以使用 Nicolas Lescure 在 [mysql 上找到的这篇文章文档][1]：

“停止行动”的另一种方法是
创建一个表（stop_action）
主键（reason_to_stop）。然后
您在该表中预先填写了一些文本
('不要那样做', '或者那样做')=>
要停止操作，只需插入
该表（stop_action）与任何
预填充值（'不做
那'）。

[1]: http://dev.mysql.com/ doc/refman/5.1/en/create-trigger.html mysql 创建触发器文档

Have you already considered the INSERT OR UPDATE syntax of mysql, or do you want the newest insertion to be 'cancelled' instead of reverting to an update ?

Edit: after reflexion, the exact syntax, as shown by afftee, of ON DUPLICATE KEY is only triggered by duplicate value on primary key or at least unique columns.

A possible solution would be to use a BEFORE INSERT trigger similar to this :

CREATE TRIGGER block_dup_insert BEFORE INSERT ON my_table
FOR EACH ROW BEGIN
  -- check condition and stop if it would be duplicated according to your logic
END

If you want to stop the trigger and prevent the actual insert, it seems like you can use this post from Nicolas Lescure found on [mysql doc][1] :

Another way to "STOP ACTION" is to
create a table (stop_action) with just
a primary key(reason_to_stop). Then
you pre-fill this table with some text
('do not do that', 'or that either')=>
to stop action, just do an insert into
this table (stop_action) with any of
the pre-filled value ('do not do
that').

[1]: http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html mysql create trigger documentation

回复收藏 0 原文

∞梦里开花 2024-08-23 22:12:03

$sql2="INSERT INTO site_shipping (ship_oid,ship_status)
         SELECT '$orderid', '$status'
         FROM site_shipping 
         WHERE NOT EXISTS 
          (SELECT * 
           FROM site_shipping 
           WHERE ship_oid = '$orderid'
          )
         LIMIT 1
      ";

$sql2="INSERT INTO site_shipping (ship_oid,ship_status)
         SELECT '$orderid', '$status'
         FROM site_shipping 
         WHERE NOT EXISTS 
          (SELECT * 
           FROM site_shipping 
           WHERE ship_oid = '$orderid'
          )
         LIMIT 1
      ";

回复收藏 0 原文

~没有更多了~