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

MariaDB

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

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