<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Chapter 20. INFORMATION_SCHEMA Tables</title><link rel="stylesheet" href="mysql-html.css" type="text/css"><meta name="generator" content="DocBook XSL Stylesheets V1.69.1"><link rel="start" href="index.html" title="MySQL 5.1 Reference Manual"><link rel="up" href="index.html" title="MySQL 5.1 Reference Manual"><link rel="prev" href="stored-programs-views.html" title="Chapter 19. Stored Programs and Views"><link rel="next" href="connectors-apis.html" title="Chapter 21. Connectors and APIs"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">Chapter 20. <code class="literal">INFORMATION_SCHEMA</code> Tables</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="stored-programs-views.html">Prev</a> </td><th width="60%" align="center"> </th><td width="20%" align="right"> <a accesskey="n" href="connectors-apis.html">Next</a></td></tr></table><hr></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="information-schema"></a>Chapter 20. <code class="literal">INFORMATION_SCHEMA</code> Tables</h2></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="information-schema.html#schemata-table">20.1. The <code class="literal">INFORMATION_SCHEMA SCHEMATA</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#tables-table">20.2. The <code class="literal">INFORMATION_SCHEMA TABLES</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#columns-table">20.3. The <code class="literal">INFORMATION_SCHEMA COLUMNS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#statistics-table">20.4. The <code class="literal">INFORMATION_SCHEMA STATISTICS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#user-privileges-table">20.5. The <code class="literal">INFORMATION_SCHEMA USER_PRIVILEGES</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#schema-privileges-table">20.6. The <code class="literal">INFORMATION_SCHEMA SCHEMA_PRIVILEGES</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#table-privileges-table">20.7. The <code class="literal">INFORMATION_SCHEMA TABLE_PRIVILEGES</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#column-privileges-table">20.8. The <code class="literal">INFORMATION_SCHEMA COLUMN_PRIVILEGES</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#character-sets-table">20.9. The <code class="literal">INFORMATION_SCHEMA CHARACTER_SETS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#collations-table">20.10. The <code class="literal">INFORMATION_SCHEMA COLLATIONS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#collation-character-set-applicability-table">20.11. The <code class="literal">INFORMATION_SCHEMA
COLLATION_CHARACTER_SET_APPLICABILITY</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#table-constraints-table">20.12. The <code class="literal">INFORMATION_SCHEMA TABLE_CONSTRAINTS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#key-column-usage-table">20.13. The <code class="literal">INFORMATION_SCHEMA KEY_COLUMN_USAGE</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#routines-table">20.14. The <code class="literal">INFORMATION_SCHEMA ROUTINES</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#views-table">20.15. The <code class="literal">INFORMATION_SCHEMA VIEWS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#triggers-table">20.16. The <code class="literal">INFORMATION_SCHEMA TRIGGERS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#plugins-table">20.17. The <code class="literal">INFORMATION_SCHEMA PLUGINS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#engines-table">20.18. The <code class="literal">INFORMATION_SCHEMA ENGINES</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#partitions-table">20.19. The <code class="literal">INFORMATION_SCHEMA PARTITIONS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#events-table">20.20. The <code class="literal">INFORMATION_SCHEMA EVENTS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#files-table">20.21. The <code class="literal">INFORMATION_SCHEMA FILES</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#processlist-table">20.22. The <code class="literal">INFORMATION_SCHEMA PROCESSLIST</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#referential-constraints-table">20.23. The <code class="literal">INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#status-table">20.24. The <code class="literal">INFORMATION_SCHEMA GLOBAL_STATUS</code> and
<code class="literal">SESSION_STATUS</code>
Tables</a></span></dt><dt><span class="section"><a href="information-schema.html#variables-table">20.25. The <code class="literal">INFORMATION_SCHEMA GLOBAL_VARIABLES</code> and
<code class="literal">SESSION_VARIABLES</code>
Tables</a></span></dt><dt><span class="section"><a href="information-schema.html#profiling-table">20.26. The <code class="literal">INFORMATION_SCHEMA PROFILING</code> Table</a></span></dt><dt><span class="section"><a href="information-schema.html#other-information-schema-tables">20.27. Other <code class="literal">INFORMATION_SCHEMA</code> Tables</a></span></dt><dt><span class="section"><a href="information-schema.html#extended-show">20.28. Extensions to <code class="literal">SHOW</code> Statements</a></span></dt></dl></div><a class="indexterm" name="id4838917"></a><a class="indexterm" name="id4838930"></a><a class="indexterm" name="id4838938"></a><a class="indexterm" name="id4838947"></a><a class="indexterm" name="id4838956"></a><p>
<code class="literal">INFORMATION_SCHEMA</code> provides access to database
metadata.
</p><p>
<em class="firstterm">Metadata</em> is data about the data, such as the
name of a database or table, the data type of a column, or access
privileges. Other terms that sometimes are used for this information
are <em class="firstterm">data dictionary</em> and <em class="firstterm">system
catalog</em>.
</p><p>
<code class="literal">INFORMATION_SCHEMA</code> is the information database,
the place that stores information about all the other databases that
the MySQL server maintains. Inside
<code class="literal">INFORMATION_SCHEMA</code> there are several read-only
tables. They are actually views, not base tables, so there are no
files associated with them.
</p><p>
In effect, we have a database named
<code class="literal">INFORMATION_SCHEMA</code>, although the server does not
create a database directory with that name. It is possible to select
<code class="literal">INFORMATION_SCHEMA</code> as the default database with a
<a href="sql-syntax.html#use" title="12.3.4. USE Syntax"><code class="literal">USE</code></a> statement, but it is possible
only to read the contents of tables. You cannot insert into them,
update them, or delete from them.
</p><p>
Here is an example of a statement that retrieves information from
<code class="literal">INFORMATION_SCHEMA</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT table_name, table_type, engine</code></strong>
-> <strong class="userinput"><code>FROM information_schema.tables</code></strong>
-> <strong class="userinput"><code>WHERE table_schema = 'db5'</code></strong>
-> <strong class="userinput"><code>ORDER BY table_name DESC;</code></strong>
+------------+------------+--------+
| table_name | table_type | engine |
+------------+------------+--------+
| v56 | VIEW | NULL |
| v3 | VIEW | NULL |
| v2 | VIEW | NULL |
| v | VIEW | NULL |
| tables | BASE TABLE | MyISAM |
| t7 | BASE TABLE | MyISAM |
| t3 | BASE TABLE | MyISAM |
| t2 | BASE TABLE | MyISAM |
| t | BASE TABLE | MyISAM |
| pk | BASE TABLE | InnoDB |
| loop | BASE TABLE | MyISAM |
| kurs | BASE TABLE | MyISAM |
| k | BASE TABLE | MyISAM |
| into | BASE TABLE | MyISAM |
| goto | BASE TABLE | MyISAM |
| fk2 | BASE TABLE | InnoDB |
| fk | BASE TABLE | InnoDB |
+------------+------------+--------+
17 rows in set (0.01 sec)
</pre><p>
Explanation: The statement requests a list of all the tables in
database <code class="literal">db5</code>, in reverse alphabetical order,
showing just three pieces of information: the name of the table, its
type, and its storage engine.
</p><p>
The definition for character columns (for example,
<code class="literal">TABLES.TABLE_NAME</code>) is generally
<code class="literal">VARCHAR(<em class="replaceable"><code>N</code></em>) CHARACTER SET
utf8</code> where <em class="replaceable"><code>N</code></em> is at least 64.
MySQL uses the default collation for this character set
(<code class="literal">utf8_general_ci</code>) for all searches, sorts,
comparisons, and other string operations on such columns. Values
such as table names in <code class="literal">INFORMATION_SCHEMA</code> columns
are treated as strings, not identifiers, and are not compared using
the identifier rules described in
<a href="language-structure.html#identifier-case-sensitivity" title="8.2.2. Identifier Case Sensitivity">Section 8.2.2, “Identifier Case Sensitivity”</a>. If the result of a
string operation on an <code class="literal">INFORMATION_SCHEMA</code> column
differs from expectations, a workaround is to use an explicit
<code class="literal">COLLATE</code> clause to force a suitable collation
(<a href="internationalization-localization.html#charset-collate" title="9.1.7.1. Using COLLATE in SQL Statements">Section 9.1.7.1, “Using <code class="literal">COLLATE</code> in SQL Statements”</a>). You can also use the
<a href="functions.html#function_upper"><code class="literal">UPPER()</code></a> or
<a href="functions.html#function_lower"><code class="literal">LOWER()</code></a> function. For example, in a
<code class="literal">WHERE</code> clause, you might use:
</p><pre class="programlisting">WHERE TABLE_NAME COLLATE utf8_bin = 'City'
WHERE TABLE_NAME COLLATE utf8_general_ci = 'city'
WHERE UPPER(TABLE_NAME) = 'CITY'
WHERE LOWER(TABLE_NAME) = 'city'
</pre><p>
Each MySQL user has the right to access these tables, but can see
only the rows in the tables that correspond to objects for which the
user has the proper access privileges. In some cases (for example,
the <code class="literal">ROUTINE_DEFINITION</code> column in the
<a href="information-schema.html#routines-table" title="20.14. The INFORMATION_SCHEMA ROUTINES Table"><code class="literal">INFORMATION_SCHEMA.ROUTINES</code></a> table),
users who have insufficient privileges will see
<code class="literal">NULL</code>.
</p><p>
The <code class="literal">SELECT ... FROM INFORMATION_SCHEMA</code> statement
is intended as a more consistent way to provide access to the
information provided by the various
<a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a> statements that MySQL supports
(<a href="sql-syntax.html#show-databases" title="12.5.5.15. SHOW DATABASES Syntax"><code class="literal">SHOW DATABASES</code></a>,
<a href="sql-syntax.html#show-tables" title="12.5.5.39. SHOW TABLES Syntax"><code class="literal">SHOW TABLES</code></a>, and so forth). Using
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> has these advantages, compared
to <a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
It conforms to Codd's rules. That is, all access is done on
tables.
</p></li><li><p>
Nobody needs to learn a new statement syntax. Because they
already know how <a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> works,
they only need to learn the object names.
</p></li><li><p>
The implementor need not worry about adding keywords.
</p></li><li><p>
There are millions of possible output variations, instead of
just one. This provides more flexibility for applications that
have varying requirements about what metadata they need.
</p></li><li><p>
Migration is easier because every other DBMS does it this way.
</p></li></ul></div><p>
However, because <a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a> is popular with
MySQL employees and users, and because it might be confusing were it
to disappear, the advantages of conventional syntax are not a
sufficient reason to eliminate <a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>.
In fact, along with the implementation of
<code class="literal">INFORMATION_SCHEMA</code>, there are enhancements to
<a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a> as well. These are described in
<a href="information-schema.html#extended-show" title="20.28. Extensions to SHOW Statements">Section 20.28, “Extensions to <code class="literal">SHOW</code> Statements”</a>.
</p><p>
There is no difference between the privileges required for
<a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a> statements and those required to
select information from <code class="literal">INFORMATION_SCHEMA</code>. In
either case, you have to have some privilege on an object in order
to see information about it.
</p><p>
The implementation for the <code class="literal">INFORMATION_SCHEMA</code>
table structures in MySQL follows the ANSI/ISO SQL:2003 standard
Part 11 <em class="citetitle">Schemata</em>. Our intent is approximate
compliance with SQL:2003 core feature F021 <em class="citetitle">Basic
information schema</em>.
</p><p>
Users of SQL Server 2000 (which also follows the standard) may
notice a strong similarity. However, MySQL has omitted many columns
that are not relevant for our implementation, and added columns that
are MySQL-specific. One such column is the <code class="literal">ENGINE</code>
column in the <a href="information-schema.html#tables-table" title="20.2. The INFORMATION_SCHEMA TABLES Table"><code class="literal">INFORMATION_SCHEMA.TABLES</code></a>
table.
</p><p>
Although other DBMSs use a variety of names, like
<code class="literal">syscat</code> or <code class="literal">system</code>, the standard
name is <code class="literal">INFORMATION_SCHEMA</code>.
</p><p>
The following sections describe each of the tables and columns that
are in <code class="literal">INFORMATION_SCHEMA</code>. For each column, there
are three pieces of information:
</p><div class="itemizedlist"><ul type="disc"><li><p>
“<span class="quote"><code class="literal">INFORMATION_SCHEMA</code> Name</span>”
indicates the name for the column in the
<code class="literal">INFORMATION_SCHEMA</code> table. This corresponds to
the standard SQL name unless the “<span class="quote">Remarks</span>” field
says “<span class="quote">MySQL extension.</span>”
</p></li><li><p>
“<span class="quote"><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a> Name</span>”
indicates the equivalent field name in the closest
<a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a> statement, if there is one.
</p></li><li><p>
“<span class="quote">Remarks</span>” provides additional information where
applicable. If this field is <code class="literal">NULL</code>, it means
that the value of the column is always <code class="literal">NULL</code>.
If this field says “<span class="quote">MySQL extension,</span>” the column is
a MySQL extension to standard SQL.
</p></li></ul></div><p>
To avoid using any name that is reserved in the standard or in DB2,
SQL Server, or Oracle, we changed the names of some columns marked
“<span class="quote">MySQL extension</span>”. (For example, we changed
<code class="literal">COLLATION</code> to <code class="literal">TABLE_COLLATION</code>
in the <a href="information-schema.html#tables-table" title="20.2. The INFORMATION_SCHEMA TABLES Table"><code class="literal">TABLES</code></a> table.) See the list of
reserved words near the end of this article:
<a href="http://web.archive.org/web/20070409075643rn_1/www.dbazine.com/db2/db2-disarticles/gulutzan5" target="_top">http://web.archive.org/web/20070409075643rn_1/www.dbazine.com/db2/db2-disarticles/gulutzan5</a>.
</p><p>
Each section indicates what <a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
statement is equivalent to a <a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a>
that retrieves information from
<code class="literal">INFORMATION_SCHEMA</code>, if there is such a statement.
For <a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a> statements that display
information for the current database if you omit a <code class="literal">FROM
<em class="replaceable"><code>db_name</code></em></code> clause, you can often
select information for the current database by adding an
<code class="literal">AND TABLE_SCHEMA = CURRENT_SCHEMA()</code> condition to
the <code class="literal">WHERE</code> clause of a query that retrieves
information from an <code class="literal">INFORMATION_SCHEMA</code> table.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
At present, there are some missing columns and some columns out of
order. We are working on this and updating the documentation as
changes are made.
</p></div><p>
For answers to questions that are often asked concerning the
<code class="literal">INFORMATION_SCHEMA</code> database, see
<a href="faqs.html#faqs-information-schema" title="A.7. MySQL 5.0 FAQ — INFORMATION_SCHEMA">Section A.7, “MySQL 5.0 FAQ — <code class="literal">INFORMATION_SCHEMA</code>”</a>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="schemata-table"></a>20.1. The <code class="literal">INFORMATION_SCHEMA SCHEMATA</code> Table</h2></div></div></div><a class="indexterm" name="id4839646"></a><p>
A schema is a database, so the
<a href="information-schema.html#schemata-table" title="20.1. The INFORMATION_SCHEMA SCHEMATA Table"><code class="literal">SCHEMATA</code></a> table provides information
about databases.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">CATALOG_NAME</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">SCHEMA_NAME</code></td><td> </td><td>Database</td></tr><tr><td><code class="literal">DEFAULT_CHARACTER_SET_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">DEFAULT_COLLATION_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">SQL_PATH</code></td><td> </td><td><code class="literal">NULL</code></td></tr></tbody></table></div><p>
The following statements are equivalent:
</p><pre class="programlisting">SELECT SCHEMA_NAME AS `Database`
FROM INFORMATION_SCHEMA.SCHEMATA
[WHERE SCHEMA_NAME LIKE '<em class="replaceable"><code>wild</code></em>']
SHOW DATABASES
[LIKE '<em class="replaceable"><code>wild</code></em>']
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="tables-table"></a>20.2. The <code class="literal">INFORMATION_SCHEMA TABLES</code> Table</h2></div></div></div><a class="indexterm" name="id4839842"></a><p>
The <a href="information-schema.html#tables-table" title="20.2. The INFORMATION_SCHEMA TABLES Table"><code class="literal">TABLES</code></a> table provides information
about tables in databases.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">TABLE_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">TABLE_SCHEMA</code></td><td><code class="literal">Table_</code>...</td><td> </td></tr><tr><td><code class="literal">TABLE_NAME</code></td><td><code class="literal">Table_</code>...</td><td> </td></tr><tr><td><code class="literal">TABLE_TYPE</code></td><td> </td><td> </td></tr><tr><td><code class="literal">ENGINE</code></td><td><code class="literal">Engine</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">VERSION</code></td><td><code class="literal">Version</code></td><td>The version number of the table's <code class="filename">.frm</code> file, MySQL
extension</td></tr><tr><td><code class="literal">ROW_FORMAT</code></td><td><code class="literal">Row_format</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">TABLE_ROWS</code></td><td><code class="literal">Rows</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">AVG_ROW_LENGTH</code></td><td><code class="literal">Avg_row_length</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">DATA_LENGTH</code></td><td><code class="literal">Data_length</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">MAX_DATA_LENGTH</code></td><td><code class="literal">Max_data_length</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">INDEX_LENGTH</code></td><td><code class="literal">Index_length</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">DATA_FREE</code></td><td><code class="literal">Data_free</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">AUTO_INCREMENT</code></td><td><code class="literal">Auto_increment</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">CREATE_TIME</code></td><td><code class="literal">Create_time</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">UPDATE_TIME</code></td><td><code class="literal">Update_time</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">CHECK_TIME</code></td><td><code class="literal">Check_time</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">TABLE_COLLATION</code></td><td><code class="literal">Collation</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">CHECKSUM</code></td><td><code class="literal">Checksum</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">CREATE_OPTIONS</code></td><td><code class="literal">Create_options</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">TABLE_COMMENT</code></td><td><code class="literal">Comment</code></td><td>MySQL extension</td></tr></tbody></table></div><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">TABLE_SCHEMA</code> and
<code class="literal">TABLE_NAME</code> are a single field in a
<a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a> display, for example
<code class="literal">Table_in_db1</code>.
</p></li><li><p>
<code class="literal">TABLE_TYPE</code> should be <code class="literal">BASE
TABLE</code> or <code class="literal">VIEW</code>. Currently, the
<a href="information-schema.html#tables-table" title="20.2. The INFORMATION_SCHEMA TABLES Table"><code class="literal">TABLES</code></a> table does not list
<code class="literal">TEMPORARY</code> tables.
</p></li><li><p>
For partitioned tables, beginning with MySQL 5.1.9, the
<code class="literal">ENGINE</code> column shows the name of the storage
engine used by all partitions. (Previously, this column showed
<code class="literal">PARTITION</code> for such tables.)
</p></li><li><p>
The <code class="literal">TABLE_ROWS</code> column is
<code class="literal">NULL</code> if the table is in the
<code class="literal">INFORMATION_SCHEMA</code> database.
</p><p>
For <code class="literal">InnoDB</code> tables, the row count
is only a rough estimate used in SQL optimization. (This is
also true if the <code class="literal">InnoDB</code> table is
partitioned.)
</p></li><li><p>
For tables using the <code class="literal">NDBCLUSTER</code>
storage engine, beginning with MySQL 5.1.12, the
<code class="literal">DATA_LENGTH</code> column reflects the true amount
of storage for variable-width columns. (See <a href="http://bugs.mysql.com/18413" target="_top">Bug#18413</a>.)
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
Because MySQL Cluster allocates storage for variable-width
columns in 10-page extents of 32 kilobytes each, space usage
for such columns is reported in increments of 320 KB.
</p></div></li><li><p>
Beginning with MySQL 5.1.28, the <code class="literal">DATA_FREE</code>
column shows the free space in bytes for
<code class="literal">InnoDB</code> tables.
</p></li><li><p>
We have nothing for the table's default character set.
<code class="literal">TABLE_COLLATION</code> is close, because collation
names begin with a character set name.
</p></li><li><p>
Beginning with MySQL 5.1.9, the
<code class="literal">CREATE_OPTIONS</code> column shows
<code class="literal">partitioned</code> if the table is partitioned.
</p></li></ul></div><p>
The following statements are equivalent:
</p><pre class="programlisting">SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = '<em class="replaceable"><code>db_name</code></em>'
[AND table_name LIKE '<em class="replaceable"><code>wild</code></em>']
SHOW TABLES
FROM <em class="replaceable"><code>db_name</code></em>
[LIKE '<em class="replaceable"><code>wild</code></em>']
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="columns-table"></a>20.3. The <code class="literal">INFORMATION_SCHEMA COLUMNS</code> Table</h2></div></div></div><a class="indexterm" name="id4840631"></a><p>
The <a href="information-schema.html#columns-table" title="20.3. The INFORMATION_SCHEMA COLUMNS Table"><code class="literal">COLUMNS</code></a> table provides
information about columns in tables.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">TABLE_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">TABLE_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">TABLE_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">COLUMN_NAME</code></td><td><code class="literal">Field</code></td><td> </td></tr><tr><td><code class="literal">ORDINAL_POSITION</code></td><td> </td><td>see notes</td></tr><tr><td><code class="literal">COLUMN_DEFAULT</code></td><td><code class="literal">Default</code></td><td> </td></tr><tr><td><code class="literal">IS_NULLABLE</code></td><td><code class="literal">Null</code></td><td> </td></tr><tr><td><code class="literal">DATA_TYPE</code></td><td><code class="literal">Type</code></td><td> </td></tr><tr><td><code class="literal">CHARACTER_MAXIMUM_LENGTH</code></td><td><code class="literal">Type</code></td><td> </td></tr><tr><td><code class="literal">CHARACTER_OCTET_LENGTH</code></td><td> </td><td> </td></tr><tr><td><code class="literal">NUMERIC_PRECISION</code></td><td><code class="literal">Type</code></td><td> </td></tr><tr><td><code class="literal">NUMERIC_SCALE</code></td><td><code class="literal">Type</code></td><td> </td></tr><tr><td><code class="literal">CHARACTER_SET_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">COLLATION_NAME</code></td><td><code class="literal">Collation</code></td><td> </td></tr><tr><td><code class="literal">COLUMN_TYPE</code></td><td><code class="literal">Type</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">COLUMN_KEY</code></td><td><code class="literal">Key</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">EXTRA</code></td><td><code class="literal">Extra</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">PRIVILEGES</code></td><td><code class="literal">Privileges</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">COLUMN_COMMENT</code></td><td><code class="literal">Comment</code></td><td>MySQL extension</td></tr></tbody></table></div><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
In <a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>, the
<code class="literal">Type</code> display includes values from several
different <a href="information-schema.html#columns-table" title="20.3. The INFORMATION_SCHEMA COLUMNS Table"><code class="literal">COLUMNS</code></a> columns.
</p></li><li><p>
<code class="literal">ORDINAL_POSITION</code> is necessary because you
might want to say <code class="literal">ORDER BY
ORDINAL_POSITION</code>. Unlike
<a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>,
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> does not have automatic
ordering.
</p></li><li><p>
<code class="literal">CHARACTER_OCTET_LENGTH</code> should be the same
as <code class="literal">CHARACTER_MAXIMUM_LENGTH</code>, except for
multi-byte character sets.
</p></li><li><p>
<code class="literal">CHARACTER_SET_NAME</code> can be derived from
<code class="literal">Collation</code>. For example, if you say
<code class="literal">SHOW FULL COLUMNS FROM t</code>, and you see in
the <code class="literal">Collation</code> column a value of
<code class="literal">latin1_swedish_ci</code>, the character set is
what is before the first underscore:
<code class="literal">latin1</code>.
</p></li></ul></div><p>
The following statements are nearly equivalent:
</p><pre class="programlisting">SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<em class="replaceable"><code>tbl_name</code></em>'
[AND table_schema = '<em class="replaceable"><code>db_name</code></em>']
[AND column_name LIKE '<em class="replaceable"><code>wild</code></em>']
SHOW COLUMNS
FROM <em class="replaceable"><code>tbl_name</code></em>
[FROM <em class="replaceable"><code>db_name</code></em>]
[LIKE '<em class="replaceable"><code>wild</code></em>']
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="statistics-table"></a>20.4. The <code class="literal">INFORMATION_SCHEMA STATISTICS</code> Table</h2></div></div></div><a class="indexterm" name="id4841240"></a><p>
The <a href="information-schema.html#statistics-table" title="20.4. The INFORMATION_SCHEMA STATISTICS Table"><code class="literal">STATISTICS</code></a> table provides
information about table indexes.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">TABLE_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">TABLE_SCHEMA</code></td><td> </td><td>= Database</td></tr><tr><td><code class="literal">TABLE_NAME</code></td><td><code class="literal">Table</code></td><td> </td></tr><tr><td><code class="literal">NON_UNIQUE</code></td><td><code class="literal">Non_unique</code></td><td> </td></tr><tr><td><code class="literal">INDEX_SCHEMA</code></td><td> </td><td>= Database</td></tr><tr><td><code class="literal">INDEX_NAME</code></td><td><code class="literal">Key_name</code></td><td> </td></tr><tr><td><code class="literal">SEQ_IN_INDEX</code></td><td><code class="literal">Seq_in_index</code></td><td> </td></tr><tr><td><code class="literal">COLUMN_NAME</code></td><td><code class="literal">Column_name</code></td><td> </td></tr><tr><td><code class="literal">COLLATION</code></td><td><code class="literal">Collation</code></td><td> </td></tr><tr><td><code class="literal">CARDINALITY</code></td><td><code class="literal">Cardinality</code></td><td> </td></tr><tr><td><code class="literal">SUB_PART</code></td><td><code class="literal">Sub_part</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">PACKED</code></td><td><code class="literal">Packed</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">NULLABLE</code></td><td><code class="literal">Null</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">INDEX_TYPE</code></td><td><code class="literal">Index_type</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">COMMENT</code></td><td><code class="literal">Comment</code></td><td>MySQL extension</td></tr></tbody></table></div><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
There is no standard table for indexes. The preceding list is
similar to what SQL Server 2000 returns for
<code class="literal">sp_statistics</code>, except that we replaced the
name <code class="literal">QUALIFIER</code> with
<code class="literal">CATALOG</code> and we replaced the name
<code class="literal">OWNER</code> with <code class="literal">SCHEMA</code>.
</p><p>
Clearly, the preceding table and the output from
<a href="sql-syntax.html#show-index" title="12.5.5.23. SHOW INDEX Syntax"><code class="literal">SHOW INDEX</code></a> are derived from the
same parent. So the correlation is already close.
</p></li></ul></div><p>
The following statements are equivalent:
</p><pre class="programlisting">SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_name = '<em class="replaceable"><code>tbl_name</code></em>'
AND table_schema = '<em class="replaceable"><code>db_name</code></em>'
SHOW INDEX
FROM <em class="replaceable"><code>tbl_name</code></em>
FROM <em class="replaceable"><code>db_name</code></em>
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="user-privileges-table"></a>20.5. The <code class="literal">INFORMATION_SCHEMA USER_PRIVILEGES</code> Table</h2></div></div></div><a class="indexterm" name="id4841707"></a><p>
The <a href="information-schema.html#user-privileges-table" title="20.5. The INFORMATION_SCHEMA USER_PRIVILEGES Table"><code class="literal">USER_PRIVILEGES</code></a> table provides
information about global privileges. This information comes from
the <code class="literal">mysql.user</code> grant table.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">GRANTEE</code></td><td> </td><td><code class="literal">'<em class="replaceable"><code>user_name</code></em>'@'<em class="replaceable"><code>host_name</code></em>'</code>
value, MySQL extension</td></tr><tr><td><code class="literal">TABLE_CATALOG</code></td><td> </td><td><code class="literal">NULL</code>, MySQL extension</td></tr><tr><td><code class="literal">PRIVILEGE_TYPE</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">IS_GRANTABLE</code></td><td> </td><td>MySQL extension</td></tr></tbody></table></div><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
This is a nonstandard table. It takes its values from the
<code class="literal">mysql.user</code> table.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="schema-privileges-table"></a>20.6. The <code class="literal">INFORMATION_SCHEMA SCHEMA_PRIVILEGES</code> Table</h2></div></div></div><a class="indexterm" name="id4841914"></a><p>
The <a href="information-schema.html#schema-privileges-table" title="20.6. The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table"><code class="literal">SCHEMA_PRIVILEGES</code></a> table provides
information about schema (database) privileges. This information
comes from the <code class="literal">mysql.db</code> grant table.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">GRANTEE</code></td><td> </td><td><code class="literal">'<em class="replaceable"><code>user_name</code></em>'@'<em class="replaceable"><code>host_name</code></em>'</code>
value, MySQL extension</td></tr><tr><td><code class="literal">TABLE_CATALOG</code></td><td> </td><td><code class="literal">NULL</code>, MySQL extension</td></tr><tr><td><code class="literal">TABLE_SCHEMA</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">PRIVILEGE_TYPE</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">IS_GRANTABLE</code></td><td> </td><td>MySQL extension</td></tr></tbody></table></div><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
This is a nonstandard table. It takes its values from the
<code class="literal">mysql.db</code> table.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="table-privileges-table"></a>20.7. The <code class="literal">INFORMATION_SCHEMA TABLE_PRIVILEGES</code> Table</h2></div></div></div><a class="indexterm" name="id4842137"></a><p>
The <a href="information-schema.html#table-privileges-table" title="20.7. The INFORMATION_SCHEMA TABLE_PRIVILEGES Table"><code class="literal">TABLE_PRIVILEGES</code></a> table provides
information about table privileges. This information comes from
the <code class="literal">mysql.tables_priv</code> grant table.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">GRANTEE</code></td><td> </td><td><code class="literal">'<em class="replaceable"><code>user_name</code></em>'@'<em class="replaceable"><code>host_name</code></em>'</code>
value</td></tr><tr><td><code class="literal">TABLE_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">TABLE_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">TABLE_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">PRIVILEGE_TYPE</code></td><td> </td><td> </td></tr><tr><td><code class="literal">IS_GRANTABLE</code></td><td> </td><td> </td></tr></tbody></table></div><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">PRIVILEGE_TYPE</code> can contain one (and only
one) of these values: <a href="server-administration.html#priv_select"><code class="literal">SELECT</code></a>,
<a href="server-administration.html#priv_insert"><code class="literal">INSERT</code></a>,
<a href="server-administration.html#priv_update"><code class="literal">UPDATE</code></a>,
<a href="server-administration.html#priv_references"><code class="literal">REFERENCES</code></a>,
<a href="server-administration.html#priv_alter"><code class="literal">ALTER</code></a>,
<a href="server-administration.html#priv_index"><code class="literal">INDEX</code></a>,
<a href="server-administration.html#priv_drop"><code class="literal">DROP</code></a>,
<a href="server-administration.html#priv_create-view"><code class="literal">CREATE VIEW</code></a>.
</p></li></ul></div><p>
The following statements are <span class="emphasis"><em>not</em></span> equivalent:
</p><pre class="programlisting">SELECT ... FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
SHOW GRANTS ...
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="column-privileges-table"></a>20.8. The <code class="literal">INFORMATION_SCHEMA COLUMN_PRIVILEGES</code> Table</h2></div></div></div><a class="indexterm" name="id4842437"></a><p>
The <a href="information-schema.html#column-privileges-table" title="20.8. The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table"><code class="literal">COLUMN_PRIVILEGES</code></a> table provides
information about column privileges. This information comes from
the <code class="literal">mysql.columns_priv</code> grant table.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">GRANTEE</code></td><td> </td><td><code class="literal">'<em class="replaceable"><code>user_name</code></em>'@'<em class="replaceable"><code>host_name</code></em>'</code>
value</td></tr><tr><td><code class="literal">TABLE_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">TABLE_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">TABLE_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">COLUMN_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">PRIVILEGE_TYPE</code></td><td> </td><td> </td></tr><tr><td><code class="literal">IS_GRANTABLE</code></td><td> </td><td> </td></tr></tbody></table></div><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
In the output from
<a href="sql-syntax.html#show-columns" title="12.5.5.6. SHOW COLUMNS Syntax"><code class="literal">SHOW FULL
COLUMNS</code></a>, the privileges are all in one field and in
lowercase, for example,
<code class="literal">select,insert,update,references</code>. In
<a href="information-schema.html#column-privileges-table" title="20.8. The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table"><code class="literal">COLUMN_PRIVILEGES</code></a>, there is one
privilege per row, in uppercase.
</p></li><li><p>
<code class="literal">PRIVILEGE_TYPE</code> can contain one (and only
one) of these values: <a href="server-administration.html#priv_select"><code class="literal">SELECT</code></a>,
<a href="server-administration.html#priv_insert"><code class="literal">INSERT</code></a>,
<a href="server-administration.html#priv_update"><code class="literal">UPDATE</code></a>,
<a href="server-administration.html#priv_references"><code class="literal">REFERENCES</code></a>.
</p></li><li><p>
If the user has <a href="server-administration.html#priv_grant-option"><code class="literal">GRANT OPTION</code></a>
privilege, <code class="literal">IS_GRANTABLE</code> should be
<code class="literal">YES</code>. Otherwise,
<code class="literal">IS_GRANTABLE</code> should be
<code class="literal">NO</code>. The output does not list
<a href="server-administration.html#priv_grant-option"><code class="literal">GRANT OPTION</code></a> as a separate
privilege.
</p></li></ul></div><p>
The following statements are <span class="emphasis"><em>not</em></span> equivalent:
</p><pre class="programlisting">SELECT ... FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
SHOW GRANTS ...
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="character-sets-table"></a>20.9. The <code class="literal">INFORMATION_SCHEMA CHARACTER_SETS</code> Table</h2></div></div></div><a class="indexterm" name="id4842797"></a><p>
The <a href="information-schema.html#character-sets-table" title="20.9. The INFORMATION_SCHEMA CHARACTER_SETS Table"><code class="literal">CHARACTER_SETS</code></a> table provides
information about available character sets.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">CHARACTER_SET_NAME</code></td><td><code class="literal">Charset</code></td><td> </td></tr><tr><td><code class="literal">DEFAULT_COLLATE_NAME</code></td><td><code class="literal">Default collation</code></td><td> </td></tr><tr><td><code class="literal">DESCRIPION</code></td><td><code class="literal">Description</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">MAXLEN</code></td><td><code class="literal">Maxlen</code></td><td>MySQL extension</td></tr></tbody></table></div><p>
The following statements are equivalent:
</p><pre class="programlisting">SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS
[WHERE name LIKE '<em class="replaceable"><code>wild</code></em>']
SHOW CHARACTER SET
[LIKE '<em class="replaceable"><code>wild</code></em>']
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="collations-table"></a>20.10. The <code class="literal">INFORMATION_SCHEMA COLLATIONS</code> Table</h2></div></div></div><a class="indexterm" name="id4842989"></a><p>
The <a href="information-schema.html#collations-table" title="20.10. The INFORMATION_SCHEMA COLLATIONS Table"><code class="literal">COLLATIONS</code></a> table provides
information about collations for each character set.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">COLLATION_NAME</code></td><td><code class="literal">Collation</code></td><td> </td></tr><tr><td><code class="literal">CHARACTER_SET_NAME</code></td><td><code class="literal">Charset</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">ID</code></td><td><code class="literal">Id</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">IS_DEFAULT</code></td><td><code class="literal">Default</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">IS_COMPILED</code></td><td><code class="literal">Compiled</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">SORTLEN</code></td><td><code class="literal">Sortlen</code></td><td>MySQL extension</td></tr></tbody></table></div><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">COLLATION_NAME</code> is the collation name.
</p></li><li><p>
<code class="literal">CHARACTER_SET_NAME</code> is the name of the
character set with which the collation is associated.
</p></li><li><p>
<code class="literal">ID</code> is the collation ID.
</p></li><li><p>
<code class="literal">IS_DEFAULT</code> indicates whether the collation
is the default for its character set.
</p></li><li><p>
<code class="literal">IS_COMPILED</code> indicates whether the character
set is compiled into the server.
</p></li><li><p>
<code class="literal">SORTLEN</code> is related to the amount of memory
required to sort strings expressed in the character set.
</p></li></ul></div><p>
Collation information is also available from the
<a href="sql-syntax.html#show-collation" title="12.5.5.5. SHOW COLLATION Syntax"><code class="literal">SHOW COLLATION</code></a> statement. The
following statements are equivalent:
</p><pre class="programlisting">SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS
[WHERE collation_name LIKE '<em class="replaceable"><code>wild</code></em>']
SHOW COLLATION
[LIKE '<em class="replaceable"><code>wild</code></em>']
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="collation-character-set-applicability-table"></a>20.11. The <code class="literal">INFORMATION_SCHEMA
COLLATION_CHARACTER_SET_APPLICABILITY</code> Table</h2></div></div></div><a class="indexterm" name="id4843312"></a><p>
The
<a href="information-schema.html#collation-character-set-applicability-table" title="20.11. The INFORMATION_SCHEMA
COLLATION_CHARACTER_SET_APPLICABILITY Table"><code class="literal">COLLATION_CHARACTER_SET_APPLICABILITY</code></a>
table indicates what character set is applicable for what
collation. The columns are equivalent to the first two display
fields that we get from <a href="sql-syntax.html#show-collation" title="12.5.5.5. SHOW COLLATION Syntax"><code class="literal">SHOW
COLLATION</code></a>.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">COLLATION_NAME</code></td><td><code class="literal">Collation</code></td><td> </td></tr><tr><td><code class="literal">CHARACTER_SET_NAME</code></td><td><code class="literal">Charset</code></td><td> </td></tr></tbody></table></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="table-constraints-table"></a>20.12. The <code class="literal">INFORMATION_SCHEMA TABLE_CONSTRAINTS</code> Table</h2></div></div></div><a class="indexterm" name="id4843454"></a><p>
The <a href="information-schema.html#table-constraints-table" title="20.12. The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table"><code class="literal">TABLE_CONSTRAINTS</code></a> table describes
which tables have constraints.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">CONSTRAINT_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">CONSTRAINT_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">CONSTRAINT_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">TABLE_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">TABLE_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">CONSTRAINT_TYPE</code></td><td> </td><td> </td></tr></tbody></table></div><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The <code class="literal">CONSTRAINT_TYPE</code> value can be
<code class="literal">UNIQUE</code>, <code class="literal">PRIMARY KEY</code>, or
<code class="literal">FOREIGN KEY</code>.
</p></li><li><p>
The <code class="literal">UNIQUE</code> and <code class="literal">PRIMARY
KEY</code> information is about the same as what you get
from the <code class="literal">Key_name</code> field in the output from
<a href="sql-syntax.html#show-index" title="12.5.5.23. SHOW INDEX Syntax"><code class="literal">SHOW INDEX</code></a> when the
<code class="literal">Non_unique</code> field is <code class="literal">0</code>.
</p></li><li><p>
The <code class="literal">CONSTRAINT_TYPE</code> column can contain one
of these values: <code class="literal">UNIQUE</code>, <code class="literal">PRIMARY
KEY</code>, <code class="literal">FOREIGN KEY</code>,
<code class="literal">CHECK</code>. This is a
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a> (not
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a>) column. The
<code class="literal">CHECK</code> value is not available until we
support <code class="literal">CHECK</code>.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="key-column-usage-table"></a>20.13. The <code class="literal">INFORMATION_SCHEMA KEY_COLUMN_USAGE</code> Table</h2></div></div></div><a class="indexterm" name="id4843791"></a><p>
The <a href="information-schema.html#key-column-usage-table" title="20.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table"><code class="literal">KEY_COLUMN_USAGE</code></a> table describes
which key columns have constraints.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">CONSTRAINT_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">CONSTRAINT_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">CONSTRAINT_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">TABLE_CATALOG</code></td><td> </td><td> </td></tr><tr><td><code class="literal">TABLE_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">TABLE_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">COLUMN_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">ORDINAL_POSITION</code></td><td> </td><td> </td></tr><tr><td><code class="literal">POSITION_IN_UNIQUE_CONSTRAINT</code></td><td> </td><td> </td></tr><tr><td><code class="literal">REFERENCED_TABLE_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">REFERENCED_TABLE_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">REFERENCED_COLUMN_NAME</code></td><td> </td><td> </td></tr></tbody></table></div><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
If the constraint is a foreign key, then this is the column of
the foreign key, not the column that the foreign key
references.
</p></li><li><p>
The value of <code class="literal">ORDINAL_POSITION</code> is the
column's position within the constraint, not the column's
position within the table. Column positions are numbered
beginning with 1.
</p></li><li><p>
The value of <code class="literal">POSITION_IN_UNIQUE_CONSTRAINT</code>
is <code class="literal">NULL</code> for unique and primary-key
constraints. For foreign-key constraints, it is the ordinal
position in key of the table that is being referenced.
</p><p>
For example, suppose that there are two tables name
<code class="literal">t1</code> and <code class="literal">t3</code> that have the
following definitions:
</p><pre class="programlisting">CREATE TABLE t1
(
s1 INT,
s2 INT,
s3 INT,
PRIMARY KEY(s3)
) ENGINE=InnoDB;
CREATE TABLE t3
(
s1 INT,
s2 INT,
s3 INT,
KEY(s1),
CONSTRAINT CO FOREIGN KEY (s2) REFERENCES t1(s3)
) ENGINE=InnoDB;
</pre><p>
For those two tables, the
<a href="information-schema.html#key-column-usage-table" title="20.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table"><code class="literal">KEY_COLUMN_USAGE</code></a> table has two
rows:
</p><div class="itemizedlist"><ul type="circle"><li><p>
One row with <code class="literal">CONSTRAINT_NAME</code> =
<code class="literal">'PRIMARY'</code>,
<code class="literal">TABLE_NAME</code> = <code class="literal">'t1'</code>,
<code class="literal">COLUMN_NAME</code> = <code class="literal">'s3'</code>,
<code class="literal">ORDINAL_POSITION</code> =
<code class="literal">1</code>,
<code class="literal">POSITION_IN_UNIQUE_CONSTRAINT</code> =
<code class="literal">NULL</code>.
</p></li><li><p>
One row with <code class="literal">CONSTRAINT_NAME</code> =
<code class="literal">'CO'</code>, <code class="literal">TABLE_NAME</code> =
<code class="literal">'t3'</code>, <code class="literal">COLUMN_NAME</code> =
<code class="literal">'s2'</code>,
<code class="literal">ORDINAL_POSITION</code> =
<code class="literal">1</code>,
<code class="literal">POSITION_IN_UNIQUE_CONSTRAINT</code> =
<code class="literal">1</code>.
</p></li></ul></div></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="routines-table"></a>20.14. The <code class="literal">INFORMATION_SCHEMA ROUTINES</code> Table</h2></div></div></div><a class="indexterm" name="id4844288"></a><p>
The <a href="information-schema.html#routines-table" title="20.14. The INFORMATION_SCHEMA ROUTINES Table"><code class="literal">ROUTINES</code></a> table provides
information about stored routines (both procedures and functions).
The <a href="information-schema.html#routines-table" title="20.14. The INFORMATION_SCHEMA ROUTINES Table"><code class="literal">ROUTINES</code></a> table does not include
user-defined functions (UDFs) at this time.
</p><p>
The column named “<span class="quote"><code class="literal">mysql.proc</code> name</span>”
indicates the <code class="literal">mysql.proc</code> table column that
corresponds to the
<a href="information-schema.html#routines-table" title="20.14. The INFORMATION_SCHEMA ROUTINES Table"><code class="literal">INFORMATION_SCHEMA.ROUTINES</code></a> table
column, if any.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><code class="literal">mysql.proc</code> Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">SPECIFIC_NAME</code></td><td><code class="literal">specific_name</code></td><td> </td></tr><tr><td><code class="literal">ROUTINE_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">ROUTINE_SCHEMA</code></td><td><code class="literal">db</code></td><td> </td></tr><tr><td><code class="literal">ROUTINE_NAME</code></td><td><code class="literal">name</code></td><td> </td></tr><tr><td><code class="literal">ROUTINE_TYPE</code></td><td><code class="literal">type</code></td><td><code class="literal">{PROCEDURE|FUNCTION}</code></td></tr><tr><td><code class="literal">DTD_IDENTIFIER</code></td><td> </td><td>data type descriptor</td></tr><tr><td><code class="literal">ROUTINE_BODY</code></td><td> </td><td><code class="literal">SQL</code></td></tr><tr><td><code class="literal">ROUTINE_DEFINITION</code></td><td><code class="literal">body</code></td><td> </td></tr><tr><td><code class="literal">EXTERNAL_NAME</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">EXTERNAL_LANGUAGE</code></td><td><code class="literal">language</code></td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">PARAMETER_STYLE</code></td><td> </td><td><code class="literal">SQL</code></td></tr><tr><td><code class="literal">IS_DETERMINISTIC</code></td><td><code class="literal">is_deterministic</code></td><td> </td></tr><tr><td><code class="literal">SQL_DATA_ACCESS</code></td><td><code class="literal">sql_data_access</code></td><td> </td></tr><tr><td><code class="literal">SQL_PATH</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">SECURITY_TYPE</code></td><td><code class="literal">security_type</code></td><td> </td></tr><tr><td><code class="literal">CREATED</code></td><td><code class="literal">created</code></td><td> </td></tr><tr><td><code class="literal">LAST_ALTERED</code></td><td><code class="literal">modified</code></td><td> </td></tr><tr><td><code class="literal">SQL_MODE</code></td><td><a href="server-administration.html#sysvar_sql_mode"><code class="literal">sql_mode</code></a></td><td>MySQL extension</td></tr><tr><td><code class="literal">ROUTINE_COMMENT</code></td><td><code class="literal">comment</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">DEFINER</code></td><td><code class="literal">definer</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">CHARACTER_SET_CLIENT</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">COLLATION_CONNECTION</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">DATABASE_COLLATION</code></td><td> </td><td>MySQL extension</td></tr></tbody></table></div><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
MySQL calculates <code class="literal">EXTERNAL_LANGUAGE</code> thus:
</p><div class="itemizedlist"><ul type="circle"><li><p>
If <code class="literal">mysql.proc.language='SQL'</code>,
<code class="literal">EXTERNAL_LANGUAGE</code> is
<code class="literal">NULL</code>
</p></li><li><p>
Otherwise, <code class="literal">EXTERNAL_LANGUAGE</code> is what is
in <code class="literal">mysql.proc.language</code>. However, we do
not have external languages yet, so it is always
<code class="literal">NULL</code>.
</p></li></ul></div></li><li><p>
<code class="literal">CHARACTER_SET_CLIENT</code> is the session value
of the <a href="server-administration.html#sysvar_character_set_client"><code class="literal">character_set_client</code></a>
system variable when the routine was created.
<code class="literal">COLLATION_CONNECTION</code> is the session value
of the <a href="server-administration.html#sysvar_collation_connection"><code class="literal">collation_connection</code></a>
system variable when the routine was created.
<code class="literal">DATABASE_COLLATION</code> is the collation of the
database with which the routine is associated. These columns
were added in MySQL 5.1.21.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="views-table"></a>20.15. The <code class="literal">INFORMATION_SCHEMA VIEWS</code> Table</h2></div></div></div><a class="indexterm" name="id4844972"></a><p>
The <a href="information-schema.html#views-table" title="20.15. The INFORMATION_SCHEMA VIEWS Table"><code class="literal">VIEWS</code></a> table provides information
about views in databases. You must have the
<a href="server-administration.html#priv_show-view"><code class="literal">SHOW VIEW</code></a> privilege to access this
table.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">TABLE_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">TABLE_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">TABLE_NAME</code></td><td> </td><td> </td></tr><tr><td><code class="literal">VIEW_DEFINITION</code></td><td> </td><td> </td></tr><tr><td><code class="literal">CHECK_OPTION</code></td><td> </td><td> </td></tr><tr><td><code class="literal">IS_UPDATABLE</code></td><td> </td><td> </td></tr><tr><td><code class="literal">DEFINER</code></td><td> </td><td> </td></tr><tr><td><code class="literal">SECURITY_TYPE</code></td><td> </td><td> </td></tr><tr><td><code class="literal">CHARACTER_SET_CLIENT</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">COLLATION_CONNECTION</code></td><td> </td><td>MySQL extension</td></tr></tbody></table></div><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The <code class="literal">VIEW_DEFINITION</code> column has most of what
you see in the <code class="literal">Create Table</code> field that
<a href="sql-syntax.html#show-create-view" title="12.5.5.14. SHOW CREATE VIEW Syntax"><code class="literal">SHOW CREATE VIEW</code></a> produces. Skip
the words before <a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> and
skip the words <code class="literal">WITH CHECK OPTION</code>. Suppose
that the original statement was:
</p><pre class="programlisting">CREATE VIEW v AS
SELECT s2,s1 FROM t
WHERE s1 > 5
ORDER BY s1
WITH CHECK OPTION;
</pre><p>
Then the view definition looks like this:
</p><pre class="programlisting">SELECT s2,s1 FROM t WHERE s1 > 5 ORDER BY s1
</pre></li><li><p>
The <code class="literal">CHECK_OPTION</code> column has a value of
<code class="literal">NONE</code>, <code class="literal">CASCADE</code>, or
<code class="literal">LOCAL</code>.
</p></li><li><p>
MySQL sets a flag, called the view updatability flag, at
<a href="sql-syntax.html#create-view" title="12.1.20. CREATE VIEW Syntax"><code class="literal">CREATE VIEW</code></a> time. The flag is
set to <code class="literal">YES</code> (true) if
<a href="sql-syntax.html#update" title="12.2.11. UPDATE Syntax"><code class="literal">UPDATE</code></a> and
<a href="sql-syntax.html#delete" title="12.2.2. DELETE Syntax"><code class="literal">DELETE</code></a> (and similar operations)
are legal for the view. Otherwise, the flag is set to
<code class="literal">NO</code> (false). The
<code class="literal">IS_UPDATABLE</code> column in the
<a href="information-schema.html#views-table" title="20.15. The INFORMATION_SCHEMA VIEWS Table"><code class="literal">VIEWS</code></a> table displays the status
of this flag. It means that the server always knows whether a
view is updatable. If the view is not updatable, statements
such <a href="sql-syntax.html#update" title="12.2.11. UPDATE Syntax"><code class="literal">UPDATE</code></a>,
<a href="sql-syntax.html#delete" title="12.2.2. DELETE Syntax"><code class="literal">DELETE</code></a>, and
<a href="sql-syntax.html#insert" title="12.2.5. INSERT Syntax"><code class="literal">INSERT</code></a> are illegal and will be
rejected. (Note that even if a view is updatable, it might not
be possible to insert into it; for details, refer to
<a href="sql-syntax.html#create-view" title="12.1.20. CREATE VIEW Syntax">Section 12.1.20, “<code class="literal">CREATE VIEW</code> Syntax”</a>.)
</p></li><li><p>
The <code class="literal">DEFINER</code> column indicates who defined
the view. <code class="literal">SECURITY_TYPE</code> has a value of
<code class="literal">DEFINER</code> or <code class="literal">INVOKER</code>.
</p></li><li><p>
<code class="literal">CHARACTER_SET_CLIENT</code> is the session value
of the <a href="server-administration.html#sysvar_character_set_client"><code class="literal">character_set_client</code></a>
system variable when the view was created.
<code class="literal">COLLATION_CONNECTION</code> is the session value
of the <a href="server-administration.html#sysvar_collation_connection"><code class="literal">collation_connection</code></a>
system variable when the view was created. These columns were
added in MySQL 5.1.21.
</p></li></ul></div><p>
MySQL lets you use different
<a href="server-administration.html#sysvar_sql_mode"><code class="literal">sql_mode</code></a> settings to tell the
server the type of SQL syntax to support. For example, you might
use the <a href="server-administration.html#sqlmode_ansi"><code class="literal">ANSI</code></a> SQL mode to ensure
MySQL correctly interprets the standard SQL concatenation
operator, the double bar (<code class="literal">||</code>), in your queries.
If you then create a view that concatenates items, you might worry
that changing the <a href="server-administration.html#sysvar_sql_mode"><code class="literal">sql_mode</code></a>
setting to a value different from
<a href="server-administration.html#sqlmode_ansi"><code class="literal">ANSI</code></a> could cause the view to
become invalid. But this is not the case. No matter how you write
out a view definition, MySQL always stores it the same way, in a
canonical form. Here is an example that shows how the server
changes a double bar concatenation operator to a
<a href="functions.html#function_concat"><code class="literal">CONCAT()</code></a> function:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET sql_mode = 'ANSI';</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>CREATE VIEW test.v AS SELECT 'a' || 'b' as col1;</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS</code></strong>
-> <strong class="userinput"><code>WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';</code></strong>
+----------------------------------+
| VIEW_DEFINITION |
+----------------------------------+
| select concat('a','b') AS `col1` |
+----------------------------------+
1 row in set (0.00 sec)
</pre><p>
The advantage of storing a view definition in canonical form is
that changes made later to the value of
<a href="server-administration.html#sysvar_sql_mode"><code class="literal">sql_mode</code></a> will not affect the
results from the view. However an additional consequence is that
comments prior to <a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> are
stripped from the definition by the server.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="triggers-table"></a>20.16. The <code class="literal">INFORMATION_SCHEMA TRIGGERS</code> Table</h2></div></div></div><a class="indexterm" name="id4845576"></a><p>
The <a href="information-schema.html#triggers-table" title="20.16. The INFORMATION_SCHEMA TRIGGERS Table"><code class="literal">TRIGGERS</code></a> table provides
information about triggers. You must have the
<a href="server-administration.html#priv_trigger"><code class="literal">TRIGGER</code></a> privilege to access this
table (prior to MySQL 5.1.22, you must have the
<a href="server-administration.html#priv_super"><code class="literal">SUPER</code></a> privilege).
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">TRIGGER_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">TRIGGER_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">TRIGGER_NAME</code></td><td><code class="literal">Trigger</code></td><td> </td></tr><tr><td><code class="literal">EVENT_MANIPULATION</code></td><td><code class="literal">Event</code></td><td> </td></tr><tr><td><code class="literal">EVENT_OBJECT_CATALOG</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">EVENT_OBJECT_SCHEMA</code></td><td> </td><td> </td></tr><tr><td><code class="literal">EVENT_OBJECT_TABLE</code></td><td><code class="literal">Table</code></td><td> </td></tr><tr><td><code class="literal">ACTION_ORDER</code></td><td> </td><td><code class="literal">0</code></td></tr><tr><td><code class="literal">ACTION_CONDITION</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">ACTION_STATEMENT</code></td><td><code class="literal">Statement</code></td><td> </td></tr><tr><td><code class="literal">ACTION_ORIENTATION</code></td><td> </td><td><code class="literal">ROW</code></td></tr><tr><td><code class="literal">ACTION_TIMING</code></td><td><code class="literal">Timing</code></td><td> </td></tr><tr><td><code class="literal">ACTION_REFERENCE_OLD_TABLE</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">ACTION_REFERENCE_NEW_TABLE</code></td><td> </td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">ACTION_REFERENCE_OLD_ROW</code></td><td> </td><td><code class="literal">OLD</code></td></tr><tr><td><code class="literal">ACTION_REFERENCE_NEW_ROW</code></td><td> </td><td><code class="literal">NEW</code></td></tr><tr><td><code class="literal">CREATED</code></td><td> </td><td><code class="literal">NULL</code> (<code class="literal">0</code>)</td></tr><tr><td><code class="literal">SQL_MODE</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">DEFINER</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">CHARACTER_SET_CLIENT</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">COLLATION_CONNECTION</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">DATABASE_COLLATION</code></td><td> </td><td>MySQL extension</td></tr></tbody></table></div><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The <code class="literal">TRIGGER_SCHEMA</code> and
<code class="literal">TRIGGER_NAME</code> columns contain the name of
the database in which the trigger occurs and the trigger name,
respectively.
</p></li><li><p>
The <code class="literal">EVENT_MANIPULATION</code> column contains one
of the values <code class="literal">'INSERT'</code>,
<code class="literal">'DELETE'</code>, or <code class="literal">'UPDATE'</code>.
</p></li><li><p>
As noted in <a href="stored-programs-views.html#triggers" title="19.3. Using Triggers">Section 19.3, “Using Triggers”</a>, every trigger is
associated with exactly one table. The
<code class="literal">EVENT_OBJECT_SCHEMA</code> and
<code class="literal">EVENT_OBJECT_TABLE</code> columns contain the
database in which this table occurs, and the table's name.
</p></li><li><p>
The <code class="literal">ACTION_ORDER</code> statement contains the
ordinal position of the trigger's action within the list of
all similar triggers on the same table. Currently, this value
is always <code class="literal">0</code>, because it is not possible to
have more than one trigger with the same
<code class="literal">EVENT_MANIPULATION</code> and
<code class="literal">ACTION_TIMING</code> on the same table.
</p></li><li><p>
The <code class="literal">ACTION_STATEMENT</code> column contains the
statement to be executed when the trigger is invoked. This is
the same as the text displayed in the
<code class="literal">Statement</code> column of the output from
<a href="sql-syntax.html#show-triggers" title="12.5.5.40. SHOW TRIGGERS Syntax"><code class="literal">SHOW TRIGGERS</code></a>. Note that this
text uses UTF-8 encoding.
</p></li><li><p>
The <code class="literal">ACTION_ORIENTATION</code> column always
contains the value <code class="literal">'ROW'</code>.
</p></li><li><p>
The <code class="literal">ACTION_TIMING</code> column contains one of
the two values <code class="literal">'BEFORE'</code> or
<code class="literal">'AFTER'</code>.
</p></li><li><p>
The columns <code class="literal">ACTION_REFERENCE_OLD_ROW</code> and
<code class="literal">ACTION_REFERENCE_NEW_ROW</code> contain the old
and new column identifiers, respectively. This means that
<code class="literal">ACTION_REFERENCE_OLD_ROW</code> always contains
the value <code class="literal">'OLD'</code> and
<code class="literal">ACTION_REFERENCE_NEW_ROW</code> always contains
the value <code class="literal">'NEW'</code>.
</p></li><li><p>
The <code class="literal">SQL_MODE</code> column shows the server SQL
mode that was in effect at the time when the trigger was
created (and thus which remains in effect for this trigger
whenever it is invoked, <span class="emphasis"><em>regardless of the current
server SQL mode</em></span>). The possible range of values for
this column is the same as that of the
<a href="server-administration.html#sysvar_sql_mode"><code class="literal">sql_mode</code></a> system variable. See
<a href="server-administration.html#server-sql-mode" title="5.1.8. Server SQL Modes">Section 5.1.8, “Server SQL Modes”</a>.
</p></li><li><p>
The <code class="literal">DEFINER</code> column was added in MySQL
5.1.2. <code class="literal">DEFINER</code> indicates who defined the
trigger.
</p></li><li><p>
<code class="literal">CHARACTER_SET_CLIENT</code> is the session value
of the <a href="server-administration.html#sysvar_character_set_client"><code class="literal">character_set_client</code></a>
system variable when the trigger was created.
<code class="literal">COLLATION_CONNECTION</code> is the session value
of the <a href="server-administration.html#sysvar_collation_connection"><code class="literal">collation_connection</code></a>
system variable when the trigger was created.
<code class="literal">DATABASE_COLLATION</code> is the collation of the
database with which the trigger is associated. These columns
were added in MySQL 5.1.21.
</p></li><li><p>
The following columns currently always contain
<code class="literal">NULL</code>: <code class="literal">TRIGGER_CATALOG</code>,
<code class="literal">EVENT_OBJECT_CATALOG</code>,
<code class="literal">ACTION_CONDITION</code>,
<code class="literal">ACTION_REFERENCE_OLD_TABLE</code>,
<code class="literal">ACTION_REFERENCE_NEW_TABLE</code>, and
<code class="literal">CREATED</code>.
</p></li></ul></div><p>
Example, using the <code class="literal">ins_sum</code> trigger defined in
<a href="stored-programs-views.html#triggers" title="19.3. Using Triggers">Section 19.3, “Using Triggers”</a>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM INFORMATION_SCHEMA.TRIGGERS\G</code></strong>
*************************** 1. row ***************************
TRIGGER_CATALOG: NULL
TRIGGER_SCHEMA: test
TRIGGER_NAME: ins_sum
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: NULL
EVENT_OBJECT_SCHEMA: test
EVENT_OBJECT_TABLE: account
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: SET @sum = @sum + NEW.amount
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: NULL
SQL_MODE:
DEFINER: me@localhost
</pre><p>
See also <a href="sql-syntax.html#show-triggers" title="12.5.5.40. SHOW TRIGGERS Syntax">Section 12.5.5.40, “<code class="literal">SHOW TRIGGERS</code> Syntax”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="plugins-table"></a>20.17. The <code class="literal">INFORMATION_SCHEMA PLUGINS</code> Table</h2></div></div></div><a class="indexterm" name="id4846521"></a><p>
The <a href="information-schema.html#plugins-table" title="20.17. The INFORMATION_SCHEMA PLUGINS Table"><code class="literal">PLUGINS</code></a> table provides
information about server plugins.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">PLUGIN_NAME</code></td><td><code class="literal">Name</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">PLUGIN_VERSION</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">PLUGIN_STATUS</code></td><td><code class="literal">Status</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">PLUGIN_TYPE</code></td><td><code class="literal">Type</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">PLUGIN_TYPE_VERSION</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">PLUGIN_LIBRARY</code></td><td><code class="literal">Library</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">PLUGIN_LIBRARY_VERSION</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">PLUGIN_AUTHOR</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">PLUGIN_DESCRIPTION</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">PLUGIN_LICENSE</code></td><td> </td><td>MySQL extension</td></tr></tbody></table></div><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The <a href="information-schema.html#plugins-table" title="20.17. The INFORMATION_SCHEMA PLUGINS Table"><code class="literal">PLUGINS</code></a> table is a
nonstandard table. It was added in MySQL 5.1.5.
</p></li><li><p>
The <code class="literal">PLUGIN_LICENSE</code> column was added in
MySQL 5.1.12.
</p></li></ul></div><p>
See also <a href="sql-syntax.html#show-plugins" title="12.5.5.27. SHOW PLUGINS Syntax">Section 12.5.5.27, “<code class="literal">SHOW PLUGINS</code> Syntax”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="engines-table"></a>20.18. The <code class="literal">INFORMATION_SCHEMA ENGINES</code> Table</h2></div></div></div><a class="indexterm" name="id4846840"></a><p>
The <a href="information-schema.html#plugins-table" title="20.17. The INFORMATION_SCHEMA PLUGINS Table"><code class="literal">PLUGINS</code></a> table provides
information about storage engines.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">ENGINE</code></td><td><code class="literal">Engine</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">SUPPORT</code></td><td><code class="literal">Support</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">COMMENT</code></td><td><code class="literal">Comment</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">TRANSACTIONS</code></td><td><code class="literal">Transactions</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">XA</code></td><td><code class="literal">XA</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">SAVEPOINTS</code></td><td><code class="literal">Savepoints</code></td><td>MySQL extension</td></tr></tbody></table></div><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The <a href="information-schema.html#engines-table" title="20.18. The INFORMATION_SCHEMA ENGINES Table"><code class="literal">ENGINES</code></a> table is a
nonstandard table. It was added in MySQL 5.1.5.
</p></li></ul></div><p>
See also <a href="sql-syntax.html#show-engines" title="12.5.5.17. SHOW ENGINES Syntax">Section 12.5.5.17, “<code class="literal">SHOW ENGINES</code> Syntax”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="partitions-table"></a>20.19. The <code class="literal">INFORMATION_SCHEMA PARTITIONS</code> Table</h2></div></div></div><a class="indexterm" name="id4847092"></a><p>
The <a href="information-schema.html#partitions-table" title="20.19. The INFORMATION_SCHEMA PARTITIONS Table"><code class="literal">PARTITIONS</code></a> table provides
information about table partitions. See
<a href="partitioning.html" title="Chapter 18. Partitioning">Chapter 18, <i>Partitioning</i></a>, for more information about
partitioning tables.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">TABLE_CATALOG</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">TABLE_SCHEMA</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">TABLE_NAME</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">PARTITION_NAME</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">SUBPARTITION_NAME</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">PARTITION_ORDINAL_POSITION</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">SUBPARTITION_ORDINAL_POSITION</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">PARTITION_METHOD</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">SUBPARTITION_METHOD</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">PARTITION_EXPRESSION</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">SUBPARTITION_EXPRESSION</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">PARTITION_DESCRIPTION</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">TABLE_ROWS</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">AVG_ROW_LENGTH</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">DATA_LENGTH</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">MAX_DATA_LENGTH</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">INDEX_LENGTH</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">DATA_FREE</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">CREATE_TIME</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">UPDATE_TIME</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">CHECK_TIME</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">CHECKSUM</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">PARTITION_COMMENT</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">NODEGROUP</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">TABLESPACE_NAME</code></td><td> </td><td>MySQL extension</td></tr></tbody></table></div><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The <a href="information-schema.html#partitions-table" title="20.19. The INFORMATION_SCHEMA PARTITIONS Table"><code class="literal">PARTITIONS</code></a> table is a
nonstandard table. It was added in MySQL 5.1.6.
</p><p>
Each record in this table corresponds to an individual
partition or subpartition of a partitioned table.
</p></li><li><p>
<code class="literal">TABLE_CATALOG</code>: This column is always
<code class="literal">NULL</code>.
</p></li><li><p>
<code class="literal">TABLE_SCHEMA</code>: This column contains the name
of the database to which the table belongs.
</p></li><li><p>
<code class="literal">TABLE_NAME</code>: This column contains the name
of the table containing the partition.
</p></li><li><p>
<code class="literal">PARTITION_NAME</code>: The name of the partition.
</p></li><li><p>
<code class="literal">SUBPARTITION_NAME</code>: If the
<a href="information-schema.html#partitions-table" title="20.19. The INFORMATION_SCHEMA PARTITIONS Table"><code class="literal">PARTITIONS</code></a> table record
represents a subpartition, then this column contains the name
of subpartition; otherwise it is <code class="literal">NULL</code>.
</p></li><li><p>
<code class="literal">PARTITION_ORDINAL_POSITION</code>: All partitions
are indexed in the same order as they are defined, with
<code class="literal">1</code> being the number assigned to the first
partition. The indexing can change as partitions are added,
dropped, and reorganized; the number shown is this column
reflects the current order, taking into account any indexing
changes.
</p></li><li><p>
<code class="literal">SUBPARTITION_ORDINAL_POSITION</code>:
Subpartitions within a given partition are also indexed and
reindexed in the same manner as partitions are indexed within
a table.
</p></li><li><p>
<code class="literal">PARTITION_METHOD</code>: One of the values
<code class="literal">RANGE</code>, <code class="literal">LIST</code>,
<code class="literal">HASH</code>, <code class="literal">LINEAR HASH</code>,
<code class="literal">KEY</code>, or <code class="literal">LINEAR KEY</code>; that
is, one of the available partitioning types as discussed in
<a href="partitioning.html#partitioning-types" title="18.2. Partition Types">Section 18.2, “Partition Types”</a>.
</p></li><li><p>
<code class="literal">SUBPARTITION_METHOD</code>: One of the values
<code class="literal">HASH</code>, <code class="literal">LINEAR HASH</code>,
<code class="literal">KEY</code>, or <code class="literal">LINEAR KEY</code>; that
is, one of the available subpartitioning types as discussed in
<a href="partitioning.html#partitioning-subpartitions" title="18.2.5. Subpartitioning">Section 18.2.5, “Subpartitioning”</a>.
</p></li><li><p>
<code class="literal">PARTITION_EXPRESSION</code>: This is the
expression for the partitioning function used in the
<a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> or
<a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> statement that
created the table's current partitioning scheme.
</p><p>
For example, consider a partitioned table created in the
<code class="literal">test</code> database using this statement:
</p><pre class="programlisting">CREATE TABLE tp (
c1 INT,
c2 INT,
c3 VARCHAR(25)
)
PARTITION BY HASH(c1 + c2)
PARTITIONS 4;
</pre><p>
The <code class="literal">PARTITION_EXPRESSION</code> column in a
PARTITIONS table record for a partition from this table
displays <code class="literal">c1 + c2</code>, as shown here:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT DISTINCT PARTITION_EXPRESSION</code></strong>
> <strong class="userinput"><code>FROM INFORMATION_SCHEMA.PARTITIONS</code></strong>
> <strong class="userinput"><code>WHERE TABLE_NAME='tp' AND TABLE_SCHEMA='test';</code></strong>
+----------------------+
| PARTITION_EXPRESSION |
+----------------------+
| c1 + c2 |
+----------------------+
1 row in set (0.09 sec)
</pre></li><li><p>
<code class="literal">SUBPARTITION_EXPRESSION</code>: This works in the
same fashion for the subpartitioning expression that defines
the subpartitioning for a table as
<code class="literal">PARTITION_EXPRESSION</code> does for the
partitioning expression used to define a table's partitioning.
</p><p>
If the table has no subpartitions, then this column is
<code class="literal">NULL</code>.
</p></li><li><p>
<code class="literal">PARTITION_DESCRIPTION</code>: This column is used
for RANGE and LIST partitions. For a <code class="literal">RANGE</code>
partition, it contains the value set in the partition's
<code class="literal">VALUES LESS THAN</code> clause, which can be
either an integer or <code class="literal">MAXVALUE</code>. For a
<code class="literal">LIST</code> partition, this column contains the
values defined in the partition's <code class="literal">VALUES IN</code>
clause, which is a comma-separated list of integer values.
</p><p>
For partitions whose <code class="literal">PARTITION_METHOD</code> is
other than <code class="literal">RANGE</code> or
<code class="literal">LIST</code>, this column is always
<code class="literal">NULL</code>.
</p></li><li><p>
<code class="literal">TABLE_ROWS</code>: The number of table rows in the
partition.
</p><p>
For partitioned <code class="literal">InnoDB</code> tables,
the row count given in the <code class="literal">TABLE_ROWS</code>
column is only an estimated value used in SQL optimization,
and may not always be exact.
</p></li><li><p>
<code class="literal">AVG_ROW_LENGTH</code>: The average length of the
rows stored in this partition or subpartition, in bytes.
</p><p>
This is the same as <code class="literal">DATA_LENGTH</code> divided by
<code class="literal">TABLE_ROWS</code>.
</p></li><li><p>
<code class="literal">DATA_LENGTH</code>: The total length of all rows
stored in this partition or subpartition, in bytes —
that is, the total number of bytes stored in the partition or
subpartition.
</p></li><li><p>
<code class="literal">MAX_DATA_LENGTH</code>: The maximum number of
bytes that can be stored in this partition or subpartition.
</p></li><li><p>
<code class="literal">INDEX_LENGTH</code>: The length of the index file
for this partition or subpartition, in bytes.
</p></li><li><p>
<code class="literal">DATA_FREE</code>: The number of bytes allocated to
the partition or subpartition but not used.
</p></li><li><p>
<code class="literal">CREATE_TIME</code>: The time of the partition's or
subpartition's creation.
</p></li><li><p>
<code class="literal">UPDATE_TIME</code>: The time that the partition or
subpartition was last modified.
</p></li><li><p>
<code class="literal">CHECK_TIME</code>: The last time that the table to
which this partition or subpartition belongs was checked.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
Some storage engines do not update this time; for tables
using these storage engines, this value is always
<code class="literal">NULL</code>.
</p></div></li><li><p>
<code class="literal">CHECKSUM</code>: The checksum value, if any;
otherwise, this column is <code class="literal">NULL</code>.
</p></li><li><p>
<code class="literal">PARTITION_COMMENT</code>: This column contains the
text of any comment made for the partition.
</p><p>
The default value for this column is an empty string.
</p></li><li><p>
<code class="literal">NODEGROUP</code>: This is the nodegroup to which
the partition belongs. This is relevant only to MySQL Cluster
tables; otherwise the value of this column is always
<code class="literal">0</code>.
</p></li><li><p>
<code class="literal">TABLESPACE_NAME</code>: This column contains the
name of tablespace to which the partition belongs. In MySQL
5.1, the value of this column is always
<code class="literal">DEFAULT</code>.
</p></li><li><div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Important</h3><p>
If any partitioned tables created in a MySQL version prior
to MySQL 5.1.6 are present following an upgrade to MySQL
5.1.6 or later, it is not possible to
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> from,
<a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>, or
<a href="sql-syntax.html#describe" title="12.3.1. DESCRIBE Syntax"><code class="literal">DESCRIBE</code></a> the
<a href="information-schema.html#partitions-table" title="20.19. The INFORMATION_SCHEMA PARTITIONS Table"><code class="literal">PARTITIONS</code></a> table. See
<a href="news.html#news-5-1-6" title="C.1.39. Changes in MySQL 5.1.6 (01 February 2006)">Section C.1.39, “Changes in MySQL 5.1.6 (01 February 2006)”</a> <span class="emphasis"><em>before</em></span>
upgrading from MySQL 5.1.5 or earlier to MySQL 5.1.6 or
later.
</p></div></li><li><p>
A nonpartitioned table has one record in
<a href="information-schema.html#partitions-table" title="20.19. The INFORMATION_SCHEMA PARTITIONS Table"><code class="literal">INFORMATION_SCHEMA.PARTITIONS</code></a>;
however, the values of the <code class="literal">PARTITION_NAME</code>,
<code class="literal">SUBPARTITION_NAME</code>,
<code class="literal">PARTITION_ORDINAL_POSITION</code>,
<code class="literal">SUBPARTITION_ORDINAL_POSITION</code>,
<code class="literal">PARTITION_METHOD</code>,
<code class="literal">SUBPARTITION_METHOD</code>,
<code class="literal">PARTITION_EXPRESSION</code>,
<code class="literal">SUBPARTITION_EXPRESSION</code>, and
<code class="literal">PARTITION_DESCRIPTION</code> columns are all
<code class="literal">NULL</code>. (The
<code class="literal">PARTITION_COMMENT</code> column in this case is
blank.)
</p><p>
In MySQL 5.1, there is also only one record in
the <a href="information-schema.html#partitions-table" title="20.19. The INFORMATION_SCHEMA PARTITIONS Table"><code class="literal">PARTITIONS</code></a> table for a table
using the <code class="literal">NDBCLUSTER</code> storage
engine. The same columns are also <code class="literal">NULL</code> (or
empty) as for a nonpartitioned table.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="events-table"></a>20.20. The <code class="literal">INFORMATION_SCHEMA EVENTS</code> Table</h2></div></div></div><a class="indexterm" name="id4848415"></a><p>
The <a href="information-schema.html#events-table" title="20.20. The INFORMATION_SCHEMA EVENTS Table"><code class="literal">EVENTS</code></a> table provides information
about scheduled events, which are discussed in
<a href="stored-programs-views.html#events" title="19.4. Using the Event Scheduler">Section 19.4, “Using the Event Scheduler”</a>.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">EVENT_CATALOG</code></td><td> </td><td><code class="literal">NULL</code>, MySQL extension</td></tr><tr><td><code class="literal">EVENT_SCHEMA</code></td><td><code class="literal">Db</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">EVENT_NAME</code></td><td><code class="literal">Name</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">DEFINER</code></td><td><code class="literal">Definer</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">TIME_ZONE</code></td><td><code class="literal">Time zone</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">EVENT_BODY</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">EVENT_DEFINITION</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">EVENT_TYPE</code></td><td><code class="literal">Type</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">EXECUTE_AT</code></td><td><code class="literal">Execute at</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">INTERVAL_VALUE</code></td><td><code class="literal">Interval value</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">INTERVAL_FIELD</code></td><td><code class="literal">Interval field</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">SQL_MODE</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">STARTS</code></td><td><code class="literal">Starts</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">ENDS</code></td><td><code class="literal">Ends</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">STATUS</code></td><td><code class="literal">Status</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">ON_COMPLETION</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">CREATED</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">LAST_ALTERED</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">LAST_EXECUTED</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">EVENT_COMMENT</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">ORIGINATOR</code></td><td>Originator</td><td>MySQL extension</td></tr><tr><td><code class="literal">CHARACTER_SET_CLIENT</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">COLLATION_CONNECTION</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">DATABASE_COLLATION</code></td><td> </td><td>MySQL extension</td></tr></tbody></table></div><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The <a href="information-schema.html#events-table" title="20.20. The INFORMATION_SCHEMA EVENTS Table"><code class="literal">EVENTS</code></a> table is a nonstandard
table. It was added in MySQL 5.1.6.
</p></li><li><p>
<code class="literal">EVENT_CATALOG</code>: The value of this column is
always <code class="literal">NULL</code>.
</p></li><li><p>
<code class="literal">EVENT_SCHEMA</code>: The name of the schema
(database) to which this event belongs.
</p></li><li><p>
<code class="literal">EVENT_NAME</code>: The name of the event.
</p></li><li><p>
<code class="literal">DEFINER</code>: The user who created the event.
Always displayed in
<code class="literal">'<em class="replaceable"><code>user_name</code></em>'@'<em class="replaceable"><code>host_name</code></em>'</code>
format.
</p></li><li><p>
<code class="literal">TIME_ZONE</code>: The time zone in effect when
schedule for the event was last modified. If the event's
schedule has not been modified since the event was created,
then this is the time zone that was in effect at the event's
creation. The default value is <code class="literal">SYSTEM</code>.
</p><p>
This column was added in MySQL 5.1.17. See
<a href="news.html#news-5-1-17" title="C.1.28. Changes in MySQL 5.1.17 (04 April 2007)">Section C.1.28, “Changes in MySQL 5.1.17 (04 April 2007)”</a>, for important information if
you are using the Event Scheduler and are upgrading from MySQL
5.1.16 (or earlier) to MySQL 5.1.17 (or later).
</p></li><li><p>
<code class="literal">EVENT_BODY</code>: The language used for the
statements in the event's <a href="sql-syntax.html#do" title="12.2.3. DO Syntax"><code class="literal">DO</code></a>
clause; in MySQL 5.1, this is always
<code class="literal">SQL</code>.
</p><p>
This column was added in MySQL 5.1.12. It is not to be
confused with the column of the same name (now named
<code class="literal">EVENT_DEFINITION</code>) that existed in earlier
MySQL versions.
</p></li><li><p>
<code class="literal">EVENT_DEFINITION</code>: The text of the SQL
statement making up the event's
<a href="sql-syntax.html#do" title="12.2.3. DO Syntax"><code class="literal">DO</code></a> clause; in other words, the
statement executed by this event.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
Prior to MySQL 5.1.12, this column was named
<code class="literal">EVENT_BODY</code>.
</p></div></li><li><p>
<code class="literal">EVENT_TYPE</code>: One of the two values
<code class="literal">ONE TIME</code> or <code class="literal">RECURRING</code>.
</p></li><li><p>
<code class="literal">EXECUTE_AT</code>: For a one-time event, this is
the <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> value specified in
the <code class="literal">AT</code> clause of the
<a href="sql-syntax.html#create-event" title="12.1.11. CREATE EVENT Syntax"><code class="literal">CREATE EVENT</code></a> statement used to
create the event, or of the last <a href="sql-syntax.html#alter-event" title="12.1.2. ALTER EVENT Syntax"><code class="literal">ALTER
EVENT</code></a> statement that modified the event. The value
shown in this column reflects the addition or subtraction of
any <code class="literal">INTERVAL</code> value included in the event's
<code class="literal">AT</code> clause. For example, if an event is
created using <code class="literal">ON SCHEDULE AT CURRENT_TIMESTAMP +
'1:6' DAY_HOUR</code>, and the event was created at
2006-02-09 14:05:30, the value shown in this column would be
<code class="literal">'2006-02-10 20:05:30'</code>.
</p><p>
If the event's timing is determined by an
<code class="literal">EVERY</code> clause instead of an
<code class="literal">AT</code> clause (that is, if the event is
recurring), the value of this column is
<code class="literal">NULL</code>.
</p></li><li><p>
<code class="literal">INTERVAL_VALUE</code>: For recurring events, this
column contains the numeric portion of the event's
<code class="literal">EVERY</code> clause.
</p><p>
For a one-time event (that is, an event whose timing is
determined by an <code class="literal">AT</code> clause), this column's
value is <code class="literal">NULL</code>.
</p></li><li><p>
<code class="literal">INTERVAL_FIELD</code>: For recurring events, this
column contains the units portion of the
<code class="literal">EVERY</code> clause governing the timing of the
event. Thus, this column contains a value such as
'<a href="data-types.html#year" title="10.3.3. The YEAR Type"><code class="literal">YEAR</code></a>',
'<code class="literal">QUARTER</code>', '<code class="literal">DAY</code>', and so
on.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
In early MySQL 5.1 releases, this value was prefixed with
'<code class="literal">INTERVAL_</code>', and was displayed as
'<code class="literal">INTERVAL_YEAR</code>',
'<code class="literal">INTERVAL_QUARTER</code>',
'<code class="literal">INTERVAL_DAY</code>', and so on.
</p></div><p>
For a one-time event (that is, an event whose timing is
determined by an <code class="literal">AT</code> clause), this column's
value is <code class="literal">NULL</code>.
</p></li><li><p>
<code class="literal">SQL_MODE</code>: The SQL mode in effect at the
time the event was created or altered.
</p></li><li><p>
<code class="literal">STARTS</code>: For a recurring event whose
definition includes a <code class="literal">STARTS</code> clause, this
column contains the corresponding
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> value. As with the
<code class="literal">EXECUTE_AT</code> column, this value resolves any
expressions used.
</p><p>
If there is no <code class="literal">STARTS</code> clause affecting the
timing of the event, this column is empty. (Prior to MySQL
5.1.8, it contained <code class="literal">NULL</code> in such cases.)
</p></li><li><p>
<code class="literal">ENDS</code>: For a recurring event whose
definition includes a <code class="literal">ENDS</code> clause, this
column contains the corresponding
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> value. As with the
<code class="literal">EXECUTE_AT</code> column (see previous example),
this value resolves any expressions used.
</p><p>
If there is no <code class="literal">ENDS</code> clause affecting the
timing of the event, this column contains
<code class="literal">NULL</code>.
</p></li><li><p>
<code class="literal">STATUS</code>: One of the three values
<code class="literal">ENABLED</code>, <code class="literal">DISABLED</code>, or
<code class="literal">SLAVESIDE_DISABLED</code>.
</p><p>
<code class="literal">SLAVESIDE_DISABLED</code> was added to the list of
possible values for this column in MySQL 5.1.18. This value
indicates that the creation of the event occurred on another
MySQL server acting as a replication master and was replicated
to the current MySQL server which is acting as a slave, but
the event is not presently being executed on the slave. See
<a href="replication.html#replication-features-invoked" title="16.3.1.8. Replication of Invoked Features">Section 16.3.1.8, “Replication of Invoked Features”</a>, for more
information.
</p></li><li><p>
<code class="literal">ON_COMPLETION</code>: One of the two values
<code class="literal">PRESERVE</code> or <code class="literal">NOT
PRESERVE</code>.
</p></li><li><p>
<code class="literal">CREATED</code>: The date and time when the event
was created. This is a <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a>
value.
</p></li><li><p>
<code class="literal">LAST_ALTERED</code>: The date and time when the
event was last modified. This is a
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> value. If the event
has not been modified since its creation, this column holds
the same value as the <code class="literal">CREATED</code> column.
</p></li><li><p>
<code class="literal">LAST_EXECUTED</code>: The date and time when the
event last executed. A <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a>
value. If the event has never executed, this column's value is
<code class="literal">NULL</code>.
</p><p>
Before MySQL 5.1.23, <code class="literal">LAST_EXECUTED</code>
indicates when event finished executing. As of 5.1.23,
<code class="literal">LAST_EXECUTED</code> instead indicates when the
event started. As a result, the <code class="literal">ENDS</code> column
is never less than <code class="literal">LAST_EXECUTED</code>.
</p></li><li><p>
<code class="literal">EVENT_COMMENT</code>: The text of a comment, if
the event has one. If there is no comment, the value of this
column is an empty string.
</p></li><li><p>
<code class="literal">ORIGINATOR</code>: The server ID of the MySQL
server on which the event was created; used in replication.
The default value is 0. This column was added in MySQL 5.1.18.
</p></li><li><p>
<code class="literal">CHARACTER_SET_CLIENT</code> is the session value
of the <a href="server-administration.html#sysvar_character_set_client"><code class="literal">character_set_client</code></a>
system variable when the event was created.
<code class="literal">COLLATION_CONNECTION</code> is the session value
of the <a href="server-administration.html#sysvar_collation_connection"><code class="literal">collation_connection</code></a>
system variable when the event was created.
<code class="literal">DATABASE_COLLATION</code> is the collation of the
database with which the event is associated. These columns
were added in MySQL 5.1.21.
</p></li></ul></div><p>
<span class="bold"><strong>Example</strong></span>: Suppose the user
<code class="literal">jon@ghidora</code> creates an event named
<code class="literal">e_daily</code>, and then modifies it a few minutes
later using an <a href="sql-syntax.html#alter-event" title="12.1.2. ALTER EVENT Syntax"><code class="literal">ALTER EVENT</code></a>
statement, as shown here:
</p><pre class="programlisting">DELIMITER |
CREATE EVENT e_daily
ON SCHEDULE
EVERY 1 DAY
COMMENT 'Saves total number of sessions then clears the table each day'
DO
BEGIN
INSERT INTO site_activity.totals (time, total)
SELECT CURRENT_TIMESTAMP, COUNT(*)
FROM site_activity.sessions;
DELETE FROM site_activity.sessions;
END |
DELIMITER ;
ALTER EVENT e_daily
ENABLED;
</pre><p>
(Note that comments can span multiple lines.)
</p><p>
This user can then run the following
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> statement, and obtain the
output shown:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM INFORMATION_SCHEMA.EVENTS</code></strong>
> <strong class="userinput"><code>WHERE EVENT_NAME = 'e_daily'</code></strong>
> <strong class="userinput"><code>AND EVENT_SCHEMA = 'myschema'\G</code></strong>
*************************** 1. row ***************************
EVENT_CATALOG: NULL
EVENT_SCHEMA: test
EVENT_NAME: e_daily
DEFINER: paul@localhost
TIME_ZONE: SYSTEM
EVENT_BODY: SQL
EVENT_DEFINITION: BEGIN
INSERT INTO site_activity.totals (time, total)
SELECT CURRENT_TIMESTAMP, COUNT(*)
FROM site_activity.sessions;
DELETE FROM site_activity.sessions;
END
EVENT_TYPE: RECURRING
EXECUTE_AT: NULL
INTERVAL_VALUE: 1
INTERVAL_FIELD: DAY
SQL_MODE:
STARTS: 2008-09-03 12:13:39
ENDS: NULL
STATUS: ENABLED
ON_COMPLETION: NOT PRESERVE
CREATED: 2008-09-03 12:13:39
LAST_ALTERED: 2008-09-03 12:13:39
LAST_EXECUTED: NULL
EVENT_COMMENT: Saves total number of sessions then clears the
table each day
ORIGINATOR: 1
CHARACTER_SET_CLIENT: latin1
COLLATION_CONNECTION: latin1_swedish_ci
DATABASE_COLLATION: latin1_swedish_ci
</pre><p>
Prior to MySQL 5.1.17, the times displayed in the
<code class="literal">STARTS</code>, <code class="literal">ENDS</code>, and
<code class="literal">LAST_EXECUTED</code> columns were given in terms of
Universal Time (GMT or UTC), regardless of the server's time zone
setting (<a href="http://bugs.mysql.com/16420" target="_top">Bug#16420</a>). Beginning with MySQL 5.1.17, these times are
all given in terms of local time as determined by the MySQL
server's <a href="server-administration.html#sysvar_time_zone"><code class="literal">time_zone</code></a> setting. (The
same was true of the <code class="literal">starts</code>,
<code class="literal">ends</code>, and <code class="literal">last_executed</code>
columns of the <code class="literal">mysql.event</code> table as well as the
<code class="literal">Starts</code> and <code class="literal">Ends</code> columns in
the output of <a href="sql-syntax.html#show-events" title="12.5.5.19. SHOW EVENTS Syntax"><code class="literal">SHOW EVENTS</code></a>.)
</p><p>
The <code class="literal">CREATED</code> and <code class="literal">LAST_ALTERED</code>
columns use the server time zone (as do the
<code class="literal">created</code> and <code class="literal">last_altered</code>
columns of the <code class="literal">mysql.event</code> table).
</p><p>
See also <a href="sql-syntax.html#show-events" title="12.5.5.19. SHOW EVENTS Syntax">Section 12.5.5.19, “<code class="literal">SHOW EVENTS</code> Syntax”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="files-table"></a>20.21. The <code class="literal">INFORMATION_SCHEMA FILES</code> Table</h2></div></div></div><a class="indexterm" name="id4849942"></a><p>
The <a href="information-schema.html#files-table" title="20.21. The INFORMATION_SCHEMA FILES Table"><code class="literal">FILES</code></a> table provides information
about the files in which MySQL <code class="literal">NDB</code>
Disk Data tables are stored.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
This table provides information about Disk Data
<span class="emphasis"><em>files</em></span> only; you cannot use it for
determining disk space allocation or availability for individual
<code class="literal">NDB</code> tables. However, beginning with MySQL
Cluster NDB 6.3.27 and MySQL Cluster NDB 7.0.8, it is possible
to see how much space is allocated for each
<code class="literal">NDB</code> table having data stored on
disk, as well as how much remains available for storage of of
data on disk for that table, using <span><strong class="command">ndb_desc</strong></span>.
For more information, see
<a href="mysql-cluster.html#mysql-cluster-programs-ndb-desc" title="17.4.9. ndb_desc — Describe NDB Tables">Section 17.4.9, “<span><strong class="command">ndb_desc</strong></span> — Describe NDB Tables”</a>.
</p></div><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">FILE_ID</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">FILE_NAME</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">FILE_TYPE</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">TABLESPACE_NAME</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">TABLE_CATALOG</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">TABLE_SCHEMA</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">TABLE_NAME</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">LOGFILE_GROUP_NAME</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">LOGFILE_GROUP_NUMBER</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">ENGINE</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">FULLTEXT_KEYS</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">DELETED_ROWS</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">UPDATE_COUNT</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">FREE_EXTENTS</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">TOTAL_EXTENTS</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">EXTENT_SIZE</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">INITIAL_SIZE</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">MAXIMUM_SIZE</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">AUTOEXTEND_SIZE</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">CREATION_TIME</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">LAST_UPDATE_TIME</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">LAST_ACCESS_TIME</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">RECOVER_TIME</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">TRANSACTION_COUNTER</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">VERSION</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">ROW_FORMAT</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">TABLE_ROWS</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">AVG_ROW_LENGTH</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">DATA_LENGTH</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">MAX_DATA_LENGTH</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">INDEX_LENGTH</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">DATA_FREE</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">CREATE_TIME</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">UPDATE_TIME</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">CHECK_TIME</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">CHECKSUM</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">STATUS</code></td><td> </td><td>MySQL extension</td></tr><tr><td><code class="literal">EXTRA</code></td><td> </td><td>MySQL extension</td></tr></tbody></table></div><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">FILE_ID</code> column values are auto-generated.
</p></li><li><p>
<code class="literal">FILE_NAME</code> is the name of an
<code class="literal">UNDO</code> log file created by <code class="literal">CREATE
LOGFILE GROUP</code> or <code class="literal">ALTER LOGFILE
GROUP</code>, or of a data file created by <code class="literal">CREATE
TABLESPACE</code> or <code class="literal">ALTER TABLESPACE</code>.
</p></li><li><p>
<code class="literal">FILE_TYPE</code> is one of the values
<code class="literal">UNDOFILE</code> or <code class="literal">DATAFILE</code>.
</p></li><li><p>
<code class="literal">TABLESPACE_NAME</code> is the name of the
tablespace with which the file is associated.
</p></li><li><p>
Currently, the value of the
<code class="literal">TABLESPACE_CATALOG</code> column is always
<code class="literal">NULL</code>.
</p></li><li><p>
<code class="literal">TABLE_NAME</code> is the name of the Disk Data
table with which the file is associated, if any.
</p></li><li><p>
The <code class="literal">LOGFILE_GROUP_NAME</code> column gives the
name of the log file group to which the log file or data file
belongs.
</p></li><li><p>
For an <code class="literal">UNDO</code> log file, the
<code class="literal">LOGFILE_GROUP_NUMBER</code> contains the
auto-generated ID number of the log file group to which the
log file belongs.
</p></li><li><p>
For a MySQL Cluster Disk Data log file or data file, the value
of the <code class="literal">ENGINE</code> column is always
<code class="literal">NDB</code> or
<code class="literal">NDBCLUSTER</code>.
</p></li><li><p>
For a MySQL Cluster Disk Data log file or data file, the value
of the <code class="literal">FULLTEXT_KEYS</code> column is always
empty.
</p></li><li><p>
The <code class="literal">FREE EXTENTS</code> column displays the number
of extents which have not yet been used by the file. The
<code class="literal">TOTAL EXTENTS</code> column show the total number
of extents allocated to the file.
</p><p>
The difference between these two columns is the number of
extents currently in use by the file:
</p><pre class="programlisting">SELECT TOTAL_EXTENTS - FREE_EXTENTS AS extents_used
FROM INFORMATION_SCHEMA.FILES
WHERE FILE_NAME = 'myfile.dat';
</pre><p>
You can approximate the amount of disk space in use by the
file by multiplying this difference by the value of the
<code class="literal">EXTENT_SIZE</code> column, which gives the size of
an extent for the file in bytes:
</p><pre class="programlisting">SELECT (TOTAL_EXTENTS - FREE_EXTENTS) * EXTENT_SIZE AS bytes_used
FROM INFORMATION_SCHEMA.FILES
WHERE FILE_NAME = 'myfile.dat';
</pre><p>
Similarly, you can estimate the amount of space that remains
available in a given file by multiplying
<code class="literal">FREE_EXTENTS</code> by
<code class="literal">EXTENT_SIZE</code>:
</p><pre class="programlisting">SELECT FREE_EXTENTS * EXTENT_SIZE AS bytes_free
FROM INFORMATION_SCHEMA.FILES
WHERE FILE_NAME = 'myfile.dat';
</pre><div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Important</h3><p>
The byte values produced by the preceding queries are
approximations only, and their precision is inversely
proportional to the value of <code class="literal">EXTENT_SIZE</code>.
That is, the larger <code class="literal">EXTENT_SIZE</code> becomes,
the less accurate the approximations are.
</p></div><p>
It is also important to remember that once an extent is used,
it cannot be freed again without dropping the data file of
which it is a part. This means that deletes from a Disk Data
table do <span class="emphasis"><em>not</em></span> release disk space.
</p><p>
The extent size can be set in a <code class="literal">CREATE
TABLESPACE</code> statement. See
<a href="sql-syntax.html#create-tablespace" title="12.1.18. CREATE TABLESPACE Syntax">Section 12.1.18, “<code class="literal">CREATE TABLESPACE</code> Syntax”</a>, for more information.
</p></li><li><p>
The <code class="literal">INITIAL_SIZE</code> column shows the size in
bytes of the file. This is the same value that was used in the
<code class="literal">INITIAL_SIZE</code> clause of the <code class="literal">CREATE
LOGFILE GROUP</code>, <code class="literal">ALTER LOGFILE
GROUP</code>, <code class="literal">CREATE TABLESPACE</code>, or
<code class="literal">ALTER TABLESPACE</code> statement used to create
the file.
</p><p>
For MySQL Cluster Disk Data files, the value of the
<code class="literal">MAXIMUM_SIZE</code> column is always the same as
<code class="literal">INITIAL_SIZE</code>, and the
<code class="literal">AUTOEXTEND_SIZE</code> column is always empty.
</p></li><li><p>
The <code class="literal">CREATION_TIME</code> column shows the date and
time when the file was created. The
<code class="literal">LAST_UPDATE_TIME</code> column displays the date
and time when the file was last modified. The
<code class="literal">LAST_ACCESSED</code> column provides the date and
time when the file was last accessed by the server.
</p><p>
Currently, the values of these columns are as reported by the
operating system, and are not supplied by the
<code class="literal">NDB</code> storage engine. Where no
value is provided by the operating system, these columns
display <code class="literal">0000-00-00 00:00:00</code>.
</p></li><li><p>
For MySQL Cluster Disk Data files, the value of the
<code class="literal">RECOVER_TIME</code> and
<code class="literal">TRANSACTION_COUNTER</code> columns is always
<code class="literal">0</code>.
</p></li><li><p>
For MySQL Cluster Disk Data files, the following columns are
always <code class="literal">NULL</code>:
</p><div class="itemizedlist"><ul type="circle"><li><p>
<code class="literal">VERSION</code>
</p></li><li><p>
<code class="literal">ROW_FORMAT</code>
</p></li><li><p>
<code class="literal">TABLE_ROWS</code>
</p></li><li><p>
<code class="literal">AVG_ROW_LENGTH</code>
</p></li><li><p>
<code class="literal">DATA_LENGTH</code>
</p></li><li><p>
<code class="literal">MAX_DATA_LENGTH</code>
</p></li><li><p>
<code class="literal">INDEX_LENGTH</code>
</p></li><li><p>
<code class="literal">DATA_FREE</code>
</p></li><li><p>
<code class="literal">CREATE_TIME</code>
</p></li><li><p>
<code class="literal">UPDATE_TIME</code>
</p></li><li><p>
<code class="literal">CHECK_TIME</code>
</p></li><li><p>
<code class="literal">CHECKSUM</code>
</p></li></ul></div></li><li><p>
For MySQL Cluster Disk Data files, the value of the
<code class="literal">STATUS</code> column is always
<code class="literal">NORMAL</code>.
</p></li><li><p>
For MySQL Cluster Disk Data files, the
<code class="literal">EXTRA</code> column shows which data node the file
belongs to, as each data node has its own copy of the file.
For example, suppose you use this statement on a MySQL Cluster
with four data nodes:
</p><pre class="programlisting">CREATE LOGFILE GROUP mygroup
ADD UNDOFILE 'new_undo.dat'
INITIAL_SIZE 2G
ENGINE NDB;
</pre><p>
After running the <code class="literal">CREATE LOGFILE GROUP</code>
statement successfully, you should see a result similar to the
one shown here for this query against the
<a href="information-schema.html#files-table" title="20.21. The INFORMATION_SCHEMA FILES Table"><code class="literal">FILES</code></a> table:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT LOGFILE_GROUP_NAME, FILE_TYPE, EXTRA</code></strong>
-> <strong class="userinput"><code>FROM INFORMATION_SCHEMA.FILES</code></strong>
-> <strong class="userinput"><code>WHERE FILE_NAME = 'new_undo.dat';</code></strong>
+--------------------+-------------+----------------+
| LOGFILE_GROUP_NAME | FILE_TYPE | EXTRA |
+--------------------+-------------+----------------+
| mygroup | UNDO FILE | CLUSTER_NODE=3 |
| mygroup | UNDO FILE | CLUSTER_NODE=4 |
| mygroup | UNDO FILE | CLUSTER_NODE=5 |
| mygroup | UNDO FILE | CLUSTER_NODE=6 |
+--------------------+-------------+----------------+
4 rows in set (0.01 sec)
</pre></li><li><p>
The <a href="information-schema.html#files-table" title="20.21. The INFORMATION_SCHEMA FILES Table"><code class="literal">FILES</code></a> table is a nonstandard
table. It was added in MySQL 5.1.6.
</p></li><li><p>
Beginning with MySQL 5.1.14, an additional row is present in
the <a href="information-schema.html#files-table" title="20.21. The INFORMATION_SCHEMA FILES Table"><code class="literal">FILES</code></a> table following the
creation of a logfile group. This row has
<code class="literal">NULL</code> for the value of the
<code class="literal">FILE_NAME</code> column. For this row, the value
of the <code class="literal">FILE_ID</code> column is always
<code class="literal">0</code>, that of the <code class="literal">FILE_TYPE</code>
column is always <code class="literal">UNDO FILE</code>, and that of the
<code class="literal">STATUS</code> column is always
<code class="literal">NORMAL</code>. Currently, the value of the
<code class="literal">ENGINE</code> column is always
<code class="literal">NDBCLUSTER</code>.
</p><p>
The <code class="literal">FREE_EXTENTS</code> column in this row shows
the total number of free extents available to all undo files
belonging to a given log file group whose name and number are
shown in the <code class="literal">LOGFILE_GROUP_NAME</code> and
<code class="literal">LOGFILE_GROUP_NUMBER</code> columns, respectively.
</p><p>
Suppose there are no existing log file groups on your MySQL
Cluster, and you create one using the following statement:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE LOGFILE GROUP lg1</code></strong>
-> <strong class="userinput"><code>ADD UNDOFILE 'undofile.dat'</code></strong>
-> <strong class="userinput"><code>INITIAL_SIZE = 16M</code></strong>
-> <strong class="userinput"><code>UNDO_BUFFER_SIZE = 1M</code></strong>
-> <strong class="userinput"><code>ENGINE = NDB;</code></strong>
Query OK, 0 rows affected (3.81 sec)
</pre><p>
You can now see this <code class="literal">NULL</code> row when you
query the <a href="information-schema.html#files-table" title="20.21. The INFORMATION_SCHEMA FILES Table"><code class="literal">FILES</code></a> table:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT DISTINCT</code></strong>
-> <strong class="userinput"><code>FILE_NAME AS File,</code></strong>
-> <strong class="userinput"><code>FREE_EXTENTS AS Free,</code></strong>
-> <strong class="userinput"><code>TOTAL_EXTENTS AS Total,</code></strong>
-> <strong class="userinput"><code>EXTENT_SIZE AS Size,</code></strong>
-> <strong class="userinput"><code>INITIAL_SIZE AS Initial</code></strong>
-> <strong class="userinput"><code>FROM INFORMATION_SCHEMA.FILES;</code></strong>
+--------------+---------+---------+------+----------+
| File | Free | Total | Size | Initial |
+--------------+---------+---------+------+----------+
| undofile.dat | NULL | 4194304 | 4 | 16777216 |
| NULL | 4184068 | NULL | 4 | NULL |
+--------------+---------+---------+------+----------+
2 rows in set (0.01 sec)
</pre><p>
The total number of free extents available for undo logging is
always somewhat less than the sum of the
<code class="literal">TOTAL_EXTENTS</code> column values for all undo
files in the log file group due to overhead required for
maintaining the undo files. This can be seen by adding a
second undo file to the log file group, then repeating the
previous query against the <a href="information-schema.html#files-table" title="20.21. The INFORMATION_SCHEMA FILES Table"><code class="literal">FILES</code></a>
table:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>ALTER LOGFILE GROUP lg1</code></strong>
-> <strong class="userinput"><code>ADD UNDOFILE 'undofile02.dat'</code></strong>
-> <strong class="userinput"><code>INITIAL_SIZE = 4M</code></strong>
-> <strong class="userinput"><code>ENGINE = NDB;</code></strong>
Query OK, 0 rows affected (1.02 sec)
mysql> <strong class="userinput"><code>SELECT DISTINCT</code></strong>
-> <strong class="userinput"><code>FILE_NAME AS File,</code></strong>
-> <strong class="userinput"><code>FREE_EXTENTS AS Free,</code></strong>
-> <strong class="userinput"><code>TOTAL_EXTENTS AS Total,</code></strong>
-> <strong class="userinput"><code>EXTENT_SIZE AS Size,</code></strong>
-> <strong class="userinput"><code>INITIAL_SIZE AS Initial</code></strong>
-> <strong class="userinput"><code>FROM INFORMATION_SCHEMA.FILES;</code></strong>
+----------------+---------+---------+------+----------+
| File | Free | Total | Size | Initial |
+----------------+---------+---------+------+----------+
| undofile.dat | NULL | 4194304 | 4 | 16777216 |
| undofile02.dat | NULL | 1048576 | 4 | 4194304 |
| NULL | 5223944 | NULL | 4 | NULL |
+----------------+---------+---------+------+----------+
3 rows in set (0.01 sec)
</pre><p>
The amount of free space in bytes which is available for undo
logging by Disk Data tables using this log file group can be
approximated by multiplying the number of free extents by the
initial size:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT</code></strong>
-> <strong class="userinput"><code>FREE_EXTENTS AS 'Free Extents',</code></strong>
-> <strong class="userinput"><code>FREE_EXTENTS * EXTENT_SIZE AS 'Free Bytes'</code></strong>
-> <strong class="userinput"><code>FROM INFORMATION_SCHEMA.FILES</code></strong>
-> <strong class="userinput"><code>WHERE LOGFILE_GROUP_NAME = 'lg1'</code></strong>
-> <strong class="userinput"><code>AND FILE_NAME IS NULL;</code></strong>
+--------------+------------+
| Free Extents | Free Bytes |
+--------------+------------+
| 5223944 | 20895776 |
+--------------+------------+
1 row in set (0.02 sec)
</pre><p>
If you create a MySQL Cluster Disk Data table and then insert
some rows into it, you can see approximately how much space
remains for undo logging afterwards, for example:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLESPACE ts1</code></strong>
-> <strong class="userinput"><code>ADD DATAFILE 'data1.dat'</code></strong>
-> <strong class="userinput"><code>USE LOGFILE GROUP lg1</code></strong>
-> <strong class="userinput"><code>INITIAL_SIZE 512M</code></strong>
-> <strong class="userinput"><code>ENGINE = NDB;</code></strong>
Query OK, 0 rows affected (8.71 sec)
mysql> <strong class="userinput"><code>CREATE TABLE dd (</code></strong>
-> <strong class="userinput"><code>c1 INT NOT NULL PRIMARY KEY,</code></strong>
-> <strong class="userinput"><code>c2 INT,</code></strong>
-> <strong class="userinput"><code>c3 DATE</code></strong>
-> <strong class="userinput"><code>)</code></strong>
-> <strong class="userinput"><code>TABLESPACE ts1 STORAGE DISK</code></strong>
-> <strong class="userinput"><code>ENGINE = NDB;</code></strong>
Query OK, 0 rows affected (2.11 sec)
mysql> <strong class="userinput"><code>INSERT INTO dd VALUES</code></strong>
-> <strong class="userinput"><code>(NULL, 1234567890, '2007-02-02'),</code></strong>
-> <strong class="userinput"><code>(NULL, 1126789005, '2007-02-03'),</code></strong>
-> <strong class="userinput"><code>(NULL, 1357924680, '2007-02-04'),</code></strong>
-> <strong class="userinput"><code>(NULL, 1642097531, '2007-02-05');</code></strong>
Query OK, 4 rows affected (0.01 sec)
mysql> <strong class="userinput"><code>SELECT</code></strong>
-> <strong class="userinput"><code>FREE_EXTENTS AS 'Free Extents',</code></strong>
-> <strong class="userinput"><code>FREE_EXTENTS * EXTENT_SIZE AS 'Free Bytes'</code></strong>
-> <strong class="userinput"><code>FROM INFORMATION_SCHEMA.FILES</code></strong>
-> <strong class="userinput"><code>WHERE LOGFILE_GROUP_NAME = 'lg1'</code></strong>
-> <strong class="userinput"><code>AND FILE_NAME IS NULL;</code></strong>
+--------------+------------+
| Free Extents | Free Bytes |
+--------------+------------+
| 5207565 | 20830260 |
+--------------+------------+
1 row in set (0.01 sec)
</pre></li><li><p>
There are no <a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a> commands
associated with the <a href="information-schema.html#files-table" title="20.21. The INFORMATION_SCHEMA FILES Table"><code class="literal">FILES</code></a> table.
</p></li><li><p>
For additional information, and examples of creating and
dropping MySQL Cluster Disk Data objects, see
<a href="mysql-cluster.html#mysql-cluster-disk-data" title="17.5.9. MySQL Cluster Disk Data Tables">Section 17.5.9, “MySQL Cluster Disk Data Tables”</a>.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="processlist-table"></a>20.22. The <code class="literal">INFORMATION_SCHEMA PROCESSLIST</code> Table</h2></div></div></div><a class="indexterm" name="id4851951"></a><p>
The <a href="information-schema.html#processlist-table" title="20.22. The INFORMATION_SCHEMA PROCESSLIST Table"><code class="literal">PROCESSLIST</code></a> table provides
information about which threads are running.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">ID</code></td><td><code class="literal">Id</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">USER</code></td><td><code class="literal">User</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">HOST</code></td><td><code class="literal">Host</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">DB</code></td><td><code class="literal">db</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">COMMAND</code></td><td><code class="literal">Command</code></td><td>MySQL extension</td></tr><tr><td><a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a></td><td><code class="literal">Time</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">STATE</code></td><td><code class="literal">State</code></td><td>MySQL extension</td></tr><tr><td><code class="literal">INFO</code></td><td><code class="literal">Info</code></td><td>MySQL extension</td></tr></tbody></table></div><p>
For an extensive description of the table columns, see
<a href="sql-syntax.html#show-processlist" title="12.5.5.31. SHOW PROCESSLIST Syntax">Section 12.5.5.31, “<code class="literal">SHOW PROCESSLIST</code> Syntax”</a>.
</p><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The <a href="information-schema.html#processlist-table" title="20.22. The INFORMATION_SCHEMA PROCESSLIST Table"><code class="literal">PROCESSLIST</code></a> table is a
nonstandard table. It was added in MySQL 5.1.7.
</p></li><li><p>
Like the output from the corresponding
<a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a> statement, the
<a href="information-schema.html#processlist-table" title="20.22. The INFORMATION_SCHEMA PROCESSLIST Table"><code class="literal">PROCESSLIST</code></a> table will only show
information about your own threads, unless you have the
<a href="server-administration.html#priv_process"><code class="literal">PROCESS</code></a> privilege, in which
case you will see information about other threads, too. As an
anonymous user, you cannot see any rows at all.
</p></li><li><p>
If an SQL statement refers to
<a href="information-schema.html#processlist-table" title="20.22. The INFORMATION_SCHEMA PROCESSLIST Table"><code class="literal">INFORMATION_SCHEMA.PROCESSLIST</code></a>,
then MySQL will populate the entire table once, when statement
execution begins, so there is read consistency during the
statement. There is no read consistency for a multi-statement
transaction, though.
</p></li></ul></div><p>
The following statements are equivalent:
</p><pre class="programlisting">SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
SHOW FULL PROCESSLIST
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="referential-constraints-table"></a>20.23. The <code class="literal">INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS</code> Table</h2></div></div></div><a class="indexterm" name="id4852302"></a><p>
The <a href="information-schema.html#referential-constraints-table" title="20.23. The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table"><code class="literal">REFERENTIAL_CONSTRAINTS</code></a> table
provides information about foreign keys.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td>CONSTRAINT_CATALOG</td><td> </td><td>NULL</td></tr><tr><td>CONSTRAINT_SCHEMA</td><td> </td><td> </td></tr><tr><td>CONSTRAINT_NAME</td><td> </td><td> </td></tr><tr><td>UNIQUE_CONSTRAINT_CATALOG</td><td> </td><td>NULL</td></tr><tr><td>UNIQUE_CONSTRAINT_SCHEMA</td><td> </td><td> </td></tr><tr><td>UNIQUE_CONSTRAINT_NAME</td><td> </td><td> </td></tr><tr><td>MATCH_OPTION</td><td> </td><td> </td></tr><tr><td>UPDATE_RULE</td><td> </td><td> </td></tr><tr><td>DELETE_RULE</td><td> </td><td> </td></tr><tr><td>TABLE_NAME</td><td> </td><td> </td></tr><tr><td>REFERENCED_TABLE_NAME</td><td> </td><td> </td></tr></tbody></table></div><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The <a href="information-schema.html#referential-constraints-table" title="20.23. The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table"><code class="literal">REFERENTIAL_CONSTRAINTS</code></a> table
was added in MySQL 5.1.10. The
<code class="literal">REFERENCED_TABLE_NAME</code> column was added in
MySQL 5.1.16.
</p></li><li><p>
<code class="literal">TABLE_NAME</code> has the same value as
<code class="literal">TABLE_NAME</code> in
<a href="information-schema.html#table-constraints-table" title="20.12. The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table"><code class="literal">INFORMATION_SCHEMA.TABLE_CONSTRAINTS</code></a>.
</p></li><li><p>
<code class="literal">CONSTRAINT_SCHEMA</code> and
<code class="literal">CONSTRAINT_NAME</code> identify the foreign key.
</p></li><li><p>
<code class="literal">UNIQUE_CONSTRAINT_SCHEMA</code>,
<code class="literal">UNIQUE_CONSTRAINT_NAME</code>, and
<code class="literal">REFERENCED_TABLE_NAME</code> identify the
referenced key. (Note: Before MySQL 5.1.16,
<code class="literal">UNIQUE_CONSTRAINT_NAME</code> incorrectly named
the referenced table, not the constraint.)
</p></li><li><p>
The only valid value at this time for
<code class="literal">MATCH_OPTION</code> is <code class="literal">NONE</code>.
</p></li><li><p>
The possible values for <code class="literal">UPDATE_RULE</code> or
<code class="literal">DELETE_RULE</code> are <code class="literal">CASCADE</code>,
<code class="literal">SET NULL</code>, <code class="literal">SET DEFAULT</code>,
<code class="literal">RESTRICT</code>, <code class="literal">NO ACTION</code>.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="status-table"></a>20.24. The <code class="literal">INFORMATION_SCHEMA GLOBAL_STATUS</code> and
<code class="literal">SESSION_STATUS</code>
Tables</h2></div></div></div><a class="indexterm" name="id4852702"></a><a class="indexterm" name="id4852714"></a><p>
The <a href="information-schema.html#status-table" title="20.24. The INFORMATION_SCHEMA GLOBAL_STATUS and
SESSION_STATUS
Tables"><code class="literal">GLOBAL_STATUS</code></a>
and <a href="information-schema.html#status-table" title="20.24. The INFORMATION_SCHEMA GLOBAL_STATUS and
SESSION_STATUS
Tables"><code class="literal">SESSION_STATUS</code></a>
tables provide information about server status variables. Their
contents correspond to the information produced by the
<a href="sql-syntax.html#show-status" title="12.5.5.37. SHOW STATUS Syntax"><code class="literal">SHOW GLOBAL
STATUS</code></a> and
<a href="sql-syntax.html#show-status" title="12.5.5.37. SHOW STATUS Syntax"><code class="literal">SHOW SESSION
STATUS</code></a> statements (see <a href="sql-syntax.html#show-status" title="12.5.5.37. SHOW STATUS Syntax">Section 12.5.5.37, “<code class="literal">SHOW STATUS</code> Syntax”</a>).
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td>VARIABLE_NAME</td><td>Variable_name</td><td> </td></tr><tr><td>VARIABLE_VALUE</td><td>Value</td><td> </td></tr></tbody></table></div><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The
<a href="information-schema.html#status-table" title="20.24. The INFORMATION_SCHEMA GLOBAL_STATUS and
SESSION_STATUS
Tables"><code class="literal">GLOBAL_STATUS</code></a>
and
<a href="information-schema.html#status-table" title="20.24. The INFORMATION_SCHEMA GLOBAL_STATUS and
SESSION_STATUS
Tables"><code class="literal">SESSION_STATUS</code></a>
tables were added in MySQL 5.1.12.
</p></li><li><p>
Beginning with MySQL 5.1.19, the
<code class="literal">VARIABLE_VALUE</code> column for each of these
tables is defined as <code class="literal">VARCHAR(20480)</code>.
Previously, this column had the data type
<code class="literal">DECIMAL(22,7)</code>, but was changed to avoid
loss of data when working with status variables whose values
were strings (<a href="http://bugs.mysql.com/26994" target="_top">Bug#26994</a>).
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="variables-table"></a>20.25. The <code class="literal">INFORMATION_SCHEMA GLOBAL_VARIABLES</code> and
<code class="literal">SESSION_VARIABLES</code>
Tables</h2></div></div></div><a class="indexterm" name="id4852932"></a><a class="indexterm" name="id4852945"></a><p>
The
<a href="information-schema.html#variables-table" title="20.25. The INFORMATION_SCHEMA GLOBAL_VARIABLES and
SESSION_VARIABLES
Tables"><code class="literal">GLOBAL_VARIABLES</code></a>
and
<a href="information-schema.html#variables-table" title="20.25. The INFORMATION_SCHEMA GLOBAL_VARIABLES and
SESSION_VARIABLES
Tables"><code class="literal">SESSION_VARIABLES</code></a>
tables provide information about server status variables. Their
contents correspond to the information produced by the
<a href="sql-syntax.html#show-variables" title="12.5.5.41. SHOW VARIABLES Syntax"><code class="literal">SHOW GLOBAL
VARIABLES</code></a> and
<a href="sql-syntax.html#show-variables" title="12.5.5.41. SHOW VARIABLES Syntax"><code class="literal">SHOW SESSION
VARIABLES</code></a> statements (see
<a href="sql-syntax.html#show-variables" title="12.5.5.41. SHOW VARIABLES Syntax">Section 12.5.5.41, “<code class="literal">SHOW VARIABLES</code> Syntax”</a>).
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td>VARIABLE_NAME</td><td>Variable_name</td><td> </td></tr><tr><td>VARIABLE_VALUE</td><td>Value</td><td> </td></tr></tbody></table></div><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The
<a href="information-schema.html#variables-table" title="20.25. The INFORMATION_SCHEMA GLOBAL_VARIABLES and
SESSION_VARIABLES
Tables"><code class="literal">GLOBAL_VARIABLES</code></a>
and
<a href="information-schema.html#variables-table" title="20.25. The INFORMATION_SCHEMA GLOBAL_VARIABLES and
SESSION_VARIABLES
Tables"><code class="literal">SESSION_VARIABLES</code></a>
tables were added in MySQL 5.1.12.
</p></li><li><p>
Beginning with MySQL 5.1.19, the
<code class="literal">VARIABLE_VALUE</code> column for each of these
tables is defined as <code class="literal">VARCHAR(20480)</code>.
Previously, this column had the data type
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">LONGTEXT</code></a>; this
was changed in order to make these tables consistent with the
<a href="information-schema.html#status-table" title="20.24. The INFORMATION_SCHEMA GLOBAL_STATUS and
SESSION_STATUS
Tables"><code class="literal">GLOBAL_STATUS</code></a>
and
<a href="information-schema.html#status-table" title="20.24. The INFORMATION_SCHEMA GLOBAL_STATUS and
SESSION_STATUS
Tables"><code class="literal">SESSION_STATUS</code></a>
tables, whose definitions had been changed in that version
(see <a href="information-schema.html#status-table" title="20.24. The INFORMATION_SCHEMA GLOBAL_STATUS and
SESSION_STATUS
Tables">Section 20.24, “The <code class="literal">INFORMATION_SCHEMA GLOBAL_STATUS</code> and
<code class="literal">SESSION_STATUS</code>
Tables”</a>).
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="profiling-table"></a>20.26. The <code class="literal">INFORMATION_SCHEMA PROFILING</code> Table</h2></div></div></div><a class="indexterm" name="id4853174"></a><p>
The <a href="information-schema.html#profiling-table" title="20.26. The INFORMATION_SCHEMA PROFILING Table"><code class="literal">PROFILING</code></a> table provides
statement profiling information. Its contents correspond to the
information produced by the <a href="sql-syntax.html#show-profiles" title="12.5.5.33. SHOW PROFILES Syntax"><code class="literal">SHOW
PROFILES</code></a> and <a href="sql-syntax.html#show-profile" title="12.5.5.32. SHOW PROFILE Syntax"><code class="literal">SHOW PROFILE</code></a>
statements (see <a href="sql-syntax.html#show-profiles" title="12.5.5.33. SHOW PROFILES Syntax">Section 12.5.5.33, “<code class="literal">SHOW PROFILES</code> Syntax”</a>). The table is
empty unless the <a href="server-administration.html#sysvar_profiling"><code class="literal">profiling</code></a>
session variable is set to 1.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">INFORMATION_SCHEMA</code>
Name</strong></span></td><td><span class="bold"><strong><a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a>
Name</strong></span></td><td><span class="bold"><strong>Remarks</strong></span></td></tr><tr><td><code class="literal">QUERY_ID</code></td><td><code class="literal">Query_ID</code></td><td> </td></tr><tr><td><code class="literal">SEQ</code></td><td><code class="literal"></code></td><td> </td></tr><tr><td><code class="literal">STATE</code></td><td><code class="literal">Status</code></td><td> </td></tr><tr><td><code class="literal">DURATION</code></td><td><code class="literal">Duration</code></td><td> </td></tr><tr><td><code class="literal">CPU_USER</code></td><td><code class="literal">CPU_user</code></td><td> </td></tr><tr><td><code class="literal">CPU_SYSTEM</code></td><td><code class="literal">CPU_system</code></td><td> </td></tr><tr><td><code class="literal">CONTEXT_VOLUNTARY</code></td><td><code class="literal">Context_voluntary</code></td><td> </td></tr><tr><td><code class="literal">CONTEXT_INVOLUNTARY</code></td><td><code class="literal">Context_involuntary</code></td><td> </td></tr><tr><td><code class="literal">BLOCK_OPS_IN</code></td><td><code class="literal">Block_ops_in</code></td><td> </td></tr><tr><td><code class="literal">BLOCK_OPS_OUT</code></td><td><code class="literal">Block_ops_out</code></td><td> </td></tr><tr><td><code class="literal">MESSAGES_SENT</code></td><td><code class="literal">Messages_sent</code></td><td> </td></tr><tr><td><code class="literal">MESSAGES_RECEIVED</code></td><td><code class="literal">Messages_received</code></td><td> </td></tr><tr><td><code class="literal">PAGE_FAULTS_MAJOR</code></td><td><code class="literal">Page_faults_major</code></td><td> </td></tr><tr><td><code class="literal">PAGE_FAULTS_MINOR</code></td><td><code class="literal">Page_faults_minor</code></td><td> </td></tr><tr><td><code class="literal">SWAPS</code></td><td><code class="literal">Swaps</code></td><td> </td></tr><tr><td><code class="literal">SOURCE_FUNCTION</code></td><td><code class="literal">Source_function</code></td><td> </td></tr><tr><td><code class="literal">SOURCE_FILE</code></td><td><code class="literal">Source_file</code></td><td> </td></tr><tr><td><code class="literal">SOURCE_LINE</code></td><td><code class="literal">Source_line</code></td><td> </td></tr></tbody></table></div><p>
<span class="bold"><strong>Notes</strong></span>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The <a href="information-schema.html#profiling-table" title="20.26. The INFORMATION_SCHEMA PROFILING Table"><code class="literal">PROFILING</code></a> table was added in
MySQL 5.1.24.
</p></li><li><p>
<code class="literal">QUERY_ID</code> is a numeric statement identifier.
</p></li><li><p>
<code class="literal">SEQ</code> is a sequence number indicating the
display order for rows with the same
<code class="literal">QUERY_ID</code> value.
</p></li><li><p>
<code class="literal">STATE</code> is the profiling state to which the
row measurements apply.
</p></li><li><p>
<code class="literal">DURATION</code> indicates how long statement
execution remained in the given state, in seconds.
</p></li><li><p>
<code class="literal">CPU_USER</code> and <code class="literal">CPU_SYSTEM</code>
indicate user and system CPU use, in seconds.
</p></li><li><p>
<code class="literal">CONTEXT_VOLUNTARY</code> and
<code class="literal">CONTEXT_INVOLUNTARY</code> indicate how many
voluntary and involuntary context switches occurred.
</p></li><li><p>
<code class="literal">BLOCK_OPS_IN</code> and
<code class="literal">BLOCK_OPS_OUT</code> indicate the number of block
input and output operations.
</p></li><li><p>
<code class="literal">MESSAGES_SENT</code> and
<code class="literal">MESSAGES_RECEIVED</code> indicate the number of
communication messages sent and received.
</p></li><li><p>
<code class="literal">PAGE_FAULTS_MAJOR</code> and
<code class="literal">PAGE_FAULTS_MINOR</code> indicate the number of
major and minor page faults.
</p></li><li><p>
<code class="literal">SWAPS</code> indicates how many swaps occurred.
</p></li><li><p>
<code class="literal">SOURCE_FUNCTION</code>,
<code class="literal">SOURCE_FILE</code>, and
<code class="literal">SOURCE_LINE</code> provide information indicating
where in the source code the profiled state executes.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="other-information-schema-tables"></a>20.27. Other <code class="literal">INFORMATION_SCHEMA</code> Tables</h2></div></div></div><p>
We intend to implement additional
<code class="literal">INFORMATION_SCHEMA</code> tables. In particular, we
acknowledge the need for the <code class="literal">PARAMETERS</code> table.
(<code class="literal">PARAMETERS</code> is implemented in MySQL 5.5.)
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="extended-show"></a>20.28. Extensions to <code class="literal">SHOW</code> Statements</h2></div></div></div><a class="indexterm" name="id4853887"></a><a class="indexterm" name="id4853896"></a><a class="indexterm" name="id4853905"></a><p>
Some extensions to <a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a> statements
accompany the implementation of
<code class="literal">INFORMATION_SCHEMA</code>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a> can be used to get
information about the structure of
<code class="literal">INFORMATION_SCHEMA</code> itself.
</p></li><li><p>
Several <a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a> statements accept
a <code class="literal">WHERE</code> clause that provides more
flexibility in specifying which rows to display.
</p></li></ul></div><p>
<code class="literal">INFORMATION_SCHEMA</code> is an information database,
so its name is included in the output from
<a href="sql-syntax.html#show-databases" title="12.5.5.15. SHOW DATABASES Syntax"><code class="literal">SHOW DATABASES</code></a>. Similarly,
<a href="sql-syntax.html#show-tables" title="12.5.5.39. SHOW TABLES Syntax"><code class="literal">SHOW TABLES</code></a> can be used with
<code class="literal">INFORMATION_SCHEMA</code> to obtain a list of its
tables:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW TABLES FROM INFORMATION_SCHEMA;</code></strong>
+---------------------------------------+
| Tables_in_INFORMATION_SCHEMA |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+
27 rows in set (0.00 sec)
</pre><p>
<a href="sql-syntax.html#show-columns" title="12.5.5.6. SHOW COLUMNS Syntax"><code class="literal">SHOW COLUMNS</code></a> and
<a href="sql-syntax.html#describe" title="12.3.1. DESCRIBE Syntax"><code class="literal">DESCRIBE</code></a> can display information
about the columns in individual
<code class="literal">INFORMATION_SCHEMA</code> tables.
</p><p>
<a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a> statements that accept a
<a href="functions.html#operator_like"><code class="literal">LIKE</code></a> clause to limit the rows
displayed also allow a <code class="literal">WHERE</code> clause that
enables specification of more general conditions that selected
rows must satisfy:
</p><pre class="programlisting">SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS
SHOW DATABASES
SHOW FUNCTION STATUS
SHOW INDEX
SHOW OPEN TABLES
SHOW PROCEDURE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW TRIGGERS
SHOW VARIABLES
</pre><p>
The <code class="literal">WHERE</code> clause, if present, is evaluated
against the column names displayed by the
<a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a> statement. For example, the
<a href="sql-syntax.html#show-character-set" title="12.5.5.4. SHOW CHARACTER SET Syntax"><code class="literal">SHOW CHARACTER SET</code></a> statement
produces these output columns:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW CHARACTER SET;</code></strong>
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
...
</pre><p>
To use a <code class="literal">WHERE</code> clause with
<a href="sql-syntax.html#show-character-set" title="12.5.5.4. SHOW CHARACTER SET Syntax"><code class="literal">SHOW CHARACTER SET</code></a>, you would refer
to those column names. As an example, the following statement
displays information about character sets for which the default
collation contains the string <code class="literal">'japanese'</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW CHARACTER SET WHERE `Default collation` LIKE '%japanese%';</code></strong>
+---------+---------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------------------+---------------------+--------+
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+---------+---------------------------+---------------------+--------+
</pre><p>
This statement displays the multi-byte character sets:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW CHARACTER SET WHERE Maxlen > 1;</code></strong>
+---------+---------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+---------+---------------------------+---------------------+--------+
</pre></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="stored-programs-views.html">Prev</a> </td><td width="20%" align="center"> </td><td width="40%" align="right"> <a accesskey="n" href="connectors-apis.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 19. Stored Programs and Views </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Chapter 21. Connectors and APIs</td></tr></table></div></body></html>
