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

  • 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, | ...

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).

With just one sql instruction could be very difficult, I'would do a function in MySQL that:

1) Create a cursor with table A 2) Search each value from table B 3) Fill the [new] field with values from B

20000 rows is not too much.