8

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.

Wander Nauta
  • 18,832
  • 1
  • 45
  • 62
user3623903
  • 129
  • 1
  • 2
  • 5
  • Two main options: 1) Create a binary BLOB or 2) print your vector to text (each number separated by ";",for example) and save the column as a text string. Look at this post: [BLOB vs VARCHAR for storing arrays in a mySQL table](http://stackoverflow.com/questions/3106548/blob-vs-varchar-for-storing-arrays-in-a-mysql-table). – FoggyDay Jun 21 '14 at 22:38
  • A vector sounds basically like a table. In relational database modeling, you are probably looking for a 1-to-many relationship. – Brandon Jun 21 '14 at 22:38

2 Answers2

5

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.

Havenard
  • 27,022
  • 5
  • 36
  • 62
4

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, ...]

Herobrine
  • 1,661
  • 14
  • 12