Can I Join on \';\' Separated Values in a MySQL Column Without Using External Languages?

Can I Resolve This with Pure MySQL? (Joining on ';' Separated Values in a Column)

This question arises when you have data spread across multiple tables and need to retrieve it in a single query without using external languages like PHP. The data is stored in a denormalized fashion, with multiple values within a single column separated by semicolons (;).

Problem Description:

In this case, the user_resource table has a resources column that contains a list of resource IDs separated by semicolons:

user	resources
user1	1;2;4
user2	2
user3	3;4

Desired Result:

The desired result is to obtain a table that lists each user and the corresponding resources:

user	data
user1	data1
user1	data2
user1	data4
user2	data2

Solution:

The solution involves creating a 'normalized' table from the user_resource table, treating the list of resource IDs as rows, and joining the normalized table with the resource table. This 'normalization' is achieved using a combination of the COUNT_IN_SET and VALUE_IN_SET functions.

Normalized Table:

The generated 'normalized' table will resemble the following:

user	resources	resources_index	resources_value
sampleuser	1;2;3	1	1
sampleuser	1;2;3	2	2
sampleuser	1;2;3	3	3
stacky	2	1	2
testuser	1;3	1	1
testuser	1;3	2	3

Final Query:

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

Functions Used:

The COUNT_IN_SET and VALUE_IN_SET functions are used as follows:

• COUNT_IN_SET: Returns the count of character-delimited items in a given column.
• VALUE_IN_SET: Treats the delimited list as a one-based array and returns the value at a specific 'index'.

Additional Information:

• The integerseries table is a table that contains the numbers from 1 to a specific limit, used to generate the 'normalized' rows.
• This solution provides a generic approach to handling delimited list items in a MySQL column, not just in the context of the specific example provided.

The above is the detailed content of Can I Join on \';\' Separated Values in a MySQL Column Without Using External Languages?. For more information, please follow other related articles on the PHP Chinese website!