Regular expressions are pretty easy to use, however, different DBMS’s handle them differently.

In PostgreSQL, regular expression searches are done via the “~” character. For instance:

SELECT * FROM table1 WHERE column1 ~ '^(.?)unq([a-z]{4})([1-9]+)$';

In MySQL, this is done via REGEXP:

SELECT * FROM table1 WHERE column1 REGEXP '^S([c-z])n(.*)$';

In Oracle, this is done via the REGEXP_LIKE function:

SELECT * FROM table1 WHERE REGEXP_LIKE(column1, '[[:digit:]]{2}$');

Monty recently announced the Maria engine for MySQL, promising MVCC, better memory management, crash-safe design, and ACID compliance among other things. But will it live up to the hype? I’ve been disappointed in the past, mainly with InnoDB, so I’m understandably a bit skeptical that this will be a major leap for MySQL. I would much rather see better datatype integration, and things like dynamically generated default values fixed (try setting a default value to CURRENT_DATE(), you’ll see what I mean).

My biggest beef with MySQL is they never seem to fix things that do not work as they should. For example, if you do BEGIN; CREATE TABLE; ROLLBACK;, the table will still exist after the ROLLBACK command, and also, constraints are often completely ignored.

One thing I get sick of is MySQL users bashing PostgreSQL for having the VACUUM command. They basically say the usual: VACUUM is slow, it’s flawed design, why should I have to VACUUM, etc. This, despite the fact that MySQL has a command that does almost the exact same thing, the difference of course being that PostgreSQL also collects stats from the database to optimize how the planner functions.

VACUUM, of course, is slow and uses a lot of CPU/Disk, but seriously, what’s stopping you from setting up a cron script that does `echo “VACUUM ANALYZE” | nice -n 19 psql database_name`, every night at midnight? I have a ~300MB database, and it takes all of three seconds to VACUUM ANALYZE. The only problem would be if you have a humongous database, or a slow server.