MySQL Cookbook Solutions for Database Developers and Administrators, 4th Edition (Sveta Smirnova, Alkin Tezuysal) (Z-Library)

Praise for MySQL Cookbook, 4th Edition Whether you are struggling to simply log in to your MySQL server, or trying to architect the right replication topology, MySQL Cookbook has your back. Sveta and Alkin share their decades of experience helping hundreds of MySQL users complete their everyday tasks. —Henrik Ingo, Chief of Staff, Engineering, DataStax MySQL gets better with each release. This update by long-time MySQL experts makes it easier to keep up with the improvements. A valuable resource whether you are a beginner or an experienced user like me. —Mark Callaghan, ProUnlimited, long-time MySQL contributor A great learning resource for all MySQL users. Sveta and Alkin have curated a comprehensive list of solutions to problems spanning all aspects of MySQL. —Shlomi Noach, database engineer, PlanetScale I recommend this comprehensive book for all MySQL users. Its examples will help both beginners and advanced users of MySQL, and it features excellent chapters about MySQL Shell and JSON. —Frederic Descamps, MySQL Community Manager, Oracle Sveta and Alkin walk you through practical examples of things you will need to do as a MySQL developer, operator, or DBA. This wealth of knowledge, distilled and condensed, is a next level “how to” guide for mastering MySQL. —Matt Lord, Vitess Maintainer, PlanetScale

Sveta Smirnova and Alkin Tezuysal MySQL Cookbook Solutions for Database Developers and Administrators FOURTH EDITION

978-1-492-09316-9 [LSI] MySQL Cookbook by Sveta Smirnova and Alkin Tezuysal Copyright © 2022 Sveta Smirnova and Alkin Tezuysal. All rights reserved. Printed in the United States of America. Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472. O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (http://oreilly.com). For more information, contact our corporate/institutional sales department: 800-998-9938 or corporate@oreilly.com. Acquisitions Editor: Andy Kwan Development Editors: Amelia Blevins and Jeff Bleiel Production Editor: Ashley Stussy Copyeditor: Piper Editorial Consulting, LLC Proofreader: Liz Wheeler Indexer: Sue Klefstad Interior Designer: David Futato Cover Designer: Karen Montgomery Illustrator: Kate Dullea October 2002: First Edition November 2006: Second Edition August 2014: Third Edition August 2022: Fourth Edition Revision History for the Fourth Edition 2022-08-02: First Release See http://oreilly.com/catalog/errata.csp?isbn=9781492093169 for release details. The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. MySQL Cookbook, the cover image, and related trade dress are trademarks of O’Reilly Media, Inc. The views expressed in this work are those of the authors, and do not represent the publisher’s views. While the publisher and the authors have used good faith efforts to ensure that the information and instructions contained in this work are accurate, the publisher and the authors disclaim all responsibility for errors or omissions, including without limitation responsibility for damages resulting from the use of or reliance on this work. Use of the information and instructions contained in this work is at your own risk. If any code samples or other technology this work contains or describes is subject to open source licenses or the intellectual property rights of others, it is your responsibility to ensure that your use thereof complies with such licenses and/or rights.

Table of Contents Foreword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii 1. Using the mysql Client Program. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.0 Introduction 1 1.1 Setting Up a MySQL User Account 2 1.2 Creating a Database and a Sample Table 5 1.3 Finding mysql Client 7 1.4 Specifying mysql Command Options 8 1.5 Executing SQL Statements Interactively 14 1.6 Executing SQL Statements Read from a File or Program 16 1.7 Controlling mysql Output Destination and Format 18 1.8 Using User-Defined Variables in SQL Statements 23 1.9 Customizing a mysql Prompt 26 1.10 Using External Programs 28 1.11 Filtering and Processing Output 29 2. Using MySQL Shell. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 2.0 Introduction 33 2.1 Connecting to MySQL Server with MySQL Shell 34 2.2 Selecting the Protocol 37 2.3 Selecting SQL, JavaScript, or Python Mode 40 2.4 Running SQL Session 41 2.5 Running SQL in JavaScript Mode 42 2.6 Running SQL in Python Mode 44 2.7 Working with Tables in JavaScript Mode 46 2.8 Working with Tables in Python Mode 49 v

2.9 Working with Collections in JavaScript Mode 51 2.10 Working with Collections in Python Mode 54 2.11 Controlling the Output Format 56 2.12 Running Reports with MySQL Shell 60 2.13 Using MySQL Shell Utilities 63 2.14 Using the Admin API to Automate Replication Management 67 2.15 Working with JavaScript Objects 69 2.16 Filling Test Data Using Python’s Data Science Modules 73 2.17 Reusing Your Scripts for MySQL Shell 82 3. MySQL Replication. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 3.0 Introduction 87 3.1 Configuring Basic Replication Between One Source and One Replica 89 3.2 Position-Based Replication in the New Installation Environment 91 3.3 Setting Up a Position-Based Replica of a MySQL Installation that Is Already in Use 92 3.4 Setting Up GTID-Based Replication 94 3.5 Configuring a Binary Log Format 98 3.6 Using Replication Filters 100 3.7 Rewriting a Database on the Replica 105 3.8 Using a Multithreaded Replica 107 3.9 Setting Up Circular Replication 109 3.10 Using Multisource Replication 111 3.11 Using a Semisynchronous Replication Plug-In 115 3.12 Using Group Replication 118 3.13 Storing Replication Credentials Securely 123 3.14 Using TLS (SSL) for Replication 124 3.15 Replication Troubleshooting 126 3.16 Using Processlist to Understand Replication Performance 140 3.17 Setting Up Automated Replication 144 4. Writing MySQL-Based Programs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153 4.0 Introduction 153 4.1 Connecting, Selecting a Database, and Disconnecting 157 4.2 Checking for Errors 172 4.3 Writing Library Files 181 4.4 Executing Statements and Retrieving Results 196 4.5 Handling Special Characters and NULL Values in Statements 210 4.6 Handling Special Characters in Identifiers 220 4.7 Identifying NULL Values in Result Sets 221 4.8 Obtaining Connection Parameters 227 4.9 Resetting the profile Table 237 vi | Table of Contents

5. Selecting Data from Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239 5.0 Introduction 239 5.1 Specifying Which Columns and Rows to Select 240 5.2 Naming Query Result Columns 242 5.3 Sorting Query Results 246 5.4 Removing Duplicate Rows 247 5.5 Working with NULL Values 248 5.6 Writing Comparisons Involving NULL in Programs 251 5.7 Using Views to Simplify Table Access 252 5.8 Selecting Data from Multiple Tables 253 5.9 Selecting Rows from the Beginning, End, or Middle of Query Results 255 5.10 What to Do When LIMIT and the Final Result Require a Different Sort Order 258 5.11 Calculating LIMIT Values from Expressions 259 5.12 Combining Two or More SELECT Results 260 5.13 Selecting Results of Subqueries 262 6. Table Management. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265 6.0 Introduction 265 6.1 Cloning a Table 265 6.2 Saving a Query Result in a Table 266 6.3 Creating Temporary Tables 269 6.4 Generating Unique Table Names 271 6.5 Checking or Changing a Table Storage Engine 272 6.6 Copying a Table Using mysqldump 273 6.7 Copying an InnoDB Table Using Transportable Tablespaces 276 6.8 Copying a MyISAM Table Using an sdi File 278 7. Working with Strings. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281 7.0 Introduction 281 7.1 String Properties 282 7.2 Choosing a String Data Type 286 7.3 Setting the Client Connection Character Set 288 7.4 Writing String Literals 291 7.5 Checking or Changing a String’s Character Set or Collation 294 7.6 Converting the Lettercase of a String 296 7.7 Comparing String Values 298 7.8 Converting Between Decimal, Octal, and Hexadecimal Formats 301 7.9 Converting Between ASCII, BIT, and Hexadecimal Formats 302 7.10 Pattern Matching with SQL Patterns 305 7.11 Pattern Matching with Regular Expressions 308 7.12 Reversing the String Content 313 Table of Contents | vii

7.13 Searching for Substrings 314 7.14 Breaking Apart or Combining Strings 315 7.15 Using Full-Text Searches 318 7.16 Using a Full-Text Search with Short Words 323 7.17 Requiring or Prohibiting Full-Text Search Words 325 7.18 Performing Full-Text Phrase Searches 327 8. Working with Dates and Times. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329 8.0 Introduction 329 8.1 Choosing a Temporal Data Type 330 8.2 Using Fractional Seconds Support 332 8.3 Changing MySQL’s Date Format 335 8.4 Setting the Client Time Zone 339 8.5 Setting the Server Time Zone 341 8.6 Shifting Temporal Values Between Time Zones 342 8.7 Determining the Current Date or Time 344 8.8 Using TIMESTAMP or DATETIME to Track Row-Modification Times 345 8.9 Extracting Parts of Dates or Times 348 8.10 Synthesizing Dates or Times from Component Values 352 8.11 Converting Between Temporal Values and Basic Units 354 8.12 Calculating Intervals Between Dates or Times 358 8.13 Adding Date or Time Values 362 8.14 Calculating Ages 368 8.15 Finding the First Day, Last Day, or Length of a Month 369 8.16 Finding the Day of the Week for a Date 371 8.17 Finding Dates for Any Weekday of a Given Week 372 8.18 Canonizing Not-Quite-ISO Date Strings 375 8.19 Selecting Rows Based on Temporal Characteristics 376 9. Sorting Query Results. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381 9.0 Introduction 381 9.1 Using ORDER BY to Sort Query Results 382 9.2 Using Expressions for Sorting 385 9.3 Displaying One Set of Values While Sorting by Another 387 9.4 Controlling Case Sensitivity of String Sorts 390 9.5 Sorting in Temporal Order 394 9.6 Sorting by Substrings of Column Values 397 9.7 Sorting by Fixed-Length Substrings 398 9.8 Sorting by Variable-Length Substrings 401 9.9 Sorting Hostnames in Domain Order 405 9.10 Sorting Dotted-Quad IP Values in Numeric Order 408 9.11 Floating Values to the Head or Tail of the Sort Order 410 viii | Table of Contents

9.12 Defining a Custom Sort Order 412 9.13 Sorting ENUM Values 413 10. Generating Summaries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 417 10.0 Introduction 417 10.1 Summarizing with COUNT() 419 10.2 Summarizing with MIN() and MAX() 421 10.3 Summarizing with SUM() and AVG() 422 10.4 Using DISTINCT to Eliminate Duplicates 424 10.5 Creating a View to Simplify Using a Summary 426 10.6 Finding Values Associated with Minimum and Maximum Values 426 10.7 Controlling String Case Sensitivity for MIN() and MAX() 429 10.8 Dividing a Summary into Subgroups 430 10.9 Handling NULL Values with Aggregate Functions 434 10.10 Selecting Only Groups with Certain Characteristics 437 10.11 Using Counts to Determine Whether Values Are Unique 438 10.12 Grouping by Expression Results 439 10.13 Summarizing Noncategorical Data 440 10.14 Finding Smallest or Largest Summary Values 443 10.15 Producing Date-Based Summaries 445 10.16 Working with Per-Group and Overall Summary Values Simultaneously 446 10.17 Generating a Report that Includes a Summary and a List 449 10.18 Generating Summaries from Temporary Result Sets 452 11. Using Stored Routines, Triggers, and Scheduled Events. . . . . . . . . . . . . . . . . . . . . . . . . 455 11.0 Introduction 455 11.1 Creating Compound-Statement Objects 458 11.2 Using Stored Functions to Simplify Calculations 460 11.3 Using Stored Procedures to Produce Multiple Values 462 11.4 Using Triggers to Log Changes to a Table 463 11.5 Using Events to Schedule Database Actions 466 11.6 Writing Helper Routines for Executing Dynamic SQL 468 11.7 Detecting “No More Rows” Conditions Using Condition Handlers 470 11.8 Catching and Ignoring Errors with Condition Handlers 472 11.9 Raising Errors and Warnings 473 11.10 Logging Errors by Accessing the Diagnostic Area 474 11.11 Using Triggers to Preprocess or Reject Data 478 12. Working with Metadata. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481 12.0 Introduction 481 12.1 Determining the Number of Rows Affected by a Statement 483 12.2 Obtaining Result Set Metadata 486 Table of Contents | ix

12.3 Listing or Checking the Existence of Databases or Tables 496 12.4 Listing or Checking the Existence of Views 497 12.5 Accessing Table Column Definitions 498 12.6 Getting ENUM and SET Column Information 503 12.7 Getting Server Metadata 505 12.8 Writing Applications That Adapt to the MySQL Server Version 507 12.9 Getting Child Tables That Reference a Specific Table via Foreign Key Constraints 509 12.10 Listing Triggers 510 12.11 Listing Stored Routines and Scheduled Events 511 12.12 Listing Installed Plug-Ins 513 12.13 Listing Character Sets and Collations 514 12.14 Listing CHECK Constraints 517 13. Importing and Exporting Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 519 13.0 Introduction 519 13.1 Importing Data with LOAD DATA and mysqlimport 523 13.2 Specifying Column and Line Delimiters 527 13.3 Dealing with Quotes and Special Characters 529 13.4 Handling Duplicate Key Values 530 13.5 Obtaining Diagnostics About Bad Input Data 531 13.6 Skipping Datafile Lines 533 13.7 Specifying Input Column Order 534 13.8 Preprocessing Input Values Before Inserting Them 534 13.9 Ignoring Datafile Columns 536 13.10 Importing CSV Files 537 13.11 Exporting Query Results from MySQL 538 13.12 Importing and Exporting NULL Values 540 13.13 Exporting Data in SQL Format 542 13.14 Importing SQL Data 544 13.15 Exporting Query Results as XML 545 13.16 Importing XML into MySQL 546 13.17 Importing Data in JSON Format 547 13.18 Importing Data from MongoDB 549 13.19 Exporting Data in JSON Format 550 13.20 Guessing Table Structure from a Datafile 551 14. Validating and Reformatting Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 555 14.0 Introduction 555 14.1 Using the SQL Mode to Reject Bad Input Values 556 14.2 Using CHECK Constraints to Reject Invalid Values 558 14.3 Using Triggers to Reject Input Values 560 x | Table of Contents

14.4 Writing an Input-Processing Loop 562 14.5 Putting Common Tests in Libraries 563 14.6 Using Pattern Matching to Validate Data 565 14.7 Using Patterns to Match Broad Content Types 568 14.8 Using Patterns to Match Numeric Values 568 14.9 Using Patterns to Match Dates or Times 570 14.10 Using Patterns to Match Email Addresses or URLs 574 14.11 Using Table Metadata to Validate Data 575 14.12 Using a Lookup Table to Validate Data 578 14.13 Converting Two-Digit Year Values to Four-Digit Form 581 14.14 Performing Validity Checking on Date or Time Subparts 582 14.15 Writing Date-Processing Utilities 584 14.16 Importing Non-ISO Date Values 589 14.17 Exporting Dates Using Non-ISO Formats 590 14.18 Preprocessing and Importing a File 592 15. Generating and Using Sequences. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 595 15.0 Introduction 595 15.1 Generating a Sequence with AUTO_INCREMENT Columns 596 15.2 Choosing the Data Type for a Sequence Column 599 15.3 Deleting Rows Without Changing a Sequence 601 15.4 Retrieving Sequence Values 603 15.5 Renumbering an Existing Sequence 607 15.6 Extending the Range of a Sequence Column 610 15.7 Reusing Values at the Top of a Sequence 610 15.8 Ensuring That Rows Are Renumbered in a Particular Order 611 15.9 Sequencing an Unsequenced Table 612 15.10 Managing Multiple Auto-Increment Values Simultaneously 614 15.11 Using Auto-Increment Values to Associate Tables 615 15.12 Using Sequence Generators as Counters 617 15.13 Generating Repeating Sequences 621 15.14 Using Custom Increment Values 622 15.15 Using Window Functions to Number Rows in the Result Set 624 15.16 Generating Series with Recursive CTEs 626 15.17 Creating and Storing Custom Sequences 629 16. Using Joins and Subqueries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 635 16.0 Introduction 635 16.1 Finding Matches Between Tables 636 16.2 Finding Mismatches Between Tables 644 16.3 Identifying and Removing Mismatched or Unattached Rows 649 16.4 Comparing a Table to Itself 652 Table of Contents | xi

16.5 Producing Candidate-Detail Lists and Summaries 656 16.6 Enumerating a Many-to-Many Relationship 660 16.7 Finding Per-Group Minimum or Maximum Values 663 16.8 Using a Join to Fill or Identify Holes in a List 666 16.9 Using a Join to Control Query Sort Order 669 16.10 Joining Results of Multiple Queries 671 16.11 Referring to Join Output Column Names in Programs 673 17. Statistical Techniques. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 675 17.0 Introduction 675 17.1 Calculating Descriptive Statistics 676 17.2 Calculating Descriptive Statistics for Groups 679 17.3 Generating Frequency Distributions 681 17.4 Counting Missing Values 684 17.5 Calculating Linear Regressions or Correlation Coefficients 686 17.6 Generating Random Numbers 688 17.7 Randomizing a Set of Rows 690 17.8 Selecting Random Items from a Set of Rows 693 17.9 Calculating Successive-Row Differences 694 17.10 Finding Cumulative Sums and Running Averages 696 17.11 Assigning Ranks 701 17.12 Computing Team Standings 704 18. Handling Duplicates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 711 18.0 Introduction 711 18.1 Preventing Duplicates from Occurring in a Table 712 18.2 Having More Than One Unique Key in the Table 714 18.3 Dealing with Duplicates When Loading Rows into a Table 715 18.4 Counting and Identifying Duplicates 720 18.5 Eliminating Duplicates from a Table 724 19. Working with JSON. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 729 19.0 Introduction 729 19.1 Choosing the Right Data Type 730 19.2 Inserting JSON Values 731 19.3 Validating JSON 732 19.4 Formatting JSON Values 735 19.5 Extracting Values from JSON 736 19.6 Searching Inside JSON 738 19.7 Inserting New Elements into a JSON Document 739 19.8 Updating JSON 741 19.9 Removing Elements from JSON 742 xii | Table of Contents

19.10 Merging Two or More JSON Documents into One 742 19.11 Creating JSON from Relational Data 746 19.12 Converting JSON into Relational Format 748 19.13 Investigating JSON 750 19.14 Working with JSON in MySQL as a Document Store 752 20. Performing Transactions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 761 20.0 Introduction 761 20.1 Choosing a Transactional Storage Engine 762 20.2 Performing Transactions Using SQL 763 20.3 Performing Transactions from Within Programs 765 20.4 Performing Transactions in Perl Programs 767 20.5 Performing Transactions in Ruby Programs 769 20.6 Performing Transactions in PHP Programs 770 20.7 Performing Transactions in Python Programs 771 20.8 Performing Transactions in Go Programs 771 20.9 Using Context-Aware Functions to Handle Transactions in Go 772 20.10 Performing Transactions in Java Programs 776 21. Query Performance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 779 21.0 Introduction 779 21.1 Creating Indexes 781 21.2 Creating a Surrogate Primary Key 783 21.3 Maintaining Indexes 784 21.4 Deciding When a Query Can Use an Index 786 21.5 Deciding the Order for Multiple Column Indexes 787 21.6 Using Ascending and Descending Indexes 789 21.7 Using Function-Based Indexes 792 21.8 Using Indexes on Generated Columns with JSON Data 794 21.9 Using Full Text Indexes 797 21.10 Utilizing Spatial Indexes and Geographical Data 799 21.11 Creating and Using Histograms 803 21.12 Writing Performant Queries 809 22. Server Administration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 817 22.0 Introduction 817 22.1 Configuring the Server 817 22.2 Managing the Plug-In Interface 820 22.3 Controlling Server Logging 822 22.4 Rotating or Expiring Logfiles 826 22.5 Rotating Log Tables or Expiring Log Table Rows 828 22.6 Configuring Storage Engines 829 Table of Contents | xiii

23. Monitoring the MySQL Server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 833 23.0 Introduction 833 23.1 Why Monitor the MySQL Server? 834 23.2 Discovering Sources of MySQL Monitoring Information 840 23.3 Checking Server Uptime and Progress 844 23.4 Troubleshooting Server Start Problems 845 23.5 Determining the IO Utilization of the MySQL Server 846 23.6 Determining MySQL Thread’s CPU Utilization 849 23.7 Determining if MySQL Has Reached Its Connection Limits 851 23.8 Verifying That the Buffer Pool Is Sized Properly 853 23.9 Finding Information About the Storage Engine 856 23.10 Using the Error Log File to Troubleshoot MySQL Server Crashes 859 23.11 Slow Query Log File 861 23.12 Monitoring with the General Query Log 863 23.13 Using the Binary Log to Identify Changes 865 24. Security. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 867 24.0 Introduction 867 24.1 Understanding the mysql.user Table 868 24.2 Managing User Accounts 869 24.3 Implementing a Password Policy 872 24.4 Checking Password Strength 874 24.5 Expiring Passwords 875 24.6 Assigning Yourself a New Password 876 24.7 Resetting an Expired Password 877 24.8 Finding and Removing Anonymous Accounts 878 24.9 Modifying “Any Host” and “Many Host” Accounts 879 24.10 Using TLS (SSL) 880 24.11 Using Roles 883 24.12 Using Views to Secure Data Access 885 24.13 Using Stored Routines to Secure Data Modifications 887 Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 891 xiv | Table of Contents

Foreword MySQL is one of the most pragmatic relational databases that I have come across. It is fast, reliable, and easy to use. You can start off fairly easily with a very small footprint. Yet, it can be deployed at a massive scale. Some of the largest companies in the world run on MySQL. What makes MySQL attractive is that it makes the bread-and-butter features of a relational database work extremely well: these are indexes, joins and transactions. To top it off, it provides all the benefits of being open source. The recent trend to migrate software to cloud providers has brought a unique momentum to MySQL. This is because some of the complexities associated with managing a relational database are being taken on by the cloud providers. This lets you enjoy all that is good about MySQL without incurring the overhead of having to manage it. I have known Alkin and Sveta for many years. We have been meeting at various conferences, I have attended many of their sessions, and we have spent time together at many social events. Alkin was also a colleague at PlanetScale where he made sub‐ stantial contributions to the Vitess project. What is best about Alkin and Sveta? They are both genuine individuals who like to work hard and want to help the community. Also, they have a vast amount of experience and possess a deep knowledge of how to get the best out of MySQL. xv

You can see their diligence in this book. They spend time making sure that every sub‐ ject is well introduced. Each chapter reads like a story. But then, at the end, you have learned something extremely valuable. If you are getting started with MySQL, this is a great book. If you come back to this after using MySQL for a bit, you’ll discover hidden gems that you’ve missed before. And finally, the Problem-Solution-Discussion format allows you to quickly find a solution if you run into a specific problem while using MySQL. If you intend to learn and use MySQL, this is the book for you. —Sugu Sougoumarane CTO, PlanetScale Co-creator, Vitess xvi | Foreword

Preface The MySQL database management system is popular for many reasons. It’s fast, and it’s easy to set up, use, and administer. It runs under many varieties of Unix and Windows, and MySQL-based programs can be written in many languages. MySQL’s popularity raises the need to address questions its users have about how to solve specific problems. That is the purpose of MySQL Cookbook: to serve as a handy resource to which you can turn for quick solutions or techniques for attacking particular types of questions that come up when you use MySQL. Naturally, because it’s a cookbook, it contains recipes: straightforward instructions you can follow, rather than how to develop your own code from scratch. It’s written using a problem-and- solution format designed to be extremely practical and to make the contents easy to read and assimilate. It contains many short sections, each describing how to write a query, apply a technique, or develop a script to solve a problem of limited and specific scope. This book doesn’t develop full-fledged, complex applications. Instead, it assists you in developing such applications yourself by helping you get past problems that have you stumped. For example, a common question is “How can I deal with quotes and special char‐ acters in data values when I’m writing queries?” That’s not difficult, but figuring out how to do it is frustrating when you’re not sure where to start. This book demonstrates what to do; it shows you where to begin and how to proceed from there. This knowledge will serve you repeatedly because after you see what’s involved, you’ll be able to apply the technique to any kind of data, such as text, images, sound or video clips, news articles, compressed files, or PDF documents. Another common question is “Can I access data from multiple tables at the same time?” The answer is “Yes,” and it’s easy to do because it’s just a matter of knowing the proper SQL syntax. But it’s not always clear how until you see examples, which this book gives you. Other techniques that you’ll learn from this book include how to do the following: xvii

• Use SQL to select, sort, and summarize rows • Find matches or mismatches between tables • Perform transactions • Determine intervals between dates or times, including age calculations • Identify or remove duplicate rows • Use LOAD DATA to read your datafiles properly or find which values in the file are invalid • Use CHECK constraints to prevent entry of bad data into your database • Generate sequence numbers to use as unique row identifiers • Use a view as a “virtual table” • Write stored procedures and functions, set up triggers that activate to perform specific data-handling operations when you insert or update table rows, and use the Event Scheduler to run queries on a schedule • Set up replication • Manage user accounts • Control server logging One part of using MySQL is understanding how to communicate with the server— that is, how to use Structured Query Language (SQL; pronounced “sequel”), the language in which queries are formulated. Therefore, one major emphasis of this book is using SQL to formulate queries that answer particular kinds of questions. One helpful tool for learning and using SQL is the mysql client program that is included in MySQL distributions. You can use client interactively to send SQL statements to the server and see the results. This is extremely useful because it provides a direct interface to SQL—so useful, in fact, that the first chapter is devoted to mysql. But the ability to issue SQL queries alone is not enough. Information extracted from a database often requires further processing or presentation in a particular way. What if you have queries with complex interrelationships, such as when you need to use the results of one query as the basis for others? What if you need to generate a specialized report with very specific formatting requirements? These problems bring us to the other major emphasis of the book—how to write programs that interact with the MySQL server through an application programming interface (API). When you know how to use MySQL from within the context of a programming language, you gain other ways to exploit MySQL’s capabilities: xviii | Preface

• You can save query results and reuse them later. • You have full access to the expressive power of a general-purpose programming language. This enables you to make decisions based on the success or failure of a query, or on the content of the rows that are returned, and then tailor the actions taken accordingly. • You can format and display query results however you like. If you’re writing a command-line script, you can generate plain text. If it’s a web-based script, you can generate an HTML table. If it’s an application that extracts information for transfer to some other system, you might generate a datafile expressed in XML or JSON. Combining SQL with a general-purpose programming language gives you an extremely flexible framework for issuing queries and processing their results. Pro‐ gramming languages increase your capability to perform complex database opera‐ tions. But that doesn’t mean this book is complex. It keeps things simple, showing how to construct small building blocks using techniques that are easy to understand and easily mastered. We’ll leave it to you to combine these techniques in your own programs, which you can do to produce arbitrarily complex applications. After all, the genetic code is based on only four nucleic acids, but these basic elements have been combined to produce the astonishing array of biological life we see all around us. Similarly, there are only 12 notes in the scale, but in the hands of skilled composers, they are interwoven to produce a rich and endless variety of music. In the same way, when you take a set of simple recipes, add your imagination, and apply them to the database programming problems you want to solve, you can produce applications that perhaps are not works of art but are certainly useful and will help you and others be more productive. Who This Book Is For This book will be useful for anybody who uses MySQL, ranging from individuals who want to use a database for personal projects such as a blog or wiki, to professional database and web developers. The book is also intended for people who do not know how to use MySQL but would like to. If you’re new to MySQL, you’ll find lots of ways to use it here. If you’re more experienced, you’re probably already familiar with many of the problems addressed here but may not have had to solve them before and should find the book a great time-saver. Take advantage of the recipes given in the book, and use them in your own programs rather than writing the code from scratch. Preface | xix

The material ranges from introductory to advanced, so if a recipe describes techni‐ ques that seem obvious to you, skip it. Conversely, if you don’t understand a recipe, set it aside and come back to it later, perhaps after reading some of the other recipes. What’s in This Book It’s very likely when you use this book that you’re trying to develop an application but are not sure how to implement certain pieces of it. In this case, you already know what type of problem you want to solve; check the table of contents or the index for a recipe that shows how to do what you want. Ideally, the recipe will be just what you had in mind. Alternatively, you may be able to adapt a recipe for a similar problem to suit the issue at hand. We explain the principles involved in developing each technique so that you can modify it to fit the particular requirements of your own applications. Another way to approach this book is to just read through it with no specific problem in mind. This can give you a broader understanding of the things MySQL can do, so we recommend that you page through the book occasionally. It’s a more effective tool if you know the kinds of problems it addresses. As you get into later chapters, you’ll find recipes that assume a knowledge of topics covered in earlier chapters. This also applies within a chapter, where later sections often use techniques discussed earlier in the chapter. If you jump into a chapter and find a recipe that uses a technique with which you’re not familiar, check the table of contents or the index to find where the technique is explained earlier. For example, if a recipe sorts a query result using an ORDER BY clause that you don’t understand, turn to Chapter 9, which discusses various sorting methods and explains how they work. Here’s a summary of each chapter to give you an overview of the book’s contents. Chapter 1, “Using the mysql Client Program”, describes how to use the standard MySQL command-line client. mysql is often the first or primary interface to MySQL that people use, and it’s important to know how to exploit its capabilities. This program enables you to issue queries and see their results interactively, so it’s good for quick experimentation. You can also use it in batch mode to execute canned SQL scripts or send its output into other programs. In addition, the chapter discusses other ways to use mysql, such as how to make long lines more readable or generate output in various formats. Chapter 2, “Using MySQL Shell”, introduces the new MySQL command-line client, developed by the MySQL Team for versions 5.7 and newer. mysqlsh is compatible with mysql when it is running in SQL mode but also supports NoSQL in JavaScript and Python programming interfaces. With MySQL Shell, you can run SQL and NoSQL queries and automate many administrative tasks easily. xx | Preface