 | ||||||||||
 |
|
|
||||||||
|
|
|||||||||
| ||||||||||
| ||||||||||
| ||||||||||
|
|
|
||||||||
|
|
|||||||||
| ||||||||||
| ||||||||||
This is the MySQL reference manual; it documents MySQL version 3.23.21-beta. As MySQL is work in progress, the manual gets updated frequently. So there is a very good chance that this version is out of date, unless you are looking at it on-line. 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 the information in the manual, you can try its 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.
The 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).
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. 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 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 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 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 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
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. 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 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.
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 is 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
|
| Errata | are available here |
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:
DBI and PHP APIs for developing
command-line and Web-based applications
DBI API,
and PHP's MySQL-related functions
| 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.
| Title | Sams Teach Yourself MySQL in 21 Days |
| Publisher | Sams |
| Authors | Mark Maslakowski and Tony Butcher |
| Pub Date | June 2000 |
| ISBN | 0672319144 |
| Pages | N/A |
| 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 to be web developers needing 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.
| Title | E-Commerce Solutions with MySQL |
| Publisher | Prima |
| Authors | N/A |
| Pub Date | September 2000 |
| ISBN | 0761524452 |
| Pages | 500 |
| Price | $39.99 |
No description available.
| Title | Professional MySQL Programming |
| Publisher | Wrox |
| Authors | N/A |
| Pub Date | July 2000 |
| ISBN | 1861004281 |
| Pages | 1000 |
| Price | $49.99 |
No description available.
| Title | PHP3 and MySQL Web Development |
| Publisher | N/A |
| Authors | William Jason Gilmore |
| Pub Date | October 2000 |
| ISBN | 672317842 |
| Pages | N/A |
| Price | $39.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 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.34 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?'' 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 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 and 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 the 0.5
release. 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 -- Gamma
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:
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 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).
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
Apart from the following links, you can find and download a lot of MySQL programs, tools and APIs from the Contrib directory.
DBI:DBD and CGI.pm.
FutureSQL allows one to easily setup config files to view, edit, delete
and otherwise process records from a MySQL database. It uses a data
dictionary, configuration files and templates, and allows "pre-processing"
and "post-processing" on both fields, records and operations.
MySQL with other programsmSQL
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 (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.
| ||||||
|
|
|||||
| ||||||
|
||||||