Search code examples
mysqlinnodbmariadbdynamic-tables

MySQL dynamic columns - column_create does not exist

MariaDB 5.3 introduced dynamic columns. From my understanding the next version of mysql should have similar features mariadb has?

I am currently running mysql 5.5.9 and I wanted to mess around with dynamic columns per row.

So I read up on the mysql website, in order to get this working:

innodb_file_format should be set to Barracuda.

Done.

--------------
show variables like "%innodb_file%"
--------------

+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+
4 rows in set (0.00 sec)

I then go ahead and create my table for testing

 CREATE TABLE `test` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `dyn` blob,
 PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

I then try to insert 

 insert into test(`dyn`) VALUES (COLUMN_CREATE(1, "black", 3, "Linux"))

I get the following error:

 FUNCTION db.COLUMN_CREATE does not exist

So my question is does mysql not offer these functions? Should I switch to mariadb for testing?

Solution

  • Based on the documentation, it seems that MySQL does not offer this feature.

    The dynamic row format actually offers a different method for storing rows that may be more efficient in some cases, not a way to have a variable table schema:

    When innodb_file_format is set to Barracuda and a table is created with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, long column values are stored fully off-page, and the clustered index record contains only a 20-byte pointer to the overflow page.

    If you need such a flexible schema, check out the entity-attribute-value pattern.