Dynamic Columns in MariaDB 10

You are viewing an old version of this article. View the current version here.
MariaDB starting with 10.0

In MariaDB 10 we are going to get folowing improvements with dynamic columns.

Name support

Now everywhere you used numbers to refer to columns it is possible to use strings:

  • creating a dynamic column record:
COLUMN_CREATE("int", -1212 as int, "double", 1.23444e50 as double, "string", 'gdgd\\dhdjh"dhdhd' as char);
  • altering a dynamic column record (remove column with name "int" and add one with name "intint"):
COLUMN_ADD(dynamiccolumns, "int", NULL, "intint", 12);
  • when changing the format (from numeric to a column with names):
COLUMN_ADD(COLUMN_CREATE(1,"string"), "1", NULL, "int", 12);
  • checking whether a column exists
COLUMN_EXISTS(dynamic_column, "column1");
  • getting a column value:
COLUMN_GET(dynstr, "column1" as char(10));

Changes in behavior

  • Column list output now includes quoting:
select column_list(column_create(1, 22, 2, 23));
+------------------------------------------+
| column_list(column_create(1, 22, 2, 23)) |
+------------------------------------------+
| `1`,`2`                                  |
+------------------------------------------+
select column_list(column_create('column1', 22, 'column2', 23)); 
+----------------------------------------------------------+
| column_list(column_create('column1', 22, 'column2', 23)) |
+----------------------------------------------------------+
| `column1`,`column2`                                      |
+----------------------------------------------------------+
  • Column name interpretation has been changed so that the string now is not converted to a number. So some "magic" tricks will not work any more, for example, "1test" and "1" now become different column names:
select column_list(column_add(column_create('1a', 22), '1b', 23));
+------------------------------------------------------------+
| column_list(column_add(column_create('1a', 22), '1b', 23)) |
+------------------------------------------------------------+
| `1a`,`1b`                                                  |
+------------------------------------------------------------+
  • Old behavior:
select column_list(column_add(column_create('1a', 22), '1b', 23));
+------------------------------------------------------------+
| column_list(column_add(column_create('1a', 22), '1b', 23)) |
+------------------------------------------------------------+
| 1                                                          |
+------------------------------------------------------------+

New functions

The following new functions have been added to dynamic columns in MariaDB 10

COLUMN_CHECK

This function is used to check a column's integrity. When it encounters an error it does not return illegal format errors but returns false instead. It also checks integrity more thoroughly and finds errors in the dynamic column internal structures which might not be found by other functions.

select column_check(column_create('column1', 22));
+--------------------------------------------+
| column_check(column_create('column1', 22)) |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+
select column_check('abracadabra');
+-----------------------------+
| column_check('abracadabra') |
+-----------------------------+
|                           0 |
+-----------------------------+

COLUMN_JSON

This function converts all dynamic column record content to a JSON array of objects which are pairs NAME:VALUE.

select column_json(column_create('column1', 1, 'column2', "two"));
+------------------------------------------------------------+
| column_json(column_create('column1', 1, 'column2', "two")) |
+------------------------------------------------------------+
| [{"column1":1},{"column2":"two"}]                          |
+------------------------------------------------------------+

Interface with Cassandra

Some internal changes were added to dynamic columns to allow them to serve as an interface to Apache Cassandra dynamic columns. The Cassandra engine may pack all columns which were not mentioned in the MariaDB interface table definition and even bring chages in the dynamic column contents back to the cassandra columns family (the table analog in cassandra).

See Also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.