~$ make dev: сентября 2012

понедельник, 24 сентября 2012 г.

PHP расширение mysqli - хранимые процедуры

Хранимая процедура — это SQL-процедура расположенная в базе данных. От функции она отличается тем, что:

процедура не возвращает значения, но может изменять свои параметры;
процедура может возвращать множество строк результата (но не через параметр, а в результате SELECT-запросов в теле процедуры).

Хранимые процедуры (и функции) могут быть полезны в следующих случаях:

Если есть какой-то набор действий, который нужно выполнять одинаково с разных клиентов. Да даже и на одном клиенте иногда надёжнее создать sql-программу. Например если при изменении счёта пользователя нужно также отредактировать поля «приход», «уход» и дополнительно сделать запись в соответствующую действию таблицу транзакций.
Если безопасность на первом месте. Вся работа с базой данных может быть организована через процедуры и функции, при этом доступ к таблицам БД может быть закрыт.

Для кода примеров я использовал следующую процедуру:

delimiter #
 
DROP PROCEDURE IF EXISTS EXEC#
 
CREATE PROCEDURE EXEC(OUT msg VARCHAR(50))
BEGIN
 
    SET msg = "zzz";
    SELECT * FROM `test` LIMIT 5;
 
END;#
 
delimiter ;

Вызов процедуры выглядит следующим образом:

$mysqli = new mysqli('host', 'user', 'pass', 'database');
$result = $mysqli->query('CALL exec(@msg)') OR
    exit('Error: (' . $mysqli->errno . ') ' . $mysqli->error);

@msg – это выходной параметр, который в результате выполнения процедуры получает значение «zzz». Выбор результата SELECT-запроса из процедуры происходит также, как и с обычным запросом:

while ($row = $result->fetch_assoc())
{
    print_r($row);
    echo '<br />';
}
 
$result->free();

Но чтобы выбрать результаты других SELECT-запросов из тела процедуры (а также выходных параметров) или выполнить какой-либо другой запрос (не связанный с процедурой), необходимо выбрать все результаты подготовленные БД:

$mysqli->next_result();
$result = $mysqli->store_result();

Теперь можно получить значение переменной:

$result = $mysqli->query('SELECT @msg') OR
    exit('Error: (' . $mysqli->errno . ') ' . $mysqli->error);
 
$row = $result->fetch_assoc();
 
$result->free();
$mysqli->close();

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

$mysqli = new mysqli('host', 'user', 'pass', 'database');
 
$stmt = $mysqli->prepare('CALL exec(@msg)') OR
    exit('Prepare failed: (' . $mysqli->errno . ') ' . $mysqli->error);
 
$stmt->execute() OR
    exit('Execute failed: (' . $mysqli->errno . ') ' . $mysqli->error);
 
while ($stmt->more_results())
{
    $result = $stmt->get_result();
 
    if (is_object($result))
    {
        while ($row = $result->fetch_assoc())
        {
            print_r($row);
            echo '<br />';
        }
 
        $result->free();
    }
 
    $stmt->next_result();
}
 
$result = $mysqli->query('SELECT @msg') OR
    exit('Error: (' . $mysqli->errno . ') ' . $mysqli->error);
 
$row = $result->fetch_assoc();
print_r($row);
echo '<br />';
 
$mysqli->close();

Можно даже привязывать результаты запросов:

while ($stmt->more_results())
{
    $col_a = $col_b = 0;
 
    $stmt->bind_result($col_a, $col_b);
 
    while ($stmt->fetch())
    {
        echo 'col_a = ' . $col_a . '; ';
        echo 'col_b = ' . $col_b . '<br />';
    }
 
    $stmt->next_result();
}

Но вот привязать перменную к параметру процедуры у меня не получилось ни к входному, ни к выходному.

понедельник, 17 сентября 2012 г.

PHP расширение mysqli - подготовленные выражения

Подготовленное выражение (ПВ) — это выражение (SQL-запрос) с параметрами используемое для многократного выполнения с высокой эффективностью. При этом эффективность проявляется в ситуации когда запрос нужно выполнять более одного раза.

MySQL сервер поддерживает интерфейс к ПВ на уровне SQL. Но, согласно, документации этот вариант менее эффективен, чем использование специализированного интерфейса (mysqli в случае с PHP).

В mysqli выполнение ПВ состоит из двух пунктов: подготовка и выполнение. При подготовке SQL-выражение, с маркерами в виде знаков вопроса, куда будут подставляться значения, отправляется серверу БД. Сервер проверяет синтаксис и инициализируется:

$stmt = $mysqli->prepare('INSERT INTO `test` (`id`) VALUES (?)')
    OR exit('Prepare failed: (' . $mysqli->errno . ') ' .
    $mysqli->error);

При вызове метода prepare(), создаётся объект класса mysqli_stmt, представляющий собой подготовленное выражение.

На стадии выполнения клиент привязывает значения к параметрам и отправляет их серверу БД. Сервер уже выполняет SQL-запрос с подставленными параметрами:

$id  = 1;
$val = 'value';
 
$stmt->bind_param('is', $id, $val)
    OR exit('Binding failed: (' . $stmt->errno . ') ' .
    $stmt->error);
 
$stmt->execute()
    OR exit('Execute failed: (' . $stmt->errno . ') ' .
    $stmt->error);

При этом метод bind_param() принимает минимум два параметра. Первый указывает на типы передаваемых значений всех остальных параметров, по одному символу на параметр:

i - integer
d - double
s - string
b – blob (двоичные строки)

Остальные параметры bind_param() передаются по ссылке и привязывают переменную PHP к маркеру в запросе (в порядке очереди). При этом специальные символы будут экранированы сервером БД. Отправка параметра происходит только при выполнении метода execute().

Помимо параметров, можно также привязывать PHP переменные к результатам запроса:

$stmt = $mysqli->prepare('SELECT `id`, `value` FROM `test` WHERE `id` = "1"')
    OR exit('Prepare failed: (' . $mysqli->errno . ') ' .
    $mysqli->error);
 
$stmt->execute()
    OR exit('Execute failed: (' . $stmt->errno . ') ' .
    $stmt->error);
 
$stmt->bind_result($col1, $col2)
    OR exit('Binding results failed: (' . $stmt->errno . ') ' .
    $stmt->error);
 
while ($stmt->fetch())
{
    echo 'col1 = "' . $col1 . '"; col2 = "' . $col2 . '"<br />';
}

Подготовленное выражение может быть использовано многократно, при этом выражение не пересылается серверу БД и не проверяется повторно:

$stmt = $mysqli->prepare('INSERT INTO `test` (`id`, `value`) VALUES (?, ?)')
    OR exit('Prepare failed: (' . $mysqli->errno . ') ' .
    $mysqli->error);
 
$id    = 0;
$value = '';
 
$stmt->bind_param('is', $id, $value)
    OR exit('Binding failed: (' . $stmt->errno . ') ' .
    $stmt->error);
 
for ($id = 1; $id < 5; $id++)
{
    $value = 'val ' . $id;
 
    $stmt->execute()
        OR exit('Execute failed: (' . $stmt->errno . ') ' .
        $stmt->error);
}

Каждое ПВ занимает ресурсы сервера, поэтому после использования их необходимо закрывать.

$stmt->close();

В заключение простой тест для сравнения ПВ с традиционными запросами в mysqli. Время замерялось с помощью функции microtime() от создания объекта до закрытия соединения. В тесте запускалось по 1000 запросов:

INSERT INTO `test` (`id`, `value`) VALUES (?, ?)

Результат следующий:

ПВ

13.664777994156

13.61216211319

13.906296014786

13.861138105392

обычный подход

15.933166027069

16.207018136978

16.945013999939

17.206493139267

понедельник, 10 сентября 2012 г.

PHP расширение mysqli - Асинхронные запросы

Асинхронный, неблокирующий ввод-вывод это старая идея. Вот и в PHP расширении mysqli присутствует возможность асинхронных запросов. Эта возможность доступна только при использовании библиотеки mysqlnd (в противовес libmysql).

В обычной схеме, после отправки запроса к БД PHP-скрипт заблокирован до получения ответа (в самом распространённом варианте: до получения всех записей соответствующих запросу). При использовании асинхронности скрипт продолжает работу сразу же после отправки запроса, а затем уже можно производить опрос mysqli-объектов с целью получения ответов.

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

MySQL протокол клиент-сервер не имеет встроенной поддержки асинхронности, поэтому для её реализации в PHP необходимо отдельное соединение (объект mysqli) на каждый запрос.

Для отправка асинхронного запроса в метод mysqli::query() добавляется второй параметр «MYSQLI_ASYNC»:

$mysqli = new mysqli('host', 'user', 'pass', 'database');
$mysqli->query('...', MYSQLI_ASYNC);

При опросе соединения на предмет получения результата предлагается использовать статический метод mysqli::poll(). Но в PHP 5.3.3 (той, что у меня сейчас установлена) этот метод почему-то недоступен, поэтому я использую процедурный вариант, т.е. mysqli_poll():

mysqli_poll($read, $write, $error, 1);

Добавим проверку ошибок, вывод результатов и получим рабочий пример:

$mysqli = new mysqli('host', 'user', 'pass', 'database');
$mysqli->query('SELECT * FROM `test`', MYSQLI_ASYNC);
 
$all_links = array($mysqli);
$processed = 0;
 
do
{
    $read = $write = $error = array();
 
    foreach ($all_links as $link)
    {
        $read[] = $write[] = $error[] = $link;
    }
 
    if (!mysqli_poll($read, $write, $error, 1))
    {
        if (count($error))
            foreach ($error as $link)
            {
                echo $link->error . '<br />';
                $processed++;
        }
    }
 
    foreach ($read as $link)
    {
        if ($result = $link->reap_async_query())
        {
            if (is_object($result)) // for SELECT
            {
                print_r($result->fetch_row());
                $result->free();
 
            } else { // for INSERT / UPDATE / DELETE
 
                print_r($link);
            }
 
            $processed++;
        }
    }
}
while ($processed < count($all_links));
 
$mysqli->close();

Описанная выше функциональность плохо документирована, поэтому следующие ссылки могут пригодиться:

понедельник, 3 сентября 2012 г.

PHP расширение mysqli

mysqli (MySQL Improved Extension) — это одно из трёх API предоставляемых расширениями PHP для работы с MySQL (другие два это: mysql и PDO).

mysqli и PDO являются рекомендованными для новых проектов, в отличии от mysql которая считается устаревшей.

Представленные расширения для своей работы могут использовать одну из библиотек обеспечивающих необходимый протокол: mysqlnd или libmysql. mysqlnd новее (опять же рекомендована) и с версии PHP 5.3 распространяется вместе с ним.

mysqli по сравнению с mysql имеет следующие преимущества:

ОО интерфейс (процедурный интерфейс тоже в наличии);
поддержка неблокирующих, асинхронных запросов (с mysqlnd);
поддержка подготовленных выражений (Prepared Statements) на стороне сервера;
поддержка хранимых процедур;
поддержка множественных запросов (Multiple Statements);
поддержка транзакций;
продвинутые возможности отладки;
поддержка всей функциональности MySQL 5.1+.

В этой статье я рассмотрю только базовые возможности этого расширения.

Как уже упоминал, mysqli поддерживает и ОО, и процедурный интерфейс. Разницы в производительности нет и выбор зависит от предпочтений. Далее я буду использовать ОО подход, так как применяю его, по возможности, и в ежедневной работе.

Подключение

Класс mysqli представляет собой подключение к БД, и среди свойств содержит следующие:

$affected_rows — количество строк в результате последнего запроса;
$connect_errno — код ошибки последнего подключения;
$connect_error — описание ошибки последнего подключения;
$errno — код ошибки последней вызванной функции;
$error — описание последней ошибки;
$insert_id — автоматически сгенерированный идентификатор использованный в последнем запросе.

Для подключения к базе используется конструктор:

new mysqli('host', 'user', 'pass', 'database', 'port');

Но, если необходимо установить какие-то опции подключения, то нужно использовать метод real_connect():

$mysqli = new mysqli();
$mysqli->options('option', 'value');
$mysqli->real_connect('host', 'user', 'pass', 'database');

Базовые запросы выполняются следующим образом:

$result = $mysqli->query('...');
$result->free();
$mysqli->close();

Результат запроса

Метод mysqli::query() при запросе, возвращающем значения, вернёт объект класса mysqli_result.

При помощи объекта mysqli_result можно получить записи которые выбрал запрос:

if ($result->num_rows)
    while ($row = $result->fetch_assoc())
    {
    }
 
$result->free();

Помимо классов mysqli и mysqli_result есть ещё:

mysqli_stmt - подготовленное выражение (Prepared Statements);
mysqli_driver – не документировано;
mysqli_warning – не документировано;
mysqli_sql_exception – не документировано.

В заключение полный пример базового использования mysqli:

$mysqli = new mysqli('localhost', 'root', '', 'test');
 
if ($mysqli->connect_error)
    exit('Connect error: "' . $mysqli->connect_error . '"');
 
$result = $mysqli->query('SELECT * FROM `test`') OR
    exit('Query error: "' . $mysqli->error . '"');
 
while ($row = $result->fetch_assoc())
{
    print_r($row); echo '<br />';
}
 
$result->free();
$mysqli->close();

понедельник, 24 сентября 2012 г.