MySQL在不丢失数据的情况下将CHAR(32)数据类型转换为BINARY(16) [英] MySQL convert CHAR(32) datatype to BINARY(16) without losing data

问题描述

我有一个表，其中的列具有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.

之所以这样做，是因为我试图检索位于此表中的一些丢失的数据.我需要将数据导入到的表是完全相同的，但是列类型是BINARY(16)而不是CHAR(32).

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.

推荐答案

听起来好像您想将UUID表示为十六进制数字的字符串.这些通常在其中有四个破折号，因此长度实际上是36个字符.但是，如果删除破折号，则可以是32个字符.

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 |
+----------------------------------+

但是在十六进制字符串中，每两个字符表示可以编码为二进制数据的一个字节的数据.例如，FF是255的十六进制值，这是一个字节的最大值.因此，十六进制字符串占用的字节数是二进制等效数据的两倍.如果空间有限，则可能需要将UUID值转换为二进制值，以便将它们存储在一半的空间中.

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.

您可以通过 UNHEX()来执行此操作功能.

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.

但是，当您执行ALTER TABLE table_name MODIFY device_uuid BINARY(16)时，没有使用UNHEX()解码十六进制字符串.充其量，这会使ASCII十六进制字符的前16个字节映射到BINARY(16)列的16个字节，并且在那一刻截断了字符串.就像您对每一行都这样:

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                   |
+------------------------------------+

前16个字节仍然是十六进制数字.字节是这些数字的ASCII值，而不是每对数字的二进制等效值.每个字符串的后16个字节被截断，并且不存储.如果这些数据很重要，我希望您有数据库的备份，因为现在还原该备份是您可以恢复该数据的唯一方法.

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;

这篇关于MySQL在不丢失数据的情况下将CHAR(32)数据类型转换为BINARY(16)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！