4 ms·

> JSON can often be used in place of arrays This is like storing UUIDs as text. You lose type information and validation. It's like storing your array as a com

by ttfkam 2y ago

> JSON can often be used in place of arrays This is like storing UUIDs as text. You lose type information and validation. It's like storing your array as a comma-delimited string. It can work in a pinch, but it takes up more storage space and is far more error prone. > convenience types for ipv4, ipv6, and uuid. That's nice to see. A shame you have to decide ahead of time whether you're storing v6 or v4, and I don't see support for network ranges, but a definite improvement. > MariaDB supports RETURNING. That's honestly wonderful to see. Can these be used inside of CTEs as well for correlated INSERTs?

evanelias 2y ago
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.
- ttfkam 2y ago
  > the multi-table pipeline write pattern 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 ; Not super pretty, but it illustrates the point. A single statement that creates an order, gets its autogenerated id (bigint, uuid, whatever), and applies that id to the order items that follow. No network round trip necessary to get the order id before you add the items, which translates into a shorter duration for the transaction to remain open.
  - evanelias 2y ago
    Thanks, that makes sense. In this specific situation, the most common MySQL/MariaDB pattern would be to use LAST_INSERT_ID() in the second INSERT, assuming the order IDs are auto-increments. Or with UUIDs, simply generating the ID prior to the first INSERT, either on the application side or in a database-side session variable. To avoid extra network calls, this could be wrapped in a stored proc, although a fair complaint is that MySQL doesn't support a ton of different programming langauges for procs/funcs like Postgres.

by ttfkam 2y ago

evanelias 2y ago

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.

ttfkam 2y ago
> the multi-table pipeline write pattern 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 ; Not super pretty, but it illustrates the point. A single statement that creates an order, gets its autogenerated id (bigint, uuid, whatever), and applies that id to the order items that follow. No network round trip necessary to get the order id before you add the items, which translates into a shorter duration for the transaction to remain open.
- evanelias 2y ago
  Thanks, that makes sense. In this specific situation, the most common MySQL/MariaDB pattern would be to use LAST_INSERT_ID() in the second INSERT, assuming the order IDs are auto-increments. Or with UUIDs, simply generating the ID prior to the first INSERT, either on the application side or in a database-side session variable. To avoid extra network calls, this could be wrapped in a stored proc, although a fair complaint is that MySQL doesn't support a ton of different programming langauges for procs/funcs like Postgres.