MySQL to MariaDB - Dynamic Columns?

Question

I'm trying to transfer data from old_table to table with some of the data being in dynamic columns for future imports

INSERT INTO `table` (`col_1`,`col_2`,etc....   `attributes`)
SELECT `col_1`,`col_2`,etc....     COLUMN_CREATE('name',`col_99` AS UNSIGNED,'name2',`col_100` AS CHAR)
FROM `old_table`

The only documentation on dynamic tables is basically

COLUMN_CREATE('name',1 AS UNSIGNED) 

Which is not a very "dynamic" example...

Open to other solutions but NOT JSON, prefer MariaDB though as it is essentially MySQL which I'm used to

EDIT: many thanks for the answers, but for further information I am trying to use this approach documented (badly) here: https://mariadb.com/kb/en/dynamic-columns-functions/

Edit 2: I'm wondering whether EAV might be the only solution, as I seem to be hitting a wall with MariaDB Dynamic Columns, I can't find any more documentation or guides anywhere!

Edit 3: Not having any luck still with these dynamic columns? MariaDB is really confusing!

Answer 1

For Dynamic Columns, JSON, or EAV, you leave out NULL "columns". You just include the items that are non-null.

I would recommend you abandon Dynamic Columns; they exist only that one place. Meanwhile, JSON is becoming more accepted. And even if you are using an "old" version of MariaDB or MySQL, you can put JSON text in a TEXT column.

Keep in mind that none of the eav-like techniques is very clean.

More on the problem space: http://mysql.rjweb.org/doc.php/eav

As for "more searchable" -- You probably need to make compromises:

• Commonly searched fields: make into columns with INDEXes.
• Consider FULLTEXT index with some of the text data. Just toss it all into a single text column for efficient searching.
• If you need to search too many different fields; use your application code to decode the JSON to finish the search (after you have filtered on what you can in SQL).
• That is, do not expect to do everything in SQL.