MySQL Cookbook

1. Using the mysql Client Program. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

1.1. Setting Up a MySQL User Account 2

1.2. Creating a Database and a Sample Table 4

1.3. What to Do if mysql Cannot Be Found 6

1.4. Specifying mysql Command Options 8

1.5. Executing SQL Statements Interactively 13

1.6. Executing SQL Statements Read from a File or Program 15

1.7. Controlling mysql Output Destination and Format 17

1.8. Using User-Defined Variables in SQL Statements 22

2. Writing MySQL-Based Programs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

2.1. Connecting, Selecting a Database, and Disconnecting 29

2.2. Checking for Errors 42

2.3. Writing Library Files 51

2.4. Executing Statements and Retrieving Results 65

2.5. Handling Special Characters and NULL Values in Statements 79

2.6. Handling Special Characters in Identifiers 89

2.7. Identifying NULL Values in Result Sets 91

2.8. Techniques for Obtaining Connection Parameters 95

2.9. Conclusion and Words of Advice 103

3. Selecting Data from Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105

3.1. Specifying Which Columns and Rows to Select 106

3.2. Naming Query Result Columns 108

3.3. Sorting Query Results 112

3.4. Removing Duplicate Rows 113

3.5. Working with NULL Values 114

3.6. Writing Comparisons Involving NULL in Programs 116

3.7. Using Views to Simplify Table Access 117

3.8. Selecting Data from Multiple Tables 119

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

3.10. What to Do When LIMIT Requires the “Wrong” Sort Order 124

3.11. Calculating LIMIT Values from Expressions 125

4. Table Management. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127

4.1. Cloning a Table 127

4.2. Saving a Query Result in a Table 128

4.3. Creating Temporary Tables 131

4.4. Generating Unique Table Names 133

4.5. Checking or Changing a Table Storage Engine 135

4.6. Copying a Table Using mysqldump 136

5. Working with Strings. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139

5.1. String Properties 140

5.2. Choosing a String Data Type 144

5.3. Setting the Client Connection Character Set 146

5.4. Writing String Literals 148

5.5. Checking or Changing a String’s Character Set or Collation 150

5.6. Converting the Lettercase of a String 153

5.7. Controlling Case Sensitivity in String Comparisons 155

5.8. Pattern Matching with SQL Patterns 158

5.9. Pattern Matching with Regular Expressions 160

5.10. Breaking Apart or Combining Strings 165

5.11. Searching for Substrings 168

5.12. Using Full-Text Searches 169

5.13. Using a Full-Text Search with Short Words 173

5.14. Requiring or Prohibiting Full-Text Search Words 175

5.15. Performing Full-Text Phrase Searches 177

6. Working with Dates and Times. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179

6.1. Choosing a Temporal Data Type 180

6.2. Using Fractional Seconds Support 182

6.3. Changing MySQL’s Date Format 183

6.4. Setting the Client Time Zone 187

6.5. Shifting Temporal Values Between Time Zones 189

6.6. Determining the Current Date or Time 190

6.7. Using TIMESTAMP or DATETIME to Track Row-Modification Times 191

6.8. Extracting Parts of Dates or Times 194

6.9. Synthesizing Dates or Times from Component Values 199

6.10. Converting Between Temporal Values and Basic Units 201

6.11. Calculating Intervals Between Dates or Times 205

6.12. Adding Date or Time Values 210

6.13. Calculating Ages 215

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

6.15. Calculating Dates by Substring Replacement 219

6.16. Finding the Day of the Week for a Date 220

6.17. Finding Dates for Any Weekday of a Given Week 221

6.18. Performing Leap-Year Calculations 224

6.19. Canonizing Not-Quite-ISO Date Strings 227

6.20. Selecting Rows Based on Temporal Characteristics 228

7. Sorting Query Results. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233

7.1. Using ORDER BY to Sort Query Results 234

7.2. Using Expressions for Sorting 238

7.3. Displaying One Set of Values While Sorting by Another 239

7.4. Controlling Case Sensitivity of String Sorts 243

7.5. Date-Based Sorting 246

7.6. Sorting by Substrings of Column Values 250

7.7. Sorting by Fixed-Length Substrings 250

7.8. Sorting by Variable-Length Substrings 254

7.9. Sorting Hostnames in Domain Order 258

7.10. Sorting Dotted-Quad IP Values in Numeric Order 261

7.11. Floating Values to the Head or Tail of the Sort Order 263

7.12. Defining a Custom Sort Order 266

7.13. Sorting ENUM Values 267

8. Generating Summaries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271

8.1. Basic Summary Techniques 273

8.2. Creating a View to Simplify Using a Summary 279

8.3. Finding Values Associated with Minimum and Maximum Values 280

8.4. Controlling String Case Sensitivity for MIN() and MAX() 282

8.5. Dividing a Summary into Subgroups 283

8.6. Summaries and NULL Values 287

8.7. Selecting Only Groups with Certain Characteristics 290

8.8. Using Counts to Determine Whether Values Are Unique 291

8.9. Grouping by Expression Results 292

8.10. Summarizing Noncategorical Data 293

8.11. Finding Smallest or Largest Summary Values 296

8.12. Date-Based Summaries 298

8.13. Working with Per-Group and Overall Summary Values Simultaneously 300

8.14. Generating a Report That Includes a Summary and a List 303

9. Using Stored Routines, Triggers, and Scheduled Events. . . . . . . . . . . . . . . . . . . . . . . . . 307

9.1. Creating Compound-Statement Objects 310

9.2. Using Stored Functions to Encapsulate Calculations 312

9.3. Using Stored Procedures to “Return” Multiple Values 314

9.4. Using Triggers to Implement Dynamic Default Column Values 315

9.5. Using Triggers to Simulate Function-Based Indexes 317

9.6. Simulating TIMESTAMP Properties for Other Date and Time Types 320

9.7. Using Triggers to Log Changes to a Table 322

9.8. Using Events to Schedule Database Actions 325

9.9. Writing Helper Routines for Executing Dynamic SQL 327

9.10. Handling Errors Within Stored Programs 328

9.11. Using Triggers to Preprocess or Reject Data 332

10. Working with Metadata. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335

10.1. Determining the Number of Rows Affected by a Statement 337

10.2. Obtaining Result Set Metadata 340

10.3. Determining Whether a Statement Produced a Result Set 350

10.4. Using Metadata to Format Query Output 350

10.5. Listing or Checking Existence of Databases or Tables 354

10.6. Accessing Table Column Definitions 356

10.7. Getting ENUM and SET Column Information 361

10.8. Getting Server Metadata 363

10.9. Writing Applications That Adapt to the MySQL Server Version 364

11. Importing and Exporting Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367

11.1. Importing Data with LOAD DATA and mysqlimport 371

11.2. Importing CSV Files 383

11.3. Exporting Query Results from MySQL 383

11.4. Importing and Exporting NULL Values 385

11.5. Writing Your Own Data Export Programs 387

11.6. Converting Datafiles from One Format to Another 392

11.7. Extracting and Rearranging Datafile Columns 393

11.8. Exchanging Data Between MySQL and Microsoft Excel 396

11.9. Exporting Query Results as XML 398

11.10. Importing XML into MySQL 401

11.11. Guessing Table Structure from a Datafile 404

12. Validating and Reformatting Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409

12.1. Using the SQL Mode to Reject Bad Input Values 410

12.2. Validating and Transforming Data 411

12.3. Using Pattern Matching to Validate Data 415

12.4. Using Patterns to Match Broad Content Types 417

12.5. Using Patterns to Match Numeric Values 418

12.6. Using Patterns to Match Dates or Times 420

12.7. Using Patterns to Match Email Addresses or URLs 424

12.8. Using Table Metadata to Validate Data 425

12.9. Using a Lookup Table to Validate Data 428

12.10. Converting Two-Digit Year Values to Four-Digit Form 431

12.11. Performing Validity Checking on Date or Time Subparts 432

12.12. Writing Date-Processing Utilities 435

12.13. Importing Non-ISO Date Values 440

12.14. Exporting Dates Using Non-ISO Formats 441

12.15. Epilogue 442

13. Generating and Using Sequences. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445

13.1. Creating a Sequence Column and Generating Sequence Values 446

13.2. Choosing the Definition for a Sequence Column 449

13.3. The Effect of Row Deletions on Sequence Generation 451

13.4. Retrieving Sequence Values 453

13.5. Renumbering an Existing Sequence 457

13.6. Extending the Range of a Sequence Column 460

13.7. Reusing Values at the Top of a Sequence 460

13.8. Ensuring That Rows Are Renumbered in a Particular Order 461

13.9. Sequencing an Unsequenced Table 462

13.10. Managing Multiple Auto-Increment Values Simultaneously 464

13.11. Using Auto-Increment Values to Associate Tables 465

13.12. Using Sequence Generators as Counters 467

13.13. Generating Repeating Sequences 471

14. Using Joins and Subqueries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473

14.1. Finding Matches Between Tables 474

14.2. Finding Mismatches Between Tables 482

14.3. Identifying and Removing Mismatched or Unattached Rows 487

14.4. Comparing a Table to Itself 490

14.5. Producing Master-Detail Lists and Summaries 494

14.6. Enumerating a Many-to-Many Relationship 497

14.7. Finding Per-Group Minimum or Maximum Values 501

14.8. Using a Join to Fill or Identify Holes in a List 504

14.9. Using a Join to Control Query Sort Order 507

14.10. Referring to Join Output Column Names in Programs 509

15. Statistical Techniques. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 511

15.1. Calculating Descriptive Statistics 512

15.2. Per-Group Descriptive Statistics 515

15.3. Generating Frequency Distributions 517

15.4. Counting Missing Values 520

15.5. Calculating Linear Regressions or Correlation Coefficients 522

15.6. Generating Random Numbers 525

15.7. Randomizing a Set of Rows 527

15.8. Selecting Random Items from a Set of Rows 529

15.9. Calculating Successive-Row Differences 531

15.10. Finding Cumulative Sums and Running Averages 533

15.11. Assigning Ranks 538

15.12. Computing Team Standings 541

16. Handling Duplicates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 549

16.1. Preventing Duplicates from Occurring in a Table 550

16.2. Dealing with Duplicates When Loading Rows into a Table 552

16.3. Counting and Identifying Duplicates 556

16.4. Eliminating Duplicates from a Table 560

17. Performing Transactions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 565

17.1. Choosing a Transactional Storage Engine 566

17.2. Performing Transactions Using SQL 567

17.3. Performing Transactions from Within Programs 569

17.4. Using Transactions in Perl Programs 571

17.5. Using Transactions in Ruby Programs 573

17.6. Using Transactions in PHP Programs 574

17.7. Using Transactions in Python Programs 575

17.8. Using Transactions in Java Programs 576

18. Introduction to MySQL on the Web. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 577

18.1. Basic Principles of Web Page Generation 579

18.2. Using Apache to Run Web Scripts 581

18.3. Using Tomcat to Run Web Scripts 591

18.4. Encoding Special Characters in Web Output 596

19. Generating Web Content from Query Results. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 605

19.1. Displaying Query Results as Paragraphs 606

19.2. Displaying Query Results as Lists 608

19.3. Displaying Query Results as Tables 618

19.4. Displaying Query Results as Hyperlinks 622

19.5. Creating Navigation Indexes from Database Content 626

19.6. Storing Images or Other Binary Data 631

19.7. Serving Images or Other Binary Data 638

19.8. Serving Banner Ads 641

19.9. Serving Query Results for Download 643

20. Processing Web Input with MySQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 647

20.1. Writing Scripts That Generate Web Forms 650

20.2. Creating Single-Pick Form Elements from Database Content 653

20.3. Creating Multiple-Pick Form Elements from Database Content 669

20.4. Loading Database Content into a Form 674

20.5. Collecting Web Input 679

20.6. Validating Web Input 689

20.7. Storing Web Input in a Database 691

20.8. Processing File Uploads 694

20.9. Performing Web-Based Database Searches 700

20.10. Generating Previous-Page and Next-Page Links 703

20.11. Generating “Click to Sort” Table Headings 708

20.12. Web Page Access Counting 712

20.13. Web Page Access Logging 716

20.14. Using MySQL for Apache Logging 717

21. Using MySQL-Based Web Session Management. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 725

21.1. Using MySQL-Based Sessions in Perl Applications 728

21.2. Using MySQL-Based Storage in Ruby Applications 734

21.3. Using MySQL-Based Storage with the PHP Session Manager 738

21.4. Using MySQL for Session-Backing Store with Tomcat 748

22. Server Administration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 757

22.1. Configuring the Server 757

22.2. Managing the Plug-In Interface 760

22.3. Controlling Server Logging 762

22.4. Rotating or Expiring Logfiles 765

22.5. Rotating Log Tables or Expiring Log Table Rows 768

22.6. Monitoring the MySQL Server 769

22.7. Creating and Using Backups 780

23. Security. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 783

23.1. Understanding the mysql.user Table 784

23.2. Managing User Accounts 785

23.3. Implementing a Password Policy 790

23.4. Checking Password Strength 793

23.5. Expiring Passwords 794

23.6. Assigning Yourself a New Password 795

23.7. Resetting an Expired Password 795

23.8. Finding and Fixing Insecure Accounts 796 

23.9. Disabling Use of Accounts with Pre-4.1 Passwords 800 23.10. Finding and Removing Anonymous Accounts 801 23.11. Modifying “Any Host” and “Many Host” Accounts 802 Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 805