LAMPsig MySQL Class :: Class #8 (The last class)

LAMPsig Meetings

3rd Saturday of the Month 1-3pm
Tom Bradley Youth and Family Center
5213 West Pico Blvd.
Los Angeles, CA 90019

Monday, June 27. 2005

LAMPsig MySQL Class :: Class #8 (The last class)

Class #8 (the last class) of the MySQL class is scheduled for this Monday, June 27 at 7:00pm at CalTek and will cover Importing and Exporting Data Statements.

*Complete syllabus for Class #8 are continued in the further reading section.*

_Instructor:_
Christopher Thompson :: cxthompson_AT_charter_(dot)_net

_Class Contact:_
Sharon Lake :: sharon_AT_linuxchixla_(dot)_org

_Lab Help:_
Steve Glasser :: steve_AT_fpig_(dot)_net

*_CLASS #8 SYLLABUS_*

Class 8 – Importing and Exporting Data (Chapter 9, MySQL MM 1.7.5.2, 2.3.16, 8.8, 8.10, 13.1.5, 13.6.2.3 )
_5% exam material_

Review of mysqlimport and mysqldump
1. mysqlimport Command line interface to LOAD DATA INFILE.
2. mysqldump

LOAD DATA INFILE
1. Alternative to mysql INSERT statement
  1. LOAD DATA INFILE 'file_name' INTO TABLE 'table_name';
    - file name is a string and must be quoted
    - location is defaulted to localhost
    - default file format (tab delimited, newline-terminated, a value for each column in table)
  2. LOAD DATA INFILE syntax
    - LOAD DATA [LOCAL] INFILE 'file_name'
      [IGNORE | REPLACE] INTO TABLE table_name
      format_specifiers
      [IGNORE n LINES]
      [(column_list)];
2. Specifying the Datafile Location without using LOCAL
  1. Default location assumed to be local to the server as MySQL reads the file directly.
    - Absolute path on server: LOAD DATA INFILE '/path/to/file.txt' INTO TABLE table_name;
    - If using a default database then relative path is relative to the default database: LOAD DATA INFILE 'file.txt' INTO TABLE table_name;
    - If not using a default database then the relative path is relative the the specific database data directory: LOAD DATA INFILE './data_directory/file.txt' INTO TABLE table_name;
  2. Using LOCAL means the file is local to the MySQL client host. The MySQL client reads the file and sends to the server.
    - Absolute path on client
    - Relative path on client is relative to the current directory.
  3. Special note on Windows ... the path separator is \, but MySQL treats backslash as escape. You can either use the / (forward) slash, i.e., LOAD DATA INFILE 'C:/path/to/file.txt' INTO TABLE table_name; or escape the backslash character, i.e., LOAD DATA INFILE 'C:\\path\\to\\file.txt' INTO TABLE table_name;
3. Loading into Specific Columns
  1. LOAD DATA INFILE 'file.txt' INTO TABLE table_name (col1, col2);
  2. Note, if order in text file doesn't match order in table columns, you can switch the the insertation text order via specifying columns, i.e., LOAD DATA INFILE 'file.txt' INTO TABLE table_name (col2, col1);
4. Skipping datafile lines or header row(s) with IGNORE
  1. LOAD DATA INFILE 'file.txt' INTO TABLE table_name IGNORE n LINES;
5. Dealing with duplicate records.
  1. Default behavior on a duplicate Key violation is to stop loading the file at the point of error. All records previously processed remain.
  2. IGNOREkeyword will load the entire file, but discard the records causing duplicate key violations.
  3. REPLACE keyword will also load the entire file, but will replace the records with duplicate key violations with the new information.
6. Interpreting LOAD DATA INFILE statement result
  1. Records: Number of tables read from the file, but not necessarily the number of records input into the table.
  2. Deleted: Number of records replaced in the table when using the REPLACE keyword.
  3. Skipped: Number of input records ignored in the data file when using the IGNORE keyword.
  4. Warnings: Indicates possible problems in the input file, i.e., missing values, data conversions, etc. Can be a number greater than the number of records input as more than 1 error can be generated per record.
7. Privileges
  1. You need the INSERT privilege for the table in which you want to LOAD DATA INFILE for LOCAL files.
  2. You also need FILE privilege for data files which are located on the server.
8. LOAD DATA INFILE is considered an efficient insert method

SELECT INTO OUTFILE
1. Adding the INTO OUTFILE clause into a SELECT statement creates a file on the server with the SELECT result.
2. Since the file is created on the server, the user must have FILE privileges.
3. The file is created with MySQL server as the owner but world-readable. Also since it is owned by MySQL you might not be able to remove the file without server admin privileges. Also, since the file is world-readable, careful about sensitive data.
4. Default format is one line per row, delimited by tab characters, and lines terminated with newlines.
5. Since the file is created on the server, to access the file you must have a login account on the server host. Not an issue if you only want to read the file back in via a LOAD DATA INFILE as the server has access to the file ... even if you don't.

Data Format Specifiers
1. LOAD DATA INFILE format specifiers are listed after the table name.
2. SELECT ... INTO OUTFILE format specifiers are listed after the output filename.
  1. Syntax for format specifiers is the same for both statements and can be used in any order. Default values are supplied if missing.
    - FIELDS
      TERMINATEDBY 'string'
      ENCLOSEDBY 'char' / OPTIONALLY ENCLOSED BY 'char'
      ESCAPED BY 'char'
      LINES TERMINATED BY 'string';
  2. Default values
    - TERMINATED BY: tab ('\t')
    - ENCLOSED BY/OPTIONALLY ENCLOSED BY: unquoted
    - LINES TERMINATED BY: newline ('\n')
      Special Note to platform. Unix line terminator are usually '\n' (newline), MAC OS and OSX line terminators are usually '\r' (carriage return), and Windows line terminators are usually '\r\n' (carriage return and newline)
    - ESCAPE BY: backslash ('\'). Escape sequences as understood by MySQL.
      - \N (NULL)
      - \0 (ASCII NUL byte)
      - \b (backspace)
      - \n (newline)
      - \r (carriage return)
      - \s (space)
      - \' (single quote)
      - \” (double quote)
      - \\ (backslash)
3. Example. To load a file with comma-separated values, with values quoted by double quote, and line terminated by carriage returns.
  1. To insert:
    - LOAD DATA INFILE 'file.txt'
      INTO TABLE table_name
      FIELDS TERMINATED BY ','
      ENCLOSED BY '”'
      LINES TERMINATED BY '\r';
  2. To write:
    - SELECT * INTO OUTFILE 'file.txt'
      FIELDS TERMINATED BY ','
      ENCLOSED BY '“'
      LINES TERMINATED BY '\r'
      FROM table_name;

Importing and Exporting NULLValues
1. LOAD DATA INFILE a \N appearing unquoted by itself as a column value is interpreted as NULL. MySQL converts empty values to a 0, empty string, or a “zero” temporal value depending of the column type.
2. SELECT ... INTO OUTFILE writes NULL values as \N

Posted by Sharon Lake in Classes: MySQL at 19:00 | Comment (1) | Trackbacks (0)
View as PDF: This entry | This month | Full blog

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)

I just want to once again thank Sharon for the unbelievable job she did in organizing this class, setting the agenda, doing all the syllabii, getting the instructors, and seeing the class through. It was truly a herculean effort, and I've never seen it done better.

#1 David on 2005-07-03 16:48 (Reply)

Add Comment

Name
Email
Homepage
In reply to
Comment	E-Mail addresses will not be displayed and will only be used for E-Mail notifications To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly. Enter the string from the spam-prevention image above: BBCode format allowed
	Remember Information? Subscribe to this entry