외부 도구 없이 MySQL에서 세미콜론으로 구분된 값으로 테이블을 조인할 수 있습니까?

Pure MySQL로 이 문제를 해결할 수 있나요? (조인: 열에 분리된 값)

문제:

두 테이블에 데이터가 저장되어 있고 첫 번째 테이블의 한 열에는 여러 개의 열이 나열되어 있습니다. 값은 세미콜론으로 구분됩니다. 이렇게 분리된 값을 기반으로 내부 조인을 수행해야 하는데 연결 테이블이 없고 외부 프로그래밍 언어를 사용할 수 없습니다.

토론:

문제는 세미콜론으로 구분된 목록을 별도의 행으로 변환하여 두 번째 테이블과 조인할 수 있도록 하는 것입니다. 이는 데이터 "파생" 또는 "정규화"라는 기술을 사용하여 달성할 수 있습니다.

해결책:

1. 정수형 테이블 만들기:

데이터를 정규화하려면 먼저 숫자 범위가 포함된 테이블이 필요합니다. 이 경우 ID가 1부터 세미콜론으로 구분된 목록에 필요한 최대 요소 수까지인 정수 시리즈 테이블을 생성할 수 있습니다.

2. JOIN 및 하위 쿼리 사용:

정수 시리즈 테이블이 있으면 다음 쿼리를 수행합니다.

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

설명:

• 이 쿼리는 user_resource 테이블을 정수 시리즈 테이블과 조인하여 각 사용자 및 해당 리소스 열의 각 요소에 대한 행 집합을 생성합니다.
• COUNT_IN_SET 함수는 리소스 열의 요소 수를 계산하고, VALUE_IN_SET 함수는 isequence.id를 기준으로 특정 요소를 추출합니다.
• 리소스 테이블과의 최종 조인은 추출된 요소와 ID를 일치시켜 해당 데이터를 검색합니다.

추가 함수(선택 사항):

쿼리는 다음과 같이 정의할 수 있는 두 가지 사용자 지정 함수인 COUNT_IN_SET 및 VALUE_IN_SET을 사용합니다.

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

이러한 함수는 일반적인 방법을 제공합니다. SQL 쿼리 내에서 구분된 문자열을 조작합니다.

테이블 및 데이터 예:

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

출력:

실행 샘플 데이터에 대한 쿼리는 다음과 같은 출력을 생성합니다.

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

위 내용은 외부 도구 없이 MySQL에서 세미콜론으로 구분된 값으로 테이블을 조인할 수 있습니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!