JSON, Spatial, VECTOR

A JSON column such as products.attributes rejects invalid documents on write and stores valid ones in a binary format for fast access by path. Spatial types (POINT, LINESTRING, POLYGON, GEOMETRY and collections) store coordinates, ideally with an SRID: loc POINT NOT NULL SRID 4326. JSON, Full-Text, Spatial covers both in depth. VECTOR(N), added in MySQL 9.0.0 524 (July 2024), holds up to N 4-byte floats for machine-learning embeddings; N defaults to 2048 and may reach 16383.

CREATE TABLE emb (product_id INT UNSIGNED PRIMARY KEY, v VECTOR(4));
INSERT INTO emb VALUES (1, STRING_TO_VECTOR('[0.12, 0.98, -0.34, 0.05]')),
                       (2, STRING_TO_VECTOR('[1, 2, 3]'));
SELECT product_id, VECTOR_TO_STRING(v) AS v, VECTOR_DIM(v) AS dims, LENGTH(v) AS bytes
  FROM emb;
SELECT DISTANCE(v, STRING_TO_VECTOR('[0.1, 0.9, -0.3, 0.0]'), 'COSINE') FROM emb;

+------------+----------------------------------------------------+------+-------+
| product_id | v                                                  | dims | bytes |
+------------+----------------------------------------------------+------+-------+
|          1 | [1.20000e-01,9.80000e-01,-3.40000e-01,5.00000e-02] |    4 |    16 |
|          2 | [1.00000e+00,2.00000e+00,3.00000e+00]              |    3 |    12 |
+------------+----------------------------------------------------+------+-------+
ERROR 1305 (42000): FUNCTION shop.DISTANCE does not exist

VECTOR(4) is a maximum, so three elements fit; VECTOR(16384) fails with error 6137. DISTANCE() exists only in MySQL HeatWave 207 on OCI and MySQL AI, so a LAMP stack ranks embeddings in PHP. A VECTOR cannot be a key and compares only for equality.