MySQL Reference Manual

What Is MySQL?

MySQL, the most popular open source SQL database, is developed and provided by MySQL AB. MySQL AB is a commercial company that builds its business providing services around the MySQL database. See Section 1.3.

The MySQL web site (http://www.mysql.com/) provides the latest information about MySQL software and 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 Server. 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 standardised language used to access databases.

MySQL software is open source.

open source means that it is possible for anyone to use and modify. Anybody can download the MySQL software from the Internet and use it without paying anything. Anybody so inclined can study the source code and change it to fit their needs. The MySQL software uses the GPL (GNU General Public License), http://www.gnu.org/licenses/, 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 code into a commercial application you can buy a commercially licensed version from us. See Section 1.4.3.

Why use the MySQL Database Server?

The MySQL Database Server is very fast, reliable, and easy to use. If that is what you are looking for, you should give it a try. MySQL server also has a practical set of features developed in close cooperation with our users. You can find a performance comparison of MySQL server to some other database managers on our benchmark page. See Section 5.1.4.

MySQL server was originally developed to handle 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 server today offers a rich and useful set of functions. Its connectivity, speed, and security make MySQL server highly suited for accessing databases on the Internet.

The technical features of MySQL server

For advanced technical information, see Chapter 6. The MySQL Database Software 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 a wide range of programming interfaces (APIs).

We also provide MySQL server as a multi-threaded library which you can link into your application to get a smaller, faster, easier-to-manage product.

There is a large amount of contributed MySQL software available.

It is very likely that you will find that your favorite application or language already supports the MySQL Database Server.

The official way to pronounce MySQL is “My Ess Que Ell” (not “my sequel”), but we don’t mind if you pronounce it as “my sequel” or in some other localised way.

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.

The Main Features of MySQL

The following list describes some of the important characteristics of the MySQL Database Software. See Section 1.5.

Internals and Portability

• Written in C and C++. Tested with a broad range of different compilers.

• No memory leaks. The MySQL code has been tested with Purify, a commercial memory leakage detector.

• Works on many different platforms. See Section 2.2.2.

• Uses GNU Automake (1.4), Autoconf (Version 2.52 or newer), and Libtool for portability.

• APIs for C, C++, Eiffel, Java, Perl, PHP, Python, and Tcl. See Chapter 8.

• Fully multi-threaded using kernel threads. This means it can easily use multiple CPUs if available.

• Very fast B-tree disk tables with index compression.

• A very fast thread-based memory allocation system.

• Very fast joins using an optimised one-sweep multi-join.

• In-memory hash tables which are used as temporary tables.

• SQL functions are implemented through a highly optimised class library and should be as fast as possible! Usually there isn’t any memory allocation at all after query initialisation.

Column Types

• Many column types: signed/unsigned integers 1, 2, 3, 4, and 8 bytes long, FLOAT, DOUBLE, CHAR, VARCHAR, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP, YEAR, SET, and ENUM types. See Section 1.9.1.3.1.

• Fixed-length and variable-length records.

• All columns have default values. You can use INSERT to insert a subset of a table’s columns; those columns that are not explicitly given values are set to their default values.

Commands and Functions

• Full operator and function support in the SELECT and WHERE parts of queries. For example:

  mysql> SELECT CONCAT(first_name, " ", last_name)
      -> FROM tbl_name
      -> WHERE income/dependents > 10000 AND age > 30;

• Full support for SQL GROUP BY and ORDER BY clauses. Support for group functions (COUNT( ), COUNT(DISTINCT ...), AVG( ), STD( ), SUM( ), MAX( ), and MIN( )).

• Support for LEFT OUTER JOIN and RIGHT OUTER JOIN with ANSI SQL and ODBC syntax.

• Aliases on tables and columns are allowed as in the SQL92 standard.

• DELETE, INSERT, REPLACE, and UPDATE return the number of rows that were changed (affected). It is possible to return the number of rows matched instead by setting a flag when connecting to the server.

• The MySQL-specific SHOW command can be used to retrieve information about databases, tables, and indexes. The EXPLAIN command can be used to determine how the optimiser resolves a query.

• Function names do not clash with table or column names. For example, 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 6.1.6.

• You can mix tables from different databases in the same query (as of Version 3.22).

Security

• A privilege and password system that is very flexible and secure, and allows host-based verification. Passwords are secure because all password traffic is encrypted when you connect to a server.

Scalability and Limits

• Handles large databases. We are using MySQL server with some databases that contain 50 million records and we know of users that use MySQL server with 60,000 tables and about 5,000,000,000 rows.

• Up to 32 indexes per table are allowed. Each index may consist of 1 to 16 columns or parts of columns. The maximum index width is 500 bytes (this may be changed when compiling MySQL server). An index may use a prefix of a CHAR or VARCHAR field.

Connectivity

• Clients may connect to the MySQL server using TCP/IP Sockets, Unix Sockets (Unix), or Named Pipes (NT).

• ODBC (Open-DataBase-Connectivity) support for Win32 (with source). All ODBC 2.5 functions and many others. For example, you can use MS Access to connect to your MySQL server. See Section 8.3.

Localisation

• The server can provide error messages to clients in many languages. See Section 4.6.2.

• Full support for several different character sets, including ISO-8859-1 (Latin1), german, big5, ujis, and more. For example, the Scandinavian characters 'å', 'ä' and 'ö' are allowed in table and column names.

• All data is saved in the chosen character set. All comparisons for normal string columns are case-insensitive.

• Sorting is done according to the chosen character set (the Swedish way by default). It is possible to change this when the MySQL server is started. To see an example of very advanced sorting, look at the Czech sorting code. MySQL server supports many different character sets that can be specified at compile and runtime.

Clients and Tools

• Includes myisamchk, a very fast utility for table checking, optimisation, and repair. All of the functionality of myisamchk is also available through the SQL interface as well. See Chapter 4.

• All MySQL programs can be invoked with the --help or -? options to obtain online assistance.

How Stable Is MySQL?

This section addresses the questions "How stable is MySQL server?" and "Can I depend on MySQL server in this project?" We will try to clarify these issues and answer some important questions that concern many potential users. The information in this section is based on data gathered from the mailing list, which is very active in identifying problems as well as reporting types of use.

Original code stems back from the early ’80s, providing a stable code base, and the ISAM table format remains backward-compatible. At TcX, the predecessor of MySQL AB, MySQL code has worked in projects since mid-1996, without any problems. When the MySQL Database Software 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 different types of queries from us. Each new release has had fewer portability problems (even though each new release has had many new features).

Each release of the MySQL server has been usable. There have only been problems when users try code from the “gray zones.” Naturally, new users don’t know what the gray zones are; this section attempts to indicate those that are currently known. The descriptions mostly deal with Version 3.23 of MySQL server. All known and reported bugs are fixed in the latest version, with the exception of those listed in the bugs section, which are things that are design-related. See Section 1.7.5.

The MySQL server design is multi-layered with independent modules. Some of the newer modules are listed here with an indication of how well-tested each of them is:

Replication—Gamma

Large server clusters using replication are in production use, with good results. Work on enhanced replication features is continuing in MySQL 4.0.

InnoDB tables—Stable (in 3.23 from 3.23.49)

The InnoDB transactional table handler has now been declared stable in the MySQL 3.23 tree, starting from version 3.23.49. InnoDB is being used in large, heavy-load production systems.

BDB tables—Gamma

The Berkeley DB code is very stable, but we are still improving the BDB transactional table handler interface in MySQL server, so it will take some time before this is as well tested as the other table types.

FULLTEXT—Beta

Full-text search works but is not yet widely used. Important enhancements are being implemented for MySQL 4.0.

MyODBC 2.50 (uses ODBC SDK 2.5)—Gamma

Increasingly in wide use. Some issues brought up appear to be application-related and independent of the ODBC driver or underlying database server.

Automatic recovery of MyISAM tables—Gamma

This status only regards the new code in the MyISAM table handler that checks if the table was closed properly on open and executes an automatic check/repair of the table if it wasn’t.

Bulk-insert—Alpha

New feature in MyISAM tables in MySQL 4.0 for faster insert of many rows.

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 mysqld with the --skip-locking flag. Problems are known to occur on some Linux systems, and on SunOS when using NFS-mounted filesystems.

MySQL AB provides high-quality 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.

How Big Can MySQL Tables Be?

MySQL Version 3.22 has a 4G limit on table size. With the new MyISAM table type in MySQL Version 3.23, the maximum table size is pushed up to 8 million terabytes (2 ^ 63 bytes).

Note, however, that operating systems have their own file-size limits. Here are some examples:

Operating System	File-Size Limit
Linux-Intel 32 bit	2G, 4G or more, depends on Linux version
Linux-Alpha	8T (?)
Solaris 2.5.1	2G (possible 4G with patch)
Solaris 2.6	4G
Solaris 2.7 Intel	4G
Solaris 2.7 UltraSPARC	512G

On Linux 2.2 you can get bigger tables than 2G by using the LFS patch for the ext2 filesystem. On Linux 2.4 patches also exist for ReiserFS to get support for big files.

This means that the table size for MySQL databases is normally limited by the operating system.

By default, MySQL tables have a maximum size of about 4G. You can check the maximum table size for a table with the SHOW TABLE STATUS command or with the myisamchk -dv table_name. See Section 4.5.6.

If you need bigger tables than 4G (and your operating system supports this), you should set the AVG_ROW_LENGTH and MAX_ROWS parameter when you create your table. See Section 6.5.3. You can also set these later with ALTER TABLE. See Section 6.5.4.

If your big table is going to be read-only, you could use myisampack to merge and compress many tables to one. myisampack usually compresses a table by at least 50%, so you can have, in effect, much bigger tables. See Section 4.7.4.

You can go around the operating system file limit for MyISAM data files by using the RAID option. See Section 6.5.3.

Another solution can be the included MERGE library, which allows you to handle a collection of identical tables as one. See Section 7.2.

Year 2000 Compliance

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

• MySQL server 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 server treats it as 2001.

• All MySQL date functions are stored in one file, sql/time.cc, and are coded very carefully to be year 2000-safe.

• In MySQL Version 3.22 and later, 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 server 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 server 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 server is Y2K-compliant, it is your responsibility to provide unambiguous input. See Section 6.2.2.1 for MySQL server’s rules for dealing with ambiguous date input data (data containing 2-digit year values).