| Please humor me if I sound a bit triggered in this response, as MySQL pre-v8.0 is like if the creators went to data analysts and asked: "what features could we remove, and what changes could we make to an SQL-standard-compliant DB (postgres), in order to slow you down as much as possible, hinder your ability to read and write queries, and generally make your life a living hell?" I really get that MySQL is good for what it does, from an engineer's point of view. It is an absolute piss-poor excuse for a database, prior to v8.0. So what's wrong with MySQL (again, prior to v8.0, but no one seems to use the damn current version) -Not ANSI SQL compliant (unlike Postgres) -No CTEs/WITH clause (?!) -no WINDOW FUNCTIONS (?!?!?!?) -"schemas are called databases" which makes for bizarre interpretation of `information_schema` queries, which behave the same across all other DBs except mySQL. What I mean to say is MySQL calls each schema it's own database. This results in having to connect the same DB multiple times to other programs/APIs/inputs which accept JDBC. -Worse replication options than postgres, not default ACID compliant, -Don't know the programming term for this... but the horrendous "select col1, col2, col3... colN, count(<field>)
from table group by 1" implicit group by. Meaning the system takes your INVALID query, and does things underneath the hood to return a result. Systems should enforce correct syntax (you must group by all non-aggregation columns... mysql implicitly does this under the hood). -on a tangentially related note to the prior one, MySQL returns null instead of a divide by zero error when you divide by zero. Divide by zero errors are one of the few things that should ALWAYS RETURN AN ERROR NO MATTER WHAT
-mysql doesn't support EXCEPT clauses -doesn't support FULL OUTER JOIN -doesn't support generate_series, -poor JSON support -very limited, poor array/unnest support -insert VALUES () (in postgres) not supported -lack of consistent pipe operator concatenation, -weird datatype suppport and in-query doesn't support ::cast -doesn't support `select t1._* , t2.field1, t2.field2 from t1 join t2 on t1.id = t2.id` ; that is, you cannot select * from one table, and only certain fields from the other. -case dependence in field and table names when not escape quoted (mysql uses backtick, postgres uses double quote for escaping names). What the fuck is this? SQL is a case-insensitive language, then the creators build-in case sensitivity? -As I mentioned above, mysql uses backticks to escape names. This is abnormal for SQL databases. -mysql LIKE is case-insensitive (what the hell, it's case-sensitive everywhere else). Postgres has LIKE, and ILIKE (insensitive-like). -ugly and strange support for INTERVAL syntax (intervals, despite being strings, give a syntax error in mysql.
Example: In postgres or redshift etc you would right `select current_timestamp - interval '1 week'. In MySQL, you'd have to do `select current_timestamp - interval 1 week` (the '1 week' could be '7 month' or '2 day'... it's a string, and should be in single quotes. MySQL doesn't do this) -mysql doesn't even support the normal SQL comment of `--`. It uses a `#` instead. No other database does that. -probably the worst EXPLAIN/EXPLAIN ANALYZE plans I've ever seen from any database, ever -this is encapsulated in the prior points but you can't do something simple like `select <fields>, row_number() as rownum from table`. Instead you have to declare variables and increment them in the query -did I mention it's just straight up not SQL standard compliant? At least MySQL 8.0 supports window functions and CTEs (seriously it's a death knell to a data analyst not to have these). They are the absolute #1 biggest piece of missing functionality to an analyst in my opinion. This entire post focused on "mySQL have-nots", rather than "Postgres-haves" so I do think there are actually _even more_ advantages to using Postgres over MySQL. I understand MySQL is very fast for writes, but to my understanding it's not even like Postgres is slow for writes, and on the querying side of the coin, it's a universe of difference. If you ever use MySQL in the future and there will be a data analyst existing somewhere downstream of you, I implore you to use MySQL v8.0 and nothing older, at any cost, for their sake. |
Division by zero errors and non-"magical" GROUP BY have been the default mode of operation for a _little_ longer, since the 5.7 series.