Mysql field type change

Asked

Viewed 17,415 times

6

I wonder if it is possible to change the data type of a column in Mysql. For example: I have a table USUARIO with the column SITUACAO, in varchar and need to change to bit. I thought I’d do something like:

UPDATE usuario SET situacao=1 WHERE situacao='ativo';
UPDATE usuario SET situacao=0 WHERE situacao='inativo';

afterward

ALTER TABLE usuario CHANGE situacao situacao BIT;

Will it work? Someone knows a better way to do it ?

mysql sql

3 answers

7


Yeah, that’s gonna work.

In my opinion, there is no reason to seek another way of doing if this way works perfectly.

Alternatively you could create another column to save the new value (BIT) and then clean the current column, change her type, pass the values to her and then delete the second column, but as you can see, this is too much work to get exactly the same result. Depending on the case, this may be necessary, only you will know.

Here is 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;

Code on Github for future reference

  • 2

    Yeah, I can back up the column to the table itself. Thanks for the tip !!!

5

Yes, this instruction will work and will keep the converted values, in case 1 to active and 0 to inactive.

Another alternative is to create a new column and then perform the updates one by one.

ALTER TABLE usuario MODIFY situacao BIT;

  • What is the difference between MODIFY and CHANGE ?

  • With MODIFY vc cannot rename the column, only attributes as size type in that sense. With CHANGE you can change the type, size and name, see that appears twice the field name here CHANGE situacao situacao BIT or it is being renamed and type will also be changed. @Donnathegirl

  • More business use MODIFY then XD Thanks @rray !!!

  • @Donnathegirl, in this case yes, the change is only in the type and not in the name.

4

Create a backup table and save a copy of the current data which can be only the primary key and the situation field. Because, if any inconvenience happens you have a backup of the current data.

Put the data like this with equal quotes in the code below, missing this detail.

UPDATE usuario SET situacao='1' WHERE situacao='ativo';

Do the update process and if you receive any errors proceed with the other type change code.

ALTER TABLE usuario CHANGE COLUMN situacao situacao BIT;

Never do anything without having a backup !!!

  • 1

    Gee, I hadn’t thought of a backup... thank you!!!

  • It’s always good to have backup, versioning and other ways to get back to the previous state ... @Donnathegirl I hope I helped!

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.