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?

hey, someone didn't like the question :) – c00000fd Feb 23 '20 at 09:21 — c00000fd, Feb 23 '20 at 09:21

Hallah · Accepted Answer · 2020-02-23T09:57:14.170

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

edited Feb 23 '20 at 09:57

answered Feb 23 '20 at 08:44

Hallah

111
4

Your `INSERT INTO` statement has an error (it probably must be an `UPDATE`), but otherwise I like the idea. This seams like a big overkill, but there doesn't seem to be any other way without losing data. – c00000fd Feb 23 '20 at 08:54
And the cast must be `CAST(flgs AS UNSIGNED)` and not what you have in there. Then there must be `DROP COLUMN` followed by `CHANGE COLUMN`. So why don't you adjust all that and I'll mark it as an answer -- unless someone else has a better idea? – c00000fd Feb 23 '20 at 09:07
I didn't specify the version of MySQL, so maybe your syntax works on newer versions. I'm on `5.7.25` and there the cast must be `CAST(flgs AS UNSIGNED)` and renaming `ALTER TABLE tbl CHANGE new_column flgs BIGINT UNSIGNED`. – c00000fd Feb 23 '20 at 09:20
And this probably needs a transaction (or whatever they call it in MySQL.) `SET autocommit=0;` and then wrap it in `BEGIN` and `COMMIT` – c00000fd Feb 23 '20 at 09:23
IDK, do we need to lock the table or is it automatically locked after a `BEGIN`? This stuff -- `SELECT * FROM tbl LIMIT 0,18446744073709551615 FOR UPDATE;` – c00000fd Feb 23 '20 at 09:41
If there is need, then you can LOCK [table](https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html) – Hallah Feb 23 '20 at 09:45
Very good. Now it looks like it may work. Thanks. So much effort though just to do a simple `MODIFY`. – c00000fd Feb 23 '20 at 10:08
`ALTER TABLE` will commit any open transaction. See: [Statements That Cause an Implicit Commit](https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html) – Paul Spiegel Feb 23 '20 at 12:38

score 0 · Answer 2 · answered Feb 23 '20 at 13:22

As I understand - You have a bitmask stored as BIGINT (signed) and the column contains negative numbers (leftmost bit set to 1). You want to convert the column type to BIGINT UNSIGNED preserving the binary value.

I would suggest to convert the type to BINARY(8) and then to BIGINT UNSIGNED. Unfortunately the second step didn't work. But converting over BIT(64) works in my test. So you can try:

ALTER TABLE tbl MODIFY `flgs` BIT(64);
ALTER TABLE tbl MODIFY `flgs` BIGINT UNSIGNED;

Test case:

create table tbl (flgs bigint);
insert into tbl (flgs) values (1), (-1);

ALTER TABLE tbl MODIFY `flgs` BIT(64);
ALTER TABLE tbl MODIFY `flgs` BIGINT UNSIGNED;

select * from tbl;

Returns:

| flgs                 |
| -------------------- |
| 1                    |
| 18446744073709551615 |

View on DB Fiddle

yeah, thanks. That might be a better solution. At this point though I have already converted all my data using the method that I marked. So if someone else runs into the same issue, they may try your method. — c00000fd, Feb 23 '20 at 21:34

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

2 Answers2