하나의 열에 고유(여러 열) 및 null

하나의 열에 고유(여러 열) 및 null

간단한 카테고리 테이블이 있습니다.범주에는 상위 범주가 있을 수 있습니다(par_catcolumn) 또는 null(기본 범주이고 부모 범주가 동일한 경우에는 이름이나 URL이 같은 범주가 2개 이상 없어야 합니다.

이 테이블의 코드:

CREATE TABLE IF NOT EXISTS `categories` (
`id` int(10) unsigned NOT NULL,
  `par_cat` int(10) unsigned DEFAULT NULL,
  `lang` varchar(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'pl',
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `url` varchar(120) COLLATE utf8_unicode_ci NOT NULL,
  `active` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `accepted` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `priority` int(10) unsigned NOT NULL DEFAULT '1000',
  `entries` int(10) unsigned NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;



ALTER TABLE `categories`
  ADD PRIMARY KEY (`id`), 
  ADD UNIQUE KEY `categories_name_par_cat_unique` (`name`,`par_cat`), 
  ADD UNIQUE KEY `categories_url_par_cat_unique` (`url`,`par_cat`), 
  ADD KEY `categories_par_cat_foreign` (`par_cat`);


ALTER TABLE `categories`
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;

ALTER TABLE `categories`ADD CONSTRAINT `categories_par_cat_foreign` 
  FOREIGN KEY (`par_cat`) REFERENCES `categories` (`id`);

문제는 제가 고유한 키를 가지고 있어도 작동하지 않는다는 것입니다.데이터베이스에 다음을 포함하는 2개의 카테고리를 삽입하려고 하면par_cat로 설정.null그리고 동일한 이름과 url, 이 두 카테고리는 문제없이 데이터베이스에 삽입될 수 있습니다(그리고 그렇게 해서는 안 됩니다).그러나 이 범주에 대해 선택하면 다른 범주에서par_cat(예를 들어, ID가 1인 카테고리가 존재한다고 가정할 경우), 첫 번째 레코드만 삽입됩니다(이는 원하는 동작입니다).

질문 - 이 사건을 어떻게 처리해야 합니까?나는 다음을 읽었습니다.

UNIQUE 인덱스는 인덱스의 모든 값이 서로 달라야 하는 제약 조건을 만듭니다.기존 행과 일치하는 키 값으로 새 행을 추가하려고 하면 오류가 발생합니다.이 제약 조건은 BDB 스토리지 엔진을 제외한 NULL 값에는 적용되지 않습니다.다른 엔진의 경우 UNICEF 인덱스는 NULL을 포함할 수 있는 열에 대해 여러 NULL 값을 허용합니다. UNICEF 인덱스의 열에 대한 접두사 값을 지정하는 경우 열 값은 접두사 내에서 고유해야 합니다.

그러나 여러 열에 고유한 항목이 있는 경우에는 그렇지 않을 것으로 예상했습니다(단,par_catnull일 수 있습니다.name그리고.urlnull일 수 없습니다.왜냐면par_cat에 대한 언급.id같은 테이블이지만 일부 범주에는 허용해야 할 상위 범주가 없습니다.null가치.

SQL 표준에서 정의한 대로 작동합니다.NULL은 알 수 없음을 의미합니다.par_cat = NULL 및 name = 'X'의 두 레코드가 있는 경우 두 NULL은 동일한 값을 가지는 것으로 간주되지 않습니다.따라서 고유한 키 제약 조건을 위반하지 않습니다.(음, NULL은 여전히 동일한 값을 의미할 수 있지만 이 규칙을 적용하면 고유한 인덱스와 NULL 필드를 사용하는 작업이 거의 불가능합니다. NULL은 1, 2 또는 다른 값을 의미할 수도 있기 때문입니다.그래서 그들은 제 생각처럼 잘 정의했습니다.)

MySQL은 인덱스를 가질 수 있는 기능 인덱스를 지원하지 않기 때문에ISNULL(par_cat,-1), name, 제약 조건이 작동하도록 하려면 par_cat를 "부모 없음"에 대해 0 또는 -1 또는 그 밖에 없는 NOT NULL 열로 만드는 것이 유일한 옵션입니다.

이것은 2014년에 요청된 것으로 알고 있습니다.그러나 MySQL: https://bugs.mysql.com/bug.php?id=8173 및 https://bugs.mysql.com/bug.php?id=17825 에서 요청하는 경우가 많습니다.사람들은 MySQL에서 관심을 끌기 위해 영향을 클릭할 수 있습니다.

MySQL 5.7 이후로 이제 다음과 같은 해결 방법을 사용할 수 있습니다.

ALTER TABLE categories 
ADD generated_par_cat INT UNSIGNED AS (ifNull(par_cat, 0)) NOT NULL,
ADD UNIQUE INDEX categories_name_generated_par_cat (name, generated_par_cat), 
ADD UNIQUE INDEX categories_url_generated_par_cat (url, generated_par_cat); 

generated_par_cat은 가상으로 생성된 열이므로 저장 공간이 없습니다.사용자가 삽입(또는 업데이트)하면 고유 인덱스로 인해 generated_par_cat 값이 즉시 생성됩니다. 이는 매우 빠른 작업입니다.

혹시 라라벨에서 오실 때를 대비해서요

이 버전은 가상 칼럼에서 해결할 수 있는 Laravel의 마이그레이션 버전입니다.UNIQUE열 중 하나가 다음과 같은 경우 발행NULL값어치가 있는

$table->integer('generated_par_cat')->virtualAs('ifNull(par_cat, 0)');

$table->unique(['name', 'generated_par_cat'], 'name_par_cat_unique');

언급URL : https://stackoverflow.com/questions/25844786/unique-multiple-columns-and-null-in-one-column