Can I Join Tables with Semi-colon Separated Values in a Column Using Pure MySQL?

Can I resolve this with pure mysql? (joining on ';' separated values in a column)

Question:

Given two tables, a user table t1 with a resources column containing ';' separated resource IDs, and a resource table t2 with an ID and data, you want to join t1 and t2 to get a table with user and resource data.

Problem:

You can't use external languages to manipulate the results and the user_resources table is denormalized.

Solution:

Create a table called integerseries with numbers from 1 to a certain limit. Use COUNT_IN_SET and VALUE_IN_SET functions, which treat the resources column as an array, to normalize the table. Then join with integerseries to extract individual resource IDs.

<code class="sql">SELECT user_resource.user, 
       resource.data

FROM user_resource 
     JOIN integerseries AS isequence 
       ON isequence.id <= COUNT_IN_SET(user_resource.resources, ';') 

     JOIN resource 
       ON resource.id = VALUE_IN_SET(user_resource.resources, ';', isequence.id)      
ORDER BY
       user_resource.user,  resource.data</code>

The above is the detailed content of Can I Join Tables with Semi-colon Separated Values in a Column Using Pure MySQL?. For more information, please follow other related articles on the PHP Chinese website!