MySQL: Projeto Integrado
Última atualização: 2026-08-26
A ShopEasy, plataforma de comércio eletrônico fundada por Alice, acaba de garantir um financiamento e precisa migrar de um sistema de gestão baseado no Excel para o MySQL. Ela projetou do zero um banco de dados completo para comércio eletrônico, com oito módulos que abrangem todo o processo, desde o cadastro do usuário até a análise de dados. Desde que entrou em operação, a plataforma vem processando mais de 5.000 pedidos por dia, e o banco de dados tem se mantido extremamente estável.
1. Visão geral do projeto
- Abrange 8 módulos principais: Usuários, Produtos, Pedidos, Carrinho de compras, Pagamentos, Estatísticas, Permissões e Backups
- Cada módulo inclui instruções SQL completas para a criação de tabelas e lógica de negócios
- Os procedimentos armazenados encapsulam a lógica de realização de pedidos, e as transações garantem a consistência dos dados.
- As visualizações fornecem estatísticas de dados e controle de acesso para fins de segurança
- Os scripts de backup garantem a recuperabilidade dos dados
(1) Lista de módulos e tabelas
| Módulo | Tabela principal | Funções principais |
|---|---|---|
| Sistema de Usuários | usuários / endereços / registros_de_usuários | Cadastro e Login, Gerenciamento de Endereços, Auditoria de Operações |
| Sistema de Produtos | categorias / produtos / imagens_dos_produtos | Categorização com níveis ilimitados, operações CRUD de produtos, várias imagens |
| Sistema de Pedidos | pedidos / itens_de_pedido | Realização de pedidos, dedução de estoque, atualizações de status |
| Carrinho de compras | carrinho | Adicionar, excluir, editar, visualizar, mesclar carrinhos de compras |
| Histórico de pagamentos | pagamentos | Status dos pagamentos, reembolsos |
| Estatísticas | Visualizações | Produtos mais vendidos, Rankings de vendas, Relatórios mensais |
| Gerenciamento de permissões | Usuários do MySQL | Somente leitura/Aplicativo/Administrador |
| Estratégia de backup | mysqldump | Política de backup diário e retenção |
(2) Diagrama ER completo
erDiagram
USERS ||--o{ ADDRESSES : has
USERS ||--o{ ORDERS : places
USERS ||--o{ CART : adds
USERS ||--o{ USER_LOGS : generates
CATEGORIES ||--o{ CATEGORIES : parent
CATEGORIES ||--o{ PRODUCTS : contains
PRODUCTS ||--o{ PRODUCT_IMAGES : has
PRODUCTS ||--o{ ORDER_ITEMS : included_in
PRODUCTS ||--o{ CART : added_to
ORDERS ||--|{ ORDER_ITEMS : contains
ORDERS ||--o| PAYMENTS : paid_by
USERS {
bigint id PK
varchar username
varchar email
varchar password_hash
varchar phone
enum status
}
ADDRESSES {
bigint id PK
bigint user_id FK
varchar receiver
varchar phone
varchar province
varchar city
varchar detail
tinyint is_default
}
USER_LOGS {
bigint id PK
bigint user_id FK
varchar action
varchar ip_address
datetime created_at
}
CATEGORIES {
bigint id PK
varchar name
bigint parent_id FK
int sort_order
}
PRODUCTS {
bigint id PK
varchar name
decimal price
int stock
bigint category_id FK
enum status
}
PRODUCT_IMAGES {
bigint id PK
bigint product_id FK
varchar image_url
tinyint is_main
int sort_order
}
ORDERS {
bigint id PK
varchar order_no
bigint user_id FK
decimal total_amount
enum status
}
ORDER_ITEMS {
bigint id PK
bigint order_id FK
bigint product_id FK
int quantity
decimal price
}
CART {
bigint id PK
bigint user_id FK
bigint product_id FK
int quantity
}
PAYMENTS {
bigint id PK
bigint order_id FK
varchar payment_no
decimal amount
enum method
enum status
}
2. Análise de Requisitos e Projeto de Modelo Entidade-Relação
(1) Análise de Requisitos
Fluxo de trabalho principal de um sistema de comércio eletrônico: Cadastro do usuário → Navegação pelos produtos → Adicionar ao carrinho → Finalizar a compra → Pagamento → Envio → Conclusão. Cada etapa requer tabelas e restrições correspondentes.
(2) Descrição da relação
| Relação | Tipo | Descrição |
|---|---|---|
| Usuário → Endereço | Um para muitos | Um usuário pode ter vários endereços de entrega |
| Usuário → Pedido | Um para muitos | Um único usuário pode fazer vários pedidos |
| Pedido → Item do pedido | Um para muitos | Um pedido contém vários itens |
| Produto → Item da linha do pedido | Um para muitos | Um único produto pode aparecer em vários pedidos |
| Categoria → Categoria | Autorreferência | parent_id para implementar uma categorização de níveis infinitos |
| Categoria → Produto | Um para muitos | Vários produtos em uma única categoria |
| Produto → Imagem | Um para muitos | Várias imagens por produto |
| Pedido → Pagamento | Um a um | Um registro de pagamento por pedido |
▶ Exemplo: Criação de um banco de dados
SQL
CREATE DATABASE IF NOT EXISTS ecommerce
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
USE ecommerce;
3. Sistema de usuários
(1) Projeto da estrutura da tabela
SQL
CREATE TABLE users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
phone VARCHAR(20),
avatar VARCHAR(255) DEFAULT '/images/default_avatar.png',
status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_users_phone (phone),
INDEX idx_users_status (status)
) ENGINE=InnoDB COMMENT='User Table';
CREATE TABLE addresses (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
receiver VARCHAR(50) NOT NULL,
phone VARCHAR(20) NOT NULL,
province VARCHAR(30) NOT NULL,
city VARCHAR(30) NOT NULL,
district VARCHAR(30) NOT NULL,
detail VARCHAR(200) NOT NULL,
is_default TINYINT(1) DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_addresses_user (user_id),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='List of Shipping Addresses';
CREATE TABLE user_logs (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT,
action VARCHAR(50) NOT NULL,
ip_address VARCHAR(45),
user_agent VARCHAR(255),
detail JSON,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_logs_user (user_id),
INDEX idx_logs_action (action),
INDEX idx_logs_created (created_at)
) ENGINE=InnoDB COMMENT='User Activity Log Table';
(2) Cadastro e verificação de login
▶ Exemplo: Cadastro de usuário
SQL
INSERT INTO users (username, email, password_hash, phone)
VALUES (
'alice',
'alice@example.com',
SHA2('MySecret123!', 256),
'13800138000'
);
INSERT INTO users (username, email, password_hash, phone)
VALUES (
'bob_dev',
'bob@example.com',
SHA2('BobPass456!', 256),
'13900139000'
);
▶ Exemplo: Autenticação de login
SQL
SELECT id, username, status FROM users
WHERE email = 'alice@example.com'
AND password_hash = SHA2('MySecret123!', 256)
AND status = 'active';
▶ Exemplo: Gerenciamento de endereços
SQL
INSERT INTO addresses (user_id, receiver, phone, province, city, district, detail, is_default)
VALUES (1, 'Alice', '13800138000', 'Guangdong', 'Shenzhen', 'Nanshan', 'Tech Park Bldg 5 Room 301', 1);
INSERT INTO addresses (user_id, receiver, phone, province, city, district, detail, is_default)
VALUES (1, 'Alice', '13800138000', 'Beijing', 'Beijing', 'Haidian', 'Zhongguancun St 88', 0);
SELECT * FROM addresses WHERE user_id = 1 ORDER BY is_default DESC;
▶ Exemplo: Registro de atividades do usuário
SQL
INSERT INTO user_logs (user_id, action, ip_address, user_agent, detail)
VALUES (1, 'LOGIN', '192.168.1.100', 'Chrome/120', '{"method": "email"}');
INSERT INTO user_logs (user_id, action, ip_address, detail)
VALUES (1, 'UPDATE_ADDRESS', '192.168.1.100', '{"address_id": 1}');
SELECT action, COUNT(*) AS cnt
FROM user_logs
WHERE user_id = 1
GROUP BY action
ORDER BY cnt DESC;
4. Sistema de Produtos
(1) Classificação em níveis infinitos
SQL
CREATE TABLE categories (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
parent_id BIGINT DEFAULT 0,
sort_order INT DEFAULT 0,
icon VARCHAR(255),
is_visible TINYINT(1) DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_categories_parent (parent_id),
INDEX idx_categories_sort (sort_order)
) ENGINE=InnoDB COMMENT='Product Category Table';
▶ Exemplo: Dados categóricos
SQL
INSERT INTO categories (id, name, parent_id, sort_order) VALUES
(1, 'Electronics', 0, 1),
(2, 'Clothing', 0, 2),
(3, 'Home & Living', 0, 3),
(4, 'Smartphones', 1, 1),
(5, 'Laptops', 1, 2),
(6, 'Men', 2, 1),
(7, 'Women', 2, 2),
(8, 'Kitchen', 3, 1);
SELECT c1.name AS category, c2.name AS sub_category
FROM categories c1
LEFT JOIN categories c2 ON c1.id = c2.parent_id
WHERE c1.parent_id = 0
ORDER BY c1.sort_order, c2.sort_order;
(2) Produtos e imagens
SQL
CREATE TABLE products (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(200) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL CHECK (price > 0),
original_price DECIMAL(10,2),
stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
sales INT NOT NULL DEFAULT 0,
category_id BIGINT NOT NULL,
status ENUM('active', 'inactive', 'sold_out') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_products_category (category_id),
INDEX idx_products_status (status),
INDEX idx_products_sales (sales DESC),
INDEX idx_products_created (created_at DESC),
FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB COMMENT='Product List';
CREATE TABLE product_images (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
product_id BIGINT NOT NULL,
image_url VARCHAR(255) NOT NULL,
is_main TINYINT(1) DEFAULT 0,
sort_order INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_images_product (product_id),
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='Product Image Table';
▶ Exemplo: CRUD de produto
SQL
INSERT INTO products (name, description, price, original_price, stock, category_id, status)
VALUES
('Galaxy S24 Ultra', 'Samsung flagship 2024', 1299.99, 1399.99, 200, 4, 'active'),
('MacBook Pro 16', 'Apple M3 Max laptop', 2499.00, 2499.00, 50, 5, 'active'),
('Cotton T-Shirt', 'Pure cotton casual tee', 29.99, 39.99, 500, 6, 'active'),
('Smart Rice Cooker', 'IoT enabled cooker', 89.99, 119.99, 300, 8, 'active');
INSERT INTO product_images (product_id, image_url, is_main, sort_order) VALUES
(1, '/images/galaxy_s24_1.jpg', 1, 1),
(1, '/images/galaxy_s24_2.jpg', 0, 2),
(2, '/images/macbook_pro_1.jpg', 1, 1),
(3, '/images/tshirt_white.jpg', 1, 1),
(4, '/images/cooker_main.jpg', 1, 1);
SELECT p.id, p.name, p.price, pi.image_url
FROM products p
LEFT JOIN product_images pi ON p.id = pi.product_id AND pi.is_main = 1
WHERE p.category_id = 4 AND p.status = 'active'
ORDER BY p.sales DESC
LIMIT 10;
UPDATE products SET stock = stock - 1, sales = sales + 1 WHERE id = 1;
UPDATE products SET status = 'sold_out' WHERE stock <= 0;
5. Carrinho de compras
(1) Estrutura da tabela
SQL
CREATE TABLE cart (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL DEFAULT 1 CHECK (quantity > 0),
selected TINYINT(1) DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_cart_user_product (user_id, product_id),
INDEX idx_cart_user (user_id),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='Shopping Cart Table';
▶ Exemplo: Operações CRUD para um carrinho de compras
SQL
INSERT INTO cart (user_id, product_id, quantity) VALUES (1, 1, 2);
INSERT INTO cart (user_id, product_id, quantity) VALUES (1, 2, 1)
ON DUPLICATE KEY UPDATE quantity = quantity + 1;
UPDATE cart SET quantity = 3 WHERE user_id = 1 AND product_id = 1;
DELETE FROM cart WHERE user_id = 1 AND product_id = 2;
SELECT c.product_id, p.name, p.price, c.quantity,
p.price * c.quantity AS subtotal
FROM cart c
JOIN products p ON c.product_id = p.id
WHERE c.user_id = 1 AND c.selected = 1;
▶ Exemplo: Unificação de carrinhos de compras
SQL
DELIMITER //
CREATE PROCEDURE sp_merge_cart(
IN p_source_user_id BIGINT,
IN p_target_user_id BIGINT
)
BEGIN
INSERT INTO cart (user_id, product_id, quantity, selected)
SELECT p_target_user_id, product_id, quantity, selected
FROM cart
WHERE user_id = p_source_user_id
ON DUPLICATE KEY UPDATE
quantity = cart.quantity + VALUES(quantity),
selected = 1;
DELETE FROM cart WHERE user_id = p_source_user_id;
END //
DELIMITER ;
CALL sp_merge_cart(2, 1);
6. Sistema de pedidos
(1) Pedidos e itens dos pedidos
SQL
CREATE TABLE orders (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
order_no VARCHAR(32) NOT NULL UNIQUE,
user_id BIGINT NOT NULL,
total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
pay_amount DECIMAL(12,2) DEFAULT NULL,
status ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled', 'refunded')
DEFAULT 'pending',
receiver VARCHAR(50) NOT NULL,
receiver_phone VARCHAR(20) NOT NULL,
shipping_address TEXT NOT NULL,
remark VARCHAR(500),
paid_at DATETIME DEFAULT NULL,
shipped_at DATETIME DEFAULT NULL,
completed_at DATETIME DEFAULT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_orders_user (user_id),
INDEX idx_orders_status (status),
INDEX idx_orders_created (created_at DESC),
INDEX idx_orders_order_no (order_no),
FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB COMMENT='Orders Table';
CREATE TABLE order_items (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
product_name VARCHAR(200) NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
price DECIMAL(10,2) NOT NULL,
subtotal DECIMAL(12,2) GENERATED ALWAYS AS (quantity * price) STORED,
INDEX idx_items_order (order_id),
INDEX idx_items_product (product_id),
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB COMMENT='Order Details';
(2) Alterações no status do pedido
| Estado atual | Estados possíveis | Condições de acionamento |
|---|---|---|
| pendente | pago | pagamento realizado com sucesso |
| pendente | cancelado | cancelado pelo usuário/não pago devido ao tempo limite |
| pago | enviado | enviado pelo vendedor |
| pago | reembolsado | solicitar reembolso |
| enviado | concluído | comprador confirmou o recebimento |
| concluído | reembolsado | Reembolso pós-venda |
▶ Exemplo: Procedimento armazenado para criação de pedidos (incluindo dedução de estoque + transação)
SQL
📖 Somente leitura
DELIMITER //
CREATE PROCEDURE sp_create_order(
IN p_user_id BIGINT,
IN p_receiver VARCHAR(50),
IN p_phone VARCHAR(20),
IN p_address TEXT,
IN p_remark VARCHAR(500)
)
BEGIN
DECLARE v_order_no VARCHAR(32);
DECLARE v_total DECIMAL(12,2) DEFAULT 0;
DECLARE v_cart_item INT;
DECLARE v_product_id BIGINT;
DECLARE v_quantity INT;
DECLARE v_price DECIMAL(10,2);
DECLARE v_stock INT;
DECLARE v_name VARCHAR(200);
DECLARE done INT DEFAULT 0;
DECLARE cart_cursor CURSOR FOR
SELECT c.product_id, c.quantity, p.price, p.name, p.stock
FROM cart c
JOIN products p ON c.product_id = p.id
WHERE c.user_id = p_user_id AND p.status = 'active'
FOR UPDATE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
START TRANSACTION;
SET v_order_no = CONCAT(
'ORD', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'),
LPAD(FLOOR(RAND() * 10000), 4, '0')
);
INSERT INTO orders (order_no, user_id, receiver, receiver_phone, shipping_address, remark)
VALUES (v_order_no, p_user_id, p_receiver, p_phone, p_address, p_remark);
OPEN cart_cursor;
read_loop: LOOP
FETCH cart_cursor INTO v_product_id, v_quantity, v_price, v_name, v_stock;
IF done THEN LEAVE read_loop; END IF;
IF v_stock < v_quantity THEN
ROLLBACK;
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = CONCAT('Insufficient stock for: ', v_name);
END IF;
INSERT INTO order_items (order_id, product_id, product_name, quantity, price)
VALUES (LAST_INSERT_ID(), v_product_id, v_name, v_quantity, v_price);
UPDATE products
SET stock = stock - v_quantity, sales = sales + v_quantity
WHERE id = v_product_id;
SET v_total = v_total + v_quantity * v_price;
END LOOP;
CLOSE cart_cursor;
UPDATE orders SET total_amount = v_total WHERE order_no = v_order_no;
DELETE FROM cart WHERE user_id = p_user_id;
COMMIT;
END //
DELIMITER ;
▶ Exemplo: Como fazer um pedido
SQL
CALL sp_create_order(
1,
'Alice',
'13800138000',
'Tech Park Bldg 5 Room 301, Nanshan, Shenzhen, Guangdong',
'Please deliver before noon'
);
▶ Exemplo: Atualização do status do pedido
SQL
UPDATE orders SET status = 'paid', pay_amount = total_amount, paid_at = NOW()
WHERE id = 1 AND status = 'pending';
UPDATE orders SET status = 'shipped', shipped_at = NOW()
WHERE id = 1 AND status = 'paid';
UPDATE orders SET status = 'completed', completed_at = NOW()
WHERE id = 1 AND status = 'shipped';
7. Histórico de pagamentos
(1) Estrutura da tabela
SQL
CREATE TABLE payments (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
order_id BIGINT NOT NULL,
payment_no VARCHAR(64) NOT NULL UNIQUE,
amount DECIMAL(12,2) NOT NULL,
method ENUM('credit_card', 'debit_card', 'paypal', 'bank_transfer') NOT NULL,
status ENUM('pending', 'success', 'failed', 'refunded') DEFAULT 'pending',
paid_at DATETIME DEFAULT NULL,
refund_no VARCHAR(64) DEFAULT NULL,
refund_amount DECIMAL(12,2) DEFAULT NULL,
refund_reason VARCHAR(500) DEFAULT NULL,
refunded_at DATETIME DEFAULT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_payments_order (order_id),
INDEX idx_payments_no (payment_no),
INDEX idx_payments_status (status),
FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB COMMENT='Payment History Report';
(2) Evolução do status do pagamento
| Estado atual | Estados possíveis | Condições de acionamento |
|---|---|---|
| pendente | sucesso | Retorno de chamada do gateway de pagamento bem-sucedido |
| pendente | falha | Falha no retorno de chamada do gateway de pagamento |
| sucesso | reembolsado | Reembolso iniciado |
▶ Exemplo: Transação de pagamento
SQL
INSERT INTO payments (order_id, payment_no, amount, method, status, paid_at)
VALUES (1, CONCAT('PAY', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), LPAD(FLOOR(RAND()*10000),4,'0')),
3899.97, 'credit_card', 'success', NOW());
UPDATE orders SET status = 'paid', pay_amount = 3899.97, paid_at = NOW()
WHERE id = 1 AND status = 'pending';
SELECT p.payment_no, p.amount, p.method, p.status, p.paid_at
FROM payments p
WHERE p.order_id = 1;
▶ Exemplo: Processo de reembolso
SQL
UPDATE payments
SET status = 'refunded',
refund_no = CONCAT('REF', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), LPAD(FLOOR(RAND()*10000),4,'0')),
refund_amount = 3899.97,
refund_reason = 'Defective product',
refunded_at = NOW()
WHERE order_id = 1 AND status = 'success';
UPDATE orders SET status = 'refunded' WHERE id = 1;
8. Visualização de estatísticas de dados
▶ Exemplo: Visualização dos produtos mais vendidos
SQL
CREATE OR REPLACE VIEW v_hot_products AS
SELECT
p.id,
p.name,
p.price,
c.name AS category,
p.sales,
IFNULL(SUM(oi.quantity), 0) AS order_sold,
IFNULL(SUM(oi.quantity * oi.price), 0) AS revenue
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.status = 'active'
GROUP BY p.id, p.name, p.price, c.name, p.sales
ORDER BY p.sales DESC;
SELECT * FROM v_hot_products LIMIT 10;
▶ Exemplo: Classificação de gastos dos usuários
SQL
CREATE OR REPLACE VIEW v_user_ranking AS
SELECT
u.id,
u.username,
COUNT(DISTINCT o.id) AS total_orders,
IFNULL(SUM(o.total_amount), 0) AS total_spent,
IFNULL(AVG(o.total_amount), 0) AS avg_order_amount,
MAX(o.created_at) AS last_order_time
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status != 'cancelled'
GROUP BY u.id, u.username
ORDER BY total_spent DESC;
SELECT * FROM v_user_ranking LIMIT 20;
▶ Exemplo: Relatório mensal de vendas
SQL
CREATE OR REPLACE VIEW v_monthly_sales AS
SELECT
DATE_FORMAT(o.created_at, '%Y-%m') AS month,
COUNT(o.id) AS order_count,
SUM(o.total_amount) AS revenue,
SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END) AS completed,
SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
SUM(CASE WHEN o.status = 'refunded' THEN 1 ELSE 0 END) AS refunded,
ROUND(
SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END) / COUNT(o.id) * 100,
2
) AS completion_rate
FROM orders o
GROUP BY DATE_FORMAT(o.created_at, '%Y-%m')
ORDER BY month DESC;
SELECT * FROM v_monthly_sales;
9. Gerenciamento de permissões e políticas de backup
(1) Matriz de permissões
| Usuário | SELECT | INSERT | UPDATE | DELETE | EXECUTE | ALTER | DROP | CREATE |
|---|---|---|---|---|---|---|---|---|
| ecom_readonly | S | - | - | - | - | - | - | - |
| ecom_app | S | S | S | - | S | - | - | - |
| ecom_admin | S | S | S | S | S | S | S | S |
▶ Exemplo: Criação de um usuário com permissões
SQL
CREATE USER 'ecom_readonly'@'%' IDENTIFIED BY 'ReadOnly2024!';
GRANT SELECT ON ecommerce.* TO 'ecom_readonly'@'%';
CREATE USER 'ecom_app'@'%' IDENTIFIED BY 'AppUser2024!';
GRANT SELECT, INSERT, UPDATE ON ecommerce.* TO 'ecom_app'@'%';
GRANT EXECUTE ON ecommerce.* TO 'ecom_app'@'%';
CREATE USER 'ecom_admin'@'127.0.0.1' IDENTIFIED BY 'Admin2024!Secure';
GRANT ALL PRIVILEGES ON ecommerce.* TO 'ecom_admin'@'127.0.0.1';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'ecom_app'@'%';
(2) Estratégia de backup
| Tipo de backup | Frequência | Retenção | Ferramenta |
|---|---|---|---|
| Backup completo | Diariamente às 02:00 | 30 dias | mysqldump |
| Log binário | Em tempo real | 7 dias | binlog |
| Backup da estrutura das tabelas | Todos os domingos | 90 dias | mysqldump --no-data |
▶ Exemplo: Script de backup
BASH
#!/bin/bash
BACKUP_DIR="/backup/mysql"
DB_NAME="ecommerce"
DB_USER="ecom_admin"
DATE=$(date +%Y%m%d_%H%M%S)
mkdir -p $BACKUP_DIR
mysqldump -u $DB_USER -pAdmin2024!Secure \
--single-transaction \
--routines \
--triggers \
$DB_NAME | gzip > ${BACKUP_DIR}/${DB_NAME}_full_${DATE}.sql.gz
mysqldump -u $DB_USER -pAdmin2024!Secure \
--no-data \
$DB_NAME | gzip > ${BACKUP_DIR}/${DB_NAME}_schema_${DATE}.sql.gz
find $BACKUP_DIR -name "${DB_NAME}_full_*.sql.gz" -mtime +30 -delete
find $BACKUP_DIR -name "${DB_NAME}_schema_*.sql.gz" -mtime +90 -delete
echo "Backup completed: ${DATE}"
▶ Exemplo: Restauração de dados
BASH
gunzip < /backup/mysql/ecommerce_full_20240115_020000.sql.gz | mysql -u ecom_admin -p ecommerce
10. Lista de verificação para o projeto de índices
| Tabela | Nome do índice | Coluna | Tipo | Finalidade |
|---|---|---|---|---|
| usuários | idx_users_phone | telefone | BTREE | Fazer login com o número de telefone |
| usuários | idx_users_status | status | BTREE | Filtragem por status |
| endereços | idx_endereços_usuário | id_usuário | BTREE | Lista de endereços do usuário |
| categorias | idx_categories_parent | parent_id | BTREE | Encontrar subcategorias |
| produtos | idx_products_category | category_id | BTREE | Lista de produtos por categoria |
| produtos | idx_products_status | status | BTREE | Filtrar por status da listagem |
| produtos | idx_products_sales | vendas DESC | BTREE | Ordenação por mais vendidos |
| produtos | idx_products_created | created_at DESC | BTREE | Classificação por novidades |
| pedidos | idx_orders_user | user_id | BTREE | Lista de pedidos do usuário |
| pedidos | idx_orders_status | status | BTREE | Filtragem por status |
| pedidos | idx_orders_created | created_at DESC | BTREE | Ordenação por hora |
| pedidos | idx_orders_order_no | order_no | ÚNICO | Pesquisa por número de pedido |
| itens_do_pedido | idx_itens_do_pedido | id_do_pedido | BTREE | Detalhes do pedido |
| itens_do_pedido | idx_itens_do_produto | id_do_produto | BTREE | Estatísticas de vendas do produto |
| carrinho | uk_cart_user_product | user_id,product_id | UNIQUE | Remover duplicatas do carrinho de compras |
| pagamentos | idx_payments_order | order_id | BTREE | Consulta de pagamentos de pedidos |
| pagamentos | idx_payments_status | status | BTREE | Filtragem por status de pagamento |
| logs_do_usuário | logs_idx_do_usuário | id_do_usuário | BTREE | Logs do usuário |
| logs_do_usuário | logs_idx_criados | data_de_criação | BTREE | Consulta por intervalo de tempo |
❓ Perguntas Frequentes
P: Como garantir que os números de pedido sejam únicos? R: Concatenando um carimbo de data/hora e um número aleatório (por exemplo,
ORD202401151430520387), utilizando um UUID ou recorrendo a uma sequência de autoincremento do Redis. Gere o número de pedido comoCONCAT + DATE_FORMAT + RANDdurante o processo de armazenamento do pedido e utilize uma restrição UNIQUE como alternativa.
P: O que devo fazer se o estoque estiver esgotado? R: Existem duas abordagens — para o bloqueio pessimista, use
SELECT ... FOR UPDATEpara bloquear a linha do estoque e deduzir a quantidade dentro da transação; para o bloqueio otimista, use a versãoUPDATE products SET stock = stock - N, version = version + 1 WHERE id = X AND version = V. Em cenários de alta simultaneidade, recomendamos o uso do bloqueio otimista combinado com o Redis para a pré-dedução.
P: A tabela de pedidos está muito grande? R: Três estratégias — 1. Dividir a tabela por mês em
orders_202401eorders_202402; 2. Usar o particionamento do MySQLPARTITION BY RANGE (TO_DAYS(created_at)); 3. Separar os dados ativos dos inativos; arquivar os pedidos com mais de um ano em uma tabela histórica. Adicionar uma condição de tempo às consultas para direcioná-las à partição correta.
P: Como se projeta uma estrutura de classificação com níveis infinitos? R: Use
parent_idcomo chave estrangeira autorreferencial, com a categoria de nível superiorparent_id = 0. Consulte a subárvore usando um CTE recursivo:WITH RECURSIVE cat_tree AS (SELECT * FROM categories WHERE id = 1 UNION ALL SELECT c.* FROM categories c JOIN cat_tree ct ON c.parent_id = ct.id) SELECT * FROM cat_tree;
P: Os carrinhos de compras são armazenados no banco de dados ou no Redis? R: Para usuários conectados, os carrinhos de compras são armazenados no banco de dados (para persistência) e mantidos em cache no Redis (para operações rápidas de leitura/gravação). Para usuários que não estão conectados, os carrinhos de compras são armazenados no Redis ou no LocalStorage e sincronizados com o banco de dados no momento do login. Como as operações com carrinhos de compras são frequentes, o Redis ajuda a reduzir a carga sobre o banco de dados.
P: Por que usar o SHA-2 em vez do MD5 para senhas? R: O MD5 provou ser vulnerável a colisões e pode ser facilmente quebrado usando tabelas rainbow. O SHA-2-256 é mais seguro; em ambientes de produção, recomendamos o uso do bcrypt ou do Argon2, que incluem valores de salt e permitem ajustar o custo computacional.
P: Por que
order_itemsinclui uma duplicata deproduct_name? R: Os nomes dos produtos podem mudar, mas o registro do pedido deve manter as informações da data em que o pedido foi feito. Esse é um exemplo clássico de antinormalização — sacrificar um pouco de espaço de armazenamento em troca da consistência dos dados para evitar que pedidos históricos sejam exibidos incorretamente após a renomeação de um produto.
📖 Resumo
- A Análise de Requisitos é o ponto de partida; o diagrama ER mapeia as entidades e as relações; os oito módulos abrangem todo o processo de comércio eletrônico.
- Sistema do usuário: Colaboração entre três tabelas — a tabela
usersgerencia identidades, a tabelaaddressesgerencia endereços de entrega e a tabelauser_logsgerencia registros de auditoria - Sistema de Produtos: Utiliza a autorreferência
parent_idpara implementar uma categorização de níveis infinitos;product_imagessuporta várias imagens - O sistema de pedidos é o elemento central: os procedimentos armazenados encapsulam a lógica de realização de pedidos,
FOR UPDATEevita a venda excessiva e as transações garantem a atomicidade. - Cesta de compras: Use
ON DUPLICATE KEY UPDATEpara implementar a adição de itens à cesta e a fusão entre elas; os procedimentos armazenados cuidam da fusão entre usuários - Os registros de pagamento são separados dos pedidos, com atualizações claras sobre o status e campos completos para acompanhamento de reembolsos
- Análise de dados: Encapsule consultas agregadas complexas usando visualizações para gerar listas de produtos mais vendidos, classificações e relatórios mensais com um único clique
- Permissões e backups são a base da segurança: o princípio do privilégio mínimo + backups completos diários + recuperação incremental do binlog
📝 Exercícios
-
Questão básica (Dificuldade: ⭐): Crie um banco de dados de comércio eletrônico, escreva as instruções SQL para criar as quatro tabelas (usuários, endereços, categorias e produtos) e insira dados de teste.
-
Exercício avançado (Dificuldade ⭐⭐): Implemente um recurso completo de carrinho de compras — crie tabelas, escreva consultas SQL para operações CRUD, escreva o procedimento armazenado
sp_merge_carte useON DUPLICATE KEY UPDATEpara lidar com adições duplicadas ao carrinho. -
Exercício prático (Dificuldade: ⭐⭐⭐): Implemente o procedimento armazenado de realização de pedidos
sp_create_order. Ele deve incluir a iteração pelo carrinho de compras, a verificação do estoque (FOR UPDATE), a inserção do pedido e seus detalhes, a dedução do estoque e a limpeza do carrinho de compras. Todo o processo deve ser envolvido em uma transação; se o estoque for insuficiente, reverta a transação e gere um erro. -
Questão de análise (Dificuldade: ⭐⭐⭐): Crie três visualizações estatísticas (Produtos mais vendidos / Ranking de gastos dos usuários / Relatório mensal de vendas), escreva consultas para verificar os resultados das visualizações e analise os problemas de desempenho dessas visualizações quando a tabela de pedidos ultrapassar 10 milhões de linhas, apresentando também propostas de otimização.
-
Desafio (Dificuldade: ⭐⭐⭐⭐): Implemente integralmente o banco de dados de comércio eletrônico do ShopEasy — incluindo a criação do banco de dados e das tabelas, índices, procedimentos armazenados, visualizações e permissões de usuário (somente leitura/aplicativo/administrador) — juntamente com um script de backup diário. Além disso, redija um documento de implantação de 200 caracteres descrevendo as etapas de configuração inicial e os pontos-chave para as operações e a manutenção diárias.
