MySQL数据库中每个字段的多个值

Question

• First of all: I know that its really bad style to work with multiple entries in one field, but I did not create the database myself and now I have to work with it. It has around 20000 entries and I really dont want to change it at the moment.
• The table A I'm talking about has a column with some ID's, e.G.: ,282,3358,123,
• Those ID's are matching with the primary keys of some other table B. B has another column name.
• I want to get those names into my table A replacing those ID's
• Example:

Table B

| ID |      name         |     
+----+-------------------+
|282 |      name_1       |
+----+-------------------+
|3358|      name_2       |
+----+-------------------+
|123 |      name_3       |

Initial Situation TABLE A

 ... |         ID             | ...    
-----+------------------------+-----
 ... |    ,282,3358,123,      | ... 

Desired Result for TABLE A

 ... |         ID             | ...    
-----+------------------------+-----
 ... | ,name_1,name_2,name_3, | ...  

Answer 1

I found a way how do deal with my problem. However it's really not recommendable to use multiple entries per field. But if your in a situation like me, where you have to work with such an unrelational schemed database without being allowed to change the scheme, here we are:

SELECT [...] ,
(SELECT GROUP_CONCAT( table_B.name SEPARATOR ',' ) FROM table_B 
WHERE FIND_IN_SET ( B.ID , SUBSTRING ( table_A.ID , 2 , length( table_A.ID ) -2 ) ) >0 ) ,
[...]
FROM table_A

Some Explanations:

• the SUBSTRING is needed in order to remove the "," in the begining of the multiple field entry. E.g.: SUBSTRING ( ",282,3358,123," , 2 , length( ",282,3358,123," ) -2 ) results in "282,3358,123"
• the FIND_IN_SET method searches a value into a comma separated string list and returns the index of the value. If no value can be found, it returns 0. Thats why I compare the whole string against 0.
• the GROUP_CONCAT method concatenates the matching names. As a result it delivers a comma separated string list ==> name_1,name_2,name_3

Some Annotations:

• Using this query is very inefficient. That query has to deal with at least #ROWS_IN_TABLE_A * #ROWS_IN_TABLE_B table rows
• Regarding perfomance issues, one should precompute the table and store it into a database (thats what I did).