4 ms·
Good to know about INTERSECT and EXCEPT. MySQL can use computed columns to index individual JSON fields or serialize the whole thing to a varchar for a broad s
by ttfkam 3y ago
Good to know about INTERSECT and EXCEPT.
MySQL can use computed columns to index individual JSON fields or serialize the whole thing to a varchar for a broad search, but that's not at all the same as indexing with hierarchical structure preserved, which is what was being described. You cannot index for arbitrary path searches like you can in Postgres.
The storage of booleans is a separate issue from syntactic support. In MySQL, true and 1 evaluate as equal. In Postgres there is a type mismatch without an explicit cast. Therefore MySQL does not truly support booleans.
INTERVAL is not the same as a range type. Postgres supports intervals as their own type as well, eg., '18 months'::interval.
Range types allow for useful exclusion constraints for scheduling and assigning finite resources. Since you mistook ranges for intervals, I HIGHLY recommend you take a closer look at ranges. They are VERY useful to have in your data modeling toolbox.
https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-CONSTRAINT https://www.postgresql.org/docs/current/rangetypes.html#RANG...
You can "work around" the lack of array types in MySQL with comma-delimited strings… kinda.
The list of things you can "work around" in with MySQL is long, but not everything can be "worked around" without reinventing the wheel. Eventually it's worth examining whether or not it's worth "working around" instead of using a solution that "just works".
MySQL has improved by leaps and bounds in the 8.x-era, and 5.5-5.7 were both huge improvements on the dumpster fire that came in the decades before. Thankfully the famous MySQL gotchas page isn't directly relevant anymore, but that's a very low bar. (Yes, I admit I hold grudges with regard to data integrity.)
- sgarland 3y agoThis is tangential to the discussion, but I've always felt that putting JSON into RDBMS is at best a kludge anyways - it's almost never in any normal form, and also if you wanted schema-less objects, then you should use a DB system meant for it. I can appreciate that this is a strict and academic approach that will likely collide with a business' needs, though, and your point about indexing on arbitrary path searches is valid. > In MySQL, true and 1 evaluate as equal... Therefore MySQL does not truly support booleans. Also a fair point. I mostly am in the Python world, and often forget that truthiness is not the same as strict equality. > Since you mistook ranges for intervals, I HIGHLY recommend you take a closer look at ranges. I'll spend some time looking into this, thank you for the recommendation. > The list of things you can "work around" in with MySQL is long, but not everything can be "worked around" without reinventing the wheel. Also true. Much like my first point on JSON in RDBMS, there are often times where business needs make it such that you're going to be forced to work around problems, so it's easy to become accustomed to finding such hacks. Inertia is a hell of a thing. > Yes, I admit I hold grudges with regard to data integrity. A very reasonable thing to hold a grudge about. I still regard Xeons with deep suspicion due to AVX-512 forcing heavy throttling on all cores back in the day. I run them, mind you, but forever in the back of my mind I wonder, "is someone else on this multi-tenant node slowing my workloads down?"
- ttfkam 3y agoWhile Ted Codd is no longer around, Chris Date is, and even he has admitted the original "academic" notion of what constitutes a proper relational domain in SQL was incomplete/flawed. I can't recommend this presentation enough with regard to modern relational design: https://youtu.be/swR33jIhW8Q https://youtu.be/swR33jIhW8Q