About the Author

Trudy Pelzer is a senior software architect with MySQL AB, co-author of SQL Performance Tuning, and lives in Edmonton, Alberta.

We are always looking for interesting articles about MySQL! Have you written something and would like to it published here? Please contact us via feedback form »

MySQL 5.0 New Features: Data Dictionary

By Trudy Pelzer

Introduction

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.

Conventions and Styles

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.

A Definition and an Example

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:

SHOW commands are non-standard; they are specific to MySQL.
SHOW commands require that you learn an entire set of commands to be able to access the metadata you need.

In contrast:

Use of INFORMATION_SCHEMA is 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.
The tables in INFORMATION_SCHEMA can be queried via a SELECT statement, 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   |
+------------+------------+--------+

Terminology Notes

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:

Data dictionary
System catalog

I won't be using those terms in this book.

Using INFORMATION_SCHEMA

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 REFERENCE the INFORMATION_SCHEMA tables. The only action possible is SELECT.

Privileges

Accessing the 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.

Read On

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.