- Подробности
-
Категория: PHP. Базы данных
Работа с MySQL: Подробнее
Дмитрий Лебедев
2000-12-15
Полезные советы как избежать пустой траты системных ресурсов, как закрыть возможные дыры в защите. Самые популярные грабли.
1. Запросы на выборку данных (SELECT)
2. Запросы на вставку строки (INSERT)
3. Постраничный вывод
4. Функции mySQL обработки данных
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-адресам от всех других выборок в анализаторе логов. Оказывается, всё проще, чем кажется.
