Lindorm:Dynamic columns

Verify dynamic columns are enabled

SHOW TABLE VARIABLES FROM t_dynamic LIKE 'DYNAMIC_COLUMNS';

Add predefined columns after enabling dynamic columns

After enabling dynamic columns, you can still add new predefined columns to the schema:

ALTER TABLE t_dynamic ADD COLUMN c3 int;

Write using SQL parameters (recommended)

Use PreparedStatement with setBytes() to write byte arrays directly. This avoids the ambiguity that can occur when passing hex strings as plain strings — especially when using MySQL to interact with Lindorm.

The following Java (JDBC) example creates a table with dynamic columns enabled and inserts a row where c2 is a dynamic column:

Connection conn = DriverManager.getConnection(lindorm-jdbc-url);

String createTable = "CREATE TABLE testTable (p1 VARCHAR, c1 INT, PRIMARY KEY(p1)) 'DYNAMIC_COLUMNS' = 'true'";
Statement statement = conn.createStatement();
statement.execute(createTable);

// Insert p1 and c1 (predefined) and c2 (dynamic column)
String sqlUpsert = "upsert into testTable (p1, c1, c2) values(?, ?, ?)";
try (PreparedStatement stmt = conn.prepareStatement(sqlUpsert)) {
    stmt.setString(1, "pk");
    stmt.setInt(2, 4);
    stmt.setBytes(3, new byte[] {0, 1});  // Pass byte array directly
    stmt.executeUpdate();
}

Write using SQL statements (Lindorm-cli)

Pass values as hex strings in UPSERT statements. Each hex string represents a byte array, where every two hexadecimal digits encode one byte.

The following examples use the t_dynamic table (schema: p1 INT primary key, c1 INT, c2 VARCHAR). Columns c3, c4, c5, and c6 are dynamic columns.

Successful write — hex string as a plain string:

UPSERT INTO t_dynamic (p1, c2, c3) VALUES (1, '1', '41');

Successful write — hex string with multiple bytes:

UPSERT INTO t_dynamic (p1, c4) VALUES (2, 'ef0011');

Preferred syntax in Lindorm SQL 2.6.8 and later — use `x'...'` prefix to distinguish hex strings from ordinary strings:

UPSERT INTO t_dynamic (p1, c4) VALUES (3, x'ef0011');

The x'ef0011' literal writes three bytes — 0xEF, 0x00, and 0x11 — not the six-character string ef0011.

Failed write — odd-length hex string:

UPSERT INTO t_dynamic (p1, c5) VALUES (4, 'f');

This fails because f is a single hex character. Hex strings must have an even number of characters (each byte requires two hex digits). Use 0f instead.

Failed write — invalid hex characters:

UPSERT INTO t_dynamic (p1, c6) VALUES (5, x'gf');

This fails because g is not a valid hexadecimal character (valid: 0–9, A–F).

Query specific dynamic columns

Specify dynamic column names explicitly in the SELECT clause:

SELECT p1, c2, c3, c4 FROM t_dynamic WHERE p1 = 1;

Result:

+----+----+------+------+
| p1 | c2 |  c3  |  c4  |
+----+----+------+------+
| 1  | 1  | 0x41 | null |
+----+----+------+------+

Discover all dynamic columns in a table

Use SELECT * with a LIMIT clause to retrieve all columns, including dynamic ones. The LIMIT clause is required to ensure the integrity of the metadata of the result set. The default maximum value of LIMIT is 5,000. You can specify a maximum value. If the queried value exceeds the maximum value, an error is returned.

SELECT * FROM t_dynamic LIMIT 10;

Result:

+----+------+------+------+----------+
| p1 |  c1  |  c2  |  c3  |    c4    |
+----+------+------+------+----------+
| 1  | null | 1    | 0x41 | null     |
| 2  | null | null | null | 0xef0011 |
| 3  | null | null | null | 0xef0011 |
+----+------+------+------+----------+

Use dynamic columns in WHERE clauses

Always include the primary key or an index key in the WHERE clause to ensure query performance. When filtering on dynamic column values via Lindorm-cli or SQL, use hex strings.

Successful query:

SELECT p1, c4 FROM t_dynamic WHERE p1 = 3 AND c4 = x'ef0011';

Failed query — the value '1' is not a hex string:

SELECT p1, c1, c4 FROM t_dynamic WHERE p1 = 2 AND c4 = '1';

MySQL command-line tool

The MySQL command-line tool displays dynamic column values as question marks (?) by default.