MySQL 데이터베이스에서 JSON 데이터 필드를 사용하는 방법

이 기사는 MySQL 9.1에서 JSON 구현의 세부 사항을 탐구하고 SQL과 NOSQL 데이터베이스 사이의 경계가 이전 기사 "SQL vs NoSQL : The Differences"에서 흐려진다는 주장을 계속합니다. 특징. MySQL 5.7 innodb 데이터베이스와 PostgreSQL 9.2는 JSON 문서 유형을 단일 필드에 직접 저장하는 지원을 지원합니다.

모든 데이터베이스는 JSON 문서를 단일 문자열 블로브로 저장할 수 있습니다. 그러나 MySQL 및 PostgreSQL은 검증 된 JSON 데이터를 간단한 문자열 대신 실제 키 값 쌍으로 저장합니다.

키 포인트

MySQL 5.7 InnoDB 데이터베이스 및 PostgreSQL 9.2는 JSON 문서 유형을 직접 지원하지만 직접 인덱싱 제한으로 인해주의해서 사용해야합니다.

JSON은 희소 데이터, 사용자 정의 속성, 계층 및 유연성이 필요한 상황을 저장하는 데 가장 적합합니다. 자주 쿼리 또는 색인 데이터를 자주 쿼리하는 정규화 된 열을 대체하지 않아야합니다. MySQL은 JSON 객체를 작성, 검색, 검색 및 수정하는 다양한 기능을 제공합니다. 이러한 기능에는 json_array (), json_object (), json_quote (), json_type (), json_valid (), json_contains (), json_search () 및 json_set () 및 json_merge_가 포함됩니다. 기능. MySQL 9.1은 JSON 데이터에서 파생 된 생성 된 열의 기능 인덱싱을 지원하므로 특정 JSON 요소의 효율적인 쿼리가 가능합니다.

MySQL은 JSON을 지원하지만 여전히 관계형 데이터베이스입니다. JSON의 과도한 사용은 SQL의 장점을 상쇄 할 수 있습니다.

• MySQL JSON 열에 JSON 문서를 저장하는 것은 정규화는 데이터베이스 구조를 최적화하는 데 사용되는 기술입니다. 첫 번째 일반 공식 (1NF) 규칙은 각 열이 단일 값을 보유해야한다고 규정하고 있으며 다중 값 JSON 문서를 저장하는 것은 분명히이 규칙을 위반합니다.
• 데이터에 명확한 관계형 데이터 요구 사항이있는 경우 적절한 단일 값 필드를 사용하십시오. JSON은 최후의 수단으로주의해서 사용해야합니다. JSON 값 필드는 직접 색인화 할 수 없으므로 자주 업데이트되거나 검색되는 열에서 사용하지 마십시오.
JSON 데이터에서 파생 된 생성 된 열의 기능 인덱싱을 사용하면 JSON 객체의 특정 부분을 색인화하여 쿼리 성능을 향상시킬 수 있습니다.
• 즉, JSON은 희소 데이터 또는 사용자 정의 속성에 대한 좋은 사용 사례가 있습니다.
JSON 데이터 유형 열
• 가있는 테이블을 만듭니다 책을 판매하는 상점을 고려하십시오. 모든 책에는 ID, ISBN, 제목, 출판사, 페이지 수 및 기타 명확한 관계형 데이터가 있습니다.
이제 각 책에 많은 카테고리 태그를 추가하려는 경우. 다음 방법을 사용하여 SQL로 구현할 수 있습니다.
각 태그 이름과 고유 한 ID를 저장하는 태그 테이블; 많은 레코드를 포함하는 태그 매핑 테이블, 책 ID를 태그 ID에 매핑합니다.
이 방법은 작동하지만 2 차 기능의 경우 너무 번거롭고 상당한 노력이 필요합니다. 따라서 MySQL 데이터베이스의 책 테이블에서 태그에 대한 MySQL JSON 필드를 정의 할 수 있습니다.
2. MySQL JSON 열에는 기본 값을 가질 수없고 기본 키로 사용할 수 없으며 외래 키로 사용할 수 없으며 직접 인덱스를 가질 수 없습니다.
그러나 MySQL 9.1을 사용하면 JSON 데이터에서 파생 된 생성 된 열에서 기능 색인을 생성하여 JSON 문서에서 특정 요소를 색인화 할 수 있습니다. 이러한 생성 된 열은 가상 또는 저장 및 보조 인덱스로 인덱싱 될 수 있습니다.
JSON 데이터를 추가하십시오

<code class="language-sql">CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;</code>

전체 JSON 문서는 삽입 또는 업데이트 문으로 전달 될 수 있으므로 저장 및 작동을 위해 JSON을 MySQL로 쉽게 이동할 수 있습니다.

예를 들어 우리의 책 태그는 배열 (문자열 내)으로 전달 될 수 있습니다.

<code class="language-sql">ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);</code>

다음 함수를 사용하여 JSON을 만들 수도 있습니다.

배열을 만드는 데 사용되는 를 선택하십시오. json_object () 함수, 개체를 만드는 데 사용됩니다. 예를 들면 : - return { "a": 1, "b": 2} : json_object ( 'a', 1, 'b', 2); 를 선택하십시오. 를 선택하십시오. 캐스트 (JSON AS JSON) 함수는 유효성을 보장하기 위해 값을 JSON 유형으로 변환하기 위해 함수 : CAST ( '{ "A": 1, "B": 2}'AS JSON);

json_valid () 함수 반환 1 JSON이 유효한 경우 1, 그렇지 않으면 0이 반환됩니다.

유효하지 않은 JSON 문서를 삽입하려는 시도는 오류가 발생하고 전체 레코드가 삽입/업데이트되지 않습니다. MySQL JSON 열에서 JSON 문서 검색

mySQL JSON 함수를 사용하여 json_contains () 함수와 같은 JSON 문서에 특정 값이 포함되어 있는지 확인할 수 있습니다. 경기가 발견되면 1을 반환합니다. 예를 들면 :

<code class="language-sql">INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);</code>

당신은 또한 "One"및 "All"플래그 및 검색 문자열을 전달하여 모든 경기를 찾아야하는지 여부를 지정할 수 있습니다 (여기서 %는 여러 문자와 일치하고 _와 같은 한 문자와 일치합니다). 예를 들면 :

• json_table () 함수 json 데이터를 관계형 형식으로 변환하여 쿼리를보다 쉽게 ​​할 수 있도록합니다.
• JSON PATH
JSON_Extract () 함수를 사용한 mysql json 쿼리는 지정된 경로를 기반으로 JSON 문서에서 특정 값을 검색 할 수 있습니다.
모든 경로 정의는 $로 시작하여 다른 선택기가 이어집니다.

<code class="language-sql">CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;</code>

기간과 $ .website 와 같은 이름이 이어집니다 [n], 여기서 n은 제로 인덱스 어레이의 위치 입니다. [*] 와일드 카드 평가 배열 모든 멤버 prefix ** 접미사 와일드 카드는 명명 된 접두사로 시작하는 모든 경로를 평가하고 이름이 지정된 접미사 로 끝납니다.
다음 예제는 다음 JSON 문서를 나타냅니다.
• 경로 예 :
$. 반환 1
• $ $. C [1] 4
리턴 $. D.E Return 5
• <..> $ **
당신은 JSON을 사용하여 MySQL 함수를 추출하여 책 테이블에서 이름과 첫 번째 태그를 효과적으로 추출 할 수 있습니다 :
보다 복잡한 예를 들어 JSON 구성 파일 데이터가 포함 된 사용자 테이블이 있다고 가정하십시오. 예를 들면 :

JSON 경로를 사용하여 트위터 이름을 추출 할 수 있습니다. 예를 들면 :

당신은 Where 절에서 JSON 경로를 사용하여 트위터 계정으로 사용자 만 반환 할 수 있습니다.

<code class="language-sql">CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;</code>

JSON 문서의 일부를 수정하십시오

<code class="language-sql">ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);</code>

경로 표기법을 사용하여 JSON 문서의 일부를 수정할 수있는 몇 가지 MySQL 기능이 있습니다. 이러한 기능에는 다음이 포함됩니다 JSON_SET (DOC, PATH, VAL [, PATH, VAL]…) : 문서에 데이터를 삽입하거나 업데이트합니다. 업데이트 북 세트 태그 = json_set (태그, '$ [0]', '업데이트 된 태그'); JSON_INSERT (DOC, PATH, VAL [, PATH, VAL]…) : 기존 값을 덮어 쓰지 않고 문서에 데이터를 삽입하십시오. 업데이트 북 세트 태그 = json_insert (태그, '$ [0]', '새 태그'); JSON_REPLAPE (DOC, PATH, VAL [, PATH, VAL]…) : 문서의 데이터를 바꾸십시오. 업데이트 북 세트 태그 = json_replace (태그, '$ [0]', '대체 태그'); json_array_append (doc, path, val [, path, val]…) : 배열 끝에 값을 추가하십시오. 책 세트 태그 = json_array_append (tags, '$', 'new tag'); json_array_insert (doc, path, val [, path, val]…) : JSON 배열의 특정 위치에 값을 삽입하십시오. 업데이트 책 세트 태그 = json_array_insert (태그, '$ [0]', '삽입 태그'); JSON_REMOVE (DOC, PATH [, PATH]…) : 문서에서 데이터를 삭제합니다. 책 세트 tags = json_remove (tags, '$ [1]'); JSON_PRETTY (VAL) : JSON 문서를 아름답게 읽을 수 있습니다. json_pretty ( '{ "name": "sitepoint", "tags": [ "mysql", "json"]); 를 선택하십시오 예를 들어, "JavaScript"태그가있는 모든 책에 "기술적"태그를 추가하려면 JSON_MERGE_PATCH () 함수를 사용할 수 있습니다.

추가 정보

MySQL 문서는 MySQL JSON 데이터 유형 및 관련 JSON 기능에 대한 자세한 정보를 제공합니다.

다시 상기 시키십시오. 절대적으로 필요한 경우가 아니라면 JSON을 사용하지 마십시오. MySQL에서 전체 문서 지향 NOSQL 데이터베이스를 시뮬레이션 할 수 있지만 SQL의 많은 이점을 상쇄 할 수 있으며 실제 NOSQL 시스템으로 직접 전환 할 수도 있습니다!

즉, SQL 애플리케이션에서보다 모호한 데이터 요구 사항에 대해 JSON 데이터 유형은 약간의 노력을 절약 할 수 있습니다.

mysql 에서 JSON 데이터를 사용하는 것에 대한 질문이 자주 묻습니다 mySQL에서 JSON을 사용할 수 있습니까?
• MySQL은 JSON 형식으로 데이터를 열에 저장하는 데 사용되는 JSON 데이터 유형을 제공하여 JSON을 지원합니다. MySQL 5.7.8부터 JSON 열로 테이블을 만들어 SQL을 사용하여 JSON 데이터를 삽입, 업데이트 및 쿼리 할 수 ​​있습니다. MySQL은 이러한 열에서 JSON 데이터를 처리하기위한 일련의 JSON 기능을 제공하여 JSON 데이터를 추출, 수정 및 조작 할 수 있습니다.
또한 SQL 쿼리에서 JSON 데이터를 사용하여 필요할 때 json_table과 같은 함수를 사용하여 관계형 데이터로 변환 할 수 있습니다. 그러나 MySQL은 본격적으로 NOSQL JSON 데이터베이스가 아닌 관계형 컨텍스트에서 JSON 데이터의 처리를 용이하게하기 위해 설계된 JSON 데이터 유형 지원이있는 관계형 데이터베이스라는 것을 이해하는 것이 중요합니다.

위에서 언급했듯이 JSON을 저장할 수 있다고 해서이 작업을 수행해야한다는 의미는 아닙니다. 정규화는 데이터베이스 구조를 최적화하는 기술입니다. 첫 번째 일반 공식 (1NF) 규칙은 각 열이 단일 값을 보유해야하며 다중 값 JSON 문서를 저장하면이 규칙을 위반합니다.

MySQL에 JSON을 저장해도 괜찮습니까?

mySQL에 JSON을 저장하는 경우 :

반 구조적 또는 동적 데이터는 엄격한 패턴에 적합하지 않습니다.

관계 설계가 비효율적 인 사용자 정의 속성. 페이로드 또는 로그를 저장하기 위해 JSON API와 통합.

그러나 그러나 JSON은 구조화되고 자주 쿼리 데이터의 정규화 된 관계형 저장소를 대체해서는 안됩니다. MySQL 9.1은 기능 색인 및 JSON_Table과 같은 기능으로 JSON 기능을 향상 시키지만 JSON 작업은 여전히 ​​큰 데이터 세트 또는 복잡한 쿼리에 대한 오버 헤드를 유발할 수 있습니다.

MySQL 쿼리에서 JSON을 사용하는 방법은 ?

MySQL의 JSON 함수를 사용하여 MySQL 쿼리에서 JSON을 사용할 수 있습니다. 이러한 기능을 사용하면 JSON 열 또는 데이터베이스에서 JSON 형식 문자열에 저장된 JSON 데이터를 추출, 조작 및 쿼리 할 수 ​​있습니다. JSON 열에서 JSON 데이터에 액세스하려면 -& gt;
JSON_EXTART, JSON_SET 및 JSON_OBJECTAGG 및 기타 JSON 기능을 사용하면 JSON 데이터를 필터링, 수정, 집계 및 처리 할 수 ​​있습니다. JSON 값에 따라 WARE 절을 사용하여 행을 필터링 할 수도 있습니다. MySQL의 JSON 기능은 데이터베이스 쿼리에서 JSON 객체를 직접 상호 작용하고 조작하는 유연한 방법을 제공합니다.
MySQL에서 언제 JSON을 사용해야합니까?
• 당신은 다음 상황에서 mysql에서 json을 사용해야합니다 :
반 구조화 된 데이터 : 사용자 지정 속성과 같은 예측할 수 없거나 드문 드문 필드를 처리 할 때 JSON을 사용하십시오.
• <:> 동적 모드 : JSON은 데이터 요구 사항이 자주 변경 될 때 유연성을 제공합니다.
Hydraft 또는 중첩 데이터 : JSON은 부모-자식 관계 또는 배열로 데이터를 지원합니다. API 통합 : JSON 문서로 페이로드, 응답 또는 로그를 저장합니다.
그러나 다음과 같은 경우 JSON을 사용하지 마십시오

인덱싱이 필요한 필드 자주 쿼리 된 필드 (함수 인덱싱이 도움이 될 수 있지만 관계 설계는 일반적으로 더 빠릅니다). 표준화 된 엄격한 관계 데이터가 필요합니다. JSON 경로에서의 복잡한 쿼리는 성능을 저하시킬 수 있습니다.

MySQL에 JSON 데이터를 저장하는 방법은 무엇입니까?

MySQL에 JSON 데이터를 저장하려면 두 가지 주요 옵션이 있습니다. 먼저 MySQL에 도입 된 JSON 데이터 유형을 사용하여 JSON 열이있는 테이블을 만들 수 있습니다. 이 방법은 JSON 데이터의 구조적 스토리지 및 더 나은 쿼리 성능을 제공합니다.

또는 JSON 데이터를 일반 바르 차르 또는 텍스트 열에 텍스트로 저장할 수 있습니다. 이 방법은 복잡한 데이터베이스 작업없이 JSON 데이터를 저장하고 검색 해야하는 경우에 적용됩니다.

MySQL에서 JSON 데이터를 색인화하는 방법은 ?

JSON 열을 직접 색인 할 수는 없지만 MySQL을 사용하면 JSON 값에서 파생 된 생성 된 열에서 기능 색인을 생성 할 수 있습니다.

예를 들어 JSON 배열의 첫 번째 요소를 색인하려면 : 이 방법은 자주 액세스하는 JSON 경로의 쿼리 성능을 향상시킵니다.

JSON 데이터의 경우 MySQL 또는 NOSQL 데이터베이스를 사용해야합니까?

프로젝트 요구 사항에 따라 다릅니다

관계형 스토리지가 필요한 경우, 때때로 관계형 모델에서 반 구조화 된 데이터, 사용자 정의 속성 또는 계층 적 데이터를 처리하는 경우 MySQL을 선택하십시오.

프로젝트에 주요 사용 사례로서 많은 JSON 스토리지, 유연한 스토리 및 문서 기반 작업이 포함 된 경우 NOSQL 데이터베이스 (예 : MongoDB)를 선택하십시오.

<code class="language-sql">CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;</code>

MySQL의 JSON 지원은 하이브리드 워크로드에 이상적이지만 문서 저장을위한 NOSQL 데이터베이스를 완전히 대체하는 것은 아닙니다.
MySQL JSON 필드에서 특정 값을 추출하는 방법은 무엇입니까?

MySQL JSON 필드에서 특정 값을 추출하려면 JSON_Extract () 함수 또는 약어를 사용하십시오.

MySQL JSON 필드에서 데이터를 쿼리하고 필터링하는 방법은 무엇입니까?

MySQL JSON 필드에 저장된 쿼리 및 필터 데이터를 위해 JSON_CONTAINS () 및 JSON_SEARCH ()와 같은 함수를 사용할 수 있습니다. json_extract ()를 사용하여 추가 필터링을 위해 특정 값을 검색 할 수도 있습니다.

위 내용은 MySQL 데이터베이스에서 JSON 데이터 필드를 사용하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!