Skip to content

MySQL Spickzettel

Beliebtes Open-Source-Relationales-Datenbankmanagementsystem.

01

Erste Schritte

Mit MySQL verbinden

Der mysql-Client ist die Standard-CLI für MySQL. Immer -p verwenden (kein Leerzeichen danach), damit das Passwort sicher abgefragt wird statt in der Shell-History sichtbar zu sein. -h setzt den Host, -P (Großbuchstabe) den Port. \s gibt den Verbindungsstatus aus. Mit -e einmalige Abfragen in Skripten ausführen.

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

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

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

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

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

Datenbankverwaltung

utf8mb4 (nicht utf8) verwenden, um volles Unicode inklusive Emoji zu unterstützen — MySQLs utf8 ist eine 3-Byte-Teilmenge, die nicht alle Zeichen speichern kann. utf8mb4_unicode_ci ist die empfohlene Kollation für korrekte Sortierung. IF EXISTS/IF NOT EXISTS verhindern Fehler in Skripten. DROP DATABASE entfernt alle Tabellen und Daten sofort.

mysql
# list all databases
SHOW DATABASES;

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

# switch to a database
USE mydb;

# show the current database
SELECT DATABASE();

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

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

Objekte anzeigen & beschreiben

SHOW-Befehle sind MySQL-spezifische Introspektionswerkzeuge. DESCRIBE gibt einen schnellen Spaltenüberblick; SHOW CREATE TABLE liefert das vollständige DDL, das du wiederverwenden kannst. \G statt ; anhängen für vertikale Ausgabe — viel lesbarer für breite Zeilen wie bei SHOW CREATE TABLE.

mysql
# list tables in the current database
SHOW TABLES;

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

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

# list indexes on a table
SHOW INDEX FROM users;

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

# list triggers
SHOW TRIGGERS\G

Serverstatus & Variablen

MySQL-Variablen haben SESSION-(aktuelle Verbindung)- und GLOBAL-Scope. SET GLOBAL ändert den laufenden Server, wird aber beim Neustart zurückgesetzt — Einstellungen stattdessen in my.cnf persistent halten. @@var liest Werte. SHOW STATUS legt Laufzeitzähler offen (Verbindungen, Uptime, Durchsatz), nützlich fürs Monitoring.

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

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

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

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

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

Kommentare & Anweisungsgrundlagen

MySQL unterstützt drei Kommentarstile: -- (verlangt ein folgendes Leerzeichen), /* */-Blöcke und # bis Zeilenende. /*! ... */ ist ein besonderer ausführbarer Kommentar — der Code darin läuft nur auf MySQL und wird von anderen SQL-Engines ignoriert, praktisch für portable Schema-Dateien. \G führt aus und zeigt Ergebnisse vertikal.

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

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

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

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

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

Konfigurationsdatei (my.cnf)

my.cnf (Linux) / my.ini (Windows) hält persistente Server- und Client-Einstellungen, gegliedert in Sektionen: [mysqld] für den Server, [client]/[mysql] für den Client. innodb_buffer_pool_size ist die wichtigste Stellschraube für InnoDB (typisch 50-75 % des RAM). Nach dem Bearbeiten den Server neu starten. Mit mysql --help oder SHOW VARIABLES verifizieren.

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

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

[client]
default-character-set = utf8mb4

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

DDL-Operationen

Tabelle erstellen

CREATE TABLE definiert Spalten, Typen, Constraints und Tabellenoptionen. InnoDB ist die Standard- und empfohlene Engine (unterstützt Transaktionen, Locking auf Zeilenebene, Foreign Keys). BIGINT UNSIGNED für AUTO_INCREMENT vermeidet Überlauf. DECIMAL(p,s) ist exakt für Geld. ENUM validiert gegen eine feste Liste. TIMESTAMP DEFAULT CURRENT_TIMESTAMP füllt beim Insert automatisch.

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

Tabelle ändern

ALTER TABLE entwickelt das Schema weiter, ohne die Daten neu zu erzeugen. ADD COLUMN mit AFTER platziert sie an einer bestimmten Position (Spaltenreihenfolge ist in MySQL kosmetisch). MODIFY ändert Typ/Default; RENAME COLUMN (8.0+) ist sauberer als CHANGE. Große ALTER-Operationen können die Tabelle neu bauen und sperren — für große Tabellen pt-online-schema-change oder Online-DDL verwenden. RENAME TABLE ist atomar.

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

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

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

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

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

Tabelle löschen & leeren

DROP TABLE entfernt die Tabelle vollständig; TRUNCATE leert sie, behält die Struktur und setzt AUTO_INCREMENT auf den Startwert zurück. TRUNCATE ist schneller als DELETE, weil es zeilenweises Löschen und Logging überspringt, ist aber DDL (nicht rückrollbar, keine Trigger). Beim Leeren fremdreferenzierter Tabellen foreign_key_checks deaktivieren.

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

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

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

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

AUTO_INCREMENT

AUTO_INCREMENT erzeugt fortlaufende Nummern für einen Primärschlüssel — pro Tabelle gibt es nur einen. LAST_INSERT_ID() liefert die vom letzten INSERT der aktuellen Session erzeugte ID (verbindungsbezogen, nebenläufigkeitssicher). Lücken können nach Deletes, Rollbacks oder Inserts mit expliziten Werten entstehen. Zum Zurücksetzen ALTER TABLE ... AUTO_INCREMENT = n verwenden.

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

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

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

# get the last inserted id
SELECT LAST_INSERT_ID();

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

Tabellen-Constraints

Constraints erzwingen Datenintegrität auf Datenbankebene. InnoDB unterstützt Primärschlüssel, UNIQUE, NOT NULL, CHECK (erzwungen seit 8.0.16) und Foreign Keys. FOREIGN KEY ... ON DELETE CASCADE entfernt Kindzeilen beim Löschen des Parent; SET NULL setzt sie auf NULL; RESTRICT/NO ACTION blockieren das Löschen. Constraints explizit benennen für einfachere Verwaltung. Foreign Keys brauchen Indizes auf beiden Spalten.

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

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

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

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

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

Temporäre Tabellen

TEMPORARY-Tabellen existieren nur für die aktuelle Session und werden beim Schließen automatisch gelöscht. Sie können eine echte Tabelle gleichen Namens überdecken — nützlich fürs sichere Refactoring oder Staging-Daten. Zwei Sessions können temporäre Tabellen gleichen Namens ohne Konflikt anlegen. Sie sind für andere Verbindungen unsichtbar und landen standardmäßig nicht im Binlog.

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

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

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

# explicit cleanup
DROP TEMPORARY TABLE IF EXISTS tmp_active;
03

Datentypen

Numerische Typen

DECIMAL für Geldbeträge verwenden, wo Exaktheit zählt — FLOAT/DOUBLE sind Näherungen und summieren Rundungsfehler. UNSIGNED verdoppelt den positiven Bereich, verbietet aber Negative. Die Anzeigebreite (z. B. INT(11)) ist in MySQL 8.0 veraltet und wird ignoriert — sie hat den gespeicherten Bereich nie begrenzt. BIGINT UNSIGNED für AUTO_INCREMENT empfohlen, um Überlauf zu vermeiden. SERIAL ist eine praktische Kurzform für einen Surrogatschlüssel.

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

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

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

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

String- & Text-Typen

VARCHAR ist fast immer CHAR (das mit Leerzeichen auffüllt) vorzuziehen, außer bei kurzen Zeichenketten fester Länge. Bei MySQLs utf8mb4 braucht jedes Zeichen bis zu 4 Bytes — eine VARCHAR(255)-Spalte braucht bis zu 1020 Bytes plus Länge, was für Index-Schlüssellimits zählt (3072 Bytes in InnoDB). TEXT/BLOB-Familien lagern große Daten off-page; sie können keinen Default-Wert haben und ihre Indexierung verlangt eine Präfixlänge.

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

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

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

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

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

Datum- & Zeit-Typen

DATETIME speichert ein wörtliches Datum/eine wörtliche Zeit ohne Zeitzonenbezug; TIMESTAMP speichert UTC und konvertiert zur time_zone der Session bei der Anzeige — besser für App-übergreifende Zeitzonen, hat aber auf 32-Bit-Builds ein 2038-Limit. DATETIME(6)/TIMESTAMP(6) für Mikrosekunden-Präzision. CURRENT_TIMESTAMP funktioniert seit MySQL 5.6.5 als Default für beide. Session-time_zone pro Verbindung mit SET time_zone = '+08:00'; setzen.

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

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

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

ENUM- & SET-Typen

ENUM speichert einen Wert aus einer festen Liste von Strings kompakt (1-2 Bytes) und weist unbekannte Werte im Strict-Mode zurück. SET speichert jede Kombination von bis zu 64 Membern als Bitmaske. Beide machen Schemaänderungen teuer (Hinzufügen/Umordnen von Membern braucht ALTER TABLE) und sind umständlich abzufragen — viele Teams bevorzugen eine Lookup-Tabelle oder VARCHAR mit CHECK. FIND_IN_SET hilft bei SET-Spalten.

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

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

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

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

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

JSON-Typ

Der JSON-Typ (MySQL 5.7.8+) speichert JSON in einem Binärformat, das beim Schreiben validiert wird und schnellen Member-Zugriff unterstützt. Er ist besser als JSON in einer TEXT-Spalte, weil du darin indexieren und abfragen kannst. JSON_OBJECT(), JSON_ARRAY() und JSON_MERGE_PATCH() zum Aufbau von Werten verwenden. Bei häufigen Abfragen weniger Schlüssel diese in generierte Spalten extrahieren und indexieren — beste Performance.

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

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

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

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

BLOB- & Binärtypen

BLOB speichert beliebige Binärdaten (Bilder, Dateien), ist aber umständlich abzufragen und kann keinen Default haben. Für große Dateien lieber einen Pfad in der DB speichern und die Datei auf Platte/Object-Storage. UUIDs als BINARY(16) (16 Bytes) statt CHAR(36) (36 Bytes) für Index-Effizienz — MySQL 8.0 ergänzt UUID_TO_BIN()/BIN_TO_UUID() mit optionalem Swap für Index-Freundlichkeit.

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

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

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

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

DML (INSERT / UPDATE / DELETE)

INSERT

Mehrzeilen-INSERT ist viel schneller als wiederholte Einzelzeilen-Inserts, weil es Parsing, Netzwerk-Round-Trips und Index-Updates amortisiert. INSERT ... SELECT kopiert Daten zwischen Tabellen. Die SET-Syntax ist MySQL-spezifisch und praktisch für eine Zeile. Zielspalten immer explizit auflisten, damit die Anweisung Schemaänderungen übersteht. INSERT IGNORE oder ON DUPLICATE KEY UPDATE für idempotente Inserts.

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

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

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

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

INSERT ... ON DUPLICATE KEY UPDATE (Upsert)

ON DUPLICATE KEY UPDATE implementiert Upsert — bei einem UNIQUE/PRIMARY-KEY-Konflikt aktualisiert MySQL die bestehende Zeile statt einen Fehler zu werfen. VALUES(col) verweist auf den Wert, der eingefügt worden wäre; MySQL 8.0.19+ veraltet das zugunsten von Zeilen-Aliassen (AS new). Effizienter als ein separates SELECT-then-INSERT/UPDATE, weil es atomar ist. INSERT IGNORE überspringt Konflikte stattdessen stillschweigend.

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

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

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

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

UPDATE

Immer eine WHERE-Klausel einschließen — ein UPDATE ohne ändert jede Zeile. MySQL unterstützt Multi-Table-UPDATE mit JOIN, nützlich zum Denormalisieren berechneter Werte. ORDER BY + LIMIT ermöglicht sichere gebündelte Updates ohne lange Locks auf riesigen Tabellen. Im Safe-Updates-Mode verweigert der Client UPDATE/DELETE ohne Schlüssel in der WHERE-Klausel — ein Schutz vor versehentlichen Massenänderungen.

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

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

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

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

DELETE

DELETE entfernt Zeilen einzeln, ist transaktional (rückrollbar) und feuert Trigger. Multi-Table-DELETE kann aus mehreren Tabellen in einer Anweisung löschen. Zum Leeren einer ganzen Tabelle ist TRUNCATE viel schneller und setzt AUTO_INCREMENT zurück, ist aber nicht transaktional. Der QUICK-Modifikator (MyISAM) überspringt das Aktualisieren von Index-Blättern — mit InnoDB selten nötig. Immer eine WHERE-Klausel verwenden.

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

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

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

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

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

REPLACE

REPLACE ist MySQL-spezifisch: Existiert eine Zeile mit demselben PK/UNIQUE-Schlüssel, wird sie gelöscht und eine neue eingefügt; sonst wird einfach eingefügt. Einfacher als Upsert, aber mit Nebenwirkungen — es feuert DELETE- dann INSERT-Trigger (nicht UPDATE), setzt AUTO_INCREMENT zurück und stößt nicht gelieferte Spalten auf ihre Defaults. Für die meisten Fälle INSERT ... ON DUPLICATE KEY UPDATE bevorzugen.

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

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

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

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

LOAD DATA INFILE

LOAD DATA INFILE ist der schnellste Weg, CSV im Bulk zu laden — 20x schneller als INSERT-Anweisungen, weil es SQL-Parsing umgeht. Der Server liest Dateien aus einem sicheren Verzeichnis (secure_file_priv); LOCAL lässt den Client die Datei senden, ist aber aus Sicherheitsgründen standardmäßig deaktiviert. Mit @var Spalten auffangen und dann SET nutzen, um Werte beim Laden zu transformieren. SELECT ... INTO OUTFILE ist der symmetrische Export.

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

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

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

SELECT-Abfragen

Einfaches SELECT

SELECT * in Produktion vermeiden — es überträgt unnötige Daten, bricht bei hinzugefügten/entfernten Spalten und hindert den Optimierer an Covering-Indizes. Die benötigten Spalten benennen. LIMIT mit OFFSET implementiert Paginierung, ist aber bei großen Offsets langsam (scannt die übersprungenen Zeilen trotzdem); Keyset-Paginierung (WHERE id > last_id LIMIT n) skaliert viel besser.

mysql
# select all columns
SELECT * FROM users;

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

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

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

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

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

WHERE & Operatoren

Vergleiche mit NULL ergeben NULL (als falsch behandelt), daher immer IS NULL / IS NOT NULL. NULL in einer IN/NOT IN-Liste kann überraschend leere Ergebnisse liefern — stattdessen NOT EXISTS oder eine korrelierte Subquery verwenden. BETWEEN ist inklusive. MySQL wertet AND vor OR aus, gemischte Bedingungen also klammern. Für beste Performance die selektivste Bedingung zuerst und indizierte Spalten links vom Operator.

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

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

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

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

# NOT IN with NULL traps: NULL poisons the result

ORDER BY & LIMIT

ORDER BY sortiert Ergebnisse; mehrere Schlüssel werden von links nach rechts angewendet. FIELD() ermöglicht benutzerdefinierte Reihenfolge nach einer aufzählenden Liste. Bei großen Ergebnismengen kann ORDER BY ... LIMIT trotzdem alles scannen und sortieren, sofern kein Index stützt — einen Index auf die ORDER-BY-Spalten legen. Das Window-Function-Muster (ROW_NUMBER OVER PARTITION BY) ist die kanonische Top-N-pro-Gruppe-Lösung in MySQL 8.0+.

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

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

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

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

DISTINCT & Wertgruppierung

DISTINCT faltet identische Zeilen zusammen; bei mehreren Spalten dedupliziert es die Kombination. COUNT(DISTINCT col) zählt eindeutige Nicht-NULL-Werte. DISTINCT ist im Kern ein GROUP BY über alle gewählten Spalten. Um zu deduplizieren und eine repräsentative Zeile zu behalten (z. B. die neueste), ROW_NUMBER() OVER (PARTITION BY ...) verwenden. DISTINCT kann teuer sein — es baut eine temporäre Tabelle, also lieber einen Index.

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

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

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

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

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

LIKE & REGEXP-Mustererkennung

LIKE ist einfach, aber führende Wildcards (LIKE '%x') torpedieren Indizes und erzwingen einen vollständigen Scan. Die Kollation entscheidet über Groß-/Kleinschreibung — utf8mb4_0900_ai_ci ignoriert Akzente/Schreibweise. REGEXP/RLIKE ergänzen volle Regex-Kraft, nutzen aber nie einen Index. MySQL 8.0 ergänzt REGEXP_LIKE, REGEXP_REPLACE, REGEXP_INSTR und REGEXP_SUBSTR für reichere Musterbehandlung. Für Volltextsuche einen FULLTEXT-Index mit MATCH ... AGAINST verwenden.

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

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

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

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

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

CASE-Ausdruck

CASE ist der SQL-Conditional-Ausdruck. Die gesuchte Form wertet WHEN-Bedingungen von oben nach unten aus und gibt den THEN-Wert des ersten Treffers zurück, sonst ELSE (oder NULL). CASE lässt sich in SELECT, WHERE, ORDER BY und Aggregaten verwenden — das SUM(CASE ...)-Muster ist ein klassischer Pivot. CASE unterbricht die Aggregation nie, jede Zeile zählt mit. IF() und IFNULL() sind kürzere MySQL-Kurzbefehle für einfache Fälle.

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

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

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

JOINs

INNER JOIN

INNER JOIN liefert Zeilen nur, wenn es in beiden Tabellen einen Treffer gibt — ungetroffene Zeilen beider Seiten fallen weg. JOIN ist die Kurzform von INNER JOIN. USING (col) ist eine kompakte Alternative zu ON a.col = b.col, wenn der Spaltenname identisch ist und einmal im Ergebnis erscheint. INNER JOIN ist der Standard und häufigste Join-Typ für normalisierte Tabellen.

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

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

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

LEFT & RIGHT JOIN

LEFT JOIN behält jede Zeile der linken Tabelle und füllt die rechte Seite mit NULL, wenn kein Treffer vorliegt. Der WHERE o.id IS NULL-Trick (Anti-Join) findet linke Zeilen ohne passende rechte — oft klarer und schneller als NOT IN, besonders mit NULLs. RIGHT JOIN ist symmetrisch; die meisten ordnen die Tabellen um und nutzen LEFT JOIN aus Lesbarkeitsgründen. NULLs im Join-Schlüssel matchen nie.

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

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

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

CROSS JOIN

CROSS JOIN liefert das kartesische Produkt — jede Kombination der Zeilen beider Tabellen. Nützlich zum Erzeugen von Kombinationen oder zum Befüllen von Dimensionsmatrizen, erzeugt aber N x M Zeilen und ist bei großen Tabellen teuer. Ein Komma zwischen Tabellen (FROM a, b) ist ein impliziter CROSS JOIN. Das rekursive CTE-Muster ist der Standardweg, um eine Sequenz-/Dimensionstabelle in MySQL 8.0+ zu erzeugen.

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

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

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

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

Self Join

Ein Self Join referenziert dieselbe Tabelle zweimal mit verschiedenen Aliassen, um Zeilen in ihr zu relationieren — klassisch für Vorgesetzte/Mitarbeiter oder Adjazenzlisten-Hierarchien. Die Bedingung a.id < b.id vermeidet doppelte symmetrische Paare. Bei tiefen Hierarchien einen rekursiven CTE verwenden (MySQL 8.0+), der den Baum ohne feste Tiefengrenze durchläuft und mit REPEAT/CONCAT eingerückte Baumausgabe bauen kann.

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

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

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

FULL OUTER JOIN-Workaround

MySQL implementiert keinen FULL OUTER JOIN. Die Standard-Emulation ist ein LEFT JOIN UNION ein RIGHT JOIN — UNION dedupliziert, sodass jede passende Zeile einmal erscheint und ungetroffene Zeilen beider Seiten mit NULLs auf der anderen. Bei großen Ergebnissen lieber UNION ALL mit einer Deduplizierungsstrategie, da UNIONs implizites DISTINCT teuer ist. Brauchst du nur ungetroffene Zeilen, zwei Anti-Joins verwenden.

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

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

NATURAL JOIN & USING

NATURAL JOIN ist fragil — er joint über jeden geteilten Spaltennamen, sodass eine neue Spalte Ergebnisse stillschweigend ändern kann; in Produktion vermeiden. USING (col) ist sicherer und fasst die Join-Spalte zu einer Kopie in der Ausgabe zusammen. STRAIGHT_JOIN erzwingt die Tabellenreihenfolge des Optimierers und ist ein manuelles Übersteuern bei schlechten Plänen; entfernen, sobald das eigentliche Problem behoben ist. Explizites ON bevorzugen für Klarheit.

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

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

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

Aggregation & Gruppierung

Aggregatfunktionen

Aggregatfunktionen fassen viele Zeilen zu einer zusammen: COUNT, SUM, AVG, MIN, MAX. COUNT(*) zählt Zeilen; COUNT(col) zählt Nicht-NULL-Werte von col; COUNT(DISTINCT col) zählt eindeutige Werte. AVG ignoriert NULLs (nicht Null), was Ergebnisse verzerren kann — COALESCE(col,0) verwenden, wenn NULL als Null gelten soll. Aggregate ohne GROUP BY liefern eine einzelne Zeile, selbst auf leerer Tabelle.

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

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

GROUP BY

GROUP BY faltet Zeilen mit gleichen gruppierten Spaltenwerten zu einer Ausgabezeile zusammen, mit pro Gruppe berechneten Aggregaten. MySQL 8.0 aktiviert ONLY_FULL_GROUP_BY standardmäßig, was Abfragen verwirft, die Spalten wählen, die nicht in GROUP BY stehen (frühere Versionen lieferten eine beliebige Zeile — ein häufiger Bug). Nach Ausdrücken oder Aliassen gruppieren. Für Reporting-Zeitreihen nach YEAR()/MONTH()/DATE() eines Zeitstempels gruppieren.

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

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

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

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

HAVING

HAVING filtert das Ergebnis der Aggregation, während WHERE Eingabezeilen vor der Gruppierung filtert — HAVING kann also Aggregate (SUM, COUNT) referenzieren, WHERE nicht. Zeilenfilter in WHERE platzieren, aus Effizienzgründen (sie verkleinern die Zeilen vor der Gruppierung). HAVING akzeptiert Aliasse in MySQL (HAVING users > 100). Eine Abfrage kann beides nutzen: WHERE engt Zeilen ein, GROUP BY bucktet sie, HAVING engt Buckets ein.

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

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

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

GROUP BY ROLLUP

WITH ROLLUP ergänzt Super-Aggregatzeilen pro Gruppierungsebene plus eine Gesamtsumme, wobei die gruppierte Spalte NULL wird. GROUPING(col) liefert 1 auf Subtotal-/Total-Zeilen, sodass du NULLs als 'ALL' labeln kannst. ROLLUP arbeitet von links nach rechts über die GROUP-BY-Spalten. WITH CUBE gibt es in MySQL nicht — bei Bedarf jeder Dimensionskombination mit UNION ALL mehrerer ROLLUP-Abfragen emulieren.

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

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

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

GROUP_CONCAT

GROUP_CONCAT ist MySQLs String-Aggregatfunktion — sie verkettet Werte einer Gruppe zu einem String. Mit DISTINCT deduplizieren, mit ORDER BY sortieren und mit SEPARATOR das Trennzeichen ändern (Standard ','). Das Ergebnis ist auf group_concat_max_len begrenzt (Standard 1024 Bytes); für längere Listen erhöhen. Andere Datenbanken nennen das LISTAGG/STRING_AGG — GROUP_CONCAT ist das MySQL-Äquivalent.

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. Window-Aggregate

ROLLUP verkleinert das Ergebnis um Subtotal-Zeilen, während ein Window-Aggregat (OVER) die Zeilenzahl hält und das Aggregat als zusätzliche Spalte anhängt — man sieht Detail und Summen gemeinsam. ROLLUP für Übersichtsberichte, Window-Funktionen, wenn du zeilenbezogenen Kontext neben dem Aggregat brauchst. Laufende Summen sind ein klassischer SUM OVER (ORDER BY ...)-Anwendungsfall.

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

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

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

Subqueries & CTEs

Skalare Subquery

Eine skalare Subquery liefert einen einzelnen Wert (eine Zeile, eine Spalte) und ist überall gültig, wo ein Ausdruck gültig ist — SELECT-Liste, WHERE, HAVING. Liefert sie keine Zeilen, ist der Wert NULL. Der Optimierer kann eine korrelierte skalare Subquery oft in einen Join umwandeln (subquery materialization). Für Klarheit und teils Performance ist ein CTE oder JOIN besser, wenn dieselbe Subquery wiederholt referenziert wird.

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

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

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

IN / ANY / ALL-Subqueries

IN matcht jeden von der Subquery gelieferten Wert; NOT IN ist das Komplement, wird aber durch NULLs vergiftet — liefert die Subquery ein NULL, gibt NOT IN überhaupt keine Zeilen zurück, also NULLs immer ausschließen oder NOT EXISTS verwenden. ALL ist wahr, wenn der Vergleich für jeden gelieferten Wert gilt; ANY/SOME, wenn er für mindestens einen gilt. Der Optimierer schreibt diese oft in Semi-Joins um.

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

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

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

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

EXISTS & NOT EXISTS

EXISTS prüft auf das Vorhandensein von Zeilen ohne Daten zurückzugeben — er bricht beim ersten Treffer ab und ist daher effizient für Vorhandenheitschecks. Die Subquery ist korreliert (referenziert die äußere Abfrage). NOT EXISTS ist der NULL-sichere Weg, Zeilen ohne Treffer zu finden, und wird gegenüber NOT IN bevorzugt. SELECT 1 innerhalb von EXISTS ist Konvention; die Spaltenliste ist egal. Der Optimierer macht aus EXISTS oft einen Semi-Join.

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

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

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

Derived Tables

Eine derived table ist eine Subquery in der FROM-Klausel — sie materialisiert ein Zwischenergebnis, aus dem du wählen oder mit dem du joinen kannst. Immer mit Alias versehen. MySQL 8.0 kann viele derived tables in die äußere Abfrage verschmelzen (derived_merge) für bessere Pläne, aber komplexe Aggregate oder LIMIT erzwingen meist Materialisierung. Für Lesbarkeit und Wiederverwendung in einer Abfrage lieber einen benannten CTE (WITH) statt einer inline derived table.

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

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

# derived tables must be aliased

Korrelierte Subquery

Eine korrelierte Subquery referenziert eine Spalte der äußeren Abfrage und wird logisch für jede äußere Zeile neu ausgewertet — bei großen Mengen kann das langsam sein. Der Optimierer kann sie in einen Join umwandeln oder einen Cache nutzen (subquery cache), um das abzumildern. Für Rang-/Top-N-Probleme (z. B. n-höchstes Gehalt) ist eine Window-Funktion wie DENSE_RANK() OVER (ORDER BY salary DESC) klarer und schneller — bei MySQL 8.0+ greifen.

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

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

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

Common Table Expressions (CTE)

Ein CTE (WITH-Klausel) benennt eine Subquery für Lesbarkeit und Wiederverwendung innerhalb einer Anweisung; MySQL 8.0+ unterstützt das. RECURSIVE ermöglicht selbstreferenzierende CTEs — essenziell für Baumdurchlauf und Sequenzgenerierung (UNION ALL zum Rekurse, UNION zum Deduplizieren). Ein rekursiver CTE braucht einen Basisfall, einen rekursiven Arm und einen Abbruchschutz. Mehrere CTEs werden per Komma getrennt und können frühere in demselben WITH referenzieren.

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

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

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

Indizes

Index erstellen

Ein Index ist eine Datenstruktur (B+Tree in InnoDB), die Lookups und Sortierungen beschleunigt, auf Kosten von Schreib-Overhead und Speicher. Indizes auf Spalten anlegen, die in WHERE, JOIN und ORDER BY vorkommen. UNIQUE-Indizes erzwingen zusätzlich eine Eindeutigkeitsbedingung. Präfix-Indizes auf langen Textspalten sparen Platz, taugen aber nicht für Covering-Scans oder ORDER BY. Jeder Primärschlüssel ist in InnoDB geclustert — Sekundärindizes speichern den PK-Wert als Zeilenzeiger.

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

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

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

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

Composite-Index

Composite-Indizes sind zuerst nach ihrer linksten Spalte geordnet, sie helfen also nur Abfragen, die auf einem linksten Präfix der Spaltenliste filtern — die Spaltenreihenfolge ist kritisch. Die selektivste Gleichheitsspalte zuerst, dann Bereiche. Ein Covering-Index enthält jede Spalte, die die Abfrage braucht, und erlaubt einen Index-only-Scan, der den Tabellenlookup ganz überspringt (EXPLAIN zeigt 'Using index'). Nicht überindizieren — jeder Index verlangsamt Schreibvorgänge.

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

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

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

Volltext-Index

FULLTEXT-Indizes ermöglichen natürlichsprachliche und boolesche Textsuche, weit besser als LIKE '%word%'. MATCH ... AGAINST liefert eine Relevanzpunktzahl. Der boolesche Modus unterstützt Operatoren: +erfordern, -ausschließen, *wildcard, >erhöhen, ~vermindern. Benötigt InnoDB (5.6+) oder MyISAM und eine Mindestwortlänge (ft_min_word_len / innodb_ft_min_token_size, Standard 3). Für ernsthafte Suche im großen Stil eine dedizierte Engine wie Elasticsearch erwägen.

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

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

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

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

Spatial-Index

Spatial-Indizes (R-Tree) beschleunigen geografische Abfragen auf GEOMETRY/POINT/POLYGON-Spalten. MySQL 8.0 standardisierte auf SRID 4326 (WGS-84-GPS-Koordinaten) und ergänzte ST_Distance_Sphere für reale Meter-Distanzen. Die Spalte muss NOT NULL sein. ST_Within/ST_Contains/ST_Distance zum Filtern verwenden. Koordinaten immer als (longitude, latitude) ausdrücken, passend zur X/Y-Konvention. Spatial-Indizes machen Radiusabfragen dramatisch schneller.

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

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

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

Indexverwaltung

SHOW INDEX listet die Indizes einer Tabelle mit Spaltenreihenfolge und Kardinalität (Schätzung der Distinktwerte). DROP INDEX entfernt einen Index; Umbenennen (8.0+) vermeidet drop+rebuild. OPTIMIZE TABLE baut die Tabelle neu, um fragmentierten Platz zurückzuholen und Indexstatistiken zu erfrischen — stark sperrend, also bei niedrigem Traffic einplanen. Veraltete Statistiken können den Optimierer irreführen; nach großen Datenlasten ANALYZE TABLE ausführen.

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

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

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

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

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

Invisible-Indizes & Hints

Invisible-Indizes (8.0+) erlauben es, das Entfernen eines Index sicher zu testen — der Optimierer ignoriert ihn, aber Schreibvorgänge halten ihn aktuell, sodass du ihn bei Performanceeinbrüchen sofort zurückflippst; sobald überzeugt, löschen. Optimizer-Hints (/*+ ... */) sind der alten USE/FORCE/IGNORE-INDEX-Syntax vorzuziehen und betreffen nur die getaggte Anweisung. Hints sparsam einsetzen — sie sind ein Pflaster bei schlechter Datenverteilung oder fehlenden Statistiken.

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

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

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

Views

View erstellen

Ein View ist eine gespeicherte Abfrage, die sich wie eine virtuelle Tabelle verhält — er vereinfacht komplexe Abfragen, abstrahiert Schemaänderungen und erzwingt Zeilen-/Spaltensichtbarkeit. CREATE OR REPLACE aktualisiert die Definition in einem Schritt. Views speichern keine Daten (sie führen die zugrunde liegende Abfrage jedes Mal erneut aus, außer materialisiert), kosten also nichts beim Schreiben, beschleunigen aber auch nicht. Views werden nach Möglichkeit in die Abfrage verschmolzen.

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

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

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

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

Aktualisierbare Views

Ein View über einer einzigen Basistabelle ohne Aggregate/Distinct ist aktualisierbar — INSERT/UPDATE/DELETE pflanzen sich in die zugrunde liegende Tabelle fort. Standardmäßig gelingt ein UPDATE, das eine Zeile aus dem WHERE-Bereich des Views hinausbewegt (die Zeile verschwindet nur aus dem View); WITH CHECK OPTION blockiert solche Änderungen, damit Zeilen den View-Bereich nie verlassen. Views mit JOINs, GROUP BY, DISTINCT oder Subqueries sind meist nicht aktualisierbar.

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

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

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

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

View CHECK OPTION

CHECK OPTION erzwingt, dass über einen View geänderte Zeilen in diesem sichtbar bleiben. LOCAL prüft nur das eigene WHERE des Views; CASCADED (der Standard) erzwingt auch die WHERE-Klauseln aller referenzierten zugrunde liegenden Views. Bei gestapelten Views CASCADED verwenden, um die ganze Kette konsistent zu halten; LOCAL nur, wenn du den Filter eines zugrunde liegenden Views bewusst lockern willst.

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

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

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

Views verwalten

ALTER VIEW definiert einen bestehenden View um, ohne ihn zu löschen (Berechtigungen bleiben). DROP VIEW IF EXISTS vermeidet einen Fehler, wenn der View fehlt. RENAME TABLE funktioniert auch für Views, ist aber unüblich. information_schema.views legt Definition, Aktualisierbarkeit und Sicherheitstyp jedes Views offen — nützlich fürs Auditing. Ein View hängt von seinen Basistabellen ab; wird eine Basistabelle gelöscht, bleibt der View ungültig, bis er neu erzeugt wird.

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

# drop a view
DROP VIEW IF EXISTS active_users;

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

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

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

View-Sicherheit & Determinismus

SQL SECURITY DEFINER führt den View mit den Rechten des Definers aus — nützlich, um gefilterte Daten Nutzern ohne Basistabellen-Zugang bereitzustellen (z. B. ein View mit Zeilensicherheit). INVOKER prüft stattdessen die Rechte des Aufrufers. ALGORITHM MERGE faltet den View in die Abfrage (schneller, erlaubt aktualisierbare Views); TEMPTABLE materialisiert ihn zuerst (für manche Konstrukte nötig, verliert aber Aktualisierbarkeit und kann langsamer sein). UNDEFINED überlässt die Wahl MySQL.

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

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

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

Stored Procedures

Prozedur erstellen & aufrufen

Eine Stored Procedure ist ein benannter, vorkompilierter SQL-Block. Weil Prozeduren Semikolons enthalten können, ändert man den DELIMITER, damit das gesamte CREATE als eine Anweisung läuft, und setzt ihn dann zurück. CALL führt eine Prozedur aus. Prozeduren geben keinen Wert direkt zurück, können aber Ergebnismengen liefern und OUT-Parameter verändern. Sie laufen serverseitig und sparen Netzwerk-Round-Trips bei mehrstufiger Logik, sind aber schwerer zu versionieren und testen als Anwendungscode.

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

# call it
CALL get_user_by_id(42);

# drop it
DROP PROCEDURE IF EXISTS get_user_by_id;

# show the definition
SHOW CREATE PROCEDURE get_user_by_id\G

IN / OUT / INOUT-Parameter

Parameter sind IN (schreibgeschützte Eingabe), OUT (an den Aufrufer über eine Session-Variable zurückgeschrieben) oder INOUT (beides). SELECT ... INTO weist einer Variablen ein skalares Abfrageergebnis zu. Das Beispiel wickelt eine Belastung/Gutschrift in eine Transaktion mit SELECT FOR UPDATE, um Zeilen zu sperren und verlorene Updates zu verhindern. OUT-Parameter für Statuscodes nutzen; Ergebnismengen werden direkt zurückgegeben, wenn du innerhalb der Prozedur SELECT machst.

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

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

Variablen & Kontrollfluss

Stored Programs unterstützen lokale Variablen (DECLARE, begrenzt auf BEGIN...END), Nutzervariablen (@var, session-bezogen) und Systemvariablen. Der Kontrollfluss umfasst IF/ELSEIF/ELSE, CASE und drei Schleifen: LOOP (mit LEAVE/ITERATE), WHILE (Bedingung zuerst) und REPEAT (Bedingung zuletzt). Schleifen beim Verschachteln immer benennen, damit LEAVE die richtige trifft. SET weist einen Wert zu; SELECT ... INTO kopiert ein Abfrage-Skalar in eine Variable.

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

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

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

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

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

Cursors

Ein Cursor lässt eine Stored Procedure zeilenweise über eine Ergebnismenge iterieren. Zuerst den Cursor deklarieren, dann einen CONTINUE HANDLER FOR NOT FOUND zur Erschöpfungs-Erkennung (ein Flag setzen und die Schleife LEAVEn). FETCH holt eine Zeile in Variablen. Cursors sind in MySQL read-only und forward-only. Zeilenweise Verarbeitung ist langsamer als mengenbasiertes SQL — wenn möglich eine einzige UPDATE/INSERT ... SELECT-Anweisung bevorzugen; Cursors nur bei wirklich sequenzieller Logik.

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

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

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

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

CALL log_all_users();

Error-Handler

Handler fangen Fehler in Stored Programs: CONTINUE führt den Handler-Körper aus und setzt nach der fehlgeschlagenen Anweisung fort; EXIT führt den Körper aus und verlässt den BEGIN...END-Block; UNDO rollt zurück (veraltet). SQLEXCEPTION für jeden Fehler fangen, SQLWARNING für Warnungen oder spezifische SQLSTATE/errno (z. B. 1062 duplicate key). RESIGNAL hebt den aktuellen Fehler nach dem Aufräumen erneut aus. Benannte Bedingungen (CONDITION FOR) machen Handler selbsterklärend.

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

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

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

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

Prozedur-Metadaten

SHOW PROCEDURE STATUS listet Prozeduren; SHOW CREATE PROCEDURE gibt den vollständigen Körper zurück. information_schema.routines liefert reichere Metadaten inkl. Determinismus und Sicherheitstyp. Eine Funktion DETERMINISTIC (READS SQL DATA / NO SQL) markieren, wenn ihre Ausgabe nur von ihren Eingaben abhängt — erforderlich fürs Binär-Logging und die Replikation von Funktionen. Prozeduren brauchen das Deterministic-Flag nicht. Diese Views fürs Auditing und Inventar von Routines nutzen.

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

# show definition
SHOW CREATE PROCEDURE get_user_by_id\G

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

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

Funktionen

String-Funktionen

LENGTH zählt Bytes, CHAR_LENGTH zählt Zeichen (wichtig für Multibyte-utf8mb4). SUBSTRING ist 1-indiziert; negativer Start zählt vom Ende. CONCAT liefert NULL, wenn ein Argument NULL ist — CONCAT_WS überspringt NULLs. SUBSTRING_INDEX teilt an einem Trennzeichen und behält die ersten N Teile (negatives N behält von rechts). TRIM hat BOTH/LEADING/TRAILING-Varianten und kann jedes Zeichen trimmen, nicht nur Leerzeichen.

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

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

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

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

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

Numerische Funktionen

ROUND rundet von Null weg; TRUNCATE schneidet Stellen ohne zu runden. MOD und % sind austauschbar. RAND() liefert einen Float in [0,1); ORDER BY RAND() ist eine bequeme, aber teure Art, Zeilen zu samplen, weil sie die ganze Ergebnismenge sortiert — bei großen Tabellen lieber einen zufälligen PK-Bereich oder Reservoir-Sampling. FORMAT ergänzt Tausendertrennzeichen, rundet auf die angegebenen Dezimalstellen und liefert einen String.

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

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

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

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

Datum- & Zeitfunktionen

NOW() liefert das aktuelle Datum/die aktuelle Zeit in einem Aufruf (konsistent innerhalb einer Anweisung); CURDATE/CURTIME liefern Datums-/Zeitteile. DATE_ADD/SUB mit INTERVAL ist die kanonische Datum-Arithmetik — sie behandelt Monats-/Jahresübergänge korrekt. DATEDIFF liefert Tage (date1 - date2); TIMEDIFF eine TIME. DATE_FORMAT und STR_TO_DATE nutzen die Spezifizierer %Y %m %d %H %i %s (beachte kleines i für Minuten, s für Sekunden).

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

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

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

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

Eigene Funktionen

Eine Stored Function liefert einen einzelnen skalaren Wert und darf in Ausdrücken verwendet werden (anders als Prozeduren, die man CALLt). Sie muss Charakteristika deklarieren: DETERMINISTIC (gleiche Eingabe ergibt immer gleiche Ausgabe, für statement-basierte Replikation erforderlich), und eines von READS SQL DATA / NO SQL / MODIFIES SQL DATA. Funktionen dürfen keine Ergebnismengen liefern oder Transaktionskontrolle nutzen. Die Kombination NO SQL + DETERMINISTIC ist am sichersten für reine Berechnung.

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

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

# drop it
DROP FUNCTION IF EXISTS full_name;

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

Kontrollfluss-Funktionen

IF() ist ein kompaktes Ternär; IFNULL(a,b) liefert a, außer a ist NULL, dann b (nur zwei Argumente); COALESCE verallgemeinert auf N Argumente. NULLIF(a,b) liefert NULL, wenn a=b — praktisch, um Division durch Null zu vermeiden (x / NULLIF(y,0)). ISNULL() liefert 1/0, nicht den Wert — nicht mit IFNULL verwechseln. CASE ist der flexibelste und portabelste Conditional-Ausdruck und funktioniert in jedem SQL-Dialekt.

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

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

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

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

Cast & Konvertierung

CAST und CONVERT ändern den Typ eines Werts — essenziell, wenn MySQL sonst implizit konvertieren würde und Indizes aushebelte (z. B. Vergleich eines Strings mit einer Zahlspalte). CAST(... AS DATE/DATETIME/SIGNED/UNSIGNED/CHAR/DECIMAL) deckt Übliches ab. Achtung: CAST('123abc' AS SIGNED) liefert 123 mit Warnung statt einem Fehler. STR_TO_DATE für das Parsen eigener Datumsformate verwenden, da CAST ein striktes ISO-Format erwartet.

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

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

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

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

Trigger

BEFORE / AFTER-Trigger

Ein Trigger feuert automatisch bei INSERT/UPDATE/DELETE, entweder BEFORE (kann NEW-Werte ändern, validieren und abbrechen) oder AFTER (für Nebenwirkungen wie Logging). FOR EACH ROW heißt, der Körper läuft pro betroffener Zeile — Zeilen-Trigger sind die einzige Art, die MySQL unterstützt. BEFORE-Trigger eignen sich zum Vorbefüllen/Normalisieren von Daten; AFTER-Trigger für kaskadierende unkritische Arbeit. Trigger laufen in derselben Transaktion wie die auslösende Anweisung.

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

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

Audit-Trigger (UPDATE)

Ein üblicher Trigger-Einsatz ist das Auditieren von Änderungen — OLD vs. NEW in eine History-Tabelle schreiben. Wertevergleich muss NULLs berücksichtigen (OLD.email <> NEW.email ist NULL, wenn eine Seite NULL ist), also mit IS-NULL-Checks absichern. AFTER DELETE nutzt OLD (NEW gibt es beim Löschen nicht). Audit-Logik leichtgewichtig halten, da sie bei jeder Zeilenänderung läuft. Trigger feuern auch bei Bulk-UPDATE/DELETE — bei großen Operationen kann das langsam werden.

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

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

OLD- und NEW-Werte

NEW hält die eingehenden Zeilenwerte, OLD die vorherigen. NEW lässt sich in BEFORE INSERT/UPDATE ändern, um Daten zu normalisieren oder vorzubefüllen; AFTER-Trigger und DELETE sehen sie schreibgeschützt. SIGNAL SQLSTATE '45000' ist der Standardweg, um aus einem Trigger oder Stored Program einen eigenen Fehler auszulösen (45000 heißt 'unbehandelte benutzerdefinierte Ausnahme'). BEFORE-Trigger für Validierung nutzen, die die Änderung abbrechen soll; AFTER für Nebenwirkungen.

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

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

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

Trigger bei mehreren Events (FOLLOWS/PRECEDES)

Seit 5.7.2 darf eine Tabelle mehr als einen Trigger pro Event und Timing haben, geordnet mit FOLLOWS/PRECEDES. Jeder Trigger behandelt weiterhin genau ein Event (INSERT, UPDATE oder DELETE) zu einem Timing (BEFORE/AFTER) — für mehrere Events mehrere Trigger anlegen. Die Reihenfolge zählt, wenn Trigger voneinander abhängen. INSTEAD-OF-Trigger gibt es in MySQL nicht (Views sind direkt aktualisierbar).

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

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

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

Trigger verwalten

SHOW TRIGGERS listet alle Trigger auf (beachte die Backtick-Quotierung des Spaltennamens 'Table', da er reserviert ist). SHOW CREATE TRIGGER gibt den vollständigen Körper und den Definer zurück. information_schema.triggers liefert strukturierte Metadaten fürs Auditing. DROP TRIGGER IF EXISTS ist skriptsicher. Trigger hängen an der Tabelle — wird die Tabelle gelöscht, verschwinden ihre Trigger. Auf den Definer achten: wie Prozeduren laufen Trigger standardmäßig mit Definer-Rechten.

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

# show definition
SHOW CREATE TRIGGER trg_users_before_insert\G

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

# drop a trigger
DROP TRIGGER IF EXISTS trg_users_before_insert;

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

Transaktionen & Locks

Transaktionskontrolle

MySQL committet standardmäßig automatisch — jede Anweisung ist ihre eigene Transaktion. START TRANSACTION/BEGIN startet einen expliziten Block, der mit COMMIT (speichern) oder ROLLBACK (rückgängig) endet. Nur transaktionale Engines (InnoDB) unterstützen Transaktionen; MyISAM nicht. Entscheidend: DDL-Anweisungen (CREATE/ALTER/DROP/TRUNCATE) committen die aktuelle Transaktion implizit und sind nicht rückrollbar — DDL nicht mit transaktionalem DML vermischen und Atomarität erwarten.

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

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

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

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

Savepoints

SAVEPOINT markiert einen benannten Punkt innerhalb einer Transaktion, zu dem du teilweise zurückrollen kannst, ohne die ganze Transaktion abzubrechen. ROLLBACK TO SAVEPOINT macht Arbeit nach dem Savepoint rückgängig; RELEASE SAVEPOINT wirft die Markierung weg (die Arbeit bleibt). Savepoints erlauben es, einen optionalen Schritt zu versuchen und elegant zu recovern. Sie sind außerhalb der Transaktion unsichtbar und verschwinden bei COMMIT/ROLLBACK. Verschachtelte Savepoints werden unterstützt.

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

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

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

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

Isolation Levels

Die Isolationsebene tauscht Konsistenz gegen Nebenläufigkeit. MySQLs Standard REPEATABLE READ verhindert Dirty und Non-Repeatable Reads und vermeidet — anders als der SQL-Standard — auch die meisten Phantom Reads via Next-Key-Locking. READ COMMITTED ist wegen geringerem Locking beliebt, erlaubt aber Non-Repeatable Reads. SERIALIZABLE macht alle Lesevorgänge zu Locking-Reads. InnoDB nutzt MVCC, Leser blockieren Schreiber auf keiner Ebene außer SERIALIZABLE.

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

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

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

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

LOCK TABLES

LOCK TABLES nimmt Locks auf Tabellenebene und ist meist ein MyISAM-Überbleibsel — InnoDBs Zeilen-Locking ist meist besser. Ein READ-Lock lässt andere Sessions lesen, blockiert aber Schreiben; ein WRITE-Lock blockiert alle anderen. Während du Locks hältst, darfst du nur auf die gesperrten Tabellen zugreifen. LOCK TABLES committet jede aktive Transaktion implizit und lässt sich also nicht mit Transaktionen kombinieren. Für InnoDB-Nebenläufigkeitskontrolle lieber SELECT ... FOR UPDATE.

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

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

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

SELECT ... FOR UPDATE / SHARE

SELECT ... FOR UPDATE nimmt ein exklusives Zeilen-Lock, sodass andere Transaktionen blockieren, wenn sie dieselbe Zeile aktualisieren oder sperren wollen — essenziell für Read-Modify-Write-Sequenzen wie Überweisungen. FOR SHARE nimmt ein geteiltes Lock (andere können lesen, nicht schreiben). Locks werden bei COMMIT/ROLLBACK freigegeben. 8.0 ergänzt NOWAIT (Fehler statt Warten) und SKIP LOCKED (gesperrte Zeilen überspringen) — perfekt für Job-Queue-Worker, die sich nicht blockieren sollen.

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

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

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

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

Deadlocks & Erkennung

InnoDB erkennt Deadlocks automatisch und rollt eine Transaktion zurück (das Opfer), damit die andere weiterläuft — du bekommst Fehler 1213. SHOW ENGINE INNODB STATUS zeigt den letzten Deadlock; innodb_print_all_deadlocks aktivieren, um jeden zu loggen. Deadlocks vorbeugen, indem Zeilen über Transaktionen hinweg in konsistenter Reihenfolge gesperrt werden, Transaktionen kurz bleiben und die gesperrten Spalten indiziert sind (sonst eskalieren Locks zu großen Bereichen). Um Transaktionen, die deadlocken können, immer Retry-Logik schreiben.

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

# see the last deadlock
SHOW ENGINE INNODB STATUS\G

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

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

Nutzerverwaltung

Nutzer anlegen & Authentifizierung

Ein MySQL-Konto ist immer 'user'@'host' — der Host kontrolliert, von wo sich der Nutzer verbinden darf; '%' ist ein Wildcard für jeden Host. MySQL 8.0 nutzt standardmäßig caching_sha2_password (sicherer), den manche ältere Clients nicht unterstützen — für sie auf mysql_native_password zurückfallen. Eng begrenzte Hosts (10.0.0.%) statt '%' bevorzugen. CREATE USER ist der alten Syntax GRANT ... IDENTIFIED BY vorzuziehen, die in 8.0 entfernt wurde.

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

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

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

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

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

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

GRANT-Privilegien

GRANT vergibt Privilegien auf vier Ebenen: global (*.*), Datenbank (db.*), Tabelle (db.table) und Spalte/Routine. ALL PRIVILEGES ist alles außer GRANT OPTION. Nur das nötigste Privileg vergeben — ein Reporting-Nutzer sollte nur SELECT bekommen. WITH GRANT OPTION erlaubt dem Nutzer, seine Privilegien weiterzuvergeben (gefährlich). FLUSH PRIVILEGES ist selten nötig, da GRANT die In-Memory-Tabellen aktualisiert; nur nach manuellem Editieren der mysql-Tabellen.

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;

REVOKE-Privilegien

REVOKE entfernt zuvor vergebene Privilegien; der Scope muss exakt passen. Um alles inklusive GRANT OPTION zu entziehen, REVOKE ALL PRIVILEGES, GRANT OPTION verwenden. Revoking löscht den Nutzer nicht — das Konto existiert weiter und kann sich verbinden, hat aber keine Privilegien, bis neue vergeben werden. SHOW GRANTS zeigt die effektiven Privilegien eines Nutzers; vor Zugriffsänderungen immer prüfen. Ein Konto vollständig entfernen heißt DROP USER.

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

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

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

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

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

Rollen (MySQL 8.0+)

Rollen (8.0+) erlauben es, Privilegien zu gruppieren und vielen Nutzern auf einmal zuzuweisen — das vereinfacht Zugriffsverwaltung. Einem Nutzer verliehene Rollen entfalten ihre Privilegien erst nach Aktivierung — SET DEFAULT ROLE macht sie beim Login standardmäßig aktiv. CURRENT_ROLE() zeigt aktive Rollen. Eine Rolle einmal ändern, und jeder Mitgliedernutzer erbt die Änderung. Deutlich sauberer, als jedem Nutzer dieselben Privilegien einzeln zu vergeben.

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

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

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

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

# see active roles
SELECT CURRENT_ROLE();

Passwortverwaltung

ALTER USER ist der moderne Weg zur Passwortverwaltung (SET PASSWORD ist veraltet). PASSWORD EXPIRE erzwingt einen Reset beim nächsten Login — nützlich für Passwort-Rotationsrichtlinien. Die validate_password-Komponente erzwingt Länge/Komplexität auf MEDIUM (gemischte Schreibweise, Ziffer, Sonderzeichen) und STRONG (zusätzlich keine Wörterbuchwörter). ACCOUNT LOCK/UNLOCK deaktiviert ein Konto temporär ohne es zu löschen — praktisch, um Zugang während Untersuchungen zu sperren.

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

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

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

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

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

Grants & Nutzer anzeigen

mysql.user ist die Systemtabelle mit allen Konten — abfragen, um Nutzer und Flags wie account_locked und password_expired zu inventarisieren. SHOW GRANTS ist die freundlichere, dekodierte Sicht auf die Privilegien eines Nutzers. CURRENT_USER() ist die Identität, die für Privilegienprüfungen genutzt wird (kann sich von USER() dem Connection-String unterscheiden) — wichtig bei Verbindung über Rolle oder Proxy. Regelmäßig auditieren für schlafende oder überprivilegierte Konten.

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

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

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

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

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

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

Backup & Wiederherstellung

mysqldump

mysqldump ist das klassische logische Backup-Werkzeug — es gibt SQL-Anweisungen aus, die die Datenbank neu erzeugen. --single-transaction liefert einen konsistenten InnoDB-Snapshot ohne Locking über eine einzige REPEATABLE-READ-Transaktion. --routines/--triggers/--events schließen Stored Programs ein (sonst weggelassen). Bei großen Datenbanken Percona XtraBackup oder MySQL Enterprise Backup (physische Backups) vorziehen — viel schneller als mysqldump.

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

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

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

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

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

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

Aus Dump wiederherstellen

Zur Wiederherstellung die Dump-Datei in den mysql-Client pipen oder darin SOURCE verwenden. Die Zieldatenbank muss bereits existieren (CREATE DATABASE, dann USE). Für schnellere Restores foreign key- und unique-Checks und Autocommit während des Ladens deaktivieren und danach wieder aktivieren — der Dump enthält diese Schutzvorrichtungen meist bereits. Einen komprimierten Dump mit gunzip wiederherstellen vermeidet das Dekomprimieren auf Platte. Restores immer auf einem Staging-Server testen.

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

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

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

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

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

Nach CSV exportieren

SELECT ... INTO OUTFILE schreibt eine serverseitige Datei, begrenzt durch das secure_file_priv-Verzeichnis (oder komplett deaktiviert, wenn secure_file_priv NULL ist) und verlangt das FILE-Privileg — eine bestehende Datei kann es nicht überschreiben. --batch --raw im mysql-Client erzeugt lokal eine tabulatorgetrennte Datei ohne diese Beschränkungen, ideal für Cron-Jobs. Die FIELDS/LINES-Klauseln steuern Trennzeichen, Quotierung und Escaping für CSV-Kompatibilität.

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

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

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

Aus CSV importieren

LOAD DATA INFILE ist der schnellste Weg, CSV zu laden — weit schneller als INSERT-Anweisungen. Die serverseitige Form liest aus einem Pfad unter secure_file_priv; LOCAL liest eine Client-Datei (aktiviert mit local_infile auf Client und Server). IGNORE 1 LINES überspringt einen Header. Mit @var Spalten auffangen und dann SET nutzen, um Werte zu transformieren (z. B. einen Datumsstring parsen). Bei sehr großen Lasten keys/foreign keys deaktivieren und eine einzige Transaktion nutzen.

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

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

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

Binärlog (mysqlbinlog)

Das Binärlog zeichnet alle datenändernden Anweisungen für Replikation und Point-in-Time-Recovery auf. SHOW MASTER STATUS liefert die aktuelle Datei und Position (die Replikationskoordinaten). mysqlbinlog wandelt eine Binlog-Datei zurück in SQL (oder Row-Events) zur Einsicht oder Wiedergabe — mit Start-/Stopp-Zeiten oder Positionen kombinieren, um bis zu einem exakten Zeitpunkt zu recovern. PURGE BINARY LOGS gibt Plattenplatz zurück; expire_logs_days/binlog_expire_logs_seconds das automatisieren lassen.

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

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

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

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

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

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

Clone-Plugin & Replikation

Das Clone-Plugin (8.0+) kopiert ein InnoDB-Datenverzeichnis physisch, viel schneller als ein logischer Dump — ideal zum Provisionieren von Replikaten oder Snapshots. Für die Replikation ein Replica auf eine Quelle richten mit CHANGE REPLICATION SOURCE TO (8.0+; früher CHANGE MASTER TO) und START REPLICA. GTID (SOURCE_AUTO_POSITION=1) macht die Replikationsposition robust. SHOW REPLICA STATUS zeigt Verzug und Fehler. Seconds_Behind_Source für die Gesundheit des Replica überwachen.

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

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

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

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

Performance-Optimierung

EXPLAIN

EXPLAIN offenbart den Abfrageplan: welcher Index genutzt wird, wie viele Zeilen geschätzt sind und ob ein Filesort oder eine temporäre Tabelle nötig ist. Die 'type'-Spalte rangiert Zugriffsmethoden — const/eq_ref sind am besten (Einzelzeilen-Lookup), ref/range nutzen einen Index, index scannt den ganzen Index, ALL scannt die ganze Tabelle (meist schlecht). 'Using index' heißt Covering-Index-only-Scan; 'Using filesort'/'Using temporary' warnen vor teuren Operationen, die es zu optimieren gilt.

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

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

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

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

EXPLAIN ANALYZE (8.0+)

EXPLAIN ANALYZE (8.0.17+) führt die Abfrage wirklich aus und meldet echte Zeilenzahlen und Timings pro Iterator — so siehst du, wo die Zeit wirklich verbraucht wird, viel genauer als die Schätzungen im reinen EXPLAIN. Für SELECT-Abfragen in Staging verwenden. Mit SHOW WARNINGS paaren, um die umgeschriebene Abfrage des Optimierers zu sehen (nach View-Merging, Constant-Folding). Für Schreibabfragen EXPLAIN auf ein read-only-Äquivalent nutzen oder mit performance_schema untersuchen.

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

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

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

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

Optimizer-Hints

Optimizer-Hints (/*+ ... */) übersteuern den Planer für eine einzige Anweisung. Sie sind gezielter als SET GLOBAL und überstehen Anweisungs-Restarts. Damit einen schlechten Plan fixen, aber auch die Ursache behandeln (veraltete Stats, fehlender Index, schiefe Daten) — Hints veralten, wenn sich Daten ändern. MAX_EXECUTION_TIME begrenzt read-only-Abfragen und schützt den Server vor entfesselten SELECTs. SET_VAR passt eine Session-Variable nur für eine Anweisung an.

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

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

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

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

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

Slow Query Log

Das Slow-Query-Log fängt Abfragen, die long_query_time überschreiten (in Sekunden, Bruchteile erlaubt). log_queries_not_using_indexes aktivieren, um vollständige Scans zu erwischen, selbst wenn sie schnell sind. mysqldumpslow aggregiert ähnliche Abfragen und rankt sie nach Zeit/Anzahl/Zeilen, damit du die schlimmsten zuerst angehst. Perconas pt-query-digest liefert weit reichere Berichte (Fingerprints, Perzentile) — das Standardwerkzeug für Slow-Log-Analyse. Log regelmäßig rotieren und aufräumen.

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

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

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

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

SHOW PROFILE & Performance Schema

SHOW PROFILE zerlegt eine Abfrage in Stufen (opening tables, sorting, sending data) — praktisch, aber veraltet. Das moderne Äquivalent ist performance_schema, ein Framework mit geringem Overhead, in 8.0 standardmäßig aktiv. events_statements_summary_by_digest abfragen, um die Abfragen zu finden, die serverweit die meiste aggregierte Zeit fressen, dann in events_stages_history_long für Details pro Stufe abtauchen. So findest du systemische Flaschenhälse, nicht nur einzelne langsame Abfragen.

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

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

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

Index-Optimierungstipps

Indexdesign entscheidet die meisten Performance-Schlachten. Der Leftmost-Prefix-Regel folgen, führende Wildcards vermeiden und indizierte Spalten nicht in Funktionen wrappen (oder eine generierte Spalte + Index nutzen). Auf heiße Abfragen Covering-Indizes zielen. WHERE-Bedingungen sargable halten (search-argument-able), damit ein Index genutzt werden kann: created_at >= x AND created_at < y schlägt DATE(created_at)=x. ANALYZE TABLE erfrischt die Kardinalitäts-Stats, auf die der Optimierer baut.

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

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

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

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

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

JSON-Operationen

JSON-Spalte & Insert

Der JSON-Typ speichert JSON in einem binären, validierten Format. Einfügen als JSON-String-Literal oder Werte aufbauen mit JSON_OBJECT() (Schlüssel/Wert-Paare) und JSON_ARRAY(). Ungültiges JSON wird beim Insert zurückgewiesen. Weil JSON als geparster Baum gespeichert wird, ist Member-Zugriff schnell, aber das ganze Dokument wird gemeinsam gelesen — für häufig abgefragte Skalarwerte diese in eine generierte Spalte extrahieren und diese indexieren: das Beste aus beiden Welten.

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

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

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

JSON_EXTRACT / -> / ->>

-> extrahiert ein JSON-Member (liefert JSON mit Anführungszeichen); ->> liefert es als unquotierten Text (die übliche Wahl für Vergleiche). Pfade nutzen $. für die Wurzel, $.key für Member, $.arr[0] für Array-Indizes und $.a.b für Verschachtelung. JSON_CONTAINS_PATH prüft die Existenz eines Schlüssels. JSON_EXTRACT ist die Funktionsform von ->. Um Dokumente effizient nach einem Member-Wert zu finden, eine generierte Spalte + Index auf attrs->>'$.color' ergänzen.

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

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

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

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

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

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

JSON_SET / INSERT / REPLACE / REMOVE

Diese Funktionen geben ein modifiziertes JSON-Dokument zurück — das Ergebnis muss der Spalte wieder zugewiesen werden, um sie zu aktualisieren. JSON_SET macht Upsert (hinzufügen oder aktualisieren); INSERT fügt nur Fehlendes hinzu; REPLACE aktualisiert nur Bestehendes; REMOVE löscht. JSON_MERGE_PATCH (RFC 7396) verschmilzt zwei Dokumente und entfernt auf null gesetzte Schlüssel — der Standardweg, einen partiellen Patch anzuwenden. JSON_MERGE_PRESERVE behält bestehende Arrays/Objekte, statt sie zu ersetzen.

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

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

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

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

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

JSON_TABLE (8.0+)

JSON_TABLE (8.0+) ist die Brücke von JSON zu relational — er zerlegt ein JSON-Dokument in Zeilen, die du wie jede Tabelle joinen, filtern und aggregieren kannst. Die columns-Klausel mappt JSON-Pfade auf typisierte Spalten. NESTED PATH entpackt verschachtelte Arrays in einem Durchgang. Das macht JSON-Spalten praktikabel für semi-strukturierte Daten: flexibles JSON speichern, mit JSON_TABLE relational zerlegen, wenn SQL-Analytics nötig ist, und eine generierte Spalte für heiße Pfade indexieren.

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

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

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

JSON-Aggregatfunktionen

JSON_ARRAYAGG sammelt Werte pro Gruppe in ein JSON-Array; JSON_OBJECTAGG baut ein Schlüssel->Wert-Objekt (spätere Schlüssel überschreiben frühere Duplikate). Kombiniert mit JSON_OBJECT baust du ein Array von Objekten für eine API-Antwort direkt in SQL — praktisch, um verschachtelte Payloads ohne Nachverarbeitung in der Anwendung zu bauen. NULL-Eingaben überspringt beides. Auf die Ergebnisgröße achten: eine riesige Gruppe erzeugt einen riesigen JSON-Wert.

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

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

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

JSON-Validierung & Schema

JSON_VALID prüft Wohlgeformtheit (Inserts erzwingen das ohnehin). JSON_PRETTY formatiert lesbar um; JSON_STORAGE_SIZE meldet Bytes. JSON_SCHEMA_VALID (8.0.17+) lässt ein CHECK-Constraint ein JSON-Schema erzwingen (erforderliche Schlüssel, Typen) — ein leichtgewichtiger Weg, JSON-Spalten Struktur zu geben ohne Flexibilität zu verlieren. JSON_TABLE und JSON_EXTRACT akzeptieren ON EMPTY / ON ERROR-Klauseln, um das Verhalten bei fehlendem oder ungültigem Pfad zu steuern.

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

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

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

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

Window-Funktionen

ROW_NUMBER()

ROW_NUMBER() weist jeder Zeile innerhalb ihrer Partition eine eindeutige fortlaufende Ganzzahl zu, geordnet wie angegeben — der kanonische Weg zum Rangieren, Deduplizieren und Top-N-pro-Gruppe. Anders als RANK/DENSE_RANK erzeugt er nie Gleichstände. Das DELETE-via-CTE-Muster entfernt Duplikate und behält eine Zeile pro Schlüssel (direkt aus einem CTE löschen geht nicht, also zuerst die zu löschenden IDs wählen). Window-Funktionen erfordern MySQL 8.0+.

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

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

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

RANK() & DENSE_RANK()

RANK() vergibt bei Gleichständen denselben Rang und überspringt die nächsten (1,1,4,5); DENSE_RANK() vergibt denselben Rang ohne Lücke (1,1,3,4); ROW_NUMBER() hat nie Gleichstände (1,2,3,4). Nach Absicht wählen: Leaderboard-artiges Ranking will meist RANK oder DENSE_RANK. DENSE_RANK ist der klassische Weg, das N-höchste pro Gruppe zu finden (rank=2 liefert das zweitverschiedenste Gehalt). Alle drei teilen dieselbe OVER()-Syntax.

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

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

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

LAG() & LEAD()

LAG() und LEAD() blicken relativ zur aktuellen Zeile auf eine andere — LAG zurück, LEAD vorwärts — ohne Self-Join. Sie nehmen einen optionalen Offset (Standard 1) und einen Default-Wert für den Fall, dass der Offset die Partition verlässt (Standard NULL). Perfekt für Perioden-über-Perioden-Differenzen, gleitende Vergleiche und das Erkennen von Serien/Brüchen in Sequenzen. Immer ein ORDER BY angeben, damit 'vorherig' wohldefiniert ist.

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

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

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

Window-Aggregate (SUM / AVG OVER)

Aggregatfunktionen mit OVER() berechnen das Aggregat über ein gleitendes Fenster von Zeilen, statt sie zusammenzufalten. Ohne Rahmen liefert SUM OVER (ORDER BY x) eine laufende Summe; mit ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ergibt sich ein trailing 7-Zeilen-gleitender Durchschnitt. Ein leeres OVER() berechnet die Gesamtsumme über alle Zeilen. PARTITION BY setzt das Fenster pro Gruppe zurück. Rahmen nutzen ROWS- (physisch) oder RANGE-Grenzen (wertbasiert).

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

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

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

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

FIRST_VALUE / LAST_VALUE / NTH_VALUE

FIRST_VALUE/NTH_VALUE liefern den Wert einer bestimmten Zeile innerhalb des Rahmens. LAST_VALUE ist eine häufige Falle: Der Standardrahmen ist RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, heißt also 'letzte' ist die aktuelle Zeile, nicht die letzte der Partition — den Rahmen auf UNBOUNDED FOLLOWING erweitern, um den echten letzten Wert zu bekommen. NTH_VALUE liefert NULL, bis die N-te Zeile erscheint. Explizite Rahmen beseitigen Mehrdeutigkeit und lohnen sich immer.

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

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

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

NTILE & PERCENT_RANK

NTILE(n) teilt die geordneten Zeilen so gleichmäßig wie möglich in n Buckets (bei Restgröße bekommen frühere Buckets die Extra-Zeile) — praktisch für Quartile/Dezile im Reporting. PERCENT_RANK() ist der relative Rang als 0..1-Anteil; CUME_DIST() ist die kumulative Verteilung (Anteil der Zeilen auf oder unter dem aktuellen Wert). NTILE zum Buckettisieren, PERCENT_RANK/CUME_DIST für Perzentil-Analytics.

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

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

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

Datum & Zeit

Aktuelle Datum/Zeit-Funktionen

NOW()/CURRENT_TIMESTAMP liefern denselben Wert innerhalb einer Anweisung (konsistent); SYSDATE() spiegelt die echte Zeit bei jedem Aufruf (vermeiden — nicht deterministisch und bricht die Replikation). Zeitstempel in UTC speichern (UTC_TIMESTAMP) und bei der Anzeige in lokale Zeit konvertieren. NOW(6) liefert Mikrosekunden-Präzision. UNIX_TIMESTAMP konvertiert zu/von Epoch-Sekunden — praktisch für Interop mit Sprachen, die Epoch-Zeit nutzen.

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

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

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

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

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

DATE_FORMAT & STR_TO_DATE

DATE_FORMAT formatiert ein Datum/eine Zeit mit Spezifizierern zu einem String — beachte kleines %i (Minuten) und %s (Sekunden), eine häufige Falle. STR_TO_DATE ist das Gegenstück, parst einen String in DATE/TIME/DATETIME. Nutzereingaben immer mit STR_TO_DATE parsen, statt sich auf implizite String-zu-Datum-Konvertierung zu verlassen, die von Formateinstellungen abhängt und zerbrechlich ist. Daten als DATE/DATETIME-Typen speichern, nicht als Strings.

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

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

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

DATE_ADD / DATE_SUB & INTERVAL

DATE_ADD/DATE_SUB mit INTERVAL ist die kanonische Datumsarithmetik; die Kurzform + INTERVAL / - INTERVAL ist sauberer. INTERVAL behandelt Monats-/Jahresübergänge korrekt (31. Januar + 1 Monat = 29. Februar im Schaltjahr). LAST_DAY liefert den letzten Tag des Monats des Arguments — praktisch für Monatsendlogik. Zusammengesetzte Einheiten wie YEAR_MONTH nehmen '1-2' (1 Jahr 2 Monate). Für Daten nie String-Mathematik — sie scheitert an Randfällen.

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

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

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

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

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

DATEDIFF / TIMESTAMPDIFF

DATEDIFF liefert ganze Tage (date1 - date2, Zeit ignoriert). TIMESTAMPDIFF liefert die Differenz in einer gewählten Einheit (unit, from, to) und kürzt Richtung Null — TIMESTAMPDIFF(YEAR, ...) ergibt das Alter nur, wenn der Geburtstag dieses Jahr schon war; für ein exaktes Alter 1 abziehen, wenn Monat/Tag noch nicht erreicht sind. Auf die Argumentreihenfolge achten: DATEDIFF ist (a,b) = a-b; TIMESTAMPDIFF ist (unit, from, to) = to-from.

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

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

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

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

EXTRACT & Datumsteile

EXTRACT(unit FROM date) liefert eine einzelne numerische Komponente; YEAR()/MONTH()/DAY() usw. sind Kurzformen der Üblichen; DAYNAME/MONTHNAME liefern Namen. Nach YEAR()/MONTH() eines Zeitstempels zu gruppieren ist der Standardweg für Monatsberichte. Beachte: DAYOFWEEK ist 1=Sonntag..7=Samstag, WEEKDAY ist 0=Montag..6=Sonntag — die zur Konvention passende wählen. WEEK() hat Modi für Wochenstart und Zählung.

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

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

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

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

Zeitzonenkonvertierung

Die Session-time_zone setzen, damit TIMESTAMP-Spalten in lokaler Zeit angezeigt werden; benannte Zonen (Asia/Shanghai) brauchen die geladenen System-Zeitzonentabellen via mysql_tzinfo_to_sql. CONVERT_TZ konvertiert ein wörtliches Datum/Zeit zwischen Zonen. Best Practice: alle Zeiten in UTC speichern (TIMESTAMP oder DATETIME in UTC), die time_zone jeder Session für die Anzeige setzen und bei Bedarf mit CONVERT_TZ konvertieren. Das vermeidet Mehrdeutigkeit über Nutzer in verschiedenen Regionen hinweg.

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

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

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

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

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

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

Was this helpful?