// 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` or `STRING`
a| _n/a_

Either the raw bytes (the default), a base64-encoded String, or a hex-encoded String, based on the {link-prefix}:{link-mysql-connector}#mysql-property-binary-handling-mode[binary handling mode] setting

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

Either the raw bytes (the default), a base64-encoded String, or a hex-encoded String, based on the {link-prefix}:{link-mysql-connector}#mysql-property-binary-handling-mode[binary handling mode] setting

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

Either the raw bytes (the default), a base64-encoded String, or a hex-encoded String, based on the {link-prefix}:{link-mysql-connector}#mysql-property-binary-handling-mode[binary handling mode] setting

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

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

Either the raw bytes (the default), a base64-encoded String, or a hex-encoded String, based on the {link-prefix}:{link-mysql-connector}#mysql-property-binary-handling-mode[binary handling mode] setting

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

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

Either the raw bytes (the default), a base64-encoded String, or a hex-encoded String, based on the {link-prefix}:{link-mysql-connector}#mysql-property-binary-handling-mode[binary handling mode] setting

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

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

Either the raw bytes (the default), a base64-encoded String, or a hex-encoded String, based on the {link-prefix}:{link-mysql-connector}#mysql-property-binary-handling-mode[binary handling mode] setting

|`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. For `TIMESTAMP` columns whose default value is specified as `CURRENT_TIMESTAMP` or `NOW`, the value `1970-01-01 00:00:00` is used as the default value in the Kafka Connect schema. 

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

.Temporal values without time zones
The `DATETIME` type represents a local date and time such as "2018-01-13 09:48:27". As you can see, there is no time zone information. Such columns are converted into epoch milli-seconds or micro-seconds based on the column’s precision by using UTC. The `TIMESTAMP` type represents a timestamp without time zone information and is converted by MySQL from the server (or session’s) current time zone into UTC when writing and vice versa when reading back the value. For example:

* `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`.

Such columns are converted into an equivalent `io.debezium.time.ZonedTimestamp` in UTC based on the server (or session’s) current time zone. The time zone will be queried from the server by default. If this fails, it must be specified explicitly by the `database.serverTimezone` connector configuration property. For example, if the database’s time zone (either globally or configured for the connector by means of the `database.serverTimezone property`) is "America/Los_Angeles", the TIMESTAMP value "2018-06-20 06:37:03" is represented by a `ZonedTimestamp` with the value "2018-06-20T13:37:03Z".

Note that the time zone of the JVM running Kafka Connect and Debezium does not affect these conversions.

More details about properties related to termporal values are in the documentation for {link-prefix}:{link-mysql-connector}#mysql-connector-configuration-properties_{context}[MySQL connector configuration properties].

time.precision.mode=adaptive_time_microseconds(default)::
    The MySQL connector determines 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. Only positive `TIME` field values in the range of `00:00:00.000000` to `23:59:59.999999` can be captured correctly.
+
[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 time zone 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 time zone 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 time zone 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`. Only values in the range of `00:00:00.000` to `23:59:59.999` can be handled. Set `time.precision.mode=connect` only if you can ensure that the `TIME` values in your tables never exceed the supported ranges. The `connect` setting is expected to be removed in a future version of {prodname}.
+
[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 time zone information.

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

NOTE: Represents the number of milliseconds since epoch, and does not include time zone 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.

|===
