Using the XCOL table type

In a perfect world, all data in a MariaDB database would be properly defined and normalized. We don't live in such a world, and sometimes, we have to work with tables that contain one or more catchall columns stuffed full of related values. The XCOL table type enables us to work with this data as if it was stored in a separate rather than a single column.

How to do it...

1. Launch the mysql command-line client application and connect to the test database on our MariaDB server. If the test database does not exist, create it first.

2. Run the following CREATE TABLE statement to create our example table:

   CREATE TABLE superheroes ( 
     team varchar(50), 
     heroes varchar(1024)
   ); 
   

3. Add some data to our new table:

   INSERT superheroes VALUES 
     ("The Avengers","Thor, Iron Man, Black Widow, Hawkeye, Hulk, Captain America"), 
     ("The Justice League", "Superman, Batman, Aquaman, Flash, Wonder Woman"), 
     ("The X-Men", "Storm, Cyclops, Wolverine, Rogue, Iceman");
   

4. Create an XCOL table...