MariaDB Dynamic Columns

MariaDB has a feature called Dynamic Columns which is not in MySQL, and this feature requires some explanation. It is used for example by the Cassandra Storage Engine, which is also unique to MariaDB, and as this is a schema-less database, which means we need a way to handle the fact that one one end MariaDB has a fixed set of columns defined by the schema, and on the other end, Cassandra provides any kind of attribute that the developer feels he wants to have for a particular "row" (which is a row in MariaDB but is not called a row in Cassandra).

But ignoring the Cassandra engine for a while, let's look at what us mere mortals, brought up on mothers milk, SQL and Relational Theory under the watching eyes of E.F. Codd, can use this for, and fact is that it can be quite useful. All in all, what Dynamic Columns provide here is a means of adding non-schema data to a row in a structured way, you know where you used to emulate an array or something in a VARCHAR or use full blown Anchor Modelling for properties.

Which is not to say that Dynamic Columns is the best things since sliced bread, but it does have some good uses, so let me show you how they work. To begin with, a Dynamic Column contains structured data in the form of one or more pairs of <attribute name> = <attribute value>. In MariaDB 5.5, <attribute name> was numeric, in 10.x it is a proper name as it should have been from the start. All examples here assume you are using MariaDB 10.x.

 The attribute names are not predefined or in a schema or anything, each instance of a Dynamic Column (i.e. every Dynamic Column in every row) might have different attributes with different values and types. And yes, is typed, but typing is automatic per instance of a Dynamic Columns (i.e. every Dynamic Column in every row of the table might have different type for an for the same ). The possible types are:

• String (Binary or Normal)
• Date
• Datetime
• Decimal
• Double
• Integer (Signed or Unsigned)
• Time
• Dynamic column

 As can be seen from this, nesting is actually available, i.e. might be in the form of another Dynamic Column. So let's dig in with some simple examples.

First, lets create a table that holds some useful attributes, in this case I have a table with my users, and for each user I want to store a range of mixed attributes that the application finds useful, but which are not in the normal TABLE schema, such a birthplace, car make, etc etc.
CREATE TABLE users(userid INT NOT NULL PRIMARY KEY AUTO_INCREMENT, username VARCHAR(255), firstname VARCHAR(255), lastname VARCHAR(255), attrs BLOB);
Now we have to insert some data into this table. To create a Dynamic Column value you use the COLUMN_CREATE function which takes a even variable number of arguments, which are name value pairs. For example:
INSERT INTO users VALUES(NULL, 'bloggs', 'Joe', 'Bloggs', COLUMN_CREATE('shirtsize', 'L', 'colorpref', 'red'));
INSERT INTO users VALUES(NULL, 'doe', 'John', 'Doe', COLUMN_CREATE('shirtsize', 'L', 'vegetarian', FALSE));
Now we hopefully have some data in the table, and as you can see, we are mixing schema data with schemaless data in the same row. Before I end for this time on this subject, let's get a look at that data in the attrs column. There are a few different means of doing that, either you can get a specific Dynamic Column value, like this:
SELECT COLUMN_GET(attrs, 'colorpref' AS CHAR) FROM users;
+----------------------------------------+
| COLUMN_GET(attrs, 'colorpref' AS CHAR) |
+----------------------------------------+
| red                                    |
| NULL                                   |
+----------------------------------------+
2 rows in set (0.00 sec)
Or I can get all the attributes of a dynamic column in one go, in which case I get JSON back:
SELECT COLUMN_JSON(attrs) FROM users;
+-------------------------------------+
| COLUMN_JSON(attrs)                  |
+-------------------------------------+
| {"colorpref":"red","shirtsize":"L"} |
| {"shirtsize":"L","vegetarian":0}    |
+-------------------------------------+
2 rows in set (0.00 sec)
That's it for now, but I'll be back with some more details and some cool things you can do with MariaDB Dynamic Columns!

/Karlsson