October 24, 2014

Dynamic Columns Tutorial – Part 2: Searching and Updating

This is a continuation of my previous blog, where we will focus on some more advanced features related to Dynamic Columns. For an introduction to Dynamic Columns please refer to my previous blog.

Dynamic Columns within WHERE

I started todays example by adding a few more items into my table, namely my two laptops to be able to produce more meaningful results. As with any function, dynamic column functions can generally be used in the WHERE clause of SELECT and other SQL statements which manipulate data. Let's execute another SELECT statement with COLUMN_GET() in the WHERE clause:

SELECT id, name AS 'White Items'
FROM items
WHERE COLUMN_GET(attributes, 'colour' AS CHAR) = 'white';

+----+----------------------+
| id | White Items          |
+----+----------------------+
|  3 | Samsung Galaxy S5    |
|  4 | Samsung Galaxy Pro 3 |
+----+----------------------+

Notice that we had to specify the data type within the COLUMN_GET(), the AS CHAR in this example. That lets MariaDB know what to expect and how to cast the values it finds. In this case, we instructed MySQL to cast the values as characters.

Let's try another dynamic column function, but one that's designed primarily to be used in the WHERE--although it may be used elsewhere. Let's look at the COLUMN_EXISTS() function which merely checks if an attribute exists with a given name. For example we can look for all items that have an OS:

SELECT name 
FROM items 
WHERE COLUMN_EXISTS(attributes, 'OS');
+---------------------------+
| name                      |
+---------------------------+
| Samsung Galaxy S5         |
| Dell Latitude E6510       |
| Lenovo ThinkPad Carbon X1 |
| Samsung Galaxy Pro 3      |
+---------------------------+
4 rows in set (0.00 sec)

More interestingly this function can also be used to find items that do not have a specific attribute. For example let's look at all items that don't have a defined type (if there are such items:

SELECT id, name AS 'Typeless Items'
FROM items
WHERE NOT COLUMN_EXISTS(attributes, 'type');

+----+-----------------+
| id | Typeless Items  |
+----+-----------------+
|  1 | MariaDB t-shirt |
|  2 | MariaDB t-shirt |
+----+-----------------+
2 rows in set (0.00 sec)

As you can see, that worked fine. We found two items that do not have a defined value for the type attribute in the attributes column.

Updating Dynamic Columns

So far we've used dynamic functions as part of SELECT statements to extract data or as part of INSERT statements when inserting the rows. You can also change the existing rows in a table, this can be done by using the Dynamic Column functions in UPDATE statements. If you want to add another name/value pair to a row that already contains a dynamic column, you can use the COLUMN_ADD() function.

For example, in the previous example, we determined that there is no type for the MariaDB t-shirt items. I forgot that when inserting the first rows and I didn't realise that all items would eventually have a type. But that's not a big issue, I can now add a type to these items. Let's add a type of t-shirt. Here's how that is done with an UPDATE statement:

UPDATE items
SET attributes = COLUMN_ADD(attributes,'type','t-shirt')
WHERE NOT COLUMN_EXISTS(attributes, 'type');

That's simple. Here's how those items look now:

SELECT name AS 'Item',
COLUMN_LIST(attributes) AS 'Dynamic Column Names'
FROM items WHERE name LIKE 'MariaDB%';

+-----------------+------------------------+
| Items           | Dynamic Column Names   |
+-----------------+------------------------+
| MariaDB t-shirt | `size`,`type`,`colour` |
| MariaDB t-shirt | `size`,`type`,`colour` |
+-----------------+------------------------+

Perfect, job well done. Just to verify we can run the NOT COLUMN_EXISTS WHERE clause again:

SELECT id, name AS 'Typeless Items'
FROM items
WHERE NOT COLUMN_EXISTS(attributes, 'type');

Empty set (0.00 sec)

That seemed to have worked fine. Ok, now what if we want to remove an attribute from a row in the table? We can use the COLUMN_DELETE() function with the UPDATE statement like this:

UPDATE items
SET attributes = COLUMN_DELETE(attributes,'type')
WHERE name LIKE 'MariaDB%';

SELECT name AS 'Item',
COLUMN_LIST(attributes) AS 'Dynamic Column Names'
FROM items WHERE name LIKE 'MariaDB%';

+-----------------+----------------------+
| Item            | Dynamic Column Names |
+-----------------+----------------------+
| MariaDB t-shirt | `size`,`colour`      |
| MariaDB t-shirt | `size`,`colour`      |
+-----------------+----------------------+

That removed the type attribute again from the two rows of t-shirts.

Ok, but what if you want to change a specific attribute, can you do that? The answer is yes. MariaDB does not allow you to “overload” the attribute names, so you cannot have the same attribute twice in the same row. This means that when you use the COLUMN_ADD() function in an UPDATE statement to add an attribute that already exists on a specific row you are basically overwriting the old one. In that sense COLUMN_ADD() works more like the REPLACE statement than an INSERT statement.

That is all for this time, next blog will focus on search efficiency and other more advanced features of Dynamic Columns.

About Max Mether

As a co-founder Max now manages the field services and training departments at MariaDB and helps advance the MariaDB and MySQL eco-systems around the world. Max is a frequent speaker at LinuxCon, meetups and other conferences around the globe.

Read all posts by Max Mether