Tuesday, February 24, 2015

File input to My sql database

Hello, today I did a fantastic thing, I need to import csv file into database table, so normally we think to create a function that can handle formate and do insert query according to the requirement.

but when the matter is all about csv then my sql have a command which automatically handle mostly all the things it self, as well as it'll reduce execution time for the query while our function might not be optimized enough and effective.

So I would prefer to use this command: LOAD DATA INFILE
FYI: here infile means input file.

Few things to be taken care of while using this command

  • while application connect it must have proper additional connection parameter "--local-infile=1" while normal connection from command line, if you use laravel then it's should follow proper PDO **referance
  • the directory should have permission to read write for third party application (in our case mysql engine) if not then give permission using command "sudo chmod 0777 <dir_name> -R"
    note: here 0777 is for giving all permission to all user, and -R is to use it in all child dir & files
  • mysql have it's own permission that which dir are permitted to use for read and/or write operation, so we also need to add those directory into that configuration so mysql can read our file **referance
    • Step 1: for that edit file "sudo vi /etc/apparmor.d/usr.sbin.mysqld" 
    • Step 2: add this lines
      /data/ r,
      /data/* rw,
    • Step 3: run this command: "sudo /etc/init.d/apparmor reload"
if you take care of these things, then you are good to use this special and fantastic mysql query, hope this article will help you to run mass insertion effectively and speedly.


No comments:

Post a Comment