PostgreSQL | Exporting TRUE and FALSE to MySQL
One of the many pitfalls of web-related work is that sometimes you just have to deal with MySQL, as it's the only database option some clients have. Particularly irksome is the penchant many web hosters have for older MySQL versions, which is a major PITA when trying to deal with even slightly complex data.
One of the many things lacking in MySQL is a true boolean datatype.
It does possess a pseudo-type
BOOL, which is silently
TINYINT(1), and from 4.1 onwards,
to quote the manual,
FALSE evaluate to 1 and 0, respectively".
That makes it relatively trivial to port data containing boolean values from PostgreSQL to MySQL, because the following works:
mysql> CREATE TABLE booltest (val BOOL); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO booltest VALUES(TRUE); Query OK, 1 row affected (0.00 sec)
Unless the target database server is MySQL 4.0.x; then it throws an error
FALSE. Rather than messing around
with converting instances of
in the PostgreSQL dumps (a potentially error-prone process), the following
serves as a quick'n'dirty workaround:
mysql> CREATE TABLE booltest (val BOOL, true INT DEFAULT 1, false INT DEFAULT 0); Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO booltest VALUES(TRUE); Query OK, 1 row affected (0.00 sec)