Как мне хранить GUID в таблицах MySQL?
Ответы 10
Я бы сохранил его как char (36).
Я не понимаю, зачем вам хранить -.
@AfshinMehrabani Это просто, понятно, читабельно. Конечно, в этом нет необходимости, но если сохранение этих дополнительных байтов не повредит, то это лучшее решение.
Хранение тире может быть не очень хорошей идеей, потому что это приведет к дополнительным накладным расходам. Если вы хотите сделать его удобочитаемым, сделайте приложение читаемым с помощью дефисов.
@AfshinMehrabani еще одно соображение - это анализировать его из базы данных. Большинство реализаций ожидают тире в действительном guid.
Вы можете вставить дефис при выборке, чтобы легко преобразовать char (32) в char (36). используйте Insert FN mySql.
Мой администратор баз данных спросил меня, как лучше всего хранить идентификаторы GUID для моих объектов, почему мне нужно хранить 16 байтов, когда я могу сделать то же самое в 4 байтах с целым числом. Поскольку он бросил мне вызов, я подумал, что сейчас хорошее время, чтобы упомянуть об этом. Что, как говорится...
Вы можете сохранить guid как двоичный файл CHAR (16), если хотите наиболее оптимально использовать пространство для хранения.
Потому что с 16 байтами вы можете создавать что-то в разных базах данных, на разных машинах, в разное время и при этом легко объединять данные вместе :)
нужен ответ, что на самом деле представляет собой двоичный код char 16? не char? не бинарный? Я не вижу этого типа ни в одном из инструментов mysql gui, ни в какой-либо документации на сайте mysql. @BillyONeal
@nawfal: Char - это тип данных. BINARY - это спецификатор типа по отношению к типу. Единственный эффект, который он имеет, - это изменить способ сопоставления MySQL. Подробнее см. dev.mysql.com/doc/refman/5.0/en/charset-binary-op.html. Конечно, вы можете просто использовать тип BINARY напрямую, если ваш инструмент редактирования базы данных позволяет вам это делать. (Старые инструменты не знают двоичного типа данных, но знают флаг двоичного столбца)
@BillyONeal, поэтому я попробовал create table p (id char(16)). Итак, все, что он сделал, - это создал столбец char (16) с сопоставлением utf8_bin. Я не знаю, как вставить в него шестнадцатеричную строку из 36 символов (GUID). Когда я пытаюсь, я получаю ошибку с недопустимыми символами.
@BillyONeal, кроме того, думаете ли вы, что двоичный файл char 16 может превзойти двоичный 16 по производительности? Я сомневаюсь
@newfal: в вашем приложении вы превращаете эту шестнадцатеричную строку из 36 символов в 16 байтов, которые она фактически представляет, и вместо этого сохраняете их. (Вы не помещаете шестнадцатеричное представление в базу данных, если идете по маршруту char (16)). Что касается двоичного кода char (16) по сравнению с двоичным (16), я не вижу причин, по которым между ними будет какая-либо разница в производительности; единственная разница будет заключаться в сортировке, и вам все равно не следует сортировать по GUID для областей, чувствительных к производительности.
поля CHAR и BINARY по сути одно и то же. Если вы хотите перейти на самый простой из уровней, CHAR - это двоичное поле, ожидающее значения от 0 до 255 с целью представления указанного значения со значением, отображаемым из таблицы поиска (в большинстве случаев теперь UTF8). Поле BINARY ожидает значения того же типа без какого-либо намерения представлять указанные данные из таблицы поиска. Я использовал CHAR (16) еще во времена 4.x, потому что тогда MySQL был не так хорош, как сейчас.
@BillyONeal Я пробовал с двумя запросами: CHAR(16) BINARY и CHAR(16) CHARACTER SET binary. Код создания, который я получил после их выполнения, был соответственно: CHAR(16) NULL DEFAULT NULL COLLATE 'utf8_bin' и BINARY(16) NULL DEFAULT NULL. Короче говоря, второй запрос не создал ничего, кроме двоичного поля. И только в таком поле я мог хранить гид. Чистое поле CHAR(16) utf8_bin никогда не помогало мне хранить гиды. А может я не умею вставлять в такое поле!
@newfal: Я не понимаю, какая разница. Оба поля представляют собой 16-байтовые поля фиксированной ширины, без ограничений на данные, содержащиеся внутри.
Есть несколько веских причин, по которым GUID лучше автоинкремента. Джефф Этвуд перечисляет этот. Для меня лучшим преимуществом использования GUID является то, что моему приложению не потребуется обход базы данных, чтобы узнать ключ объекта: я мог бы заполнить его программно, чего я не смог бы сделать, если бы использовал поле с автоинкрементом. Это избавило меня от нескольких головных болей: с GUID я могу управлять сущностью таким же образом, независимо от того, была ли сущность уже сохранена или это совершенно новая.
@BillyONeal как насчет столкновений?
@ArialdoMartini, следили ли вы за обсуждением в комментариях к этой записи в блоге? меня не продают по GUID. для меня это похоже на использование одного и того же поля базы данных для хранения нескольких значений. если у вас есть проблема с URL-адресами веб-сайтов, то ее следует решить на веб-сайте
char (36) будет хорошим выбором. Также можно использовать функцию MySQL UUID (), которая возвращает текстовый формат из 36 символов (шестнадцатеричный с дефисами), который можно использовать для получения таких идентификаторов из базы данных.
Добавляя к ответу ThaBadDawg, используйте эти удобные функции (благодаря моему более мудрому коллеге), чтобы получить строку длиной 36 обратно в массив байтов из 16.
DELIMITER $$
CREATE FUNCTION `GuidToBinary`(
$Data VARCHAR(36)
) RETURNS binary(16)
DETERMINISTIC
NO SQL
BEGIN
DECLARE $Result BINARY(16) DEFAULT NULL;
IF $Data IS NOT NULL THEN
SET $Data = REPLACE($Data,'-','');
SET $Result =
CONCAT( UNHEX(SUBSTRING($Data,7,2)), UNHEX(SUBSTRING($Data,5,2)),
UNHEX(SUBSTRING($Data,3,2)), UNHEX(SUBSTRING($Data,1,2)),
UNHEX(SUBSTRING($Data,11,2)),UNHEX(SUBSTRING($Data,9,2)),
UNHEX(SUBSTRING($Data,15,2)),UNHEX(SUBSTRING($Data,13,2)),
UNHEX(SUBSTRING($Data,17,16)));
END IF;
RETURN $Result;
END
$$
CREATE FUNCTION `ToGuid`(
$Data BINARY(16)
) RETURNS char(36) CHARSET utf8
DETERMINISTIC
NO SQL
BEGIN
DECLARE $Result CHAR(36) DEFAULT NULL;
IF $Data IS NOT NULL THEN
SET $Result =
CONCAT(
HEX(SUBSTRING($Data,4,1)), HEX(SUBSTRING($Data,3,1)),
HEX(SUBSTRING($Data,2,1)), HEX(SUBSTRING($Data,1,1)), '-',
HEX(SUBSTRING($Data,6,1)), HEX(SUBSTRING($Data,5,1)), '-',
HEX(SUBSTRING($Data,8,1)), HEX(SUBSTRING($Data,7,1)), '-',
HEX(SUBSTRING($Data,9,2)), '-', HEX(SUBSTRING($Data,11,6)));
END IF;
RETURN $Result;
END
$$
CHAR(16) на самом деле является BINARY(16), выберите свой предпочтительный вкус
Чтобы лучше следовать коду, возьмите пример с указанным ниже GUID с порядковым номером. (Недопустимые символы используются в иллюстративных целях - каждый помещает уникальный символ.) Функции преобразуют порядок байтов для достижения порядка битов для более высокой кластеризации индекса. Переупорядоченный гид показан под примером.
12345678-9ABC-DEFG-HIJK-LMNOPQRSTUVW
78563412-BC9A-FGDE-HIJK-LMNOPQRSTUVW
Удалены дефисы:
123456789ABCDEFGHIJKLMNOPQRSTUVW
78563412BC9AFGDEHIJKLMNOPQRSTUVW
Вот приведенный выше GuidToBinary без удаления дефисов из строки: CREATE FUNCTION GuidToBinary ($ guid char (36)) RETURNS binary (16) RETURN CONCAT (UNHEX (SUBSTRING ($ guid, 7, 2)), UNHEX (SUBSTRING ($ guid , 5, 2)), UNHEX (SUBSTRING ($ guid, 3, 2)), UNHEX (SUBSTRING ($ guid, 1, 2)), UNHEX (SUBSTRING ($ guid, 12, 2)), UNHEX (SUBSTRING ( $ guid, 10, 2)), UNHEX (SUBSTRING ($ guid, 17, 2)), UNHEX (SUBSTRING ($ guid, 15, 2)), UNHEX (SUBSTRING ($ guid, 20, 4)), UNHEX ( SUBSTRING ($ guid, 25, 12)));
Для любопытных, эти функции лучше UNHEX (REPLACE (UUID (), '-', '')), потому что они упорядочивают биты в таком порядке, который будет лучше работать в кластеризованном индексе.
Это очень полезно, но я считаю, что его можно улучшить с помощью источника эквивалентности CHAR и BINARY (документы, похоже, подразумевает наличие важных различий и объяснение того, почему производительность кластерного индекса лучше с переупорядоченными байтами.
Когда я использую это, мой гид меняется. Я пробовал вставить его, используя как unhex (replace (string, '-', '')), и функцию выше, и когда я конвертирую их обратно, используя те же методы, выбранный guid не тот, который был вставлен. Что трансформирует гид? Все, что я сделал, это скопировал код сверху.
@JonathanOliver Не могли бы вы поделиться кодом функции BinaryToGuid ()?
Ого, слишком многословно. я буду придерживаться UNHEX (REPLACE (UUID (), '-', '') и вложенных функций вставки insert (insert (insert (insert (HEX (MyBin16Col), 9,0, '-'), 14,0, '-'), 19,0, '-'), 24,0, '-')
Двоичный (16) будет лучше, чем использование varchar (32).
«Лучше» зависит от того, для чего вы оптимизируете.
Насколько вы заботитесь о размере / производительности хранилища по сравнению с простотой разработки? Что еще более важно, вы генерируете достаточно GUID или получаете их достаточно часто, чтобы это было важно?
Если ответ - «нет», char(36) более чем достаточно хорош, и он делает хранение / выборку GUID чрезвычайно простым. В противном случае binary(16) является разумным, но вам придется полагаться на MySQL и / или выбранный вами язык программирования для преобразования туда и обратно из обычного строкового представления.
Если вы размещаете программное обеспечение (например, веб-страницу) и не продаете / не устанавливаете в клиенте, вы всегда можете начать с char (36) для облегчения разработки на ранней стадии программного обеспечения и изменить его на более компактный формат, поскольку система растет в использовании и начинает нуждаться в оптимизации.
Самый большой недостаток гораздо большего символа char (36) - это то, сколько места займет индекс. Если у вас есть большое количество записей в базе данных, вы удваиваете размер индекса.
если у вас есть значение char / varchar, отформатированное как стандартный GUID, вы можете просто сохранить его как BINARY (16), используя простой CAST (MyString AS BINARY16), без всех этих ошеломляющих последовательностей CONCAT + SUBSTR.
Поля BINARY (16) сравниваются / сортируются / индексируются намного быстрее, чем строки, а также занимают в два раза меньше места в базе данных.
Выполнение этого запроса показывает, что CAST преобразует строку uuid в байты ASCII: set @a = uuid (); выберите @a, hex (cast (@a AS BINARY (16))); Я получаю 16f20d98-9760-11e4-b981-feb7b39d48d6: 3136663230643938 2D 39373630 2D 3131 (для форматирования добавлены пробелы). 0x31 = ascii 1, 0x36 = ascii 6. Мы даже получаем 0x2D, то есть дефис. Это не сильно отличается от простого сохранения guid в виде строки, за исключением того, что вы обрезаете строку до 16-го символа, который отщепляет часть идентификатора, зависящую от машины.
Да, это просто усечение. select CAST("hello world, this is as long as uiid" AS BINARY(16)); производит hello world, thi
Подпрограмму GuidToBinary, опубликованную KCD, следует настроить, чтобы учесть битовую компоновку метки времени в строке GUID. Если строка представляет UUID версии 1, например, те, которые возвращает подпрограмма uuid () mysql, тогда компоненты времени вставляются в буквы 1-G, за исключением D.
12345678-9ABC-DEFG-HIJK-LMNOPQRSTUVW
12345678 = least significant 4 bytes of the timestamp in big endian order
9ABC = middle 2 timestamp bytes in big endian
D = 1 to signify a version 1 UUID
EFG = most significant 12 bits of the timestamp in big endian
При преобразовании в двоичный формат наилучшим порядком индексации будет: EFG9ABC12345678D + остальные.
Вы не хотите менять местами 12345678 на 78563412, потому что большой порядок байтов уже дает лучший порядок байтов двоичного индекса. Однако вы действительно хотите, чтобы самые важные байты перемещались перед младшими байтами. Следовательно, EFG идет первым, за ним следуют средние биты и младшие биты. Сгенерируйте около дюжины UUID с помощью uuid () в течение минуты, и вы увидите, как этот порядок дает правильный ранг.
select uuid(), 0
union
select uuid(), sleep(.001)
union
select uuid(), sleep(.010)
union
select uuid(), sleep(.100)
union
select uuid(), sleep(1)
union
select uuid(), sleep(10)
union
select uuid(), 0;
/* output */
6eec5eb6-9755-11e4-b981-feb7b39d48d6
6eec5f10-9755-11e4-b981-feb7b39d48d6
6eec8ddc-9755-11e4-b981-feb7b39d48d6
6eee30d0-9755-11e4-b981-feb7b39d48d6
6efda038-9755-11e4-b981-feb7b39d48d6
6f9641bf-9755-11e4-b981-feb7b39d48d6
758c3e3e-9755-11e4-b981-feb7b39d48d6
Первые два UUID были сгенерированы наиболее близко по времени. Они различаются только в последних 3 полубайтах первого блока. Это наименее значимые биты временной метки, что означает, что мы хотим сдвинуть их вправо, когда мы преобразуем их в индексируемый массив байтов. В качестве примера счетчика последний идентификатор является самым последним, но алгоритм подкачки KCD поместит его перед третьим идентификатором (3e до dc, последние байты из первого блока).
Правильный порядок индексации:
1e497556eec5eb6...
1e497556eec5f10...
1e497556eec8ddc...
1e497556eee30d0...
1e497556efda038...
1e497556f9641bf...
1e49755758c3e3e...
Дополнительную информацию см. В этой статье: http://mysql.rjweb.org/doc.php/uuid
*** обратите внимание, что я не разделяю полубайт версии на старшие 12 бит временной метки. Это кусочек D из вашего примера. Я просто бросаю его вперед. Итак, моя двоичная последовательность оказывается DEFG9ABC и так далее. Это означает, что все мои индексированные UUID начинаются с одного полубайта. Статья делает то же самое.
цель этого - сэкономить место для хранения? или сделать сортировку полезной?
@ MD004. Это создает лучший индекс сортировки. Пространство остается прежним.
Согласно исследованиям Percona, для тех, кто только что наткнулся на это, теперь есть гораздо лучшая альтернатива.
Он состоит из реорганизации фрагментов UUID для оптимальной индексации, а затем преобразования в двоичный файл для уменьшения объема памяти.
Читать статью полностью здесь
Я читал эту статью раньше. Я нахожу это очень интересным, но как тогда нам выполнять запрос, если мы хотим фильтровать по идентификатору, который является двоичным? Думаю, нам нужно снова проклясть, а затем применить критерии. Это так сложно? Зачем хранить двоичный код (16) (конечно, лучше, чем varchar (36)) вместо 8-байтового bigint?
Есть обновленная статья от MariaDB, которая должна ответить на ваш вопрос mariadb.com/kb/en/mariadb/guiduuid-performance
fwiw, UUIDv4 полностью случайный и не требует разбиения на части.
Я бы предложил использовать следующие функции, поскольку те, что упомянуты @ bigh_29, преобразуют мои гиды в новые (по причинам, которых я не понимаю). Кроме того, они немного быстрее в тестах, которые я проводил на своих столах. https://gist.github.com/damienb/159151
DELIMITER |
CREATE FUNCTION uuid_from_bin(b BINARY(16))
RETURNS CHAR(36) DETERMINISTIC
BEGIN
DECLARE hex CHAR(32);
SET hex = HEX(b);
RETURN LOWER(CONCAT(LEFT(hex, 8), '-', MID(hex, 9,4), '-', MID(hex, 13,4), '-', MID(hex, 17,4), '-', RIGHT(hex, 12)));
END
|
CREATE FUNCTION uuid_to_bin(s CHAR(36))
RETURNS BINARY(16) DETERMINISTIC
RETURN UNHEX(CONCAT(LEFT(s, 8), MID(s, 10, 4), MID(s, 15, 4), MID(s, 20, 4), RIGHT(s, 12)))
|
DELIMITER ;
"thaBadDawg" предлагает хороший ответ. На Stack Overflow есть параллельная ветка, в которой обсуждается эта тема. Я добавил несколько комментариев к тем ответам на темы, которые содержат более подробные ссылки на ресурсы. Вот ссылка на вопрос: stackoverflow.com/questions/547118/storing-mysql-guid-uuids - я ожидаю, что эта тема станет более распространенной, когда люди начнут рассматривать AWS и Aurora.