PHP MySQL e MariaDB

Até aqui, a lojinha da Ana guardou tudo num SQLite: um arquivo loja.db ao lado dos programas. Para um site pequeno, isso basta. Mas a maioria das hospedagens e dos sistemas PHP do mundo (WordPress, Moodle, lojas virtuais) usa um servidor de banco de dados, quase sempre o MySQL ou o seu "irmão" MariaDB. A boa notícia: com o PDO, o seu código PHP quase não muda. Muda a linha da conexão e alguns detalhes do SQL. Esta aula mostra exatamente quais.

Antes desta aula, você precisa conhecer o PDO, o CRUD e os prepared statements. Os programas foram executados com o PHP 8.5 na máquina fictícia da Ana, na pasta ~/loja.

A ideia: o caderno e a secretaria

Pense em duas formas de guardar as fichas dos alunos de uma escola:

• Um caderno na mesa da professora (o SQLite). Quem quer uma ficha abre o caderno. É simples, não precisa de ninguém para cuidar, mas só uma pessoa escreve de cada vez, e o caderno está naquela mesa.
• A secretaria da escola (o MySQL). As fichas ficam num arquivo trancado, e existe um funcionário no balcão. Você não mexe nas fichas: vai ao balcão, diz quem é (usuário e senha) e faz o pedido. O funcionário atende muitas pessoas ao mesmo tempo, controla quem pode ver o quê e pode ficar em outro prédio.

Esse "funcionário" é um programa servidor que fica rodando o tempo todo (no Linux, o processo se chama mariadbd ou mysqld) e atende pedidos pela rede, normalmente na porta 3306. O seu PHP é o cliente: ele se conecta, se identifica e envia SQL.

MySQL ou MariaDB?

O MySQL nasceu em 1995 e virou o banco mais usado com PHP. Em 2009, quando ele passou a pertencer à Oracle, um dos criadores (Michael "Monty" Widenius) fez uma cópia livre chamada MariaDB (MySQL e MariaDB têm os nomes das filhas dele, My e Maria). Hoje:

• O Debian e várias outras distribuições trazem o MariaDB como padrão. O comando mysql continua existindo nelas, apontando para o MariaDB.
• Para tudo o que este guia ensina (criar tabelas, SELECT, INSERT, conectar pelo PHP), os dois são iguais. O PHP usa o mesmo driver para os dois: pdo_mysql, com o prefixo mysql:.
• As diferenças aparecem em recursos avançados e em versões novas. Se a sua hospedagem diz "MySQL 8" ou "MariaDB 11", o código desta aula funciona nos dois.

No resto da página, "MySQL" vale para os dois.

1. Instalar o servidor e a extensão do PHP

São duas peças diferentes, e esquecer uma delas é o erro mais comum:

1. O servidor (MariaDB ou MySQL): o "funcionário da secretaria".
2. A extensão pdo_mysql do PHP: o "idioma" que o PHP usa para falar com ele. Sem ela, o PHP nem tenta conectar (você vai ver a mensagem could not find driver mais adiante).

A tabela abaixo mostra os comandos de cada sistema. Ela é ilustrativa: os nomes dos pacotes mudam de versão para versão, então confira a documentação do seu sistema. A instalação do PHP em si está em Instalar no Linux e Instalar no Windows e macOS.

No Debian e no Ubuntu, o servidor já começa a rodar depois da instalação. Vale ainda rodar sudo mariadb-secure-installation, um assistente que remove usuários de teste e o banco test que vêm de fábrica.

Conferindo se o PHP tem o driver

Antes de qualquer coisa, pergunte ao PHP se a extensão está lá. O php -m lista os módulos (extensões) e o grep -i mysql filtra só as linhas com "mysql":

$ php -m | grep -i mysql
mysqlnd                       « a biblioteca que fala o "idioma" do MySQL
pdo_mysql                     « o driver do PDO: é este que você precisa
$ php -r 'print_r(PDO::getAvailableDrivers());'
Array
(
    [0] => mysql
    [1] => pgsql
    [2] => sqlite
)

PDO::getAvailableDrivers() devolve a lista de bancos que este PHP sabe usar. Na máquina da Ana são três: mysql (MySQL e MariaDB), pgsql (PostgreSQL) e sqlite. Se mysql não aparecer, instale a extensão (tabela acima) antes de continuar.

<?php
print_r(PDO::getAvailableDrivers());

try {
    $pdo = new PDO("mysql:host=localhost;dbname=loja;charset=utf8mb4", "ana", "troque-esta-senha");
    echo "Conectado!\n";
} catch (PDOException $e) {
    echo "Falhou: ", $e->getMessage(), "\n";
}

2. Criar o banco e o usuário da loja

Um servidor MySQL guarda vários bancos (um para a loja, outro para o blog…), cada um com suas tabelas. E ele tem usuários próprios, que não são os usuários do Linux. A regra de ouro: cada sistema tem o seu usuário, com acesso só ao seu banco. Nunca ponha o usuário root do MySQL no código PHP.

Você entra no servidor como administrador com o cliente de linha de comando (no Debian, sudo mariadb; em outros sistemas, mysql -u root -p) e digita:

CREATE DATABASE loja CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE USER 'ana'@'localhost' IDENTIFIED BY 'troque-esta-senha';

GRANT ALL PRIVILEGES ON loja.* TO 'ana'@'localhost';

A tabela produtos, versão MySQL

A mesma tabela que você criou no SQLite em CRUD com PDO fica assim no MySQL:

CREATE TABLE produtos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    preco DECIMAL(10,2) NOT NULL,
    estoque INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INT AUTO_INCREMENT PRIMARY KEY

Número inteiro que o servidor preenche sozinho (1, 2, 3…). No SQLite, o equivalente é INTEGER PRIMARY KEY.

VARCHAR(100)

Texto de até 100 caracteres. O MySQL quer saber o tamanho máximo; o SQLite usa só TEXT.

DECIMAL(10,2)

Número exato com 10 dígitos, 2 deles depois da vírgula: o tipo certo para dinheiro (nada de 0.1 + 0.2 dando 0.30000000000000004).

ENGINE=InnoDB

O "motor" que guarda a tabela. O InnoDB é o padrão e suporta transações.

DEFAULT CHARSET=utf8mb4

A tabela guarda texto em UTF-8 completo.

3. O DSN do MySQL

Lembra que o PDO recebe um DSN (Data Source Name, "nome da fonte de dados"), um texto que diz qual banco usar e onde ele está? No SQLite, era só sqlite: e o caminho do arquivo. No MySQL, o DSN tem mais peças, separadas por ;:

mysql:host=localhost;port=3306;dbname=loja;charset=utf8mb4

mysql:

O driver: "fale com um MySQL (ou MariaDB)". Precisa da extensão pdo_mysql.

host=localhost

Onde o servidor está. localhost = esta mesma máquina. Pode ser um IP (127.0.0.1) ou um nome (db.minhahospedagem.com.br).

port=3306

A porta. Opcional: se você não escrever, vale 3306, a padrão.

dbname=loja

Qual banco, dos vários do servidor.

charset=utf8mb4

Em que codificação o texto vai e volta. Sem isso, acentos podem virar é.

Repare no que não vai no DSN: o usuário e a senha. Eles são o segundo e o terceiro argumento do new PDO.

mysql:host=localhost;port=3306;dbname=loja;charset=utf8mb4

<?php
$host  = "localhost";
$porta = 3306;
$banco = "loja";

// monte o $dsn aqui

echo $dsn, "\n";

<?php
$host  = "localhost";
$porta = 3306;
$banco = "loja";

$dsn = "mysql:host=$host;port=$porta;dbname=$banco;charset=utf8mb4";

echo $dsn, "\n";

4. Conectar com o PDO

Com o servidor rodando, o banco e o usuário criados, a conexão é a mesma de Conectar com PDO, só que com o DSN novo e com usuário e senha:

<?php
$dsn = "mysql:host=localhost;port=3306;dbname=loja;charset=utf8mb4";
$usuario = "ana";
$senha = "troque-esta-senha";

try {
    $pdo = new PDO($dsn, $usuario, $senha, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
    echo "Conectado ao MySQL!\n";
} catch (PDOException $e) {
    error_log("Falha no banco: " . $e->getMessage());
    echo "Desculpe, a loja está fora do ar. Tente mais tarde.\n";
    exit(1);
}

Na máquina da Ana não há servidor MySQL. Veja o que acontece:

$ php conexao.php
Falha no banco: SQLSTATE[HY000] [2002] No such file or directory      « veio do error_log
Desculpe, a loja está fora do ar. Tente mais tarde.
$ echo $?
1
$ php conexao.php 2>/dev/null                                           « escondendo a saída de erros
Desculpe, a loja está fora do ar. Tente mais tarde.

Na primeira execução aparecem as duas linhas, porque no terminal a saída de erros (onde o error_log escreve) e a saída normal vão para a mesma tela. Na segunda, o 2>/dev/null joga fora a saída de erros, e sobra só o que um visitante do site veria: a mensagem amigável. Num site de verdade, o error_log vai para o arquivo de log do servidor web, e o visitante nunca vê detalhes técnicos.

E se eu não tratar o erro?

Sem o try/catch, a exceção não é capturada e o PHP para com um Fatal error:

<?php
$pdo = new PDO("mysql:host=localhost;dbname=loja;charset=utf8mb4", "ana", "segredo");
echo "Conectou!\n";

$ php conectar.php

Fatal error: Uncaught PDOException: SQLSTATE[HY000] [2002] No such file or directory in /home/ana/loja/conectar.php:2
Stack trace:
#0 /home/ana/loja/conectar.php(2): PDO->__construct('mysql:host=loca...', 'ana', Object(SensitiveParameterValue))
#1 {main}
  thrown in /home/ana/loja/conectar.php on line 2

Leia a linha #0 do stack trace (o "rastro" das chamadas): o PHP mostra os argumentos do new PDO, mas a senha aparece como Object(SensitiveParameterValue) ("valor sensível"), e não como 'segredo'. Desde o PHP 8.2+, parâmetros marcados como sensíveis são escondidos assim. Mesmo assim, o caminho dos arquivos, o nome do usuário e o começo do DSN aparecem. Por isso: trate o erro de conexão e, no servidor de produção, deixe display_errors desligado (veja Tipos de Erro).

5. As mensagens de erro de conexão

A mensagem SQLSTATE[HY000] [2002] … tem três partes:

SQLSTATE[HY000]

Um código padrão de erro de SQL. HY000 é o "erro geral": quer dizer só "veja o resto".

[2002]

O código do próprio MySQL. 2002 = "não consegui chegar até o servidor".

No such file or directory

O motivo, explicado pelo sistema.

O motivo muda conforme o host. Este programa tenta três endereços e mostra a mensagem de cada um:

<?php
$enderecos = [
    "mysql:host=localhost;dbname=loja;charset=utf8mb4",
    "mysql:host=127.0.0.1;port=3306;dbname=loja;charset=utf8mb4",
    "mysql:host=banco.exemplo.invalid;dbname=loja;charset=utf8mb4",
];

foreach ($enderecos as $dsn) {
    echo $dsn, "\n";
    try {
        new PDO($dsn, "ana", "troque-esta-senha");
    } catch (PDOException $e) {
        echo "  → ", $e->getMessage(), "\n";
    }
}

$ php tres-servidores.php
mysql:host=localhost;dbname=loja;charset=utf8mb4
  → SQLSTATE[HY000] [2002] No such file or directory
mysql:host=127.0.0.1;port=3306;dbname=loja;charset=utf8mb4
  → SQLSTATE[HY000] [2002] Connection refused
mysql:host=banco.exemplo.invalid;dbname=loja;charset=utf8mb4
  → SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo for banco.exemplo.invalid failed: Name does not resolve

Três mensagens diferentes para o mesmo problema ("não achei o servidor"). Cada uma aponta uma causa:

Quando o servidor existe e atende, os erros passam a ser sobre quem você é e o que pediu. Estes não puderam ser reproduzidos na máquina da Ana (ela não tem servidor), mas você vai reconhecê-los pelo código:

6. SQLite × MySQL: o que muda no SQL

Com o PDO, o PHP é o mesmo: prepare, execute, fetch, lastInsertId, rowCount, transações. O que muda é o SQL que você escreve, porque cada banco tem o seu "sotaque".

A armadilha: SQL do MySQL dentro do SQLite

Muito tutorial na internet traz o CREATE TABLE do MySQL. O que acontece se você colar isso num SQLite? Com o final ENGINE=InnoDB, o SQLite reclama na hora:

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->exec("CREATE TABLE produtos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

$ php engine.php

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 1 near "=": syntax error in /home/ana/loja/engine.php:3
Stack trace:
#0 /home/ana/loja/engine.php(3): PDO->exec('CREATE TABLE pr...')
#1 {main}
  thrown in /home/ana/loja/engine.php on line 3

near "=": syntax error = "erro de sintaxe perto do =": o SQLite não conhece ENGINE=…. Até aí, fácil de ver. O perigo é o próximo caso: sem o ENGINE, o SQLite aceita o resto sem reclamar…

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->exec("CREATE TABLE produtos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    preco DECIMAL(10,2) NOT NULL
)");
$pdo->exec("INSERT INTO produtos (nome, preco) VALUES ('Caderno', 15.90)");
$pdo->exec("INSERT INTO produtos (nome, preco) VALUES ('Caneta', 2.50)");

foreach ($pdo->query("SELECT id, nome FROM produtos") as $linha) {
    echo "id: ", var_export($linha["id"], true), " | nome: ", $linha["nome"], "\n";
}

$ php mysql-no-sqlite.php
id: NULL | nome: Caderno          « o id não foi numerado!
id: NULL | nome: Caneta

Nenhum erro, e os produtos ficaram sem id. O motivo: o SQLite leu INT AUTO_INCREMENT como um nome de tipo qualquer (ele aceita quase tudo como tipo) e não entendeu AUTO_INCREMENT como "numere sozinho". No SQLite, só a forma exata INTEGER PRIMARY KEY (com INTEGER escrito por extenso) cria a numeração automática. Um INT PRIMARY KEY é só uma coluna comum, que aceita NULL.

Os tipos flexíveis do SQLite

Outra diferença que surpreende quem vem do MySQL:

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->exec("CREATE TABLE produtos (nome TEXT, preco REAL)");
$pdo->exec("INSERT INTO produtos VALUES ('Caderno', 'quinze reais')");

$linha = $pdo->query("SELECT preco, typeof(preco) AS tipo FROM produtos")->fetch(PDO::FETCH_ASSOC);
print_r($linha);

$ php tipos.php
Array
(
    [preco] => quinze reais        « texto guardado numa coluna de números
    [tipo] => text
)

O SQLite guardou o texto quinze reais na coluna preco sem reclamar (a função typeof do SQLite confirma: text). Um MySQL em modo estrito recusaria esse INSERT. Moral: valide os dados no PHP antes de gravar (veja Validar Dados), qualquer que seja o banco. No SQLite, você pode ainda criar a tabela com a palavra STRICT no fim (CREATE TABLE … (…) STRICT), e aí ele passa a recusar, com a mensagem cannot store TEXT value in REAL column produtos.preco.

7. Trocar de banco mudando só a conexão

A grande vantagem do PDO: se o SQL da aplicação é o "comum" (o que é igual nos dois), só a criação do PDO muda. Um jeito organizado é guardar os dados da conexão num arquivo de configuração e montar o DSN a partir dele:

<?php
// Em casa: SQLite. Na hospedagem: troque por MySQL.
return [
    "driver"  => "mysql",
    "host"    => "localhost",
    "banco"   => "loja",
    "usuario" => "ana",
    "senha"   => "troque-esta-senha",
];

O resto do sistema faz $config = require "config.php"; (veja include e require) e nunca precisa saber qual banco está do outro lado. Duas regras de segurança para esse arquivo: ele não vai para o Git (a senha ficaria pública) e não fica numa pasta acessível pelo navegador.

sqlite:loja.db
mysql:host=localhost;dbname=loja;charset=utf8mb4
Conectado com o driver sqlite

<?php
function montarDsn(array $c): string
{
    // se o driver for "sqlite", devolva outro DSN aqui

    return "mysql:host={$c['host']};dbname={$c['banco']};charset=utf8mb4";
}

echo montarDsn(["driver" => "sqlite", "arquivo" => "loja.db"]), "\n";
echo montarDsn(["driver" => "mysql", "host" => "localhost", "banco" => "loja"]), "\n";

$pdo = new PDO(montarDsn(["driver" => "sqlite", "arquivo" => ":memory:"]));
echo "Conectado com o driver ", $pdo->getAttribute(PDO::ATTR_DRIVER_NAME), "\n";

<?php
function montarDsn(array $c): string
{
    if ($c["driver"] === "sqlite") {
        return "sqlite:" . $c["arquivo"];
    }

    return "mysql:host={$c['host']};dbname={$c['banco']};charset=utf8mb4";
}

echo montarDsn(["driver" => "sqlite", "arquivo" => "loja.db"]), "\n";
echo montarDsn(["driver" => "mysql", "host" => "localhost", "banco" => "loja"]), "\n";

$pdo = new PDO(montarDsn(["driver" => "sqlite", "arquivo" => ":memory:"]));
echo "Conectado com o driver ", $pdo->getAttribute(PDO::ATTR_DRIVER_NAME), "\n";

$pdo->getAttribute(PDO::ATTR_DRIVER_NAME) pergunta ao objeto PDO com qual driver ele está conectado. Útil quando um trecho de SQL precisa ser diferente para cada banco.

8. mysqli × PDO

Você vai encontrar em tutoriais e em sistemas antigos uma segunda forma de falar com MySQL: a extensão mysqli (MySQL improved, "MySQL melhorado"). Ela é boa e segura quando usada com prepared statements, mas só fala com MySQL/MariaDB. Este é o mesmo SELECT nas duas, só para você reconhecer (exemplo para ler):

<?php
$db = new mysqli("localhost", "ana", "troque-esta-senha", "loja");
$db->set_charset("utf8mb4");

$stmt = $db->prepare("SELECT nome, preco FROM produtos WHERE preco < ?");
$stmt->bind_param("d", $limite);   // "d" = double (número com casas decimais)
$limite = 20;
$stmt->execute();
foreach ($stmt->get_result() as $linha) {
    echo $linha["nome"], "\n";
}

<?php
$pdo = new PDO("mysql:host=localhost;dbname=loja;charset=utf8mb4", "ana", "troque-esta-senha");

$stmt = $pdo->prepare("SELECT nome, preco FROM produtos WHERE preco < ?");
$stmt->execute([20]);
foreach ($stmt as $linha) {
    echo $linha["nome"], "\n";
}

Qual usar? Para aprender e para sistemas novos, PDO: uma forma só de trabalhar, para qualquer banco, e você treina tudo com SQLite antes de ter um servidor. Use mysqli se for manter um sistema que já o usa. Se encontrar funções mysql_query, mysql_connect (sem o "i"), é código muito antigo: essa extensão foi removida no PHP 7.0 e não existe mais.

Quando dá errado

$ php conexao.php
Falha no banco: SQLSTATE[HY000] [2002] No such file or directory
Desculpe, a loja está fora do ar. Tente mais tarde.

$ php tres-servidores.php
mysql:host=localhost;dbname=loja;charset=utf8mb4
  → SQLSTATE[HY000] [2002] No such file or directory
mysql:host=127.0.0.1;port=3306;dbname=loja;charset=utf8mb4
  → SQLSTATE[HY000] [2002] Connection refused
mysql:host=banco.exemplo.invalid;dbname=loja;charset=utf8mb4
  → SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo for banco.exemplo.invalid failed: Name does not resolve

$ php -r 'new PDO("sqlsrv:Server=localhost;Database=loja");'

Fatal error: Uncaught PDOException: could not find driver in Command line code:1
Stack trace:
#0 Command line code(1): PDO->__construct('sqlsrv:Server=l...')
#1 {main}
  thrown in Command line code on line 1

$ php -r 'new mysqli("localhost", "ana", "segredo", "loja");'

Fatal error: Uncaught Error: Class "mysqli" not found in Command line code:1
Stack trace:
#0 {main}
  thrown in Command line code on line 1

$ php mysql-no-sqlite.php
id: NULL | nome: Caderno
id: NULL | nome: Caneta

id: 1 | nome: Caderno
id: 2 | nome: Caneta

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->exec("CREATE TABLE produtos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    preco DECIMAL(10,2) NOT NULL
)");
$pdo->exec("INSERT INTO produtos (nome, preco) VALUES ('Caderno', 15.90)");
$pdo->exec("INSERT INTO produtos (nome, preco) VALUES ('Caneta', 2.50)");

foreach ($pdo->query("SELECT id, nome FROM produtos") as $linha) {
    echo "id: ", var_export($linha["id"], true), " | nome: ", $linha["nome"], "\n";
}

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->exec("CREATE TABLE produtos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nome TEXT NOT NULL,
    preco REAL NOT NULL
)");
$pdo->exec("INSERT INTO produtos (nome, preco) VALUES ('Caderno', 15.90)");
$pdo->exec("INSERT INTO produtos (nome, preco) VALUES ('Caneta', 2.50)");

foreach ($pdo->query("SELECT id, nome FROM produtos") as $linha) {
    echo "id: ", var_export($linha["id"], true), " | nome: ", $linha["nome"], "\n";
}

Referência

Exercícios

Resumo

• MySQL e MariaDB são servidores: um programa que fica rodando, com usuários e senhas, e atende muitos acessos ao mesmo tempo. O SQLite é um arquivo.
• Você precisa de duas peças: o servidor e a extensão pdo_mysql do PHP. Confira com php -m.
• Crie um banco e um usuário só para o sistema; nunca use o root do MySQL no código.
• DSN: mysql:host=localhost;dbname=loja;charset=utf8mb4; usuário e senha vão separados no new PDO.
• Use utf8mb4 no banco, nas tabelas e na conexão.
• [2002] = não chegou ao servidor (No such file or directory: socket; Connection refused: porta; getaddrinfo: nome). could not find driver = falta a extensão.
• Trate o erro de conexão com try/catch: log para você, mensagem amigável para o visitante.
• O SQL muda nos detalhes: AUTO_INCREMENT × INTEGER PRIMARY KEY, VARCHAR/DECIMAL × TEXT/REAL, tipos rígidos × flexíveis.
• Prefira PDO a mysqli: uma forma de trabalhar para qualquer banco.

Com isso, você termina o Nível 2. Para ver tudo junto num sistema de verdade, faça o projeto Tarefas com SQLite; no Nível 3, você vai organizar o código da conexão em classes.