Rechercher dans le manuel MySQL
25.1 Introduction
INFORMATION_SCHEMA
provides access to database
metadata, information about
the MySQL server such as the name of a database or table, the data
type of a column, or access privileges. Other terms that are
sometimes used for this information are
data dictionary and
system catalog.
INFORMATION_SCHEMA Usage Notes
INFORMATION_SCHEMA
is a database within each
MySQL instance, the place that stores information about all the
other databases that the MySQL server maintains. The
INFORMATION_SCHEMA
database contains several
read-only tables. They are actually views, not base tables, so
there are no files associated with them, and you cannot set
triggers on them. Also, there is no database directory with that
name.
Although you can select INFORMATION_SCHEMA
as
the default database with a USE
statement, you can only read the contents of tables, not perform
INSERT
,
UPDATE
, or
DELETE
operations on them.
Here is an example of a statement that retrieves information
from INFORMATION_SCHEMA
:
- ORDER BY table_name;
- +------------+------------+--------+
- +------------+------------+--------+
- +------------+------------+--------+
Explanation: The statement requests a list of all the tables in
database db5
, showing just three pieces of
information: the name of the table, its type, and its storage
engine.
The definition for character columns (for example,
TABLES.TABLE_NAME
) is generally
VARCHAR(
where N
) CHARACTER SET
utf8N
is at least
64. MySQL uses the default collation for this character set
(utf8_general_ci
) for all searches, sorts,
comparisons, and other string operations on such columns.
Because some MySQL objects are represented as files, searches in
INFORMATION_SCHEMA
string columns can be
affected by file system case sensitivity. For more information,
see Section 10.8.7, “Using Collation in INFORMATION_SCHEMA Searches”.
The SELECT ... FROM INFORMATION_SCHEMA
statement is intended as a more consistent way to provide access
to the information provided by the various
SHOW
statements that MySQL
supports (SHOW DATABASES
,
SHOW TABLES
, and so forth). Using
SELECT
has these advantages,
compared to SHOW
:
It conforms to Codd's rules, because all access is done on tables.
You can use the familiar syntax of the
SELECT
statement, and only need to learn some table and column names.The implementor need not worry about adding keywords.
You can filter, sort, concatenate, and transform the results from
INFORMATION_SCHEMA
queries into whatever format your application needs, such as a data structure or a text representation to parse.This technique is more interoperable with other database systems. For example, Oracle Database users are familiar with querying tables in the Oracle data dictionary.
Because SHOW
is familiar and
widely used, the SHOW
statements
remain as an alternative. In fact, along with the implementation
of INFORMATION_SCHEMA
, there are enhancements
to SHOW
as described in
Section 25.42, “Extensions to SHOW Statements”.
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 ROUTINE_DEFINITION
column
in the INFORMATION_SCHEMA
ROUTINES
table), users who have
insufficient privileges see NULL
. These
restrictions do not apply for
InnoDB
tables; you can see them
with only the PROCESS
privilege.
The same privileges apply to selecting information from
INFORMATION_SCHEMA
and viewing the same
information through SHOW
statements. In either case, you must have some privilege on an
object to see information about it.
INFORMATION_SCHEMA
queries that search for
information from more than one database might take a long time
and impact performance. To check the efficiency of a query, you
can use EXPLAIN
. For information
about using EXPLAIN
output to
tune INFORMATION_SCHEMA
queries, see
Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries”.
The implementation for the INFORMATION_SCHEMA
table structures in MySQL follows the ANSI/ISO SQL:2003 standard
Part 11 Schemata. Our intent is
approximate compliance with SQL:2003 core feature F021
Basic information schema.
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 added column is the
ENGINE
column in the
INFORMATION_SCHEMA
TABLES
table.
Although other DBMSs use a variety of names, like
syscat
or system
, the
standard name is INFORMATION_SCHEMA
.
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 “MySQL extension”. (For example, we changed
COLLATION
to
TABLE_COLLATION
in the
TABLES
table.) See the list of
reserved words near the end of this article:
https://web.archive.org/web/20070428032454/http://www.dbazine.com/db2/db2-disarticles/gulutzan5.
The following sections describe each of the tables and columns
in INFORMATION_SCHEMA
. For each column, there
are three pieces of information:
“
INFORMATION_SCHEMA
Name” indicates the name for the column in theINFORMATION_SCHEMA
table. This corresponds to the standard SQL name unless the “Remarks” field says “MySQL extension.”“
SHOW
Name” indicates the equivalent field name in the closestSHOW
statement, if there is one.“Remarks” provides additional information where applicable. If this field is
NULL
, it means that the value of the column is alwaysNULL
. If this field says “MySQL extension,” the column is a MySQL extension to standard SQL.
Many sections indicate what SHOW
statement is equivalent to a
SELECT
that retrieves information
from INFORMATION_SCHEMA
. For
SHOW
statements that display
information for the default database if you omit a FROM
clause, you can
often select information for the default database by adding an
db_name
AND TABLE_SCHEMA = SCHEMA()
condition to the
WHERE
clause of a query that retrieves
information from an INFORMATION_SCHEMA
table.
These sections discuss additional
INFORMATION_SCHEMA
-related topics:
information about
INFORMATION_SCHEMA
tables specific to theInnoDB
storage engine: Section 25.39, “INFORMATION_SCHEMA InnoDB Tables”information about
INFORMATION_SCHEMA
tables specific to the thread pool plugin: Section 25.40, “INFORMATION_SCHEMA Thread Pool Tables”information about
INFORMATION_SCHEMA
tables specific to theCONNECTION_CONTROL
plugin: Section 25.41, “INFORMATION_SCHEMA Connection-Control Tables”Answers to questions that are often asked concerning the
INFORMATION_SCHEMA
database: Section A.7, “MySQL 8.0 FAQ: INFORMATION_SCHEMA”INFORMATION_SCHEMA
queries and the optimizer: Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries”The effect of collation on
INFORMATION_SCHEMA
comparisons: Section 10.8.7, “Using Collation in INFORMATION_SCHEMA Searches”
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-information-schema-introduction.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.