same name.
--fields-terminated-
by=charThis option specifies what character separates the values from
one another. In a comma-delimited file, it would be the comma.
The mysqlimport utility defaults to the tab character.
--lines-terminated-
by=strThis option specifies what ends a line of data. By default,
mysqlimport assumes the newline character. A string could
be used instead of a single character—a newline and a
carriage return, for example.
The mysqlimport command common options, such as -v for version and -p for password.Importing a Comma-Delimited File
Now, assume that you need to import a comma-delimited text file that you generated from a spread sheet
program. Each column of data is separated by a comma and enclosed in quotation marks, as shown in the
following:
"1", "ORD89876", "1 Dozen Roses", "19991226"
Some of the data in the file came from the database and may or may not have been updated. This task
must be accomplished by the end of the day on the production system. Your job is to import this data
into the Orders table in the Meet_A_Geek database. You would use the following command:
bin/mysqlimport –prl –fields-enclosed-by="
–fields-terminated-by=, Meet_A_Geek Orders.txt
This command may look a little bit intimidating, but once you get into it, it's pretty simple. The first part,
bin/mysqlimport, basically tells the OS what command you want to run. The p option was used
because you have learned how to secure your database and must now authenticate yourself before you
make any structural changes. The r option is used because you want to replace any records that have
the same unique key. You're doing this because you were told that the spreadsheet may or may not
have been updated in this scenario. You must assume that your spreadsheet is the most current data,
and you want to replace any data in the database with the current data. The l option is used because
you want to lock the table while you are inserting records. This prevents a user from selecting or
changing any data while you are updating the table. You will learn more about locks on Day 11,
"MySQL Table Locks and Assorted Keys." The next couple of options describe the data that is held in
your import file. You are telling mysqlimport that your data is enclosed by quotation marks and that
the fields are separated by commas. Finally, you tell mysqlimport what database to use, as well as
the table into which to INSERT the data. Remember, mysqlimport gets the name of the table from the
filename. It assumes that everything to the left of the first period is fair game for the table name, so
make sure your filename matches your table name.Import with Batching
Another way MySQL can import data is through batching. Batching is a method of running the mysql
program passively rather than interactively, as you do with the MySQL monitor, although you still use the
same command. To do this, you redirect a file to the mysql program. Sound a little confusing? It's not. In
fact, it's quite simple.
To do this, you need a text file that contains the same text you would type into the MySQL monitor. For
example, suppose that you wanted to INSERT some data. You could create a text file that had the
following text:
USE Meet_A_Geek;
INSERT INTO Customers (Customer_ID, Last_Name)
VALUES(NULL, "Block");
INSERT INTO Customers (Customer_ID, Last_Name)
VALUES(NULL, "Newton");
INSERT INTO Customers (Customer_ID, Last_Name)
VALUES(NULL, "Simmons");
Notice that these commands are syntactically correct—SQL statements are covered in more detail
tomorrow—and end in a semicolon, just as they would if you were to type this into the MySQL monitor.
The next part is where you will actually import this data into MySQL. Before you can do that, though, the