Андрей Клочков

Что такое MySQL

Прежде чем делать выводы, стоит ли применять пакет MySQL в качестве сервера баз данных, вначале надо выяснить, что он собой представляет. MySQL - это реляционная СУБД.

MySQL поддерживает SQL (структурированный язык запросов) и может применяться в качестве SQL-сервера. Это означает, что общаться с сервером можно на языке SQL: клиент посылает серверу запрос, тот его обрабатывает и отдает клиенту только те данные, которые были получены в результате этого запроса. Тем самым клиенту не требуется выкачивать данные и производить вычисления, как, например, в Microsoft Access.

Кроме того, MySQL - это ПО с открытым кодом, т.е. его можно свободно изучать и изменять. Пакет распространяется на условиях GPL (General Public License), его можно бесплатно загрузить из Интернета (http://www.mysql.com) для некоммерческого применения.

С появлением Интернет-технологий, позволяющих создавать динамичные Web-страницы, необычайно возрос спрос и на СУБД, которые наиболее полно подходили бы для этого по быстродействию, надежности и стабильности. И здесь хорошо проявил себя пакет MySQL, который получился быстрым, простым и надежным, но, правда, за счет ухудшения функциональности (сразу оговоримся, что разработчики MySQL обещают добавить недостающие функции уже в ближайших версиях программы).

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

Недостатки

Вот краткий перечень основных функций, которых не хватает в MySQL.

Транзакции - позволяют объединить несколько SQL-запросов в одну единицу работы и в случае сбоя любого из запросов, входящего в эту единицу, выполнить откат, чтобы вернуть данные в исходное состояние. Поясним на примере.

Необходимо снять деньги с одного счета и положить на другой. Для этого нужно выполнить два SQL-запроса: первый - снять деньги с одного счета, второй - зачислить их на другой счет. Если не применять транзакции, то в случае сбоя при выполнении второго запроса деньги будут сняты со счета, но не будут зачислены на другой счет. Применение транзакций позволяет сделать откат, как если бы деньги вообще не снимались со счета.

Заметим, что при помощи команды LOCK TABLES в MySQL можно эмулировать транзакцию. Эта команда блокирует таблицу на время выполнения запросов, и тем самым обеспечивается целостность данных, но откат все равно нельзя сделать.

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

Хранимые процедуры - это несколько SQL-команд, которые хранятся в базе данных под неким именем и в совокупности выполняют некую функцию. При помощи хранимых процедур можно расширить синтаксис SQL так, что он будет похож на обычный язык программирования (например, Oracle PL/SQL). В нашем примере с переводом денег два SQL-запроса можно было бы сохранить под одним именем, а потом вызвать эту процедуру, передав ей в качестве параметров два номера счета и сумму денег. Тогда оба запроса выполнялись бы в одной транзакции.

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

SELECT auto FROM autopark WHERE massa > !Больше чего? Я понятия не имею, каково среднее значение!

Для этого среднее значение в поле massa нужно вычислить:

SELECT AVG(massa) FROM autopark

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

SELECT auto FROM autopark WHERE massa >(SELECT AVG(massa) FROM autopark)

Но в случае с MySQL среднее значение приходится находить отдельно и подставлять в другой запрос непосредственно в CGI-сценарии, что, несомненно, сказывается на производительности.

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

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

Преимущества

А теперь перечислим преимущества MySQL.

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

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

Лицензия. Раньше лицензирование MySQL было немного запутанным; сейчас эта программа для некоммерческих целей распространяется бесплатно.

Открытость кода. Благодаря этому вы сможете сами добавлять в пакет нужные функции, расширяя его функциональность так, как вам требуется. Кстати, за отдельную плату для вас это могут сделать и сами авторы MySQL. Чтобы заказать расширение MySQL у создателей пакета, просто зайдите на сайт http://www.mysql.com и заполните соответствующую форму.

Надежность. Создатели MySQL потрудились на славу: насколько мне известно, этот пакет довольно стабилен и его трудно вывести из строя. Я не отслеживаю специально сводки результатов хакерских атак на MySQL, но мне ни разу не попадалось на глаза (в отличие от тех же Web-серверов) сообщение о том, что MySQL был поврежден в результате чьего-то злого умысла.

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

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

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

Не знаю, к недостаткам или преимуществам отнести тот факт, что у MySQL нет графического интерфейса пользователя (GUI). Мне, например, удобнее написать SQL-запрос вручную (кстати, результаты его выполнения можно перенаправить в файл), чем пользоваться мастером запросов, как в Microsoft SQL Server.

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

• Winmysqladmin - входит в Windows-дистрибутив MySQL, имеет стандартный графический интерфейс и позволяет администрировать MySQL;
• MySqlManager - входит в Windows-дистрибутив MySQL, несет в себе клиентские функции (но ничего серьезного с ее помощью автору сделать так и не удалось);
• MySQL Administrator for Windows - более "продвинутая" утилита сторонних разработчиков. Позволяет зарегистрировать и подключаться одновременно к нескольким MySQL-серверам, создавать, удалять и изменять структуру баз данных и таблиц, создавать в таблицах ключи, писать SQL-запросы и сохранять их в файле:
• XMySQL - клиент MySQL для X Window-подобных систем. Предоставляет полный доступ к таблицам, допускает групповые вставки и удаления, имеет конструктор запросов и функции администрирования пакета. Программу можно найти по адресу http://web.wt.net/~dblhack/ .

Более полный список всевозможных утилит для MySQL (а он очень велик) есть по адресу http://www.mysql.com/downloads/ . Там вы найдете массу интересных и полезных вещей: экспорт данных из MySQL в Microsoft Access и обратно, драйверы ODBC и т.д.

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

• MySQLAdmin - главный инструмент администрирования MySQL. С его помощью вы можете создавать, уничтожать, изменять базы данных и полностью контролировать свой сервер.
• MySQLDump - утилита резервирования данных.
• MySQLAccess - позволяет изменять таблицы прав доступа и выводить их содержание в удобном для чтения виде.
• MySQLBug - в случае ошибки в MySQL эта утилита создает для разработчиков программы отчет об ошибках, отсылая его также в почтовый список рассылки MySQL, чтобы специалисты могли помочь решить вашу проблему.
• MySQLImport - импортирует данные из файла с разделителями в базу данных.
• MySQLShow - показывает структуру баз данных и таблиц, из которых они состоят.

Хочу заострить внимание читателя вот на чем: сейчас появились программы, работающие через CGI-интерфейс, которые предоставляют практически полный пакет услуг администрирования баз данных. Эти программы лежат на Web-серверах и представляют собой обычные CGI-скрипты. Очень часто эти скрипты размещаются в доступных для общего пользования каталогах. Опасность заключается в том, что с помощью поисковых машин любой может найти такие программы по имени файла, а потом сделать с вашей базой данных все, что его душе угодно. Эту проблему легко обойти, если размещать эти скрипты в закрытых паролем каталогах сервера. Но лучшее решение - вообще отказаться от использования таких программ на сервере.

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

SELECT title, LENGTH(title) FROM table

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

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

SELECT event FROM table WHERE time>(Unix_TIMESTAMP()-(60*60*24))

Здесь функция Unix_TIMESTAMP() вычисляет текущее время, от которого мы отнимаем одни сутки.

Применение

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

Что же касается использования MySQL в качестве корпоративной базы данных, то здесь ситуация складывается не слишком благоприятно. Сформулируем требования к SQL-cерверу корпоративной базы данных, исходя из специфики работы сотрудников, и оценим с точки зрения этих требований MySQL.

Возможность работы нескольких пользователей. Это очевидное требование следует дополнить тем, что интенсивность использования базы данных в данном случае будет значительно выше, чем на Web-сервере. В самом деле, для сайта 20 посетителей одновременно считается большим успехом, а в случае корпоративной базы таким показателем может похвастаться даже небольшая фирма. Особое внимание надо обратить на то обстоятельство, что корпоративная база данных использует более сложные пользовательские интерфейсы, чем странички на сайте; иными словами, более интенсивно посылает запросы на сервер. В техническом плане это означает, что нужна блокировка на уровне изменяемой записи. Здесь MySQL показывает себя не лучшим образом: блокировка в нем осуществляется на уровне таблиц. Это означает, в частности, что если кто-то вводит заказ, то всем запросам (анализирующим статистику, выбирающим записи для отчета и т.п.) придется ждать, пока ввод заказа закончится. В случае корпоративной базы данных это сводит на нет даже такое преимущество MySQL, как быстродействие.

Контроль целостности данных на уровне SQL-сервера. Корпоративная база данных отличается сложной схемой данных, и поддерживать целостность данных средствами клиентской программы очень трудно: одна реляция может соединять пять-семь таблиц, а число таблиц может достигать 30--40. И в этом случае существенной становится отсутствующая у MySQL возможность каскадного обновления и удаления записей в связанных таблицах.

Кроме того, мы уже упоминали, что корпоративная база данных использует более сложный интерфейс, а это обстоятельство порождает еще два требования: поддержку всех стандартных инструкций SQL (и полезных расширений), а также использование сохраненных процедур (stored procedure) и триггеров. Увы, и здесь MySQL нас не радует.

Суммируя все сказанное, можно сделать вывод, что для большинства Интернет-проектов возможностей СУБД MySQL вполне достаточно. Их будет достаточно и для хранения адресной книги во внутренней сети предприятия.

Практическое использование MySQL++

Часть 1. С чего начать

Серия контента:

Подавляющее большинство приложений в той или иной степени используют в процессе работы базы данных. При разработке таких приложений весьма полезными оказываются инструментальные средства, упрощающие взаимодействие с различными СУБД. Одним из таких инструментов является библиотека MySQL++, которая позволяет эффективно организовать доступ к базам данных MySQL из программ, написанных на языке C++. Я попытаюсь подробно разобрать функциональные возможности этой библиотеки, её достоинства и недостатки. Начну с общих характеристик MySQL++.

1. Что такое MySQL++

MySQL++ - это специализированная библиотека так называемых "обёрточных" (wrapper) методов для прикладного программного интерфейса C API для СУБД MySQL. Главная цель этой библиотеки - сделать работу с SQL-запросами такой же простой, как работа с STL-контейнерами.

Самую последнюю версию библиотеки MySQL++ можно найти на официальном Web-сайте . Тем не менее, чтобы избежать дополнительных сложностей, лучше обратиться к репозиторию пакетов для вашего дистрибутива.

1.1. Немного истории

В 1998 г. Кевин Аткинсон начал разработку библиотеки, которая по его первоначальному замыслу обеспечивала бы выполнение SQL-запросов и обработку их результатов без привязки к какой-либо конкретной СУБД. Это было отражено даже в оригинальном названии - SQL++. Все версии, предшествующие 1.0, являются плодом индивидуальной работы Кевина.

В 1999 г. библиотекой занялась компания MySQL AB. Сначала некоторую работу проделал Майкл "Монти" Видениус , затем он передал полномочия другому сотруднику MySQL AB Синише Миливоевичу . Были выпущены версии 1.0 и 1.1, после чего Аткинсон официально передал все функции сопровождения библиотеки в руки Миливоевича и полностью устранился от какого бы то ни было участия в данном проекте. Миливоевич довёл библиотеку до версии 1.7.9, которая была выпущена в середине 2001 г. К этому моменту стала очевидной невозможность реализации универсальной библиотеки SQL-запросов, независимой от конкретных реализаций СУБД. Ориентация на MySQL стала неизбежной.

После выпуска версии 1.7.9 в работе над MySQL наступил период некоторого застоя, который продолжался три года, до августа 2004 г., когда ситуацию под контроль взял Уоррен Янг . Уоррен сразу же выпустил версию 1.7.10, устранил все проблемы с компиляцией при использовании GCC, исправил большое количество ошибок, добавил новые возможности. В общем, как говорится, "процесс пошёл"...

В данной статье я рассматриваю версию 3.0.9 библиотеки MySQL++. На официальном Web-сайте эта версия объявлена как "последняя стабильная".

2. Краткое описание основных объектов (соединение, запрос, результаты)

MySQL++ обеспечивает поддержку большинства разнообразных способов и приёмов работы с базами данных. И всё же, несмотря на это разнообразие, можно выделить обобщённую схему использования API-интерфейса, предназначенного для доступа к базам данных:

• создание (открытие) соединения с базой данных,
• формирование и выполнение запроса,
• при успешном выполнении запроса - обработка итогового набора (result set) - итеративный последовательный проход по записям этого набора,
• если выполнение запроса завершилось неудачно, то необходимо обеспечить обработку ошибок (исключений).

Каждому из перечисленных выше этапов соответствует класс или иерархия классов библиотеки MySQL++. Рассмотрим их немного подробнее.

2.1. Объект Connection (соединение)

Объект Connection управляет соединением с сервером MySQL. Для того чтобы выполнять какие-либо операции в базе данных, необходим по меньшей мере один такой объект. В приложении использование всех прочих MySQL++-объектов зависит (хотя и не всегда непосредственно) от экземпляра Connection, поэтому пока в вашей программе применяются объекты библиотеки MySQL++, должен существовать и объект Connection.

В СУБД MySQL допускается использование нескольких различных типов соединений между клиентом и сервером: сокеты TCP/IP, сокеты Unix-домена, именованные программные каналы. Базовый класс Connection поддерживает все эти типы соединений. Какой именно тип соединения необходим в каждом конкретном случае, вы определяете с помощью параметров, передаваемых в метод Connection::connect(). Но если вы заранее решили, что ваше приложение будет работать только с одним типом соединений, то вашему вниманию предлагаются специализированные подклассы с упрощёнными интерфейсами. Например, если в программе предполагаются обращения исключительно к сетевому серверу баз данных, то вы можете воспользоваться подклассом TCPConnection.

2.2. Объект Query (запрос)

Чаще всего SQL-запросы создаются с использованием объекта Query, инициализируемого объектом Connection.

Query работает практически аналогично потоку вывода в стандартном C++, поэтому вы можете записывать в него данные так же, как в std::cout или std::ostringstream. Это наиболее близкий стилю языка C++ способ, используемый MySQL++ для компоновки строк запросов. В библиотеку включены манипуляторы потока с контролем типов данных, что существенно упрощает создание синтаксически корректных SQL-команд.

Ещё одной функциональной особенностью Query являются запросы-шаблоны (Template Queries), которые в определённой степени напоминают функцию языка C printf: формируется строка запроса с включаемыми в неё тэгами, обозначающими места вставки переменных элементов данных. Это удобно в тех случаях, когда в программе используются многочисленные запросы с одинаковой структурой - определив один шаблон, вы можете применять его многократно в различных частях своего приложения.

Третий метод создания запросов - использование объекта Query совместно со специализированными структурами SSQLS (Specialized SQL Structures), которые позволяют создавать структуры C++, являющиеся точным отображением схем конкретных баз данных. Это даёт объекту Query информацию, необходимую для формирования обобщённых SQL-запросов.

2.3. Наборы результатов (Result Sets)

Данные полей в наборе результатов запроса сохраняются в специальном классе с именем String (аналог стандартного std::string). Этот класс предоставляет операторы для автоматизированного преобразования объектов наборов результатов в любой базовый тип C/C++. Кроме того, MySQL++ определяет классы, такие как DateTime, которые вы можете инициализировать данными SQL-типа DATETIME. Для этих автоматических преобразований осуществляется контроль их корректности, и при ошибках конвертации выставляется соответствующий флаг-предупреждение или генерируется исключение (в зависимости от настроек библиотеки).

Для предоставления результатов выполнения SQL-команд в библиотеке MySQL++ реализованы следующие подходы.

2.3.1. Команды, не возвращающие данные

Не все команды SQL возвращают данные. Пример такой команды CREATE TABLE. Для подобных команд имеется специальный тип результата (SimpleResult), который возвращает только состояние после выполнения команды: успешное завершение выполнения команды, количество строк, на которые подействовала команда (если подразумевалось какое-либо воздействие), и т.п.

2.3.2. Запросы, возвращающие данные: структуры данных MySQL++

Самый простой способ получения набора результатов - использование метода Query::store(). Этот метод возвращает объект StoreQueryResult, являющийся производным от std::vector, представляющий собой контейнер с произвольным доступом, сформированный из Row-строк. В свою очередь, каждый объект Row является аналогом вектора (std::vector) строк (тип String), по одному объекту на каждое поле в наборе результатов. Таким образом, вы можете рассматривать StoreQueryResult как двумерный массив, т.е. чтобы получить пятое поле из второй строки, можно просто написать result. К полям можно обращаться и по их именам, поэтому возможна и такая форма записи: result["price"].

Несколько менее удобным способом работы с набором результатов является применение метода Query::use(), возвращающего объект UseQueryResult. Этот класс функционирует подобно стандартному STL-итератору. В этом случае произвольного доступа к данным уже не получится - вы последовательно проходите по строкам набора результатов с ограничением по направлению: только от начала к концу. Нет возможности возвращаться к уже пройденным строкам, и вы не знаете, сколько строк в данном наборе до тех пор, пока не достигнете последней строки. В качестве компенсации за эти неудобства вы получаете более рациональное использование оперативной памяти, поскольку нет необходимости загружать в память весь набор полностью. Это особенно важно, если приходится работать с чрезвычайно большими наборами результатов.

2.3.3. Запросы, возвращающие данные: специализированные структуры SSQLS

Доступ к результатам запросов посредством структур данных библиотеки MySQL++ представляет достаточно низкий уровень абстракции - это лучше, чем применение MySQL C API-интерфейса, но не намного. Приблизить логику решения к предметной области задачи можно с помощью специализированных структур SSQLS (Specialized SQL Structures). Эти SSQLS-объекты позволяют определять структуры языка C++, которые соответствуют структурам таблиц в схеме конкретной базы данных. Кроме того, гораздо проще состыковать SSQLS-объекты со стандартными STL-контейнерами (а следовательно, и с алгоритмами).

Преимущество этого способа заключается в том, что в программе потребуется включение минимального объёма SQL-кода. Можно выполнить запрос и получить результат в виде структур данных языка С++, доступ к которым не отличается от доступа к любым другим структурам. Доступ к полученным данным можно организовать через объект Row или же обратиться к методам библиотеки, чтобы "сбросить" результаты в STL-контейнер - с последовательным, произвольным или ассоциативным доступом - выбор за вами.

Рассмотрим следующий фрагмент кода:

Практически "чистый" код на C++, без излишеств.

Если по каким-либо причинам создание SSQLS-объектов, соответствующих структурам таблиц баз данных, нежелательно или невозможно, то вы можете использовать объект Row, и приведённый выше фрагмент кода теперь будет выглядеть так:

Различия невелики. Первый фрагмент выглядит более лаконично, но на внутреннюю логику это не влияет.

2.4. Ошибки и исключения

По умолчанию при возникновении ошибок библиотека генерирует исключения (exceptions). При необходимости вы можете настроить вместо генерации исключений установку специального флага ошибки (error flag), но помните, что исключения предоставляют более подробную информацию. В пределах одного блока try-catch вы можете выявлять различные типы ошибок.

3. Простой пример использования MySQL++

В следующем примере показано создание (открытие) соединения с базой данных, выполнение простого запроса и вывод полученных результатов - всё, о чём мы говорили до сих пор.

Для обеспечения работы примера необходимо создать базу данных MySQL с именем test_db, содержащую таблицу с именем dvd. Эта таблица может иметь, например, следующую структуру:

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

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

Что касается самой программы, то она запрашивает только поле имён (name) из таблицы dvd, и из полученного набора результатов res последовательно выводит все строки (названия DVD). Пример очень простой, но в нём проиллюстрированы три из четырёх этапов, упомянутых в начале данной статьи. О четвёртом этапе - обработке исключений - речь пойдёт в одной из следующих статей цикла.

4. Заключение

Даже после первого, достаточно беглого взгляда на функциональные свойства и характеристики библиотеки MySQL++ становится понятно, что она способна оказать существенную помощь разработчикам приложений, взаимодействующих с серверами СУБД MySQL. Библиотека легко адаптируется к потребностям программиста, не содержит ненужных излишеств и без затруднений "вписывается" в исходный код на языке C++.

В данной статье рассматривались общие характеристики библиотеки MySQL++, её функциональные возможности. В качестве иллюстрации был приведён простой пример исходного кода с применением объектов MySQL++. Следующая статья цикла будет посвящена настройке и адаптации библиотеки MySQL++ в различных проектах. В третьей статье будет подробно описано выполнение SQL-запросов и обработка их результатов. Тема четвёртой статьи - обработка ошибок и использование транзакций. В пятой статье мы рассмотрим различные типы данных и работу с ними. Специализированным формам запросов уделяется особое внимание в шестой статье. В заключительной, седьмой статье цикла будет продемонстрировано практическое применение библиотеки MySQL++ в многопоточных приложениях.

Ресурсы для скачивания

static.content.url=http://www.сайт/developerworks/js/artrating/

Zone=Linux, Open source

ArticleID=497598

ArticleTitle=Практическое использование MySQL++: Часть 1. С чего начать

От автора: вас обозвали чайником? Ну, это дело поправимое! Каждый самовар когда-то был чайником! Или каждый профессионал был когда-то самоваром? Нет, опять что-то не то! В общем, MySQL для начинающих.

Зачем чайникам MySQL

Если вы всерьез собрались связать свою жизнь с интернетом, то сразу на первых же шагах в «паутине» столкнетесь с этой СУБД. MySQL можно смело назвать «всея интернетной» системой управления базами данных. Без нее не обходится ни один более-менее серьезный ресурса, она присутствует в админке каждого хостинга. И большая часть всех популярных CMS и даже «самопальных» движков построены с ее участием.

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

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

Основные понятия

Для начала пройдемся по основным понятиям, которые мы будем упоминать в этой публикации:

База данных (БД) – основная составляющая единица СУБД. БД включает в себя таблицы, которые состоят из столбцов и записей (строк). Образуемые на пересечении ячейки содержат в себе структурированные данные определенного типа.

СУБД (система управления БД) – совокупность всех программных модулей для администрирования баз данных.

SQL – язык структурированных запросов, с помощью которого разработчик «общается» с ядром (сервером) СУБД. Как и любой программный язык, SQL имеет свой синтаксис, набор команд и операторов, поддерживаемые типы данных.

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

Подбор правильного инструмента

Изрядно «порывшись» во всем ассортименте оболочек MySQL для начинающих, понял, что таких просто не существует. Все программные продукты для администрирования СУБД требуют наличия уже установленного сервера БД. В общем, решил в очередной раз не изобретать «самокат», и остановил свой выбор на отечественном пакете Denwer. Скачать его можно на официальном сайте .

В его состав уже входят все составляющие СУБД, позволяющие новичку сразу после несложной и понятной установки приступить к практическому знакомству с MySQL. Кроме этого Denwer включает в себя еще несколько необходимых для начинающего разработчика инструментов: локальный сервер, PHP.

Первые шаги

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

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

На странице «Ура, заработало!» перейдите по одной из указанных на снимке ссылок. После чего вы попадете в phpMyAdmin – оболочку для администрирования баз данных.

Перейдя по ссылке http://downloads.mysql.com/docs/world.sql.zip, вы скачаете пример тестовой БД с официального сайта MySQL. Опять перейдите в phpMyAdmin, в основном меню сверху зайдите во вкладку «Импорт». В окне «Импорт на текущий» в первом разделе («Импортируемый файл») установите значение «Обзор вашего компьютера».

В окне проводника выберите архив со скачанным примером БД. Внизу основного окна не забудьте нажать «Ок».

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

Если все прошло хорошо, то вверху появится сообщение программы, что импорт выполнен успешно, а слева в списке БД — еще одна (word).

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

Нажмите на название БД MySQL для начинающих. Под ней отобразится список таблиц, из которых она состоит. Кликните по одной из них. Затем перейдите в пункт верхнего меню «Структура». В основной рабочей зоне отобразится структура таблицы: имена всех столбцов, типы данных и все атрибуты.

РАБОТА С БАЗОЙ ДАННЫХ MySQL СРЕДСТВАМИ РНР

Лекция. Подготовлена Прохоровым В.С.

1. СОЕДИНЕНИЕ РНР-СЦЕНАРИЕВ с таблицами MySQL

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

При взаимодействии РНР и MySQL программа взаимодействует с СУБД посредством совокупности функций.

1.1 Соединение с сервером. Функция mysql_connect

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

resource mysql_connect(]])

Эта функция устанавливает сетевое соединение с базой данных MySQL, расположенной на хосте $server (по умолчанию это localhost, т.е. текущий компьютер) и возвращает идентификатор открытого соединения. Вся дальнейшая работа ведется именно с этим идентификатором. Все другие функции, принимающие этот идентификатор (дескриптор) в качестве аргумента, будут однозначно определять выбранную базу данных. При регистрации указывается имя пользователя $username и пароль $password (по умолчанию имя пользователя, от которого запущен текущий процесс – при отладке скриптов: root, и пустой пароль):

$dbpasswd = ""; //Пароль

//Выводим предупреждение

echo("

");

Переменные $dblocation, $dbuser и $dbpasswd хранят имя сервера, имя пользователя и пароль.

1.2 Разрыв соединения с сервером. Функция mysql_close

Соединение с MySQL – сервером будет автоматически закрыто по завершении работы сценария, либо же при вызове функции mysql_close

bool mysql_close ()

Эта функция разрывает соединение с сервером MySQL, и возвращает true при успешном выполнении операции и false в противном случае. Функция принимает в качестве аргумента дескриптор соединения с базой данных, возвращаемый функцией mysql_connect.

$dblocation = "localhost"; //Имя сервера

$dbuser = "root"; //Имя пользователя

$dbpasswd = ""; //Пароль

//Осуществляем соединение с сервером базы данных

//Подавляем вывод ошибок символом @ перед вызовом функции

$dbcnx = @ mysql_connect($dblocation, $dbuser, $dbpasswd);

if (!$dbcnx) //Если дескриптор равен 0, соединение не установлено

//Выводим предупреждение

echo("

B настоящий момент сервер базы данных не доступен, поэтому корректное отображение страницы невозможно.");

if (mysql_close($dbcnx)) //разрываем соединение

echo("Соединение с базой данных прекращено");

echo("He удалось завершить соединение");

1.3 Создание базы данных. Функция CREATE DATABASE

Команда - создание базы данных доступна только администратору сервера, и на большинстве хостингов ее нельзя выполнять:

CREATE DATABASE ИмяБазыДанных

Создает новую базу данных с именем имяБазыданных.

Пример работы с этой функцией:

@mysql_query("CREATE DATABASE $dbname");

Рекомендуется везде использовать апострофы ("SQL – команда") в качестве ограничителей строк, содержащих SQL – команды. Этим можно гарантировать, что никакая $ - переменная случайно не будет интерполирована (т.е. не заменится на свое значение), и увеличится безопасность скриптов.

Команда создания базы данных CREATE DATABASE доступна только суперпользователю, и на большинстве хостингов простому пользователю ее выполнить невозможно. Она доступна только администратору сервера.

Для экспериментов создадим базу данных testbase, выполнив SQL-запрос из командной строки. Для этого нужно войти в систему MySQL и ввести в командной строке MySQL:

mysql> create database testbase;

После этого следует набрать:

mysql>use testbase;

База данных создана:

1.4 Выбор базы данных. Функция mysql_select_db

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

bool mysql_select_db(string $database_name [,resource $link_identifier])

Она уведомляет PHP, что в дальнейших операциях с соединением $link_identifier будет использоваться база данных $database_name.

Использование этой функции эквивалентно вызову команды use в SQL-запросе, т. е. функция mysql_select_db выбирает базу данных для дальнейшей работы, и все последующие SQL-запросы применяются к выбранной базе данных. Функция принимает в качестве аргументов название выбираемой базы данных database_name и дескриптор соединения resource. Функция возвращает true при успешном выполнении операции и false - в противном случае:

//Код соединения с базой данных

if (! @mysql_select_db($dbname, $dbcnx))

//Выводим предупреждение

echo("

B настоящий момент база данных не доступна, поэтому корректное отображение страницы невозможно. ");

1.5 Обработка ошибок

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

Важно аккуратно и своевременно использовать эти функции, потому что иначе отладка сценариев может усложниться.

● Функция:

int mysql_errno ()

возвращает номер последней зарегистрированной ошибки. Идентификатор соединения $link_identifier можно не указывать, если за время работы сценария было установлено только одно соединение.

● Функция:

string mysql_error()

возвращает не номер, а строку, содержащую текст сообщения об ошибке. Ее удобно применять в отладочных целях. Обычно mysql_error используют вместе с конструкцией or die (), например:

@mysql_connect("localhost", "user", "password")

or die("Ошибка при подключении к базе данных: ".mysql_error());

Оператор @, как обычно, служит для подавления стандартного предупреждения, которое может возникнуть в случае ошибки.

В последних версиях РНР предупреждения в MySQL-функциях по умолчанию не регистрируются.

1.6 Автоматизация подключения к MySQL. Файл ( config.php )

Обычно на сайте существует сразу несколько скриптов, которым нужен доступ к одной и той же базе данных.

Код, ответственный за подключение к MySQL рекомендуется выделить в отдельный файл, а затем подключать с помощью функции include к нужным скриптам.

Имеет смысл помещать функции для соединения, выбора и создания базы данных в тот же файл (config.php), где объявлены переменные с именем сервера $dblocation, именем пользователя $dbuser, паролем $dbpasswd и именем базы данных $dbname:

Листинг config.php:

//config.php код файла, содержащего параметры соединения с сервером и выбора базы данных

//выводит сообщения об ошибках соединения в браузер

$dblocation = "localhost"; //Имя сервера

$dbname = "вставить имя базы" //Имя базы данных: создаваемой или уже существующей

$dbuser = "root"; //Имя пользователя базы данных

$dbpasswd = ""; //Пароль

//Осуществляем соединение с сервером базы данных

//Подавляем вывод ошибок символом @ перед вызовом функции

$dbcnx=@mysql_connect($dblocation,$dbuser,$dbpasswd);

if (!$dbcnx) //Если дескриптор равен 0, соединение с сервером базы данных не установлено

//Выводим предупреждение

echo("

В настоящее время сервер базы данных не доступен, поэтому корректное отображение страницы невозможно.

//Создаем базу данных $dbname – это может делать только суперпользователь

//Если база данных уже существует, будет некритическая ошибка

@mysql_query("CREATE DATABASE if not exists $dbname’);

//Код соединения с базой данной: осуществляем однозначный выбор только что созданной базы или уже существующей базы данных

//Подавляем вывод ошибок символом @ перед вызовом функции

if(!@mysql_select_db($dbname, $dbcnx)) //Если дескриптор равен 0, соединение с базой данных не установлено

//Выводим предупреждение

echo("

В настоящее время база данных не доступна, поэтому корректное отображение страницы невозможно.

//Небольшая вспомогательная функция, которая выводит сообщение

//об ошибке в случае ошибки запроса к базе данных

function puterror($message)

echo("");

2. ВЫПОЛНЕНИЕ ЗАПРОСОВ К БАЗЕ ДАННЫХ

2.1 Создание таблицы. Функция CREATE TABLE:

CREATE TABLE Имя Таблицы (ИмяПоля тип, ИмяПоля тип,)

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

Листинг test_11.php. Программа, создающая новую таблицу в базе данных:

include "config.php";//Подключение к серверу и выбор базы данных

mysql_query("CREATE TABLE if not exists people

id INT AUTO_INCREMENT PRIMARY KEY,

or die("MySQL error: ".mysql_error());

Этот сценарий создает новую таблицу people с двумя полями. Первое поле имеет тип INT (целое) и имя id. Второе - тип TEXT (текстовая строка) и имя name.

Если таблица существует, сработает конструкция or die ().

Необязательная фраза if not exists, если она задана, говорит серверу MySQL, что он не должен генерировать сообщение об ошибке, если таблица с указанным именем уже существует в базе данных.

Оптимальное использование MySQL

Введение

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

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

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

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

Какие данные нужно хранить в MySQL

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

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

Оптимизация запросов

В ситуациях, когда реально требуется получить только определенную порцию данных из MySQL, можно использовать ключ LIMIT для функции SELECT . Это полезно, когда, например, нужно показать результаты поиска чего-либо в базе данных. Допустим, в базе есть список товаров, которые предлагает Ваш интернет-магазин. Выдавать весь список товаров в нужной категории несколько негуманно по отношению к пользователю - каналы связи с интернет не у всех быстрые и выдача лишних ста килобайт информации зачастую заставляет пользователяй провести не одну минуту в ожидании результатов загрузки страницы. В таких ситуациях информацию выдают порциями по, допустим, 10 позиций. Неправильно делать выборку из базы всей информации и фильтрацию вывода скриптом. Гораздо оптимальнее будет сделать запрос вида

select good, price from books limit 20,10

В результате, MySQL "отдаст" Вам 10 записей из базы начиная с 20-й позиции. Выдав результат пользователю, сделайте ссылки "Следующие 10 товаров", в качестве параметра передав скрипту следующую позицию, с которой будет делаться вывод списка товаров, и используйте это число при генерации запроса к MySQL.

Также следует помнить, что при составлении запросов к базе данных (SQL queries) следует запрашивать только ту информацию, которая Вам реально нужна. Например, если в базе 10 полей, а в данный момент реально требуется получить только два из них, вместо запроса

select * from table_name

используйте конструкцию вида

select field1, field2 from table_name

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

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

select title from books where author=" Иванов"

Также есть ключ LIKE, который позволяет искать поля, значения которых "похожи" на заданный шаблон:

select title from books where author like " Иванов%"

В данном случае MySQL выдаст названия книг, значения поля author у которых начинаются с " Иванов"

Ресурсоемкие операции

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

Индексы

Индексы используют для более быстрого поиска по значению одного из полей. Если индекс не создается, то MySQL осуществляет последовательный просмотр всех полей с самой первой записи до самой последней, осуществляя сопоставление выбранного значения с исходным. Чем больше таблица и чем больше в ней полей, тем дольше осуществляется выборка. Если же у данной таблицы существует индекс для рассматриваемого столбца, то MySQL сможет сделать быстрое позиционирование к физическому расположению данных без необходимости осуществлять полный просмотр таблицы. Например, если таблица состоит из 1000 строк, то скорость поиска будет как минимум в 100 раз быстрее. Эта скорость будет еще выше, если есть необходимость обратиться сразу ко всем 1000 столбцам, т.к. в этом случае не происходит затрат времени на позиционирование жесткого диска.

В каких ситуациях создание индекса целесообразно:

1. Быстрый поиск строк при использовании конструкции WHERE
2. Поиск строк из других таблиц при выполнении объединения
3. Поиск значения MIN() или MAX() для проиндексированного поля
4. Сортировка или группировка таблицы в случае, если используется проиндексированное поле
5. В некоторых случаях полностью теряется необходимость обращаться к файлу данных. Если все используемые поля для некоторой таблицы цифровые и формируют левосторонний индекс для некоторого ключа, то значения могут быть возвращены полностью из индексного дерева с намного большей скоростью.
6. Если выполняются запросы вида
   SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
   и существует смешанный индекс для полей col1 и col2, то данные будут возвращены напрямую. Если же созданы отдельные индексы для col1 и для col2, то оптимизатор попробует найти наиболее ограниченный индекс путем определения того, какой из индексов может найти меньше строк, и будет использовать этот индекс для получения данных.
   Если у таблицы есть смешанный индекс, то будет использоваться любое левостороннее совпадение с существующим индексом. Например, если есть смешанный индекс 3-х полей (col1, col2, col3), то индексный поиск можно осуществлять по полям (col1), (col1, col2) и (col1, col2, col3).

Подробнее об индексировании

Поддержка соединения

Как Вы наверняка знаете, для работы с MySQL-сервером необходимо предварительно установить с ним соединение, предъявив логин и пароль. Процесс установки соединения может продолжаться гораздо большее время, нежели непосредственная обработка запроса к базе после установки соединения. Следуя логике, надо избегать лишних соединений к базе, не отсоединяясь от нее там, где это можно сделать, если в дальнейшем планируется продолжить работу с SQL-сервером. Например, если Ваш скрипт установил соединение к базе, сделал выборку данных для анализа, не нужно закрывать соединение к базе, если в процессе работы этого же скрипта Вы планируете результаты анализа поместить в базу.

Также можно поддерживать так называемое persistent (постоянное) соединение к базе, но это возможно в полном объеме при использовании более сложных сред программирования, чем php или perl в обычном CGI-режиме, когда интерпретатор соответствующего языка разово запускается веб-сервером для выполнения пришедшего запроса.