Database Reference
In-Depth Information
Information_schema
In the SQL:2003 standard, access to the data dictionary (or database metadata) is
provided by a structure called
information_schema.
As this is part of the standard,
and already exists in other database systems, the decision to implement this feature
into MySQL was a very good one.
MySQL has added some information that is not part of the standard—for
example,
INFORMATION_SCHEMA.COLUMNS.COLUMN_TYPE.
Beware of
the fact that if you plan to use this information in a software project, it has
to remain portable to other SQL implementations.
A phpMyAdmin user sees the
information_schema
as a normal database
containing views that describe many aspects of the structure of the databases hosted
on this server. Here is a subset of what can be seen (and in fact, the only possible
operation on this database is
SELECT
).
Internally, phpMyAdmin calls the
information_schema
for some of its operations
instead of the corresponding SHOW statements to retrieve metadata. However,
phpMyAdmin developers have noticed—and this was confirmed by other
developers at the 2007 MySQL Users Conference—that some SELECT operations
involving a
WHERE
clause on
information_schema
are really slow (many minutes of
wait time) when the server hosts hundreds of databases or tables. Therefore, in the
current state of MySQL, caution must be exercised about using this feature in our
programming projects.
Search WWH ::
Custom Search