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 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 characters 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:
Use SQL to select, sort, and summarize rows
Find matches or mismatches between tables
Determine intervals between dates or times, including age calculations
Identify or remove duplicate rows
DATAto read your datafiles properly or find which values in the file are invalid
Use strict mode 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
Generate web pages from database content
Manage user accounts
Control server logging
One part of using MySQL is understanding how to communicate with the server—that is, how to use SQL, 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:
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 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.
Combining SQL with a general-purpose programming language gives you an extremely flexible framework for issuing queries and processing their results. Programming languages increase your capability to perform complex database operations. 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.
I’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 now use MySQL, but would like to. For example, it will be useful if you want to learn about databases but realize that a “big” database system such as Oracle can be daunting as a learning tool. (Perhaps I shouldn’t say that. Oracle bought MySQL in 2010 and is now my employer!)
If you’re new to MySQL, you’ll find lots of ways to use it here that may be new to you. 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 timesaver. Take advantage of the recipes given in the book and use them in your own programs rather than writing the code from scratch.
The material ranges from introductory to advanced, so if a recipe describes techniques 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. I 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 I 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
clause that you don’t understand, turn to Chapter 7,
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, Writing MySQL-Based Programs, demonstrates
the essential elements of MySQL programming: how to connect to the server,
issue queries, retrieve the results, and handle errors. It also discusses
how to handle special characters and
NULL values in queries, how to write library
files to encapsulate code for commonly used operations, and various ways
to gather the parameters needed for making connections to the
Chapter 3, Selecting Data from Tables, covers
several aspects of the
statement, which is the primary vehicle for retrieving data from the MySQL
server: specifying which columns and rows you want to retrieve, dealing
NULL values, and selecting one
section of a query result. Later chapters cover some of these topics in
more detail, but this chapter provides an overview of the concepts on
which they depend if you need some introductory background on row
selection or don’t yet know a lot about SQL.
Chapter 4, Table Management, covers table cloning, copying results into other tables, using temporary tables, and checking or changing a table’s storage engine.
Chapter 5, Working with Strings, describes
how to deal with string data. It covers character sets and collations,
string comparisons, dealing with case-sensitivity issues, pattern
matching, breaking apart and combining strings, and performing
Chapter 6, Working with Dates and Times, shows how
to work with temporal data. It describes MySQL’s date format and how to
display date values in other formats. It also covers how to use MySQL’s
TIMESTAMP data type, how to set
the time zone, how to convert between different temporal units, how to
perform date arithmetic to compute intervals or generate one date from
another, and how to perform leap-year calculations.
Chapter 7, Sorting Query Results, describes
how to put the rows of a query result in the order you want. This includes
specifying the sort direction, dealing with
NULL values, accounting for string case
sensitivity, and sorting by dates or partial column values. It also
provides examples that show how to sort special kinds of values, such as
domain names, IP numbers, and
Chapter 8, Generating Summaries, shows techniques for assessing the general characteristics of a set of data, such as how many values it contains or its minimum, maximum, and average values.
Chapter 9, Using Stored Routines, Triggers, and Scheduled Events, describes how to write stored functions and procedures that are stored on the server side, triggers that activate when tables are modified, and events that execute on a scheduled basis.
Chapter 10, Working with Metadata, discusses how to get information about the data that a query returns, such as the number of rows or columns in the result, or the name and data type of each column. It also shows how to ask MySQL what databases and tables are available or determine the structure of a table.
Chapter 11, Importing and Exporting Data, describes
how to transfer information between MySQL and other programs. This
includes how to use
DATA, convert files from one format to another,
and determine table structure appropriate for a dataset.
Chapter 12, Validating and Reformatting Data, describes how to extract or rearrange columns in datafiles, check and validate data, and rewrite values such as dates that often come in a variety of formats.
Chapter 13, Generating and Using Sequences,
MySQL’s mechanism for producing sequence numbers. It shows how to generate
new sequence values or determine the most recent value, how to resequence
a column, and how to use sequences to generate counters. It also shows how
AUTO_INCREMENT values to
maintain a master-detail relationship between tables, including pitfalls
Chapter 14, Using Joins and Subqueries, shows how to perform operations that select rows from multiple tables. It demonstrates how to compare tables to find matches or mismatches, produce master-detail lists and summaries, and enumerate many-to-many relationships.
Chapter 15, Statistical Techniques, illustrates how to produce descriptive statistics, frequency distributions, regressions, and correlations. It also covers how to randomize a set of rows or pick rows at random from the set.
Chapter 16, Handling Duplicates, discusses how to identify, count, and remove duplicate rows—and how to prevent them from occurring in the first place.
Chapter 17, Performing Transactions, shows how to handle multiple SQL statements that must execute together as a unit. It discusses how to control MySQL’s auto-commit mode and how to commit or roll back transactions.
Chapter 18, Introduction to MySQL on the Web, gets you set up to write web-based MySQL scripts. Web programming enables you to generate dynamic pages from database content or collect information for storage in your database. The chapter discusses how to configure Apache to run Perl, Ruby, PHP, and Python scripts, and how to configure Tomcat to run Java scripts written using JSP notation.
Chapter 19, Generating Web Content from Query Results, shows how to use the query results to generate various HTML structures such as paragraphs, lists, tables, hyperlinks, and navigation indexes. It also describes how to store images into MySQL and retrieve and display them later, and how to generate downloadable result sets.
Chapter 20, Processing Web Input with MySQL, discusses how to obtain input from users over the Web and use it to create new database rows or as the basis for performing searches. It deals heavily with form processing, including how to construct form elements such as radio buttons, pop-up menus, or checkboxes, based on information contained in your database.
Chapter 21, Using MySQL-Based Web Session Management, describes how to write web applications that remember information across multiple requests, using MySQL for backing store. This is useful for collecting information in stages, or when you need to make decisions based on prior user actions.
Chapter 22, Server Administration, is written for database administrators. It covers server configuration, the plug-in interface, log management, server monitoring, and making backups.
Chapter 23, Security, is another administrative chapter. It discusses user account management, including creating accounts, setting passwords, and assigning privileges. It also describes how to implement password policy, find and fix insecure accounts, and expire or unexpire passwords.
MySQL APIs Used in This Book
MySQL programming interfaces exist for many languages, including C, C++, Eiffel, Go, Java, Perl, PHP, Python, Ruby, and Tcl. Given this fact, writing a MySQL cookbook presents an author with a challenge. The book should provide recipes for doing many interesting and useful things with MySQL, but which API or APIs should the book use? Showing an implementation of every recipe in every language results either in covering very few recipes or in a very, very large book! It also results in redundancies when implementations in different languages bear a strong resemblance to each other. On the other hand, it’s worthwhile taking advantage of multiple languages, because one often is more suitable than another for solving a particular problem.
To resolve this dilemma, I’ve chosen a small number of APIs to write the recipes in this book. This makes its scope manageable while permitting latitude to choose from multiple APIs:
The Perl and Ruby DBI modules
PHP, using the PDO extension
Python, using the MySQL Connector/Python driver for the DB API
Java, using the MySQL Connector/J driver for the JDBC interface
Why these languages? Perl and PHP were easy to pick. Perl is a widely used language that became so based on certain strengths such as its text-processing capabilities. In addition, it’s very popular for writing MySQL programs. Ruby has an easy-to-use database-access module modeled after the Perl module. PHP is widely deployed, especially on the Web. One of PHP’s strengths is the ease with which you can use it to access databases, making it a natural choice for MySQL scripting. Python and Java are perhaps not as popular as Perl or PHP for MySQL programming, but each has a significant number of followers. In the Java community in particular, MySQL has a strong following among developers who use JavaServer Pages (JSP) technology to build database-backed web applications.
I believe these languages taken together reflect pretty well the majority of the existing user base of MySQL programmers. If you prefer some language not shown here, be sure to pay careful attention to Chapter 2, to familiarize yourself with the book’s primary APIs. Knowing how to perform database operations with the programming interfaces used here will help you translate recipes for other languages.
Version and Platform Notes
Development of the code in this book took place under MySQL 5.5,
5.6, and 5.7. Because new features are added to MySQL on a regular basis,
some examples will not work under older versions. For example, MySQL 5.5
introduces authentication plug-ins, and MySQL 5.6 introduces
TIMESTAMP-like auto-initialization and
auto-update properties for the
I do not assume that you are using Unix, although that is my own preferred development platform. (In this book, “Unix” also refers to Unix-like systems such as Linux and Mac OS X.) Most of the material here is applicable both to Unix and Windows.
Conventions Used in This Book
This book uses the following font conventions:
Used for program listings, as well as within paragraphs to refer to program elements such as variable or function names, databases, data types, environment variables, statements, and keywords.
Used to indicate text that you type when running commands.
Used to indicate variable input; you should substitute a value of your own choosing.
Used for URLs, hostnames, names of directories and files, Unix commands and options, programs, and occasionally for emphasis.
This element signifies a tip or suggestion.
This element indicates a warning or caution.
This element signifies a general note.
Commands often are shown with a prompt to illustrate the context in
which they are used. Commands issued from the command line are shown with
chmod 600 my.cnf
That prompt is one that Unix users are used to seeing, but it
doesn’t necessarily signify that a command works only under Unix. Unless
indicated otherwise, commands shown with a
% prompt generally should work under Windows,
If you should run a command under Unix as the
root user, the prompt is
perl -MCPAN -e shell
Commands that are specific to Windows use the
"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql"
SQL statements that are issued from within the mysql client program are shown with a
mysql> prompt and terminated with a
SELECT * FROM my_table;
For examples that show a query result as you would see it when using
mysql, I sometimes truncate the output,
using an ellipsis (
...) to indicate
that the result consists of more rows than are shown. The following query
produces many rows of output, from which those in the middle have been
SELECT name, abbrev FROM states ORDER BY name;+----------------+--------+ | name | abbrev | +----------------+--------+ | Alabama | AL | | Alaska | AK | | Arizona | AZ | … | West Virginia | WV | | Wisconsin | WI | | Wyoming | WY | +----------------+--------+
Examples that show only the syntax for SQL statements do not include
mysql> prompt, but they do
include semicolons as necessary to make it clearer where statements end.
For example, this is a single statement:
But this example represents two statements:
The semicolon is a notational convenience used within mysql as a statement terminator. But it is not part of SQL itself, so when you issue SQL statements from within programs that you write (for example, using Perl or Java), don’t include terminating semicolons.
The MySQL Cookbook Companion Website
MySQL Cookbook has a companion website where you can obtain source code and sample data for examples developed throughout this book, errata, and auxiliary documentation.
The website also makes examples from the book available online so you can try them from your browser.
Recipe Source Code and Data
The examples in this book are based on source code and sample data
from two distributions named
mcb-kjv available at the
recipes distribution is the
primary source of examples, and references to it occur throughout the
book. The distribution is available as a compressed TAR file (recipes.tar.gz) or as a ZIP file (recipes.zip). Either distribution format when
unpacked creates a directory named recipes.
recipes distribution to
save yourself a lot of typing. For example, when you see a
TABLE statement in the book that describes
what a database table looks like, you’ll usually find an SQL batch file
in the tables directory that you
can use to create the table instead of entering the definition manually.
Change location into the tables
directory and execute the following command, where
filename is the name of the file containing
mysql cookbook <
If you need to specify MySQL username or password options, add them to the command line.
contains programs as shown in the book, but in many cases also includes
implementations in additional languages. For example, a script shown in
the book using Python may be available in the
recipes distribution in Perl, Ruby, PHP, or
Java as well. This may save you translation effort should you wish to
convert a program shown in the book to a different language.
The other distribution is named
mcb-kjv and contains the text of the King
James Version of the Bible, formatted suitably for loading into MySQL.
It’s used in Chapter 5 as the source of a
reasonably large body of text for examples that demonstrate
FULLTEXT searches, and occasionally elsewhere
in the book. This distribution is provided separately from the
recipes distribution due to its size. It’s
available as a compressed TAR file (mcb-kjv.tar.gz) or as a ZIP file (mcb-kjv.zip). Either distribution format when
unpacked creates a directory named mcb-kjv.
mcb-kjv distribution is
derived from KJV text originally found on the Unbound Bible site,
restructured to be more usable for examples in the book. The
distribution includes notes that describe the modifications I
MySQL Cookbook Companion Documents
Some appendixes included in previous MySQL Cookbook editions are now available in standalone form at the companion website. They provide background information for topics covered in the book.
“Executing Programs from the Command Line” provides instructions for executing commands at the command prompt and setting environment variables such as
“JSP, JSTL, and Tomcat Primer” provides a general overview of JavaServer Pages (JSP) programming and installation instructions for the Tomcat web server. Read this document if you need to install Tomcat or are not familiar with it, or if you’ve never written pages using JSP notation. It also provides an overview of the Java Standard Tag Library (JSTL) that is used heavily for JSP pages in this book. This material is background for topics covered in the web programming chapters, beginning with Chapter 18.
Obtaining MySQL and Related Software
To run the examples in this book, you need access to MySQL, as well as the appropriate MySQL-specific interfaces for the programming languages that you want to use. The following notes describe what software is required and where to get it.
If you access a MySQL server run by somebody else, you need only the MySQL client software on your own machine. To run your own server, you need a full MySQL distribution.
To write your own MySQL-based programs, you communicate with the
server through a language-specific API. The Perl and Ruby interfaces rely
on the MySQL C API client library to handle the low-level client-server
protocol. This is also true for the PHP interface, unless PHP is
configured to use
mysqlnd, the native
protocol driver. For Perl and Ruby, you must install the C client library
and header files first. PHP includes the required MySQL client support
files, but must be compiled with MySQL support enabled or you won’t be
able to use it. The Python and Java drivers for MySQL implement the
client-server protocol directly, so they do not require the MySQL C client
You may not need to install the client software yourself—it might already be present on your system. This is a common situation if you have an account with an Internet service provider (ISP) that provides services such as a web server already enabled for access to MySQL.
If you need to install the MySQL C client library and header files, they’re included when you install MySQL from a source distribution, or when you install MySQL using a binary (precompiled) distribution other than an RPM binary distribution. Under Linux, you have the option of installing MySQL using RPM files, but the client library and header files are not installed unless you install the development RPM. (There are separate RPM files for the server, the standard client programs, and the development libraries and header files.) If you don’t install the development RPM, you’ll join the many Linux users who’ve asked, “I installed MySQL, but I cannot find the libraries or header files; where are they?”
General Perl information is available on the Perl Programming Language website.
You can obtain Perl software from the Comprehensive Perl Archive Network (CPAN).
To write MySQL-based Perl programs, you need the DBI module and the MySQL-specific DBD module, DBD::mysql.
To install these modules under Unix, let Perl itself help you. For
example, to install DBI and DBD::mysql, run the following commands
(you’ll probably need to do this as
perl -MCPAN -e shellcpan>
If the last command complains about failed tests, use
DBD::mysql instead. Under ActiveState Perl for
Windows, use the ppm utility:
You can also use the CPAN shell or ppm to install other Perl modules mentioned in this book.
Once the DBI and DBD::mysql modules are installed, documentation is available from the command line:
Documentation is also available from the Perl website.
The primary Ruby website provides access to Ruby distributions and documentation.
The Ruby DBI and MySQL driver modules are available from RubyGems; the Ruby DBI driver for
MySQL requires the
also available from RubyGems.
To use session support as described in Chapter 21, you need the
mysql-session package. It’s available from the
MySQL Cookbook companion website described earlier
in this Preface. Obtain the
mysql-session package, unpack it, and install
its mysqlstore.rb and sqlthrow.rb files in some directory that your
Ruby interpreter searches when looking for library files (see Writing Library Files).
The primary PHP website provides access to PHP distributions and documentation, including PDO documentation.
PHP source distributions include PDO support, so you need not obtain it separately. However, you must enable PDO support for MySQL when you configure the distribution. If you use a binary distribution, be sure that it includes PDO MySQL support.
For MySQL Connector/Python, the driver module that provides MySQL connectivity for the DB API, distributions and documentation are available from http://bit.ly/py-connect and http://bit.ly/py-dev-guide.
You need a Java compiler to build and run Java programs. The javac and jikes compilers are two possible choices. On many systems, you’ll find one or both installed already. Otherwise, you can get a compiler as part of the Java Development Kit (JDK). If no JDK is installed on your system, versions are available for Solaris, Linux, and Windows at Oracle’s Java site. The same site provides access to documentation (including the specifications) for JDBC, servlets, JavaServer Pages (JSP), and the JSP Standard Tag Library (JSTL).
Using Code Examples
This book is here to help you get your job done. In general, if example code is offered with this book, you may use it in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing a CD-ROM of examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission.
We appreciate, but do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: “MySQL Cookbook, Third Edition by Paul DuBois (O’Reilly). Copyright 2014 Paul DuBois, 978-1-449-37402-0.”
If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at email@example.com.
Safari® Books Online
Technology professionals, software developers, web designers, and business and creative professionals use Safari Books Online as their primary resource for research, problem solving, learning, and certification training.
Safari Books Online offers a range of product mixes and pricing programs for organizations, government agencies, and individuals. Subscribers have access to thousands of books, training videos, and prepublication manuscripts in one fully searchable database from publishers like O’Reilly Media, Prentice Hall Professional, Addison-Wesley Professional, Microsoft Press, Sams, Que, Peachpit Press, Focal Press, Cisco Press, John Wiley & Sons, Syngress, Morgan Kaufmann, IBM Redbooks, Packt, Adobe Press, FT Press, Apress, Manning, New Riders, McGraw-Hill, Jones & Bartlett, Course Technology, and dozens more. For more information about Safari Books Online, please visit us online.
How to Contact Us
Please address comments and questions concerning this book to the publisher:
|O’Reilly Media, Inc.|
|1005 Gravenstein Highway North|
|Sebastopol, CA 95472|
|800-998-9938 (in the United States or Canada)|
|707-829-0515 (international or local)|
We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at http://bit.ly/mysql_ckbk_3e.
To comment or ask technical questions about this book, send email to firstname.lastname@example.org.
For more information about our books, courses, conferences, and news, see our website at http://www.oreilly.com.
Find us on Facebook: http://facebook.com/oreilly
Follow us on Twitter: http://twitter.com/oreillymedia
Watch us on YouTube: http://www.youtube.com/oreillymedia
To each reader, thank you for reading my book. I hope that it serves you well and that you find it useful.
Thanks to my technical reviewers, Johannes Schlüter, Geert Vanderkelen, and Ulf Wendel. They made several corrections and suggestions that improved the text in many ways, and I appreciate their help.
Andy Oram prodded me to begin the third edition and served as its editor, Nicole Shelby guided the book through production, and Kim Cofer and Lucie Haskins provided proofreading and indexing.
Thanks to my wife Karen, whose encouragement and support throughout the writing process means more than I can say.