Cover | Table of Contents

Table of Contents

Chapter 1: Using the mysql Client Program

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The MySQL database system uses a client-server architecture that centers around the server , mysqld. The server is the program that actually manipulates databases. Client programs don’t do that directly. Instead, they communicate your intent to the server by means of statements written in Structured Query Language (SQL). Client programs are installed locally on the machine from which you want to access MySQL, but the server can be installed anywhere, as long as clients can connect to it. MySQL is an inherently networked database system, so clients can communicate with a server that is running locally on your machine or one that is running somewhere else, perhaps on a machine on the other side of the planet. Clients can be written for many different purposes, but each interacts with the server by connecting to it, sending SQL statements to it to have database operations performed, and receiving the statement results from it.

One such client is the mysql program that is included in MySQL distributions. When used interactively, mysql prompts you for a statement, sends it to the MySQL server for execution, and then displays the results. This capability makes mysql useful in its own right, but it’s also a valuable tool to help you with your MySQL programming activities. It’s often convenient to be able to quickly review the structure of a table that you’re accessing from within a script, to try a statement before using it in a program to make sure that it produces the right kind of output, and so forth. mysql is just right for these jobs. mysql also can be used noninteractively; for example, to read statements from a file or from other programs. This enables you to use mysql from within scripts or cron jobs or in conjunction with other applications.

This chapter describes mysql’s capabilities so that you can use it more effectively:

• Starting and stopping

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 2: Writing MySQL-Based Programs

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter discusses how to write programs that use MySQL. It covers basic application programming interface (API) operations that are fundamental to the programming recipes developed in later chapters. These operations include connecting to the MySQL server, issuing statements, and retrieving the results.

MySQL-based client programs can be written using several languages. The languages covered in this book are Perl , Ruby, PHP, Python, and Java, for which we’ll use the interfaces in the following table. indicates where to get the software for each interface.

Language	Interface
Perl	Perl DBI
Ruby	Ruby DBI
PHP	PEAR DB
Python	DB-API
Java	JDBC

MySQL client APIs provide the following capabilities, each of which is covered in a section of this chapter:

Connecting to the MySQL server, selecting a database, and disconnecting from the server

Every program that uses MySQL must first establish a connection to the server, and most programs also select a default database to use. In addition, well-behaved MySQL programs close the connection to the server when they’re done with it.

Checking for errors

Many people write MySQL programs that perform no error checking at all. Such programs are difficult to debug when things go wrong. Any database operation can fail and you should know how to find out when that occurs and why. That enables you to take appropriate action such as terminating the program or informing the user of the problem.

Executing SQL statements and retrieving results

The whole point of connecting to a database server is to execute SQL statements. Each API provides at least one way to do this, as well as several methods for processing statements results.

Handling special characters and values in statements

One way to write a statement that refers to specific data values is to embed the values directly in the statement string. However, some characters such as quotes and backslashes have special meaning, and you must take certain precautions when constructing statements containing them. The same is true for

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 3: Selecting Data from Tables

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter focuses on the SELECT statement, which retrieves database information. It shows how to use SELECT to tell MySQL what you want to see. You should find the chapter helpful if your SQL background is limited or if you want to find out about the MySQL-specific extensions to SELECT syntax.

There are so many ways to write SELECT statements that we’ll look at only a few of them. Consult the MySQL Reference Manual or a general MySQL text for more information about SELECT syntax and the functions and operators that you can use to extract and manipulate data.

SELECT gives you control over several aspects of row retrieval:

• Which table to use
• Which columns and rows to retrieve from the table
• How to name the output columns
• How to sort the rows

Many useful queries are quite simple and don’t specify all those things. For example, some forms of SELECT don’t even name a table—a fact used earlier in , which discusses how to use mysql as a calculator. Other nontable-based queries are useful for purposes such as determining what version of the server you’re running or the name of the default database:

mysql>SELECT VERSION(), DATABASE();
+------------+------------+
| VERSION()  | DATABASE() |
+------------+------------+
| 5.0.27-log | cookbook   |
+------------+------------+

To answer more involved questions, normally you’ll need to pull information from one or more tables. Many of the examples in this chapter use a table named mail, which contains rows that track mail message traffic between users on a set of hosts. The mail table definition looks like this:

CREATE TABLE mail
(
  t       DATETIME, # when message was sent
  srcuser CHAR(8),  # sender (source user and host)
  srchost CHAR(20),
  dstuser CHAR(8),  # recipient (destination user and host)
  dsthost CHAR(20),
  size    BIGINT,   # message size in bytes
  INDEX (t)
);

And its contents look like this:

+---------------------+---------+---------+---------+---------+---------+
| t                   | srcuser | srchost | dstuser | dsthost | size    |
+---------------------+---------+---------+---------+---------+---------+
| 2006-05-11 10:15:08 | barb    | saturn  | tricia  | mars    |   58274 |
| 2006-05-12 12:48:13 | tricia  | mars    | gene    | venus   |  194925 |
| 2006-05-12 15:02:49 | phil    | mars    | phil    | saturn  |    1048 |
| 2006-05-13 13:59:18 | barb    | saturn  | tricia  | venus   |     271 |
| 2006-05-14 09:31:37 | gene    | venus   | barb    | mars    |    2291 |
| 2006-05-14 11:52:17 | phil    | mars    | tricia  | saturn  |    5781 |
| 2006-05-14 14:42:21 | barb    | venus   | barb    | venus   |   98151 |
| 2006-05-14 17:03:01 | tricia  | saturn  | phil    | venus   | 2394482 |
| 2006-05-15 07:17:48 | gene    | mars    | gene    | saturn  |    3824 |
| 2006-05-15 08:50:57 | phil    | venus   | phil    | venus   |     978 |
| 2006-05-15 10:25:52 | gene    | mars    | tricia  | saturn  |  998532 |
| 2006-05-15 17:35:31 | gene    | saturn  | gene    | mars    |    3856 |
| 2006-05-16 09:00:28 | gene    | venus   | barb    | mars    |     613 |
| 2006-05-16 23:04:19 | phil    | venus   | barb    | venus   |   10294 |
| 2006-05-17 12:49:23 | phil    | mars    | tricia  | saturn  |     873 |
| 2006-05-19 22:21:51 | gene    | saturn  | gene    | venus   |   23992 |
+---------------------+---------+---------+---------+---------+---------+

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 4: Table Management

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter covers topics that relate to creating and populating tables:

• Cloning a table
• Copying one table to another
• Using temporary tables
• Generating unique table names
• Determining what storage engine a table uses or converting a table to use a different storage engine

To create and load the mail table used for examples in this chapter, change location into the tables directory of the recipes distribution, and run this command:

%mysql cookbook < mail.sql

You need into create a table that has exactly the same structure as an existing table.

Use CREATE TABLE ... LIKE to clone the table structure. If it’s also necessary to copy some or all of the rows from the original table to the new one, use INSERT INTO ... SELECT.

When you need to create a new table that is just like an existing table, use this statement:

CREATE TABLEnew_table LIKE original_table;

The structure of the new table will be exactly the same as that of the original table, with a few exceptions: CREATE TABLE ... LIKE does not copy foreign key definitions, and it doesn’t copy any DATA DIRECTORY or INDEX DIRECTORY table options that the table might use.

The new table will be empty. If you also need to copy the rows from the original table to the new table, use an INSERT INTO ... SELECT statement:

INSERT INTOnew_table SELECT * FROM original_table;

To copy only part of the table, add an appropriate WHERE clause that identifies which rows to copy. For example, these statements create a copy of the mail table named mail2 and populate it with the rows only for mail sent by barb:

mysql>CREATE TABLE mail2 LIKE mail;
mysql> INSERT INTO mail2 SELECT * FROM mail WHERE srcuser = 'barb';
mysql> SELECT * FROM mail2;
+---------------------+---------+---------+---------+---------+-------+
| t                   | srcuser | srchost | dstuser | dsthost | size  |
+---------------------+---------+---------+---------+---------+-------+
| 2006-05-11 10:15:08 | barb    | saturn  | tricia  | mars    | 58274 |
| 2006-05-13 13:59:18 | barb    | saturn  | tricia  | venus   |   271 |
| 2006-05-14 14:42:21 | barb    | venus   | barb    | venus   | 98151 |
+---------------------+---------+---------+---------+---------+-------+

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 5: Working with Strings

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Like most types of data, string values can be compared for equality or inequality or relative ordering. However, strings have some additional features to consider:

• A string can be binary or nonbinary. Binary strings are used for raw data such as images, music files, or encrypted values. Nonbinary strings are used for character data such as text and are associated with a character set and collation (sorting order).
• A character set determines which characters are legal in a string. Collations can be chosen according to whether you need comparisons to be case-sensitive or case-insensitive, or to use the rules of a particular language.
• Data types for binary strings are BINARY, VARBINARY, and BLOB. Data types for nonbinary strings are CHAR, VARCHAR, and TEXT, each of which allows CHARACTER SET and COLLATE attributes. See for information about choosing data types for string columns.
• You can convert a binary string to a nonbinary string and vice versa, or convert a nonbinary string from one character set or collation to another.
• You can use a string in its entirety or extract substrings from it. Strings can be combined with other strings.
• You can apply pattern-matching operations to strings.
• FULLTEXT searching is available for efficient queries on large collections of text.

This chapter discusses how to use all those features, so that you can store, retrieve, and manipulate strings according to whatever requirements your applications have.

Scripts to create the tables used in this chapter can be found in the tables directory of the recipes distribution.

One property of a string is whether it is binary or nonbinary:

• A binary string is a sequence of bytes. It can contain any type of information, such as images, MP3 files, or compressed or encrypted data. A binary string is not associated with a character set, even if you store a value such as

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 6: Working with Dates and Times

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

MySQL has several data types for representing dates and times, and several functions for operating on them. MySQL stores dates and times in specific formats, and it’s important to understand them to avoid surprising results when you manipulate temporal data. This chapter covers the following aspects of working with date and time values in MySQL:

Choosing a temporal data type

MySQL provides several temporal data types to choose from when you create tables. By knowing their properties, you’ll be able to choose them appropriately.

Displaying dates and times

MySQL displays temporal values using specific formats by default, but you can produce other formats by using the appropriate functions.

Changing the client time zone

The server interprets TIMESTAMP values in the client’s current time zone rather than its own. Clients in different time zones should set their zone so that the server can properly interpret TIMESTAMP values for them.

Determining the current date or time

MySQL provides functions that return the date and time, which is useful for applications that need to know these values or need to calculate other temporal values in relation to them.

Using TIMESTAMP values to track row modifications

The TIMESTAMP data type has some special properties that make it convenient for recording row creation and modification times automatically.

Breaking dates or times into component values

You can split date and time values when you need only a piece, such as the month part of a date or the hour part of a time.

Synthesizing dates and times from component values

The complement of splitting apart temporal values is to create them from subparts.

Converting between dates or times and basic units

Some temporal calculations such as date arithmetic operations are more easily performed using the number of days or seconds represented by a date or time value than by using the value itself. MySQL makes it possible to perform several kinds of conversions between date and time values and more basic units such as days or seconds.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 7: Sorting Query Results

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter covers sorting, an operation that is extremely important for controlling how MySQL displays results from SELECT statements. Sorting is performed by adding an ORDER BY clause to a query. Without such a clause, MySQL is free to return rows in any order, so sorting helps bring order to disorder and makes query results easier to examine and understand. (Sorting is also performed implicitly when you use a GROUP BY clause, as discussed in .)

You can sort rows of a query result several ways:

• Using a single column, a combination of columns, or even parts of columns
• Using ascending or descending order
• Using the result of an expression
• Using case-sensitive or case-insensitive string comparisons
• Using temporal ordering

The driver_log table is used for several examples in this chapter; it contains columns for recording daily mileage logs for a set of truck drivers:

mysql>SELECT * FROM driver_log;
+--------+-------+------------+-------+
| rec_id | name  | trav_date  | miles |
+--------+-------+------------+-------+
|      1 | Ben   | 2006-08-30 |   152 |
|      2 | Suzi  | 2006-08-29 |   391 |
|      3 | Henry | 2006-08-29 |   300 |
|      4 | Henry | 2006-08-27 |    96 |
|      5 | Ben   | 2006-08-29 |   131 |
|      6 | Henry | 2006-08-26 |   115 |
|      7 | Suzi  | 2006-09-02 |   502 |
|      8 | Henry | 2006-09-01 |   197 |
|      9 | Ben   | 2006-09-02 |    79 |
|     10 | Henry | 2006-08-30 |   203 |
+--------+-------+------------+-------+

Many other examples use the mail table (used in earlier chapters):

mysql>SELECT * FROM mail;
+---------------------+---------+---------+---------+---------+---------+
| t                   | srcuser | srchost | dstuser | dsthost | size    |
+---------------------+---------+---------+---------+---------+---------+
| 2006-05-11 10:15:08 | barb    | saturn  | tricia  | mars    |   58274 |
| 2006-05-12 12:48:13 | tricia  | mars    | gene    | venus   |  194925 |
| 2006-05-12 15:02:49 | phil    | mars    | phil    | saturn  |    1048 |
| 2006-05-13 13:59:18 | barb    | saturn  | tricia  | venus   |     271 |
| 2006-05-14 09:31:37 | gene    | venus   | barb    | mars    |    2291 |
| 2006-05-14 11:52:17 | phil    | mars    | tricia  | saturn  |    5781 |
| 2006-05-14 14:42:21 | barb    | venus   | barb    | venus   |   98151 |
| 2006-05-14 17:03:01 | tricia  | saturn  | phil    | venus   | 2394482 |
| 2006-05-15 07:17:48 | gene    | mars    | gene    | saturn  |    3824 |
| 2006-05-15 08:50:57 | phil    | venus   | phil    | venus   |     978 |
| 2006-05-15 10:25:52 | gene    | mars    | tricia  | saturn  |  998532 |
| 2006-05-15 17:35:31 | gene    | saturn  | gene    | mars    |    3856 |
| 2006-05-16 09:00:28 | gene    | venus   | barb    | mars    |     613 |
| 2006-05-16 23:04:19 | phil    | venus   | barb    | venus   |   10294 |
| 2006-05-17 12:49:23 | phil    | mars    | tricia  | saturn  |     873 |
| 2006-05-19 22:21:51 | gene    | saturn  | gene    | venus   |   23992 |
+---------------------+---------+---------+---------+---------+---------+

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 8: Generating Summaries

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Database systems are useful for storing and retrieving records, but they can also summarize your data in more concise forms. Summaries are useful when you want the overall picture rather than the details. They’re also typically more readily understood than a long list of records. Summary techniques enable you to answer questions such as "How many?" or "What is the total?" or "What is the range of values?" If you’re running a business, you may want to know how many customers you have in each state, or how much sales volume you’re generating each month. You could determine the per-state count by producing a list of customer records and counting them yourself, but that makes no sense when MySQL can count them for you. Similarly, to determine sales volume by month, a list of raw order information records is not especially useful if you have to add up the order amounts yourself. Let MySQL do it.

The examples just mentioned illustrate two common summary types. The first (the number of customer records per state) is a counting summary. The content of each record is important only for purposes of placing it into the proper group or category for counting. Such summaries are essentially histograms, where you sort items into a set of bins and count the number of items in each bin. The second example (sales volume per month) is an instance of a summary that’s based on the contents of records—sales totals are computed from sales values in individual order records.

Yet another kind of summary produces neither counts nor sums, but simply a list of unique values. This is useful if you don’t care how many instances of each value are present, but only which values are present. If you want to know the states in which you have customers, you want a list of the distinct state names contained in the records, not a list consisting of the state value from every record. Sometimes it’s even useful to apply one summary technique to the result of another summary. For example, to determine how many states your customers live in, generate a list of unique customer states, and then count them.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 9: Obtaining and Using Metadata

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Most of the SQL statements used so far have been written to work with the data stored in the database. That is, after all, what the database is designed to hold. But sometimes you need more than just data values. You need information that characterizes or describes those values—that is, the statement metadata. Metadata information is used most often in relation to processing result sets, but also is available for other aspects of your interaction with MySQL. This chapter describes how to obtain and use the following types of metadata:

Information about statement results

For statements that delete or update rows, you can determine how many rows were changed. For a SELECT statement, you can find out the number of columns in the result set, as well as information about each column in the result set, such as the column name and its display width. Such information often is essential for processing the results. For example, if you’re formatting a tabular display, you can determine how wide to make each column and whether to justify values to the left or right.

Information about tables and databases

Information pertaining to the structure of tables and databases is useful for applications that need to enumerate a list of tables in a database or databases hosted on a server (for example, to present a display allowing the user to select one of the available choices). You can also use this information to determine whether tables or databases exist. Another use for table metadata is to determine the legal values for ENUM or SET columns.

Information about the MySQL server

Some APIs provide information about the database server or about the status of your current connection with the server. Knowing the server version can be useful for determining whether it supports a given feature, which helps you build adaptive applications. Information about the connection includes such values as the current user and the default database.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 10: Importing and Exporting Data

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Suppose that you have a file named somedata.csv that contains 12 columns of data in comma-separated values (CSV) format. From this file you want to extract only columns 2, 11, 5, and 9, and use them to create database rows in a MySQL table that contains name, birth, height, and weight columns. You need to make sure that the height and weight are positive integers, and convert the birth dates from MM/DD/YY format to CCYY-MM-DD format. How can you do this?

In one sense, that problem is very specialized. But in another, it’s not at all atypical, because data transfer problems with specific requirements occur frequently when you transfer data into MySQL. It would be nice if datafiles were always nicely formatted and ready to load into MySQL with no preparation, but frequently that is not so. As a result, it’s often necessary to preprocess information to put it into a format that MySQL finds acceptable. The reverse also is true; data exported from MySQL may need massaging to be useful for other programs.

Although some data transfer operations are so difficult that they require a great deal of hand checking and reformatting, you can do at least part of the job automatically in most cases. Virtually all transfer problems involve at least some elements of a common set of conversion issues. This chapter discusses what these issues are, how to deal with them by taking advantage of the existing tools at your disposal, and how to write your own tools when necessary. The idea is not to cover all possible import and export situations (an impossible task), but to show some representative techniques and utilities. You can use them as is or adapt them for problems that they don’t handle. (There are also commercial conversion tools that may assist you, but my purpose here is to help you do things yourself.)

The first recipes in the chapter cover MySQL’s native facilities for importing data (the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 11: Generating and Using Sequences

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

A sequence is a set of integers (1, 2, 3, ...) that are generated in order on demand. Sequences are frequently used in databases because many applications require each row in a table to contain a unique value, and sequences provide an easy way to generate them. This chapter describes how to use sequences in MySQL. It covers the following topics:

Using columns to create sequences

The AUTO_INCREMENT column is MySQL’s mechanism for generating a sequence over a set of rows. Each time you create a row in a table that contains an AUTO_INCREMENT column, MySQL automatically generates the next value in the sequence as the column’s value. This value serves as a unique identifier, making sequences an easy way to create items such as customer ID numbers, shipping package waybill numbers, invoice or purchase order numbers, bug report IDs, ticket numbers, or product serial numbers.

Retrieving sequence values

For many applications, it’s not enough just to create sequence values. It’s also necessary to determine the sequence value for a just-inserted row. A web application may need to redisplay to a user the contents of a row created from the contents of a form just submitted by the user. The value may also need to be retrieved so it can be stored in rows of a related table.

Resequencing techniques

This topic describes how to renumber a sequence that has holes in it due to row deletions, and also discusses reasons to avoid resequencing. Other topics include starting sequences at values other than 1 and adding a sequence column to a table that doesn’t have one.

Using an column to create multiple sequences

In many cases, the AUTO_INCREMENT column in a table is independent of other columns, and its values increment throughout the table in a single monotonic sequence. However, if you create a multiple-column index that contains an

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 12: Using Multiple Tables

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

For the most part, recipes in earlier chapters have used single tables. But for any application of even moderate complexity, it’s likely that you’ll need to use multiple tables. Some questions simply cannot be answered using a single table, and the real power of a relational database comes into play when you start to combine the information from multiple sources. There are several reasons to use multiple tables:

• To combine rows from tables to obtain more comprehensive information than can be obtained from individual tables alone
• To hold intermediate results for a multiple-stage operation
• To modify rows in one table based on information from another

A statement that uses multiple tables can be a join between tables, a subquery that nests one SELECT within another, or a union that combines the results of multiple SELECT statements. Subqueries have already been touched on in earlier chapters to some extent. In this chapter, the primary focus is on joins and unions, although subqueries occur on occasion as well. The following topics are covered here:

Joining tables to find matches or mismatches between rows in different tables

To solve such problems, you should know which types of joins apply. Inner joins show which rows in one table are matched by rows in another. Outer joins show matching rows, but they can also be used to find which rows in one table are not matched by rows in another.

Comparing a table to itself

Some problems require that you compare a table to itself. This is similar to performing a join between different tables, except that you must use table aliases to disambiguate table references.

Using unions to combine result sets

For some queries, the required information consists of multiple result sets, either selected from different tables or selected in different ways from the same table. To produce such a result, use a UNION that combines the result sets from multiple

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 13: Statistical Techniques

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter covers several topics that relate to basic statistical techniques. For the most part, these recipes build on those described in earlier chapters, such as the summary techniques discussed in . The examples here thus show additional ways to apply the material from those chapters. Broadly speaking, the topics discussed in this chapter include:

• Techniques for data characterization, such as calculating descriptive statistics, generating frequency distributions, counting missing values, and calculating least-squares regressions or correlation coefficients
• Randomization methods, such as how to generate random numbers and apply them to randomizing of a set of rows or to selecting individual items randomly from the rows
• Rank assignments

Statistics covers such a large and diverse array of topics that this chapter necessarily only scratches the surface and simply illustrates a few of the potential areas in which MySQL may be applied to statistical analysis. Note that some statistical measures can be defined in different ways (for example, do you calculate standard deviation based on n degrees of freedom, or n–1?). For that reason, if the definition I use for a given term doesn’t match the one you prefer, you’ll need to adapt the queries or algorithms shown here to some extent.

You can find scripts related to the examples discussed here in the stats directory of the recipes distribution, and scripts for creating some of the example tables in the tables directory.

You want to characterize a dataset by computing general descriptive or summary .

Many common descriptive statistics, such as mean and standard deviation, can be obtained by applying aggregate functions to your data. Others, such as median or mode, can be calculated based on counting queries.

Suppose that you have a table testscore containing observations representing subject ID, age, sex, and test score:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 14: Handling Duplicates

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Tables or result sets sometimes contain duplicate rows. In some cases this is acceptable. For example, if you conduct a web poll that records date and client IP number along with the votes, duplicate rows may be allowable, because it’s possible for large numbers of votes to appear to originate from the same IP number for an Internet service that routes traffic from its customers through a single proxy host. In other cases, duplicates will be unacceptable, and you’ll want to take steps to avoid them. Operations related to handling of duplicate rows include the following:

• Preventing duplicates from being created within a table in the first place. If each row in a table is intended to represent a single entity (such as a person, an item in a catalog, or a specific observation in an experiment), the occurrence of duplicates presents significant difficulties in using it that way. Duplicates make it impossible to refer to every row in the table unambiguously, so it’s best to make sure duplicates never occur.
• Counting the number of duplicates to determine if they are present and to what extent.
• Identifying duplicated values (or the rows containing them) so you can see what they are and where they occur.
• Eliminating duplicates to ensure that each row is unique. This may involve removing rows from a table to leave only unique rows. Or it may involve selecting a result set in such a way that no duplicates appear in the output. For example, to display a list of the states in which you have customers, you probably wouldn’t want a long list of state names from all customer records. A list showing each state name only once suffices and is easier to understand.

Several tools are at your disposal for dealing with duplicate rows. Choose them according to the objective that you’re trying to achieve:

• Creating a table to include a primary key or unique index prevents duplicates from being added to the table. MySQL uses the index as a constraint to enforce the requirement that each row in the table contains a unique key in the indexed column or columns.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 15: Performing Transactions

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The MySQL server can handle multiple clients at the same time because it is multithreaded. To deal with contention among clients, the server performs any necessary locking so that two clients cannot modify the same data at once. However, as the server executes SQL statements, it’s very possible that successive statements received from a given client will be interleaved with statements from other clients. If a client issues multiple statements that are dependent on each other, the fact that other clients may be updating tables in between those statements can cause difficulties. Statement failures can be problematic, too, if a multiple-statement operation does not run to completion. Suppose that you have a flight table containing information about airline flight schedules and you want to update the row for Flight 578 by choosing a pilot from among those available. You might do so using three statements as follows:

SELECT @p_val := pilot_id FROM pilot WHERE available = 'yes' LIMIT 1;
UPDATE pilot SET available = 'no' WHERE pilot_id = @p_val;
UPDATE flight SET pilot_id = @p_val WHERE flight_id = 578;

The first statement chooses one of the available pilots, the second marks the pilot as unavailable, and the third assigns the pilot to the flight. That’s straightforward enough in practice, but in principle there are a couple of significant difficulties with the process:

Concurrency issues

If two clients want to schedule pilots, it’s possible that both of them would run the initial SELECT query and retrieve the same pilot ID number before either of them has a chance to set the pilot’s status to unavailable. If that happens, the same pilot would be scheduled for two flights at once.

Integrity issues

All three statements must execute successfully as a unit. For example, if the SELECT and the first UPDATE run successfully, but the second UPDATE fails, the pilot’s status is set to unavailable without the pilot being assigned a flight. The database will be left in an inconsistent state.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 16: Using Stored Routines, Triggers,

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter discusses the following kinds of database objects:

Stored routines (functions and procedures)

A stored function performs a calculation and returns a value that can be used in expressions just like a built-in function such as RAND⁠(⁠ ⁠ ⁠), NOW⁠(⁠ ⁠ ⁠), or LEFT⁠(⁠ ⁠ ⁠). A stored procedure performs calculations for which no return value is needed. Procedures are not used in expressions, they are invoked with the CALL statement. A procedure might be executed to update rows in a table or produce a result set that is sent to the client program. One reason for using a stored routine is that it encapsulates the code for performing a calculation. This enables you to perform the calculation easily by invoking the routine rather than by repeating all its code each time.

Triggers

A trigger is an object that is defined to activate when a table is modified. Triggers are available for INSERT, UPDATE, and DELETE statements. For example, you can check values before they are inserted into a table, or specify that any row deleted from a table should be logged to another table that serves as a journal of data changes. Triggers are useful for automating these actions so that you don’t need to remember to do them yourself each time you modify a table.

Events

An event is an object that executes SQL statements at a scheduled time or times. You can think of an event as something like a Unix cron job, but that runs within MySQL. For example, events can help you perform administrative tasks such as deleting old table records periodically or creating nightly summaries.

Stored routines and triggers are supported as of MySQL 5.0. Event support begins with MySQL 5.1.

These different kinds of objects have in common the property that they are user-defined but stored on the server side for later execution. This differs from sending an SQL statement from the client to the server for immediate execution. Each of these objects also has the property that it is defined in terms of other SQL statements to be executed when the object is invoked. The object has a body that is a single SQL statement, but that statement can use compound-statement syntax (a

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 17: Introduction to MySQL on the Web

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter and the next few discuss some of the ways that MySQL can help you build a better web site. One significant benefit is that MySQL enables you to create a more interactive site because it becomes easier to provide dynamic content rather than static content. Static content exists as pages in the web server’s document tree that are served exactly as is. Visitors can access only the documents that you place in the tree, and changes occur only when you add, modify, or delete those documents. By contrast, dynamic content is created on demand. Rather than opening a file and serving its contents directly to the client, the web server executes a script that generates the page and sends the resulting output. For example, a script can process a keyword request and return a page that lists items in a catalog that match the keyword. Each time a keyword is submitted, the script produces a result appropriate for the request. And that’s just for starters; web scripts have access to the power of the programming language in which they’re written, so the actions that they perform to generate pages can be quite extensive. For example, web scripts are important for form processing, and a single script may be responsible for generating a form and sending it to the user, processing the contents of the form when the user submits it later, and storing the contents in a database. By operating this way, web scripts interact with users of your web site and tailor the information provided according to what those users want to see.

This chapter covers the introductory aspects of writing scripts that use MySQL in a web environment. Some of the material is not MySQL-related, but is necessary to establish the initial groundwork for using your database from within the context of web programming. The topics covered here include:

• How web scripting differs from writing static HTML documents or scripts intended to be executed from the command line.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 18: Incorporating Query Results

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

When you store information in your database, you can easily retrieve it for use on the Web in a variety of ways. Query results can be displayed as unstructured paragraphs or as structured elements such as lists or tables; you can display static text or create hyperlinks. Query metadata can be useful when formatting query results, too, such as when generating an HTML table that displays a result set and uses its metadata to get the column headings for the table. These tasks combine statement processing with web scripting, and are primarily a matter of properly encoding any special characters in the results (like & or <) and adding the appropriate HTML tags for the types of elements you want to produce.

This chapter shows how to generate several types of web output from query results:

• Paragraphs
• Lists
• Tables
• Hyperlinks
• Navigation indexes (single- and multiple-page)

The chapter also covers techniques for inserting binary data into your database and for retrieving and transferring that kind of information to clients. (It’s easiest and most common to work with text for creating web pages from database content, but you can also use MySQL to help service requests for binary data such as images, sounds, or PDF files.) You can also serve query results for download rather than for display in a page. Finally, the chapter discusses the use of template packages for generating web pages.

The recipes here build on the techniques shown in for generating web pages from scripts and for encoding output for display. See that chapter if you need background in these topics.

Scripts to create the tables used in this chapter are located in the tables directory of the recipes distribution. The scripts for the examples can be found under the directories named for the servers used to run them. For Perl, Ruby, PHP, and Python examples, look under the apache directory. Utility routines used by the example scripts are found in files located in the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 19: Processing Web Input with MySQL

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The previous chapter describes how to retrieve information from MySQL and display it in web pages using various types of HTML constructs such as tables or hyperlinks. That’s a use of MySQL to send information in one direction (from web server to user), but web-based database programming is also useful for collecting information sent in the other direction (from user to web server), such as the contents of a submitted form. For example, if you’re processing a survey form, you might store the information for later use. If the form contains search keywords, you’d use them as the basis for a query that searches the database for information the user wants to see.

MySQL comes into these activities in a fairly obvious way, as the repository for storing information or as the source from which search results are drawn. But before you can process input from a form, you have to create the form and send it to the user. MySQL can help with this, too, because it’s often possible to use information stored in your database to generate form elements such as radio buttons, checkboxes, pop-up menus, or scrolling lists:

• You can select a set of items from a table that lists countries, states, or provinces and convert them into a pop-up menu in a form that collects address information.
• You can use the list of legal values for an ENUM column that contains allowable salutations (Mr., Mrs., and so forth) to generate a set of radio buttons.
• You can use lists of available colors, sizes, or styles stored in an inventory database to construct fields for a clothing ordering form.
• If you have an application that enables the user to pick a database or table, you can query the MySQL server for a list of databases or tables and use the resulting names to create a list element.

By using database content to generate form elements, you lessen the amount of explicit knowledge your programs must have about table structure and content, and you enable them to determine what they need automatically. A script that uses database content to figure out for itself how to generate form elements will also adaptively handle changes to the database. To add a new country, create a new row in the table that stores the list of countries. To add a new salutation, change the definition of the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 20: Using MySQL-Based Web Session Management

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Many web applications interact with users over a series of requests and, as a result, need to remember information from one request to the next. A set of related requests is called a session. Sessions are useful for activities such as performing login operations and associating a logged-in user with subsequent requests, managing a multiple-stage online ordering process, gathering input from a user in stages (possibly tailoring the questions asked to the user’s earlier responses), and remembering user preferences from visit to visit. Unfortunately, HTTP is a stateless protocol, which means that web servers treat each request independently of any other—unless you take steps to ensure .

This chapter shows how to make information persist across multiple requests, which will help you develop applications for which one request retains memory of previous ones. The techniques shown here are general enough that you should be able to adapt them to a variety of state-maintaining web applications.

Some session management methods rely on information stored on the client. One way to implement client-side storage is to use cookies, which are implemented as information that is transmitted back and forth in special request and response headers. When a session begins, the application generates and sends the client a cookie containing the initial information to be stored. The client returns the cookie to the server with each subsequent request to identify itself and to enable the application to associate the requests as belonging to the same client session. At each stage of the session, the application uses the data in the cookie to determine the state (or status) of the client. To modify the session state, the application sends the client a new cookie containing updated information to replace the old cookie. This mechanism allows data to persist across requests while still affording the application the opportunity to update the information as necessary. Cookies are easy to use, but have some disadvantages. For example, it’s possible for the client to modify cookie contents, possibly tricking the application into misbehaving. Other client-side session storage techniques suffer the same drawback.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Appendix : Obtaining MySQL Software

Content preview·Buy reprint rights for this chapter

Most of the table definitions and programs discussed in this book are available online so that you can avoid typing them in yourself. To run the examples, you’ll also need access to MySQL, of course, as well as the appropriate MySQL-specific interfaces for the programming languages that you want to use. This appendix describes what software you need and where to get it.

Obtaining Sample Source Code and Data

Obtaining MySQL and Related Software

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Appendix : Executing Programs from the Command Line

Content preview·Buy reprint rights for this chapter

As you work through this book, you’ll run many examples using the mysql client program, and there are lots of programs in the recipes distribution for you to try as you read. And of course one purpose of the book is to enable you to write your own MySQL-based programs. Consequently, you’ll often need to execute programs at the command line—that is, at the prompt of your shell or command interpreter. For best use of this book, you should be able to run mysql easily (by entering just its name), and you should be able to execute programs from the recipes distribution or that you write yourself. To accomplish those goals, it’s important that your PATH environment variable be set correctly, and that you know how to make programs executable. This appendix shows how to do those things; if you already know how, you can skip it.

Setting Environment Variables

Executing Programs

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Appendix : JSP and Tomcat Primer

Content preview·Buy reprint rights for this chapter

This appendix describes some essential concepts of JavaServer Pages (JSP) programming, which is used earlier in this book beginning with . The necessary background is fairly extensive, which is why the material is presented here in a separate appendix rather than breaking up the flow of that chapter. The topics discussed here are:

• A brief overview of servlet and JSP technologies
• Setting up the Tomcat server
• Tomcat’s directory structure
• The layout of web applications
• Elements of JSP pages

For pointers to additional information about JSP pages, servlets, or Tomcat, see .

Servlet and JavaServer Pages Overview

Setting Up a Tomcat Server

Tomcat’s Directory Structure

Restarting Applications Without Restarting Tomcat

Web Application Structure

Elements of JSP Pages

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Appendix : References

Content preview·Buy reprint rights for this chapter

This appendix lists some references that you should find helpful if you want more information about topics discussed in this book.

MySQL Resources

Perl Resources

Ruby Resources

PHP Resources

Python Resources

Java Resources

Other Resources

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Return to MySQL Cookbook