MySQL Cookbook

Paul DuBois was one of the first contributors to the online MySQL Reference Manual, a renowned documentation project that supported MySQL administrators and database developers in the first few years of MySQL's existence in the late 1990's. Paul went on to write more than six books on MySQL, including the first edition of "MySQL Cookbook". He is also the author of "Using csh & tcsh" and "Software Portability with imake", both by O'Reilly.

Chapter 1Using the mysql Client Program
Introduction
Setting Up a MySQL User Account
Creating a Database and a Sample Table
What to Do if mysql Cannot Be Found
Specifying mysql Command Options
Executing SQL Statements Interactively
Executing SQL Statements Read from a File or Program
Controlling mysql Output Destination and Format
Using User-Defined Variables in SQL Statements
Chapter 2Writing MySQL-Based Programs
Introduction
Connecting, Selecting a Database, and Disconnecting
Checking for Errors
Writing Library Files
Executing Statements and Retrieving Results
Handling Special Characters and NULL Values in Statements
Handling Special Characters in Identifiers
Identifying NULL Values in Result Sets
Techniques for Obtaining Connection Parameters
Conclusion and Words of Advice
Chapter 3Selecting Data from Tables
Introduction
Specifying Which Columns and Rows to Select
Naming Query Result Columns
Sorting Query Results
Removing Duplicate Rows
Working with NULL Values
Writing Comparisons Involving NULL in Programs
Using Views to Simplify Table Access
Selecting Data from Multiple Tables
Selecting Rows from the Beginning, End, or Middle of Query Results
What to Do When LIMIT Requires the “Wrong” Sort Order
Calculating LIMIT Values from Expressions
Chapter 4Table Management
Introduction
Cloning a Table
Saving a Query Result in a Table
Creating Temporary Tables
Generating Unique Table Names
Checking or Changing a Table Storage Engine
Copying a Table Using mysqldump
Chapter 5Working with Strings
Introduction
String Properties
Choosing a String Data Type
Setting the Client Connection Character Set
Writing String Literals
Checking or Changing a String’s Character Set or Collation
Converting the Lettercase of a String
Controlling Case Sensitivity in String Comparisons
Pattern Matching with SQL Patterns
Pattern Matching with Regular Expressions
Breaking Apart or Combining Strings
Searching for Substrings
Using Full-Text Searches
Using a Full-Text Search with Short Words
Requiring or Prohibiting Full-Text Search Words
Performing Full-Text Phrase Searches
Chapter 6Working with Dates and Times
Introduction
Choosing a Temporal Data Type
Using Fractional Seconds Support
Changing MySQL’s Date Format
Setting the Client Time Zone
Shifting Temporal Values Between Time Zones
Determining the Current Date or Time
Using TIMESTAMP or DATETIME to Track Row-Modification Times
Extracting Parts of Dates or Times
Synthesizing Dates or Times from Component Values
Converting Between Temporal Values and Basic Units
Calculating Intervals Between Dates or Times
Adding Date or Time Values
Calculating Ages
Finding the First Day, Last Day, or Length of a Month
Calculating Dates by Substring Replacement
Finding the Day of the Week for a Date
Finding Dates for Any Weekday of a Given Week
Performing Leap-Year Calculations
Canonizing Not-Quite-ISO Date Strings
Selecting Rows Based on Temporal Characteristics
Chapter 7Sorting Query Results
Introduction
Using ORDER BY to Sort Query Results
Using Expressions for Sorting
Displaying One Set of Values While Sorting by Another
Controlling Case Sensitivity of String Sorts
Date-Based Sorting
Sorting by 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 Values to the Head or Tail of the Sort Order
Defining a Custom Sort Order
Sorting ENUM Values
Chapter 8Generating Summaries
Introduction
Basic Summary Techniques
Creating a View to Simplify Using a Summary
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
Using Counts to Determine Whether Values Are Unique
Grouping by Expression Results
Summarizing Noncategorical Data
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
Chapter 9Using Stored Routines, Triggers, and Scheduled Events
Introduction
Creating Compound-Statement Objects
Using Stored Functions to Encapsulate Calculations
Using Stored Procedures to “Return” Multiple Values
Using Triggers to Implement Dynamic Default Column Values
Using Triggers to Simulate Function-Based Indexes
Simulating TIMESTAMP Properties for Other Date and Time Types
Using Triggers to Log Changes to a Table
Using Events to Schedule Database Actions
Writing Helper Routines for Executing Dynamic SQL
Handling Errors Within Stored Programs
Using Triggers to Preprocess or Reject Data
Chapter 10Working with Metadata
Introduction
Determining the Number of Rows Affected by a Statement
Obtaining Result Set Metadata
Determining Whether a Statement Produced a Result Set
Using Metadata to Format Query Output
Listing or Checking Existence of Databases or Tables
Accessing Table Column Definitions
Getting ENUM and SET Column Information
Getting Server Metadata
Writing Applications That Adapt to the MySQL Server Version
Chapter 11Importing and Exporting Data
Introduction
Importing Data with LOAD DATA and mysqlimport
Importing CSV Files
Exporting Query Results from MySQL
Importing and Exporting NULL Values
Writing Your Own Data Export Programs
Converting Datafiles from One Format to Another
Extracting and Rearranging Datafile Columns
Exchanging Data Between MySQL and Microsoft Excel
Exporting Query Results as XML
Importing XML into MySQL
Guessing Table Structure from a Datafile
Chapter 12Validating and Reformatting Data
Introduction
Using the SQL Mode to Reject Bad Input Values
Validating and Transforming Data
Using Pattern Matching to Validate Data
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 or URLs
Using Table Metadata to Validate Data
Using a Lookup Table to Validate Data
Converting Two-Digit Year Values to Four-Digit Form
Performing Validity Checking on Date or Time Subparts
Writing Date-Processing Utilities
Importing Non-ISO Date Values
Exporting Dates Using Non-ISO Formats
Epilogue
Chapter 13Generating and Using Sequences
Introduction
Creating a Sequence Column and Generating Sequence Values
Choosing the Definition for a Sequence Column
The Effect of Row Deletions on Sequence Generation
Retrieving Sequence Values
Renumbering an Existing Sequence
Extending the Range of a Sequence Column
Reusing Values at the Top of a Sequence
Ensuring That Rows Are Renumbered in a Particular Order
Sequencing an Unsequenced Table
Managing Multiple Auto-Increment Values Simultaneously
Using Auto-Increment Values to Associate Tables
Using Sequence Generators as Counters
Generating Repeating Sequences
Chapter 14Using Joins and Subqueries
Introduction
Finding Matches Between Tables
Finding Mismatches Between Tables
Identifying and Removing Mismatched or Unattached Rows
Comparing a Table to Itself
Producing Master-Detail Lists and Summaries
Enumerating a Many-to-Many Relationship
Finding Per-Group Minimum or Maximum Values
Using a Join to Fill or Identify Holes in a List
Using a Join to Control Query Sort Order
Referring to Join Output Column Names in Programs
Chapter 15Statistical 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
Calculating Successive-Row Differences
Finding Cumulative Sums and Running Averages
Assigning Ranks
Computing Team Standings
Chapter 16Handling Duplicates
Introduction
Preventing Duplicates from Occurring in a Table
Dealing with Duplicates When Loading Rows into a Table
Counting and Identifying Duplicates
Eliminating Duplicates from a Table
Chapter 17Performing Transactions
Introduction
Choosing a Transactional Storage Engine
Performing Transactions Using SQL
Performing Transactions from Within Programs
Using Transactions in Perl Programs
Using Transactions in Ruby Programs
Using Transactions in PHP Programs
Using Transactions in Python Programs
Using Transactions in Java Programs
Chapter 18Introduction to MySQL on the Web
Introduction
Basic Principles of Web Page Generation
Using Apache to Run Web Scripts
Using Tomcat to Run Web Scripts
Encoding Special Characters in Web Output
Chapter 19Generating Web Content from Query Results
Introduction
Displaying Query Results as Paragraphs
Displaying Query Results as Lists
Displaying Query Results as Tables
Displaying Query Results as Hyperlinks
Creating Navigation Indexes from Database Content
Storing Images or Other Binary Data
Serving Images or Other Binary Data
Serving Banner Ads
Serving Query Results for Download
Chapter 20Processing Web Input with MySQL
Introduction
Writing Scripts That Generate Web Forms
Creating Single-Pick Form Elements from Database Content
Creating Multiple-Pick Form Elements from Database Content
Loading Database Content into a Form
Collecting Web Input
Validating Web Input
Storing Web Input in a Database
Processing File Uploads
Performing Web-Based Database Searches
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
Chapter 21Using MySQL-Based Web Session Management
Introduction
Using MySQL-Based Sessions in Perl Applications
Using MySQL-Based Storage in Ruby Applications
Using MySQL-Based Storage with the PHP Session Manager
Using MySQL for Session-Backing Store with Tomcat
Chapter 22Server Administration
Introduction
Configuring the Server
Managing the Plug-In Interface
Controlling Server Logging
Rotating or Expiring Logfiles
Rotating Log Tables or Expiring Log Table Rows
Monitoring the MySQL Server
Creating and Using Backups
Chapter 23Security
Introduction
Understanding the mysql.user Table
Managing User Accounts
Implementing a Password Policy
Checking Password Strength
Expiring Passwords
Assigning Yourself a New Password
Resetting an Expired Password
Finding and Fixing Insecure Accounts
Disabling Use of Accounts with Pre-4.1 Passwords
Finding and Removing Anonymous Accounts
Modifying “Any Host” and “Many Host” Accounts