Join Sheeri Cabral for an in-depth discussion in this video Adding a dynamic column part to all records, part of Understanding MariaDB for MySQL Users.
- View Offline
…Let's say we want to add a dynamic column called, in stock, to all our records.…Our first instinct is to use COLUMN_ADD.…But can we use COLUMN_ADD to add information to an empty BLOB?…Let's find out.…Remember that our CD has no extra values yet.…So let's double check that.…SELECT COLUMN_ JSON of extra FROM items.…WHERE type equals CD.…And you can see that it's NULL.…We have no values in our dynamic column yet.…
Now let's try to add the fact that we have ten CDs in…stock to the dynamic column field using the COLUMN_ADD function.…So UPDATE items, SET extra equal to COLUMN_ADD, extra, in stock, 10.…WHERE title equals My CD.…The query succeeded with no errors or warnings but it is misleading.…Look at the return row.…One row matched, 0 rows changed, 0 warnings.…Let's verify what happened by looking at the JSON again.…
In fact, there's still nothing there.…So how would we handle,…if we wanted to add a dynamic column part to more than one record.…Where some records have an empty BLOB and others already have some values.…
- Why use dynamic columns?
- Creating, updating, and querying dynamic columns
- Installing the CONNECT storage engine
- Connecting to remote data
- Connecting to CSV, INI, and XML files
- Using PIVOT tables
- Accessing database metadata
- Working with the Cassandra storage engine