Insert records from array in MySQL query

Question

I have an array of 1500000 records in it as below

Array = ["2","3","6","7","A5057",......]

How would I be able to insert all this records in a table(which has only one field(XXX_id)) directly from the MySQL command I tried with the below query

INSERT INTO TABLE_NAME (XXX_id) VALUES (["2","3","6","7","A5057",......]);

If we have to insert from php script, no doubt we can follow this question form community.

use the stored procedure,first split the array into rows and store them into temporary table .Then after insert data from temporary table to physical table — Ankit Agrawal, Oct 26 '17 at 06:03

score 2 · Answer 1 · answered Oct 26 '17 at 05:51

2

You can create array format like this

INSERT INTO `TABLE_NAME`(`XXX_id`) VALUES (1),(2),(3),(4)

answered Oct 26 '17 at 05:51

Aiyaz Khorajia

598
2
11

score 1 · Answer 2 · answered Oct 26 '17 at 05:50

1

You can use multi-row insert to achieve what you want

Just format your array as VALUES (X), (X), ... with PHP

answered Oct 26 '17 at 05:50

Plotisateur

476
9
23

I think trying to do that for 1.5 million rows will exceed the max_packet_size limit. – Barmar Oct 26 '17 at 05:53
This is MySQL server dependant from the Op (and changeable anyway) but it's still the optimized way for very large insert – Plotisateur Oct 26 '17 at 05:58

Barmar · Answer 3 · 2017-10-26T05:58:16.503

1

Put the array data into a file, with one ID per line. Then you can use LOAD DATA INFILE:

LOAD DATA INFILE 'filename'
INTO TABLE table_name (XXX_id)

edited Oct 26 '17 at 05:58

answered Oct 26 '17 at 05:50

Barmar

741,623
53
500
612

I am trying to insert the records from phpmyadmin. – Mohammed Abrar Ahmed Oct 26 '17 at 05:53
You tagged it `php`. – Barmar Oct 26 '17 at 05:54
There are no arrays in phpMyAdmin. It's a web interface, not a programming language. So now your question makes no sense. – Barmar Oct 26 '17 at 05:56
My mistake, I changed the tags. – Mohammed Abrar Ahmed Oct 26 '17 at 05:56
Yes you are correct I can't use array but can change my array to the format as @Plotisateur mentioned. – Mohammed Abrar Ahmed Oct 26 '17 at 05:59
I created a .csv file where it shows 140613 records but then when I load the values in a table in shows total records 140466. – Mohammed Abrar Ahmed Oct 26 '17 at 06:05
Maybe there was a duplicate in the file. It should have reported an error when it got there. – Barmar Oct 26 '17 at 06:11
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/157539/discussion-between-mohammed-abrar-ahmed-and-barmar). – Mohammed Abrar Ahmed Oct 26 '17 at 06:12

score 1 · Answer 4 · answered Oct 26 '17 at 06:07

Your Query should be like this:

INSERT INTO TABLE_NAME (XXX_id) VALUES (2),(3),(6),(7),(A5057),......;

But you are going to insert larege amount of rows with just one Query. So, it may exceeds to MySQL query limitation. Every query is limited by max_allowed_packet in general for MySQL.

1) Execute the following command in in MySQL to view default value for 'max_allowed_packet ':

show variables like 'max_allowed_packet';

2) Standard MySQL installation has a default value of 1048576 bytes (1MB). But this can be increased by setting the higher value to 500MB or may be more:

SET GLOBAL max_allowed_packet=524288000;

3) Check max_allowed_packet value again by following 1) command in MySQL.

Hope this helps you.

score 1 · Answer 5 · answered Oct 26 '17 at 06:33

we can use LOAD DATA LOCAL INFILE

LOAD DATA LOCAL INFILE  
'path/file.csv'
INTO TABLE giata_table 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

This will fetch all the records in a table we have to make sure that the field name in the table and the heading in the csv file matches the same.

If this didn't works for you then you can follow the @Plotisateur and @Aiyaz khorajia answer as

INSERT INTO `TABLE_NAME`(`XXX_id`) VALUES (1),(2),(3),(4)

score 1 · Accepted Answer · answered Oct 26 '17 at 08:46

There are actually two ways you can do this

1.LOAD INFILE

LOAD DATA LOCAL INFILE  
'path/file.csv'
INTO TABLE giata_table 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

2.Use multi-row insert

INSERT INTO `TABLE_NAME`(`XXX_id`) VALUES (1),(2),(3),(4)

Tarun · Answer 7 · 2017-10-26T06:03:46.723

0

$string =  ' (" '.implode(' "),(" ',$array).' ") ';
$query = "INSERT INTO TABLE_NAME (XXX_id) VALUES {$string}";

IF you have large number of records, I would suggest you to make a bunch of 100-200 records and then insert:

for($i=0; $i<sizeof($array); $i++){

   $string = $string==""?'('.$array[$i].')':$string.',('.$array[$i].')';
   if($i % 100 == 0){
     $query = INSERT INTO TABLE_NAME (XXX_id) VALUES ($string);
     // execute the query
    $string = "";
  }
}

if($string != ""){
$query = INSERT INTO TABLE_NAME (XXX_id) VALUES ($string);
     // execute the query
}

edited Oct 26 '17 at 06:03

answered Oct 26 '17 at 05:52

Tarun

3,162
3
29
45

1

I don't think he wants to put them all into one record, I think each one is a separate row. – Barmar Oct 26 '17 at 05:54

score 0 · Answer 8 · answered Oct 26 '17 at 06:19

do the following stuff in your procedure to get rid of your problems:

-- Temporary table to hold splited row value of string


drop temporary table if exists temp_convert;
        create temporary table temp_convert(split_data varchar(2056) );

       -- dynamic query to break comma separated string as rows and 

insert into column
        set @sql = concat("
        insert into temp_convert (split_data) 
                               values ('", replace(
        ( select group_concat(in_string) as converted_data), ",", "'),('"),"'
                                                   );"
        );
        prepare stmt1 from @sql;
        execute stmt1;

Insert records from array in MySQL query

8 Answers8