MySQL Cookbook

Preface
	Who This Book Is For
	What’s in This Book
	MySQL APIs Used in This Book
	Version and Platform Notes
	Conventions Used in This Book
	The MySQL Cookbook Companion GitHub Repository
	Obtaining MySQL and Related Software
	Using Code Examples
	Safari® Books Online
	How to Contact Us
	Acknowledgments
1. Using the mysql Client Program
	1.0. Introduction
	1.1. Setting Up a MySQL User Account
	1.2. Creating a Database and a Sample Table
	1.3. What to Do if mysql Cannot Be Found
	1.4. Specifying mysql Command Options
	1.5. Executing SQL Statements Interactively
	1.6. Executing SQL Statements Read from a File or Program
	1.7. Controlling mysql Output Destination and Format
	1.8. Using User-Defined Variables in SQL Statements
	1.9. Customizing mysql Prompt
	1.10. Using External Programs
	1.11. Filter and process output
2. MySQL Replication
	2.0. Introduction
	2.1. Configuration of the Basic Replication between One Source and One Replica
	2.2. Position-Based Replication in the New Installation Environment
	2.3. Position-Based Replication for the Source Server that is Already in Use
	2.4. GTID-based Replication
	2.5. Binary Log Format
	2.6. Replication Filters
	2.7. Rewriting Database on the Replica
	2.8. Multithreaded Replica
	2.9. Circular Replication
	2.10. Multisource Replication
	2.11. Semisynchronous Replication
	2.12. Group Replication
	2.13. Storing Replication Credentials Securely
	2.14. Using TLS (SSL) for Replication
	2.15. Replication Troubleshooting
	2.16. Use Processlist to Understand Replication Performance
	2.17. Replication Automation
3. Selecting Data from Tables
	3.0. Introduction
	3.1. Specifying Which Columns and Rows to Select
	3.2. Naming Query Result Columns
	3.3. Sorting Query Results
	3.4. Removing Duplicate Rows
	3.5. Working with NULL Values
	3.6. Writing Comparisons Involving NULL in Programs
	3.7. Using Views to Simplify Table Access
	3.8. Selecting Data from Multiple Tables
	3.9. Selecting Rows from the Beginning, End, or Middle of Query Results
	3.10. What to Do When LIMIT Requires the Wrong Sort Order
	3.11. Calculating LIMIT Values from Expressions
	3.12. Combining Two or More SELECT Results
	3.13. Selecting Results of Subqueries
4. Table Management
	4.0. Introduction
	4.1. Cloning a Table
	4.2. Saving a Query Result in a Table
	4.3. Creating Temporary Tables
	4.4. Generating Unique Table Names
	4.5. Checking or Changing a Table Storage Engine
	4.6. Copying a Table Using mysqldump
	4.7. Copying an InnoDB Table Using Transportable Tablespaces
5. Sorting Query Results
	5.0. Introduction
	5.1. Using ORDER BY to Sort Query Results
	5.2. Using Expressions for Sorting
	5.3. Displaying One Set of Values While Sorting by Another
	5.4. Controlling Case Sensitivity of String Sorts
	5.5. Sorting in Temporal Order
	5.6. Sorting by Substrings of Column Values
	5.7. Sorting by Fixed-Length Substrings
	5.8. Sorting by Variable-Length Substrings
	5.9. Sorting Hostnames in Domain Order
	5.10. Sorting Dotted-Quad IP Values in Numeric Order
	5.11. Floating Values to the Head or Tail of the Sort Order
	5.12. Defining a Custom Sort Order
	5.13. Sorting ENUM Values
6. Generating Summaries
	6.0. Introduction
	6.1. Summarizing with COUNT()
	6.2. Summarizing with MIN() and MAX()
	6.3. Summarizing with SUM() and AVG()
	6.4. Using DISTINCT to eliminate duplicates
	6.5. Creating a View to Simplify Using a Summary
	6.6. Finding Values Associated with Minimum and Maximum Values
	6.7. Controlling String Case Sensitivity for MIN() and MAX()
	6.8. Dividing a Summary into Subgroups
	6.9. Handling NULL Values with Aggregate Functions
	6.10. Selecting Only Groups with Certain Characteristics
	6.11. Using Counts to Determine Whether Values Are Unique
	6.12. Grouping by Expression Results
	6.13. Summarizing Noncategorical Data
	6.14. Finding Smallest or Largest Summary Values
	6.15. Producing Date-Based Summaries
	6.16. Working with Per-Group and Overall Summary Values Simultaneously
	6.17. Generating a Report That Includes a Summary and a List
	6.18. Generating Summaries from Temporary Result Sets
7. Using Stored Routines, Triggers, and Scheduled Events
	7.0. Introduction
	7.1. Creating Compound-Statement Objects
	7.2. Using Stored Functions to Simplify Calculations
	7.3. Using Stored Procedures to Produce Multiple Values
	7.4. Using Triggers to Implement Dynamic Default Column Values
	7.5. Simulating TIMESTAMP Properties for Other Date and Time Types
	7.6. Using Triggers to Log Changes to a Table
	7.7. Using Events to Schedule Database Actions
	7.8. Writing Helper Routines for Executing Dynamic SQL
	7.9. Detecting No More Rows Conditions Using Condition Handlers
	7.10. Catching and Ignoring Errors with Condition Handlers
	7.11. Raising Errors and Warnings
	7.12. Logging Errors by Accessing the Diagnostic Area
	7.13. Using Triggers to Preprocess or Reject Data
8. Working with Metadata
	8.0. Introduction
	8.1. Determining the Number of Rows Affected by a Statement
	8.2. Obtaining Result Set Metadata
	8.3. Determining Whether a Statement Produced a Result Set
	8.4. Using Metadata to Format Query Output
	8.5. Listing or Checking the Existence of Databases or Tables
	8.6. Listing or Checking the Existence of Views
	8.7. Accessing Table Column Definitions
	8.8. Getting ENUM and SET Column Information
	8.9. Getting Server Metadata
	8.10. Writing Applications That Adapt to the MySQL Server Version
	8.11. Getting Child Tables That Reference a Specific Table via Foreign Key Constraints
	8.12. Listing Triggers
	8.13. Listing Stored Routines and Scheduled Events
	8.14. Listing installed plugins
	8.15. Listing Character Sets and Collations
	8.16. Listing CHECK Constraints
9. Importing and Exporting Data
	9.0. Introduction
	9.1. Importing Data with LOAD DATA and mysqlimport
	9.2. Specifying Column and Line Delimiters
	9.3. Dealing with Quotes and Special Characters
	9.4. Handling Duplicate Key Values
	9.5. Obtaining Diagnostics about Bad Input Data
	9.6. Skipping Datafile Lines
	9.7. Specifying Input Column Order
	9.8. Preprocessing Input Values Before Inserting Them
	9.9. Ignoring Datafile Columns
	9.10. Importing CSV Files
	9.11. Exporting Query Results from MySQL
	9.12. Importing and Exporting NULL Values
	9.13. Writing Your Own Data Export Programs
	9.14. Converting Datafiles from One Format to Another
	9.15. Extracting and Rearranging Datafile Columns
	9.16. Exchanging Data Between MySQL and Microsoft Excel
	9.17. Exporting Query Results as XML
	9.18. Importing XML into MySQL
	9.19. Exporting Data in SQL Format
	9.20. Importing SQL data
	9.21. Importing Data in JSON Format
	9.22. Importing data from MongoDB
	9.23. Exporting Data in JSON Format
	9.24. Guessing Table Structure from a Datafile
10. Validating and Reformatting Data
	10.0. Introduction
	10.1. Using the SQL Mode to Reject Bad Input Values
	10.2. Using CHECK Constraints to Reject Invalid Values
	10.3. Using Triggers to Reject Input Values
	10.4. Writing an input-processing loop
	10.5. Putting common tests in libraries
	10.6. Using Pattern Matching to Validate Data
	10.7. Using Patterns to Match Broad Content Types
	10.8. Using Patterns to Match Numeric Values
	10.9. Using Patterns to Match Dates or Times
	10.10. Using Patterns to Match Email Addresses or URLs
	10.11. Using Table Metadata to Validate Data
	10.12. Using a Lookup Table to Validate Data
	10.13. Converting Two-Digit Year Values to Four-Digit Form
	10.14. Performing Validity Checking on Date or Time Subparts
	10.15. Writing Date-Processing Utilities
	10.16. Importing Non-ISO Date Values
	10.17. Exporting Dates Using Non-ISO Formats
	10.18. Pre-processing and Importing a File
11. Generating and Using Sequences
	11.0. Introduction
	11.1. Generating a Sequence with AUTO_INCREMENT Columns
	11.2. Choosing the Data Type for a Sequence Column
	11.3. The Effect of Row Deletions on Sequence Generation
	11.4. Retrieving Sequence Values
	11.5. Renumbering an Existing Sequence
	11.6. Extending the Range of a Sequence Column
	11.7. Reusing Values at the Top of a Sequence
	11.8. Ensuring That Rows Are Renumbered in a Particular Order
	11.9. Sequencing an Unsequenced Table
	11.10. Managing Multiple Auto-Increment Values Simultaneously
	11.11. Using Auto-Increment Values to Associate Tables
	11.12. Using Sequence Generators as Counters
	11.13. Generating Repeating Sequences
	11.14. Using Custom Increment Values
	11.15. Using Window Functions to Number Rows In the Result Set
	11.16. Generating Series with Recursive CTEs
	11.17. Creating and Storing Custom Sequences
12. Statistical Techniques
	12.0. Introduction
	12.1. Calculating Descriptive Statistics
	12.2. Calculating Descriptive Statistics for Groups
	12.3. Generating Frequency Distributions
	12.4. Counting Missing Values
	12.5. Calculating Linear Regressions or Correlation Coefficients
	12.6. Generating Random Numbers
	12.7. Randomizing a Set of Rows
	12.8. Selecting Random Items from a Set of Rows
	12.9. Calculating Successive-Row Differences
	12.10. Finding Cumulative Sums and Running Averages
	12.11. Assigning Ranks
	12.12. Computing Team Standings
About the Authors