Can I Join Tables on Semicolon-Separated Values in MySQL Without External Tools?

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

Problem:

You have data stored in two tables, with one column in the first table listing multiple values separated by semicolons. You need to perform an inner join based on these separated values, but you don't have a linking table and cannot use any external programming languages.

Discussion:

The challenge lies in converting the semicolon-separated list into separate rows, making it possible to join with the second table. This can be achieved using a technique called "deriving" or "normalizing" the data.

Solution:

1. Create an Integerseries Table:

To normalize the data, you first need a table that contains a range of numbers. In this case, you could create an integerseries table with IDs from 1 to the maximum number of elements you expect in the semicolon-separated list.

2. Use JOIN and Subqueries:

Once you have the integerseries table, perform the following 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, ';') /* normalize */

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

Explanation:

• This query joins the user_resource table with the integerseries table to generate a set of rows for each user and each element in the corresponding resources column.
• The COUNT_IN_SET function counts the number of elements in the resources column, and the VALUE_IN_SET function extracts a specific element based on the isequence.id.
• The final join with the resource table matches the extracted element with the ID to retrieve the corresponding data.

Additional Functions (Optional):

The query uses two custom functions, COUNT_IN_SET and VALUE_IN_SET, which can be defined as follows:

<code class="sql">-- Function to count the number of delimited items in a string
DELIMITER $$
DROP FUNCTION IF EXISTS `COUNT_IN_SET`$$
CREATE FUNCTION `COUNT_IN_SET`(haystack VARCHAR(1024), 
                               delim CHAR(1)
                               ) RETURNS INTEGER
BEGIN
      RETURN CHAR_LENGTH(haystack) - CHAR_LENGTH( REPLACE(haystack, delim, '')) + 1;
END$$
DELIMITER ;

-- Function to get the value at a specific index in a delimited string
DELIMITER $$
DROP FUNCTION IF EXISTS `VALUE_IN_SET`$$
CREATE FUNCTION `VALUE_IN_SET`(haystack VARCHAR(1024), 
                               delim CHAR(1), 
                               which INTEGER
                               ) RETURNS VARCHAR(255) CHARSET utf8 COLLATE utf8_unicode_ci
BEGIN
      RETURN  SUBSTRING_INDEX(SUBSTRING_INDEX(haystack, delim, which),
                     delim,
                     -1);
END$$
DELIMITER ;</code>

These functions provide a generic way to manipulate delimited strings within SQL queries.

Example Tables and Data:

<code class="sql">-- Integerseries table
CREATE TABLE `integerseries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);
-- Resource table
CREATE TABLE `resource` (
  `id` int(11) NOT NULL,
  `data` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

-- Data for resource table
INSERT INTO `resource` (`id`, `data`) VALUES
(1, 'abcde'),
(2, 'qwerty'),
(3, 'azerty');

-- User_resource table
CREATE TABLE `user_resource` (
  `user` varchar(50) NOT NULL,
  `resources` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`user`)
);

-- Data for user_resource table
INSERT INTO `user_resource` (`user`, `resources`) VALUES
('sampleuser', '1;2;3'),
('stacky', '2'),
('testuser', '1;3');</code>

Output:

Executing the query on the sample data will produce the following output:

+----------+-------+
| user     | data   |
+----------+-------+
| sampleuser | abcde  |
| sampleuser | qwerty |
| sampleuser | azerty |
| stacky    | qwerty |
| testuser  | abcde  |
| testuser  | azerty |
+----------+-------+

The above is the detailed content of Can I Join Tables on Semicolon-Separated Values in MySQL Without External Tools?. For more information, please follow other related articles on the PHP Chinese website!