切换数据库mysql中的所有二进制值

Question

I've been tasked with doing a '2.0' rewrite for a website with a relatively large mysql database ( not THAT large but too big to go through manually ).

I'm currently going through the process of re-organizing and normalizing the db, which was conceived on the fly without much planning.

One of the more glaring problems is that for some reason, all binary values in the database are switched, to where 0 is true and 1 is false across all 50 tables. I feel like this is a recipe for future bugs and confusion in the data and the code.

Is there any way to change this back to normal across the DB with sql / phpmyadmin?

Answer 1

If the number of columns that has this problem is limited, and all those columns are numeric or textual and only contain values 0 or 1, you can do this PER column that has the problem:

update myTable set myColumn = '2' where myColumn = '1';
update myTable set myColumn = '1' where myColumn = '0';
update myTable set myColumn = '0' where myColumn = '2';

If the columns are enum, you will need an alternative approach. (A nice work-around would be to null or empty string instead of 2, but that can only be done if the column then is not-null.)

edit

It can be done more simple, and it doesn't matter what the type of the columns is.

If you don't expect nulls:

update myTable set myColumn = if(myColumn='1','0','1');

If nulls should stay nulls:

update myTable set myColumn = if(myColumn is null, null, if(myColumn='1','0','1'));

If nulls should become 1:

update myTable set myColumn = if(myColumn is null, '1', if(myColumn='1','0','1'));