MySQL Cookbook, 4th Edition

MySQL Cookbook, 4th Edition

by Sveta Smirnova, Alkin Tezuysal
Released August 2022
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781492093169

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 Buy on ebooks.com
Start your free trial

Book description

For MySQL, the price of popularity comes with a flood of questions from users on how to solve specific data-related issues. That's where this cookbook comes in. When you need quick solutions or techniques, this handy resource provides scores of short, focused pieces of code, hundreds of worked-out examples, and clear, concise explanations for programmers who don't have the time (or expertise) to resolve MySQL problems from scratch.

In this updated fourth edition, authors Sveta Smirnova and Alkin Tezuysal provide more than 200 recipes that cover powerful features in both MySQL 5.7 and 8.0. Beginners as well as professional database and web developers will dive into topics such as MySQL Shell, MySQL replication, and working with JSON.

You'll learn how to:

  • Connect to a server, issue queries, and retrieve results
  • Retrieve data from the MySQL Server
  • Store, retrieve, and manipulate strings
  • Work with dates and times
  • Sort query results and generate summaries
  • Assess the characteristics of a dataset
  • Write stored functions and procedures
  • Use stored routines, triggers, and scheduled events
  • Perform basic MySQL administration tasks
  • Understand MySQL monitoring fundamentals

Publisher resources

View/Submit Errata

Table of contents

  1. Foreword
  2. Preface
    1. Who This Book Is For
    2. What’s in This Book
    3. MySQL APIs Used in This Book
    4. Version and Platform Notes
    5. Conventions Used in This Book
    6. The MySQL Cookbook Companion GitHub Repository
    7. Obtaining MySQL and Related Software
    8. Using Code Examples
    9. O’Reilly Online Learning
    10. How to Contact Us
    11. Acknowledgments
  3. 1. Using the mysql Client Program
    1. 1.0. Introduction
    2. 1.1. Setting Up a MySQL User Account
    3. 1.2. Creating a Database and a Sample Table
    4. 1.3. Finding mysql Client
    5. 1.4. Specifying mysql Command Options
    6. 1.5. Executing SQL Statements Interactively
    7. 1.6. Executing SQL Statements Read from a File or Program
    8. 1.7. Controlling mysql Output Destination and Format
    9. 1.8. Using User-Defined Variables in SQL Statements
    10. 1.9. Customizing a mysql Prompt
    11. 1.10. Using External Programs
    12. 1.11. Filtering and Processing Output
  4. 2. Using MySQL Shell
    1. 2.0. Introduction
    2. 2.1. Connecting to MySQL Server with MySQL Shell
    3. 2.2. Selecting the Protocol
    4. 2.3. Selecting SQL, JavaScript, or Python Mode
    5. 2.4. Running SQL Session
    6. 2.5. Running SQL in JavaScript Mode
    7. 2.6. Running SQL in Python Mode
    8. 2.7. Working with Tables in JavaScript Mode
    9. 2.8. Working with Tables in Python Mode
    10. 2.9. Working with Collections in JavaScript Mode
    11. 2.10. Working with Collections in Python Mode
    12. 2.11. Controlling the Output Format
    13. 2.12. Running Reports with MySQL Shell
    14. 2.13. Using MySQL Shell Utilities
    15. 2.14. Using the Admin API to Automate Replication Management
    16. 2.15. Working with JavaScript Objects
    17. 2.16. Filling Test Data Using Python’s Data Science Modules
    18. 2.17. Reusing Your Scripts for MySQL Shell
  5. 3. MySQL Replication
    1. 3.0. Introduction
    2. 3.1. Configuring Basic Replication Between One Source and One Replica
    3. 3.2. Position-Based Replication in the New Installation Environment
    4. 3.3. Setting Up a Position-Based Replica of a MySQL Installation that Is Already in Use
    5. 3.4. Setting Up GTID-Based Replication
    6. 3.5. Configuring a Binary Log Format
    7. 3.6. Using Replication Filters
    8. 3.7. Rewriting a Database on the Replica
    9. 3.8. Using a Multithreaded Replica
    10. 3.9. Setting Up Circular Replication
    11. 3.10. Using Multisource Replication
    12. 3.11. Using a Semisynchronous Replication Plug-In
    13. 3.12. Using Group Replication
    14. 3.13. Storing Replication Credentials Securely
    15. 3.14. Using TLS (SSL) for Replication
    16. 3.15. Replication Troubleshooting
    17. 3.16. Using Processlist to Understand Replication Performance
    18. 3.17. Setting Up Automated Replication
  6. 4. Writing MySQL-Based Programs
    1. 4.0. Introduction
    2. 4.1. Connecting, Selecting a Database, and Disconnecting
    3. 4.2. Checking for Errors
    4. 4.3. Writing Library Files
    5. 4.4. Executing Statements and Retrieving Results
    6. 4.5. Handling Special Characters and NULL Values in Statements
    7. 4.6. Handling Special Characters in Identifiers
    8. 4.7. Identifying NULL Values in Result Sets
    9. 4.8. Obtaining Connection Parameters
    10. 4.9. Resetting the profile Table
  7. 5. Selecting Data from Tables
    1. 5.0. Introduction
    2. 5.1. Specifying Which Columns and Rows to Select
    3. 5.2. Naming Query Result Columns
    4. 5.3. Sorting Query Results
    5. 5.4. Removing Duplicate Rows
    6. 5.5. Working with NULL Values
    7. 5.6. Writing Comparisons Involving NULL in Programs
    8. 5.7. Using Views to Simplify Table Access
    9. 5.8. Selecting Data from Multiple Tables
    10. 5.9. Selecting Rows from the Beginning, End, or Middle of Query Results
    11. 5.10. What to Do When LIMIT and the Final Result Require a Different Sort Order
    12. 5.11. Calculating LIMIT Values from Expressions
    13. 5.12. Combining Two or More SELECT Results
    14. 5.13. Selecting Results of Subqueries
  8. 6. Table Management
    1. 6.0. Introduction
    2. 6.1. Cloning a Table
    3. 6.2. Saving a Query Result in a Table
    4. 6.3. Creating Temporary Tables
    5. 6.4. Generating Unique Table Names
    6. 6.5. Checking or Changing a Table Storage Engine
    7. 6.6. Copying a Table Using mysqldump
    8. 6.7. Copying an InnoDB Table Using Transportable Tablespaces
    9. 6.8. Copying a MyISAM Table Using an sdi File
  9. 7. Working with Strings
    1. 7.0. Introduction
    2. 7.1. String Properties
    3. 7.2. Choosing a String Data Type
    4. 7.3. Setting the Client Connection Character Set
    5. 7.4. Writing String Literals
    6. 7.5. Checking or Changing a String’s Character Set or Collation
    7. 7.6. Converting the Lettercase of a String
    8. 7.7. Comparing String Values
    9. 7.8. Converting Between Decimal, Octal, and Hexadecimal Formats
    10. 7.9. Converting Between ASCII, BIT, and Hexadecimal Formats
    11. 7.10. Pattern Matching with SQL Patterns
    12. 7.11. Pattern Matching with Regular Expressions
    13. 7.12. Reversing the String Content
    14. 7.13. Searching for Substrings
    15. 7.14. Breaking Apart or Combining Strings
    16. 7.15. Using Full-Text Searches
    17. 7.16. Using a Full-Text Search with Short Words
    18. 7.17. Requiring or Prohibiting Full-Text Search Words
    19. 7.18. Performing Full-Text Phrase Searches
  10. 8. Working with Dates and Times
    1. 8.0. Introduction
    2. 8.1. Choosing a Temporal Data Type
    3. 8.2. Using Fractional Seconds Support
    4. 8.3. Changing MySQL’s Date Format
    5. 8.4. Setting the Client Time Zone
    6. 8.5. Setting the Server Time Zone
    7. 8.6. Shifting Temporal Values Between Time Zones
    8. 8.7. Determining the Current Date or Time
    9. 8.8. Using TIMESTAMP or DATETIME to Track Row-Modification Times
    10. 8.9. Extracting Parts of Dates or Times
    11. 8.10. Synthesizing Dates or Times from Component Values
    12. 8.11. Converting Between Temporal Values and Basic Units
    13. 8.12. Calculating Intervals Between Dates or Times
    14. 8.13. Adding Date or Time Values
    15. 8.14. Calculating Ages
    16. 8.15. Finding the First Day, Last Day, or Length of a Month
    17. 8.16. Finding the Day of the Week for a Date
    18. 8.17. Finding Dates for Any Weekday of a Given Week
    19. 8.18. Canonizing Not-Quite-ISO Date Strings
    20. 8.19. Selecting Rows Based on Temporal Characteristics
  11. 9. Sorting Query Results
    1. 9.0. Introduction
    2. 9.1. Using ORDER BY to Sort Query Results
    3. 9.2. Using Expressions for Sorting
    4. 9.3. Displaying One Set of Values While Sorting by Another
    5. 9.4. Controlling Case Sensitivity of String Sorts
    6. 9.5. Sorting in Temporal Order
    7. 9.6. Sorting by Substrings of Column Values
    8. 9.7. Sorting by Fixed-Length Substrings
    9. 9.8. Sorting by Variable-Length Substrings
    10. 9.9. Sorting Hostnames in Domain Order
    11. 9.10. Sorting Dotted-Quad IP Values in Numeric Order
    12. 9.11. Floating Values to the Head or Tail of the Sort Order
    13. 9.12. Defining a Custom Sort Order
    14. 9.13. Sorting ENUM Values
  12. 10. Generating Summaries
    1. 10.0. Introduction
    2. 10.1. Summarizing with COUNT()
    3. 10.2. Summarizing with MIN() and MAX()
    4. 10.3. Summarizing with SUM() and AVG()
    5. 10.4. Using DISTINCT to Eliminate Duplicates
    6. 10.5. Creating a View to Simplify Using a Summary
    7. 10.6. Finding Values Associated with Minimum and Maximum Values
    8. 10.7. Controlling String Case Sensitivity for MIN() and MAX()
    9. 10.8. Dividing a Summary into Subgroups
    10. 10.9. Handling NULL Values with Aggregate Functions
    11. 10.10. Selecting Only Groups with Certain Characteristics
    12. 10.11. Using Counts to Determine Whether Values Are Unique
    13. 10.12. Grouping by Expression Results
    14. 10.13. Summarizing Noncategorical Data
    15. 10.14. Finding Smallest or Largest Summary Values
    16. 10.15. Producing Date-Based Summaries
    17. 10.16. Working with Per-Group and Overall Summary Values Simultaneously
    18. 10.17. Generating a Report that Includes a Summary and a List
    19. 10.18. Generating Summaries from Temporary Result Sets
  13. 11. Using Stored Routines, Triggers, and Scheduled Events
    1. 11.0. Introduction
    2. 11.1. Creating Compound-Statement Objects
    3. 11.2. Using Stored Functions to Simplify Calculations
    4. 11.3. Using Stored Procedures to Produce Multiple Values
    5. 11.4. Using Triggers to Log Changes to a Table
    6. 11.5. Using Events to Schedule Database Actions
    7. 11.6. Writing Helper Routines for Executing Dynamic SQL
    8. 11.7. Detecting “No More Rows” Conditions Using Condition Handlers
    9. 11.8. Catching and Ignoring Errors with Condition Handlers
    10. 11.9. Raising Errors and Warnings
    11. 11.10. Logging Errors by Accessing the Diagnostic Area
    12. 11.11. Using Triggers to Preprocess or Reject Data
  14. 12. Working with Metadata
    1. 12.0. Introduction
    2. 12.1. Determining the Number of Rows Affected by a Statement
    3. 12.2. Obtaining Result Set Metadata
    4. 12.3. Listing or Checking the Existence of Databases or Tables
    5. 12.4. Listing or Checking the Existence of Views
    6. 12.5. Accessing Table Column Definitions
    7. 12.6. Getting ENUM and SET Column Information
    8. 12.7. Getting Server Metadata
    9. 12.8. Writing Applications That Adapt to the MySQL Server Version
    10. 12.9. Getting Child Tables That Reference a Specific Table via Foreign Key Constraints
    11. 12.10. Listing Triggers
    12. 12.11. Listing Stored Routines and Scheduled Events
    13. 12.12. Listing Installed Plug-Ins
    14. 12.13. Listing Character Sets and Collations
    15. 12.14. Listing CHECK Constraints
  15. 13. Importing and Exporting Data
    1. 13.0. Introduction
    2. 13.1. Importing Data with LOAD DATA and mysqlimport
    3. 13.2. Specifying Column and Line Delimiters
    4. 13.3. Dealing with Quotes and Special Characters
    5. 13.4. Handling Duplicate Key Values
    6. 13.5. Obtaining Diagnostics About Bad Input Data
    7. 13.6. Skipping Datafile Lines
    8. 13.7. Specifying Input Column Order
    9. 13.8. Preprocessing Input Values Before Inserting Them
    10. 13.9. Ignoring Datafile Columns
    11. 13.10. Importing CSV Files
    12. 13.11. Exporting Query Results from MySQL
    13. 13.12. Importing and Exporting NULL Values
    14. 13.13. Exporting Data in SQL Format
    15. 13.14. Importing SQL Data
    16. 13.15. Exporting Query Results as XML
    17. 13.16. Importing XML into MySQL
    18. 13.17. Importing Data in JSON Format
    19. 13.18. Importing Data from MongoDB
    20. 13.19. Exporting Data in JSON Format
    21. 13.20. Guessing Table Structure from a Datafile
  16. 14. Validating and Reformatting Data
    1. 14.0. Introduction
    2. 14.1. Using the SQL Mode to Reject Bad Input Values
    3. 14.2. Using CHECK Constraints to Reject Invalid Values
    4. 14.3. Using Triggers to Reject Input Values
    5. 14.4. Writing an Input-Processing Loop
    6. 14.5. Putting Common Tests in Libraries
    7. 14.6. Using Pattern Matching to Validate Data
    8. 14.7. Using Patterns to Match Broad Content Types
    9. 14.8. Using Patterns to Match Numeric Values
    10. 14.9. Using Patterns to Match Dates or Times
    11. 14.10. Using Patterns to Match Email Addresses or URLs
    12. 14.11. Using Table Metadata to Validate Data
    13. 14.12. Using a Lookup Table to Validate Data
    14. 14.13. Converting Two-Digit Year Values to Four-Digit Form
    15. 14.14. Performing Validity Checking on Date or Time Subparts
    16. 14.15. Writing Date-Processing Utilities
    17. 14.16. Importing Non-ISO Date Values
    18. 14.17. Exporting Dates Using Non-ISO Formats
    19. 14.18. Preprocessing and Importing a File
  17. 15. Generating and Using Sequences
    1. 15.0. Introduction
    2. 15.1. Generating a Sequence with AUTO_INCREMENT Columns
    3. 15.2. Choosing the Data Type for a Sequence Column
    4. 15.3. Deleting Rows Without Changing a Sequence
    5. 15.4. Retrieving Sequence Values
    6. 15.5. Renumbering an Existing Sequence
    7. 15.6. Extending the Range of a Sequence Column
    8. 15.7. Reusing Values at the Top of a Sequence
    9. 15.8. Ensuring That Rows Are Renumbered in a Particular Order
    10. 15.9. Sequencing an Unsequenced Table
    11. 15.10. Managing Multiple Auto-Increment Values Simultaneously
    12. 15.11. Using Auto-Increment Values to Associate Tables
    13. 15.12. Using Sequence Generators as Counters
    14. 15.13. Generating Repeating Sequences
    15. 15.14. Using Custom Increment Values
    16. 15.15. Using Window Functions to Number Rows in the Result Set
    17. 15.16. Generating Series with Recursive CTEs
    18. 15.17. Creating and Storing Custom Sequences
  18. 16. Using Joins and Subqueries
    1. 16.0. Introduction
    2. 16.1. Finding Matches Between Tables
    3. 16.2. Finding Mismatches Between Tables
    4. 16.3. Identifying and Removing Mismatched or Unattached Rows
    5. 16.4. Comparing a Table to Itself
    6. 16.5. Producing Candidate-Detail Lists and Summaries
    7. 16.6. Enumerating a Many-to-Many Relationship
    8. 16.7. Finding Per-Group Minimum or Maximum Values
    9. 16.8. Using a Join to Fill or Identify Holes in a List
    10. 16.9. Using a Join to Control Query Sort Order
    11. 16.10. Joining Results of Multiple Queries
    12. 16.11. Referring to Join Output Column Names in Programs
  19. 17. Statistical Techniques
    1. 17.0. Introduction
    2. 17.1. Calculating Descriptive Statistics
    3. 17.2. Calculating Descriptive Statistics for Groups
    4. 17.3. Generating Frequency Distributions
    5. 17.4. Counting Missing Values
    6. 17.5. Calculating Linear Regressions or Correlation Coefficients
    7. 17.6. Generating Random Numbers
    8. 17.7. Randomizing a Set of Rows
    9. 17.8. Selecting Random Items from a Set of Rows
    10. 17.9. Calculating Successive-Row Differences
    11. 17.10. Finding Cumulative Sums and Running Averages
    12. 17.11. Assigning Ranks
    13. 17.12. Computing Team Standings
  20. 18. Handling Duplicates
    1. 18.0. Introduction
    2. 18.1. Preventing Duplicates from Occurring in a Table
    3. 18.2. Having More Than One Unique Key in the Table
    4. 18.3. Dealing with Duplicates When Loading Rows into a Table
    5. 18.4. Counting and Identifying Duplicates
    6. 18.5. Eliminating Duplicates from a Table
  21. 19. Working with JSON
    1. 19.0. Introduction
    2. 19.1. Choosing the Right Data Type
    3. 19.2. Inserting JSON Values
    4. 19.3. Validating JSON
    5. 19.4. Formatting JSON Values
    6. 19.5. Extracting Values from JSON
    7. 19.6. Searching Inside JSON
    8. 19.7. Inserting New Elements into a JSON Document
    9. 19.8. Updating JSON
    10. 19.9. Removing Elements from JSON
    11. 19.10. Merging Two or More JSON Documents into One
    12. 19.11. Creating JSON from Relational Data
    13. 19.12. Converting JSON into Relational Format
    14. 19.13. Investigating JSON
    15. 19.14. Working with JSON in MySQL as a Document Store
  22. 20. Performing Transactions
    1. 20.0. Introduction
    2. 20.1. Choosing a Transactional Storage Engine
    3. 20.2. Performing Transactions Using SQL
    4. 20.3. Performing Transactions from Within Programs
    5. 20.4. Performing Transactions in Perl Programs
    6. 20.5. Performing Transactions in Ruby Programs
    7. 20.6. Performing Transactions in PHP Programs
    8. 20.7. Performing Transactions in Python Programs
    9. 20.8. Performing Transactions in Go Programs
    10. 20.9. Using Context-Aware Functions to Handle Transactions in Go
    11. 20.10. Performing Transactions in Java Programs
  23. 21. Query Performance
    1. 21.0. Introduction
    2. 21.1. Creating Indexes
    3. 21.2. Creating a Surrogate Primary Key
    4. 21.3. Maintaining Indexes
    5. 21.4. Deciding When a Query Can Use an Index
    6. 21.5. Deciding the Order for Multiple Column Indexes
    7. 21.6. Using Ascending and Descending Indexes
    8. 21.7. Using Function-Based Indexes
    9. 21.8. Using Indexes on Generated Columns with JSON Data
    10. 21.9. Using Full Text Indexes
    11. 21.10. Utilizing Spatial Indexes and Geographical Data
    12. 21.11. Creating and Using Histograms
    13. 21.12. Writing Performant Queries
  24. 22. Server Administration
    1. 22.0. Introduction
    2. 22.1. Configuring the Server
    3. 22.2. Managing the Plug-In Interface
    4. 22.3. Controlling Server Logging
    5. 22.4. Rotating or Expiring Logfiles
    6. 22.5. Rotating Log Tables or Expiring Log Table Rows
    7. 22.6. Configuring Storage Engines
  25. 23. Monitoring the MySQL Server
    1. 23.0. Introduction
    2. 23.1. Why Monitor the MySQL Server?
    3. 23.2. Discovering Sources of MySQL Monitoring Information
    4. 23.3. Checking Server Uptime and Progress
    5. 23.4. Troubleshooting Server Start Problems
    6. 23.5. Determining the IO Utilization of the MySQL Server
    7. 23.6. Determining MySQL Thread’s CPU Utilization
    8. 23.7. Determining if MySQL Has Reached Its Connection Limits
    9. 23.8. Verifying That the Buffer Pool Is Sized Properly
    10. 23.9. Finding Information About the Storage Engine
    11. 23.10. Using the Error Log File to Troubleshoot MySQL Server Crashes
    12. 23.11. Slow Query Log File
    13. 23.12. Monitoring with the General Query Log
    14. 23.13. Using the Binary Log to Identify Changes
  26. 24. Security
    1. 24.0. Introduction
    2. 24.1. Understanding the mysql.user Table
    3. 24.2. Managing User Accounts
    4. 24.3. Implementing a Password Policy
    5. 24.4. Checking Password Strength
    6. 24.5. Expiring Passwords
    7. 24.6. Assigning Yourself a New Password
    8. 24.7. Resetting an Expired Password
    9. 24.8. Finding and Removing Anonymous Accounts
    10. 24.9. Modifying “Any Host” and “Many Host” Accounts
    11. 24.10. Using TLS (SSL)
    12. 24.11. Using Roles
    13. 24.12. Using Views to Secure Data Access
    14. 24.13. Using Stored Routines to Secure Data Modifications
  27. Index
  28. About the Authors

Product information

  • Title: MySQL Cookbook, 4th Edition
  • Author(s): Sveta Smirnova, Alkin Tezuysal
  • Release date: August 2022
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781492093169