mysql char binary,MySQL在不丢失数据的情况下将CHAR（32）数据类型转换为BINARY（16）...

Hi I have a table which has a column with a char(32) datatype, I need to convert this to a BINARY(16) datatype. I have tried just altering the column type but that removes all the data in the column.

The following code is how I updated the datatype of the column. This resulted in me losing all the data in the column.

ALTER TABLE table_name MODIFY device_uuid BINARY(16)

Is there a way to change the datatype of the column and convert all the data to the new datatype without losing any data.

The reason I am doing it is because I am trying to retrieve some lost data which is located in this table. The table I need to import the data to is exactly the same but the column type is BINARY(16) not CHAR(32).

Thank you in advance if you are able to help with this.

解决方案

It sounds like you want to have a UUID represented as a string of hexadecimal digits. These normally have four dashes in them so the length is actually 36 characters. But if you remove the dashes, it can be 32 characters.

mysql> SELECT UUID();

+--------------------------------------+

| UUID() |

+--------------------------------------+

| b4d841ec-5220-11e9-901f-a921a9eb9f5b |

+--------------------------------------+

mysql> SELECT REPLACE(UUID(), '-', '');

+----------------------------------+

| REPLACE(UUID(), '-', '') |

+----------------------------------+

| d3dbd450522011e9901fa921a9eb9f5b |

+----------------------------------+

But in a hex string, each two characters represent data that could be encoded in one byte of binary data. For example, FF is the hex value for 255, which is the maximum value of one byte. Therefore hex strings take twice as many bytes as the equivalent data in binary. If space is constrained, you might want to convert your UUID values to binary so you can store them in half the space.

You can do this with the UNHEX() function.

mysql> SELECT UNHEX(REPLACE(UUID(), '-', ''));

+---------------------------------+

| UNHEX(REPLACE(UUID(), '-', '')) |

+---------------------------------+

| $S,vR!??!??[ |

+---------------------------------+

Binary data isn't pleasant to display or type in human-oriented interfaces, because some bytes correspond to unprintable characters.

But when you did ALTER TABLE table_name MODIFY device_uuid BINARY(16), you didn't decode the hex strings with UNHEX(). At best, this caused the first 16 bytes of ASCII hexadecimal characters to be mapped to the 16 bytes of your BINARY(16) column, and it truncated the string at that point. It's as if you did this to every row:

mysql> SELECT LEFT(REPLACE(UUID(), '-', ''), 16);

+------------------------------------+

| LEFT(REPLACE(UUID(), '-', ''), 16) |

+------------------------------------+

| 364e6db8522211e9 |

+------------------------------------+

The first 16 bytes are still hexadecimal digits. The bytes are ASCII values for those digits, not the binary equivalent of each pair of digits. The latter 16 bytes of every string were truncated, and not stored. If that data was important, I hope you have a backup of your database, because restoring that backup is now the only way you can recover that data.

What you should have done is the following:

ALTER TABLE table_name ADD COLUMN device_uuid_bin BINARY(16);

UPDATE table_name SET device_uuid_bin = UNHEX(device_uuid);

...check the data to make sure the conversion worked...

...test any applications work with the binary data...

ALTER TABLE table_name DROP COLUMN device_uuid;