General Information about MySQL

This is the MySQL reference manual. This version documents the version of MySQL.

MySQL is a basically free SQL database server. See section Licensing or When do I have/want to pay for MySQL?.

The latest information about MySQL is found at the MySQL Home page

To see what it can do. See section The main features of MySQL.

For installation instructions See section Compiling and installing MySQL. For tips on how to port MySQL to new machines/operating systems See section Comments on porting to other systems..

If you have any suggestions concerning additions or corrections to this manual, please send them to the MySQL mailing list Here]}. See section Subscribing to/un-subscribing from the MySQL mailing list..

See section Upgrading to 3.21 from a 3.20 version, for information about upgrading from a 3.20 release.

For examples of SQL and benchmarking information see the `bench' directory.

For future plans See section List of things we want to add to MySQL in the future..

A history of new features/bug fixes See section MySQL change history.

For the currently known bugs/misfeatures (known errors) See section Known errors and design deficiencies in MySQL.

For A list of all the contributors to this product See section Who has helped to make MySQL..

IMPORTANT:

Send bug (error) reports, questions and comments to the mailing list at

Please use the mysqlbug script when posting bug reports or questions about MySQL. mysqlbug will gather some information about your system and start your editor with a form in which you can describe your problem. Bug reports might be silently ignored by the MySQL maintainers if there is not a good reason included in the report as to why mysqlbug has not been used. A report that says 'MySQL does not work for me. Why?' is not consider a valid bug report.

The mysqlbug script can be found in the `scripts' directory in the distribution, that is `there-you-installed-mysql/scripts'.

What is MySQL?

MySQL is a SQL (Structured Query Language) 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/libraries.

The main goals of MySQL are speed and robustness.

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. While MySQL is actively developed, it already offers a rich and highly useful function set.

The official way to pronounce MySQL is 'My Ess Que Ell' (Not MY-SEQUEL).

About this manual

This manual is currently available in TeXInfo, Raw text, Info and HTML versions. A PostScript version as available do download separately because of its size.

The primary document is the TeXInfo file. The HTML version is automatically produced with a modified texi2html. The ASCII and info version are produced with makeinfo. The Postscript version is produced using texi2dvi and dvips.

This manual was written and is maintained by David Axmark, Michael (Monty) Widenius and Kim Aldale. For other contributors See section Contributed programs.

History of MySQL

We once started off with the intention to use mSQL to connect to our own fast low level (ISAM) tables. However, after some testing we came to the conclusion that mSQL was not fast 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.

It is not perfectly clear where the name MySQL derives from. Our base directory and a large amount 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 main features of MySQL

• Fully multi-threaded using kernel threads. That means that it easily can use multiple CPUs if available.
• C, C++, Java, Perl, Python and TCL API's. See section MySQL client tools and API's
• Works on a lot of different platforms. See section Which operating systems does MySQL support?.
• Lots of column types like: signed/unsigned integers 1,2,3,4 and 8 bytes long, FLOAT, DOUBLE, CHAR, VARCHAR, TEXT, BLOB, DATE, SET and ENUM types. See section Column types.
• Very fast joins using a optimised one-sweep multi-join.
• Full function support in the SELECT and WHERE parts. Example: select column1 + column2 from table where column1/column2 > 0
• SQL functions are implemented through a very optimised class library and should be as fast as they can get! Usually there shouldn't be any memory allocation at all after the query initialisation.
• Full support for SQL GROUP BY and ORDER BY. Support for group functions (COUNT, AVG, SUM, MAX and MIN).
• Support for LEFT OUTER JOIN with ANSI SQL and ODBC syntax.
• A privilege and password system with is very flexible and secure. Allows host based verification.
• Secure passwords since all password traffic on the net is encrypted.
• ODBC Open-DataBase-Connectivity for Windows95 (with source). All ODBC 2.5 functions and lots of others. So you can for example use Access to connect to your MySQL server. See section ODBC
• Very fast B-tree disk tables with index compression.
• 16 indexes/table. Each index may consist of 1 to 15 columns/parts of columns. Max index length is 127 bytes. An index may be a prefix of a CHAR field.
• Fixed and variable length records.
• In memory hash tables always used as temporary tables.
• Can handle big databases (we are using MySQL with some databases that contain 50,000,000 records).
• All columns have default values. One can always use INSERT on any subset of columns.
• Uses GNU autoconf for portability.
• Written in C and C++. Tested with gcc 2.7.2.
• A very fast thread-based memory allocation system.
• No memory leaks. Tested with a commercial memory leakage detector (Purify).
• A very fast table check,optimise and repair utility. See section The MySQL table check, optimise and repair program
• All data saved in ISO8859_1 format. All comparisons for normal string columns are case insensitive.
• Full ISO8859_1 (Latin1) support. For example Scandinavian едц is allowed in table and column names.
• Sorts by ISO8859_1 Latin1 (the Swedish way at the moment). It is possible to change this in the source by adding new sort order arrays. To see a example of a very advanced sorting, look at the Czeck sorting code.
• Alias on tables and columns as in the SQL92 standard.
• DELETE, INSERT, REPLACE, and UPDATE returns how many rows were affected.
• Function names do not clash with table or column names. For example ABS is a valid column name. See section Is MySQL picky about reserved words?
• All MySQL commands have --help or -? for help.
• The server currently supports error messages to clients in many languages. See section Which languages are supported by MySQL?.
• The client uses a TCP connection or unix socket when connecting to the MySQL server.
• User commands as show tables, show index from table and show columns from table

General SQL information and tutorials

There is one SQL tutor on the net.

This book has been recommended by a lot of 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/

And another book also recommended by people on the MySQL mailing list.

Understanding SQL
ISBN 0-89588-644-8
Publisher Sybex 510 523 8233
Alameda CA USA

Useful MySQL-related links

Some web development tools which support MySQL

• PHP: A server-side HTML-embedded scripting language
• MySQL + PHP demos
• WWW-SQL: Display databases information
• Minivend: A Web shopping cart
• HeiTML: A server-side extension of HTML and a 4GL language at the same time
• Metahtml: A Dynamic Programming Language for WWW Applications

Web servers with MySQL tools

• An Apache authentication module
• The Roxen Challenger Web server

Other MySQL related links.

• Comercial Web defect tracking system
• PTS - Project Tracking System
• Support for BIND (The Internet Domain Name Server)
• A REXX interface to SQL databases
• Registry of Web providers who support MySQL
• MySQL TCL patch homepage
• Full-text search engine for MySQL
• WDB; Web browser as a universal front end to databases
• EasySQL; An ODBC like driver manager
• Network Database Connection For Linux
• ExportSQL A script to export data from Access
• SAL (Scientific Applications on Linux) MySQL entry
• MySQL Apps and Utilities Listing
• The MySQL reference manual in Adobe PDF format
• A consulting company which mentions MySQL in the right company.
• PMP Computer Solutions. Database developers using mySQL and mSQL

General database links.

• Database Jump Site
• The JDBC database access API
• Homepage of the webdb-l (Web Databases) mailing list.

There are also many web pages that use MySQL. See section Some users of MySQL.. Send any additions to this list to

What are stored procedures and triggers and so on?

A stored procedure is some code that is stored and run in the server. After this, the client doesn't have to issue the whole query but can refer to the stored procedure. This gives more speed because the query only has to be parsed once and less data has to be sent between the server and the client. You can also raise the conceptual level by having libraries of functions in the server.

A trigger is a stored procedure that is invoked when something happens. For example, one can install a stored procedure that checks every delete to a transaction table and does an automatic delete on the corresponding customer when all their transactions are deleted.

To see when MySQL might get these functions See section List of things we want to add to MySQL in the future..

Go to the first, previous, next, last section, table of contents.