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

Kalsomine

Note that LIMIT without ORDER BY is fairly meaningless – Wellesley

Are you trying to insert new records, or update existing ones? – Lakes

Unclear. Please show us the table schema (SHOW CREATE TABLE) and the desired result of the operation. – Browbeat

is id in myTable an AUTO INCREMENT PRIMARY KEY? Your update query shows that it is not, or that you have some other field on which you sort your table by default. – Mulvey

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.

Godunov

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
);

Hydromagnetics

please explain more about the content of column col1 in table myTable and column value in the vectorTab. thx – Schuyler

Answer 3

What you should do is export your R vector as JSON using toJSON() function for example:

myJSONVector = toJSON(c("hi","I", "am"))

Also create or alter myTable so that myColumn has the appropriate JSON Data Type

Attempting to insert a value into a JSON column succeeds if the value is a valid JSON value, but fails if it is not:

Example

CREATE TABLE `myTable` (`myColumn` JSON);
INSERT INTO `myTable` VALUES(myJSONVector); // will fail if myJSONVector is not valid JSON
// update query would be
UPDATE `myTable` SET `myColumn` = myJSONVector
WHERE `id` IN (3,4,5);

In addition you can make an R vector from JSON using function fromJSON().

Mulvey