Hacker News Reader

Regarding using JSON for arrays, MySQL and MariaDB both support validation using JSON Schema. For example, you can enforce that a JSON column only stores an array of numbers by calling JSON_SCHEMA_VALID in a CHECK constraint.

Granted, using validated JSON is more hoops than having an array type directly. But in a pinch it's totally doable.

MySQL also stores JSON values using a binary representation, it's not a comma-separated string.

Alternatively, in some cases it may also be fine to pack an array of multi-byte ints into a VARBINARY. Or for an array of floats, MySQL 9 now has a VECTOR type.

Regarding ipv6 addresses: MariaDB's inet6 type can also store ipv4 values as well, although it can be inefficient in terms of storage. (inet6 values take up a fixed 16 bytes, regardless of whether the value is an ipv4 or ipv6 address.)

As for using RETURNING inside a writable CTE in MariaDB: not sure, I'd assume probably not. I must admit I'm not familiar with the multi-table pipeline write pattern that you're describing.

View on HN

WITH new_order AS ( INSERT INTO order (po_number, bill_to, ship_to) VALUES ('ABCD1234', 42, 64) RETURNING order_id ) INSERT INTO order_item (order_id, product_id, quantity) SELECT new_order.order_id, vals.product_id, vals.quantity FROM (VALUES (10, 1), (11, 5), (12, 3)) AS vals(product_id, quantity) CROSS JOIN new_order ;