Ranking Web

Notícias o tempo todo

Como usar campos de dados JSON em bancos de dados MySQL — SitePoint

Posted by:

actmkt

|

On:

|

Meu artigo “SQL vs NoSQL: as diferenças” observou que a linha entre os bancos de dados SQL e NoSQL tornou-se cada vez mais tênue, com cada campo adotando recursos do outro. Os bancos de dados MySQL 5.7+ InnoDB e PostgreSQL 9.2+ suportam diretamente tipos de documentos JSON em um único campo. Este artigo examinará a implementação JSON do MySQL 9.1 com mais detalhes.

Observe que qualquer banco de dados aceitará documentos JSON como um blob de cadeia única. No entanto, MySQL e PostgreSQL suportam dados JSON validados em pares chave/valor reais, em vez de uma string básica.

Principais conclusões

  • Os tipos de documentos JSON são suportados diretamente nos bancos de dados MySQL 5.7+ InnoDB e PostgreSQL 9.2+, mas devem ser usados ​​com sabedoria devido às limitações na indexação direta.
  • JSON é mais adequado para dados pouco preenchidos, atributos personalizados, estruturas hierárquicas e casos que exigem flexibilidade. Não deve substituir colunas normalizadas para dados frequentemente consultados ou indexados.
  • MySQL oferece uma variedade de funções para criar, validar, pesquisar e modificar objetos JSON. Estes incluem JSON_ARRAY(), JSON_OBJECT(), JSON_QUOTE(), JSON_TYPE(), JSON_VALID(), JSON_CONTAINS(), JSON_SEARCH(), e functions like JSON_SET() and JSON_MERGE_PATCH() para atualizar documentos JSON usando notação de caminho.
  • O MySQL 9.1 suporta indexação funcional em colunas geradas derivadas de dados JSON, permitindo consultas eficientes de elementos JSON específicos.
  • Embora o MySQL suporte JSON, ele continua sendo um banco de dados relacional. O uso excessivo de JSON pode anular os benefícios do SQL.

Só porque você pode armazenar documentos JSON em colunas JSON do MySQL…

… isso não significa que você deveria.

A normalização é uma técnica usada para otimizar a estrutura do banco de dados. A regra do Primeiro Formulário Normal (1NF) determina que cada coluna deve conter um único valor – o que é claramente quebrado pelo armazenamento de documentos JSON de vários valores.

Se você tiver requisitos de dados relacionais claros, use campos de valor único apropriados. JSON deve ser usado com moderação como último recurso. Os campos de valor JSON não podem ser indexados diretamente, portanto evite usá-los em colunas atualizadas ou pesquisadas regularmente.

A indexação funcional em colunas geradas derivadas de JSON permite indexar partes do seu objeto JSON, melhorando o desempenho da consulta.

Dito isto, existem bons casos de uso de JSON para dados pouco preenchidos ou atributos personalizados.

Crie uma tabela com uma coluna de tipo de dados JSON

Considere uma loja que vende livros. Todos os livros possuem ID, ISBN, título, editora, número de páginas e outros dados relacionais claros.

Agora, se você quiser adicionar qualquer número de tags de categoria a cada livro. Você poderia conseguir isso em SQL usando:

  1. UM marcação tabela que armazenou cada nome de tag com um ID exclusivo e
  2. UM mapa de tags tabela com registros muitos para muitos mapeando IDs de livros para IDs de tags

Funcionará, mas é um esforço complicado e considerável para um recurso menor. Portanto, você pode definir um campo MySQL JSON para tags no banco de dados MySQL livro mesa:

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

As colunas JSON do MySQL não podem ter um valor padrão, não podem ser usadas como chave primária, não podem ser usadas como chave estrangeira ou ter índices diretos.

Mas, com o MySQL 9.1, você pode criar índices funcionais sobre colunas geradas derivado de dados JSON, que permite a indexação de elementos específicos em um documento JSON. Essas colunas geradas podem ser virtuais ou armazenadas e indexadas como índices secundários.

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$(0)')),
ADD INDEX idx_first_tag (first_tag);

Adicionando dados JSON

Documentos JSON inteiros podem ser passados INSERIR ou ATUALIZAR instruções, facilitando a movimentação do JSON para o MySQL para armazenamento e manipulação.

Por exemplo, nossas tags de livro podem ser passadas como um array (dentro de uma string):

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '("JavaScript", "ES2015", "JSON")'
);

JSON também pode ser criado com estes:

  • Função JSON_ARRAY()que cria matrizes. Por exemplo:-- returns (1, 2, "abc"): SELECT JSON_ARRAY(1, 2, 'abc');
  • Função JSON_OBJECT()que cria objetos. Por exemplo:-- returns {"a": 1, "b": 2}: SELECT JSON_OBJECT('a', 1, 'b', 2);
  • Função JSON_QUOTE()que cita uma string como um valor JSON. Por exemplo:-- returns "(1, 2, "abc")": SELECT JSON_QUOTE('(1, 2, "abc")');
  • Função CAST (anyValue AS JSON)que converte um valor como um tipo JSON para validade:SELECT CAST('{"a": 1, "b": 2}' AS JSON);

O Função JSON_TYPE() permite verificar os tipos de valor JSON. Deve retornar OBJECT, ARRAY, um tipo escalar (INTEGER, BOOLEAN, etc), NULL ou um erro. Por exemplo:


SELECT JSON_TYPE('(1, 2, "abc")');


SELECT JSON_TYPE('{"a": 1, "b": 2}');


SELECT JSON_TYPE('{"a": 1, "b": 2');

O Função JSON_VALID() retorna 1 se o JSON for válido ou 0 caso contrário:


SELECT JSON_TYPE('(1, 2, "abc")');


SELECT JSON_TYPE('{"a": 1, "b": 2}');


SELECT JSON_TYPE('{"a": 1, "b": 2');

A tentativa de inserir um documento JSON inválido gerará um erro e todo o registro não será inserido/atualizado.

Pesquisando documentos JSON na coluna JSON do MySQL

Com funções JSON MySQL como Função JSON_CONTAINS()você pode verificar se um documento JSON contém um valor específico. Ele retorna 1 quando uma correspondência é encontrada. Por exemplo:


SELECT * FROM `book` WHERE JSON_CONTAINS(tags, '("JavaScript")');

O Função JSON_SEARCH() retorna o caminho para um valor em um documento JSON. Retorna NULL quando não há correspondência.

Você também pode especificar se precisa encontrar todas ou uma única correspondência passando os sinalizadores ‘um’ e ‘todos’ ao lado da string de pesquisa (onde % corresponde a qualquer número de caracteres e _ corresponde a um caractere de maneira idêntica a COMO). Por exemplo:


SELECT * FROM `book` WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;

O Função JSON_TABLE() transforma dados JSON em um formato relacional para facilitar a consulta:

SELECT * 
FROM JSON_TABLE(
    '({"tag": "SQL"}, {"tag": "JSON"})', 
    '$(*)' COLUMNS (tag VARCHAR(50) PATH '$.tag')
) AS tags_table;

Caminhos JSON

Uma consulta MySQL JSON usando o Função JSON_EXTRACT() pode recuperar valores específicos de um documento JSON com base em um caminho especificado.


SELECT JSON_EXTRACT('{"id": 1, "website": "SitePoint"}', '$.website');

Todas as definições de caminho começam com $ seguido por outros seletores:

  • Um ponto seguido por um nome, como $.website
  • (N) onde N é a posição em uma matriz indexada a zero
  • O .
  • curinga avalia todos os membros de um objeto
  • O

curinga avalia todos os membros de uma matriz

{
  "a": ,
  "b": 2,
  "c": (, ),
  "d": {
    "e": ,
    "f": 6
  }
}

O curinga prefix**suffix avalia todos os caminhos que começam com o prefixo nomeado e terminam com o sufixo nomeado

  • Os exemplos a seguir referem-se ao seguinte documento JSON:
  • Caminhos de exemplo:
  • $.a retorna 1
  • $.c retorna (3, 4)
  • $.c(1) retorna 4

$.de retorna 5

SELECT
  title, tags->"$(0)" AS `tag1`
FROM `book`;

$**.e retorna (5) Você poderia usar a função JSON extract MySQL para extrair o nome e a primeira tag da sua tabela de livros de forma eficiente: Para um exemplo mais complexo, suponha que você tenha um

usuário tabela com dados de perfil JSON. Por exemplo: eu ia
nome perfil 1
Craig { “e-mail”: (“craig@email1.com”, “craig@email2.com”), “twitter”: “@craigbuckler” } 2

SitePoint

SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`;

{ “e-mail”: (), “twitter”: “@sitepointdotcom” }

SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`
WHERE
  profile->"$.twitter" IS NOT NULL;

Você pode extrair o nome do Twitter usando um caminho JSON. Por exemplo:

Você poderia usar um caminho JSON na cláusula WHERE para retornar apenas usuários com uma conta do Twitter:

JSON_PRETTY(valor)

UPDATE book
SET tags = JSON_MERGE_PATCH(tags, '("technical")')
WHERE JSON_SEARCH(tags, 'one', 'JavaScript') IS NOT NULL;

: imprime documentos JSON de forma bonita para melhor legibilidade.

Por exemplo, se você deseja adicionar uma tag “técnica” a qualquer livro que já possua uma tag “JavaScript”, você pode usar a função JSON_MERGE_PATCH(): Mais informações A documentação do MySQL fornece informações detalhadas sobre o MySQL Tipo de dados JSONe o

funções JSON associadas .Novamente, recomendo que você não use JSON, a menos que seja absolutamente necessário. Você poderia emular todo um banco de dados NoSQL orientado a documentos no MySQL, mas isso anularia muitos

benefícios do SQL

e você também pode mudar para um sistema NoSQL real!

Dito isso, os tipos de dados JSON podem economizar esforços para requisitos de dados mais obscuros em um aplicativo SQL.

Perguntas frequentes sobre como trabalhar com dados JSON no MySQL

Você pode usar JSON no MySQL?

MySQL oferece suporte a JSON, oferecendo um tipo de dados JSON para armazenar dados formatados em JSON em colunas. A partir do MySQL 5.7.8, você pode criar tabelas com colunas JSON, permitindo inserir, atualizar e consultar dados JSON usando SQL. O MySQL fornece uma variedade de funções JSON para trabalhar com dados JSON nessas colunas, permitindo extração, modificação e manipulação.

Além disso, você pode usar dados JSON em consultas SQL, convertendo-os em dados relacionais quando necessário usando funções como JSON_TABLE. No entanto, é importante entender que o MySQL é fundamentalmente um banco de dados relacional, e seu suporte ao tipo de dados JSON tem como objetivo facilitar o trabalho com dados JSON dentro de um contexto relacional, em vez de ser um banco de dados NoSQL JSON completo.

Conforme descrito no artigo acima, só porque você pode armazenar JSON, não significa que deva: Normalização é uma técnica usada para otimizar a estrutura do banco de dados. A regra do Primeiro Formulário Normal (1NF) determina que cada coluna deve conter um único valor – que é quebrado pelo armazenamento de documentos JSON de vários valores.

  • Posso armazenar JSON no MySQL? Não há problema em armazenar JSON no MySQL para cenários como:
  • Dados semiestruturados ou dinâmicos isso não se encaixa bem em um esquema rígido.
  • Atributos personalizados onde o design relacional seria ineficiente.

Integração com APIs baseadas em JSON para armazenar cargas úteis ou logs. No entanto, JSON deve

não

substitua o armazenamento relacional normalizado por dados estruturados e consultados com frequência. Embora o MySQL 9.1 melhore a funcionalidade JSON com recursos como índices funcionais e JSON_TABLE, as operações JSON ainda podem introduzir sobrecarga para grandes conjuntos de dados ou consultas complexas. Como usar JSON em uma consulta MySQL?Você pode usar JSON em consultas MySQL empregando

Funções JSON do MySQL

. Essas funções permitem extrair, manipular e consultar dados JSON armazenados em colunas JSON ou strings formatadas em JSON em seu banco de dados. Para acessar dados JSON em uma coluna JSON, use o operador -> seguido do caminho para o elemento JSON desejado.

Funções JSON como JSON_EXTRACT, JSON_SET e JSON_OBJECTAGG permitem filtrar, modificar, agregar e trabalhar com dados JSON. Você também pode filtrar linhas com base em valores JSON usando a cláusula WHERE. Os recursos JSON do MySQL fornecem uma maneira versátil de interagir e manipular um objeto JSON diretamente nas consultas do seu banco de dados.

  1. Quando usar JSON no MySQL?Você deve usar JSON no MySQL para os seguintes cenários:
  2. Dados semiestruturados: use JSON ao lidar com campos imprevisíveis ou esparsos (por exemplo, atributos personalizados).
  3. Esquemas dinâmicos: JSON oferece flexibilidade quando os requisitos de dados mudam com frequência.
  4. Dados hierárquicos ou aninhados: JSON oferece suporte a dados com relacionamentos ou matrizes pai-filho.

Integração de API

  • : armazene cargas, respostas ou logs como documentos JSON.
  • No entanto, evite JSON para:
  • Campos consultados com frequência que requerem indexação (índices funcionais podem ajudar, mas o design relacional costuma ser mais rápido).

Dados estritamente relacionais que requerem normalização.

Situações em que consultas complexas em caminhos JSON prejudicariam o desempenho.

Como armazenar dados JSON no MySQL?

Para armazenar dados JSON no MySQL, você tem duas opções principais. Primeiro, você pode usar o tipo de dados JSON introduzido no MySQL para criar uma tabela com uma coluna JSON. Este método fornece armazenamento estruturado e melhor desempenho de consulta para dados JSON.

Como alternativa, você pode armazenar dados JSON como texto em uma coluna VARCHAR ou TEXT normal. Essa abordagem é adequada quando você precisa principalmente armazenar e recuperar dados JSON sem operações complexas de banco de dados. Como você indexa dados JSON no MySQL? Embora você não possa indexar diretamente uma coluna JSON, o MySQL permite criar

índices funcionais

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$(0)')),
ADD INDEX idx_first_tag (first_tag);

em colunas geradas derivadas de valores JSON.

Por exemplo, para indexar o primeiro elemento de uma matriz JSON:

Essa abordagem melhora o desempenho da consulta para caminhos JSON acessados ​​com frequência.

  • Você deve usar MySQL ou um banco de dados NoSQL para dados JSON? Depende dos requisitos do seu projeto:
  • Escolha MySQL se você precisar de armazenamento relacional com manipulação ocasional de JSON para dados semiestruturados, atributos personalizados ou dados hierárquicos em um modelo relacional.

Escolha um banco de dados NoSQL

(como MongoDB) se o seu projeto envolver armazenamento JSON extenso, esquemas flexíveis e operações baseadas em documentos como caso de uso principal.

O suporte JSON do MySQL é excelente para cargas de trabalho híbridas, mas não pode substituir totalmente um banco de dados NoSQL criado especificamente para armazenamento de documentos. Como você extrai valores específicos de um campo JSON do MySQL?Para extrair valores específicos de um


SELECT JSON_EXTRACT(tags, '$(0)') AS first_tag FROM book;


SELECT tags->'$(0)' AS first_tag FROM book;

Campo JSON do MySQL

use a função JSON_EXTRACT() ou o operador abreviado ->. Como você consulta e filtra dados em um campo JSON do MySQL?Para consultar e filtrar dados armazenados em um


SELECT * FROM book 
WHERE JSON_CONTAINS(tags, '("JavaScript")');


SELECT * FROM book 
WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;


SELECT * FROM book 
WHERE JSON_EXTRACT(tags, '$(0)') = 'JavaScript';

Campo JSON do MySQL, você pode usar funções como JSON_CONTAINS() e JSON_SEARCH(). Você também pode usar JSON_EXTRACT() para recuperar valores específicos para filtragem adicional.

Source link

Posted by

actmkt

in

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *