Postgre를 합니까?SQL에서 "어센틱하지 않은" 데이터 정렬을 지원합니까?

Postgre를 합니까?SQL에서 "어센틱하지 않은" 데이터 정렬을 지원합니까?

Microsoft SQL Server에서는 데이터베이스, 테이블 또는 열에 대해 "악센트가 없는" 조합을 지정할 수 있습니다. 즉, 다음과 같은 쿼리가 가능합니다.

SELECT * FROM users WHERE name LIKE 'João'

행을 Joaohttp://///t.

저는 포스트그레의 현에서 악센트를 제거하는 것이 가능하다는 것을 알고 있습니다.unaccent_string 기여 함수를 사용하는 SQL인데 Postgre가 맞는지 궁금합니다.SQL은 이러한 "악센트가 없는" 데이터 정렬을 지원하므로SELECT위에 있는 것이 좋습니다.

Postgres 12 이상에 대한 업데이트

Postgres 12는 비결정론적 ICU 조합을 추가하여 대/소문자를 구분하지 않고 악센트를 구분하지 않는 그룹화 및 정렬을 가능하게 합니다.설명서:

Postgre에서 ICU 지원이 구성된 경우에만 ICU 로케일을 사용할 수 있습니다.SQL이 구축되었습니다.

그렇다면 다음과 같은 이점이 있습니다.

CREATE COLLATION ignore_accent (provider = icu, locale = 'und-u-ks-level1-kc-true', deterministic = false);

CREATE INDEX users_name_ignore_accent_idx ON users(name COLLATE ignore_accent);

SELECT * FROM users WHERE name = 'João' COLLATE ignore_accent;

만지작거리다

자세한 내용은 설명서를 참조하십시오.로렌츠 알베의 이 블로그 게시물은 이해하는 데 도움이 될 수 있습니다.

하지만 ICU 데이터 수집에는 단점도 있습니다.설명서:

[...] 또한 몇 가지 단점이 있습니다.무엇보다도, 그들의 사용은 성능 저하로 이어집니다.특히 B-트리는 비결정론적 조합을 사용하는 인덱스와 중복제거를 사용할 수 없습니다.또한 패턴 일치 작업과 같은 비결정론적인 정렬에서는 특정 작업이 불가능합니다.그러므로, 그것들은 그것들이 특별히 필요한 경우에만 사용되어야 합니다.

저의 "레거시" 솔루션이 여전히 우수할 수 있습니다.

모든 버전에 대해

연결 대상과 완전히 다른 악센트가 없는 모듈을 사용합니다.

unaccent는 어휘소에서 악센트(분음 부호)를 제거하는 텍스트 검색 사전입니다.

다음을 사용하여 데이터베이스당 한 번 설치:

CREATE EXTENSION unaccent;

다음과 같은 오류가 발생하는 경우:

ERROR: could not open extension control file
"/usr/share/postgresql/<version>/extension/unaccent.control": No such file or directory

다음 관련 답변의 지침에 따라 데이터베이스 서버에 기여 패키지를 설치합니다.

• Postgre에서 악센트가 없는 확장을 만드는 동안 오류 발생SQL

에서, 은 다도보기그, ▁function다 기능을 합니다.unaccent()할 수 당은당예함사수있용습다니할께시와신의신▁you있▁(▁with다).LIKE필요 없어 보입니다).

SELECT *
FROM   users
WHERE  unaccent(name) = unaccent('João');

색인

해당 쿼리에 인덱스를 사용하려면 식에 인덱스를 작성합니다.그러나 Postgres는 다음만 수락합니다.IMMUTABLE인덱스에 대한 함수입니다.함수가 동일한 입력에 대해 다른 결과를 반환할 수 있는 경우 인덱스가 자동으로 손상될 수 있습니다.

unaccent() 직오의STABLE것은 아니다.IMMUTABLE

불하게도행,,unaccent() 인입니다.STABLE,것은 아니다.IMMUTABLEpgsql-bugs의 이 스레드에 따르면 이는 세 가지 이유 때문입니다.

1. 그것은 사전의 행동에 달려 있습니다.
2. 이 사전에는 유선 연결이 없습니다.
3. 따라서 전류에도 의존합니다.search_path쉽게 변할 수 있는 것.

웹의 일부 튜토리얼은 기능 변동성을 다음과 같이 변경하도록 지시합니다.IMMUTABLE이 브루트 포스 방식은 특정 조건에서 파괴될 수 있습니다.

다른 사람들은 간단한 포장지 기능을 제안합니다(예전에 제가 했던 것처럼).

변수를 두 개의 매개 변수로 만들 것인지에 대한 논쟁이 계속되고 있습니다. IMMUTABLE사용된 사전을 명시적으로 선언합니다.여기나 여기서 읽으세요.

또 다른 대안은 Github에서 제공되는 Musicbrainz의 불변 기능이 있는 이 모듈일 것입니다.제가 직접 테스트해 본 적은 없습니다.더 좋은 아이디어를 생각해 낸 것 같습니다.

지금이 가장 좋습니다.

이 접근 방식은 다른 솔루션보다 더 효율적이고 안전합니다.
다음을 작성합니다.IMMUTABLE유선 연결된 스키마 정규화된 함수와 사전을 사용하여 두 매개 변수 형식을 실행하는 SQL 래퍼 함수입니다.

수정할 수 없는 함수를 중첩하면 함수 인라인이 비활성화되므로 C 함수의 복사본을 기반으로 합니다. (가짜) 선언됨IMMUTABLE뿐만 아니라.유일한 목적은 SQL 함수 래퍼에서 사용하는 것입니다.자체적으로 사용할 수 없습니다.

C 함수 선언에서 사전을 하드와이어로 연결할 방법이 없기 때문에 정교함이 필요합니다. (C 코드 자체를 해킹해야 합니다.SQL 래퍼 함수는 이를 수행하며 함수 인라인 및 식 인덱스를 모두 허용합니다.

CREATE OR REPLACE FUNCTION public.immutable_unaccent(regdictionary, text)
  RETURNS text
  LANGUAGE c IMMUTABLE PARALLEL SAFE STRICT AS
'$libdir/unaccent', 'unaccent_dict';

그러면:

CREATE OR REPLACE FUNCTION public.f_unaccent(text)
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
SELECT public.immutable_unaccent(regdictionary 'public.unaccent', $1)
$func$;

Postgres 14 이상에서는 SQL 표준 함수가 약간 저렴하지만 다음과 같습니다.

CREATE OR REPLACE FUNCTION public.f_unaccent(text)
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT
  BEGIN ATOMIC
SELECT public.immutable_unaccent(regdictionary 'public.unaccent', $1);
END;

참조:

• BEGIN ATOMIC은 Postgre에서 무엇을 의미합니까?SQL SQL 함수/프로시저?

떨어지다PARALLEL SAFEPostgres 9.5 이상의 경우 두 기능 모두에서 사용할 수 있습니다.

public입니다.public기본값)입니다.

명시적 형식 선언(regdictionary는 악의적인 사용자에 는 악의적인 사용자에 의해 오버로드된 함수 변형으로 가상 공격을 방지합니다.

_{이전에 저는 다음을 기반으로 한 래퍼 기능을 지지했습니다.STABLEunaccent()악센트가 없는 모듈과 함께 제공됩니다.안감 기능이 비활성화되었습니다.이 버전은 제가 이전에 여기서 했던 간단한 래퍼 기능보다 10배 더 빠르게 실행됩니다.
그리고 그것은 이미 추가된 첫 번째 버전보다 두 배 더 빨랐습니다.SET search_path = public, pg_temp사전도 스키마 자격을 얻을 수 있다는 것을 발견하기 전까지.여전히 (사후 12) 문서에서 너무 명확하지 않습니다.}

C 함수를 생성하는 데 필요한 권한이 없는 경우 두 번째로 우수한 구현으로 돌아갑니다.안IMMUTABLE 능퍼래기(Wrapper) 래퍼STABLE unaccent()모듈에서 제공하는 기능:

CREATE OR REPLACE FUNCTION public.f_unaccent(text)
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
SELECT public.unaccent('public.unaccent', $1)  -- schema-qualify function and dictionary
$func$;

마지막으로 쿼리를 빠르게 만드는 표현식 인덱스:

CREATE INDEX users_unaccent_name_idx ON users(public.f_unaccent(name));

인덱스를 다시 만들지 않는 인플레이스 주 릴리스 업그레이드와 같이 기능 또는 사전을 변경한 후 이 기능과 관련된 인덱스를 다시 만들어야 합니다.최근 주요 릴리스에는 모두 다음에 대한 업데이트가 있었습니다.unaccent모듈.

인덱스와 일치하도록 쿼리를 조정합니다(쿼리 플래너가 인덱스를 사용하도록).

SELECT * FROM users
WHERE  f_unaccent(name) = f_unaccent('João');

연산자 오른쪽에 있는 식의 함수는 필요 없습니다.거기서 우리는 또한 다음과 같은 악센트가 없는 문자열을 제공할 수 있습니다.'Joao'직접적으로.

더 빠른 함수는 표현식 색인을 사용하는 훨씬 더 빠른 쿼리로 변환되지 않습니다.인덱스 조회는 사전 계산된 값에서 작동하며 어느 쪽이든 매우 빠릅니다.그러나 인덱스 유지보수 및 인덱스를 사용하지 않는 쿼리는 이점이 있습니다.또한 비트맵 인덱스 스캔과 같은 액세스 방법은 힙(주 관계)의 값을 다시 확인해야 할 수 있으며, 이 경우 기본 기능을 실행해야 합니다.참조:

• 비트맵 인덱스 검사가 있는 쿼리 계획의 "Reccheck Cond:" 행

Postgres 10.3 / 9.6.8 등을 통해 클라이언트 프로그램의 보안이 강화되었습니다.인덱스에 사용할 때 표시된 대로 함수 및 사전 이름을 스키마로 한정해야 합니다.참조:

• postgres 로그의 '텍스트 검색 사전 "unaccent"가 존재하지 않습니다." 항목은 자동 분석 중인 것으로 추정됩니다.

결박

Postgres 9.5 이상에서 'œ' 또는 'ß'와 같은 리거는 수동으로 확장해야 합니다(필요한 경우).unaccent()항상 단일 문자로 대체:

SELECT unaccent('Œ Æ œ æ ß');

unaccent
----------
E A e a S

이 업데이트는 Postgres 9.6에서 악센트가 없는 것으로 좋습니다.

확다하contrib/unaccent의 표준 의준기unaccent.rules유니코드에 알려진 모든 분음 부호를 처리하고, 연결을 올바르게 확장하는 파일(토마스 먼로, 레오나르 베네데티)

굵은 글씨로 강조해주세요.이제 다음을 확인할 수 있습니다.

SELECT unaccent('Œ Æ œ æ ß');

unaccent
----------
OE AE oe ae ss

패턴 매칭

임의 패턴에 대해 또는 임의 패턴과 함께 이를 Postgre의 모듈과 결합합니다.SQL 9.1 이상.Trigram GIN(일반적으로 선호됨) 또는 GIST 식 인덱스를 만듭니다.GIN의 예:

CREATE INDEX users_unaccent_name_trgm_idx ON users
USING gin (f_unaccent(name) gin_trgm_ops);

다음과 같은 쿼리에 사용할 수 있습니다.

SELECT * FROM users
WHERE  f_unaccent(name) LIKE ('%' || f_unaccent('João') || '%');

GIN 및 GIST 인덱스는 일반 B-트리보다 유지 관리 비용이 더 높습니다.

• GiST와 GIN 지수의 차이

왼쪽 고정된 패턴에 대한 더 간단한 솔루션이 있습니다.패턴 매칭 및 성능에 대한 자세한 정보:

• Postgre의 LIKE, SILLIX TO 또는 정규 표현식과 패턴 일치SQL

pg_trgm또한 "유사성"()% 및 "거리"()<->에 대한 유용한 연산자를 제공합니다.

는 "Trigram 같간은단정지다원니합규을식또한한다과음덱인는스▁regular▁express다▁trig▁simple"로 간단한 정규 표현을 지원합니다.~기타 및 대/소문자 구분 없이 일치하는 패턴ILIKE:

• PostgreSQL 액센트 + 대소문자 구분 안 함 검색

아니요, 포스트그리SQL은 그런 의미에서 데이터 정렬을 지원하지 않습니다.

PostgreSQL은 이진법이 동일하지 않은 한 비교가 동일하게 반환될 수 없기 때문에 그러한(악센트가 중요하지 않거나 중요하지 않음) 데이터 정렬을 지원하지 않습니다.이는 내부적으로 해시 인덱스와 같은 많은 복잡성을 초래할 것이기 때문입니다.이러한 이유로 가장 엄격한 의미의 조합은 순서에만 영향을 미치고 동일성에는 영향을 주지 않습니다.

해결 방법

강세를 취소하는 전체 텍스트 검색 사전입니다.

에는 FTS 경다사용사여전정있수다습니의할을하음을 하여 자신만의 할 수 .unaccent,

CREATE EXTENSION unaccent;

CREATE TEXT SEARCH CONFIGURATION mydict ( COPY = simple );
ALTER TEXT SEARCH CONFIGURATION mydict
  ALTER MAPPING FOR hword, hword_part, word
  WITH unaccent, simple;

그런 다음 기능성 색인으로 색인화할 수 있습니다.

-- Just some sample data...
CREATE TABLE myTable ( myCol )
  AS VALUES ('fóó bar baz'),('qux quz');

-- No index required, but feel free to create one
CREATE INDEX ON myTable
  USING GIST (to_tsvector('mydict', myCol));

이제 매우 간단하게 쿼리할 수 있습니다.

SELECT *
FROM myTable
WHERE to_tsvector('mydict', myCol) @@ 'foo & bar'

    mycol    
-------------
 fóó bar baz
(1 row)

참고 항목

• 필드에 대/소문자 구분 안 함 및 액센트/분음 부호 구분 안 함 검색 만들기

억양 자체가 없습니다.

또한 모듈은 FTS 통합 없이 자체적으로 사용할 수 있습니다. 이를 위해 Erwin의 답변을 확인하십시오.

저는 Postgre가SQL은 데이터 정렬을 위해 기본 운영 체제를 사용합니다.새 콜리메이션 작성 및 콜리메이션 사용자 정의를 지원합니다.하지만 그게 당신에게 얼마나 많은 일이 될지는 잘 모르겠습니다. (꽤 많을 수도 있습니다.)

언급URL : https://stackoverflow.com/questions/11005036/does-postgresql-support-accent-insensitive-collations