9.5 Exercises
| Question 1: | Assume that you have a text file containing tab-separated data that you want to load into a table named loadtest that has the following structure:
mysql> DESCRIBE loadtest;
+---------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| number1 | int(11) | YES | | NULL | |
| char1 | char(1) | YES | | NULL | |
| date1 | date | YES | | NULL | |
+---------+---------+------+-----+---------+-------+
One line of the file looks like this, where whitespace between values represents tab characters:
NULL NULL NULL
If you use LOAD DATA INFILE to load the file, what values will be created in the table from the values in this line? What values should the line contain if you actually want a row of NULL values to be created in the table? | | Question 2: | Here's the structure of an empty table called personnel:
mysql> DESCRIBE personnel;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
| pid | smallint(5) unsigned | | PRI | NULL | auto_increment |
| unit | tinyint(3) unsigned | YES | | NULL | |
| grade | tinyint(3) unsigned | YES | | NULL | |
+-------+----------------------+------+-----+---------+----------------+
mysql> SELECT * FROM personnel;
Empty set
A file called /tmp/personnel.dat contains data that you want to load into the personnel table, using the LOAD DATA INFILE statement. The datafile contains data for the unit and grade columns and looks like this:
23;42
23;53
23;123
23;142
23;198
23;248
23;294
42;110
42;256
The lines are terminated by \r. Upon which operating system was the datafile most probably generated? | | Question 3: | Here's the structure of an empty table called personnel:
mysql> DESCRIBE personnel;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
| pid | smallint(5) unsigned | | PRI | NULL | auto_increment |
| unit | tinyint(3) unsigned | YES | | NULL | |
| grade | tinyint(3) unsigned | YES | | NULL | |
+-------+----------------------+------+-----+---------+----------------+
mysql> SELECT * FROM personnel;
Empty set
A file called /tmp/personnel.dat contains data that you want to load into the personnel table, using the LOAD DATA INFILE statement. The datafile contains data for the unit and grade columns and looks like this:
23;42
23;53
23;123
23;142
23;198
23;248
23;294
42;110
42;256
The lines are terminated by \r. What statement would you issue to load the data into the table? | | Question 4: | Here's the structure of an empty table called personnel:
mysql> DESCRIBE personnel;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
| pid | smallint(5) unsigned | | PRI | NULL | auto_increment |
| unit | tinyint(3) unsigned | YES | | NULL | |
| grade | tinyint(3) unsigned | YES | | NULL | |
+-------+----------------------+------+-----+---------+----------------+
mysql> SELECT * FROM personnel;
Empty set
A file called /tmp/personnel.dat contains data that you want to load into the personnel table, using the LOAD DATA INFILE statement. The datafile contains data for the unit and grade columns and looks like this:
23;42
23;53
23;123
23;142
23;198
23;248
23;294
42;110
42;256
The lines are terminated by \r. What entries will be in the table if the data import succeeds? | | Question 5: | Is there a MySQL client program that could be used for data import instead of the LOAD DATA INFILE statement? If so, what is it called? | | Question 6: | The simplest form of the LOAD DATA INFILE statement looks like this:
LOAD DATA INFILE 'file_name' INTO TABLE table_name;
MySQL will assume a number of defaults for options that are omitted from the statement. Assuming that the filename has a single component, what will MySQL use as the default location for file_name? | | Question 7: | The simplest form of the LOAD DATA INFILE statement looks like this:
LOAD DATA INFILE 'file_name' INTO TABLE table_name;
MySQL will assume a number of defaults for options that are omitted from the statement. What will MySQL use as the default separators for fields and lines? | | Question 8: | The simplest form of the LOAD DATA INFILE statement looks like this:
LOAD DATA INFILE 'file_name' INTO TABLE table_name;
MySQL will assume a number of defaults for options that are omitted from the statement. What will MySQL use as the default table columns to load? | | Question 9: | The simplest form of the LOAD DATA INFILE statement looks like this:
LOAD DATA INFILE 'file_name' INTO TABLE table_name;
MySQL will assume a number of defaults for options that are omitted from the statement. What will MySQL use as the default number of lines of file_name to skip? | | Question 10: | The simplest form of the LOAD DATA INFILE statement looks like this:
LOAD DATA INFILE 'file_name' INTO TABLE table_name;
MySQL will assume a number of defaults for options that are omitted from the statement. What will MySQL use as the default behavior when there are lines in file_name that would duplicate unique-valued key entries? Does it make a difference if file_name is located on the client host rather than on the server host? | | Question 11: | After loading data into a table, you see the following result from the LOAD DATA INFILE statement. What does it mean?
Query OK, 18 rows affected (0.00 sec)
Records: 9 Deleted: 9 Skipped: 0 Warnings: 2
| | Question 12: | After loading data into a table, you see the following result from the LOAD DATA INFILE statement. What does it mean?
Query OK, 0 rows affected (0.00 sec)
Records: 9 Deleted: 0 Skipped: 9 Warnings: 2
| | Question 13: | Consider the following table data:
mysql> SELECT * FROM personnel;
+-----+------+-------+
| pid | unit | grade |
+-----+------+-------+
| 46 | 23 | 42 |
| 47 | 23 | 53 |
| 48 | 23 | 123 |
| 49 | 23 | 142 |
| 50 | 23 | 198 |
| 60 | 23 | 248 |
| 70 | 23 | 255 |
| 80 | 42 | 110 |
| 90 | 42 | 255 |
+-----+------+-------+
Assume that you want to export the pid and unit columns for the five highest grades to a file that has Windows-like line terminators (\r\n) and that looks like this:
"70";"23"
"90";"42"
"60";"23"
"50";"23"
"49";"23"
What statement would you issue? |
Answers to Exercises | | | Answer 1: | The actual result of the LOAD DATA statement will look like this:
mysql> SELECT * FROM loadtest;
+---------+-------+------------+
| number1 | char1 | date1 |
+---------+-------+------------+
| 0 | N | 0000-00-00 |
+---------+-------+------------+
The import file values are interpreted as the string 'NULL', not as NULL values. For nonstring columns, these strings are converted first before they are inserted. This conversion results in values of 0 for integers and '0000-00-00' for dates. For the string column, the import string is clipped to the length of the column, resulting in a value of 'N'. If you wanted to actually insert NULL values, the import file would have to contain the \N sequence that LOAD DATA INFILE interprets as representing NULL. | | | | Answer 2: | The datafile personnel.dat was most probably generated under Mac OS because carriage returns (\r) are a common line terminator under that operating system. | | | | Answer 3: | This statement loads the data into the personnel table:
mysql> LOAD DATA
-> INFILE '/tmp/personnel.dat'
-> INTO TABLE personnel
-> FIELDS TERMINATED BY ';'
-> LINES TERMINATED BY '\r'
-> (unit, grade)
-> ;
Query OK, 9 rows affected (0.01 sec)
Records: 9 Deleted: 0 Skipped: 0 Warnings: 2
Note the two warnings. | | | | Answer 4: | The table will contain the following data:
mysql> SELECT * FROM personnel;
+-----+------+-------+
| pid | unit | grade |
+-----+------+-------+
| 1 | 23 | 42 |
| 2 | 23 | 53 |
| 3 | 23 | 123 |
| 4 | 23 | 142 |
| 5 | 23 | 198 |
| 6 | 23 | 248 |
| 7 | 23 | 255 |
| 8 | 42 | 110 |
| 9 | 42 | 255 |
+-----+------+-------+
9 rows in set
As you would expect, the pid column values were assigned automatically due to the AUTO_INCREMENT option of the pid column. For the grade column, however, the import encountered two problem lines (7 and 9), where the provided values of 294 and 256 are out of the range of the TINYINT UNSIGNED datatype (which has a maximum value of 255). The values given were therefore truncated to the maximum value for this datatype before being inserted. MySQL gives a warning for each of the values truncated, as shown in the message displayed by the LOAD DATA INFILE statement. (See answer to previous question.) | | | | Answer 5: | Instead of using the SQL command LOAD DATA INFILE, you could use the MySQL client program named mysqlimport. It takes arguments that correspond to the various clauses of the LOAD DATA INFILE statement. | | | | Answer 6: | The default location of file_name is the directory of the default database. | | | | Answer 7: | The default separator for fields is the tab character (\t). The default separator for lines is the newline character (\n). | | | | Answer 8: | All table columns will be loaded by default. This means that file_name should specify a value for every table column. If lines don't contain values for all columns, each missing column is set to its default value. | | | | Answer 9: | No lines of file_name will be skipped by default. | | | | Answer 10: | If the datafile is located on the server host, the default behavior in case of duplicate unique-valued key entries is to return an error. MySQL will not insert the duplicating line, and will then skip the rest of the lines. If the datafile is located on the client host, the default behavior changes. In this case, the default is to skip lines that would duplicate records; that is, MySQL operates as if the keyword IGNORE had been specified. | | | | Answer 11: | The message provides four pieces of information.
Nine lines were read from the datafile (Records: 9). The keyword REPLACE was used in the LOAD DATA INFILE statement. Each one of the nine lines in the datafile has the same unique-valued key entries. The same value already exists in the table, so all nine entries were deleted and then replaced by the nine lines from the datafile (Deleted: 9, and 18 rows affected). The warnings indicate that two problems were found in the import file (Warnings: 2).
| | | | Answer 12: | The message provides four pieces of information.
Nine lines were read from the datafile (Records: 9). The keyword IGNORE was used in the LOAD DATA INFILE statement. Each one of the nine lines in the datafile has the same unique-valued key entries. The same value already exists in the table, so all nine entries were skipped, that is, they were not inserted (Skipped: 9, and 0 rows affected). The warnings indicate that two problems were found in the import file (Warnings: 2).
| | | | Answer 13: |
mysql> SELECT pid, unit
-> INTO OUTFILE 'highpers.dat'
-> FIELDS TERMINATED BY ';'
-> ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n'
-> FROM personnel
-> ORDER BY grade DESC
-> LIMIT 5
-> ;
|
|
