Dynamic Columns

Location: Ballroom G
Presentation: Dynamic Columns Presentation [ODP]
Average rating: **...
(2.33, 3 ratings)

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));
- updating
UPDATE item SET extra=COLUMN_ADD(extra, 6 /
Memory /, 2048) WHERE id=2;
- deleting
UPDATE item SET extra=COLUMN_DELETE(extra, 6) WHERE id=2;
- selecting
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.

Photo of Michael Widenius

Michael Widenius

Monty Program Ab

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.

Photo of Oleksandr Byelkin

Oleksandr Byelkin

Monty Program

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.


Picture of Oleksandr Byelkin
Oleksandr Byelkin
04/17/2011 9:46pm PDT

Changed order of parameters for column_get()

Picture of Oleksandr Byelkin
Oleksandr Byelkin
04/15/2011 6:14am PDT

It will, if you mean something else except what was mentioned in the plans, please describe it.

John Schulz
04/14/2011 10:04pm PDT

Important problem. Only a partial solution. Needs to be done much better.

  • EnterpriseDB
  • Amazon Web Services
  • Clustrix
  • Continuent
  • Facebook
  • HTI Consultoria e Tecnologia
  • Monty Program
  • Percona
  • Rackspace Hosting
  • Schooner Information Technology
  • SkySQL
  • Xeround

Sponsorship Opportunities

For information on exhibition and sponsorship opportunities at the conference, contact Yvonne Romaine at yromaine@oreilly.com

Media Partners Opportunities

For media partnerships, contact mediapartners@ oreilly.com

Press & Media

For media-related inquiries, contact Maureen Jennings at maureen@oreilly.com

O'Reilly MySQL Conference Bulletin

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).

Contact Us

View a complete list of O'Reilly MySQL Conference Contacts