phpmysqlsqlmariadbmariadb-10.5

INSERTing into a tag_map in MySQL


I have a simple article and tag_map tables as

CREATE TABLE Articles
(
  ArticleID int(11) unsigned NOT NULL AUTO_INCREMENT,
  Title varchar(255),
  PRIMARY KEY(ArticleID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

CREATE TABLE Tags
(
  TagID int(11) unsigned NOT NULL AUTO_INCREMENT,
  Tag varchar(255),
  UNIQUE INDEX(Tag),
  PRIMARY KEY(TagID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

CREATE TABLE TagMap
(
  ArticleID int(11) unsigned NOT NULL,
  TagID int(11) unsigned NOT NULL,
  INDEX(TagID),
  PRIMARY KEY(ArticleID,TagID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

I add tags via PHP

$result = $mysqli->query("SELECT TagID FROM Tags WHERE Tag='$tag'");

if($result->num_rows == 1) {
    $row = $result->fetch_assoc();
    $tag_id = $row['TagID'];
}
else {
    $mysqli->query("INSERT INTO Tags (Tag) VALUES ('$tag')");
    $tag_id = $mysqli->insert_id;
}

$mysqli->query("INSERT INTO TagMap (ArticleID,TagID) VALUES ($article_id,$tag_id)");

I wonder if there is a faster way to do this in one query within MySQL.

Here, I need 2 or 3 queries for adding each tag.

Additionally, I hope to find a way for batch INSERT (possibly via LOAD DATA LOCAL INFILE) when we have a list of tags as

ArticleID,Tag
1,tag2
2,tag11
4,tag3

Solution

  • A pattern:

    CREATE PROCEDURE load_to_TagMap ()
    BEGIN
    -- create table for loading data
    CREATE TABLE tmp_TagMap ( ArticleID INT, Tag VARCHAR(255) ) ENGINE = Memory;
    -- load data from file
    LOAD DATA INFILE '/directory/filename.ext'
        INTO TABLE tmp_TagMap
        SKIP 1 LINES;
    -- add absent tags into Tags table
    INSERT INTO Tags (Tag)
        SELECT tmp_TagMap.Tag
        FROM tmp_TagMap 
        LEFT JOIN Tags USING (Tag)
        WHERE Tags.Tag IS NULL;
    -- insert loaded data into TagMap table with lookup
    INSERT INTO TagMap
        SELECT ArticleID, TagID
        FROM Tag
        JOIN tmp_TagMap USING (Tag);
    -- remove loaded data table
    DROP TABLE tmp_TagMap;
    END
    

    From PHP simply execute CALL load_to_TagMap;.