mysql-database

name	MySQL Database
description	Padrões de desenvolvimento com MySQL/MariaDB via SQLdb/Zeos — conexão, stored procedures, AUTO_INCREMENT, JSON, triggers, replication, migrations

MySQL Database — Skill

Use esta skill ao trabalhar com banco de dados MySQL ou MariaDB em projetos FreePascal/Lazarus via SQLdb/Zeos.

Quando Usar

• Ao configurar conexão SQLdb/Zeos com MySQL ou MariaDB
• Ao criar tabelas, stored procedures, functions, triggers e views
• Ao implementar Repositories com SQLdb/Zeos + MySQL
• Ao trabalhar com JSON nativo (MySQL 5.7+), Full-Text Search, Partitioning
• Ao planejar migrações de schema (scripts versionados)
• Ao desenvolver aplicações web com backend MySQL

Versões do MySQL

Versão	Novidades Relevantes
5.7	JSON nativo, Generated Columns, sys schema, Group Replication
8.0	CTEs recursivas, 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

Versão	Novidades Relevantes
10.2	CTEs recursivas, Window Functions, DEFAULT (expr)
10.3	INVISIBLE columns, INTERSECT/EXCEPT, Sequences
10.5	INET6 type, JSON_TABLE, S3 storage engine
11.0+	Calendário de versões, UUID v7, VECTOR type

Recomendação: Use MySQL 8.0+ ou MariaDB 10.5+ para novos projetos.

Conexão SQLdb/Zeos com MySQL

Configuração Mínima

unit MeuApp.Infra.Database.MySQL.Connection;

interface

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

type
  /// <summary>
  ///   Factory de conexão MySQL via SQLdb/Zeos.
  /// </summary>
  TMySQLConnectionFactory = class
  public
    class function CreateConnection(
      const AServer: string;
      const ADatabase: string;
      const AUserName: string = 'root';
      const APassword: string = '';
      APort: Integer = 3306
    ): TSQLConnection;
  end;

implementation

uses
  System.SysUtils;

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

  Result := TSQLConnection.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';  // SEMPRE utf8mb4 (suporta emoji/4-byte)

    { Opções do driver SQLdb/Zeos }
    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 — Configurar Client Library

uses
  SQLdb/Zeos.Phys.MySQLWrapper,
  SQLdb/Zeos.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;

ATENÇÃO: utf8 no MySQL tem apenas 3 bytes (não suporta emoji 🎉). Use sempre utf8mb4 para charset completo. O utf8 do MySQL é um alias para 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';

Tipos de Dados — Mapeamento MySQL ↔ FreePascal/Lazarus

MySQL	FreePascal/Lazarus (SQLdb/Zeos)	Observação
INT / INTEGER	ftInteger / AsInteger	32-bit signed
BIGINT	ftLargeint / AsLargeInt	64-bit
SMALLINT	ftSmallint / AsSmallInt	16-bit
TINYINT	ftSmallint / AsSmallInt	8-bit (não existe ftByte)
TINYINT(1)	ftBoolean / AsBoolean	Convenção MySQL para Boolean
VARCHAR(N)	ftString / AsString	Texto limitado
TEXT	ftMemo / AsString	Texto longo (até 64KB)
LONGTEXT	ftMemo / AsString	Texto muito longo (até 4GB)
DECIMAL(P,S)	ftBCD / AsCurrency	Valores monetários
DOUBLE	ftFloat / AsFloat	Ponto flutuante
FLOAT	ftSingle / AsSingle	32-bit float
DATE	ftDate / AsDateTime	Apenas data
TIME	ftTime / AsDateTime	Apenas hora
DATETIME	ftDateTime / AsDateTime	Data + Hora (sem timezone)
TIMESTAMP	ftDateTime / AsDateTime	Data + Hora (auto-update, UTC)
BOOLEAN / BOOL	ftBoolean / AsBoolean	Alias para TINYINT(1)
JSON	ftMemo / AsString	JSON nativo (MySQL 5.7+)
BLOB	ftBlob / AsBytes	Dados binários
LONGBLOB	ftBlob / AsBytes	Binário grande (até 4GB)
ENUM(...)	ftString / AsString	Até 65535 valores
SET(...)	ftString / AsString	Combinação de valores
CHAR(36)	ftString / AsString	UUID como string

AUTO_INCREMENT

Tabela com 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;

Obter o ID Gerado no FreePascal/Lazarus

/// <summary>
///   Insere cliente e obtém o id gerado pelo AUTO_INCREMENT.
///   MySQL NÃO suporta RETURNING — usar LAST_INSERT_ID().
/// </summary>
procedure TMySQLCustomerRepository.Insert(ACustomer: TCustomer);
var
  LQuery: TSQLQuery;
begin
  LQuery := TSQLQuery.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 SQLdb/Zeos (mais direto) }
    // ACustomer.Id := FConnection.GetLastAutoGenValue('');
  finally
    LQuery.Free;
  end;
end;

⚠️ ATENÇÃO: MySQL NÃO suporta RETURNING. Usar LAST_INSERT_ID() ou FConnection.GetLastAutoGenValue(''). Esta é uma diferença crítica em relação a Firebird e 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;

No FreePascal/Lazarus:

procedure TMySQLCustomerRepository.Upsert(ACustomer: TCustomer);
var
  LQuery: TSQLQuery;
begin
  LQuery := TSQLQuery.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;

JSON Nativo (MySQL 5.7+)

Armazenamento e Consulta

-- 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);

No FreePascal/Lazarus:

{ 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 e 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 ;

Chamar no FreePascal/Lazarus:

{ 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;

Nota: MySQL Procedures são chamadas com CALL, Functions com SELECT. Procedures podem retornar result sets via SELECT dentro do corpo.

ENUM e 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');

No FreePascal/Lazarus (mapear para enum Pascal):

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 e Isolation Levels

Níveis de Isolamento no MySQL

Nível	SQLdb/Zeos	Uso
Read Uncommitted	xiDirtyRead	Quase nunca — lê dados não commitados
Read Committed	xiReadCommitted	✅ Padrão recomendado
Repeatable Read	xiRepeatableRead	Padrão do InnoDB — snapshot no início da tx
Serializable	xiSerializable	Máxima consistência (locks implícitos)

Nota: O isolamento padrão do InnoDB é REPEATABLE READ, diferente de Firebird/PostgreSQL que usam READ COMMITTED.

Transação Explícita

procedure ExecuteInTransaction(AConnection: TSQLConnection; 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	✅ Sim	❌ Não
Foreign Keys	✅ Sim	❌ Não
Row-level Locking	✅ Sim	❌ Table-level
Full-Text Search	✅ Sim (5.6+)	✅ Sim
Crash Recovery	✅ Sim	❌ Não

Regra: Use sempre InnoDB (ENGINE=InnoDB). Nunca MyISAM em novos projetos.

Criação de Schema — Script de Migração

/* 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 ;

Migração de Schema no FreePascal/Lazarus

/// <summary>
///   Verifica se uma tabela existe no MySQL.
/// </summary>
function TableExists(AConnection: TSQLConnection;
  const ATableName: string): Boolean;
var
  LQuery: TSQLQuery;
begin
  LQuery := TSQLQuery.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>
///   Verifica se uma coluna existe em uma tabela.
/// </summary>
function ColumnExists(AConnection: TSQLConnection;
  const ATableName, AColumnName: string): Boolean;
var
  LQuery: TSQLQuery;
begin
  LQuery := TSQLQuery.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;

Tratamento de Erros MySQL

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 e 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 como 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 FreePascal/Lazarus e enviar como parâmetro

No FreePascal/Lazarus:

uses
  System.SysUtils;

{ Gerar UUID no FreePascal/Lazarus 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
);

Diferenças Chave: MySQL vs Firebird vs PostgreSQL

Feature	MySQL	Firebird	PostgreSQL
Auto-increment	AUTO_INCREMENT	Generator + BI Trigger	SERIAL / IDENTITY
Obter ID gerado	LAST_INSERT_ID()	RETURNING id	RETURNING id
UPSERT	ON DUPLICATE KEY UPDATE	Não nativo (FB5 parcial)	ON CONFLICT
JSON	JSON (5.7+)	Não nativo	JSONB (indexável)
Full-Text Search	FULLTEXT index	Não nativo	tsvector
ENUM	ENUM(...) nativo	Domain + CHECK	CREATE TYPE
Embedded	Não	Sim (fbclient.dll)	Não
Engine Choice	InnoDB, MyISAM, etc.	Single engine	Single engine
Charset recomendado	utf8mb4	UTF8	UTF8
Driver SQLdb/Zeos	MySQL	FB	PG
Client Library	libmysql.dll	fbclient.dll	libpq.dll
Default Isolation	Repeatable Read	Read Committed	Read Committed
Stored Procs	CALL sp()	EXECUTE PROCEDURE	CALL sp() (PG 11+)
Windows Functions	8.0+	3.0+ (básico)	Amplo
CTEs	8.0+	3.0+	Todas versões

Anti-Patterns MySQL a Evitar

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

// ✅ Parâmetros parametrizados
LQuery.SQL.Text := 'SELECT * FROM customers WHERE name = :name';
LQuery.ParamByName('name').AsString := AName;

// ❌ Usar utf8 (3 bytes, não suporta emoji)
Result.Params.Values['CharacterSet'] := 'utf8';

// ✅ Usar utf8mb4 (4 bytes, suporte completo)
Result.Params.Values['CharacterSet'] := 'utf8mb4';

// ❌ Tentar usar RETURNING (não existe no MySQL!)
LQuery.SQL.Text := 'INSERT INTO ... RETURNING id';

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

// ❌ Usar MyISAM para tabelas novas
CREATE TABLE t (...) ENGINE=MyISAM;

// ✅ Usar InnoDB sempre
CREATE TABLE t (...) ENGINE=InnoDB;

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

// ✅ LIMIT para paginação
LQuery.SQL.Text := 'SELECT id, customer_id, total FROM orders LIMIT :limit OFFSET :offset';

// ❌ Ignorar índices em colunas de WHERE/JOIN
// ✅ Criar índices para colunas usadas em filtros

Checklist MySQL

• Driver MySQL configurado no SQLdb/Zeos?
• CharacterSet := 'utf8mb4' (NÃO utf8)?
• libmysql.dll (32/64-bit) no PATH ou VendorLib?
• Tabelas criadas com ENGINE=InnoDB?
• AUTO_INCREMENT nas PKs com LAST_INSERT_ID() no FreePascal/Lazarus?
• Queries parametrizadas (sem concatenação)?
• Transactions explícitas para operações compostas?
• Erros tratados via EFDDBEngineException.Kind?
• Índices criados para colunas em WHERE e JOIN?
• Foreign Keys com ON DELETE/ON UPDATE apropriados?
• COLLATE utf8mb4_unicode_ci nas tabelas para comparação correta?
• information_schema para verificar metadata?