MySQL
|
|
MySQL is a multithreaded, multi-user, SQL (Structured Query Language) relational database server (RDBMS). MySQL is open source software available either under the GNU General Public License (GPL) or under other licenses when the GPL is inapplicable to the intended use.
Unlike projects such as Apache, MySQL is owned and sponsored by a single for-profit firm, the Swedish company MySQL AB. The company develops and maintains the system, selling support and service contracts, as well as commercially-licensed copies of MySQL, and employing people all over the world who work together via the Internet. Two Swedes and a Finn founded MySQL AB: David Axmark, Allan Larsson and Michael "Monty" Widenius.
| Contents |
Pronunciation
Despite the widespread pronunciation of "SQL" as the word "sequel," MySQL AB recommends that "MySQL" be pronounced as (IPA for "My S-Q-L"), not (IPA for "My Sequel") [1] (http://dev.mysql.com/doc/mysql/en/What-is.html#IDX25). This may have been to avoid confusion with the earlier database language SEQUEL which was a predecessor of SQL. [2] (http://www.faqs.org/docs/ppbook/c1164.htm)
Programming languages
Programming languages which can access MySQL databases include: C, C++, C#, Eiffel, Smalltalk, Java (with a native Java driver implementation), Lisp, Perl, PHP, Python, Ruby and Tcl; each of these uses a specific API. An ODBC interface called MyODBC allows additional programming languages that support the ODBC interface to communicate with a MySQL database. MySQL uses ANSI C as its "native" language.
Uses
MySQL is popular for web applications like Wikipedia and PHP-Nuke and acts as the database component of LAMP. The popularity as a web application is closely tied to the popularity of PHP, which is often combined with MySQL and nicknamed the Dynamic Duo. It is easy to find many references that combine the two from books (PHP and MySQL for Dummies, PHP and MySQL Bible, Beginning PHP and MySQL, etc.) to websites. The implication in many books is that MySQL is easier to learn and use than many other database applications. An example from the Dummies book is that you can quit MySQL with an obvious command of either exit or quit.
Administration
To administer MySQL databases you can use the included command-line tool (commands: mysql and mysqladmin). Also downloadable from the MySQL site are GUI administration tools: MySQL Administrator (http://www.mysql.com/products/administrator) and MySQL Query Browser (http://www.mysql.com/products/query-browser).
A widespread and popular alternative, written in PHP, is the open source web application phpMyAdmin.
phpMyBackupPro, also written in PHP, can create and manage backups. It can create pseudo-cronjobs, which can be used (optionally combined with emails) to backup the MySQL database at fixed intervals.
Program specifications
Platforms
MySQL works on many different platforms—including AIX, BSDi, FreeBSD, HP-UX, Linux, Mac OS X, NetBSD, Netware, OpenBSD, OS/2 Warp, QNX, SGI IRIX, Solaris, SunOS, SCO OpenServer, SCO UnixWare, Tru64, Windows 95, Windows 98, Windows NT, Windows 2000, Windows XP and more recent versions of Windows. A port of MySQL to OpenVMS is available here (http://www.pi-net.dyndns.org/anonymous/kits/).
The latest production version
As of 2005, MySQL offers production version 4.1.12. It includes the following features:
- A broad subset of ANSI SQL 99, as well as extensions
- Cross-platform support
- Independent storage engines (MyISAM for read speed, InnoDB for transactions and referential integrity)
- Transactions, including savepoints
- SSL support
- Query caching
- Sub-SELECTs (or nested SELECTs)
- Replication with one master per slave, many slaves per master
- Full-text indexing and searching using MyISAM engine
- Embedded database library
- Full Unicode support
- ACID compliance using InnoDB engine
- Shared-nothing clustering through MySQL Cluster
It does not include the following features:
- Stored procedures
- Triggers
- Cursors
- Views
- Multi-master replication
Future releases
MySQL 5.0, which is currently in a beta release, contains support for the following features:
- Stored procedures
- Views
- Cursors
- True VARCHAR support
- Triggers
- INFORMATION_SCHEMA
- Strict mode
- Better optimizer
MySQL 5.1 will most likely contain support for:
- Online backup for all storage engines
- Foreign key support for all storage engines
- Fail-safe replication
- Column-level constraints
Criticisms of MySQL
Early versions of MySQL included few standard RDBMS features, and the current production version still lacks many properties found in other SQL RDBMSs. This has led some database experts, such as Chris Date and Fabian Pascal, to criticize MySQL as falling short of being an RDBMS.
Many of the early criticisms have been remedied in later versions of the software, including the lack of transactions and relational integrity constraints. These are features necessary to support the "ACID properties" for relational databases, which allow the DBMS to ensure that client applications cannot interfere with one another or insert inconsistent values. [3] (http://www.service-architecture.com/database/articles/acid_properties.html) Other criticisms include MySQL's divergence from the SQL standard on the subject of treatment of NULL values and default values. Its handling of dates allowed invalid dates to be stored, and arithmetic operations are vulnerable to either integer overflow or floating point truncation. [4] (http://sql-info.de/mysql/gotchas.html)
Earlier versions of the MySQL manual included claims that certain essential missing features were useless or even harmful, and that users were better off without them. One section, entitled "Reasons NOT to use Foreign Keys constraints," (sic) advised users that relational-integrity checking was difficult to use and complicated a database application, and that its only useful purpose was to allow client software to diagram the relationships between database tables. [5] (http://sunsite.univie.ac.at/textbooks/mysql/manual.html#Broken_Foreign_KEY) Another section claimed that a DBMS lacking transactions can provide as reliable of data-integrity assurances as one supporting them—conflating the issue of transactional integrity with that of saving data when the database server loses power. [6] (http://sunsite.univie.ac.at/textbooks/mysql/manual.html#IDX340) Since these claims contradicted basic principles of relational database design, they caused MySQL to be ridiculed by some database experts. Right or wrong, these claims have since been removed in more recent versions of the manual. MySQL today supports the previously-dismissed features of relational integrity checking and transactions.
When the beta version of MySQL 5.0 was released in March 2005, David Axmark, a co-founder of MySQL, said that "People have been criticising MySQL since we started for not having stored procedures, triggers and views," and "We're fixing 10 years of criticism in one release." [7] (http://news.zdnet.co.uk/software/applications/0,39020384,39192964,00.htm)
Critics find MySQL's popularity surprising in the light of the existence of other open source database projects with comparable performance and in closer compliance to the SQL standards. MySQL advocates reply that the program serves its purpose for its users, who are willing to accept the program's limitations (which decrease with every major revision) in exchange for speed, simplicity and rapid development. Another, perhaps simpler, explanation for MySQL's popularity is that it's often included as a part of low-cost commercial hosting plans, so for application developers (mostly using PHP and Perl) MySQL is the only DBMS choice unless they want to host their own sites.
Some users have also criticized MySQL AB's position on the licensing of the software. A few open source projects, such as Asterisk PBX, have reduced support for MySQL because of licensing difficulties. Further, there was some controversy generated regarding the distribution of MySQL library files with other open source applications (the biggest controversy arising with PHP). This was later resolved when MySQL made an exception for inclusion in open source projects.
Licensing
Both the MySQL server software itself and the client libraries are distributed under a dual-licensing format. Users may choose GNU General Public License [8] (http://www.mysql.com/products/licensing/opensource-license.html), or they may choose a commercial license.
Some users have independently continued to develop an earlier version of the client libraries, which was distributed under the less-restrictive Lesser General Public License. [9] (http://packages.debian.org/testing/libs/libmysqlclient10)
Trivia
- The dolphin symbol in the MySQL logo was given the Swazi name Sakila in October 2002 after a naming contest. [10] (http://www.mysql.com/news-and-events/news/article_116.html)
See also
- List of relational database management systems
- Comparison of relational database management systems
- MySQL Cluster
- ODBC
- UnixODBC
External links
- MySQL Website (http://www.mysql.com)
- fabFORCE.net DBDesigner 4 (http://www.fabforce.net/dbdesigner4)
- MySQL for OpenVMS (http://www.pi-net.dyndns.org/anonymous/kits/)
- MySQL from/to Access (http://www.ems-hitech.com/mysqlutils#datapump)
- MySQL Gotchas (http://sql-info.de/mysql/gotchas.html) — Description of many features in MySQL, which work as advertised, but not as expected
- MySQL Manager (http://www.ems-hitech.com/mymanager)
- phpMyAdmin (http://www.phpMyAdmin.net)
- phpMyBackupPro (http://www.phpMyBackupPro.net) — database backup tool
- MySql Manual on UnixCities.Com (http://www.unixcities.com/mysql/manual_toc.html)
- MySQL Reference Guide (http://www.contrib.andrew.cmu.edu/~shadow/sql.html)
- MySQL roadmap (http://www.databasejournal.com/features/mysql/article.php/2210771)
- MySQL Webmonkey tutorial (http://hotwired.lycos.com/webmonkey/programming/php/tutorials/tutorial4.html)
- Open Source Databases: As The Tables Turn (http://www.phpbuilder.com/columns/tim20001112.php3) by Tim Perdue
- PHP MySQL Tutorial (http://www.php-mysql-tutorial.com) — A tutorial that explains using MySQL and PHP plus examples like creating a guestbook, contact form, uploading files to database, creating a simple Content Management System (CMS), etc.
- PostgreSQL vs MySQL: Which is better? (http://www.databasejournal.com/features/mysql/article.php/3288951) by Ian Gilfillan
ca:MySQL cs:MySQL da:MySQL de:MySQL es:MySQL eo:MySQL fr:MySQL ko:MySQL it:MySQL he:MySQL ku:MySQL nl:MySQL no:MySQL ja:MySQL pl:MySQL ru:MySQL fi:MySQL pt:MySQL sk:MySQL sv:MySQL tr:MySQL uk:MySQL zh:MySQL