키 값을 기반으로 MySQL의 JSON 개체에서 텍스트 값을 효율적으로 검색하는 방법은 무엇입니까?

MySQL의 JSON 키에서 개체 검색

문제:
MySQL 데이터베이스에서 JSON 데이터로 작업할 때 어떻게 효율적으로 개체를 검색할 수 있습니까? 다른 키를 사용하여 쿼리하는 동안 키 값을 기반으로 특정 객체를 검색하시겠습니까?

스키마:

다음 예제 스키마를 고려하세요.

<code class="sql">CREATE TABLE `fields` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `label` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `options` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;</code>

<code class="sql">INSERT INTO `fields` (label, options) VALUES
(
  'My Field', 
  '[{"text": "Grass", "value": "1"}, {"text": "Synthetic (New Type - Soft)", "value": "2"}, ...]');</code>

목표:

필드의 ID가 주어지면 해당 값을 기준으로 특정 옵션의 해당 텍스트 값을 검색합니다.

이전 해결 방법:

JSON 함수 조합을 사용하여 초기 시도를 했습니다. 그러나 이러한 접근 방식은 번거롭고 복잡한 표현식이 필요했습니다.

JSON_TABLE() 함수(MySQL 8.0):

MySQL 8.0에서는 JSON_TABLE() 함수를 도입했습니다. 프로세스:

<code class="sql">SELECT field_options.* 
FROM fields 
CROSS JOIN 
JSON_TABLE(fields.options, 
  '$[*]' 
COLUMNS(
  text TEXT PATH '$.text',
  value TEXT PATH '$.value'
 )
) AS field_options
WHERE field_options.value = 1;

+-------+-------+
| text  | value |
+-------+-------+
| Grass | 1     |
+-------+-------+</code>

JSON이 아닌 대안:

더 간단한 해결책은 JSON 데이터를 정규화된 테이블로 변환하는 것입니다.

<code class="sql">CREATE TABLE `field_options` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `field_id` int(10) unsigned NOT NULL,
  `text` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `value` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`field_id`) REFERENCES `fields`(`id`)
);</code>

<code class="sql">INSERT INTO `field_options` (field_id, text, value) VALUES
(
  1, 'Grass', '1'),
  (1, 'Synthetic (New Type - Soft)', '2'),
  ...
);</code>

이 접근 방식을 사용하면 기존 SQL 기술을 사용하여 효율적인 조회가 가능합니다.

<code class="sql">SELECT *
FROM `field_options`
WHERE value = '1';</code>

위 내용은 키 값을 기반으로 MySQL의 JSON 개체에서 텍스트 값을 효율적으로 검색하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!