Skip to content

MySQL Folha de referência

Sistema popular de gestão de banco de dados relacional open-source.

01

Primeiros Passos

Conectar ao MySQL

O cliente mysql é a CLI padrão do MySQL. Sempre use -p (sem espaço depois) para que a senha seja pedida de forma segura, em vez de ficar visível no histórico do shell. -h define o host, -P (maiúscula) a porta. \s imprime o status da conexão. Use -e para rodar consultas pontuais em scripts.

mysql
# connect to local server (prompt for password)
mysql -u root -p

# connect to a remote host on a custom port
mysql -h 192.168.1.100 -P 3307 -u admin -p

# connect directly to a specific database
mysql -u root -p mydb

# non-interactive: run a query and exit
mysql -u root -p -e "SELECT VERSION();"

# show server status inside the client
\s
SELECT VERSION(), CURRENT_USER, DATABASE();

Gestão de Bancos

Use utf8mb4 (não utf8) para suportar o Unicode inteiro, incluindo emoji — o utf8 do MySQL é um subconjunto de 3 bytes que não guarda cada um dos caracteres. utf8mb4_unicode_ci é a collation recomendada para ordenação correta. IF EXISTS/IF NOT EXISTS previnem erros em scripts. DROP DATABASE remove todas as tabelas e dados instantaneamente.

mysql
# list all databases
SHOW DATABASES;

# create a database with charset and collation
CREATE DATABASE mydb
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

# switch to a database
USE mydb;

# show the current database
SELECT DATABASE();

# drop a database (irreversible)
DROP DATABASE IF EXISTS mydb;

# alter a database's charset
ALTER DATABASE mydb CHARACTER SET utf8mb4;

Mostrar e Descrever Objetos

Comandos SHOW são ferramentas de introspecção específicas do MySQL. DESCRIBE dá uma visão rápida das colunas; SHOW CREATE TABLE dá o DDL completo que você pode reutilizar. Acrescente \G em vez de ; para saída vertical — muito mais legível para linhas largas como SHOW CREATE TABLE.

mysql
# list tables in the current database
SHOW TABLES;

# show table structure (columns, types, keys)
DESCRIBE users;
# equivalent
SHOW COLUMNS FROM users;

# show create statement for a table
SHOW CREATE TABLE users\G

# list indexes on a table
SHOW INDEX FROM users;

# show stored procedures / functions
SHOW PROCEDURE STATUS WHERE Db = 'mydb';
SHOW FUNCTION STATUS WHERE Db = 'mydb';

# list triggers
SHOW TRIGGERS\G

Status e Variáveis do Servidor

Variáveis MySQL têm escopos SESSION (conexão atual) e GLOBAL. SET GLOBAL muda o servidor em execução, mas reseta no reinício — persista as configurações no my.cnf em vez disso. @@var lê valores. SHOW STATUS expõe contadores de runtime (conexões, uptime, rendimento) úteis para monitoramento.

mysql
# view a system variable
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'version%';

# view session vs global variable
SELECT @@session.sql_mode, @@global.sql_mode;

# set a variable for the current session
SET sql_mode = 'STRICT_TRANS_TABLES';
SET @@session.foreign_key_checks = 0;

# set globally (needs SUPER/privilege, persists until restart)
SET GLOBAL max_connections = 200;

# show server status counters
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Uptime';

Comentários e Básico de Instruções

MySQL suporta três estilos de comentário: -- (exige espaço depois), blocos /* */ e # até o fim da linha. /*! ... */ é um comentário executável especial — o código dentro roda só no MySQL e é ignorado por outros engines SQL, útil para arquivos de schema portáveis. \G executa e exibe os resultados verticalmente.

mysql
-- single-line comment (note the space after --)
SELECT 1; -- inline comment

/* multi-line
   comment block */
SELECT 2;

# hash-style comment (MySQL-specific, to end of line)
SELECT 3;

# MySQL executable comments: run only on MySQL
SELECT 1 /*!50100 , 2 */;  /* the part runs on MySQL >= 5.1 */

# statements end with semicolon; \G ends and prints vertically
SELECT * FROM users\G

Arquivo de Configuração (my.cnf)

my.cnf (Linux) / my.ini (Windows) guarda configurações persistentes do servidor e do cliente organizadas em seções: [mysqld] para o servidor, [client]/[mysql] para o cliente. innodb_buffer_pool_size é o único controle de ajuste mais importante para o InnoDB (tipicamente 50-75% da RAM). Depois de editar, reinicie o servidor. Use mysql --help ou SHOW VARIABLES para verificar.

mysql
# /etc/my.cnf or ~/.my.cnf  (Linux/macOS)
# C:\ProgramData\MySQL\MySQL Server 8.0\my.ini  (Windows)

[mysqld]
port            = 3306
datadir         = /var/lib/mysql
max_connections = 200
character-set-server = utf8mb4
collation-server     = utf8mb4_unicode_ci
innodb_buffer_pool_size = 2G
slow_query_log  = 1
long_query_time = 2

[client]
default-character-set = utf8mb4

[mysql]
prompt = \u@\h [\d]>\_
02

Operações DDL

Criar Tabela

CREATE TABLE define colunas, tipos, restrições e opções de tabela. InnoDB é o engine padrão e recomendado (suporta transações, travas no nível de linha, chaves estrangeiras). BIGINT UNSIGNED para AUTO_INCREMENT evita overflow. DECIMAL(p,s) é exato para dinheiro. ENUM valida contra uma lista fixa. TIMESTAMP DEFAULT CURRENT_TIMESTAMP se preenche automaticamente na inserção.

mysql
CREATE TABLE users (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username    VARCHAR(50)  NOT NULL UNIQUE,
  email       VARCHAR(255) NOT NULL,
  birth_date  DATE         NULL,
  status      ENUM('active','inactive','banned') NOT NULL DEFAULT 'active',
  balance     DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Alterar Tabela

ALTER TABLE evolui o schema sem recriar dados. ADD COLUMN com AFTER posiciona-a num lugar específico (a ordem das colunas é cosmética no MySQL). MODIFY muda tipo/padrão; RENAME COLUMN (8.0+) é mais limpo que CHANGE. Operações ALTER grandes podem reconstruir e travar a tabela — use pt-online-schema-change ou DDL online para tabelas grandes. RENAME TABLE é atômico.

mysql
# add a column
ALTER TABLE users
  ADD COLUMN phone VARCHAR(20) NULL AFTER email;

# add multiple columns
ALTER TABLE users
  ADD COLUMN first_name VARCHAR(50) NULL,
  ADD COLUMN last_name  VARCHAR(50) NULL;

# modify a column type
ALTER TABLE users
  MODIFY COLUMN phone VARCHAR(30) NOT NULL;

# rename a column (MySQL 8+ preserves data)
ALTER TABLE users
  RENAME COLUMN phone TO phone_number;

# rename a table
RENAME TABLE users TO members;
ALTER TABLE users RENAME TO members;

Descartar e Truncar Tabela

DROP TABLE remove a tabela inteira; TRUNCATE a esvazia, mas mantém a estrutura e reseta o AUTO_INCREMENT ao seu valor inicial. TRUNCATE é mais rápido que DELETE, porque pula a deleção e o log por linha, mas é DDL (não pode ser desfeito, não dispara triggers). Desative foreign_key_checks ao truncar tabelas referenciadas por outras.

mysql
# drop a table (removes structure and data)
DROP TABLE IF EXISTS old_logs;

# drop multiple tables
DROP TABLE IF EXISTS temp1, temp2;

# truncate: empty the table, keep structure, reset AUTO_INCREMENT
TRUNCATE TABLE session_data;

# truncate cannot be rolled back in some engines
# (InnoDB: TRUNCATE is DDL, implicitly commits)
SET foreign_key_checks = 0;
TRUNCATE TABLE child_table;
SET foreign_key_checks = 1;

AUTO_INCREMENT

AUTO_INCREMENT gera números sequenciais para uma chave primária — cada tabela pode ter só um. LAST_INSERT_ID() retorna o id gerado pelo INSERT mais recente na sessão atual (por conexão, seguro para uso concorrente). Lacunas podem aparecer após deletes, rollbacks ou inserções que especificam valores explícitos. Para resetar, use ALTER TABLE ... AUTO_INCREMENT = n.

mysql
CREATE TABLE orders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  amount DECIMAL(10,2)
);

# set the next auto-increment value
ALTER TABLE orders AUTO_INCREMENT = 1000;

# insert without specifying id
INSERT INTO orders (amount) VALUES (99.50);

# get the last inserted id
SELECT LAST_INSERT_ID();

# show current AUTO_INCREMENT value
SHOW TABLE STATUS LIKE 'orders'\G

Restrições de Tabela

Restrições impõem integridade de dados no nível do banco. InnoDB suporta chaves primárias, UNIQUE, NOT NULL, CHECK (imposta desde a 8.0.16) e chaves estrangeiras. FOREIGN KEY ... ON DELETE CASCADE remove linhas filhas quando o pai é deletado; SET NULL as anula; RESTRICT/NO ACTION bloqueiam a deleção. Nomeie as restrições explicitamente, para gestão mais fácil. Chaves estrangeiras exigem índices nas duas colunas.

mysql
CREATE TABLE orders (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      INT UNSIGNED NOT NULL,
  total        DECIMAL(10,2) NOT NULL,
  status       VARCHAR(20) NOT NULL,

  # unique constraint
  CONSTRAINT uq_order_no UNIQUE (user_id, status),

  # check constraint (MySQL 8.0.16+ enforced)
  CONSTRAINT chk_total CHECK (total >= 0),

  # foreign key with cascading actions
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
) ENGINE=InnoDB;

# add a constraint later
ALTER TABLE orders
  ADD CONSTRAINT chk_status CHECK (status IN ('paid','shipped','cancelled'));

Tabelas Temporárias

Tabelas TEMPORARY existem só pela sessão atual e são descartadas automaticamente quando ela fecha. Podem sombrear uma tabela real de mesmo nome, útil para refatoração segura ou estagiar dados. Duas sessões podem criar tabelas temporárias com o mesmo nome sem conflito. Não são visíveis a outras conexões e não são escritas no binlog por padrão.

mysql
# session-scoped, auto-dropped on disconnect
CREATE TEMPORARY TABLE tmp_active
  SELECT id, username FROM users WHERE status = 'active';

# identical name shadowing a real table
CREATE TEMPORARY TABLE users
  (id INT, name VARCHAR(50));

# only this session sees the temp table
SELECT * FROM users;

# explicit cleanup
DROP TEMPORARY TABLE IF EXISTS tmp_active;
03

Tipos de Dados

Tipos Numéricos

Use DECIMAL para valores monetários em que a exatidão importa — FLOAT/DOUBLE são aproximados e acumulam erros de arredondamento. UNSIGNED dobra a faixa positiva, mas proíbe negativos. A largura de exibição (ex.: INT(11)) está obsoleta no MySQL 8.0 e é ignorada — nunca limitou a faixa guardada. BIGINT UNSIGNED é recomendado para AUTO_INCREMENT, evitando overflow. SERIAL é um atalho prático para chave substituta.

mysql
# integers (with optional UNSIGNED)
TINYINT        -- 1 byte, -128..127 or 0..255 (UNSIGNED)
SMALLINT       -- 2 bytes
MEDIUMINT      -- 3 bytes
INT            -- 4 bytes
BIGINT         -- 8 bytes (use for large AUTO_INCREMENT)

# fixed-point (exact, for money)
DECIMAL(10,2)  -- 10 digits total, 2 after decimal
NUMERIC(8,4)

# floating-point (approximate)
FLOAT          -- 4 bytes
DOUBLE         -- 8 bytes

# other
BIT(8)         -- bit-field
SERIAL         -- alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

Tipos de String e Texto

VARCHAR é quase sempre preferido a CHAR (que preenche com espaços), exceto para códigos curtos de comprimento fixo. Para o utf8mb4 do MySQL, cada caractere pode ocupar até 4 bytes — uma coluna VARCHAR(255) precisa de até 1020 bytes mais o comprimento, o que importa para os limites de chave de índice (3072 bytes no InnoDB). As famílias TEXT/BLOB guardam dados grandes fora da página; não podem ter valor padrão e indexá-las exige um comprimento de prefixo.

mysql
# fixed vs variable length
CHAR(10)       -- fixed 10 chars, padded with spaces
VARCHAR(255)   -- variable, up to 255 chars (stores actual length)

# large text
TINYTEXT       -- up to 255 bytes
TEXT           -- up to 64 KB
MEDIUMTEXT     -- up to 16 MB
LONGTEXT       -- up to 4 GB

# binary counterparts
BINARY(16), VARBINARY(255), BLOB, MEDIUMBLOB, LONGBLOB

# national character set
NATIONAL VARCHAR(100)  -- same as VARCHAR with utf8mb4

# VARCHAR with charset counts characters, not bytes
VARCHAR(100) CHARACTER SET utf8mb4

Tipos de Data e Hora

DATETIME guarda data/hora literal sem consciência de fuso; TIMESTAMP guarda UTC e converte para o time_zone da sessão na exibição, tornando-o melhor para apps multi-fuso — mas tem limite de 2038 em builds de 32 bits. Use DATETIME(6)/TIMESTAMP(6) para precisão de microssegundo. CURRENT_TIMESTAMP serve como padrão para ambos desde o MySQL 5.6.5. Defina o time_zone da sessão por conexão com SET time_zone = '+08:00';.

mysql
DATE            -- 'YYYY-MM-DD', range 1000-01-01 .. 9999-12-31
TIME            -- 'HH:MM:SS', can be negative for elapsed time
DATETIME        -- 'YYYY-MM-DD HH:MM:SS', 8 bytes, no timezone
TIMESTAMP       -- stored as UTC, converted to session timezone, 4 bytes
YEAR            -- YEAR(4) e.g. 2024

# DATETIME with fractional seconds and auto-default
CREATE TABLE events (
  ts DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3)
                 ON UPDATE CURRENT_TIMESTAMP(3)
);

# TIMESTAMP range: 1970-01-01 .. 2038-01-19 (32-bit limit)
# MySQL 8.0.28+ extends TIMESTAMP to 24 bits of range

Tipos ENUM e SET

ENUM guarda um de uma lista fixa de valores de string de forma compacta (1-2 bytes) e rejeita valores desconhecidos em modo estrito. SET guarda qualquer combinação de até 64 membros como bitmask. Ambos tornam mudanças de schema caras (adicionar/reordenar membros precisa de ALTER TABLE) e são desajeitados de consultar — muitas equipes preferem uma tabela de consulta ou VARCHAR com CHECK. FIND_IN_SET ajuda a consultar colunas SET.

mysql
# ENUM: exactly one value from a list
CREATE TABLE shirts (
  size ENUM('S','M','L','XL') NOT NULL DEFAULT 'M',
  color ENUM('red','green','blue') NULL
);

# stored as integer index (1-based), space-efficient
INSERT INTO shirts (size) VALUES ('L'), ('XL');

# SET: zero or more values from a list (bit flags)
CREATE TABLE posts (
  tags SET('news','tech','sports','fun') NOT NULL DEFAULT ''
);

INSERT INTO posts (tags) VALUES ('tech,fun');

# query SET members with FIND_IN_SET
SELECT * FROM posts WHERE FIND_IN_SET('tech', tags);

Tipo JSON

O tipo JSON (MySQL 5.7.8+) guarda JSON num formato binário validado na escrita e suporta acesso rápido a membros. É preferível a guardar JSON numa coluna TEXT, porque você pode indexar e consultar dentro dele. Use JSON_OBJECT(), JSON_ARRAY() e JSON_MERGE_PATCH() para construir valores. Para consultas frequentes de poucas chaves, extraia-as para colunas geradas e indexe-as, pelo melhor desempenho.

mysql
CREATE TABLE products (
  id    INT PRIMARY KEY,
  name  VARCHAR(100),
  attrs JSON NOT NULL
);

# insert JSON literals
INSERT INTO products (id, name, attrs) VALUES
  (1, 'Widget', '{"color":"red","size":42,"tags":["new","sale"]}'),
  (2, 'Gadget', '{"color":"blue","in_stock":true}');

# JSON values are validated on insert and stored binary
SELECT attrs FROM products WHERE id = 1;

# pretty-print JSON
SELECT JSON_PRETTY(attrs) FROM products WHERE id = 1;

Tipos BLOB e Binários

BLOB guarda dados binários arbitrários (imagens, arquivos), mas é desajeitado de consultar e não pode ter padrão. Para arquivos grandes, prefira guardar um caminho no banco e o arquivo em disco/objetos. Guarde UUIDs como BINARY(16) (16 bytes) em vez de CHAR(36) (36 bytes), por eficiência de índice — o MySQL 8.0 adiciona UUID_TO_BIN()/BIN_TO_UUID() com troca opcional para amigabilidade de índice.

mysql
# binary large objects
TINYBLOB   -- up to 255 bytes
BLOB       -- up to 64 KB
MEDIUMBLOB -- up to 16 MB
LONGBLOB   -- up to 4 GB

# fixed-size binary (e.g. hashes, UUIDs)
BINARY(16)    -- fixed 16 bytes, right-padded with \0
VARBINARY(255)

# store a UUID as binary for compactness
CREATE TABLE sessions (
  id BINARY(16) PRIMARY KEY,
  data VARBINARY(1000)
);
INSERT INTO sessions (id, data) VALUES (UUID_TO_BIN(UUID()), '...');

# retrieve as text
SELECT BIN_TO_UUID(id) FROM sessions;
04

DML (INSERT / UPDATE / DELETE)

INSERT

INSERT de múltiplas linhas é muito mais rápido que inserções de linha única repetidas, porque amortiza parsing, idas e voltas de rede e atualizações de índice. INSERT ... SELECT copia dados entre tabelas. A sintaxe SET é específica do MySQL e conveniente para uma linha. Sempre liste as colunas alvo explicitamente, para que a instrução sobreviva a mudanças de schema. Use INSERT IGNORE ou ON DUPLICATE KEY UPDATE para inserções idempotentes.

mysql
# single row
INSERT INTO users (username, email)
VALUES ('alice', '[email protected]');

# multiple rows in one statement
INSERT INTO users (username, email) VALUES
  ('bob', '[email protected]'),
  ('carol', '[email protected]'),
  ('dave', '[email protected]');

# insert from a SELECT
INSERT INTO archive_users (username, email)
SELECT username, email FROM users WHERE status = 'inactive';

# insert with column-order-free syntax
INSERT INTO users SET username='eve', email='[email protected]';

INSERT ... ON DUPLICATE KEY UPDATE (Upsert)

ON DUPLICATE KEY UPDATE implementa upsert — quando ocorre conflito de UNIQUE/PRIMARY KEY, o MySQL atualiza a linha existente em vez de dar erro. VALUES(col) refere-se ao valor que teria sido inserido; MySQL 8.0.19+ torna isso obsoleto em favor de apelidos de linha (AS novo). É mais eficiente que um SELECT-e-INSERT/UPDATE separado, porque é atômico. INSERT IGNORE pula conflitos silenciosamente.

mysql
CREATE TABLE counters (
  name VARCHAR(50) PRIMARY KEY,
  hits INT NOT NULL DEFAULT 0
);

# upsert: insert or update on key conflict
INSERT INTO counters (name, hits)
VALUES ('home', 1)
ON DUPLICATE KEY UPDATE hits = hits + 1;

# reference the proposed values with VALUES()
INSERT INTO counters (name, hits) VALUES ('about', 5)
ON DUPLICATE KEY UPDATE hits = VALUES(hits) + 1;

# MySQL 8.0.20+: use an alias for the row being inserted
INSERT INTO counters (name, hits) VALUES ('home', 1) AS new
ON DUPLICATE KEY UPDATE hits = counters.hits + new.hits;

UPDATE

Sempre inclua uma cláusula WHERE — um UPDATE sem uma modifica toda linha. MySQL suporta UPDATE multi-tabela com JOIN, útil para desnormalizar valores computados. ORDER BY + LIMIT habilita atualizações em lote seguras, evitando travas longas em tabelas enormes. No modo safe-updates, o cliente recusa UPDATE/DELETE sem uma chave na cláusula WHERE, uma salvaguarda contra mudanças em massa acidentais.

mysql
# basic update with WHERE (always include one!)
UPDATE users
SET status = 'banned', email = NULL
WHERE id = 42;

# update with expression
UPDATE products
SET price = price * 1.10
WHERE category = 'electronics';

# multi-table update with JOIN
UPDATE users u
JOIN orders o ON o.user_id = u.id
SET u.total_spent = u.total_spent + o.amount
WHERE o.paid = 1;

# limit and order (useful for batched updates)
UPDATE logs SET archived = 1
WHERE archived = 0
ORDER BY created_at ASC
LIMIT 1000;

DELETE

DELETE remove linhas uma por vez, é transacional (pode ser desfeito) e dispara triggers. DELETE multi-tabela pode remover de várias tabelas numa única instrução. Para esvaziar uma tabela inteira, TRUNCATE é muito mais rápido e reseta o AUTO_INCREMENT, mas não é transacional. O modificador QUICK (MyISAM) pula a atualização das folhas de índice — raramente necessário com InnoDB. Sempre use uma cláusula WHERE.

mysql
# delete specific rows
DELETE FROM users WHERE status = 'banned' AND last_login < '2020-01-01';

# delete with LIMIT (batched)
DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 5000;

# multi-table delete with JOIN
DELETE u, o
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.id = 99;

# delete all rows (keeps table & AUTO_INCREMENT, unlike TRUNCATE)
DELETE FROM temp_data;

# quick delete that doesn't return row count
DELETE QUICK FROM logs WHERE year = 2020;

REPLACE

REPLACE é específico do MySQL: se existe uma linha com a mesma chave PK/UNIQUE, ela é deletada e uma linha nova inserida; caso contrário, apenas insere. É mais simples que upsert, mas tem efeitos colaterais — dispara triggers DELETE e depois INSERT (não UPDATE), reseta o AUTO_INCREMENT e descarta colunas não fornecidas para seus padrões. Prefira INSERT ... ON DUPLICATE KEY UPDATE para a maioria dos casos.

mysql
# REPLACE deletes then inserts on key conflict
REPLACE INTO users (id, username, email)
VALUES (1, 'alice', '[email protected]');

# REPLACE ... SET form
REPLACE INTO users SET id = 1, username = 'alice', email = '[email protected]';

# REPLACE from SELECT
REPLACE INTO daily_stats (day, visits)
SELECT CURDATE(), COUNT(*) FROM visits_log;

# caveat: REPLACE fires DELETE + INSERT triggers,
# not UPDATE, and resets columns not listed

LOAD DATA INFILE

LOAD DATA INFILE é a forma mais rápida de carregar CSV em massa — 20x mais rápido que instruções INSERT, pois contorna o parsing do SQL. O servidor lê arquivos de um diretório seguro (secure_file_priv); LOCAL faz o cliente enviar o arquivo, mas está desativado por padrão por segurança. Use @var para capturar colunas e depois SET para transformar valores durante a carga. SELECT ... INTO OUTFILE é a exportação simétrica.

mysql
# fast bulk import from a CSV file
LOAD DATA INFILE '/var/lib/mysql-files/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(username, email, @created)
SET created_at = STR_TO_DATE(@created, '%Y-%m-%d %H:%i:%s');

# LOCAL reads from the client machine (needs --local-infile)
LOAD DATA LOCAL INFILE 'C:/data/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES (username, email);

# export counterpart
SELECT * FROM users INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
05

Consultas SELECT

SELECT Básico

Evite SELECT * em produção — transfere dados desnecessários, quebra se colunas forem adicionadas/removidas e impede o otimizador de usar índices cobertores. Nomeie as colunas de que precisa. LIMIT com OFFSET implementa paginação, mas é lenta para offsets grandes (ainda varre as linhas puladas); a paginação por keyset (WHERE id > ultimo_id LIMIT n) escala muito melhor.

mysql
# select all columns
SELECT * FROM users;

# select specific columns (preferred)
SELECT id, username, email FROM users;

# column aliases
SELECT username AS name, email AS contact FROM users;

# constants and expressions
SELECT 1 + 1 AS sum, NOW() AS now, 'hello' AS greeting;

# limit rows and skip (pagination)
SELECT id, username FROM users
ORDER BY id DESC
LIMIT 10 OFFSET 20;

# MySQL shorthand: LIMIT offset, count
SELECT id, username FROM users LIMIT 20, 10;

WHERE e Operadores

Comparações com NULL resultam NULL (tratado como falso), então sempre use IS NULL / IS NOT NULL. NULL numa lista IN/NOT IN pode produzir resultados vazios surpreendentes — use NOT EXISTS ou uma subconsulta correlacionada em vez disso. BETWEEN é inclusivo. MySQL avalia AND antes de OR, então parenthesize condições mistas. Para o melhor desempenho, ponha a condição mais seletiva primeiro e garanta que colunas indexadas fiquem à esquerda do operador.

mysql
# comparison operators
SELECT * FROM products WHERE price < 100;
SELECT * FROM products WHERE price BETWEEN 50 AND 150;
SELECT * FROM users WHERE id IN (1, 5, 9);

# logical operators
SELECT * FROM users
WHERE status = 'active' AND (age >= 18 OR verified = 1);

# NULL checks (never use = with NULL)
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

# BETWEEN is inclusive on both ends
SELECT * FROM orders WHERE created_at
  BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59';

# NOT IN with NULL traps: NULL poisons the result

ORDER BY e LIMIT

ORDER BY ordena os resultados; múltiplas chaves se aplicam da esquerda para a direita. FIELD() habilita ordenação personalizada por uma lista enumerada. Para conjuntos de resultados grandes, ORDER BY ... LIMIT ainda pode varrer e ordenar o conjunto inteiro, a menos que um índice o suporte — adicione um índice nas colunas do ORDER BY. O padrão de função de janela (ROW_NUMBER OVER PARTITION BY) é a solução canônica de top-N-por-grupo no MySQL 8.0+.

mysql
# ascending (default) and descending
SELECT * FROM users ORDER BY created_at;
SELECT * FROM users ORDER BY created_at DESC, username ASC;

# order by expression
SELECT product, price * stock AS value
FROM inventory
ORDER BY value DESC;

# order by FIELD() for custom ordering
SELECT * FROM tasks
ORDER BY FIELD(priority, 'high', 'medium', 'low');

# stable top-N per group via window function (8.0+)
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id DESC) rn
  FROM orders
) t WHERE rn <= 3;

DISTINCT e Agrupamento de Valores

DISTINCT colapsa linhas idênticas; com múltiplas colunas, deduplica a combinação. COUNT(DISTINCT col) conta valores únicos não nulos. DISTINCT é essencialmente GROUP BY sobre todas as colunas selecionadas. Para deduplicar mantendo uma linha representativa (ex.: a mais nova), use ROW_NUMBER() OVER (PARTITION BY ...). DISTINCT pode ser caro — constrói uma tabela temporária, então prefira um índice.

mysql
# unique values of one column
SELECT DISTINCT country FROM users;

# distinct over multiple columns
SELECT DISTINCT city, country FROM users;

# COUNT distinct values
SELECT COUNT(DISTINCT country) AS countries FROM users;

# GROUP BY returns one row per group; DISTINCT is a special case
SELECT country, COUNT(*) AS users
FROM users
GROUP BY country;

# deduplicate while keeping the latest row (8.0+)
WITH latest AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id DESC) rn
  FROM users
)
SELECT id, email FROM latest WHERE rn = 1;

Casamento de Padrões LIKE e REGEXP

LIKE é simples, mas curingas iniciais (LIKE '%x') derrotam índices e forçam varredura completa. A collation decide a sensibilidade à caixa — utf8mb4_0900_ai_ci é insensível a acento/caixa. REGEXP/RLIKE adicionam o poder completo de regex, mas nunca usam índice. MySQL 8.0 adiciona REGEXP_LIKE, REGEXP_REPLACE, REGEXP_INSTR e REGEXP_SUBSTR para tratamento de padrões mais rico. Para busca de texto completo, use um índice FULLTEXT com MATCH ... AGAINST.

mysql
# LIKE wildcards: % (any chars) and _ (one char)
SELECT * FROM users WHERE username LIKE 'a%';     -- starts with a
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE username LIKE '_an';    -- 3 chars, ends 'an'

# escape a wildcard literally
SELECT * FROM users WHERE path LIKE '50\%' ESCAPE '\\';

# case-insensitive LIKE depends on column collation
# *_ci collations are case-insensitive

# REGEXP / RLIKE for regular expressions
SELECT * FROM users WHERE email REGEXP '^[a-z]+@';
SELECT * FROM products WHERE name RLIKE 'iPhone|Galaxy';

# capture groups with REGEXP_REPLACE (8.0+)
SELECT REGEXP_REPLACE(phone, '([0-9]{3})([0-9]{4})', '$1-$2')
FROM contacts;

Expressão CASE

CASE é a expressão condicional do SQL. A forma buscada avalia as condições WHEN de cima para baixo e retorna o valor THEN do primeiro casamento, senão o ELSE (ou NULL). CASE pode ser usado em SELECT, WHERE, ORDER BY e agregados — o padrão SUM(CASE ...) é um pivot clássico. CASE nunca curto-circuita agregação, então toda linha é contada. IF() e IFNULL() são atalhos MySQL mais curtos para casos simples.

mysql
# searched CASE (like if/else)
SELECT username,
  CASE
    WHEN age < 18 THEN 'minor'
    WHEN age < 65 THEN 'adult'
    ELSE 'senior'
  END AS age_group
FROM users;

# simple CASE (compare to a value)
SELECT order_id,
  CASE status
    WHEN 0 THEN 'pending'
    WHEN 1 THEN 'paid'
    WHEN 2 THEN 'shipped'
    ELSE 'unknown'
  END AS status_label
FROM orders;

# CASE in aggregate (pivot)
SELECT
  SUM(CASE WHEN status='paid' THEN 1 ELSE 0 END) AS paid_count,
  SUM(CASE WHEN status='shipped' THEN 1 ELSE 0 END) AS shipped_count
FROM orders;
06

JOINs

INNER JOIN

INNER JOIN retorna linhas só quando há correspondência nas duas tabelas — linhas sem correspondência de qualquer lado são descartadas. JOIN é abreviação de INNER JOIN. USING (col) é uma alternativa concisa a ON a.col = b.col quando o nome da coluna é idêntico e aparece uma vez no resultado. INNER JOIN é o tipo de join padrão e mais comum para relacionar tabelas normalizadas.

mysql
# only matching rows from both tables
SELECT u.username, o.id AS order_id, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE o.paid = 1;

# join three tables
SELECT u.username, o.id, oi.product_id, oi.qty
FROM users u
JOIN orders o        ON o.user_id   = u.id
JOIN order_items oi  ON oi.order_id = o.id;

# USING clause when join columns share a name
SELECT u.username, o.id
FROM users u
JOIN orders o USING (user_id);

LEFT e RIGHT JOIN

LEFT JOIN mantém toda linha da tabela à esquerda e preenche o lado direito com NULL quando não há correspondência. O truque WHERE o.id IS NULL (anti-join) acha linhas da esquerda sem correspondência à direita — frequentemente mais claro e rápido que NOT IN, especialmente com NULLs. RIGHT JOIN é simétrico; a maioria das pessoas reordena as tabelas e usa LEFT JOIN por legibilidade. NULLs na chave de join nunca casam.

mysql
# LEFT JOIN: all left rows, with NULLs where no match
SELECT u.username, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

# find users with NO orders (anti-join pattern)
SELECT u.username
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

# RIGHT JOIN is the mirror (rarely used; swap tables instead)
SELECT u.username, o.id
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;

CROSS JOIN

CROSS JOIN retorna o produto cartesiano — toda combinação das linhas das duas tabelas. Útil para gerar combinações ou semear matrizes de dimensão, mas produz N x M linhas, sendo caro em tabelas grandes. Uma vírgula entre tabelas (FROM a, b) é um CROSS JOIN implícito. O padrão de CTE recursivo é a forma padrão de gerar uma tabela de sequência/dimensão no MySQL 8.0+.

mysql
# Cartesian product: every row of A paired with every row of B
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

# same effect with a comma join
SELECT s.size, c.color FROM sizes s, colors c;

# generate a series of dates (no built-in GENERATE_SERIES)
WITH RECURSIVE nums(n) AS (
  SELECT 1 UNION ALL SELECT n+1 FROM nums WHERE n < 7
)
SELECT DATE_ADD('2024-01-01', INTERVAL n-1 DAY) AS day
FROM nums;

# build a matrix of all category x region combinations
SELECT cat.name, reg.name
FROM categories cat CROSS JOIN regions reg;

Self Join

Um self join referencia a mesma tabela duas vezes com apelidos diferentes, para relacionar linhas dentro dela — clássico para hierarquias de gerente/funcionário ou listas de adjacência. A condição a.id < b.id evita pares simétricos duplicados. Para hierarquias profundas use um CTE recursivo (MySQL 8.0+), que percorre a árvore sem limite fixo de profundidade e pode construir saída de árvore indentada com REPEAT/CONCAT.

mysql
# employees and their managers (same table twice)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

# find pairs of users in the same city
SELECT a.username AS user_a, b.username AS user_b, a.city
FROM users a
JOIN users b ON a.city = b.city AND a.id < b.id;

# hierarchical tree with recursive CTE (8.0+)
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT CONCAT(REPEAT('-- ', depth-1), name) AS tree
FROM org;

Alternativa ao FULL OUTER JOIN

MySQL não implementa FULL OUTER JOIN. A emulação padrão é um LEFT JOIN UNION um RIGHT JOIN — UNION deduplica, então cada linha com correspondência aparece uma vez e linhas sem correspondência de qualquer lado aparecem com NULLs do outro. Para resultados grandes, prefira UNION ALL com uma estratégia de deduplicação, já que o DISTINCT implícito do UNION é caro. Se só precisa das linhas sem correspondência, use dois anti-joins.

mysql
# MySQL has no FULL OUTER JOIN — emulate with LEFT + UNION + RIGHT
SELECT u.username, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
UNION
SELECT u.username, o.id AS order_id
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;

# the UNION removes duplicates; use UNION ALL to keep them
# for an anti-union (mismatches only) add WHERE o.id IS NULL
#   or u.id IS NULL in each branch

NATURAL JOIN e USING

NATURAL JOIN é frágil — une em cada nome de coluna compartilhado, então adicionar uma coluna pode mudar os resultados silenciosamente; evite em produção. USING (col) é mais seguro e colapsa a coluna de join numa única cópia na saída. STRAIGHT_JOIN força a ordem de tabelas que o otimizador usa e é uma sobrescrita manual para planos ruins; remova-o quando o problema real for corrigido. Prefira ON explícito, por clareza.

mysql
# NATURAL JOIN matches all same-named columns automatically
SELECT * FROM users NATURAL JOIN profiles;

# USING matches specific shared columns, dedups them in output
SELECT u.username, user_id
FROM users u
JOIN orders o USING (user_id);

# STRAIGHT_JOIN forces the optimizer to join left-to-right
SELECT STRAIGHT_JOIN u.username, o.id
FROM users u
JOIN orders o ON o.user_id = u.id;
07

Agregação e Agrupamento

Funções de Agregação

Funções de agregação colapsam muitas linhas numa: COUNT, SUM, AVG, MIN, MAX. COUNT(*) conta linhas; COUNT(col) conta valores não nulos de col; COUNT(DISTINCT col) conta valores únicos. AVG ignora NULLs (não zero), o que pode enviesar resultados — use COALESCE(col,0) se quiser NULL tratado como zero. Agregados sem GROUP BY retornam uma única linha mesmo numa tabela vazia.

mysql
SELECT
  COUNT(*)               AS row_count,
  COUNT(email)           AS emails,        -- ignores NULLs
  COUNT(DISTINCT country) AS countries,
  MIN(created_at)        AS first_user,
  MAX(created_at)        AS last_user,
  AVG(age)               AS avg_age,
  SUM(balance)           AS total_balance
FROM users;

# aggregate over a filtered set
SELECT SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid';

GROUP BY

GROUP BY colapsa linhas que compartilham os valores das colunas agrupadas numa linha de saída, com agregados computados por grupo. MySQL 8.0 habilita ONLY_FULL_GROUP_BY por padrão, que rejeita consultas que selecionam colunas fora do GROUP BY (versões anteriores retornavam uma linha arbitrária — um bug comum). Agrupe por expressões ou apelidos. Para relatórios de séries temporais, agrupe por YEAR()/MONTH()/DATE() de um timestamp.

mysql
# one row per country
SELECT country, COUNT(*) AS users, AVG(age) AS avg_age
FROM users
GROUP BY country;

# group by multiple columns
SELECT country, city, COUNT(*) AS users
FROM users
GROUP BY country, city
ORDER BY country, users DESC;

# group by expression
SELECT YEAR(created_at) AS yr, MONTH(created_at) AS m, COUNT(*)
FROM users
GROUP BY yr, m;

# MySQL 8.0 enforces ONLY_FULL_GROUP_BY:
# every non-aggregated SELECT column must be in GROUP BY

HAVING

HAVING filtra o resultado da agregação, enquanto WHERE filtra linhas de entrada antes do agrupamento — então HAVING pode referenciar agregados (SUM, COUNT) e WHERE não. Ponha filtros no nível de linha no WHERE, por eficiência (reduzem as linhas antes do agrupamento). HAVING aceita apelidos no MySQL (HAVING usuarios > 100). Uma consulta pode usar ambos: WHERE estreita linhas, GROUP BY as agrupa em baldes, HAVING estreita os baldes.

mysql
# HAVING filters groups (after aggregation); WHERE filters rows (before)
SELECT country, COUNT(*) AS users
FROM users
GROUP BY country
HAVING COUNT(*) > 100
ORDER BY users DESC;

# filter on an aggregate
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 1000;

# combine WHERE and HAVING
SELECT country, COUNT(*) AS users, AVG(age) AS avg_age
FROM users
WHERE status = 'active'
GROUP BY country
HAVING avg_age >= 30;

GROUP BY ROLLUP

WITH ROLLUP adiciona linhas super-agregadas extras para cada nível de agrupamento mais um total geral, em que a coluna agrupada vira NULL. GROUPING(col) retorna 1 numa linha de subtotal/total, para que você possa rotular NULLs como 'TUDO'. ROLLUP funciona da esquerda para a direita nas colunas do GROUP BY. Não há WITH CUBE no MySQL — emule com UNION ALL de múltiplas consultas ROLLUP se precisar de toda combinação de dimensões.

mysql
# ROLLUP adds subtotals and a grand total row
SELECT country, city, COUNT(*) AS users
FROM users
GROUP BY country, city WITH ROLLUP;

# output includes:
#   country | city    | users
#   'US'    | 'NYC'   | 120
#   'US'    | NULL    | 200   <- US subtotal
#   NULL    | NULL    | 950   <- grand total

# identify super-aggregate rows with GROUPING()
SELECT
  IF(GROUPING(country)=1,'ALL',country) AS country,
  IF(GROUPING(city)=1,'ALL',city)       AS city,
  COUNT(*) AS users
FROM users
GROUP BY country, city WITH ROLLUP;

GROUP_CONCAT

GROUP_CONCAT é a função de agregação de strings do MySQL — concatena os valores de um grupo numa única string. Use DISTINCT para deduplicar, ORDER BY para ordenar e SEPARATOR para trocar o delimitador (padrão ','). O resultado é limitado por group_concat_max_len (padrão 1024 bytes); aumente para listas mais longas. Outros bancos chamam isto de LISTAGG/STRING_AGG — GROUP_CONCAT é o equivalente MySQL.

mysql
# concatenate grouped values into one string
SELECT country,
  GROUP_CONCAT(username) AS all_names
FROM users
GROUP BY country;

# order within the list and deduplicate
SELECT user_id,
  GROUP_CONCAT(DISTINCT tag ORDER BY tag SEPARATOR ',') AS tags
FROM post_tags
GROUP BY user_id;

# the default separator is ',', max length is 1024 by default
# raise the limit:
# SET SESSION group_concat_max_len = 1000000;

SELECT department,
  GROUP_CONCAT(name ORDER BY salary DESC SEPARATOR ' | ') AS ranking
FROM employees
GROUP BY department;

WITH ROLLUP vs Agregados de Janela

ROLLUP encolhe o resultado adicionando linhas de subtotal, enquanto um agregado de janela (OVER) mantém a mesma contagem de linhas e acrescenta o agregado como coluna extra — então você pode ver detalhe e totais juntos. Use ROLLUP para relatórios resumidos e funções de janela quando precisar de contexto por linha junto com o agregado. Totais acumulados são um caso de uso clássico de SUM OVER (ORDER BY ...).

mysql
# ROLLUP gives fewer rows (subtotals mixed into the result)
SELECT country, city, COUNT(*) AS users
FROM users
GROUP BY country, city WITH ROLLUP;

# a window aggregate keeps every detail row AND adds the total
SELECT
  country, city,
  COUNT(*)              AS users_in_city,
  COUNT(*) OVER (PARTITION BY country) AS users_in_country,
  COUNT(*) OVER ()                      AS users_total
FROM users;

# running total over time
SELECT created_at, amount,
  SUM(amount) OVER (ORDER BY created_at) AS running_total
FROM daily_sales;
08

Subconsultas e CTEs

Subconsulta Escalar

Uma subconsulta escalar retorna um único valor (uma linha, uma coluna) e pode ser usada em qualquer lugar onde uma expressão seja válida — lista do SELECT, WHERE, HAVING. Se não retorna linhas, o valor é NULL. O otimizador frequentemente transforma uma subconsulta escalar correlacionada num join (materialização de subconsulta). Por clareza e às vezes desempenho, um CTE ou JOIN é preferível quando a mesma subconsulta é referenciada repetidamente.

mysql
# returns a single value
SELECT username, balance,
  (SELECT AVG(balance) FROM users) AS avg_balance
FROM users
WHERE balance > (SELECT AVG(balance) FROM users);

# use in SELECT list, WHERE, HAVING, etc.
SELECT product, price,
  price - (SELECT MIN(price) FROM products) AS above_min
FROM products;

# a scalar subquery must return exactly one row, one column

Subconsultas IN / ANY / ALL

IN casa qualquer valor retornado pela subconsulta; NOT IN é o complemento, mas é envenenado por NULLs — se a subconsulta retorna um NULL, NOT IN não retorna linha nenhuma, então sempre exclua NULLs ou use NOT EXISTS. ALL é verdadeiro quando a comparação vale para cada valor retornado; ANY/SOME quando vale para pelo menos um. O otimizador frequentemente reescreve estes em semi-joins.

mysql
# IN: value matches any row of the subquery
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE status='active');

# NOT IN with NULLs is dangerous — prefer NOT EXISTS
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);

# ANY / SOME / ALL with comparisons
SELECT * FROM products
WHERE price > ALL (SELECT price FROM products WHERE category='toys');

SELECT * FROM products
WHERE price > ANY (SELECT price FROM products WHERE category='toys');

EXISTS e NOT EXISTS

EXISTS testa a existência de linhas sem retornar dados — para a varredura na primeira correspondência, então é eficiente para checagens de presença. A subconsulta é correlacionada (referencia a consulta externa). NOT EXISTS é a forma segura contra NULL de achar linhas sem correspondência e é geralmente preferido a NOT IN. SELECT 1 dentro de EXISTS é convenção; a lista de colunas é irrelevante. O otimizador frequentemente transforma EXISTS num semi-join.

mysql
# EXISTS: true if the subquery returns any rows
SELECT u.username
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

# NOT EXISTS: the standard anti-semi-join (NULL-safe)
SELECT u.username
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

# correlated: the subquery references the outer row (u.id)
# EXISTS stops at the first matching row — efficient for presence checks

Tabelas Derivadas

Uma tabela derivada é uma subconsulta na cláusula FROM — materializa um resultado intermediário do qual você pode selecionar ou unir. Sempre dê-lhe um apelido. MySQL 8.0 pode mesclar muitas tabelas derivadas na consulta externa (derived_merge) para planos melhores, mas agregações complexas ou LIMIT geralmente forçam a materialização. Por legibilidade e reuso dentro da consulta, prefira um CTE nomeado (WITH) a uma tabela derivada inline.

mysql
# a subquery in the FROM clause is a derived table
SELECT t.country, t.users
FROM (
  SELECT country, COUNT(*) AS users
  FROM users
  GROUP BY country
) t
WHERE t.users > 50;

# join a derived table
SELECT u.username, agg.order_count
FROM users u
JOIN (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY user_id
) agg ON agg.user_id = u.id;

# derived tables must be aliased

Subconsulta Correlacionada

Uma subconsulta correlacionada referencia uma coluna da consulta externa, então é logicamente reavaliada para cada linha externa — o que pode ser lento em conjuntos grandes. O otimizador pode transformá-la num join ou usar um cache (cache de subconsulta) para mitigar. Para problemas de rank/top-N (ex.: enésimo salário mais alto), uma função de janela como DENSE_RANK() OVER (ORDER BY salario DESC) é mais clara e rápida — recorra a ela no MySQL 8.0+.

mysql
# the subquery references the outer row (u.id) and runs per row
SELECT u.username,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

# correlated subquery in WHERE (nth-highest salary classic)
SELECT e1.name, e1.salary
FROM employees e1
WHERE 2 = (
  SELECT COUNT(DISTINCT e2.salary)
  FROM employees e2
  WHERE e2.salary > e1.salary
);

# prefer a window function for rank-style problems in 8.0+

Expressões de Tabela Comuns (CTE)

Um CTE (cláusula WITH) nomeia uma subconsulta por legibilidade e reuso dentro de uma instrução; MySQL 8.0+ suporta. RECURSIVE habilita CTEs autorreferentes — essenciais para travessia de árvore e geração de sequência (UNION ALL para recursar, UNION para deduplicar). Um CTE recursivo precisa de um caso base, um braço recursivo e uma salvaguarda de terminação. Múltiplos CTEs são separados por vírgula e podem referenciar os anteriores no mesmo WITH.

mysql
# a named CTE (WITH) — readable, can be referenced multiple times
WITH active_users AS (
  SELECT id, username FROM users WHERE status = 'active'
)
SELECT au.username, COUNT(o.id) AS orders
FROM active_users au
LEFT JOIN orders o ON o.user_id = au.id
GROUP BY au.username;

# RECURSIVE CTE: walk a hierarchy or generate a series
WITH RECURSIVE nums(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;

# multiple CTEs, comma-separated
WITH a AS (SELECT ...), b AS (SELECT ...)
SELECT * FROM a JOIN b USING (id);
09

Índices

Criar Índice

Um índice é uma estrutura de dados (B+Tree no InnoDB) que acelera buscas e ordenações ao custo de overhead de escrita e armazenamento. Crie índices em colunas usadas em WHERE, JOIN e ORDER BY. Índices UNIQUE também impõem uma restrição de unicidade. Índices de prefixo em colunas de texto longas economizam espaço, mas não podem ser usados para varreduras cobertoras nem ORDER BY. Toda chave primária é clusterizada no InnoDB — índices secundários guardam o valor da PK como ponteiro de linha.

mysql
# single-column index
CREATE INDEX idx_email ON users(email);

# unique index (enforces uniqueness, allows fast lookup)
CREATE UNIQUE INDEX uq_username ON users(username);

# create with a table
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255),
  UNIQUE KEY uq_email (email)
);

# full-table prefix index (limited length)
CREATE INDEX idx_name ON users(last_name(20));

Índice Composto

Índices compostos são ordenados pela coluna mais à esquerda primeiro, então só ajudam consultas que filtram num prefixo mais à esquerda da lista de colunas — a ordem das colunas é crítica. Ponha a coluna de igualdade mais seletiva primeiro, depois intervalos. Um índice cobertor contém toda coluna de que a consulta precisa, permitindo uma varredura só de índice que pula a busca na tabela por completo (EXPLAIN mostra 'Using index'). Não indexe demais — cada índice retarda escritas.

mysql
# multi-column index (column order matters!)
CREATE INDEX idx_last_first ON users(last_name, first_name);

# supports these lookups (leftmost prefix rule):
#   WHERE last_name = 'X'                       -> uses index
#   WHERE last_name = 'X' AND first_name = 'Y'  -> uses index
#   WHERE first_name = 'Y'                      -> CANNOT use index

# covering index: all needed columns are in the index
CREATE INDEX idx_cover ON orders(user_id, status, amount);
SELECT user_id, SUM(amount) FROM orders
WHERE user_id = 5 AND status = 'paid' GROUP BY user_id;
# ^ index-only scan, no table lookup needed

Índice de Texto Completo

Índices FULLTEXT habilitam busca de texto em linguagem natural e booleana, muito melhor que LIKE '%palavra%'. MATCH ... AGAINST retorna uma pontuação de relevância. O modo booleano suporta operadores: +exigir, -excluir, *curinga, >aumentar, ~diminuir. Requer InnoDB (5.6+) ou MyISAM e um comprimento mínimo de palavra (ft_min_word_len / innodb_ft_min_token_size, padrão 3). Para busca séria em escala, considere um engine dedicado como Elasticsearch.

mysql
CREATE TABLE articles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT KEY ft_title_body (title, body)
) ENGINE=InnoDB;

# natural-language search
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST ('database performance');

# boolean mode: +require -exclude ~relax
SELECT id, title
FROM articles
WHERE MATCH(title, body)
  AGAINST ('+MySQL -Oracle >index' IN BOOLEAN MODE);

# query expansion (second pass adds related terms)
SELECT id FROM articles
WHERE MATCH(title, body) AGAINST ('database' WITH QUERY EXPANSION);

Índice Espacial

Índices espaciais (R-tree) aceleram consultas geográficas em colunas GEOMETRY/POINT/POLYGON. MySQL 8.0 padronizou SRID 4326 (coordenadas GPS WGS 84) e adicionou ST_Distance_Sphere para distâncias reais em metros. A coluna deve ser NOT NULL. Use ST_Within/ST_Contains/ST_Distance para filtrar. Sempre expresse coordenadas como (longitude, latitude) para casar com a convenção X/Y. Índices espaciais tornam consultas de raio muito mais rápidas.

mysql
CREATE TABLE places (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  location POINT NOT NULL SRID 4326,
  SPATIAL KEY sp_location (location)
) ENGINE=InnoDB;

# insert a point (longitude, latitude)
INSERT INTO places (name, location)
VALUES ('HQ', ST_PointFromText('POINT(116.40 39.90)', 4326));

# find points within a bounding distance
SELECT name, ST_Distance_Sphere(location,
  ST_PointFromText('POINT(116.40 39.90)', 4326)) AS meters
FROM places
WHERE ST_Within(location,
  ST_Buffer(ST_PointFromText('POINT(116.40 39.90)', 4326), 0.01));

Gestão de Índices

SHOW INDEX lista os índices de uma tabela com ordem de colunas e cardinalidade (estimativa de valores distintos). DROP INDEX remove um índice; renomear (8.0+) evita descartar+reconstruir. OPTIMIZE TABLE reconstrói a tabela para recuperar espaço fragmentado e atualizar estatísticas de índice — pesado em travas, então agende em tráfego baixo. Estatísticas velhas podem enganar o otimizador; rode ANALYZE TABLE para atualizá-las após grandes cargas de dados.

mysql
# show indexes on a table
SHOW INDEX FROM users;

# drop an index
DROP INDEX idx_email ON users;
ALTER TABLE users DROP INDEX uq_username;

# rename an index (8.0+)
ALTER TABLE users RENAME INDEX idx_email TO idx_user_email;

# rebuild all indexes on a table
ALTER TABLE users ENGINE=InnoDB;
OPTIMIZE TABLE users;

# check index cardinality
SHOW INDEX FROM users;
SELECT index_name, cardinality
FROM information_schema.statistics
WHERE table_schema='mydb' AND table_name='users';

Índices Invisíveis e Hints

Índices invisíveis (8.0+) permitem testar a remoção de um índice com segurança — o otimizador o ignora, mas as escritas o mantêm atualizado, então você pode reativá-lo instantaneamente se as consultas regredirem; descarte-o quando confiante. Hints de otimizador (/*+ ... */) são preferidos à velha sintaxe USE/FORCE/IGNORE INDEX e afetam só a instrução marcada. Use hints com parcimônia — são um band-aid para distribuição ruim de dados ou estatísticas ausentes.

mysql
# make an index invisible (8.0+): optimizer ignores it, still maintained
ALTER TABLE users ALTER INDEX idx_email SET INVISIBLE;
# ... observe query plans, then drop or restore:
ALTER TABLE users ALTER INDEX idx_email SET VISIBLE;

# optimizer hints (8.0+) to force / avoid indexes
SELECT /*+ INDEX(u idx_email) */ * FROM users u WHERE email LIKE 'a%';
SELECT /*+ NO_INDEX(u idx_email) */ * FROM users u;
SELECT /*+ FORCE INDEX(u idx_email) */ * FROM users u;

# index merge / IGNORE_INDEX legacy hint
SELECT * FROM users USE INDEX (idx_email) WHERE email = '[email protected]';
SELECT * FROM users IGNORE INDEX (idx_email) WHERE email = '[email protected]';
10

Views

Criar View

Uma view é uma consulta guardada que se comporta como tabela virtual — simplifica consultas complexas, abstrai mudanças de schema e impõe visibilidade de linhas/colunas. CREATE OR REPLACE atualiza a definição num único passo. Views não guardam dados (reexecutam a consulta subjacente a cada vez, a menos que materializadas), então não adicionam custo de escrita, mas tampouco aceleração de leitura. Views são mescladas na consulta quando possível.

mysql
# a view is a stored SELECT
CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE status = 'active';

# query it like a table
SELECT * FROM active_users WHERE username LIKE 'a%';

# create or replace, with explicit columns
CREATE OR REPLACE VIEW user_summary (username, order_count, total) AS
SELECT u.username, COUNT(o.id), COALESCE(SUM(o.amount), 0)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.username;

# list and inspect views
SHOW FULL TABLES WHERE Table_type = 'VIEW';
SHOW CREATE VIEW active_users\G

Views Atualizáveis

Uma view sobre uma única tabela base sem agregados/distinct é atualizável — INSERT/UPDATE/DELETE se propagam à tabela subjacente. Por padrão, um UPDATE que move uma linha para fora da cláusula WHERE da view tem sucesso (a linha simplesmente desaparece da view); WITH CHECK OPTION bloqueia tais mudanças, para que as linhas nunca saiam do escopo da view. Views com JOINs, GROUP BY, DISTINCT ou subconsultas geralmente não são atualizáveis.

mysql
# a view over a single base table is updatable
CREATE VIEW active_users AS
SELECT id, username, email, status
FROM users
WHERE status = 'active';

# INSERT through the view inserts into the base table
INSERT INTO active_users (id, username, email, status)
VALUES (10, 'newbie', '[email protected]', 'active');

# UPDATE through the view only touches visible rows
UPDATE active_users SET email = '[email protected]' WHERE id = 10;

# WITH CHECK OPTION prevents inserts/updates that leave the view
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email, status FROM users WHERE status='active'
WITH CHECK OPTION;

CHECK OPTION da View

CHECK OPTION impõe que as linhas alteradas através de uma view permaneçam visíveis a ela. LOCAL verifica só o WHERE da própria view; CASCADED (o padrão) também impõe as cláusulas WHERE de quaisquer views subjacentes referenciadas. Use CASCADED ao empilhar views, para manter toda a cadeia consistente; use LOCAL só quando deliberadamente quiser relaxar o filtro de uma view subjacente.

mysql
# LOCAL: only the defining view's WHERE is enforced
CREATE VIEW v_paid AS
  SELECT * FROM orders WHERE status='paid' WITH LOCAL CHECK OPTION;

# CASCADED (default): all underlying views' checks are enforced too
CREATE VIEW v_paid_large AS
  SELECT * FROM v_paid WHERE amount > 100
  WITH CASCADED CHECK OPTION;

# an insert that violates v_paid's filter:
INSERT INTO v_paid_large (id, status, amount) VALUES (1, 'unpaid', 200);
# CASCADED -> rejected (status would leave v_paid)
# LOCAL    -> accepted (only v_paid_large's filter checked)

Gerenciar Views

ALTER VIEW redefine uma view existente sem descartá-la (preservando concessões). DROP VIEW IF EXISTS evita erro quando a view está ausente. RENAME TABLE também funciona para views, mas é incomum. information_schema.views expõe a definição de cada view, se é atualizável e o tipo de segurança — útil para auditoria. Uma view depende das tabelas base; descartar uma tabela base deixa a view inválida até ser recriada.

mysql
# alter a view's definition (CREATE OR REPLACE is simpler)
ALTER VIEW active_users AS
  SELECT id, username FROM users WHERE status='active';

# drop a view
DROP VIEW IF EXISTS active_users;

# drop multiple views
DROP VIEW IF EXISTS v1, v2, v3;

# rename a view (no RENAME VIEW; recreate or use a table rename trick)
RENAME TABLE old_view TO new_view;

# view metadata
SELECT table_name, view_definition, is_updatable
FROM information_schema.views
WHERE table_schema = 'mydb';

Segurança e Determinismo de Views

SQL SECURITY DEFINER roda a view com os privilégios do definidor — útil para expor dados filtrados a usuários sem acesso à tabela base (ex.: uma view de segurança no nível de linha). INVOKER verifica os privilégios do chamador. ALGORITHM MERGE dobra a view na consulta (mais rápido, permite views atualizáveis); TEMPTABLE materializa-a primeiro (necessário para algumas construções, mas perde a atualizabilidade e pode ser mais lento). UNDEFINED deixa o MySQL escolher.

mysql
# DEFINER (default): runs with the view creator's privileges
CREATE VIEW secret_emails SQL SECURITY DEFINER AS
  SELECT email FROM users;

# INVOKER: runs with the querying user's privileges
CREATE VIEW my_emails SQL SECURITY INVOKER AS
  SELECT email FROM users WHERE id = CURRENT_USER_ID();

# ALGORITHM: MERGE (preferred) vs TEMPTABLE vs UNDEFINED
CREATE ALGORITHM=MERGE VIEW v_active AS
  SELECT * FROM users WHERE status='active';
11

Stored Procedures

Criar e Chamar Procedure

Uma stored procedure é um bloco de SQL nomeado e pré-compilado. Como procedures podem conter ponto e vírgula, você muda o DELIMITER para que o CREATE inteiro rode como uma instrução, e depois o reseta. CALL executa uma procedure. Procedures não retornam um valor diretamente, mas podem retornar conjuntos de resultados e modificar parâmetros OUT. Rodam no lado do servidor, cortando idas e voltas de rede para lógica de múltiplos passos, mas são mais difíceis de versionar e testar que código da aplicação.

mysql
DELIMITER //
CREATE PROCEDURE get_user_by_id(IN p_id INT)
BEGIN
  SELECT id, username, email FROM users WHERE id = p_id;
END //
DELIMITER ;

# call it
CALL get_user_by_id(42);

# drop it
DROP PROCEDURE IF EXISTS get_user_by_id;

# show the definition
SHOW CREATE PROCEDURE get_user_by_id\G

Parâmetros IN / OUT / INOUT

Parâmetros são IN (entrada somente leitura), OUT (escritos de volta ao chamador via variável de sessão) ou INOUT (ambos). SELECT ... INTO atribui um resultado escalar de consulta a uma variável. O exemplo envolve um débito/crédito numa transação com SELECT FOR UPDATE para travar linhas e prevenir atualizações perdidas. Use parâmetros OUT para retornar códigos de status; conjuntos de resultados são retornados diretamente quando você faz SELECT dentro da procedure.

mysql
DELIMITER //
CREATE PROCEDURE transfer(
  IN  p_from   INT,
  IN  p_to     INT,
  IN  p_amount DECIMAL(10,2),
  OUT p_result VARCHAR(50)
)
BEGIN
  DECLARE v_bal DECIMAL(10,2);
  START TRANSACTION;
  SELECT balance INTO v_bal FROM accounts WHERE id = p_from FOR UPDATE;
  IF v_bal >= p_amount THEN
    UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
    COMMIT;
    SET p_result = 'OK';
  ELSE
    ROLLBACK;
    SET p_result = 'INSUFFICIENT_FUNDS';
  END IF;
END //
DELIMITER ;

# call with a session variable for the OUT value
CALL transfer(1, 2, 50.00, @res);
SELECT @res;

Variáveis e Fluxo de Controle

Programas armazenados suportam variáveis locais (DECLARE, com escopo em BEGIN...END), variáveis de usuário (@var, escopo de sessão) e variáveis de sistema. O fluxo de controle inclui IF/ELSEIF/ELSE, CASE e três loops: LOOP (com LEAVE/ITERATE), WHILE (condição primeiro) e REPEAT (condição por último). Sempre nomeie os loops ao aninhar, para que LEAVE mire o certo. SET atribui um valor; SELECT ... INTO copia um escalar de consulta para uma variável.

mysql
DELIMITER //
CREATE PROCEDURE classify(IN p_age INT, OUT p_label VARCHAR(20))
BEGIN
  DECLARE v_count INT DEFAULT 0;

  # IF / ELSEIF / ELSE
  IF p_age < 13 THEN
    SET p_label = 'child';
  ELSEIF p_age < 20 THEN
    SET p_label = 'teen';
  ELSE
    SET p_label = 'adult';
  END IF;

  # simple loop with a counter
  simple_loop: LOOP
    SET v_count = v_count + 1;
    IF v_count >= 5 THEN LEAVE simple_loop; END IF;
  END LOOP;

  # WHILE / REPEAT alternatives
  # WHILE cond DO ... END WHILE;
  # REPEAT ... UNTIL cond END REPEAT;

  # CASE statement
  CASE p_label
    WHEN 'child' THEN SET p_label = CONCAT(p_label, '!');
    ELSE SET p_label = CONCAT(p_label, '.');
  END CASE;
END //
DELIMITER ;

Cursores

Um cursor permite que uma stored procedure itere linha a linha sobre um conjunto de resultados. Declare o cursor primeiro, depois um CONTINUE HANDLER FOR NOT FOUND para detectar o esgotamento (defina uma flag e LEAVE o loop). FETCH recupera uma linha para variáveis. Cursores são somente leitura e só para frente no MySQL. Processar linha a linha é lento comparado ao SQL baseado em conjunto — prefira um único UPDATE/INSERT ... SELECT quando possível; use cursores só quando a lógica for genuinamente sequencial.

mysql
DELIMITER //
CREATE PROCEDURE log_all_users()
BEGIN
  DECLARE v_id INT;
  DECLARE v_name VARCHAR(50);
  DECLARE done INT DEFAULT 0;

  # declare the cursor BEFORE handlers
  DECLARE cur CURSOR FOR
    SELECT id, username FROM users WHERE status='active';

  # continue handler for end of cursor -> set done=1
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_id, v_name;
    IF done THEN LEAVE read_loop; END IF;
    INSERT INTO audit_log(user_id, action) VALUES (v_id, CONCAT('seen:', v_name));
  END LOOP;
  CLOSE cur;
END //
DELIMITER ;

CALL log_all_users();

Handlers de Erro

Handlers capturam erros dentro de programas armazenados: CONTINUE roda o corpo do handler e depois retoma após a instrução que falhou; EXIT roda o corpo e sai do bloco BEGIN...END; UNDO desfaz (obsoleto). Capture SQLEXCEPTION para qualquer erro, SQLWARNING para avisos, ou SQLSTATE/errno específicos (ex.: 1062 chave duplicada). RESIGNAL relança o erro atual após a limpeza. Condições nomeadas (CONDITION FOR) tornam os handlers autoexplicativos.

mysql
DELIMITER //
CREATE PROCEDURE safe_insert(IN p_email VARCHAR(255))
BEGIN
  # SQLEXCEPTION catches any error; SQLSTATE/errno catch specific ones
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;  -- re-raise so the caller sees the error
  END;

  DECLARE CONTINUE HANDLER FOR 1062  -- duplicate key
  BEGIN
    SELECT 'Duplicate skipped' AS msg;
  END;

  START TRANSACTION;
  INSERT INTO users (username, email) VALUES ('x', p_email);
  INSERT INTO stats (email) VALUES (p_email);
  COMMIT;
END //
DELIMITER ;

# named conditions make handlers readable
DECLARE dup_key CONDITION FOR 1062;
DECLARE CONTINUE HANDLER FOR dup_key SELECT 'dup';

Metadados de Procedures

SHOW PROCEDURE STATUS lista procedures; SHOW CREATE PROCEDURE retorna o corpo completo. information_schema.routines dá metadados mais ricos, incluindo determinismo e tipo de segurança. Marque uma função como DETERMINISTIC (READS SQL DATA / NO SQL) se sua saída depender só das entradas — exigido para logging binário e replicação de funções. Procedures não precisam da flag determinística. Use estas views para auditar e inventariar rotinas.

mysql
# list procedures in a database
SHOW PROCEDURE STATUS WHERE Db = 'mydb';

# show definition
SHOW CREATE PROCEDURE get_user_by_id\G

# query information_schema for routines
SELECT routine_name, routine_type, created, last_altered,
       is_deterministic, security_type
FROM information_schema.routines
WHERE routine_schema = 'mydb';

# list stored functions too
SELECT routine_name, routine_type
FROM information_schema.routines
WHERE routine_schema = 'mydb' AND routine_type='FUNCTION';
12

Funções

Funções de String

LENGTH conta bytes, CHAR_LENGTH conta caracteres (importante para utf8mb4 multibyte). SUBSTRING é indexado a partir de 1; início negativo conta do fim. CONCAT retorna NULL se qualquer argumento for NULL — use CONCAT_WS para pular NULLs. SUBSTRING_INDEX divide num delimitador e mantém as primeiras N partes (N negativo mantém da direita). TRIM tem variantes BOTH/LEADING/TRAILING e pode aparar qualquer caractere, não só espaços.

mysql
# length & case
SELECT LENGTH('hello'), CHAR_LENGTH('héllo');  -- bytes vs chars
SELECT UPPER('abc'), LOWER('ABC');

# substring & position
SELECT SUBSTRING('MySQL', 3);           -- 'SQL'
SELECT SUBSTRING('MySQL', 1, 2);        -- 'My'
SELECT INSTR('MySQL', 'SQL');           -- 3
SELECT LOCATE('SQL', 'MySQL', 1);       -- 3

# trim & pad
SELECT TRIM('  hi  '), LTRIM('  hi'), RTRIM('hi  ');
SELECT LPAD('5', 3, '0'), RPAD('5', 3, '-');  -- 005, 5--

# split & replace
SELECT REPLACE('a-b-c', '-', '/');
SELECT SUBSTRING_INDEX('a,b,c', ',', 2);       -- 'a,b'

# concatenate with NULL-safe separator
SELECT CONCAT_WS(',', 'a', NULL, 'b');         -- 'a,b'

Funções Numéricas

ROUND arredonda metade para longe do zero; TRUNCATE corta dígitos sem arredondar. MOD e % são intercambiáveis. RAND() retorna um float em [0,1); ORDER BY RAND() é uma forma conveniente, mas cara, de amostrar linhas, porque ordena o conjunto de resultados inteiro — para tabelas grandes, use um intervalo de PK aleatório ou amostragem de reservatório em vez disso. FORMAT adiciona separadores de milhar e arredonda às decimais dadas, retornando uma string.

mysql
# rounding
SELECT ROUND(2.567, 1);   -- 2.6
SELECT CEIL(2.1), FLOOR(2.9);   -- 3, 2
SELECT TRUNCATE(2.567, 1);      -- 2.5

# modular & power
SELECT MOD(17, 5), 17 % 5;      -- 2, 2
SELECT POWER(2, 10);            -- 1024
SELECT SQRT(16);                -- 4

# random
SELECT RAND();                  -- 0..1 float
SELECT FLOOR(RAND() * 100);     -- 0..99 integer
SELECT * FROM users ORDER BY RAND() LIMIT 5;  -- random sample (slow!)

# formatting
SELECT FORMAT(1234567.891, 2);  -- '1,234,567.89'
SELECT ABS(-5), SIGN(-5);       -- 5, -1

Funções de Data e Hora

NOW() retorna a data/hora atual numa única chamada (consistente numa instrução); CURDATE/CURTIME retornam partes de data/hora. DATE_ADD/SUB com INTERVAL é a aritmética de datas canônica — trata corretamente a virada de mês/ano. DATEDIFF retorna dias (data1 - data2); TIMEDIFF retorna um TIME. DATE_FORMAT e STR_TO_DATE usam especificadores %Y %m %d %H %i %s (note o i minúsculo para minutos, s para segundos).

mysql
# current values
SELECT NOW(), CURDATE(), CURTIME(), UTC_TIMESTAMP();

# parts of a date
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()), DAYNAME(NOW());
SELECT WEEK(NOW()), WEEKDAY(NOW()), DAYOFWEEK(NOW());

# arithmetic
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
SELECT DATE_SUB('2024-01-31', INTERVAL 1 MONTH);  -- 2023-12-31
SELECT DATEDIFF('2024-12-31', '2024-01-01');      -- 365 days
SELECT TIMEDIFF('18:00:00', '09:30:00');          -- 08:30:00

# formatting
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
SELECT STR_TO_DATE('31/12/2024', '%d/%m/%Y');

Funções Personalizadas

Uma stored function retorna um único valor escalar e pode ser usada dentro de expressões (diferente de procedures, que você CALL). Precisa declarar características: DETERMINISTIC (mesma entrada sempre produz mesma saída, exigido para replicação baseada em instrução) e uma de READS SQL DATA / NO SQL / MODIFIES SQL DATA. Funções não podem retornar conjuntos de resultados nem usar controle de transação. A combinação NO SQL + DETERMINISTIC é a mais segura para computação pura.

mysql
DELIMITER //
CREATE FUNCTION full_name(p_first VARCHAR(50), p_last VARCHAR(50))
RETURNS VARCHAR(101)
DETERMINISTIC
READS SQL DATA
BEGIN
  RETURN CONCAT_WS(' ', p_first, p_last);
END //
DELIMITER ;

# use it like a built-in
SELECT id, full_name(first_name, last_name) FROM users;

# drop it
DROP FUNCTION IF EXISTS full_name;

# functions MUST declare their nature for binary logging:
#   DETERMINISTIC  - same inputs -> same output (required for replication)
#   READS SQL DATA - reads but doesn't modify
#   NO SQL         - no SQL at all

Funções de Fluxo de Controle

IF() é um ternário compacto; IFNULL(a,b) retorna a a menos que a seja NULL, então b (só dois argumentos); COALESCE generaliza para N argumentos. NULLIF(a,b) retorna NULL quando a=b, prático para evitar divisão por zero (x / NULLIF(y,0)). ISNULL() retorna 1/0, não o valor — não confunda com IFNULL. CASE é a expressão condicional mais flexível e portátil e funciona em qualquer dialeto SQL.

mysql
# IF(test, true_val, false_val) - ternary
SELECT IF(age >= 18, 'adult', 'minor') FROM users;

# IFNULL / NULLIF
SELECT IFNULL(email, 'no-email') FROM users;          -- coalesce one
SELECT COALESCE(nick, email, username) FROM users;    -- coalesce many
SELECT NULLIF(status, 'inactive');  -- NULL if equal, else status

# CASE as a function (see SELECT section for full CASE)
SELECT CASE WHEN x>0 THEN 'pos' WHEN x<0 THEN 'neg' ELSE 'zero' END;

# ISNULL() returns 1/0 (different from IFNULL!)
SELECT ISNULL(email) FROM users;

Cast e Conversão

CAST e CONVERT mudam o tipo de um valor — essenciais quando o MySQL faria de outra forma uma conversão implícita que derrota índices (ex.: comparar uma string a uma coluna numérica). CAST(... AS DATE/DATETIME/SIGNED/UNSIGNED/CHAR/DECIMAL) cobre necessidades comuns. Cuidado: CAST('123abc' AS SIGNED) retorna 123 com um aviso, em vez de dar erro. Use STR_TO_DATE para analisar formatos de data personalizados, já que CAST espera um formato ISO estrito.

mysql
# CAST to a specific type
SELECT CAST('2024-01-15' AS DATE);
SELECT CAST(3.14 AS SIGNED);          -- 3
SELECT CAST('123abc' AS SIGNED);      -- 123 (warns in strict mode)
SELECT CAST(123 AS CHAR(10));

# CONVERT is the same thing with different syntax
SELECT CONVERT('2024-01-15', DATE);
SELECT CONVERT('hello' USING utf8mb4);  -- charset conversion

# common: string -> date for comparison
SELECT * FROM orders
WHERE created_at >= CAST('2024-01-01' AS DATETIME);

# format a number as a zero-padded string
SELECT LPAD(CAST(id AS CHAR), 6, '0') FROM orders;
13

Triggers

Triggers BEFORE / AFTER

Um trigger dispara automaticamente em INSERT/UPDATE/DELETE, seja BEFORE (pode modificar valores NEW, validar e abortar) ou AFTER (para efeitos colaterais como logging). FOR EACH ROW significa que o corpo roda uma vez por linha afetada — triggers de linha são o único tipo que MySQL suporta. Triggers BEFORE servem para padronizar/normalizar dados; AFTER para trabalho em cascata não crítico. Triggers rodam na mesma transação da instrução disparadora.

mysql
DELIMITER //
CREATE TRIGGER trg_users_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  # normalize email to lowercase before saving
  SET NEW.email = LOWER(NEW.email);
  IF NEW.created_at IS NULL THEN
    SET NEW.created_at = NOW();
  END IF;
END //
DELIMITER ;

# AFTER INSERT trigger logs the event
CREATE TRIGGER trg_users_after_insert
AFTER INSERT ON users
FOR EACH ROW
INSERT INTO audit_log(table_name, row_id, action, at)
VALUES ('users', NEW.id, 'INSERT', NOW());

Trigger de Auditoria (UPDATE)

Um uso comum de triggers é auditar mudanças — capture OLD vs NEW numa tabela de histórico. Comparar valores precisa contabilizar NULLs (OLD.email <> NEW.email é NULL se qualquer lado for NULL), então proteja com checagens IS NULL. AFTER DELETE usa OLD (não há NEW na deleção). Mantenha a lógica de auditoria leve, já que roda a cada mudança de linha. Lembre que triggers disparam também para UPDATE/DELETE em massa, o que pode ser lento em operações grandes.

mysql
DELIMITER //
CREATE TRIGGER trg_users_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  IF OLD.email <> NEW.email OR (OLD.email IS NULL) <> (NEW.email IS NULL) THEN
    INSERT INTO audit_log(table_name, row_id, field, old_val, new_val, at)
    VALUES ('users', NEW.id, 'email', OLD.email, NEW.email, NOW());
  END IF;
END //
DELIMITER ;

# a DELETE audit trigger
CREATE TRIGGER trg_users_after_delete
AFTER DELETE ON users
FOR EACH ROW
INSERT INTO archive_users(id, username, email, deleted_at)
VALUES (OLD.id, OLD.username, OLD.email, NOW());

Valores OLD e NEW

NEW guarda os valores da linha de entrada, OLD os anteriores. Você pode modificar NEW em BEFORE INSERT/UPDATE para normalizar ou padronizar dados; triggers AFTER e DELETE os veem somente leitura. SIGNAL SQLSTATE '45000' é a forma padrão de levantar um erro personalizado de um trigger ou programa armazenado (45000 significa 'exceção definida pelo usuário não tratada'). Use triggers BEFORE para validação que deve abortar a mudança; AFTER para efeitos colaterais.

mysql
# OLD: the row before the change (available on UPDATE/DELETE)
# NEW: the row after the change  (available on INSERT/UPDATE)

# BEFORE INSERT: only NEW, you can modify it
# AFTER  INSERT: only NEW, read-only
# BEFORE UPDATE: both OLD and NEW, you can modify NEW
# AFTER  UPDATE: both OLD and NEW, read-only
# BEFORE DELETE: only OLD, read-only
# AFTER  DELETE: only OLD, read-only

CREATE TRIGGER trg_balance_check
BEFORE UPDATE ON accounts
FOR EACH ROW
BEGIN
  IF NEW.balance < 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Balance cannot be negative';
  END IF;
END;

Trigger em Múltiplos Eventos (FOLLOWS/PRECEDES)

Desde a 5.7.2, uma tabela pode ter mais de um trigger para o mesmo evento e timing, ordenados com FOLLOWS/PRECEDES. Cada trigger ainda trata exatamente um evento (INSERT, UPDATE ou DELETE) num timing (BEFORE/AFTER) — para tratar vários eventos, você cria múltiplos triggers. A ordem importa quando triggers dependem dos efeitos uns dos outros. Não há trigger INSTEAD OF no MySQL (views são atualizáveis diretamente).

mysql
# MySQL allows multiple triggers with the same event/timing on one table (5.7.2+)
# order them with FOLLOWS / PRECEDES

DELIMITER //
CREATE TRIGGER trg_users_after_insert_2
AFTER INSERT ON users
FOR EACH ROW
FOLLOWS trg_users_after_insert
BEGIN
  UPDATE user_stats SET total = total + 1 WHERE user_id = NEW.id;
END //
DELIMITER ;

# events: INSERT | UPDATE | DELETE
# timing: BEFORE | AFTER
# a trigger cannot span multiple events; create one per event

Gerenciar Triggers

SHOW TRIGGERS lista cada um dos triggers (note as aspas com crase no nome da coluna 'Table', pois é reservado). SHOW CREATE TRIGGER retorna o corpo completo e o definidor. information_schema.triggers dá metadados estruturados para auditoria. DROP TRIGGER IF EXISTS é seguro para scripts. Triggers são ligados à tabela — descartar a tabela descarta seus triggers. Fique de olho no definidor: como procedures, triggers rodam com privilégios do definidor por padrão.

mysql
# list triggers
SHOW TRIGGERS\G
SHOW TRIGGERS WHERE `Table` = 'users';

# show definition
SHOW CREATE TRIGGER trg_users_before_insert\G

# query metadata
SELECT trigger_name, event_manipulation, action_timing,
       event_object_table, created
FROM information_schema.triggers
WHERE trigger_schema = 'mydb';

# drop a trigger
DROP TRIGGER IF EXISTS trg_users_before_insert;

# triggers fire on the table's schema; definer is recorded
14

Transações e Travas

Controle de Transações

MySQL autoconfirma por padrão — cada instrução é sua própria transação. START TRANSACTION/BEGIN inicia um bloco explícito terminado por COMMIT (salvar) ou ROLLBACK (desfazer). Só engines transacionais (InnoDB) suportam transações; MyISAM não. Crucialmente, instruções DDL (CREATE/ALTER/DROP/TRUNCATE) confirmam implicitamente a transação atual e não podem ser desfeitas, então não misture DDL com DML transacional esperando atomicidade.

mysql
# explicit transaction (autocommit is on by default)
START TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- or ROLLBACK;

# alternative syntax
BEGIN;
  INSERT INTO orders (user_id, amount) VALUES (1, 50);
  UPDATE users SET balance = balance - 50 WHERE id = 1;
COMMIT;

# turn autocommit off for the session
SET autocommit = 0;
-- every statement is now part of a transaction until COMMIT/ROLLBACK
SET autocommit = 1;

# DDL (CREATE/ALTER/DROP) implicitly commits — cannot be rolled back

Savepoints

SAVEPOINT marca um ponto nomeado dentro de uma transação até o qual você pode desfazer parcialmente, sem abortar a transação inteira. ROLLBACK TO SAVEPOINT desfaz o trabalho depois do savepoint; RELEASE SAVEPOINT descarta o marcador (o trabalho permanece). Savepoints permitem tentar um passo opcional e recuperar com elegância. Não são visíveis fora da transação e desaparecem em COMMIT/ROLLBACK. Savepoints aninhados são suportados.

mysql
START TRANSACTION;
  INSERT INTO orders (amount) VALUES (10);
  SAVEPOINT sp_after_order;

  INSERT INTO order_items (order_id) VALUES (LAST_INSERT_ID());
  SAVEPOINT sp_after_items;

  -- something risky
  UPDATE inventory SET stock = stock - 1 WHERE sku='X';
  -- undo just the last step, keep the order + items
  ROLLBACK TO SAVEPOINT sp_after_items;

  -- release a savepoint when no longer needed
  RELEASE SAVEPOINT sp_after_order;
COMMIT;

Níveis de Isolamento

O nível de isolamento troca consistência por concorrência. O REPEATABLE READ padrão do MySQL previne leituras sujas e não repetíveis e, diferente do padrão SQL, também evita a maioria das leituras fantasmas via travamento next-key. READ COMMITTED é popular por menos travamento, mas permite leituras não repetíveis. SERIALIZABLE transforma todas as leituras em leituras de travamento. InnoDB usa MVCC, então leitores não bloqueiam escritores em nenhum nível, exceto SERIALIZABLE.

mysql
# four levels, from least to most strict
# READ UNCOMMITTED  - dirty reads possible (rarely used)
# READ COMMITTED    - no dirty reads; non-repeatable reads possible
# REPEATABLE READ   - default; consistent reads within a transaction
# SERIALIZABLE      - locks everything; safest, slowest

# set for the session
SET SESSION transaction_isolation = 'REPEATABLE-READ';
# MySQL 8.0+ name; older: tx_isolation

# set globally (new connections inherit it)
SET GLOBAL transaction_isolation = 'READ-COMMITTED';

# inspect
SELECT @@transaction_isolation;
SELECT @@GLOBAL.transaction_isolation;

LOCK TABLES

LOCK TABLES toma travas no nível de tabela e é majoritariamente um recurso da era MyISAM — o travamento no nível de linha do InnoDB costuma ser preferível. Uma trava READ deixa outras sessões lerem, mas bloqueia escritas; uma WRITE bloqueia cada um dos outros. Enquanto segura travas, você só pode acessar as tabelas travadas. LOCK TABLES confirma implicitamente qualquer transação ativa, então não compõe com transações. Prefira SELECT ... FOR UPDATE para controle de concorrência no InnoDB.

mysql
# explicit table locks (outside transactions; MyISAM-style)
LOCK TABLES users WRITE, orders READ;
  -- only these tables are accessible; users is write-locked
  SELECT * FROM users;
UNLOCK TABLES;

# READ  lock: this session can read; others can read but not write
# WRITE lock: only this session can read/write; others blocked entirely

# in InnoDB prefer row-level locks (SELECT FOR UPDATE) over LOCK TABLES
# LOCK TABLES implicitly commits the active transaction

SELECT ... FOR UPDATE / SHARE

SELECT ... FOR UPDATE toma uma trava exclusiva de linha, para que outras transações bloqueiem se tentarem atualizar ou travar a mesma linha — essencial para sequências de ler-modificar-escrever como transferências. FOR SHARE toma uma trava compartilhada (outros podem ler, mas não escrever). As travas se liberam em COMMIT/ROLLBACK. A 8.0 adiciona NOWAIT (erro em vez de esperar) e SKIP LOCKED (pular linhas travadas) — perfeito para workers de fila de trabalho que não devem se bloquear.

mysql
START TRANSACTION;
  # lock the row so other transactions cannot modify it until commit
  SELECT balance INTO @b FROM accounts WHERE id = 1 FOR UPDATE;

  IF @b >= 100 THEN
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  END IF;
COMMIT;   -- releases the lock

# FOR UPDATE: exclusive lock (write intent)
# FOR SHARE:  shared lock (read intent, others can read)

# NOWAIT / SKIP LOCKED reduce waiting (8.0+)
SELECT * FROM jobs WHERE status='pending' LIMIT 1 FOR UPDATE SKIP LOCKED;

Deadlocks e Detecção

InnoDB detecta deadlocks automaticamente e desfaz uma transação (a vítima), para que a outra prossiga — você recebe o erro 1213. SHOW ENGINE INNODB STATUS mostra o último deadlock; habilite innodb_print_all_deadlocks para registrá-los. Previna deadlocks travando linhas numa ordem consistente entre transações, mantendo transações curtas e indexando as colunas que você trava (senão as travas escalam para intervalos grandes). Sempre escreva lógica de repetição em torno de transações que podem sofrer deadlock.

mysql
# deadlock: two transactions each hold a lock the other needs
# Tx1: locks A, wants B
# Tx2: locks B, wants A
# -> InnoDB detects it and rolls back the victim automatically

# see the last deadlock
SHOW ENGINE INNODB STATUS\G

# log all deadlocks to the error log
SET GLOBAL innodb_print_all_deadlocks = 1;

# mitigate by:
#  - locking tables/rows in a consistent order across transactions
#  - keeping transactions short
#  - adding appropriate indexes (avoid gap locks on full scans)
#  - using a lower isolation level where safe
15

Gestão de Usuários

Criar Usuário e Autenticação

Uma conta MySQL é sempre 'usuario'@'host' — o host controla de onde o usuário pode conectar; '%' é um curinga para qualquer host. MySQL 8.0 assume caching_sha2_password (mais seguro), que alguns clientes antigos não suportam — recue para mysql_native_password para eles. Prefira hosts de escopo estreito (10.0.0.%) a '%'. CREATE USER é preferido à velha sintaxe GRANT ... IDENTIFIED BY, removida na 8.0.

mysql
# create a user that can connect from anywhere
CREATE USER 'app'@'%' IDENTIFIED BY 'StrongPass!2024';

# restrict to localhost only
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'localpass';

# MySQL 8.0 default plugin is caching_sha2_password
CREATE USER 'dev'@'10.0.%.%' IDENTIFIED WITH caching_sha2_password
  BY 'devpass';

# legacy mysql_native_password (needed for some older clients)
CREATE USER 'legacy'@'%' IDENTIFIED WITH mysql_native_password
  BY 'legacypass';

# rename or change host
RENAME USER 'app'@'%' TO 'appuser'@'10.0.0.%';

# drop a user
DROP USER IF EXISTS 'legacy'@'%';

Privilégios GRANT

GRANT atribui privilégios em quatro escopos: global (*.*), banco (db.*), tabela (db.tabela) e coluna/rotina. ALL PRIVILEGES é tudo, exceto GRANT OPTION. Use o menor privilégio necessário — um usuário de relatórios só deveria receber SELECT. WITH GRANT OPTION deixa o usuário dar seus privilégios a outros (perigoso). FLUSH PRIVILEGES raramente é necessário, já que GRANT atualiza as tabelas em memória; use-o só após editar manualmente as tabelas mysql.

mysql
# grant all on a specific database
GRANT ALL PRIVILEGES ON mydb.* TO 'app'@'%';

# grant read-only on one table
GRANT SELECT ON mydb.users TO 'reporting'@'%';

# grant specific privileges
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'editor'@'localhost';

# grant the ability to grant to others (WITH GRANT OPTION)
GRANT SELECT ON mydb.* TO 'lead'@'%' WITH GRANT OPTION;

# grant at server level (use sparingly)
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'10.0.0.5';

# reload privilege tables into memory (usually automatic)
FLUSH PRIVILEGES;

Privilégios REVOKE

REVOKE remove privilégios previamente concedidos; você precisa casar o escopo exatamente. Para tirar tudo, incluindo GRANT OPTION, use REVOKE ALL PRIVILEGES, GRANT OPTION. Revogar não descarta o usuário — a conta ainda existe e pode conectar, mas não tem privilégios até serem concedidos de novo. Use SHOW GRANTS para inspecionar os privilégios efetivos de um usuário; SEMPRE revise antes de mudar acesso. Remover um usuário por completo é DROP USER.

mysql
# remove specific privileges
REVOKE INSERT, UPDATE ON mydb.* FROM 'editor'@'localhost';

# remove all privileges (but keep the user)
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app'@'%';

# revoke at a specific scope
REVOKE SELECT ON mydb.users FROM 'reporting'@'%';

# see what a user currently has
SHOW GRANTS FOR 'app'@'%';

# show grants for the current user
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER();

Papéis (Roles) (MySQL 8.0+)

Papéis (8.0+) permitem agrupar privilégios e atribuí-los a muitos usuários de uma vez, simplificando a gestão de acesso. Um usuário que recebeu um papel não ganha seus privilégios até o papel ser ativado — SET DEFAULT ROLE o torna ativo por padrão no login. CURRENT_ROLE() mostra os papéis ativos. Mude um papel uma vez e cada usuário membro herda a mudança. Muito mais limpo que conceder os mesmos privilégios a cada usuário individualmente.

mysql
# a role is a named bundle of privileges
CREATE ROLE 'app_read', 'app_write';

GRANT SELECT ON mydb.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON mydb.* TO 'app_write';

# grant a role to users
GRANT 'app_read' TO 'alice'@'%', 'bob'@'%';
GRANT 'app_write' TO 'carol'@'%';

# a user must activate a role to use it (default: none)
SET DEFAULT ROLE 'app_read' TO 'alice'@'%';
SET DEFAULT ROLE ALL TO 'alice'@'%';

# see active roles
SELECT CURRENT_ROLE();

Gestão de Senhas

ALTER USER é a forma moderna de gerenciar senhas (SET PASSWORD está obsoleto). PASSWORD EXPIRE força uma troca no próximo login — útil para políticas de rotação de senha. O componente validate_password impõe comprimento/complexidade em MEDIUM (caixa mista, dígito, caractere especial) e STRONG (também sem palavras de dicionário). ACCOUNT LOCK/UNLOCK desativa temporariamente uma conta sem descartá-la — prático para suspender acesso durante investigações.

mysql
# change your own password
ALTER USER USER() IDENTIFIED BY 'NewPass!2024';

# change another user's password
ALTER USER 'app'@'%' IDENTIFIED BY 'NewAppPass!2024';

# require a password to be expired (user must reset on next login)
ALTER USER 'app'@'%' PASSWORD EXPIRE;

# enforce password policy (8.0+ validate_password component)
INSTALL COMPONENT 'file://component_validate_password';
SET GLOBAL validate_password.policy = 'MEDIUM';  -- 0/LOW,1/MEDIUM,2/STRONG
SET GLOBAL validate_password.length = 12;

# lock an account temporarily
ALTER USER 'app'@'%' ACCOUNT LOCK;
ALTER USER 'app'@'%' ACCOUNT UNLOCK;

Mostrar Grants e Usuários

mysql.user é a tabela de sistema que guarda todas as contas — consulte-a para inventariar usuários e flags como account_locked e password_expired. SHOW GRANTS é a visão mais amigável e decodificada dos privilégios de um usuário. CURRENT_USER() é a identidade usada para checagens de privilégio (que pode diferir de USER(), a string de conexão) — importante ao conectar através de um papel ou proxy. Audite estes regularmente por contas dormentes ou com privilégios em excesso.

mysql
# list all users (MySQL 8.0+)
SELECT user, host, account_locked, password_expired
FROM mysql.user;

# grants for a specific user
SHOW GRANTS FOR 'app'@'%';

# grants for the current session
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER();

# who am I?
SELECT CURRENT_USER(), USER();

# active roles for the current session
SELECT CURRENT_ROLE();

# list global privileges
SELECT user, host, Super_priv, Process_priv, Reload_priv
FROM mysql.user;
16

Backup e Restauração

mysqldump

mysqldump é a ferramenta clássica de backup lógico — emite instruções SQL que recriam o banco. --single-transaction dá um snapshot InnoDB consistente sem travar, usando uma única transação REPEATABLE READ. --routines/--triggers/--events incluem programas armazenados (caso contrário, omitidos). Para bancos grandes, prefira Percona XtraBackup ou MySQL Enterprise Backup (backups físicos), muito mais rápidos que mysqldump.

mysql
# dump a single database to a file
mysqldump -u root -p mydb > mydb.sql

# dump all databases
mysqldump -u root -p --all-databases > alldb.sql

# dump with routines, triggers and events
mysqldump -u root -p --routines --triggers --events mydb > mydb_full.sql

# dump schema only (no data) or data only
mysqldump -u root -p --no-data mydb > schema.sql
mysqldump -u root -p --no-create-info mydb > data.sql

# consistent dump of InnoDB in one transaction
mysqldump -u root -p --single-transaction mydb > mydb.sql

# compress on the fly
mysqldump -u root -p mydb | gzip > mydb.sql.gz

Restaurar de Dump

Para restaurar, conecte por pipe o arquivo de dump ao cliente mysql ou use SOURCE dentro dele. O banco alvo já deve existir (CREATE DATABASE e depois USE). Para restaurações mais rápidas, desative checagens de chave estrangeira e única e o autocommit durante a carga e depois reative — o próprio dump geralmente já contém essas salvaguardas. Restaurar um dump comprimido com gunzip evita descomprimir no disco. Sempre teste restaurações num servidor de staging.

mysql
# restore a database dump (database must exist)
mysql -u root -p mydb < mydb.sql

# restore all databases (creates them)
mysql -u root -p < alldb.sql

# restore a compressed dump
gunzip < mydb.sql.gz | mysql -u root -p mydb

# restore from inside the mysql client
SOURCE /path/to/mydb.sql;

# speed up restores: disable checks and autocommit during load
SET foreign_key_checks = 0;
SET unique_checks = 0;
SET autocommit = 0;
SOURCE /path/to/mydb.sql;
COMMIT;
SET foreign_key_checks = 1;
SET unique_checks = 1;

Exportar para CSV

SELECT ... INTO OUTFILE escreve um arquivo no lado do servidor, limitado pelo diretório secure_file_priv (ou totalmente desativado se secure_file_priv for NULL) e exige o privilégio FILE — não pode sobrescrever um arquivo existente. --batch --raw no cliente mysql produz um arquivo separado por tabulação localmente, sem essas restrições, ideal para trabalhos cron. As cláusulas FIELDS/LINES controlam delimitadores, aspas e escape para compatibilidade CSV.

mysql
# server-side export to a file (needs FILE privilege + secure_file_priv)
SELECT id, username, email, created_at
FROM users
INTO OUTFILE '/var/lib/mysql-files/users.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n';

# client-side export with the mysql client (no FILE privilege needed)
mysql -u root -p -e "SELECT id, username, email FROM users" \
  --batch --raw mydb > users.tsv

# include column headers with a UNION
SELECT 'id','username','email' UNION
SELECT id, username, email FROM users
INTO OUTFILE '/var/lib/mysql-files/users.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

Importar de CSV

LOAD DATA INFILE é a forma mais rápida de carregar CSV — muito mais rápida que instruções INSERT. A forma do lado do servidor lê de um caminho sob secure_file_priv; LOCAL lê um arquivo do cliente (habilitado com local_infile no cliente e no servidor). IGNORE 1 LINES pula um cabeçalho. Use @var para capturar colunas e depois SET para transformar valores (ex.: analisar uma string de data). Para cargas muito grandes, desative chaves/chaves estrangeiras e use uma única transação.

mysql
# server-side import (needs FILE privilege)
LOAD DATA INFILE '/var/lib/mysql-files/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, username, email, @created_at)
SET created_at = STR_TO_DATE(@created_at, '%Y-%m-%d %H:%i:%s');

# client-side import (no FILE privilege, sends file from client)
LOAD DATA LOCAL INFILE 'C:/data/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES (id, username, email);

# enable LOCAL on the client:  --local-infile=1
# and on the server:  SET GLOBAL local_infile = 1;

Binary Log (mysqlbinlog)

O binary log registra todas as instruções que mudam dados, para replicação e recuperação point-in-time. SHOW MASTER STATUS dá o arquivo e a posição atuais (as coordenadas de replicação). mysqlbinlog converte um arquivo binlog de volta a SQL (ou eventos de linha) para inspeção ou reprodução — combine com horários ou posições de início/fim para recuperar a um momento preciso. PURGE BINARY LOGS recupera disco; deixe expire_logs_days/binlog_expire_logs_seconds automatizar isto.

mysql
# enable the binary log (my.cnf)
# [mysqld]
# log_bin = mysql-bin
# binlog_format = ROW
# server_id = 1

# show current binlog files and position
SHOW BINARY LOGS;
SHOW MASTER STATUS;

# view events in a binlog file
mysqlbinlog mysql-bin.000123

# filter by time / position
mysqlbinlog --start-datetime="2024-06-01 00:00:00" \
            --stop-datetime="2024-06-01 12:00:00" \
            mysql-bin.000123 > replay.sql

# replay events to recover
mysql -u root -p < replay.sql

# purge old binlogs
PURGE BINARY LOGS BEFORE '2024-06-01 00:00:00';

Plugin Clone e Replicação

O plugin clone (8.0+) copia fisicamente um diretório de dados InnoDB muito mais rápido que um dump lógico, ideal para provisionar réplicas ou snapshots. Para replicação, aponte uma réplica a uma fonte com CHANGE REPLICATION SOURCE TO (8.0+; era CHANGE MASTER TO) e START REPLICA. GTID (SOURCE_AUTO_POSITION=1) torna o posicionamento da replicação robusto. SHOW REPLICA STATUS revela atraso e erros. Monitore Seconds_Behind_Source pela saúde da réplica.

mysql
# install the clone plugin (8.0+) for fast physical copy
INSTALL PLUGIN clone SONAME 'mysql_clone.so';

# clone a local data directory to another path
CLONE LOCAL DATA DIRECTORY = '/var/lib/mysql-clone';

# clone from a remote donor (good for provisioning replicas)
CLONE INSTANCE FROM 'donor'@'10.0.0.2':3306
  IDENTIFIED BY 'donorpass';

# set up replication from a donor
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='10.0.0.2', SOURCE_PORT=3306,
  SOURCE_USER='repl', SOURCE_PASSWORD='replpass',
  SOURCE_AUTO_POSITION=1;
START REPLICA;   -- 8.0+ (was START SLAVE)
SHOW REPLICA STATUS\G
17

Otimização de Desempenho

EXPLAIN

EXPLAIN revela o plano da consulta: qual índice é usado, quantas linhas são estimadas e se é preciso um filesort ou tabela temporária. A coluna 'type' ranqueia as estratégias de acesso — const/eq_ref são os melhores (busca de linha única), ref/range usam um índice, index varre o índice inteiro, ALL varre a tabela inteira (geralmente ruim). 'Using index' significa uma varredura cobertora só de índice; 'Using filesort'/'Using temporary' avisam de operações caras a otimizar.

mysql
# show the execution plan for a query
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

# extended: extra info + warnings
EXPLAIN EXTENDED SELECT ...;
SHOW WARNINGS;

# format as a tree (8.0+) — more readable
EXPLAIN FORMAT=TREE SELECT ...;

# the key columns to read:
#   type        - access method (const > eq_ref > ref > range > index > ALL)
#   key         - the index chosen
#   rows        - estimated rows scanned
#   Extra       - 'Using index' (good) / 'Using filesort' / 'Using temporary'

EXPLAIN ANALYZE (8.0+)

EXPLAIN ANALYZE (8.0.17+) realmente executa a consulta e reporta contagens e tempos reais de linhas por iterador, revelando onde o tempo é realmente gasto — muito mais preciso que as estimativas do EXPLAIN puro. Use-o em consultas SELECT no staging. Pareie com SHOW WARNINGS para ver a consulta reescrita pelo otimizador (após mesclagem de views, dobra de constantes). Para consultas de escrita, use EXPLAIN num equivalente somente leitura ou inspecione com performance_schema.

mysql
# actually runs the query and reports real per-step timing
EXPLAIN ANALYZE
SELECT u.username, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

# output shows actual rows, loops and per-iterator ms:
# -> Table scan on users  (actual rows=1000, loops=1)
#     -> Covering index lookup on o  (actual rows=2.4, loops=1000)

# unlike EXPLAIN, this executes the statement (read-only queries only)

# also useful: SHOW WARNINGS after EXPLAIN to see the rewritten query
EXPLAIN SELECT ...;
SHOW WARNINGS\G

Hints de Otimizador

Hints de otimizador (/*+ ... */) sobrescrevem o planejador para uma única instrução. São mais direcionados que SET GLOBAL e sobrevivem a reinícios de instrução. Use-os para corrigir um plano ruim, mas trate também a causa subjacente (estatísticas velhas, índice ausente, dados enviesados) — hints se degradam conforme os dados mudam. MAX_EXECUTION_TIME limita consultas somente leitura, protegendo o servidor de SELECTs descontrolados. SET_VAR ajusta uma variável de sessão só para uma instrução.

mysql
# index hints (8.0+ style, after SELECT)
SELECT /*+ INDEX(u idx_email) */ *
FROM users u
WHERE email = '[email protected]';

SELECT /*+ NO_RANGE_OPTIMIZATION(u) */ *
FROM users u WHERE id BETWEEN 1 AND 1000;

# join order / algorithm hints
SELECT /*+ JOIN_ORDER(a,b,c) */ * FROM a JOIN b ON ... JOIN c ON ...;
SELECT /*+ HASH_JOIN(b) */ * FROM a JOIN b ON a.id=b.aid;
SELECT /*+ NLJ(b) */ * FROM a JOIN b ON a.id=b.aid;

# resource hints
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM big_table;
# aborts the query after 1000 ms (read-only)

# SET_VAR hint changes a variable for one statement
SELECT /*+ SET_VAR(sort_buffer_size=16M) */ ... FROM ...;

Slow Query Log

O slow query log captura consultas que excedem long_query_time (em segundos, sub-segundo permitido). Habilite log_queries_not_using_indexes para capturar varreduras completas, mesmo as rápidas. mysqldumpslow agrega consultas similares e as ranqueia por tempo/contagem/linhas, para que você priorize os piores. O pt-query-digest da Percona dá relatórios muito mais ricos (impressões digitais, percentis) — a ferramenta padrão de análise de slow log. Rotacione e limpe o log regularmente.

mysql
# enable in my.cnf
# [mysqld]
# slow_query_log = 1
# slow_query_log_file = /var/log/mysql/slow.log
# long_query_time = 2          # log queries slower than 2s
# log_queries_not_using_indexes = 1
# log_slow_admin_statements = 1

# enable at runtime (persists until restart)
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;

# analyze the slow log with mysqldumpslow
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
#   -s t sort by total time, -t 10 top 10
#   -s c count, -s r rows, -s l lock time

# pt-query-digest (Percona Toolkit) gives richer analysis
pt-query-digest /var/log/mysql/slow.log

SHOW PROFILE e Performance Schema

SHOW PROFILE divide uma consulta em estágios (abrir tabelas, ordenar, enviar dados) — prático, mas obsoleto. O equivalente moderno é performance_schema, um framework de instrumentação de baixo custo habilitado por padrão na 8.0. Consulte events_statements_summary_by_digest para achar as consultas que consomem mais tempo agregado no servidor, depois aprofunde em events_stages_history_long para detalhe por estágio. É assim que se acha gargalos sistêmicos, não só consultas lentas individuais.

mysql
# profiling (deprecated but quick) - per-statement stage timings
SET profiling = 1;
SELECT COUNT(*) FROM huge_table;
SHOW PROFILE;
SHOW PROFILE FOR QUERY 1;

# preferred: performance_schema (8.0+, enabled by default)
# top queries by total latency
SELECT digest_text, count_star, sum_timer_wait/1e9 AS total_s
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

# which stage of which query took longest
SELECT event_name, format_time(timer_wait) AS took
FROM performance_schema.events_stages_history_long
ORDER BY timer_wait DESC LIMIT 10;

Dicas de Otimização de Índices

O design de índices vence a maioria das batalhas de desempenho. Siga a regra do prefixo mais à esquerda, evite curingas iniciais e não envolva colunas indexadas em funções (ou use uma coluna gerada + índice). Mire em índices cobertores nas consultas quentes. Mantenha as condições WHERE sargáveis (pesquisáveis por argumento), para que um índice possa ser usado: created_at >= x AND created_at < y vence DATE(created_at)=x. ANALYZE TABLE atualiza as estatísticas de cardinalidade de que o otimizador depende.

mysql
# 1. index columns used in WHERE / JOIN / ORDER BY
#    with the most selective equality column first
CREATE INDEX idx_user_status ON orders(user_id, status, created_at);

# 2. avoid leading wildcards (defeats the index)
SELECT * FROM users WHERE name LIKE 'A%';   -- OK
SELECT * FROM users WHERE name LIKE '%A';   -- full scan

# 3. don't wrap indexed columns in functions
SELECT * FROM orders WHERE DATE(created_at)='2024-01-01';        -- bad
SELECT * FROM orders WHERE created_at >= '2024-01-01'
  AND created_at < '2024-01-02';                                  -- good

# 4. use a covering index so the query is index-only
EXPLAIN SELECT user_id, status FROM orders WHERE user_id=5;
-- 'Using index' in Extra means no table lookup

# 5. refresh statistics after big loads
ANALYZE TABLE orders;
18

Operações JSON

Coluna JSON e Inserção

O tipo JSON guarda JSON num formato binário validado. Insira com um literal de string JSON ou construa valores com JSON_OBJECT() (pares chave/valor) e JSON_ARRAY(). JSON inválido é rejeitado na inserção. Como o JSON é guardado como árvore analisada, o acesso a membros é rápido, mas o documento inteiro é lido junto — para valores escalares consultados com frequência, extraia-os para uma coluna gerada e indexe-a, pelo melhor dos dois mundos.

mysql
CREATE TABLE products (
  id    INT PRIMARY KEY,
  name  VARCHAR(100),
  attrs JSON
);

# insert JSON literals
INSERT INTO products VALUES
  (1, 'Widget',
   '{"color":"red","size":42,"tags":["new","sale"],"stock":{"ny":5,"la":3}}'),
  (2, 'Gadget', '{"color":"blue","in_stock":true}');

# build JSON with functions
INSERT INTO products VALUES (3, 'Thing',
  JSON_OBJECT('color','green','tags',JSON_ARRAY('new')));

JSON_EXTRACT / -> / ->>

-> extrai um membro JSON (retorna JSON com aspas); ->> retorna-o como texto sem aspas (a escolha comum para comparações). Caminhos usam $. para a raiz, $.chave para membros, $.arr[0] para índices de array e $.a.b para aninhamento. JSON_CONTAINS_PATH verifica a existência de chave. JSON_EXTRACT é a forma de função de ->. Para achar documentos por um valor de membro com eficiência, adicione uma coluna gerada + índice em attrs->>'$.cor'.

mysql
# extract a member (returns JSON)
SELECT JSON_EXTRACT(attrs, '$.color') FROM products WHERE id=1;   -- "red"
SELECT attrs->'$.color'   FROM products WHERE id=1;               -- "red"

# extract as text (unquoted)
SELECT attrs->>'$.color'  FROM products WHERE id=1;               -- red

# array element access
SELECT attrs->>'$.tags[0]' FROM products WHERE id=1;              -- new

# nested paths
SELECT attrs->>'$.stock.ny' FROM products WHERE id=1;             -- 5

# filter rows by a JSON value
SELECT id, name FROM products
WHERE attrs->>'$.color' = 'red';

# exists check
SELECT id FROM products
WHERE JSON_CONTAINS_PATH(attrs, 'one', '$.stock');

JSON_SET / INSERT / REPLACE / REMOVE

Estas funções retornam um documento JSON modificado — você precisa atribuir o resultado de volta para atualizar a coluna. JSON_SET faz upsert (adiciona ou atualiza); INSERT só adiciona o ausente; REPLACE só atualiza o existente; REMOVE deleta. JSON_MERGE_PATCH (RFC 7396) mescla dois documentos e remove chaves definidas como null — a forma padrão de aplicar um patch parcial. JSON_MERGE_PRESERVE mantém arrays/objetos existentes em vez de substituí-los.

mysql
# SET updates or adds a member (returns the modified document)
UPDATE products
SET attrs = JSON_SET(attrs, '$.color', 'green', '$.weight', 1.2)
WHERE id = 1;

# INSERT adds only if the path doesn't exist
UPDATE products
SET attrs = JSON_INSERT(attrs, '$.code', 'W-100')
WHERE id = 1;

# REPLACE updates only if the path exists
UPDATE products
SET attrs = JSON_REPLACE(attrs, '$.size', 99)
WHERE id = 1;

# REMOVE deletes a member
UPDATE products
SET attrs = JSON_REMOVE(attrs, '$.tags[0]')
WHERE id = 1;

# MERGE_PATCH merges two JSON documents (8.0+)
SET attrs = JSON_MERGE_PATCH(attrs, '{"size":50,"color":null}')

JSON_TABLE (8.0+)

JSON_TABLE (8.0+) é a ponte do JSON para o relacional — fatia um documento JSON em linhas que você pode unir, filtrar e agregar como qualquer tabela. A cláusula columns mapeia caminhos JSON para colunas tipadas. NESTED PATH desempacota arrays aninhados numa única passada. Isso torna colunas JSON práticas para dados semiestruturados: guarde JSON flexível, fatie para relacional com JSON_TABLE quando precisar de análises SQL e indexe uma coluna gerada para caminhos quentes.

mysql
# turn a JSON array of objects into relational rows
SELECT jt.*
FROM products p,
JSON_TABLE(p.attrs, '$.tags[*]'
  COLUMNS (
    tag VARCHAR(50) PATH '$'  -- each element as a column
  )
) AS jt
WHERE p.id = 1;

# flatten an array of objects
SELECT p.id, jt.color, jt.size
FROM products p,
JSON_TABLE(p.attrs, '$'
  COLUMNS (
    color VARCHAR(20) PATH '$.color',
    size  INT          PATH '$.size'
  )
) AS jt;

# nested arrays: NESTED PATH
JSON_TABLE(j, '$.stock.*'
  COLUMNS (warehouse VARCHAR(5) PATH '$.wh', qty INT PATH '$.qty'))

Funções de Agregação JSON

JSON_ARRAYAGG coleta valores num array JSON por grupo; JSON_OBJECTAGG constrói um objeto chave->valor (chaves posteriores sobrescrevem duplicatas anteriores). Combinado com JSON_OBJECT, você pode construir um array de objetos para uma resposta de API direto no SQL — prático para montar payloads aninhados sem pós-processamento na aplicação. Entradas NULL são puladas por ambos. Fique atento ao tamanho do resultado: um grupo enorme produz um valor JSON enorme.

mysql
# JSON_ARRAYAGG: collect values into a JSON array
SELECT user_id,
  JSON_ARRAYAGG(email) AS emails
FROM contacts
GROUP BY user_id;
-- ["[email protected]","[email protected]"]

# JSON_OBJECTAGG: key/value pairs into a JSON object
SELECT user_id,
  JSON_OBJECTAGG(type, value) AS prefs
FROM user_prefs
GROUP BY user_id;
-- {"theme":"dark","lang":"en"}

# build a JSON array of objects with JSON_OBJECT + ARRAYAGG
SELECT JSON_ARRAYAGG(
  JSON_OBJECT('id', id, 'name', username)
) AS users
FROM users WHERE status='active';

Validação e Schema JSON

JSON_VALID verifica a boa formação (inserções já impõem isto). JSON_PRETTY reformata por legibilidade; JSON_STORAGE_SIZE reporta bytes. JSON_SCHEMA_VALID (8.0.17+) permite que uma restrição CHECK imponha um JSON Schema (chaves obrigatórias, tipos) — uma forma leve de dar estrutura a colunas JSON sem perder flexibilidade. JSON_TABLE e JSON_EXTRACT aceitam cláusulas ON EMPTY / ON ERROR para controlar o comportamento quando um caminho está ausente ou inválido.

mysql
# validate a string is JSON
SELECT JSON_VALID('{"a":1}');          -- 1
SELECT JSON_VALID('{bad}');            -- 0

# pretty-print and minify
SELECT JSON_PRETTY(attrs) FROM products WHERE id=1;
SELECT JSON_STORAGE_SIZE(attrs) FROM products WHERE id=1;  -- bytes

# JSON_SCHEMA_VALID (8.0.17+) enforces a schema with CHECK
CREATE TABLE events (
  id INT PRIMARY KEY,
  data JSON,
  CHECK (JSON_SCHEMA_VALID(
    '{"type":"object","required":["ts","type"],
      "properties":{"ts":{"type":"string"},
                    "type":{"type":"string"}}}', data))
);

# JSON_TABLE with ERROR ON ERROR for strict validation
SELECT * FROM JSON_TABLE(j, '$' COLUMNS(v INT PATH '$.x')
  DEFAULT NULL ON EMPTY ERROR ON ERROR) AS t;
19

Funções de Janela

ROW_NUMBER()

ROW_NUMBER() atribui um inteiro sequencial único a cada linha dentro de sua partição, ordenado conforme especificado — a forma canônica de ranquear, deduplicar e escolher top-N-por-grupo. Diferente de RANK/DENSE_RANK, nunca produz empates. O padrão de DELETE via CTE remove duplicatas mantendo uma linha por chave (você não pode deletar direto de um CTE, então seleciona os IDs a deletar primeiro). Funções de janela exigem MySQL 8.0+.

mysql
# unique sequential number per row within a partition
SELECT username, country,
  ROW_NUMBER() OVER (PARTITION BY country ORDER BY created_at) AS rn
FROM users;

# top 3 oldest users per country
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY country ORDER BY birth_date) AS rn
  FROM users
)
SELECT * FROM ranked WHERE rn <= 3;

# deduplicate: keep the latest row per email
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY id DESC) AS rn
  FROM users
)
DELETE FROM users WHERE id IN (
  SELECT id FROM ranked WHERE rn > 1
);

RANK() e DENSE_RANK()

RANK() dá aos empates o mesmo rank e pula os ranks seguintes (1,1,4,5); DENSE_RANK() dá aos empates o mesmo rank sem lacuna (1,1,3,4); ROW_NUMBER() nunca empata (1,2,3,4). Escolha pela intenção: rankings estilo leaderboard geralmente querem RANK ou DENSE_RANK. DENSE_RANK é a forma clássica de achar o N-ésimo mais alto por grupo (rank=2 retorna o segundo salário distinto). Cada um dos três compartilha a mesma sintaxe OVER().

mysql
# all three ranking functions on the same data
SELECT name, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
  RANK()       OVER (ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS drnk
FROM players;

# with scores 100,100,90,80 the functions return:
#   rn: 1,2,3,4         (always sequential)
#   rnk:1,2,4,5         (ties share a rank, next rank skips)
#   drnk:1,2,3,4        (ties share a rank, no gap)

# find the 2nd-highest salary per department
WITH ranked AS (
  SELECT dept, salary,
    DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dr
  FROM employees
)
SELECT DISTINCT dept, salary FROM ranked WHERE dr = 2;

LAG() e LEAD()

LAG() e LEAD() espiam outra linha relativa à atual — LAG olha para trás, LEAD para frente — sem self join. Recebem um offset opcional (padrão 1) e um valor padrão para quando o offset cai fora da partição (padrão NULL). Perfeitos para diffs período a período, comparações móveis e detecção de sequências/quebras em sequências. Sempre especifique um ORDER BY, para que 'anterior' seja bem definido.

mysql
# compare each row to the previous/next row
SELECT day, sales,
  LAG(sales)  OVER (ORDER BY day) AS prev_day,
  sales - LAG(sales) OVER (ORDER BY day) AS diff,
  LEAD(sales) OVER (ORDER BY day) AS next_day
FROM daily_sales;

# with an explicit offset and default value
SELECT day, sales,
  LAG(sales, 7, 0) OVER (ORDER BY day) AS same_day_last_week
FROM daily_sales;

# year-over-year comparison
SELECT year, region, revenue,
  LAG(revenue) OVER (PARTITION BY region ORDER BY year) AS prev_year,
  revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY year) AS yoy
FROM annual_revenue;

Agregados de Janela (SUM / AVG OVER)

Funções de agregação com OVER() computam o agregado sobre uma janela móvel de linhas em vez de colapsá-las. Sem frame, SUM OVER (ORDER BY x) dá um total acumulado; com ROWS BETWEEN 6 PRECEDING AND CURRENT ROW é uma média móvel dos últimos 7. Um OVER() vazio computa o total geral sobre todas as linhas. PARTITION BY reseta a janela por grupo. Frames usam fronteiras ROWS (físicas) ou RANGE (baseadas em valor).

mysql
# running total over time
SELECT day, sales,
  SUM(sales) OVER (ORDER BY day) AS running_total
FROM daily_sales;

# moving average over a 7-day window
SELECT day, sales,
  AVG(sales) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS ma7
FROM daily_sales;

# each row's share of its group total
SELECT user_id, amount,
  amount / SUM(amount) OVER (PARTITION BY user_id) AS share
FROM orders;

# grand total alongside detail rows
SELECT username, balance,
  SUM(balance) OVER () AS total_balance
FROM users;

FIRST_VALUE / LAST_VALUE / NTH_VALUE

FIRST_VALUE/NTH_VALUE retornam o valor de uma linha específica dentro do frame. LAST_VALUE é uma armadilha comum: o frame padrão é RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, então 'último' significa a linha atual, não a última da partição — estenda o frame até UNBOUNDED FOLLOWING para obter o verdadeiro último valor. NTH_VALUE retorna NULL até a N-ésima linha aparecer. Frames explícitos removem a ambiguidade e valem ser sempre especificados.

mysql
# first value in each partition (per ordering)
SELECT day, region, sales,
  FIRST_VALUE(sales) OVER (PARTITION BY region ORDER BY day) AS first_sale
FROM daily_sales;

# LAST_VALUE needs an explicit frame to include current row!
SELECT day, region, sales,
  LAST_VALUE(sales) OVER (
    PARTITION BY region ORDER BY day
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS latest_so_far
FROM daily_sales;

# the Nth value within a partition
SELECT day, region, sales,
  NTH_VALUE(sales, 3) OVER (
    PARTITION BY region ORDER BY day
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS third
FROM daily_sales;

NTILE e PERCENT_RANK

NTILE(n) divide as linhas ordenadas em n buckets o mais uniformemente possível (buckets anteriores recebem a linha extra quando não divide exato) — prático para quartis/decis em relatórios. PERCENT_RANK() é o rank relativo como fração 0..1; CUME_DIST() é a distribuição cumulativa (fração de linhas no valor atual ou abaixo). Use NTILE para agrupar em baldes e PERCENT_RANK/CUME_DIST para análises estilo percentil.

mysql
# divide rows into N equal buckets (1..N)
SELECT name, salary,
  NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;
-- quartile 1 = top 25%, quartile 4 = bottom 25%

# relative rank as a fraction 0..1
SELECT name, salary,
  PERCENT_RANK() OVER (ORDER BY salary) AS pct,
  CUME_DIST()    OVER (ORDER BY salary) AS cum
FROM employees;
-- pct  = (rank-1)/(N-1), 0 = lowest, 1 = highest
-- cum  = (rows up to and including current)/N

# CUME_DIST: fraction of rows with a value <= current
20

Tratamento de Data e Hora

Funções de Data/Hora Atuais

NOW()/CURRENT_TIMESTAMP retornam o mesmo valor ao longo de uma instrução (consistente); SYSDATE() reflete a hora real a cada chamada (evite — é não determinística e quebra a replicação). Guarde timestamps em UTC (UTC_TIMESTAMP) e converta para hora local na exibição. NOW(6) dá precisão de microssegundo. UNIX_TIMESTAMP converte para/de segundos da época — conveniente para interop com linguagens que usam tempo de época.

mysql
# current timestamp (date + time) in one call
SELECT NOW(), SYSDATE(), CURRENT_TIMESTAMP, LOCALTIME;

# current date / time separately
SELECT CURDATE(), CURRENT_DATE;     -- '2024-07-02'
SELECT CURTIME(), CURRENT_TIME;     -- '14:30:00'

# UTC equivalents (store UTC, display local)
SELECT UTC_TIMESTAMP(), UTC_DATE(), UTC_TIME();

# microsecond precision with (6)
SELECT NOW(6), CURRENT_TIMESTAMP(6);

# UNIX timestamp (seconds since epoch) and back
SELECT UNIX_TIMESTAMP();                 -- 1719912600
SELECT FROM_UNIXTIME(1719912600);        -- '2024-07-02 12:30:00'
SELECT FROM_UNIXTIME(1719912600, '%Y-%m-%d');

DATE_FORMAT e STR_TO_DATE

DATE_FORMAT formata uma data/hora para string usando especificadores — note o %i minúsculo (minutos) e %s (segundos), pegadinha comum. STR_TO_DATE é o inverso, analisando uma string num DATE/TIME/DATETIME. Sempre use STR_TO_DATE para analisar entrada de usuário, em vez de depender da conversão implícita string-para-data, que depende das configurações de formato e é frágil. Guarde datas como tipos DATE/DATETIME, não strings.

mysql
# format a date/time to a string
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');   -- 2024-07-02 14:30:00
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y');            -- 02/07/2024
SELECT DATE_FORMAT(NOW(), '%W, %M %e %Y');        -- Tuesday, July 2 2024

# parse a string into a date
SELECT STR_TO_DATE('02/07/2024', '%d/%m/%Y');     -- 2024-07-02
SELECT STR_TO_DATE('Jul 2, 2024 2:30 PM', '%b %e, %Y %h:%i %p');

# common specifiers:
#   %Y 4-digit year   %y 2-digit year
#   %m month(01-12)   %c month(1-12)   %M month name   %b abbreviated
#   %d day(01-31)     %e day(1-31)     %j day of year
#   %H hour(00-23)    %h hour(01-12)   %i minutes  %s seconds
#   %W weekday name   %a abbreviated   %p AM/PM

DATE_ADD / DATE_SUB e INTERVAL

DATE_ADD/DATE_SUB com INTERVAL é a aritmética de datas canônica; a abreviação + INTERVAL / - INTERVAL é mais limpa. INTERVAL trata corretamente a virada de mês/ano (31 de jan + 1 mês = 29 de fev num ano bissexto). LAST_DAY retorna o último dia do mês do argumento — prático para lógica de fim de mês. Unidades compostas como YEAR_MONTH recebem '1-2' (1 ano 2 meses). Nunca use matemática de string para datas — quebra em casos extremos.

mysql
# add an interval to a date
SELECT DATE_ADD('2024-01-31', INTERVAL 1 MONTH);   -- 2024-02-29
SELECT DATE_ADD('2024-01-31', INTERVAL 1 DAY);     -- 2024-02-01
SELECT '2024-01-31' + INTERVAL 1 MONTH;            -- shorthand

# subtract
SELECT DATE_SUB(NOW(), INTERVAL 7 DAY);
SELECT NOW() - INTERVAL 1 HOUR;

# interval units: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR
#   SECOND_MICROSECOND MINUTE_MICROSECOND ... YEAR_MONTH (compound)

# add to a datetime (keeps the time component)
SELECT DATE_ADD(NOW(), INTERVAL 30 MINUTE);

# last day of next month
SELECT LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH));

DATEDIFF / TIMESTAMPDIFF

DATEDIFF retorna dias inteiros (data1 - data2, ignorando a hora). TIMESTAMPDIFF retorna a diferença numa unidade de sua escolha (unidade, de, para) e trunca para zero — então TIMESTAMPDIFF(YEAR, ...) dá a idade só se o aniversário já passou neste ano; para idade exata, subtraia 1 quando o mês/dia ainda não chegou. Fique atento à ordem dos argumentos: DATEDIFF é (a,b) = a-b; TIMESTAMPDIFF é (unidade, de, para) = para-de.

mysql
# DATEDIFF: difference in DAYS (date1 - date2)
SELECT DATEDIFF('2024-12-31', '2024-01-01');   -- 365
SELECT DATEDIFF(NOW(), birth_date) AS days_alive FROM users;

# TIMESTAMPDIFF: difference in a chosen unit (date1, date2) -> date2 - date1
SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age;
SELECT TIMESTAMPDIFF(MONTH, '2024-01-15', '2024-07-02');
SELECT TIMESTAMPDIFF(MICROSECOND, '2024-07-02 09:00:00', NOW());

# units: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR
# (TIMESTAMPDIFF argument order is (unit, from, to))

# TIME values: TIMEDIFF returns a TIME
SELECT TIMEDIFF('18:00:00', '09:30:00');   -- 08:30:00

EXTRACT e Partes de Data

EXTRACT(unidade FROM data) retorna um único componente numérico; YEAR()/MONTH()/DAY() etc. são abreviações para os comuns; DAYNAME/MONTHNAME retornam nomes. Agrupar por YEAR()/MONTH() de um timestamp é a forma padrão de construir relatórios mensais. Note que DAYOFWEEK é 1=Domingo..7=Sábado, enquanto WEEKDAY é 0=Segunda..6=Domingo — escolha o que casa com sua convenção. WEEK() tem modos para o dia de início da semana e contagem.

mysql
# pull out a single component
SELECT EXTRACT(YEAR FROM created_at)   AS yr,
       EXTRACT(MONTH FROM created_at)  AS mon,
       EXTRACT(DAY FROM created_at)    AS dy
FROM orders;

# dedicated functions for each part
SELECT YEAR(created_at), MONTH(created_at), DAY(created_at),
       HOUR(created_at), MINUTE(created_at), SECOND(created_at),
       DAYOFWEEK(created_at), DAYOFYEAR(created_at),
       WEEK(created_at), QUARTER(created_at);

# names instead of numbers
SELECT DAYNAME(created_at), MONTHNAME(created_at);

# group orders by month for a report
SELECT YEAR(created_at) AS yr, MONTH(created_at) AS mon,
       COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
GROUP BY yr, mon
ORDER BY yr, mon;

Conversão de Fuso Horário

Defina o time_zone da sessão para que colunas TIMESTAMP exibam na hora local; zonas nomeadas (Asia/Shanghai) precisam das tabelas de fuso horário do sistema carregadas via mysql_tzinfo_to_sql. CONVERT_TZ converte um datetime literal entre zonas. Boa prática: guarde cada um dos horários em UTC (use TIMESTAMP ou DATETIME em UTC), defina o time_zone de cada sessão para exibição e converta com CONVERT_TZ quando necessário. Isso evita ambiguidade entre usuários de regiões diferentes.

mysql
# see the session time zone
SELECT @@session.time_zone, @@global.time_zone;

# set the session time zone (UTC offset or named, needs tz data loaded)
SET time_zone = '+08:00';            # Asia/Shanghai offset
SET time_zone = 'Asia/Shanghai';     # named zone (load tz tables first)

# convert a datetime between time zones
SELECT CONVERT_TZ('2024-07-02 09:00:00', '+00:00', '+08:00');
-- 2024-07-02 17:00:00

# named zones require the timezone tables to be loaded:
#   mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

# TIMESTAMP columns auto-convert to the session time zone on display;
# DATETIME columns do not -- they store the literal value

# store all times in UTC and convert on the way out
SELECT CONVERT_TZ(created_at, '+00:00', @@session.time_zone)
FROM orders;

Was this helpful?