This is the MySQL reference manual; it documents MySQL Version 3.23.28-gamma. As MySQL is work in progress, the manual gets updated frequently. There is a very good chance that this version is out of date, unless you are looking at it online. The most recent version of this manual is available at http://www.mysql.com/documentation/ in many different formats. If you have a hard time finding information in the manual, you can try the searchable PHP version at http://www.mysql.com/documentation/manual.php.
MySQL is a very fast, multi-threaded, multi-user, and robust SQL (Structured Query Language) database server.
MySQL is free software. It is licensed with the GNU GENERAL PUBLIC LICENSE http://www.gnu.org/. See section 3 MySQL Licensing and Support.
The MySQL home page provides the latest information about MySQL.
The following list describes some useful sections of the manual:
IMPORTANT:
Reports of errors (often called bugs), as well as questions and comments,
should be sent to the mailing list at mysql@lists.mysql.com.
See section 2.3 How to Report Bugs or Problems.
The mysqlbug script should be used to generate bug reports.
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).
This is a reference manual; it does not provide general instruction on SQL or relational database concepts. If you want general information about SQL, see section 1.8 General SQL Information and Tutorials. For books that focus more specifically on MySQL, see section 1.4 Books About MySQL.
The official way to pronounce MySQL is ``My Ess Que Ell'' (not MY-SEQUEL). But we try to avoid correcting people who say MY-SEQUEL.
MySQL core values
We want MySQL to be:
MySQL AB and the people of MySQL AB:
This manual is currently available in Texinfo, plain text, Info, HTML,
PostScript, and PDF versions. The primary document is the Texinfo file.
The HTML version is produced automatically using 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, Jeremy Cole, and Paul DuBois. For other contributors, see section D Contributors to MySQL.
This manual uses certain typographical conventions:
constant
mysqladmin works, invoke it with the
--help option.''
When commands are shown that are meant to be executed by a particular
program, the program is indicated by a prompt shown before 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 program:
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
Often, database, table, and column names 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 an 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. For example, 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
same 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 (`[' and `]'):
TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
When one member from a set of choices must be chosen, the alternatives are listed within braces (`{' and `}'):
{DESCRIBE | DESC} tbl_name {col_name | wild}
We once started out 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 nor
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.
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. It is sometimes nice to have a bound book to read in bed or while you travel. Here is a list of books about MySQL (in English):
MySQL
| Available | Barnes and Noble |
| 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
|
| Errata | are available here |
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, and provided 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:
DBI and PHP APIs for developing
command-line and Web-based applications
DBI API,
and PHP's MySQL-related functions
MySQL & mSQL
| Available | Barnes and Noble |
| 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.
Sams' Teach Yourself MySQL in 21 Days
| Available | Barnes and Noble |
| Publisher | Sams |
| Authors | Mark Maslakowski and Tony Butcher |
| Pub Date | June 2000 |
| ISBN | 0672319144 |
| Pages | 650 |
| Price | $39.99 |
Sams Teach Yourself MySQL in 21 Days is for intermediate Linux users
who want to move into databases. A large share of the audience is Web
developers who need a database to store large amounts of information that can
be retrieved via the Web. Sams' Teach Yourself MySQL in 21 Days is a
practical, step-by-step tutorial. The reader will learn to design and employ
this open source database technology into his/her Web site using practical,
hands-on examples to follow.
E-Commerce Solutions with MySQL
| Available | Barnes and Noble |
| Publisher | Prima Communications, Inc. |
| Authors | N/A |
| Pub Date | January 2000 |
| ISBN | 0761524452 |
| Pages | 500 |
| Price | $39.99 |
No description available.
Professional MySQL Programming
| Available | Barnes and Noble |
| Publisher | Wrox Press, Inc. |
| Authors | N/A |
| Pub Date | July 2000 |
| ISBN | 1861004281 |
| Pages | 1000 |
| Price | $49.99 |
No description available.
PHP3 and MySQL Web Development
| Available | Barnes and Noble |
| Publisher | N/A |
| Authors | William Jason Gilmore |
| Pub Date | October 2000 |
| ISBN | 0672317842 |
| Pages | N/A |
| Price | $49.99 |
No description available.
The following list describes some of the important characteristics of MySQL:
FLOAT, DOUBLE, CHAR, VARCHAR,
TEXT, BLOB, DATE, TIME, DATETIME,
TIMESTAMP, YEAR, SET, and ENUM types. See section 7.3 Column Types.
SELECT and WHERE
parts of queries. Example:
mysql> SELECT CONCAT(first_name, " ", last_name) FROM tbl_name
WHERE income/dependents > 10000 AND age > 30;
GROUP BY and ORDER BY
clauses. Support for group functions (COUNT(),
COUNT(DISTINCT), AVG(), STD(), SUM(),
MAX() and MIN()).
LEFT OUTER JOIN and RIGHT OUTER JOIN with ANSI
SQL and ODBC syntax.
CHAR or VARCHAR field.
INSERT to insert a
subset of a table's columns; those columns that are not explicitly given
values are set to their default values.
libtool for portability.
purify).
myisamchk, a very fast utility for table checking,
optimization, and repair.
See section 15 Maintaining a MySQL installation.
DELETE, INSERT, REPLACE, and UPDATE return
how many rows were changed (affected). It is possible to return the number
of rows matched instead by setting a flag when connecting to the server.
ABS
is a valid column name. The only restriction is that for a function call, no
spaces are allowed between the function name and the `(' that follows it.
See section 7.39 Is MySQL picky about reserved words?.
--help or -?
options to obtain online assistance.
SHOW command can be used to retrieve
information about databases, tables, and indexes. The EXPLAIN command
can be used to determine how the optimizer resolves a query.
This section addresses the questions ``How stable is MySQL?'' and ``Can I depend on MySQL in this project?'' 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).
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 don't know what the gray zones are; this section attempts to indicate those that are currently known. The descriptions deal with Version 3.23.x 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 F 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:
mysql, mysqladmin, mysqlshow,
mysqldump, and mysqlimport.
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.
fcntl() call, which is
fixed by using the --skip-locking option to
mysqld. Some people have reported lockup problems with Version 0.5.
LinuxThreads will need to be recompiled if you plan to use
1000+ concurrent connections. Although it is possible to run that many
connections with the default LinuxThreads (however, you will never go
above 1021), the default stack spacing of 2 MB makes the application
unstable, and we have been able to reproduce a coredump after creating
1021 idle connections. See Linux Notes for more details.
SELECT statements are usually done in one time frame so there shouldn't
be a mutex locking/thread juggling.
LOAD DATA ..., INSERT ... SELECT -- Stable
ALTER TABLE -- Stable
mysqlaccess -- Stable
GRANT -- Stable
MERGE tables is still not that tested. The
other part of the MERGE code is quite well tested.
MySQL AB provides e-mail 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.
MySQL itself has no problems with Year 2000 (Y2K) compliance:
2069; all 2-digit years are regarded to be in the range
1970 to 2069, which means that if you store 01 in a
year column, MySQL treats it as 2001.
YEAR column type
can store years 0 and 1901 to 2155 in 1 byte and display
them using 2 or 4 digits.
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, because 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 will not
give any problems with future dates (they handle dates until the year
9999).
The TIMESTAMP type, which 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).
The following book has been recommended by 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
The following 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
An SQL tutorial is available on the net at http://www.geocities.com/SiliconValley/Vista/2207/sql1.html.
Apart from the following links, you can find and download a lot of MySQL programs, tools and APIs from the Contrib directory.
1.9.1 Tutorials and Manuals
mSQL.
mSQL Tcl.
DBI/DBD.
DBI/DBD modules homepage.
There are also many Web pages that use MySQL. See section B Some MySQL users. Send any additions to this list to webmaster@mysql.com. We now require that you show a MySQL logo somewhere if you wish your site to be added. (It is okay to have it on a ``used tools'' page or something similar.)
Go to the first, previous, next, last section, table of contents.