Работа с MySQL: Подробнее
Автор: Дмитрий Лебедев
Источник: detail.phpclub.net
Полезные советы как избежать
пустой траты системных ресурсов, как закрыть возможные дыры в
защите. Самые популярные грабли.
1. Запросы на выборку
данных (SELECT)
- Во избежание путаницы полей (если встречаются поля с
одинаковыми названиями) используйте в запросах оператор AS:
"SELECT table1.id as id1, table2.id as id2". Это поможет
избежать ошибок в запросе (например, если не указана
таблица, а поле с таким названием есть в нескольких
запрашиваемых таблицах, mysql выдаёт ошибку), а так же вы
избежите недоразумений при работе с полученными данными
(echo $row["id1"] писать гораздо проще, чем $row[$x]).
- Данные типа DATE, TIME, DATETIME и TIMESTAMP можно
форматировать с помощью функции date_format (см. руководство
по mysql). Используйте его, и не форматируйте данные через
php - это не просто "самодеятельность", а ещё и растрата
системных ресурсов.
- По возможности минимально используйте LEFT JOIN для
объединения таблиц. Это весьма трудоёмкая операция для базы
данных.
- Там, где можно, используйте идентификаторы - выборка
данных при указании ключевого поля происходит быстрее, чем
при указании обычного.
- Вместо "WHERE id=1 OR id=3 OR id=232" можно использовать
встроенную функцию IN: "WHERE id IN (1,3,232)".
- Если нужен текстовый поиск, осторожней со знаком "%". Во
всяком случае, запросы типа somefield LIKE '%a%' лучше не
делать - опять же слишком трудоёмкая операция. По крайней
мере, надо фильтровать слова и отрезать те, которые короче 3
символов.
- Используйте минимум необходимых полей в запросе. "SELECT
* FROM sometable" выполняется медленнее, чем "SELECT id FROM
sometable", тем более если в таблице много данных. Для
подсчёта количества строк в таблице вообще (или подпадающих
под некоторое условие) достаточно одного поля.
- Разбивайте данные на страницы, используя оператор LIMIT.
Это экономит время выполнения запроса и уменьшает объем
страницы, которую получает пользователь.
Даже если вам не грозит "падение" от наплыва посетителей,
лучше взять себе в привычку, чтобы потом не было проблем с
адаптацией к новым задачам. Теперь о безопасности работы.
- Старайтесь не допускать внесения в базу данных символа
одинарной кавычки ("'"), поскольку это служебный символ
запросов БД. Перед внесением в базу поле можно обработать
функцией str_replace: $somefield = str_replace("'", "'",
$somefield);
К тому же это лишний барьер на пути взломщиков вашего
сайта. Пример "взлома" простой:
mysql_query("UPDATE users SET password=PASSWORD('$passwd') WHERE login='$login'");
Если кавычку не обработать на входе, злоумышленник может в
качестве логина сунуть строку "vasya_pupkin' OR login LIKE
'%". В базу данных залетит запрос: mysql_query("UPDATE users
SET password=PASSWORD('$passwd') WHERE login='vasya_pupkin' OR
login LIKE '%'"); То есть все пароли будут одинаковые. Это
только один пример. Итак,
- Обрабатывайте данные, получаемые из адресной строки или
из формы, и приводите их к нужному типу во избежание ошибок
и "взломов" сайта. (ещё пример: если требуется
идентификатор, то есть целое число, надо обработать его с
помощью intval: $id = intval($id)).
2. Запросы на вставку
строки (INSERT)
- Поле идентификатора вставлять не нужно. На это есть
свойство поля AUTO_INCREMENT.
Забавно читать, как в форуме пишут:
- Как мне быть с генератором случайных чисел?! неправильно работает!
- А зачем тебе?
- Да в базе id использовать...
В общем, не надо самодеятельности
- Если в поле формата DATE, TIME, DATETIME или TIMESTAMP
надо вставить текущее время, используйте встроенную в mysql
функцию NOW: "INSERT INTO vote (ip, date) VALUES
($REMOTE_ADDR, NOW())"
- Хранимые в базе пароли лучше прикрыть функцией php md5:
"INSERT INTO user (login, pass) VALUES ('$login', ".
md5($pass). ")" "SELECT * FROM user WHERE login='$login' AND
pass=". md5($pass)
Советы, кажется, уже исчерпаны. Напоследок. С недавних пор
я стал думать, что при написании скриптов, работающих с БД,
надо ориентироваться не только на глупого и шаловливого
посетителя, но и на криворукого администратора. Даже если мы
внимательно будем следить за текстом, который вставляем в
текстовое поле (одинарные кавычки не писать, делать их
автозамену в Word-е, белое не носить), вероятность попадания
служебных символов в запрос ненулевая.
3. Постраничный вывод
Регулярно в форуме задают один и тот же вопрос: как сделать
постраничный вывод. И каждый раз человеку отвечают: "Легко! m
строк, начиная с n-ной: Select запрос Limit $n,$m". На самом
деле не так всё просто.
Я уже писал про синтаксис параметра LIMIT, однако, без
толку. Для полноценного постраничного вывода строк из базы
требуется большее. Требуется
- Обработать номер страницы (в том числе проверить, не
больше ли он общего количества страниц)
- Нарисовать навигационную строку (чтобы не просто
"вперед-назад", а с ссылками на несколько соседних страниц)
Тут-то и начинаются главные проблемы.
Недавно я работал над сайтом, в котором эти постраничные
выводы в статистике были в каждом списке (а списков было
много!). Тут-то и созрело решение, как свести все эти штучки к
простому и единому решению. Получились четыре функции, которые
я теперь использую везде, где нужен постраничный вывод данных,
и не напрягаю попусту голову проблемой (как же я делал это
там, как бы вынуть этот код оттуда?).
Первая функция — для внутреннего пользования двумя
следующими. Берёт номер страницы, общее количество строк и
количество строк на странице и выдаёт номер страницы, уже
проверенный. Вторая берёт то же самое, проверяет номер
страницы и выдаёт парамерт LIMIT либо полный (LIMIT n,m), либо
краткий (LIMIT m), если это первая страница, либо ничего не
выдаёт. Третья функция из тех же трёх параметров и адреса для
ссылки делает навигационную строку. Ещё одна функция выдаёт
число для нумерованного списка.
Этого достаточно для нормальной работы с постраничным
выводом данных. Посмотрим, что получается в коде
программы:
<?php
// кол-во строк в страницах
$in_page = 10;
// получаем
количество строк
$amount = @mysql_result(mysql_query("SELECT count(id) as goods_total FROM
goods"),0);
// рисуем
навигационную строку и пишем начало таблицы
print("<div align=center>". <b>draw_bar($page, $amount, $in_page,
"goods.php?page=")</b>. "</div>\n<table>");
// формируем запрос к базе
$goods_result = mysql_query("SELECT id, name, description, price
FROM goods
ORDER BY name, price
". <b>get_limit($page, $amount, $in_page)</b>);
// получаем номер для нумерованного
списка
$count = <b>get_count_limit($page, $amount, $in_page)</b>;
// выводим строки
while ($good_row = mysql_fetch_array($goods_result)) {
$count++;
print ("<tr");
// фон каждой второй строки — серым
цветом
if
($count/2==intval($count/2))
print (" bgcolor=#e1e1e1");
print ("><td
align=right>$count.</td><td>${good_row[name]}
<br>${good_row[description]}</td><td
align=right>${good_row[price]}</td></tr>\n");
};
// конец таблицы и
нижняя навигационная строка
print("</table><div
align=center>".
<b>draw_bar($page, $amount,
$in_page, "goods.php?page=")</b>. "</div>\n");
Это ВСЁ, что нужно для постраничного вывода! Больше
напрягаться не надо!
Одно только пояснение — в качестве параметра функции
draw_bar указывается адрес этого скрипта со всеми параметрами
так, чтобы он туда только дописывал номер страницы. Если
сложная выборка, надо будет ручками формировать этот адрес
(всё-таки упрощение жизни вышло относительное: упрощаем
одно — усложняем другое).
Навигационная панель сделана в виде номеров страниц (" 1 |
2 | 3 "). Но привести к виду "0-10 | 11-20 | 21-30" не
проблема.
4. Функции mySQL обработки
данных
Должен признаться, много времени на изучение руководства по
MySQL я не уделял, а брал его только когда было совсем
необходимо что-то узнать. Как выяснилось, я многое
потерял — вещи, над которыми иногда задумывался,
оказывается, уже в базах данных MySQL реализованы.
Посмотрев в перечень математических функций, я несколько
переделал подсчёт данных в своей игре. Данные вынимались
из базы запросом, проводились вычисления, затем возвращались
обратно. Теперь количество запросов для этой операции сведено
к одному — отправляется сразу UPDATE-запрос, внутри
которого указываются все вычисления и сопутствующие данные.
Пока что расскажу про функции (не все, конечно), которые
нужно знать каждому, кто много работает с базой.
Функции условий
IFNULL(x,y) — если x не NULL, тогда выдаёт x,
иначе — y.
NULLIF(x,y) — если x и y равны, выдаёт NULL,
если не равны — x.
IF(x,y,z) — если x = true (вернее, если x не
равен 0 и не NULL), выдаёт y, если нет — z.
К примеру, в форуме хранится информация о пользователях и
есть возможность не показывать другим пользователям свой
Email. Делается поле show_email, в котором лежит 0, если
пользователь не хочет показывать адрес, и 1, если
разрешает.
SELECT ..., IF
(show_email,CONCAT('<a href=mailto:',email,'>написать
письмо</a>'),'адрес не указан') AS email, ...
Математические
функции
MOD(N,M) или "%" — остаток от деления N на M.
FLOOR и CEILING — округление до целого вниз и
вверх.
ROUND — округление до целого или до
определенной десятичной дроби.
LEAST (X,Y,...) и GREATEST(X,Y,...) —
минимальное и максимальное числа из указанных.
Не упоминаю стандартные математические функции взятия
модуля, знака, работы с углами.
Строковые функции
CONV(N,система_из,система_в) — конвертация
числа из одной системы исчисления в другую: select
CONV("ff",16,10); => 255. Кстати, конвертировать можно
не только в стандартных системах (2,8,10,16), но и в любых
других от 2 до 36 — насколько хватает букв латинского
алфавита.
CONCAT(X,Y,...) — объединение строк и чисел в
одну строку (пример приведён выше).
CONCAT_WS(разделитель,X,Y,...) — аналог функции implode.
LENGTH(строка) — strlen.
LOCATE(подстрока, строка) — strpos.
SUBSTRING(строка, отступ, длина) — substr.
TRIM() — удаление лишних символов из начала и
конца строки. В отличие от функции php trim позволяет не только пробелы, а любые символы и даже комбинации
символов.
REPLACE (строка, X, Y) — заменяет в строке X на
Y (не перепутайте порядок с порядком параметров в
str_replace).
Дата и время
Функций много, отмечу только некоторые самые важные:
неправда, что MySQL считает дни недели только с воскресенья,
как принято в Америке. Нужно использовать не функцию
DAYOFWEEK, а WEEKDAY, тогда понедельнику будет соответствовать
номер 0, вторнику — 1, воскресенью — 6.
Для сложного форматирования даты (например, для вывода даты
в виде 18.08.01), есть функции DATE_FORMAT (для даты и
времени) и TIME_FORMAT (только для времени). Работа с этими
функциями удобнее, чем использование своих собственных (потому
что это средство стандартное и универсальное, чего в
самопальном приспособлении добиться очень сложно), а так же
быстрее (используются встроенные функции mysql-сервера,
которые уже сидят в памяти, вместо компиляции при каждом
запуске скрипта собственного кода).
Юниксовский timestamp MySQL тоже поддерживает —
переводы в него и из него через функции UNIX_TIMESTAMP и
FROM_UNIXTIME:
UNIX_TIMESTAMP([дата-время]) — выдаёт дату в
юниксовом формате (если аргумент пропущен — текущую
дату).
FROM_UNIXTIME(дата [, формат]) — выдаёт дату в
обычном формате (во втором аргументе может быть указан формат
по правилам как в DATE_FORMAT).
Кроме того, основные параметры даты — число, день
недели и месяц (возможно и словом), год, квартал (!), неделя и
многое другое доступно не только через общую функцию
DATE_FORMAT, но и через отдельные специальные функции.
Остальные функции
LAST_INSERT_ID() — как и mysql_insert_id(),
выдаёт последний идентификатор, который сгенерировала база
данных по запросу с данного соединения.
MD5(строка) — поскольку говорят, что
зашифрованный функцией PASSWORD() пароль легко расшифровать, я
храню хэш md5 от пароля.
FORMAT(X, D) — форматировать число X в виде
"#,###,###.##", округлённое до D знаков после запятой.
Подумал, что неплохо бы в моей игре сделать
форматированные для удобного чтения числа, глянул в
руководство, вот оно. Всё уже написано.
Ещё две функции, про которые я забыл, когда писал про
оптимизацию работы логов.
INET_NTOA(число) — аналог long2ip().
INET_ATON(ip-адрес) — аналог ip2long().
А я только начал думать, как отделять статистику по
ip-адресам от всех других выборок в анализаторе
логов. Оказывается, всё проще, чем кажется.