MySQL Prepared Statement

Este artigo tem o objetivo de apresentar mais um dos vários recursos disponibilizados pelo Sistema de Gerenciamento de Bancos de Dados MySQL 5.0, os Prepared Statements, que colaboram diretamente com a otimização da execução de consultas repetitivas, com intenção de se obter resultados específicos, consultando tabelas do esquema de bancos de dados, contidos no SGBD MySQL. Também, abordar as principais bibliotecas PHP para se trabalhar com esse recurso.

Após ler este artigo, você estará apto a:

Definir o que é um Prepared Statement;
Trabalhar a sintaxe de preparo e despreparo de uma declaração no lado servidor;
Trabalhar com Prepared Statements em meio à linguagem de programação PHP;

O MySQL 5.0 provê suporte à criação ou preparo de declarações SQL para posterior execução, dentro do servidor de bancos de dados. Este recurso está presente no MySQL desde a sua versão 4.1 e trabalha diretamente com uma API[1] do próprio MySQL, chamada C API Prepared Statements. Esses procedimentos, após preparados, conseguem prover para a execução de declarações SQL grande rapidez e agilidade, utilizando um protocolo binário próprio da C API.

[1] API, de Application Programming Interface (ou Interface de Programação de Aplicativos) é um conjunto de rotinas e padrões estabelecidos por um software para a utilização das suas funcionalidades por programas aplicativos – isto é: programas que não querem envolver-se em detalhes da implementação do software, mas apenas usar seus serviços. De modo geral, a API é composta por uma série de funções acessíveis somente por programação, assim como um webservice, e que permitem utilizar características do software menos evidentes ao utilizador tradicional.

Para trabalharmos com tal recurso, basta termos conhecimento em selecionar, atualizar, inserir e excluir dados a partir de uma ou mais tabelas – geralmente, utilizam-se procedimentos preparados executando consultas SELECT, INSERT, DELETE ou UPDATE – e termos também, habilidades em trabalhar com “passagem” de parâmetros, de acordo com o que veremos na sintaxe geral de criação dos Prepared Statements.

Os Prepared Statements são declarações preparadas – literalmente como uma tradução do inglês para o português – que são armazenados no servidor de bancos de dados com a finalidade de executar consultas sem variação sintática, com dinamismo apenas nos parâmetros. Um Prepared Statement pode ainda ser definido dentro ou fora do contexto de um banco de dados e pode receber ou não parâmetros para sua execução.

A sintaxe geral para a criação de um Prepared Statement é a seguinte:

PREPARE stmt_name FROM preparable_stmt;

Explicando a sintaxe geral:

PREPARE é a declaração que utilizamos para solicitar ao SGBD que prepare o comando inserido após o FROM;
stmt_name é o nome da da declaração preparada;
preparable_stmt é onde informamos, com delimitação de aspas simples ou duplas, o comando SQL que executaremos de forma dinâmica;

O maior benefício que se tem em trabalhar com os Prepared Statements é a velocidade na execução dos comandos SQL, pois, após preparada, a declaração SQL é armazenada de forma pré-compilada no servidor de bancos de dados, sendo “parseado” somente uma vez, mesmo que executado várias vezes. Após prepararmos uma declaração, podemos executá-lo através do comando EXECUTE, seguido pelo nome dado ao Prepared Statement.

A seguir, criaremos o nosso primeiro Prepared Statement bem simples, ainda sem a passagem de parâmetros:

mysql> PREPARE minha_declaracao FROM 'SELECT NOW() AS Agora';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

Basta então, executarmos, como já explicado, chamando nossa declaração pelo nome, como segue:

mysql> EXECUTE minha_declaracao;
---------------------
 Agora               
---------------------
 2008-12-15 10:33:30 
---------------------
1 row in set (0.00 sec)

Os nomes para os Prepared Statements não são case sensitives, ou seja, criando uma declaração com o nome de MYSQL é o mesmo que MySQL ou ainda mysql. Além disso, o nome de um Prepared Statement requer uma string literal, não sendo permitido informar um via user ou local variables.

O caractere “?” – ponto de interrogação – é utilizado como caracter coringa para indicar a espera de um parâmetro em meio a uma declaração ou comando que será executado através de uma declaração preparada. Os caracteres coringas podem aparecer em meio aos comandos, quantas vezes forem necessárias, somente em substituição a dados e não em substituição a palavras reservadas à linguagem SQL.

Já que sabemos, agora, da existência do caracter coringa, podemos desenvolver declarações mais sofisticadas para armazená-las no servidor de bancos de dados. Utilizando a mesma idéia inicial, podemos tornar o mesmo SELECT um pouco mais sofisticado, como exibido a seguir.

mysql> PREPARE minha_declaracao FROM "SELECT DATE_FORMAT(?, '%d/%m') AS DiaMes";
Query OK, 0 rows affected (0.01 sec)

Statement prepared

Ao executarmos o último Prepared Statement criado, teremos que informar um parâmetro que será encaixado dentro da função DATE_FORMAT(), que é uma função nativa do MySQL para formatação de datas de acordo com o seu interesse. Perceba que iremos exibir, a parte do valor da variável, passado com parâmetro – neste contexto, uma data, obrigatoriamente – , somente o dia e o mês.

Para executarmos o nosso Prepared Statement passando um parâmetro, utilizamos a declaração USING, que nos permite trabalhar da seguinte forma:


mysql> SET @var =NOW(); EXECUTE minha_declaracao USING @var;

Query OK, 0 rows affected (0.00 sec)

--------

 DiaMes 
--------

 15/12  
--------

1 row in set (0.01 sec)

Veja que na mesma linha de execução aparece também outro comando, o SET, que tem a função de atribuir explicitamente um valor a uma variável do usuário, também conhecida como user variable. Ao executar a declaração preparada anteriormente, enviando a variável com o valor setado como parâmetro, este é passado diretamente ao Prepared Statement que exibe dia e mês.

Podemos preparar uma declaração que receba mais de um parâmetro e enviá-los todos de uma só vez para que sejam executados em meio à consulta. Para tal, criaremos uma tabela de exemplo chamada, tbl_usuario, onde faremos um INSERT de um novo usuário e depois um UPDATE das informações contidas na tabela relacionadas com o usuário que inserimos anteriormente. A seguir, criamos uma nova tabela, dentro do banco de dados test.

mysql> CREATE TABLE tbl_usuario (
    ->  usuario_id int unsigned not null auto_increment,
    ->  usuario_nome char(60) not null,
    ->  usuario_email char(80) not null unique,
    ->  dt_cadastro timestamp default current_timestamp(),
    ->  primary key(usuario_id)
    -> ) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

Com a tabela de exemplo criada, temos que inserir um usuário através de um Prepare Statement que receberá mais de um parâmetro. Como nossa tabela conta com duas colunas mandatórias – os dados devem ser obrigatoriamente informados – , nossa declaração contará com a espera de dois parâmetros. A seguir, preparamos a declaração.

mysql> PREPARE in_usuario
    -> FROM 'INSERT INTO tbl_usuario (usuario_nome,usuario_email) values (?,?)';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

Inserindo um usuário e selecionando os dados para aferir o resultado:

mysql> SET @nome='foo';SET @email='a@a.com';EXECUTE in_usuario USING @nome,@email;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

mysql> select * from tbl_usuario;
+------------+--------------+---------------+---------------------+
| usuario_id | usuario_nome | usuario_email | dt_cadastro         |
+------------+--------------+---------------+---------------------+
|          1 | foo          | a@a.com       | 2008-12-15 15:29:51 |
+------------+--------------+---------------+---------------------+
1 row in set (0.01 sec)

Podemos perceber que, quando temos mais de um parâmetro para ser passado através de um Prepared Statement, colocamos todos em sequência, de acordo com os dados a serem inseridos, na declaração USING, separados por vírgula. Podemos facilmente adaptar esse tipo de procedimento armazenado em meio às interações de aplicações com o banco de dados MySQL – lembre-se, existe ganho de performance.

Suponhamos que, neste momento, precisamos atualizar o nome do usuário, cujo usuario_id é igual a 1, de “foo” para “voo”. Podemos preparar um declaração que faça isso, como veremos a seguir.

mysql> PREPARE up_usuario
    -> FROM 'UPDATE tbl_usuario SET usuario_nome=? WHERE usuario_id =?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

Para excutar, seguimos o mesmo exemplo quando da inserção do usuário:

mysql> SET @nome='voo';SET @id=1;EXECUTE up_usuario USING @nome,@id;
Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Logicamente, o ganho de performace que apresenta tal procedimento, Prepared Statement, somente será percebido com operações em lote, de processamento mais pesado, o que veremos mais à frente, com a linguagem PHP.

Particularidade

Vale ressaltar que um Prepared Statement só poderá ser acessado em um servidor de bancos de dados MySQL pelo próprio usuário que o criou. Outro usuário não terá acesso aos Prepared Statements criados em sua conexão. O controle destes procedimentos é feito por thread[2], já que toda conexão de um usuário com o servidor de bancos de dados MySQL é atendida por uma nova thread.

Thread, ou linha de execução em português, é uma forma de um processo dividir a si mesmo em duas ou mais tarefas que podem ser executadas simultaneamente. Um exemplo clássico da criação de uma thread é quando há uma solicitação para efetuar o shutdown do MySQL. Internamente, é criada uma thread para atender tal solicitação, encerrando os procedimentos para que o servidor possa realizar sua desativação.

Para desalocar um Prepared Statement do servidor de bancos de dados MySQL, basta utilizar DEALLOCATE ou DROP seguido pelo nome da declaração, como exibido a seguir.

mysql> DEALLOCATE PREPARE minha_declaracao;
Query OK, 0 rows affected (0.00 sec)

Prepared Statement com linguagem PHP

Assim como exibido na documentação do MySQL e também é de ciência de todos que trabalham com linguagens de programação e bancos de dados, o PHP é uma linguagem de plena compatibilidade e transparência com o servidor de bancos de dados MySQL, tendo várias bibliotecas conhecidas como third party como ADOdb ou mesmo bibliotecas nativas, como a mysql e mysqli.

Com o ADOdb, podemos preparar declarações da seguinte forma:

01. $stmt = DB->Prepare('insert into table (col1,col2) values (?,?)'); 
02. for ($i=0; $i <= $max; $i++)  {
03.     $DB->Execute($stmt,array((string) rand(), $i)); 
04. }

Perceba que na linha 01, atribuímos à variável stmt o Prepared Statement para inserção de dados em lote. Nesse momento, temos a declaração preparada, armazenada no servidor de forma pré-compilada, o que torna a execução dos comandos SQL bem bem mais rápidas. Dentro do loop for(), já na linha 03, executamos o Prepared Stetament, contida na variável stmt, passando os parâmetros em um array().

Com o mysqli, conhecida também por MySQL Improved ou mesmo MySQL Melhorada, podemos trabalhar com os Prepared Statements da seguinte forma:

// parâmetro que utilizaremos para efetuar a busca
$city = "Amersfoort";


// criamos o Prepared Statement
if ($stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")) {


    // passamos os parâmetros
    $stmt->bind_param("s", city);


    // executamos a consulta
    $stmt->execute();


    // buscamos os resultados
    $stmt->bind_result(district);


    // procuramos o valor
    $stmt->fetch();


    printf("s is in district s\n", $city, district);


    // fechamos a declaração
    $stmt->close();

}

// fechamos a conexão

$mysqli->close();

Conclusão

Vimos, então, como tornar a execução de cosultas com poucas modificações, mais rápidas, trabalhando com os Prepared Statements. O MySQL, além de já ser bastante rápido na execução de consultas, traz mais este recurso para tornar o seu trabalho ainda mais eficiente. Experimente trabalho com este recurso e perceba como e de quanto será o seu ganho em performance em aspectos gerais, na aplicação e com a integração desta com o banco de dados MySQL.

No próximo artigo, abordaremos o Tratamento de Erros em Stored Procedures no MySQL.

De 0 a 10, o quanto você recomendaria este artigo para um amigo?

Powered by:

MySQL Prepared Statement

Wagner Bianchi

Wagner Bianchi