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.40. 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 found a sensitive security bug in MySQL, you should send an email to security@mysql.com.

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.9 General SQL Information and Tutorials. For books that focus more specifically on MySQL, see section 1.5 Books About MySQL.

1.1 What Is MySQL

MySQL, the most popular Open Source SQL database, is provided by MySQL AB. MySQL AB is a commercial company that builds its business providing services around the MySQL database. See section 1.2 What Is MySQL AB.

MySQL is a database management system.
A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network. To add, access, and process data stored in a computer database, you need a database management system such as MySQL. Since computers are very good at handling large amounts of data, database management plays a central role in computing, as stand-alone utilities, or as parts of other applications.
MySQL is a relational database management system.
A relational database stores data in separate tables rather than putting all the data in one big storeroom. This adds speed and flexibility. The tables are linked by defined relations making it possible to combine data from several tables on request. The SQL part of MySQL stands for "Structured Query Language" - the most common standardized language used to access databases.
MySQL is Open Source Software.
Open Source means that it is possible for anyone to use and modify. Anybody can download MySQL from the Internet and use it without paying anything. Anybody so inclined can study the source code and change it to fit their needs. MySQL uses the GPL (GNU General Public License) http://www.gnu.org, to define what you may and may not do with the software in different situations. If you feel uncomfortable with the GPL or need to embed MySQL into a commercial application you can buy a commercially licensed version from us.
Why use MySQL?
MySQL is very fast, reliable, and easy to use. If that is what you are looking for, you should give it a try. MySQL also has a very practical set of features developed in very close cooperation with our users. You can find a performance comparison of MySQL to some other database managers on our benchmark page. See section 13.7 Using Your Own Benchmarks. MySQL was originally developed to handle very large databases much faster than existing solutions and has been successfully used in highly demanding production environments for several years. Though under constant development, MySQL today offers a rich and very useful set of functions. The connectivity, speed, and security make MySQL highly suited for accessing databases on the Internet.
The technical features of MySQL
For advanced technical information, see section 7 MySQL Language Reference. MySQL is a client/server system that consists of a multi-threaded SQL server that supports different backends, several different client programs and libraries, administrative tools, and several programming interfaces. We also provide MySQL as a multi-threaded library which you can link into your application to get a smaller, faster, easier to manage product.
MySQL has a lot of contributed software available.
It is very likely that you will find that your favorite application or language already supports 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.

1.2 What Is MySQL AB

MySQL AB is the Swedish company owned and run by the MySQL founders and main developers. We are dedicated to developing MySQL and spreading our database to new users. MySQL AB owns the copyright to the MySQL server source code and the MySQL trademark. A significant amount of revenues from our services goes to developing MySQL. See section 1.1 What Is MySQL.

MySQL AB has been profitable providing MySQL from the start. We don't get any outside funding, but have earned all our money ourselves.

We are searching after partners that would like to support our development of MySQL so that we could accelerate the development pace. If you are interested in doing this, you can email partner@mysql.com about this!

MySQL AB has currently 20+ people (http://www.mysql.com/development/team.html) on its payroll and is growing rapidly.

Our main sources of income are:

The MySQL core values show our dedication to MySQL and Open Source.

We want MySQL to be:

MySQL AB and the people of MySQL AB:

1.3 About This Manual

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 E Credits.

1.3.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 wild card, use the `%' character.''
italic
Italic font is used for emphasis, like this.
boldface
Boldface font is used for access privilege names (for example, ``do not grant the process privilege lightly'') and occasionally to convey especially strong emphasis.

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 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}

1.4 History of MySQL

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. Which of the two gave its name to MySQL is still a mystery, even for us.

1.5 Books About MySQL

While this manual is still the right place for up to date technical 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 and related subjects (in English).

By purchasing a book through these hyperlinks provided herein, you are contributing to the development of MySQL.

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 distribution
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, 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:


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 plenty of 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 or 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.

MySQL and PHP from Scratch
Available Barnes and Noble
Publisher Que
Authors N/A
Pub Date September 2000
ISBN 0789724405
Pages 550
Price $34.99

This book puts together information on installing, setting up, and troubleshooting Apache, MySQL, PHP3, and IMP into one complete volume. You also learn how each piece is part of a whole by learning, step-by-step, how to create a web-based e-mail system. Learn to run the equivalent of Active Server Pages (ASP) using PHP3, set up an e-commerce site using a database and the Apache web server, and create a data entry system (such as sales, product quality tracking, customer preferences, etc) that no installation in the PC.

Professional MySQL Programming
Available Barnes and Noble
Publisher Wrox Press, Inc.
Authors N/A
Pub Date Late 2001
ISBN 1861005164
Pages 1000
Price $49.99

No description available.

Professional Linux Programming
Available Barnes and Noble
Publisher Wrox Press, Inc.
Authors N/A
Pub Date September 2000
ISBN 1861003013
Pages 1155
Price $47.99

In this follow-up to the best-selling Beginning Linux Programming, you will learn from the authors' real-world knowledge and experience of developing software for Linux; you'll be taken through the development of a sample 'DVD Store' application, with 'theme' chapters addressing different aspects of its implementation. Meanwhile, individual ``take-a-break'' chapters cover important topics that go beyond the bounds of the central theme. All focus on the practical aspects of programming, showing how crucial it is to choose the right tools for the job, use them as they should be used, and get things right first time.

PHP and MySQL Web Development
Available Barnes and Noble
Publisher Sams
Authors Luke Welling, Laura Thomson
Pub Date March 2001
ISBN 0672317842
Pages 700
Price $49.99

PHP and MySQL Web Development introduces you to the advantages of implementing both MySQL and PHP. These advantages are detailed through the provision of both statistics and several case studies. A practical web application is developed throughout the book, providing you with the tools necessary to implement a functional online database. Each function is developed separately, allowing you the choice to incorporate only those parts that you would like to implement. Programming concepts of the PHP language are highlighted, including functions which tie MySQL support into a PHP script and advanced topics regarding table manipulation.

Books recommended by the MySQL Developers

SQL-99 Complete, Really
Available Barnes and Noble
Publisher CMP Books
Authors Peter Gulutzan, Trudy Pelzer
Pub Date April 1999
ISBN 0879305681
Pages 1104
Price $55.96

This book contains complete descriptions of the new standards for syntax, data structures, and retrieval processes of SQL databases. As an example-based reference manual, it includes all of the CLI functions, information, schema tables, and status codes, as well as a working SQL database provided on the companion disk.

C, A reference manual
Available Barnes and Noble
Publisher Prentice Hall
Authors Samuel P. Harbison, Guy L. Steele
Pub Date September 1994
ISBN 0133262243
Pages 480
Price $35.99

A new and improved revision of the bestselling C language reference. This manual introduces the notion of "Clean C", writing C code that can be compiled as a C++ program, C programming style that emphasizes correctness, portability, maintainability, and incorporates the ISO C Amendment 1 (1994) which specifies new facilities for writing portable, international programs in C.

C++ for Real Programmers
Available Barnes and Noble
Publisher Academic Press, Incorporated
Authors Jeff Alger, Jim Keogh
Pub Date February 1998
ISBN 0120499428
Pages 388
Price $39.95

C++ For Real Programmers bridges the gap between C++ as described in beginner and intermediate-level books and C++ as it is practiced by experts. Numerous valuable techniques are described, organized into three simple themes: indirection, class hierarchies, and memory management. It also provides in-depth coverage of template creation, exception handling, pointers and optimization techniques. The focus of the book is on ANSI C++ and, as such, is compiler independent.

C++ For Real Programmers is a revision of Secrets of the C++ Masters and includes a new appendix comparing C++ with Java. The book comes with a 3.5" disk for Windows with source code.

Algorithms in C
Available Barnes and Noble
Publisher Addison Wesley Longman, Inc.
Authors Robert Sedgewick
Pub Date April 1990
ISBN 0201514257
Pages 648
Price $45.75

Algorithms in C describes a variety of algorithms in a number of areas of interest, including: sorting, searching, string-processing, and geometric, graph and mathematical algorithms. The book emphasizes fundamental techniques, providing readers with the tools to confidently implement, run, and debug useful algorithms.

Multithreaded Programming with Pthreads
Available Barnes and Noble
Publisher Prentice Hall
Authors Bil Lewis, Daniel J. Berg
Pub Date October 1997
ISBN 0136807291
Pages 432
Price $34.95

Based on the best-selling Threads Primer, Multithreaded Programming with Pthreads gives you a solid understanding of Posix threads: what they are, how they work, when to use them, and how to optimize them. It retains the clarity and humor of Threads Primer, but includes expanded comparisons to Win32 and OS/2 implementations. Code examples tested on all of the major UNIX platforms are featured along with detailed explanations of how and why they use threads.

Programming the PERL DBI: Database Programming with PERL
Available Barnes and Noble
Publisher O'Reilly & Associates, Incorporated
Authors Alligator Descartes, Tim Bunce
Pub Date February 2000
ISBN 1565926994
Pages 400
Price $27.96

Programming the Perl DBI is coauthored by Alligator Descartes, one of the most active members of the DBI community, and by Tim Bunce, the inventor of DBI. For the uninitiated, the book explains the architecture of DBI and shows you how to write DBI-based programs. For the experienced DBI dabbler, this book explains DBI's nuances and the peculiarities of each individual DBD.

The book includes:


1.6 The Main Features of MySQL

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

1.7 How Stable Is MySQL?

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 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 G 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 Version 3.22 and earlier. 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.
The MyISAM table handler -- Stable
This is new in MySQL Version 3.23. It's largely based on the ISAM table code but has a lot of new and 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 Version 3.20 releases, there were some limitations in the send/receive buffer size. As of Version 3.21, the buffer size is now dynamic up to a default of 16M.
Standard client programs -- Stable
These include mysql, mysqladmin, 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 -- Stable
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 -- Stable
The major 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 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 section 4.12.5 Linux Notes (All Linux Versions).
Solaris 2.5+ pthreads -- Stable
We use this for all our production work.
MIT-pthreads (Other systems) -- Stable
There have been no reported bugs since Version 3.20.15 and no known bugs since Version 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 -- Beta - Gamma
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 thought they had 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 Version 3.22.12.
DBD -- Stable
Now maintained by Jochen Wiedmann (wiedmann@neckar-alb.de). Thanks!
mysqlaccess -- Stable
Written and maintained by Yves Carlier (Yves.Carlier@rug.ac.be). Thanks!
GRANT -- Stable
Big changes made in MySQL Version 3.22.12.
MyODBC (uses ODBC SDK 2.5) -- Gamma
It seems to work well with some programs.
Replication -- Beta / Gamma
We are still working on replication, so don't expect this to be rock solid yet. On the other hand, some MySQL users are already using this with good results.
BDB Tables -- Beta
The Berkeley DB code is very stable, but we are still improving the interface between MySQL and BDB tables, so it will take some time before this is as tested as the other table types.
InnoDB Tables -- Beta
This is a recent addition to MySQL. They appear to work good and can be used after some initial testing.
Automatic recovery of MyISAM tables - Beta
This only affects the new code that checks if the table was closed properly on open and executes an automatic check/repair of the table if it wasn't.
MERGE tables -- Beta / Gamma
The usage of keys on MERGE tables is still not that tested. The other part of the MERGE code is quite well tested.
FULLTEXT -- Beta
Text search seems to work, but is still not widely used.

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.

1.8 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, 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;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE y2k (date date, date_time datetime, time_stamp timestamp);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO y2k VALUES 
    -> ("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",20500101000000);
Query OK, 13 rows affected (0.01 sec)
Records: 13  Duplicates: 0  Warnings: 0

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.3.1 Y2K Issues and Date Types for MySQL's rules for dealing with ambiguous date input data (data containing 2-digit year values).

1.9 General SQL Information and Tutorials

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

A SQL tutorial is available on the net at http://w3.one.net/~jhoffman/sqltut.htm

1.10 Useful MySQL-related Links

Apart from the following links, you can find and download a lot of MySQL programs, tools and APIs from the Contrib directory.

MySQL

1.10.1 Tutorials and Manuals

MySQL Myths Debunked
MySQL used in the real world.
http://www.4t2.com/mysql
Information about the German MySQL mailing list.
http://www2.rent-a-database.de/mysql/
MySQL handbook in German.
http://www.bitmover.com:8888//home/bk/mysql
Web access to the MySQL BitKeeper repository.
http://www.analysisandsolutions.com/code/mybasic.htm
Beginners MySQL Tutorial on how to install and set up MySQL on a Windows machine.
http://www.devshed.com/Server_Side/MySQL/
A lot of MySQL tutorials.
http://mysql.hitstar.com/
MySQL manual in Chinese.
http://www.linuxplanet.com/linuxplanet/tutorials/1046/1/
Setting Up a MySQL-based Web site.
http://www.hotwired.com/webmonkey/backend/tutorials/tutorial1.html
MySQL-Perl tutorial.
http://www.iserver.com/support/contrib/perl5/modules.html
Installing new Perl modules that require locally installed modules.
http://www.hotwired.com/webmonkey/databases/tutorials/tutorial4.html
PHP/MySQL Tutorial.
http://www.useractive.com/
Hands on tutorial for MySQL.

1.10.2 Porting MySQL/Using MySQL on Different Systems

http://xclave.macnn.com/MySQL/
The Mac OS Xclave. Running MySQL on Mac OS X.
http://www.prnet.de/RegEx/mysql.html
MySQL for Mac OS X Server.
http://www.latencyzero.com/macosx/mysql.html
Building MySQL for Mac OS X.
http://www.essencesw.com/Software/mysqllib.html
New Client libraries for the Mac OS Classic (Macintosh).
http://www.lilback.com/macsql/
Client libraries for Mac OS Classic (Macintosh).
http://sixk.maniasys.com/index_en.html
MySQL for Amiga

1.10.3 Perl-related Links

http://dbimysql.photoflux.com/
Perl DBI with MySQL FAQ.

1.10.4 MySQL Discussion Forums

http://www.weberdev.com/
Examples using MySQL; (check Top 20)
http://futurerealm.com/forum/futureforum.htm
FutureForum Web Discussion Software.

1.10.5 Commercial Applications that Support MySQL

http://www.supportwizard.com/
SupportWizard; Interactive helpdesk on the Web (This product includes a licensed copy of MySQL.)
http://www.sonork.com/
Sonork, Instant Messenger that is not only Internet oriented. It's focused on private networks and on small to medium companies. Client is free, server is free for up to 5 seats.
http://www.stweb.org/
StWeb - Stratos Web and Application server - An easy-to-use, cross platform, Internet/Intranet development and deployment system for development of web-enabled applications. The standard version of StWeb has a native interface to MySQL database.
http://www.rightnowtech.com/
Right Now Web; Web automation for customer service.
http://www.icaap.org/Bazaar/
Bazaar; Interactive Discussion Forums with Web interface.
http://www.phonesweep.com/
PhoneSweepT is the world's first commercial Telephone Scanner. Many break-ins in recent years have come not through the Internet, but through unauthorized dial-up modems. PhoneSweep lets you find these modems by repeatedly placing phone calls to every phone number that your organization controls. PhoneSweep has a built-in expert system that can recognize more than 250 different kinds of remote-access programs, including Carbon Copy(TM), pcANYWHERE(TM), and Windows NT RAS. All information is stored in the SQL database. It then generates a comprehensive report detailing which services were discovered on which dial-up numbers in your organization.

1.10.6 SQL Clients and Report Writers

urSQL
SQL Editor and Query Utility. Custom syntax highlighting, editable results grid, exportable result-sets, basic MySQL admin functions, Etc.. For Windows.
MySQL Data Manager
MySQL Data Manager * is platform independent web client (written in perl) for MySQL server over TCP/IP.
http://ksql.sourceforge.net/
KDE MySQL client.
http://www.ecker-software.de
A Windows GUI client by David Ecker.
http://www.icaap.org/software/kiosk/
Kiosk; a MySQL client for database management. Written in Perl. Will be a part of Bazaar.
http://www.casestudio.com/
Db design tool that supports MySQL 3.23.
http://home.skif.net/~voland/zeos/eng/index.html
Zeos - A client that supports MySQL, Interbase and PostgreSQL.
http://www.geocities.com/SiliconValley/Ridge/4280/GenericReportWriter/grwhome.html
A free report writer in Java
http://www.javaframework.de
MySQLExport - Export of MySQL create statements and data in a lot of different formats (SQL, HTML, CVS, text, ZIP, GZIP...)
http://dlabs.4t2.com
M2D, a MySQL Administration client for Windows. M2D supports administration of MySQL databases, creation of new databases and tables, editing, and more.
http://dlabs.4t2.com
Dexter, a small server written in Perl which can be used as a proxy server for MySQL or as a database extender.
http://www.scibit.com/Products/Software/Utils/Mascon.asp
Mascon is a powerful Win32 GUI for administering MySQL databases.
http://www.rtlabs.com/
MacSQL Monitor. GUI for MySQL, ODBC, and JDBC databases for the Mac OS.

1.10.7 Distributions that Include MySQL

http://www.suse.com/
SuSE Linux (6.1 and above)
http://www.redhat.com/
RedHat Linux (7.0 and above)
http://distro.conectiva.com.br
Conectiva Linux (4.0 and above)

1.10.8 Web Development Tools that Support MySQL

http://www.php.net/
PHP: A server-side HTML-embedded scripting language.
http://www.midgard-project.org
The Midgard Application Server; a powerful Web development environment based on MySQL and PHP.
http://www.smartworker.org
SmartWorker is a platform for Web application development.
http://xsp.lentus.se/
XSP: e(X)tendible (S)erver (P)ages and is a HTML embedded tag language written in Java (previously known as XTAGS.)
http://www.dbServ.de/
dbServ is an extension to a web server to integrate database output into your HTML code. You may use any HTML function in your output. Only the client will stop you. It works as standalone server or as Java servlet.
http://www.chilisoft.com/
Platform independent ASP from Chili!Soft
http://www.voicenet.com/~zellert/tjFM
A JDBC driver for MySQL.
http://www.wernhart.priv.at/php/
MySQL + PHP demos.
http://www.dbwww.com/
ForwardSQL: HTML interface to manipulate MySQL databases.
http://www.daa.com.au/~james/www-sql/
WWW-SQL: Display database information.
http://www.minivend.com/minivend/
Minivend: A Web shopping cart.
http://www.heitml.com/
HeiTML: A server-side extension of HTML and a 4GL language at the same time.
http://www.metahtml.com/
Metahtml: A Dynamic Programming Language for WWW Applications.
http://www.binevolve.com/
VelocityGen for Perl and Tcl.
http://hawkeye.net/
Hawkeye Internet Server Suite.
http://www.fastflow.com/
Network Database Connection For Linux
http://www.wdbi.net/
WDBI: Web browser as a universal front end to databases which supports MySQL well.
http://www.webgroove.com/
WebGroove Script: HTML compiler and server-side scripting language.
http://www.ihtml.com/
A server-side Web site scripting language.
ftp://ftp.igc.apc.org/pub/myodbc/README
How to use MySQL with ColdFusion on Solaris.
http://calistra.com/MySQL/
Calistra's ODBC MySQL Administrator.
http://www.webmerger.com
Webmerger - This CGI tool interprets files and generates dynamic output based on a set of simple tags. Ready-to-run drivers for MySQL and PostgreSQL through ODBC.
http://phpclub.net/
PHPclub - Tips and tricks for PHP.
http://www.penguinservices.com/scripts
MySQL and Perl Scripts.
http://www.widgetchuck.com
The Widgetchuck; Web Site Tools and Gadgets
http://www.adcycle.com/
AdCycle - advertising management software.
http://sourceforge.net/projects/pwpage/
pwPage - provides an extremely fast and simple approach to the creation of database forms. That is, if a database table exists and an HTML page has been constructed using a few simple guidelines, pwPage can be immediately used for table data selections, insertions, updates, deletions and selectable table content reviewing.
http://www.omnis-software.com/products/studio/studio.html
OMNIS Studio is a rapid application development (RAD) tool.
http://www.webplus.com
talentsoft Web+ 4.6 - a powerful and comprehensive development language for use in creating web-based client/server applications without writing complicated, low-level, and time-consuming CGI programs.

1.10.9 Database Design Tools with MySQL Support

http://www.mysql.com/documentation/dezign/
"DeZign for databases" is a database development tool that uses an entity relationship diagram (ERD).

1.10.10 Web Servers with MySQL Tools

ftp://ftp.kcilink.com/pub/
mod_auth_mysql, An Apache authentication module.
http://www.roxen.com/
The Roxen Challenger Web server.

1.10.11 Extensions for Other Programs

http://www.seawood.org/msql_bind/
MySQL support for BIND (The Internet Domain Name Server).
http://www.inet-interactive.com/sendmail/
MySQL support for Sendmail and Procmail.

1.10.12 Using MySQL with Other Programs

http://www.iserver.com/support/addonhelp/database/mysql/msaccess.html
Using MySQL with Access.
http://www.iserver.com/support/contrib/perl5/modules.html
Installing new Perl modules that require locally installed modules.

1.10.13 ODBC-related Links

http://www.iodbc.org/
Popular iODBC Driver Manager (libiodbc) now available as Open Source.
http://users.ids.net/~bjepson/freeODBC/
The FreeODBC Pages.
http://genix.net/unixODBC/
The unixODBC Project goals are to develop and promote unixODBC to be the definitive standard for ODBC on the Linux platform. This is to include GUI support for KDE.
http://www.sw-soft.com/products/BtrieveODBC/
A MySQL-based ODBC driver for Btrieve.

1.10.14 API-related Links

http://www.jppp.com/
Partially implemented TDataset-compatible components for MySQL.
http://www.riverstyx.net/qpopmysql/
qpopmysql - A patch to allow POP3 authentication from a MySQL database. There's also a link to Paul Khavkine's patch for Procmail to allow any MTA to deliver to users in a MySQL database.
http://www.pbc.ottawa.on.ca
Visual Basic class generator for Active X.
http://www.essencesw.com/Software/mysqllib.html
New Client libraries for the Mac OS Classic (Macintosh).
http://www.lilback.com/macsql/
Client libraries for the Macintosh.
http://www.essencesw.com/Plugins/mysqlplug.html
Plugin for REALbasic (for Macintosh)
http://www.iis.ee.ethz.ch/~neeri/macintosh/gusi-qa.html
A library that emulates BSD sockets and pthreads on Macintosh. This can be used if you want to compile the MySQL client library on Mac. It could probably even be sued to port MySQL to Macintosh, but we don't know of anyone that has tried that.
http://www.dedecker.net/jessie/scmdb/
SCMDB - an add-on for SCM that ports the MySQL C library to scheme (SCM). With this library scheme developers can make connections to a MySQL database and use embedded SQL in their programs.

1.10.15 Other MySQL-related Links

SAT
The Small Application Toolkit (SAT) is a collection of utilities intended to simplify the development of small, multi-user, GUI based applications in a (Microsoft -or- X) Windows Client / Unix Server environment.
http://www.wix.com/mysql-hosting/
Registry of Web providers who support MySQL.
http://www.softagency.co.jp/mysql/index.en.html
Links about using MySQL in Japan/Asia.
http://abattoir.cc.ndsu.nodak.edu/~nem/mysql/udf/
MySQL UDF Registry.
http://www.open.com.au/products.html
Commercial Web defect tracking system.
http://www.stonekeep.com/pts/
PTS: Project Tracking System.
http://tomato.nvgc.vt.edu/~hroberts/mot
Job and software tracking system.
http://www.cynergi.net/exportsql/
ExportSQL: A script to export data from Access95+.
http://SAL.KachinaTech.COM/H/1/MYSQL.html
SAL (Scientific Applications on Linux) MySQL entry.
http://www.infotech-nj.com/itech/index.shtml
A consulting company which mentions MySQL in the right company.
http://www.pmpcs.com/
PMP Computer Solutions. Database developers using MySQL and mSQL.
http://www.aewa.org/
Airborne Early Warning Association.
http://www.dedserius.com/y2kmatrix/
Y2K tester.

1.10.16 SQL and Database Interfaces

http://java.sun.com/products/jdbc/
The JDBC database access API.
http://www.gagme.com/mysql
Patch for mSQL Tcl.
http://www.amsoft.ru/easysql/
EasySQL: An ODBC-like driver manager.
http://www.lightlink.com/hessling/rexxsql.html
A REXX interface to SQL databases.
http://www.mytcl.cx/
Tcl interface based on tcl-sql with many bugfixes.
http://www.binevolve.com/~tdarugar/tcl-sql/
Tcl interface.
http://www.contrib.andrew.cmu.edu/~shadow/sql.html
SQL Reference Page with a lot of interesting links.

1.10.17 Examples of MySQL Use

http://www.little6.com/about/linux/
Little6 Inc., An online contract and job finding site that is powered by MySQL, PHP3, and Linux.
http://www.delec.com/is/products/prep/examples/BookShelf/index.html
DELECis - A tool that makes it very easy to create an automatically generated table documentation. They have used MySQL as an example.
http://www.worldrecords.com
World Records - A search engine for information about music that uses MySQL and PHP.
http://www.webtechniques.com/archives/1998/01/note/
A Contact Database using MySQL and PHP.
http://modems.rosenet.net/mysql/
Web based interface and Community Calendar with PHP.
http://www.odbsoft.com/cook/sources.htm
Perl package to generate html from a SQL table structure and for generating SQL statements from an html form.
http://www.gusnet.cx/proj/telsql/
Basic telephone database using DBI/DBD.
http://tecfa.unige.ch/guides/java/staf2x/ex/jdbc/coffee-break
JDBC examples by Daniel K. Schneider.
http://www.spade.com/linux/howto/PostgreSQL-HOWTO-41.html
SQL BNF
http://www.ooc.com/
Object Oriented Concepts Inc; CORBA applications with examples in source.
http://www.pbc.ottawa.on.ca/
DBWiz; Includes an example of how to manage cursors in VB.
http://keilor.cs.umass.edu/pluribus/
Pluribus is a free search engine that learns to improve the quality of its results over time. Pluribus works by recording which pages a user prefers among those returned for a query. A user votes for a page by selecting it; Pluribus then uses that knowledge to improve the quality of the results when someone else submits the same (or similar) query. Uses PHP and MySQL.
http://www.stopbit.com/
Stopbit - A technology news site using MySQL and PHP.
http://www.linuxsupportline.com/~kalendar/
KDE based calendar manager - The calendar manager has both single user (file based) and multi-user (MySQL database) support.
http://tim.desert.net/~tim/imger/
Example of storing/retrieving images with MySQL and CGI.
http://www.penguinservices.com/scripts
Online shopping cart system.
http://www.city-gallery.com/album/
Old Photo Album - The album is a collaborative popular history of photography project that generates all pages from data stored in a MySQL database. Pages are dynamically generated through a php3 interface to the database content. Users contribute images and descriptions. Contributed images are stored on the web server to avoid storing them in the database as BLOBs. All other information is stored on the shared MySQL server.

1.10.18 General Database Links

http://www.pcslink.com/~ej/dbweb.html
Database Jump Site
http://black.hole-in-the.net/guy/webdb/
Homepage of the webdb-l (Web Databases) mailing list.
http://www.symbolstone.org/technology/perl/DBI/index.html
Perl DBI/DBD modules homepage.
http://www.student.uni-koeln.de/cygwin/
Cygwin tools. Unix on top of Windows.
http://dbasecentral.com/
dbasecentral.com; Development and distribution of powerful and easy-to-use database applications and systems.
http://www.tek-tips.com/
Tek-Tips Forums are 800+ independent peer-to-peer non-commercial support forums for Computer Professionals. Features include automatic e-mail notification of responses, a links library, and member confidentiality guaranteed.
http://www.public.asu.edu/~peterjn/btree/
B-Trees: Balanced Tree Data Structures.
http://www.fit.qut.edu.au/~maire/baobab/lecture/sld001.htm
A lecture about B-Trees.

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.