 |
Go to the first, previous, next, last section, table of contents.
This is the MySQL reference manual; it documents MySQL
version 3.23.5-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.4 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 8 MySQL Tutorial.
For examples of SQL and benchmarking information, see the benchmarking
directory. For source distributions, this is the `bench'
directory. For binary distributions, this is the `sql-bench'
directory.
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 product, see section C Contributors to MySQL.
IMPORTANT:
Send bug (error) reports, questions and comments to the mailing list at
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 MySQL mailing list
(mysql@lists.mysql.com) with the following subject line:
documentation suggestion: [Insert Topic Here].
See section 2.1 The MySQL mailing lists.
MySQL is a true multi-user, multi-threaded SQL database server. SQL
is the most popular 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 at TcX needed a SQL server
that could handle very large databases an order of magnitude faster than what
any database vendor could offer to us. 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).
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 the Ghostscript utility ps2pdf.
This manual is written and maintained by David Axmark, Michael (Monty)
Widenius, Paul DuBois and Kim Aldale. For other contributors, see
section C Contributors to MySQL.
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}
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.
The following list describes some of the important characteristics
of 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.
MySQL itself has no problems with Year 2000 (Y2K) compliance:
-
MySQL uses Unix time functions and has no problems with dates
until
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.
-
All MySQL date
functions are stored in one file `sql/time.cc' and coded very carefully
to be year 2000-safe.
-
In MySQL 3.22 and later versions, the new
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, 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.2.6.1 Y2K issues and date types for MySQL's rules
for dealing with ambiguous date input data (data containing 2-digit year
values).
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 on the mailing list:
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.8.1 Tutorials
1.8.2 Commercial applications that support MySQL
1.8.3 SQL Clients
1.8.4 Web development tools that support MySQL
1.8.5 Databse design tools with MySQL support
1.8.6 Web servers with MySQL tools
1.8.7 Extensions for other programs
1.8.8 Using MySQL with other programs
1.8.9 ODBC related links
1.8.10 API related links
1.8.11 Other MySQL-related links
1.8.12 SQL and database interfaces
1.8.13 Examples of MySQL use
1.8.14 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.
|
