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 <p><strong>Functions Used:</strong></p>
<p>The COUNT_IN_SET and VALUE_IN_SET functions are used as follows:</p>
<ul>
<li>
<strong>COUNT_IN_SET</strong>: Returns the count of character-delimited items in a given column.</li>
<li>
<strong>VALUE_IN_SET</strong>: Treats the delimited list as a one-based array and returns the value at a specific 'index'.</li>
</ul>
<p><strong>Additional Information:</strong></p>
<ul>
<li>The integerseries table is a table that contains the numbers from 1 to a specific limit, used to generate the 'normalized' rows.</li>
<li>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.</li>
</ul></code>

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!