3 ms·

That's a good list [1]. A handful of these are already doable in modern MySQL and/or MariaDB though. JSON can often be used in place of arrays, and JSON_TABLE

That's a good list [1]. A handful of these are already doable in modern MySQL and/or MariaDB though. JSON can often be used in place of arrays, and JSON_TABLE in both MySQL and MariaDB converts JSON into tabular data. MySQL supports multi-valued indexes over JSON, where each row can have multiple index entries (or no entries, e.g. partial index). MariaDB has built-in convenience types for ipv4, ipv6, and uuid. Or in MySQL you can just use virtual columns to add human-readable conversions of binary columns, although that is admittedly slightly annoying. MariaDB supports RETURNING. [1] Edit to add: I do mean that honestly, it's an accurate and insightful list of nice Postgres features, most of which aren't in MySQL or MariaDB. Honestly baffled as to why I'm being downvoted.

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.
ComputerGuru 2y ago
You still can’t use uuid as proper foreign keys with validation on mariaDB/MySQL though, right? It wasn’t possible with blobs at any rate.
- evanelias 2y ago
  This has always been possible, for example using the BINARY(16) column type if you want to be efficient. Or in MariaDB 10.7+ you can now use the dedicated UUID column type, which is equivalent to BINARY(16) under the hood, but provides a human-readable hex value when queried. UUIDs are fixed-length. Blobs are not the appropriate type for that.
  - ComputerGuru 2y ago
    Sorry, I was mistaken and I already do have that set up and working using binary. There’s something else I’m trying but failing to remember that isn’t possible with binary keys.

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.

ComputerGuru 2y ago

You still can’t use uuid as proper foreign keys with validation on mariaDB/MySQL though, right? It wasn’t possible with blobs at any rate.

evanelias 2y ago
This has always been possible, for example using the BINARY(16) column type if you want to be efficient. Or in MariaDB 10.7+ you can now use the dedicated UUID column type, which is equivalent to BINARY(16) under the hood, but provides a human-readable hex value when queried. UUIDs are fixed-length. Blobs are not the appropriate type for that.
- ComputerGuru 2y ago
  Sorry, I was mistaken and I already do have that set up and working using binary. There’s something else I’m trying but failing to remember that isn’t possible with binary keys.