What is the purpose of the JSON column in MYSQL?

Asked

Viewed 4,323 times

12

I realized that Mysql has released a new feature, which is the column can have the type JSON (which can even be saved in binary, as I was reading).

Documentation:

As of Mysql 5.7.8, Mysql Supports a Native JSON data type that Enables Efficient access to data in JSON (Javascript Object Notation) Documents.

That translating, it is:

From the MySQL 5.7.8, Mysql natively supports data type JSON which allows efficient access to data in JSON (Javascript Object Notation).

  • With this implementation, what are the acquired advantages? Is there any difference in performance or ease of recovery of these values?

  • Simply use a field of the type TEXT to save my JSON wasn’t enough?

mysql json

  • I think this tag is not related to the bank.

1 answer

14


There are many advantages to the countryside JSON in relation to the TEXT:

  • Validation - Data is automatically validated. If JSON is invalid, the record will not be inserted and the operation will produce an error
  • Efficient access - Storage format is optimized. JSON documents saved in type columns JSON are actually converted into an internal format that allows quick reading of the document elements. When the server needs to read a JSON value stored in this binary format, the value does not need to be converted from a text representation.
  • Performance - It is possible to improve the performance of the query by creating indexes within the JSON columns. This is possible through secondary indices in virtual columns.

  • Expediency - It is much easier to recover values in queries through the function JSON_EXTRACT or with the new syntax coluna->caminho:

    SELECT nome, conta->"$.saldo" FROM clientes WHERE conta->"$.atrasada" = true;

So now it’s much easier to manipulate the data and store these complex values in a column. One could say that Mysql supports a non-relational data structure (Nosql) within a relational structure. Amazing!

However, as the Article Mysql 5.7 brings sexy back with JSON, note that:

Nosql specialized databases (document-oriented databases, key-value banks and graph-based databases) remain better options for their specific use cases, but adding this type of data can allow you to reduce the complexity of your technology stack.

Browser other questions tagged mysql json

You are not signed in. Login or sign up in order to post.