MySQL Cookbook: Solutions for Database Developers and Administrators, 4th Edition
- Length: 922 pages
- Edition: 4
- Language: English
- Publisher: O'Reilly Media
- Publication Date: 2022-10-04
- ISBN-10: 1492093165
- ISBN-13: 9781492093169
- Sales Rank: #1540719 (See Top 100 Books)
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
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 O’Reilly Online Learning 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. 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 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 the 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 Multithreaded Replica 3.9. Setting Up Circular Replication 3.10. Using Multisource Replication 3.11. Using a Semisynchronous Replication Plugin 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 Final Result Require 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 plugins 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. Pre-processing 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 Surrogate Primary Key 21.3. Maintaining Indexes 21.4. Deciding When a Query Can Use an Index 21.5. Deciding 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 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
Donate to keep this site alive
How to download source code?
1. Go to: https://www.oreilly.com/
2. Search the book title: MySQL Cookbook: Solutions for Database Developers and Administrators, 4th Edition
, sometime you may not get the results, please search the main title
3. Click the book title in the search results
3. Publisher resources
section, click Download Example Code
.
1. Disable the AdBlock plugin. Otherwise, you may not get any links.
2. Solve the CAPTCHA.
3. Click download link.
4. Lead to download server to download.