MySQL Cookbook, 2nd Edition

MySQL Cookbook, 2nd Edition

by Paul DuBois
Released November 2006
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596527082

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon
Start your free trial

Book description

Along with MySQL's popularity has come a flood of questions about solving specific problems, and that's where this Cookbook is essential. Designed as a handy resource when you need quick solutions or techniques, the book offers dozens of short, focused pieces of code and hundreds of worked-out examples for programmers of all levels who don't have the time (or expertise) to solve MySQL problems from scratch.

The new edition covers MySQL 5.0 and its powerful new features, as well as the older but still widespread MySQL 4.1. One major emphasis of this book is how to use SQL to formulate queries for particular kinds of questions, using the mysql client program included in MySQL distributions. The other major emphasis is how to write programs that interact with the MySQL server through an API. You'll find plenty of examples using several language APIs in multiple scenarios and situations, including the use of Ruby to retrieve and format data. There are also many new examples for using Perl, PHP, Python, and Java as well.

Other recipes in the book teach you to:



  • Access data from multiple tables at the same time
  • Use SQL to select, sort, and summarize rows
  • Find matches or mismatches between rows in two tables
  • Determine intervals between dates or times, including age calculations
  • Store images into MySQL and retrieve them for display in web pages
  • Get LOAD DATA to read your data files properly or find which values in the file are invalid
  • Use strict mode to prevent entry of bad data into your database
  • Copy a table or a database to another server
  • Generate sequence numbers to use as unique row identifiers
  • Create database events that execute according to a schedule
  • And a lot more

MySQL Cookbook doesn't attempt to develop full-fledged, complex applications. Instead, it's intended to assist you in developing applications yourself by helping you get past problems that have you stumped.

Table of contents

  1. MySQL Cookbook
  2. A Note Regarding Supplemental Files
  3. Preface
    1. Who This Book Is For
    2. What’s in This Book
    3. MySQL APIs Used in This Book
    4. Conventions Used in This Book
    5. The MySQL Cookbook Companion Web Site
    6. Version and Platform Notes
    7. Upgrade Note for First Edition Readers
    8. Additional Resources
    9. Using Code Examples
    10. Safari® Enabled
    11. How to Contact Us
    12. Acknowledgments
      1. Second Edition
      2. First Edition
  4. 1. Using the mysql Client Program
    1. Introduction
    2. Setting Up a MySQL User Account
      1. Problem
      2. Solution
      3. Discussion
    3. Creating a Database and a Sample Table
      1. Problem
      2. Solution
      3. Discussion
    4. Starting and Stopping mysql
      1. Problem
      2. Solution
      3. Discussion
    5. Specifying Connection Parameters Using Option Files
      1. Problem
      2. Solution
      3. Discussion
    6. Protecting Option Files from Other Users
      1. Problem
      2. Solution
      3. Discussion
    7. Mixing Command-Line and Option File Parameters
      1. Problem
      2. Solution
      3. Discussion
    8. What to Do if mysql Cannot Be Found
      1. Problem
      2. Solution
      3. Discussion
    9. Issuing SQL Statements
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    10. Canceling a Partially Entered Statement
      1. Problem
      2. Solution
      3. Discussion
    11. Repeating and Editing SQL Statements
      1. Problem
      2. Solution
      3. Discussion
    12. Using Auto-Completion for Database and Table Names
      1. Problem
      2. Solution
      3. Discussion
    13. Telling mysql to Read Statements from a File
      1. Problem
      2. Solution
      3. Discussion
    14. Telling mysql to Read Statements from Other Programs
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    15. Entering an SQL One-Liner
      1. Problem
      2. Solution
      3. Discussion
    16. Using Copy and Paste as a mysql Input Source
      1. Problem
      2. Solution
      3. Discussion
    17. Preventing Query Output from Scrolling off the Screen
      1. Problem
      2. Solution
      3. Discussion
    18. Sending Query Output to a File or to a Program
      1. Problem
      2. Solution
      3. Discussion
    19. Selecting Tabular or Tab-Delimited Query Output Format
      1. Problem
      2. Solution
      3. Discussion
    20. Specifying Arbitrary Output Column Delimiters
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    21. Producing HTML or XML Output
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    22. Suppressing Column Headings in Query Output
      1. Problem
      2. Solution
      3. Discussion
    23. Making Long Output Lines More Readable
      1. Problem
      2. Solution
      3. Discussion
    24. Controlling mysql’s Verbosity Level
      1. Problem
      2. Solution
      3. Discussion
    25. Logging Interactive mysql Sessions
      1. Problem
      2. Solution
      3. Discussion
    26. Creating mysql Scripts from Previously Executed Statements
      1. Problem
      2. Solution
      3. Discussion
    27. Using User-Defined Variables in SQL Statements
      1. Problem
      2. Solution
      3. Discussion
    28. Numbering Query Output Lines
      1. Problem
      2. Solution
      3. Discussion
    29. Using mysql as a Calculator
      1. Problem
      2. Solution
      3. Discussion
    30. Using mysql in Shell Scripts
      1. Problem
      2. Solution
      3. Discussion
        1. Writing shell scripts under Unix
        2. Writing shell scripts under Windows
  5. 2. Writing MySQL-Based Programs
    1. Introduction
      1. Assumptions
      2. MySQL Client API Architecture
    2. Connecting, Selecting a Database, and Disconnecting
      1. Problem
      2. Solution
      3. Discussion
        1. Perl
        2. Ruby
        3. PHP
        4. Python
        5. Java
    3. Checking for Errors
      1. Problem
      2. Solution
      3. Discussion
        1. Perl
        2. Ruby
        3. PHP
        4. Python
        5. Java
    4. Writing Library Files
      1. Problem
      2. Solution
      3. Discussion
        1. Choosing a library file installation location
        2. Setting library file access privileges
        3. Perl
        4. Ruby
        5. PHP
        6. Python
        7. Java
    5. Issuing Statements and Retrieving Results
      1. Problem
      2. Solution
      3. Discussion
        1. SQL statement categories
        2. Perl
        3. Ruby
        4. PHP
        5. Python
        6. Java
    6. Handling Special Characters and NULL Values in Statements
      1. Problem
      2. Solution
      3. Discussion
        1. Using placeholders
        2. Using a quoting function
        3. Perl
        4. Ruby
        5. PHP
        6. Python
        7. Java
    7. Handling Special Characters in Identifiers
      1. Problem
      2. Solution
      3. Discussion
    8. Identifying NULL Values in Result Sets
      1. Problem
      2. Solution
      3. Discussion
        1. Perl
        2. Ruby
        3. PHP
        4. Python
        5. Java
    9. Techniques for Obtaining Connection Parameters
      1. Problem
      2. Solution
      3. Discussion
        1. Getting parameters from the command line
        2. Getting parameters from option files
    10. Conclusion and Words of Advice
  6. 3. Selecting Data from Tables
    1. Introduction
    2. Specifying Which Columns to Select
      1. Problem
      2. Solution
      3. Discussion
    3. Specifying Which Rows to Select
      1. Problem
      2. Solution
      3. Discussion
    4. Giving Better Names to Query Result Columns
      1. Problem
      2. Solution
      3. Discussion
    5. Using Column Aliases to Make Programs Easier to Write
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    6. Combining Columns to Construct Composite Values
      1. Problem
      2. Solution
      3. Discussion
    7. WHERE Clauses and Column Aliases
      1. Problem
      2. Solution
      3. Discussion
    8. Debugging Comparison Expressions
      1. Problem
      2. Solution
      3. Discussion
    9. Removing Duplicate Rows
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    10. Working with NULL Values
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    11. Writing Comparisons Involving NULL in Programs
      1. Problem
      2. Solution
      3. Discussion
    12. Sorting a Result Set
      1. Problem
      2. Solution
      3. Discussion
    13. Using Views to Simplify Table Access
      1. Problem
      2. Solution
      3. Discussion
    14. Selecting Data from More Than One Table
      1. Problem
      2. Solution
      3. Discussion
    15. Selecting Rows from the Beginning or End of a Result Set
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    16. Selecting Rows from the Middle of a Result Set
      1. Problem
      2. Solution
      3. Discussion
    17. Choosing Appropriate LIMIT Values
      1. Problem
      2. Solution
      3. Discussion
    18. What to Do When LIMIT Requires the Wrong Sort Order
      1. Problem
      2. Solution
      3. Discussion
    19. Calculating LIMIT Values from Expressions
      1. Problem
      2. Solution
      3. Discussion
  7. 4. Table Management
    1. Introduction
    2. Cloning a Table
      1. Problem
      2. Solution
      3. Discussion
    3. Saving a Query Result in a Table
      1. Problem
      2. Solution
      3. Discussion
    4. Creating Temporary Tables
      1. Problem
      2. Solution
      3. Discussion
    5. Checking or Changing a Table’s Storage Engine
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    6. Generating Unique Table Names
      1. Problem
      2. Solution
      3. Discussion
  8. 5. Working with Strings
    1. Introduction
    2. String Properties
    3. Choosing a String Data Type
      1. Problem
      2. Solution
      3. Discussion
    4. Setting the Client Connection Character Set Properly
      1. Problem
      2. Solution
      3. Discussion
    5. Writing String Literals
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    6. Checking a String’s Character Set or Collation
      1. Problem
      2. Solution
      3. Discussion
    7. Changing a String’s Character Set or Collation
      1. Problem
      2. Solution
      3. Discussion
    8. Converting the Lettercase of a String
      1. Problem
      2. Solution
      3. Discussion
    9. Converting the Lettercase of a Stubborn String
      1. Problem
      2. Solution
      3. Discussion
    10. Controlling Case Sensitivity in String Comparisons
      1. Problem
      2. Solution
      3. Discussion
    11. Pattern Matching with SQL Patterns
      1. Problem
      2. Solution
      3. Discussion
    12. Pattern Matching with Regular Expressions
      1. Problem
      2. Solution
      3. Discussion
    13. Controlling Case Sensitivity in Pattern Matching
      1. Problem
      2. Solution
      3. Discussion
    14. Breaking Apart or Combining Strings
      1. Problem
      2. Solution
      3. Discussion
    15. Searching for Substrings
      1. Problem
      2. Solution
      3. Discussion
    16. Using FULLTEXT Searches
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    17. Using a FULLTEXT Search with Short Words
      1. Problem
      2. Solution
      3. Discussion
    18. Requiring or Excluding FULLTEXT Search Words
      1. Problem
      2. Solution
      3. Discussion
    19. Performing Phrase Searches with a FULLTEXT Index
      1. Problem
      2. Solution
      3. Discussion
  9. 6. Working with Dates and Times
    1. Introduction
    2. Choosing a Temporal Data Type
      1. Problem
      2. Solution
      3. Discussion
    3. Changing MySQL’s Date Format
      1. Problem
      2. Solution
      3. Discussion
    4. Setting the Client Time Zone
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    5. Determining the Current Date or Time
      1. Problem
      2. Solution
      3. Discussion
    6. Using TIMESTAMP to Track Row Modification Times
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    7. Extracting Parts of Dates or Times
      1. Problem
      2. Solution
      3. Discussion
        1. Decomposing dates or times using component-extraction functions
        2. Decomposing dates or times using formatting functions
        3. Decomposing dates or times using string functions
    8. Synthesizing Dates or Times from Component Values
      1. Problem
      2. Solution
      3. Discussion
    9. Converting Between Temporal Data Types and Basic Units
      1. Problem
      2. Solution
      3. Discussion
        1. Converting between times and seconds
        2. Converting between dates and days
        3. Converting between date-and-time values and seconds
    10. Calculating the Interval Between Two Dates or Times
      1. Problem
      2. Solution
      3. Discussion
        1. Calculating intervals with temporal-difference functions
        2. Calculating intervals using basic units
        3. Time interval calculation using basic units
        4. Date or date-and-time interval calculation using basic units
    11. Adding Date or Time Values
      1. Problem
      2. Solution
      3. Discussion
        1. Adding temporal values using temporal-addition functions or operators
        2. Adding temporal values using basic units
    12. Calculating Ages
      1. Problem
      2. Solution
      3. Discussion
    13. Shifting a Date-and-Time Value to a Different Time Zone
      1. Problem
      2. Solution
      3. Discussion
    14. Finding the First Day, Last Day, or Length of a Month
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    15. Calculating Dates by Substring Replacement
      1. Problem
      2. Solution
      3. Discussion
    16. Finding the Day of the Week for a Date
      1. Problem
      2. Solution
      3. Discussion
    17. Finding Dates for Any Weekday of a Given Week
      1. Problem
      2. Solution
      3. Discussion
    18. Performing Leap Year Calculations
      1. Problem
      2. Solution
      3. Discussion
        1. Determining whether a date occurs in a leap year
        2. Using leap year tests for year-length calculations
        3. Using leap year tests for month-length calculations
    19. Canonizing Not-Quite-ISO Date Strings
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    20. Treating Dates or Times as Numbers
      1. Problem
      2. Solution
      3. Discussion
    21. Forcing MySQL to Treat Strings as Temporal Values
      1. Problem
      2. Solution
      3. Discussion
    22. Selecting Rows Based on Their Temporal Characteristics
      1. Problem
      2. Solution
      3. Discussion
        1. Comparing dates to one another
        2. Comparing times to one another
        3. Comparing dates to calendar days
  10. 7. Sorting Query Results
    1. Introduction
    2. Using ORDER BY to Sort Query Results
      1. Problem
      2. Solution
      3. Discussion
    3. Using Expressions for Sorting
      1. Problem
      2. Solution
      3. Discussion
    4. Displaying One Set of Values While Sorting by Another
      1. Problem
      2. Solution
      3. Discussion
    5. Controlling Case Sensitivity of String Sorts
      1. Problem
      2. Solution
      3. Discussion
    6. Date-Based Sorting
      1. Problem
      2. Solution
      3. Discussion
    7. Sorting by Calendar Day
      1. Problem
      2. Solution
      3. Discussion
    8. Sorting by Day of Week
      1. Problem
      2. Solution
      3. Discussion
    9. Sorting by Time of Day
      1. Problem
      2. Solution
      3. Discussion
    10. Sorting Using Substrings of Column Values
      1. Problem
      2. Solution
      3. Discussion
    11. Sorting by Fixed-Length Substrings
      1. Problem
      2. Solution
      3. Discussion
    12. Sorting by Variable-Length Substrings
      1. Problem
      2. Solution
      3. Discussion
    13. Sorting Hostnames in Domain Order
      1. Problem
      2. Solution
      3. Discussion
    14. Sorting Dotted-Quad IP Values in Numeric Order
      1. Problem
      2. Solution
      3. Discussion
    15. Floating Values to the Head or Tail of the Sort Order
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    16. Sorting in User-Defined Orders
      1. Problem
      2. Solution
      3. Discussion
    17. Sorting ENUM Values
      1. Problem
      2. Solution
      3. Discussion
  11. 8. Generating Summaries
    1. Introduction
    2. Summarizing with COUNT()
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    3. Summarizing with MIN() and MAX()
      1. Problem
      2. Solution
      3. Discussion
    4. Summarizing with SUM() and AVG()
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    5. Using DISTINCT to Eliminate Duplicates
      1. Problem
      2. Solution
      3. Discussion
    6. Finding Values Associated with Minimum and Maximum Values
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    7. Controlling String Case Sensitivity for MIN() and MAX()
      1. Problem
      2. Solution
      3. Discussion
    8. Dividing a Summary into Subgroups
      1. Problem
      2. Solution
      3. Discussion
    9. Summaries and NULL Values
      1. Problem
      2. Solution
      3. Discussion
    10. Selecting Only Groups with Certain Characteristics
      1. Problem
      2. Solution
      3. Discussion
    11. Using Counts to Determine Whether Values Are Unique
      1. Problem
      2. Solution
      3. Discussion
    12. Grouping by Expression Results
      1. Problem
      2. Solution
      3. Discussion
    13. Categorizing Noncategorical Data
      1. Problem
      2. Solution
      3. Discussion
    14. Controlling Summary Display Order
      1. Problem
      2. Solution
      3. Discussion
    15. Finding Smallest or Largest Summary Values
      1. Problem
      2. Solution
      3. Discussion
    16. Date-Based Summaries
      1. Problem
      2. Solution
      3. Discussion
    17. Working with Per-Group and Overall Summary Values Simultaneously
      1. Problem
      2. Solution
      3. Discussion
    18. Generating a Report That Includes a Summary and a List
      1. Problem
      2. Solution
      3. Discussion
  12. 9. Obtaining and Using Metadata
    1. Introduction
    2. Obtaining the Number of Rows Affected by a Statement
      1. Problem
      2. Solution
      3. Discussion
        1. Perl
        2. Ruby
        3. PHP
        4. Python
        5. Java
    3. Obtaining Result Set Metadata
      1. Problem
      2. Solution
      3. Discussion
        1. Perl
        2. Ruby
        3. PHP
        4. Python
        5. Java
    4. Determining Whether a Statement Produced a Result Set
      1. Problem
      2. Solution
      3. Discussion
    5. Using Metadata to Format Query Output
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    6. Listing or Checking Existence of Databases or Tables
      1. Problem
      2. Solution
      3. Discussion
    7. Accessing Table Column Definitions
      1. Problem
      2. Solution
      3. Discussion
        1. Using INFORMATION_SCHEMA to get table structure
        2. Using SHOW COLUMNS to get table structure
        3. Using CREATE TABLE to get table structure
    8. Getting ENUM and SET Column Information
      1. Problem
      2. Solution
      3. Discussion
    9. Using Table Structure Information in Applications
      1. Problem
      2. Solution
      3. Discussion
        1. Displaying column lists
        2. Interactive record editing
        3. Mapping column definitions onto web page elements
        4. Adding elements to ENUM or SET column definitions
        5. Selecting all except certain columns
    10. Getting Server Metadata
      1. Problem
      2. Solution
      3. Discussion
    11. Writing Applications That Adapt to the MySQL Server Version
      1. Problem
      2. Solution
      3. Discussion
    12. Determining the Default Database
      1. Problem
      2. Solution
      3. Discussion
    13. Monitoring the MySQL Server
      1. Problem
      2. Solution
      3. Discussion
    14. Determining Which Storage Engines the Server Supports
      1. Problem
      2. Solution
      3. Discussion
  13. 10. Importing and Exporting Data
    1. Introduction
      1. General Import and Export Issues
      2. File Formats
      3. Notes on Invoking Shell Commands
    2. Importing Data with LOAD DATA and mysqlimport
      1. Problem
      2. Solution
      3. Discussion
    3. Specifying the Datafile Location
      1. Problem
      2. Solution
      3. Discussion
    4. Specifying the Structure of the Datafile
      1. Problem
      2. Solution
      3. Discussion
    5. Dealing with Quotes and Special Characters
      1. Problem
      2. Solution
      3. Discussion
    6. Importing CSV Files
      1. Problem
      2. Solution
      3. Discussion
    7. Reading Files from Different Operating Systems
      1. Problem
      2. Solution
      3. Discussion
    8. Handling Duplicate Key Values
      1. Problem
      2. Solution
      3. Discussion
    9. Obtaining Diagnostics About Bad Input Data
      1. Problem
      2. Solution
      3. Discussion
    10. Skipping Datafile Lines
      1. Problem
      2. Solution
      3. Discussion
    11. Specifying Input Column Order
      1. Problem
      2. Solution
      3. Discussion
    12. Preprocessing Input Values Before Inserting Them
      1. Problem
      2. Solution
      3. Discussion
    13. Ignoring Datafile Columns
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    14. Exporting Query Results from MySQL
      1. Problem
      2. Solution
      3. Discussion
        1. Exporting with the SELECT ... INTO OUTFILE statement
        2. Using the mysql client to export data
      4. See Also
    15. Exporting Tables as Text Files
      1. Problem
      2. Solution
      3. Discussion
    16. Exporting Table Contents or Definitions in SQL Format
      1. Problem
      2. Solution
      3. Discussion
    17. Copying Tables or Databases to Another Server
      1. Problem
      2. Solution
      3. Discussion
    18. Writing Your Own Export Programs
      1. Problem
      2. Solution
      3. Discussion
    19. Converting Datafiles from One Format to Another
      1. Problem
      2. Solution
      3. Discussion
    20. Extracting and Rearranging Datafile Columns
      1. Problem
      2. Solution
      3. Discussion
    21. Using the SQL Mode to Control Bad Input Data Handling
      1. Problem
      2. Solution
      3. Discussion
    22. Validating and Transforming Data
      1. Problem
      2. Solution
      3. Discussion
        1. Writing an input-processing loop
        2. Putting common tests in libraries
    23. Using Pattern Matching to Validate Data
      1. Problem
      2. Solution
      3. Discussion
    24. Using Patterns to Match Broad Content Types
      1. Problem
      2. Solution
      3. Discussion
    25. Using Patterns to Match Numeric Values
      1. Problem
      2. Solution
      3. Discussion
    26. Using Patterns to Match Dates or Times
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    27. Using Patterns to Match Email Addresses or URLs
      1. Problem
      2. Solution
      3. Discussion
    28. Using Table Metadata to Validate Data
      1. Problem
      2. Solution
      3. Discussion
    29. Using a Lookup Table to Validate Data
      1. Problem
      2. Solution
      3. Discussion
        1. Issue individual statements
        2. Construct a hash from the entire lookup table
        3. Use a hash as a cache of already-seen lookup values
    30. Converting Two-Digit Year Values to Four-Digit Form
      1. Problem
      2. Solution
      3. Discussion
    31. Performing Validity Checking on Date or Time Subparts
      1. Problem
      2. Solution
      3. Discussion
    32. Writing Date-Processing Utilities
      1. Problem
      2. Solution
      3. Discussion
    33. Using Dates with Missing Components
      1. Problem
      2. Solution
      3. Discussion
    34. Importing Non-ISO Date Values
      1. Problem
      2. Solution
      3. Discussion
    35. Exporting Dates Using Non-ISO Formats
      1. Problem
      2. Solution
      3. Discussion
    36. Importing and Exporting NULL Values
      1. Problem
      2. Solution
      3. Discussion
    37. Guessing Table Structure from a Datafile
      1. Problem
      2. Solution
      3. Discussion
    38. Exchanging Data Between MySQL and Microsoft Access
      1. Problem
      2. Solution
      3. Discussion
    39. Exchanging Data Between MySQL and Microsoft Excel
      1. Problem
      2. Solution
      3. Discussion
    40. Exporting Query Results as XML
      1. Problem
      2. Solution
      3. Discussion
    41. Importing XML into MySQL
      1. Problem
      2. Solution
      3. Discussion
    42. Epilogue
  14. 11. Generating and Using Sequences
    1. Introduction
    2. Creating a Sequence Column and Generating Sequence Values
      1. Problem
      2. Solution
      3. Discussion
    3. Choosing the Data Type for a Sequence Column
      1. Problem
      2. Solution
      3. Discussion
    4. The Effect of Row Deletions on Sequence Generation
      1. Problem
      2. Solution
      3. Discussion
    5. Retrieving Sequence Values
      1. Problem
      2. Solution
      3. Discussion
        1. Using LAST_INSERT_ID() to obtain AUTO_INCREMENT values
        2. Using API-specific methods to obtain AUTO_INCREMENT values
        3. Server-side and client-side sequence value retrieval compared
    6. Renumbering an Existing Sequence
      1. Problem
      2. Solution
      3. Discussion
    7. Extending the Range of a Sequence Column
      1. Problem
      2. Solution
      3. Discussion
    8. Reusing Values at the Top of a Sequence
      1. Problem
      2. Solution
      3. Discussion
    9. Ensuring That Rows Are Renumbered in a Particular Order
      1. Problem
      2. Solution
      3. Discussion
    10. Starting a Sequence at a Particular Value
      1. Problem
      2. Solution
      3. Discussion
    11. Sequencing an Unsequenced Table
      1. Problem
      2. Solution
      3. Discussion
    12. Using an AUTO_INCREMENT Column to Create Multiple Sequences
      1. Problem
      2. Solution
      3. Discussion
    13. Managing Multiple Simultaneous AUTO_INCREMENT Values
      1. Problem
      2. Solution
      3. Discussion
    14. Using AUTO_INCREMENT Values to Relate Tables
      1. Problem
      2. Solution
      3. Discussion
    15. Using Sequence Generators as Counters
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    16. Generating Repeating Sequences
      1. Problem
      2. Solution
      3. Discussion
    17. Numbering Query Output Rows Sequentially
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
  15. 12. Using Multiple Tables
    1. Introduction
    2. Finding Rows in One Table That Match Rows in Another
      1. Problem
      2. Solution
      3. Discussion
    3. Finding Rows with No Match in Another Table
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    4. Comparing a Table to Itself
      1. Problem
      2. Solution
      3. Discussion
    5. Producing Master-Detail Lists and Summaries
      1. Problem
      2. Solution
      3. Discussion
    6. Enumerating a Many-to-Many Relationship
      1. Problem
      2. Solution
      3. Discussion
    7. Finding Rows Containing Per-Group Minimum or Maximum Values
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    8. Computing Team Standings
      1. Problem
      2. Solution
      3. Discussion
    9. Using a Join to Fill or Identify Holes in a List
      1. Problem
      2. Solution
      3. Discussion
    10. Calculating Successive-Row Differences
      1. Problem
      2. Solution
      3. Discussion
    11. Finding Cumulative Sums and Running Averages
      1. Problem
      2. Solution
      3. Discussion
    12. Using a Join to Control Query Output Order
      1. Problem
      2. Solution
      3. Discussion
    13. Combining Several Result Sets in a Single Query
      1. Problem
      2. Solution
      3. Discussion
    14. Identifying and Removing Mismatched or Unattached Rows
      1. Problem
      2. Solution
      3. Discussion
    15. Performing a Join Between Tables in Different Databases
      1. Problem
      2. Solution
      3. Discussion
    16. Using Different MySQL Servers Simultaneously
      1. Problem
      2. Solution
      3. Discussion
    17. Referring to Join Output Column Names in Programs
      1. Problem
      2. Solution
      3. Discussion
  16. 13. Statistical Techniques
    1. Introduction
    2. Calculating Descriptive Statistics
      1. Problem
      2. Solution
      3. Discussion
    3. Per-Group Descriptive Statistics
      1. Problem
      2. Solution
      3. Discussion
    4. Generating Frequency Distributions
      1. Problem
      2. Solution
      3. Discussion
    5. Counting Missing Values
      1. Problem
      2. Solution
      3. Discussion
    6. Calculating Linear Regressions or Correlation Coefficients
      1. Problem
      2. Solution
      3. Discussion
    7. Generating Random Numbers
      1. Problem
      2. Solution
      3. Discussion
    8. Randomizing a Set of Rows
      1. Problem
      2. Solution
      3. Discussion
    9. Selecting Random Items from a Set of Rows
      1. Problem
      2. Solution
      3. Discussion
    10. Assigning Ranks
      1. Problem
      2. Solution
      3. Discussion
  17. 14. Handling Duplicates
    1. Introduction
    2. Preventing Duplicates from Occurring in a Table
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    3. Dealing with Duplicates When Loading Rows into a Table
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    4. Counting and Identifying Duplicates
      1. Problem
      2. Solution
      3. Discussion
    5. Eliminating Duplicates from a Table
      1. Problem
      2. Solution
      3. Discussion
        1. Removing duplicates using table replacement
        2. Removing duplicates by adding an index
        3. Removing duplicates of a particular row
    6. Eliminating Duplicates from a Self-Join Result
      1. Problem
      2. Solution
      3. Discussion
  18. 15. Performing Transactions
    1. Introduction
    2. Choosing a Transactional Storage Engine
      1. Problem
      2. Solution
      3. Discussion
    3. Performing Transactions Using SQL
      1. Problem
      2. Solution
      3. Discussion
    4. Performing Transactions from Within Programs
      1. Problem
      2. Solution
      3. Discussion
    5. Using Transactions in Perl Programs
      1. Problem
      2. Solution
      3. Discussion
    6. Using Transactions in Ruby Programs
      1. Problem
      2. Solution
      3. Discussion
    7. Using Transactions in PHP Programs
      1. Problem
      2. Solution
      3. Discussion
    8. Using Transactions in Python Programs
      1. Problem
      2. Solution
      3. Discussion
    9. Using Transactions in Java Programs
      1. Problem
      2. Solution
      3. Discussion
    10. Using Alternatives to Transactions
      1. Problem
      2. Solution
      3. Discussion
        1. Grouping statements using locks
        2. Rewriting statements to avoid transactions
  19. 16. Using Stored Routines, Triggers, and Events
    1. Introduction
    2. Creating Compound-Statement Objects
      1. Problem
      2. Solution
      3. Discussion
    3. Using a Stored Function to Encapsulate a Calculation
      1. Problem
      2. Solution
      3. Discussion
    4. Using a Stored Procedure to Return Multiple Values
      1. Problem
      2. Solution
      3. Discussion
    5. Using a Trigger to Define Dynamic Default Column Values
      1. Problem
      2. Solution
      3. Discussion
    6. Simulating TIMESTAMP Properties for Other Date and Time Types
      1. Problem
      2. Solution
      3. Discussion
    7. Using a Trigger to Log Changes to a Table
      1. Problem
      2. Solution
      3. Discussion
    8. Using Events to Schedule Database Actions
      1. Problem
      2. Solution
      3. Discussion
  20. 17. Introduction to MySQL on the Web
    1. Introduction
    2. Basic Principles of Web Page Generation
      1. Problem
      2. Solution
      3. Discussion
    3. Using Apache to Run Web Scripts
      1. Problem
      2. Solution
      3. Discussion
        1. Perl
        2. Ruby
        3. PHP
        4. Python
    4. Using Tomcat to Run Web Scripts
      1. Problem
      2. Solution
      3. Discussion
        1. Installing the mcb application
        2. Installing the JDBC driver
        3. Installing the JSTL distribution
        4. Writing JSP pages with JSTL
        5. Writing a MySQL script using JSP and JSTL
    5. Encoding Special Characters in Web Output
      1. Problem
      2. Solution
      3. Discussion
        1. General encoding principles
        2. Encoding special characters using web APIs
  21. 18. Incorporating Query Results into Web Pages
    1. Introduction
    2. Displaying Query Results as Paragraph Text
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    3. Displaying Query Results as Lists
      1. Problem
      2. Solution
      3. Discussion
        1. Ordered lists
        2. Unordered lists
        3. Definition lists
        4. Unmarked lists
        5. Nested lists
      4. See Also
    4. Displaying Query Results as Tables
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    5. Displaying Query Results as Hyperlinks
      1. Problem
      2. Solution
      3. Discussion
    6. Creating a Navigation Index from Database Content
      1. Problem
      2. Solution
      3. Discussion
        1. Creating a single-page navigation index
        2. Creating a multiple-page navigation index
      4. See Also
    7. Storing Images or Other Binary Data
      1. Problem
      2. Solution
      3. Discussion
        1. Storing images with LOAD_FILE()
        2. Storing images using a script
      4. See Also
    8. Retrieving Images or Other Binary Data
      1. Problem
      2. Solution
      3. Discussion
    9. Serving Banner Ads
      1. Problem
      2. Solution
      3. Discussion
    10. Serving Query Results for Download
      1. Problem
      2. Solution
      3. Discussion
    11. Using a Template System to Generate Web Pages
      1. Problem
      2. Solution
      3. Discussion
        1. Using page template for web page generation in Ruby
        2. Using Smarty for web page generation in PHP
  22. 19. Processing Web Input with MySQL
    1. Introduction
    2. Writing Scripts That Generate Web Forms
      1. Problem
      2. Solution
      3. Discussion
        1. Perl
        2. Ruby
        3. PHP
        4. Python
        5. Java
      4. See Also
    3. Creating Single-Pick Form Elements from Database Content
      1. Problem
      2. Solution
      3. Discussion
    4. Creating Multiple-Pick Form Elements from Database Content
      1. Problem
      2. Solution
      3. Discussion
    5. Loading a Database Record into a Form
      1. Problem
      2. Solution
      3. Discussion
    6. Collecting Web Input
      1. Problem
      2. Solution
      3. Discussion
        1. Web input extraction conventions
    7. Validating Web Input
      1. Problem
      2. Solution
      3. Discussion
    8. Storing Web Input in a Database
      1. Problem
      2. Solution
      3. Discussion
      4. See Also
    9. Processing File Uploads
      1. Problem
      2. Solution
      3. Discussion
        1. Uploads in Perl
        2. Uploads in PHP
        3. Uploads in Python
    10. Performing Searches and Presenting the Results
      1. Problem
      2. Solution
      3. Discussion
    11. Generating Previous-Page and Next-Page Links
      1. Problem
      2. Solution
      3. Discussion
        1. Paged displays with previous-page and next-page links
        2. Paged displays with links to each page
    12. Generating Click to Sort Table Headings
      1. Problem
      2. Solution
      3. Discussion
    13. Web Page Access Counting
      1. Problem
      2. Solution
      3. Discussion
    14. Web Page Access Logging
      1. Problem
      2. Solution
      3. Discussion
    15. Using MySQL for Apache Logging
      1. Problem
      2. Solution
      3. Discussion
        1. Setting up database logging
        2. Analyzing the logfile
        3. Other logging issues
  23. 20. Using MySQL-Based Web Session Management
    1. Introduction
      1. Session Management Issues
    2. Using MySQL-Based Sessions in Perl Applications
      1. Problem
      2. Solution
      3. Discussion
        1. Installing Apache::Session
        2. The Apache::Session interface
        3. A sample application
        4. Session expiration
    3. Using MySQL-Based Storage in Ruby Applications
      1. Problem
      2. Solution
      3. Discussion
    4. Using MySQL-Based Storage with the PHP Session Manager
      1. Problem
      2. Solution
      3. Discussion
        1. The PHP session management interface
        2. Specifying a user-defined storage module
    5. Using MySQL for Session-Backing Store with Tomcat
      1. Problem
      2. Solution
      3. Discussion
        1. The Servlet and JSP Session Interface
        2. A sample JSP session application
        3. Telling Tomcat to save session records in MySQL
        4. Session expiration in Tomcat
        5. Session tracking in Tomcat
  24. A. Obtaining MySQL Software
    1. Obtaining Sample Source Code and Data
    2. Obtaining MySQL and Related Software
      1. MySQL
      2. Perl Support
      3. Ruby Support
      4. PHP Support
      5. Python Support
      6. Java Support
      7. Web Servers
  25. B. Executing Programs from the Command Line
    1. Setting Environment Variables
      1. Setting the PATH Variable on Unix
      2. Setting the PATH Variable on Windows
    2. Executing Programs
      1. Executing Perl, Ruby, PHP, or Python Scripts
      2. Compiling and Executing Java Programs
  26. C. JSP and Tomcat Primer
    1. Servlet and JavaServer Pages Overview
      1. JSP Pages: An Alternative to Servlets
      2. Custom Actions and Tag Libraries
    2. Setting Up a Tomcat Server
      1. Installing a Tomcat Distribution
      2. Starting and Stopping Tomcat
    3. Tomcat’s Directory Structure
      1. Application Directories
      2. Configuration and Control Directories
      3. Class Directories
      4. Operational Directories
    4. Restarting Applications Without Restarting Tomcat
    5. Web Application Structure
    6. Elements of JSP Pages
      1. Scripting Elements
      2. JSP Directives
      3. Action Elements
      4. Using a Tag Library
      5. Implicit JSP Objects
      6. Levels of Scope in JSP Pages
  27. D. References
    1. MySQL Resources
      1. Bibliography
    2. Perl Resources
      1. Bibliography
    3. Ruby Resources
      1. Bibliography
    4. PHP Resources
      1. Bibliography
    5. Python Resources
      1. Bibliography
    6. Java Resources
      1. Bibliography
    7. Other Resources
      1. Bibliography
  28. Index
  29. About the Author
  30. Colophon
  31. Copyright

Product information

  • Title: MySQL Cookbook, 2nd Edition
  • Author(s): Paul DuBois
  • Release date: November 2006
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596527082