Understanding mysqlimport (Importing Data Made Easy)

Whether you’re new to MySQL or a seasoned user, the mysqlimport tool significantly enriches the process of loading data. 

In this article, learn:

• How to use mysqlimport?
• When to use mysqlimport?
• Why is mysqlimport a go-to tool to save time?

What is MySQL?

MySQL is a popular and widely used Open-Source Relational Database Management System. Like other RDMS, MySQL stores your data in separate tables with columns and rows.

You can use SQL (Standard Query Language) to interact with the MySQL Database.One popular approach to uploading files to MySQL Tables is via the mysqlimport command. SQL can also be used to define Table Schemas or configure the relationship between tables in the database.

Key Features of MySQL

• Ease-of-Use & Flexibility: MySQL is compatible with various Operating Systems such as UNIX, Linux, Windows, etc. It also supports a variety of programming languages such as PHP, PERL, C, C++, JAVA, etc.
• Top-Notch Performance: You can rest assured that you will find the best-in-class query performance with the wide array of cluster servers that back MySQL. It effectively handles your fluctuating workloads and processes massive volumes of data with optimal speed. 
• Open-Source: MySQL is licensed under the GNU General Public License (GPL), meaning it will always be freely available. You can customize it on Oracle’s Open-Source MySQL Codebase according to your requirements.
• Secure: Using SQL, you can configure your Data Access Control settings. With the Access Privilege System and User Account Management, you can decide who can view or use your MySQL data.

How to Upload Data to MySQL using mysqlimport?

SysAdmins and DBAs (Database Administrators) who manage MySQL Databases are required to upload data from Text, Csv, or Excel files into MySQL Tables. To perform this, you can use the mysqlimport Command.

The mysqlimport Client supplies a Command-Line Interface to the LOAD DATA SQL statement. You will find that most of the mysqlimport options refer directly to the LOAD DATA SQL syntax.

mysqlimport Syntax

• To upload data via mysqlimport Command, you can follow the syntax given below:

mysqlimport [options] db_name textfile1 [textfile2 ...]

• db_name refers to the name of the database that contains the tables in which you want to upload the data from files textfile1, textfile2, etc. 
• MySQL will take each file mentioned in the command line above individually and strip the extension from its name.
• The remaining filename is used to determine the name of the table into which the data needs to be uploaded. 
• For instance, data from files such as Employee.txt, Employee.text, and Employee will be imported into a table named Employee. 
• Below is the list of the options that MySQL Import supports. You can mention them on the command line or in the [mysqlimport] and [client] groups of an option file:

You can check the detailed documentation provided by MySQL – mysqlimport Options.

mysqlimport Example 

To understand mysqlimport, let’s go through the following example:

• Creating a student Table in a Database called Test and importing Student data

mysql -e 'CREATE TABLE student(id INT, n VARCHAR(30))' test
$> ed
a
231     Mike Rogers
232     Ryan Smith
.
w imptest.txt
32
q
$> od -c imptest.txt
0000000   1   0   0  t   M   i   k	e       R   o   g   e   r   s  n   1   0
0000020   1  t   R   a   y   n       S   m   i   t   h  n
0000040
$> mysqlimport --local test student.txt
test.imptest: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
$> mysql -e 'SELECT * FROM student' test
+------+------------+
| id   | n          |
+------+------------+
|  231 | Mike Rogers   |
|  232 | Ryan Smith |
+------+------------+

Note: The file name should be exactly the same as the Table name you want the data imported into. If not, you will get an error message.

For example, the “stu” table doesn’t exist in the test database. Hence, the filename student.txt should be selected to match the Student Table Name.

$> mysqlimport --local test stu.txt
mysqlimport: Error: Table 'test.stu' doesn't exist, when using table: stu

Advantages of using mysqlimport

Here are the main reasons users use mysqlimport Client:

• Speed: mysqlimport loads large datasets into MySQL by accelerating the process, therefore saving time.
• Simplicity: It is very user-friendly with minimal command-line information.
• Automation: Supports batch processing, which makes imports of bulk data smooth.
• Flexibility: It accepts any file type, such as CSV or TSV.
• Integration: It integrates well with other MySQL tools, which is effective for dealing with the database.

Summary

• In this article, you have learned how to effectively use the mysqlimport command to upload data to your MySQL Database.
• The Standard mysqlimport command seamlessly imports data from the files you mention to your tables.
• By naming the files the same as the Table name, you can avoid any errors and effortlessly import data to your tables.
• mysqlimport command also provides a list of options to enter columns list, compression settings, passwords, etc.

Take Hevo’s 14-day free trial to experience a better way to manage your data pipelines. 

You can also check out the unbeatable pricing, which will help you choose the right plan for your business needs.

Tell us about your experience of working with the MySQLImport command! Share your thoughts with us in the comments section below.

Frequently Asked Questions