Treinamento Júniors - Criação de tabelas

A criação de tabelas no MySQL pode ser abordada em três níveis de complexidade: básico, intermediário e avançado. Aqui estão detalhes para cada um desses níveis:

1. Básico

No nível básico, a criação de tabelas concentra-se em definir o nome da tabela e os tipos de dados das colunas, sem se preocupar com otimizações ou funcionalidades adicionais.

CREATE TABLE exemplo_basico (

          id INT AUTO_INCREMENT,

          nome VARCHAR(255),

          idade INT,

          PRIMARY KEY (id)

          );

Neste exemplo:

id é uma coluna de inteiro que autoincrementa, servindo como chave primária.
nome e idade são colunas para armazenar texto e inteiro, respectivamente.

Tipos de dados:

Os tipos de dados i, s, d, e b são usados no MySQLi do PHP, especificamente quando você prepara e executa consultas SQL utilizando declarações preparadas (prepared statements). Eles são usados para definir os tipos dos parâmetros que são ligados (bind) às declarações. Aqui está uma explicação detalhada para cada tipo, organizada por níveis de complexidade:

No nível básico, você aprenderá a usar os tipos de dados em consultas simples.

i - representa um tipo de dado inteiro.
s - representa um tipo de dado string.
d - representa um tipo de dado double (números com ponto flutuante).
b - representa um tipo de dado blob, que é usado para dados binários.

Exemplo:


            $stmt = $mysqli->prepare("INSERT INTO usuarios (nome, idade, salario, foto) VALUES (?, ?, ?, ?)");

            $stmt->bind_param("sids", $nome, $idade, $salario, $foto);

            $nome = "João";

            $idade = 30;

            $salario = 2000.50;

            $foto = file_get_contents("foto.jpg"); // Exemplo de BLOB

            $stmt->execute();

Neste exemplo:

nome é uma string (s).
idade é um inteiro (i).
salario é um double (d).
foto é um blob (b).

2. Intermediário

No nível intermediário, você adiciona mais características à tabela, como chaves estrangeiras, índices para melhorar a performance de consulta e restrições de integridade.


          CREATE TABLE exemplo_intermediario (

          id INT AUTO_INCREMENT,

          nome VARCHAR(255) NOT NULL,

          idade INT,

          cargo_id INT,

          PRIMARY KEY (id),

          FOREIGN KEY (cargo_id) REFERENCES cargos(id),

          INDEX idx_nome (nome)

          );

Neste exemplo:

NOT NULL garante que a coluna nome não aceite valores nulos.
FOREIGN KEY relaciona cargo_id com a coluna id da tabela cargos.
INDEX em nome melhora a velocidade de buscas baseadas nesta coluna.

Sobre tipo de dados, aqui você integra esses tipos em consultas mais complexas, como atualizações condicionais e seleções com múltiplos parâmetros.


            $stmt = $mysqli->prepare("UPDATE usuarios SET salario = ? WHERE idade > ? AND ativo = ?");

            $stmt->bind_param("dis", $novo_salario, $idade_minima, $ativo);

            $novo_salario = 2500.75;

            $idade_minima = 25;

            $ativo = '1'; // Assume que 'ativo' é armazenado como um char '0' ou '1'

            $stmt->execute();

Neste exemplo:

novo_salario é um double (d).
idade_minima é um inteiro (i).
ativo é uma string (s), mesmo sendo um caractere, porque é assim que o MySQLi lida com caracteres.

3. Avançado

No nível avançado, incluem-se características como partições, triggers, e uso de tipos de dados avançados como JSON. Adicionalmente, pode-se especificar opções de storage engine e outras otimizações específicas do MySQL.


          CREATE TABLE exemplo_avancado (

          id INT AUTO_INCREMENT,

          detalhes JSON,

          data_cadastro DATETIME DEFAULT CURRENT_TIMESTAMP,

          PRIMARY KEY (id)

          ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4;

Neste exemplo:

detalhes é uma coluna que armazena dados no formato JSON.
data_cadastro tem um valor padrão que é a data e hora do momento da inserção.
ENGINE=InnoDB especifica o motor de armazenamento, com suporte a transações, rollback e recuperação após falhas.
DEFAULT CHARSET=utf8mb4 define o conjunto de caracteres padrão para suportar Unicode completo, incluindo emojis.

Sobre tipo de dados, aqui você pode se concentrar em técnicas mais sofisticadas como a utilização de blobs em operações complexas e otimização de consultas para melhorar o desempenho.


            $stmt = $mysqli->prepare("SELECT foto FROM usuarios WHERE id = ?");

            $stmt->bind_param("i", $id_usuario);

            $id_usuario = 1;

            $stmt->execute();

            $stmt->bind_result($foto);

            $stmt->fetch();

            header("Content-Type: image/jpeg");

            echo $foto;

Neste exemplo:

A busca recupera um blob (b), que é uma foto armazenada no banco de dados.
Utiliza-se o método bind_result para associar a variável $foto diretamente ao resultado da consulta, que é tratada como um blob.

Os Tipos de dados ilustram o uso prático dos tipos i, s, d, e b em diferentes níveis de complexidade de programação com MySQLi, ajudando a lidar com dados em várias formas em um banco de dados MySQL.

O que é BLOB

O tipo de dado BLOB (Binary Large Object) no MySQL é usado para armazenar dados binários, como imagens, arquivos de áudio ou qualquer outro tipo de dados que não sejam simplesmente texto ou números. Aqui está um exemplo prático de como inserir e recuperar uma imagem (BLOB) em um banco de dados MySQL usando PHP e MySQLi.

Estrutura da Tabela
Primeiro, você precisa criar uma tabela com uma coluna BLOB para armazenar os dados binários:


            CREATE TABLE imagens (

                id INT AUTO_INCREMENT PRIMARY KEY,

                nome_arquivo VARCHAR(255),

                tipo_arquivo VARCHAR(50),

                dados BLOB

            );

Inserindo Dados (Imagem) no Banco

Agora, vamos supor que você queira inserir uma imagem no banco de dados. Aqui está o código PHP usando MySQLi:


              $mysqli = new mysqli("localhost", "usuario", "senha", "banco_de_dados");


              // Verificar a conexão

              if ($mysqli->connect_error) {

                  die("Falha na conexão: " . $mysqli->connect_error);

              }



              // Caminho para o arquivo de imagem

              $caminho_imagem = "caminho/para/imagem.jpg";

              $nome_arquivo = basename($caminho_imagem);

              $tipo_arquivo = mime_content_type($caminho_imagem);

              $dados_binarios = file_get_contents($caminho_imagem);



              // Preparar a consulta

              $stmt = $mysqli->prepare("INSERT INTO imagens (nome_arquivo, tipo_arquivo, dados) VALUES (?, ?, ?)");

              $stmt->bind_param("ssb", $nome_arquivo, $tipo_arquivo, $dados_binarios);



              // Enviar dados binários como parâmetro

              $stmt->send_long_data(2, $dados_binarios);



              // Executar a consulta

              if ($stmt->execute()) {

                  echo "Imagem inserida com sucesso!";

              } else {

                  echo "Erro ao inserir imagem: " . $stmt->error;

              }



              $stmt->close();

              $mysqli->close();

Recuperando e Mostrando a Imagem

Para recuperar e exibir a imagem armazenada no banco de dados:


            $mysqli = new mysqli("localhost", "usuario", "senha", "banco_de_dados");


            // Verificar a conexão

            if ($mysqli->connect_error) {

                die("Falha na conexão: " . $mysqli->connect_error);

            }


            
            // Preparar a consulta

            $stmt = $mysqli->prepare("SELECT tipo_arquivo, dados FROM imagens WHERE id = ?");

            $stmt->bind_param("i", $id_imagem);

            $id_imagem = 1; // Supondo que você quer recuperar a imagem com ID 1


            
            // Executar a consulta

            $stmt->execute();


            
            // Vincular o resultado

            $stmt->bind_result($tipo_arquivo, $dados_binarios);

            $stmt->fetch();


            
            // Definir o cabeçalho para o tipo de conteúdo correto

            header("Content-Type: " . $tipo_arquivo);

            echo $dados_binarios;


            
            $stmt->close();

            $mysqli->close();

Vantagens:

Consistência: Os dados da imagem estão diretamente associados ao banco de dados, garantindo que qualquer operação de backup ou transferência do banco de dados incluirá as imagens.
Controle de Acesso: É mais fácil controlar quem acessa as imagens, pois o controle de acesso pode ser gerenciado pelo banco de dados. A imagem só é acessível através da aplicação.

Desvantagens:

Uso de Recursos: Armazenar imagens grandes no banco de dados pode consumir uma quantidade significativa de recursos, tanto em termos de armazenamento quanto de processamento.
Performance: Pode haver uma diminuição na performance ao recuperar imagens do banco de dados, especialmente se o banco de dados for grande e não estiver bem otimizado.
Complexidade: Lidar com BLOBs pode adicionar complexidade ao código da aplicação, especialmente quando se trata de manipulação e exibição de dados binários.

Conclusão

A escolha de armazenar o caminho da imagem usando BLOB (tipo b) depende das necessidades específicas da aplicação e das restrições de infraestrutura. Em geral, armazenar caminhos de imagens é adequado para a maioria das aplicações web devido à eficiência e simplicidade, enquanto o armazenamento de imagens como BLOBs pode ser preferido em sistemas que requerem alta integridade e segurança dos dados de imagem.
É importante garantir que o tratamento de arquivos grandes com BLOB seja feito com cuidado para não sobrecarregar o servidor ou a aplicação.

Esses exemplos cobrem desde a criação básica até configurações mais complexas, proporcionando uma visão ampla das possibilidades ao criar tabelas no MySQL.