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>
  ///   Factory de connection MySQL 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>
///   Insere customer 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: 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 UPDAT