MySQL Cookbook

• #20 in Best Mysql Books

27 Reviews

Sort by: Most Helpful ▲ Date Rating

I am an experienced web developer and recently started using Perl with MySQL to build database-driven CGI applications.
MySQL Cookbook by Paul DuBois turned out to be a valuable reference resource.

While the title mentions only MySQL, the book provides a great deal of solutions (and code) for using MySQL with Perl, PHP, Python and Java. If you are familiar with any of these computer languages, this book and MySQL manual is all you need to start building applications with MySQL-compatible database backend.

I built several web sites using PHP/MySQL and the only two books I use day-to-day are "MySQL Cookbook" and "PHP Cookbook".

If you're already somewhat familiar with MySQL, this book is a great reference for the SQL and database manipulation you already know how to do. In addition, the examples push the envelope of what is possible with MySQL making it a good learning tool for becoming truly expert.

Some of the information in the book I found useful includes:

- simplifying complex SQL using temporary tables.

- using FULLTEXT searches (similar to pattern matching but more efficient and easier to code when you're looking for the same text in several columns at the same time).

- effective use of LEFT JOIN's for finding rows in one table that don't have a match in another table.

- effective use of LAST_INSERT_ID( fieldName + 1 ) for transactionally updating a numeric field without using transactions and without locking a table.

- numbering output rows using SQL variables (select @rownum := @rownum + 1; (see chapter 13.9))

I really can't say enough good things about this book. It's comprehensive, easy to read and just plain awesome.

There are many databases out there. MySQL is one of the better ones. It's a free (for personal use) database system which can be easily integrated into a web application on virtually any system. It has supports most of the standard feature found in most database system and has quiet a few features unique to MySQL. This particular book is a good reference for the experienced user as well as for new comers and as an added bonus even covers MySQL 4.0.

This is book was my first introduction to O'Reilly's cookbook series. It provides solutions to some of the most common challenged faced by the particular subject being covered (in this case MySQL). I thoroughly enjoyed it and was quiet impressed with it. Too many technical books simply introduce the concept without relating it to real world applications.

This particular book introduces all of the most basic concepts of database manipulation (table creation, data insertion, data deletion, data update). As well as writing simple and advanced SQL statements to retrieve data. It approaches database design using 4 of the most popular languages (Perl, PHP, Python and Java). These are only a few of the many possible languages which can be used to manipulate a MySQL database.

MySQL cookbook touches on a variety of different topics which I don't have the space or time to cover in detail, but here is a list of them:

* Handeling duplicates
* MySQL on the Web
* Processing Web input with MySQL
* Using MySQL-based Web Session Management

One of my favorite topics covered in the book is the idea of storing binary data such as images within a database. Although not ideal for most cases (unless you need fast access to a vast array of images), just the idea of it has a certain kewlness effect.

Well, overall I give it 4 out of 5 stars. It needs to touch slightly more on the basic concepts of databases, and it can become the only book you'll ever need for MySQL.

This book is full of examples. This is perfect for a learn-by-example programmer like me. It makes a handy reference, too. He has examples and tips for just about everything the typical MySQL coder would ever need to do. Excellent reference - I highly recommend it.

I am a big fan of hands-on books. I especially like the `From Scratch` and `Cookbook` type titles.

Well, lucky me. When I attended the 2005 MySQL Users Conference in Santa Clara CA in April '05, I received the "MySQL Cookbook" by Paul DuBois as a reward for early registration.

The day after I returned back to work from the conference, one of my co-workers came looking for some guidance in regards to a MySQL database issue he was having. His description sounded familiar to one of the recipes I skimmed in the cookbook. Sure enough, I found the resolution within two minutes, and had sent
another satisfied customer on his way.

The great aspect of this book is that it is written to provide solutions to over 315 real-world problems. Whenever I come up against an obstacle in regards to MySQL, I head straight to the table of contents and scan for a recipe that looks like it might be a match.

One topic I wish was covered somewhere in this text is the concept of derived tables, which are basically sub-queries used as tables in a join.

(...)
The "MySQL Cookbook" and "MySQL in a Nutshell" books are permanent fixtures in my office ... except for when my co-workers borrow them.

-Brett Berry
PERL/PHP/JavaScript/MySQL
(...)

This book is well done, clearly written and sensibly organized. I read the same author's New Riders Book on MySQL and found it similarly useful; but if you've already got all the theory down, this may well be the only MySQL book you'll need. Wish I'd had it a few years ago.

As an experienced Oracle DBA I had a need to learn about MySQL for the development of the FmPro Migrator utility to migrate FileMaker databases to MySQL. This book enabled me to quickly learn how to perform tasks in MySQL which are equivalent to what I would perform with Oracle. Congratulations to O'Reilly and Paul DuBois for continuing the fine tradition of Cookbook series books.

At ~950 pages, the 'MySQL Cookbook' is an EXTENSIVE look at using MySQL from top to bottom. Upgrading this cookbook with lots of new material and Ruby examples, Paul DuBois takes a great book and makes it that much better. There is SO much material it would be difficult to easily condense it all, but I can provide a chapter listing that should make it easier to see all the yummies included within:

01. Using the mysql Client Program
02. Writing MySQL-based Programs
03. Selecting Data
04. Table Management
05. Strings
06. Dates and Times
07. Sorting Query Results
08. Generating Summaries
09. Obtaining and Using Metadata
10. Importing/Exporting Data
11. Generating and Using Sequences
12. Using Multiple Tables
13. Statistical Techniques
14. Handling Duplicates
15. Transactions
16. Stored Procedures, Triggers, Events
17. MySQL on the Web
18. Incorporating Query Results into Web Pages
19. Processing Web Input with MySQL
20. Using MySQL-Based Web Session Management

Add on 4 appendixes and 250+ tips/tricks/recipes/yummies (yes 250!) and you have an indispensable reference for any and all MySQL developers and/or administrators. Solid layout and regular fantastic O'Reilly writing make this a MUST HAVE book for MySQL users. Go out and get it now!!

***** HIGHLY RECOMMENDED

This 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. 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. 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.

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.

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 rows--and 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.

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.

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.

I had a recent perl+mysql project and flipped between the Cookbook and the author's other work, MySQL, mentioned in other reviews. ( also referred to the O'Reilly Perl books _Programming Perl_ and _Perl for System Administration_. I don't usually have time to master any topic or skillset; I need to get in, get it going, and move on. This book is perfect for that. "How do I get the date?" There's a recipe for that. "How do I get perl to connect to the database and insert stuff?" There's a recipe. Also for PHP, python, etc. It's really focused on problem solving, and the problems are really well chosen. This book will save you hours of effort.

The best thing about the book is that all you need is a rough inkling of what you want to do and you get an immediate kick start. As an added bonus, skimming quickly through the entire book is a great refresher course if you are at all rusty. Any additional details you can get from Paul Dubois' equally excellent MySQL book (New Riders 2000). Handily wins five stars!

The MySQL Cookbook removes some abstraction from reading the docs. The recipes are concise answers to real-world questions about how to accomplish specific goals with MySQL. It covers common vexing problems that would require significant spelunking in the manuals or web search engines. The recipes provide more clarifying context than the manual, making it easier to learn how to use all those arcane features you had barely noticed. Great as a reference or, upgrade your brain by reading a few entries a day.

In this "MySQL Cookbook", Paul DuBois ensured that a wide range of topics were covered. In fact, most Unix and Mac OS X users would cherish the contents of this text. Comprehensive information were presented in a very uncomplicated way. The book did advanced analysis of all MySQL-based scripts which are applicable to Java, Perl, Python and PHP. The same story goes for MySQL-based web scripts under Apache and Tomcat. It gave readers sufficient guidance to codes, using time-saving illustrative examples. However, beginners who need to start from the scratch may not appreciate this advanced approach.
In conclusion, this is a dependable text that both intermediate and advanced MySQL learners would appreciate.

As indicated, this book is true to its title. It is a cookbook, by which I mean it contains a great number of (generally useful) recipes of varying complexity, but lacks detail and analysis that a more focused text would have.

Mr. DuBois and O'Reilly publishing are clearly targeting a wide audience: the recipes range from frighteningly simple (e.g. 3.1, Specifying Which Columns to Select) to fairly sophisticated (e.g. 12.14, Performing a Join Between Tables in Different Databases). The recipes are typically clearly written, with ample supporting code examples and few typographical errors.

I must also note that Mr. DuBois nicely avoids a pitfall many authors (the competence of whom I have to question) in this genre encounter: failure to weave security considerations into the text. While other books often mention security as an afterthought, or worse include code examples featuring disasters like non-escaped strings (hello, SQL injection!), DuBois explicitly points out the need to sanitize input and writes code examples that demonstrate the use of prepared statements in best-practices.

Unfortunately, in other areas he is less thorough. For example, only passing mention is made of the (possible) dependence of FULLTEXT indexes on choice of storage engine. There is an entire chapter on handling duplicate rows, but the oft-needed (and non-obvious) process for removing pseudo-duplicate rows differing only by a primary key field is not directly addressed. Stored procedures, triggers, and other new additions to MySQL are among the least-well understood but most powerful features of the database engine, yet astonishingly little space is spent on them. (I could understand not discussing them in depth as there are other books available, but length did not seem to be a concern anywhere else in this book.)

In summary, the book is an excellent resource for novices and experts alike - but only as a starting point. To return to the cooking analogy, the book at times feels like it is loaded down with recipes on how to add sugar to flour, but omits recipes on how to ice the cake.

If this was a quick reference book, then the information contained would be useful. This, unfortunately is a big book containing a little book's amount of useful info. Save your money and print out the mysql users guide that is available online for free. I found myself using the online guide as a reference even when the book was in front of me.

Bottom line: A wide range of methods for doing things you probably already know how to do if you are using mysql.

Visuals
Good. Page numbers and section title can be found on the bottom of every page.

Audience
From the book: "This book should be useful for anybody who uses MySQL, ranging from individuals [...] to professional database and web developers."

That is a pretty broad range, basically from introductory to advanced. Nevertheless I would have to say that the book meets this challenge well. This book should be of use to anybody who uses MySQL.

Practicality:
I have to say that I have fallen for Cookbook style books. While I still thoroughly enjoy more theory heavy books, cookbooks can really deliver in the immediate practicality department.

The MySQL Cookbook is a good example of this. This book has dozens upon dozens of recipes that will likely have some measure of application in whatever MySQL dependent system you are working on.

Each recipe contains a brief "Problem" scenario, followed by an often equally brief "Solution", and finally a more lengthy discussion that shows how to actually implement the solution.

Overall
I am very pleased with this book. Mr. DuBois and O'Reilly did a great job. For instance, one of the things I especially like is that often multiple solutions are offered. For example if you were to look up Section 6.6 "Extracting Parts of Dates or Times" you will find the solution lists 3 possible options. The discussion section contains 5 pages of examples of how you might use those options to extract the desired data.

If you fit in to the intended audience of "anybody who uses MySQL", I would highly recommend this book.

The MySQL Cookbook weighs in at over 940 pages and will help you find quick answers to everything from beginner-level basics up through dba and software developer tricks.

The book is a collection of real-world tasks and compliments Paul's DuBois' other MySQL books very well. It goes beyond basic use and admin-level goodies - it has loads of code examples as well. As a software developer using MySQL for many projects, I find the MySQL Cookbook indispensible.

If you deal with MySQL on a daily or weekly basis then this book is a perfect go-to. So many scenarios (recipes) are covered, from simple to fairly advanced.

This second edition is almost twice as thick as the first, but that doesn't mean it's too much to handle since you wouldn't entirely read it from cover to cover because you'll want to go try out so much of it.

This really is worth space on a shelf (digital or not). I would prefer the digital edition since it'd be a breeze to search through.

Paul DuBois' MY SQL COOKBOOK, 2nd EDITION isn't your usual guide to applications development: it IS a 'bible' of applications routines that helps designers move beyond problems they experience in the MySQL environment. This latest edition covers MySQL 5.0 and offers 'recipes' explaining how and why the code works to encourage more detailed learning rather than quick fixes alone. From accessing data from multiple tables to storing images and displaying them in web pages, any serious MySQL programmer or computer reference library needs this reference.

Excellent text well written and logical sequencing of chapters.

Very good for students in their second or third semester of RDBMS studies.

I do not recomend for beginning relational databse students.

This book is
-a great resource for those looking to implement various mysql functions
-a great resource for database programmers
-a trove of information on powerful query and sort techniques

Finding information about the topic you're looking for is a breeze in this book - the chapters are well-organized, and this book has anwered all questions I've posed to it....

The cookbook is a powerful tool to those who know some mysql, and some dB design. I recommend that you use another book to learn basic mysql (or just an online tutorial), and another to learn database design (Navathe).

While I'm very familiar with working with relational databases, my MySQL skills are weak. This book was exactly what I needed while building a new web application that uses MySQL. For every question I had, I found the answer in this book. Highly recommended.

As with most of the O'Reilly programming languages cookbooks I think this one is excellent. The problem/solution format helps to quickly drill down to what one wants to know. I wish that there was more discussion of the operating system tablespaces (ie. file systems).

this is a great book. It has tons of recipes for those 'little things' that could take a lot of time to tackle, but that if you know how to do them, you can sort them out in no time. An excellent book that has helped me to get quick results.

You need to have some knowledge about relational databases and sql and queries. This is not a book for beginners, but rather for someone with a sound knowledge of relational databases, queries and sql, that just don't know the exact syntax for those queries in mysql.

I give this book full 5 stars.

I really enjoy cookbooks because of their no nonsense organization. This MySQL book exactly fits that description. Chances are just scanning the table of contents you'll find the question you are asking with the answer and explanation at your fingertips.

Nuggets of information I found quickly include: paging producing HTML output with the mysql client, checking and changing a tables storage engine, working with fulltext searches, summary and grouping query examples, importing data from CSV, exporting in various formats, dealing with auto_increment columns, sequences, deletes, gaps, and so on. Also handling duplicates, detecting, eliminating, working with transactions, and a whole lot more.

The book is mainly geared towards web applications and MySQL. The languages that they cover include Perl, Ruby, PHP, Java, Python. If you're using any of those languages for your web application, it is more than likely you want a copy of this book. It's very quick and painless to lookup how to do something, and not have to wade through tons of information that's not relevant to you.

The target audience for this book is really developers not DBAs, so keep that in mind. If you're doing web development in Ruby, PHP, Perl, Python or Java, you should consider this book. It concentrates solely on the how to do specific things, so you won't get weighed down by too much theory and so on. With that in mind, it's not per se a book on performance, testing, benchmarking and profiling applications in those languages, so if that is what you need, you'll require additional material.

At 900+ pages this book addresses a lot of common SQL tasks. And that's what a good cookbook should do. Not only does it present the SQL queries in the MySQL dialect, it also covers how to pump those queries through Pearl, Ruby, PHP, Python, and Java APIs.
As an added bonus you can actually use this book to learn SQL. I thought the chapter on JOINS was especially well written.

This book is useful for anyone looking to find examples of MySQL 4 usage in multiple languages such as PHP, Perl, Java, and Python. It covers many areas that are used in real world applications and is great as a reference material. This book is structured in the Problem/Solution layout of the Oreilly cookbook series.