Dynamic column is the way to address data in the row depending on this row content. Actually it is set of function that effectively packs and unpacks data in the row on request. This allow to a user of the database only store data it need specially for this row and describe the set/schema of the data for each row dynamically.
Example of using:
- Creating column_blob as the column for dynamic columns:
CREATE TABLE item (ID int auto_increment primary_key, Type_id int, Price decimal(7,2), Country_id int, Manufacturer_id int, extra column_blob);
- Initializing the columns:
INSERT into item (NULL, 1 /* T-shirt /, 10, 1 / * Germany */, 1 / Nike /, COLUMN_CREATE(1 /* color /, “Blue”, 2 /* Size /, “M”));
INSERT into item (NULL, 2 / computer /, 1 /* Germany /, 2 /* intel /, COLUMN_CREATE(3 / cpu /, “T9400”, 5 / MHz /, 800));
UPDATE item SET extra=COLUMN_ADD(extra, 6 / Memory /, 2048) WHERE id=2;
UPDATE item SET extra=COLUMN_DELETE(extra, 6) WHERE id=2;
SELECT id, COLUMN_GET(extra, 1 / color*/,char(255)) from item;
The feature will be useful for them who need to store fuzzy data or properties efficiently without building additional tables and LEFT JOIN. For example we collect information about real world objects (for web-shop or information system). We could assign for every object type, description, price, photo and some other common attributes, but computer has CPU type and speed, memory size and so on; T-shirt described by size and colour. Dynamic columns allow to store such attribute in one row to retrieve them fast and even have index over them in future.
Founder of MySQL AB.
Original author & architect of the MySQL server.
Founder and CEO of Monty Program Ab and creator of MariaDB. Founder of the Open Database Alliance.
Oleksandr “Sanja” Byelkin works for Monty Program Ab, on MariaDB server as Senior MariaDB developer. He lives in Lugansk, Ukraine and worked at MySQL since December of 2001. Author of query cache, first implementation of subqueries and views in MySQL.
Comments on this page are now closed.
For information on exhibition and sponsorship opportunities at the conference, contact Yvonne Romaine at firstname.lastname@example.org
For media partnerships, contact mediapartners@ oreilly.com
For media-related inquiries, contact Maureen Jennings at email@example.com
To stay abreast of conference news and to receive email notification when registration opens, please sign up for the O'Reilly MySQL Conference Bulletin (login required).
View a complete list of O'Reilly MySQL Conference Contacts