Go to the first, previous, next, last section, table of contents.


1 General Information about MySQL

This is the MySQL reference manual; it documents MySQL version 3.23.12c-alpha.

MySQL is a very fast, multi-threaded, multi-user and robust SQL (Structured Query Language) database server.

For Unix and OS/2 platforms, MySQL is basically free; for Microsoft platforms you must get a MySQL license after a trial time of 30 days. See section 3 MySQL licensing and support.

The MySQL home page provides the latest information about MySQL.

For a discussion of MySQL's capabilities, see section 1.5 The main features of MySQL.

For installation instructions, see section 4 Installing MySQL. For tips on porting MySQL to new machines or operating systems, see section G Comments on porting to other systems.

For information about upgrading from a 3.21 release, see section 4.16.2 Upgrading from a 3.21 version to 3.22.

For a tutorial introduction to MySQL, see section 9 MySQL Tutorial.

For examples of SQL and benchmarking information, see the benchmarking directory (`sql-bench' in the distribution).

For a history of new features and bug fixes, see section D MySQL change history.

For a list of currently known bugs and misfeatures, see section E Known errors and design deficiencies in MySQL.

For future plans, see section F List of things we want to add to MySQL in the future (The TODO).

For a list of all the contributors to this project, see section C Contributors to MySQL.

IMPORTANT:

Send error (often called bugs) reports, questions and comments to the mailing list at mysql@lists.mysql.com. See section 2.3 How to report bugs or problems.

For source distributions, the mysqlbug script can be found in the `scripts' directory. For binary distributions, mysqlbug can be found in the `bin' directory.

If you have any suggestions concerning additions or corrections to this manual, please send them to the manual team at (docs@mysql.com).

1.1 What is MySQL?

MySQL is a true multi-user, multi-threaded SQL database server. SQL (Structured Query Language) is the most popular and standardized database language in the world. MySQL is a client/server implementation that consists of a server daemon mysqld and many different client programs and libraries.

SQL is a standardized language that makes it easy to store, update and access information. For example, you can use SQL to retrieve product information and store customer information for a web site. MySQL is also fast and flexible enough to allow you to store logs and pictures in it.

The main goals of MySQL are speed, robustness and ease of use. MySQL was originally developed because we needed a SQL server that could handle very large databases an order of magnitude faster than what any database vendor could offer to us on inexpensive hardware. We have now been using MySQL since 1996 in an environment with more than 40 databases containing 10,000 tables, of which more than 500 have more than 7 million rows. This is about 100 gigabytes of mission-critical data.

The base upon which MySQL is built is a set of routines that have been used in a highly demanding production environment for many years. Although MySQL is still under development, it already offers a rich and highly useful function set.

The official way to pronounce MySQL is ``My Ess Que Ell'' (Not MY-SEQUEL).

1.2 About this manual

This manual is currently available in Texinfo, plain text, Info, HTML, PostScript and PDF versions. Because of their size, PostScript and PDF versions are not included with the main MySQL distribution, but are available for separate download at http://www.mysql.com.

The primary document is the Texinfo file. The HTML version is produced automatically with a modified version of texi2html. The plain text and Info versions are produced with makeinfo. The Postscript version is produced using texi2dvi and dvips. The PDF version is produced with pdftex.

This manual is written and maintained by David Axmark, Michael (Monty) Widenius and Paul DuBois. For other contributors, see section C Contributors to MySQL.

1.2.1 Conventions used in this manual

This manual uses certain typographical conventions:

constant
Constant-width font is used for command names and options; SQL statements; database, table and column names; C and Perl code; and environment variables. Example: ``To see how mysqladmin works, invoke it with the --help option.''
`filename'
Constant-width font with surrounding quotes is used for filenames and pathnames. Example: ``The distribution is installed under the `/usr/local/' directory.''
`c'
Constant-width font with surrounding quotes is also used to indicate character sequences. Example: ``To specify a wildcard, use the `%' character.''
italic
Italic font is used for emphasis, like this.
boldface
Boldface font is used for access privilege names (e.g., ``do not grant the process privilege lightly'') and to convey especially strong emphasis.

When commands are shown that are meant to be executed by a particular program, the program is indicated by the prompt shown with the command. For example, shell> indicates a command that you execute from your login shell, and mysql> indicates a command that you execute from the mysql client:

shell> type a shell command here
mysql> type a mysql command here

Shell commands are shown using Bourne shell syntax. If you are using a csh-style shell, you may need to issue commands slightly differently. For example, the sequence to set an environment variable and run a command looks like this in Bourne shell syntax:

shell> VARNAME=value some_command

For csh, you would execute the sequence like this:

shell> setenv VARNAME value
shell> some_command

Database, table and column names often must be substituted into commands. To indicate that such substitution is necessary, this manual uses db_name, tbl_name and col_name. For example, you might see a statement like this:

mysql> SELECT col_name FROM db_name.tbl_name;

This means that if you were to enter a similar statement, you would supply your own database, table and column names, perhaps like this:

mysql> SELECT author_name FROM biblio_db.author_list;

SQL statements may be written in uppercase or lowercase. When this manual shows a SQL statement, uppercase is used for particular keywords if those keywords are under discussion (to emphasize them) and lowercase is used for the rest of the statement. So you might see the following in a discussion of the SELECT statement:

mysql> SELECT count(*) FROM tbl_name;

On the other hand, in a discussion of the COUNT() function, the statement would be written like this:

mysql> select COUNT(*) from tbl_name;

If no particular emphasis is intended, all keywords are written uniformly in uppercase.

In syntax descriptions, square brackets (`[' and `]') are used to indicate optional words or clauses:

DROP TABLE [IF EXISTS] tbl_name

When a syntax element consists of a number of alternatives, the alternatives are separated by vertical bars (`|'). When one member from a set of choices may be chosen, the alternatives are listed within square brackets. When one member from a set of choices must be chosen, the alternatives are listed within braces (`{' and `}'):

TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
{DESCRIBE | DESC} tbl_name {col_name | wild}

1.3 History of MySQL

We once started off with the intention of using mSQL to connect to our tables using our own fast low-level (ISAM) routines. However, after some testing we came to the conclusion that mSQL was not fast enough or flexible enough for our needs. This resulted in a new SQL interface to our database but with almost the same API interface as mSQL. This API was chosen to ease porting of third-party code.

The derivation of the name MySQL is not perfectly clear. Our base directory and a large number of our libraries and tools have had the prefix ``my'' for well over 10 years. However, Monty's daughter (some years younger) is also named My. So which of the two gave its name to MySQL is still a mystery, even for us.

1.4 Books about MySQL

While this manual is still the right place for up to date techical information, its primary goal is to contain everything there is to know about MySQL. And it is sometimes nice to have a bound book to read in bed or while you travel. Here are a list of books about MySQL (in English).

Title MySQL
Publisher New Riders
Author Paul DuBois
Pub Date 1st Edition December 1999
ISBN 0735709211
Pages 800
Price $49.99 US
Downloadable examples samp_db.tar.gz

Foreword by Michael "Monty" Widenius, MySQL Moderator

In MySQL, Paul DuBois provides you with a comprehensive guide to one of the most popular relational database systems. Paul has contributed to the online documentation for MySQL, and is an active member of the MySQL community. The principal MySQL developer, Monty Widenius, and a network of his fellow developers reviewed the manuscript, providing Paul with the kind of insight no one else could supply.

Instead of merely giving you a general overview of MySQL, Paul teaches you how to make the most of its capabilities. Through two sample database applications that run throughout the book, he gives you solutions to problems you're sure to face. He helps you integrate MySQL efficiently with third-party tools, such as PHP and Perl, enabling you to generate dynamic Web pages through database queries. He teaches you to write programs that access MySQL databases, and also provides a comprehensive set of references to column types, operators, functions, SQL syntax, MySQL programming, C API, Perl DBI, and PHP API. MySQL simply gives you the kind of information you won't find anywhere else.

If you use MySQL, this book provides you with:

Title MySQL & mSQL
Publisher O'Reilly
Authors Randy Jay Yarger, George Reese & Tim King
Pub Date 1st Edition July 1999
ISBN 1-56592-434-7, Order Number: 4347
Pages 506
Price $34.95

This book teaches you how to use MySQL and mSQL, two popular and robust database products that support key subsets of SQL on both Linux and UNIX systems. Anyone who knows basic C, Java, Perl, or Python can write a program to interact with a database, either as a stand-alone application or through a Web page. This book takes you through the whole process, from installation and configuration to programming interfaces and basic administration. Includes ample tutorial material.

1.5 The main features of MySQL

The following list describes some of the important characteristics of MySQL:

1.6 How stable is MySQL?

This section addresses the questions, ``how stable is MySQL?'' and, ``can I depend on MySQL in this project?'' Here we will try to clarify some issues and to answer some of the more important questions that seem to concern many people. This section has been put together from information gathered from the mailing list (which is very active in reporting bugs).

At TcX, MySQL has worked without any problems in our projects since mid-1996. When MySQL was released to a wider public, we noticed that there were some pieces of ``untested code'' that were quickly found by the new users who made queries in a manner different than our own. Each new release has had fewer portability problems than the previous one (even though each has had many new features), and we hope that it will be possible to label one of the next releases ``stable''.

Each release of MySQL has been usable and there have been problems only when users start to use code from ``the gray zones''. Naturally, outside users can't know what the gray zones are; this section attempts to indicate those that are currently known. The descriptions deal with the 3.22.x version of MySQL. All known and reported bugs are fixed in the latest version, with the exception of the bugs listed in the bugs section, which are things that are ``design''-related. See section E Known errors and design deficiencies in MySQL.

MySQL is written in multiple layers and different independent modules. These modules are listed below with an indication of how well-tested each of them is:

The ISAM table handler -- Stable
This manages storage and retrieval of all data in MySQL 3.22 and earlier versions. In all MySQL releases there hasn't been a single (reported) bug in this code. The only known way to get a corrupted table is to kill the server in the middle of an update. Even that is unlikely to destroy any data beyond rescue, because all data are flushed to disk between each query. There hasn't been a single bug report about lost data because of bugs in MySQL, either.
The MyISAM table handler -- Beta
This is new in MySQL 3.23. It's largely based on the ISAM table code but has a lot of new very useful features.
The parser and lexical analyser -- Stable
There hasn't been a single reported bug in this system for a long time.
The C client code -- Stable
No known problems. In early 3.20 releases, there were some limitations in the send/receive buffer size. As of 3.21.x, the buffer size is now dynamic up to a default of 24M.
Standard client programs -- Stable
These include mysql, mysqladmin and mysqlshow, mysqldump, and mysqlimport.
Basic SQL -- Stable
The basic SQL function system and string classes and dynamic memory handling. Not a single reported bug in this system.
Query optimizer -- Stable
Range optimizer -- Gamma
Join optimizer -- Stable
Locking -- Gamma
This is very system-dependent. On some systems there are big problems using standard OS locking (fcntl()). In these cases, you should run the MySQL daemon with the --skip-locking flag. Problems are known to occur on some Linux systems and on SunOS when using NFS-mounted file systems.
Linux threads -- Gamma
The only problem found has been with the fcntl() call, which is fixed by using the --skip-locking option to mysqld. Some people have reported lockup problems with the 0.5 release.
Solaris 2.5+ pthreads -- Stable
We use this for all our production work.
MIT-pthreads (Other systems) -- Gamma
There have been no reported bugs since 3.20.15 and no known bugs since 3.20.16. On some systems, there is a ``misfeature'' where some operations are quite slow (a 1/20 second sleep is done between each query). Of course, MIT-pthreads may slow down everything a bit, but index-based SELECT statements are usually done in one time frame so there shouldn't be a mutex locking/thread juggling.
Other thread implementions -- Alpha - Beta
The ports to other systems are still very new and may have bugs, possibly in MySQL, but most often in the thread implementation itself.
LOAD DATA ..., INSERT ... SELECT -- Stable
Some people have thought they have found bugs here, but these usually have turned out to be misunderstandings. Please check the manual before reporting problems!
ALTER TABLE -- Stable
Small changes in 3.22.12.
DBD -- Stable
Now maintained by Jochen Wiedmann
mysqlaccess -- Stable
Written and maintained by Yves Carlier
GRANT -- Gamma
Big changes made in MySQL 3.22.12.
MyODBC (uses ODBC SDK 2.5) -- Gamma
It seems to work well with some programs.

TcX provides email support for paying customers, but the MySQL mailing list usually provides answers to common questions. Bugs are usually fixed right away with a patch; for serious bugs, there is almost always a new release.

1.7 Year 2000 compliance

MySQL itself has no problems with Year 2000 (Y2K) compliance:

You may run into problems with applications that use MySQL in a way that is not Y2K-safe. For example, many old applications store or manipulate years using 2-digit values (which are ambiguous) rather than 4-digit values. This problem may be compounded by applications that use values such as 00 or 99 as ``missing'' value indicators.

Unfortunately, these problems may be difficult to fix, since different applications may be written by different programmers, each of whom may use a different set of conventions and date-handling functions.

Here is a simple demonstration illustrating that MySQL doesn't have any problems with dates until the year 2030!

mysql> DROP TABLE IF EXISTS y2k;
mysql> CREATE TABLE y2k (date date, date_time datetime, time_stamp timestamp);
mysql> INSERT INTO y2k VALUES ("1998-12-31","1998-12-31 23:59:59",19981231235959);
mysql> INSERT INTO y2k VALUES ("1999-01-01","1999-01-01 00:00:00",19990101000000);
mysql> INSERT INTO y2k VALUES ("1999-09-09","1999-09-09 23:59:59",19990909235959);
mysql> INSERT INTO y2k VALUES ("2000-01-01","2000-01-01 00:00:00",20000101000000);
mysql> INSERT INTO y2k VALUES ("2000-02-28","2000-02-28 00:00:00",20000228000000);
mysql> INSERT INTO y2k VALUES ("2000-02-29","2000-02-29 00:00:00",20000229000000);
mysql> INSERT INTO y2k VALUES ("2000-03-01","2000-03-01 00:00:00",20000301000000);
mysql> INSERT INTO y2k VALUES ("2000-12-31","2000-12-31 23:59:59",20001231235959);
mysql> INSERT INTO y2k VALUES ("2001-01-01","2001-01-01 00:00:00",20010101000000);
mysql> INSERT INTO y2k VALUES ("2004-12-31","2004-12-31 23:59:59",20041231235959);
mysql> INSERT INTO y2k VALUES ("2005-01-01","2005-01-01 00:00:00",20050101000000);
mysql> INSERT INTO y2k VALUES ("2030-01-01","2030-01-01 00:00:00",20300101000000);
mysql> INSERT INTO y2k VALUES ("2050-01-01","2050-01-01 00:00:00",20500101000000);
mysql> SELECT * FROM y2k;
+------------+---------------------+----------------+
| date       | date_time           | time_stamp     |
+------------+---------------------+----------------+
| 1998-12-31 | 1998-12-31 23:59:59 | 19981231235959 |
| 1999-01-01 | 1999-01-01 00:00:00 | 19990101000000 |
| 1999-09-09 | 1999-09-09 23:59:59 | 19990909235959 |
| 2000-01-01 | 2000-01-01 00:00:00 | 20000101000000 |
| 2000-02-28 | 2000-02-28 00:00:00 | 20000228000000 |
| 2000-02-29 | 2000-02-29 00:00:00 | 20000229000000 |
| 2000-03-01 | 2000-03-01 00:00:00 | 20000301000000 |
| 2000-12-31 | 2000-12-31 23:59:59 | 20001231235959 |
| 2001-01-01 | 2001-01-01 00:00:00 | 20010101000000 |
| 2004-12-31 | 2004-12-31 23:59:59 | 20041231235959 |
| 2005-01-01 | 2005-01-01 00:00:00 | 20050101000000 |
| 2030-01-01 | 2030-01-01 00:00:00 | 20300101000000 |
| 2050-01-01 | 2050-01-01 00:00:00 | 00000000000000 |
+------------+---------------------+----------------+

13 rows in set (0.00 sec)

This shows that the DATE and DATETIME types are will not give any problems with future dates (they handle dates until the year 9999).

The TIMESTAMP type, that is used to store the current time, has a range up to only 2030-01-01. TIMESTAMP has a range of 1970 to 2030 on 32-bit machines (signed value). On 64-bit machines it handles times up to 2106 (unsigned value).

Even though MySQL is Y2K-compliant, it is your responsibility to provide unambiguous input. See section 7.3.6.1 Y2K issues and date types for MySQL's rules for dealing with ambiguous date input data (data containing 2-digit year values).

1.8 General SQL information and tutorials

This book has been recommended by a several people on the MySQL mailing list:

Judith S. Bowman, Sandra L. Emerson and Marcy Darnovsky
The Practical SQL Handbook: Using Structured Query Language
Second Edition
Addison-Wesley
ISBN 0-201-62623-3
http://www.awl.com

This book has also received some recommendations by MySQL users:

Martin Gruber
Understanding SQL
ISBN 0-89588-644-8
Publisher Sybex 510 523 8233
Alameda, CA USA

A SQL tutorial is available on the net at http://www.geocities.com/SiliconValley/Vista/2207/sql1.html

SQL in 21 Tagen (online book in German language): http://www.mut.de/leseecke/buecher/sql/inhalt.htm

1.9 Useful MySQL-related links

1.9.1 Tutorials

1.9.2 Porting MySQL / Using MySQL on different systems

1.9.3 Perl related links

1.9.4 MySQL discussion forums

1.9.5 Commercial applications that support MySQL

1.9.6 SQL Clients/Report writers

1.9.7 Web development tools that support MySQL

1.9.8 Databse design tools with MySQL support

1.9.9 Web servers with MySQL tools

1.9.10 Extensions for other programs

1.9.11 Using MySQL with other programs

1.9.12 ODBC related links

1.9.13 API related links

1.9.14 Other MySQL-related links

1.9.15 SQL and database interfaces

1.9.16 Examples of MySQL use

1.9.17 General database links

There are also many web pages that use MySQL. See section A Some MySQL users. Send any additions to this list to MySQL logo somewhere (It is okay to have it on a ``used tools'' page or something similar) to be added.


Go to the first, previous, next, last section, table of contents.