MySQL Administrator’s Bible- P15

MySQL Administrator’s Bible- P15

MySQL Administrator’s Bible- P15: Welcome to the MySQL Administrator’s Bible. Like all books in the Bible series, you
can expect to find both hands-on tutorials and real-world practical application information,
as well as reference and background information that provide a context for
what you are learning.

Nội dung Text: MySQL Administrator’s Bible- P15

The MySQL Data
Dictionary
M
ySQL stores information about the data in the databases; this
is called metadata. Much of this information is stored in the IN THIS CHAPTER
INFORMATION_SCHEMA database, following the SQL 2003
standard. Examining the Object catalog
Tables in the INFORMATION_SCHEMA database are read-only, in-memory, System information
and show data from various sources. Managing permissions
SHOW CREATE TABLE will show the tables as TEMPORARY, because
Developing custom metadata
they reside in memory and do not persist between mysqld restarts.
INFORMATION_SCHEMA tables are called system views and they may be of
different storage engine types. At the time of this writing all the system
views are either the MEMORY, MyISAM, or Maria storage engine.
Regular SQL statements can be used to query them, though they have
some special properties that other views do not have:
■ mysqldump will not export any information (data, schema) from
INFORMATION_SCHEMA system views
■ There is no data directory for the INFORMATION_SCHEMA database
■ There is no .frm ﬁle associated with the INFORMATION_SCHEMA
views. The deﬁnitions are hard-coded into the database.
The table deﬁnitions for the data dictionary are hard-coded into the source
code, and loaded when mysqld starts. Unlike other databases, there is
no directory in the datadir for the INFORMATION_SCHEMA database.
All users have permission to see the INFORMATION_SCHEMA database;
however, they can only see the objects they have permission to see. For
example, table details in the TABLES system view are limited to the tables
that the user has permission to see.
667

Part IV Extending Your Skills
Some of the metadata provided by the INFORMATION_SCHEMA database is also provided by
various SHOW commands (see Chapter 4). The INFORMATION_SCHEMA database is a more
complete data dictionary than using SHOW commands. Also, standard SQL statements can be
used to query the system views to retrieve metadata.
The INFORMATION_SCHEMA database contains more than 40 system views. They can be infor-
mally categorized as:
■ Object catalog (databases, tables, columns, and so on)
■ System information (variables, statistics, available options)
■ Permissions
■ Storage engine-speciﬁc metadata
Querying metadata is a powerful tool for a database administrator, answering simple questions
such as ‘‘how many tables of each storage engine type exist?’’ and ‘‘which tables have columns
using the DECIMAL type?’’ and ‘‘how many foreign key constraints exist?’’ and even ‘‘how much
space does a certain group of three tables use?’’ Querying metadata provides a way to retrieve
information about the system that can be used to track and tune performance. Any tool that per-
forms queries can retrieve metadata by querying the INFORMATION_SCHEMA database, exactly
the same way it queries any other database.
Object Catalog
The INFORMATION_SCHEMA database contains system views with metadata about objects such as
databases, tables, views, columns, indexes, partitions, stored routines, triggers, and events.
SCHEMATA
‘‘Schema’’ is another name for a database, and ‘‘schemata’’ is the plural of schema. The
SCHEMATA system view in the INFORMATION_SCHEMA database provides information about all
the databases, including the mysql system database and the INFORMATION_SCHEMA database
itself. The ﬁelds in the SCHEMATA system view are:
■ CATALOG_NAME — Provided for standards compliance. However, because MySQL does
not have catalogs, this value is always NULL.
■ SCHEMA_NAME — The name of the database, such as sakila.
■ DEFAULT_CHARACTER_SET_NAME — The default character set of the database. If no
default character set is assigned by a CREATE DATABASE or ALTER DATABASE command,
the default character set for the system is stored. Thus, the DEFAULT_CHARACTER_SET_
NAME ﬁeld always has a non-NULL value, and defaults to the character set of the system at
the time of database creation.
668

The MySQL Data Dictionary 21
■ DEFAULT_COLLATION_NAME — The default collation of the database. If no default
collation is assigned by a CREATE DATABASE or ALTER DATABASE command, the default
collation for the system is stored. Thus, the DEFAULT_CHARACTER_SET_NAME ﬁeld
always has a non-NULL value, and defaults to the collation of the system at the time of
database creation.
■ SQL_PATH — Provided for standards compliance, this ﬁeld is usually used to ﬁnd ﬁles
related to the database. However, MySQL does not support this ﬁeld, so it is always NULL.
The SHOW DATABASES command is a shorter way to ﬁnd the names of existing databases than
running SELECT SCHEMA_NAME FROM SCHEMATA. To show a subset of all databases, it is easier
to use the SCHEMATA system view. The SHOW DATABASES command returns a result set where
the ﬁeld name is Database. Because Database is a reserved word, in order to use the WHERE
extension to SHOW DATABASES, the Database ﬁeld must be quoted:
mysql> SHOW DATABASES WHERE Database NOT IN (’mysql’,’information_
schema’);
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near ’NOT IN (’mysql’,’information_schema’)’ at line 1
mysql> SHOW DATABASES WHERE `Database` NOT IN (’mysql’,’information_
schema’);
+----------+
| Database |
+----------+
| sakila |
| test |
+----------+
2 rows in set (0.02 sec)
See Chapter 4 for more information about using the backtick (`) to quote identiﬁers.
The ﬁeld name in the SCHEMATA system view is SCHEMA_NAME, which is not a reserved word,
and does not need to be escaped:
mysql> USE INFORMATION_SCHEMA;
Database changed
mysql> SELECT SCHEMA_NAME FROM SCHEMATA WHERE SCHEMA_NAME
NOT IN (’mysql’,’information_schema’);
+-------------+
| SCHEMA_NAME |
+-------------+
| sakila |
| test |
+-------------+
2 rows in set (0.02 sec)
The SHOW DATABASES command also accepts the LIKE extension. The SHOW SCHEMAS
command behaves the same way as the SHOW DATABASES command, and outputs the same
information — SCHEMAS is an alias for DATABASES.
669

Part IV Extending Your Skills
The SHOW CREATE DATABASE command returns two ﬁelds: Database, which is equivalent to
SCHEMA_NAME, and Create Database, which does not have an exact equivalent but contains
the value of DEFAULT_CHARACTER_SET_NAME. SCHEMA is an alias for DATABASE, so SHOW
CREATE SCHEMA returns the same information as SHOW CREATE DATABASE does.
TABLES
Metadata about non-temporary tables is available in the TABLES system view. The ﬁelds in the
TABLES system view are:
■ TABLE_CATALOG — Provided for standards compliance. However, because MySQL does
not have catalogs, this value is always NULL.
■ TABLE_SCHEMA — The name of the database, such as sakila.
■ TABLE_NAME — The name of the table.
■ TABLE_TYPE — Whether the table is a base table, view, or system view. Only the INFOR-
MATION_SCHEMA views are system views.
■ ENGINE — The storage engine of the table, such as InnoDB. To ﬁnd out which tables, if
any, in the INFORMATION_SCHEMA database do not use the MEMORY storage engine:
mysql> SELECT TABLE_NAME,ENGINE FROM TABLES WHERE TABLE_
SCHEMA=’INFORMATION_SCHEMA’ AND ENGINE!=’MEMORY’;
+-------------+--------+
| TABLE_NAME | ENGINE |
+-------------+--------+
| COLUMNS | MARIA |
| EVENTS | MARIA |
| PARAMETERS | MARIA |
| PARTITIONS | MARIA |
| PLUGINS | MARIA |
| PROCESSLIST | MARIA |
| ROUTINES | MARIA |
| TRIGGERS | MARIA |
| VIEWS | MARIA |
+-------------+--------+
9 rows in set (0.56 sec)
■ From this query we see that most system views in the INFORMATION_SCHEMA database
are the MEMORY storage engine, but there are some that use the Maria storage engine.
■ VERSION — The version of the .frm ﬁle, currently 10. The VERSION is NULL for table
objects that do not have .frm ﬁles, such as views. The exception to this rule is system
views, which have a VERSION of 10 — even though there are no .frm ﬁles, system views
have hard-coded deﬁnitions, and thus have versions.
■ ROW_FORMAT — Different storage engines allow the row storage to vary. Fixed-width
rows are a ﬁxed size, which minimizes fragmentation. Dynamic rows are a variable size,
which are good for variable-length data, such as VARCHAR, TEXT, and BLOB. InnoDB has
670

The MySQL Data Dictionary 21
a compact row format by default, which eliminates some redundant data. When an Inn-
oDB table has a redundant format, there is less CPU work needed at the cost of additional
storage space. MyISAM has a compressed format, obtained by packing the data with the
myisampack tool. See Chapter 11 for more details on myisampack.
■ TABLE_ROWS — The number of rows this table contains. This value may be an estimate,
depending on the storage engine. The value is NULL for views and system views
(INFORMATION_SCHEMA tables).
■ AVG_ROW_LENGTH — The average size in bytes of rows this table contains. This value may
be an estimate, depending on the storage engine. The value is NULL for views, but has a
value for system views. If there are no rows, the value will be 0.
■ DATA_LENGTH — The size in bytes of rows this table contains. This value may be an esti-
mate, depending on the storage engine. The value is NULL for views and 0 for MEMORY
tables. System views that are not MEMORY tables have a value for DATA_LENGTH.
■ MAX_DATA_LENGTH — The maximum size in bytes that this table may contain. The value
is NULL for views, because there is no data stored. The value is 0 for storage engines that
do not populate this ﬁeld, such as Falcon and CSV.
■ INDEX_LENGTH — The size in bytes of the indexes for this table. This value may be an
estimate, depending on the storage engine. The value is NULL for views and 0 for MEM-
ORY tables. System views that are not MEMORY tables have a value for DATA_LENGTH.
■ DATA_FREE — The size in bytes of the free space allocated for this table, and still avail-
able. This value may be an estimate, depending on the storage engine. The value is NULL
for views and 0 for system views. Many tables have a DATA_FREE value of 0 because there
is not space allocated for them, though there may be plenty of free space available to
them. For example, CSV tables simply use available disk space, without needing MySQL
to allocate space for rows. In some storage engines such as MyISAM, this might indicate
fragmentation and that the table needs to be rebuilt with an OPTIMIZE command. See
Chapter 4 for more information about OPTIMIZE.
■ AUTO_INCREMENT — The next AUTO_INCREMENT value to be used. If the maximum
AUTO_INCREMENT value for a table is 100, the value of AUTO_INCREMENT is 101. If a
table has an AUTO_INCREMENT value and no rows have ever been stored in the table, the
value of AUTO_INCREMENT is 1.
■ CREATE_TIME — The DATETIME the table was created. The value is NULL for views and
MEMORY tables. System views that use storage engines other than MEMORY have a
proper DATETIME value. FEDERATED tables have a value of NULL.
■ UPDATE_TIME — The most recent DATETIME that an ALTER TABLE was performed on
the table. The value is NULL for views, CSV, and MEMORY tables. MyISAM, Archive, and
Maria tables that have never had ALTER TABLE performed on them have an UPDATE_TIME
equivalent to their CREATE_TIME. InnoDB and Falcon tables that have never had ALTER
TABLE performed on them have a NULL value. System views that use storage engines
other than MEMORY have a proper DATETIME value. FEDERATED tables have a value
of NULL.
671

Part IV Extending Your Skills
■ CHECK_TIME — The most recent DATETIME the table was checked with CHECK TABLE.
The value is NULL for views, system views, and tables that have never been checked or do
not support the check function.
■ TABLE_COLLATION — The character set and collation of the table, for example
utf8_bin utf8_general_ci, or latin1_swedish_ci. The value is NULL for views.
If no default character set and collation is assigned by a CREATE TABLE or ALTER TABLE
command, the default character set and collation are stored. Thus, this ﬁeld always has a
non-NULL value for base tables and system views.
■ CHECKSUM — Live checksums can be maintained for MyISAM tables (see Chapter 11). If
this table is a MyISAM table with CHECKSUM=1, the live checksum value is displayed. For
all other tables, the value is NULL.
■ CREATE_OPTIONS — CREATE TABLE has many different options. The options that are not
shown in other ﬁelds (such as TABLE_COLLATION) are shown in this ﬁeld, separated by a
space. Sample values are partitioned and max_rows=10000 checksum=1. If there are
no relevant options to CREATE TABLE, the value is the empty string (’’). The value is NULL
for views.
■ TABLE_COMMENT — The COMMENT option to CREATE TABLE and ALTER TABLE can be
used to provide information about a table. If there was no comment speciﬁed, the value is
the empty string (‘’). The value is VIEW for views. Most of the tables in the mysql system
database have comments:
mysql> SELECT TABLE_NAME, TABLE_COMMENT FROM TABLES WHERE
TABLE_SCHEMA=’mysql’\G
*************************** 1. row ***************************
TABLE_NAME: backup_history
TABLE_COMMENT:
*************************** 2. row ***************************
TABLE_NAME: backup_progress
TABLE_COMMENT:
*************************** 3. row ***************************
TABLE_NAME: columns_priv
TABLE_COMMENT: Column privileges
*************************** 4. row ***************************
TABLE_NAME: db
TABLE_COMMENT: Database privileges
*************************** 5. row ***************************
TABLE_NAME: event
TABLE_COMMENT: Events
*************************** 6. row ***************************
TABLE_NAME: func
TABLE_COMMENT: User defined functions
*************************** 7. row ***************************
TABLE_NAME: general_log
TABLE_COMMENT: General log
*************************** 8. row ***************************
TABLE_NAME: help_category
672

Part IV Extending Your Skills
*************************** 25. row ***************************
TABLE_NAME: user
TABLE_COMMENT: Users and global privileges
25 rows in set (0.00 sec)
There are a few SHOW commands that show table information. SHOW TABLES returns one ﬁeld,
the equivalent of TABLE_NAME. SHOW FULL TABLES adds another ﬁeld, Table_type, which
is the equivalent of TABLE_TYPE.
The SHOW CREATE TABLE command returns two ﬁelds: Table, which is the equivalent of
TABLE_NAME, and Create Table, which is the full CREATE TABLE statement. There is
no equivalent in the TABLES system view, though it includes the information in ENGINE,
AUTO_INCREMENT, CREATE_OPTIONS, and TABLE_COMMENT.
SHOW TABLE STATUS returns many ﬁelds:
■ Name — Equivalent to TABLE_NAME.
■ Engine, Version, and Row_format — Equivalent to ENGINE, VERSION, and
ROW_FORMAT.
■ Rows — Equivalent to TABLE_ROWS.
■ Avg_row_length, Data_length, Max_data_length, Index_length, Data_free,
Auto_increment, Create_time, Update_time, and Check_time — Equivalent to
AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE,
AUTO_INCREMENT, CREATE_TIME, UPDATE_TIME, and CHECK_TIME.
■ Collation — Equivalent to TABLE_COLLATION.
■ Checksum, Create_options — Equivalent to CHECKSUM and CREATE_OPTIONS.
■ Comment — Equivalent to TABLE_COMMENT.
VIEWS
The TABLES system view includes rows for views. However, many of the ﬁelds in TABLES are
NULL for views, and some features speciﬁc to views are not encompassed in the TABLES system
view. So MySQL provides the VIEWS system view, with the following ﬁelds:
■ TABLE_CATALOG — Provided for standards compliance. However, because MySQL does
not have catalogs, this value is always NULL.
■ TABLE_SCHEMA — The name of the database, such as sakila.
■ TABLE_NAME — The name of the view.
■ VIEW_DEFINITION — The SELECT statement that deﬁnes the view. If the current user is
not the deﬁner, the value will be blank, even if the current user has permissions to see the
view deﬁnition:
mysql> SELECT DEFINER,VIEW_DEFINITION,CURRENT_USER()
-> FROM VIEWS
-> WHERE TABLE_NAME=’staff_list’;
674

The MySQL Data Dictionary 21
+----------------+-----------------+----------------+
| DEFINER | VIEW_DEFINITION | CURRENT_USER() |
+----------------+-----------------+----------------+
| root@localhost | | root@127.0.0.1 |
+----------------+-----------------+----------------+
1 row in set (0.03 sec)
mysql> SHOW GRANTS;
+-------------------------------------------------------------+
| Grants for root@127.0.0.1 |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO ’root’@’127.0.0.1’ WITH GRANT|
| OPTION |
+-------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE VIEW sakila.staff_list\G
*************************** 1. row ***************************
View: staff_list
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=root@
localhost SQL SECURITY DEFINER VIEW sakila.staff_list AS select
s.staff_id AS ID,concat(s.first_name,_utf8’ ’,s.last_name) AS name,
a.address AS address,a.postal_code AS zip code,a.phone AS phone,
sakila.city.city AS city,sakila.country.country AS country,s.
store_id AS SID from (((sakila.staff s join sakila.address a
on((s.address_id = a.address_id))) join sakila.city on((a.city_id
= sakila.city.city_id))) join sakila.country on((sakila.city.
country_id = sakila.country.country_id)))
character_set_client: utf8mb3
collation_connection: utf8mb3_general_ci
1 row in set (0.00 sec)
■ In this example, the user root@127.0.0.1 saw a blank view deﬁnition for the
staff_list view, because the DEFINER is root@localhost.
■ CHECK_OPTION — This value is NONE if the view deﬁnition has no WITH CHECK OPTION
clause; CASCADED if the view deﬁnition contains WITH [CASCADED] CHECK OPTION, and
LOCAL if the view deﬁnition contains WITH LOCAL CHECK OPTION.
■ IS_UPDATABLE — YES if the view is updatable, NO if the view is not updatable. See
Chapter 8, subsection ‘‘Updatable Views,’’ for more information on updatable views.
■ DEFINER — The view deﬁner, in the MySQL user@host format.
■ SECURITY_TYPE — DEFINER if the view deﬁnition was speciﬁed with the SQL
SECURITY DEFINER option or did not contain an SQL SECURITY option. The value is
INVOKER if the view deﬁnition was speciﬁed with the SQL SECURITY INVOKER option.
■ CHARACTER_SET_CLIENT — Stores the environmental character set as it was when the
view was created.
675

Part IV Extending Your Skills
■ COLLATION_CONNECTION — Stores the environmental collation as it was when the view
was created.
SHOW CREATE VIEW is the SHOW command that shares the most information with the VIEWS sys-
tem view. The ﬁelds of SHOW CREATE VIEW are:
■ View — Equivalent to TABLE_NAME.
■ Create View — No exact equivalent. This is the full CREATE VIEW statement, and has
elements from TABLE_NAME, VIEW_DEFINITION, CHECK_OPTION, IS_UPDATABLE,
DEFINER, and SECURITY_TYPE.
■ character_set_client — Equivalent to CHARACTER_SET_CLIENT.
■ collation_connection — Equivalent to COLLATION_CONNECTION.
COLUMNS
The COLUMNS system view contains information about table ﬁelds. This system view contains
information about the ﬁelds from every table, view, and system view.
■ TABLE_CATALOG — Provided for standards compliance. However, because MySQL does
not have catalogs, this value is always NULL.
■ TABLE_SCHEMA — The name of the database, such as sakila.
■ TABLE_NAME — The name of the table.
■ COLUMN_NAME — The name of the ﬁeld.
■ ORDINAL_POSITION — The number representing the order of the ﬁeld. The ﬁrst ﬁeld has
a value of 1, the third ﬁeld has a value of 3, and so on. The value is never NULL.
■ COLUMN_DEFAULT — The default value of the ﬁeld. If the default is not speciﬁed or speci-
ﬁed as NULL, the value is NULL.
■ IS_NULLABLE — Whether or not the ﬁeld is allowed to be null. If the ﬁeld is speciﬁed as
NOT NULL, the value is NO. Otherwise, the value is YES. Note that it is possible to have a
table where the value of IS_NULLABLE is NO and the COLUMN_DEFAULT is NULL:
mysql> USE test;
Database changed
mysql> CREATE TABLE paradox (numfield INT NOT NULL);
Query OK, 0 rows affected (0.11 sec)
mysql> SELECT IS_NULLABLE,COLUMN_DEFAULT
-> FROM INFORMATION_SCHEMA.COLUMNS
-> WHERE TABLE_NAME=’paradox’ AND TABLE_SCHEMA=’test’;
+-------------+----------------+
| IS_NULLABLE | COLUMN_DEFAULT |
+-------------+----------------+
| NO | NULL |
+-------------+----------------+
1 row in set (0.00 sec)
676

The MySQL Data Dictionary 21
■ DATA_TYPE — The data type of the ﬁeld, such as INT, CHAR, or ENUM.
■ CHARACTER_MAXIMUM_LENGTH — The maximum number of characters allowed by
the ﬁeld deﬁnition. For example, a ﬁeld deﬁned as VARCHAR(64) has a CHARAC-
TER_MAXIMUM_LENGTH of 64. This is only valid for string data, which includes CHAR,
VARCHAR, TEXT, BLOB, SET, and ENUM types.
■ CHARACTER_OCTET_LENGTH — Maximum octet (byte) size allowed by the ﬁeld deﬁni-
tion. CHARACTER_OCTET_LENGTH is the same as CHARACTER_MAXIMUM_LENGTH except
when multi-byte character sets are used (such as utf8).
■ NUMERIC_PRECISION — Mathematically, precision is the number of digits used to deﬁne
a number, for example 10 for an INT and 5 for a SMALLINT. This is only valid for numeric
data types, including INT, DECIMAL, and FLOAT. For all other data types, the value is
NULL.
■ NUMERIC_SCALE — This is only valid for numeric data types, including INT, DECIMAL,
and FLOAT. For all other data types, the value is NULL. This value is equivalent to the
number of digits after the decimal point, which is 0 for data types that are integers.
■ CHARACTER_SET_NAME — The default character set for this column. This is only valid for
non-binary string data, which includes CHAR, VARCHAR, TEXT, SET, and ENUM types.
■ COLLATION_NAME — The default collation for this column. This is only valid for
non-binary string data, which includes CHAR, VARCHAR, TEXT, SET, and ENUM types.
Sample values are utf8_general_ci, latin1_bin, and latin1_swedish_ci.
■ COLUMN_TYPE — The entire data type part of the column deﬁnition, without any
NULL or DEFAULT deﬁners. For example, varchar(64), bigint(21) unsigned,
enum(’N’,’Y’), or year(4).
■ COLUMN_KEY — If the column is not part of an index, this ﬁeld contains the empty string
(‘’). Otherwise, the value is the type of index the columns is a part of:
■ PRI — Primary key
■ UNI — Unique, non-primary key
■ MUL — Non-unique key
■ EXTRA — This ﬁeld stores extra information about the column that does not have a place
in another ﬁeld. If there is no such extra information, the value is the empty string (‘’).
Example values are on update CURRENT_TIMESTAMP and auto_increment.
■ PRIVILEGES — The privileges the querying user has for use with this column. If the user
has no privileges for a column, there is no row returned for that column. Therefore, the
ﬁeld always contains at least the select privilege. Privileges are separated by a comma,
for example select,insert,update.
■ COLUMN_COMMENT — The COMMENT option to a column deﬁnition within CREATE TABLE
and ALTER TABLE can be used to provide information about a column. If there was no
comment speciﬁed, the value is the empty string (‘’).
677

Part IV Extending Your Skills
■ STORAGE — Indicates whether the column is stored in memory or on disk. These only
apply to columns in NDB tables. The value for all other tables is Default.
■ FORMAT — Indicates whether the column storage format is ﬁxed, dynamic, or default.
Fixed and dynamic storage formats only apply to columns in NDB tables. The value for all
other tables is Default.
The SHOW COLUMNS command is the SHOW command that shares the most information with the
COLUMNS system view. It accepts the LIKE and WHERE extensions, and will show three extra
ﬁelds when the SHOW FULL COLUMNS syntax is used:
mysql> SHOW COLUMNS FROM sakila.staff LIKE ’s%’\G
*************************** 1. row ***************************
Field: staff_id
Type: tinyint(3) unsigned
Null: NO
Key: PRI
Default: NULL
Extra: auto_increment
*************************** 2. row ***************************
Field: store_id
Type: tinyint(3) unsigned
Null: NO
Key: MUL
Default: NULL
Extra:
2 rows in set (0.00 sec)
mysql> SHOW FULL COLUMNS FROM sakila.staff LIKE ’s%’\G
*************************** 1. row ***************************
Field: staff_id
Type: tinyint(3) unsigned
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Extra: auto_increment
Privileges: select,insert,update,references
Comment:
*************************** 2. row ***************************
Field: store_id
Type: tinyint(3) unsigned
Collation: NULL
Null: NO
Key: MUL
Default: NULL
Extra:
678

The MySQL Data Dictionary 21
Privileges: select,insert,update,references
Comment:
2 rows in set (0.00 sec)
The ﬁelds from SHOW COLUMNS and SHOW FULL COLUMNS are:
■ Field — Equivalent to COLUMN_NAME.
■ Type — Equivalent to COLUMN_TYPE.
■ Collation — Equivalent to COLLATION_NAME. SHOW FULL COLUMNS only.
■ Null — Equivalent to IS_NULLABLE.
■ Key — Equivalent to COLUMN_KEY.
■ Default — Equivalent to COLUMN_DEFAULT.
■ Extra — Equivalent to EXTRA.
■ Privileges — Equivalent to PRIVILEGES. SHOW FULL COLUMNS only.
■ Comment — Equivalent to COLUMN_COMMENT. SHOW FULL COLUMNS only.
SHOW COLUMNS returns the ﬁelds in the order in which they appear in the table. To guarantee
that ordering with the COLUMNS system view, add ORDER BY ORDINAL_POSITION to queries.
STATISTICS
Information about indexes is stored in the STATISTICS system view, which has one row for
each ﬁeld in an index. STATISTICS has the following ﬁelds:
■ TABLE_CATALOG — Provided for standards compliance. However, because MySQL does
not have catalogs, this value is always NULL.
■ TABLE_SCHEMA — The name of the database that contains the table that is associated with
this index, such as sakila.
■ TABLE_NAME — The name of the table that is associated with this index.
■ NON_UNIQUE — Whether or not the index is unique. The value is 0 for unique indexes
and 1 for non-unique indexes.
■ INDEX_SCHEMA — The name of the database that contains this index. This is always the
same as TABLE_SCHEMA.
■ INDEX_NAME — The name of the index.
■ SEQ_IN_INDEX — The position of this ﬁeld in the index. The ﬁrst ﬁeld in an index has
a value of 1, the second ﬁeld in an index has a value of 2, and so on. For example, the
sakila.rental table has an index deﬁned as:
UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,
`customer_id`),
679

Part IV Extending Your Skills
This index is represented by three rows in the STATISTICS system view:
mysql> SELECT SEQ_IN_INDEX, COLUMN_NAME
-> FROM STATISTICS
-> WHERE TABLE_SCHEMA=’sakila’
-> AND TABLE_NAME=’rental’
-> AND INDEX_NAME=’rental_date’;
+--------------+--------------+
| SEQ_IN_INDEX | COLUMN_NAME |
+--------------+--------------+
| 1 | rental_date |
| 2 | inventory_id |
| 3 | customer_id |
+--------------+--------------+
3 rows in set (0.00 sec)
■ COLUMN_NAME — The name of the ﬁeld.
■ COLLATION — The collation for this ﬁeld. Currently all records have NULL values. How-
ever, in the future, when mysqld supports ascending and descending collations, the value
will be A for ascending collations and D for descending collations.
■ CARDINALITY — The cardinality of this ﬁeld. The cardinality of a ﬁeld is the number of
unique values in that ﬁeld. This value may be an estimation, depending on the storage
engine of the table. If the cardinality is small compared to the number of rows in the table,
it means that there are many repeating values. This means a query will return multiple
values for a ﬁeld when searching on a value that is indexed, but repeating.
When the ﬁeld cardinality is large, approaching the value of the number of rows, it means
that there are very few repeating values. Fields in a unique index have a cardinality equal
to the number of rows in the table.
■ SUB_PART — The number of characters in the preﬁx of the index for this ﬁeld. If
the index does not contain a preﬁx for this ﬁeld, the value is NULL. For example, the
sakila.film table has the following index deﬁned:
KEY `idx_title` (`title`(191)),
And the value of SUB_PART is 191:
mysql> SELECT INDEX_NAME, COLUMN_NAME, SUB_PART
-> FROM STATISTICS
-> WHERE TABLE_SCHEMA=’sakila’
-> AND TABLE_NAME=’film’
-> AND INDEX_NAME=’idx_title’;
+------------+-------------+----------+
| INDEX_NAME | COLUMN_NAME | SUB_PART |
+------------+-------------+----------+
| idx_title | title | 191 |
+------------+-------------+----------+
1 row in set (0.00 sec)
680

The MySQL Data Dictionary 21
■ PACKED — Whether or not the index is packed. This only applies to MyISAM tables. If
the index is not packed, the value is NULL. If the index is packed, the value is 0 (nothing
packed), 1 (strings and integers are packed), or DEFAULT (only strings are packed).
■ NULLABLE — Whether or not the ﬁeld can contain NULL values. If the ﬁeld can contain
NULL values, the value is YES. If the ﬁeld cannot contain NULL values, the value is the
empty string (‘’).
■ INDEX_TYPE — The type of index. Example values are BTREE, FULLTEXT, HASH, and
RTREE.
■ COMMENT — Always the empty string (‘’).
■ INDEX_COMMENT — The comment deﬁned by the index_comment option when creating
or changing an index.
The output of SHOW INDEX is very similar to the ﬁelds in the STATISTICS system view:
■ Table — Equivalent to TABLE_NAME.
■ Non_unique — Equivalent to NON_UNIQUE.
■ Key_name — Equivalent to INDEX_NAME.
■ Seq_in_index, Column_name, Collation, Cardinality, Sub_part, Packed,
Null, Index_type, Comment, and Index_Comment — Equivalent to SEQ_IN_INDEX,
COLUMN_NAME, COLLATION, CARDINALITY, SUB_PART, PACKED, NULLABLE,
INDEX_TYPE, COMMENT, and INDEX_COMMENT.
TABLE_CONSTRAINTS
Unique and primary keys restrict data values in a table, only allowing one set of values for the
ﬁelds in those indexes. A foreign key restricts the allowable data for ﬁelds in a table by only
allowing values from another set of ﬁelds. The restrictions that unique, primary, and foreign
keys place on tables are referred to as table constraints. The TABLE_CONSTRAINTS system view
has information about unique keys, primary keys, and foreign keys in the following ﬁelds:
■ CONSTRAINT_CATALOG — Provided for standards compliance. However, because MySQL
does not have catalogs, this value is always NULL.
■ CONSTRAINT_SCHEMA — The name of the database that the constraint belongs to, such
as sakila. This is the same as the TABLE_SCHEMA ﬁeld.
■ CONSTRAINT_NAME — The name of the index. The value is PRIMARY for a primary key.
Unique keys and foreign keys have names that can be set when an index is created or
changed. The default name for foreign keys starts with fk_.
■ TABLE_SCHEMA — The name of the database that the constrained table belongs to.
■ TABLE_NAME — The name of the table constrained.
■ CONSTRAINT_TYPE — The type of constraint. Either PRIMARY KEY, UNIQUE, or
FOREIGN KEY.
681

Part IV Extending Your Skills
There is no equivalent SHOW statement for the data in TABLE_CONSTRAINTS, but the output of
SHOW INDEX has some of the same ﬁelds:
■ Table — Equivalent to TABLE_NAME.
■ Key_name — Equivalent to CONSTRAINT_NAME for unique and primary keys. For foreign
keys, the Key_name is the name of the index on the ﬁeld, and the CONSTRAINT_NAME is
the name of the foreign key constraint.
As an example, in the sakila.staff table, the address_id ﬁeld is a foreign key, deﬁned
with:
KEY idx_fk_address_id (address_id),
CONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES
address (address_id) ON UPDATE CASCADE,
The Key_name from SHOW INDEX is idx_fk_address_id, and the CONSTRAINT_NAME that
appears in the TABLE_CONSTRAINTS system view is fk_staff_address.
■ Column_name — Equivalent to COLUMN_NAME.
Note that SHOW INDEX shows all indexes and TABLE_CONSTRAINTS has information only for
unique, primary, and foreign keys.
KEY_COLUMN_USAGE
Like the TABLE_CONSTRAINTS system view, the KEY_COLUMN_USAGE system view only
shows information about unique keys, primary keys, and foreign keys. The ﬁelds in
KEY_COLUMN_USAGE are:
■ CONSTRAINT_CATALOG — Provided for standards compliance. However, because MySQL
does not have catalogs, this value is always NULL.
■ CONSTRAINT_SCHEMA — The name of the database that the constraint belongs to, such
as sakila. This is the same as the TABLE_SCHEMA ﬁeld.
■ CONSTRAINT_NAME — The name of the index. The value is PRIMARY for a primary key.
Unique keys and foreign keys have names that can be set when an index is created or
changed. The default name for foreign keys starts with fk_.
■ TABLE_CATALOG — Provided for standards compliance. However, because MySQL does
not have catalogs, this value is always NULL.
■ TABLE_SCHEMA — The name of the database that the constrained table belongs to.
■ TABLE_NAME — The name of the table constrained.
■ COLUMN_NAME — The name of the ﬁeld constrained.
■ ORDINAL_POSITION — The constrained ﬁeld’s position in the index. The ﬁrst ﬁeld in
an index has a value of 1, the second ﬁeld has a value of 2, and so on. For example, the
sakila.rental table has a composite unique index (a unique index with more than one
ﬁeld):
UNIQUE KEY rental_date (rental_date, inventory_id, customer_id)
682

The MySQL Data Dictionary 21
The ORDINAL_POSITION of rental_date is 1, of inventory_id is 2, and of customer_id
is 3.
■ POSITION_IN_UNIQUE_CONSTRAINT — NULL for keys that are not foreign keys. For
foreign keys, the value is the referenced ﬁeld’s position in the foreign key.
As an example, the foreign key fk_staff_address on the sakila.staff table is deﬁned as:
CONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES
address (address_id) ON UPDATE CASCADE
The value of POSITION_IN_UNIQUE_CONSTRAINT is 1, referring to the fact that the
address_id ﬁeld is the ﬁrst referenced ﬁeld. Foreign keys are usually deﬁned as:
FOREIGN KEY (fld1,fld2) REFERENCES reftbl (reffld1,reffld2)
Thus, the POSITION_IN_UNIQUE_CONSTRAINT is the same as ORDINAL_POSITION.
■ REFERENCED_TABLE_SCHEMA — NULL for keys that are not foreign keys. For foreign
keys, the database name of the referenced table. As an example, the foreign key
fk_staff_address on the sakila.staff table is deﬁned as:
CONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES
address (address_id) ON UPDATE CASCADE
The REFERENCED_TABLE_SCHEMA is sakila, which is the database containing the address
table.
■ REFERENCED_TABLE_NAME — NULL for keys that are not foreign keys. For foreign keys,
the name of the referenced table. In the previous example, the value is address.
■ REFERENCED_COLUMN_NAME — NULL for keys that are not foreign keys. For foreign keys,
the name of the referenced ﬁeld. In the previous example, the value is address_id.
There is no equivalent SHOW statement for the data in KEY_COLUMN_USAGE, but the output of
SHOW INDEX has some of the same ﬁelds:
■ Table — Equivalent to TABLE_NAME.
■ Key_name — Equivalent to CONSTRAINT_NAME for unique and primary keys. For foreign
keys, the Key_name is the name of the index on the ﬁeld, and the CONSTRAINT_NAME is
the name of the foreign key constraint.
As an example, in the sakila.staff table, the address_id ﬁeld is a foreign key, deﬁned
with:
KEY idx_fk_address_id (address_id),
CONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES
address (address_id) ON UPDATE CASCADE,
The Key_name from SHOW INDEX is idx_fk_address_id, and the CONSTRAINT_NAME that
appears in the TABLE_CONSTRAINTS system view is fk_staff_address.
■ Column_name — Equivalent to COLUMN_NAME.
683

Part IV Extending Your Skills
Note that SHOW INDEX shows all indexes and KEY_COLUMN_USAGE has information only for
unique, primary, and foreign keys.
REFERENTIAL_CONSTRAINTS
There is more information about foreign keys than the KEY_COLUMN_USAGE and TABLE_
CONSTRAINTS system views show. The REFERENTIAL_CONSTRAINTS system view shows the
behavior of a foreign key during updates and deletes. These behaviors are deﬁned in the foreign
key constraint with the ON UPDATE and ON DELETE clauses. The REFERENTIAL_CONSTRAINTS
system view also repeats the constraint and referenced constraint:
■ CONSTRAINT_CATALOG — Provided for standards compliance. However, because MySQL
does not have catalogs, this value is always NULL.
■ CONSTRAINT_SCHEMA — The name of the database that the constraint belongs to, such
as sakila.
■ CONSTRAINT_NAME — The name of the index. The value is PRIMARY for a primary key.
Unique keys and foreign keys have names that can be set when an index is created or
changed. The default name for foreign keys starts with fk_.
■ UNIQUE_CONSTRAINT_CATALOG — Provided for standards compliance. However,
because MySQL does not have catalogs, this value is always NULL.
■ UNIQUE_CONSTRAINT_SCHEMA — The database name of the referenced table. This is
equivalent to the REFERENCED_TABLE_SCHEMA in the KEY_COLUMN_USAGE system view.
■ UNIQUE_CONSTRAINT_NAME — The name of the referenced constraint. Similar to the
CONSTRAINT_NAME ﬁeld of the KEY_COLUMN_USAGE system view, this value is PRIMARY
for a primary key or the name of the index for non-primary keys.
■ MATCH_OPTION — This feature has not been implemented yet, so the value is always
NONE.
■ UPDATE_RULE — The update behavior of the foreign key, as set by the foreign key deﬁni-
tion. Possible values are CASCADE, NO ACTION, RESTRICT, SET NULL, SET DEFAULT. See
Chapter 6 for more information on deﬁning foreign keys.
■ DELETE_RULE — The delete behavior of the foreign key, as set by the foreign key deﬁni-
tion. Possible values are CASCADE, NO ACTION, RESTRICT, SET NULL, SET DEFAULT. See
Chapter 6 for more information on deﬁning foreign keys.
■ TABLE_NAME — The table name that the foreign key constraint belongs to.
■ REFERENCED_TABLE_NAME — The table name of the referenced table.
There is no complete SHOW statement for the ﬁelds in REFERENTIAL_CONSTRAINTS, but the
Key_name ﬁeld from the output of SHOW INDEX is equivalent to CONSTRAINT_NAME.
684

The MySQL Data Dictionary 21
TRIGGERS
Triggers are user-deﬁned stored SQL that get run when data changes. Triggers are deﬁned on
a per-table basis, and can occur before and after inserts, updates, and deletes. The ﬁelds in the
TRIGGERS system view are:
■ TRIGGER_CATALOG — Provided for standards compliance. However, because MySQL
does not have catalogs, this value is always NULL.
■ TRIGGER_SCHEMA — The name of the database that the trigger belongs to, such as
sakila.
■ TRIGGER_NAME — The name of the trigger.
■ EVENT_MANIPULATION — The name of the action that calls this trigger. Possible values
are INSERT, DELETE, or UPDATE.
■ EVENT_OBJECT_CATALOG — Provided for standards compliance. However, because
MySQL does not have catalogs, this value is always NULL.
■ EVENT_OBJECT_SCHEMA — The name of the database that the table associated with the
trigger belongs to, such as sakila.
■ EVENT_OBJECT_TABLE — The name of the table associated with the trigger.
■ ACTION_ORDER — The order the trigger is run in. This value is always 0 because MySQL
only supports one trigger per set of ACTION_TIMING and EVENT_MANIPULATION.
■ ACTION_CONDITION — Always NULL.
■ ACTION_STATEMENT — The SQL that is run by the trigger.
■ ACTION_ORIENTATION — Always ROW.
■ ACTION_TIMING — When the trigger occurs. Possible values are BEFORE or AFTER.
■ ACTION_REFERENCE_OLD_TABLE — Always NULL.
■ ACTION_REFERENCE_NEW_TABLE — Always NULL.
■ ACTION_REFERENCE_OLD_ROW — How the trigger references the row prior to the data
change. This is not settable by the user, and is always OLD.
■ ACTION_REFERENCE_NEW_ROW — How the trigger references the row after the data is
changed. This is not settable by the user, and is always NEW.
■ CREATED — Always NULL.
■ SQL_MODE — Stores the environmental sql_mode as it was when the trigger was created.
This is also the sql_mode that is used when the trigger is invoked.
■ DEFINER — The trigger deﬁner, in the MySQL user@host format.
685

Part IV Extending Your Skills
■ CHARACTER_SET_CLIENT — Stores the environmental character set as it was when the
trigger was created. This is also the character_set_client that is used when the trig-
ger is invoked.
■ COLLATION_CONNECTION — Stores the environmental collation as it was when the trig-
ger was created. This is also the collation_connection that is used when the trigger is
invoked.
■ DATABASE_COLLATION — The default collation of the database associated with the
trigger.
For more information on triggers, see Chapter 7.
The SHOW TRIGGERS statement outputs some of the same information in the TRIGGERS system
view. The equivalent ﬁelds are:
■ Trigger — Equivalent to TRIGGER_NAME.
■ Event — Equivalent to EVENT_MANIPULATION.
■ Table — Equivalent to EVENT_OBJECT_TABLE.
■ Statement — Equivalent to ACTION_STATEMENT.
■ Timing — Equivalent to ACTION_TIMING.
■ Created, sql_mode, Definer, character_set_client, collation
connection — Equivalent to CREATED, SQL_MODE, DEFINER,
CHARACTER_SET_CLIENT, COLLATION_CONNECTION.
The SHOW CREATE TRIGGER statement outputs the following ﬁelds, which are similar to ﬁelds in
the TRIGGERS system view:
■ Trigger — Equivalent to TRIGGER_NAME.
■ sql_mode — Equivalent to SQL_MODE.
■ SQL Original Statement — This is the entire CREATE TRIGGER statement, which
includes the information shown in the EVENT_MANIPULATION, ACTION_ORDER,
ACTION_STATEMENT, ACTION_TIMING, and DEFINER ﬁelds.
■ character_set_client, collation connection, Database Collation —
Equivalent to CHARACTER_SET_CLIENT, COLLATION_CONNECTION, and DATABASE_
COLLATION.
ROUTINES
The ROUTINES system view stores metadata about stored procedures and stored routines.
For more information on stored routines, see Chapter 7. The ﬁelds in the ROUTINES system
view are:
■ SPECIFIC_NAME — The name of the stored routine.
■ ROUTINE_CATALOG — Provided for standards compliance. However, because MySQL
does not have catalogs, this value is always NULL.
686