Mysql database

MySQL Database — Skill

Use this skill when working with MySQL or MariaDB databases in Delphi projects via FireDAC.

When to Use

• When configuring FireDAC connection with MySQL or MariaDB
• When creating tables, stored procedures, functions, triggers and views
• When implementing Repositories with FireDAC + MySQL
• When working with native JSON (MySQL 5.7+), Full-Text Search, Partitioning
• When planning schema migrations (versioned scripts)
• When developing web applications with MySQL backend

MySQL Versions

Version	Relevant News
5.7	Native JSON, Generated Columns, sys schema, Group Replication
8.0	Recursive CTEs, Window Functions, DEFAULT (expr), Roles, INVISIBLE indexes, NOWAIT/SKIP LOCKED
8.4 LTS	LTS release, Firewall improvements, Plugin improvements
9.0+	Vector type, JavaScript stored programs (preview)

MariaDB

Version	Relevant News
10.2	Recursive CTEs, Window Functions, DEFAULT (expr)
10.3	INVISIBLE columns, INTERSECT/EXCEPT, Sequences
10.5	INET6 type, JSON_TABLE, S3 storage engine
11.0+	Release Calendar, UUID v7, VECTOR type

Recommendation: Use MySQL 8.0+ or ​​MariaDB 10.5+ for new projects.

FireDAC connection with MySQL

Minimum Configuration

unit MeuApp.Infra.Database.MySQL.Connection;

interface

uses
  FireDAC.Comp.Client,
  FireDAC.Phys.MySQL,       //Driver MySQL
  FireDAC.Phys.MySQLDef,    //Defaults do MySQL
  FireDAC.Stan.Def,
  FireDAC.DApt;

type
  ///<summary>
  ///MySQL connection factory via FireDAC.
  ///</summary>
  TMySQLConnectionFactory = class
  public
    class function CreateConnection(
      const AServer: string;
      const ADatabase: string;
      const AUserName: string = 'root';
      const APassword: string = '';
      APort: Integer = 3306
    ): TFDConnection;
  end;

implementation

uses
  System.SysUtils;

class function TMySQLConnectionFactory.CreateConnection(
  const AServer, ADatabase, AUserName, APassword: string;
  APort: Integer): TFDConnection;
begin
  if ADatabase.Trim.IsEmpty then
    raise EArgumentException.Create('ADatabase não pode ser vazio');

  Result := TFDConnection.Create(nil);
  try
    Result.DriverName := 'MySQL';
    Result.Params.Values['Server'] := AServer;
    Result.Params.Values['Port'] := APort.ToString;
    Result.Params.Database := ADatabase;
    Result.Params.UserName := AUserName;
    Result.Params.Password := APassword;

    { Configurações recomendadas }
    Result.Params.Values['CharacterSet'] := 'utf8mb4';  //ALWAYS utf8mb4 (suporta emoji/4-byte)

    { Opções do driver FireDAC }
    Result.FormatOptions.StrsTrim2Len := True;
    Result.FetchOptions.Mode := fmAll;
    Result.ResourceOptions.AutoReconnect := True;
    Result.TxOptions.Isolation := xiReadCommitted;

    Result.Connected := True;
  except
    Result.Free;
    raise;
  end;
end;

FDPhysMySQLDriverLink — Configure Client Library

uses
  FireDAC.Phys.MySQLWrapper,
  FireDAC.Phys.MySQL;

var
  LDriverLink: TFDPhysMySQLDriverLink;
begin
  LDriverLink := TFDPhysMySQLDriverLink.Create(nil);
  try
    { Para MySQL 8.x: libmysql.dll }
    LDriverLink.VendorLib := 'C:\MySQL\lib\libmysql.dll';
    { Para MariaDB: libmariadb.dll }
    //LDriverLink.VendorLib := 'C:\MariaDB\lib\libmariadb.dll';
  finally
    { DriverLink vive por toda a aplicação — criar no DataModule }
  end;
end;

ATTENTION: utf8 in MySQL is only 3 bytes (does not support emoji 🎉). always use utf8mb4 for full charset. MySQL's utf8 is an alias for utf8mb3.

Connection Pooling

{ Via FDManager }
with FDManager.ConnectionDefs.AddConnectionDef do
begin
  Name := 'MySQL_Pool';
  DriverID := 'MySQL';
  Params.Values['Server'] := 'localhost';
  Params.Values['Port'] := '3306';
  Params.Values['Database'] := 'meubanco';
  Params.Values['User_Name'] := 'root';
  Params.Values['Password'] := 'senha';
  Params.Values['CharacterSet'] := 'utf8mb4';
  Params.Values['Pooled'] := 'True';
  Params.Values['POOL_MaximumItems'] := '50';
  Params.Values['POOL_CleanupTimeout'] := '30000';
end;

SSL/TLS

Result.Params.Values['SSL_ca'] := '/path/to/ca-cert.pem';
Result.Params.Values['SSL_cert'] := '/path/to/client-cert.pem';
Result.Params.Values['SSL_key'] := '/path/to/client-key.pem';

Data Types — MySQL Mapping ↔ Delphi

MySQL	Delphi (FireDAC)	Note
INT / INTEGER	ftInteger / AsInteger	32-bit signed
BIGINT	ftLargeint / AsLargeInt	64-bit
SMALLINT	ftSmallint / AsSmallInt	16-bit
TINYINT	ftSmallint / AsSmallInt	8-bit (ftByte does not exist)
TINYINT(1)	ftBoolean / AsBoolean	MySQL Convention for Boolean
VARCHAR(N)	ftString / AsString	Limited text
TEXT	ftMemo / AsString	Long text (up to 64KB)
LONGTEXT	ftMemo / AsString	Very long text (up to 4GB)
DECIMAL(P,S)	ftBCD / AsCurrency	Monetary values ​​
DOUBLE	ftFloat / AsFloat	Ponto flutuante
FLOAT	ftSingle / AsSingle	32-bit float
DATE	ftDate / AsDateTime	Date only
TIME	ftTime / AsDateTime	Just in time
DATETIME	ftDateTime / AsDateTime	Date + Time (without timezone)
TIMESTAMP	ftDateTime / AsDateTime	Data + Hora (auto-update, UTC)
BOOLEAN / BOOL	ftBoolean / AsBoolean	Alias ​​for TINYINT(1)
JSON	ftMemo / AsString	Native JSON (MySQL 5.7+)
BLOB	ftBlob / AsBytes	Binary data
LONGBLOB	ftBlob / AsBytes	Large binary (up to 4GB)
ENUM(...)	ftString / AsString	Up to 65535 values ​​
SET(...)	ftString / AsString	Combination of values ​​
CHAR(36)	ftString / AsString	UUID as string

AUTO_INCREMENT

Table with AUTO_INCREMENT

CREATE TABLE customers (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(100) NOT NULL,
  email VARCHAR(150)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Get the ID Generated in Delphi

///<summary>
///Inserts customer and obtains the id generated by AUTO_INCREMENT.
///MySQL DOES NOT support RETURNING — use LAST_INSERT_ID().
///</summary>
procedure TMySQLCustomerRepository.Insert(ACustomer: TCustomer);
var
  LQuery: TFDQuery;
begin
  LQuery := TFDQuery.Create(nil);
  try
    LQuery.Connection := FConnection;

    { Método 1: Duas queries (mais seguro e portável) }
    LQuery.SQL.Text :=
      'INSERT INTO customers (name, cpf, email, status) ' +
      'VALUES (:name, :cpf, :email, :status)';
    LQuery.ParamByName('name').AsString := ACustomer.Name;
    LQuery.ParamByName('cpf').AsString := ACustomer.Cpf;
    LQuery.ParamByName('email').AsString := ACustomer.Email;
    LQuery.ParamByName('status').AsSmallInt := Ord(ACustomer.Status);
    LQuery.ExecSQL;

    { Obter LAST_INSERT_ID() }
    LQuery.SQL.Text := 'SELECT LAST_INSERT_ID() AS new_id';
    LQuery.Open;
    ACustomer.Id := LQuery.FieldByName('new_id').AsInteger;

    { Método 2: Via propriedade FireDAC (mais direto) }
    //ACustomer.Id := FConnection.GetLastAutoGenValue('');
  finally
    LQuery.Free;
  end;
end;

⚠️ ATTENTION: MySQL DOES NOT support RETURNING. Use LAST_INSERT_ID() or FConnection.GetLastAutoGenValue(''). This is a critical difference compared to Firebird and PostgreSQL.

UPSERT — INSERT ... ON DUPLICATE KEY UPDATE

-- Inserir ou atualizar se a PK/UNIQUE já existir
INSERT INTO customers (cpf, name, email, status)
VALUES (:cpf, :name, :email, :status)
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  email = VALUES(email),
  status = VALUES(status);

-- MySQL 8.0.19+: Alias com AS
INSERT INTO customers (cpf, name, email, status)
VALUES (:cpf, :name, :email, :status) AS new_data
ON DUPLICATE KEY UPDATE
  name = new_data.name,
  email = new_data.email,
  status = new_data.status;

In Delphi:

procedure TMySQLCustomerRepository.Upsert(ACustomer: TCustomer);
var
  LQuery: TFDQuery;
begin
  LQuery := TFDQuery.Create(nil);
  try
    LQuery.Connection := FConnection;
    LQuery.SQL.Text :=
      'INSERT INTO customers (cpf, name, email, status) ' +
      'VALUES (:cpf, :name, :email, :status) ' +
      'ON DUPLICATE KEY UPDATE ' +
      '  name = VALUES(name), email = VALUES(email), status = VALUES(status)';
    LQuery.ParamByName('cpf').AsString := ACustomer.Cpf;
    LQuery.ParamByName('name').AsString := ACustomer.Name;
    LQuery.ParamByName('email').AsString := ACustomer.Email;
    LQuery.ParamByName('status').AsSmallInt := Ord(ACustomer.Status);
    LQuery.ExecSQL;

    { Obter ID (seja insert ou update) }
    LQuery.SQL.Text := 'SELECT LAST_INSERT_ID() AS new_id';
    LQuery.Open;
    ACustomer.Id := LQuery.FieldByName('new_id').AsInteger;
  finally
    LQuery.Free;
  end;
end;

Native JSON (MySQL 5.7+)

Storage and Query

-- Tabela com coluna JSON
CREATE TABLE customer_settings (
  customer_id  INT NOT NULL REFERENCES customers(id),
  settings     JSON NOT NULL,
  PRIMARY KEY (customer_id)
);

-- Inserir JSON
INSERT INTO customer_settings (customer_id, settings)
VALUES (1, '{"theme": "dark", "language": "pt-BR", "notifications": true}');

-- Consultar campo específico (operador ->>)
SELECT JSON_UNQUOTE(JSON_EXTRACT(settings, '$.theme')) AS theme
FROM customer_settings WHERE customer_id = 1;

-- Sintaxe curta com ->>
SELECT settings->>'$.theme' AS theme FROM customer_settings WHERE customer_id = 1;

-- Filtrar por valor JSON
SELECT * FROM customer_settings
WHERE JSON_CONTAINS(settings, '"dark"', '$.theme');

-- Índice virtual para busca em JSON (Generated Column + Index)
ALTER TABLE customer_settings
  ADD COLUMN theme VARCHAR(50) GENERATED ALWAYS AS (settings->>'$.theme') VIRTUAL,
  ADD INDEX idx_theme (theme);

In Delphi:

{ Inserir JSON }
LQuery.SQL.Text :=
  'INSERT INTO customer_settings (customer_id, settings) ' +
  'VALUES (:customer_id, :settings)';
LQuery.ParamByName('customer_id').AsInteger := ACustomerId;
LQuery.ParamByName('settings').AsString := AJsonString;
LQuery.ExecSQL;

{ Ler campo JSON }
LQuery.SQL.Text :=
  'SELECT settings->>''$.theme'' AS theme ' +
  'FROM customer_settings WHERE customer_id = :id';
LQuery.ParamByName('id').AsInteger := ACustomerId;
LQuery.Open;
LTheme := LQuery.FieldByName('theme').AsString;

Full-Text Search (InnoDB)

-- Índice FULLTEXT (InnoDB, MyISAM)
ALTER TABLE products ADD FULLTEXT INDEX ft_product_search (name, description);

-- Busca Natural Language
SELECT *, MATCH(name, description) AGAINST('camisa azul' IN NATURAL LANGUAGE MODE) AS relevance
FROM products
WHERE MATCH(name, description) AGAINST('camisa azul' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;

-- Busca Boolean Mode (mais controle)
SELECT * FROM products
WHERE MATCH(name, description) AGAINST('+camisa +azul -infantil' IN BOOLEAN MODE);

Stored Procedures and Functions

-- Procedure (equivale a Executable no Firebird)
DELIMITER //
CREATE PROCEDURE sp_deactivate_customer(IN p_id INT)
BEGIN
  UPDATE customers SET status = 1, updated_at = NOW() WHERE id = p_id;
  IF ROW_COUNT() = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Customer not found';
  END IF;
END //
DELIMITER ;

-- Function escalar
DELIMITER //
CREATE FUNCTION fn_customer_full_name(p_id INT) RETURNS VARCHAR(200)
  READS SQL DATA
BEGIN
  DECLARE v_name VARCHAR(200);
  SELECT name INTO v_name FROM customers WHERE id = p_id;
  IF v_name IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Customer not found';
  END IF;
  RETURN v_name;
END //
DELIMITER ;

Call in Delphi:

{ Procedure }
LQuery.SQL.Text := 'CALL sp_deactivate_customer(:p_id)';
LQuery.ParamByName('p_id').AsInteger := ACustomerId;
LQuery.ExecSQL;

{ Function escalar }
LQuery.SQL.Text := 'SELECT fn_customer_full_name(:p_id) AS full_name';
LQuery.ParamByName('p_id').AsInteger := ACustomerId;
LQuery.Open;
LFullName := LQuery.FieldByName('full_name').AsString;

Note: MySQL Procedures are called with CALL, Functions with SELECT. Procedures can return result sets via SELECT inside the body.

ENUM and SET

-- ENUM: valor único de uma lista
CREATE TABLE orders (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  status    ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled')
              NOT NULL DEFAULT 'pending',
  priority  ENUM('low', 'medium', 'high') NOT NULL DEFAULT 'medium'
);

-- SET: múltiplos valores de uma lista
CREATE TABLE products (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  name    VARCHAR(100),
  tags    SET('new', 'sale', 'featured', 'limited') NOT NULL DEFAULT ''
);

-- Inserir SET
INSERT INTO products (name, tags) VALUES ('Camisa', 'new,featured');

In Delphi (map to Pascal enum):

type
  TOrderStatus = (osPending, osProcessing, osShipped, osDelivered, osCancelled);

const
  ORDER_STATUS_NAMES: array[TOrderStatus] of string = (
    'pending', 'processing', 'shipped', 'delivered', 'cancelled'
  );

{ Ler do banco }
LOrder.Status := StringToOrderStatus(LQuery.FieldByName('status').AsString);

{ Gravar no banco }
LQuery.ParamByName('status').AsString := ORDER_STATUS_NAMES[AOrder.Status];

##Triggers

DELIMITER //

-- Trigger BEFORE INSERT para validação
CREATE TRIGGER trg_customer_before_insert BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
  SET NEW.created_at = NOW();
  SET NEW.updated_at = NOW();
  IF NEW.name = '' OR NEW.name IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Customer name cannot be empty';
  END IF;
END //

-- Trigger BEFORE UPDATE para atualizar timestamp
CREATE TRIGGER trg_customer_before_update BEFORE UPDATE ON customers
FOR EACH ROW
BEGIN
  SET NEW.updated_at = NOW();
END //

DELIMITER ;

Transactions and Isolation Levels

Isolation Levels in MySQL

Level	FireDAC	Usage
Read Uncommitted	xiDirtyRead	Almost never — reads uncommitted data
Read Committed	xiReadCommitted	✅ Recommended pattern
Repeatable Read	xiRepeatableRead	InnoDB default — snapshot at start of tx
Serializable	xiSerializable	Maximum consistency (implicit locks)

Note: InnoDB's default isolation is REPEATABLE READ, unlike Firebird/PostgreSQL which use READ COMMITTED.

Explicit Transaction

procedure ExecuteInTransaction(AConnection: TFDConnection; AProc: TProc);
begin
  AConnection.StartTransaction;
  try
    AProc;
    AConnection.Commit;
  except
    AConnection.Rollback;
    raise;
  end;
end;

SAVEPOINT

FConnection.StartTransaction;
try
  FCustomerRepo.Insert(LCustomer);

  FConnection.ExecSQL('SAVEPOINT before_order');
  try
    FOrderRepo.Insert(LOrder);
  except
    FConnection.ExecSQL('ROLLBACK TO SAVEPOINT before_order');
  end;

  FConnection.Commit;
except
  FConnection.Rollback;
  raise;
end;

InnoDB vs MyISAM

Feature	InnoDB	MyISAM
Transactions	✅ Yes	❌ No
Foreign Keys	✅ Yes	❌ No
Row-level Locking	✅ Yes	❌ Table-level
Full-Text Search	✅ Yes (5.6+)	✅ Yes
Crash Recovery	✅ Yes	❌ No

Rule: Use always InnoDB (ENGINE=InnoDB). Never MyISAM in new projects.

Schema Creation — Migration Script

/* migration_001_initial_schema.sql */

/* ===== Tabelas ===== */
CREATE TABLE IF NOT EXISTS customers (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  cpf        VARCHAR(14) UNIQUE,
  email      VARCHAR(150),
  status     TINYINT NOT NULL DEFAULT 0 COMMENT '0=active, 1=inactive, 2=suspended',
  notes      TEXT,
  metadata   JSON,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_customer_name (name),
  INDEX idx_customer_cpf (cpf)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS products (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  price       DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  stock_qty   INT NOT NULL DEFAULT 0,
  description TEXT,
  status      TINYINT NOT NULL DEFAULT 0,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FULLTEXT INDEX ft_product (name, description)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS orders (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  customer_id   INT NOT NULL,
  order_date    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  total_amount  DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  status        ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled')
                  NOT NULL DEFAULT 'pending',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_order_customer (customer_id),
  INDEX idx_order_date (order_date),
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_items (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  order_id    INT NOT NULL,
  product_id  INT NOT NULL,
  quantity    INT NOT NULL,
  unit_price  DECIMAL(15, 2) NOT NULL,
  total_price DECIMAL(15, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* ===== Triggers ===== */
DELIMITER //
CREATE TRIGGER trg_customer_validate BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
  IF NEW.name = '' OR NEW.name IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Name cannot be empty';
  END IF;
END //
DELIMITER ;

/* ===== Procedures ===== */
DELIMITER //
CREATE PROCEDURE sp_deactivate_customer(IN p_id INT)
BEGIN
  UPDATE customers SET status = 1 WHERE id = p_id;
  IF ROW_COUNT() = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Customer not found';
  END IF;
END //
DELIMITER ;

Schema migration in Delphi

///<summary>
///Checks if a table exists in MySQL.
///</summary>
function TableExists(AConnection: TFDConnection;
  const ATableName: string): Boolean;
var
  LQuery: TFDQuery;
begin
  LQuery := TFDQuery.Create(nil);
  try
    LQuery.Connection := AConnection;
    LQuery.SQL.Text :=
      'SELECT COUNT(*) FROM information_schema.tables ' +
      'WHERE table_schema = DATABASE() AND table_name = :name';
    LQuery.ParamByName('name').AsString := ATableName;
    LQuery.Open;
    Result := LQuery.Fields[0].AsInteger > 0;
  finally
    LQuery.Free;
  end;
end;

///<summary>
///Checks whether a column exists in a table.
///</summary>
function ColumnExists(AConnection: TFDConnection;
  const ATableName, AColumnName: string): Boolean;
var
  LQuery: TFDQuery;
begin
  LQuery := TFDQuery.Create(nil);
  try
    LQuery.Connection := AConnection;
    LQuery.SQL.Text :=
      'SELECT COUNT(*) FROM information_schema.columns ' +
      'WHERE table_schema = DATABASE() AND table_name = :table AND column_name = :col';
    LQuery.ParamByName('table').AsString := ATableName;
    LQuery.ParamByName('col').AsString := AColumnName;
    LQuery.Open;
    Result := LQuery.Fields[0].AsInteger > 0;
  finally
    LQuery.Free;
  end;
end;

MySQL Error Handling

except
  on E: EFDDBEngineException do
  begin
    case E.Kind of
      ekUKViolated:
        raise EDuplicateException.Create('Registro duplicado: ' + E.Message);
      ekFKViolated:
        raise EDependencyException.Create('Violação de FK: ' + E.Message);
      ekRecordLocked:
        raise EConflictException.Create('Registro bloqueado — deadlock');
      ekServerGone:
        raise EConnectionLostException.Create('Conexão com MySQL perdida');
    else
      raise;
    end;
  end;
end;

{ Verificar código de erro MySQL específico }
except
  on E: EFDDBEngineException do
  begin
    { Códigos de erro MySQL comuns: }
    { 1062 = ER_DUP_ENTRY (duplicate key) }
    { 1451 = ER_ROW_IS_REFERENCED_2 (FK restrict) }
    { 1452 = ER_NO_REFERENCED_ROW_2 (FK no parent) }
    { 1213 = ER_LOCK_DEADLOCK }
    { 1205 = ER_LOCK_WAIT_TIMEOUT }
    { 2006 = CR_SERVER_GONE_ERROR }
    { 2013 = CR_SERVER_LOST }
    if E.Errors[0].ErrorCode = 1062 then
      raise EDuplicateException.Create('Valor duplicado')
    else
      raise;
  end;
end;

CTEs and Window Functions (MySQL 8.0+)

-- CTE (MySQL 8.0+)
WITH active_customers AS (
  SELECT id, name, email FROM customers WHERE status = 0
)
SELECT ac.name, COUNT(o.id) AS total_orders
FROM active_customers ac
LEFT JOIN orders o ON o.customer_id = ac.id
GROUP BY ac.id, ac.name;

-- CTE Recursiva (hierarquia)
WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 0 AS level
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, ct.level + 1
  FROM categories c JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY level, name;

-- Window Functions (MySQL 8.0+)
SELECT name, total_spent,
  RANK() OVER (ORDER BY total_spent DESC) AS ranking,
  ROW_NUMBER() OVER (ORDER BY total_spent DESC) AS row_num
FROM (
  SELECT c.name, SUM(o.total_amount) AS total_spent
  FROM customers c JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.name
) ranked;

UUID as Primary Key

-- Gerar UUID no MySQL
CREATE TABLE sessions (
  id        CHAR(36) NOT NULL DEFAULT (UUID()) PRIMARY KEY,
  user_id   INT NOT NULL,
  token     VARCHAR(255),
  INDEX idx_session_user (user_id)
) ENGINE=InnoDB;

-- MySQL 8.0+: UUID() como DEFAULT
-- MySQL < 8.0: gerar no Delphi e enviar como parâmetro

In Delphi:

uses
  System.SysUtils;

{ Gerar UUID no Delphi para MySQL < 8.0 }
LQuery.ParamByName('id').AsString := TGUID.NewGuid.ToString;

Partitioning

-- Particionamento por RANGE
CREATE TABLE orders (
  id          INT AUTO_INCREMENT,
  customer_id INT NOT NULL,
  order_date  DATE NOT NULL,
  total       DECIMAL(15,2),
  PRIMARY KEY (id, order_date)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p2026 VALUES LESS THAN (2027),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);

Key Differences: MySQL vs Firebird vs PostgreSQL

Feature	MySQL	Firebird	PostgreSQL
Auto-increment	AUTO_INCREMENT	Generator + BI Trigger	SERIAL / IDENTITY
Get generated ID	LAST_INSERT_ID()	RETURNING id	RETURNING id
UPSERT	ON DUPLICATE KEY UPDATE	Non-native (partial FB5)	ON CONFLICT
JSON	JSON (5.7+)	Non-native	JSONB (indexable)
Full-Text Search	FULLTEXT index	Non-native	tsvector
ENUM	ENUM(...) native	Domain + CHECK	CREATE TYPE
Embedded	No	Yes (fbclient.dll)	No
Engine Choice	InnoDB, MyISAM, etc.	Single engine	Single engine
Recommended Charset	utf8mb4	UTF8	UTF8
FireDAC Driver	MySQL	FB	PG
Client Library	libmysql.dll	fbclient.dll	libpq.dll
Default Isolation	Repeatable Read	Read Committed	Read Committed
StoredProcs	CALL sp()	EXECUTE PROCEDURE	CALL sp() (PG 11+)
Windows Functions	8.0+	3.0+ (basic)	Ample
CTEs	8.0+	3.0+	All versions

MySQL Anti-Patterns to Avoid

//❌ Concatenar SQL
LQuery.SQL.Text := 'SELECT * FROM customers WHERE name = ''' + AName + '''';

//✅ Parameterized parameters
LQuery.SQL.Text := 'SELECT * FROM customers WHERE name = :name';
LQuery.ParamByName('name').AsString := AName;

//❌ Use utf8 (3 bytes, does not support emoji)
Result.Params.Values['CharacterSet'] := 'utf8';

//✅ Use utf8mb4 (4 bytes, full support)
Result.Params.Values['CharacterSet'] := 'utf8mb4';

//❌ Try using RETURNING (it doesn't exist in MySQL!)
LQuery.SQL.Text := 'INSERT INTO ... RETURNING id';

//✅ Usar LAST_INSERT_ID()
LQuery.ExecSQL;
LQuery.SQL.Text := 'SELECT LAST_INSERT_ID()';
LQuery.Open;

//❌ Use MyISAM for new tables
CREATE TABLE t (...) ENGINE=MyISAM;

//✅ Always use InnoDB
CREATE TABLE t (...) ENGINE=InnoDB;

//❌ SELECT * sem LIMIT
LQuery.SQL.Text := 'SELECT * FROM orders';

//✅ LIMIT for pagination
LQuery.SQL.Text := 'SELECT id, customer_id, total FROM orders LIMIT :limit OFFSET :offset';

//❌ Ignore indexes on WHERE/JOIN columns
//✅ Create indexes for columns used in filters

MySQL Checklist

• Driver MySQL configured on FireDAC?
• CharacterSet := 'utf8mb4' (NOT utf8)?
• libmysql.dll (32/64-bit) in PATH or VendorLib?
• Tables created with ENGINE=InnoDB?
• AUTO_INCREMENT in PKs with LAST_INSERT_ID() in Delphi?
• Parameterized queries (without concatenation)?
• Explicit transactions for compound operations?
• Errors handled via EFDDBEngineException.Kind?
• Indexes created for columns in WHERE and JOIN?
• Foreign Keys with appropriate ON DELETE/ON UPDATE?
• COLLATE utf8mb4_unicode_ci in the tables for correct comparison?
• information_schema to check metadata?