mySQL>>规范以逗号分隔的字段 [英] mySQL >> Normalizing a comma-delimited field

问题描述

我目前有一个名为"RESOURCES"的表，其中的关键字字段为"RES_Tags". "RES_Tags"字段包含每个记录的以逗号分隔的关键字列表.

I currently have a table called "RESOURCES" with a keywords field called "RES_Tags". The "RES_Tags" field contains a comma-delimited list of keywords for each record.

我需要规范化此表/字段.

I need to normalize this table/field.

我已经设置了以下表格:TAGS，TAGS_TO_RESOURCES.

I have already set up the following tables: TAGS, TAGS_TO_RESOURCES.

请在此处查看架构: http://sqlfiddle.com/#!9/edac4 /1

什么是查询，使我能够解析RES_Tags中的关键字，将它们写入TAGS表而不创建重复项，然后在TAGS_TO_RESOURCES表中写入列表?

What is a query that will allow me to parse the keywords in RES_Tags, write them into the TAGS table without creating duplicates and then write a listing in the TAGS_TO_RESOURCES table?

推荐答案

1. 基于RESOURCES.RES_tags创建一组INSERT ... INTO TAGS ...语句.防止使用TAGS和ON DUPLICATE KEY ...中的UNIQUE约束或使用INSERT ... SELECT ... NOT EXISTS()进行复制:

1. based on RESOURCES.RES_tags create set of INSERT ... INTO TAGS ... statements. Prevent duplicating either with UNIQUE constraint in TAGS and ON DUPLICATE KEY ... or using INSERT ... SELECT ... NOT EXISTS():

a)动态地在RES_tags的开头添加一些字符，并在结尾添加其他字符(例如-开始，+结束)-但不要将其保存回DB(a,b,c会转换进入-a,b,c+)

a) append on the fly some character to the start of RES_tags and different character to the end(say - to start, + to end) - but don't save it back into DB(a,b,c would transform into -a,b,c+)

b)即时替换每个'，'以结束上一个INSERT语句并开始下一个语句；用仅开头的'+'代替'-'；仅带有结尾部分(例如，-替换为insert into tags(tag) values("，+变为'")，而,将是"), ("-但要使其唯一，则需要添加步骤1中提到的内容)

b) replace on the fly each ',' into ending previous INSERT statement and starting next one; replace '-' with starting only, '+; with ending part only(e.g. - is replaced with insert into tags(tag) values(", + becomes '") and , would be "), (" - but for keeping them unique it will be required to add something mentioned in step #1)

1. 执行由#1(例如insert into tags(tag) values("a"), ("b"), ("c"))

使用以下标签链接带有标签的实体:

link entity with tags using:

INSERT INTO TAGS_TO_RESOURCES(resource_id, tag_id)
SELECT RESOURCES.id, TAGS.id
FROM RESOURCES
INNER JOIN TAGS
ON INSTR(CONCAT(',', RESOURCES.RES.tags, ','), CONCAT(',', TAGS.tag, ','))> 0   

这篇关于mySQL>>规范以逗号分隔的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！