UUID 기본 키가 BINARY(16)인 MariaDB 테이블이 NULL이 아님

UUID 기본 키가 BINARY(16)인 MariaDB 테이블이 NULL이 아님

테이블에서 BINARY UUID를 기본 키로 사용하고 싶지만 다음 문서를 기반으로 느슨하게 최적화된 UUID를 생성하는 자체 사용자 지정 함수를 사용합니다. https://mariadb.com/kb/en/guiduuid-performance/

표 구조와 두 가지 주요 관심 기능은 다음과 같습니다.

CREATE TABLE `Test` (
  `Id` BINARY(16),
  `Data` VARCHAR(100)
) ENGINE=InnoDB
ROW_FORMAT=DYNAMIC CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';

CREATE DEFINER = 'user'@'%' FUNCTION `OPTIMISE_UUID_STR`(`_uuid` VARCHAR(36))
    RETURNS VARCHAR(32) CHARACTER SET utf8mb4
    DETERMINISTIC
    NO SQL
    SQL SECURITY INVOKER
    COMMENT ''
BEGIN
/*
FROM

00       10        20        30
123456789012345678901234567890123456
====================================
AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE


TO

00       10        20        30
12345678901234567890123456789012
================================
CCCCBBBBAAAAAAAADDDDEEEEEEEEEEEE
*/
RETURN UCASE(CONCAT(
        SUBSTR(_uuid, 15, 4), /* Time nodes reversed */
        SUBSTR(_uuid, 10, 4),
        SUBSTR(_uuid,  1, 8),
        SUBSTR(_uuid, 20, 4),  /* MAC nodes last */
        SUBSTR(_uuid, 25, 12)));
END;


CREATE DEFINER = 'user'@'%' FUNCTION `CONVERT_OPTIMISED_UUID_STR_TO_BIN`(`_hexstr` BINARY(32))
    RETURNS BINARY(16)
    DETERMINISTIC
    NO SQL
    SQL SECURITY INVOKER
    COMMENT ''
BEGIN
/*
Convert optimised UUID from string hex representation to binary. If the UUID is not optimised, it makes no sense to convert
*/
RETURN UNHEX(_hexstr);
END;

아래와 같이 열 정의에서 사용자 지정 함수를 사용할 수 없습니다.

CREATE TABLE `Test` (
  `Id` BINARY(16) NOT NULL DEFAULT CONVERT_OPTIMISED_UUID_STR_TO_BIN(OPTIMISE_UUID_STR(UUID())),

"Function or expression" 오류가 표시됩니다.OPTIMISE_UUID_STR()'은(는) 의 DEFAULT 절에서 사용할 수 없습니다.Id"

그래서 트리거:에서 동일하게 사용해 보았습니다.

CREATE DEFINER = 'user'@'%' TRIGGER `Test_before_ins_tr1` BEFORE INSERT ON `Test`
  FOR EACH ROW
BEGIN
  IF (new.Id IS NULL) OR (new.Id = X'0000000000000000') OR (new.Id = X'FFFFFFFFFFFFFFFF') THEN
    SET new.Id = CONVERT_OPTIMISED_UUID_STR_TO_BIN(OPTIMISE_UUID_STR(UUID()));
  END IF;   
END;

위의 것들은 꽤 잘 작동하지만, 문제는 제가 정의할 수 없다는 것입니다.Id열을 주 키로 지정합니다. 주 키는 NULL이 아니어야 하기 때문입니다. 이 설정은 최적화된 UUID를 미리 생성해야 한다는 것을 의미합니다. DB에서 최적화된 UUID 생성을 처리하기 위해 이 작업을 수행하지 않습니다.

위의 Trigger 정의를 보고 유추하셨을 수도 있지만, 저는 다음과 같은 기본값을 설정해 보았습니다.Id열(예:

Id` BINARY(16) NOT NULL DEFAULT X'0000000000000000'

그리고.

Id` BINARY(16) NOT NULL DEFAULT X'FFFFFFFFFFFFFFFF'

그리고.

Id` BINARY(16) NOT NULL DEFAULT '0' /* I tried setting 0, but always seem to revert to '0' */

이 기본값은 트리거에 의해 선택되고 올바른 최적화된 UUID가 할당됩니다.그러나 DEFAULT 값이 설정되어 있더라도 DB가 "Column 'Id'는 null일 수 없습니다"라고 불평하기 때문에 이 방법도 작동하지 않습니다.

따라서 실제 질문은 다음과 같습니다. 주 키 열에 대한 사용자 지정(최적화된 UUID) BINARY 값을 생성할 수 값을 생성할 수 있습니까?

예, 트리거 및/또는 저장된 기능이 없어도 실행할 수 있습니다.

버전 10.6의 MariaDB:

함수 사용SYS_GUID()UUID()와 동일한 결과를 반환하지만 UUID()는 반환하지 않습니다.-성격.이 함수의 결과는 UNHEX() 함수를 사용하여 16바이트 값으로 직접 변환할 수 있습니다.

예:

CREATE TABLE test (a BINARY(16) NOT NULL DEFAULT UNHEX(SYS_GUID()) PRIMARY KEY);
INSERT INTO test VALUES (DEFAULT);
INSERT INTO test VALUES (DEFAULT);
SELECT HEX(a) FROM test;
+----------------------------------+
| HEX(a)                           |
+----------------------------------+
| 53EE84FB733911EDA238D83BBF89F2E2 |
| 61AC0286733911EDA238D83BBF89F2E2 |
+----------------------------------+

버전 10.7의 MariaDB(Danielblack의 코멘트에 언급된 바와 같이):

사용하다UUIDUUID()(및 SYS_GUID() 값을 16바이트로 저장하는 데이터 유형:

CREATE TABLE test (a UUID not NULL default UUID() PRIMARY KEY);
INSERT INTO test VALUES (DEFAULT);
INSERT INTO test VALUES (DEFAULT);
SELECT a FROM test;
+--------------------------------------+
| a                                    |
+--------------------------------------+
| 6c42e367-733b-11ed-a238-d83bbf89f2e2 |
| 6cbc0418-733b-11ed-a238-d83bbf89f2e2 |
+--------------------------------------+

부록:만약 당신이 버전 < 10.6을 사용하고 있고 당신의 요구사항이 다음의 제한사항과 일치한다면, 당신은 또한 사용할 수 있습니다.UUID_SHORT()64비트 식별자를 생성하는 함수입니다.

단답: 예

긴 답변:

그PRIMARY KEY생성할 수 있는 모든 값을 가진 거의 모든 데이터 유형일 수 있습니다.

• TEXT또는BLOB허용되지 않습니다.심지어는TINYTEXT.
• VARCHAR(etc)는 일부 크기를 초과할 수 없습니다(버전 및 버전에 따라 다름).CHARACTER SET).VARCHAR(191)(또는 그 이하)는 모든 조합에서 작동합니다.서나 볼 수 있는 VARCHAR(255)여러 상황에서 작동합니다.

MySQL 8.0에는 이진 및 문자열 UUID 간 변환을 위한 함수가 내장되어 있습니다.이 기능은 다음과 같은 UUID에 대한 기능도 제공합니다.

언급URL : https://stackoverflow.com/questions/74665341/mariadb-table-with-uuid-primary-key-as-binary16-not-null