开发者

mysql group_concat one table to another

i would like to have a query that will solve my problem in native sql.

i have a table named "synonym" which holds words and the words' synonyms.

id, word, synonym
1, abandon, forsaken
2, abandon, desolate
...

As you can see words are repeated in this table lots of times and this makes the table unnecessarily big. i would like to have a table named "words" which doesn't have duplicate words like:

开发者_高级运维
id, word, synonyms
1, abandon, 234|90
...

note: "234" and "90" here are the id's of forsaken and desolate in newly created words table.

so i already created a new "words" table with unique words from word field at synonym table. what i need is an sql query that will look at the synonym table for each word's synonyms then find their id's from words table and update the "synonyms" field with vertical line seperated ids. then i will just drop the synonym table.

just like:

UPDATE words SET synonyms= ( vertical line seperated id's (id's from words table) of the words at the synonyms at synonym table )

i know i must use group_concat but i couldn't achieved this.

hope this is clear enough. thanks for the help!


Your proposed schema is plain horrible.

Why not use a many-to-many relationship ?

Table words

id     word
1      abandon
234    forsaken

Table synonyms

wid    sid
1      234
1      90


You can avoid using update and do it using the queries below:

TRUNCATE TABLE words;

INSERT INTO words
SELECT (@rowNum := @rowNum+1), 
                a.word, 
                SUBSTRING(REPLACE(a.syns, a.id + '|', ''), 2) syns
FROM (
            SELECT a.*,group_concat(id SEPARATOR '|') syns
            FROM synonyms a
            GROUP BY word
         ) a,  
         (SELECT @rowNum := 0) b

Test Script:

CREATE TABLE `ts_synonyms` (
    `id` INT(11) NULL DEFAULT NULL,
    `word` VARCHAR(20) NULL DEFAULT NULL,
    `synonym` VARCHAR(2000) NULL DEFAULT NULL
);

CREATE TABLE `ts_words` (
    `id` INT(11) NULL DEFAULT NULL,
    `word` VARCHAR(20) NULL DEFAULT NULL,
    `synonym` VARCHAR(2000) NULL DEFAULT NULL
);

INSERT INTO ts_synonyms
VALUES ('1','abandon','forsaken'),
('2','abandon','desolate'),
('3','test','tester'),
('4','test','tester4'),
('5','ChadName','Chad'),
('6','Charles','Chuck'),
('8','abandon','something');


INSERT INTO ts_words
SELECT (@rowNum := @rowNum+1), 
                a.word, 
                SUBSTRING(REPLACE(a.syns, a.id + '|', ''), 2) syns
    FROM (
                    SELECT a.*, 
                                    GROUP_CONCAT(id SEPARATOR '|') syns
                        FROM ts_synonyms a
                        GROUP BY word
              ) a, 
                (SELECT @rowNum := 0) b;
SELECT * FROM ts_synonyms;              
SELECT * FROM ts_words;             
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜