dcsimg

MySQL 5: information_schema and Strict Mode

Understand and control your data with information_schema and strict mode in MySQL 5.

December 2005’s feature story on MySQL 5 established the foundation of a stock tracking application using stored procedures, views, and triggers (available online at http://www.linux-mag.com/2005-12/mysql.html.) The February 2006 issue of Linux Magazine presented MySQL 5’s stored functions and cursors (see http://www.linux-mag.com/2006-02/mysql.html.) If you didn’t retain the database constructed in those two articles, use Listing One to (re) create the database and then follow along.[ You can download the SQL scripts and code used throughout this article from http://www.linux-mag.com/downloads/2006-06/source.tgz.]

LISTING ONE: A database schema and data for stock tracking

DROP DATABASE IF EXISTS stock_app;
CREATE DATABASE stock_app;
USE stock_app;

CREATE TABLE IF NOT EXISTS Stock (
stock_symbol CHAR(5) NOT NULL
, name VARCHAR(30) NOT NULL
, PRIMARY KEY pk_stock (stock_symbol)
);

CREATE TABLE IF NOT EXISTS StockPriceHistory (
stock CHAR(5) NOT NULL
, time_taken DATETIME NOT NULL
, price DECIMAL(9,4) NOT NULL
, PRIMARY KEY pk_stock_price_history (stock, time_taken)
);

CREATE TABLE IF NOT EXISTS Watchlist (
watchlist_id SMALLINT UNSIGNED NOT NULL
, name VARCHAR(30) NOT NULL
, PRIMARY KEY pk_watchlist (watchlist_id)
);

CREATE TABLE IF NOT EXISTS WatchlistStock (
watchlist SMALLINT UNSIGNED NOT NULL
, stock CHAR(5) NOT NULL
, quantity_owned INT UNSIGNED NOT NULL
, PRIMARY KEY pk_watchlist_stock (watchlist, stock)
);

DELIMITER //

CREATE PROCEDURE stock_app.InsertStockPrice (
IN symbol CHAR(5)
, IN current_price DECIMAL(9,4)
) DETERMINISTIC
BEGIN
INSERT INTO StockPriceHistory (stock, price, time_taken)
VALUES (symbol, current_price, NOW());
END //

DELIMITER ;

INSERT INTO Stock (stock_symbol, name)
VALUES (’RHAT’,’Red Hat Inc.’)
, (’DELL’,’Dell Inc.’)
, (’AMD’,’Advanced Micro Devices’)
, (’INTC’,’Intel Corporation’)
, (’MSFT’,’Microsoft Corporation’);

CALL InsertStockPrice(’RHAT’,34.05);
CALL InsertStockPrice(’DELL’,56.65);
CALL InsertStockPrice(’AMD’,23.75);
CALL InsertStockPrice(’INTC’,45.63);
CALL InsertStockPrice(’MSFT’,33.87);

INSERT INTO WatchlistStock (watchlist, stock, quantity_owned)
SELECT 1, stock_symbol, 50.00
FROM Stock;
INSERT INTO WatchlistStock (watchlist, stock, quantity_owned)
VALUES (2, ’AMD’, 300.00)
, (2, ’INTC’, 450.00)
, (2, ’RHAT’, 50.00);

Fetching Data About Your Data

With the release of MySQL 5, you can now retrieve a wealth of information about your database using the information_schema, a virtual database filled with metadata, or data about your data. (If you have experience with another database system, you may be familiar with the concept of metadata, albeit by a different name, such as” data dictionary” or” system catalog.” Both data dictionary and system catalog are synonymous with information_schema.)

information_schema is virtual, because its data isn’t actually stored anywhere on disk. Much like a database view, information_schema points to information contained elsewhere, both in the MySQL server and in other databases.

If you’re well-versed in MySQL, you may be wondering why you should go to the trouble of learning and using information_schema when you already have a perfectly good set of SHOW commands that retrieve information about your database and tables. While information_schema is like SHOW, it’s much more powerful.

*information_schema is accessed using standard SELECT statements, allowing you to choose specific columns of data, use WHERE clauses, and JOIN multiple metadata tables. Combining metadata tables with the flexibility of SELECT means unlimited output possibilities.

*Inside information_schema, you’ll find structure, privilege, statistics, and usage information about each MySQL database.

*information_schema is in the SQL:2003 standard, which defines how to retrieve metadata from a database.

Using standard SQL to get metadata means you aren’t required to learn and remember the syntax and options of SHOW. Moreover, a standards-based solution can be applied across many database systems.

All metadata is stored in tables in the information_schema database. For example, Figure One demonstrates how to find the names of all your MySQL databases.

FIGURE ONE: The information_schema table SCHEMATA contains the list of available databases

mysql> use information_schema;
Database changed
mysql> SELECT SCHEMA_NAME FROM SCHEMATA;
+——————–+
| SCHEMA_NAME |
+——————–+
| information_schema |
| mysql |
| test |
| stock_app |
+——————–+
4 rows in set (0.01 sec)

By default MySQL 5 comes with three pre-installed databases, or SCHEMATA: information_schema, mysql, and test. Assuming you created stock_app using Listing One, the command SELECT SCHEMA_NAME FROM SCHEMATA lists four databases. As with SHOW, MySQL limits what metadata is shown based on your privileges. If you do not have the rights to see a particular database or table, its information won’t appear in your query results.

To see what kind of information is available about your databases and tables, look at all of the tables available in information_schema. Figure Two shows how it’s done in MySQL 5.

FIGURE TWO: Tables in the information_schema database

mysql> SELECT TABLE_SCHEMA, TABLE_NAME
-> FROM TABLES
-> WHERE TABLE_SCHEMA=’information_schema’;
+——————–+—————————————+
| TABLE_SCHEMA | TABLE_NAME |
+——————–+—————————————+
| information_schema | CHARACTER_SETS |
| information_schema | COLLATIONS |
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY |
| information_schema | COLUMNS |
| information_schema | COLUMN_PRIVILEGES |
| information_schema | KEY_COLUMN_USAGE |
| information_schema | ROUTINES |
| information_schema | SCHEMATA |
| information_schema | SCHEMA_PRIVILEGES |
| information_schema | STATISTICS |
| information_schema | TABLES |
| information_schema | TABLE_CONSTRAINTS |
| information_schema | TABLE_PRIVILEGES |
| information_schema | TRIGGERS |
| information_schema | USER_PRIVILEGES |
| information_schema | VIEWS |
+——————–+—————————————+
16 rows in set (0.05 sec)

Each table has a unique set of fields, providing a wide array of information about your database. Table One describes the purpose of each table.

TABLE ONE: The purpose of the information_schema tables

Table name Contents
CHARACTER_SETS Details about the available character sets
COLLATIONS Information about the available collations
COLLATION_CHARACTER_SET_APPLICABILITY Relationships between collations and character sets
COLUMNS Cross-database information about table columns
COLUMN_PRIVILEGES Column-level privilege information from the mysql.columns table
KEY_COLUMN_USAGE Information about foreign key usage in tables
ROUTINES Details regarding stored procedures and functions
SCHEMATA Specifics about databases in this instance of MySQL
SCHEMA_PRIVILEGES Database-level privilege information from the mysql.db table
STATISTICS Information about indexes
TABLES Cross-database information about tables
TABLE_CONSTRAINTS Information about key usage in tables
TABLE_PRIVILEGES Table-level privilege information from the mysql.tables_priv table
TRIGGERS Cross-database specifics about triggers
USER_PRIVILEGES User privileges from the mysql.user table
VIEWS Cross-database specifics about views

For any of the tables listed in Table One, DESCRIBE lists information about the columns stored within that virtual table. For example, Figure Three shows detailed information about the TABLES table.

FIGURE THREE: Description of the TABLES table in information_schema

mysql> DESCRIBE TABLES;
+—————–+————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+—————–+————–+——+—–+———+——-+
| TABLE_CATALOG | varchar(512) | YES | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES | | | |
| VERSION | bigint(21) | YES | | | |
| ROW_FORMAT | varchar(10) | YES | | | |
| TABLE_ROWS | bigint(21) | YES | | | |
| AVG_ROW_LENGTH | bigint(21) | YES | | | |
| DATA_LENGTH | bigint(21) | YES | | | |
| MAX_DATA_LENGTH | bigint(21) | YES | | | |
| INDEX_LENGTH | bigint(21) | YES | | | |
| DATA_FREE | bigint(21) | YES | | | |
| AUTO_INCREMENT | bigint(21) | YES | | | |
| CREATE_TIME | datetime | YES | | | |
| UPDATE_TIME | datetime | YES | | | |
| CHECK_TIME | datetime | YES | | | |
| TABLE_COLLATION | varchar(64) | YES | | | |
| CHECKSUM | bigint(21) | YES | | | |
| CREATE_OPTIONS | varchar(255) | YES | | | |
| TABLE_COMMENT | varchar(80) | NO | | | |
+—————–+————–+——+—–+———+——-+
21 rows in set (0.02 sec)

As you can see, a lot of information is available. Any of these columns can be put into a SELECT statement. For instance, suppose you want to see all the columns used in the stock_app database, across all of its tables. Moreover, you would like to see the average record length and the storage engine used for each table to help determine if the database is tuned approriately. You can easily get this information with a join of the TABLES and COLUMNS tables, as shown in Figure Four.

FIGURE FOUR: Joining multiple information_schema tables

mysql> SELECT C.TABLE_NAME, C.COLUMN_NAME, C.COLUMN_TYPE,
-> T.ENGINE, T.AVG_ROW_LENGTH
-> FROM TABLES T, COLUMNS C
-> WHERE T.TABLE_SCHEMA = C.TABLE_SCHEMA
-> AND T.TABLE_NAME = C.TABLE_NAME
-> AND C.TABLE_SCHEMA=’stock_app’;
+——————-+—————-+———————-+——–+—————-+
| TABLE_NAME | COLUMN_NAME | COLUMN_TYPE |ENGINE | AVG_ROW_LENGTH |
+——————-+—————-+———————-+——–+—————-+
| Stock | stock_symbol | char(5) | MyISAM | 27 |
| Stock | name | varchar(30) | MyISAM | 27 |
| StockPriceHistory | stock | char(5) | MyISAM | 19 |
| StockPriceHistory | time_taken | datetime | MyISAM | 19 |
| StockPriceHistory | price | decimal(9,4) | MyISAM | 19 |
| Watchlist | watchlist_id | smallint(5) unsigned | MyISAM | 0 |
| Watchlist | name | varchar(30) | MyISAM | 0 |
| WatchlistStock | watchlist | smallint(5) unsigned | MyISAM | 12 |
| WatchlistStock | stock | char(5) | MyISAM | 12 |
| WatchlistStock | quantity_owned | int(10) unsigned | MyISAM | 12 |
+——————-+—————-+———————-+——–+—————-+
10 rows in set (0.15 sec)

Joining information across multiple tables gives you limitless flexibility in building reports and customizing the output for your particular needs.

The information_schema is exciting, but may be dauting for for folks habituated to using SHOW commands. To jump start usage of information_schema, refer to Table Two for a list of common SHOW commands and the equivalent SQL statements using information_schema.

TABLE TWO: SHOW commands with equivalent information_schema queries

SHOW command information_schema query
SHOW DATABASES SELECT SCHEMA_NAME FROM SCHEMATA
SHOW TABLES SELECT TABLE_NAME FROM TABLES or SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA= ’& lt;database& gt;’
SHOW INDEX FROM& lt;table& gt; SELECT* FROM STATISTICS WHERE TABLE_NAME= ’& lt;table& gt;’
SHOW CHARACTER SET SELECT* FROM information_schema. CHARACTER_SETS
SHOW COLUMNS FROM& lt;table& gt; SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM COLUMNS WHERE TABLE_NAME= ’& lt;table& gt;’

The information_schema virtual database provides a rich set of metadata that’s easy to access with standard SQL.