Rechercher dans le manuel MySQL

14.7 Data Dictionary Usage Differences

Use of a data dictionary-enabled MySQL server entails some operational differences compared to a server that does not have a data dictionary:

  • Previously, enabling the innodb_read_only system variable prevented creating and dropping tables only for the InnoDB storage. As of MySQL 8.0, enabling innodb_read_only prevents these operations for all storage engines. Table creation and drop operations for any storage engine modify data dictionary tables in the mysql system database, but those tables use the InnoDB storage engine and cannot be modified when innodb_read_only is enabled. The same principle applies to other table operations that require modifying data dictionary tables. Examples:

    Note

    Enabling innodb_read_only also has important implications for non-data dictionary tables in the mysql system database. For details, see the description of innodb_read_only in Section 15.13, “InnoDB Startup Options and System Variables”

  • Previously, tables in the mysql system database were visible to DML and DDL statements. As of MySQL 8.0, data dictionary tables are invisible and cannot be modified or queried directly. However, in most cases there are corresponding INFORMATION_SCHEMA tables that can be queried instead. This enables the underlying data dictionary tables to be changed as server development proceeds, while maintaining a stable INFORMATION_SCHEMA interface for application use.

  • INFORMATION_SCHEMA tables in MySQL 8.0 are closely tied to the data dictionary, resulting in several usage differences:

    • Previously, INFORMATION_SCHEMA queries for table statistics in the STATISTICS and TABLES tables retrieved statistics directly from storage engines. As of MySQL 8.0, cached table statistics are used by default. The information_schema_stats_expiry system variable defines the period of time before cached table statistics expire. The default is 86400 seconds (24 hours). (To update the cached values at any time for a given table, use ANALYZE TABLE.) If there are no cached statistics or statistics have expired, statistics are retrieved from storage engines when querying table statistics columns. To always retrieve the latest statistics directly from storage engines, set information_schema_stats_expiry to 0. For more information, see Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries”.

    • Several INFORMATION_SCHEMA tables are views on data dictionary tables, which enables the optimizer to use indexes on those underlying tables. Consequently, depending on optimizer choices, the row order of results for INFORMATION_SCHEMA queries might differ from previous results. If a query result must have specific row ordering characteristics, include an ORDER BY clause.

    • Queries on INFORMATION_SCHEMA tables may return column names in a different lettercase than in earlier MySQL series. Applications should test result set column names in case-insensitive fashion. If that is not feasible, a workaround is to use column aliases in the select list that return column names in the required lettercase. For example:

      1. SELECT TABLE_SCHEMA AS table_schema, TABLE_NAME AS table_name
      2. FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users';
    • mysqldump and mysqlpump no longer dump the INFORMATION_SCHEMA database, even if explicitly named on the command line.

    • CREATE TABLE dst_tbl LIKE src_tbl requires that src_tbl be a base table and fails if it is an INFORMATION_SCHEMA table that is a view on data dictionary tables.

    • Previously, result set headers of columns selected from INFORMATION_SCHEMA tables used the capitalization specified in the query. This query produces a result set with a header of table_name:

      1. SELECT table_name FROM INFORMATION_SCHEMA.TABLES;

      As of MySQL 8.0, these headers are capitalized; the preceding query produces a result set with a header of TABLE_NAME. If necessary, a column alias can be used to achieve a different lettercase. For example:

      1. SELECT table_name AS 'table_name' FROM INFORMATION_SCHEMA.TABLES;
  • The data directory affects how mysqldump and mysqlpump dump information from the mysql system database:

    • Previously, it was possible to dump all tables in the mysql system database. As of MySQL 8.0, mysqldump and mysqlpump dump only non-data dictionary tables in that database.

    • Previously, the --routines and --events options were not required to include stored routines and events when using the --all-databases option: The dump included the mysql system database, and therefore also the proc and event tables containing stored routine and event definitions. As of MySQL 8.0, the event and proc tables are not used. Definitions for the corresponding objects are stored in data dictionary tables, but those tables are not dumped. To include stored routines and events in a dump made using --all-databases, use the --routines and --events options explicitly.

    • Previously, the --routines option required the SELECT privilege for the proc table. As of MySQL 8.0, that table is not used; --routines requires the global SELECT privilege instead.

    • Previously, it was possible to dump stored routine and event definitions together with their creation and modification timestamps, by dumping the proc and event tables. As of MySQL 8.0, those tables are not used, so it is not possible to dump timestamps.

  • Previously, creating a stored routine that contains illegal characters produced a warning. As of MySQL 8.0, this is an error.


Suchen Sie im MySQL-Handbuch

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-data-dictionary-usage-differences.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

  1. Zeigen Sie - html-Dokument Sprache des Dokuments:en Manuel MySQL : https://dev.mysql.com/

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.

Inhaltsverzeichnis Haut