如果连接的表上不存在mysql插入 [英] mysql insert if not exists on joined tables

问题描述

我的表格如下:

表格帖子"

posts.id = PK, auto-increment
posts.text = varchar
posts.tag_id 1 = int, FK to tags.id
posts.tag_id 2 = int, FK to tags.id
posts.tag_id 3 = int, FK to tags.id

表格标签"

tags.id = PK, auto-increment
tags.tag = varchar

现在我要插入以下数据:

Now I want to insert the following data:

text: 'lorem ipsum'
tag1: 'music'
tag2: 'pop'
tag3: 'singer'

因此，我需要一个查询来检查标签"中是否已存在tag1/tag2/tag3，否则将其插入，然后将其作为外键+文本"插入"posts"中的新行中.

So I need a query that checks if tag1/tag2/tag3 already exist in "tags", inserts them otherwise and then insert those as foreign-keys + the "text" into a new row in "posts".

我查看了mysql INSERT(如果不存在)，但是我只是被卡住了，不知道从哪里开始.我知道我可以通过多个查询来处理它，但是必须有另一种更简单的方法来实现相同的结果.

I had a look into mysql INSERT IF NOT EXISTS but I'm just stuck and don't know where to start. I know I could handle it with multiple queries but there has to be another, lighter way to achieve the same result.

有人对此有任何经验吗?

Has anyone any experience with this?

更新

我的一个朋友提出了这样的建议:

A friend of mine proposed something like this:

CREATE FUNCTION getTagID(tag VARCHAR('100')) RETURNS int

INSERT INTO posts (text,tag1,tag2,tag3) 
VALUES ('lorem ipsum', getTagID('music'), getTagID('pop'), getTagID('singer'));

当然仍然缺少getTagId的实现，但这有意义吗? getTagID应该选择具有给定标签的ID，如果不存在，则将其插入并返回.感谢您的帮助.

Of course the implementation of getTagId is still missing, but does that make sense? getTagID should select the id with the given tag, if it doesn't exist, insert it and return this. Any help is appreciated.

解决方案

我在MySql中创建了一个自定义函数:

I created a custom function in MySql:

DELIMITER ;;

CREATE FUNCTION getTagID(tag VARCHAR(100)) RETURNS BIGINT
BEGIN
  DECLARE tagID BIGINT;
  SET tagID = (SELECT id FROM tags WHERE text = tag);
  IF tagID IS NULL
  THEN
    INSERT INTO tags (text) VALUES (tag);
    SET tagID = (SELECT LAST_INSERT_ID());
  END IF;
  RETURN tagID;
END;;

DELIMITER ;

现在我可以像这样插入帖子:

and now I can just insert into posts like that:

INSERT INTO posts (text,tag1,tag2,tag3) 
VALUES ('lorem ipsum', getTagID('music'), getTagID('pop'), getTagID('singer'));

带有该功能的

，仅当该标签尚不存在时才插入标签"，并返回现有或新创建的标签的ID. Yipeee:)

with that function, which inserts into "tags" only if the tag does not yet exist and gives back the ID of the existing or newly created tag. Yipeee :)

推荐答案

您必须先插入帖子，然后再插入标签. SQL中没有多表插入解决方案.

You have to insert to posts, then insert to tags. There is no multi-table insert solution in SQL.

您甚至不能将帖子文本插入到来自标签触发器的帖子中，因为插入标签只能包含属于标签的列.

You can't even insert the post text to posts from a trigger on tags, because the insert to tags can only carry columns that belong to tags.

如果需要避免标签表中的重复项，则可以使用INSERT IGNORE或REPLACE或INSERT ... ON DUPLICATE KEY UPDATE.请参阅我对"INSERT IGNORE"和"INSERT"的回答. ..关于重复的密钥更新" ，以获取更多详细信息.

You can use INSERT IGNORE or REPLACE or INSERT ... ON DUPLICATE KEY UPDATE if you need to avoid duplicates in the tags table. See my answer to "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE" for more details on that.

发表评论.

仅当让自动递增机制生成新的id时，您才能获取生成的PK.如果让自动递增发生，则可以保证不会导致重复的PK.

You can get the generated PK only if you let the auto-increment mechanism generate a new id. If you let auto-increment happen, you're guaranteed not to result in a duplicate PK.

如果您指定一个ID并绕过自动递增，则无论如何都无法获得该ID.但是，如果您在INSERT中指定了该值，则无需查询它-您应该已经拥有它.

If you specify an id and bypass auto-increment, you can't get the id anyway. But you don't need to query for the value if you specified it in your INSERT -- you should have it already.

唯一的其他情况是您拥有一个不会自动递增的二级唯一密钥.

The only other case is if you have a secondary unique key that's not auto-increment.

CREATE TABLE foo (id int auto_increment primary key, name varchar(10), unique key (name));
INSERT INTO foo (name) VALUES ('bill');
SELECT LAST_INSERT_ID(); -- returns 1
INSERT INTO foo (name) VALUES ('bill');
SELECT LAST_INSERT_ID(); -- returns 1

这有点令人困惑，因为最后一个插入ID来自成功并生成一个ID的最后一个INSERT.

This is slightly confusing because the last insert id comes from the last INSERT that succeeded and generated an id.

使用INSERT INTO posts (text,tag1,tag2,tag3)重新进行更新.这就是重复组.如果您需要四个标签怎么办?如果帖子只有两个标签怎么办?如何对带有给定标签的帖子进行索引搜索?

Re your update with INSERT INTO posts (text,tag1,tag2,tag3). That's called repeating groups. What if you need four tags? What if a post has only two tags? How do you make an indexed search for posts with a given tag?

这篇关于如果连接的表上不存在mysql插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！