Numeric, String, Temporal, JSON, Spatial, and Binary Data Types

Learning outcomes

A table definition is an executable data contract. Choosing VARCHAR(255), DOUBLE, DATETIME, or JSON by habit can silently encode the wrong range, precision, comparison, storage, or application semantics. This lesson starts from domain facts and asks which MySQL type can represent them without inventing values or losing information.

The course domain is ServiceHub, a field-service system. A work order has an integer identifier, status text, exact money, measurement values, scheduled timestamps, optional binary attachments, JSON metadata, and a geographic service location. That gives us a realistic reason to compare major MySQL type families.

Start from domain constraints, not favorite types

For each column, ask five questions: What values are valid? What precision must survive? How will the value be compared or sorted? Does character encoding matter? What will application drivers map to the value? The storage size is important, but correctness comes first.

Exact versus approximate numeric values

MySQL integer types and DECIMAL are exact-value types. FLOAT and DOUBLE are approximate floating-point types. For money, exact invoice totals, tax rates that must round under explicit business rules, or identifiers, an approximate floating type is usually the wrong representation. Scientific measurements may reasonably use DOUBLE when the application accepts floating-point behavior.

DROP TABLE IF EXISTS servicehub_lab.type_probe;CREATE TABLE servicehub_lab.type_probe (  probe_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,  quantity SMALLINT UNSIGNED NOT NULL,  exact_cost DECIMAL(12,2) NOT NULL,  measured_value DOUBLE NULL) ENGINE=InnoDB;INSERT INTO servicehub_lab.type_probeVALUES (1, 12, 199.95, 0.1 + 0.2);SELECT probe_id, quantity, exact_cost,       measured_value,       measured_value = 0.3 AS float_equals_literalFROM servicehub_lab.type_probe;

Do not promise a particular binary floating representation from a formatted client display. The important lesson is conceptual: DOUBLE uses approximate floating-point semantics, while DECIMAL is designed for exact fixed-point values. Test business calculations using the type you intend to deploy.

SELECT @@SESSION.sql_mode;-- SMALLINT UNSIGNED cannot represent a negative value.INSERT INTO servicehub_lab.type_probe  (probe_id, quantity, exact_cost)VALUES (2, -1, 10.00);SHOW WARNINGS;

With the default MySQL 8.4 strict transactional mode, this kind of invalid value is expected to fail rather than silently becoming an in-range value. Later in Lesson 5 you will deliberately change session sql_mode and compare the data-quality boundary.

Character strings versus binary strings

CHAR, VARCHAR, and TEXT store character data, so character sets and collations affect interpretation, comparison, and sorting. BINARY, VARBINARY, and BLOB store byte strings. A cryptographic digest or compressed payload is not text merely because an application can print it as hexadecimal.

CREATE TABLE servicehub_lab.text_binary_probe (  code CHAR(8) CHARACTER SET ascii NOT NULL,  customer_name VARCHAR(120) CHARACTER SET utf8mb4    COLLATE utf8mb4_0900_ai_ci NOT NULL,  notes TEXT CHARACTER SET utf8mb4 NULL,  sha256 BINARY(32) NOT NULL,  payload VARBINARY(256) NULL,  PRIMARY KEY (code)) ENGINE=InnoDB;SHOW CREATE TABLE servicehub_lab.text_binary_probe;SELECT COLUMN_NAME, COLUMN_TYPE,       CHARACTER_SET_NAME, COLLATION_NAME,       CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTHFROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_SCHEMA='servicehub_lab'  AND TABLE_NAME='text_binary_probe'ORDER BY ORDINAL_POSITION;

Notice that character maximum length and octet length can differ for multibyte character sets. Avoid treating VARCHAR(120) as “120 bytes.” In utf8mb4, a character may require multiple bytes. Index limits, row size, and connector encoding therefore require byte-aware reasoning.

Temporal values: DATE, DATETIME, TIMESTAMP, and explicit semantics

DATE is a calendar date. DATETIME is a date and time value without the same automatic session-time-zone conversion behavior as TIMESTAMP. TIMESTAMP represents an instant with server/session time-zone conversion semantics and has a more constrained range. The right choice depends on whether the domain value is a wall-clock schedule or a global instant.

DROP TABLE IF EXISTS servicehub_lab.temporal_probe;CREATE TABLE servicehub_lab.temporal_probe (  id INT NOT NULL PRIMARY KEY,  scheduled_wall DATETIME(6) NOT NULL,  recorded_instant TIMESTAMP(6) NOT NULL) ENGINE=InnoDB;SET time_zone = '+00:00';INSERT INTO servicehub_lab.temporal_probeVALUES (1, '2026-08-16 09:30:00.000000', '2026-08-16 09:30:00.000000');SELECT @@SESSION.time_zone, scheduled_wall, recorded_instantFROM servicehub_lab.temporal_probe;SET time_zone = '+04:00';SELECT @@SESSION.time_zone, scheduled_wall, recorded_instantFROM servicehub_lab.temporal_probe;SET time_zone = '+00:00';

Expected observation: the DATETIME display remains the stored wall-clock value while the TIMESTAMP display reflects the session time zone. This is exactly why a domain model must decide whether “09:30” is a local appointment time or an instant on a global timeline.

JSON, spatial, and binary data are not generic escape hatches

MySQL’s native JSON type validates that stored documents are valid JSON and integrates with JSON functions. Spatial types such as POINT, LINESTRING, and POLYGON carry geometry semantics and can use spatial functions and indexes. Neither should be used to avoid relational design when data has stable structure, constraints, and query patterns.

CREATE TABLE servicehub_lab.flex_probe (  id BIGINT UNSIGNED NOT NULL PRIMARY KEY,  attributes JSON NULL,  service_point POINT SRID 4326 NULL) ENGINE=InnoDB;INSERT INTO servicehub_lab.flex_probeVALUES (  1,  JSON_OBJECT('priority','high','tags',JSON_ARRAY('pump','urgent')),  ST_SRID(POINT(51.3890, 35.6892), 4326));SELECT id,       JSON_EXTRACT(attributes, '$.priority') AS priority,       ST_SRID(service_point) AS srid,       ST_AsText(service_point) AS point_textFROM servicehub_lab.flex_probe;

Coordinate order and the meaning of an SRID must be designed deliberately; do not copy spatial examples into a production geospatial model without reviewing MySQL’s spatial-reference-system rules. The lesson’s point is that the type carries semantics beyond “two numbers.”

Failure drill: a type that accepts the wrong business meaning

A schema can be syntactically valid and still be wrong. Storing money in DOUBLE, storing binary hashes in a case-insensitive text column, or storing local appointments in TIMESTAMP without a clear time-zone policy may pass every CREATE TABLE check while violating the business contract.

Repair the design by documenting the domain rule next to the DDL: “currency stored as DECIMAL(12,2),” “SHA-256 stored as BINARY(32),” “recorded_at is a global instant,” and so on. Schema review becomes much stronger when reviewers can challenge explicit assumptions.

Hands-on lab: build the ServiceHub type matrix

1. Record VERSION(), session sql_mode, character set/collation, and time zone.
2. Create the numeric, text/binary, temporal, and flexible probes above.
3. Run one valid insert and one boundary/invalid insert for each relevant family.
4. After every warning-producing statement, run SHOW WARNINGS before executing another statement.
5. Query INFORMATION_SCHEMA.COLUMNS to record COLUMN_TYPE, nullability, defaults, charset/collation, and generated metadata.
6. Write a short “why this type” note for each column; if you cannot justify it, revise the DDL.

Production judgment and references

Type choices influence indexes, row size, buffer-pool efficiency, connector mappings, migrations, replication compatibility, and every query that compares or converts values. Treat a type change as a data migration, not cosmetic DDL. Before narrowing a range or precision, profile existing data and test conversion under the target sql_mode.

The next lesson adds keys and constraints so that the server can reject invalid relationships and cross-column states instead of relying exclusively on application code.

Authoritative references

• MySQL 8.4 Reference Manual — Data Types
• MySQL 8.4 Reference Manual — Numeric Data Types
• MySQL 8.4 Reference Manual — DECIMAL
• MySQL 8.4 Reference Manual — String Data Types
• MySQL 8.4 Reference Manual — Date and Time Data Types
• MySQL 8.4 Reference Manual — The JSON Data Type
• MySQL 8.4 Reference Manual — Spatial Data Types