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.
You can create array format like this
INSERT INTO `TABLE_NAME`(`XXX_id`) VALUES (1),(2),(3),(4)
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)
You can use multi-row insert to achieve what you want
Just format your array as VALUES (X), (X), ... with PHP
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)
php.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.
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)
$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
}
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;
