Primeros pasos
Conectar a MySQL
El cliente mysql es la CLI estándar de MySQL. Usa siempre -p (sin espacio después) para que la contraseña se pida de forma segura en lugar de verse en el historial del shell. -h fija el host, -P (mayúscula) el puerto. \s imprime el estado de la conexión. Usa -e para ejecutar consultas puntuales en scripts.
# 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();Gestión de bases de datos
Usa utf8mb4 (no utf8) para soportar Unicode por completo, incluidos los emoji — el utf8 de MySQL es un subconjunto de 3 bytes que no puede guardar la totalidad de los caracteres. utf8mb4_unicode_ci es la colación recomendada para una ordenación correcta. IF EXISTS/IF NOT EXISTS evitan errores en scripts. DROP DATABASE elimina todas las tablas y datos al instante.
# 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 y describir objetos
Los comandos SHOW son herramientas de introspección específicas de MySQL. DESCRIBE da una visión rápida de columnas; SHOW CREATE TABLE da el DDL completo reutilizable. Añade \G en lugar de ; para salida vertical — mucho más legible para filas anchas como SHOW CREATE TABLE.
# 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\GEstado y variables del servidor
Las variables de MySQL tienen ámbitos SESSION (conexión actual) y GLOBAL. SET GLOBAL cambia el servidor en marcha pero se reinicia al reiniciar — persiste los ajustes en my.cnf en su lugar. @@var lee valores. SHOW STATUS expone contadores de runtime (conexiones, uptime, rendimiento) útiles para monitoreo.
# 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';Comentarios y fundamentos de sentencias
MySQL soporta tres estilos de comentario: -- (exige un espacio final), bloques /* */ y # hasta el fin de línea. /*! ... */ es un comentario ejecutable especial — el código dentro corre solo en MySQL y es ignorado por otros motores SQL, útil para archivos de esquema portables. \G ejecuta y muestra los resultados en vertical.
-- 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\GArchivo de configuración (my.cnf)
my.cnf (Linux) / my.ini (Windows) guarda los ajustes persistentes de servidor y cliente organizados en secciones: [mysqld] para el servidor, [client]/[mysql] para el cliente. innodb_buffer_pool_size es la palanca de ajuste más importante para InnoDB (típicamente 50-75% de la RAM). Tras editar, reinicia el servidor. Verifica con mysql --help o SHOW VARIABLES.
# /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]>\_Operaciones DDL
Crear tabla
CREATE TABLE define columnas, tipos, restricciones y opciones de tabla. InnoDB es el motor por defecto y recomendado (soporta transacciones, bloqueo a nivel de fila, claves foráneas). BIGINT UNSIGNED para AUTO_INCREMENT evita el desbordamiento. DECIMAL(p,s) es exacto para dinero. ENUM valida contra una lista fija. TIMESTAMP DEFAULT CURRENT_TIMESTAMP se autocompleta al insertar.
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 tabla
ALTER TABLE evoluciona el esquema sin recrear los datos. ADD COLUMN con AFTER lo coloca en una posición concreta (el orden de columnas es cosmético en MySQL). MODIFY cambia tipo/valor por defecto; RENAME COLUMN (8.0+) es más limpio que CHANGE. Los ALTER grandes pueden reconstruir y bloquear la tabla — usa pt-online-schema-change o DDL en línea para tablas grandes. RENAME TABLE es atómico.
# 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;Drop y truncate de tabla
DROP TABLE elimina la tabla por completo; TRUNCATE la vacía pero conserva la estructura y reinicia AUTO_INCREMENT a su valor inicial. TRUNCATE es más rápido que DELETE porque se salta la eliminación por fila y el registro, pero es DDL (no puede revertirse, no dispara triggers). Desactiva foreign_key_checks al truncar tablas referenciadas por otras.
# 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 genera números secuenciales para una clave primaria — cada tabla solo puede tener uno. LAST_INSERT_ID() devuelve el id generado por el INSERT más reciente en la sesión actual (por conexión, seguro para uso concurrente). Pueden aparecer huecos tras deletes, rollbacks o inserts que especifiquen valores explícitos. Para reiniciar, usa ALTER TABLE ... AUTO_INCREMENT = n.
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'\GRestricciones de tabla
Las restricciones imponen integridad de datos a nivel de base de datos. InnoDB soporta claves primarias, UNIQUE, NOT NULL, CHECK (aplicada desde 8.0.16) y claves foráneas. FOREIGN KEY ... ON DELETE CASCADE elimina las filas hijas cuando se elimina el padre; SET NULL las pone a nulo; RESTRICT/NO ACTION bloquean la eliminación. Nombra las restricciones explícitamente para gestionarlas mejor. Las claves foráneas exigen índices en ambas columnas.
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'));Tablas temporales
Las tablas TEMPORARY existen solo para la sesión actual y se eliminan automáticamente al cerrarse. Pueden ocultar una tabla real del mismo nombre, útil para refactorizar con seguridad o preparar datos. Dos sesiones pueden crear tablas temporales con el mismo nombre sin conflicto. No son visibles para otras conexiones y por defecto no se escriben al binlog.
# 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;Tipos de datos
Tipos numéricos
Usa DECIMAL para valores monetarios donde la exactitud importa — FLOAT/DOUBLE son aproximados y acumulan errores de redondeo. UNSIGNED duplica el rango positivo pero prohíbe los negativos. El ancho de visualización (por ejemplo, INT(11)) está obsoleto en MySQL 8.0 y se ignora — nunca limitó el rango almacenado. BIGINT UNSIGNED se recomienda para AUTO_INCREMENT para evitar desbordamiento. SERIAL es un atajo práctico para una clave sustituta.
# 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 UNIQUETipos de cadena y texto
VARCHAR es casi siempre preferible a CHAR (que rellena con espacios) salvo para códigos cortos de longitud fija. Para el utf8mb4 de MySQL cada carácter puede ocupar hasta 4 bytes — una columna VARCHAR(255) necesita hasta 1020 bytes más la longitud, lo que importa para los límites de clave de índice (3072 bytes en InnoDB). Las familias TEXT/BLOB almacenan datos grandes fuera de página; no pueden tener valor por defecto e indexarlas exige una longitud de prefijo.
# 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 utf8mb4Tipos de fecha y hora
DATETIME guarda una fecha/hora literal sin conciencia de zona horaria; TIMESTAMP guarda UTC y convierte al time_zone de la sesión al mostrarse, haciéndolo mejor para apps de varias zonas — pero tiene un límite de 2038 en compilaciones de 32 bits. Usa DATETIME(6)/TIMESTAMP(6) para precisión de microsegundos. CURRENT_TIMESTAMP funciona como valor por defecto para ambos desde MySQL 5.6.5. Fija el time_zone de sesión por conexión con SET time_zone = '+08:00';.
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 rangeTipos ENUM y SET
ENUM guarda de forma compacta (1-2 bytes) uno de una lista fija de valores de cadena y rechaza valores desconocidos en modo estricto. SET guarda cualquier combinación de hasta 64 miembros como máscara de bits. Ambos hacen costosos los cambios de esquema (añadir/reordenar miembros necesita ALTER TABLE) y son incómodos de consultar — muchos equipos prefieren una tabla de búsqueda o VARCHAR con CHECK. FIND_IN_SET ayuda a consultar columnas SET.
# 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
El tipo JSON (MySQL 5.7.8+) guarda JSON en un formato binario validado al escribir y soporta acceso rápido a miembros. Es preferible a guardar JSON en una columna TEXT porque puedes indexar y consultar dentro. Usa JSON_OBJECT(), JSON_ARRAY() y JSON_MERGE_PATCH() para construir valores. Para consultar con frecuencia unas pocas claves, extráelas a columnas generadas e indexa esas por el mejor rendimiento.
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 y binarios
BLOB guarda datos binarios arbitrarios (imágenes, archivos) pero es incómodo de consultar y no puede tener valor por defecto. Para archivos grandes, prefiere guardar una ruta en la base de datos y el archivo en disco/almacenamiento de objetos. Guarda UUIDs como BINARY(16) (16 bytes) en lugar de CHAR(36) (36 bytes) por eficiencia de índice — MySQL 8.0 añade UUID_TO_BIN()/BIN_TO_UUID() con intercambio opcional para amabilidad de índice.
# 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;DML (INSERT / UPDATE / DELETE)
INSERT
El INSERT de varias filas es mucho más rápido que los inserts de una sola fila repetidos porque amortiza el parseo, los viajes de red y las actualizaciones de índice. INSERT ... SELECT copia datos entre tablas. La sintaxis SET es específica de MySQL y cómoda para una fila. Lista siempre las columnas objetivo explícitamente para que la sentencia sobreviva a cambios de esquema. Usa INSERT IGNORE o ON DUPLICATE KEY UPDATE para inserts idempotentes.
# 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 — cuando ocurre un conflicto de UNIQUE/PRIMARY KEY, MySQL actualiza la fila existente en lugar de dar error. VALUES(col) se refiere al valor que se habría insertado; MySQL 8.0.19+ lo desaconseja en favor de alias de fila (AS new). Es más eficiente que un SELECT-then-INSERT/UPDATE separado porque es atómico. INSERT IGNORE se salta los conflictos silenciosamente.
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
Incluye siempre una cláusula WHERE — un UPDATE sin ella modifica cada fila. MySQL soporta UPDATE multi-tabla con JOIN, útil para desnormalizar valores calculados. ORDER BY + LIMIT habilita actualizaciones por lotes seguras que evitan locks largos en tablas enormes. En modo safe-updates el cliente rechaza UPDATE/DELETE sin una clave en el WHERE, una guardia contra cambios masivos accidentales.
# 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 elimina filas de una en una y es transaccional (puede revertirse) y dispara triggers. El DELETE multi-tabla puede eliminar de varias tablas en una sentencia. Para vaciar una tabla entera, TRUNCATE es mucho más rápido y reinicia AUTO_INCREMENT, pero no es transaccional. El modificador QUICK (MyISAM) se salta actualizar las hojas del índice — rara vez necesario con InnoDB. Usa siempre una cláusula WHERE.
# 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 es específico de MySQL: si existe una fila con la misma PK/clave UNIQUE, se elimina y se inserta una nueva; si no, simplemente inserta. Es más simple que upsert pero tiene efectos secundarios — dispara triggers DELETE y luego INSERT (no UPDATE), reinicia AUTO_INCREMENT y deja las columnas no proporcionadas en sus valores por defecto. Prefiere INSERT ... ON DUPLICATE KEY UPDATE para la mayoría de casos de uso.
# 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 listedLOAD DATA INFILE
LOAD DATA INFILE es la forma más rápida de cargar CSV en lote — 20 veces más veloz que sentencias INSERT porque se salta el parseo SQL. El servidor lee archivos de un directorio seguro (secure_file_priv); LOCAL hace que el cliente envíe el archivo pero está deshabilitado por defecto por seguridad. Usa la captura de columnas con @var y luego SET para transformar valores durante la carga. SELECT ... INTO OUTFILE es la exportación simétrica.
# 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';Consultas SELECT
SELECT básico
Evita SELECT * en producción — transfiere datos innecesarios, se rompe si se añaden/quitan columnas e impide que el optimizador use índices cubrientes. Nombra las columnas que necesites. LIMIT con OFFSET implementa paginación pero es lento para offsets grandes (sigue escaneando las filas saltadas); la paginación por keyset (WHERE id > last_id LIMIT n) escala mucho mejor.
# 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 y operadores
Las comparaciones con NULL dan NULL (tratado como false), así que usa siempre IS NULL / IS NOT NULL. Un NULL en una lista IN/NOT IN puede producir resultados vacíos sorprendentes — usa NOT EXISTS o una subconsulta correlacionada en su lugar. BETWEEN es inclusivo. MySQL evalúa AND antes que OR, así que entre paréntesis las condiciones mezcladas. Para el mejor rendimiento pon la condición más selectiva primero y asegúrate de que las columnas indexadas queden a la izquierda del operador.
# 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 resultORDER BY y LIMIT
ORDER BY ordena resultados; las claves múltiples se aplican de izquierda a derecha. FIELD() habilita la ordenación personalizada por una lista enumerada. Para conjuntos de resultados grandes, ORDER BY ... LIMIT aún puede escanear y ordenar el conjunto completo salvo que un índice lo soporte — añade un índice sobre las columnas del ORDER BY. El patrón de funciones de ventana (ROW_NUMBER OVER PARTITION BY) es la solución canónica de top-N por grupo en MySQL 8.0+.
# 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 y agrupar valores
DISTINCT colapsa filas idénticas; con varias columnas deduplica la combinación. COUNT(DISTINCT col) cuenta valores únicos no nulos. DISTINCT es esencialmente un GROUP BY sobre todas las columnas seleccionadas. Para deduplicar conservando una fila representativa (por ejemplo, la más nueva), usa ROW_NUMBER() OVER (PARTITION BY ...). DISTINCT puede ser caro — construye una tabla temporal, así que prefiere un índice.
# 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;Coincidencia de patrones LIKE y REGEXP
LIKE es simple pero los comodines iniciales (LIKE '%x') derrotan a los índices y fuerzan un escaneo completo. La colación decide la sensibilidad a mayúsculas — utf8mb4_0900_ai_ci no distingue acentos ni mayúsculas. REGEXP/RLIKE añaden toda la potencia de regex pero nunca usan índice. MySQL 8.0 añade REGEXP_LIKE, REGEXP_REPLACE, REGEXP_INSTR y REGEXP_SUBSTR para un manejo de patrones más rico. Para búsqueda de texto completo usa un índice FULLTEXT con MATCH ... AGAINST.
# 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;Expresión CASE
CASE es la expresión condicional de SQL. La forma buscada evalúa las condiciones WHEN de arriba abajo y devuelve el THEN de la primera coincidencia, o el ELSE (o NULL). CASE puede usarse en SELECT, WHERE, ORDER BY y agregados — el patrón SUM(CASE ...) es un pivote clásico. CASE nunca cortocircuita la agregación, así que cada fila cuenta. IF() e IFNULL() son atajos de MySQL más cortos para casos simples.
# 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;JOINs
INNER JOIN
INNER JOIN devuelve filas solo cuando hay coincidencia en ambas tablas — las filas sin coincidencia de cualquiera de los lados se descartan. JOIN es abreviatura de INNER JOIN. USING (col) es una alternativa concisa a ON a.col = b.col cuando el nombre de columna es idéntico y aparece una vez en el resultado. INNER JOIN es el tipo de join por defecto y más común para relacionar tablas normalizadas.
# 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 y RIGHT JOIN
LEFT JOIN conserva cada fila de la tabla izquierda y rellena el lado derecho con NULL cuando no hay coincidencia. El truco WHERE o.id IS NULL (anti-join) encuentra filas de la izquierda sin fila derecha coincidente — a menudo más claro y rápido que NOT IN, especialmente con NULLs. RIGHT JOIN es simétrico; la mayoría reordena las tablas y usa LEFT JOIN por legibilidad. Los NULLs en la clave de join nunca coinciden.
# 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 devuelve el producto cartesiano — cada combinación de las filas de ambas tablas. Es útil para generar combinaciones o sembrar matrices de dimensión, pero produce N x M filas así que es caro en tablas grandes. Una coma entre tablas (FROM a, b) es un CROSS JOIN implícito. El patrón de CTE recursivo es la forma estándar de generar una tabla de secuencia/dimensión en MySQL 8.0+.
# 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
Un self join referencia la misma tabla dos veces con alias distintos para relacionar filas dentro de ella — clásico para jerarquías jefe/empleado o listas de adyacencia. La condición a.id < b.id evita pares simétricos duplicados. Para jerarquías profundas usa un CTE recursivo (MySQL 8.0+), que recorre el árbol sin límite de profundidad fijo y puede construir salida de árbol indentada con REPEAT/CONCAT.
# 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;Solución para FULL OUTER JOIN
MySQL no implementa FULL OUTER JOIN. La emulación estándar es un LEFT JOIN UNION un RIGHT JOIN — UNION deduplica para que cada fila coincidente aparezca una vez y las filas sin coincidencia de cualquier lado aparezcan con NULLs del otro. Para resultados grandes prefiere UNION ALL con una estrategia de deduplicación, ya que el DISTINCT implícito de UNION es costoso. Si solo necesitas las filas sin coincidencia, usa dos anti-joins.
# 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 branchNATURAL JOIN y USING
NATURAL JOIN es frágil — hace join sobre cada nombre de columna compartido, así que añadir una columna puede cambiar los resultados silenciosamente; evítalo en producción. USING (col) es más seguro y colapsa la columna de join a una copia en la salida. STRAIGHT_JOIN fuerza el orden de tablas que usa el optimizador y es una anulación manual de planes malos; quítalo una vez arreglado el problema real. Prefiere ON explícito por claridad.
# 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;Agregación y agrupación
Funciones de agregación
Las funciones de agregación colapsan muchas filas en una: COUNT, SUM, AVG, MIN, MAX. COUNT(*) cuenta filas; COUNT(col) cuenta los valores no nulos de col; COUNT(DISTINCT col) cuenta valores únicos. AVG ignora los NULLs (no los ceros) lo que puede sesgar resultados — usa COALESCE(col,0) si quieres que el NULL se trate como cero. Los agregados sin GROUP BY devuelven una sola fila incluso en una tabla vacía.
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 las filas que comparten los valores de la columna agrupada en una fila de salida, con agregados calculados por grupo. MySQL 8.0 habilita ONLY_FULL_GROUP_BY por defecto, que rechaza las consultas que seleccionan columnas que no están en GROUP BY (las versiones anteriores devolvían una fila arbitraria — un bug común). Agrupa por expresiones o alias. Para reportes de series temporales, agrupa por YEAR()/MONTH()/DATE() de una marca de tiempo.
# 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 BYHAVING
HAVING filtra el resultado de la agregación mientras que WHERE filtra las filas de entrada antes de agrupar — así HAVING puede referenciar agregados (SUM, COUNT) y WHERE no. Pon los filtros a nivel de fila en WHERE por eficiencia (reducen las filas antes de agrupar). HAVING acepta alias en MySQL (HAVING users > 100). Una consulta puede usar ambos: WHERE estrecha filas, GROUP BY las agrupa en cubetas, HAVING estrecha cubetas.
# 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 añade filas superagregadas extra para cada nivel de agrupación más un gran total, donde la columna agrupada pasa a NULL. GROUPING(col) devuelve 1 en una fila de subtotal/total para que puedas etiquetar los NULLs como 'ALL'. ROLLUP funciona de izquierda a derecha sobre las columnas del GROUP BY. No hay WITH CUBE en MySQL — emúlalo con UNION ALL de varias consultas ROLLUP si necesitas cada combinación de dimensiones.
# 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 es la función de agregación de cadenas de MySQL — concatena los valores de un grupo en una sola cadena. Usa DISTINCT para deduplicar, ORDER BY para ordenar y SEPARATOR para cambiar el delimitador (',' por defecto). El resultado se acota a group_concat_max_len (1024 bytes por defecto); súbelo para listas más largas. Otras bases de datos lo llaman LISTAGG/STRING_AGG — GROUP_CONCAT es el equivalente de 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 ventana
ROLLUP encoge el resultado añadiendo filas de subtotal, mientras que un agregado de ventana (OVER) mantiene el mismo número de filas y añade el agregado como columna extra — así puedes ver detalle y totales juntos. Usa ROLLUP para reportes resumidos y funciones de ventana cuando necesites contexto por fila junto al agregado. Los totales acumulados son un caso de uso clásico de SUM OVER (ORDER BY ...).
# 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;Subconsultas y CTEs
Subconsulta escalar
Una subconsulta escalar devuelve un solo valor (una fila, una columna) y puede usarse donde una expresión sea válida — lista de SELECT, WHERE, HAVING. Si no devuelve filas el valor es NULL. El optimizador a menudo puede convertir una subconsulta escalar correlacionada en un join (materialización de subconsultas). Por claridad y a veces por rendimiento, un CTE o JOIN es preferible cuando la misma subconsulta se referencia repetidamente.
# 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 columnSubconsultas IN / ANY / ALL
IN coincide con cualquier valor devuelto por la subconsulta; NOT IN es el complemento pero se envenena con NULLs — si la subconsulta devuelve un NULL, NOT IN no devuelve ninguna fila, así que excluye siempre los NULLs o usa NOT EXISTS. ALL es verdadero cuando la comparación se cumple para cada valor devuelto; ANY/SOME cuando se cumple para al menos uno. El optimizador a menudo reescribe estos en semi-joins.
# 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 y NOT EXISTS
EXISTS prueba la existencia de filas sin devolver datos — deja de escanear en la primera coincidencia, así que es eficiente para comprobaciones de presencia. La subconsulta es correlacionada (referencia la consulta externa). NOT EXISTS es la forma segura con NULLs de encontrar filas sin coincidencias y generalmente se prefiere sobre NOT IN. SELECT 1 dentro de EXISTS es convencional; la lista de columnas es irrelevante. El optimizador a menudo convierte EXISTS en un semi-join.
# 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 checksTablas derivadas
Una tabla derivada es una subconsulta en la cláusula FROM — materializa un resultado intermedio del que puedes seleccionar o hacer join. Aliasala siempre. MySQL 8.0 puede fusionar muchas tablas derivadas en la consulta externa (derived_merge) para mejores planes, pero los agregados complejos o LIMIT suelen forzar la materialización. Por legibilidad y reutilización dentro de una consulta, prefiere un CTE con nombre (WITH) sobre una tabla derivada en línea.
# 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 aliasedSubconsulta correlacionada
Una subconsulta correlacionada referencia una columna de la consulta externa, así que se reevalúa lógicamente por cada fila externa — lo que puede ser lento en conjuntos grandes. El optimizador puede transformarla en un join o usar una caché (caché de subconsultas) para mitigarlo. Para problemas de ranking/top-N (por ejemplo, el n-ésimo salario más alto) una función de ventana como DENSE_RANK() OVER (ORDER BY salary DESC) es más clara y rápida — recurre a ella en MySQL 8.0+.
# 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+Expresiones de tabla comunes (CTE)
Un CTE (cláusula WITH) nombra una subconsulta por legibilidad y reutilización dentro de una sentencia; MySQL 8.0+ lo soporta. RECURSIVE habilita los CTEs autorreferentes — esenciales para recorrer árboles y generar secuencias (UNION ALL para recursar, UNION para deduplicar). Un CTE recursivo necesita un caso base, un brazo recursivo y una guardia de terminación. Varios CTEs van separados por comas y pueden referenciar a anteriores en el mismo WITH.
# 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);Índices
Crear índice
Un índice es una estructura de datos (B+Tree en InnoDB) que acelera las búsquedas y las ordenaciones al coste de sobrecarga de escritura y almacenamiento. Crea índices sobre columnas usadas en WHERE, JOIN y ORDER BY. Los índices UNIQUE también imponen una restricción de unicidad. Los índices de prefijo en columnas de texto largo ahorran espacio pero no pueden usarse para escaneos cubrientes ni ORDER BY. Toda clave primaria está agrupada en InnoDB — los índices secundarios guardan el valor de la PK como puntero de fila.
# 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 compuesto
Los índices compuestos se ordenan primero por su columna más a la izquierda, así que solo ayudan a consultas que filtran por un prefijo más a la izquierda de la lista de columnas — el orden de columnas es crítico. Pon primero la columna de igualdad más selectiva, luego los rangos. Un índice cubriente contiene cada columna que la consulta necesita, permitiendo un escaneo de solo índice que se salta por completo la búsqueda en la tabla (EXPLAIN muestra 'Using index'). No sobreindices — cada índice ralentiza las escrituras.
# 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
Los índices FULLTEXT habilitan la búsqueda de texto en lenguaje natural y booleana, mucho mejor que LIKE '%palabra%'. MATCH ... AGAINST devuelve una puntuación de relevancia. El modo booleano soporta operadores: +requerir, -excluir, *comodín, >aumentar, ~disminuir. Requiere InnoDB (5.6+) o MyISAM y una longitud mínima de palabra (ft_min_word_len / innodb_ft_min_token_size, 3 por defecto). Para búsqueda seria a escala considera un motor dedicado como Elasticsearch.
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
Los índices espaciales (R-tree) aceleran las consultas geográficas sobre columnas GEOMETRY/POINT/POLYGON. MySQL 8.0 se estandarizó en SRID 4326 (coordenadas GPS WGS 84) y añadió ST_Distance_Sphere para distancias reales en metros. La columna debe ser NOT NULL. Usa ST_Within/ST_Contains/ST_Distance para filtrar. Expresa siempre las coordenadas como (longitud, latitud) para coincidir con la convención X/Y. Los índices espaciales hacen las consultas de radio mucho más rápidas.
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));Gestión de índices
SHOW INDEX lista los índices de una tabla con orden de columnas y cardinalidad (estimación de valores distintos). DROP INDEX elimina un índice; renombrar (8.0+) evita eliminar+reconstruir. OPTIMIZE TABLE reconstruye la tabla para recuperar espacio fragmentado y refrescar las estadísticas de índice — con mucho lock, así que prográmalo con poco tráfico. Las estadísticas obsoletas pueden engañar al optimizador; corre ANALYZE TABLE para refrescarlas tras cargas de datos grandes.
# 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 invisibles y hints
Los índices invisibles (8.0+) te permiten probar quitar un índice con seguridad — el optimizador lo ignora pero las escrituras lo mantienen al día, así que puedes volver a activarlo al instante si las consultas retroceden; elimínalo una vez confiado. Los hints del optimizador (/*+ ... */) se prefieren a la sintaxis antigua USE/FORCE/IGNORE INDEX y solo afectan a la sentencia etiquetada. Usa los hints con moderación — son una tirita para mala distribución de datos o estadísticas faltantes.
# 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]';Vistas
Crear vista
Una vista es una consulta guardada que se comporta como una tabla virtual — simplifica consultas complejas, abstrae cambios de esquema e impone visibilidad de filas/columnas. CREATE OR REPLACE actualiza la definición en un paso. Las vistas no guardan datos (re-ejecutan la consulta subyacente cada vez salvo materialización) así que no añaden coste de escritura pero tampoco aceleración de lectura. Las vistas se fusionan en la consulta cuando es posible.
# 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\GVistas actualizables
Una vista sobre una sola tabla base sin agregados/distinct es actualizable — INSERT/UPDATE/DELETE se propagan a la tabla subyacente. Por defecto, un UPDATE que mueve una fila fuera del WHERE de la vista tiene éxito (la fila simplemente desaparece de la vista); WITH CHECK OPTION bloquea tales cambios para que las filas nunca salgan del ámbito de la vista. Las vistas con JOINs, GROUP BY, DISTINCT o subconsultas generalmente no son actualizables.
# 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 de las vistas
CHECK OPTION impone que las filas cambiadas a través de una vista sigan visibles para esa vista. LOCAL comprueba solo el WHERE de la propia vista; CASCADED (el valor por defecto) también impone los WHERE de cualquier vista subyacente que referencie. Usa CASCADED al apilar vistas para mantener toda la cadena consistente; usa LOCAL solo cuando deliberadamente quieras relajar el filtro de una vista subyacente.
# 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)Gestionar vistas
ALTER VIEW redefinice una vista existente sin eliminarla (conservando los permisos). DROP VIEW IF EXISTS evita un error cuando la vista está ausente. RENAME TABLE también funciona para vistas pero es poco común. information_schema.views expone la definición de cada vista, si es actualizable y el tipo de seguridad — útil para auditar. Una vista depende de sus tablas base; eliminar una tabla base deja la vista inválida hasta recrearla.
# 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';Seguridad y determinismo de vistas
SQL SECURITY DEFINER corre la vista con los privilegios del definidor — útil para exponer datos filtrados a usuarios que carecen de acceso a las tablas base (por ejemplo, una vista de seguridad a nivel de fila). INVOKER comprueba los privilegios de quien llama en su lugar. ALGORITHM MERGE pliega la vista en la consulta (más rápido, permite vistas actualizables); TEMPTABLE la materializa primero (necesario para algunas construcciones pero pierde actualizabilidad y puede ser más lento). UNDEFINED deja que MySQL elija.
# 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';Procedimientos almacenados
Crear y llamar procedimientos
Un procedimiento almacenado es un bloque de SQL con nombre y precompilado. Como los procedimientos pueden contener punto y coma, cambias el DELIMITER para que el CREATE completo corra como una sola sentencia y luego lo reinicias. CALL ejecuta un procedimiento. Los procedimientos no devuelven un valor directamente pero pueden devolver conjuntos de resultados y modificar parámetros OUT. Corren del lado del servidor, recortando viajes de red para lógica de varios pasos, pero son más difíciles de versionar y probar que el código de la aplicación.
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\GParámetros IN / OUT / INOUT
Los parámetros son IN (entrada de solo lectura), OUT (escrito de vuelta a quien llama vía una variable de sesión) o INOUT (ambos). SELECT ... INTO asigna el resultado escalar de una consulta a una variable. El ejemplo envuelve un débito/crédito en una transacción con SELECT FOR UPDATE para bloquear filas y prevenir actualizaciones perdidas. Usa parámetros OUT para devolver códigos de estado; los conjuntos de resultados se devuelven directamente cuando haces SELECT dentro del procedimiento.
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;Variables y flujo de control
Los programas almacenados soportan variables locales (DECLARE, con ámbito BEGIN...END), variables de usuario (@var, con ámbito de sesión) y variables de sistema. El flujo de control incluye IF/ELSEIF/ELSE, CASE y tres bucles: LOOP (con LEAVE/ITERATE), WHILE (condición primero) y REPEAT (condición al final). Nombra siempre los bucles al anidarlos para que LEAVE apunte al correcto. SET asigna un valor; SELECT ... INTO copia un escalar de consulta a una variable.
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
Un cursor permite a un procedimiento almacenado iterar fila por fila sobre un conjunto de resultados. Declara primero el cursor, luego un CONTINUE HANDLER FOR NOT FOUND para detectar el agotamiento (fija un flag y haz LEAVE del bucle). FETCH recupera una fila a variables. Los cursores son de solo lectura y solo hacia adelante en MySQL. El procesamiento fila por fila es lento comparado con el SQL por conjuntos — prefiere un único UPDATE/INSERT ... SELECT cuando sea posible; usa cursores solo cuando la lógica sea genuinamente secuencial.
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();Manejadores de errores
Los manejadores capturan errores dentro de los programas almacenados: CONTINUE corre el cuerpo del manejador y luego retoma tras la sentencia fallida; EXIT corre el cuerpo y sale del bloque BEGIN...END; UNDO revierte (obsoleto). Captura SQLEXCEPTION para cualquier error, SQLWARNING para advertencias, o SQLSTATE/errno concretos (por ejemplo, 1062 clave duplicada). RESIGNAL relanza el error actual tras la limpieza. Las condiciones con nombre (CONDITION FOR) hacen los manejadores autoexplicativos.
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';Metadatos de procedimientos
SHOW PROCEDURE STATUS lista procedimientos; SHOW CREATE PROCEDURE devuelve el cuerpo completo. information_schema.routines da metadatos más ricos incluidos determinismo y tipo de seguridad. Marca una función DETERMINISTIC (READS SQL DATA / NO SQL) si su salida depende solo de sus entradas — requerido para el log binario y la replicación de funciones. Los procedimientos no necesitan el flag determinista. Usa estas vistas para auditar e inventariar rutinas.
# 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';Funciones
Funciones de cadena
LENGTH cuenta bytes, CHAR_LENGTH cuenta caracteres (importante para utf8mb4 multibyte). SUBSTRING está indexado desde 1; un inicio negativo cuenta desde el final. CONCAT devuelve NULL si cualquier argumento es NULL — usa CONCAT_WS para saltar los NULLs. SUBSTRING_INDEX divide por un delimitador y conserva las primeras N partes (N negativo conserva desde la derecha). TRIM tiene variantes BOTH/LEADING/TRAILING y puede recortar cualquier carácter, no solo espacios.
# 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'Funciones numéricas
ROUND redondea el medio alejándose de cero; TRUNCATE corta dígitos sin redondear. MOD y % son intercambiables. RAND() devuelve un float en [0,1); ORDER BY RAND() es una forma cómoda pero cara de muestrear filas porque ordena el conjunto de resultados completo — para tablas grandes usa un rango de PK aleatorio o muestreo de embalse. FORMAT añade separadores de miles y redondea a los decimales dados, devolviendo una cadena.
# 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, -1Funciones de fecha y hora
NOW() devuelve la fecha/hora actual en una sola llamada (consistente a lo largo de una sentencia); CURDATE/CURTIME devuelven las partes de fecha/hora. DATE_ADD/SUB con INTERVAL es la aritmética de fechas canónica — maneja correctamente los cambios de mes/año. DATEDIFF devuelve días (fecha1 - fecha2); TIMEDIFF devuelve un TIME. DATE_FORMAT y STR_TO_DATE usan los especificadores %Y %m %d %H %i %s (ojo, i minúscula para minutos, s para segundos).
# 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');Funciones personalizadas
Una función almacenada devuelve un único valor escalar y puede usarse dentro de expresiones (a diferencia de los procedimientos, que se CALL). Debe declarar características: DETERMINISTIC (misma entrada siempre da la misma salida, requerido para replicación basada en sentencias), y una de READS SQL DATA / NO SQL / MODIFIES SQL DATA. Las funciones no pueden devolver conjuntos de resultados ni usar control de transacciones. La combinación NO SQL + DETERMINISTIC es la más segura para computación pura.
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 allFunciones de flujo de control
IF() es un ternario compacto; IFNULL(a,b) devuelve a salvo que a sea NULL y entonces b (solo dos argumentos); COALESCE se generaliza a N argumentos. NULLIF(a,b) devuelve NULL cuando a=b, práctico para evitar la división por cero (x / NULLIF(y,0)). ISNULL() devuelve 1/0, no el valor — no lo confundas con IFNULL. CASE es la expresión condicional más flexible y portable y funciona en cualquier dialecto SQL.
# 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 y conversión
CAST y CONVERT cambian el tipo de un valor — esencial cuando MySQL haría de otro modo una conversión implícita que derrota a los índices (por ejemplo, comparar una cadena con una columna numérica). CAST(... AS DATE/DATETIME/SIGNED/UNSIGNED/CHAR/DECIMAL) cubre las necesidades comunes. Cuidado: CAST('123abc' AS SIGNED) devuelve 123 con una advertencia en lugar de dar error. Usa STR_TO_DATE para parsear formatos de fecha personalizados, ya que CAST espera un formato ISO estricto.
# 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;Triggers
Triggers BEFORE / AFTER
Un trigger se dispara automáticamente en INSERT/UPDATE/DELETE, ya sea BEFORE (puede modificar valores NEW, validar y abortar) o AFTER (para efectos secundarios como logging). FOR EACH ROW significa que el cuerpo corre una vez por fila afectada — los triggers de fila son el único tipo que MySQL soporta. Los triggers BEFORE son buenos para valores por defecto/normalizar datos; los AFTER para trabajo no crítico en cascada. Los triggers corren en la misma transacción que la sentencia disparadora.
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 auditoría (UPDATE)
Un uso común de los triggers es auditar cambios — captura OLD vs NEW a una tabla de historial. Comparar valores debe contar los NULLs (OLD.email <> NEW.email es NULL si cualquiera de los lados es NULL), así que protégelo con comprobaciones IS NULL. AFTER DELETE usa OLD (no hay NEW en delete). Mantén la lógica de auditoría ligera pues corre en cada cambio de fila. Recuerda que los triggers se disparan también para UPDATE/DELETE masivos, lo que puede ser lento en operaciones grandes.
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 y NEW
NEW guarda los valores entrantes de la fila, OLD los previos. Puedes modificar NEW en BEFORE INSERT/UPDATE para normalizar o poner valores por defecto; los triggers AFTER y DELETE los ven de solo lectura. SIGNAL SQLSTATE '45000' es la forma estándar de lanzar un error personalizado desde un trigger o programa almacenado (45000 significa 'excepción definida por el usuario no manejada'). Usa triggers BEFORE para validación que deba abortar el cambio; AFTER para efectos secundarios.
# 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 con varios eventos (FOLLOWS/PRECEDES)
Desde 5.7.2 una tabla puede tener más de un trigger para el mismo evento y momento, ordenados con FOLLOWS/PRECEDES. Cada trigger sigue manejando exactamente un evento (INSERT, UPDATE o DELETE) en un momento (BEFORE/AFTER) — para manejar varios eventos creas múltiples triggers. El orden importa cuando los triggers dependen de los efectos de otros. No hay trigger INSTEAD OF en MySQL (las vistas son actualizables directamente).
# 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 eventGestionar triggers
SHOW TRIGGERS lista cada trigger (fíjate en las comillas invertidas del nombre de columna 'Table' pues está reservado). SHOW CREATE TRIGGER devuelve el cuerpo completo y el definidor. information_schema.triggers da metadatos estructurados para auditoría. DROP TRIGGER IF EXISTS es seguro para scripts. Los triggers están atados a la tabla — eliminar la tabla elimina sus triggers. Vigila al definidor: como los procedimientos, los triggers corren por defecto con privilegios del definidor.
# 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 recordedTransacciones y locks
Control de transacciones
MySQL hace autocommit por defecto — cada sentencia es su propia transacción. START TRANSACTION/BEGIN inicia un bloque explícito terminado por COMMIT (guardar) o ROLLBACK (deshacer). Solo los motores transaccionales (InnoDB) soportan transacciones; MyISAM no. Crucialmente, las sentencias DDL (CREATE/ALTER/DROP/TRUNCATE) confirman implícitamente la transacción actual y no pueden revertirse, así que no mezcles DDL con DML transaccional esperando atomicidad.
# 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 backSavepoints
SAVEPOINT marca un punto con nombre dentro de una transacción al que puedes revertir parcialmente sin abortar toda la transacción. ROLLBACK TO SAVEPOINT deshace el trabajo posterior al savepoint; RELEASE SAVEPOINT descarta el marcador (el trabajo se queda). Los savepoints permiten probar un paso opcional y recuperarse con elegancia. No son visibles fuera de la transacción y desaparecen en COMMIT/ROLLBACK. Los savepoints anidados están soportados.
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;Niveles de aislamiento
El nivel de aislamiento intercambia consistencia por concurrencia. El REPEATABLE READ por defecto de MySQL previene lecturas sucias y no repetibles y, a diferencia del estándar SQL, también evita la mayoría de lecturas fantasma vía bloqueo next-key. READ COMMITTED es popular por menos bloqueo pero permite lecturas no repetibles. SERIALIZABLE convierte todas las lecturas en lecturas con bloqueo. InnoDB usa MVCC así que los lectores no bloquean a los escritores en ningún nivel salvo SERIALIZABLE.
# 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 locks a nivel de tabla y es mayormente una característica de la era MyISAM — el bloqueo a nivel de fila de InnoDB suele ser preferible. Un lock READ deja que otras sesiones lean pero bloquea escrituras; un lock WRITE bloquea al resto. Mientras sostienes locks solo puedes acceder a las tablas bloqueadas. LOCK TABLES confirma implícitamente cualquier transacción activa, así que no compone con transacciones. Prefiere SELECT ... FOR UPDATE para el control de concurrencia de InnoDB.
# 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 transactionSELECT ... FOR UPDATE / SHARE
SELECT ... FOR UPDATE toma un lock exclusivo de fila para que otras transacciones se bloqueen si intentan actualizar o bloquear la misma fila — esencial para secuencias de lectura-modificación-escritura como transferencias. FOR SHARE toma un lock compartido (otros pueden leer pero no escribir). Los locks se liberan en COMMIT/ROLLBACK. 8.0 añade NOWAIT (error en lugar de esperar) y SKIP LOCKED (saltar filas bloqueadas) — perfecto para workers de colas de trabajos que no deben bloquearse entre sí.
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 y detección
InnoDB detecta deadlocks automáticamente y revierte una transacción (la víctima) para que la otra continúe — recibes el error 1213. SHOW ENGINE INNODB STATUS muestra el último deadlock; habilita innodb_print_all_deadlocks para registrar cada uno. Previene deadlocks bloqueando filas en un orden consistente entre transacciones, manteniendo las transacciones cortas e indexando las columnas que bloqueas (de lo contrario los locks escalan a rangos grandes). Escribe siempre lógica de reintento alrededor de transacciones que puedan tener deadlock.
# 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 safeGestión de usuarios
Crear usuario y autenticación
Una cuenta de MySQL es siempre 'usuario'@'host' — el host controla desde dónde puede conectarse el usuario; '%' es un comodín para cualquier host. MySQL 8.0 usa por defecto caching_sha2_password (más seguro) que algunos clientes antiguos no soportan — recurre a mysql_native_password para ellos. Prefiere hosts de ámbito estrecho (10.0.0.%) sobre '%'. CREATE USER se prefiere sobre la sintaxis antigua GRANT ... IDENTIFIED BY, eliminada en 8.0.
# 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'@'%';Privilegios GRANT
GRANT asigna privilegios en cuatro ámbitos: global (*.*), base de datos (db.*), tabla (db.tabla) y columna/rutina. ALL PRIVILEGES concede la totalidad salvo GRANT OPTION. Usa el mínimo privilegio necesario — un usuario de reportes solo debería recibir SELECT. WITH GRANT OPTION deja al usuario dar sus privilegios a otros (peligroso). FLUSH PRIVILEGES rara vez se necesita ya que GRANT actualiza las tablas en memoria; úsalo solo tras editar manualmente las tablas de 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;Privilegios REVOKE
REVOKE elimina privilegios concedidos previamente; debes coincidir el ámbito exactamente. Para quitar la totalidad, incluido GRANT OPTION, usa REVOKE ALL PRIVILEGES, GRANT OPTION. Revocar no elimina al usuario — la cuenta sigue existiendo y puede conectarse pero no tiene privilegios hasta concedérsele de nuevo. Usa SHOW GRANTS para inspeccionar los privilegios efectivos de un usuario; revisa SIEMPRE antes de cambiar accesos. Eliminar a un usuario por completo es DROP USER.
# 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();Roles (MySQL 8.0+)
Los roles (8.0+) te permiten agrupar privilegios y asignarlos a muchos usuarios a la vez, simplificando la gestión de accesos. Un usuario al que se concede un rol no recibe sus privilegios hasta que el rol se active — SET DEFAULT ROLE lo activa por defecto al iniciar sesión. CURRENT_ROLE() muestra los roles activos. Cambia un rol una vez y cada usuario miembro hereda el cambio. Esto es mucho más limpio que conceder los mismos privilegios a cada usuario individualmente.
# 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();Gestión de contraseñas
ALTER USER es la forma moderna de gestionar contraseñas (SET PASSWORD está obsoleto). PASSWORD EXPIRE fuerza un restablecimiento en el próximo login — útil para políticas de rotación de contraseñas. El componente validate_password impone longitud/complejidad en MEDIUM (mayúsculas y minúsculas, dígito, carácter especial) y STRONG (además sin palabras de diccionario). ACCOUNT LOCK/UNLOCK deshabilita temporalmente una cuenta sin eliminarla — práctico para suspender accesos durante investigaciones.
# 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;Show grants y usuarios
mysql.user es la tabla del sistema que guarda todas las cuentas — consúltala para inventariar usuarios y flags como account_locked y password_expired. SHOW GRANTS es la vista más amigable y decodificada de los privilegios de un usuario. CURRENT_USER() es la identidad usada para las comprobaciones de privilegios (que puede diferir de USER(), la cadena de conexión) — importante al conectarse a través de un rol o proxy. Audita esto regularmente en busca de cuentas dormidas o sobredimensionadas.
# 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;Copia de seguridad y restauración
mysqldump
mysqldump es la herramienta clásica de respaldo lógico — emite sentencias SQL que recrean la base de datos. --single-transaction da una instantánea consistente de InnoDB sin bloquear usando una única transacción REPEATABLE READ. --routines/--triggers/--events incluyen los programas almacenados (de otro modo se omiten). Para bases de datos grandes prefiere Percona XtraBackup o MySQL Enterprise Backup (respaldos físicos) que son mucho más rápidos que mysqldump.
# 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.gzRestaurar desde volcado
Para restaurar, pasa el archivo de volcado por pipe al cliente mysql o usa SOURCE dentro de él. La base de datos objetivo debe existir ya (CREATE DATABASE y luego USE). Para restauraciones más rápidas desactiva las comprobaciones de claves foráneas y únicas y el autocommit durante la carga, y luego reactiva — el volcado mismo normalmente contiene ya estas guardias. Restaurar un volcado comprimido con gunzip evita descomprimir a disco. Prueba siempre las restauraciones en un servidor de staging.
# 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 a CSV
SELECT ... INTO OUTFILE escribe un archivo del lado del servidor limitado por el directorio secure_file_priv (o deshabilitado por completo si secure_file_priv es NULL) y exige el privilegio FILE — no puede sobrescribir un archivo existente. --batch --raw en el cliente mysql produce un archivo separado por tabulaciones localmente sin esas restricciones, ideal para trabajos cron. Las cláusulas FIELDS/LINES controlan delimitadores, comillas y escapes para compatibilidad CSV.
# 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 desde CSV
LOAD DATA INFILE es la forma más rápida de cargar CSV — mucho más veloz que sentencias INSERT. La forma del lado del servidor lee de una ruta bajo secure_file_priv; LOCAL lee un archivo del cliente (habilitado con local_infile en cliente y servidor). IGNORE 1 LINES se salta un encabezado. Usa la captura con @var de columnas y luego SET para transformar valores (por ejemplo, parsear una cadena de fecha). Para cargas muy grandes desactiva claves/foráneas y usa una única transacción.
# 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;Log binario (mysqlbinlog)
El log binario registra todas las sentencias que cambian datos para replicación y recuperación a un punto en el tiempo. SHOW MASTER STATUS da el archivo y la posición actuales (las coordenadas de replicación). mysqlbinlog convierte un archivo binlog de vuelta a SQL (o eventos de fila) para inspección o reproducción — combínalo con tiempos o posiciones de inicio/fin para recuperar a un momento preciso. PURGE BINARY LOGS recupera disco; deja que expire_logs_days/binlog_expire_logs_seconds lo automatice.
# 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 y replicación
El plugin clone (8.0+) copia físicamente un directorio de datos de InnoDB mucho más rápido que un volcado lógico, ideal para aprovisionar réplicas o snapshots. Para replicación, apunta una réplica a una fuente con CHANGE REPLICATION SOURCE TO (8.0+; antes CHANGE MASTER TO) y START REPLICA. GTID (SOURCE_AUTO_POSITION=1) hace el posicionamiento de replicación robusto. SHOW REPLICA STATUS revela retrasos y errores. Monitorea Seconds_Behind_Source para la salud de la réplica.
# 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\GOptimización de rendimiento
EXPLAIN
EXPLAIN revela el plan de consulta: qué índice se usa, cuántas filas se estiman y si hace falta un filesort o una tabla temporal. La columna 'type' clasifica las modalidades de acceso — const/eq_ref son lo mejor (búsqueda de fila única), ref/range usan un índice, index escanea el índice completo, ALL escanea la tabla entera (normalmente mal). 'Using index' significa un escaneo cubriente de solo índice; 'Using filesort'/'Using temporary' advierten de operaciones caras a optimizar.
# 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 ejecuta la consulta y reporta conteos y tiempos reales de filas por iterador, exponiendo dónde se gasta realmente el tiempo — mucho más preciso que las estimaciones del EXPLAIN plano. Úsalo en consultas SELECT en staging. Emparéjalo con SHOW WARNINGS para ver la consulta reescrita por el optimizador (tras fusionar vistas, plegar constantes). Para consultas de escritura usa EXPLAIN sobre un equivalente de solo lectura o inspecciona con performance_schema.
# 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\GHints del optimizador
Los hints del optimizador (/*+ ... */) anulan al planificador para una sola sentencia. Son más dirigidos que SET GLOBAL y sobreviven a reinicios de sentencia. Úsalos para arreglar un plan malo, pero trata también la causa subyacente (estadísticas rancias, índice faltante, datos sesgados) — los hints decaen a medida que cambian los datos. MAX_EXECUTION_TIME acota las consultas de solo lectura, protegiendo al servidor de SELECTs desbocados. SET_VAR ajusta una variable de sesión solo para una sentencia.
# 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 ...;Log de consultas lentas
El log de consultas lentas captura las consultas que exceden long_query_time (en segundos, se permite subsegundo). Habilita log_queries_not_using_indexes para atrapar los escaneos completos aunque sean rápidos. mysqldumpslow agrega consultas similares y las clasifica por tiempo/conteo/filas para que priorices a los peores infractores. El pt-query-digest de Percona da reportes mucho más ricos (huellas, percentiles) — la herramienta estándar de análisis de logs lentos. Rota y limpia el log regularmente.
# 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.logSHOW PROFILE y Performance Schema
SHOW PROFILE descompone una consulta en etapas (abrir tablas, ordenar, enviar datos) — práctico pero obsoleto. El equivalente moderno es performance_schema, un framework de instrumentación de poca sobrecarga habilitado por defecto en 8.0. Consulta events_statements_summary_by_digest para hallar las consultas que consumen más tiempo agregado en el servidor, y luego profundiza en events_stages_history_long para detalle por etapa. Así se encuentran cuellos de botella sistémicos, no solo consultas lentas puntuales.
# 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;Consejos de optimización de índices
El diseño de índices gana la mayoría de batallas de rendimiento. Sigue la regla del prefijo más a la izquierda, evita los comodines iniciales y no envuelvas columnas indexadas en funciones (o usa una columna generada + índice). Apunta a índices cubrientes en las consultas calientes. Mantén las condiciones WHERE sargables (capaces de usar argumento de búsqueda) para que un índice pueda usarse: created_at >= x AND created_at < y supera a DATE(created_at)=x. ANALYZE TABLE refresca las estadísticas de cardinalidad de las que depende el optimizador.
# 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;Operaciones JSON
Columna JSON e insert
El tipo JSON guarda JSON en un formato binario validado. Inserta con un literal de cadena JSON o construye valores con JSON_OBJECT() (pares clave/valor) y JSON_ARRAY(). El JSON inválido se rechaza al insertar. Como JSON se guarda como árbol parseado, el acceso a miembros es rápido, pero el documento entero se lee junto — para valores escalares consultados con frecuencia, extráelos a una columna generada e indexa esa para lo mejor de ambos mundos.
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 / -> / ->>
-> extrae un miembro JSON (devuelve JSON con comillas); ->> lo devuelve como texto sin comillas (la elección común para comparaciones). Las rutas usan $. para la raíz, $.clave para miembros, $.arr[0] para índices de array y $.a.b para anidamiento. JSON_CONTAINS_PATH comprueba la existencia de claves. JSON_EXTRACT es la forma de función de ->. Para hallar documentos por un valor de miembro eficientemente, añade una columna generada + índice sobre attrs->>'$.color'.
# 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 funciones devuelven un documento JSON modificado — debes asignar el resultado de vuelta para actualizar la columna. JSON_SET hace upsert (añade o actualiza); INSERT solo añade faltantes; REPLACE solo actualiza existentes; REMOVE elimina. JSON_MERGE_PATCH (RFC 7396) fusiona dos documentos y elimina las claves puestas a null — la forma estándar de aplicar un parche parcial. JSON_MERGE_PRESERVE conserva los arrays/objetos existentes en lugar de reemplazarlos.
# 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+) es el puente de JSON a relacional — trocea un documento JSON en filas que puedes unir, filtrar y agregar como cualquier tabla. La cláusula columns mapea rutas JSON a columnas tipadas. NESTED PATH desempaqueta arrays anidados en una pasada. Esto hace que las columnas JSON sean prácticas para datos semiestructurados: guarda JSON flexible, trocea a relacional con JSON_TABLE cuando necesites analítica SQL, e indexa una columna generada para los caminos calientes.
# 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'))Funciones de agregación JSON
JSON_ARRAYAGG recoge valores en un array JSON por grupo; JSON_OBJECTAGG construye un objeto clave->valor (las claves posteriores sobrescriben duplicados anteriores). Combinado con JSON_OBJECT puedes construir un array de objetos para una respuesta de API directamente en SQL — práctico para ensamblar payloads anidados sin postprocesar en la aplicación. Ambos se saltan las entradas NULL. Vigila el tamaño del resultado: un grupo enorme produce un valor JSON enorme.
# 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';Validación y esquema JSON
JSON_VALID comprueba que esté bien formado (los inserts ya lo imponen). JSON_PRETTY reformatea por legibilidad; JSON_STORAGE_SIZE reporta bytes. JSON_SCHEMA_VALID (8.0.17+) permite que una restricción CHECK imponga un JSON Schema (claves requeridas, tipos) — una forma ligera de dar estructura a las columnas JSON sin perder flexibilidad. JSON_TABLE y JSON_EXTRACT aceptan cláusulas ON EMPTY / ON ERROR para controlar el comportamiento cuando falta o es inválida una ruta.
# 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;Funciones de ventana
ROW_NUMBER()
ROW_NUMBER() asigna un entero secuencial único a cada fila dentro de su partición, ordenado como se especifica — la forma canónica de clasificar, deduplicar y elegir top-N por grupo. A diferencia de RANK/DENSE_RANK nunca produce empates. El patrón de DELETE-vía-CTE elimina duplicados conservando una fila por clave (no puedes eliminar directamente de un CTE, así que primero seleccionas los IDs a eliminar). Las funciones de ventana exigen MySQL 8.0+.
# 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() y DENSE_RANK()
RANK() da a los empates el mismo rango y salta los siguientes rangos (1,1,4,5); DENSE_RANK() da a los empates el mismo rango sin hueco (1,1,3,4); ROW_NUMBER() nunca empata (1,2,3,4). Elige por intención: el ranking tipo tabla de clasificación normalmente quiere RANK o DENSE_RANK. DENSE_RANK es la forma clásica de hallar el n-ésimo más alto por grupo (rank=2 devuelve el segundo salario distinto). Los tres comparten la misma sintaxis OVER().
# 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() y LEAD()
LAG() y LEAD() miran otra fila relativa a la actual — LAG mira hacia atrás, LEAD hacia adelante — sin un self join. Toman un offset opcional (1 por defecto) y un valor por defecto para cuando el offset cae fuera de la partición (NULL por defecto). Perfectas para diferencias periodo a periodo, comparaciones móviles y detectar rachas/rupturas en secuencias. Especifica siempre un ORDER BY para que 'anterior' esté bien definido.
# 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 ventana (SUM / AVG OVER)
Las funciones de agregación con OVER() calculan el agregado sobre una ventana móvil de filas en lugar de colapsarlas. Sin marco, SUM OVER (ORDER BY x) da un total acumulado; con ROWS BETWEEN 6 PRECEDING AND CURRENT ROW es una media móvil de 7 filas final. Un OVER() vacío calcula el gran total sobre todas las filas. PARTITION BY reinicia la ventana por grupo. Los marcos usan límites ROWS (físicos) o RANGE (basados en valor).
# 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 devuelven el valor de una fila concreta dentro del marco. LAST_VALUE es una trampa común: el marco por defecto es RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, así que 'último' significa la fila actual, no la última de la partición — extiende el marco a UNBOUNDED FOLLOWING para obtener el verdadero último valor. NTH_VALUE devuelve NULL hasta que aparece la fila n-ésima. Los marcos explícitos eliminan ambigüedad y vale la pena especificarlos siempre.
# 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 y PERCENT_RANK
NTILE(n) divide las filas ordenadas en n cubetas lo más uniformemente posible (las cubetas anteriores reciben la fila extra cuando no divide exacto) — práctico para cuartiles/deciles en reportes. PERCENT_RANK() es el rango relativo como fracción 0..1; CUME_DIST() es la distribución acumulativa (fracción de filas en o por debajo del valor actual). Usa NTILE para cubetizar y PERCENT_RANK/CUME_DIST para analítica tipo percentil.
# 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 <= currentManejo de fecha y hora
Funciones de fecha/hora actuales
NOW()/CURRENT_TIMESTAMP devuelven el mismo valor a lo largo de una sentencia (consistente); SYSDATE() refleja el tiempo real en cada llamada (evítalo — no es determinista y rompe la replicación). Guarda las marcas de tiempo en UTC (UTC_TIMESTAMP) y convierte a hora local al mostrar. NOW(6) da precisión de microsegundos. UNIX_TIMESTAMP convierte a/desde segundos de época — cómodo para interoperar con lenguajes que usan tiempo de época.
# 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 y STR_TO_DATE
DATE_FORMAT formatea una fecha/hora a cadena usando especificadores — ojo, %i minúscula (minutos) y %s (segundos), una trampa común. STR_TO_DATE es el inverso, parsea una cadena a DATE/TIME/DATETIME. Usa siempre STR_TO_DATE para parsear la entrada del usuario en lugar de confiar en la conversión implícita cadena-fecha, que depende de ajustes de formato y es frágil. Guarda las fechas como tipos DATE/DATETIME, no como cadenas.
# 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/PMDATE_ADD / DATE_SUB e INTERVAL
DATE_ADD/DATE_SUB con INTERVAL es la aritmética de fechas canónica; la abreviatura + INTERVAL / - INTERVAL es más limpia. INTERVAL maneja correctamente los cambios de mes/año (31 de enero + 1 mes = 29 de febrero en año bisiesto). LAST_DAY devuelve el último día del mes del argumento — práctico para lógica de fin de mes. Las unidades compuestas como YEAR_MONTH toman '1-2' (1 año 2 meses). Nunca uses aritmética de cadenas para fechas — se rompe en casos límite.
# 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 devuelve días enteros (fecha1 - fecha2, ignorando la hora). TIMESTAMPDIFF devuelve la diferencia en la unidad que elijas (unidad, desde, hasta) y trunca hacia cero — así que TIMESTAMPDIFF(YEAR, ...) da la edad solo si el cumpleaños ya pasó este año; para una edad exacta resta 1 cuando el mes/día aún no ha ocurrido. Vigila el orden de los argumentos: DATEDIFF es (a,b) = a-b; TIMESTAMPDIFF es (unidad, desde, hasta) = hasta-desde.
# 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:00EXTRACT y partes de fecha
EXTRACT(unidad FROM fecha) devuelve un único componente numérico; YEAR()/MONTH()/DAY() etc. son abreviaturas de los comunes; DAYNAME/MONTHNAME devuelven nombres. Agrupar por YEAR()/MONTH() de una marca de tiempo es la forma estándar de construir reportes mensuales. Ojo: DAYOFWEEK es 1=Domingo..7=Sábado mientras que WEEKDAY es 0=Lunes..6=Domingo — elige el que coincida con tu convención. WEEK() tiene modos para el día de inicio de semana y el conteo.
# 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;Conversión de zona horaria
Fija el time_zone de sesión para que las columnas TIMESTAMP se muestren en hora local; las zonas con nombre (Asia/Shanghai) exigen las tablas de zonas horarias del sistema cargadas vía mysql_tzinfo_to_sql. CONVERT_TZ convierte un datetime literal entre zonas. Buena práctica: guarda las marcas de tiempo en UTC (usa TIMESTAMP o DATETIME en UTC), fija el time_zone de cada sesión para mostrar y convierte con CONVERT_TZ cuando haga falta. Esto evita ambigüedad entre usuarios de distintas regiones.
# 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;Fragmentos de MySQL relacionados
Copy-paste ready code for common tasks.
SELECT with JOINs
Combine rows from multiple tables using JOINs.
Subqueries
Use scalar, IN, EXISTS, and derived-table subqueries.
Indexes
Create single, composite, unique, and fulltext indexes.
Transactions
Use BEGIN, COMMIT, ROLLBACK, and isolation levels.
Stored Procedures
Define reusable procedural routines with parameters.
Triggers
Run logic automatically on INSERT, UPDATE, or DELETE.
Views
Create virtual tables to simplify and secure queries.
Query Optimization
Diagnose and optimize slow queries with EXPLAIN and indexes.
Was this helpful?