Inside Army's MySQL Dialect: When SQL Dialects Are First-Class Citizens

GitHub: https://github.com/PillArmy/army

Module: io.qinarmy:army-mysql (built on army-core / army-jdbc) · Supports MySQL 5.5 / 5.6 / 5.7 / 8.0

Every claim in this article comes from the actual army-mysql and army-core source, and every code snippet is copied verbatim from the framework source or the army-example test suite — nothing is invented.

Most Java SQL libraries treat a "dialect" as one enum, one common syntax tree, and a handful of if/else branches. You get portable CRUD — but the moment you need STRAIGHT_JOIN, LOAD DATA, ON DUPLICATE KEY UPDATE, or SET @row := 0, you are back to string concatenation.

Army takes the opposite route. In Army, MySQL is not a flag — it is an independent grammar modeled clause by clause from the MySQL reference manual. The army-mysql module is about 30,000 lines of main code, of which the MySQL-specific SQL rendering layer alone is over 3,700 lines. Its entry class MySQLs exposes 18 statement factories covering SELECT/INSERT/REPLACE/UPDATE/DELETE (single-table, multi-table, and batch variants), VALUES, LOAD DATA, SET, and SHOW.

This article walks through that dialect from the source, showing what "dialect as a first-class citizen" actually means.

---

1. Module Anatomy: Three Layers

The army-mysql code separates cleanly into three layers:

io.army.criteria.mysql     — Contract layer: MySQLQuery / MySQLInsert / MySQLUpdate /
                            MySQLDelete / MySQLLoadData / MySQLReplace / MySQLShow ...
                            (all nested staged interfaces — they describe only what
                             you are allowed to write)

io.army.criteria.impl     — Builder layer: MySQLQueries / MySQLInserts / MySQLMultiUpdates /
                            MySQLHints / MySQLJsonFunctions / MySQLSpatialFunctions ...
                            (the MySQLs entry class lives here too)

io.army.dialect           — Rendering layer: MySQLParser → MySQLDialectParser (1,292 lines),
                            MySQLDdlParser (507 lines), MySQLIdentifierHandler,
                            MySQLLiteralHandler, MySQLMappingHandler, MySQLParserFactory

Wiring goes through a factory selected only when the target database is MySQL:

@Override
public DialectParser createDialectParser(DialectEnv environment) {
    final MySQLDialect dialect;
    dialect = (MySQLDialect) ParserFactoryUtils.targetDialect(environment, Database.MySQL);
    return MySQLDialectParser.create(environment, dialect);
}

The concrete version comes from ServerMeta — the metadata read from the live connection. MySQLDialect is a four-constant enum with deliberately simple version mapping:

public enum MySQLDialect implements Dialect {
    MySQL55(55), MySQL56(56), MySQL57(57), MySQL80(80);

    public static MySQLDialect from(final ServerMeta meta) {
        switch (meta.major()) {
            case 5:
                switch (meta.minor()) {
                    case 5: return MySQLDialect.MySQL55;
                    case 6: return MySQLDialect.MySQL56;
                    case 7: return MySQLDialect.MySQL57;
                    default: throw Database.unsupportedVersion(meta);
                }
            case 8:
            default:  return MySQLDialect.MySQL80;
        }
    }
}

That Dialect object is not decorative — the rendering layer uses it everywhere for version gating. CTEs, for example (only available in MySQL 8.0), are hard-blocked in MySQLParser.parseWithClause:

@Override
protected final void parseWithClause(final _Statement._WithClauseSpec spec, final _SqlContext context) {
    final List<_Cte> cteList = spec.cteList();
    if (cteList.isEmpty()) {
        return;
    }
    if (!this.asOf80) {
        throw _Exceptions.dontSupportWithClause(this.dialect);
    }
    withSubQuery(spec.isRecursive(), cteList, context, _SQLConsultant::assertStandardCte);
}

asOf80 is a boolean computed once at construction (dialect.compareWith(MySQLDialect.MySQL80) >= 0) and shared across the whole module. Writing .with("cte") against a 5.7 server does not produce SQL that fails on execution — it fails during statement rendering, before anything touches the database.

---

2. The Entry Point: 18 Factories on MySQLs

public abstract class MySQLs extends MySQLSyntax {

    // —— Queries: primary / batch / subquery / scalar subquery ——
    public static MySQLQuery.WithSpec<Select>                          query();
    public static MySQLQuery.WithSpec<Statement._BatchSelectParamSpec> batchQuery();
    public static MySQLQuery.WithSpec<SubQuery>                      subQuery();
    public static MySQLQuery.WithSpec<Expression>                   scalarSubQuery();

    // —— Writes: single-table / multi-table / batch, one factory each ——
    public static MySQLInsert._PrimaryOptionSpec                     singleInsert();
    public static MySQLReplace._PrimaryOptionSpec                    singleReplace();
    public static MySQLUpdate._SingleWithSpec<Update>                singleUpdate();
    public static MySQLUpdate._SingleWithSpec<...Batch...>            batchSingleUpdate();
    public static MySQLUpdate._MultiWithSpec<Update>                  multiUpdate();
    public static MySQLUpdate._MultiWithSpec<...Batch...>            batchMultiUpdate();
    public static MySQLDelete._SingleWithSpec<Delete>                singleDelete();
    public static MySQLDelete._SingleWithSpec<...Batch...>           batchSingleDelete();
    public static MySQLDelete._MultiWithSpec<Delete>                 multiDelete();
    public static MySQLDelete._MultiWithSpec<...Batch...>            batchMultiDelete();

    // —— MySQL-specific statements ——
    public static MySQLLoadData._LoadDataClause<DmlCommand>            loadDataStmt();
    public static MySQLSet._SetSpec                                   setStmt();
    public static MySQLValues.ValuesSpec<Values>                     valuesStmt();
    public static MySQLValues.ValuesSpec<SubValues>                   subValues();
}

Note the design decisions:

1. Single-table and multi-table DML are different types. singleUpdate() and multiUpdate() return entirely different spec interfaces, because UPDATE t SET ... and UPDATE t JOIN j ON ... SET ... are different statements in MySQL. Army refuses to stuff both into one interface and punish you with a runtime error.
2. Batch is not a boolean flag, it is a separate factory. batchMultiUpdate() returns Statement._BatchUpdateParamSpec, whose parameter-binding contract differs from a single statement — the distinction is in the type.
3. REPLACE is a standalone statement, not an insert().replace() modifier — in MySQL it is genuinely different (delete-then-insert, with different auto-increment and trigger behavior).
4. Every factory is a zero-state static method returning an immutable chained builder.

---

3. The Type System: MySQL's Quirks Encoded in an Enum

The MySQLType enum in army-core is MySQL's type dictionary; each entry is a triple of (SQL type name, cross-dialect semantic tag (ArmyType), default Java class). The MySQL-specific half is the most revealing:

public enum MySQLType implements SQLType {
    TINYINT_UNSIGNED("TINYINT UNSIGNED",  ArmyType.TINYINT_UNSIGNED,  Short.class),
    SMALLINT_UNSIGNED("SMALLINT UNSIGNED",  ArmyType.SMALLINT_UNSIGNED, Integer.class),
    MEDIUMINT_UNSIGNED("MEDIUMINT UNSIGNED", ArmyType.MEDIUMINT_UNSIGNED, Integer.class),
    INT_UNSIGNED("INT UNSIGNED",           ArmyType.INTEGER_UNSIGNED, Long.class),
    BIGINT_UNSIGNED("BIGINT UNSIGNED",     ArmyType.BIGINT_UNSIGNED,  BigInteger.class),
    DECIMAL_UNSIGNED("DECIMAL UNSIGNED",    ArmyType.DECIMAL_UNSIGNED, BigDecimal.class),

    YEAR("YEAR", ArmyType.YEAR, Year.class),
    ENUM("ENUM", ArmyType.ENUM, String.class),
    SET("SET", ArmyType.DIALECT_TYPE, String.class),
    JSON("JSON", ArmyType.JSON, String.class),
    VECTOR("VECTOR", ArmyType.VECTOR, float[].class),       // MySQL 9.x vector type

    POINT("POINT"), LINESTRING("LINESTRING"), POLYGON("POLYGON"),
    MULTIPOINT("MULTIPOINT"), MULTIPOLYGON("MULTIPOLYGON"),
    MULTILINESTRING("MULTILINESTRING"), GEOMETRYCOLLECTION("GEOMETRYCOLLECTION"),
    GEOMETRY("GEOMETRY", ArmyType.GEOMETRY, byte[].class),              // WKB binary
    // ...
}

Two details worth expanding on.

3.1 Unsigned integers: the smallest Java type that cannot overflow

MySQL's five unsigned integer ranges misalign naturally with Java's signed types. Army's default mapping:

MySQL type	Range	Army's default Java type	Why
TINYINT UNSIGNED	0 ~ 255	Short	255 does not fit in byte (-128~127)
SMALLINT UNSIGNED	0 ~ 65,535	Integer	65535 does not fit in short
MEDIUMINT UNSIGNED	0 ~ 16,777,215	Integer	Still within int range
INT UNSIGNED	0 ~ 4.29 billion	Long	Does not fit in int
BIGINT UNSIGNED	0 ~ 2^64−1	BigInteger	Beyond long

This is not a marketing promise; both layers enforce it. TinyIntUnsignedType.javaType() returns Short.class, and the dispatch table in AbstractMappingType routes each ArmyType.*_UNSIGNED to its implementation. You write no @TypeHandler — conversion behavior is a property of the type itself.

3.2 Where the proprietary types land

• SET maps to Java EnumSet; ENUM works through one of Army's three enum strategies (CodeEnum / LabelEnum / NameEnum).
• All eight spatial types bind as WKB (Well-Known Binary) byte[], paired with a spatial function library of 154 static methods (see Section 8).
• VECTOR targets the MySQL 9.x native vector type and shares the same ArmyType.VECTOR semantic tag and VectorType mapping class as PostgreSQL's pgvector — cross-dialect vector code stays largely reusable.

---

4. SELECT: A Typed Transcription of the MySQL Manual

This is where the dialect's depth is most visible. Each feature below maps to a real method (or method family) on the MySQLQuery chain.

4.1 SELECT modifiers: all 16 keywords modeled

MySQLs exposes every SELECT modifier from the manual as a type-safe constant:

public static final Modifier ALL;
public static final Modifier DISTINCTROW;
public static final Modifier HIGH_PRIORITY;
public static final Modifier STRAIGHT_JOIN;
public static final Modifier SQL_SMALL_RESULT;
public static final Modifier SQL_BIG_RESULT;
public static final Modifier SQL_BUFFER_RESULT;
public static final Modifier SQL_NO_CACHE;
public static final Modifier SQL_CALC_FOUND_ROWS;
// plus LOW_PRIORITY / DELAYED / QUICK / IGNORE / CONCURRENT / LOCAL,
// reused by INSERT / UPDATE / DELETE / LOAD DATA

Modifiers are passed as a List<Modifier>, and the renderer validates legality — SQL_CALC_FOUND_ROWS is only valid in SELECT, QUICK only in UPDATE/DELETE. A misplaced one fails at render time rather than producing SQL the server rejects.

4.2 Partition selection: .partition(p0, p1)

MySQL supports pruning directly on table references (FROM t PARTITION (p0,p1)). Army models this as a distinct chain stage right after the table reference (and alias). The real example below is from the MySQLCriteriaUnitTests in army-example; it also demonstrates DELETE modifiers and single-table DELETE ORDER BY + LIMIT in one chain:

stmt = MySQLs.singleDelete()
        .delete(hintSupplier, Arrays.asList(MySQLs.LOW_PRIORITY, MySQLs.QUICK, MySQLs.IGNORE))
        .from(ChinaRegion_.T, AS, "r")
        .partition("p1")
        .where(ChinaRegion_.createTime.between(SQLs::literal, map.get("startTime"), AND, map.get("endTIme")))
        .and(ChinaRegion_.updateTime.between(SQLs::param, map.get("startTime"), AND, map.get("endTIme")))
        .ifAnd(ChinaRegion_.version::equal, SQLs::literal, map.get("version"))
        .orderBy(ChinaRegion_.name::desc, ChinaRegion_.id)
        .ifLimit(map.get("rowCount"))
        .asDelete();

Single-table UPDATE, multi-table DML, and LOAD DATA carry the same partition clause.

4.3 Index hints: USE / IGNORE / FORCE INDEX × three purposes

This is the DBA's daily tuning tool, and Army models both of MySQL's orthogonal hint systems in full. The first is the table-level index hint, attached right after a table reference:

// USE / IGNORE / FORCE INDEX, each with optional FOR JOIN / FOR ORDER BY / FOR GROUP BY
.update(ChinaProvince_.T, AS, "p").useIndex(FOR, JOIN, "PRIMARY")
.join(ChinaRegion_.T, AS, "c").useIndex(FOR, JOIN, "PRIMARY")
    .on(ChinaRegion_.id::equal, ChinaProvince_.id)

Those lines are not mine — they are copied verbatim from MultiUpdateTests in army-example. One table can carry multiple hints, and every hint supports three styles: static enumeration, dynamic Consumer, and conditional ifUseIndex(BooleanSupplier).

4.4 Optimizer hints: the full ~30-item /*+ ... */ catalog

The second system is the MySQL 8.0 optimizer hint comment. The MySQLHints.HintType enum is essentially a table of contents of the manual:

enum HintType {
    JOIN_FIXED_ORDER, JOIN_ORDER, JOIN_PREFIX, JOIN_SUFFIX,
    BKA, NO_BKA, BNL, NO_BNL,
    DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN,
    HASH_JOIN, NO_HASH_JOIN, MERGE, NO_MERGE,
    GROUP_INDEX, NO_GROUP_INDEX, INDEX, NO_INDEX, INDEX_MERGE, NO_INDEX_MERGE,
    JOIN_INDEX, NO_JOIN_INDEX, MRR, NO_MRR, NO_ICP, NO_RANGE_OPTIMIZATION,
    ORDER_INDEX, NO_ORDER_INDEX, SKIP_SCAN, NO_SKIP_SCAN,
    SEMIJOIN, NO_SEMIJOIN,
    MAX_EXECUTION_TIME, SET_VAR, RESOURCE_GROUP, QB_NAME
}

Factories are public on MySQLSyntax (joinFixedOrder(qbName), joinOrder(...), indexLevelHint(...), subQueryHint(...), maxExecutionTime(ms), setVar("k=v"), qbName(name), etc.). Each hint is a self-describing node (_SelfDescribed.appendSql) that renders its own syntax and performs its own version check — JOIN_FIXED_ORDER, for example, throws "dontSupportHint" below 8.0.

Hints enter the statement lazily as a Supplier<List<Hint>>, alongside the modifiers. Again from MultiUpdateTests, a real case that hangs both /*+ SET_VAR(foreign_key_checks=OFF) */ and LOW_PRIORITY on one multi-table UPDATE:

final Supplier<List<Hint>> hintSupplier =
        () -> Collections.singletonList(MySQLs.setVar("foreign_key_checks=OFF"));

stmt = MySQLs.multiUpdate()
        .with("cte").as(sw -> sw.select(ChinaRegion_.id)
                .from(ChinaRegion_.T, AS, "c")
                .where(ChinaRegion_.id.in(SQLs::rowParam, extractRegionIdList(regionList)))
                .and(ChinaRegion_.regionType.equal(SQLs::param, RegionType.PROVINCE))
                .asQuery()
        ).space()
        .update(hintSupplier, Collections.singletonList(MySQLs.LOW_PRIORITY))
        .space(ChinaProvince_.T, AS, "p").useIndex(FOR, JOIN, "PRIMARY")
        .join(ChinaRegion_.T, AS, "c").useIndex(FOR, JOIN, "PRIMARY")
            .on(ChinaRegion_.id::equal, ChinaProvince_.id)
        .join("cte").on(ChinaRegion_.id::equal, SQLs.refField("cte", ChinaRegion_.ID))
        .set(ChinaRegion_.regionGdp, SQLs::plusEqual, SQLs::param, gdpAmount)
        .where(ChinaRegion_.id::in, SQLs.subQuery()
                .select(s -> s.space(SQLs.refField("subCte", ChinaRegion_.ID)))
                .from("cte", AS, "subCte")
                .asQuery())
        .and(ChinaRegion_.createTime.between(SQLs::param, now.minusMinutes(10), AND, now.plusSeconds(1)))
        .asUpdate();

One statement demonstrates: CTE → multi-table UPDATE → optimizer hint → legacy modifier → index hint → compound assignment (+= gdp) → subquery → dynamic predicate. This is what dialect depth means: every item in a MySQL DBA's playbook has a typed place in the API.

4.5 STRAIGHT_JOIN, WITH ROLLUP, and locking clauses

• STRAIGHT_JOIN: .straightJoin(table).on(...) forces the optimizer to join in the given order; a statement-level STRAIGHT_JOIN SELECT modifier is also available.
• WITH ROLLUP: there is a separate .withRollup() after GROUP BY and after ORDER BY (matching MySQL's two positions), each with an ifWithRollup(BooleanSupplier) variant.
• Locks: .forUpdate() / .forShare(), with .of(table...) to restrict locked tables, .nowait() / .skipLocked() (8.0), and the legacy .lockInShareMode().
• SELECT INTO variables: .into("@id", "@name") maps to SELECT ... INTO @id, @name.
• Set operations: MySQL 8.0's INTERSECT / EXCEPT (with ALL/DISTINCT variants) sit on the chain alongside UNION.

4.6 MySQL-ization in the renderer: backticks and LIMIT m, n

The shared ArmyParser exposes dialect differences as template methods, overridden by MySQLParser. The two most typical overrides:

abstract class MySQLParser extends _ArmyDialectParser {
    static final char BACKTICK = '`';

    @Override
    protected final char identifierDelimitedQuote() {
        return BACKTICK;                 // MySQL identifiers: backticks; Postgres: double quotes
    }

    @Override
    protected final void standardLimitClause(final _Expression offset,
                                            final _Expression rowCount, _SqlContext context) {
        if (offset != null && rowCount != null) {
            // MySQL native form: LIMIT offset, rowCount (not the standard LIMIT n OFFSET m)
            context.sqlBuilder().append(_Constant.SPACE_LIMIT);
            offset.appendSql(...);
            context.sqlBuilder().append(_Constant.SPACE_COMMA);
            rowCount.appendSql(...);
        }
        ...
    }
}

The constructor also validates escape policy up front: the MySQL dialect accepts only EscapeMode.DEFAULT for identifier escaping and DEFAULT/BACK_SLASH for literals — because the MySQL server defaults to backslash escaping, and allowing anything else would only generate SQL the server cannot parse.

---

5. DML: UPSERT, REPLACE, and Multi-Table Syntax

5.1 ON DUPLICATE KEY UPDATE — down to the 8.0.19 row alias

MySQLInsert._OnDuplicateKeyUpdateSpec offers two entry styles:

_StaticConflictUpdateClause<I, T> onDuplicateKey();        // field-by-field chaining
_DmlInsertClause<I> onDuplicateKeyUpdate(Consumer<...> c); // batched via Consumer

Real example, verbatim from InsertTests:

stmt = MySQLs.singleInsert()
        .ignoreReturnIds()
        .insertInto(ChinaRegion_.T)
        .parens(s -> s.space(ChinaRegion_.name, ChinaRegion_.regionGdp)
                .comma(ChinaRegion_.population, ChinaRegion_.parentId))
        .defaultValue(ChinaRegion_.regionGdp, SQLs::param, "88888.88")
        .defaultValue(ChinaRegion_.visible, SQLs::param, true)
        .defaultValue(ChinaRegion_.parentId, SQLs::param, 0)
        .values(regionList)
        .as("c")                                    // MySQL 8.0.19+ row alias AS new_row
        .onDuplicateKey()
        .update(ChinaRegion_.population, SQLs.field("c", ChinaRegion_.population))
        .comma(ChinaRegion_.regionGdp, SQLs.field("c", ChinaRegion_.regionGdp))
        .asInsert();

.as("c") corresponds to the ... AS new ON DUPLICATE KEY UPDATE col = new.col syntax introduced in MySQL 8.0.19 — hence the SQLs.field("c", ...) references to the incoming row. The older VALUES(col) function style is supported as well; both eras coexist on the chain.

Even better, the singleInsert() Javadoc turns MySQL's official affected-rows semantics (1 = inserted, 2 = updated, 0 = set to current values — which "actually never 0" in Army because the framework maintains updateTime) into an API contract, and states three limitations explicitly:

• if the table has a visible logical-delete field, UPSERT works only in Visible.BOTH mode;
• auto-increment PK + multi-row child-table domain insert cannot use ON DUPLICATE (the server cannot return correct multi-row generated keys on conflict);
• auto-increment PK + multi-row domain syntax with conflicts requires .ignoreReturnIds() beforehand.

These are not excuses for framework gaps — they are limitations of the MySQL server protocol, surfaced in the API contract before you hit them.

5.2 REPLACE: a standalone statement with its own limits

singleReplace() produces a dedicated MySQLReplace statement (rendered as REPLACE INTO), with its own entry Javadoc — including the same "multi-row domain replace of auto-increment keys requires ignoreReturnIds()" rule and the logical-delete visibility constraint.

5.3 Multi-table UPDATE / DELETE

• multiUpdate(): UPDATE t [hint] JOIN j [hint] ON ... SET ... WHERE .... SET supports compound assignment operators (SQLs::plusEqual renders gdp = gdp + ?), and everything FROM supports — CTEs, derived tables, index hints — is available.
• multiDelete(): both MySQL multi-table DELETE shapes are modeled — DELETE t1 FROM t1 JOIN t2 ... and DELETE FROM t1 USING t1 JOIN t2 ..., distinguished by how the target tables are chosen.
• Single-table UPDATE/DELETE keep MySQL's ORDER BY + LIMIT: the _OrderBySpec → _LimitSpec chain makes UPDATE t SET ... ORDER BY id LIMIT 100 and DELETE FROM t WHERE ... ORDER BY id LIMIT 100 — MySQL's unique batching idiom — a legal path.

On the rendering side, MySQLDialectParser (1,292 lines) has a dedicated method per shape — parseSingleUpdate / parseMultiUpdate / parseSingleDelete / parseMultiDelete — rather than one giant switch. Multi-delete rendering is annotated step by step in the source: WITH → DELETE keyword → hint comment block → modifiers → …

---

6. LOAD DATA: An "Ops Command" Made Type-Safe

LOAD DATA INFILE is MySQL's ultimate CSV-import weapon, and the area most ORMs refuse to touch. Army's MySQLLoadData interface models the entire manual syntax, and the chain order is the syntax order:

LOAD DATA [LOCAL] INFILE 'file'
    [REPLACE | IGNORE]
    INTO TABLE tbl [PARTITION (...)]
    [CHARACTER SET charset]
    [{FIELDS | COLUMNS} [TERMINATED BY 'x'] [[OPTIONALLY] ENCLOSED BY 'x'] [ESCAPED BY 'x']]
    [LINES [STARTING BY 'x'] [TERMINATED BY 'x']]
    [IGNORE n {LINES | ROWS}]
    [(col1, col2, ...)]
    [SET col = expr, ...]

The example from the entry Javadoc (mirrored by the real LoadDataTests):

stmt = MySQLs.loadDataStmt()
        .loadData(MySQLs.LOCAL)
        .infile(csvFile)
        .ignore()
        .intoTable(ChinaRegion_.T)
        .characterSet("utf8mb4")
        .columns(s -> s.terminatedBy(","))
        .lines(s -> s.terminatedBy("\n"))
        .ignore(1, SQLs.LINES)
        .set(ChinaRegion_.visible, SQLs::literal, true)
        .set(ChinaRegion_.regionType, SQLs::literal, RegionType.NONE)
        .asCommand();

rows = session.update(stmt, SyncStmtOption.preferServerPrepare(false));

Deep details:

1. Parent/child tables can be imported in a chain: .intoTable(ParentTableMeta) returns a _ChildLoadData spec, after which .child().loadData(LOCAL).infile(childCsv)...intoTable(ChildTableMeta)...asCommand() composes a parent CSV and a child CSV into one command sequence — serving Army's own parent/child domain model, a need no generic ETL framework would anticipate.
2. Escape policy is forced: the characters in TERMINATED BY / ENCLOSED BY / ESCAPED BY are always parsed with EscapeMode.BACK_SLASH regardless of the global LITERAL_ESCAPE_MODE, and the Javadoc explicitly requires the server's NO_BACKSLASH_ESCAPES SQL mode to be disabled — LOAD DATA escaping rules are hard-wired by MySQL.
3. Execution prerequisites are a contract: local_infile=ON, the JDBC allowLoadLocalInfile=true property, and the requirement for a client-prepared or static statement (the LOCAL INFILE Request protocol) are all listed in the entry Javadoc with links to the official docs.
4. The renderer parseLoadData builds the statement in numbered steps: LOAD DATA keyword → modifier validation (anything other than LOCAL/CONCURRENT throws) → INFILE → REPLACE/IGNORE → table and partitions → charset → FIELDS/LINES → IGNORE count → SET.

---

7. SET Statements and User Variables: Home for MySQL Procedural Tricks

7.1 setStmt(): four scopes

MySQL 8.x SET syntax has four scopes — GLOBAL, SESSION, PERSIST, and PERSIST_ONLY. Army distinguishes them with typed VarScope constants (MySQLs.PERSIST / PERSIST_ONLY reuse core's KeyWordVarScope), corresponding to dynamic server-variable persistence.

7.2 User variables and inline := assignment

This is the classic MySQL trick: generating row numbers inside one query with a user variable. Army not only supports it — it is a real test in army-example (VariableTests):

stmt = MySQLs.query()
        .select(s -> s.space(MySQLs.at("my_row_number").increment().as("rowNumber"))
                .comma("t", PERIOD, ChinaRegion_.T))
        .from(ChinaRegion_.T, AS, "t")
        .crossJoin(SQLs.subQuery()
                .select(MySQLs.at("my_row_number", SQLs.COLON_EQUAL, SQLs.LITERAL_0).as("n"))
                .asQuery()
        ).as("s")
        .where(ChinaRegion_.id.in(SQLs::rowParam, extractRegionIdList(regionList)))
        .orderBy(ChinaRegion_.id)
        .asQuery();

The generated SQL is the textbook pattern:

SELECT (@my_row_number := @my_row_number + 1) AS rowNumber, t.*
FROM china_region t
CROSS JOIN (SELECT @my_row_number := 0) s
WHERE t.id IN (...)
ORDER BY t.id

MySQLs.at(name) builds a user-variable reference, MySQLs.at(name, COLON_EQUAL, init) builds inline assignment, and atAtSession("sql_mode") / atAtGlobal(...) reference @@session. / @@global. system variables. The inline comment — "defer SELECT clause, so SELECT clause is executed after FROM clause" — shows the author knows exactly how MySQL's evaluation timing enables this trick. Modeling it type-safely is only possible if the builder understands how the server executes the SQL.

Additionally, the MySQLShow interface turns SHOW BINLOG EVENTS ... FROM ... IN ... LIMIT, SHOW CHARACTER SET LIKE/WHERE, SHOW COLUMNS, and SHOW COLLATION into DQL statements (returning DqlStatement, executable through the session) — bringing MySQL client-style commands into the same unified execution pipeline.

---

8. The Function Libraries: JSON, Spatial, and Aggregate Depth

Army does not expose functions as a func(String name, Object... args) escape hatch. MySQL-specific functions are statically implemented by family:

Implementation	Static methods	Representative functions
MySQLJsonFunctions	52	JSON_TABLE, JSON_VALUE, JSON_SEARCH, JSON_CONTAINS, -> / ->> operators
MySQLSpatialFunctions	154	ST_DISTANCE / ST_CONTAINS (OpenGIS family) plus the MBR family (mbrContains, mbrWithin, …)
MySQLStringFunctions	60	ELT, EXPORT_SET, FROM_BASE64, FIND_IN_SET, …
MySQLTimeFunctions	67	MySQL date/time functions and interval units
MySQLMiscellaneousFunctions	73	GROUP_CONCAT and friends
MySQLWindowFunctions	48	MySQL-side window function bindings

The JSON_TABLE depth deserves its own demonstration. A real example from FunctionTests:

stmt = MySQLs.query()
        .select(s -> s.space("t", PERIOD, ASTERISK))
        .from(jsonTable(jsonDocument, "$[*]", COLUMNS, s -> s
                .space("rowId", FOR_ORDINALITY)
                .comma("ac", MySQLType.VARCHAR.parens(100).characterSet("utf8mb4")
                                .collate("utf8mb4_unicode_ci"),
                        PATH, "$.a", o -> o.spaceDefault("111").onEmpty()
                                          .spaceDefault("999").onError())
                .comma("aj", MySQLType.JSON, PATH, "$.a",
                        o -> o.spaceDefault("{\"x\":333}").onEmpty())
                .comma("bx", MySQLType.INT, EXISTS, PATH, "$.b")
        ))
        .as("t")
        .asQuery();

This covers every JSON_TABLE column kind: FOR ORDINALITY ordinal columns, PATH columns (with SQL type, character set, and collation), EXISTS PATH boolean columns, and the two-level DEFAULT ... ON EMPTY / ON ERROR clauses. Note that MySQLType.VARCHAR.parens(100).characterSet(...).collate(...) returns a TypeDef — DDL-style type definitions are themselves composible chain objects, not concatenated strings.

MySQL's proprietary GROUP_CONCAT is equally complete (DISTINCT, multiple expressions, ORDER BY, SEPARATOR):

select(groupConcat(SQLs.DISTINCT, s -> s.space(ChinaRegion_.name)
                .comma(ChinaRegion_.createTime)
                .comma(ChinaRegion_.regionGdp),
        s -> s.orderBy(ChinaRegion_.name).separator(","))
        .as("nameGroup"))

---

9. DDL: Table Options Are Part of the Dialect Too

MySQLDdlParser (507 lines) renders schema diffs to DDL, including MySQL-proprietary table options: auto-increment columns render AUTO_INCREMENT, and CREATE TABLE statements carry ENGINE=InnoDB / CHARACTER SET=utf8mb4 table options. Type-name rendering is gated by MySQLParser.typeName — only data types that are instanceof MySQLType may appear in MySQL DDL/DML; sneaking in a PostgreSQL INT4RANGE is rejected at render time with unrecognizedTypeName.

---

10. Philosophy and Costs

What it gets right

1. Syntactic correctness is guaranteed by the type system, not by experience. Partitions, index hints, modifiers, locks, ROLLUP, and row aliases each occupy an unskippable chain stage; wrong clauses fail to compile in Java, and wrong versions (CTEs on 5.7, 8.0 hints on 5.7) fail immediately at render time.
2. Dialect differences live in template methods, not scattered ifs. Backticks, LIMIT m,n, WITH version gates, and lock syntax are final overrides of ArmyParser in MySQLParser; MySQL-specific statements self-render through _SelfDescribed nodes. The boundary between the shared skeleton and dialect flesh is clean.
3. Server-protocol limits are written as contracts. LOAD DATA's three prerequisites, the ON DUPLICATE conflicts with auto-increment keys and logical deletes, and the hard escape-mode constraints all live in entry Javadoc and build-time validation — not in production deadlocks or corrupted generated keys.
4. MySQL operational culture is respected. LOW_PRIORITY, QUICK, IGNORE, SET_VAR, SKIP LOCKED, user-variable row numbering, SHOW commands, multi-table deletes — the real DBA's toolbox, which most "database abstraction layers" pretend does not exist.

The costs are equally real

• A huge interface surface. The criteria/mysql package alone is roughly 7,200 lines of nested interfaces. Chains like _PrimaryOptionSpec → _PartitionSpec → _ColumnListSpec → _OnAsRowAliasSpec are a high wall for newcomers; the framework trades syntactic correctness for staged interfaces and passes the learning cost straight to the user.
• Version span carries baggage. Still supporting MySQL 5.5 means WITH, windows, INTERSECT/EXCEPT, and JSON_TABLE must all exist behind version gates; an 8.0-only baseline would be simpler.
• Portability is one-directional. A chain built with MySQLs.query() is bound to MySQL; cross-database code must stay on the SQLs.query() common subset — which deliberately removes about half of the features in this article. That is a conscious trade-off, but know it before adopting.
• No string-concatenation escape hatch is a double-edged sword. Every new MySQL syntax addition (newer JSON functions, for example) waits for framework modeling — but the release cadence is controlled by the maintainer.

Closing

Army's MySQL dialect answers a question few take seriously: if you do not aim for "write once, run anywhere," but instead for "write everything MySQL can, on MySQL," how far can a Java SQL DSL go? Its answer spans version awareness from 5.5 to 9.x, rendering overrides from backticks to LIMIT m,n, Java type selection for all five unsigned integers, parent/child LOAD DATA imports, 30 optimizer hints, and all four JSON_TABLE column kinds. The MySQL reference manual's table of contents is, quite literally, this module's package structure.

If your systems depend heavily on MySQL-specific power — multi-table DML, UPSERT, LOAD DATA, index hints, spatial/JSON — and you are not willing to give up compile-time checking, this dialect is worth an afternoon reading the MySQL suites in army-example.

---

Source code and runnable examples: https://github.com/PillArmy/army (army-mysql module; examples in army-example/src/test/java/io/army/session/sync/mysql/)