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.
