The Ultimate Guide to High Availability with MariaDB

JSON Data Type

You are viewing an old version of this article. View the current version here.
MariaDB starting with 10.2.7

The JSON alias was added in MariaDB 10.2.7. This was done to make possible to use JSON columns in statement based replication from MySQL to MariaDB and to make it possibile for MariaDB to read mysqldump's from MySQL.

JSON is an alias for LONGTEXT introduced for compatibility reasons with MySQL's JSON data type. MariaDB implements this as a LONGTEXT rather, as the JSON data type contradicts the SQL standard, and MariaDB's benchmarks indicate that performance is at least equivalent.

In order to ensure that a a valid json document is inserted, the JSON_VALID function can be used as a CHECK constraint.

Examples

CREATE TABLE t (j JSON);

DESC t;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| j     | longtext | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

With validation:

CREATE TABLE t2 (
  j JSON 
  CHECK (JSON_VALID(j))
);

INSERT INTO t2 VALUES ('invalid');
ERROR 4025 (23000): CONSTRAINT `j` failed for `test`.`t2`

INSERT INTO t2 VALUES ('{"id": 1, "name": "Monty"}');
Query OK, 1 row affected (0.13 sec)

Replicating JSON data between MySQL and MariaDB

The JSON type in MySQL stores the JSON object in a compact form, not as LONGTEXT as in MariaDB. This means that row based replication will not work for JSON types from MySQL to MariaDB.

There is a a few different ways to solve this:

  • Use statement based replication
  • Change the JSON column to type TEXT in MySQL

Converting a MySQL TABLE with JSON fields to MariaDB

MariaDB can't directly access MySQL JSON format.

There is a a few different ways to move the table to MariaDB:

  • Change the JSON column to type TEXT in MySQL. After this MariaDB can directly use the table without any need of dump and restore.
  • Use mysqldump to copy the table.

See also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.