json_capa

Hoje vamos falar um pouco sobre um assunto muito desejado e esperado, tanto por desenvolvedores quanto por DBAs.
O MySQL suporta armazenar documentos JSON????
A resposta é um grande e sonoro SIM!!!
Na versão 5.7.8 foi adicionado o data type JSON e com ele você pode armazenar e manipular documentos JSON.

Isso significa que posso fazer no MySQL tudo que faço no MongoDB?

Não, pelo menos por enquanto não. Eu ainda não vejo dessa forma, na maturidade atual do recurso ele já pode ajudar muito a suprir algumas necessidades em armazenar e manipular documentos JSON, mas não temos(ainda!?) uma engine completa e altamente robusta para isso!

Vamos ver alguns exemplos de como armazenar, buscar e manipular documentos JSON. Em um próximo POST vou falar sobre performance e melhores práticas.

Para esse exemplo estou utilizando o MySQL 5.7.11 que foi instalado no Ubuntu 15.10.
Veja como fazer instalação do MySQL 5.7 em alguns ambientes que não sejam derivados do RedHat e do Debian.
FreeBSD
SlackWare
Ubuntu 15.10

Criando uma tabela e uma coluna com o data type JSON

CREATE TABLE pessoas
(
 id INT AUTO_INCREMENT PRIMARY KEY
 , pessoa JSON
) ENGINE = InnoDB;

Mas Diego, por que utilizar o data type JSON se podemos utilizar o TEXT ou o BLOB?
A resposta é simples, utilizando o data type JSON você ganha algumas ferramentas valiosas, como uma validação dos documentos, ou seja, nada de gravar documentos incompletos ou com erro de sintaxe, ganha opção de realizar busca e manipulação dos documentos sem o uso de REGEXP, o que vai facilitar muito a sua vida, sem contar com os recursos de performance(que vou falar em outro post).

Inserindo os dados

INSERT INTO pessoas 
(pessoa) 
VALUES
('{"nome": "Fulano", "sexo": "M", "dtNascimento": "1990-01-01", "cidade": "Curitiba", "uf": "PR"}');

 

select_json_1

Mas também podemos inserir um array de objetos.

INSERT INTO pessoas (pessoa) VALUES('[{"nome": "Fulano"}, {"nome": "Beltrano"}, {"nome": "Ciclano"}]');

select_json_2

 

O MySQL vai tratar como types diferentes.

select_json_3_JSON_type

Para os próximo exemplos vou alterar os dados da tabela pessoas.

TRUNCATE TABLE pessoas;
INSERT INTO pessoas (pessoa) VALUES('{"nome": "Fulano", "sexo":"m", "idade":18, "localizacao":{"cidade":"Curitiba", "UF":"PR"} , "areas_interesse": ["A", "B", "c"]}');
INSERT INTO pessoas (pessoa) VALUES('{"nome": "Beltrana", "sexo":"f", "idade":19, "localizacao":{"cidade":"Florianópolos", "UF":"SC"} , "areas_interesse": ["A", "C", "F"]}');
INSERT INTO pessoas (pessoa) VALUES('{"nome": "Ciclano", "sexo":"m", "idade":20, "localizacao":{"cidade":"Porto Alegre", "UF":"RS"} , "areas_interesse": ["A", "B", "D"]}');
INSERT INTO pessoas (pessoa) VALUES('{"nome": "Zezinho", "sexo":"m", "idade":21, "localizacao":{"cidade":"São Paulo", "UF":"SP"} , "areas_interesse": ["E"]}');
INSERT INTO pessoas (pessoa) VALUES('{"nome": "Luizinha", "sexo":"m", "idade":22, "localizacao":{"cidade":"Rio de Janeiro", "UF":"RJ"} , "areas_interesse": ["A", "E"]}');

 

Selecionando e filtrando dados dos documentos JSON

Assim como já acontece em outras linguagens de programação e outros bancos de dados que manipulam documentos JSON, o caracter sifrão($) é utilizado para representar o documento, e após podem ser utilizados seletores.

JSON_EXTRACT

Retorna o valor de uma chave do documento.

SELECT JSON_EXTRACT(pessoa, '$.nome') FROM pessoas;
select_json_extract_1

SELECT JSON_EXTRACT(pessoa, '$.areas_interesse') FROM pessoas;
select_json_extract_2

Mais alguns exemplos

SELECT JSON_EXTRACT(pessoa, '$.localizacao') FROM pessoas;
SELECT JSON_EXTRACT(pessoa, '$.localizacao.UF') FROM pessoas;
SELECT * FROM pessoas WHERE JSON_EXTRACT(pessoa, '$.localizacao.cidade') = 'Curitiba';

JSON_UNQUOTE

Remove as aspas quando o valor de uma chave for string.

SELECT JSON_UNQUOTE(JSON_EXTRACT(pessoa, '$.nome')) AS nome FROM pessoas;

 JSON_KEYS

Retornas as chaves de um documento.

SELECT JSON_KEYS(pessoa) FROM pessoas;
SELECT JSON_KEYS(pessoa, '$.localizacao') FROM pessoas;

JSON_LENGTH

Retorna a quantidade de chaves que existem no documento, ou a partir de uma profundidade específica.

SELECT JSON_LENGTH(pessoa) FROM pessoas;
SELECT JSON_LENGTH(pessoa, '$.localizacao' ) FROM pessoas;

Da para utilizar essa função para saber se uma chave existe, isso pode ser bem útil, já que o que eu mais escuto é que o “bom” de armazenar documentos, cada um pode ter uma estrutura diferente.

SELECT * FROM pessoas WHERE JSON_LENGTH(pessoa, '$.localizacao.UF') = 1;
SELECT * FROM pessoas WHERE JSON_LENGTH(pessoa, '$.localizacao') >= 1;

 

JSON_SEARCH

Retorna o path de um valor que será buscado no documento. Caso o valor não seja localizado, o valor NULL será retornado. o primeiro parâmetro é o documento JSON, o segundo deve ser o valor “one” ou o valor “all”, caso o valor do segundo parâmetro seja “one”, a função irá retornar a primeira chave(elemento) que possui esse valor, e caso o valor do parâmetro seja “all”, será retornada todas as chaves(elementos) que possuem o valor, o terceiro parâmetro é a string que será buscada e aqui é necessário uma atenção especial, pois o valor que será buscado é case sensitive., o quarto valor é o caracter de escape que deve ser utilizado para realizar a busca, caso não tenho o valor NULL deve ser informado, esse parâmetro é opcional, o quinto parâmetro é o path que a busca deve ser realizada, esse parâmetro também não é obrigatório.

Para entender melhor o diferença dos valores “one”e “all”no segundo parâmetro faça esse teste.

INSERT INTO pessoas (pessoa) VALUES('{"nome": "A", "sexo":"A", "idade":"A", "localizacao":{"cidade":"A", "UF":"A"} , "areas_interesse": ["A", "A"]}');
SELECT JSON_SEARCH(pessoa, 'one', 'A') FROM pessoas;
SELECT JSON_SEARCH(pessoa, 'all', 'A') FROM pessoas;

Veja como fazer para selecionar quem tem o valor “D” informado no elemento areas_interesse.

SELECT * FROM pessoas WHERE JSON_SEARCH(pessoa, 'one', 'D', NULL, '$.areas_interesse') IS NOT NULL;

Ou quem é da cidade de Curitiba.

SELECT * FROM pessoas WHERE JSON_SEARCH(pessoa, 'one', 'Curitiba', NULL, '$.localizacao.cidade') IS NOT NULL;

Adicionando Elementos em um documento JSON

JSON_SET

Insere ou altera um elemento no documento. Sua utilização é bem simples, e ela recebe 3 elementos. O primeiro é o documento, o segundo é o path e nome do novo elemento, e o terceiro é o valor.

SELECT JSON_SET('{"a":"a1", "b":"b2"}', '$.c', 'c3');
SELECT JSON_SET('{"a":"a1", "b":"b2"}', '$.a', 'c3');
UPDATE pessoas SET pessoa = JSON_SET(pessoa, '$.dtNascimento', '1990-01-01') WHERE JSON_SEARCH(pessoa, 'one', 'Fulano', NULL, '$.nome') IS NOT NULL;
UPDATE pessoas SET pessoa = JSON_SET(pessoa, '$.dtNascimento', '1991-02-02') WHERE JSON_EXTRACT(pessoa, '$.nome') = 'Beltrana';

 

json_select_dtnascimento

Agora que temos um data em nosso documento, também podemos realizar pesquisas por datas, pois o MySQL indexa as datas nos formatos DATE, DATETIME ou TIMESTAMP.

json_select_dtnascimento_2

JSON_ARRAY_APPEND

Adiciona um novo elemento em um array.

json_select_extract_3

UPDATE pessoas SET pessoa = JSON_ARRAY_APPEND(pessoa, '$.areas_interesse', 'J') WHERE id = 4;

json_select_extract_4

JSON_REMOVE

Remove uma chave ou um elemento de um array.

Removendo uma chave

json_select_extract_5
UPDATE pessoas SET pessoa = JSON_REMOVE(pessoa, '$.dtNascimento') WHERE id = 1;

json_select_extract_6

 

Removendo um elemento de um array

Para remover um elemento de um array é necessário informar a posição do elemento, essa posição é o índice interno do array. O primeiro elemento sempre é o índice 0(zero) e assim por diante, sempre incrementando 1.
Para facilitar essa remoção eu utilizei outra função que já falei nesse artigo, que é a função JSON_SEARCH.

json_select_extract_7

Vamos remover o elemento “E”.

 

UPDATE pessoas
SET pessoa = JSON_REMOVE(pessoa, JSON_UNQUOTE(JSON_SEARCH(pessoa, 'one', 'E', NULL, '$.areas_interesse')))
WHERE id = 4;

Veja que também foi necessário utilizar a função JSON_UNQUOTE para remover as aspas, assim fazendo a função JSON_REMOVE remover o elemento corretamente.

json_select_extract_8

 

Eu busquei fazer os exemplos para atender a uma necessidade, mas além das funções que mostrei aqui existe outras, veja mais em https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.htmlhttps://dev.mysql.com/doc/refman/5.7/en/json.html

Espero que esse artigo ajude vocês, e em breve irei fazer outro falando da performance e dicas para melhores práticas.

Gostou? compartilha esse artigo ai :)
Curta a página da PerformanceDB no Facebook e receba sempre em primeira mão os novos artigos.

federated_select_performancedb

Muitas vezes temos a necessidade de realizar consultas ou até mesmo JOINs com tabelas que estão em outro servidor MySQL/MariaDB. E no nosso cenário não tenos um MySQL Cluster, nem um MySQL Galera Cluster, nem sequer uma replicação, seja isso por qualquer motivo(projetos diferentes, empresas diferentes, etc…).
Para isso o MySQL tem(e faz muito tempo) uma engine específica, a engine FEDERATED. Sim ela permite realizar uma conexão com outro servidor MySQL/MariaDB e executar uma consulta em uma tabela. Mas tenha em mente que a performance vai depender de vários itens, como a velocidade da comunicação pela internet/rede local, quantidade de dados e itens relacionados a performance em geral(estrutura da tabela, índices, engine…).

No exemplo estou usando o MySQL 5.6.28, mas esse mesmo exemplo vai funcionar para o MySQL 5.5 e 5.7, no MySQL 5.1 e 5.0 não testei, se não for isso é muito parecido.

 

Para esse teste, estou usando 2 servidores que vou chamar de mysql56_1 e mysql56_2. O Objetivo é realizar um query que tenha um JOIN com uma tabela que está no mysql56_1 e outra no mysql56_2.

No servidor mysql56_1, tenho o database cidades, com a tabela cidades. Nessa tabela eu tenho o UF e o nome da cidade.
federated_cidades

No servidor mysql56_2, tenho o database estados, com a tabela uf. Nessa tabela eu tenho o UF e o nome do estado.
federated_estados

O objetivo é fazer um select no servidor mysql56_1 e mostrar o nome da cidade e o nome do estado.

Habilitando a engine

A engine FEDERATED não vem habilitada por padrão no MySQL, e a primeira coisa a ser feita é habilitar.

show_engines_sem

A engine já vem instalada, basta habilitar e isso é bem simples, vá até o seu my.cnf(normalmente em /etc/my.cnf) e adicionar a palavra federated dentro da sessão do mysqld, salve e reinicie o serviço do MySQL.

[mysqld]
federated

deferated_show_engines_com

Essa engine só precisa estar habilitada em um dos servidores. Apenas no servidor que a tabela será criada.
No meu exemplo, eu habilitei no mysql56_1.

Criando a tabela

Temos duas opções para criar a tabela FEDERATED, podemos criar um SERVER CONNECTION ou CONNETION.  A diferença é que quando criamos um SERVER CONNECTION, podemos reutilizar a mesma conexão para outras tabelas e a CONNECTION todos os dados da conexão devem ser declarados no comando de CREATE TABLE.

CREATE SERVER

Esse é o modo que eu gosto de utilizar.
A primeira coisa que precisamos fazer é criar um servidor(Não um servidor físico, e sim uma conexão com outro servidor).
Vou criar a conexão no mysql56_1 se conectando no mysql56_2.

CREATE SERVER conn_mysql56_2_estados
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'usr_estados', HOST '192.168.0.21', PASSWORD '123', DATABASE 'estados');

Onde:

conn_mysql56_2_estados: é o nome da conexão, ele será utilizado no momento de criar a tabela FEDERATED.
mysql: Em FOREIGN DATA WRAPPER mysql significa que é uma conexão com outro servidor MySQL, esse valor não deve ser alterado, pois até o momento é a única disponível.
usr_estados: é o nome do usuário no servidor mysql56_2.
192.168.0.21: é o IP do mysql56_2, também pode ser utilizado DNS.
123: é a senha so usuário usr_estados.
estados: é o nome do banco de dados que eu estou me conectando no servidor mysql56_2.

Agora podemos criar a tabela.

CREATE TABLE mysql56_2_uf (
 uf char(2) NOT NULL,
 nome varchar(50) DEFAULT NULL,
 PRIMARY KEY (`uf`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='conn_mysql56_2_estados/uf';

Onde:

FEDERATED: é a engine da nossa “tabela remota”.
conn_mysql56_2_estados: é o nome da conxão criada no comando CREATE SERVER.
uf: é o nome da tabela alvo, ou seja, a tabela no servidor remoto que vamos fazer o link com a tabela mysql56_2_uf.

Atnção! Veja que no momento em que criei a tabela mysql56_2_uf eu criei com a mesma estrutura que da tabela uf do servidor mysql56_2, mas isso não é uma regra, a tabela pode ser criada apenas com as colunas necessárias.

CONNECTION

A outra opção é não criar uma conexão com um servidor, mas sim declarar toda a string de conexão no momento da criação da tabela.

CREATE TABLE mysql56_2_uf (
 uf char(2) NOT NULL,
 nome varchar(50) DEFAULT NULL,
 PRIMARY KEY (`uf`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://usr_estados:123@192.168.0.21/estados/uf';

Onde:

FEDERATED: é a engine da nossa “tabela remota”.
mysql://: é o FOREIGN DATA WRAPPER esse valor não deve ser alterado, pois até o momento é a única disponível.
usr_estados: é o nome do usuário no servidor mysql56_2.
123: é a senha so usuário usr_estados.
192.168.0.21: é o IP do mysql56_2, também pode ser utilizado DNS.
estados: é o nome do banco de dados que eu estou me conectando no servidor mysql56_2.
uf: é o nome da tabela alvo, ou seja, a tabela no servidor remoto que vamos fazer o link com a tabela mysql56_2_uf.

Atnção! Veja que no momento em que criei a tabela mysql56_2_uf eu criei com a mesma estrutura que da tabela uf do servidor mysql56_2, mas isso não é uma regra, a tabela pode ser criada apenas com as colunas necessárias.

Utilizando a tabela FEDERATED

Agora bata utilizar como se fosse uma tabela qualquer do seu servidor.

federated_select_in_federated_table

Vamos ao JOIN!

federated_select_in_federated_table_join

 

Espero que esse tutorial ajude muito vocês!

Gostou? compartilha esse artigo ai :)