How to insert a vector into a column of a table in mysql?

Question

In R, I have a vector, "myVector", of strings which I want to insert into a column, "myColumn", of a mysql table, "myTable". I understand I can write the sql query and run it in R using dbSendQuery. So let's figure out the sql query first. Here is an example:

myVector = c("hi","I", "am")

Let's insert myVector in the column myColumn of myTable, row numbers 3 to 5, here is the sql query which works except for the last line I have no idea:

UPDATE myTable t JOIN
       (SELECT id
        FROM myTable tt
        LIMIT 3, 3
       ) tt
       ON tt.id = t.id
    SET myColumn = myVector;

Thanks

Answer 1

Assuming that I understand your problem correctly, I have two possible solutions on my mind:

1. one column per element: if your vectors are all have equal number of elements, you could store each of them in a seperate column. Proceeding from your example above, the table could look like this. (the size of the columns and whether to allow null values or not depends on your data)

CREATE TABLE `myTable` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `element1` varchar(255) DEFAULT NULL,
  `element2` varchar(255) DEFAULT NULL,
  `element3` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The statement for inserting your vector from above would be:

INSERT INTO `myTable` (`id`, `element1`, `element2`, `element3`)
VALUES (1, 'hi', 'I', 'am');

Depending on how much elements your vectors have this approach might be more or less applicable.

2. Storing the vector as a blob: Another approach could be storing the vector as a blob. Blob (Binary Large Object) is a datatype to store a variable amount of (binary) data (see: https://dev.mysql.com/doc/refman/5.7/en/blob.html). This idea is taken from this article: http://jfaganuk.github.io/2015/01/12/storing-r-objects-in-sqlite-tables/

The table could be created using the following statement:

CREATE TABLE `myTable` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `myVector` blob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

When inserting your vector you bind the variable to your query. As I am not a R specialist I would refer to this article for the implementation details.

Answer 2

I'm not aware, if MySQL support Vector data type, but you could design your table as workaround where Vector can be store in different table and will have relation with myTable as 1-M.

This is help you to manage and retrieve details easily. So, assuming myTable is your table and it's existing design is :

myTable
-------
id
col1
vectorCol

So, you main table can be

CREATE TABLE myTable (
    id        INT NOT NULL AUTO_INCREMENT,
    col1  varchar(50),
    PRIMARY KEY (id)
);

and table which will store your vector.

CREATE TABLE vectorTab ( 
    id    INT NOT NULL AUTO_INCREMENT, -- in case ordering matter
    parent_id        INT NOT NULL, 
    value     TEXT,
    PRIMARY KEY (id),
    FOREIGN KEY (parent_id) REFERENCES myTable (id) ON DELETE CASCADE ON UPDATE CASCADE
);