MySQL Cookbook Second Early Release 4th Edition by Sveta Smirnova, Alkin Tezuysal 1492093114 9781492093114

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. Finding mysql Client

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 a mysql Prompt

1.10. Using External Programs

1.11. Filtering and Processing Output

2. Using MySQL Shell

2.0. Introduction

2.1. Connecting to MySQL Server with MySQL Shell

2.2. Selecting the Protocol

2.3. Selecting SQL, JavaScript, or Python Mode

2.4. Running SQL Session

2.5. Running SQL in JavaScript Mode

2.6. Running SQL in Python Mode

2.7. Working with Tables in JavaScript Mode

2.8. Working with Tables in Python Mode

2.9. Working with Collections in JavaScript Mode

2.10. Working with Collections in Python Mode

2.11. Controlling the Output Format

2.12. Running Reports with MySQL Shell

2.13. Using MySQL Shell Utilities

2.14. Using the Admin API to Automate Replication Management

2.15. Working with JavaScript Objects

2.16. Filling Test Data Using Python’s Data Science Modules

2.17. Reusing Your Scripts for MySQL Shell

3. MySQL Replication

3.0. Introduction

3.1. Configuring Basic Replication Between One Source and One Replica

3.2. Position-Based Replication in the New Installation Environment

3.3. Setting Up a Position-Based Replica of a MySQL Installation that Is Already in Use

3.4. Setting Up GTID-Based Replication

3.5. Configuring a Binary Log Format

3.6. Using Replication Filters

3.7. Rewriting a Database on the Replica

3.8. Using a Multithreaded Replica

3.9. Setting Up Circular Replication

3.10. Using Multisource Replication

3.11. Using a Semisynchronous Replication Plug-In

3.12. Using Group Replication

3.13. Storing Replication Credentials Securely

3.14. Using TLS (SSL) for Replication

3.15. Replication Troubleshooting

3.16. Using Processlist to Understand Replication Performance

3.17. Setting Up Automated Replication

4. Writing MySQL-Based Programs

4.0. Introduction

4.1. Connecting, Selecting a Database, and Disconnecting

4.2. Checking for Errors

4.3. Writing Library Files

4.4. Executing Statements and Retrieving Results

4.5. Handling Special Characters and NULL Values in Statements

4.6. Handling Special Characters in Identifiers

4.7. Identifying NULL Values in Result Sets

4.8. Obtaining Connection Parameters

4.9. Resetting the profile Table

5. Selecting Data from Tables

5.0. Introduction

5.1. Specifying Which Columns and Rows to Select

5.2. Naming Query Result Columns

5.3. Sorting Query Results

5.4. Removing Duplicate Rows

5.5. Working with NULL Values

5.6. Writing Comparisons Involving NULL in Programs

5.7. Using Views to Simplify Table Access

5.8. Selecting Data from Multiple Tables

5.9. Selecting Rows from the Beginning, End, or Middle of Query Results

5.10. What to Do When LIMIT and the Final Result Require a Different Sort Order

5.11. Calculating LIMIT Values from Expressions

5.12. Combining Two or More SELECT Results

5.13. Selecting Results of Subqueries

6. Table Management

6.0. Introduction

6.1. Cloning a Table

6.2. Saving a Query Result in a Table

6.3. Creating Temporary Tables

6.4. Generating Unique Table Names

6.5. Checking or Changing a Table Storage Engine

6.6. Copying a Table Using mysqldump

6.7. Copying an InnoDB Table Using Transportable Tablespaces

6.8. Copying a MyISAM Table Using an sdi File

7. Working with Strings

7.0. Introduction

7.1. String Properties

7.2. Choosing a String Data Type

7.3. Setting the Client Connection Character Set

7.4. Writing String Literals

7.5. Checking or Changing a String’s Character Set or Collation

7.6. Converting the Lettercase of a String

7.7. Comparing String Values

7.8. Converting Between Decimal, Octal, and Hexadecimal Formats

7.9. Converting Between ASCII, BIT, and Hexadecimal Formats

7.10. Pattern Matching with SQL Patterns

7.11. Pattern Matching with Regular Expressions

7.12. Reversing the String Content

7.13. Searching for Substrings

7.14. Breaking Apart or Combining Strings

7.15. Using Full-Text Searches

7.16. Using a Full-Text Search with Short Words

7.17. Requiring or Prohibiting Full-Text Search Words

7.18. Performing Full-Text Phrase Searches

8. Working with Dates and Times

8.0. Introduction

8.1. Choosing a Temporal Data Type

8.2. Using Fractional Seconds Support

8.3. Changing MySQL’s Date Format

8.4. Setting the Client Time Zone

8.5. Setting the Server Time Zone

8.6. Shifting Temporal Values Between Time Zones

8.7. Determining the Current Date or Time

8.8. Using TIMESTAMP or DATETIME to Track Row-Modification Times

8.9. Extracting Parts of Dates or Times

8.10. Synthesizing Dates or Times from Component Values

8.11. Converting Between Temporal Values and Basic Units

8.12. Calculating Intervals Between Dates or Times

8.13. Adding Date or Time Values

8.14. Calculating Ages

8.15. Finding the First Day, Last Day, or Length of a Month

8.16. Finding the Day of the Week for a Date

8.17. Finding Dates for Any Weekday of a Given Week

8.18. Canonizing Not-Quite-ISO Date Strings

8.19. Selecting Rows Based on Temporal Characteristics

9. Sorting Query Results

9.0. Introduction

9.1. Using ORDER BY to Sort Query Results

9.2. Using Expressions for Sorting

9.3. Displaying One Set of Values While Sorting by Another

9.4. Controlling Case Sensitivity of String Sorts

9.5. Sorting in Temporal Order

9.6. Sorting by Substrings of Column Values

9.7. Sorting by Fixed-Length Substrings

9.8. Sorting by Variable-Length Substrings

9.9. Sorting Hostnames in Domain Order

9.10. Sorting Dotted-Quad IP Values in Numeric Order

9.11. Floating Values to the Head or Tail of the Sort Order

9.12. Defining a Custom Sort Order

9.13. Sorting ENUM Values

10. Generating Summaries

10.0. Introduction

10.1. Summarizing with COUNT()

10.2. Summarizing with MIN() and MAX()

10.3. Summarizing with SUM() and AVG()

10.4. Using DISTINCT to Eliminate Duplicates

10.5. Creating a View to Simplify Using a Summary

10.6. Finding Values Associated with Minimum and Maximum Values

10.7. Controlling String Case Sensitivity for MIN() and MAX()

10.8. Dividing a Summary into Subgroups

10.9. Handling NULL Values with Aggregate Functions

10.10. Selecting Only Groups with Certain Characteristics

10.11. Using Counts to Determine Whether Values Are Unique

10.12. Grouping by Expression Results

10.13. Summarizing Noncategorical Data

10.14. Finding Smallest or Largest Summary Values

10.15. Producing Date-Based Summaries

10.16. Working with Per-Group and Overall Summary Values Simultaneously

10.17. Generating a Report that Includes a Summary and a List

10.18. Generating Summaries from Temporary Result Sets

11. Using Stored Routines, Triggers, and Scheduled Events

11.0. Introduction

11.1. Creating Compound-Statement Objects

11.2. Using Stored Functions to Simplify Calculations

11.3. Using Stored Procedures to Produce Multiple Values

11.4. Using Triggers to Log Changes to a Table

11.5. Using Events to Schedule Database Actions

11.6. Writing Helper Routines for Executing Dynamic SQL

11.7. Detecting “No More Rows” Conditions Using Condition Handlers

11.8. Catching and Ignoring Errors with Condition Handlers

11.9. Raising Errors and Warnings

11.10. Logging Errors by Accessing the Diagnostic Area

11.11. Using Triggers to Preprocess or Reject Data

12. Working with Metadata

12.0. Introduction

12.1. Determining the Number of Rows Affected by a Statement

12.2. Obtaining Result Set Metadata

12.3. Listing or Checking the Existence of Databases or Tables

12.4. Listing or Checking the Existence of Views

12.5. Accessing Table Column Definitions

12.6. Getting ENUM and SET Column Information

12.7. Getting Server Metadata

12.8. Writing Applications That Adapt to the MySQL Server Version

12.9. Getting Child Tables That Reference a Specific Table via Foreign Key Constraints

12.10. Listing Triggers

12.11. Listing Stored Routines and Scheduled Events

12.12. Listing Installed Plug-Ins

12.13. Listing Character Sets and Collations

12.14. Listing CHECK Constraints

13. Importing and Exporting Data

13.0. Introduction

13.1. Importing Data with LOAD DATA and mysqlimport

13.2. Specifying Column and Line Delimiters

13.3. Dealing with Quotes and Special Characters

13.4. Handling Duplicate Key Values

13.5. Obtaining Diagnostics About Bad Input Data

13.6. Skipping Datafile Lines

13.7. Specifying Input Column Order

13.8. Preprocessing Input Values Before Inserting Them

13.9. Ignoring Datafile Columns

13.10. Importing CSV Files

13.11. Exporting Query Results from MySQL

13.12. Importing and Exporting NULL Values

13.13. Exporting Data in SQL Format

13.14. Importing SQL Data

13.15. Exporting Query Results as XML

13.16. Importing XML into MySQL

13.17. Importing Data in JSON Format

13.18. Importing Data from MongoDB

13.19. Exporting Data in JSON Format

13.20. Guessing Table Structure from a Datafile

14. Validating and Reformatting Data

14.0. Introduction

14.1. Using the SQL Mode to Reject Bad Input Values

14.2. Using CHECK Constraints to Reject Invalid Values

14.3. Using Triggers to Reject Input Values

14.4. Writing an Input-Processing Loop

14.5. Putting Common Tests in Libraries

14.6. Using Pattern Matching to Validate Data

14.7. Using Patterns to Match Broad Content Types

14.8. Using Patterns to Match Numeric Values

14.9. Using Patterns to Match Dates or Times

14.10. Using Patterns to Match Email Addresses or URLs

14.11. Using Table Metadata to Validate Data

14.12. Using a Lookup Table to Validate Data

14.13. Converting Two-Digit Year Values to Four-Digit Form

14.14. Performing Validity Checking on Date or Time Subparts

14.15. Writing Date-Processing Utilities

14.16. Importing Non-ISO Date Values

14.17. Exporting Dates Using Non-ISO Formats

14.18. Preprocessing and Importing a File

15. Generating and Using Sequences

15.0. Introduction

15.1. Generating a Sequence with AUTO_INCREMENT Columns

15.2. Choosing the Data Type for a Sequence Column

15.3. Deleting Rows Without Changing a Sequence

15.4. Retrieving Sequence Values

15.5. Renumbering an Existing Sequence

15.6. Extending the Range of a Sequence Column

15.7. Reusing Values at the Top of a Sequence

15.8. Ensuring That Rows Are Renumbered in a Particular Order

15.9. Sequencing an Unsequenced Table

15.10. Managing Multiple Auto-Increment Values Simultaneously

15.11. Using Auto-Increment Values to Associate Tables

15.12. Using Sequence Generators as Counters

15.13. Generating Repeating Sequences

15.14. Using Custom Increment Values

15.15. Using Window Functions to Number Rows in the Result Set

15.16. Generating Series with Recursive CTEs

15.17. Creating and Storing Custom Sequences

16. Using Joins and Subqueries

16.0. Introduction

16.1. Finding Matches Between Tables

16.2. Finding Mismatches Between Tables

16.3. Identifying and Removing Mismatched or Unattached Rows

16.4. Comparing a Table to Itself

16.5. Producing Candidate-Detail Lists and Summaries

16.6. Enumerating a Many-to-Many Relationship

16.7. Finding Per-Group Minimum or Maximum Values

16.8. Using a Join to Fill or Identify Holes in a List

16.9. Using a Join to Control Query Sort Order

16.10. Joining Results of Multiple Queries

16.11. Referring to Join Output Column Names in Programs

17. Statistical Techniques

17.0. Introduction

17.1. Calculating Descriptive Statistics

17.2. Calculating Descriptive Statistics for Groups

17.3. Generating Frequency Distributions

17.4. Counting Missing Values

17.5. Calculating Linear Regressions or Correlation Coefficients

17.6. Generating Random Numbers

17.7. Randomizing a Set of Rows

17.8. Selecting Random Items from a Set of Rows

17.9. Calculating Successive-Row Differences

17.10. Finding Cumulative Sums and Running Averages

17.11. Assigning Ranks

17.12. Computing Team Standings

18. Handling Duplicates

18.0. Introduction

18.1. Preventing Duplicates from Occurring in a Table

18.2. Having More Than One Unique Key in the Table

18.3. Dealing with Duplicates When Loading Rows into a Table

18.4. Counting and Identifying Duplicates

18.5. Eliminating Duplicates from a Table

19. Working with JSON

19.0. Introduction

19.1. Choosing the Right Data Type

19.2. Inserting JSON Values

19.3. Validating JSON

19.4. Formatting JSON Values

19.5. Extracting Values from JSON

19.6. Searching Inside JSON

19.7. Inserting New Elements into a JSON Document

19.8. Updating JSON

19.9. Removing Elements from JSON

19.10. Merging Two or More JSON Documents into One

19.11. Creating JSON from Relational Data

19.12. Converting JSON into Relational Format

19.13. Investigating JSON

19.14. Working with JSON in MySQL as a Document Store

20. Performing Transactions

20.0. Introduction

20.1. Choosing a Transactional Storage Engine

20.2. Performing Transactions Using SQL

20.3. Performing Transactions from Within Programs

20.4. Performing Transactions in Perl Programs

20.5. Performing Transactions in Ruby Programs

20.6. Performing Transactions in PHP Programs

20.7. Performing Transactions in Python Programs

20.8. Performing Transactions in Go Programs

20.9. Using Context-Aware Functions to Handle Transactions in Go

20.10. Performing Transactions in Java Programs

21. Query Performance

21.0. Introduction

21.1. Creating Indexes

21.2. Creating a Surrogate Primary Key

21.3. Maintaining Indexes

21.4. Deciding When a Query Can Use an Index

21.5. Deciding the Order for Multiple Column Indexes

21.6. Using Ascending and Descending Indexes

21.7. Using Function-Based Indexes

21.8. Using Indexes on Generated Columns with JSON Data

21.9. Using Full Text Indexes

21.10. Utilizing Spatial Indexes and Geographical Data

21.11. Creating and Using Histograms

21.12. Writing Performant Queries

22. Server Administration

22.0. Introduction

22.1. Configuring the Server

22.2. Managing the Plug-In Interface

22.3. Controlling Server Logging

22.4. Rotating or Expiring Logfiles

22.5. Rotating Log Tables or Expiring Log Table Rows

22.6. Configuring Storage Engines

23. Monitoring the MySQL Server

23.0. Introduction

23.1. Why Monitor the MySQL Server?

23.2. Discovering Sources of MySQL Monitoring Information

23.3. Checking Server Uptime and Progress

23.4. Troubleshooting Server Start Problems

23.5. Determining the IO Utilization of the MySQL Server

23.6. Determining MySQL Thread’s CPU Utilization

23.7. Determining if MySQL Has Reached Its Connection Limits

23.8. Verifying That the Buffer Pool Is Sized Properly

23.9. Finding Information About the Storage Engine

23.10. Using the Error Log File to Troubleshoot MySQL Server Crashes

23.11. Slow Query Log File

23.12. Monitoring with the General Query Log

23.13. Using the Binary Log to Identify Changes

24. Security

24.0. Introduction

24.1. Understanding the mysql.user Table

24.2. Managing User Accounts

24.3. Implementing a Password Policy

24.4. Checking Password Strength

24.5. Expiring Passwords

24.6. Assigning Yourself a New Password

24.7. Resetting an Expired Password

24.8. Finding and Removing Anonymous Accounts

24.9. Modifying “Any Host” and “Many Host” Accounts

24.10. Using TLS (SSL)

24.11. Using Roles

24.12. Using Views to Secure Data Access

24.13. Using Stored Routines to Secure Data Modifications

Index

About the Authors