Укажите какие типы алгоритмов используются в mysql при описании представлений
Перейти к содержимому

Укажите какие типы алгоритмов используются в mysql при описании представлений

  • автор:

Представления (VIEW) в MySQL

В комментариях Хабра упоминались вопросы по использованию представлений. Данный топик является обзором представлений, появившихся в MySQL версии 5.0. В нем рассмотрены вопросы создания, преимущества и ограничения представлений.

Что такое представление?

Представление (VIEW) — объект базы данных, являющийся результатом выполнения запроса к базе данных, определенного с помощью оператора SELECT, в момент обращения к представлению.

Представления иногда называют «виртуальными таблицами». Такое название связано с тем, что представление доступно для пользователя как таблица, но само оно не содержит данных, а извлекает их из таблиц в момент обращения к нему. Если данные изменены в базовой таблице, то пользователь получит актуальные данные при обращении к представлению, использующему данную таблицу; кэширования результатов выборки из таблицы при работе представлений не производится. При этом, механизм кэширования запросов (query cache) работает на уровне запросов пользователя безотносительно к тому, обращается ли пользователь к таблицам или представлениям.

Представления могут основываться как на таблицах, так и на других представлениях, т.е. могут быть вложенными (до 32 уровней вложенности).

Преимущества использования представлений:

  1. Дает возможность гибкой настройки прав доступа к данным за счет того, что права даются не на таблицу, а на представление. Это очень удобно в случае если пользователю нужно дать права на отдельные строки таблицы или возможность получения не самих данных, а результата каких-то действий над ними.
  2. Позволяет разделить логику хранения данных и программного обеспечения. Можно менять структуру данных, не затрагивая программный код, нужно лишь создать представления, аналогичные таблицам, к которым раньше обращались приложения. Это очень удобно когда нет возможности изменить программный код или к одной базе данных обращаются несколько приложений с различными требованиями к структуре данных.
  3. Удобство в использовании за счет автоматического выполнения таких действий как доступ к определенной части строк и/или столбцов, получение данных из нескольких таблиц и их преобразование с помощью различных функций.

Ограничения представлений в MySQL

  • нельзя повесить триггер на представление,
  • нельзя сделать представление на основе временных таблиц; нельзя сделать временное представление;
  • в определении представления нельзя использовать подзапрос в части FROM,
  • в определении представления нельзя использовать системные и пользовательские переменные; внутри хранимых процедур нельзя в определении представления использовать локальные переменные или параметры процедуры,
  • в определении представления нельзя использовать параметры подготовленных выражений (PREPARE),
  • таблицы и представления, присутствующие в определении представления должны существовать.
  • только представления, удовлетворяющие ряду требований, допускают запросы типа UPDATE, DELETE и INSERT.

Создание представлений

Для создания представления используется оператор CREATE VIEW, имеющий следующий синтаксис:

CREATE [ OR REPLACE]
[ALGORITHM = ]
VIEW view_name [(column_list)]
AS select_statement
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

* This source code was highlighted with Source Code Highlighter .

view_name — имя создаваемого представления. select_statement — оператор SELECT, выбирающий данные из таблиц и/или других представлений, которые будут содержаться в представлении

  1. OR REPLACE — при использовании данной конструкции в случае существования представления с таким именем старое будет удалено, а новое создано. В противном случае возникнет ошибка, информирующая о сществовании представления с таким именем и новое представление создано не будет. Следует отметить одну особенность — имена таблиц и представлений в рамках одной базы данных должны быть уникальны, т.е. нельзя создать представление с именем уже существующей таблицы. Однако конструкция OR REPLACE действует только на представления и замещать таблицу не будет.
  2. ALGORITM — определяет алгоритм, используемый при обращении к представлению (подробнее речь об этом пойдет ниже).
  3. column_list — задает имена полей представления.
  4. WITH CHECK OPTION — при использовании данной конструкции все добавляемые или изменяемые строки будут проверяться на соответствие определению представления. В случае несоответствия данное изменение не будет выполнено. Обратите внимание, что при указании данной конструкции для необновляемого представления возникнет ошибка и представление не будет создано. (подробнее речь об этом пойдет ниже).

CREATE VIEW v AS SELECT a.id, b.id FROM a,b;

* This source code was highlighted with Source Code Highlighter .

Для избежания такой ситуации нужно явно указывать имена полей представления

CREATE VIEW v (a_id, b_id) AS SELECT a.id, b.id FROM a,b;

* This source code was highlighted with Source Code Highlighter .

Того же результата можно добиться, используя синонимы (алиасы) для названий колонок:

CREATE VIEW v AS SELECT a.id a_id, b.id b_id FROM a,b;

* This source code was highlighted with Source Code Highlighter .

CREATE VIEW v AS SELECT group_concat( DISTINCT column_name oreder BY column_name separator ‘+’ ) FROM table_name;

* This source code was highlighted with Source Code Highlighter .

  1. Если в обоих операторах встречается условие WHERE, то оба этих условия будут выполнены как если бы они были объединены оператором AND.
  2. Если в определении представления есть конструкция ORDER BY, то она будет работать только в случае отсутствия во внешнем операторе SELECT, обращающемся к представлению, собственного условия сортировки. При наличии конструкции ORDER BY во внешнем операторе сортировка, имеющаяся в определении представления, будет проигнорирована.
  3. При наличии в обоих операторах модификаторов, влияющих на механизм блокировки, таких как HIGH_PRIORITY, результат их совместного действия неопределен. Для избежания неопределенности рекомендуется в определении представления не использовать подобные модификаторы.

Алгоритмы представлений

Существует два алгоритма, используемых MySQL при обращении к представлению: MERGE и TEMPTABLE.

В случае алгоритма MERGE, MySQL при обращении к представлению добавляет в использующийся оператор соответствующие части из определения представления и выполняет получившийся оператор.

В случае алгоритма TEMPTABLE, MySQL заносит содержимое представления во временную таблицу, над которой затем выполняется оператор обращенный к представлению.
Обратите внимание: в случае использования этого алгоритма представление не может быть обновляемым (см. далее).

При создании представления есть возможность явно указать используемый алгоритм с помощью необязательной конструкции [ALGORITHM = ]
UNDEFINED означает, что MySQL сам выбирает какой алгоритм использовать при обращении к представлению. Это значение по умолчанию, если данная конструкция отсутствует.

Использование алгоритма MERGE требует соответствия 1 к 1 между строками таблицы и основанного на ней представления.

Пусть наше представление выбирает отношение числа просмотров к числу ответов для тем форума:

CREATE VIEW v AS SELECT subject, num_views/num_replies AS param FROM topics WHERE num_replies>0;

* This source code was highlighted with Source Code Highlighter .

Для данного представления каждая строка соответствует единственной строке из таблицы topics, т.е. может быть использован алгоритм MERGE. Рассмотрим следующее обращение к нашему представлению:

SELECT subject, param FROM v WHERE param>1000;

* This source code was highlighted with Source Code Highlighter .

В случае MERGE алгоритма MySQL включает определение представления в использующийся оператор SELECT: заменяет имя представления на имя таблицы, заменяет список полей на определения полей представления и добавляет условие в части WHERE с помощью оператора AND. Итоговый оператор, выполняемый затем MySQL, выглядит следующим образом:

SELECT subject, num_views/num_replies AS param FROM topics WHERE num_replies>0 AND num_views/num_replies>1000;

* This source code was highlighted with Source Code Highlighter .

Если в определении представления используются групповые функции (count, max, avg, group_concat и т.д.), подзапросы в части перечисления полей или конструкции DISTINCT, GROUP BY, то не выполняется требуемое алгоритмом MERGE соответствие 1 к 1 между строками таблицы и основанного на ней представления.

Пусть наше представление выбирает количество тем для каждого форума:

CREATE VIEW v AS SELECT forum_id, count (*) AS num FROM topics GROUP BY forum_id;

* This source code was highlighted with Source Code Highlighter .

Найдем максимальное количество тем в форуме:

* This source code was highlighted with Source Code Highlighter .

Если бы использовался алгоритм MERGE, то этот запрос был бы преобразован следующим образом:

SELECT MAX ( count (*)) FROM topics GROUP BY forum_id;

* This source code was highlighted with Source Code Highlighter .

Выполнение этого запроса приводит к ошибке «ERROR 1111 (HY000): Invalid USE of GROUP function», так как используется вложенность групповых функций.

В этом случае MySQL использует алгоритм TEMPTABLE, т.е. заносит содержимое представления во временную таблицу (данный процесс иногда называют «материализацией представления»), а затем вычисляет MAX() используя данные временной таблицы:

CREATE TEMPORARY TABLE tmp_table SELECT forum_id, count (*) AS num FROM topics GROUP BY forum_id;
SELECT MAX (num) FROM tmp_table;
DROP TABLE tpm_table;

* This source code was highlighted with Source Code Highlighter .

  1. В случае UNDEFINED MySQL пытается использовать MERGE везде где это возможно, так как он более эффективен чем TEMPTABLE и, в отличие от него, не делает представление не обновляемым.
  2. Если вы явно указываете MERGE, а определение представления содержит конструкции запрещающие его использование, то MySQL выдаст предупреждение и установит значение UNDEFIND.

Обновляемость представлений

  1. Соответствие 1 к 1 между строками представления и таблиц, на которых основано представление, т.е. каждой строке представления должно соответствовать по одной строке в таблицах-источниках.
  2. Поля представления должны быть простым перечислением полей таблиц, а не выражениеями col1/col2 или col1+2.

Обновляемое представление может допускать добавление данных (INSERT), если все поля таблицы-источника, не присутствующие в представлении, имеют значения по умолчанию.

Обратите внимание: для представлений, основанных на нескольких таблицах, операция добавления данных (INSERT) работает только в случае если происходит добавление в единственную реальную таблицу. Удаление данных (DELETE) для таких представлений не поддерживается.

  • Изменение данных (UPDATE) будет происходить только если строка с новыми значениями удовлетворяет условию WHERE в определении представления.
  • Добавление данных (INSERT) будет происходить только если новая строка удовлетворяет условию WHERE в определении представления.
  • Для LOCAL происходит проверка условия WHERE только в собственном определении представления.
  • Для CASCADED происходит проверка для всех представлений на которых основанно данное представление. Значением по умолчанию является CASCADED.

punbb > CREATE OR REPLACE VIEW v AS
-> SELECT forum_name, `subject`, num_views FROM topics,forums f
-> WHERE forum_id=f.id AND num_views>2000 WITH CHECK OPTION ;
Query OK, 0 rows affected (0.03 sec)

punbb > UPDATE v SET num_views=2003 WHERE subject= ‘test’ ;
Query OK, 0 rows affected (0.03 sec)
Rows matched: 1 Changed: 0 WARNINGS: 0

punbb > SELECT subject, num_views FROM topics WHERE subject= ‘test’ ;
+———+————+
| subject | num_views |
+———+————+
| test | 2003 |
+———+————+
1 rows IN SET (0.01 sec)

* This source code was highlighted with Source Code Highlighter .

Однако, если мы попробуем установить значение num_views меньше 2000, то новое значение не будет удовлетворять условию WHERE num_views>2000 в определении представления и обновления не произойдет.

punbb > UPDATE v SET num_views=1999 WHERE subject= ‘test’ ;
ERROR 1369 (HY000): CHECK OPTION failed ‘punbb.v’

* This source code was highlighted with Source Code Highlighter .

Не все обновляемые представления позволяют добавление данных:

punbb > INSERT INTO v (subject,num_views) VALUES ( ‘test1’ ,4000);
ERROR 1369 (HY000): CHECK OPTION failed ‘punbb.v’

* This source code was highlighted with Source Code Highlighter .

Причина в том, что значением по умолчанию колонки forum_id является 0, поэтому добавляемая строка не удовлетворяет условию WHERE forum_id=f.id в определении представления. Указать же явно значение forum_id мы не можем, так как такого поля нет в определении представления:

punbb > INSERT INTO v (forum_id,subject,num_views) VALUES (1, ‘test1’ ,4000);
ERROR 1054 (42S22): Unknown COLUMN ‘forum_id’ IN ‘field list’

* This source code was highlighted with Source Code Highlighter .

С другой строны:

punbb > INSERT INTO v (forum_name) VALUES ( ‘TEST’ );
Query OK, 1 row affected (0.00 sec)

* This source code was highlighted with Source Code Highlighter .

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

Удачи в работе с представлениями!

SQL-Ex blog

Подобно другим системам управления базами данных, MySQL позволяет вам создавать представления, которые дают возможность пользователям и приложениям получать данные без предоставления им непосредственного доступа к лежащим в основе таблицам. Вы можете думать о представлении как о предварительно определенном запросе, который MySQL выполняет при обращении к представлению. MySQL хранит определение представления как объект базы данных, подобно табличному объекту.

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

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

Несмотря на преимущества, которыми обладает представление, имеется и ряд ограничений. Например, MySQL не позволяет вам создавать индекс на представлении, определять на представлении триггер или ссылаться на системную или определяемую пользователем переменную в запросе представления.

Кроме того, допускается удаление таблицы, на которую ссылается представление, без возникновения ошибки. Это происходит только тогда, когда пользователь или приложение попытается обратиться к представлению; MySQL подаст сигнал, который может серьезно повлиять на рабочую нагрузку. (Полное изложение ограничений для представлений и другую информацию о представлениях можно найти в документации MySQL.)

Подготовка вашей среды MySQL

Представление является сохраненным запросом, который MySQL выполняет, когда выполняется обращение к представлению. Обычно запрос представляет оператор SELECT, который запрашивает данные из одной или более таблиц. Начиная с MySQL 8.0.19, запросом может служить оператор VALUES или TABLE, но в большинстве случаев используется оператор SELECT, поэтому этот вариант мы и будем рассматривать в данной статье.

Любые таблицы, к которым обращается оператор SELECT, должны существовать к моменту, когда вы создаете представление. Помимо этого, мало что требуется, чтобы добавить представление в базу данных, разве что убедиться, что у вас есть необходимые разрешения на создание представления и запрос к таблицам, лежащим в его основе (эту тему я буду обсуждать подробно далее в данной серии).

Для демонстрации примеров в этой статье я создал базу данных travel и добавил таблицы manufacturers и airplanes. Те же самые таблицы я использовал и обновлял в предыдущей статье в этой серии. Чтобы добавить базу данных и таблицы в ваш экземпляр MySQL, вы можете выполнить следующий код SQL:

DROP DATABASE IF EXISTS travel; 
CREATE DATABASE travel;
USE travel;
CREATE TABLE manufacturers (
manufacturer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
manufacturer VARCHAR(50) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (manufacturer_id) )
ENGINE=InnoDB AUTO_INCREMENT=1001;
CREATE TABLE airplanes (
plane_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
plane VARCHAR(50) NOT NULL,
manufacturer_id INT UNSIGNED NOT NULL,
engine_type VARCHAR(50) NOT NULL,
engine_count TINYINT NOT NULL,
max_weight MEDIUMINT UNSIGNED NOT NULL,
wingspan DECIMAL(5,2) NOT NULL,
plane_length DECIMAL(5,2) NOT NULL,
parking_area INT GENERATED ALWAYS AS ((wingspan * plane_length)) STORED,
icao_code CHAR(4) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (plane_id),
CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id)
REFERENCES manufacturers (manufacturer_id) )
ENGINE=InnoDB AUTO_INCREMENT=101;

Чтобы выполнить эти операторы, скопируйте код и вставьте его на вкладку запросов в Workbench. Затем вы можете выполнить операторы все сразу или по одному в представленном порядке. Вы должны создать таблицу manufacturers до создания таблицы airplanes, поскольку у таблицы airplanes имеет внешний ключ, который ссылается на таблицу manufacturers.

Когда вы создаете представление, хорошей идеей является протестировать оператор SELECT в представлении, а затем выполнить представление после создания запроса. Для этого вам потребуются некоторые тестовые данные. Два следующих оператора INSERT добавят небольшое количество данных в две таблицы, достаточное, чтобы начать создавать представление:

INSERT INTO manufacturers (manufacturer) 
VALUES ('Airbus'), ('Beechcraft'), ('Piper');
INSERT INTO airplanes
(plane, manufacturer_id, engine_type, engine_count,
max_weight, wingspan, plane_length, icao_code)
VALUES
('A380-800', 1001, 'jet', 4, 1267658, 261.65, 238.62, 'A388'),
('A319neo Sharklet', 1001, 'jet', 2, 166449, 117.45, 111.02, 'A319'),
('ACJ320neo (Corporate Jet version)', 1001, 'jet', 2, 174165, 117.45, 123.27, 'A320'),
('A300-200 (A300-C4-200, F4-200)', 1001, 'jet', 2, 363760, 147.08, 175.50, 'A30B'),
('Beech 390 Premier I, IA, II (Raytheon Premier I)', 1002, 'jet', 2, 12500, 44.50, 46.00, 'PRM1'),
('Beechjet 400 (from/same as MU-300-10 Diamond II)', 1002, 'jet', 2, 15780, 43.50, 48.42, 'BE40'),
('1900D', 1002, 'Turboprop', 2,17120, 57.75, 57.67, 'B190'),
('PA-24-400 Comanche', 1003, 'piston', 1, 3600, 36.00, 24.79, 'PA24'),
('PA-46-600TP Malibu Meridian, M600', 1003, 'Turboprop', 1, 6000, 43.17, 29.60, 'P46T'),
('J-3 Cub', 1003, 'piston', 1, 1220, 38.00, 22.42, 'J3');

Я буду обсуждать оператор INSERT более подробно позже в этой серии, поэтому не буду тратить много времени на это сейчас. На этом этапе вам нужно знать, что первый оператор добавляет три строки в таблицу manufacturers, а второй — 10 строк в таблицу airplanes.

Вы можете выполнить оба оператора одновременно или по очереди. Вы должны их выполнять в указанном здесь порядке , чтобы не нарушить ограничение внешнего ключа, определенного в таблице airplanes. Поскольку внешний ключ создан на столбце manufacturer_id, значения в этом столбце должны уже иметься в таблице manufacturers. Опять таки, я собираюсь глубже обсудить это в последующих статьях.

Это все, что вам необходимо для подготовки среды MySQL, поэтому вы сможете выполнять последующие примеры из этой статьи. Как и для первых двух статей этой серии, я использовал редакцию MySQL Community на компьютере с Windows для построения примеров. Я создавал примеры в Workbench, который устанавливается вместе с редакцией Community.

Создание представления в MySQL

Если вы знакомились с предыдущей статьей в этой серии, то знаете, что Workbench GUI предоставляет вкладку Table — удобное средство для построения и редактирования определения таблицы. Однако другая вкладка для создания представлений — вкладка View — не так полезна. Одно из главных достоинств вкладки Table, особенно для новичков, состоит в том, что на ней представлены различные опции, доступные для определения таблицы. Вкладка View не имеет такого преимущества. В основном она позволяет вам построить оператор CREATE VIEW, как вы могли бы просто это сделать на вкладке запросов.

В этой статье я использовал вкладку запросов для всех примеров. Однако полезно знать, как попасть на вкладку View в случае, если вы захотите использовать ее. Чтобы открыть вкладку, выберите базу данных в навигаторе (Navigator), а затем щелкните на кнопке создания представления (create view) на панели инструментов Workbench. (Эта кнопка находится справа от иконки создания таблицы и имеет всплывающую подсказку Create a new view in the active schema in the connected server.) При щелчке на этой кнопке Workbench откроет вкладку View, как показано на рис.1.

Рис.1 Добавление представления с помощью Workbench GUI

На этой вкладке вы можете сделать только одно — написать оператор CREATE VIEW. Эта вкладка предоставляет несколько заглушек для начала работы с оператором, только и всего. Вы сами пишете фактический оператор (или копируете его из другого источника). Отсюда вы должны пройти еще пару экранов, подобных тем, которые сопровождали вкладку Table, но без преимущества автоматической генерации оператора.

Использовать вкладку View ли нет — вам решать. В любом случае вы должны еще придумать оператор CREATE VIEW. Имея это в виду, рассмотрим следующий пример, который создает представление на базе двух таблиц в базе данных travel.

CREATE VIEW airbus_info 
AS
SELECT a.plane, a.engine_type, a.engine_count,
a.wingspan, a.plane_length, a.parking_area
FROM airplanes a INNER JOIN manufacturers m
ON a.manufacturer_id = m.manufacturer_id
WHERE m.manufacturer = 'airbus'
ORDER BY a.plane;

В основном оператор CREATE VIEW требует, чтобы вы указали имя представления, затем ключевое слово AS с последующим оператором SELECT. В нашем случае представление называется airbus_info.

Сам оператор SELECT относительно прост. Он делает внутреннее соединение таблиц airplanes и manufacturers по столбцу manufacturer_id. Предложение WHERE ограничивает результаты только теми строками, для которых значением производителя является airbus, а предложение ORDER BY сортирует результаты по столбцу plane.

При создании представления полезно выполнить отдельно оператор SELECT, чтобы убедиться в том, что возвращаются требуемые результаты. Я придерживался относительно простых операторов, поскольку более подробное обсуждение операторов SELECT я планирую позже в этой серии. Однако используемый оператор выполняет необходимое — он демонстрирует как создавать представление. Оператор SELECT в представлении может быть как простым, так и сложным, таким, какой вам нужен.

Когда вы выполняете оператор CREATE VIEW, MySQL добавляет определение представления в активную базу данных. Вы можете проверить, что представление было создано, в навигаторе. Вам может потребоваться обновить навигатор, чтобы увидеть список. Ваше представление должно появиться в узле Views, как показано на рис.2.

Рис.2 Просмотр нового представления в навигаторе

Отсюда вы можете открыть определение представления на вкладке View. Если навести курсор на имя представления в навигаторе, вы увидите несколько небольших иконок для доступа к дополнительным функциям. Одна из их выглядит как гаечный ключ. Если щелкнуть по ней, Workbench откроет вкладку View и отобразит код представления, как показано на рис.3.

Рис.3 Доступ к определению представления в Workbench GUI

MySQL добавил несколько опций для определения представления, которые не включаются в исходный оператор CREATE VIEW. Все эти опции имеют значения по умолчанию. Мы скоро обсудим их.

Вы можете отредактировать оператор CREATE VIEW непосредственно на вкладке View. После внесения необходимых изменений щелкните Apply, проверьте код, снова щелкните Apply и затем Finish. Теперь закройте вкладку View. Мы не будем использовать этот метод в настоящей статье, но знайте, что есть такой вариант, если вы решите этим воспользоваться.

Вы можете также открыть оператор CREATE VIEW на вкладке запросов. Выполните щелчок правой кнопкой в навигаторе, укажите Send to SQL Editor (послать в редактор SQL), а затем щелкните Create Statement (оператор создания). Оператор вытягивается в одну строку, что довольно неудобно. Однако вы можете исправить это, щелкнув кнопку переформатирования на панели инструментов вкладки. (Эта кнопка выглядит как метелка и имеет всплывающую подсказку Beautify/reformat the SQL script.)

Доступ к информации о представлении в помощью базы данных INFORMATION_SCHEMA

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

Информация, доступная посредством базы данных INFORMATION_SCHEMA, представлена как набор представлений, одно из который называется Views. Через него вы можете получить доступ к информации о представлениях, которые вы создаете в базе данных MySQL. Например, следующий запрос возвращает подробную информацию о представлении, которое мы только что создали в базе данных travel:

SELECT * FROM information_schema.views 
WHERE table_schema = 'travel';

Оператор использует символ звездочки (*), указывающий, что должны возвращаться все столбцы. Он также задает имя целевого представления в предложении FROM, включающее имя базы данных (information_schema) с последующей разделительной точкой и именем представления (view). Кроме того, оператор содержит предложение WHERE, которое отфильтровывает все представления за исключением тех, которые содержатся в базе данных travel.

Если вы выполните этот оператор, результаты должны включать строку для представления airbus_info, которое вы создали выше. Информация включает детали о том, как было определено представление. Смысл многих столбцов в результирующем наборе должен быть понятен, а остальные будут обсуждаться ниже. Но я бы хотел особо выделить два столбца, а именно: CHECK_OPTION и IS_UPDATEABLE.

Оба столбца относятся к обновляемым представлениям, о которых я расскажу позже в этой серии. Пока нужно иметь в виду, что MySQL поддерживает обновляемые представления, и что представление считается обновляемым, если оно удовлетворяет определенному набору критериев. MySQL автоматически определяет, является ли представление обновляемым, на основе этих критериев. Если оно является обновляемым, MySQL устанавливает столбец IS_UPDATEABLE в значение YES (истина). В противном случае — в NO (ложь).

Следует отметить еще один столбец — VIEW_DEFINITION, который содержит запрос в представлении. Если вы хотите увидеть только этот запрос и ничего более, вы можете ограничить оператор SELECT только этими результатами:

SELECT view_definition 
FROM information_schema.views
WHERE table_schema = 'travel'
AND table_name = 'airbus_info';

Теперь предложение SELECT задает только возврат столбца view_definition, а не всех столбцов. Однако даже при ограничении результатов они все равно читаются с трудом. К счастью, Workbench предоставляет полезную функцию для просмотра значения столбца в полном объеме. Для этого выполните щелчок правой кнопкой непосредственно на значении в результатах и щелкните Open Value in Viewer (открыть значение в просмотрщике). MySQL откроет отдельное окно, в котором отобразит значение, как показано на рис.4.

Рис.4 Проверка оператора SELECT представления в просмотрщике

Здесь вы можете просматрвать как бинарные, так и текстовые значения. Кроме того, вы можете сохранить оператор в текстовом файле, щелкнув на кнопке Save. Вы не можете сохранить бинарное значение в файл.

Запрос к представлению в MySQL

Как отмечалось ранее, я подробно рассмотрю оператор SELECT позже в этой серии, но я хочу дать вам краткий обзор, как можно написать запрос к представлению, после его создания. По большей части он работает так же, как и запрос к таблице. Следующий пример показывает самый базовый оператор SELECT c представлением airbus_info, указанным в предложении FROM:

SELECT * FROM airbus_info;

При выполнении этого оператора SELECT MySQL выполняет запрос в определении представления и возвращает результаты, подобные тем, как если бы запрос выполнялся непосредственно. На рис.5 показаны результаты, которые должен вернуть ваш оператор SELECT.

Рис.5 Просмотр результатов после вызова представления airbus_info

Вы можете также уточнить ваш оператор SELECT, как вы могли бы это сделать в представлении. Например, следующий оператор SELECT включает предложение WHERE, которое ограничивает результаты теми, для которых значение parking_area больше 20000:

SELECT * FROM airbus_info 
WHERE parking_area > 20000
ORDER BY parking_area DESC;

Этот оператор включает также предложение ORDER BY, которое сортирует результаты по столбцу parking_area в убывающем порядке. Когда вы включаете предложение ORDER BY при вызове представления, оно отменяет предложение ORDER BY в самом определении представления (если оно там имеется). На рис.6 показаны данные, которые теперь должен вернуть ваш оператор SELECT:

Рис.6 Уточнение результатов запроса при обращении к представлению

Как можно увидеть, результаты теперь включают только две строки, и эти строки отсортированы по значениям столбца parking_area, при этом сначала идет наибольшее значение. Предложение ORDER BY в определении представления сортирует данные по значениям plane.

Обновление определения представления в MySQL

MySQL предоставляет несколько методов модификации определения представления. Одним и них является использование оператора CREATE VIEW, который включает предложение OR REPLACE, что продемонстрировано следующим примером:

CREATE OR REPLACE 
ALGORITHM = MERGE
DEFINER = CURRENT_USER
SQL SECURITY INVOKER
VIEW airbus_info
AS
SELECT a.plane, a.engine_type, a.engine_count,
a.wingspan, a.plane_length, a.parking_area
FROM airplanes a INNER JOIN manufacturers m
ON a.manufacturer_id = m.manufacturer_id
WHERE m.manufacturer = 'airbus'
ORDER BY a.plane;

Добавив предложение OR REPLACE после ключевого слова CREATE, вы можете уточнить существующее определение представления, а затем выполнить оператор без генерации ошибки, что было бы в случае отсутствия этого предложения. Это отличный инструмент при активной разработке базы данных и постоянном изменении схемы.

Помимо предложения OR REPLACE определение представления включает несколько других элементов, которых не было в исходном операторе CREATE VIEW, который вы создали. Первым является предложение ALGORITHM, которое сообщает MySQL использовать алгоритм MERGE при обработке представления. Алгоритм выполняет слияние вызывающего оператора и определения представления таким образом, чтобы сделать обработку представления более эффективной. Этот алгоритм также требуется для того, чтобы представление было обновляемым. Более подробно об алгоритмах смотрите в документации MySQL.

Две других новых опции — DEFINER и SQL SECURITY управляют тем, какой аккаунт пользователя имеет привилегии на использование при обработке представления. Опция DEFINER указывает, какой аккаунт назначается создателем приложения. В нашем случае опция устанавливается в CURRENT_USER, поэтому «определителем» является пользователь, который фактически выполнил оператор CREATE VIEW.

Опция SQL SECURITY может принимать аргументом либо DEFINER, либо INVOKER. Если задан DEFINER, то представление будет обрабатываться под аккаунтом указанного DEFINER. Если указан INVOKER, то представление будет обрабатываться под аккаунтом пользователя, который обращается к представлению.

После выполнения предыдущего оператора CREATE VIEW, вы можете проверить, что опции были обновлены, выполнив запрос к представлению INFORMATION_SCHEMA.VIEWS:

SELECT table_name AS view_name, 
is_updatable, definer, security_type
FROM information_schema.views
WHERE table_schema = 'travel'
AND table_name = 'airbus_info';

На рис.7 показаны результаты, которые я получил при выполнении оператора SELECT в моей системе. Поскольку я выполнял оператор CREATE VIEW как пользователь root, столбец DEFINER показывает мое имя пользователя и localhost в качестве экземпляра сервера. Результаты также показывают значение INVOKER в столбце SECURITY_TYPE, который соответствует опции SQL SECURITY.

Рис.7 Просмотр результатов из базы данных INFORMATION_SCHEMA

Вы могли заметить, что INFORMATION_SCHEMA.VIEWS не возвращает подробной информации об указанном алгоритме. Однако в этом случае столбец IS_UPDATABLE установлен в значение YES, которое указывает, что представление является обновляемым, и работает только с алгоритмом MERGE. Тогда, если столбец установлен в NO, вы не можете быть уверены, какой алгоритм был использован, поскольку другие факторы могут повлиять на то, почему представление не является обновляемым.

Другим подходом, который вы можете использовать для обновления определения представления, является выполнене оператора ALTER VIEW. Синтаксис оператора ALTER VIEW почти тот же, что синтаксис CREATE VIEW. Например, следующий оператор ALTER VIEW подобен предыдущему оператору CREATE VIEW за исключением того, что он еще указывает имена столбцов, используемые для возвращаемого результирующего набора:

ALTER 
ALGORITHM = MERGE
DEFINER = CURRENT_USER
SQL SECURITY INVOKER
VIEW airbus_info
(plane, engine, count, wingspan, length, area)
AS
SELECT a.plane, a.engine_type, a.engine_count,
a.wingspan, a.plane_length, a.parking_area
FROM airplanes a INNER JOIN manufacturers m
ON a.manufacturer_id = m.manufacturer_id
WHERE m.manufacturer = 'airbus'
ORDER BY a.plane;

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

После выполнения оператора ALTER VIEW вы можете затем написать запрос к представлению, как вы делали это раньше, только теперь вы должны использовать указанные имена столбцов. Например, следующий оператор SELECT ограничвает и упорядочивает результаты, как вы видели в предыдущем примере:

SELECT plane, wingspan, length, area 
FROM airbus_info
WHERE area > 20000
ORDER BY area DESC;

Обратите внимание, что оператор SELECT использует новые имена столбцов, которые также отображаются в результатах, как показано на Рис.8.

Рис.8 Запрос к обновленному представлению airbus_info

Задавая имена возвращаемых столбцов, вы можете абстрагировать базовую схему еще больше, что обеспечивает еще один уровень безопасности, облегчая изменение схемы базовых таблиц. Например, если вы измените имя столбца базовой таблицы, вам может потребоваться обновить запрос в представлении, но не имена возвращаемых столбцов, избегая каких-либо нарушений в работе вызывающих приложений.

Удаление представления в MySQL

Удаление представления — относительно простой процесс. Вы можете использовать Workbench GUI или выполнить оператор DROP VIEW. Чтобы использовать GUI, выполните щелчок правой кнопкой на представлении в навигаторе и щелкните Drop View. Когда появится диалоговое окошко, щелкните Drop Now.

Для использования оператора DROP VIEW вам нужно указать только имя представления и, опционально, предложение IF EXISTS, как показано в следующем примере:

DROP VIEW IF EXISTS airbus_info;

Вы можете убедиться, что представление было удалено, выполнив следующий оператор SELECT к INFORMATION_SCHEMA.VIEWS:

SELECT * FROM information_schema.views 
WHERE table_schema = 'travel';

Результаты больше не будут содержать строку для представления airbus_info.

Работа с представлениями в базу данных MySQL

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

Я возвращусь к теме представлений позже в этой серии, когда буду обсуждать выборку и модификацию данных. А пока информация, представленная в этой статье, должна дать вам хороший старт для работы с представлениям. По мере того, как вы будете становиться более искусными в написании операторов SELECT, вы сможете создавать более эффективные представления, которые будут возвращать разнообразную информацию.

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись

Типы данных в MySQL

В этой инструкции рассказываем про основные типы данных в MySQL и даем советы, где лучше использовать тот или иной тип.

Эта инструкция — часть курса «MySQL для новичков».

Смотреть весь курс

Введение

При создании таблиц в MySQL для каждого столбца нужно указывать тип данных. Он определяет, какие данные там могут храниться, как будут обрабатываться и сколько места будут занимать. В MySQL все типы данных делятся на несколько классов: числовые типы, символьные, дата/время и так далее. В каждом классе есть несколько типов данных, которые внешне могут быть похожи, но их поведение или принципы хранения отличаются. Важно выбрать правильный тип сразу при создании таблицы, потому что потом готовую структуру и приложения будет сложней переделать.

В этой статье мы расскажем об основных типах данных в MySQL и дадим советы, где лучше использовать тот или иной тип.

Возможности MySQL в «Облачных базах данных»

Числовые типы

Используются для хранения чисел. Бывают целочисленными, с плавающей точкой и с фиксированной точкой.

Целые числа

Хранят только целые числа, без дробной части. Делятся на signed (со знаком) и unsigned (без знака). Типы singed позволяют хранить как положительные, так и отрицательные значения. Типы unsigned хранят только положительные числа, но зато диапазон значений больше. Это может быть полезно в случаях, когда хранимые значения заведомо не могут быть отрицательным. Например, количество товара или идентификатор записи в таблице.

Тип Размер (байт) Диапазон signed Диапазон unsigned
TINYINT 1 -128 … 127 0 … 255
SMALLINT 2 -3 2768 … 32 767 0 … 65 535
MEDIUMINT 3 -8 388 608 … 8388607 0 … 16 777 215
INT 4 2 147 483 648 … 2 147 483 647 0 … 42 94 967 295
BIGINT 8 -2 63 … 2 63 -1 0 … 2 64 -1

Числа с плавающей точкой

Хранят приблизительные значения. Не резервируют определенное количество бит для целочисленной или дробной частей. Поэтому у всех значений в таблице количество до и после запятой будет разным.

Тип Размер (байт) Диапазон
FLOAT 4 -3.402823466E+38 … -1.175494351E-38 и 1.175494351E-38 … 3.402823466E+38
DOUBLE 8 -1.7976931348623157E+308 … -2.2250738585072014E-308 и2.2250738585072014E-308 … 1.7976931348623157E+308

До версии MySQL 8.0.17 эти типы данных поддерживали диапазон unsigned, но он работал не так, как с целыми числами. Беззнаковый диапазон просто запрещал сохранять отрицательные значения, при этом максимальное значение не увеличивалось. Начиная с версии 8.0.17, использование unsigned не рекомендуется и считается устаревшим. В будущих версиях MySQL поддержку беззнакового диапазона для этого типа данных могут вовсе удалить.

Числа с фиксированной точкой

Эти типы данных используются для сохранения заданной точности. Такие числа резервируют определенное количество бит для целочисленной и дробной частей. Независимо от того, насколько большое или маленькое число, оно всегда будет использовать одно и то же количество бит для сохранения каждой части. Эти типы данных подходят для случаев, когда важна точность, например, деньги.

В MySQL для хранения чисел с фиксированной точкой используются типы DECIMAL(M,D) и NUMERIC(M,D), но по факту это синонимы. Можно использовать любой из этих типов, результат будет одинаковым. В таких столбцах максимально можно хранить до 65 чисел вместе с целочисленной и дробной частями.

Символьные (строковые)

Символьные типы используются для хранения текстов. Есть два основных типа: CHAR и VARCHAR. С точки зрения пользователя они выглядят похоже, но MySQL хранит и обрабатывает их по-разному.

  • CHAR хранит строку фиксированной длины до 255 символов. Если длина вставляемой записи меньше, то MySQL автоматически дополняет значение пробелами. Например, если мы указали тип CHAR(10) и сохранили строку «Привет», то по факту в БД будет храниться строка «Привет » (обратите внимание на четыре пробела в конце строки).

Кажется, что это лишняя трата места. Но если во всех ваших записях хранятся строки примерно одной длины, то тип CHAR будет очень производительным. Например, его можно использовать для хранения хешей, у которых длина всегда одинакова.

  • VARCHAR хранит строки переменной длины до 65 535 символов. Причем в памяти хранится именно та длина, которая была указана при создании. VARCHAR занимает меньше места, чем CHAR, но подвержен фрагментации и из-за этого может проигрывать в скорости обработки данных.

Текстовые и бинарные

Текстовые (TEXT) и бинарные (BLOB) типы данных используются для хранения больших объемов текста или двоичных данных. Эти типы похожи, но отличаются по способу хранения и обработки внутри MySQL.

  • BLOB обрабатывается как двоичные данные. В нем не хранится набор символов, а операции сортировки и сравнения основаны на числовых значениях байтов.
  • TEXT обрабатывается как символьные строки. В нем хранится именно набор символов, а значения сортируются и сравниваются на основе сопоставления набора символов..

Текстовые типы отлично подходят для хранения больших текстов: статей, докладов и так далее. А бинарные типы могут хранить любые файлы или мультимедиа-контент.

Тип Размер (байт) Макс. размер символов
TINYTEXT / TINYBLOB 255 255
TEXT / BLOB 65 535 65 535
MEDIUMTEXT / MEDIUMBLOB 16 777 215 2 24 -1
LONGTEXT / LONGBLOB 4 294 967 295 2 32 -1

Кажется, что типы TINYTEXT и TEXT похожи на CHAR и VARCHAR. Но разница в том, что MySQL не умеет индексировать текстовые и бинарные типы и не может использовать индексы для сортировки.

Дата/время

Типы данных, которые позволяют работать с датой и временем.

Тип Размер (байт) Описание
DATE 3 Только дата в формате YYYY-MM-DD. Допустимые значения от 1000-01-01 до 9999-12-31
DATETIME 8 Дата и время в формате YYYY-MM-DD HH:MM:SS. Допустимые значения от 1000-01-01 00:00:00 до 9999-12-31 23:59:59
TIMESTAMP 4 Дата и время. Хранится в виде количества секунд, прошедших с 1 января 1970 года по гринвичу. Занимает в два раза меньше места, чем тип DATETIME. Но при этом диапазон ограничен значениями от 1970-01-01 00:00:01 до 2038-01-09 03:14:07
TIME 3 Только время в формате HH:MM:SS. Допустимые значения от 00:00:00 до 23:59:59
YEAR(N) 1 Только год в формате YYYY или YY. Допустимые значения от 1901 до 2155 или от 70 до 69 (1970 — 2069)

JSON

Это относительно новый тип данных, который появился в MySQL версии 5.7.8. Он позволяет нативно хранить и обрабатывать данные в JSON-формате.

В отличие от хранения объектов в виде текста, в использовании специального типа данных есть несколько преимуществ:

  1. Валидация JSON-объектов. Если попытаться сохранить неправильный JSON, MySQL сгенерирует ошибку.
  2. Возможность нативно работать с JSON, выбирать и обновлять только отдельные части объектов, а не весь объект целиком.
  3. MySQL сохраняет тип JSON в специальном внутреннем формате. Такой способ более производительный, чем работа с JSON в виде строки.

Составные типы

Строковые типы данных, которые могут хранить значения только из заранее определенного списка. Несмотря на то, что список значений состоит из строк, в самих таблицах хранятся только числа, которые ассоциированы со справочником возможных значений. Поэтому они занимают мало места.

  • ENUM может хранить только одно значение из списка, занимает 1-2 байта.
  • SET может хранить одновременно до 64 значений из списка, занимает от 1 до 8 байт.

Заключение

Мы рассмотрели основные типы данных и полей в MySQL, объяснили разницу между схожими типами. Теперь вы можете создавать свои структуры БД, используя полученные знания.

Создание базы данных в MySQL

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

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