Home > Database > Mysql Tutorial > body text

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

Susan Sarandon
Release: 2024-10-31 12:56:02
Original
779 people have browsed it

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>
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template