Создание сайта. Часть 8: MySQL

Продолжаем тему создания сайта. Полное содержание серии - в конце статьи.

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

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

Скорее всего по MySQL тоже есть книги, но мне хватило русской справки в формате chm. Я читал ее, как книгу :-), последовательно, от раздела к разделу, стараясь понять все, что там написано. Конечно все сразу не запомнилось, но на то он и справочник, чтоб обращаться к нему за уточнениями.

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

MySQL для общения с разработчиком использует консоль. В принципе работать через консоль можно и достаточно терпимо, но мне этот вариант показался неудобным. Выход: phpMyAdmin или Navicat. Первая софтина – это система php-скриптов, естественно работает через веб-сервер с интерфейсом в браузере. Скорее всего такой сервис вам предоставит хостер, но можете его развернуть и на локальной машине. phpMyAdmin бесплатный, есть на русском в инете, хорошо документирован.

Navicat так же значительно удобнее консоли MySQL. Программа платная, но все равно добавьте ее в свои веб-инструменты. Есть дистрибутив под Windows. Ссылку не даю, т.к. скорее всего вы будете искать ломанную версию ;) поэтому все в Гугль.

Практические советы

Я предполагаю, что вы уже работаете с MySQL или прочитали хотя бы что-то из предложенного выше.

Выбор расширения

Если скрипты пишете на PHP, то у вас на вооружение есть несколько расширений для общения с MySQL-сервером. Рекомендую сразу садиться на MySQLi или PDO, т.к. древнее расширение "MySQL" упраздняется с PHP 5.5. Почитайте справку PHP по этим расширениям, сами решите, какой стиль выбрать, процедурный или объектно-ориентированный. Учтите еще ньанс, в PHP что-то работает только в процедурном стиле, что-то - в обоих. Например, mysqli->connect_error() корректно работает начиная с PHP 5.2.9 (так сказано в справке).

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

Организация БД

Тип таблиц в своей БД предлагаю использовать MyISAM. Почитайте справку по MySQL, может выберете другой. Кодировку ставьте такую же, которая у вас в в мета-теге charset заявлена и в которой скрипты написаны. Я использовал cp1251 (cp1251 _general_ci), теперь есть проблемы с переносом локальной копии сайта на Линуху. Параметр в скобках – collation, он определяет способ, с помощью которого следует упорядочивать и сравнивать данные в БД. Для одной кодировки текста существует как правило несколько collation. В частности, cp1251_general_ci - сравнение нечувствительное к регистру, cp1251_bin - чувствительное к регистру.

Храните в базе данных только тексты, а картинки в отдельном каталоге. Почему картинки отдельно? Хотя бы потому, что не нужно лезть в БД, чтобы их обновить или вписать в html-код. Но это ваш выбор, можно вообще все запихать в базу. Вам нужно определиться, какую именно информацию хранить в БД, на какие таблицы эту инфу разбить, какими ключами ее связать, что именно индексировать для поиска. Проще говоря, постоить структуру БД. И этот вопрос нужно четко решить сразу (кроме индексов) еще до написания скриптов, т.к. дальнейшие изменения структуры приведут к конкретной каше в коде, проверено на личном опыте. Что-то потом менять придется, но чем точнее опишете структуру априори, тем лучше.

По поводу "что хранить в БД?" могу привести пример. Все статьи моего блога хранятся в таблице MyISAM. Они попадают под ежедневное резервирование данных на хостинге. Построены индексы по заголовкам, дате и основному тексту. А вот данные с формы обратной связи – обычный текстовый файл. Я не стал его пихать в базу данных, т.к. нужен он будет крайне редко и не требуется поиск или бэкап инфы из этого файла.

Индексы. Что это и зачем, вы конечно выясните и будете использовать. Примите на заметку: если у вас будет поиск по нескольким полям сразу, то лучше создать для них комбинированный индекс, а не отдельный на каждое поле. Пример такой необходимости посмотрите в статье «Подбор мотоцикла по росту». Здесь запрос в базу идет как минимум по двум полям: типу байка и высоте. На Хабре есть статья по этому поводу и несколько полезных комментариев к ней.

Бинарные логи

Если будете поднимать MySQL-сервер на своем компе, включите на нем бинарные логи. В текущем конфиге my.ini (для Linux- my.cnf) должно быть:

Если указанного выше нет, нужно дописать и перегрузить MySQL-сервак. Логи пишутся в файлы mysql-bin.000001, mysql-bin.000002 и т.д. Список этих файлов хранится в mysql-bin.index (обычный текстовый файл). Сами файлы складываются в [..\MySQL Server 5.1\data\]

Как восстановить данные из log-bin? Лог-файлы читаются в блокноте, но там много спец символов, что не айс. Нужно в консоли использовать [..MySQL Server 5.1\bin\mysqlbinlog.exe].

Замечание: все программы из каталога [..\MySQL Server 5.1\bin\] при запусках читают my.cnf, независимо от ОСи. Об этом нужно помнить. К примеру, я прописал там параметр "default-character-set=cp1251" для другой консольной проги. Так вот mysqlbinlog.exe его не понимает, поэтому перед запуском нужно его закомментить.

Утилита mysqlbinlog.exe обрабатывает файлы журнала и выводит пригодный к использованию SQL код прямо в консоль. Вывод можно перенаправить в файл. Пример такого вызова:

Вместо "somebase" - имя вашей БД. если укажете несуществующую базу, файл все равно будет создан, но без данных естествено.

В результирующий файл попадает много всего, но вполне можно разобрать sql-команды UPDATE или DELETE (особенно с подсветкой кода :) Кстати, если было удаление записи, то в лог пишется именно команда удаления, типа "DELETE FROM `parser` WHERE (`id`='41') LIMIT 1". Т.е. если удалил запись, то восстановить ее по последним данным лога не получится, придется искать в предыдущих, где она была создана или изменена.

Бинарные логи сами по себе не удаляются, они копятся на диске. Ручная зачистка: останавливаем MySQL-сервер, удаляем ВСЕ mysql-bin.0000nn и mysql-bin.index и запускаем сервак. Он сам все создаст заново.

Кроме логирования сервером рекомендую делать ручные backup-ы базы, вдруг чего натворите и потеряете текущие данные. Такие бэкапы выглядят как SQL-запросы на добавление/замещение записей, только в файле все записи выбранных для копирования таблиц, иногда вместе с описанием структуры таблиц. Процедура называется "dump" (англ. - свалка). По-русски так и говорят - "дамп БД". Не знаю, как в консоли, но phpMyAdmin и Navicat точно умеют их делать. А еще можно самому накатать скрипт для создания бэкапов, но это вариант для опытных программистов.

MySQL injection

Это очень большая тема. Вероятно она будет сложной для новичка, но знать про эту атаку нужно до того, как это произойдет с вами. Я даю вам только словосочетание для поиска, читайте, разбирайтесь. Вот полезная статья, с которой можно начать.