Preface
| The MySQL database management system has gained a large following in recent years. This has been true especially in the Linux and open source communities, but MySQL has an increasing foothold in the commercial sector as well. MySQL is well liked for several reasons: it's fast, and it's easy to set up, use, and administer. MySQL runs under many varieties of Unix and Windows, and MySQL-based programs can be written in many languages. Historically, MySQL has been especially popular for constructing database-backed web sites that involve dynamic content generation. Moreover, with the introduction of features in MySQL 5.0 such as views, triggers, and stored procedures and functions, the penetration of MySQL into other areas of application development is on the upswing. With MySQL's popularity comes the need to address the questions posed by its users about how to solve specific problems. That is the purpose of MySQL Cookbook. It's designed to serve as a handy resource to which you can turn when you need 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 attempt to develop full-fledged, complex applications. Instead, it's intended to assist 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 things that you'll learn from this book include:
One part of knowing how to use MySQL is understanding how to communicate with the serverthat is, how to use SQL, the language through which queries are formulated. Therefore, one major emphasis of this book is on 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. By using this client interactively, you can send SQL statements to the server and see the results. This is extremely useful because it provides a direct interface to SQL. The mysql client is so useful, in fact, that the entire first chapter is devoted to it. But the ability to issue SQL queries alone is not enough. Information extracted from a database often needs to be processed further or presented in a particular way to be useful. 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? Or 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 bookhow 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 the ability to exploit MySQL's capabilities in the following ways:
When you combine SQL with a general purpose programming language, you have an extremely flexible framework for issuing queries and processing their results. Programming languages increase your capabilities by giving you a great deal of additional power to perform complex database operations. This doesn't mean this book is complicated, though. It keeps things simple, showing how to construct small building blocks by 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 can be 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 ForThis book should 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 should also appeal to people who do not now use MySQL, but would like to. For example, it should be useful if you want to learn about databases but realize that a "big" database system such as Oracle isn't the best choice as a learning tool. If you're relatively new to MySQL, you'll probably find lots of ways to use it here that you hadn't thought of. If you're more experienced, you'll probably be familiar with many of the problems addressed here, but you 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 figuring out how to write the code from scratch. The book also can be useful for people who aren't even using MySQL. You might suppose that because this is a MySQL cookbook and not a PostgreSQL cookbook or an InterBase cookbook that it won't apply to database systems other than MySQL. To some extent that's true, because some of the SQL constructs are MySQL-specific. But many of the queries use standard SQL that is portable to many other database engines, so you should be able to use them with little or no modification. In addition, several programming language interfaces provide database-independent access methods; you use them the same way regardless of which type of database server you connect to. The material ranges from introductory to advanced, so if a recipe describes techniques that seem obvious to you, skip it. Or if you find that you don't understand a recipe, it may be best to set it aside for a while and come back to it later, perhaps after reading some of the preceding recipes. More advanced readers may wonder on occasion why, in a book on MySQL, I sometimes provide explanatory material on certain basic topics that are not directly MySQL-related, such as how to set environment variables. I decided to do this based on my experience in helping people who are just getting started with MySQL. One thing that makes MySQL attractive is that it is easy to use, which makes it a popular choice for people without extensive background in databases. However, many of these same people also tend to be thwarted by simple impediments to more effective use of MySQL, as evidenced by the common question, "How can I avoid having to type the full pathname of mysql each time I invoke it?" Experienced readers will recognize immediately that this is simply a matter of setting the PATH environment variable to include the directory where mysql is installed. But other readers will not, particularly Windows users who are used to dealing only with a graphical interface and, more recently, Mac OS X users who find their familiar user interface now augmented by the powerful but sometimes mysterious command line provided by the Terminal application. If that describes you, I hope that you'll find these more elementary sections helpful for knocking down barriers that keep you from using MySQL more easily. If you're a more advanced user, just skip over such sections. What's in This BookIt's very likely when you use this book that you'll have an application in mind you're trying to develop but are not sure how to implement certain pieces of it. In this case, you'll already know what type of problem you want to solve, so you should search the table of contents or the index looking for a recipe that shows how to do what you want. Ideally, the recipe will be just what you had in mind. Failing that, you should be able to find a recipe for a similar problem that you can adapt to suit the issue at hand. I try to explain the principles involved in developing each technique so that you'll be able to 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 help you because it will 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 have a general familiarity with it and know the kinds of problems it addresses. As you get into later chapters, you'll sometimes 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 covered. You should find that it's been explained earlier. For example, if you find that a recipe sorts a query result using an ORDER BY clause that you don't understand, turn to Chapter 7, which discusses various sorting methods and explains how they work. The following paragraphs summarize 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 number output lines or make long lines more readable, how to generate various output formats, and how to log mysql sessions. Chapter 2, Writing MySQL-Based Programs, demonstrates the basic 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 describes various ways to gather the parameters needed for making connections to the server. Chapter 3, Selecting Data from Tables, covers several aspects of the SELECT statement, which is the primary vehicle for retrieving data from the MySQL server: specifying which columns and rows you want to retrieve, performing comparisons, dealing with 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. You should read it if you need some introductory background on row selection or you 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 FULLTEXT searches. 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 special TIMESTAMP data type, how to set the time zone, conversion between different temporal units, how to perform date arithmetic to compute intervals or generate one date from another, and 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 ENUM values. Chapter 8, Generating Summaries, shows techniques that are useful for assessing the general characteristics of a set of data, such as how many values it contains or what its minimum, maximum, or average values are. Chapter 9, Obtaining and Using 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 type of each column. It also shows how to ask MySQL what databases and tables are available or find out about the structure of a table and its columns. Chapter 10, Importing and Exporting Data, describes how to transfer information between MySQL and other programs. This includes how to convert files from one format to another, extract or rearrange columns in datafiles, check and validate data, rewrite values such as dates that often come in a variety of formats, and how to figure out which data values cause problems when you load them into MySQL with LOAD DATA. Chapter 11, Generating and Using Sequences, discusses AUTO_INCREMENT columns, 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, how to begin a sequence at a given value, and how to set up a table so that it can maintain multiple sequences at once. It also shows how to use AUTO_INCREMENT values to maintain a master-detail relationship between tables, including some of the pitfalls to avoid. Chapter 12, Using Multiple Tables, shows how to perform joins, which are operations that combine rows in one table with those from another. It demonstrates how to compare tables to find matches or mismatches, produce master-detail lists and summaries, enumerate many-to-many relationships, and update or delete rows in one table based on the contents of another. Chapter 13, 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 a row at random from the set. Chapter 14, Handling Duplicates, discusses how to identify, count, and remove duplicate rowsand how to prevent them from occurring in the first place. Chapter 15, 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, and demonstrates some workarounds you can use for non-transactional storage engines. Chapter 16, Using Stored Routines, Triggers, and 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 17, 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. It also provides an overview of the Java Standard Tag Library (JSTL) that is used heavily for JSP pages in the following chapters. Chapter 18, Incorporating Query Results in Web Pages, shows how to use the results of queries to produce various types of 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 send a downloadable result set to a browser. The chapter also includes a section that demonstrates how to use a template package to generate web pages. Chapter 19, 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 20, 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 when you want to collect information in stages, or when you need to make decisions based on what the user has done earlier. Appendix A, Obtaining MySQL Software, indicates where to get the source code for the examples shown in this book, and where to get the software you need to use MySQL and write your own database programs. Appendix B, Executing Commands from the Command Line, provides background on executing commands at the command prompt and how to set environment variables such as PATH. Appendix C, JSP and Tomcat Primer, provides a general overview of JSP and installation instructions for the Tomcat web server. Read this if you need to install Tomcat or are not familiar with it, or if you're never written pages using JSP notation. Appendix D, References, lists sources of information that provide additional information about topics covered in this book. It also lists some books that provide introductory background for the programming languages used here. MySQL APIs Used in This BookMySQL programming interfaces exist for many languages, including C, C++, Eiffel, Java, Pascal, Perl, PHP, Python, Ruby, Smalltalk, and Tcl. Given this fact, writing a MySQL cookbook presents an author with something of a challenge. Clearly, 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 would result either in covering very few recipes or in a very, very large book! It would also result in a lot of redundancy 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 language often will be more suitable than another for solving a particular type of problem. To resolve this dilemma, I've picked a small number of APIs from among those that are available and used them to write the recipes in this book. This limits its scope to a manageable number of APIs while allowing some latitude to choose from among them:
Why these languages? Perl and PHP were easy to pick. Perl is arguably the most widely used language on the Web, and it became so based on certain strengths such as its text-processing capabilities. In particular, it's very popular for writing MySQL programs. PHP also is widely deployed. 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 significant numbers 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. Ruby was not covered in the first edition, but I have included it for the second because Ruby is much more popular now and it has an easy to use database-access module modeled after the Perl module. 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, you can still use this book, but 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 understand the recipes in later chapters so that you can translate them for other languages. Conventions Used in This BookThe following font conventions are used throughout the book:
NOTE
This icon indicates a tip, suggestion, or general note.
Commands often are shown with a prompt to illustrate the context in which they are used. Commands that you issue from the command line are shown with a % prompt: % chmod 600 my.cnf That prompt is one that Unix users are used to seeing, but it doesn't necessarily signify that a command will work only under Unix. Unless indicated otherwise, commands shown with a % prompt generally should work under Windows, too. If you should run a command under Unix as the root user, the prompt is # instead: # perl -MCPAN -e shell Commands that are specific only to Windows use the C:\> prompt: C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysql" SQL statements that are issued from within the mysql client program are shown with a mysql> prompt and terminated with a semicolon: mysql> 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, of which those in the middle have been omitted: mysql> 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 the mysql> prompt, but they do include semicolons as necessary to make it clearer where statements end. For example, this is a single statement: CREATE TABLE t1 (i INT) SELECT * FROM t2; But this example represents two statements: CREATE TABLE t1 (i INT); SELECT * FROM t2; 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), you should not include terminating semicolons. The MySQL Cookbook Companion Web SiteMySQL Cookbook has a companion web site that you can visit to obtain the source code and sample data for examples developed throughout this book:
The sample data and errata are also available at the O'Reilly web site listed in the "How to Contact Us" section. The main software distribution is named recipes, and you'll find many references to it throughout the book. You can use the distribution to save a lot of typing. For example, when you see a CREATE 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 of the recipes distribution that you can use to create the table instead of entering the definition manually. Change location into the tables directory, and then execute the following command, where filename is the name of the file containing the CREATE TABLE statement: % mysql cookbook < filename If you need to specify MySQL username or password options, put them before the database name. For more information about the recipes distribution, see Appendix A. The Kitebird site also makes some of the examples from the book available online so that you can try them from your browser. Version and Platform NotesDevelopment of the code in this book took place under MySQL 5.0 and 5.1. Because new features are added to MySQL on a regular basis, some examples will not work under older versions. For example, MySQL 5.0 introduces views, triggers, and stored procedures and functions, and the INFORMATION_SCHEMA metadata database. MySQL 5.1 introduces events. On occasion, I point out workarounds that you can use in MySQL 4.1 to compensate for the lack of a 5.0 feature. For example, the INFORMATION_SCHEMA database is used extensively for getting information about table structure, but does not exist prior to MySQL 5.0. Sometimes this information can be obtained by using statements such as SHOW COLUMNS, and that's what recipes in the first edition used. These recipes are still available from the Kitebird site as part of the recipes distribution for the first edition. If you have an older version of MySQL, you may find it worthwhile to obtain a copy of the older distribution. The versions of the database API modules that were current as of this writing are Perl DBI 1.52, DBD::mysql 3.0, Ruby DBI 0.1.1, PEAR DB 1.7.6, MySQLdb 1.2.0, and MySQL Connector/J 3.1. Perl DBI requires Perl 5.6 or higher, although it's slated to require Perl 5.8 or higher beginning with DBI 1.51. Ruby DBI requires Ruby 1.8.0 or higher. Most PHP scripts shown here will run under either PHP 4.1 or PHP 5 (I recommend PHP 5). MySQLdb requires Python 2.3.4 or higher. MySQL Connector/J requires Java SDK 1.2 or higher (1.4 or higher is recommended). 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 should be applicable both to Unix and Windows. The operating systems I used most to develop the recipes in this book were Mac OS X, Gentoo Linux, and Windows XP. If you use Windows, I assume that you're using a relatively recent version such as Windows 2000 or XP. Some features discussed in this book are not supported on older non-NT-based versions such as Windows Me or 98. I do assume that MySQL is installed already and available for you to use. I also assume that if you plan to write your own MySQL-based programs, you're reasonably familiar with the language you'll use. If you need to install software, see Appendix A. If you require background material on the programming languages used here, see Appendix D. Upgrade Note for First Edition ReadersIf you have the first edition of MySQL Cookbook, be warned that the PHP library files in the second edition have the same names as in the first edition, but are incompatible with them. If you are using any PHP scripts that depend on the first edition library files, they will break if you install the second edition library files on top of them. To prevent this from happening, use the following procedure:
Now you can install the second edition library files on top of the first edition files that have the same names. PHP scripts from the second edition will use these new library files, and old scripts should continue to work correctly. Additional ResourcesAny language that attracts a following can benefit from the efforts of its user community because people who use the language produce code that they make available to others. Perl in particular is served by an extensive support network designed to provide external modules that are not distributed with Perl itself. This is called the Comprehensive Perl Archive Network (CPAN), a mechanism for organizing and distributing Perl code and documentation. CPAN contains modules that enable database access, web programming, and XML processing, to name a few of direct relevance to this cookbook. External support exists for the other languages as well: Ruby has the Ruby Application Archive, PHP has the PEAR archive, and Python has a module archive called the Vaults of Parnassus. For Java, a good starting point is Sun's Java site. The following table shows sites that you can visit to find more information.
Using Code ExamplesThis book is here to help you get your job done. In general, you may use the code in this book 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, Second Edition, by Paul DuBois. Copyright 2007 O'Reilly Media, Inc., 978-0-596-52708-2." If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at permissions@oreilly.com. Safari® EnabledNOTE
When you see a Safari® Enabled icon on the cover of your favorite technology book, that means the book is available online through the O'Reilly Network Safari Bookshelf. Safari offers a solution that's better than e-books. It's a virtual library that lets you easily search thousands of top tech books, cut and paste code samples, download chapters, and find quick answers when you need the most accurate, current information. Try it for free at http://safari.oreilly.com. (http://safari.oreilly.com) How to Contact UsPlease address comments and questions concerning this book to the publisher:
We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at:
To comment or ask technical questions about this book, send email to:
For more information about our books, conferences, Resource Centers, and the O'Reilly Network, see our web site at:
AcknowledgmentsSecond EditionThanks to my technical reviewer, Richard Sonnen, who spotted a number of problems and made many suggestions to improve the book. Andy Oram prodded me to begin the second edition and served as its initial editor. When other duties called him away, Brian Jepson took over. My thanks to both for providing editorial oversight and direction. Adam Witwer directed the production process. Mary Anne Mayo copyedited the text, and Sada Preisch proofread it. Joe Wizda wrote the index. Thanks to my wife Karen, who again provided valuable support during the process of producing this new edition. First EditionI'd like to thank my technical reviewers, Tim Allwine, David Lane, Hugh Williams, and Justin Zobel. They made several helpful suggestions and corrections with regard to both organizational structure and technical accuracy. Several members of MySQL AB were gracious enough to add their comments: In particular, principal MySQL developer Monty Widenius combed the text and spotted many problems. Arjen Lentz, Jani Tolonen, Sergei Golubchik, and Zak Greant reviewed sections of the manuscript as well. Andy Dustman, author of the Python MySQLdb module, and Mark Matthews, author of MM.MySQL and MySQL Connector/J, also provided feedback. My thanks to all for improving the manuscript; any errors remaining are my own. Laurie Petrycki, executive editor, conceived the idea for the book and provided valuable overall editorial guidance and cattle-prodding. Lenny Muellner, tools expert, assisted in the conversion of the manuscript from my original format into something printable. David Chu acted as editorial assistant. Ellie Volckhausen designed the cover, which I am happy to see is reptilian in nature. Linley Dolby served as the production editor and proofreader, and Colleen Gorman, Darren Kelly, Jeffrey Holcomb, Brian Sawyer, and Claire Cloutier provided quality control. Some authors are able to compose text productively while sitting at a keyboard, but I write better while sitting far from a computerpreferably with a cup of coffee. That being so, I'd like to acknowledge my debt to the Sow's Ear coffee shop in Verona for providing pleasant surroundings in which to spend many hours scribbling on paper. My wife Karen provided considerable support and understanding in what turned out to be a much longer endeavor than anticipated. Her encouragement is much appreciated, and her patience something to marvel at. |
