What Is MySQL?
==============
`MySQL', the most popular `Open Source' SQL database, is developed,
distributed, and supported by `MySQL AB'. `MySQL AB' is a commercial
company, founded by the MySQL developers, that builds its business
providing services around the `MySQL' database. *Note What is MySQL
AB::.
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 systems play 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 `SQL' part of "`MySQL'" stands for "`Structured
Query Language'". SQL is the most common standardised language
used to access databases and is defined by the ANSI/ISO SQL
Standard.(The SQL standard has been evolving since 1986 and
several versions exist. In this manual, "`SQL-92'" refers to the
standard released in 1992, "`SQL-99'" refers to the standard
released in 1999, and "`SQL:2003'" refers to the version of the
standard that is expected to be released in mid-2003.We use the
term "`the SQL standard'" to mean the current version of the SQL
Standard at any time.)
MySQL software is `Open Source'.
`Open Source' means that it is possible for anyone to use and
modify the software. Anybody can download the `MySQL' software
from the Internet and use it without paying anything. If you
wish, you may study the source code and change it to suit your
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. *Note MySQL licenses::.
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' with other database
managers on our benchmark page. *Note MySQL Benchmarks::.
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.
`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
----------------
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 clear. Our base directory
and a large number of our libraries and tools have had the prefix "my"
for well over 10 years. However, co-founder Monty Widenius'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 name of the MySQL Dolphin (our logo) is `Sakila'. `Sakila' was
chosen by the founders of MySQL AB from a huge list of names suggested
by users in our "Name the Dolphin" contest. The winning name was
submitted by Ambrose Twebaze, an open source software developer from
Swaziland, Africa. According to Ambrose, the name Sakila has its roots
in SiSwati, the local language of Swaziland. Sakila is also the name of
a town in Arusha, Tanzania, near Ambrose's country of origin, Uganda.
The Main Features of MySQL
--------------------------
The following list describes some of the important characteristics of
the `MySQL Database Software'. *Note MySQL 4.0 Nutshell::.
Internals and Portability
* Written in C and C++. Tested with a broad range of different
compilers.
* Works on many different platforms. *Note Which OS::.
* Uses GNU Automake, Autoconf, and Libtool for portability.
* APIs for C, C++, Eiffel, Java, Perl, PHP, Python, Ruby, and
Tcl. *Note Clients::.
* Fully multi-threaded using kernel threads. This means it can
easily use multiple CPUs if available.
* Transactional and non-transactional storage engines.
* Very fast B-tree disk tables (MyISAM) with index compression.
* Relatively easy to add another storage engine. This is useful
if you want to add an SQL interface to an in-house database.
* 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.
initialisation.
* The `MySQL' code gets tested with Purify (a commercial memory
leakage detector) as well as with Valgrind, a `GPL' tool
(`http://developer.kde.org/~sewardj/').
* Available as client/server or embedded (linked) version.
`TIMESTAMP', `YEAR',
`SET', and `ENUM' types. *Note Column types::.
* Fixed-length and variable-length records.
Commands and Functions
* Full operator and function support in the `SELECT' and `WHERE'
clauses of queries. For example:
mysql> SELECT CONCAT(first_name, " ", last_name)
-> FROM tbl_name
-> WHERE income/dependents > 10000 AND age > 30;
`GROUP_CONCAT()').
* Support for `LEFT OUTER JOIN' and `RIGHT OUTER JOIN' with
both standard SQL and ODBC syntax.
* Support for aliases on tables and columns as required by
SQL-92.
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. *Note
Reserved words::.
* 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 use `MySQL Server' with
databases that contain 50 million records. We also 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 are supported, as are many
others. For example, you can use MS Access to connect to
your `MySQL' server. *Note ODBC::.
Localisation
* The server can provide error messages to clients in many
languages. *Note Languages::.
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.
*Note MySQL Database Administration::.
* 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, our new users quickly found
some pieces of "untested code". Each new release since then has had
fewer portability problems (even though each new release has also had
many new features).
Each release of the `MySQL Server' has been usable. Problems have
occurred only when users try code from the "gray zones." Naturally,
new users don't know what the gray zones are; this section therefore
attempts to document those areas that are currently known. The
descriptions mostly deal with Version 3.23 and 4.0 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. *Note Bugs::.
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.x.
*`InnoDB' tables -- Stable (in 3.23 from 3.23.49)*
The `InnoDB' transactional storage engine has 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 storage engine 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 have been implemented in `MySQL' 4.0.
*`MyODBC 3.51' (uses ODBC SDK 3.51) -- Stable*
In wide production 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 applies only to the new code in the `MyISAM' storage
engine 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-external-locking' flag.
Problems are known to occur on some Linux systems, and on SunOS
when using NFS-mounted filesystems.
there is
almost always a new release.
How Big Can MySQL Tables Be?
----------------------------
`MySQL' Version 3.22 had a 4 GB (4 gigabyte) limit on table size. With
the `MyISAM' table type in `MySQL' Version 3.23, the maximum table size
was pushed up to 8 million terabytes (2 ^ 63 bytes).
In effect, then, the table size for `MySQL' databases is normally
limited by the operating system.
Note, however, that operating systems have their own file-size limits.
Here are some examples:
*Operating System* *File-Size Limit*
Linux-Intel 32 bit 2 GB, much more when using LFS
Linux-Alpha 8 TB (?)
Solaris 2.5.1 2 GB (possible 4GB with patch)
Solaris 2.6 4 GB (can be changed with flag)
Solaris 2.7 Intel 4 GB
Solaris 2.7 512 GB
UltraSPARC
On Linux 2.2 you can get tables larger than 2 GB in size by using the
LFS patch for the ext2 filesystem. On Linux 2.4 patches also exist for
ReiserFS to get support for big files. Most current distributions are
based on kernel 2.4 and already include all the required Large File
Support (LFS) patches. However, the maximum available file size still
depends on several factors, one of them being the file system used to
store MySQL tables.
For a very detailed overview about LFS in Linux, have a look at Andreas
Jaeger's "Large File Support in Linux" page at
.
By default, `MySQL' tables have a maximum size of about 4 GB. You can
check the maximum table size for a table with the `SHOW TABLE STATUS'
command or with the `myisamchk -dv table_name'. *Note SHOW::.
If you need a table that will be larger than 4 GB in size (and your
operating system supports it), set the `AVG_ROW_LENGTH' and `MAX_ROWS'
parameters accordingly when you create your table. *Note CREATE
TABLE::. You can also set these parameters later, with `ALTER TABLE'.
*Note ALTER TABLE::.
If your big table is a read-only table, you could use `myisampack' to
merge and compress many tables into one. `myisampack' usually
compresses a table by at least 50%, so you can have, in effect, much
bigger tables. *Note `myisampack': myisampack.
You can get around the operating system file limit for `MyISAM'
datafiles using the `RAID' option. *Note CREATE TABLE::.
Another solution can be the included `MERGE' library, which allows you
to handle a collection of identical tables as one. *Note `MERGE'
tables: MERGE.
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 considered 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 `YEAR' column type can
store years `0' and `1901' to `2155' in one byte and display them
using two or four digits.
rather
than 4-digit values. This problem may be compounded by applications
that use values such as `00' or `99' as "missing" value indicators.
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>
| 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 example shows that the `DATE' and `DATETIME' datatypes will not
give any problems with future dates (they handle dates until the year
9999).
The `TIMESTAMP' datatype, which is used to store the current time,
supports values that range from `19700101000000' to `20300101000000' on
32-bit machines (signed value). On 64-bit machines, `TIMESTAMP'
handles values up to `2106' (unsigned value).
Even though `MySQL Server' is Y2K-compliant, it is your responsibility
to provide unambiguous input. See *Note Y2K issues:: for `MySQL
Server''s rules for dealing with ambiguous date input data (data
containing 2-digit year values).
[Назад] [Содержание] [Вперед]
| Главная |