Блог

Привет, у меня есть таблица, в которой есть столбец с char(32) типом данных, мне нужно преобразовать это в BINARY(16) тип данных. Я пытался просто изменить тип столбца, но это удаляет все данные в столбце.

Следующий код показывает, как я обновил тип данных столбца. Это привело к тому, что я потерял все данные в столбце.

 ALTER TABLE table_name MODIFY device_uuid BINARY(16)
  

Есть ли способ изменить тип данных столбца и преобразовать все данные в новый тип данных без потери каких-либо данных.

Причина, по которой я это делаю, заключается в том, что я пытаюсь восстановить некоторые потерянные данные, которые находятся в этой таблице. Таблица, в которую мне нужно импортировать данные, точно такая же, но тип столбца BINARY(16) не CHAR(32) такой.

Заранее благодарю вас, если вы сможете помочь с этим.

Похоже, вы хотите, чтобы UUID был представлен в виде строки шестнадцатеричных цифр. Обычно в них четыре тире, поэтому длина фактически составляет 36 символов. Но если вы удалите тире, это может быть 32 символа.

 mysql> SELECT UUID();
 -------------------------------------- 
| UUID()                               |
 -------------------------------------- 
| b4d841ec-5220-11e9-901f-a921a9eb9f5b |
 -------------------------------------- 

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

Но в шестнадцатеричной строке каждые два символа представляют данные, которые могут быть закодированы в одном байте двоичных данных. Например, FF — это шестнадцатеричное значение для 255, которое является максимальным значением в один байт. Поэтому шестнадцатеричные строки занимают в два раза больше байт, чем эквивалентные данные в двоичном формате. Если пространство ограничено, вы можете преобразовать свои значения UUID в двоичный, чтобы вы могли хранить их в два раза меньше места.

Вы можете сделать это с помощью функции UNHEX().

 mysql> SELECT UNHEX(REPLACE(UUID(), '-', ''));
 --------------------------------- 
| UNHEX(REPLACE(UUID(), '-', '')) |
 --------------------------------- 
| $S,vR!??!??[                      |
 --------------------------------- 
  

Двоичные данные неприятно отображать или вводить в интерфейсах, ориентированных на пользователя, потому что некоторые байты соответствуют непечатаемым символам.

Но когда вы это делали ALTER TABLE table_name MODIFY device_uuid BINARY(16) , вы не декодировали шестнадцатеричные строки с помощью UNHEX() . В лучшем случае это приводило к сопоставлению первых 16 байт шестнадцатеричных символов ASCII с 16 байтами вашего ДВОИЧНОГО (16) столбца, и в этот момент строка усекалась. Это как если бы вы делали это с каждой строкой:

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

Первые 16 байт по-прежнему являются шестнадцатеричными цифрами. Байты представляют собой значения ASCII для этих цифр, а не двоичный эквивалент каждой пары цифр. Последние 16 байт каждой строки были усечены и не сохранены. Если эти данные были важны, я надеюсь, у вас есть резервная копия вашей базы данных, потому что восстановление этой резервной копии — теперь единственный способ восстановить эти данные.

Что вам следовало сделать, так это следующее:

 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;
  

Если вы воспользуетесь алгоритмом, предоставленным Karwin, и немного перетасуете биты, вы получите упорядоченный по времени UUID. Это часто намного улучшает производительность из-за того, что является полуупорядоченным, а не «случайным».

Я обсуждаю это в своем блоге здесь. Это указывает на то, что эквивалентный код был предоставлен в стандартных функциях в MySQL 8.0.

Также обратите внимание, что этот «трюк» применим только к идентификаторам uuid «Типа 1», которые использует MySQL / MariaDB.