Personally, I would not use a database which is 'lax' by default instead of 'strict'. What other choices have they made which I need to learn OR it will bite me big in Production?
MySQL has a large legacy of being used as a very-immediate-although-somewhat-toy database at its roots.
For example, in absolute terms, I would find much more troubling the usage of non-transactional tables, justified by meaningless microbenchmarks, which has been somewhat common for some time.
Nowadays MySQL is definitely reliable, and it has a much more expert surrounding culture than the past, so if a person/company is willing to put time and knowledge, it's a reasonable choice.
I don't find automatic conversion so damaging that people should stay away just because it's default.
When you reach some level, you definitely need to have a relatively intimate knowledge of your tools, and at such point, one is far from the "defaults".
You can subtract two DATETIME columns in MySQL and it will not generate any warnings. It will give you an answer that probably is within the ballpark of the actual time difference but isn't the actual time difference.
In a previous life, the lax by default approach of MySQL bit us hard on a regular basis; a number of discrepancies vs. reasonable expectation were had. At the time I left, they were working on migrating toward Postgres, and reports suggest they are extremely happy with pg.
utf8 is not really utf8 and doesn't support 4 byte characters that exist outside the BMP (you'll need the separate utf8mb4 type for that, assuming you're not running on a legacy version that doesn't support that either).
- STRICT_TRANS_TABLES is on by default for "new installations" starting from MySQL 5.6 (2013). What this means is that the bundled config files all turn it on.
- Starting from MySQL 5.7, it is a compiled default, along with several other more "strict" options. Effectively making it default to ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO,
NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER.
When buying a car, would you prefer for it to be safe by default or only after you've enabled half a dozen features such as "air bags" and "seatbelts"?
> when defining a field as int(11) you can just happily insert textual data and MySQL will try to convert it.
this is dependent on the SQL Mode, which is quite flexible. for example, the STRICT_ALL_TABLES will prevent strings to be inserted in INT fields:
mysql> create table example ( `number` int(11) not null );
mysql> insert into example (number) values ('wat'); Query OK, 1 row affected, 1 warning (0,00 sec)
mysql> SET sql_mode = 'STRICT_ALL_TABLES';
mysql> insert into example (number) values ('wat'); ERROR 1366 (HY000): Incorrect integer value: 'wat' for column 'number' at row 1
There are certainly advantages in choosing PostgreSQL over MySQL... this is just not one of them :-)