Type Mapping
Oninit® MySQL-2-Informix — Type Mapping
CREATE TABLE statements are intercepted, parsed column by
column, and each MySQL type is rewritten to its Informix equivalent
before the statement is forwarded. The reverse mapping is applied by
SHOW CREATE TABLE so the output reads MySQL-shaped —
ORM introspection and developer-facing
SHOW CREATE TABLE calls see the same column declarations
the user wrote.
Numeric
| MySQL type | Informix type |
|---|
| TINYINT | SMALLINT |
| MEDIUMINT | INTEGER |
| INT / INTEGER | INTEGER |
| BIGINT | BIGINT |
| DOUBLE | FLOAT |
| FLOAT / REAL | SMALLFLOAT |
| DECIMAL(p,s) / NUMERIC(p,s) | DECIMAL(p,s) |
| BOOL / BOOLEAN | BOOLEAN |
| BIT(N) | SMALLINT / INTEGER / BIGINT selected by N; b'01010'-style literals are pre-rewritten to decimal integers so they survive into the integer column transparently. |
| SERIAL keyword | BIGSERIAL |
| AUTO_INCREMENT=N | SERIAL(N) / BIGSERIAL(N) seed clause |
Date and time
| MySQL type | Informix type |
|---|
| DATE | DATE (no change) |
| DATETIME / TIMESTAMP | DATETIME YEAR TO SECOND |
| DATETIME(n) / TIMESTAMP(n) | DATETIME YEAR TO FRACTION(n) (n=1–5; 6 clamped to 5) |
| TIME | DATETIME HOUR TO SECOND |
| TIME(n) | DATETIME HOUR TO FRACTION(n) |
Character
| MySQL type | Informix type |
|---|
| CHAR(N) | CHAR(N) |
| VARCHAR(N) | VARCHAR(N) (or LVARCHAR(N) when N exceeds VARCHAR limits) |
| TINYTEXT / MEDIUMTEXT / LONGTEXT | TEXT |
| ENUM(...) / SET(...) | VARCHAR(255) |
Binary
| MySQL type | Informix type |
|---|
| BLOB / TINYBLOB / MEDIUMBLOB / LONGBLOB | BYTE |
| BINARY(N) / VARBINARY(N) | LVARCHAR(N) (binary-faithful via the byte-clean column path) |
INSERTs into BYTE columns from MySQL clients carry the
bytes inline (INSERT INTO t VALUES (1, 'data') or with a
hex literal x'68656c6c6f'). Informix rejects inline
literals into BYTE with Error 274; the SQLI-protocol
backend transparently rewrites the statement to a bound INSERT on
the wire so the row lands. See the
Features page
§“INSERT into BYTE / TEXT columns” for the
behavioural surface (column-list INSERTs, multi-row VALUES, and
NULL-into-blob handling).
JSON and spatial
| MySQL type | Informix type |
|---|
| JSON | JSON via the UDTVAR wire path; SHOW CREATE TABLE reverses correctly via the sysxtdtypes catalog. |
| GEOMETRY, POINT, LINESTRING, POLYGON, multi-* and GEOMETRYCOLLECTION | LVARCHAR(2048) WKT (well-known text) by default; rewrites to native ST_* types when the cfg key spatial_datablade=true is set and the IDS instance has the Spatial DataBlade installed (probed at startup against the sysxtdtypes catalog). |
Stripped attributes
The translator removes MySQL-only attributes that have no Informix
equivalent or that Informix handles by default:
- UNSIGNED, ZEROFILL
- AUTO_INCREMENT (the =N seed value, when present, is folded into the SERIAL / BIGSERIAL clause)
- ENGINE=..., DEFAULT CHARSET=..., COLLATE=...
- Backtick-quoted identifiers — reduced to bare names
Reverse mapping (SHOW CREATE TABLE)
The same table is consulted in reverse when an Informix table is
re-presented to the MySQL client through SHOW CREATE TABLE.
The output reads MySQL-shaped: an ORM that introspected the schema
for code generation, or a developer running
SHOW CREATE TABLE from mysql(1), sees the same
column declarations they originally wrote. The reverse path joins
the sysxtdtypes catalog so JSON columns reverse to
JSON rather than leaking the underlying
LVARCHAR / UDTVAR representation.
