MySQL과 Postgres 모두에 대해 대소 문자를 구분하지 않는 쿼리를 어떻게 작성합니까?

MySQL과 Postgres 모두에 대해 대소 문자를 구분하지 않는 쿼리를 어떻게 작성합니까?

---

개발을 위해 로컬에서 MySQL 데이터베이스를 실행하고 있지만 Postgres를 사용하는 Heroku에 배포합니다. Heroku는 거의 모든 것을 처리하지만 대소 문자를 구분하지 않는 Like 문은 대소 문자를 구분합니다. iLike 문을 사용할 수는 있지만 로컬 MySQL 데이터베이스는이를 처리 할 수 ​​없습니다.

MySQL 및 Postgres와 호환되는 대소 문자를 구분하지 않는 쿼리를 작성하는 가장 좋은 방법은 무엇입니까? 아니면 내 앱이 말하는 DB에 따라 별도의 Like 및 iLike 문을 작성해야합니까?

---

select * from foo where upper(bar) = upper(?);

호출자에서 매개 변수를 대문자로 설정하면 두 번째 함수 호출을 피할 수 있습니다.

---

이 이야기의 교훈은 다음과 같습니다. 개발 및 생산을 위해 다른 소프트웨어 스택을 사용하지 마십시오. 못.

여러분은 dev에서 재현 할 수없는 버그로 끝날 것입니다. 당신의 테스트는 쓸모가 없을 것입니다. 그냥 하지마.

다른 데이터베이스 엔진을 사용하는 것은 의문의 여지가 없습니다. LIKE와 다르게 동작하는 경우가 훨씬 더 많을 것입니다 (또한 데이터베이스에서 사용중인 데이터 정렬을 확인 했습니까? 모든 경우에서 동일합니까? 그렇지 않은 경우 가능) 동일하게 작동하는 varchar 열에서 ORDER BY를 잊어 버림)

---

Arel 사용 :

Author.where(Author.arel_table[:name].matches("%foo%"))

matchesILIKEPostgres 및 LIKE기타 모든 작업에 연산자를 사용합니다 .

---

postgres에서는 다음을 수행 할 수 있습니다.

SELECT whatever FROM mytable WHERE something ILIKE 'match this';

MySQL에 상응하는 것이 있는지 확실하지 않지만 항상 약간 추악하지만 MySQL과 postgres 모두에서 작동 해야하는 이것을 할 수 있습니다.

SELECT whatever FROM mytable WHERE UPPER(something) = UPPER('match this');

---

몇 가지 답변이 있지만 그중 어느 것도 매우 만족스럽지 않습니다.

• (?) LOWER (바) = LOWER는 것입니다 작동 MySQL과 포스트 그레스에,하지만 가능성이 MySQL을 몹시 수행 : MySQL이 때문에 LOWER 기능의 해당 인덱스를 사용하지 않습니다. Postgres에서는 기능 색인 ( LOWER (bar) )을 추가 할 수 있지만 MySQL은이를 지원하지 않습니다.
• MySQL은 (대소 문자 구분 데이터 정렬 을 설정하지 않는 한 ) 자동으로 대소 문자를 구분하지 않고 색인을 사용합니다. ( bar =? ).
• 데이터베이스 외부의 코드에서 bar 및 bar_lower 필드를 유지하십시오 . 여기서 bar_lower에는 lower (bar) 의 결과가 포함 됩니다. (데이터베이스 트리거를 사용하여 가능할 수도 있습니다.) ( Drupal 에서이 솔루션에 대한 설명을 참조하십시오 .) 이것은 서 투르지만 적어도 거의 모든 데이터베이스에서 동일한 방식으로 실행됩니다.

---

REGEXP는 대소 문자를 구분하지 않으며 (BINARY와 함께 사용하지 않는 한) 다음과 같이 사용할 수 있습니다.

    SELECT id FROM person WHERE name REGEXP 'john';

... 'John', 'JOHN', 'john'등과 일치합니다.

---

PostgreSQL 8.4를 사용하는 경우 citext 모듈을 사용하여 대소 문자를 구분하지 않는 텍스트 필드를 만들 수 있습니다.

---

COLLATE를 사용하십시오.

http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

---

LIKE / ILIKE 스위치를 사용 하는 searchlogic 플러그인을 확인하는 것도 고려할 수 있습니다 .

---

블록 내의 하위 문자열을 일치 시키려면 postgres에서 ~ *를 사용할 수도 있습니다. ~는 대소 문자를 구분하는 하위 문자열, ~ * 대소 문자를 구분하지 않는 하위 문자열과 일치합니다. 느린 작업이지만 검색에 유용 할 수 있습니다.

Select * from table where column ~* 'UnEvEn TeXt';
Select * from table where column ~ 'Uneven text';

둘 다 "Some Uneven text here"를 쳤을 것입니다. 전자 만이 "Some UNEVEN TEXT here"를 쳤을 것입니다.

---

가장 많이 사용되는 3 개의 Rails 데이터베이스 백엔드에 대한 호환 구문을 다루기 때문에 upper로 변환하는 것이 가장 좋습니다. PostgreSQL, MySQL 및 SQLite는 모두이 구문을 지원합니다. 응용 프로그램이나 조건 문자열에서 검색 문자열을 대문자로 바꿔야한다는 (사소한) 단점이있어 좀 더보기 좋지만, 얻을 수있는 호환성이 가치가 있다고 생각합니다.

MySQL과 SQLite3에는 모두 대소 문자를 구분하지 않는 LIKE 연산자가 있습니다. PostgreSQL에만 대소 문자를 구분하는 LIKE 연산자와 대소 문자를 구분하지 않는 검색을위한 PostgreSQL 별 (수동 기준) ILIKE 연산자가 있습니다. Rails 애플리케이션의 조건에서 LIKE의 ILIKE insead를 지정할 수 있지만 애플리케이션은 MySQL 또는 SQLite에서 작동하지 않습니다.

세 번째 옵션은 사용중인 데이터베이스 엔진을 확인하고 그에 따라 검색 문자열을 수정하는 것입니다. ActiveRecord의 연결 어댑터를 해킹 / 원키 패칭하여 더 잘 수행 할 수 있으며 PostgreSQL 어댑터가 쿼리 실행 전에 "ILIKE"를 "LIKE"로 대체하도록 쿼리 문자열을 수정하도록 할 수 있습니다. 그러나이 솔루션은 가장 복잡하고 두 용어를 모두 대문자로 쓰는 것과 같은 쉬운 방법을 고려할 때 노력이 필요하지 않다고 생각합니다 (이 방법으로 많은 브라우니 포인트를 얻 겠지만).

참고 URL : https://stackoverflow.com/questions/203399/how-do-you-write-a-case-insensitive-query-for-both-mysql-and-postgres