insertar - querying json in mysql

Almacenamiento de datos en MySQL como JSON

Question

Pensé que esto era algo nuevo que hacer. Y, entonces, nunca lo hice. Luego vi que FriendFeed hizo esto y, de hecho, mejoró su escala de DB y disminuyó la latencia. Tengo curiosidad si debería hacer esto. Y, si es así, ¿cuál es la forma correcta de hacerlo?

Básicamente, ¿cuál es un buen lugar para aprender a almacenar todo en MySQL como un tipo de DB CouchDB? Almacenar todo, ya que JSON parece ser más fácil y rápido (no construir, menos latencia).

Además, ¿es fácil editar, eliminar, etc. cosas almacenadas como JSON en la base de datos?

Answer 1

Aquí hay una función que guardará / actualizará las claves de una matriz JSON en una columna y otra función que recuperará los valores JSON. Estas funciones se crean suponiendo que el nombre de la columna de almacenamiento de la matriz JSON es json . Está usando PDO .

Función Guardar / Actualizar

function save($uid, $key, $val){
global $dbh; // The PDO object
$sql = $dbh->prepare("SELECT `json` FROM users WHERE `id`=?");
$sql->execute(array($uid));
$data = $sql->fetch();
$arr = json_decode($data[''json''],true);
$arr[$key] = $val; // Update the value
$sql=$dbh->prepare("UPDATE `users` SET `json`=? WHERE `id`=?");
$sql->execute(array(
json_encode($arr),
$uid
));
}

donde $ uid es la identificación del usuario, tecla $ - la clave JSON para actualizar y su valor se menciona como $ val .

Obtener la función de valor

function get($uid, $key){
global $dbh;
$sql = $dbh->prepare("SELECT `json` FROM `users` WHERE `id`=?");
$sql->execute(array($uid));
$data = $sql->fetch();
$arr = json_decode($data[''json''], true);
return $arr[$key];
}

donde $ key es una clave de matriz JSON de la que necesitamos el valor.

Answer 2

CouchDB y MySQL son dos bestias muy diferentes. JSON es la forma nativa de almacenar cosas en CouchDB. En MySQL, lo mejor que puedes hacer es almacenar datos JSON como texto en un solo campo. Esto sería totalmente contrario al propósito de almacenarlo en un RDBMS y complicaría en gran medida cada transacción de base de datos.

No lo hagas

Habiendo dicho eso, FriendFeed parecía usar un esquema extremadamente personalizado sobre MySQL. Realmente depende de qué exactamente desee almacenar, apenas hay una respuesta definitiva sobre cómo abusar de un sistema de base de datos por lo que tiene sentido para usted. Dado que el artículo es muy antiguo y su principal razón contra Mongo y Couch era la inmadurez, reevaluaría estos dos si MySQL no lo hace por usted. Deberían haber crecido mucho ahora.

Answer 3

Creo que el almacenamiento de JSON en una base de datos mysql de hecho frustra el propósito de usar RDBMS como se pretende. No lo usaría en ningún dato que pudiera ser manipulado en algún momento o en el que se informara, ya que no solo agrega complejidad sino que también podría afectar fácilmente el rendimiento según cómo se use.

Sin embargo, tenía curiosidad si alguien más pensó en una posible razón para hacer esto. Estaba pensando hacer una excepción para fines de registro. En mi caso, quiero registrar las solicitudes que tienen una cantidad variable de parámetros y errores. En esta situación, quiero usar tablas para el tipo de solicitudes y las solicitudes en sí mismas con una cadena JSON de diferentes valores que se obtuvieron.

En la situación anterior, las solicitudes se registran y nunca se manipulan o indizan dentro del campo de cadena JSON. SIN EMBARGO, en un entorno más complejo, probablemente intente usar algo que tenga más intención para este tipo de datos y almacenarlo con ese sistema. Como han dicho otros, realmente depende de lo que intenta lograr, pero seguir los estándares siempre ayuda a la longevidad y la confiabilidad.

Answer 4

El soporte inicial para almacenar JSON en MySQL se ha agregado a la versión de laboratorio MySQL 5.7.7 JSON ( binarios de Linux , source ). El lanzamiento parece haber crecido a partir de una serie de funciones definidas por el usuario relacionadas con JSON que se hicieron públicas en 2013 .

Este soporte nativo de JSON parece ir en una dirección muy positiva, incluida la validación de JSON en INSERT, un formato de almacenamiento binario optimizado que incluye una tabla de búsqueda en el preámbulo que permite a la función JSN_EXTRACT realizar búsquedas binarias en lugar de analizar en cada acceso. También hay una gran cantidad de nuevas funciones para manejar y consultar tipos de datos JSON específicos:

CREATE TABLE users (id INT, preferences JSON);
INSERT INTO users VALUES (1, JSN_OBJECT(''showSideBar'', true, ''fontSize'', 12));
SELECT JSN_EXTRACT(preferences, ''$.showSideBar'') from users;
+--------------------------------------------------+
| id | JSN_EXTRACT(preferences, ''$.showSideBar'') |
+--------------------------------------------------+
| 1 | true |
+--------------------------------------------------+

En mi humilde opinión, el anterior es un gran caso de uso para esta nueva funcionalidad; muchas bases de datos SQL ya tienen una tabla de usuarios y, en lugar de hacer cambios de esquema infinitos para acomodar un conjunto cambiante de preferencias de usuario, tener una sola columna JSON es perfecto. Especialmente porque es poco probable que alguna vez necesite ser consultado por artículos individuales.

Si bien aún es temprano, el equipo de servidores de MySQL está haciendo un gran trabajo al comunicar los cambios on the blog .

Answer 5

Esta es una vieja pregunta, pero todavía puedo ver esto en la parte superior del resultado de búsqueda de Google, así que creo que sería significativo agregar una nueva respuesta 4 años después de que se hace la pregunta.

En primer lugar, existe un mejor soporte para almacenar JSON en RDBMS. Puede considerar cambiar a PostgreSQL (aunque MySQL ha sido compatible con JSON desde la v5.7.7). PostgreSQL usa comandos SQL muy similares a MySQL, excepto que admiten más funciones. Una de las funciones que agregaron es que proporcionan el tipo de datos JSON y ahora puede consultar el JSON almacenado. ( Algunas referencias sobre esto ) Si no está inventando la consulta directamente en su programa, por ejemplo, usando PDO en php o elocuente en Laravel, todo lo que necesita hacer es instalar PostgreSQL en su servidor y cambiar la configuración de conexión de la base de datos. Ni siquiera necesitas cambiar tu código.

La mayoría de las veces, como sugieren las otras respuestas, almacenar datos como JSON directamente en RDBMS no es una buena idea. Sin embargo, hay algunas excepciones. Una situación en la que puedo pensar es un campo con número variable de entradas enlazadas.

Por ejemplo, para almacenar la etiqueta de una publicación de blog, normalmente deberá tener una tabla para publicar en el blog, una tabla de etiquetas y una tabla que coincida. Entonces, cuando el usuario quiera editar una publicación y necesite mostrar qué etiqueta está relacionada con esa publicación, necesitará consultar 3 tablas. Esto dañará mucho el rendimiento si la tabla / tabla de etiquetas que coinciden es larga.

Al almacenar las etiquetas como JSON en la tabla de publicaciones del blog, la misma acción solo requiere una búsqueda en una sola tabla. El usuario podrá ver que la publicación del blog se edite más rápido, pero esto dañará el rendimiento si desea hacer un informe sobre qué publicación está vinculada a una etiqueta, o tal vez buscar por etiqueta.

También puede intentar desnormalizar la base de datos. Al duplicar los datos y almacenar los datos de ambas maneras, puede recibir los beneficios de ambos métodos. Solo necesitará un poco más de tiempo para almacenar sus datos y más espacio de almacenamiento (que es barato en comparación con el costo de más potencia de computación)

Answer 6

JSON también es un tipo de datos válido en la base de datos PostgreSQL. Sin embargo, la base de datos MySQL aún no ha sido oficialmente compatible con JSON. Pero está horneando: http://mysqlserverteam.com/json-labs-release-native-json-data-type-and-binary-format/

También estoy de acuerdo en que hay muchos casos válidos de que algunos datos se serializan mejor en una cadena en una base de datos. La razón principal podría ser cuando no se consulta con regularidad, y cuando su propio esquema puede cambiar, no quiere cambiar el esquema de la base de datos correspondiente a eso. La segunda razón es cuando la cadena serializada proviene directamente de fuentes externas, es posible que no desee analizarlas todas y alimentarlas en la base de datos a cualquier costo hasta que las use. Así que estaré esperando que la nueva versión de MySQL sea compatible con JSON, ya que entonces será más fácil cambiar entre diferentes bases de datos.

Answer 7

Los personajes json no son nada especial cuando se trata de almacenamiento, caracteres como

{ , } , [ , ] , '' , az , 0-9 .... no son nada especial y se pueden almacenar como texto.

el primer problema que vas a tener es este

{id_perfil: 22, nombre de usuario: ''Robert'', contraseña: ''skhgeeht893htgn34ythg9er''}

eso almacenado en una base de datos no es tan simple de actualizar a menos que usted tenga su propio procedimiento y haya desarrollado un jsondecode para mysql

UPDATE users SET JSON(user_data,''username'') = ''New User'';

Entonces, como no puedes hacer eso, primero debes SELECCIONAR el JSON, decodificarlo, cambiarlo, actualizarlo, ¡así que en teoría podrías pasar más tiempo construyendo una estructura de base de datos adecuada!

Utilizo json para almacenar datos, pero solo metadatos, datos que no se actualizan a menudo, no relacionados con el usuario específico ... ejemplo si un usuario agrega una publicación, y en esa publicación agrega imágenes, analiza las imágenes y crea pulgares y luego usa las URL de pulgar en un formato json.

Answer 8

Me parece que todos los que responden a esta pregunta están perdiendo el único problema crítico, excepto @deceze, usan la herramienta adecuada para el trabajo . Puede forzar una base de datos relacional para almacenar casi cualquier tipo de datos y puede obligar a Mongo a manejar datos relacionales, pero ¿a qué costo? Terminas introduciendo complejidad en todos los niveles de desarrollo y mantenimiento, desde el diseño del esquema hasta el código de la aplicación; por no mencionar el golpe de rendimiento.

En 2014, tenemos acceso a muchos servidores de bases de datos que manejan tipos de datos específicos excepcionalmente bien.

• Mongo (almacenamiento de documentos)
• Redis (almacenamiento de datos clave-valor)
• MySQL / Maria / PostgreSQL / Oracle / etc (datos relacionales)
• CouchDB (JSON)

Estoy seguro de que eché de menos a otros, como RabbirMQ y Cassandra. Mi punto es que use la herramienta adecuada para los datos que necesita almacenar.

Si su aplicación requiere almacenamiento y recuperación de una variedad de datos realmente, muy rápido, (y quién no) no evite usar múltiples fuentes de datos para una aplicación. Los marcos web más populares brindan soporte para múltiples fuentes de datos (Rails, Django, Grails, Cake, Zend, etc.). Esta estrategia limita la complejidad a un área específica de la aplicación, el ORM o la interfaz de fuente de datos de la aplicación.

Answer 9

MySQL 5.7 Ahora es compatible con un tipo de datos JSON nativo similar a MongoDB y otros almacenes de datos de documentos sin esquema:

Soporte JSON

A partir de MySQL 5.7.8, MySQL admite un tipo JSON nativo. Los valores JSON no se almacenan como cadenas, sino que usan un formato binario interno que permite un acceso de lectura rápido a los elementos del documento. Los documentos JSON almacenados en columnas JSON se validan automáticamente cada vez que se insertan o actualizan, y un documento no válido genera un error. Los documentos JSON se normalizan en la creación y se pueden comparar usando la mayoría de los operadores de comparación, como =, <, <=,>,> =, <>,! = Y <=>; para obtener información sobre los operadores admitidos, así como sobre la precedencia y otras reglas que MySQL sigue al comparar valores JSON, vea Comparación y pedido de valores JSON.

MySQL 5.7.8 también presenta una serie de funciones para trabajar con valores JSON. Estas funciones incluyen las que se enumeran a continuación:

1. Funciones que crean valores JSON: JSON_ARRAY (), JSON_MERGE () y JSON_OBJECT (). Consulte la Sección 12.16.2, "Funciones que crean valores JSON".
2. Funciones que buscan valores JSON: JSON_CONTAINS (), JSON_CONTAINS_PATH (), JSON_EXTRACT (), JSON_KEYS () y JSON_SEARCH (). Consulte la Sección 12.16.3, "Funciones que buscan valores JSON".
3. Funciones que modifican valores JSON: JSON_APPEND (), JSON_ARRAY_APPEND (), JSON_ARRAY_INSERT (), JSON_INSERT (), JSON_QUOTE (), JSON_REMOVE (), JSON_REPLACE (), JSON_SET () y JSON_UNQUOTE (). Consulte la Sección 12.16.4, "Funciones que modifican los valores JSON".
4. Funciones que proporcionan información sobre los valores JSON: JSON_DEPTH (), JSON_LENGTH (), JSON_TYPE () y JSON_VALID (). Consulte la Sección 12.16.5, "Funciones que devuelven atributos de valor JSON".

En MySQL 5.7.9 y posterior, puede usar column-> path como abreviación de JSON_EXTRACT (columna, ruta). Esto funciona como un alias para una columna dondequiera que pueda aparecer un identificador de columna en una instrucción SQL, incluidas las cláusulas WHERE, ORDER BY y GROUP BY. Esto incluye SELECT, UPDATE, DELETE, CREATE TABLE y otras declaraciones SQL. El lado izquierdo debe ser un identificador de columna JSON (y no un alias). El lado derecho es una expresión de ruta JSON citada que se evalúa con el documento JSON devuelto como valor de columna.

Consulte la Sección 12.16.3, "Funciones que buscan valores JSON", para obtener más información sobre -> y JSON_EXTRACT (). Para obtener información acerca del soporte de ruta JSON en MySQL 5.7, consulte Búsqueda y modificación de valores JSON. Consulte también Índices secundarios y Columnas generadas virtuales.

Más información:

https://dev.mysql.com/doc/refman/5.7/en/json.html

Answer 10

Para ilustrar lo difícil que es obtener datos JSON usando una consulta, compartiré la consulta que hice para manejar esto.

No tiene en cuenta matrices u otros objetos, solo tipos de datos básicos. Debería cambiar las 4 instancias de columna por el nombre de columna que almacena el JSON, y cambiar las 4 instancias de myfield al campo JSON al que desea acceder.

SELECT
SUBSTRING(
REPLACE(REPLACE(REPLACE(column, ''{'', ''''), ''}'', '',''), ''"'', ''''),
LOCATE(
CONCAT(''myfield'', '':''),
REPLACE(REPLACE(REPLACE(column, ''{'', ''''), ''}'', '',''), ''"'', '''')
) + CHAR_LENGTH(CONCAT(''myfield'', '':'')),
LOCATE(
'','',
SUBSTRING(
REPLACE(REPLACE(REPLACE(column, ''{'', ''''), ''}'', '',''), ''"'', ''''),
LOCATE(
CONCAT(''myfield'', '':''),
REPLACE(REPLACE(REPLACE(column, ''{'', ''''), ''}'', '',''), ''"'', '''')
) + CHAR_LENGTH(CONCAT(''myfield'', '':''))
)
) - 1
)
AS myfield
FROM mytable WHERE id = ''3435''

Answer 11

Parece que todo el mundo comenta al respecto desde el ángulo equivocado, está bien almacenar código JSON mediante PHP en una base de datos relacional y, de hecho, será más rápido cargar y visualizar datos complejos como este, sin embargo, tendrá consideraciones de diseño tales como búsqueda, indexación, etc.

La mejor manera de hacerlo es usar datos híbridos, por ejemplo, si necesita buscar en función de la fecha, MySQL (rendimiento ajustado) va a ser mucho más rápido que PHP y, por ejemplo, buscar la distancia de lugares MySQL también debe ser mucho más rápido (aviso de que la búsqueda no tiene acceso). Los datos en los que no necesita buscar pueden almacenarse en JSON, BLOB o cualquier otro formato que realmente considere necesario.

Los datos a los que debe acceder se almacenan fácilmente como JSON, por ejemplo, un sistema básico de facturación por caja. No se benefician mucho de RDBMS, y podrían almacenarse en JSON con json_encoding ($ _ POST [''entires'']) si tiene la estructura de formulario HTML correcta.

Me alegro de que esté satisfecho con MongoDB y espero que siga sirviéndole bien, pero no piense que MySQL siempre estará fuera de su radar, a medida que su aplicación aumenta en complejidad, es posible que necesite un RDBMS para algunas funcionalidades y características (incluso si solo es para retirar datos archivados o informes comerciales)

Answer 12

Puede usar esta esencia: https://gist.github.com/AminaG/33d90cb99c26298c48f670b8ffac39c3

Después de instalarlo en el servidor (solo necesita el privilegio de root no super), puede hacer algo como esto:

select extract_json_value(''{"a":["a","2"]}'',''(/a)'')

Devolverá a 2 Puedes devolver cualquier cosa dentro de JSON usando esto. Lo bueno es que es compatible con MySQL 5.1.5.2.5.6. Y no necesita instalar ningún binario en el servidor.

Basado en el common-schema proyecto antiguo, pero todavía funciona hoy https://code.google.com/archive/p/common-schema/

Answer 13

Realmente depende de tu caso de uso. Si está almacenando información que no tiene absolutamente ningún valor en los informes, y no se consultará a través de JOIN con otras tablas, puede tener sentido que almacene sus datos en un solo campo de texto, codificado como JSON.

Esto podría simplificar enormemente su modelo de datos. Sin embargo, como menciona RobertPitt, no espere poder combinar estos datos con otros datos que se hayan normalizado.

Answer 14

Sé que esto es muy tarde, pero tuve una situación similar en la que usé un enfoque híbrido para mantener los estándares RDBMS de normalizar tablas hasta un punto y luego almacenar datos en JSON como valor de texto más allá de ese punto. Entonces, por ejemplo, almaceno datos en 4 tablas siguiendo las reglas de normalización de RDBMS. Sin embargo, en la 4ta tabla para acomodar el esquema dinámico, almaceno datos en formato JSON. Cada vez que quiero recuperar datos recupero los datos JSON, los analizo y los visualizo en Java. Esto me ha funcionado hasta el momento y para asegurarme de que todavía puedo indexar los campos que transformo en datos JSON en la tabla de forma normalizada utilizando un ETL. Esto asegura que mientras el usuario está trabajando en la aplicación, se enfrenta a un retraso mínimo y los campos se transforman en un formato RDBMS amigable para análisis de datos, etc. Veo que este enfoque funciona bien y creo que dado MYSQL (5.7+) también permite el análisis de JSON este enfoque le brinda los beneficios de ambas bases de datos RDBMS y NOSQL.

Answer 15

Utilizo json para registrar algo para un proyecto, de hecho utilizo tres tablas. uno para los datos en json, uno para el índice de cada metadato de la estructura json (cada meta está codificado por una identificación única) y uno para el usuario de la sesión, eso es todo. El punto de referencia no se puede cuantificar en este estado inicial de código, pero por ejemplo, yo era usuario (unión interna con índice) para obtener una categoría (o cualquier cosa, como usuario, ...), y era muy lento (muy, muy lento) , la vista usada en mysql no es la buena manera). El módulo de búsqueda, en esta estructura, puede hacer lo que yo quiera, pero creo que mongodb será más eficiente en este concepto de registro de datos completo. Para mi ejemplo, las vistas de usuario para crear árbol de categoría, y la ruta de navegación, ¡Dios mío! ¡Tantas consultas para hacer! Apache se ha ido! y, de hecho, para este pequeño sitio web, uso know a php que genera tree y breadcrumb, la extracción de los datos es realizada por el módulo de búsqueda (que usa solo index), la tabla de datos se usa solo para la actualización. Si lo deseo, puedo destruir todos los índices y regenerarlos con cada dato, y hacer el trabajo inverso para destruir todos los datos (json) y regenerarlos solo con la tabla de índice. Mi proyecto es joven, se ejecuta bajo php y mysql, pero alguna vez creo que usar el nodo js y mongodb será más eficiente para este proyecto.

Use json si cree que puede hacerlo, solo para hacerlo, ¡porque puede! y, olvídalo si fue un error; intenta por hacer una buena o mala elección, ¡pero inténtalo!

Bajo

un usuario francés

Answer 16

Yo diría que las únicas dos razones para considerar esto son:

• el rendimiento simplemente no es lo suficientemente bueno con un enfoque normalizado
• no puede modelar fácilmente sus datos particularmente fluidos / flexibles / cambiantes

Escribí un poco sobre mi propio enfoque aquí:

¿Qué problemas de escalabilidad encontró con un almacén de datos NoSQL?

(ver la respuesta superior)

Incluso JSON no fue lo suficientemente rápido, por lo que utilizamos un enfoque de formato de texto personalizado. Funcionó / ​​continúa trabajando bien para nosotros.

¿Hay alguna razón por la que no estés usando algo como MongoDB? (podría ser MySQL es "requerido"; simplemente curioso)