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
  /// 
  ///   Factory de connection MySQL via FireDAC.
  /// 
  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

/// 
///   Insere customer e obtém o id gerado pelo AUTO_INCREMENT.
///   MySQL NÃO suporta RETURNING — usar LAST_INSERT_ID().
/// 
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_

…

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [delphicleancode](https://github.com/delphicleancode)
- **Source:** [delphicleancode/delphi-spec-kit](https://github.com/delphicleancode/delphi-spec-kit)
- **License:** MIT
- **Homepage:** https://inovefast.com.br

Install and usage instructions live in the source repository linked above.