My experiences are with a pretty old mysql version (5.5, iirc), but that version allowed inserts without supplying a value to a column that is NOT NULL and has no default value.
If that's the strict mode, I shudder to think what the lax mode must be... :(
And upgrade to MariaDB 10.3 caught those cases, so at least there's some progress in the mysql/mariadb ecosystem.
> MySQL has a JSON data type with validation, binary storage, efficient updates and efficient replication.
Postgres has, in addition, indexes over expression, which allows you to build indexes for specific queries into JSONB.
(I'd argue that if you need indexes, it's typically better to extract that data into the regular table and not keep it in the json, but it can still save your butt if you need to react quickly to a requirement change and no time for a data migration).
> My experiences are with a pretty old mysql version (5.5, iirc), but that version allowed inserts without supplying a value to a column that is NOT NULL and has no default value.
With recent MySQL:
create table ttt (col int not null); insert into ttt () values();
ERROR: 1364: Field 'col' doesn't have a default value
> Postgres has, in addition, indexes over expression, which allows you to build indexes for specific queries into JSONB.
MySQL has that, too. I consider this quite useful, especially since MySQL also supports indexing an array of values from a JSON document. (Given `{"colors": ["red", "green", "blue" ]}` a search for `JSON_CONTAINS('$.colors', 'red')` benefits from the index.
Quite a while ago MySQL changed the default to be strict on types and not doing truncations anymore. Recently it also (finally) got check constraints.
> 2. JSONB support makes it easy to use a 'hybrid' schema
MySQL has a JSON data type with validation, binary storage, efficient updates and efficient replication.