Is it possible to use values separated by commas to insert in a single column in MySQL?

Question

Is it possible to use values separated by commas to insert in a single column in MySQL?

Navigation

#1 by (2 votes)
#2 by (2 votes)
#3 by (-1 votes)
#4 by (-1 votes)

-1

Reviewing the documentation syntax on INSERT INTO ... , which to me judgment is not entirely clear, it would seem that it is possible to insert a list of values in the same column.

{VALUES | VALUE} (value_list) [, (value_list)] ...

When reading VALUE and then next, two possibilities of value_list , it would seem that it is possible to do something like this:

INSERT INTO liturgia_biblicas (id_pericopa) VALUE (685,1281,1282);

In the query, the idea is to insert three new rows in the table, with id_pericopa equal to 685 in one row, 1281 in another row and 1282 in another.

But the query fails:

Column count doesn't match value count at row 1

And if I write it that way, it also gives an error:

INSERT INTO liturgia_biblicas (id_pericopa) VALUES (685,1281,1282);

Is it possible to insert several rows in the same column without having to use the parentheses for each row? How should I write the query if possible?

It would be useful to be able to do it this way in some contexts in which I manually receive a list of values to insert in my table manually.

mysql

asked by A. Cedano 24.07.2018 в 05:51

source

4 answers

Achieve this effect with CSS inputs how to use the variable rand in c ++ and not repeat the number? [closed]

score 2 · Answer 1

It is possible that the definition is not completely explicit at the beginning, but then in the detail it is explained quite well (in my humble opinion), specifically in the following paragraphs:

Each value list must contain exactly as many values as are   inserted per row. The following statement is invalid because it   contains one list of nine values, rather than three lists of three   values each:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3,4,5,6,7,8,9);

VALUE is a synonym for VALUES in this context. Neither implies   anything about the number of values lists, nor about the number of   values per list. Either may be used if there is a single values   list or multiple lists, and regardless of the number of values per   list.

Therefore, regarding your question:

Is it possible to insert several rows in the same column without having to use the parentheses for each row?

I consider that MySQL currently does not support such syntax.

One option that may be useful is 13.2.6.1 INSERT ... SELECT Syntax , but it will depend on your specific use case.

score 2 · Answer 2

Not directly, but you can use a variation of "GROUP UNCONCAT"

SET @sourceString = '685,1281,1282';
SET @sql = CONCAT('INSERT INTO liturgia_biblicas (id_pericopa) VALUES (\'',
  REPLACE(@sourceString, ',', '\'),(\''), '\')');
PREPARE myStmt FROM @sql;
EXECUTE myStmt;

lefidel: link

score -1 · Answer 3

As it is to make an INSERT by hand, I suggest the following:

Create your INSERT in this way:

INSERT INTO liturgia_biblicas (id_pericopa) VALUE (685,1281,1282);

and then with any editor it looks for / replaces , with ),(

Obviously it will only save you time if you insert a lot of data.

score -1 · Answer 4

If the column is numerical, forget it, in the case that it is a varchar or equivalent, you can make the entry what you want to put in quotes.

INSERT INTO liturgia_biblicas (id_pericopa) VALUES ("685,1281,1282");

In any case you could consider creating another table to relate the values, much more practical at the maintenance level