Convert MySQL tinyint types to bit

Question

I used the MySQL Migration Toolkit to migrate a SQL Server 2008 database to MySQL. However, it converted all my bit types to tinyint. My data model doesn't like this, because I have a bunch of expressions testing for true/false, and not 0-255.

Is there a way to tell the Toolkit to convert these different, or some SQL I can run on the new database to convert?

Answer 1

In MySQL prior to 5.0.5 Bit didn't really exist so the Migration Toolkit probably hasn't updated yet but you can use a TINYINT(1) instead as per this quote from the mysql website:

"As of MySQL 5.0.3, a BIT data type is available for storing bit-field values. (Before 5.0.3, MySQL interprets BIT as TINYINT(1).) In MySQL 5.0.3, BIT is supported only for MyISAM. MySQL 5.0.5 extends BIT support to MEMORY, InnoDB, BDB, and NDBCLUSTER."

In order to convert you can use ALTER TABLE:

ALTER TABLE MODIFY TINYINT(1);

Or if you want to use the new BIT type you could sub that in there as well but since it is somewhat new I think that we'll continue to have some problems with the BIT type for a, well, bit.

There isn't really a great way to do this to all columns though, I usually use sed or perl -pi to make the modifications in my .sql file before I import.

Hope that helps!