Banco de exemplo para testes

Os comandos de banco de dados ficam mais claros quando são executados e o resultado pode ser comparado com a explicação. Um banco de exemplo, criado em um servidor de testes, permite repetir as consultas, conferir os planos e provocar erros sem nenhum risco para os dados de uma loja. O script a seguir cria o banco loja, usado nos exemplos, com as tabelas, os índices, os dados e algumas situações preparadas para as consultas de diagnóstico.

Onde executar

O script é executado em um servidor MySQL de testes, instalado em um computador pessoal ou em uma máquina virtual, com uma conta que tenha permissão para criar bancos. O servidor de uma loja ou da matriz não serve para testes, porque um comando de teste pode alterar dados reais ou pesar sobre o atendimento.

Os comandos usam recursos disponíveis tanto nas versões antigas, como a 5.1, quanto nas atuais. O banco nasce com o conjunto de caracteres padrão do servidor, e por isso os exemplos que dependem do utf8mb4 só funcionam nas versões que têm esse conjunto.

As tabelas nascem no engine padrão do servidor, e o mesmo script funciona com qualquer um deles. Nas versões atuais, o padrão é o InnoDB. Nas versões antigas, o padrão pode ser o MyISAM, e em alguns servidores o InnoDB nem está disponível, porque foi desligado na configuração, com a opção skip-innodb. O comando SHOW ENGINES; mostra os engines do servidor, com a palavra DEFAULT no padrão e a palavra NO nos indisponíveis.

Uma ferramenta gráfica de consulta é a forma mais simples de execução. Cada item da lista é um comando completo, terminado em ponto e vírgula, e os comandos são executados na ordem em que aparecem. O primeiro comando informa ao servidor que os textos chegam em UTF-8, e uma ferramenta configurada com outra codificação grava os acentos de forma errada. O cliente de linha de comando do Windows, por exemplo, recebe o texto na codificação da janela do Prompt de Comando, e nele os comandos com acentos podem falhar ou gravar caracteres trocados.

O segundo comando apaga o banco loja, se ele já existir, com todas as tabelas, e por isso cada execução recomeça do zero. Esse comando é mais um motivo para o script rodar apenas em um servidor de testes, porque, em um servidor com um banco real chamado loja, ele apagaria esse banco sem nenhum aviso.

O que o script faz

• define a codificação da conexão, apaga o banco de uma execução anterior, cria o banco de novo e o coloca em uso;
• cria as tabelas de produtos, de clientes, de vendas, de itens das vendas e de controle das ordens, com as chaves primárias e os índices;
• cria uma tabela auxiliar com os algarismos de 0 a 9, usada para gerar as linhas em quantidade;
• grava os produtos e os clientes, com algumas linhas escritas uma a uma e as demais geradas;
• grava as vendas e os itens e calcula o total de cada venda a partir dos itens;
• prepara algumas situações de erro e registra as ordens na tabela de controle;
• remove a tabela auxiliar e atualiza as estatísticas das tabelas.

O script

1. SET NAMES utf8;
2. DROP DATABASE IF EXISTS loja;
3. CREATE DATABASE loja;
4. USE loja;
5. CREATE TABLE produtos (id INT NOT NULL AUTO_INCREMENT, codigo VARCHAR(20) NOT NULL, descricao VARCHAR(100) NOT NULL, descricao_busca VARCHAR(100) NOT NULL, preco DECIMAL(10,2) NOT NULL, estoque INT NOT NULL, ativo TINYINT NOT NULL, excluido CHAR(1) NULL, PRIMARY KEY (id), KEY produtos_cod (codigo, id), KEY produtos_desc (descricao_busca, id));
6. CREATE TABLE clientes (id INT NOT NULL AUTO_INCREMENT, nome VARCHAR(100) NOT NULL, email VARCHAR(100) NULL, excluido CHAR(1) NULL, PRIMARY KEY (id));
7. CREATE TABLE vendas (id INT NOT NULL AUTO_INCREMENT, numero_documento INT NOT NULL, data_venda DATETIME NOT NULL, cliente_id INT NULL, caixa VARCHAR(10) NOT NULL, total DECIMAL(10,2) NOT NULL, desconto DECIMAL(10,2) NULL, situacao CHAR(1) NULL, PRIMARY KEY (id), KEY idx_vendas_documento (numero_documento), KEY idx_vendas_data (data_venda));
8. CREATE TABLE itens_venda (id INT NOT NULL AUTO_INCREMENT, venda_id INT NOT NULL, produto_id INT NOT NULL, quantidade INT NOT NULL, preco_unitario DECIMAL(10,2) NOT NULL, PRIMARY KEY (id), KEY idx_itens_venda (venda_id));
9. CREATE TABLE controle_ordens (tabela VARCHAR(64) NOT NULL, ordem VARCHAR(30) NOT NULL, expressao VARCHAR(200) NOT NULL, condicao VARCHAR(200) NULL, indice VARCHAR(64) NOT NULL, PRIMARY KEY (tabela, ordem));
10. CREATE TABLE digitos (d INT NOT NULL);
11. INSERT INTO digitos (d) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
12. INSERT INTO produtos (id, codigo, descricao, descricao_busca, preco, estoque, ativo, excluido) VALUES (12, '7891234567890', 'DIPIRONA 500MG', 'DIPIRONA 500MG', 12.50, 0, 1, 'S'), (315, '7891234567890', 'DIPIRONA SÓDICA 500MG', 'DIPIRONA 500MG', 12.90, 40, 1, 'N'), (316, '00123', 'PARACETAMOL 750MG', 'PARACETAMOL 750MG', 8.00, 25, 1, 'N'), (317, '123', 'ÁGUA MINERAL 500ML', 'AGUA MINERAL 500ML', 2.50, 120, 1, 'N'), (318, '7891234567891 ', 'SORO FISIOLÓGICO 500ML', 'SORO FISIOLOGICO 500ML', 6.40, 18, 1, 'N');
13. INSERT INTO produtos (id, codigo, descricao, descricao_busca, preco, estoque, ativo, excluido) SELECT n, CONCAT('789', LPAD(n, 10, '0')), CONCAT('PRODUTO ', LPAD(n, 4, '0')), CONCAT('PRODUTO ', LPAD(n, 4, '0')), 1 + MOD(n * 37, 500) / 10, MOD(n * 13, 200), IF(MOD(n, 25) = 0, 0, 1), IF(n <= 100, NULL, IF(MOD(n, 40) = 0, 'S', 'N')) FROM (SELECT a.d + b.d * 10 + c.d * 100 + 1 AS n FROM digitos a, digitos b, digitos c) x WHERE n NOT IN (12, 315, 316, 317, 318);
14. INSERT INTO clientes (id, nome, email, excluido) VALUES (10, 'Jose da Silva', 'jose.silva@exemplo', 'N'), (11, 'José', 'jose@example.com', 'N'), (12, 'JOSÉ', NULL, 'N'), (13, 'jose', '', 'N'), (14, 'Maria Souza', NULL, 'S'), (15, 'Ana Lima', 'ana.lima@example.com', NULL);
15. INSERT INTO clientes (id, nome, email, excluido) SELECT n, CONCAT('CLIENTE ', LPAD(n, 3, '0')), IF(MOD(n, 10) = 0, NULL, IF(MOD(n, 15) = 0, '', CONCAT('cliente', LPAD(n, 3, '0'), '@example.com'))), IF(MOD(n, 50) = 0, 'S', IF(MOD(n, 30) = 0, NULL, 'N')) FROM (SELECT a.d + b.d * 10 + c.d * 100 + 1 AS n FROM digitos a, digitos b, digitos c) x WHERE n <= 200 AND n NOT IN (10, 11, 12, 13, 14, 15);
16. INSERT INTO vendas (id, numero_documento, data_venda, cliente_id, caixa, total, desconto, situacao) SELECT 1001 + n, 4000 + n DIV 2, '2026-06-30 08:00:00' + INTERVAL (n DIV 81) DAY + INTERVAL (MOD(n, 81) * 9) MINUTE, IF(MOD(n, 5) IN (1, 3), NULL, 1 + MOD(n * 7, 179)), IF(MOD(n, 2) = 0, 'CX01', 'CX02'), 0, IF(MOD(n, 10) < 3, 1 + MOD(n, 5), NULL), IF(n < 30, NULL, IF(MOD(n, 37) = 0, 'C', 'F')) FROM (SELECT a.d + b.d * 10 + c.d * 100 + e.d * 1000 AS n FROM digitos a, digitos b, digitos c, digitos e) x WHERE n NOT IN (50, 51);
17. INSERT INTO itens_venda (venda_id, produto_id, quantidade, preco_unitario) SELECT v.id, p.id, 1 + MOD(v.id + d.d, 3), p.preco FROM vendas v JOIN digitos d ON d.d <= MOD(v.id, 3) JOIN produtos p ON p.id = 1 + MOD(v.id * 7 + d.d * 131, 1000) ORDER BY v.id, d.d;
18. INSERT INTO itens_venda (venda_id, produto_id, quantidade, preco_unitario) VALUES (99999, 315, 1, 12.90), (1200, 5000, 1, 3.20);
19. UPDATE vendas v JOIN (SELECT venda_id, SUM(quantidade * preco_unitario) AS soma FROM itens_venda GROUP BY venda_id) s ON s.venda_id = v.id SET v.total = s.soma;
20. UPDATE vendas SET total = total + 10 WHERE id = 1100;
21. INSERT INTO controle_ordens (tabela, ordem, expressao, condicao, indice) VALUES ('produtos', 'PRODUTOS_COD', 'codigo', NULL, 'produtos_cod'), ('produtos', 'PRODUTOS_DESC', 'descricao_busca', NULL, 'produtos_desc'), ('produtos', 'PRODUTOS_ATIVOS', 'descricao_busca', 'ativo = 1', 'produtos_ativos'), ('vendas', 'VENDAS_DOC', 'numero_documento', NULL, 'idx_vendas_documento'), ('vendas', 'VENDAS_DATA', 'data_venda', NULL, 'idx_vendas_data');
22. DROP TABLE digitos;
23. ANALYZE TABLE produtos, clientes, vendas, itens_venda, controle_ordens;

O que o banco traz

• Produtos: 1.000 produtos, com o código, a descrição, a descrição de busca, o preço, o estoque, a indicação de produto ativo e a marca de exclusão.
• Clientes: 200 clientes, com o nome, o e-mail e a marca de exclusão.
• Vendas: 9.998 vendas dos caixas CX01 e CX02, de 30 de junho a 31 de outubro de 2026, com o total calculado pela soma dos itens.
• Itens das vendas: 19.998 itens, de um a três por venda, com a quantidade e o preço unitário.
• Tabela de controle: as ordens das tabelas de produtos e de vendas, com o nome do índice correspondente a cada uma.

O engine das tabelas é o padrão do servidor, e por isso o mesmo script cria tabelas InnoDB em um servidor atual e tabelas MyISAM em um servidor antigo que tenha esse padrão. As contas de usuário dos exemplos não fazem parte do script, e a criação delas entra nos testes de permissões.

Como as quantidades são formadas

Os 23 comandos formam o script inteiro, mas a maior parte das linhas não está escrita neles. Os comandos 13, 15, 16 e 17 geram essas linhas durante a execução, e cada um grava centenas ou milhares de linhas de uma vez. A tabela auxiliar guarda os algarismos de 0 a 9, e a combinação de três cópias dessa tabela forma os números de 1 a 1.000, um para cada combinação de unidade, dezena e centena. Com quatro cópias, a combinação forma os números de 0 a 9.999. Cada número gera uma linha, e as quantidades finais saem destas contas:

• Produtos: os números de 1 a 1.000 geram 995 produtos, porque os cinco identificadores dos produtos escritos à mão ficam de fora, e esses cinco completam os 1.000.
• Clientes: os números de 1 a 200 geram 194 clientes, sem os seis identificadores dos clientes escritos à mão, e esses seis completam os 200.
• Vendas: os números de 0 a 9.999 geram 9.998 vendas, porque dois números ficam de fora para formar o intervalo entre 1050 e 1053.
• Itens: cada venda recebe de um a três itens, conforme o resto da divisão do identificador da venda por 3, o que soma 19.996 itens, e os dois itens preparados à parte completam os 19.998.

As situações preparadas

• o documento 4521 em duas vendas, uma em cada caixa, e uma delas sem cliente identificado;
• um intervalo nos identificadores das vendas, entre 1050 e 1053;
• as vendas mais antigas com a situação em NULL;
• clientes sem e-mail, com o valor NULL ou com um texto vazio, e clientes sem nenhuma venda;
• os nomes José, JOSÉ e jose, para os testes de comparação de textos;
• o cliente 10, com o nome sem acento e o e-mail incompleto, para os testes de correção;
• o código 7891234567890 em dois produtos, um deles marcado como excluído;
• os códigos 00123 e 123, para a comparação entre texto e número;
• um código com um espaço no fim;
• os primeiros produtos com a marca de exclusão em NULL;
• o produto 315 com a descrição de busca desatualizada;
• a venda 1100 com o total diferente da soma dos itens;
• um item ligado a uma venda que não existe e outro ligado a um produto que não existe;
• a ordem PRODUTOS_ATIVOS registrada na tabela de controle, sem o índice correspondente no banco.

Conferir a criação

• SELECT (SELECT COUNT(*) FROM produtos) AS produtos, (SELECT COUNT(*) FROM clientes) AS clientes, (SELECT COUNT(*) FROM vendas) AS vendas, (SELECT COUNT(*) FROM itens_venda) AS itens;: conta as linhas de cada tabela;
• SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'loja';: mostra o engine de cada tabela.

O resultado esperado é 1000 produtos, 200 clientes, 9998 vendas e 19998 itens, com todas as tabelas no engine padrão do servidor. Uma contagem diferente indica que algum comando falhou no meio do caminho, e a mensagem de erro da ferramenta mostra qual foi.

Trocar o engine das tabelas

Os exemplos de verificação e de reparo do MyISAM usam a tabela de vendas. Em um servidor que criou as tabelas no InnoDB, a conversão dessa tabela basta para praticá-los, e o banco fica com uma tabela MyISAM ao lado das tabelas InnoDB, um arranjo comum em instalações antigas. Em um servidor que criou as tabelas no MyISAM, os exemplos de reparo funcionam sem conversão, e os exemplos de transações e de bloqueio por linha dependem da conversão para o InnoDB, possível apenas quando esse engine está disponível:

• ALTER TABLE vendas ENGINE = MyISAM;: converte a tabela de vendas para o MyISAM;
• ALTER TABLE vendas ENGINE = InnoDB;: converte a tabela de vendas para o InnoDB.

Em um servidor sem o InnoDB, a segunda conversão não acontece. Com a opção NO_ENGINE_SUBSTITUTION no modo do servidor, o comando falha com o erro 1286, de engine desconhecido, e, sem essa opção, termina com um aviso e deixa a tabela no engine em que estava. Em um servidor de testes, o InnoDB desligado pela opção skip-innodb volta a funcionar com a retirada dessa opção do arquivo de configuração e o reinício do serviço.

Sem a conversão, os exemplos de transações e de bloqueios mostram o comportamento do MyISAM. O ROLLBACK não desfaz as alterações e termina com o aviso 1196, os bloqueios atingem a tabela inteira, e um comando interrompido no meio deixa parte das linhas alteradas. Essas diferenças também fazem parte do estudo, porque muitas instalações antigas usam apenas o MyISAM.

Recomeçar do zero

Os testes alteram os dados, e alguns deixam o banco diferente do estado inicial. Para recomeçar, basta executar o script de novo, porque o segundo comando apaga o banco anterior. Para apagar o banco de exemplo sem criá-lo outra vez, o comando é este:

• DROP DATABASE loja;: apaga o banco de exemplo inteiro, com todas as tabelas, e só pode ser executado no servidor de testes.