PHP и MySQL. Часть 1. Прямая работа с MySQL
PHP и MySQL. Часть 1. Прямая работа с MySQL
Сплав двух этих технологий позволяет создавать интерактивные web-сайты начиная с простейшей формы заказа и заканчивая сложными безопасными сайтами электронной торговли. Если вы знакомы с HTML и уже строили сайты с использованием обычного HTML, то объяснять ограничения такого подхода нет необходимости. Статическое оформление сайта не только не дает пользователям самостоятельно работать с таким сайтом, но и делает процесс его физического обновления крайне трудоемким и длительным. К тому же, если целью проекта является извлечение реальной пользы из наполнения сайта, то он должен быть динамическим: настраиваемым и содержащим информацию, изменяемую в реальном времени. Использование языка PHP и базы данных MySQL позволяет при наличии некоторых навыков сделать это достаточно быстро и качественно.
PHP. Это скриптовый язык (язык создания сценариев), разработанный специально для Web. В отличие от Java, при работе с ним нет необходимости иметь интерпретатор на машине клиента — сценарии обрабатываются на стороне сервера. В любую страницу HTML можно внедрить код PHP, и он будет обрабатываться при каждом посещении этой страницы, каждый раз генерируя HTML-код или другой вывод, наблюдаемый посетителем страницы. На текущий момент последней версией препроцессора является четвертая. Если кто еще не знает, найти файлы установки и документации можно тут: http://php.net .
MySQL. Достаточно быстрая и надежная система управления реляционными базами данных (СУРБД). Вокруг нее ходит много споров — в частности, о необходимости введения механизма транзакций и ссылочной целостности — однако на практике подавляющее большинство разработчиков используют именно эту СУРБД для работы над web-проектами. И стоит заметить, что как многопользовательский, многопоточный сервер MySQL справляется с возложенными на нее задачами. Как и все современные системы, MySQL поддерживает стандарты SQL, а потому работа с ней не является трудной для понимания и освоения.
Но PHP и MySQL, конечно, не единственный способ решения задачи по созданию web-сайта. Практически на каждом современном языке можно написать движок для сайта и привязать его к любой базе данных. Просто не стоит изобретать велосипед, когда вокруг все на нем уже ездят. Этот путь является хорошо изученным, протоптанным, так сказать. Существует масса материалов по этой теме: книги для начинающих и опытных программистов, обучающие курсы, масса примеров и уже готовых решений в Интернет. В конце концов — сосед по работе, администратор сервера, сутки напролет (мечты, мечты) ковыряющийся в консоли с исходными текстами какой-нибудь программы.
Как положительный факт, говорящий в пользу вышеупомянутого сплава, стоит упомянуть, что и язык PHP, и СУРБД MySQL предоставляются совершенно бесплатно, с исходными текстами и могут работать на множестве платформ. А это позволяет не задумываться над лицензионной чистотой написанного программного обеспечения и позволяет коду быть максимально переносимым и платформонезависимым. Кроме того, использование реляционных баз данных имеет свои весомые преимущества. Например, СУРБД имеют встроенный механизм работы с параллельным доступом, и программисту нет необходимости об этом беспокоиться. Скорость доступа значительно выше при работе с СУРБД, чем, к примеру, с двухмерными файлами. Нет необходимости перебирать постоянно данные в файле, так как достаточно просто отправить запрос на поиск необходимой информации. СУРБД имеют систему поддержки привилегий, что также немаловажно. Другими словами, использование базы данных позволяет быстро и просто ответить на вопросы, ставящиеся перед приложением, скажем: "откуда ваши клиенты?", "какой тип продукции наиболее популярен?"
А теперь перейдем к терминологии баз данных. Углубляться в нее нет необходимости: об этом было много написано и верно для любой СУРБД. База данных (в общем) состоит из таблиц. Каждая таблица представляет собой набор упорядоченных столбцов, называемых полями (атрибутами). Каждый столбец имеет уникальное имя внутри таблицы и обладает определенным типом данных. Таблицу можно также представлять как набор строк (или записей, кортежей), состоящих из столбцов. Каждый столбец соответствует одной записи в таблице. У каждой строки одни и те же столбцы (атрибуты). Некоторые столбцы имеют определенную функцию — они являются ключами. По значению в таких столбцах можно однозначно определить уникальность записи в таблицах. Ключ также часто имеет связующую функцию, то есть обеспечивает связь данных из двух разных таблиц, которая позволяет соотносить записи в двух таблицах по специальным правилам. Сама связь, образованная ключами, называется отношением между таблицами. Отношение может быть трех типов: "один к одному", "один к многим" и "многие к многим". Все вместе: таблицы, правила их отношений и связей, — называется схемой базы данных и представляет собой структурированный чертеж, визуально отображающий структуру базы данных.
Перед тем, как начать собственно проектирование базы данных, подумайте о реальных объектах. Ведь, как правило, вам предстоит моделировать предметы и отношения между ними исходя из их положения в реальном мире. Опираясь на это, на каждый "предмет" реального мира вам потребуется своя таблица.
В качестве примера возьмем такой вариант: получение заказов на поставку запчастей с завода-производителя дилерам. Нам потребуются данные о запчастях, клиентах, заказах и их особенностях. Запчасти обладают соответствующими атрибутами — каталожным кодом (номером), названием и ценой. Исходя из этого, в базе данных должны быть как минимум три таблицы: запчасти (Materials), информация о клиентах (Clients) и заказы (Orders).
Теперь, когда в общих чертах структура базы данных определена, необходимо кратко описать внешнее построение системы web баз данных. Структура такого построения очень проста и включает в себя web-браузер, web-сервер, механизм сценариев и сервер баз данных, как показано на рис.1:
Типичная транзакция web базы данных состоит из шести этапов, обозначенных цифрами:
1. Web-браузер пользователя отправляет HTTP-запрос определенной web-страницы. Например, всех материалов, которые относятся к группе "Амортизаторы". Страница с результатами поиска называется results.php.
2. Web-сервер принимает запрос на results.php, получает файл и передает его механизму PHP для обработки.
3. Механизм PHP начинает синтаксический анализ сценария. В сценарии присутствует команда подключения к базе данных и выполнения запроса в ней. PHP открывает соединение с сервером MySQL и отправляет необходимый запрос.
4. Сервер MySQL принимает запрос в базу данных, обрабатывает его, затем отправляет результаты — в данном случае список материалов (запчастей) — обратно механизму PHP.
5. PHP завершает выполнение сценария, форматируя результаты запроса в виде HTML, после чего отправляет результаты в HTML-формате web-серверу.
6. Web-сервер пересылает HTML в браузер, с помощью которого пользователь просматривает список необходимых книг.
Процесс этот, как правило, протекает вне зависимости от сценарного механизма и того, какой сервер баз данных используется. Программное обеспечение web-сервера, механизм РНР и сервер баз данных могут находиться как на одной машине, так и на разных машинах в сети. Второй вариант часто используется для повышения безопасности и разделения нагрузки. В плане производительности, конечно, второй вариант предпочтительнее. Нам же достаточно будет и одной машины, на которой стоит все сразу.
Итак, приступаем. Определим структуры таблиц, с которыми мы будем работать в будущем. Если не использовать дополнительных программ для создания структуры базы данных, достаточно будет воспользоваться Notepad'ом и сохранять каждую таблицу в отдельном файле. Для чего нам это — будет рассказано немного ниже.
create table clients
(clientID int unsigned not null auto_increment primary key,
fullname char(30) not null,
address char(50) not null,
city char(20) not null);
create table materials
(matID int unsigned not null auto_increment primary key,
matcode char(30) not null,
matname char(30) not null,
matspec char(30) not null,
cost float(6,2) not null);
create table orders
(orderID int unsigned not null auto_increment primary key,
orderdate date not null,
clientID int unsigned not null);
create table order_detail
(detID int unsigned not null auto_increment primary key,
orderID int unsigned not null,
matID int unsigned not null,
quantity int unsigned not null);
Думаю, немного пояснить стоит только один тип столбцов, а именно — идентификатор записи, однозначно определяющий строку данных в таблице. В таблице Clients поле ClientID является идентификатором, первичным ключом. Согласно записи, он представляется целым числом (тип int), причем без знака. Флаг auto_increment указывает, что значения для этого поля генерируются самим сервером по порядку начиная с единицы. Об этом программист уже может не думать: заботу об уникальности номеров записей берет на себя MySQL. Директива not null означает, что в записи всегда должно присутствовать непустое значение этого поля.
Можно задаться вопросом, почему некоторые поля в таблицах определены как тип char, когда заведомо известно, что длина строк в этих полях может быть разной? Да, можно использовать тип varchar, который имеет нефиксированную длину. Однако с типом char сервер работает быстрее, а с типом varchar он тратит некоторое время на обработку переменной длины строки. Сомнительная оптимизация, приходится согласиться. Но на данном этапе оптимизацией заниматься пока не будем, оставим определения полей таблиц как есть. Или можно самому изменить типы полей — хуже от этого точно никому не будет.
Предполагается, что на машине, на которой будут производиться все последующие манипуляции с PHP и MySQL, они уже установлены и настроены. Все последующие примеры будут показаны в системе MS Windows 2000 Pro SP3, хотя большой разницы между CMD.EXE и Console в Linux для проведения нижеследующих манипуляций я не вижу. Запускаем CMD.EXE и переходим в папку, где у нас установлен сервер MySQL. Сам сервер при этом должен работать — в окне сервисов (services) служба MySQL должна быть запущена. Выполняем mysql и видим приветствие. Для начала создадим саму базу данных, выполнив следующий скрипт:
mysql> create database auto;
В ответ нам должно выдать:
Quеry OK, 1 row affected (0.00 sec)
Затем определяем базу данных, с которой работаем:
mysql> use auto;
После ввода этой команды MySQL выдаст:
Database changed
И теперь, когда база данных выбрана, нам остается только создать таблицы. Но чтобы не набивать вручную, скормим MySQL тот текстовый файл, в котором содержится описание таблиц. Чтобы не было проблем с указанием путей к файлу: находится он прямо в том каталоге, где и mysql. После нехитрых манипуляций получаем следующее окно, утверждающее, что все таблицы были созданы (рис.2):
Чтобы не быть совсем уж голословными, можно проверить создание таблиц и их структуру следующими командами: show tables — выводит список всех таблиц в выбранной базе данных, а describe <tablename> выводит информацию о таблице с именем <tablename> — список полей, их тип, значения по умолчанию (если есть). Вполне возможно, что этими командами придется воспользоваться, особенно при написании больших проектов, в которых количество таблиц велико, и запомнить их поля практически невозможно.
Вывод первой команды будет таким:
mysql> show tables;
+----------------+
| Tables_in_auto |
+----------------+
| clients |
| materials |
| order_detail |
| orders |
+----------------+
4 rows in set (0.00 sec)
Хорошо. База данных у нас есть, таблицы у нас тоже есть. Они пока пустые, поэтому сейчас надо вставить в них немного данных.
Достаточно будет всего несколько записей, чтобы протестировать основные приемы работы. Снова создадим файл insert.sql, в который впишем следующие строки (для сокращения места приведены только первые строки insert-скрипта для каждой из таблиц):
use auto;
insert into clients
(fullname,address,city)
values
('Сидоров Петр Андреевич','Скорины д. 2 к. 1. кв. 70','Полоцк');
insert into materials
(matcode,matname,matspec,cost)
values
('001002','АМОРТИЗАТОР','для VW',12);
Скармливаем этот файл MySQL аналогичным образом, который был приведен выше. Затем можем снова проверить свою работу и запросить следующие данные: select * from clients; на что сервер нам ответит так, как показано на рис.3:
Думаю, нет смысла описывать работу с данными далее. Как изменять, удалять данные, модифицировать структуру таблиц, изменять типы полей, можно найти в любом учебнике по SQL. Ввиду того, что MySQL поддерживает DDL и DML (Data Definition Language и Data Manipulation Language), в этом нет необходимости. Любой, кто хоть раз в жизни сталкивался с базами данных на этапе разработки, сможет это сделать. Зато стоит поговорить о системе привилегий.
В основе защиты любой компьютерной системы лежит принцип минимизации привилегий. Другими словами, все, что не разрешено, должно быть запрещено. Этот принцип стандартный, он очень важен, но часто упускается из виду. То есть пользователь должен обладать наименьшим уровнем привилегий для выполнения назначенного задания. Привилегия — это право определенного пользователя выполнять определенное действие над определенным объектом. Концепция очень похожа на концепцию прав доступа к файлам. При подключении к MySQL пользователю даются определенные права, которые ограничивают круг возможных действий пользователя. Что-то он может сделать, а что-то — нет. Пользователю, который хочет выполнить запрос посредством Web, нет необходимости иметь все те привилегии, которые доступны пользователю root. Следовательно, нам потребуется завести еще одного пользователя, у которого будут все необходимые привилегии для работы с базой данных. Именно необходимые, и не больше. Пользователи наделяются правами и лишаются их с помощью команд grant и revoke соответственно. Всего существует четыре уровня привилегий: глобальный, базы данных, таблицы и столбца. С помощью команды grant можно заводить новых пользователей и предоставлять им некоторые права:
GRANT privileges [columns]
ON item
TO username [IDENTIFIED BY 'password']
[WITH GRUNT OPTION]
Параметры, заключенные в квадратные скобки, являются необязательными. Пробежимся по обязательным параметрам. Первый из них, privileges, заполняется разделенным запятыми списком привилегий, четко определенных в MySQL. Этот список невелик: select, insert, update, delete, index, alter, create, drop. Но это — привилегии для пользователей. Отдельные привилегии для администраторов тоже существуют, но тут рассматриваться пока не будут. Параметр item может содержать имя базы данных или таблицы, к которым применяются новые привилегии. Если на его месте указать '*.*', то привилегии данного пользователя распространятся на все базы данных и на все таблицы в них. В этом случае будет произведено назначение глобальных привилегий. Тот же эффект получается и от указания в параметре просто '*'. Но чаще всего в реальной жизни задаются конкретные таблицы из базы данных — например, так: auto.clients, auto.materials. В случае указания имени таблицы приобретает смысл необязательный параметр [columns], в котором можно указать список столбцов таблицы, на которые будут распространяться новые привилегии пользователя. Следующий параметр, username является именем пользователя, под которым он будет входить в MySQL. Естественно, что оно не должно совпадать с вашим рабочим именем. В MySQL имя может включать в себя название хоста, что помогает лучше различать пользователей. Скажем, если вы работаете локально, то ваше имя — root@localhost, и у вас имеются все привилегии администратора. В то же время пользователь с именем root@remotehost.com привилегий администратора не имеет и работает как обычный пользователь. Параметр 'password' представляет собой новый пароль для пользователя. Не забудьте взять его в кавычки. Если же опция [WITH GRANT OPTION] присутствует в скрипте, то пользователь сможет передавать привилегии другим пользователям. В качестве альтернативы команде grant можно самому курами править таблицы mysql.user, mysql.db, mysql.tables-priv, mysql.columns_priv; они напрямую относятся к вышеперечисленным уровням привелегий.
Необходимо заметить, что, если мы один раз предоставили пользователю какие-либо права на работу с базой данных, в случае повторного изменения его прав указывать пароль (пароль пользователя) не обязательно.
Команда revoke работает в противоположном направлении.
REVOKE privileges
ON item
FROM username
К примеру, для начала просто подключим пользователя безо всяких привилегий к базе данных:
grant usage on auto.* to denver identified by 'denv2003';
Узнав пользователя Denver поближе, вы решили прибавить ему прав для работы с базой данных:
Grant select, insert, delete, index, alter, create, drop on auto.* to denver;
Однако, как оказалось, Denver чего-то натворил в базе данных. В результате права его решили немного "обрезать":
revoke alter, create, drop on auto.* from denver;
Когда пользователю вообще не нужно пользоваться базой данных, привилегии с него можно снять вообще:
revoke all on auto.* from denver;
Разделение прав пользователей повышает общую безопасность системы. В случае использования службы web-хостинга вы получаете имя_пользователя и пароль (один и тот же) для работы из командной строки (настройка таблиц, администрирование и т.п.) и для подключения web-сценариев к MySQL (выполнение запросов к базе данных). Факт использования одного и того же имени_пользователя и пароля, наоборот, сильно снижает безопасность всей системы. Поэтому для работы сценариев необходимо создать отдельного пользователя с паролем и наделить его только теми правами, которые необходимы для нормальной работы web-содержимого сайта.
К сожалению, хоть статья и содержит в названии "РНР", разговор о нем уже не помещается в эту часть. Поэтому вопросы доступа к базе данных MySQL из Web с помощью PHP, описание некоторых дополнительных возможностей сервера MySQL отложим на следующий раз.
Денис "Denver" Мигачев, dtm@tut.by
Сплав двух этих технологий позволяет создавать интерактивные web-сайты начиная с простейшей формы заказа и заканчивая сложными безопасными сайтами электронной торговли. Если вы знакомы с HTML и уже строили сайты с использованием обычного HTML, то объяснять ограничения такого подхода нет необходимости. Статическое оформление сайта не только не дает пользователям самостоятельно работать с таким сайтом, но и делает процесс его физического обновления крайне трудоемким и длительным. К тому же, если целью проекта является извлечение реальной пользы из наполнения сайта, то он должен быть динамическим: настраиваемым и содержащим информацию, изменяемую в реальном времени. Использование языка PHP и базы данных MySQL позволяет при наличии некоторых навыков сделать это достаточно быстро и качественно.
PHP. Это скриптовый язык (язык создания сценариев), разработанный специально для Web. В отличие от Java, при работе с ним нет необходимости иметь интерпретатор на машине клиента — сценарии обрабатываются на стороне сервера. В любую страницу HTML можно внедрить код PHP, и он будет обрабатываться при каждом посещении этой страницы, каждый раз генерируя HTML-код или другой вывод, наблюдаемый посетителем страницы. На текущий момент последней версией препроцессора является четвертая. Если кто еще не знает, найти файлы установки и документации можно тут: http://php.net .
MySQL. Достаточно быстрая и надежная система управления реляционными базами данных (СУРБД). Вокруг нее ходит много споров — в частности, о необходимости введения механизма транзакций и ссылочной целостности — однако на практике подавляющее большинство разработчиков используют именно эту СУРБД для работы над web-проектами. И стоит заметить, что как многопользовательский, многопоточный сервер MySQL справляется с возложенными на нее задачами. Как и все современные системы, MySQL поддерживает стандарты SQL, а потому работа с ней не является трудной для понимания и освоения.
Но PHP и MySQL, конечно, не единственный способ решения задачи по созданию web-сайта. Практически на каждом современном языке можно написать движок для сайта и привязать его к любой базе данных. Просто не стоит изобретать велосипед, когда вокруг все на нем уже ездят. Этот путь является хорошо изученным, протоптанным, так сказать. Существует масса материалов по этой теме: книги для начинающих и опытных программистов, обучающие курсы, масса примеров и уже готовых решений в Интернет. В конце концов — сосед по работе, администратор сервера, сутки напролет (мечты, мечты) ковыряющийся в консоли с исходными текстами какой-нибудь программы.
Как положительный факт, говорящий в пользу вышеупомянутого сплава, стоит упомянуть, что и язык PHP, и СУРБД MySQL предоставляются совершенно бесплатно, с исходными текстами и могут работать на множестве платформ. А это позволяет не задумываться над лицензионной чистотой написанного программного обеспечения и позволяет коду быть максимально переносимым и платформонезависимым. Кроме того, использование реляционных баз данных имеет свои весомые преимущества. Например, СУРБД имеют встроенный механизм работы с параллельным доступом, и программисту нет необходимости об этом беспокоиться. Скорость доступа значительно выше при работе с СУРБД, чем, к примеру, с двухмерными файлами. Нет необходимости перебирать постоянно данные в файле, так как достаточно просто отправить запрос на поиск необходимой информации. СУРБД имеют систему поддержки привилегий, что также немаловажно. Другими словами, использование базы данных позволяет быстро и просто ответить на вопросы, ставящиеся перед приложением, скажем: "откуда ваши клиенты?", "какой тип продукции наиболее популярен?"
А теперь перейдем к терминологии баз данных. Углубляться в нее нет необходимости: об этом было много написано и верно для любой СУРБД. База данных (в общем) состоит из таблиц. Каждая таблица представляет собой набор упорядоченных столбцов, называемых полями (атрибутами). Каждый столбец имеет уникальное имя внутри таблицы и обладает определенным типом данных. Таблицу можно также представлять как набор строк (или записей, кортежей), состоящих из столбцов. Каждый столбец соответствует одной записи в таблице. У каждой строки одни и те же столбцы (атрибуты). Некоторые столбцы имеют определенную функцию — они являются ключами. По значению в таких столбцах можно однозначно определить уникальность записи в таблицах. Ключ также часто имеет связующую функцию, то есть обеспечивает связь данных из двух разных таблиц, которая позволяет соотносить записи в двух таблицах по специальным правилам. Сама связь, образованная ключами, называется отношением между таблицами. Отношение может быть трех типов: "один к одному", "один к многим" и "многие к многим". Все вместе: таблицы, правила их отношений и связей, — называется схемой базы данных и представляет собой структурированный чертеж, визуально отображающий структуру базы данных.
Перед тем, как начать собственно проектирование базы данных, подумайте о реальных объектах. Ведь, как правило, вам предстоит моделировать предметы и отношения между ними исходя из их положения в реальном мире. Опираясь на это, на каждый "предмет" реального мира вам потребуется своя таблица.
В качестве примера возьмем такой вариант: получение заказов на поставку запчастей с завода-производителя дилерам. Нам потребуются данные о запчастях, клиентах, заказах и их особенностях. Запчасти обладают соответствующими атрибутами — каталожным кодом (номером), названием и ценой. Исходя из этого, в базе данных должны быть как минимум три таблицы: запчасти (Materials), информация о клиентах (Clients) и заказы (Orders).
Теперь, когда в общих чертах структура базы данных определена, необходимо кратко описать внешнее построение системы web баз данных. Структура такого построения очень проста и включает в себя web-браузер, web-сервер, механизм сценариев и сервер баз данных, как показано на рис.1:
Типичная транзакция web базы данных состоит из шести этапов, обозначенных цифрами:
1. Web-браузер пользователя отправляет HTTP-запрос определенной web-страницы. Например, всех материалов, которые относятся к группе "Амортизаторы". Страница с результатами поиска называется results.php.
2. Web-сервер принимает запрос на results.php, получает файл и передает его механизму PHP для обработки.
3. Механизм PHP начинает синтаксический анализ сценария. В сценарии присутствует команда подключения к базе данных и выполнения запроса в ней. PHP открывает соединение с сервером MySQL и отправляет необходимый запрос.
4. Сервер MySQL принимает запрос в базу данных, обрабатывает его, затем отправляет результаты — в данном случае список материалов (запчастей) — обратно механизму PHP.
5. PHP завершает выполнение сценария, форматируя результаты запроса в виде HTML, после чего отправляет результаты в HTML-формате web-серверу.
6. Web-сервер пересылает HTML в браузер, с помощью которого пользователь просматривает список необходимых книг.
Процесс этот, как правило, протекает вне зависимости от сценарного механизма и того, какой сервер баз данных используется. Программное обеспечение web-сервера, механизм РНР и сервер баз данных могут находиться как на одной машине, так и на разных машинах в сети. Второй вариант часто используется для повышения безопасности и разделения нагрузки. В плане производительности, конечно, второй вариант предпочтительнее. Нам же достаточно будет и одной машины, на которой стоит все сразу.
Итак, приступаем. Определим структуры таблиц, с которыми мы будем работать в будущем. Если не использовать дополнительных программ для создания структуры базы данных, достаточно будет воспользоваться Notepad'ом и сохранять каждую таблицу в отдельном файле. Для чего нам это — будет рассказано немного ниже.
create table clients
(clientID int unsigned not null auto_increment primary key,
fullname char(30) not null,
address char(50) not null,
city char(20) not null);
create table materials
(matID int unsigned not null auto_increment primary key,
matcode char(30) not null,
matname char(30) not null,
matspec char(30) not null,
cost float(6,2) not null);
create table orders
(orderID int unsigned not null auto_increment primary key,
orderdate date not null,
clientID int unsigned not null);
create table order_detail
(detID int unsigned not null auto_increment primary key,
orderID int unsigned not null,
matID int unsigned not null,
quantity int unsigned not null);
Думаю, немного пояснить стоит только один тип столбцов, а именно — идентификатор записи, однозначно определяющий строку данных в таблице. В таблице Clients поле ClientID является идентификатором, первичным ключом. Согласно записи, он представляется целым числом (тип int), причем без знака. Флаг auto_increment указывает, что значения для этого поля генерируются самим сервером по порядку начиная с единицы. Об этом программист уже может не думать: заботу об уникальности номеров записей берет на себя MySQL. Директива not null означает, что в записи всегда должно присутствовать непустое значение этого поля.
Можно задаться вопросом, почему некоторые поля в таблицах определены как тип char, когда заведомо известно, что длина строк в этих полях может быть разной? Да, можно использовать тип varchar, который имеет нефиксированную длину. Однако с типом char сервер работает быстрее, а с типом varchar он тратит некоторое время на обработку переменной длины строки. Сомнительная оптимизация, приходится согласиться. Но на данном этапе оптимизацией заниматься пока не будем, оставим определения полей таблиц как есть. Или можно самому изменить типы полей — хуже от этого точно никому не будет.
Предполагается, что на машине, на которой будут производиться все последующие манипуляции с PHP и MySQL, они уже установлены и настроены. Все последующие примеры будут показаны в системе MS Windows 2000 Pro SP3, хотя большой разницы между CMD.EXE и Console в Linux для проведения нижеследующих манипуляций я не вижу. Запускаем CMD.EXE и переходим в папку, где у нас установлен сервер MySQL. Сам сервер при этом должен работать — в окне сервисов (services) служба MySQL должна быть запущена. Выполняем mysql и видим приветствие. Для начала создадим саму базу данных, выполнив следующий скрипт:
mysql> create database auto;
В ответ нам должно выдать:
Quеry OK, 1 row affected (0.00 sec)
Затем определяем базу данных, с которой работаем:
mysql> use auto;
После ввода этой команды MySQL выдаст:
Database changed
И теперь, когда база данных выбрана, нам остается только создать таблицы. Но чтобы не набивать вручную, скормим MySQL тот текстовый файл, в котором содержится описание таблиц. Чтобы не было проблем с указанием путей к файлу: находится он прямо в том каталоге, где и mysql. После нехитрых манипуляций получаем следующее окно, утверждающее, что все таблицы были созданы (рис.2):
Чтобы не быть совсем уж голословными, можно проверить создание таблиц и их структуру следующими командами: show tables — выводит список всех таблиц в выбранной базе данных, а describe <tablename> выводит информацию о таблице с именем <tablename> — список полей, их тип, значения по умолчанию (если есть). Вполне возможно, что этими командами придется воспользоваться, особенно при написании больших проектов, в которых количество таблиц велико, и запомнить их поля практически невозможно.
Вывод первой команды будет таким:
mysql> show tables;
+----------------+
| Tables_in_auto |
+----------------+
| clients |
| materials |
| order_detail |
| orders |
+----------------+
4 rows in set (0.00 sec)
Хорошо. База данных у нас есть, таблицы у нас тоже есть. Они пока пустые, поэтому сейчас надо вставить в них немного данных.
Достаточно будет всего несколько записей, чтобы протестировать основные приемы работы. Снова создадим файл insert.sql, в который впишем следующие строки (для сокращения места приведены только первые строки insert-скрипта для каждой из таблиц):
use auto;
insert into clients
(fullname,address,city)
values
('Сидоров Петр Андреевич','Скорины д. 2 к. 1. кв. 70','Полоцк');
insert into materials
(matcode,matname,matspec,cost)
values
('001002','АМОРТИЗАТОР','для VW',12);
Скармливаем этот файл MySQL аналогичным образом, который был приведен выше. Затем можем снова проверить свою работу и запросить следующие данные: select * from clients; на что сервер нам ответит так, как показано на рис.3:
Думаю, нет смысла описывать работу с данными далее. Как изменять, удалять данные, модифицировать структуру таблиц, изменять типы полей, можно найти в любом учебнике по SQL. Ввиду того, что MySQL поддерживает DDL и DML (Data Definition Language и Data Manipulation Language), в этом нет необходимости. Любой, кто хоть раз в жизни сталкивался с базами данных на этапе разработки, сможет это сделать. Зато стоит поговорить о системе привилегий.
В основе защиты любой компьютерной системы лежит принцип минимизации привилегий. Другими словами, все, что не разрешено, должно быть запрещено. Этот принцип стандартный, он очень важен, но часто упускается из виду. То есть пользователь должен обладать наименьшим уровнем привилегий для выполнения назначенного задания. Привилегия — это право определенного пользователя выполнять определенное действие над определенным объектом. Концепция очень похожа на концепцию прав доступа к файлам. При подключении к MySQL пользователю даются определенные права, которые ограничивают круг возможных действий пользователя. Что-то он может сделать, а что-то — нет. Пользователю, который хочет выполнить запрос посредством Web, нет необходимости иметь все те привилегии, которые доступны пользователю root. Следовательно, нам потребуется завести еще одного пользователя, у которого будут все необходимые привилегии для работы с базой данных. Именно необходимые, и не больше. Пользователи наделяются правами и лишаются их с помощью команд grant и revoke соответственно. Всего существует четыре уровня привилегий: глобальный, базы данных, таблицы и столбца. С помощью команды grant можно заводить новых пользователей и предоставлять им некоторые права:
GRANT privileges [columns]
ON item
TO username [IDENTIFIED BY 'password']
[WITH GRUNT OPTION]
Параметры, заключенные в квадратные скобки, являются необязательными. Пробежимся по обязательным параметрам. Первый из них, privileges, заполняется разделенным запятыми списком привилегий, четко определенных в MySQL. Этот список невелик: select, insert, update, delete, index, alter, create, drop. Но это — привилегии для пользователей. Отдельные привилегии для администраторов тоже существуют, но тут рассматриваться пока не будут. Параметр item может содержать имя базы данных или таблицы, к которым применяются новые привилегии. Если на его месте указать '*.*', то привилегии данного пользователя распространятся на все базы данных и на все таблицы в них. В этом случае будет произведено назначение глобальных привилегий. Тот же эффект получается и от указания в параметре просто '*'. Но чаще всего в реальной жизни задаются конкретные таблицы из базы данных — например, так: auto.clients, auto.materials. В случае указания имени таблицы приобретает смысл необязательный параметр [columns], в котором можно указать список столбцов таблицы, на которые будут распространяться новые привилегии пользователя. Следующий параметр, username является именем пользователя, под которым он будет входить в MySQL. Естественно, что оно не должно совпадать с вашим рабочим именем. В MySQL имя может включать в себя название хоста, что помогает лучше различать пользователей. Скажем, если вы работаете локально, то ваше имя — root@localhost, и у вас имеются все привилегии администратора. В то же время пользователь с именем root@remotehost.com привилегий администратора не имеет и работает как обычный пользователь. Параметр 'password' представляет собой новый пароль для пользователя. Не забудьте взять его в кавычки. Если же опция [WITH GRANT OPTION] присутствует в скрипте, то пользователь сможет передавать привилегии другим пользователям. В качестве альтернативы команде grant можно самому курами править таблицы mysql.user, mysql.db, mysql.tables-priv, mysql.columns_priv; они напрямую относятся к вышеперечисленным уровням привелегий.
Необходимо заметить, что, если мы один раз предоставили пользователю какие-либо права на работу с базой данных, в случае повторного изменения его прав указывать пароль (пароль пользователя) не обязательно.
Команда revoke работает в противоположном направлении.
REVOKE privileges
ON item
FROM username
К примеру, для начала просто подключим пользователя безо всяких привилегий к базе данных:
grant usage on auto.* to denver identified by 'denv2003';
Узнав пользователя Denver поближе, вы решили прибавить ему прав для работы с базой данных:
Grant select, insert, delete, index, alter, create, drop on auto.* to denver;
Однако, как оказалось, Denver чего-то натворил в базе данных. В результате права его решили немного "обрезать":
revoke alter, create, drop on auto.* from denver;
Когда пользователю вообще не нужно пользоваться базой данных, привилегии с него можно снять вообще:
revoke all on auto.* from denver;
Разделение прав пользователей повышает общую безопасность системы. В случае использования службы web-хостинга вы получаете имя_пользователя и пароль (один и тот же) для работы из командной строки (настройка таблиц, администрирование и т.п.) и для подключения web-сценариев к MySQL (выполнение запросов к базе данных). Факт использования одного и того же имени_пользователя и пароля, наоборот, сильно снижает безопасность всей системы. Поэтому для работы сценариев необходимо создать отдельного пользователя с паролем и наделить его только теми правами, которые необходимы для нормальной работы web-содержимого сайта.
К сожалению, хоть статья и содержит в названии "РНР", разговор о нем уже не помещается в эту часть. Поэтому вопросы доступа к базе данных MySQL из Web с помощью PHP, описание некоторых дополнительных возможностей сервера MySQL отложим на следующий раз.
Денис "Denver" Мигачев, dtm@tut.by
Компьютерная газета. Статья была опубликована в номере 11 за 2003 год в рубрике программирование :: разное
