Numeric, String, Temporal, UUID, JSON/Text, Vector, Spatial, and Binary Types

Learning outcomes

ServiceHub must now store money, measured coordinates, timestamps, technician identifiers, flexible JSON metadata, binary signatures, and vector embeddings. The dangerous shortcut is to choose every type by habit—FLOAT for money, VARCHAR(255) for everything text-like, DATETIME for every time, and CHAR(36) for every UUID. Those declarations may accept data, but acceptance is not the same as preserving the domain correctly.

A database type is part of the application contract. It constrains representable values, storage/encoding, comparison semantics, indexes, conversion behavior, client metadata and sometimes replication compatibility. MariaDB also has engine-specific capabilities that deserve explicit version labels: the native UUID type is available from 10.7, while VECTOR(N) is available from 11.7.1. Both are valid on the 12.3.2 course baseline, but a migration to older servers must detect them.

1. Start with domain questions, not SQL spelling

Before choosing a type, ask what values are legal, whether arithmetic must be exact, the required range/scale, how values sort/compare, whether the value needs an index, whether an application driver has a natural mapping, and how the field should behave across time zones or character collations. A technically valid type can still be semantically wrong.

2. Exact and approximate numerics answer different questions

DECIMAL stores exact fixed-point values suited to money, rates with contractual precision, and other quantities where decimal rounding must be controlled. FLOAT and DOUBLE use approximate floating-point representation and are appropriate for scientific/measurement data when tiny binary rounding differences are expected. The mistake is not using floating point; the mistake is using it for a domain that requires exact decimal equality.

CREATE TABLE servicehub_sandbox.numeric_probe (  id INT PRIMARY KEY,  invoice_amount DECIMAL(12,2) NOT NULL,  latitude DOUBLE NOT NULL) ENGINE=InnoDB;INSERT INTO servicehub_sandbox.numeric_probeVALUES (1, 19.99, 35.6892);SELECT invoice_amount,       invoice_amount * 3 AS exact_decimal_total,       latitudeFROM servicehub_sandbox.numeric_probe;

3. Character data includes a collation contract

MariaDB character types are not just byte containers. A character set defines how characters are encoded; a collation defines comparison and ordering rules. CHAR is fixed-length, VARCHAR varies within a declared maximum, and the TEXT family supports larger values. The right choice depends on domain length, indexing, comparison, and access patterns—not an inherited “255” convention.

SELECT @@character_set_server,       @@collation_server,       @@character_set_connection,       @@collation_connection;CREATE TABLE servicehub_sandbox.customer_name_probe (  customer_id BIGINT PRIMARY KEY,  display_name VARCHAR(160)    CHARACTER SET utf8mb4    COLLATE utf8mb4_unicode_ci NOT NULL) ENGINE=InnoDB;SHOW FULL COLUMNS FROM servicehub_sandbox.customer_name_probe;

Collation affects uniqueness and sort semantics. If two strings compare equal under a chosen collation, a UNIQUE constraint may treat them as duplicates. Chapter 09 studies index behavior; for now, record the collation as part of the data contract and avoid changing it casually during migration.

4. Temporal types: storage and time-zone semantics are different

DATE stores a calendar date, TIME stores a time/duration-style value, and DATETIME stores a date-and-time value without a stored time-zone identifier. TIMESTAMP is special: MariaDB converts inserted values from the session time zone to UTC for storage and converts them back to the session time zone on retrieval. The server does not store the originating zone identifier with a TIMESTAMP value.

CREATE TABLE servicehub_sandbox.time_probe (  id INT PRIMARY KEY,  scheduled_local DATETIME(6) NOT NULL,  occurred_at TIMESTAMP(6) NOT NULL) ENGINE=InnoDB;SET time_zone = '+00:00';INSERT INTO servicehub_sandbox.time_probeVALUES (1,'2026-08-20 10:00:00.000000','2026-08-20 10:00:00.000000');SET time_zone = '+04:00';SELECT scheduled_local, occurred_atFROM servicehub_sandbox.time_probe;

The DATETIME text remains the same while the TIMESTAMP display shifts with the session zone. That does not make TIMESTAMP universally superior. Choose according to whether the domain represents an instant, a local schedule, or another temporal concept, and keep the application’s zone handling explicit.

5. MariaDB JSON is validated text, not MySQL binary JSON

MariaDB defines JSON as an alias for LONGTEXT COLLATE utf8mb4_bin for compatibility. Current documentation states that using the JSON alias automatically includes JSON validity checking, so malformed JSON is rejected. The assigned text representation is retained. This differs materially from MySQL’s native binary JSON representation, so dumps, metadata, indexing strategies and migration tests must not assume identical storage semantics.

CREATE TABLE servicehub_sandbox.asset_metadata (  asset_id BIGINT PRIMARY KEY,  attributes JSON NULL) ENGINE=InnoDB;INSERT INTO servicehub_sandbox.asset_metadataVALUES (1, '{"model":"HX-9","voltage":24}');SELECT DATA_TYPE, COLUMN_TYPE, COLLATION_NAMEFROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_SCHEMA='servicehub_sandbox'  AND TABLE_NAME='asset_metadata'  AND COLUMN_NAME='attributes';-- Intentionally invalid JSON: expected to fail validation.INSERT INTO servicehub_sandbox.asset_metadataVALUES (2, '{broken json}');

A JSON column is valuable when document-shaped optional attributes are genuinely part of the domain. It should not become an excuse to hide stable relational keys, monetary fields, timestamps or integrity rules inside an opaque document. MariaDB offers JSON functions, but the schema still needs an ownership and indexing strategy.

6. Native UUID, VECTOR, spatial and binary types need explicit prerequisites

MariaDB’s native UUID type is available from 10.7 and stores 128-bit UUID values with UUID-aware ordering behavior. On newer MariaDB releases, UUID generation functions include variants suited to different ordering/randomness goals. The native type avoids forcing applications to treat every identifier as printable text, but an existing application may still choose another representation for cross-database compatibility.

CREATE TABLE servicehub_sandbox.technician_token (  technician_uuid UUID PRIMARY KEY,  issued_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)) ENGINE=InnoDB;INSERT INTO servicehub_sandbox.technician_tokenVALUES (UUID_v7(), DEFAULT);SELECT technician_uuid, issued_atFROM servicehub_sandbox.technician_token;

VECTOR(N) is available from MariaDB 11.7.1. N is the fixed dimension, with current documentation allowing dimensions up to 16383. Vector indexes/search are workload-specific features; Chapter 09 handles their access paths. On 12.3.2, a small local vector column can be used to learn representation, but production dimension/index choices must match the embedding model.

CREATE TABLE servicehub_sandbox.asset_embedding (  asset_id BIGINT PRIMARY KEY,  embedding VECTOR(4) NOT NULL) ENGINE=InnoDB;INSERT INTO servicehub_sandbox.asset_embeddingVALUES (1, VEC_FromText('[0.12,-0.34,0.56,0.78]'));SELECT asset_id, VEC_ToText(embedding)FROM servicehub_sandbox.asset_embedding;

Spatial types represent geometry/geography-style structures and have specialized functions/indexes; binary types such as BINARY, VARBINARY and BLOB store raw bytes without character collation. Use binary storage for cryptographic hashes, opaque protocol data or compressed payloads when the application truly treats them as bytes—not merely to avoid thinking about text encoding.

7. Build a typed ServiceHub table

CREATE TABLE servicehub_sandbox.assets (  asset_id UUID PRIMARY KEY,  asset_code VARCHAR(40) CHARACTER SET ascii COLLATE ascii_bin NOT NULL UNIQUE,  purchase_price DECIMAL(12,2) NULL,  commissioned_on DATE NULL,  last_seen_at TIMESTAMP(6) NULL,  attributes JSON NULL,  signature VARBINARY(64) NULL,  embedding VECTOR(4) NULL,  CONSTRAINT chk_purchase_price CHECK (purchase_price IS NULL OR purchase_price >= 0)) ENGINE=InnoDB;SHOW CREATE TABLE servicehub_sandbox.assets;SHOW FULL COLUMNS FROM servicehub_sandbox.assets;

Notice that every declaration carries domain intent. The code is ASCII and case-sensitive; money is exact; the commissioning date has no invented time zone; the last-seen value represents an instant; JSON is validated optional metadata; the signature is binary; the embedding dimension is explicit; and a CHECK rejects negative purchase prices. A schema review can debate each of those choices in business terms.

8. Hands-on lab and verification checklist

1. Create numeric_probe and confirm exact DECIMAL arithmetic.
2. Create a utf8mb4 text table and inspect the effective collation.
3. Run the TIMESTAMP-versus-DATETIME time-zone observation.
4. Create a JSON column, insert valid JSON, then prove malformed JSON is rejected.
5. Create a UUID row with UUID_v7() and verify the native type metadata.
6. Create a four-dimensional VECTOR column and round-trip one vector with VEC_FromText/VEC_ToText.
7. Create the final assets table and capture SHOW CREATE TABLE.

Verification checklist

• No currency field uses an approximate numeric type without explicit justification.
• Text columns have deliberate charset/collation assumptions.
• The temporal design distinguishes an instant from a local calendar value.
• JSON invalid input fails.
• UUID and VECTOR usage is labeled with minimum-version requirements.
• Binary data is not compared through a text collation.

9. Summary and bridge

Data types are executable domain decisions. MariaDB gives you exact and approximate numerics, text with explicit character/collation semantics, temporal types with different time-zone behavior, validated-text JSON, native UUIDs, vectors, spatial structures and raw binary types. The correct choice is driven by range, precision, comparison, index and application requirements—not by familiarity.

The next lesson makes those type choices enforceable. You will use PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, DEFAULT and generated columns to reject invalid states before they become application cleanup work.