// Metadata created by nebel
//

[id="how-the-mysql-connector-maps-data-types_{context}"]
= How the MySQL connector maps data types

The {prodname} MySQL connector represents changes to rows with events that are structured like the table in which the row exists. The event contains a field for each column value. The MySQL data type of that column dictates how the value is represented in the event.

Columns that store strings are defined in MySQL with a character set and collation. The MySQL connector uses the column's character set when reading the binary representation of the column values in the binlog events. The following table shows how the connector maps the MySQL data types to both _literal_ and _semantic_ types.

* *literal type* : how the value is represented using Kafka Connect schema types
* *semantic type* : how the Kafka Connect schema captures the meaning of the field (schema name)

[cols="2,2,6"]
|===
|MySQL type |Literal type |Semantic type

|`BOOLEAN, BOOL`
|`BOOLEAN`
a| _n/a_

|`BIT(1)`
|`BOOLEAN`
a| _n/a_

|`BIT(>1)`
|`BYTES`
a| io.debezium.data.Bits

NOTE: The `length` schema parameter contains an integer that represents the number of bits. The `byte[]` contains the bits in _little-endian_ form and is sized to contain the specified number of bits.

.example (where n is bits)
----
numBytes = n/8 + (n%8== 0 ? 0 : 1)
----

|`TINYINT`
|`INT16`
a| _n/a_

|`SMALLINT[(M)]`
|`INT16`
a| _n/a_

|`MEDIUMINT[(M)]`
|`INT32`
a| _n/a_

|`INT, INTEGER[(M)]`
|`INT32`
a| _n/a_

|`BIGINT[(M)]`
|`INT64`
a| _n/a_

|`REAL[(M,D)]`
|`FLOAT32`
a| _n/a_

|`FLOAT[(M,D)]`
|`FLOAT64`
a| _n/a_

|`DOUBLE[(M,D)]`
|`FLOAT64`
a| _n/a_

|`CHAR(M)]`
|`STRING`
a| _n/a_

|`VARCHAR(M)]`
|`STRING`
a| _n/a_

|`BINARY(M)]`
|`BYTES`
a| _n/a_

|`VARBINARY(M)]`
|`BYTES`
a| _n/a_

|`TINYBLOB`
|`BYTES`
a| _n/a_

|`TINYTEXT`
|`STRING`
a| _n/a_

|`BLOB`
|`BYTES`
a| _n/a_

|`TEXT`
|`STRING`
a| _n/a_

|`MEDIUMBLOB`
|`BYTES`
a| _n/a_

|`MEDIUMTEXT`
|`STRING`
a| _n/a_

|`LONGBLOB`
|`BYTES`
a| _n/a_

|`LONGTEXT`
|`STRING`
a| _n/a_

|`JSON`
|`STRING`
a| io.debezium.data.Json

NOTE: Contains the string representation of a `JSON` document, array, or scalar.

|`ENUM`
|`STRING`
a| io.debezium.data.Enum

NOTE: The `allowed` schema parameter contains the comma-separated list of allowed values.

|`SET`
|`STRING`
a| io.debezium.data.EnumSet

NOTE: The `allowed` schema parameter contains the comma-separated list of allowed values.

|`YEAR[(2\|4)]`
|`INT32`
| io.debezium.time.Year

|`TIMESTAMP[(M)]`
|`STRING`
a| io.debezium.time.ZonedTimestamp

NOTE: In link:https://www.iso.org/iso-8601-date-and-time-format.html[ISO 8601] format with microsecond precision. MySQL allows `M` to be in the range of `0-6`.

|===

== Temporal values

Excluding the `TIMESTAMP` data type, MySQL temporal types depend on the value of the `time.precision.mode` configuration property.

TIP: See {link-prefix}:{link-mysql-connector}#mysql-connector-configuration-properties_{context}[MySQL connector configuration properties] for more details.

Temporal values without timezones are converted from UTC to milliseconds or microseconds (`DATETIME`) or to the configured database timezone (`TIMESTAMP`).

* `DATETIME` with a value of `2018-06-20 06:37:03` becomes `1529476623000`.
* `TIMESTAMP` with a value of `2018-06-20 06:37:03` becomes `2018-06-20T13:37:03Z`.

NOTE: MySQL allows zero-values for ``DATE``, ``DATETIME``, and ``TIMESTAMP`` columns, which are sometimes preferred over null values. However, the MySQL connector represents them as null values when the column definition allows nulls, or as the epoch day when the column does not allow nulls.

time.precision.mode=adaptive_time_microseconds(default)::
    The MySQL connector determins the literal type and semantic type based on the column's data type definition so that events represent exactly the values in the database; all time fields are in microseconds.
+
[cols="2,2,6"]
|===
|MySQL type |Literal type |Semantic type

|`DATE`
|`INT32`
a| io.debezium.time.Date

NOTE: Represents the number of days since epoch.

|`TIME[(M)]`
|`INT64`
a| io.debezium.time.MicroTime

NOTE: Represents the time value in microseconds and does not include timezone information. MySQL allows `M` to be in the range of `0-6`.

|`DATETIME, DATETIME(0), DATETIME(1), DATETIME(2), DATETIME(3)`
|`INT64`
a| io.debezium.time.Timestamp

NOTE: Represents the number of milliseconds past epoch and does not include timezone information.

|`DATETIME(4), DATETIME(5), DATETIME(6)`
|`INT64`
a| io.debezium.time.MicroTimestamp

NOTE: Represents the number of microseconds past epoch and does not include timezone information.

|===
+

time.precision.mode=connect::
    The MySQL connector uses the predefined Kafka Connect logical types. This approach is less precise than the default approach and the events could be less precise if the database column has a _fractional second precision_ value of greater than `3`.
+
[cols="2,2,6"]
|===
|MySQL type |Literal type |Semantic type

|`DATE`
|`INT32`
a| org.apache.kafka.connect.data.Date

NOTE: Represents the number of days since epoch.

|`TIME[(M)]`
|`INT64`
a| org.apache.kafka.connect.data.Time

NOTE: Represents the time value in microseconds since midnight and does not include timezone information.

|`DATETIME[(M)]`
|`INT64`
a| org.apache.kafka.connect.data.Timestamp

NOTE: Represents the number of milliseconds since epoch, and does not include timezone information.

|===
+


== Decimal values

Decimals are handled via the `decimal.handling.mode` property.

TIP: See {link-prefix}:{link-mysql-connector}#mysql-connector-configuration-properties_{context}[MySQL connector configuration properties] for more details.

decimal.handling.mode=precise::
+
[cols="3,2,5"]
|===
|MySQL type |Literal type |Semantic type

|`NUMERIC[(M[,D])]`
|`BYTES`
a| org.apache.kafka.connect.data.Decimal

NOTE: The `scale` schema parameter contains an integer that represents how many digits the decimal point shifted.

|`DECIMAL[(M[,D])]`
|`BYTES`
a| org.apache.kafka.connect.data.Decimal

NOTE: The `scale` schema parameter contains an integer that represents how many digits the decimal point shifted.

|===
+


decimal.handling.mode=double::
+
[cols="3,2,5"]
|===
|MySQL type |Literal type |Semantic type

|`NUMERIC[(M[,D])]`
|`FLOAT64`
a| _n/a_

|`DECIMAL[(M[,D])]`
|`FLOAT64`
a| _n/a_

|===
+


decimal.handling.mode=string::
+
[cols="3,2,5"]
|===
|MySQL type |Literal type |Semantic type

|`NUMERIC[(M[,D])]`
|`STRING`
a| _n/a_

|`DECIMAL[(M[,D])]`
|`STRING`
a| _n/a_

|===
+


== Boolean values

MySQL handles the `BOOLEAN` value internally in a specific way.
The `BOOLEAN` column is internally mapped to `TINYINT(1)` datatype.
When the table is created during streaming then it uses proper `BOOLEAN` mapping as {prodname} receives the original DDL.
During snapshot {prodname} executes `SHOW CREATE TABLE` to obtain table definition which returns `TINYINT(1)` for both `BOOLEAN` and `TINYINT(1)` columns.

{prodname} then has no way how to obtain the original type mapping and will map to `TINYINT(1)`.
ifdef::community[]
The operator can configure the out-of-the-box {link-prefix}:{link-custom-converters}[custom converter] `TinyIntOneToBooleanConverter` that would either map all `TINYINT(1)` columns to `BOOLEAN` or if `selector` parameter is set then a subset of columns could be enumerated using comma-separated regular expressions.
endif::community[]

An example configuration is

----
converters=boolean
boolean.type=io.debezium.connector.mysql.converters.TinyIntOneToBooleanConverter
boolean.selector=db1.table1.*, db1.table2.column1
----


== Spatial data types

Currently, the {prodname} MySQL connector supports the following spatial data types:

[cols="2,2,6"]
|===
|MySQL type |Literal type |Semantic type

|`GEOMETRY, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION`
|`STRUCT`
a| io.debezium.data.geometry.Geometry

NOTE: Contains a structure with two fields:

* `srid (INT32`: a spatial reference system id that defines the type of geometry object stored in the structure
* `wkb (BYTES)`: a binary representation of the geometry object encoded in the Well-Known-Binary (wkb) format. See the link:https://www.opengeospatial.org/standards/sfa[Open Geospatial Consortium] for more details.

|===
