SQL批量重新标记项目

如果我正确理解你,你可以尝试这样的方法:

/* new tag/item table clustered PK optimised for group by tag_id 
  or tag_id = ? queries !! */

drop table if exists tag_item;
create table tag_item
(
tag_id smallint unsigned not null,
item_id int unsigned not null,
primary key (tag_id, item_id), -- clustered PK innodb only
key (item_id)
)
engine=innodb;

-- populate new table with distinct tag/items

insert ignore into tag_item 
 select tag_id, item_id from item_tag order by tag_id, item_id;

-- update counters

update tag inner join
(
select 
 tag_id,
 count(*) as counter
from
 tag_item
group by
 tag_id
) c on tag.tag_id = c.tag_id
set
 tag.counter = c.counter;

上的索引/约束 item_tag 表可以防止重复的标记;或者使用组合主键创建表 item_id 和 tag_id .

至于计数,放下 count 列从 tag 表并创建一个视图以获取结果:

CREATE VIEW tag_counts AS SELECT tag_id, name, COUNT(*) AS count GROUP BY tag_id, name

那么你的计数总是最新的。

这就是目前为止我所得到的,它似乎有效,但我还没有足够的数据来了解它的性能。欢迎评论。

一些注释:

• 必须向item_tags表中添加一个唯一的id字段,才能使重复的标记清理工作正常进行。
• 增加了对标签别名的支持,这样就有了重新标记标签的记录。
• 我之前没有提到过,但是每个项目都有一个已发布的标志,只有已发布的项目才会影响标签上的Count字段。
• 代码使用C,亚音速+Linq+“编码恐怖”,但相当不言自明。

代码:

public static void Retag(string new_tag, List<string> old_tags)
{
    // Check new tag name is valid
    if (!Utils.IsValidTag(new_tag))
    {
        throw new RuleException("NewTag", string.Format("Invalid tag name - {0}", new_tag));
    }

    // Start a transaction
    using (var scope = new SimpleTransactionScope(megDB.GetInstance().Provider))
    {
        // Get the new tag
        var newTag = tag.SingleOrDefault(x => x.name == new_tag);

        // If the new tag is an alias, remap to the alias instead
        if (newTag != null && newTag.alias != null)
        {
            newTag = tag.SingleOrDefault(x => x.tag_id == newTag.alias.Value);
        }

        // Get the old tags
        var oldTags = new List<tag>();
        foreach (var old_tag in old_tags)
        {
            // Ignore same tag
            if (string.Compare(old_tag, new_tag, true)==0)
                continue;
            var oldTag = tag.SingleOrDefault(x => x.name == old_tag);
            if (oldTag != null)
                oldTags.Add(oldTag);
        }

        // Redundant?
        if (oldTags.Count == 0)
            return;

        // Simple rename?
        if (oldTags.Count == 1 && newTag == null)
        {
            oldTags[0].name = new_tag;
            oldTags[0].Save();
            scope.Complete();
            return;
        }

        // Create new tag?
        if (newTag == null)
        {
            newTag = new tag();
            newTag.name = new_tag;
            newTag.Save();
        }

        // Build a comma separated list of old tag id's for use in sql 'IN' clause
        var sql_old_tags = string.Join(",", (from t in oldTags select t.tag_id.ToString()).ToArray());

        // Step 1 - Retag, allowing duplicates for now
        var sql = @"
            UPDATE item_tags
                SET tag_id=@newtagid
                WHERE tag_id IN (" + sql_old_tags + @");
            ";

        // Step 2 - Delete the duplicates
        sql += @"
            DELETE t1 
                FROM item_tags t1, item_tags t2
                WHERE t1.tag_id=t2.tag_id 
                    AND t1.item_id=t2.item_id
                    AND t1.item_tag_id > t2.item_tag_id;
            ";

        // Step 3 - Update the use count of the destination tag
        sql += @"
            UPDATE tags 
                SET tags.count=
                    (
                        SELECT COUNT(items.item_id)
                        FROM items
                        INNER JOIN item_tags ON item_tags.item_id = items.item_id
                        WHERE items.published=1 AND item_tags.tag_id=@newtagid
                    )
                WHERE
                    tag_id=@newtagid;
            ";

        // Step 4 - Zero the use counts of the old tags and alias the old tag to the new tag
        sql += @"
            UPDATE tags
                SET tags.count=0,
                    alias=@newtagid
                WHERE tag_id IN (" + sql_old_tags + @");
            ";

        // Do it!
        megDB.CodingHorror(sql, newTag.tag_id, newTag.tag_id, newTag.tag_id, newTag.tag_id).Execute();
        scope.Complete();
    }