Information schema что это
Перейти к содержимому

Information schema что это

  • автор:

База данных INFORMATION_SCHEMA.

База данных (схема) INFORMATION_SCHEMA служит как центральный репозиторий метаданных MySQL. Это виртуальная база данных. Виртуальная в том смысле, что из нее ничего не сохраняется на диск. Но, как и в другой реальной базе данных, в ней есть таблицы. Информацию из этих таблиц можно получить с помощью команды SELECT, как и из обычных таблиц в другой базе данных. На самом деле это не “реальные” таблицы, а системные представления (system views), которые показывают динамическую информацию в зависимости от привилегий пользователя.

Лично мне больше нравится, когда слово “view” не переводят на русский язык, как “представление” или “вид”, а используют либо английское название, либо его производные “вьюшка” и т.п.

Список всех таблиц в базе данных information_schema можно получить следующим запросом:

mysql> select table_name from information_schema.tables
-> where table_schema=’information_schema’
-> order by table_name;
+—————————————+
| table_name |
+—————————————+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| INNODB_BUFFER_PAGE |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_BUFFER_POOL_STATS |
| INNODB_CMP |
| INNODB_CMPMEM |
| INNODB_CMPMEM_RESET |
| INNODB_CMP_RESET |
| INNODB_LOCKS |
| INNODB_LOCK_WAITS |
| INNODB_TRX |
| KEY_COLUMN_USAGE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+—————————————+
40 rows in set (0.00 sec)

Рассмотрим подробнее какую информацию содержат данные таблицы.

Информация о таблицах и базах данных:

  • COLUMNS: описание столбцов в таблицах и представлениях.
  • ENGINES: описание storage engines (“движков” базы данных).
  • SCHEMATA: описание баз данных.
  • TABLES: описание таблиц.
  • VIEWS: описание представлеий.
  • PARTITIONS: информация о партициях в таблицах.

Привилегии:

  • COLUMN_PRIVILEGES: привилегии на столбцы таблиц.
  • SCHEMA_PRIVILEGES: привилегии на базы данных.
  • TABLE_PRIVILEGES: привилегии на таблицы.
  • USER_PRIVILEGES: глобальные привилегии пользователей.

Кодировка:

  • CHARACTER_SETS: доступные кодировки.
  • COLLATIONS: сортировки для каждой кодировки.
  • COLLATION_CHARACTER_SET_APPLICABILITY: какая кодировка применима для определенной кодировки.

Констрейнты и индексы:

  • KEY_COLUMN_USAGE: констрейнты на столбцы.
  • REFERENTIAL_CONSTRAINTS: внешние ключи.
  • STATISTICS: индексы на таблицы.
  • TABLE_CONSTRAINTS: констрейнты на таблицы.

Установки и статусы сервера:

  • GLOBAL_STATUS: глобальная статистика MySQL сервера.
  • GLOBAL_VARIABLES: глобальные переменные сервера.
  • PLUGINS: плагины сервера.
  • PROCESSLIST: показывает подключения к БД.
  • SESSION_STATUS: статистика текущей сессии.
  • SESSION_VARIABLES: переменные сервера установленные в текущей сессии.

Триггеры, процедуры, параметры:

  • EVENTS: планировщик заданий в базе данных.
  • ROUTINES: информация о сохраненных функциях и процедурах.
  • TRIGGERS: триггеры в базе данных.
  • PARAMETERS: информация о параметрах сохраненных процедур и функций и возвращаемых значениях.

InnoDB:

  • INNODB_LOCKS: блокировки, которые каждая транзакция InnoDB удерживает или запрашивает.
  • INNODB_LOCK_WAITS: блокировки, которые ждет указанная транзакция.
  • INNODB_TRX: каждая транзакция исполняющаяся в InnoDB.

Подробнее про INFORMATION_SCHEMA можно почитать в документации MySQL.

INFORMATION_SCHEMA

INFORMATION_SCHEMA ( information_schema ) — это системная база данных, содержащая представления. Используя эти представления, вы можете получить информацию о метаданных объектов базы данных. Эти представления считывают данные из столбцов системных таблиц system.columns, system.databases и system.tables.

Структура и состав системных таблиц могут меняться в разных версиях СУБД ClickHouse, но поддержка information_schema позволяет изменять структуру системных таблиц без изменения способа доступа к метаданным. Запросы метаданных не зависят от используемой СУБД.

SHOW TABLES FROM INFORMATION_SCHEMA; 
┌─name─────┐ │ COLUMNS │ │ SCHEMATA │ │ TABLES │ │ VIEWS │ └──────────┘

INFORMATION_SCHEMA содержит следующие представления:

COLUMNS​

Содержит столбцы, которые считываются из системной таблицы system.columns, и столбцы, которые не поддерживаются в ClickHouse или не имеют смысла (всегда имеют значение NULL ), но должны быть по стандарту.

  • table_catalog (String) — имя базы данных, в которой находится таблица.
  • table_schema (String) — имя базы данных, в которой находится таблица.
  • table_name (String) — имя таблицы.
  • column_name (String) — имя столбца.
  • ordinal_position (UInt64) — порядковый номер столбца в таблице (нумерация начинается с 1).
  • column_default (String) — выражение для значения по умолчанию или пустая строка.
  • is_nullable (UInt8) — флаг, показывающий является ли столбец типа Nullable .
  • data_type (String) — тип столбца.
  • character_maximum_length (Nullable(UInt64)) — максимальная длина в байтах для двоичных данных, символьных данных или текстовых данных и изображений. В ClickHouse имеет смысл только для типа данных FixedString . Иначе возвращается значение NULL .
  • character_octet_length (Nullable(UInt64)) — максимальная длина в байтах для двоичных данных, символьных данных или текстовых данных и изображений. В ClickHouse имеет смысл только для типа данных FixedString . Иначе возвращается значение NULL .
  • numeric_precision (Nullable(UInt64)) — точность приблизительных числовых данных, точных числовых данных, целочисленных данных или денежных данных. В ClickHouse это разрядность для целочисленных типов и десятичная точность для типов Decimal . Иначе возвращается значение NULL .
  • numeric_precision_radix (Nullable(UInt64)) — основание системы счисления точности приблизительных числовых данных, точных числовых данных, целочисленных данных или денежных данных. В ClickHouse значение столбца равно 2 для целочисленных типов и 10 — для типов Decimal . Иначе возвращается значение NULL .
  • numeric_scale (Nullable(UInt64)) — масштаб приблизительных числовых данных, точных числовых данных, целочисленных данных или денежных данных. В ClickHouse имеет смысл только для типов Decimal . Иначе возвращается значение NULL .
  • datetime_precision (Nullable(UInt64)) — десятичная точность для данных типа DateTime64 . Для других типов данных возвращается значение NULL .
  • character_set_catalog (Nullable(String)) — NULL , не поддерживается.
  • character_set_schema (Nullable(String)) — NULL , не поддерживается.
  • character_set_name (Nullable(String)) — NULL , не поддерживается.
  • collation_catalog (Nullable(String)) — NULL , не поддерживается.
  • collation_schema (Nullable(String)) — NULL , не поддерживается.
  • collation_name (Nullable(String)) — NULL , не поддерживается.
  • domain_catalog (Nullable(String)) — NULL , не поддерживается.
  • domain_schema (Nullable(String)) — NULL , не поддерживается.
  • domain_name (Nullable(String)) — NULL , не поддерживается.

Пример

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE (table_schema=currentDatabase() OR table_schema='') AND table_name NOT LIKE '%inner%' LIMIT 1 FORMAT Vertical; 
Row 1: ────── table_catalog: default table_schema: default table_name: describe_example column_name: id ordinal_position: 1 column_default: is_nullable: 0 data_type: UInt64 character_maximum_length: ᴺᵁᴸᴸ character_octet_length: ᴺᵁᴸᴸ numeric_precision: 64 numeric_precision_radix: 2 numeric_scale: 0 datetime_precision: ᴺᵁᴸᴸ character_set_catalog: ᴺᵁᴸᴸ character_set_schema: ᴺᵁᴸᴸ character_set_name: ᴺᵁᴸᴸ collation_catalog: ᴺᵁᴸᴸ collation_schema: ᴺᵁᴸᴸ collation_name: ᴺᵁᴸᴸ domain_catalog: ᴺᵁᴸᴸ domain_schema: ᴺᵁᴸᴸ domain_name: ᴺᵁᴸᴸ

SCHEMATA​

Содержит столбцы, которые считываются из системной таблицы system.databases, и столбцы, которые не поддерживаются в ClickHouse или не имеют смысла (всегда имеют значение NULL ), но должны быть по стандарту.

  • catalog_name (String) — имя базы данных.
  • schema_name (String) — имя базы данных.
  • schema_owner (String) — имя владельца схемы, всегда ‘default’ .
  • default_character_set_catalog (Nullable(String)) — NULL , не поддерживается.
  • default_character_set_schema (Nullable(String)) — NULL , не поддерживается.
  • default_character_set_name (Nullable(String)) — NULL , не поддерживается.
  • sql_path (Nullable(String)) — NULL , не поддерживается.

Пример

SELECT * FROM information_schema.schemata WHERE schema_name ILIKE 'information_schema' LIMIT 1 FORMAT Vertical; 
Row 1: ────── catalog_name: INFORMATION_SCHEMA schema_name: INFORMATION_SCHEMA schema_owner: default default_character_set_catalog: ᴺᵁᴸᴸ default_character_set_schema: ᴺᵁᴸᴸ default_character_set_name: ᴺᵁᴸᴸ sql_path: ᴺᵁᴸᴸ

TABLES​

Содержит столбцы, которые считываются из системной таблицы system.tables.

  • table_catalog (String) — имя базы данных, в которой находится таблица.
  • table_schema (String) — имя базы данных, в которой находится таблица.
  • table_name (String) — имя таблицы.
  • table_type (Enum8) — тип таблицы. Возможные значения:
    • BASE TABLE
    • VIEW
    • FOREIGN TABLE
    • LOCAL TEMPORARY
    • SYSTEM VIEW

    Пример

    SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE (table_schema = currentDatabase() OR table_schema = '') AND table_name NOT LIKE '%inner%' LIMIT 1 FORMAT Vertical; 
    Row 1: ────── table_catalog: default table_schema: default table_name: describe_example table_type: BASE TABLE

    VIEWS​

    Содержит столбцы, которые считываются из системной таблицы system.tables, если использован движок View.

    • table_catalog (String) — имя базы данных, в которой находится таблица.
    • table_schema (String) — имя базы данных, в которой находится таблица.
    • table_name (String) — имя таблицы.
    • view_definition (String) — SELECT запрос для представления.
    • check_option (String) — NONE , нет проверки.
    • is_updatable (Enum8) — NO , представление не обновляется.
    • is_insertable_into (Enum8) — показывает является ли представление материализованным. Возможные значения:
      • NO — создано обычное представление.
      • YES — создано материализованное представление.

      Пример

      CREATE VIEW v (n Nullable(Int32), f Float64) AS SELECT n, f FROM t; CREATE MATERIALIZED VIEW mv ENGINE = Null AS SELECT * FROM system.one; SELECT * FROM information_schema.views WHERE table_schema = currentDatabase() LIMIT 1 FORMAT Vertical; 
      Row 1: ────── table_catalog: default table_schema: default table_name: mv view_definition: SELECT * FROM system.one check_option: NONE is_updatable: NO is_insertable_into: YES is_trigger_updatable: NO is_trigger_deletable: NO is_trigger_insertable_into: NO

      Information schema что это

      Информационная схема состоит из набора представлений, содержащих информацию об объектах, определённых в текущей базе данных. Информационная схема описана в стандарте SQL и поэтому можно рассчитывать на её переносимость и стабильность — в отличие от системных каталогов, которые привязаны к PostgreSQL , и моделируются, отталкиваясь от реализации. Представления информационной схемы, однако, не содержат информацию о функциях, присущих исключительно PostgreSQL ; чтобы получить информацию о них, необходимо обратиться к системным каталогам или другим специфическим представлениям PostgreSQL .

      Примечание

      Когда из базы данных запрашивается информация об ограничениях, возможна ситуация, когда соответствующий стандарту запрос, который должен возвращать одну строку, возвращает несколько. Это связано с тем, что стандарт SQL требует, чтобы имена ограничений были уникальными в схеме, но в PostgreSQL такого требования нет. Имена ограничений, которые PostgreSQL генерирует автоматически, не должны дублироваться в одной схеме, но сами пользователи могут назначить подобные дублирующиеся имена.

      Эта проблема может проявиться при обращении к таким представлениям информационной схемы, как check_constraint_routine_usage , check_constraints , domain_constraints и referential_constraints . В некоторых других представлениях она могла бы тоже иметь место, но они содержат имя таблицы, помогающее различить дублирующиеся строки, например: constraint_column_usage , constraint_table_usage , table_constraints .

      Пред. Наверх След.
      34.16. Внутреннее устройство Начало 35.1. Схема

      Глава 20. INFORMATION_SCHEMA Таблицы

      INFORMATION_SCHEMA обеспечивает доступ к метаданным базы данных, информации о сервере MySQL, таком как имя базы данных или таблицы, типа данных столбца, или прав доступа. Другие термины, которые иногда используются для этой информации, являются словарем данных и системным каталогом.

      Примечания использования для INFORMATION_SCHEMA База данных

      INFORMATION_SCHEMA база данных в пределах каждого экземпляра MySQL, место, которое хранит информацию обо всех других базах данных, которые поддерживает сервер MySQL. INFORMATION_SCHEMA база данных содержит несколько таблиц только для чтения. Они — фактически представления, не базовые таблицы, таким образом нет никаких файлов, связанных с ними, и невозможно установить, включает их. Кроме того, нет никакого каталога базы данных с тем именем.

      Хотя можно выбрать INFORMATION_SCHEMA как база данных значения по умолчанию с a USE оператор, можно только считать содержание таблиц, не выполнить INSERT , UPDATE , или DELETE операции на них.

      Пример

      Вот пример оператора, который получает информацию от INFORMATION_SCHEMA :

      mysql> SELECT table_name, table_type, engine -> FROM information_schema.tables -> WHERE table_schema = 'db5' -> ORDER BY table_name;+------------+------------+--------+| table_name | table_type | engine |+------------+------------+--------+| fk | BASE TABLE | InnoDB || fk2 | BASE TABLE | InnoDB || goto | BASE TABLE | MyISAM || into | BASE TABLE | MyISAM || k | BASE TABLE | MyISAM || kurs | BASE TABLE | MyISAM || loop | BASE TABLE | MyISAM || pk | BASE TABLE | InnoDB || t | BASE TABLE | MyISAM || t2 | BASE TABLE | MyISAM || t3 | BASE TABLE | MyISAM || t7 | BASE TABLE | MyISAM || tables | BASE TABLE | MyISAM || v | VIEW | NULL || v2 | VIEW | NULL || v3 | VIEW | NULL || v56 | VIEW | NULL |+------------+------------+--------+17 rows in set (0.01 sec)

      Объяснение: оператор запрашивает список всех таблиц в базе данных db5 , показ только три сведения: имя таблицы, ее типа, и ее механизма хранения.

      Соображения Набора символов

      Определение для символьных столбцов (например, TABLES.TABLE_NAME ) обычно VARCHAR( N ) CHARACTER SET utf8 где N по крайней мере 64. MySQL использует сопоставление значения по умолчанию для этого набора символов ( utf8_general_ci ) для всех поисков, видов, сравнений, и других строковых операций на таких столбцах.

      Поскольку некоторые объекты MySQL представляются как файлы, поискы в INFORMATION_SCHEMA на строковые столбцы может влиять чувствительность к регистру файловой системы. Для получения дополнительной информации см. Раздел 10.1.7.9, «Сопоставление и INFORMATION_SCHEMA Поискы».

      INFORMATION_SCHEMA как Альтернатива SHOW Операторы

      SELECT . FROM INFORMATION_SCHEMA оператор предназначается как более непротиворечивый способ обеспечить доступ к информации, предоставленной различным SHOW операторы, которые поддерживает MySQL ( SHOW DATABASES , SHOW TABLES , и т.д). Используя SELECT имеет эти преимущества, по сравнению с SHOW :

      • Это соответствует правилам Кодда, потому что весь доступ делается на таблицах.
      • Можно использовать знакомый синтаксис SELECT оператор, и только должен изучить некоторые имена таблиц и имена столбцов.
      • Конструктор не должен волноваться о добавляющих ключевых словах.
      • Можно фильтровать, сортировать, связать, и преобразовать следствия INFORMATION_SCHEMA запросы в любой формат Ваши потребности приложения, такие как структура данных или текстовое представление синтаксическому анализу.
      • Этот метод является более взаимодействующим с другими системами баз данных. Например, пользователи Базы данных Oracle знакомы с запросами таблиц в словаре данных Oracle.

      Поскольку SHOW знакомо и широко используемый, SHOW операторы остаются альтернативой. Фактически, наряду с реализацией INFORMATION_SCHEMA , есть улучшения к SHOW как описано в Разделе 20.32, «Расширения SHOW Операторы».

      Полномочия

      Каждый пользователь MySQL имеет право получить доступ к этим таблицам, но может видеть только строки в таблицах, которые соответствуют объектам, для которых у пользователя есть надлежащие права доступа. В некоторых случаях (например, ROUTINE_DEFINITION столбец в INFORMATION_SCHEMA.ROUTINES таблица), пользователи, у которых есть недостаточные полномочия, видят NULL . Эти ограничения не просят InnoDB таблицы; можно видеть их с только PROCESS полномочие.

      Те же самые полномочия применяются к выбору информации от INFORMATION_SCHEMA и просмотр той же самой информации через SHOW операторы. В любом случае у Вас должно быть некоторое полномочие на объекте видеть информацию об этом.

      Соображения производительности

      INFORMATION_SCHEMA запросы, которые ищут информацию больше чем от одной базы данных, могли бы занять много времени и воздействовать на производительность. Чтобы проверить эффективность запроса, можно использовать EXPLAIN . Для получения информации об использовании EXPLAIN вывод, чтобы настроиться INFORMATION_SCHEMA запросы, см. Раздел 8.2.4, «Оптимизируя INFORMATION_SCHEMA Запросы».

      Соображения стандартов

      Реализация для INFORMATION_SCHEMA структуры таблиц в MySQL следуют за ANSI/ISO стандартный компонент SQL:2003 11 Схем. Наше намерение является приблизительным соответствием с базовой функцией SQL:2003 схема Основной информации F021.

      Пользователи SQL Server 2000 (который также следует за стандартом) могут заметить подобие strong. Однако, MySQL опустил много столбцов, которые не важны для нашей реализации, и добавили столбцы, которые специфичны для MySQL. Один такой столбец ENGINE столбец в INFORMATION_SCHEMA.TABLES таблица.

      Хотя другие DBMSs используют множество имен, как syscat или system , стандартное имя INFORMATION_SCHEMA .

      Чтобы избегать использования любого имени, которое резервируется в стандарте или в DB2, SQL-сервере, или Oracle, мы поменяли имена некоторых столбцов отмеченное » расширение MySQL » . (Например, мы изменились COLLATION к TABLE_COLLATION в TABLES таблица.) См. список зарезервированных слов около конца этой статьи: http://web.archive.org/web/20070409075643rn_1/www.dbazine.com/db2/db2-disarticles/gulutzan5 .

      Соглашения в INFORMATION_SCHEMA Ссылочные Разделы

      Следующие разделы описывают каждую из таблиц и столбцов в INFORMATION_SCHEMA . Для каждого столбца есть три сведения:

      • » INFORMATION_SCHEMA Имя » указывает на имя для столбца в INFORMATION_SCHEMA таблица. Это соответствует стандартному имени SQL, если поле «Remarks» не говорит » расширение MySQL. «
      • » SHOW Имя » указывает на эквивалентное имя поля в самом близком SHOW оператор, если есть тот.
      • «Комментарии» обеспечивают дополнительную информацию где применимый. Если это поле NULL , это означает, что значение столбца всегда NULL . Если это поле говорит » расширение MySQL, » столбец является расширением MySQL стандартного SQL.

      Много разделов указывают что SHOW оператор эквивалентен a SELECT это получает информацию от INFORMATION_SCHEMA . Для SHOW операторы, которые выводят на экран информацию для базы данных значения по умолчанию, если Вы опускаете a FROM db_name пункт, можно часто выбирать информацию для базы данных значения по умолчанию, добавляя AND TABLE_SCHEMA = SCHEMA() условие к WHERE пункт запроса, который получает информацию от INFORMATION_SCHEMA таблица.

      Для ответов на вопросы, которые часто задают относительно INFORMATION_SCHEMA база данных, см. Раздел B.7, «FAQ MySQL 5.6: INFORMATION_SCHEMA «.

      Предыдущий Затем
      Глава 19. Сохраненные Программы и Представления Домой Глава 21. MySQL Performance Schema
      Похожие публикации:
      1. Mx master 3 как подключить по bluetooth
      2. Unlock login keyring как убрать linux mint
      3. Как пишется оса
      4. Как связать qcombobox с qlineedit

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *