Excerpts

Introduction A relational database management system (RDBMS) is an essential tool in many environments, from the more traditional uses in business, research, and educational contexts, to more recent applications such as powering search engines on the Internet. However, despite the importance of a good database system for managing and accessing information resources, many organizations have found them to be out of reach of their financial resources. Historically, database systems have been an expensive proposition, with vendors charging healthy fees both for software and for support. Also, because database engines often had substantial hardware requirements to run with any reasonable performance, the cost was even greater. In recent years, the situation has changed, on both the hardware and software sides of the picture. Personal computers have become inexpensive but powerful, and there is a thriving movement devoted to writing high-performance operating systems for them. They are available for the cost of an inexpensive CD, or free over the Internet. These include several BSD Unix derivatives (FreeBSD, NetBSD, OpenBSD) as well as various distributions of Linux (Fedora, Gentoo, SuSE, to name a few). Production of free operating systems to drive personal computers to their full capabilities has proceeded in concert with--and to a large extent has been made possible by--the development of freely available tools like gcc , the GNU C compiler. These efforts to make software available to anyone who wants it have resulted in what is now called the Open Source movement, and which has produced many important pieces of software. For example, Apache is the most widely used Web server on the Internet. Other Open Source successes are the Perl, Python, and Ruby general-purpose scripting languages and PHP, a language that is popular due largely to the ease with which it allows dynamic Web pages to be written. These all stand in contrast to proprietary solutions that lock you into high-priced products from vendors that don't even provide source code. Database software has become more accessible, too. Open Source database systems such as PostgreSQL are available for free, and commercial vendors such as Informix and Oracle offer their software at no cost for operating systems such as Linux. (However, the commercial-vendor products generally come in binary-only form with no support, which lessens their usefulness.) Another entry into the no-to-low cost database arena is MySQL, a SQL client/server relational database management system originating from Scandinavia. MySQL includes an SQL server, client programs for accessing the server, administrative tools, and a programming interface for writing your own programs. MySQL's roots begin in 1979, with the UNIREG database tool created by Michael "Monty" Widenius for the Swedish company TcX. In 1994, TcX began searching for an RDBMS with an SQL interface for use in developing Web applications. They tested some commercial servers, but found them all too slow for TcX's large tables. They also took a look at mSQL, but it lacked certain features that TcX required. Consequently, Monty began developing a new server. The programming interface was explicitly designed to be similar to the one used by mSQL because several free tools were available for mSQL, and by using a similar interface, those same tools could be used for MySQL with a minimum of porting effort. In 1995, David Axmark of Detron HB began to push for TcX to release MySQL on the Internet. David also worked on the documentation and on getting MySQL to build with the GNU configure utility. MySQL 3.11.1 was unleashed on the world in 1996 in the form of binary distributions for Linux and Solaris. Today, MySQL works on many more platforms and is available in both binary and source form. The company MySQL AB has been formed to provide distributions of MySQL under both Open Source and commercial licenses, and to offer support and training services. Initially, MySQL became widely popular because of its speed and simplicity. But there was criticism, too, because it lacked features such as transactions and foreign key support. Nevertheless, MySQL continued to develop, adding not only those features but others such as row-level locking, replication, subqueries, stored procedures, views, and triggers. There is still work to do, but the addition of these capabilities has caused people who once would have considered only "big engine" database systems for their applications to give MySQL a second look. MySQL is an Open Source project that can be used for free under many circumstances, which is one reason that it enjoys widespread popularity in the Open Source community. But MySQL's popularity isn't limited to Open Source enthusiasts. Yes, it runs on personal computers (indeed, much MySQL development takes place on inexpensive Linux systems). But MySQL is portable and runs on commercial operating systems (such as Solaris, Mac OS X, and Windows) and on hardware all the way up to enterprise servers. Furthermore, its performance rivals any database system you care to put up against it, and it can handle large databases with billions of records. In the business world, MySQL's presence continues to increase as companies discover it to be capable of handling their database needs at a fraction of what they are using to pay for commercial licensing and support. MySQL lies squarely within the picture that unfolds before us: freely available operating systems running on powerful but inexpensive hardware, putting substantial processing power and capabilities in the hands of more individuals and businesses than ever before, on a wider variety of systems than ever before. This lowering of the economic barriers to computing puts powerful database solutions within reach of more people and organizations than at any time in the past. Organizations that once could only dream of putting the power of a high-performance RDBMS to work for them now can do so for very little cost. This is true for individuals as well. For example, I use MySQL with Perl, PHP, and Apache on my Apple iBook running Mac OS X. This allows me to carry my work with me anywhere. Total cost: the cost of the iBook. Why Choose MySQL? If you're looking for a free or low-cost database management system, several are available from which to choose: MySQL, PostgreSQL, SQLite, one of the free-but-unsupported engines from commercial vendors, and so forth. When you compare MySQL with other database systems, think about what's most important to you. Performance, support, features (such as SQL conformance or extensions), licensing conditions and restrictions, and price all are factors to take into account. Given these considerations, MySQL has many attractive features to offer: Speed. MySQL is fast. Its developers contend that MySQL is about the fastest database system you can get. You can investigate this claim by visiting http://dev.mysql.com/tech-resources/benchmarks/ , a performance-comparison page on the MySQL AB Web site. Ease of use. MySQL is a high-performance but relatively simple database system and is much less complex to set up and administer than larger systems. Query language support. MySQL understands SQL (Structured Query Language), the standard language of choice for all modern database systems. Capability. The MySQL server is multi-threaded, so many clients can connect to it at the same time. Each client can use multiple databases simultaneously. You can access MySQL interactively using several interfaces that let you enter queries and view the results: command-line clients, Web browsers, or GUI clients. In addition, programming interfaces are available for many languages, such as C, Perl, Java, PHP, Python, and Ruby. You can also access MySQL using applications that support ODBC (Open Database Connectivity), a database communications protocol developed by Microsoft. This gives you the choice of using prepackaged client software or writing your own for custom applications. Connectivity and security. MySQL is fully networked, and databases can be accessed from anywhere on the Internet, so you can share your data with anyone, anywhere. But MySQL has access control so that one person who shouldn't see another's data cannot. To provide additional security, MySQL supports encrypted connections using the Secure Sockets Layer (SSL) protocol. Portability. MySQL runs on many varieties of Unix, as well as on other non-Unix systems, such as Windows, NetWare, and OS/2. MySQL runs on hardware from small personal computers (even palmtop devices) to high-end servers. Small size. MySQL has a modest distribution size, especially compared to the huge disk space footprint of certain commercial database systems. Availability and cost. MySQL is an Open Source project with dual licensing. First, it is available under the terms of the GNU General Public License (GPL). This means that MySQL is available without cost for most in-house uses. Second, for organizations that prefer or require formal arrangements or that do not want to be bound by the conditions of the GPL, commercial licenses are available. Open distribution and source code. MySQL is easy to obtain; just use your Web browser. If you don't understand how something works, are curious about an algorithm, or want to perform a security audit, you can get the source code and examine it. If you think you've found a bug, report it; the developers want to know. What about support? Good question; a database system isn't much use if you can't get help for it. This book is one form of assistance, and I like to think that it's useful in that regard. (The fact that the book has reached its third edition suggests that it accomplishes that goal.) There are other resources open to you as well, and you'll find that MySQL has good support: The MySQL Reference Manual is included in MySQL distributions, and also is available online and in printed form. The Reference Manual regularly receives good marks in the MySQL user community. This is important, because the value of a good product is diminished if no one can figure out how to use it. Training classes and technical support contracts are available from MySQL AB. MySQL AB sponsors several active mailing lists to which anyone may subscribe. These lists have many helpful participants, including several MySQL developers. As a support resource, many people find these lists invaluable. The MySQL community, developers and non-developers alike, is very responsive. Answers to questions on the mailing lists often arrive within minutes. When bugs are reported, the developers generally fix them quickly, and fixes become available daily over the Internet. Contrast this with the often-frustrating experience of navigating the Byzantine support channels of big vendors. You've been there? Me, too. I know which alternative I prefer when I have a question about a product. Being put on hold at a vendor's convenience has no appeal compared to being able to post a question to a mailing list and check for replies at my convenience. MySQL is an ideal candidate for evaluation if you are in the database-selection process. You can try MySQL with no risk or financial commitment. If you get stuck, you can use the mailing lists to get help. An evaluation costs some of your time, but that's true no matter what database system you're considering--and it's a safe bet that your installation and setup time for MySQL will be less than for many other systems. Already Running Another RDBMS? If you're currently running another database system, should you convert to MySQL? Not necessarily. If you're happy with your current system, why switch? But if you feel constrained by what you're using, you definitely should consider MySQL. Perhaps performance of your current system is a concern, or it's proprietary and you don't like being locked into it. Perhaps you'd like to run on hardware that's not supported by your current system, or your software is provided in binary-only format and you'd really prefer to have the source available. Or maybe it just costs too much! All of these are reasons to look into MySQL. Use this book to familiarize yourself with MySQL's capabilities, contact the sales crew at MySQL AB, ask questions on the MySQL mailing lists, and you'll find the answers you need to make a decision. One thing to keep in mind is that although all major database engines support SQL, each supports a somewhat different dialect. Check out the comparison page on the MySQL AB Web site at http://dev.mysql.com/tech-resources/crash-me.php to see which engines support which features. Then check the chapters in this book that deal with MySQL's SQL dialect and data types. You may decide that the version of SQL supported by your current RDBMS is too different and that porting your applications would involve significant effort. Part of your evaluation should be to try porting a few examples, of course. This will give you valuable experience in making an assessment. One of MySQL AB's commitments is to an ongoing increased conformance to standard SQL. That has the practical consequence of eliminating porting roadblocks as time goes on, so the porting effort may turn out to be easier than you expect. Tools Provided with MySQL MySQL distributions include the following tools: A SQL server. This is the engine that powers MySQL and provides access to your databases. Client and utility programs. These include an interactive client program that allows you to enter queries directly and view the results. Also available are several administrative and utility programs that help you run your site: One allows you to monitor and control the server; others let you import data, perform backups, check tables for problems, and more. A client library for writing your own programs. You can write client programs in C because the library is in C, but the library also can be linked into other language processors such as Perl, PHP, or Ruby to provide the basis for MySQL interfaces in those languages. In addition to the software provided with MySQL itself, MySQL is used by many talented and capable people who like writing software to enhance their productivity and who are willing to share that software. The result is that you have access to a variety of third-party tools that make MySQL easier to use or that extend its reach into areas such as Web site development. What You Can Expect from This Book By reading this book, you'll learn how to use MySQL effectively so that you can get your work done more productively. You'll be able to figure out how to get your information into a database, and you'll learn how to get it back out by formulating queries that give you the answers to the questions you want to ask of that data. You don't need to be a programmer to understand or use SQL. This book will show you how it works. But there's more to understanding how to use a database system properly than just knowing SQL syntax. This book emphasizes MySQL's unique capabilities and shows how to use them. You'll also see how MySQL integrates with other tools. The book shows how to use MySQL with Perl or PHP to generate dynamic Web pages created from the result of database queries. You'll learn how to write your own programs that access MySQL databases. All of these enhance MySQL's capabilities to handle the requirements of your particular applications. If you'll be responsible for administering a MySQL installation, this book will tell you what your duties are and how to carry them out. You'll learn how to create user accounts, perform database backups, set up replication, and make sure your site is secure. Road Map to This Book This book is organized into four parts. The first concentrates on general concepts of database use. The second focuses on writing your own programs that use MySQL. The third is aimed at those readers who have administrative duties. The fourth consists of a set of appendixes that provide reference material. Part I: General MySQL Use Getting Started with MySQL and SQL. Discusses how MySQL can be useful to you, and provides a tutorial that introduces the interactive MySQL client program, covers the basics of SQL, and demonstrates MySQL's general capabilities. MySQL SQL Syntax and Use. Every major RDBMS now available understands SQL, but every database engine implements a slightly different SQL dialect. This chapter discusses SQL with particular emphasis on those features that make MySQL distinctive. Working with Data in MySQL. Discusses the data types that MySQL provides for storing your information, the properties and limitations of each type, when and how to use them, how to choose between similar types, expression evaluation, and type conversion. Query Optimization. How to make your queries run more efficiently. Part II: Using MySQL Programming Interfaces Introduction to MySQL Programming. Discusses some of the application programming interfaces (APIs) available for MySQL and provides a general comparison of the APIs that the book covers in detail. Writing MySQL Programs Using C. How to write C programs using the API provided by the MySQL C client library. Writing MySQL Programs Using Perl DBI. How to write Perl scripts using the DBI module. Covers standalone command-line scripts and scripts for Web site programming. Writing MySQL Programs Using PHP. How to use the PHP scripting language and the PEAR DB module to write dynamic Web pages that access MySQL databases. Part III: MySQL Administration Introduction to MySQL Administration. An overview of what a database administrator's duties are and what you should know to run a MySQL site successfully. The MySQL Data Directory. An in-depth look at the organization and contents of the data directory, the area under which MySQL stores databases, log files, and status files. General MySQL Administration. How to make sure your operating system starts and stops the MySQL server properly when your system comes up and shuts down. Also includes instructions for setting up MySQL user accounts, and discusses log file maintenance, configuring storage engines, server tuning, running multiple servers, and setting up replication servers. MySQL and Security. What you need to know to make your MySQL installation safe from intrusion, both from other users on the server host and from clients connecting over the network. Describes how to set up your MySQL server to support secure connections over SSL. Database Backups, Maintenance, and Repair. Discusses how to reduce the likelihood of disaster through preventive maintenance, how to back up your databases, and how to perform crash recovery if disaster strikes in spite of your preventive measures. Part IV: Appendixes Obtaining and Installing Software. Where to get and how to install the major tools and sample database files described in the book. Data Type Reference. Descriptions of MySQL's data types. Operator and Function Reference. Descriptions of the operators and functions that are used to write expressions in SQL statements. System, Status, and User Variable Reference. Descriptions of each variable maintained by the MySQL server, and how to use your own variables in SQL statements. SQL Syntax Reference. Descriptions of each SQL statement supported by MySQL. MySQL Program Reference. Descriptions of the programs provided in the MySQL distribution. C API Reference. Descriptions of data types and functions in the MySQL C client library. Perl DBI API Reference. Descriptions of methods and attributes provided by the Perl DBI module. PHP and PEAR DB API Reference. Descriptions of the PEAR DB module methods provided for MySQL support in PHP. How to Read This Book Whichever part of the book you happen to be reading at any given time, it's best to try the examples as you go along. That means you should do two things: If MySQL isn't installed on your system, you should install it or ask someone to do so for you. You should get the files needed to set up the sampdb sample database to which we'll be referring throughout the book. Appendix A, "Obtaining and Installing Software," indicates where you can obtain all the necessary components and has instructions for installing them. If you're a complete newcomer to MySQL or to SQL, begin with Chapter 1, "Getting Started with MySQL and SQL." This provides you with a tutorial introduction that grounds you in basic MySQL and SQL concepts and brings you up to speed for the rest of the book. Then proceed to Chapter 2, "MySQL SQL Syntax and Use," and Chapter 3, "Working with Data in MySQL," to find out how to describe and manipulate your own data so that you can exploit MySQL's capabilities for your own applications. If you already know some SQL, you should still read Chapter 2 and Chapter 3. SQL implementations vary, and you'll want to find out what makes MySQL's implementation distinctive in comparison to others with which you may be familiar. If you have experience with MySQL but need more background on the details of performing particular tasks, use the book as a reference, looking up topics on a need-to-know basis. You'll find the appendixes especially useful for reference purposes. If you're interested in writing your own programs to access MySQL databases, read the API chapters, beginning with Chapter 5, "Introduction to MySQL Programming." If you want to produce a Web-based front end to your databases for easier access to them, or, conversely, to provide a database back end for your Web site to enhance your site with dynamic content, check out Chapter 7, "Writing MySQL Programs Using Perl DBI." and Chapter 8, "Writing MySQL Programs Using PHP." If you're evaluating MySQL to find out how it compares to your current RDBMS, several parts of the book will be useful. Read the SQL syntax and data type chapters in Part I to compare MySQL to the version of SQL that you're used to, the programming chapters in Part II if you need to write custom applications, and the administrative chapters in Part III to assess the level of administrative support a MySQL installation requires. This information is also useful if you're not currently using a database but are performing a comparative analysis of MySQL along with other database systems for the purpose of choosing one of them. Versions of Software Covered in This Book The first edition of this book covered MySQL 3.22 and the beginnings of MySQL 3.23 (up to about 3.23.3). The second edition expanded that range to include MySQL 4.0 and the first release of MySQL 4.1. The first edition was already a large book, and the additional material for the second edition made it even bigger. Now that MySQL has entered its 5.0 release series, it's become necessary to take a different approach to keep the book a more manageable size. For this third edition, the baseline for coverage is MySQL 4.1. That is, the book covers MySQL 4.1 and the early releases of MySQL 5.0. Most of this book still applies if you have an older version, but differences specific to older versions usually are not explicitly noted. For example, the book does not list limitations on foreign key support that apply only before MySQL 4.1. The MySQL 4.1 series now has reached production status, which means that it is considered stable for use in production environments. There were a lot of changes in earlier pre-production versions, and I recommend that you use the most recent version if possible. The current version as I write is 4.1.9. The MySQL 5.0 series is in early development (currently at 5.0.3). You'll need MySQL 5.0 if you want to try stored procedures, views, or triggers. These are still new, so expect some rough edges for a while. If you're using an older version of MySQL, be aware that the following major features will not be available to you: MySQL 4.1 adds subqueries, multiple character sets and collations, multiple time zones, a new binary client/server protocol that supports prepared statements, and spatial data types. MySQL 5.0 adds stored procedures and functions, views, triggers, strict input handling, true VARCHAR , and INFORMATION_SCHEMA . If you're using a version of MySQL older than 4.1, you may also find that certain features differ from the current behavior described in this book. Here are two resources to consult for more information about older versions: Check the MySQL Reference Manual: I have moved older material from previous editions of this book into an addendum that is available at the book's companion Web site: In this addendum, you will find details about the version in which statements, data types, and functions were introduced (when that happened before MySQL 4.1). The addendum also contains information about how older versions differ in behavior from 4.1 and up. I also draw your attention to some topics that are not covered in this book: The NDB storage engine and MySQL Cluster, which provide in-memory storage, high availability, and redundancy. NDB is relatively new in MySQL 4.1, and configuration management for NDB is still being worked out, so anything I might write here will be out of date quickly. See the MySQL Reference Manual for current details. MySQL Administrator and MySQL Query Browser. These are new tools that provide a graphical user interface (GUI) to MySQL databases. For more information or to download these programs, visit this page: In addition, features such as views and triggers were added very recently. They are covered, but in less depth than you can expect in a future edition of this book, after development has matured further. For the other major software packages discussed in the book, any recent versions should be sufficient for the examples shown. The current versions are shown in the following table: Package Version Perl DBI module 1.47 Perl DBD::mysql module 2.9004 PHP 4.3.10/5.0.3 Apache 1.3.33/2.0.52 CGI.pm 3.05 All software discussed in this book is available on the Internet. Appendix A, "Obtaining and Installing Software," provides instructions for getting MySQL, Perl DBI support, PHP, Apache, and CGI.pm onto your system. The appendix also contains instructions for obtaining the sampdb sample database that is used in examples throughout the book, as well as the example programs that are developed in the programming chapters. Conventions Used in This Book This book uses the following typographical conventions: Monospaced font indicates hostnames, filenames, directory names, commands, options, and Web sites. Bold monospaced font is used in command examples to indicate the part that you type. Italic monospaced font is used in commands to indicate where you should substitute a value of your own choosing. I assume that you enter commands by typing them into a terminal window or console window. To provide context, the prompt in command examples indicates the program from which you run the command. For example, SQL statements that are issued from within the mysql client program are shown preceded by the mysql> prompt. For commands that you issue from your command interpreter, the % prompt usually is used. In general, this prompt indicates commands that can be run either on Unix or Windows, although the particular prompt you see will depend on your command interpreter. (The command interpreter is your login shell on Unix, or cmd.exe or command.com on Windows.) More specialized command-line prompts are # , which indicates a command run on Unix as the root user via su or sudo , and C:\> to indicate a command intended specifically for Windows. The following example shows a command that should be entered from your command interpreter. The % indicates the prompt, which you do not type. To issue the command, you'd enter the boldface characters as shown, and substitute your own username for the italic word: In SQL statements, SQL keywords and function names are written in uppercase. Database, table, and column names are written in lowercase. In syntax descriptions, square brackets () indicate optional information. In lists of alternatives, vertical bar () is used as a separator between items. A list enclosed withinis optional and indicates that an item may be chosen from the list. A list enclosed within {{}} is mandatory and indicates that an item must be chosen from the list. The term "Windows NT-based systems" stands collectively for the family of Windows variants that are based on Windows NT, which includes systems such as Windows NT, 2000, XP, and 2003. It does not include Windows 95, 98, or Me. Additional Resources If you have a question that this book doesn't answer, where should you turn? Useful resources include the Web sites for the software you need help with: Package Primary Web Site MySQL http://dev.mysql.com/doc/ Perl DBI http://dbi.perl.org/ PHP http://www.php.net/ PEAR http://pear.php.net/ Apache http://httpd.apache.org/ CGI.pm http://stein.cshl.org/WWW/software/CGI/ Those sites contain pointers to various forms of information, such as reference manuals, frequently asked-question (FAQ) lists, and mailing lists: Reference manuals. The primary documentation included with MySQL itself is the Reference Manual. It's available in several formats, including an online version. For those who prefer printed format, MySQL Press publishes a MySQL Administrator's Guide and MySQL Language Reference, both of which are derived from the Reference Manual. PHP's manual comes in several forms, too. Manual pages. Documentation for the DBI module and its MySQL-specific driver, DBD::mysql, can be read online with the perldoc command. Try perldoc DBI and perldoc DBD::mysql . The DBI document provides general concepts. The MySQL driver document discusses capabilities specific to MySQL. FAQs. There are frequently asked-question lists for DBI, PHP, PEAR, and Apache. Mailing lists. Several mailing lists centering around the software discussed in this book are available. It's a good idea to subscribe to the ones that deal with the tools you want to use. It's also a good idea to use the archives for those lists that have them. When you're new to a tool, you will have many of the same questions that have been asked (and answered) a million times, and there is no reason to ask again if you can find the answer with a quick search of the archives. Instructions for subscribing to the mailing lists vary, but you can find the necessary information at the URLs shown here: Package Mailing List Instructions MySQL http://lists.mysql.com/ Perl DBI http://dbi.perl.org/ PHP http://www.php.net/mailing-lists.php PEAR http://pear.php.net/support/lists.php Apache http://httpd.apache.org/lists.html Ancillary Web sites. Besides the official Web sites, some of the tools discussed here have ancillary sites that provide more information, such as sample source code or topical articles. Check for a "Links" area on the official site you're visiting. (c) Copyright Pearson Education. All rights reserved. Excerpted from MySQL by Paul DuBois All rights reserved by the original copyright owners. Excerpts are provided for display purposes only and may not be reproduced, reprinted or distributed without the written permission of the publisher.