Managing & Using MySQL | O'Reilly Media

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

Anyone can relate to the gap in usefulness between piles of paper on a desktop and those same papers organized in a filing cabinet. The former is a mess. If you actually want a particular paper, you need to dig through the piles to find it. If you are lucky, you will just happen to start with the pile in which the desired paper is located. It could, however, require you to go through each piece of paper in each pile to find what you are seeking. With a filing cabinet, however, you know exactly where to look and will fumble through just a few papers to find your goal.

Relational databases have been acting as the electronic filing cabinets for the voluminous and complex data storage needs of large companies for over two decades. A relational database is simply the only tool capable of structuring most data so that it is actually usable—not just piles of bits on a hard drive. Until recently, you were simply out of luck if you wanted to build an application backed by a robust database on a small budget. Your choices were quite simple: shell out thousands—or even tens or hundreds of thousands—of dollars on Oracle or Sybase or build the application against "toy" databases such as Access and FileMakerPro. Thanks to such databases as mSQL, PostgreSQL, and MySQL, however, you now have a variety of choices that suit different needs. This book, of course, is the story of MySQL.

In the simplest terms, a database is a collection of data. An example of a nonelectronic database is the public library. The library stores books, periodicals, and other documents. When you need to locate some data at the library, you search through the card catalog or the periodicals index, or maybe you even ask the librarian. Those unsorted piles of papers on your desk also form a database. This database can potentially work, because the size of the database is incredibly small. A stack of papers certainly would not work with a larger set of data, such as the collections in the library. The library would still be a database without the card catalog, periodicals index, and librarian; it would just be an unusable database. A database generally requires some sort of organization to be of value. The paper pile, for example, is of greater value when organized into filing cabinets. So, restating our definition, a database is 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!

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

In the simplest terms, a database is a collection of data. An example of a nonelectronic database is the public library. The library stores books, periodicals, and other documents. When you need to locate some data at the library, you search through the card catalog or the periodicals index, or maybe you even ask the librarian. Those unsorted piles of papers on your desk also form a database. This database can potentially work, because the size of the database is incredibly small. A stack of papers certainly would not work with a larger set of data, such as the collections in the library. The library would still be a database without the card catalog, periodicals index, and librarian; it would just be an unusable database. A database generally requires some sort of organization to be of value. The paper pile, for example, is of greater value when organized into filing cabinets. So, restating our definition, a database is an organized collection of data.

The library and the stack of papers have many similarities. They are both databases of documents. It makes no sense, however, to combine them because your papers are interesting only to you, and the library contains documents of general interest. Both databases have specific purposes and are organized according to those purposes. We will therefore amend our definition a bit further: a database is a collection of data that is organized and stored according to some purpose.

Traditional paper-based databases have many disadvantages. They require a tremendous amount of physical space. Libraries occupy entire buildings, and searching a library is relatively slow. Anyone who has spent time in a library knows that it can consume a nontrivial amount of time to find the information you seek. Libraries are also tedious to maintain, and an inordinate amount of time is spent keeping the catalogs and shelves consistent. Electronic storage of a database helps to address these issues.

MySQL is not a database, per se. It is computer software that enables you to create, maintain, and manage electronic databases. This category of software is known as a database management system (DBMS). A DBMS acts as a broker between the physical database and the users of that 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!

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

This story actually goes back to 1979 when MySQL's inventor, Michael Widenius (a.k.a. Monty) developed an in-house database tool called UNIREG for managing databases. UNIREG is a tty interface builder that uses a low-level connection to an ISAM storage with indexing. Since then, UNIREG has been rewritten in several different languages and extended to handle big databases. It is still available today, but is largely supplanted by MySQL.

The Swedish company TcX began developing web-based applications in 1994 and used UNIREG to support this effort. Unfortunately, UNIREG created too much overhead to be successful in dynamically generating web pages. TcX thus began looking at alternatives.

TcX looked at SQL and mSQL. mSQL was a cheap DBMS that gave away its source code with database licenses—almost open source. At the time, mSQL was still in its 1.x releases and had even fewer features than the currently available version. Most important to Monty, it did not support any indexes. mSQL's performance was therefore poor in comparison to UNIREG.

Monty contacted David Hughes, the author of mSQL, to see if Hughes would be interested in connecting mSQL to UNIREG's B+ ISAM handler to provide indexing to mSQL. Hughes was already well on his way to mSQL 2, however, and had his indexing infrastructure in place. TcX decided to create a database server that was more compatible with its requirements.

TcX was smart enough not to try to reinvent the wheel. It built upon UNIREG and capitalized on the growing number of third-party mSQL utilities by writing an API into its system that was, at least initially, practically identical to the mSQL API. Consequently, an mSQL user who wanted to move to TcX's more feature-rich database server would only have to make trivial changes to any existing code. The code supporting this new database, however, was completely original.

By May 1995, TcX had a database that met its internal needs: MySQL 3.11. A business partner, David Axmark at Detron HB, began pressing TcX to release this server on the Internet and follow a business model pioneered by Aladdin's L. Peter Deutsch. Specifically, this business model enabled TcX developers to work on projects of their own choosing and release the results as free software. Commercial support for the software generated enough income to create a comfortable lifestyle. The result is a very flexible copyright that makes MySQL "more free" than mSQL. Eventually, Monty released MySQL under the GPL so that MySQL is now "free as in speech" and "free as in beer."

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

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

Working from the legacy of mSQL, TcX decided MySQL had to be at least as fast as mSQL with a much greater feature set. At that time, mSQL defined good database performance, so TcX's goal was no small task. MySQL's specific design goals were speed, robustness, and ease of use. To get this sort of performance, TcX decided to make MySQL a multithreaded database engine. A multithreaded application performs many tasks at the same time as if multiple instances of that application were running simultaneously. Fortunately, multithreaded applications do not pay the very expensive cost of starting up new processes.

In being multithreaded, MySQL has many advantages. A separate thread handles each incoming connection with an extra thread that is always running to manage the connections. Multiple clients can perform read operations simultaneously without impacting one another. But write operations, to a degree that depends on the type of table in use, only hold up other clients that need access to the data being updated. While any thread is writing to a table, all other threads requesting access to that table simply wait until the table is free. Your client can perform any allowed operation without concern for other concurrent connections. The connection-managing thread prevents other threads from reading or writing to a table in the middle of an update.

Another advantage of this architecture is inherent to all multithreaded applications: even though the threads share the same process space, they execute individually. Because of this separation, multiprocessor machines can spread the threads across many CPUs as long as the host operating system supports multiple CPUs.

In addition to the performance gains introduced by multithreading, MySQL has a richer subset of SQL than mSQL. MySQL supports over a dozen data types and additionally supports SQL functions. Your application can access these functions through the American National Standards Institute (ANSI) SQL statements.

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

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

We have already mentioned multithreading as a key feature to support MySQL's performance design goals. It is the core feature around which MySQL is built. Other features include:

Openness: MySQL is open in every sense of the term. Its SQL dialect uses ANSI SQL2 as its foundation. The database engine runs on countless platforms, including Windows 2000, Mac OS X, Linux, FreeBSD, and Solaris. If no binary is available for your platform, you have access to the source to compile to that platform.
Application support: MySQL has an API for just about any programming language. Specifically, you can write database applications that access MySQL in C, C++, Eiffel, Java, Perl, PHP, Python, and Tcl. In this book, we cover C, Java, Python, Perl, and PHP.
Cross-database joins: You can construct MySQL queries that can join tables from different databases.
Outer join support: MySQL supports both left and right outer joins using both ANSI and ODBC syntax.
Internationalization: MySQL supports several different character sets, including ISO-8859-1, Big5, and Shift-JIS. It also supports sorting for different character sets and can be customized easily. Error messages can be provided in different languages as well.

Above all else, MySQL is cheap and fast. Other features of MySQL may attract you, but cost and performance are its greatest benefits. The other relational databases fall into two categories:

Low-cost database engines such as mSQL, PostgreSQL, and InstantDB
Commercial vendors such as Oracle, Microsoft, and Sybase

MySQL compares well with other free database engines. It blows them away, however, in terms of performance. In fact, mSQL does not compare with MySQL on any level. InstantDB compares reasonably on a feature level, but MySQL is still much faster. PostgreSQL has some cool SQL3 features, but it carries the bloat of commercial database engines. If you are looking at low-cost database engines and are using advanced SQL3 features, you probably want PostgresSQL; use MySQL if you are doing anything else.

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

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

According to our definition, a database is an organized collection of data that serves some purpose. Simply having MySQL up and running is not sufficient to give your database a purpose. How you use the data you put in your MySQL database defines its purpose. Imagine a library where nobody ever reads the books. There would not be much point in storing and organizing all those books if they're never used. Now, imagine a library where you could not change or add to the collection. The utility of the library as a database would decrease over time since obsolete books could never be replaced and new books could never be added. In short, a library exists so that people may read the books and find the information they seek.

Databases exist so that people can interact with them. In the case of electronic databases, the interaction occurs not directly with the database, but indirectly through software applications. Before the emergence of the World Wide Web, databases typically were used by large corporations to support various business functions: accounting and finance, shipping and inventory control, manufacturing planning, human resources, and so on. The Web and more complex home computing tasks have helped move the need for database applications outside the realm of the large corporation.

Therefore, it is not surprising that the area in which databases have experienced the most explosive growth—an area where MySQL excels—is web application development. As the demand for more complex and robust web applications grows, so does the need for databases. A database backend can support many critical functions on the Web. Virtually any web content can be driven by a database.

Consider the example of a catalog retailer who wants to publish on the Web and accept orders online. If the contents of the catalog are entered directly into one or more HTML files, someone has to hand edit the files each time a new item is added to the catalog or a price is changed. If the catalog information is instead stored in a relational database, it is possible to publish real-time catalog updates simply by changing the product or price data in the database. It is also possible to integrate the online catalog with existing electronic order-processing systems. Using a database to drive such a web site has obvious advantages for both the retailer and the customer.

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

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

MySQL is a relational database management system. It includes not only a server process to manage databases, but also tools for accessing the databases and building applications against those databases. Among these tools are:

mysql: Executes SQL against MySQL, and can be used to execute SQL commands stored in a file
mysqlaccess: Manages users
mysqladmin: Enables you to manage the database server, including the creation and deletion of databases
mysqld: The actual MySQL server process
mysqldump: Dumps the definition and contents of a MySQL database or table to a file
mysqlhotcopy: Performs a hot backup of a MySQL database
mysqlimport: Imports data in different file formats into a MySQL table
mysqlshow: Shows information about the MySQL server and any objects (such as databases and tables) in that server
safe_mysqld or mysqld_safe: Safely starts up and manages the mysqld process on a Unix machine

Over the course of this book, we will go into the details of each of these tools. How you use these tools and this book will depend on how you want to use MySQL.

Are you a database administrator (DBA) responsible for the MySQL runtime environment? The chief concerns of a DBA are the installation, maintenance, security, and performance of MySQL. We tackle these issues in Part II.

Are you a database or application architect responsible for the design of solid database applications? We address the impact of MySQL on these issues in the first few chapters of Part III.

Are you a database application developer responsible for building applications that rely on a database? Database application developers need tools for providing their applications with data from MySQL. Most of Part III covers the various programming APIs that support application interaction with MySQL.

No matter who you are, you need to know the language spoken by MySQL: SQL. Like most database engines, MySQL supports the ANSI SQL2 standard with proprietary extensions. Chapter 3 is a comprehensive tutorial on MySQL's dialect of SQL. The details of the language are covered in Part IV.

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

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

This chapter describes how to download and install MySQL. MySQL is available for a wide variety of target operating systems. In this chapter, we provide an overview of how to install MySQL in binary and source formats for Solaris and Linux as well as binary installation for Windows 9x/NT/2000/XP. Though we specifically address only Solaris, Linux, and Win32, the Solaris/Linux instructions apply to most Unix-based operating systems, including Mac OS X, FreeBSD, and AIX.

Before you begin installing MySQL, you must answer the following questions:

Which version will you install?
This is typically a decision between the latest stable release and the latest development release. In general, we recommended that you go with the latest stable release, unless you need specific features in a development release that are not available in the stable release.
The current stable versions are MySQL 3.23 and MySQL-Max 3.23. MySQL-Max is a beta release of the MySQL software with support for transactions (via BerkeleyDB and InnoDB tables). The standard MySQL binary does not include support for these types of tables.
The current development versions are MySQL 4.0 and MySQL-Max 4.0. The installation instructions provided here will work with either Version 3.23 or 4.0.
Are you going to install MySQL as root or as another user?
MySQL does not require root access to run, but installing it as root will enable you to make one copy available to everyone on your system. If you do not have root access, you must install it in your home directory. However, even if you install MySQL as root, it is a good idea to run it as a different user. In this way, all data in the database can be protected from all other users by setting the permissions on the datafiles to be readable by only the special MySQL user. In addition, if the security of the database becomes compromised, the attacker has access only to the special MySQL user account, which has no privileges beyond the database.
Do you want to install a source or binary distribution?
In general, we recommend that you install a binary distribution if one is available for your platform. In most cases, a binary distribution is easier to install than a source distribution and provides the fastest and most reliable way to get MySQL up and running. The MySQL team and contributors have gone to great lengths to ensure that the binary distributions on their site are built with the best possible options. However, you may encounter cases in which you need to build your MySQL distribution from scratch. For example, here are a few reasons why you would need to install a source distribution:

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

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

Before you begin installing MySQL, you must answer the following questions:

Which version will you install?
This is typically a decision between the latest stable release and the latest development release. In general, we recommended that you go with the latest stable release, unless you need specific features in a development release that are not available in the stable release.
The current stable versions are MySQL 3.23 and MySQL-Max 3.23. MySQL-Max is a beta release of the MySQL software with support for transactions (via BerkeleyDB and InnoDB tables). The standard MySQL binary does not include support for these types of tables.
The current development versions are MySQL 4.0 and MySQL-Max 4.0. The installation instructions provided here will work with either Version 3.23 or 4.0.
Are you going to install MySQL as root or as another user?
MySQL does not require root access to run, but installing it as root will enable you to make one copy available to everyone on your system. If you do not have root access, you must install it in your home directory. However, even if you install MySQL as root, it is a good idea to run it as a different user. In this way, all data in the database can be protected from all other users by setting the permissions on the datafiles to be readable by only the special MySQL user. In addition, if the security of the database becomes compromised, the attacker has access only to the special MySQL user account, which has no privileges beyond the database.
Do you want to install a source or binary distribution?
In general, we recommend that you install a binary distribution if one is available for your platform. In most cases, a binary distribution is easier to install than a source distribution and provides the fastest and most reliable way to get MySQL up and running. The MySQL team and contributors have gone to great lengths to ensure that the binary distributions on their site are built with the best possible options. However, you may encounter cases in which you need to build your MySQL distribution from scratch. For example, here are a few reasons why you would need to install a source distribution:

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

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

MySQL is available for a wide variety of Unix platforms. In this chapter, we go over the steps necessary to install binary and source distributions on Solaris and Linux. These can also be used as a general guide to installation on other operating systems, which should be very similar to our examples.

To install a binary distribution, you need the tar utility and the GNU gunzip utility. These tools are now a part of the standard distribution of many Unix systems.

Solaris tar is known to have problems with some of the long filenames in the MySQL binary distribution. To unpack the binary distribution successfully on a Solaris system, you may need to obtain GNU gtar. A binary distribution version of this is available from http://www.mysql.com/downloads/os-solaris.html.

The binary distributions are all named using the following convention: mysql- version-os .tar.gz. The version placeholder is the version number of the software contained in the distribution. The os placeholder is the operating system for which the binary distribution is built. Binary distributions named mysql-max- version-os .tar.gz contain a version of MySQL compiled with support for transaction-safe tables.

Assume, for example, that we have chosen to install MySQL 3.23.40 on a Sun Solaris server. Also assume the distribution file mysql-3.23.40-sun-solaris2.7-sparc.tar.gz has been downloaded into the /tmp directory.

We recommend that you create a user and group for MySQL administration. This user should be used to run the mysql server and perform administrative tasks. It is possible to run the server as root, but this is not recommended.

The first step is to create a user to run the MySQL server. On Solaris and Linux, this can be done with the useradd and groupadd utilities. In our example, we create a user called mysql . In practice, you can choose any username you like. Typically, you will need to be the root user to successfully perform these tasks, so before anything else, use 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!

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

The distributions for Windows can be found in the same place as the distributions for Unix: at http://www.mysql.com/downloads or at one of the mirror sites. Windows installation is simply a matter of downloading the mysql -version. zip, unzipping it, and running the set-up program.

The default install location for MySQL Windows is c:\mysql. The installer will allow you to change the location. However, if you choose to do so, you may need to modify some configuration files to get everything working correctly. Refer to the full MySQL installation documentation at http://www.mysql.com/documentation for more information.

The installer will give you the choice between a typical, compact, or custom install. You should use the typical install unless you wish to modify the list of components that are installed. In that case, use the custom install.

The Windows binary distribution contains several servers from which to choose. Table 2-2 lists these servers.

Table 2-2: Servers that come with the Windows distribution
Server name	Description
`mysqld`	Debug binary with memory allocation checking, symbolic link support, and transactional table support (InnoDB and DBD)
`mysqld-opt`	Optimized binary with no support for transactional tables
`mysqld-nt`	Optimized binary with support for NT named pipes

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

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

The Structured Query Language (SQL) is used to read and write to MySQL databases. Using SQL, you can search for, enter, modify, or delete data. SQL is the most fundamental tool you will need for your interactions with MySQL. Even if you are using some application or graphical user interface to access the database, somewhere under the hood that application is generating SQL.

SQL is a sort of "natural" language. In other words, an SQL statement should read—at least on the surface—like a sentence of English text. This approach has both benefits and drawbacks, but the end result is a language unlike traditional programming languages such as C, Java, or Perl.

SQL is "structured" in the sense that it follows a very specific set of rules. A computer program can parse a formulated SQL query easily. In fact, the O'Reilly book lex & yacc by John Levine, Tony Mason, and Doug Brown implements an SQL grammar to demonstrate the process of writing a program to interpret language! A query is a fully specified command sent to the database server, which then performs the requested action. Here's an example of an SQL query:

SELECT name FROM people WHERE name LIKE 'Stac%'

As you can see, this statement reads almost like a form of broken English: "Select names from a list of people where the names are like Stac." SQL uses few of the formatting and special characters that are typically associated with computer languages.

IBM invented SQL in the 1970s shortly after Dr. E. F. Codd invented the concept of a relational database. From the beginning, SQL was an easy-to-learn, yet powerful language. It resembles a natural language such as English, so it is less daunting to a nontechnical person. In the 1970s, even more than today, this advantage was important.

There were no casual hackers in the early 1970s. No one grew up learning BASIC or building web pages in HTML. The people programming computers were people who knew everything about how a computer worked. SQL was aimed at the army of nontechnical accountants and business and administrative staff who would benefit from being able to access the power of a relational 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!

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

SQL is "structured" in the sense that it follows a very specific set of rules. A computer program can parse a formulated SQL query easily. In fact, the O'Reilly book lex & yacc by John Levine, Tony Mason, and Doug Brown implements an SQL grammar to demonstrate the process of writing a program to interpret language! A query is a fully specified command sent to the database server, which then performs the requested action. Here's an example of an SQL query:

SELECT name FROM people WHERE name LIKE 'Stac%'

As you can see, this statement reads almost like a form of broken English: "Select names from a list of people where the names are like Stac." SQL uses few of the formatting and special characters that are typically associated with computer languages.

IBM invented SQL in the 1970s shortly after Dr. E. F. Codd invented the concept of a relational database. From the beginning, SQL was an easy-to-learn, yet powerful language. It resembles a natural language such as English, so it is less daunting to a nontechnical person. In the 1970s, even more than today, this advantage was important.

There were no casual hackers in the early 1970s. No one grew up learning BASIC or building web pages in HTML. The people programming computers were people who knew everything about how a computer worked. SQL was aimed at the army of nontechnical accountants and business and administrative staff who would benefit from being able to access the power of a relational database.

SQL was so popular with its target audience, in fact, that in the 1980s, Oracle Corporation launched the world's first publicly available commercial SQL system. Oracle SQL was a huge hit and spawned an entire industry built around SQL. Sybase, Informix, Microsoft, and several other companies have since come forward with their implementations of SQL-based relational database management systems (RDBMSs).

When Oracle and its first competitors hit the scene, SQL was still relatively new and there was no standard. It was not until 1989 that the ANSI standards body issued the first public SQL standard. These days, the standard is referred to as SQL89. That new standard, unfortunately, did not go far enough into defining the technical structure of the language. Thus, even though the various commercial SQL languages were drawing closer together, differences in syntax still made it nontrivial to switch among implementations. It was not until 1992 that the ANSI SQL standard came into its own.

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

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

To get started using MySQL, you need to create a database. First, let's take a look at the databases that come with a clean MySQL installation using the

SHOW
DATABASES

command. Upon installation of MySQL 3.23.40, the following tables already exist:

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.37 sec)

The first database, mysql, is MySQL's system database, which you will learn more about in Chapter 5. The second database, test, is a play database you can use to learn MySQL and run tests against. You may find other databases on your server if you are not dealing with a clean installation. For now, however, we want to create a new database to illustrate the use of the MySQL CREATE statement:

CREATE DATABASE TEMPDB;

and then to work with the new database TEMPDB:

USE TEMPDB;

Finally, you can delete that database by issuing the

DROP
DATABASE

command:

DROP DATABASE TEMPDB;

You can create new objects using the CREATE statement and destroy things using the DROP statement, just as we used them here.

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

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

You should now feel comfortable connecting to a database on a MySQL server. For the rest of the chapter, you can use either the test database that comes with MySQL or your own play database. Using the SHOW command, you can display a list of tables in the current database the same way you used it to show databases. In a brand new installation, the test database has no tables. The following shows the output of the

SHOW
TABLES

command when connected to the mysql system database:

mysql> USE mysql;
Database changed
mysql> SHOW TABLES;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv    |
| db              |
| func            |
| host            |
| tables_priv     |
| user            |
+-----------------+
6 rows in set (0.00 sec)

These are the six system tables MySQL requires to do its work. To see what one of these tables looks like, you can use the DESCRIBE command:

mysql> DESCRIBE db;
+-----------------+-----------------+------+-----+---------+-------+
| Field           | Type            | Null | Key | Default | Extra |
+-----------------+-----------------+------+-----+---------+-------+
| Host            | char(60) binary |      | PRI |         |       |
| Db              | char(64) binary |      | PRI |         |       |
| User            | char(16) binary |      | PRI |         |       |
| Select_priv     | enum('N','Y')   |      |     | N       |       |
| Insert_priv     | enum('N','Y')   |      |     | N       |       |
| Update_priv     | enum('N','Y')   |      |     | N       |       |
| Delete_priv     | enum('N','Y')   |      |     | N       |       |
| Create_priv     | enum('N','Y')   |      |     | N       |       |
| Drop_priv       | enum('N','Y')   |      |     | N       |       |
| Grant_priv      | enum('N','Y')   |      |     | N       |       |
| References_priv | enum('N','Y')   |      |     | N       |       |
| Index_priv      | enum('N','Y')   |      |     | N       |       |
| Alter_priv      | enum('N','Y')   |      |     | N       |       |
+-----------------+-----------------+------+-----+---------+-------+
13 rows in set (0.36 sec)

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

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

In a table, each column has a type. As we mentioned earlier, an SQL data type is similar to a data type in traditional programming languages. While many languages define a bare-minimum set of types necessary for completeness, SQL goes out of its way to provide types such as DATE that will be useful to everyday users. You could store a DATE type in a more basic numeric type, but having a type specifically dedicated to the nuances of date processing adds to SQL's ease of use—one of SQL's primary goals.

Chapter 16 provides a full reference of SQL types supported by MySQL. Table 3-1 is an abbreviated listing of the most common types.

Table 3-1: Common MySQL data types (see Chapter 16 for a full list)
Data type	Description
`INT`	An integer value. MySQL allows an `INT` to be either signed or unsigned.
`REAL`	A floating-point value. This type offers a greater range and more precision than the `INT` type, but it does not have the exactness of an `INT`.
`CHAR` (length)	A fixed-length character value. No `CHAR` fields can hold strings greater in length than the specified value. Fields of lesser length are padded with spaces. This type is the most commonly used in any SQL implementation.
`VARCHAR` (length)

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

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

While MySQL has better performance than any of the larger database servers, some problems still call for careful database design. For instance, if we had a table with millions of rows of data, a search for a specific row would take a long time. Most database engines allow indexes to aid in such searches.

Indexes help the database store data in a way that makes for quicker searches. Unfortunately, you sacrifice disk space and modification speed for the benefit of quicker searches. The most efficient use of indexes is to create an index for columns on which you tend to search the most. MySQL supports the following syntax for creating an index for a table:

CREATE INDEX index_name ON tablename (column1,
                                      column2,
                                      ...,
                                      columnN)

MySQL also lets you create an index at the same time you create a table using the following syntax:

CREATE TABLE material (id         INT      NOT NULL,
                       name       CHAR(50) NOT NULL,
                       resistance INT,
                       melting_pt REAL,
                       INDEX index1 (id, name),
                       UNIQUE INDEX index2 (name))

The previous example creates two indexes for the table. The first index—named index1—consists of both the id and name fields. The second index includes only the name field and specifies that values for the name field must always be unique. If you try to insert a field with a name held by a row already in the database, the insert will fail. Generally, you should declare all fields in a unique index as NOT NULL .

Even though we created an index for name by itself, we did not create an index for just id. If we did want such an index, we would not need to create it—it is already there. When an index contains more than one column (for example: name, rank, and serial_number), MySQL reads the columns in order from left to right. Because of the structure of the index MySQL uses, any subset of the columns from left to right are automatically created as indexes within the "main" index. For example,

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

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

The first thing you will probably want to do with a newly created table is add data to it. Once the data is in place, you need to maintain it—add to it, modify it, and perhaps even delete it.

Adding a row to a table is one of the more straightforward concepts in SQL. You have already seen several examples of it in this book. MySQL supports the standard SQL INSERT syntax:

INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN)

Under this syntax, you specify the columns followed by the values to populate those columns for the new row. When inserting data into numeric fields, you can insert the value as is; for all other fields, you must wrap them in single quotes. For example, to insert a row of data into a table of addresses, you might issue the following command:

INSERT INTO addresses (name, address, city, state, phone, age)
VALUES('Irving Forbush', '123 Mockingbird Lane', 'Corbin', 'KY',
       '(800) 555-1234', 26)

In addition, the escape character—\, by default—enables you to escape single quotes and other literal instances of the escape character:

# Insert info for the directory Stacie's Directory which
# is in c:\Personal\Stacie
INSERT INTO files (description, location) 
VALUES ('Stacie\'s Directory', 'C:\\Personal\\Stacie')

MySQL allows you to leave out the column names as long as you specify a value for every column in the table in the order they were specified in the table's CREATE call. If you want to use the default values for a column, however, you must specify the names of the columns for which you intend to insert nondefault data. For example, if the earlier files table had contained a column called size, the default value would be used for Stacie's Directory. MySQL allows you to specify a custom default value in the table's CREATE call. If you do not have a default value set up for a column, and that column is NOT NULL, you must include that column in the INSERT statement with a non-NULL value.

Newer versions of MySQL support a nonstandard INSERT call for inserting multiple rows at once:

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

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

The last common SQL command, SELECT , enables you to view the data in the database. This action is by far the most common action performed in SQL. While data entry and modifications do happen on occasion, most databases spend the vast majority of their lives serving up data for reading. The general form of the SELECT statement is as follows:

SELECT column1, column2, ..., columnN 
FROM table1, table2, ..., tableN
[WHERE clause]

This syntax is certainly the most common way to retrieve data from any SQL database. The SELECT statement enables you to identify the columns you want from one or more tables. The WHERE clause identifies the rows with the data you seek.

Of course, there are variations for performing complex and powerful queries. (We cover the full range of the SELECT syntax in Chapter 15.) The simplest form is:

SELECT 1;

This simple, though completely useless query returns a result set with a single row containing a single column with the value of 1. A more useful version of this query might be something like:

mysql> SELECT DATABASE(  );
+------------+
| DATABASE(  ) |
+------------+
| test       |
+------------+
1 row in set (0.01 sec)

The expression

DATABASE(
)

is a MySQL function that returns the name of the current database. (We will cover functions in more detail later in the chapter.) Nevertheless, you can see how simple SQL can provide a quick-and-dirty way of finding out important information.

Most of the time, however, you should use slightly more complex queries that help you pull data from a table in the database. The first part of a SELECT statement enumerates the columns you wish to retrieve. You may specify a * to say that you want to select all columns. The FROM clause specifies which tables those columns come from. The WHERE clause identifies the specific rows to be used and enables you to specify how to join two tables.

Joins put the "relational" in relational databases by enabling you to relate the data in one table with data in other tables. The basic form of a join is sometimes described as 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!

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

So far, we have used the = operator for the obvious task of verifying that two values in a WHERE clause equal one another. Other fairly basic operations include <>, >, <, <=, and >=. Note that MySQL allows you to use either <> or != for "not equal." Table 3-6 contains a full set of simple SQL operators.

Table 3-6: The simple SQL operators supported by MySQL
Operator	Context	Description
`+`	Arithmetic	Addition
`-`	Arithmetic	Subtraction
`*`	Arithmetic	Multiplication
`/`	Arithmetic	Division
`=`

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

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

Using the SQL presented thus far in this chapter should handle 90% of your database programming needs. On occasion, however, you will need some extra power not available in the basic SQL functionality. We close out the chapter with a discussion of a few of these features.

MySQL introduced the ability to search on text elements within a text field in Version 3.23.23 through a special index called a FULLTEXT index. It specifically enables you to do something like:

INSERT INTO Document (url, page_text )
VALUES ('index.html', 'The page contents.');
SELECT url FROM Document WHERE MATCH ( page_text ) AGAINST ('page');

INSERT adds a row to a Document table containing the URL of a web page and its text content. SELECT then looks for the URLs of all documents with the word page embedded in their text.

Section 3.9.1.1: The Basics

The magic behind full text searching lies in a FULLTEXT index. The CREATE statement for the Document table might look like this:

CREATE TABLE Document (
    url       VARCHAR(255) NOT NULL PRIMARY KEY,
    page_text TEXT         NOT NULL,
    FULLTEXT ( page_text )
);

The FULLTEXT index enables you to search the index using words or phrases that will not match exactly and then weigh the relevance of any matches. As with other indexes, you can create multicolumn FULLTEXT indexes:

CREATE TABLE Document (
    url       VARCHAR(255) NOT NULL PRIMARY KEY,
    title     VARCHAR(100) NOT NULL,
    page_text TEXT         NOT NULL,
    FULLTEXT ( title, page_text )
);

With this table structure, you can now search for documents that have the word MySQL anywhere in the title or body of the page. You must keep your searches structured against the index, not against the columns. In other words, you can match against title and page_text together with this table, but you cannot look for words that exist only in the title unless you create a separate FULLTEXT index on it alone. Your combined search will look like the following:

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

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

For the most part, MySQL is low-maintenance software. Once you have installed MySQL, it does not place heavy administrative demands on you. It is not, however, maintenance free. Typical MySQL administration tasks include:

Installation
Configuration and tuning
Access control
Logging
Backup and recovery
Table maintenance

We cover some of these topics in detail in other chapters—installation in Chapter 2, performance tuning in Chapter 5, and access control in Chapter 6.

You may find this chapter helpful even if you are not the one responsible for the administration of your MySQL server. Knowing about database administration can help you diagnose problems before you have to approach your database administrator.

To perform many administrative tasks, you must have administrative access to MySQL. (We describe access privileges and how to set yourself up as the MySQL database administrator in detail in Chapter 6.) You will additionally require administrative access to your operating system—root on Unix systems, Administrator on Windows NT/2000/XP—to perform a number of tasks.

MySQL requires the configuration of the MySQL server process, mysqld , and its several client processes such as the mysql command-line utility. MySQL exposes its Unix roots in how you configure it. Specifically, you configure it using a combination of command-line options, configuration files, and environment variables. Just about any configurable item can be managed using these three mechanisms.

Because you can define options in multiple ways, MySQL has a built-in order of preference that defines how it resolves conflicts:

Command-line options
Configuration options
Environment variable options

In other words, if you have three different values specified for the password option, MySQL client tools will use the one you specified on the command line.

The simplest, most common way to handle your MySQL options is with a configuration file. A configuration file enables you to stick all your options in a file so you do not have to specify them each time you run a command or log into a machine.

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

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

MySQL requires the configuration of the MySQL server process, mysqld , and its several client processes such as the mysql command-line utility. MySQL exposes its Unix roots in how you configure it. Specifically, you configure it using a combination of command-line options, configuration files, and environment variables. Just about any configurable item can be managed using these three mechanisms.

Because you can define options in multiple ways, MySQL has a built-in order of preference that defines how it resolves conflicts:

Command-line options
Configuration options
Environment variable options

In other words, if you have three different values specified for the password option, MySQL client tools will use the one you specified on the command line.

The simplest, most common way to handle your MySQL options is with a configuration file. A configuration file enables you to stick all your options in a file so you do not have to specify them each time you run a command or log into a machine.

On Unix systems, MySQL looks in the following locations—in order—for configuration files:

In the /etc/my.cnf file. The first place MySQL looks is the global options file. In general, you will want to place the default options to be used by all users and all servers in this file.
In the DATADIR/my.cnf file. DATADIR is the directory where a MySQL server instance keeps its datafiles. This configuration file exists specifically for configuration parameters specific to a given server instance.
In the location specified through the --defaults-extra-file=filename command-line option. This command-line option enables the MySQL server or client utilities to look in an arbitrary location for a configuration file.
In the $HOME/.my.cnf file. $HOME is the Unix environment variable that holds the home directory of the current user. This configuration file in a user's home directory

Table of Contents

Section 3.9.1.1: The Basics