28 ms·

Does MySQL support CHECK constraints yet? Last time I used it it silently ignored them if they were in the DDL.

by Slansitartop 9y ago

moolcool 9y ago
Good heavens, then why even have them?
- Slansitartop 9y ago
  I figured it was so MySQL wouldn't barf so badly on DDL written for other databases. That's the only justification I can think of, and it's a pretty terrible one. I have a pretty low opinion of it because of this.
  - Something1234 9y ago
    It already barfs on a fair amount of valid SQL. Autoincrement vs auto_increment anybody?
    - jimktrains2 9y ago
      Actually there isn't* a standard for auto increment columns. * There is one for an identity column, which often ends up being similar `GENERATED BY DEFAULT AS IDENTITY` https://blog.2ndquadrant.com/postgresql-10-identity-columns/ https://blog.2ndquadrant.com/postgresql-10-identity-columns/ https://dba.stackexchange.com/a/161356 https://dba.stackexchange.com/a/161356 and I don't believe mysql supports that syntax https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html https://dev.mysql.com/doc/refman/5.7/en/create-table-generat...
- jimktrains2 9y ago
  Everytime I deal with mysql I ask why there is mysql. It's terrible when compared to postgres at consistency, correctness, functionality (e.g. check constraints, cte, and window functions, pg_tgrm, full text search, index flexibility and that's without thinking about PostGIS, pg-routing and many of the other excellent 3rd party extensions) and error handling and nearly equivalent when it comes for basic queries that mysql even supports. Edit: forgot transactional ddls, real ones, not the ones mysql has in the pipeline.
  - mulmen 9y ago
    The industry seems to finally be warming up to Postgres and I find that really exciting. I think MySQL popularity had a lot to do with it's willingness to say "yes". It's really easy to ship something on a database that never warns you or refuses to do the wrong thing. Postgres is "harder" to use because when you make a dumb mistake it tells you about it. This post is an oversimplification but summarizes my general impression.
  - captain_perl 9y ago
    Half of those items are already in MySQL 5.7 or 5.8. What's terrible to you is a plus for Web 2.0 data storage - not everybody wants B&D just to insert a row or to select a timestamp. I happen to use both databases, but I recommend MySQL first for startups.
    - jimktrains2 9y ago
      Your recommendation is terrible. Mysql has data consistency issues. It's simply terrible to work with. It doesn't have real data types or complex indecies. The window functions they're adding aren't all that comprehensive. Window functions and ctes are in a pre release version, compared to what? Nearly a decade of production use? There are so many fewer gotchyas with mysql it's not even funny. Just stupid things like "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'". The query planner is crap and I've actually had it hang the instance. The explain output is barely usable compared to postgres'. Postgres supports a rich set of languages to write functions in. Postgres supports rich set of column, row, and table access control. Postgres supports returning syntax on updates. As far as I know check constraints are still _silently_ ignored in mysql 5.8. silently! That illustrates exactly why I don't trust mysql and probably never will. What is one advantage that mysql has that outweighs its enormous pile of garbage it brings along? What is one advantage worth not trusting your tool to work?
      - tomnipotent 9y ago
        Many of the things you've listed are not important for a wide class of projects that MySQL is used for. Facebook, Google, and Yahoo must be fools for using MySQL - clearly don't know what they're doing /s. > What is one advantage that mysql has that outweighs Replication & performance for most workloads. I've found it helpful to think of MySQL as less of a relational database, and more of a KV database with secondary index support and a SQL api.
DasIch 9y ago
No.

Does MySQL support CHECK constraints yet? Last time I used it it silently ignored them if they were in the DDL.

moolcool 9y ago

Good heavens, then why even have them?

Slansitartop 9y ago
I figured it was so MySQL wouldn't barf so badly on DDL written for other databases. That's the only justification I can think of, and it's a pretty terrible one. I have a pretty low opinion of it because of this.
- Something1234 9y ago
  It already barfs on a fair amount of valid SQL. Autoincrement vs auto_increment anybody?
  - jimktrains2 9y ago
    Actually there isn't* a standard for auto increment columns. * There is one for an identity column, which often ends up being similar `GENERATED BY DEFAULT AS IDENTITY` https://blog.2ndquadrant.com/postgresql-10-identity-columns/ https://blog.2ndquadrant.com/postgresql-10-identity-columns/ https://dba.stackexchange.com/a/161356 https://dba.stackexchange.com/a/161356 and I don't believe mysql supports that syntax https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html https://dev.mysql.com/doc/refman/5.7/en/create-table-generat...
jimktrains2 9y ago
Everytime I deal with mysql I ask why there is mysql. It's terrible when compared to postgres at consistency, correctness, functionality (e.g. check constraints, cte, and window functions, pg_tgrm, full text search, index flexibility and that's without thinking about PostGIS, pg-routing and many of the other excellent 3rd party extensions) and error handling and nearly equivalent when it comes for basic queries that mysql even supports. Edit: forgot transactional ddls, real ones, not the ones mysql has in the pipeline.
- mulmen 9y ago
  The industry seems to finally be warming up to Postgres and I find that really exciting. I think MySQL popularity had a lot to do with it's willingness to say "yes". It's really easy to ship something on a database that never warns you or refuses to do the wrong thing. Postgres is "harder" to use because when you make a dumb mistake it tells you about it. This post is an oversimplification but summarizes my general impression.
- captain_perl 9y ago
  Half of those items are already in MySQL 5.7 or 5.8. What's terrible to you is a plus for Web 2.0 data storage - not everybody wants B&D just to insert a row or to select a timestamp. I happen to use both databases, but I recommend MySQL first for startups.
  - jimktrains2 9y ago
    Your recommendation is terrible. Mysql has data consistency issues. It's simply terrible to work with. It doesn't have real data types or complex indecies. The window functions they're adding aren't all that comprehensive. Window functions and ctes are in a pre release version, compared to what? Nearly a decade of production use? There are so many fewer gotchyas with mysql it's not even funny. Just stupid things like "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'". The query planner is crap and I've actually had it hang the instance. The explain output is barely usable compared to postgres'. Postgres supports a rich set of languages to write functions in. Postgres supports rich set of column, row, and table access control. Postgres supports returning syntax on updates. As far as I know check constraints are still _silently_ ignored in mysql 5.8. silently! That illustrates exactly why I don't trust mysql and probably never will. What is one advantage that mysql has that outweighs its enormous pile of garbage it brings along? What is one advantage worth not trusting your tool to work?
    - tomnipotent 9y ago
      Many of the things you've listed are not important for a wide class of projects that MySQL is used for. Facebook, Google, and Yahoo must be fools for using MySQL - clearly don't know what they're doing /s. > What is one advantage that mysql has that outweighs Replication & performance for most workloads. I've found it helpful to think of MySQL as less of a relational database, and more of a KV database with secondary index support and a SQL api.

DasIch 9y ago

No.