Работа с MySQL: Подробнее

Работа с 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-адресам от всех других выборок в анализаторе логов. Оказывается, всё проще, чем кажется.