Web Hosting How to Upload Large Data Into Mysql Database

Uploading several rows of data from a text, csv, and excel file into a MySQL table is a routine task for sysadmins and DBAs who are managing MySQL database. This article explains iv practical examples on how to import data from a file to MySQL table using both mysqlimport and "load data local infile" method. Instead of importing information, if yous want to backup and restore MySQL database, please utilise mysqldump or mysqlhotcopy.

Create an employee table and employee.txt datafile

For the examples mentioned in this commodity, permit us create a very simple employee table with three columns–employee number, employee name and job.

# mysql -u root -ptmppassword mysql> use exam Database changed mysql> create tabular array employee     -> (     -> empno int,     -> ename varchar(15),     -> chore varchar(10)     -> ); Query OK, 0 rows afflicted (0.01 sec)

Create a examination datafile employee.txt with fields delimited past tab as shown beneath.

# cat employee.txt 100     John Doe        DBA 200     John Smith      Sysadmin 300     Raj Patel       Developer

1. Upload tab delimited datafile to MySQL tabular array

Apply mysqlimport to import the employee.txt datafile to employee table in test database, as shown beneath:

# mysqlimport -u root -ptmppassword --local test employee.txt examination.employee: Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Verify that the records got uploaded successfully.

# mysql -u root -ptmppassword mysql> employ test; mysql> select * from employee; +-------+------------+-----------+ | empno | ename      | job       | +-------+------------+-----------+ |   100 | John Doe   | DBA       | |   200 | John Smith | Sysadmin  | |   300 | Raj Patel  | Developer | +-------+------------+-----------+ iii rows in set (0.00 sec)

Note: In mysqlimport, the proper noun of the datafile should lucifer the name of the table. The extension of the datafile tin exist annihilation. In the above example, only employee.* datafile tin be used to upload information to employee table. You lot'll get the post-obit error message when the filename is not same as tablename:

# mysqlimport -u root -ptmppassword --local test emp.txt mysqlimport: Error: Tabular array 'test.emp' doesn't be, when using tabular array: emp          [Note: The tabular array name is employee. So, datafile name should exist employee.*]        

2. Import multiple datafiles into multiple MySQL tables

The post-obit case uploads data from ii different datafiles to 2 different tables. i.e It uploads employee.txt to employee tabular array and manager.txt to managing director tabular array.

# mysqlimport -u root -ptmppassword --local test employee.txt manager.txt

three. Utilize LOAD DATA LOCAL INFILE to upload data to MySQL tables

The mysqlimport client is simply a command-line interface to the LOAD DATA LOCAL INFILE SQL argument. Near options to mysqlimport correspond directly to clauses of "load data local infile" syntax. You can perfrom the same upload explained in example#i using "load data local infile" instead of mysqlimport as explained beneath:

# mysql -u root -ptmppassword mysql> use test; mysql> LOAD DATA LOCAL INFILE '/home/ramesh/employee.txt'     -> INTO TABLE employee     -> FIELDS TERMINATED BY '\t'     -> LINES TERMINATED By '\n'     -> (empno, ename, job); Query OK, 3 rows afflicted (0.01 sec) Records: 3  Deleted: 0  Skipped: 0  Warnings: 0  mysql> select * from employee; +-------+------------+-----------+ | empno | ename      | job       | +-------+------------+-----------+ |   100 | John Doe   | DBA       | |   200 | John Smith | Sysadmin  | |   300 | Raj Patel  | Developer | +-------+------------+-----------+ 3 rows in set (0.00 sec)

four. Nearly frequently used mysqlimport options

The nearly frequently used mysqlimport options are shown in the example beneath. Most of these options are self explanatory.

• compress: Shrink all information sent betwixt the client and the server
• delete: This option is very handy when yous want to empty the tabular array earlier importing the text file
• local: Read input files locally from the client host
• lock-tables: Lock all tables for writing earlier processing any text files. This ensures that all tables are synchronized on the server.

# mysqlimport \     --user=root \     --password=tmppassword \     --columns=empno,ename,chore \     --compress \     --delete \     --fields-optionally-enclosed-by='"' \     --fields-terminated-by='\t' \     --fields-escaped-past='' \     --lines-terminated-past='\northward' \     --local \     --lock-tables \     --verbose \     exam employee.txt

Output of the to a higher place mysqlimport command:

Connecting to localhost Selecting database test Locking tables for write Deleting the old data from table employee Loading information from LOCAL file: /abode/ramesh/employee.txt into employee test.employee: Records: 3  Deleted: 0  Skipped: 0  Warnings: 0 Disconnecting from localhost

mackathim1955.blogspot.com

Source: https://www.thegeekstuff.com/2008/10/import-and-upload-data-to-mysql-tables-using-mysqlimport/

Share this post