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

출처1 : http://small-dbtalk.blogspot.kr/2014/12/mysql-uuid.html

출처2 : http://mysql.rjweb.org/doc.php/uuid

2018/07/10 - [프로그램 자료/Java Script] - [Javascript] UUID/BINARY(16)를 부호있는 정수배열로 변환하기 Convert UUID/BINARY(16) to Signd Integer Array

uuid 를 pk로 사용할 때 아래와 같은 방식으로 관리를 한다면 int 형의 자동증가처럼 적절하게 사용 할 수 있다.

DELIMITER $$

 

USE `DbName`$$

 

DROP FUNCTION IF EXISTS ordered_uuid$$

 

CREATE DEFINER='userId'@'%' FUNCTION ordered_uuid() RETURNS BINARY(16)

BEGIN

SET @UUID = UUID();

RETURN

UNHEX(CONCAT(

SUBSTR(@UUID, 15, 4),

SUBSTR(@UUID, 10, 4),

SUBSTR(@UUID,  1, 8),

SUBSTR(@UUID, 20, 4),

SUBSTR(@UUID, 25)));

END$$

 

DELIMITER ;

다음은 BINARY(16) 형태의 데이터를 UUID or GUID 형태로 변환하는 함수

DELIMITER $$

USE `DbName`$$

 

DROP FUNCTION IF EXISTS binary_to_uuid$$

CREATE DEFINER=`userId`@`%` FUNCTION `binary_to_uuid`(

    `_bin` BINARY(16)

)

RETURNS varchar(36) CHARSET utf8

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

BEGIN

   

    IF _bin IS NULL THEN

        RETURN NULL;

    ELSE

        RETURN

            LCASE(CONCAT_WS('-',

                HEX(CONCAT(SUBSTR(_bin, 4, 1), SUBSTR(_bin, 3, 1), SUBSTR(_bin,  2, 1),SUBSTR(_bin, 1, 1))),

                HEX(CONCAT(SUBSTR(_bin, 6, 1), SUBSTR(_bin, 5, 1))),

                HEX(CONCAT(SUBSTR(_bin, 8, 1), SUBSTR(_bin, 7, 1))),

                HEX(SUBSTR(_bin, 9, 2)),

                HEX(SUBSTR(_bin, 11, 8))

            ));

    END IF;

 

END$$

DELIMITER ;

 

Again, this code is obviated in MySQL 8.0 by UUID_TO_BIN(str, swap_flag)

Let's make Stored Functions to do the messy work of the two actions: 
    ⚈  Rearrange fields 
    ⚈  Convert to/from BINARY(16) 

    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 ;

Then you would do things like the following. (Applies to 8.0, but with function names changed to UUID_TO_BIN() and BIN_TO_UUID().)

    -- 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 ...;

Do not flip the WHERE; this will be inefficent because it won't use INDEX(uuid):

    WHERE UuidFromBin(uuid) = '1026-baba-6ccd780c-9564-0040f4311e29' -- NO