Работа с MySQL: Подробнее
Автор:Дмитрий Лебедев
Источник:detail.phpclub.net
Полезные советы как избежать пустой траты системных ресурсов, как закрыть возможные дыры в защите. Самые популярные грабли.
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 ("ОБНОВЛЕНИЕ пользователей УСТАНОВИТЕ пароль = ПАРОЛЬ ('$ passwd') ГДЕ 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-адресам от всех других выборок в анализаторе логов. Оказывается, всё проще, чем кажется.
