Профессиональная разработка Web-приложений.  
Боишься нашего дизайна?
Новости
PDF журнал
Участники проектa
Сотрудничество
Ссылки
Карта сайта
Комментарии
Комментарии к статье
Добавить комментарий
Обсудить на форуме
Информация об авторе
Оценка статьи

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


For comment register here
   Unknown 2000-12-29 13:26
http://www.php.net/addslashes - read!

   Unknown 2001-01-21 14:34
> Данные типа DATE, TIME, DATETIME и TIMESTAMP можно форматировать с помощью функции
> date_format (см. руководство по mysql). Используйте его, и не форматируйте данные
> через php - это не просто "самодеятельность", а ещё и растрата системных ресурсов.

Можно подумать, что MySQL, форматируя дата не тратит те же системные ресурсы... хотя если сервер СУБД на другой машине, то тратятся конечно другие. Но все равно, те же яйца.

   Unknown 2001-01-21 14:37
"обработать его с помощью intval: $id = intval($id))" Будет короче: $id = (int) $id. А еще забыли о крайне полезной штуке - LIMIT

   Unknown 2001-01-21 15:27
Тратит, но на порядок меньше&nbsp;&#151; а это существенно. Эта функция полезна не только этим. Например, построить из логов распределение посещений по дням недели (группировка нужна)&nbsp;&#151; это только через date_format можно.

   Unknown 2001-02-08 13:43
$somefield = str_replace("'", "'", $somefield).
Хммм... А может быть правильно $somefield = str_replace("'", "", $somefield)?

   2001-03-19 16:45
> $somefield = str_replace("'", "'", $somefield). Хммм... А может быть правильно $somefield = str_replace("'", "", $somefield)?

А можно просто пресечь все(кроме a-z 0-9 - кто-нибудь серьезно пользуется логинами типа ##kolya или $%^vasya?) Из полученной строки исключаются все ключевые слова SQL.
Если это имя пользователя, и при регистрации он захотел себе странноватое имя, напоминающее SQL запрос, то вываливается сообщение типа "Это имя уже занято..." :)

Проще надо быть, а то живете почти что на физическом уровне OSI, про простейшие вещи забываете. И про методологию проектирования БД почти ничего нет. Так в основном, примитивные вещи. Скажете - а зачем это "им"? "Нам" это было бы полезно... Местами...
После нас эта "БЭДЭ" достанется другим. Очень не хочется, чтобы кто-нибудь плевался из-за того, что я когда-то считал себя Программистом.

   Unknown 2001-04-30 16:01
Зачем нужны функции, если эта навигационная строка используется
на странице один раз? Проще инклудить или реквестить pages.inc, рисующий нужную строку,
а текущую страницу и количество страниц получать в запросе в основной странице.
Это позволит и учитывать специфичность запросов - фильтрация, сортировка, и в начале страницы
нередко полезно указать страница 5 из 46.
Проверять же достоверность количества страниц вовсе не барское дело - всегда найдется клиент, которому интересно
будет ввести число по больше ручками.

   Unknown 2001-04-30 16:40
коментарий со ссылки выше
Php doesn't strip the slashes when info comes out of the database. The difference here is that php4 automatically adds slashes to incomming variables (from a post/get) and php3 didn't. So if you're adding slashes manually, suddenly you have double. Either drop your second addslashes() or add a stripslashes to the beginning to all the variables that come in.

Истинная правда, и про это нельзя забывать

   Unknown 2001-04-30 16:54
Навигационная строка может использоваться два раза - если списка на странице два (хотя это, конечно, редкий изврат :). Просто у меня include и require для вывода данных запрещены. Потому что шаблонами пользуюсь. Там делается

$page->assign(array("limit" => get_limit(...), "nav_bar" => draw_bar(...)));

>страница 5 из 46
Кому надо, пусть свою функцию напишет - это ж для программистов сайт! Что им, сложно? :)

   2001-05-04 13:30
Все это достаточно красиво и просто, но лучше бы было продолжение(расширение) статьи для случая, когда существуют (используются) несколько источников (возможно разнородных по структуре) для формирования результирующего списка.

   Unknown 2001-05-06 12:03
> if ($count/2==intval($count/2))

Замени на $count & 1. После такого остально читать не хочется.

   Unknown 2001-05-06 12:05
> $amount = @mysql_result(mysql_query("SELECT count(id) as goods_total FROM goods"),0);

Э... а это что? :((
list ($amount) = mysql_fetch_row (...); не лучше?

   2001-05-16 07:25
все эти варианты хороши только для случая, когда все разбито именно по записям. А как же быть с вариантами когда в поле сидит здоровый кусок текста и именно его нужно разбить по страницам, причем в зависимости от того сколько строк хочет увидеть пользователь на странице? Есть алгоритмы? Я вот с этим трапался довольно долго...

   2001-05-18 14:31
Отличный скрипт. Спасибо Дима! Только при работе замечен один баг ( или не баг?) - если общее количество строк меньше чем количество строк на странице, то выводятся все строки из таблицы :-

   2001-05-27 15:38
Можно ли в phpMyAdmn вставлить текст из текстфайла, например CSV?

   Unknown 2001-05-27 16:31
Не пробовал. Лучше посмотреть в readme по phpMyAdmin или на их сайте.

   2001-06-25 10:08
Rebjata. pomogite..sdaju na dnjach ecsamen..mne nugen malenkii schop na php, Na php ja probolela i ne snau kak eto teper nakorjabat..Mьnchen .tatjana tel:0173/6722727.Spasibo...

   Unknown 2001-06-26 11:53
А вот такой вопрос:
Что в данном случае продуктивней - использовать LIMIT и в результате 2 запроса к базе ( один count и один основной) или сразу выбирать все записи, а потом делать mysql_data_seek ?

   Unknown 2001-06-26 12:46
Лучше сделать один запрос с count, а второй - с LIMIT. Только count не пройдёт, если у тебя в запросе группировка.

   2001-07-05 12:22
Есть у меня куча новостей на сайте. Разбиваю их на страницы по N штук. Новости отсортированы по дате в обратном порядке (более молодые идут первыми). Через некоторое время, самая первая новость будет на странице M, а значит url будет вида page.php?page_number=M Когда-то эта новость была на M-K странице и именно в тот момент ее проидексировал поисковик. Потом, при поиске он выдает именно этот url, на котором уже давным давно нет этой новости. Так что такой метод разбиения на страницы мало кого устроит. Как-то надо делать url вида
page.php?page_where_first_news_id=L. Тогда запрос будет вида select (...) from news where news_id < $page_where_first_news limit N. Как получить url предыдущей и следующей страницы - понятно. А вот как вычислить url страницы I я пока не понял. Есть идеи как это сделать минимальным количеством запросов?

   2001-07-30 08:35
У нас есть сайт. Написан на php. Разбираемся с ним и учимся.

Проблема следующая. На сайте сейчас все перемешано. Вся информация
берется из базы. Хотелось бы всю информацию упорядочить по темам. Не совсем понятно как это сделать с помощью PHP. Как сформировать запрос к базе так, чтобы на страничку выводилась только нужная инфа? Хотелось бы базу не трогать, а переписать только сами запросы. Т.е. на страничке уже должны быть отсортированные данные.

И еще. На серваке хранится файл без расширения весом 280К. Так как
ничего похожего на базу больше нет, то есть предположение, что это и есть
база. Вобще обязательно ли база MySQL должна иметь расширение? И какое?

   Unknown 2001-07-30 09:28
База - это директория, в которой лежат файлы - по три на таблицу. Расширения у всех есть.

   2001-08-22 23:58
Вот как оказывается... жизнь на самом деле никак и не меняется: старого RTFM никто не отменял :о))

   Unknown 2001-08-23 00:51
Эт о была попытка сподвигнуть остальных на RTFM. :)

   2001-08-25 12:46
Большинство встроенных функций MySql имеет смысл использовать только в WHERE при объеденении таблиц.
В остальных случаях это лишняя нагрузка на СУБД.

   Unknown 2001-08-25 13:39
И уменьшение нагрузки на php за счёт упрощения синтаксических конструкций.

   2001-09-21 19:04
Благородные доны, подскажите, плз, обязателен ли при вызове mysql_query второй параметр под юниксом:
$res=mysql_query($qstring)
$res=mysql_query($qstring,$conn)
Под виндой у меня первая строка работает, а под юниксом меня ткнули носом во вторую, хотя она там тоже работает :), с заявлением "за такое руки отрывать надо"...

   Unknown 2001-09-28 11:43
2Lina: смотрим доку. Видим:
--------------------------------
resource mysql_query (string query [, resource link_identifier])
mysql_query() sends a query to the currently active database on the server that's associated with the specified link identifier. If link_identifier isn't specified, the last opened link is assumed. If no link is open, the function tries to establish a link as if mysql_connect() was called with no arguments, and use it.
--------------------------

какие могут быть вопросы? Просто, при использовании второй формы при просмотре текста может быть несколько неочевиднр, какой ресурс используется. Но, мне как хорошо знакомому с языком Perl, где часто используются умолчания, такая ситуация кажется вполне нормальной.

   2001-10-30 11:56
А вот деревце построить одной выборкой ?
типа :
id,pid,text
pid=Parent Id
слабо ?

   2001-10-31 18:09
Люди! Тут у нас в городе (город маленький), возникла такая ситуация: когда покупаешь катрочку на инет, пишешь в форме тыры-пыры, то идет ссылка на card.php3. Кому не лень, еси время бут, то поюзайте. Вот адрес: ppp.migsv.ru/card/card.php3,
ppp.migsv.ru/card

   2001-11-26 07:11
у меня проблема возникла такая.... Отсортировать постранично - уже в прошлом, хочется архив новостей сделать по годам и месяцам, дата новости у меня типа DATE в формате ГГГГ-ММ-ЧЧ.... Это можно реализовать? Или нужно каждую страницу делать отдельно?

   2002-02-11 03:43
Вот и мне довелось пару десятков разделов делать с постраничным выводом. Спасибо Дима, столько времени сэкономил. Отличные функции.

   2002-06-01 21:00
Все это хорошо когда записей в базе мало. А вот если у меня 100000.. записей? Прикинте какая строчка получится с номерами страниц? Ну на полэкрана минимум. Только не надо предлагать что типа надо выводить по-больше строк на страницу. Есть интересные решения (одно я выдрал из форума phpbb), но хочется чего-нибудь лучшего, красивого. Кто возмется написать универсальный скриптик чтоб страницы выдавал?

   Unknown 2002-06-01 21:53
В этой строке выводятся только 5 соседних страниц. Если надо больше, можно сделать.

   2003-05-26 18:48
> if ($count/2==intval($count/2))
ИМХО не лучший вариант. Не красиво!!!
Я делаю так:
$bgcolor=array("#eeeeee", "#fafafa");
for ($i=0, $y=count($post); $i<$y; $i++) {
print "<tr bgcolor=".$bgcolor[($i % 2)]."><td></td></tr>";
}//for
Эту идею и здесь прикрутить нет проблемммм...
А ваще, и дальше всT в таком же духе... слишком много кода... поэтому особо не вникал... функции какие-то... зафиг они нужны?.. но может одна для навигации и всT... Мой собственный код работает у меня без проблем при том, что он раз 5-ть короче... и в нTм отсутствуют баги про которые говорили выше...


   2003-11-27 11:52
2Nexus: самое простое, что можно сделать с деревом - построить его при помощи одного запроса к БД.

   2004-01-28 05:12
Я конечно все понял, кроме этого текста:
"Одно только пояснение — в качестве параметра функции draw_bar указывается адрес этого скрипта со всеми параметрами так, чтобы он туда только дописывал номер страницы"
Что это за фунгция draw_bar()? И где ее достать...?

   2004-07-20 15:56
ошибочка: функция php trim(), обрезает не только пробелы, но и перевод строки, табуляцию, перевод каретки и кажется еще что-то

   2004-11-11 18:16
Evgeniy: это называется "пробельные символы".

Mysql и php, работа с базой, основы mysql. Выборка из базы. Mysql, select, insert, update, delete, table, SQL, выборка, база, таблица

 
 
 
    © 1997-2008 PHPClubTeam
[]