As you can see, there are many different ways of importing data into a database. How you do it is up to
you. There are also many helpful tools available. Fortunately, MySQL includes some of them.MySQL Import Tools
MySQL has a utility that is made specifically for loading data into a table. It is somewhat crude, but very
effective. This utility is the mysqlimport tool. It resides in the mysql/bin directory. It is a command-line
utility that takes two parameters plus a number of options. This tool will take a text file and import it into the
database and table that you specify. For example, if you wanted to load a file with data into the Customers
table, you would use the mysqlimport command in the following way:
bin/mysqlimport Meet_A_Geek Customers.txt
This command takes the contents of the text file and loads them into the table specified by the filename
up to the first period. In this example, the data in the text file would go into the Customers table. If your
file had a name like Cus.to.mer.txt, the data from this file would go into the Cus table. That's why
this tool is a little crude, but it does get the job done. Another point worth mentioning is that if the table
does not exist or if the data in the file does not match up with that in the table's columns, an error would
occur. The data must match in type and in number—just as with an INSERT statement. In the previous
example, if the table that was receiving the data had Customer_ID int, Last_Name varchar(25),
First_Name(15) columns, and your file had "I", 1 , "Stan", "Behm" comma-delimited values, an
error would occur. The data does not match in type—the first value in the data file ("I") does not match
the data type for that column, which is an integer. The second problem is that the data file contains
more entries than the table has columns. To prevent these kinds of mishaps, ensure that everything is
correct before trying to insert your data.mysqlimport Options
As mentioned earlier, the mysqlimport utility can also take many options. Table 8.1 shows a list of options
and what they do.
Table 8.1 mysqlimport Options
Option Action Performed-d or --delete (^) This option will delete all the existing data in the table before
importing the new data.
-f or --force (^) This option will force mysqlimport to continue inserting data,
regardless of any errors it may encounter.
-i or --ignore (^) Causes mysqlimport to skip or ignore rows that share the
same unique number. The data from the import file will be
ignored.
-L or -local (^) This option forces mysqlimport to use a file on your local
machine, not the MySQL server. This is handy if you want to
use a file locally and import it remotely. It is a little slower, but
you don't have to FTP it and then run the mysqlimport
command.
-l or -lock-tables
This option locks each table before any data is inserted. A good
option to use if you are importing a large amount of data on a
busy server.
-r or -replace (^) This option is the opposite of the -I option. It will replace the
field in the table that shares the same unique value.
--fields-enclosed-
by=
char
This specifies what character encloses the data in your file. For
example, a lot of products enclose data with quotation marks.
You could specify that to the import utility with this option. By
default, the mysqlimport utility assumes that there are no
enclosing characters.
--fields-escaped-by=
char
This option tells the mysqlimport utility what character is the
escape character for special characters. Characters that may
need to be escaped are the backslash () and the newline
character (\n). The default is no escape character.
--fields-optionally-
terminated-by=char
This option states that data can be enclosed by a special
character. Otherwise, it works just like the command of the