Classes · 2 min read

AuroraDbStatementBuilder

A class used to build the SQL statements for Aurora MySQL.

This class is the BaseStatementBuilder-derived implementation for Aurora MySQL. It is automatically registered by AuroraDbBootstrap — you do not need to instantiate it directly under normal use.

It generates `table`-quoted, @-parameterized SQL, and uses LIMIT/OFFSET for BatchQuery and the internal skip/take query.

For Insert and InsertAll, the newly generated identity value is returned via LAST_INSERT_ID(); for a non-identity primary key it is echoed back with a follow-up SELECT keyed on the supplied value instead. InsertAll reads every row’s identity from a single standalone VALUES ROW(LAST_INSERT_ID() + n), ROW(LAST_INSERT_ID() + n), ... statement, assuming the batch’s auto-increment values were assigned contiguously.

The standalone VALUES ROW(...) statement requires MySQL 8.0.19 or later — Aurora MySQL version 3. Aurora MySQL version 2 (MySQL 5.7-compatible) rejects it with a syntax error.

Merge and MergeAll are compiled as INSERT ... ON DUPLICATE KEY UPDATE ... (MySQL’s native upsert), returning COALESCE(<primary key parameter>, LAST_INSERT_ID()) so the result reflects either the supplied key or the identity value generated by the insert branch.

The identity column, when present, is never overwritten by the UPDATE branch’s incoming parameter — it is instead reassigned to COALESCE(NULLIF(<parameter>, 0), LAST_INSERT_ID(<column>)), keeping it pointed at the affected row’s real identity whether the statement inserted or updated. Without this, an update carrying a default (0) identity value would corrupt the row’s real identity and make LAST_INSERT_ID() unreliable for the rest of the batch.

That COALESCE(...) expression is additionally wrapped in a CAST(... AS UNSIGNED)/CAST(... AS SIGNED), chosen from the key column’s CLR type (unsigned integer types cast to UNSIGNED, signed integer types cast to SIGNED, defaulting to UNSIGNED when there is no key column) — this avoids a type-mismatch error when combining a signed literal with an unsigned AUTO_INCREMENT value.

This builder also strips the extra space the base builder emits before the opening parenthesis of COUNT, MAX, MIN and SUM calls (e.g. COUNT ( becomes COUNT().

No convertFieldResolver is supplied to the base constructor, so — like MySQL — a Field with an explicit .Type is not rendered as a CAST/CONVERT expression for Aurora MySQL.

Constructors

public AuroraDbStatementBuilder()
public AuroraDbStatementBuilder(IDbSetting dbSetting,
    IResolver<Field, IDbSetting, string> convertFieldResolver = null,
    IResolver<Type, Type> averageableClientTypeResolver = null)

The parameterless constructor resolves dbSetting via DbSettingMapper.Get<AuroraDbConnection>(), so it can only be used after AuroraDbBootstrap has run.

Usability

Use StatementBuilderMapper to override it with a custom implementation.

StatementBuilderMapper.Add(typeof(AuroraDbConnection), new MyCustomAuroraDbStatementBuilder(dbSetting));