MariaDB: JSON datatype supported as of 10.2

Oh Dear! monitors your entire site, not just the homepage. We crawl and search for broken pages and mixed content, send alerts when your site is down and notify you on expiring SSL certificates.

Start your free 10 day trial! »

Image of Mattias Geniar

Mattias Geniar, August 18, 2017

Follow me on Twitter as @mattiasgeniar

Let’s say you have the following CREATE TABLE statement you wanted to run on a MariaDB instance.

CREATE TABLE `test` (
  `id` int unsigned not null auto_increment primary key,
  `value` json null
) default character set utf8mb4 collate utf8mb4_unicode_ci

You might be greeted with this error message;

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL
syntax; check the manual that corresponds to your MariaDB server version for the right
syntax to use near 'json null at line 1

Syntax-wise all is good, but the JSON data type is actually pretty new, and it’s only “supported” (these parentheses will become clear in a moment) as of MariaDB 10.2.

First of all: make sure you run the latest MariaDB.

MariaDB> select @@version;
+-----------------+
| @@version       |
+-----------------+
| 10.0.31-MariaDB |
+-----------------+

If, like me, you were on an older release, upgrade that until you’re on MariaDB 10.2.

MariaDB> select @@version;
+----------------+
| @@version      |
+----------------+
| 10.2.7-MariaDB |
+----------------+

Then, be aware that MariaDB’s JSON implementation is slightly different than MySQL’s, as explained in MDEV-9144.

JSON data type directly contradicts SQL standard, that says, that JSON_* functions take a string as an argument.

Also, speed-wise MariaDB does not need binary JSON, according to our benchmarks, our JSON parser is as fast on text JSON as MySQL on binary JSON.

[…]

We’ll add JSON “type” for MySQL compatibility, though.

And as a final remark;

added JSON as an alias for TEXT

So behind the scenes, a JSON data type is actually a TEXT data type. But at least those CREATE TABLE queries will actually work.

That same CREATE TABLE statement above gets translated to this in MariaDB;

MariaDB> SHOW CREATE TABLE test;

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `value` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Note the TEXT datatype for the ‘value’ column, that I specified as JSON.

Will you help me share this post?

It would mean a lot to me if you could help share this post on social media. 🤗