mysql-patterns

MySQL / MariaDB Database — Rules

Use these rules when developing with MySQL or MariaDB databases via FireDAC.

Connection Required

{ Configuração base }
FConnection.DriverName := 'MySQL';
FConnection.Params.Values['Server'] := 'localhost';
FConnection.Params.Values['Port'] := '3306';
FConnection.Params.Database := 'meubanco';
FConnection.Params.UserName := 'root';
FConnection.Params.Password := 'senha';
FConnection.Params.Values['CharacterSet'] := 'utf8mb4';  //NEVER 'utf8' (only 3 bytes!)

AUTO_INCREMENT and LAST_INSERT_ID()

//⚠️ MySQL DOES NOT support RETURNING!
//Use LAST_INSERT_ID() after INSERT

LQuery.SQL.Text := 'INSERT INTO customers (name) VALUES (:name)';
LQuery.ParamByName('name').AsString := ACustomer.Name;
LQuery.ExecSQL;

//Get generated ID
LQuery.SQL.Text := 'SELECT LAST_INSERT_ID() AS new_id';
LQuery.Open;
ACustomer.Id := LQuery.FieldByName('new_id').AsInteger;

//Alternativa FireDAC:
//ACustomer.Id := FConnection.GetLastAutoGenValue('');

UPSERT — INSERT ... ON DUPLICATE KEY UPDATE

LQuery.SQL.Text :=
  'INSERT INTO customers (cpf, name, email) ' +
  'VALUES (:cpf, :name, :email) ' +
  'ON DUPLICATE KEY UPDATE ' +
  '  name = VALUES(name), email = VALUES(email)';
LQuery.ExecSQL;

Native JSON (MySQL 5.7+)

{ Gravar JSON }
LQuery.ParamByName('settings').AsString := '{"theme":"dark"}';

{ Ler campo JSON — operador ->> }
LQuery.SQL.Text := 'SELECT settings->>''$.theme'' FROM ... WHERE id = :id';

Stored Procedures and Functions

Type	Delphi Call
Procedure	CALL sp_nome(:param) + ExecSQL
Scalar function	SELECT fn_nome(:param) + Open
Procedure with result set	CALL sp_nome(:param) + Open

Transactions

FConnection.StartTransaction;
try
  FRepoA.Insert(LObjA);
  FRepoB.Insert(LObjB);
  FConnection.Commit;
except
  FConnection.Rollback;
  raise;
end;

Isolation	FireDAC	Usage
Read Uncommitted	xiDirtyRead	⚠️ Almost never
Read Committed	xiReadCommitted	✅ Recommended
Repeatable Read	xiRepeatableRead	InnoDB Standard
Serializable	xiSerializable	Maximum consistency