How to convert MySQL's BIGINT into unsigned without losing data?

Question

I need to convert MySQL database tables column from (signed) BIGINT into unsigned BIGINT. If I do:

ALTER TABLE `$tblNm` MODIFY `flgs` BIGINT UNSIGNED;

Any values that have 63-bit set are reset to 0!

What do I need to do so that all values are converted without any loss?

Answer 1

Similar to this

You can also try to add new column with unsigned BIGINT

         ALTER TABLE `$tblNm`
         ADD COLUMN new_column BIGINT UNSIGNED AFTER flgs;

Then update casted values in new column by using cast

        UPDATE `$tblNm` 
        SET new_column=CAST(flgs AS UNSIGNED);

Then drop column flgs

       ALTER TABLE `$tblNm`
       DROP COLUMN flgs;

Rename the new column with flgs

       ALTER TABLE `$tblNm`
       CHANGE COLUMN new_column flgs BIGINT UNSIGNED

EDIT In order to execute this in transactions we can update this as following.

         SET autocommit=0;
         START TRANSACTION;
         ALTER TABLE `$tblNm`
         ADD COLUMN new_column BIGINT UNSIGNED AFTER flgs;
         UPDATE `$tblNm` 
         SET new_column=CAST(flgs AS UNSIGNED);
         ALTER TABLE `$tblNm`
         DROP COLUMN flgs;
         ALTER TABLE `$tblNm`
         CHANGE COLUMN new_column flgs BIGINT UNSIGNED;
         COMMIT;

EDIT-2 If there is need to lock tables before starting transactions, the above script will be changed as

         SET autocommit=0;
         LOCK TABLES `$tblNm` WRITE
         ALTER TABLE `$tblNm`
         ADD COLUMN new_column BIGINT UNSIGNED AFTER flgs;
         UPDATE `$tblNm` 
         SET new_column=CAST(flgs AS UNSIGNED);
         ALTER TABLE `$tblNm`
         DROP COLUMN flgs;
         ALTER TABLE `$tblNm`
         CHANGE COLUMN new_column flgs BIGINT UNSIGNED;
         COMMIT;
         UNLOCK TABLES;

In this case, you dont need to explicitly write START TRANSACTION