I've come to MySQL from several years of using PostgreSQL, used MySQL for two years of database instruction, and now I've gone back to PostgreSQL.

Here are a few problems I've encountered with MySQL. I've divided the problems into "Hazards" and "Nuisances." The Hazards can get you in trouble by providing a false sense of security. The Nuisances are annoying, but can be worked around.

These observations are from using MySQL 5.1 and 5.5.

Hazards

• MySQL ignores check constraints in create table and similar statements. That is, you can specify a check constraint, and there will be no error message, and no warning; but MySQL will not enforce the constraint. This is true for all storage engines, including InnoDB.

• Unless you are using the InnoDB storage engine, it will similarly ignore foreign key constraints. Even if you are, it may ignore the constraint without any warning if you are a bit careless with your syntax. (Note: InnoDB is the default engine in MySQL 5.5.)

• Unless you're using the InnoDB storage engine, it does not enforce the ACID properties of transactions. (Note: InnoDB is the default storage engine in MySQL 5.5.)

• In my experience, it has been difficult to be sure that a database is using the InnoDB storage engine. That is, I have sometimes created a database and felt sure that it was using InnoDB, only to find out later that it was not (because it ignored foreign key constraints or did not enforce the ACID properties).

Nuisances

• There is no create role or create group statement. You can create individual users, but you cannot grant privileges to a group of users or revoke them from a group; you have to do it on an individual basis.

• The select statement does not support the set operators intersect and except. Easily worked around using nested queries:

  -- A intersect B, where k is the primary key of A and B
  select * from A where k in (select k from B);
  
  -- A except B, where k is the primary key of A and B
  select * from A where k not in (select k from B);

• There is no create domain or create type statement. (Even if there were, it probably would not support check constraints; see "Hazards" section.)

• String comparisons are case-insensitive, unless you specify "binary". For example, 'abc' = 'ABC' is true (1), but binary 'abc' = 'ABC' is false (0).

• Join syntax does not support the full outer join. To work around this, take the union of the left and right outer joins.

Gregory Weber