How do I create a field with a vector type in MySQL?

Question

I created a table in MySQL. I want to store a name, a surname and a vector of doubles, but the problem is: how do I create a vector column in MySQL? My vector contains 130 elements.

Answer 1

There are essentially two ways you can do that.

A simple one is to create a LONGBLOB or LONGTEXT field where you will store a serialized version of your vector.

But this is a quite ugly solution from a database modeling perspective because the DBMS is not capable of performing searches or to index the content of those vectors.

The correct way would be to use two tables in a 1-to-many relationship.

It means, you would have a table table_A with the following structure:

CREATE TABLE table_A ( -- records
    id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name      TEXT,
    surname   TEXT,
    PRIMARY KEY (id)
);

And a table table_B containing the values in the vector and an association with their respective records in table_A:

CREATE TABLE table_B ( -- vector values
    parent    INT UNSIGNED NOT NULL,
    id        INT UNSIGNED NOT NULL, -- for indexing, in case the order of the vector elements matter
    value     TEXT,
    PRIMARY KEY (parent, id),
    FOREIGN KEY (parent) REFERENCES table_A (id) ON DELETE CASCADE ON UPDATE CASCADE
);

Working exemple: http://sqlfiddle.com/#!2/79521/2

With this format you are capable of allowing the DBMS to perform searches and manage the values of the vectors.

Answer 2

I suggest you to take a look at the JSON data type. This way you can store your vector in a more efficient way than text or varchar, and you can access your data directly form MySQL without having to parse the whole thing.

Take a look at this link : https://dev.mysql.com/doc/refman/5.7/en/json.html

You'll just have to store your vector in a JSON form like this [24, 12, 54, 39, ...]