I wonder if it is possible to change the data type of a column in Mysql. For example: I have a USUARIO table with the SITUACAO column, in varchar and need to change to bit . I thought about doing something like:
UPDATE usuario SET situacao=1 WHERE situacao='ativo';
UPDATE usuario SET situacao=0 WHERE situacao='inativo';
after
ALTER TABLE usuario CHANGE situacao situacao BIT;
Will this work? Does anyone know a better way to do this?
Yes, this will work.
In my opinion, there is no reason to look for another way to do this if it works perfectly.
Alternatively you could create another column to save the new value (% with%) and then clear the current column, change its type, pass the values to it and then delete the second column, but as you can see, this it's too hard to get exactly the same result. Depending on the case, this may be necessary, only you will know.
Here's an example.
ALTER TABLE USUARIO ADD SITUACAO_BACKUP BIT NOT NULL;
UPDATE USUARIO SET SITUACAO_BACKUP = 0 WHERE SITUACAO = 'inativo';
UPDATE USUARIO SET SITUACAO_BACKUP = 1 WHERE SITUACAO = 'ativo';
UPDATE USUARIO SET SITUACAO = NULL;
ALTER TABLE USUARIO CHANGE COLUMN SITUACAO SITUACAO BIT;
UPDATE USUARIO SET SITUACAO = SITUACAO_BACKUP;
ALTER TABLE USUARIO DROP COLUMN SITUACAO_BACKUP;
Yes, this statement will work and will keep the values converted, in case 1 for active and 0 for inactive.
Another option is to create a new column and then perform the updates one by one.
ALTER TABLE usuario MODIFY situacao BIT;
Create a support table and save a copy of the current data that can be only the primary key and the situation field. Because if any inconvenience occurs you have a backup of the current data.
Put the data with the same quotation marks in the code below, that was missing.
UPDATE usuario SET situacao='1' WHERE situacao='ativo';
Perform the upgrade process and if you receive any error proceed to the other type change code.
ALTER TABLE usuario CHANGE COLUMN situacao situacao BIT;
Never do anything without having a backup !!!