Dont Assume LOAD DATA Knows More than It Does
Don t Assume LOAD DATA Knows More than It Does
10.10.1 Problem
You think LOAD DATA is smarter than it really is.
10.10.2 Solution
Don't assume that LOAD DATA knows anything at all about the format of your datafile. And make sure you yourself know what its format is. If the file has been transferred from one machine to another, its contents may have been changed in subtle ways of which you're not aware.
10.10.3 Discussion
Many LOAD DATA frustrations occur because people expect MySQL to know things that it cannot possibly know. LOAD DATA makes certain assumptions about the structure of input files, represented as the default settings for the line and field terminators, and for the quote and escape character settings. If your input doesn't match those assumptions, you need to tell MySQL about it.
When in doubt, check the contents of your datafile using a hex dump program or other utility that displays a visible representation of whitespace characters like tab, carriage return, and linefeed. Under Unix, the od program can display file contents in a variety of formats. If you don't have od or some comparable utility, the transfer directory of the recipes distribution contains hex dumpers written in Perl and Python (hexdump.pl and hexdump.py), as well as a couple of programs that display printable representations of all characters of a file (see.pl and see.py). You may find them useful for examining files to see what they really contain. In some cases, you may be surprised to discover that a file's contents are different than you think. This is in fact quite likely if the file has been transferred from one machine to another:
- An FTP transfer between machines running different operating systems typically translates line endings to those that are appropriate for the destination machine if the transfer is performed in text mode rather than in binary (image) mode. Suppose you have tab-delimited linefeed-terminated records in a datafile that load into MySQL on a Unix system just fine using the default LOAD DATA settings. If you copy the file to a Windows machine with FTP using a text transfer mode, the linefeeds probably will be converted to carriage return/linefeed pairs. On that machine, the file will not load properly with the same LOAD DATA statement, because its contents will have been changed. Does MySQL have any way of knowing that? No. So it's up to you to tell it, by adding a LINES TERMINATED BY ' ' clause to the statement. Transfers between any two systems with dissimilar default line endings can cause these changes. For example, a Macintosh file containing carriage returns may contain linefeeds after transfer to a Unix system. You should either account for such changes with a LINES TERMINATED BY clause that reflects the modified line-ending sequence, or transfer the file in binary mode so that its contents do not change.
- Datafiles pasted into email messages often do not survive intact. Mail software may wrap (break) long lines or convert line-ending sequences. If you must transfer a datafile by email, it's best sent as an attachment.
Using the mysql Client Program
- Introduction
- Setting Up a MySQL User Account
- Creating a Database and a Sample Table
- Starting and Terminating mysql
- Specifying Connection Parameters by Using Option Files
- Protecting Option Files
- Mixing Command-Line and Option File Parameters
- What to Do if mysql Cannot Be Found
- Setting Environment Variables
- Issuing Queries
- Selecting a Database
- Canceling a Partially Entered Query
- Repeating and Editing Queries
- Using Auto-Completion for Database and Table Names
- Using SQL Variables in Queries
- Telling mysql to Read Queries from a File
- Telling mysql to Read Queries from Other Programs
- Specifying Queries on the Command Line
- Using Copy and Paste as a mysql Input Source
- Preventing Query Output from Scrolling off the Screen
- Sending Query Output to a File or to a Program
- Selecting Tabular or Tab-Delimited Query Output Format
- Specifying Arbitrary Output Column Delimiters
- Producing HTML Output
- Producing XML Output
- Suppressing Column Headings in Query Output
- Numbering Query Output Lines
- Making Long Output Lines More Readable
- Controlling mysqls Verbosity Level
- Logging Interactive mysql Sessions
- Creating mysql Scripts from Previously Executed Queries
- Using mysql as a Calculator
- Using mysql in Shell Scripts
Writing MySQL-Based Programs
- Introduction
- Connecting to the MySQL Server, Selecting a Database, and Disconnecting
- Checking for Errors
- Writing Library Files
- Issuing Queries and Retrieving Results
- Moving Around Within a Result Set
- Using Prepared Statements and Placeholders in Queries
- Including Special Characters and NULL Values in Queries
- Handling NULL Values in Result Sets
- Writing an Object-Oriented MySQL Interface for PHP
- Ways of Obtaining Connection Parameters
- Conclusion and Words of Advice
Record Selection Techniques
- Introduction
- Specifying Which Columns to Display
- Avoiding Output Column Order Problems When Writing Programs
- Giving Names to Output Columns
- Using Column Aliases to Make Programs Easier to Write
- Combining Columns to Construct Composite Values
- Specifying Which Rows to Select
- WHERE Clauses and Column Aliases
- Displaying Comparisons to Find Out How Something Works
- Reversing or Negating Query Conditions
- Removing Duplicate Rows
- Working with NULL Values
- Negating a Condition on a Column That Contains NULL Values
- Writing Comparisons Involving NULL in Programs
- Mapping NULL Values to Other Values for Display
- Sorting a Result Set
- Selecting Records from the Beginning or End of a Result Set
- 18 Pulling a Section from the Middle of a Result Set
- Choosing Appropriate LIMIT Values
- Calculating LIMIT Values from Expressions
- What to Do When LIMIT Requires the Wrong Sort Order
- Selecting a Result Set into an Existing Table
- Creating a Destination Table on the Fly from a Result Set
- Moving Records Between Tables Safely
- Creating Temporary Tables
- Cloning a Table Exactly
- Generating Unique Table Names
Working with Strings
- Introduction
- Writing Strings That Include Quotes or Special Characters
- Preserving Trailing Spaces in String Columns
- Testing String Equality or Relative Ordering
- Decomposing or Combining Strings
- Checking Whether a String Contains a Substring
- Pattern Matching with SQL Patterns
- Pattern Matching with Regular Expressions
- Matching Pattern Metacharacters Literally
- Controlling Case Sensitivity in String Comparisons
- Controlling Case Sensitivity in Pattern Matching
- Using FULLTEXT Searches
- Using a FULLTEXT Search with Short Words
- Requiring or Excluding FULLTEXT Search Words
- Performing Phrase Searches with a FULLTEXT Index
Working with Dates and Times
- Introduction
- Changing MySQLs Date Format
- Telling MySQL How to Display Dates or Times
- Determining the Current Date or Time
- Decomposing Dates and Times Using Formatting Functions
- Decomposing Dates or Times Using Component-Extraction Functions
- Decomposing Dates or Times Using String Functions
- Synthesizing Dates or Times Using Formatting Functions
- Synthesizing Dates or Times Using Component-Extraction Functions
- Combining a Date and a Time into a Date-and-Time Value
- Converting Between Times and Seconds
- Converting Between Dates and Days
- Converting Between Date-and-Time Values and Seconds
- Adding a Temporal Interval to a Time
- Calculating Intervals Between Times
- Breaking Down Time Intervals into Components
- Adding a Temporal Interval to a Date
- Calculating Intervals Between Dates
- Canonizing Not-Quite-ISO Date Strings
- Calculating Ages
- Shifting Dates by a Known Amount
- Finding First and Last Days of Months
- Finding the Length of a Month
- Calculating One Date from Another by Substring Replacement
- Finding the Day of the Week for a Date
- Finding Dates for Days of the Current Week
- Finding Dates for Weekdays of Other Weeks
- Performing Leap Year Calculations
- Treating Dates or Times as Numbers
- Forcing MySQL to Treat Strings as Temporal Values
- Selecting Records Based on Their Temporal Characteristics
- Using TIMESTAMP Values
- Recording a Rows Last Modification Time
- Recording a Rows Creation Time
- Performing Calculations with TIMESTAMP Values
- Displaying TIMESTAMP Values in Readable Form
Sorting Query Results
- Introduction
- Using ORDER BY to Sort Query Results
- Sorting Subsets of a Table
- Sorting Expression Results
- Displaying One Set of Values While Sorting by Another
- Sorting and NULL Values
- Controlling Case Sensitivity of String Sorts
- Date-Based Sorting
- Sorting by Calendar Day
- Sorting by Day of Week
- Sorting by Time of Day
- Sorting Using Substrings of Column Values
- Sorting by Fixed-Length Substrings
- Sorting by Variable-Length Substrings
- Sorting Hostnames in Domain Order
- Sorting Dotted-Quad IP Values in Numeric Order
- Floating Specific Values to the Head or Tail of the Sort Order
- Sorting in User-Defined Orders
- Sorting ENUM Values
Generating Summaries
- Introduction
- Summarizing with COUNT( )
- Summarizing with MIN( ) and MAX( )
- Summarizing with SUM( ) and AVG( )
- Using DISTINCT to Eliminate Duplicates
- Finding Values Associated with Minimum and Maximum Values
- Controlling String Case Sensitivity for MIN( ) and MAX( )
- Dividing a Summary into Subgroups
- Summaries and NULL Values
- Selecting Only Groups with Certain Characteristics
- Determining Whether Values are Unique
- Grouping by Expression Results
- Categorizing Non-Categorical Data
- Controlling Summary Display Order
- Finding Smallest or Largest Summary Values
- Date-Based Summaries
- Working with Per-Group and Overall Summary Values Simultaneously
- Generating a Report That Includes a Summary and a List
Modifying Tables with ALTER TABLE
- Introduction
- Dropping, Adding, or Repositioning a Column
- Changing a Column Definition or Name
- The Effect of ALTER TABLE on Null and Default Value Attributes
- Changing a Columns Default Value
- Changing a Table Type
- Renaming a Table
- Adding or Dropping Indexes
- Eliminating Duplicates by Adding an Index
- Using ALTER TABLE to Normalize a Table
Obtaining and Using Metadata
- Introduction
- Obtaining the Number of Rows Affected by a Query
- Obtaining Result Set Metadata
- Determining Presence or Absence of a Result Set
- Formatting Query Results for Display
- Getting Table Structure Information
- Getting ENUM and SET Column Information
- Database-Independent Methods of Obtaining Table Information
- Applying Table Structure Information
- Listing Tables and Databases
- Testing Whether a Table Exists
- Testing Whether a Database Exists
- Getting Server Metadata
- Writing Applications That Adapt to the MySQL Server Version
- Determining the Current Database
- Determining the Current MySQL User
- Monitoring the MySQL Server
- Determining Which Table Types the Server Supports
Importing and Exporting Data
- Introduction
- Importing Data with LOAD DATA and mysqlimport
- Specifying the Datafile Location
- Specifying the Datafile Format
- Dealing with Quotes and Special Characters
- Importing CSV Files
- Reading Files from Different Operating Systems
- Handling Duplicate Index Values
- Getting LOAD DATA to Cough Up More Information
- Dont Assume LOAD DATA Knows More than It Does
- Skipping Datafile Lines
- Specifying Input Column Order
- Skipping Datafile Columns
- Exporting Query Results from MySQL
- Exporting Tables as Raw Data
- Exporting Table Contents or Definitions in SQL Format
- Copying Tables or Databases to Another Server
- Writing Your Own Export Programs
- Converting Datafiles from One Format to Another
- Extracting and Rearranging Datafile Columns
- Validating and Transforming Data
- Validation by Direct Comparison
- Validation by Pattern Matching
- Using Patterns to Match Broad Content Types
- Using Patterns to Match Numeric Values
- Using Patterns to Match Dates or Times
- Using Patterns to Match Email Addresses and URLs
- Validation Using Table Metadata
- Validation Using a Lookup Table
- Converting Two-Digit Year Values to Four-Digit Form
- Performing Validity Checking on Date or Time Subparts
- Writing Date-Processing Utilities
- Using Dates with Missing Components
- Performing Date Conversion Using SQL
- Using Temporary Tables for Data Transformation
- Dealing with NULL Values
- Guessing Table Structure from a Datafile
- A LOAD DATA Diagnostic Utility
- Exchanging Data Between MySQL and Microsoft Access
- Exchanging Data Between MySQL and Microsoft Excel
- Exchanging Data Between MySQL and FileMaker Pro
- Exporting Query Results as XML
- Importing XML into MySQL
- Epilog
Generating and Using Sequences
- Introduction
- Using AUTO_INCREMENT To Set Up a Sequence Column
- Generating Sequence Values
- Choosing the Type for a Sequence Column
- The Effect of Record Deletions on Sequence Generation
- Retrieving Sequence Values
- Determining Whether to Resequence a Column
- Extending the Range of a Sequence Column
- Renumbering an Existing Sequence
- Reusing Values at the Top of a Sequence
- Ensuring That Rows Are Renumbered in a Particular Order
- Starting a Sequence at a Particular Value
- Sequencing an Unsequenced Table
- Using an AUTO_INCREMENT Column to Create Multiple Sequences
- Managing Multiple SimultaneousAUTO_INCREMENT Values
- Using AUTO_INCREMENT Valuesto Relate Tables
- Using Single-Row Sequence Generators
- Generating Repeating Sequences
- Numbering Query Output Rows Sequentially
Using Multiple Tables
- Introduction
- Combining Rows in One Table with Rows in Another
- Performing a Join Between Tables in Different Databases
- Referring to Join Output Column Names in Programs
- Finding Rows in One Table That Match Rows in Another
- Finding Rows with No Match in Another Table
- Finding Rows Containing Per-Group Minimum or Maximum Values
- Computing Team Standings
- Producing Master-Detail Lists and Summaries
- Using a Join to Fill in Holes in a List
- Enumerating a Many-to-Many Relationship
- Comparing a Table to Itself
- Calculating Differences Between Successive Rows
- Finding Cumulative Sums and Running Averages
- Using a Join to Control Query Output Order
- Converting Subselects to Join Operations
- Selecting Records in Parallel from Multiple Tables
- Inserting Records in One Table That Include Values from Another
- Updating One Table Based on Values in Another
- Using a Join to Create a Lookup Table from Descriptive Labels
- Deleting Related Rows in Multiple Tables
- Identifying and Removing Unattached Records
- Using Different MySQL Servers Simultaneously
Statistical Techniques
- Introduction
- Calculating Descriptive Statistics
- Per-Group Descriptive Statistics
- Generating Frequency Distributions
- Counting Missing Values
- Calculating Linear Regressions or Correlation Coefficients
- Generating Random Numbers
- Randomizing a Set of Rows
- Selecting Random Items from a Set of Rows
- Assigning Ranks
Handling Duplicates
- Introduction
- Preventing Duplicates from Occurring in a Table
- Dealing with Duplicates at Record-Creation Time
- Counting and Identifying Duplicates
- Eliminating Duplicates from a Query Result
- Eliminating Duplicates from a Self-Join Result
- Eliminating Duplicates from a Table
Performing Transactions
- Introduction
- Verifying Transaction Support Requirements
- Performing Transactions Using SQL
- Performing Transactions from Within Programs
- Using Transactions in Perl Programs
- Using Transactions in PHP Programs
- Using Transactions in Python Programs
- Using Transactions in Java Programs
- Using Alternatives to Transactions
Introduction to MySQL on the Web
- Introduction
- Basic Web Page Generation
- Using Apache to Run Web Scripts
- Using Tomcat to Run Web Scripts
- Encoding Special Characters in Web Output
Incorporating Query Resultsinto Web Pages
- Introduction
- Displaying Query Results as Paragraph Text
- Displaying Query Results as Lists
- Displaying Query Results as Tables
- Displaying Query Results as Hyperlinks
- Creating a Navigation Index from Database Content
- Storing Images or Other Binary Data
- Retrieving Images or Other Binary Data
- Serving Banner Ads
- Serving Query Results for Download
Processing Web Input with MySQL
- Introduction
- Creating Forms in Scripts
- Creating Single-Pick Form Elements from Database Content
- Creating Multiple-Pick Form Elements from Database Content
- Loading a Database Record into a Form
- Collecting Web Input
- Validating Web Input
- Using Web Input to Construct Queries
- Processing File Uploads
- Performing Searches and Presenting the Results
- Generating Previous-Page and Next-Page Links
- Generating Click to Sort Table Headings
- Web Page Access Counting
- Web Page Access Logging
- Using MySQL for Apache Logging
Using MySQL-Based Web Session Management
- Introduction
- Using MySQL-Based Sessions in Perl Applications
- Using MySQL-Based Storage with the PHP Session Manager
- Using MySQL for Session BackingStore with Tomcat
Appendix A. Obtaining MySQL Software
- Appendix A. Obtaining MySQL Software
- A.1. Obtaining Sample Source Code and Data
- A.2. Obtaining MySQL and Related Software
Appendix B. JSP and Tomcat Primer
- Appendix B. JSP and Tomcat Primer
- B.1. Servlet and JavaServer Pages Overview
- B.2. Setting Up a Tomcat Server
- B.3. Web Application Structure
- B.4. Elements of JSP Pages
Appendix C. References
