출처1 :&nbsp;<a href="http://small-dbtalk.blogspot.kr/2014/12/mysql-uuid.html" target="_blank" class="tx-link">http://small-dbtalk.blogspot.kr/2014/12/mysql-uuid.html</a>출처2 :&nbsp;<a href="http://mysql.rjweb.org/doc.php/uuid" target="_blank" class="tx-link">http://mysql.rjweb.org/doc.php/uuid</a>출처3 :&nbsp;<a href="https://www.percona.com/blog/2014/12/19/store-uuid-optimized-way/" target="_blank">https://www.percona.com/blog/2014/12/19/store-uuid-optimized-way/</a> <h5 style="width: 1110px;">[Javascript] UUID/BINARY(16)를 부호있는 정수배열로 변환하기 Convert UUID/BINARY(16) to Signd Integer Array</h5><a href="https://hvy.kr/post/348" target="_blank">https://hvy.kr/post/348</a> uuid 를 pk로 사용할 때 아래와 같은 방식으로 관리를 한다면 int 형의 자동증가처럼 적절하게 사용 할 수 있다. <pre style="background-color:#2b2b2b;color:#a9b7c6;font-family:'JetBrains Mono',monospace;font-size:9.8pt;">DELIMITER $$ DROP FUNCTION IF EXISTS BIN_ID$$ CREATE FUNCTION BIN_ID() RETURNS BINARY(16) BEGIN DECLARE MUUID CHAR(36); SET MUUID = UUID(); RETURN UNHEX(CONCAT( SUBSTR(MUUID, 15, 4), SUBSTR(MUUID, 10, 4), SUBSTR(MUUID, 1, 8), SUBSTR(MUUID, 20, 4), SUBSTR(MUUID, 25))); END$$ DELIMITER ; DELIMITER $$ DROP FUNCTION IF EXISTS ID_AS_UUID$$ CREATE FUNCTION `ID_AS_UUID`( `_bin` BINARY(16) ) RETURNS VARCHAR(36) CHARSET UTF8MB4 BEGIN IF _BIN IS NULL THEN RETURN NULL; ELSE RETURN LCASE(CONCAT_WS('-', HEX(SUBSTR(_BIN, 5, 4)), HEX(SUBSTR(_BIN, 3, 2)), HEX(SUBSTR(_BIN, 1, 2)), HEX(SUBSTR(_BIN, 9, 2)), HEX(SUBSTR(_BIN, 11)) )); END IF; END$$ DELIMITER ;</pre> <h4>Java</h4><pre style="background-color:#2b2b2b;color:#a9b7c6;font-family:'D2Coding',monospace;font-size:10.5pt;">import java.util.Arrays; public class SortedUUIDHelper { private static String getHexStringFromByteArray(byte[] a) { StringBuilder sb = new StringBuilder(); for (final byte b : a) sb.append(String.format("%02x", b &amp; 0xff)); return sb.toString(); } private static String getHexStringFromByte(byte a) { return String.format("%02x", a &amp; 0xff); } private static byte[] getByteArrayFromHexString(String s) { int len = s.length(); byte[] data = new byte[len / 2]; for (int i = 0; i &lt; len; i += 2) { data[i / 2] = (byte) ((Character.digit(s.charAt(i), 16) &lt;&lt; 4) + Character.digit(s.charAt(i + 1), 16)); } return data; } public static String getUUIDStringFromByteArray(byte[] id) { String str1 = getHexStringFromByteArray(Arrays.copyOfRange(id, 4, 8)); String str2 = getHexStringFromByteArray(Arrays.copyOfRange(id, 2, 4)); String str3 = getHexStringFromByteArray(Arrays.copyOfRange(id, 0, 2)); String str4 = getHexStringFromByteArray(Arrays.copyOfRange(id, 8, 10)); String str5 = getHexStringFromByteArray(Arrays.copyOfRange(id, 10, 16)); return String.join("-", str1, str2, str3, str4, str5); } public static byte[] getByteArrayFromUUIDString(String id) { try { String hexStr = id.substring(14, 18) + id.substring(9, 13) + id.substring(0, 8) + id.substring(19, 23) + id.substring(24); return getByteArrayFromHexString(hexStr); } catch (Exception e) { return null; } } } </pre> <div class="txc-textbox" style="border-style: solid; border-width: 1px; border-color: rgb(254, 254, 184); background-color: rgb(254, 254, 184); padding: 10px;"><a name="code_to_do_it" style="font-family: &quot;Malgun Gothic&quot;; font-size: medium;"> Again, this code is obviated in MySQL 8.0 by&nbsp;</a><a href="https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_uuid-to-bin" style="font-family: &quot;Malgun Gothic&quot;; font-size: medium;">UUID_TO_BIN(str, swap_flag)</a> Let's make Stored Functions to do the messy work of the two actions:&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;⚈&nbsp;&nbsp;Rearrange fields&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;⚈&nbsp;&nbsp;Convert to/from&nbsp;<tt>BINARY(16)</tt>&nbsp; <pre> DELIMITER //

 CREATE FUNCTION UuidToBin(_uuid BINARY(36))
 RETURNS BINARY(16)
 LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY INVOKER
 RETURN
 UNHEX(CONCAT(
 SUBSTR(_uuid, 15, 4),
 SUBSTR(_uuid, 10, 4),
 SUBSTR(_uuid, 1, 8),
 SUBSTR(_uuid, 20, 4),
 SUBSTR(_uuid, 25) ));

 CREATE FUNCTION UuidFromBin(_bin BINARY(16))
 RETURNS BINARY(36)
 LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY INVOKER
 RETURN
 LCASE(CONCAT_WS('-',
 HEX(SUBSTR(_bin, 5, 4)),
 HEX(SUBSTR(_bin, 3, 2)),
 HEX(SUBSTR(_bin, 1, 2)),
 HEX(SUBSTR(_bin, 9, 2)),
 HEX(SUBSTR(_bin, 11))
 ));

 //
 DELIMITER ;
</pre> Then you would do things like the following. (Applies to 8.0, but with function names changed to&nbsp;<tt>UUID_TO_BIN()</tt>&nbsp;and&nbsp;<tt>BIN_TO_UUID()</tt>.)<pre> -- Letting MySQL create the UUID:
 INSERT INTO t (uuid, ...) VALUES (UuidToBin(UUID()), ...);

 -- Creating the UUID elsewhere:
 INSERT INTO t (uuid, ...) VALUES (UuidToBin(?), ...);

 -- Retrieving (point query using uuid):
 SELECT ... FROM t WHERE uuid = UuidToBin(?);

 -- Retrieving (other):
 SELECT UuidFromBin(uuid), ... FROM t ...;
</pre>Do not flip&nbsp;the WHERE; this will be inefficent because it won't use&nbsp;<tt>INDEX(uuid)</tt>:<pre> WHERE UuidFromBin(uuid) = '1026-baba-6ccd780c-9564-0040f4311e29' -- NO
</pre> </div>

[MySQL / MariaDB] UUID GUID Key 관리방법