The world's most popular open source database
Trudy Pelzer is a senior software architect with MySQL AB, co-author of SQL Performance Tuning, and lives in Edmonton, Alberta.
By Trudy Pelzer
This book is for the long-time MySQL user who wants to know "what's new" in version 5. The short answer is "stored procedures, triggers, views, and information schema". The long answer is the MySQL 5.0 New Features series, and this book is the last in that series.
Whenever I want to show actual code, such as something that comes directly from the screen of my mysql client program, I switch to a Courier font, which looks different from the regular text font. For example:
mysql> CREATE TABLE table1 (column1 INT); Query OK, 0 rows affected (0.00 sec)
When the example is large and I want to draw attention to a particular line or phrase, I highlight it with a double underline and a small arrow on the right of the page. For example:
mysql> CREATE VIEW v AS -> SELECT column1 AS c /* view col name is c */ -> FROM table1; Query OK, 0 rows affected (0.01 sec)
Sometimes I will leave out the mysql> and -> prompts so that you can cut the examples and paste them into your copy of the mysql client program. (If you aren't reading the text of this book in a machine-readable form, try looking for the script on the mysql.com web site.)
All of the examples in this book were tested with the publicly-available alpha version of MySQL 5.0.3 on the SuSE Linux operating system (version 9.1). By the time you read this, the version number will be higher and the available operating systems will include Windows, Sparc, and HP-UX. So I'm confident that you'll be able to run every example on your own computer. But if not, well, as an experienced MySQL user you know that help and support is always available.
Standard SQL (SQL:2003) provides a method of accessing database metadata through a schema called INFORMATION_SCHEMA. Until now, MySQL has provided metadata only through a series of SHOW commands. SHOW, however, has two disadvantages:
SHOWcommands are non-standard; they are specific to MySQL.
SHOWcommands require that you learn an entire set of commands to be able to access the metadata you need.
INFORMATION_SCHEMAis standard SQL; thus alleviating some problems that may occur in porting applications from one DBMS to another. For example, Microsoft SQL Server also supports
INFORMATION_SCHEMA, while IBM DB2 supports a similar structure, albeit with different names.
INFORMATION_SCHEMAcan be queried via a
SELECTstatement, just as regular tables can be queried; thus there is no need to learn a new set of commands to be able to access the metadata you need.
So MySQL AB made the decision to implement support for the
INFORMATION_SCHEMA. Effective with MySQL 5.0.2, your MySQL installation will automatically contain a schema (usually called a database in MySQL parlance) called
INFORMATION_SCHEMA; it contains a set of views that allow you to look at (but not change) the description of your database objects just as if the descriptions are regular SQL data. Here is an example:
mysql> SELECT table_name, table_type, engine -> FROM INFORMATION_SCHEMA.tables -> WHERE table_schema = 'tp' -> ORDER BY table_type ASC, table_name DESC; +------------+------------+--------+ | table_name | table_type | engine | +------------+------------+--------+ | t2 | BASE TABLE | MyISAM | | t1 | BASE TABLE | InnoDB | | v1 | VIEW | NULL | +------------+------------+--------+
Metadata refers to data about the data. For example, the name of a table and the data type of a column is metadata. There are two other terms that are often used as synonyms for metadata:
I won't be using those terms in this book.
MySQL now has a new "database" named
INFORMATION_SCHEMA. It is a virtual database only; there will never be a need to create a file by that name, and the MySQL server itself creates and populates the tables therein. It is not possible to
USE INFORMATION_SCHEMA; nor is it possible to
UPDATE, INSERT, DELETE, or even
INFORMATION_SCHEMA tables. The only action possible is
INFORMATION_SCHEMA tables does not require a special privilege: the
SELECT privilege on each table is automatically granted to every user.
Thus, there is no difference between the current (
SHOW) privilege requirement and the
SELECT requirement. In either case, you have to have some privilege on an object in order to see the metadata information about that object.
The whole article is long, so we thought it better to make it a PDF. To download, click here (no registration required!) You could even print out the PDF so you can peruse at your leisure.