Литература Статьи по MySQL

[ddd]

Статья: Работа с MySQL. На дорожку
Источник: dev.vitgroup.com

Во избежание путаницы полей (если встречаются поля с одинаковыми названиями) используйте в запросах оператор AS: "SELECT table1.id as id1, table2.id as id2". Это поможет избежать ошибок в запросе (например, если не указана таблица, а поле с таким названием есть в нескольких запрашиваемых таблицах, mysql выдаёт ошибку), а так же вы избежите недоразумений при работе с полученными данными (echo $row["id1"] писать гораздо проще, чем $row[$x]).

• Во избежание путаницы полей (если встречаются поля с одинаковыми названиями) используйте в запросах оператор 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)).

Запросы на вставку строки (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-е, белое не носить), вероятность попадания служебных символов в запрос ненулевая.

Статья: Репликация MySQL на одном компьютере
Источник: davojan.ru

Предупреждение. Если Вы не знакомы с репликацией в mysql, то вряд ли поймёте что-нибудь из ниженаписанного, Вам следует сперва ознакомиться с документацией
по репликации и по
запуску нескольких серверов mysql на одном компьютере.
​

Прелюдия. Понадобилось мне организовать двухстороннюю репликацию БД на двух mysql-серверах, запущенных на одной машине (для обхода блокировки myisam-таблиц). Казалось бы ничего сложного и особенного тут нет, учитывая то, что я знаю как запускать несколько mysql-серверов на одной машине и как организовать репликацию БД между двумя разными машинами. Но задача оказалась не самой простой. Не буду рассказывать, как в течение целого дня бился с конфигами, сколько вариантов запуска перепробовал и т.д., а просто напишу про подводные камни:

Подводный камень №1. У mysql есть параметры master-host, master-port, но нет параметра master-socket, поэтому когда я для первого сервера написал
master-host = localhost
master-port = 3307,
он цеплялся не к 127.0.0.1:3307, а к /tmp/mysql.sock, т.е. к самому себе, а не ко второму серверу. Причём в логах информация о том, куда он на самом деле цепляется случайно появилась только когда я там сильно нахимичил.
Обход этой проблемы — написать master-host = 127.0.0.1.

Подводный камень №2. Даже просле того, как я в my.cnf изменил master-host на 127.0.0.1, система ни хрена не заработала. Тут причина в том, что оказывается параметр master-host и некоторые другие параметры репликации считываются из основного конфига только в том случае, если что-то не в порядке с файлом master.info (он создаётся автоматом в каталоге данных), например, он просто отсутствует, а если он существует, то master-host считывается оттуда, и серверу положить, что я там поменял в my.cnf. В документации об этом сказано, но я это нашёл уже после того, как сам выяснил путём эксперимента… надо было выделить это красным жирным шрифтом, блин.

Обход этой проблемы — отредактировать вручную, либо, что проще, удалить master.info, не забыв перед этим остановить сервер mysql. При удалении потеряется оперативная инфа по репликации — текущий файл журнала — так что надо быть осторожным, безопаснее всё же отредактировать.

Что в итоге получилось. Вот кусок my.cnf, относящийся к делу, с которым всё работает замечательно (версия mysql 4.0.18):

################
# MySQL server 1
[mysqld1]
port = 3306
socket = /tmp/mysql.sock
datadir = /data/mysql1
pid-file = /data/mysql1/mysqld.pid
user = mysql

# replication
log-bin
server-id = 1
binlog-do-db = test

master-host = 127.0.0.1
master-port = 3307
master-user = replicator
master-connect-retry = 10
replicate-do-db = test
################

################
# MySQL server 2
[mysqld2]
port = 3307
socket = /tmp/mysql2.sock
datadir = /data/mysql2
pid-file = /data/mysql2/mysqld.pid
user = mysql

# replication
log-bin
server-id = 2
binlog-do-db = test

master-host = localhost
master-port = 3306
master-user = replicator
master-connect-retry = 10
replicate-do-db = test
################

На первом сервере следует сделать:
GRANT REPLICATION SLAVE ON *.* TO replicator@localhost;

на втором:
GRANT REPLICATION SLAVE ON *.* TO replicator@127.0.0.1;

О производительности (вместо заключения). Как видите, для второго сервера я оставил master-host = localhost, это вполне правомерно, т.к. он будет соединяться с /tmp/mysql.sock, т.е. к первому серверу, а нам туда и надо. Вроде бы как обмен через unix domain около двух раз быстрее, чем через сетевые сокеты. Вряд ли это может сильно повлиять на производительность репликации, тем более, что в большинстве случаев она реализуется между двумя разными машинами через сеть. Но несмотря на это смущает то, что разработчики mysql не предположили случая репликации на одном компьютере и не реализовали параметр master-socket. Написал им feature-request по этому поводу, посмотрим что скажут…

 

[ddd]

Статья: Конвертация баз MySQL в dBase
Автор: Алимов Рустем
Источник: arkasoft.narod.ru

Во многих предприятиях, разместивших свои ресурсы в интернете, осталось большое количество программных комплексов и различных скриптов, работающих с данными в формате dBase. Появилась задача обратной совместимости.

Данный класс поможет Вам преобразовывать базы данных MySQL в dBase.

Для работы этого скрипта необходимо, чтобы PHP был скомпилирован с поддержкой dBase. Чтобы узнать это, достаточно вызвать функцию phpinfo(). Если в строке Configure Command сведений о версии PHP есть параметр --enable-dbase, значит Ваша версия поддерживает dBase (можно также посмотреть в разделе Additional Modules наличие строки dBase).

class ConvdBase
{
  var                          $conv_dir;  // каталог конвертации - здесь будут                          DBF-файлы

  var $struct;    //                          структура текущей таблицы,
  // используется при                          конвертации данных

  function                          ConvdBase($dir)
  {
                              $this->setoutdir($dir);
                           }

  // имя поля и его тип формата MySQL                          преобразуем к dBase
  // например: code int(10),                          преобразуем к array('code', 'N', 10, 0)
  //                          если $mysql == true, то возвращаем только тип                          int
  // который записывается в                          $this->struct
  function getfield($f_name,                          $f_type, $mysql = false)
  {
                              $len = 0;
     $perc =                          0;

     $pos = strpos($f_type,                          '(');
     if($pos)
                              {
        $type =                          strtolower(substr($f_type, 0, $pos));
                                 sscanf(substr($f_type, $pos), '(%d,%d)',                          &$len, &$perc);
                              }
     else
                              $type = strtolower($f_type);

                              if($mysql)
     return                          $type;

                              switch($type)
     {
                                 case 'double': case                          'float':
        $len  = 19;                          $perc = 3;
        $type =                          'N';
        break;
                                 case 'enum':
                                 $len = 5;
                                 case 'int': case 'tinyint': case 'smallint':
                                 if(!$len)
                                 $len = 19;
        $perc =                          0;
        // break не                          нужен
        case                          'decimal':
        $type =                          'N';
        break;
                                 case 'tinytext': case 'text': case                          'longtext':
        case                          'varchar': case 'char':
                                 if(!$len || $len > 254)
                                 $len = 254;
        $type                          = 'C';
        break;
                                 case 'year': case 'datetime': case                          'timestamp':
        $len = $perc                          = 0;
        $type =                          'D';
        break;
                              }
     $field =                          array($f_name, $type, $len, $perc);
                              return $field;
  }

  //                          установка исходящего каталога
  function                          setoutdir($dir)
  {
                              $this->conv_dir = $dir;
                              @mkdir($this->conv_dir, 0777);
                           }

  // конвертация данных
  function                          convertdata($row)
  {
                              $data = array();
     for($i =                          0; $i < sizeof($this->struct); $i++)
                              {
                                 switch($this->struct[$i])
                                 {
                                    case 'year': case 'datetime': case                          'timestamp':
                                    $data[] = substr($row[$i], 0, 8);
                                    break;
                                    case 'tinytext': case 'text':                          case 'longtext':
                                    case 'varchar':  case 'char':
                                    if(strlen($row[$i])                          > 254)
                                    {
                                       $data[] = substr($row[$i], 0, 254);
                                                                break;
                                    }
           //                          break не нужен
                                    case 'smallint': case 'double': case 'float': case                          'enum':
           case                          'int': case 'tinyint': case 'decimal': case                          'int':
                                    $data[] = $row[$i];
                                    break;
                                 }
     }
     return                          $data;
  }

  // конвертируем                          базу
  function convert($server, $user,                          $password, $db_name)
  {
                              // отключить тайм-аут
     if(                          !get_cfg_var('safe_mode') )
                              set_time_limit(0);

                              $link = mysql_connect($server, $user,                          $password);
     if(!$link)
                              exit('Error connect');

                              mysql_select_db($db_name);
                              $tables_query = mysql_query('SHOW TABLES',                          $link);

     $tek_table = 0; //                          порядковый номер таблицы - если обязательно
                              // требуется имя файла не более 8                          символов
     while($tables =                          mysql_fetch_array($tables_query, MYSQL_NUM) )
                              {
        $table =                          $tables[0];

                                 $field_array = array();
                                 $this->struct = array();

                                 // создаем структуру таблицы
                                 $fields_query = mysql_query('SHOW FIELDS                          FROM ' . $table);
                                 $tek_field = 0;
        while                          ($field = mysql_fetch_array($fields_query, MYSQL_ASSOC)                          )
        {
                                    $f_name =                          $field['Field'];

                                    // имена полей dBase не должны быть более                          10 символов
                                    if(strlen($f_name) > 10)
                                    $f_name = sprintf('%s%03d',                          substr($f_name, 0, 7), sizeof($field_array) +                          1);

                                    $field_array[] = $this->getfield($f_name,                          $field['Type']);
                                    $this->struct[] =                          $this->getfield($tek_field, $field['Type'],                          true);
        }

                                 if(strlen($table) > 8) //                          см.выше о именах таблиц более 8 символов
                                 $table_name = sprintf('%s%03d',                          substr($table, 0, 5), $tek_table++);
                                 else
                                 $table_name = $table;

                                 $base = dbase_create( $this->conv_dir . '/' .                          $table_name . '.DBF', $field_array);
                                 if($base)
                                 {
           // таблица                          создана - перекидываем данные
                                    $rows_query = mysql_query("SELECT *                          FROM " . $table);
                                    while($row = mysql_fetch_array($rows_query,                          MYSQL_NUM))
                                    {
                                       $row = $this->convertdata($row);
                                                                if($row)
                                       if( !dbase_add_record($base, $row) )
                                       exit('Error                          add record');
                                    }
                                    dbase_close($base);
                                 }
     }
                              mysql_close($link);
   }
}

А теперь рассмотрим пример использования конвертатора.

$dir = './CONVERT_' . date('YmdHi'); // здесь будут                          DBF
$conv = new                          ConvdBase($dir);
$conv->convert('localhost',                          'user', 'pass', 'base');

Если типы данных year, datetime, timestamp надо передавать в неизменном виде, приведите их к типу dBase "С", изменив функции getfield и convertdata.

Статья: Управление доступом к базам MySQL
Автор: Александр Лозовюк
Источник: hostinfo.ru

Система управления базами данных MySQL очень часто применяется для хранения важной информации на веб-сайтах. Если это обычный сайт или форум – в базе могут храниться сообщения пользователей, данные для динамических страниц, данные о посещениях, если это какой-либо интерактивный сервис, то кроме данных про доступ (конфиденциальных), там хранится и другая информация о пользователе и его действиях. Все это приводит к тому, что общая безопасность сайта, вернее всей веб-системы, зависит от того, насколько защищен именно сервер базы данных.

На обычных виртуальных хостингах каждый клиент получает свой логин и пароль, и ему доступна только одна база, в которой он может создавать произвольное количество таблиц. Один и тот же физический сервер БД используют разные клиенты, каждый из которых имеет доступ только до одной определенной базы данных. Ситуация, когда у пользователя одна база, за владение которой сражаются и "движок" форума (которому требуется создать сотню и больше таблиц), и скрипты списков рассылки, новостей, поисковый скрипт, а если еще установлена система управления контентом (CMS) или электронный магазин – тогда в этой базе возникает такое огромное количество различных таблиц, порой с очень странными и ничего не обозначающими названиями (хорошо, если два скрипта не используют таблицы с одинаковыми названиями, но разной структурой). В таких случаях очень желательно иметь возможность создать несколько отдельных баз данных, и выделить их для разных приложений (к примеру, одна база для форума, другая для электронного магазина).

Кроме сложностей с управлением несколькими сотнями таблиц в одной базе, вы столкнетесь с необходимостью ограничивать доступ разных пользователей к таблицам, базам и даже отдельным столбцам конкретной таблицы. Спешим вас успокоить – разработчики СУБД MySQL уже позаботились о подобной ситуации – в MySQL есть очень гибкий и мощный механизм управления и разграничения доступа пользователей к базам и таблицам.

Работает этот механизм, естественно, через служебные таблицы. В списке баз данных есть одна служебная база под названием "mysql", в которой хранятся в нескольких таблицах все служебные данные, необходимые для работы сервера. Пока нас интересует только управление правами доступа, за которые отвечают следующие таблицы:

• columns_priv – доступ на уровне столбцов таблицы;
• db – доступ к отдельным базам данных;
• host – доступ к базам с конкретных хостов/IP;
• tables_priv – доступ к отдельным таблицам базы данных;
• user – глобальные настройки доступа для конкретных пользователей.

Давайте детально рассмотрим таблицу user и специфику работы с ней, работа же с остальными таблицами аналогична и не будет сложнее.

Права, определяемые в таблице user, являются глобальными, это значит, что они применяются ко всем базам данных, к которым имеет доступ конкретных пользователь. Также в этой таблице хранятся сами имена пользователей и их пароли. Причем имена пользователей хранятся в виде открытого текста, а пароли зашифрованы функцией PASSWORD(). Сервер MySQL применяет свой метод шифрования вместо системного (если такая функция предоставляется ОС, к примеру, Linux/UNIX).

Поскольку MySQL предназначена для работы через сеть, то к серверу могут подключаться клиенты с различных хостов. Поэтому в таблице привилегий есть столбец host, в котором можно задать конкретный компьютер, IP или диапазон адресов, с которых определенный пользователь (или все, любые пользователи) могут подключаться, или же им полностью запрещен доступ. К примеру, если ваш тестовый сервер находится в локальной сети, то можно выборочно разрешить доступ к нему с отдельных компьютеров, а для всех остальных полностью заблокировать доступ.

Если для одного и того же пользователя надо разрешить доступ с различных компьютеров, то в таблице привилегий надо создать две (три или больше – сколько понадобится) записей, определяя в каждой отдельный параметр доступа. К примеру, для доступа пользователя root с компьютеров localhost и 192.168.1.138 надо в таблицу user внести две одинаковые записи, которые различаются только полем host. Хотя можно, конечно, и не делать их одинаковыми – тогда тот же пользователь, но в зависимости от места, откуда он подключился, будет иметь разные привилегии.

Значение столбца host можно задавать в различных форматах. Это может быть как имя хоста – localhost, так и IP-адрес – 192.168.1.138. При необходимости, можно применять символы подстановки. Они аналогичны тем же. Что применяются в синтаксисе SQL-запросов. Символ "_" означает любой символ, а "%" - любое количество символов. К примеру, для разрешения доступа с любого компьютера, содержащего в имени hostinfo.ru надо прописать %.hostinfo.ru в столбце host. Таким же способом можно задавать и диапазоны IP-адресов. Для задания маски сети доступен также вариант написания через слеш – 192.168.1/255. Для глобального задания доступа можно в столбце host прописать только символ "%" или оставить его пустым, что равнозначно.

Следующие поля – user и password, определяют имя и пароль пользователя. Эти поля уже, в отличие от host, различают регистр введенных символов, поэтому пользователь Root и root – это два разных пользователя. Знаки подстановки в этом поле уже не работают – имя пользователя %" означает не "любой пользователь", а именно "пользователь с именем %". Для задания анонимного пользователя надо просто оставить поле пустым, иначе любой символ будет считаться именем пользователя. Так же и в поле password – пароль хранится в зашифрованном виде, знаки подстановки недействительны, а пустое поле означает, что пользователь вовсе не должен указывать пароль, а не то, что пароль может быть произвольным. Напомним, что просто пароль (пусть и зашифрованный) нельзя помещать в поле password, предварительно его надо шифровать функцией PASSWORD("пароль_открытым_текстом"). Например:

INSERT INTO user SET host="192.168.1.138", user="raiden", password=PASSWORD("my_password");

Следующим полем является поле базы данных – db. В других таблицах оно также присутствует, поэтому во всех таблицах правила написания имен баз данных должны быть одинаковыми. Разрешается использовать символы подстановки "%" и "_", пустое значение соответствует "все базы". Поле чувствительно к регистру!

Далее идет целый набор столбцов, разрешающие те или иные действия для пользователя. К примеру, Select_priv, Insert_priv, Update_priv, Delete_priv, Index_priv и Alter_priv стоит установить в "Y" (разрешено) для обычного пользователя, так как такие запросы чаще всего встречаются в обычных приложениях. Остальные возможности для большей безопасности надо отключить для всех пользователей (особенно, если есть учетная запись анонимного пользователя). Поэтому в следующих столбцах - Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv и References_priv надо выставить значение "N"(запрещено), а при необходимости разрешить действия только администратору и максимально ограничить возможности подключения такого пользователя – указать конкретный IP-адрес или имя компьютера, выбрать длинный и сложный для подбора пароль.

Аналогична таблице user и таблица db, в которой можно задавать отдельные привилегии для конкретных баз данных. Таким образом, можно разрешить пользователям создавать новые таблицы только в своих базах данных и запретить даже простой просмотр чужих баз. Синтаксис всех полей полностью аналогичный таблице user.

При подключении пользователя сервер MySQL сначала обрабатывает таблицу user (записанные там правила доступа глобальные), а потом, если прав недостаточно или нельзя определить права доступа к конкретной базе – тогда уже поиск продолжается в таблице db. Если же и во второй таблице нет соответствующих привилегий, сервер пробует найти их в таблицах tables_priv и columns_priv. И только когда на основании информации со всех доступных таблиц сервер определяет, что клиенту не разрешено запрошенное действие, он отвергает клиентский запрос.

Таблица host также аналогична описанным выше, только вместо имен и паролей пользователей для идентификации привилегий служит имя хоста или IP-адрес. На первый взгляд, есть существенное дублирование информации. Ведь таблицы db и host почти равнозначны и содержат одинаковые данные. На самом деле это немного не так. Таблица host недоступна для изменения через операторы SQL GRANT и REVOKE, ее можно только править непосредственно. Когда сервер принимает решение о допуске клиента к базам, он сначала ищет упоминание имени хоста или IP в таблице host. Если там присутствует соответствующая запись, то тогда права из таблиц host и db обьединяються и клиент получает некоторые привилегии. Если же в таблице host нет указаний на счет хоста или IP, то сервер даже не рассматривает дальше таблицу db, так как если клиенту не разрешено подключение с хоста, то тогда бессмысленно искать права на доступ к отдельным базам.

И, напоследок, давайте остановимся на одном нюансе при определении привилегий. Сервер MySQL, когда ищет и сравнивает привилегии, не сравнивает их напрямую. К примеру, символьное обозначение параметра имеет больший приоритет, чем использование символов подстановки. Пример: если есть две записи - %.hostinfo.ru и admin.hostinfo.ru, то вторая запись имеет больший приоритет. Поэтому если для одного пользователя существуют разные привилегии в зависимости от хоста или других параметров, то будет выбираться первой та запись, где более точно и однозначно указан параметр. Это же касается и IP-адресов.

 

[ddd]

Статья: Лабораторная работа: MySQL
Автор: Олег П. Филон

первая чать

Заняться выполнением этой лабораторной работы меня побудили несколько причин. Во-первых, занимаясь построением серверов и сетей на основе Линукс'а, я догадывался, что где-то совсем рядом лежит сказочно богатый континент, пока не нанесенный на мою карту компьютерного мира. Во-вторых, авторы одной из самых популярных открытых программ - СУБД MySQL, недавно приняли GNU GPL (General Public License) как лицензию, по которой распространяется эта программа, и теперь MySQL является полноценным проектом GNU.

Эти юридические тонкости имеют самое непосредственное отношение к нам, пользователям, чему я немного ниже приведу пример. И наконец, в-третьих, пытаясь отыскать хорошие руководства по SQL в сети, я в конце концов обнаружил, что самые лучшие он-лайновые учебники по этой теме, оказывается, написаны нашими соотечественниками, на русском языке, и лежат у меня на диске - в зеркале сервера CITFORUM

Особенно полезны учебный курс "Введение в системы управления базами данных" Пушникова А.Ю., и курс лекций "Основы современных баз данных" Сергея Кузнецова. Недавно к ним добавилось подробное описание СУБД MySQL , сделанное Паутовым Алексеем Валентиновичем на основе оригинальной документации и такое же доскональное.

Итак, пришла пора взяться за учебники, а для меня еще и достать припасенный для такого случая особый файл. Этот файл представляет собой телефонный справочник службы 09 нашего города, пару лет назад попавший в местную ФИДО-сеть. Мне не очень важна его актуальность, зато очень подходит его размер - свыше 120 тысяч записей. Очень часто примеры, даваемые в учебниках, являются слишком игрушечными, чтобы вызывать интерес. Затем, на крошечной БД невозможно почувствовать скорость и мощь современных программ и компьютеров, или наоборот, плохо настроенную БД или неправильно составленный запрос. Кроме этого, ситуация с построеним БД вокруг уже имеющихся данных вполне жизненна.

Вполне возможно, что у вас файла с такими данными в пределах досягаемости нет. Ничего страшного - его можно сделать самому, использовав подручные средства - например, взять простой текстовый файл, обычные textutils, и интерпретатор языка awk. Пример, как это можно сделать, приведен здесь . Конечно, данные в таком файле будут случайными, только внешне похожими на настоящий телефонный справочник.
Для начала надо установить на вашем компьютере MySQL. Не буду пересказывать главы из документации, имеющиеся в описании Алексея Паутова. Скажу лишь, что для установленного у меня дистрибутива Дебьян установка программы свелась к выполнению команды: ...$ dpkg -i mysql-server_номер_версии.deb mysql-client_номер_версии.deb

В дистрибутиве Mandrake, который я также иногда использую, используется программа-установщик rpm с соответствующими ключиками, или же какая-то из графических надстроек над rpm. Вполне возможно, что в вашей Линукс системе эта СУБД установилась сама собой по умолчанию.
Инсталяция MySQL под Windows, равно как и Apache, PHP и Perl, рассказана Дмитрием Котеровым на том же ЦитФоруме.
Предупреждаю, однако, что все, что написано ниже, проверено только под Линуксом.
Если вам повезло, и команда ...$ mysql из вашего шелла выдала приглашение наподобие:

Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL
connection id is 28 to server version: 3.22.32-log
Type 'help' for help.

mysql>, то в ответ на него наберите q, оставим на время интерпретатор SQL запросов, и займемся администрированием сервера MySQL.
Прежде всего, надеюсь, вы установили пароль администратора сервера БД, и пока его не забыли. Теперь нужно завести пользователей и дать им некоторые права. Все администрирование ведется через обычные таблицы MySQL, и их правка также осуществляется стандартными SQL командами. Самая первая таблица, которая определяет допуск юзера к серверу, так и называется - user. Давайте глянем, кто у нас там есть и что он может делать: ...$ mysqldump -u root -p --opt mysql user>mysql-users.sql
После выполнения этой команды у нас появился файл mysql-users.sql Загрузим его в текстовый редактор, чтобы поподробнее изучить, и, возможно, немного поправить.

# MySQL dump 7.1
#
# Host: localhost Database: mysql
#--------------------------------------------------------
# Server version 3.22.32-log
#
# Table structure for table 'user'
#
DROP TABLE IF EXISTS user;
CREATE TABLE user (
Host char(60) DEFAULT '' NOT NULL,
User char(16) DEFAULT '' NOT NULL,
Password char(16) DEFAULT '' NOT NULL,
Select_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Insert_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Update_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Delete_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Create_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Drop_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Reload_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Shutdown_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Process_priv enum('N','Y') DEFAULT 'N' NOT NULL,
File_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Grant_priv enum('N','Y') DEFAULT 'N' NOT NULL,
References_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Index_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Alter_priv enum('N','Y') DEFAULT 'N' NOT NULL,
PRIMARY KEY (Host,User)
);
#
# Dumping data for table 'user'
#
LOCK TABLES user WRITE;
INSERT INTO user VALUES
('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'),
('localhost','ophil','','N','N','N','N','Y','N','Y','N','N','Y','N','N','N','N'),
('localhost','proba','','N','N','N','N','N','N','N','N','N','N','N','N','N','N');
UNLOCK TABLES;

Вот уже и показались первые SQL-предложения, хотя мы пока не начинали программировать или что-либо запрашивать. В предложении CREATE TABLE перечислены все 14 различных привилегий, которые могут иметь или быть лишены пользователи. Первые 6 - Select, Insert, Update, Delete, Create и Drop, касаются права пользователя работать с записями таблиц и с самими таблицами. Следующие 4 - Reload, Shutdown, Process и File - касаются сервера в целом. Привилегии Grant, References, Index и Alter дают право передавать права, а также изменять, связывать и индексировать таблицы.
Два важных замечания.

Во-первых, права, данные в этой таблице, по умолчанию распространяются на все БД, имеющиеся на сервере. Поэтому не давайте в этой таблице никаких привилегий, касающихся таблиц. Более тонко, с точностью до отдельных полей и адресов хостов, права пользователей настраиваются в других таблицах, а эта таблица должна только разрешать вход на сервер.

Во-вторых, привилегии, касающиеся сервера в целом, настраиваются только в этой таблице, и хотя бы один пользователь, в данном случае root, должен иметь эти привилегии, иначе сервер станет неуправляемым.
Если в ваши планы входит дать доступ к серверу всем пользователям и под любым именем, заведите пользователя с пустым именем ''. Те же правила применяются к именам и адресам компьютеров-хостов.

Теперь, размножив и поправив записи в таблице, но ни в коем случае не ее структуру, отправим команды обратно в MySQL.

...$ mysql -u root -p > mysql-users.sql

и попросим сервер перечитать измененные права

...$ mysqladmin -u root -p reload

Еще одно замечание насчет паролей. В рассмотренном нами файле поля паролей пусты, и это надо немедленно исправить. Править их в текстовом файле неудобно, потому что MySQL используем для шифрования пароля отдельную программу, и хранит пароль в зашифрованом виде. Чтобы установить пароль, например, пользователю "proba", надо выполнить такую команду:

...$ mysql mysql -e 'update user set password=password("0") where user="proba";

Поздравляю, мы только что составили и выполнили первый SQL запрос в нашей лабораторной работе, хотя и сделали это из командной строки, со всеми ее удобствами и неудобствами. К неудобствам можно отнести то, что наш пароль высветился на экране, мог попасть в список процессов, в разные журнальные файлы. Будет лучше не полениться, запустить монитор mysql и задавать пароли в нем с помощью того же запроса внутри СУБД MySQL.

Отличие от обычной системы паролей в том, что имена пользователей БД могут быть не связаны с их регистрационными именами в системе, пользователи не могут менять свои пароли, и этот пароль известен администратору БД.

Разобравшись с самой первой административной таблицей user, остальные таблицы: db, host, tables_priv, columns_priv, func - правим аналогично. Каждую из команд, посылаемую MySQL, можно задавать либо в мониторе запросов mysql, либо из командной строки, либо создав файл и отправив его в интерпретатор MySQL через тот же монитор. Можно также обратиться к MySQL через интерфейсы с другими языками программирования из программ, написанных на C, Perl, PHP, Python и других.

Вывод на экран может немного отличаться в каждом случае, а также в зависимости от того, на экран или в файл (канал) направлен вывод. При работе в интерпретаторе всегда сообщается время, потраченное на выполнение запроса, а при выводе в файл (канал) не рисуется рамочка вокруг таблицы. Это делается только для нашего удобства, и не влияет ни на результат, ни на сам SQL запрос. Итак, обговорив разные способы ввода команд и вывода результата, займемся собственно SQL предложениями и преобразованием исходных данных. Доставшийся мне по случаю файл 09phone.txt представляет собой текстовый файл с полями в фиксированых колонках, как здесь:

107003 банки "приорбанк" первомайская ул. 1 бнк
107007 центры информацио жукова ул. 4а цен
107026 предприятия транс артиллерийская ул. 8а пре

и каждая запись содержит 5 полей:

1. номер телефона
2. фамилия или название организации
3. улица
4. номер дома
5. номер квартиры или примечание

Если бы исходные данные пришли из другой SQL БД и были в виде, как уже изученная нами таблица user, все, что нам пришлось бы сделать, это отправить в интерпретатор этот файл. Если бы текстовый файл был в более удобоваримом виде, например, с полями, разделенными знаками табуляции, то загрузить его в таблицу также можно было бы за один шаг. Но в нашем случае придется создать временную таблицу:

create table tmp
(
line varchar(80)
);

и импортировать в нее данные с помощью

load data infile '/tmp/09phone.txt' into table tmp;

Таким образом мы записали в таблицу tmp каждую запись как строку без разделения на поля. Импорт занял на моем компьютере около 4.4 сек. Здесь и далее и привожу время только для сравнения, для своего компьютера и настроек программы, сделанных по умолчанию.

 

[ddd]

вторая часть

Следующий шаг - извлечь данные из полей на фиксированных позициях и поместить их в промежуточную таблицу old с теми же полями, что и в исходной БД.
Сначала создадим таблицу:

create table old
(
phonum int unsigned not null,
title varchar(64) not null,
street varchar(40) not null,
bldng varchar(8) not null,
other varchar(8) not null
);

а затем заполняем ее данными, пройдя по всем строкам таблицы tmp:

insert into old
( phonum, title, street, bldng, other )
select
trim(mid(line,1,6)),
trim(mid(line,8,18)),
trim(mid(line,34,19)),
trim(mid(line,58,4)),
trim(mid(line,63,3))
from tmp;

Функция mid(...) извлекает из первого аргумента подстроку в соответствующих позициях, а функция trim(...) удаляет пробелы в начале и конце строки. Теперь можно спокойно сделать

drop table tmp;

Опять же, только для сравнения, 123 тысячи записей обработаны за 8.6 сек.Для того, чтобы узнать время запроса из программы, содержащей все команды и выполняемой неинтерактивно, пришлось применить такой способ:

create table times
(
start int unsigned
);
insert into times values ( unix_timestamp() );

Чистый SQL и MySQL не поддерживают никаких иных переменных, кроме таблиц и полей, так что для хранения времени пришлось завести отдельную таблицу. Нет также ничего похожего на print или echo, так что сообщать результат получилось только злоупотребив оператором select:

select "импорт данных выполнен: ", unix_timestamp()-start, " сек." from times;

Получившаяся таблица old еще нуждается в нормализации, но уже первые тривиальные запросы выявили одну проблему. Сортировка по алфавиту использовала по умолчанию чуждую русскому языку кодировку ISO-8859-1. Хотя в последних версиях, возможно, уже можно менять порядок сортировки на ходу, в той версии, которая входит в Debian v2.2, для правильной работы с русским языком необходима перекомпиляция (сборка) программы с параметром:

...$ ./configure --with-charset=koi8_ru

В Debian'е для сборки пакетов есть масса скриптов, которые и делают всю работу. Таким образом, поправив файл debian/rules и произведя магическое заклинание

...$ debuild -b -uc 2>&1|tee build.log

вскоре я получил готовый к инсталяции пакет с правильным понятием по-русски.
Но тут же возникла следующая проблема. FSF (Free Software Foundation) и Debian очень щепетильно относятся к любым ограничениям на свободу программ, и те ограничения на коммерческое использование MySQL, которые были в их старой лицензии, привели к тому, что MySQL оказался в секции non-free. Желание иметь клиентскую часть свободной вынудило разработчиков вырезать из оригинальных исходников чисто GPL-ные куски и образовать отдельное дерево исходников. По-английски это называется "fork", а в русском языке вполне подходит слово "раскол". Хоть это слово и с маленькой буквы, явление весьма неприятное, распыляющее силы разработчиков и создающее неудобства пользователям. В моем случае пришлось пересобирать также и GPL-ные исходники, а затем бороться с конфликтом зависимостей пакетов.

Но вот борьба позади, и мы приступаем к разбиению единой таблицы на несколько связанных и нормализованных, что, собственно, и дает право называться СУБД реляционной. Из таблицы old с теми же полями, что и в исходном файле, мы сделаем 3 таблицы, связанные, как это обычно рисуется на схемах, таким образом:

phone
------
phonum building
naim -------- street
bd_id >------------ bd_id ------
other st_id >----------- st_id
bldng nick

Значок >-- обозначает сторону "много" в отношении "один ко многим" и означает, что в одном здании может быть много телефонных номеров, а на одной улице много зданий.
Начать придется с конца, с таблицы street, которая будет содержать список улиц, и на которую будет ссылаться таблица building, содержащая, в свою очередь, список всех телефонизированных зданий в городе.

create table street
(
st_id smallint unsigned not null auto_increment,
nick varchar(32) not null,
primary key (st_id)
);
insert into street ( nick )
select distinct street
from old;

Заполнение таблицы заняло 12.1 сек. Теперь создадим таблицу building

create table building
(
bd_id smallint unsigned not null auto_increment,
st_id smallint unsigned not null references street,
bldng varchar(8) not null,
tmp varchar(40) not null, # временно, для соответствия с old
primary key (bd_id)
);

и также заполним ее

insert into building ( st_id, tmp, bldng )
select distinct street.st_id, street.nick, old.bldng
from old, street
where old.street=street.nick;

Таблица заполнялась аж 5 мин. 23 сек., так что было время задуматься. Прояснить ситуацию в таких случаях помогает особая команда explain, например

explain select distinct street.st_id, street.nick, old.bldng
from old, street
where old.street=street.nick;

которая выдала следующую подсказку:

+--------+------+---------------+------+---------+------+--------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+--------+------+---------------+------+---------+------+--------+------------+
| street | ALL | NULL | NULL | NULL | NULL | 591 | |
| old | ALL | NULL | NULL | NULL | NULL | 122794 | where used |
+--------+------+---------------+------+---------+------+--------+------------+

Оказывается, для каждой записи из old происходит поиск в таблице street, т.е. просматриваются O(122794*591) строк.
Попробуем проиндексировать эти две таблицы по общему полю

create index street on street (nick);
create index street on old (street);

Тот же самый запрос теперь выглядит изнутри вот так:

+--------+------+---------------+------+---------+------+--------+
| table | type | possible_keys | key | key_len | ref | rows |
+--------+------+---------------+------+---------+------+--------+
| street | ALL | street | NULL | NULL | NULL | 591 |
| old | ALL | street | NULL | NULL | NULL | 122794 |
+--------+------+---------------+------+---------+------+--------+
----------------------------------------------+
Extra |
----------------------------------------------+
range checked for each record (index map: 1) |
----------------------------------------------+

и занимает 19.7 секунд. Даже с учетом ~1 мин. на создание индексов, выигрыш в скорости заметен. Разобравшись с индексами, можно их удалить

drop index street on street;
drop index street on old;

Создаем теперь новую таблицу phone

create table phone
(
phonum char(6) not null default "000000",
naim varchar(48) not null default "",
bd_id smallint unsigned not null references building,
other varchar(8) not null
);

Для заполнения последней таблицы даже не пробуем делать выборку из неиндексированых таблиц, а первым делом создаем индексы, используя заранее предусмотренное временное поле, соответствующее названию улицы.

create index building on building (tmp, bldng);
create index building on old (street, bldng);
insert into phone ( phonum, naim, bd_id, other )
select old.phonum, old.title, building.bd_id, old.other
from old, building
where old.street=building.tmp and old.bldng=building.bldng;

Индексы создались за 3 и 45 сек., а данные вставились за 19 сек. Теперь можно удалить рабочую таблицу и лишние индекс и поле:

drop table old;
drop index building on building;
alter table building drop tmp;

Подведем некоторые итоги.

Из исходного 8-мегабайтного текстового файла получились 3 связанные таблицы общим размером ~3.8MB. Простые запросы, например

select p.phonum, p.naim, s.nick, b.bldng
from phone p, street s, building b # короткие синонимы таблиц
where
p.bd_id=b.bd_id # таким образом
and b.st_id=s.st_id # связывают таблицы
and p.phonum like "%1234%" # собственно запрос
order by p.naim;

занимают ~1.6 сек. Это приблизительно совпадает с результатом сканера grep на оригинальном текстовом файле при поиске тех же строк, и немного превосходит время, демонстрируемое интерпретатором awk.

Но, конечно, MySQL создан не для того, чтобы соревноваться с grep или awk. Используя язык SQL, можно создавать БД и манипулировать данными любой сложности. В области клиент-серверных приложений MySQL вполне способен конкурировать с признанными коммерческими СУБД. Но вся мощь MySQL раскрывается в соединении с технологиями Internet, если так можно выразиться, в "дважды клиент-серверных" технологиях. Доступ к БД выполняется из приложений, запускаемых на web-сервере, результат выдается в виде HTML страниц. Затем web-сервер доставляет страницу в клиентский браузер.

В таком виде web-сервер Apache и реляционная СУБД MySQL образуют необычайно масштабируемую платформу для создания приложений. MySQL успешно трудится на самых разных аппаратных платформах, включая суперкомпьютеры, и может обслуживать много web-серверов, работающих на одном или на разных компьютерах. А можно настроить такой же тандем для работы на единственной скромной персоналке.

Как уже упоминалось, для доступа к MySQL можно использовать разные языки программирования. Похожий выбор языков программирования предлагает также и Apache через дополнительные модули, расширяющие возможности сервера. Существуют и успешно развиваются общедоступные открытые проекты Zope и Midgard , объединяющие Apache, MySQL и распространенные языки программирования в интегрированную среду разработки с единым интерфейсом.