MySQL Document Store: Getting Started

MySQL's new NoSQL database is quite a big change for all used to using a SQL speaking relational database. The Document Store makes use of the new MySQL Shell, the new X Protocol, and the new X Devapi to provide a NoSQL document storage service. This provides a way for developers to have a schema-less, NoSQL data store to build services and applications is a quick fashion without having to design a schema, normalize data, develop queries in SQL, or any of the rest of the overhead needed to create a relational databases. Behind the scenes and invisible to the developer is that the schema-less data is stores in a JSON type column in a MySQL instance.

Please note that I am using JavaScript Mode (instead of SQL or Python) for these examples. No special reason.

First Step

The new MySQL Shell is a separate install from the server software. Once installed it will provide the ability to use MySQL as a document store.

The db object is how we can communicate to the server and the world_x data. Now we can create a simple collection.

Whoa! Something is wrong, isn't it?

The Document Store adds an _id column automatically. You can specify your own _id but be warned it is defined by default as an UNIQUE index, so no duplicates. The InnoDB storage engine desires a index and will pick a column to index if you do not specify one (usually not a great choice in a column BTW). But JSON columns can not be directly indexed. But do not worry about the details but do note that the _id column is there.

Also notice the order of the data returned. The MySQL JSON data type organizes the data in a special binary format for faster look ups and the side effect is the alphabetizing of the keys in the key/value pairs.

Filters

Filters can be added to find specific records and to return just the desired information (default is like a SELECT *).

You can bin values to variables to separate the data from the condition.

Modify Data

If you are used to UPDATE set x=y WHERE z=n then the next part may look a little odd.

You can add more than simple key/value pairs. Here is an example of adding an array.

And of course you need the compliment to set, unset.

We can also add to or remove items for arrays within the data. The $ is short hand for the 'current document'.

And of course db.foobar.remove() would delete all the records in the collection.

Next Time: Indexes and Documents

This time we skimmed the surface of the MySQL Document Store but next time we will look at getting some extra performance by using indexes.