[mysql] MySQL에서 SQL 대소 문자 구분 문자열 비교를 어떻게 할 수 있습니까?

Program / 글쓴이

대소 문자를 혼합하여 5자를 반환하는 함수가 있습니다. 이 문자열에서 쿼리를 수행하면 대소 문자에 관계없이 값을 반환합니다.

MySQL 문자열 쿼리를 대소 문자를 구분하려면 어떻게해야합니까?

답변

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

기본 문자 집합 및 데이터 정렬은 latin1 및 latin1_swedish_ci이므로 이진 문자열 비교는 기본적으로 대소 문자를 구분하지 않습니다. 즉, col_name LIKE ‘a %’로 검색하면 A 또는 a로 시작하는 모든 열 값을 얻게됩니다. 이 검색에서 대소 문자를 구분하려면 피연산자 중 하나에 대소 문자 구분 또는 이진 데이터 정렬이 있어야합니다. 예를 들어 latin1 문자 집합이있는 열과 문자열을 비교하는 경우 COLLATE 연산자를 사용하여 피연산자가 latin1_general_cs 또는 latin1_bin 데이터 정렬을 갖도록 할 수 있습니다.

col_name COLLATE latin1_general_cs LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_general_cs
col_name COLLATE latin1_bin LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_bin

열을 항상 대 / 소문자를 구분하여 처리하려면 대 / 소문자를 구분하거나 이진 데이터 정렬을 사용하여 선언하십시오.

답변

좋은 소식은 대소 문자를 구분하는 쿼리를 작성해야 할 경우 매우 쉽다는 것입니다.

SELECT *  FROM `table` WHERE BINARY `column` = 'value'

답변

답변을 Craig White에 의해 게시 함 

SELECT *  FROM `table` WHERE BINARY `column` = 'value'

인덱스를 사용하지 않기 때문입니다. 따라서 https://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html에서 언급 한 것처럼 테이블 데이터 정렬을 변경해야합니다 .

또는

가장 쉬운 수정은 BINARY 값을 사용해야합니다.

SELECT *  FROM `table` WHERE `column` = BINARY 'value'

예 : 

mysql> EXPLAIN SELECT * FROM temp1 WHERE BINARY col1 = "ABC" AND col2 = "DEF" ;
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | temp1  | ALL  | NULL          | NULL | NULL    | NULL | 190543 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

VS 

mysql> EXPLAIN SELECT * FROM temp1 WHERE col1 = BINARY "ABC" AND col2 = "DEF" ;
+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+
| id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra                              |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+
|  1 | SIMPLE      | temp1 | range | col1_2e9e898e | col1_2e9e898e | 93      | NULL |    2 | Using index condition; Using where |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+
enter code here

1 행 세트 (0.00 초)

답변

= 연산자를 사용하는 대신 LIKE 또는 LIKE BINARY를 사용할 수 있습니다. 

// this returns 1 (true)
select 'A' like 'a'

// this returns 0 (false)
select 'A' like binary 'a'


select * from user where username like binary 'a'

상태가 ‘A’가 아닌 ‘a’를 사용합니다.

답변

BINARY를 사용하기 전에 인덱스를 사용하려면 큰 테이블이있는 경우 이와 같은 작업을 수행 할 수 있습니다.

SELECT
   *
FROM
   (SELECT * FROM `table` WHERE `column` = 'value') as firstresult
WHERE
   BINARY `column` = 'value'

하위 쿼리는 대소 문자를 구분하지 않는 작은 부분 집합을 생성하며 대소 문자를 구분하는 유일한 일치 항목을 선택합니다.

답변

쿼리중인 열의 데이터 정렬을 변경하지 않고 대 / 소문자를 구분하는 문자열 비교를 수행하는 가장 올바른 방법은 열을 비교할 값의 문자 집합과 데이터 정렬을 명시 적으로 지정하는 것입니다.

select * from `table` where `column` = convert('value' using utf8mb4) collate utf8mb4_bin;

왜 사용하지 binary않습니까?

binary인코딩 된 문자열의 실제 바이트를 비교하므로 연산자를 사용 하지 않는 것이 좋습니다. 다른 문자 집합을 사용하여 인코딩 된 두 문자열의 실제 바이트를 비교할 경우 동일한 것으로 간주되어야하는 두 문자열은 같지 않을 수 있습니다. 예를 들어 latin1문자 집합 을 사용하는 열이 있고 서버 / 세션 문자 집합이 utf8mb4인 경우 열을 ‘café’와 같은 악센트가 포함 된 문자열과 비교하면 동일한 문자열이 포함 된 행과 일치하지 않습니다! 이는 latin1é에서 바이트로 인코딩 0xE9되지만 utf82 바이트 이기 때문입니다 0xC3A9.

왜 convert뿐만 아니라 사용 collate합니까?

데이터 정렬은 문자 집합과 일치해야합니다. 따라서 서버 나 세션이 latin1문자 세트를 사용하도록 설정된 collate latin1_bin경우 사용해야 하지만 문자 세트가 utf8mb4있는 경우을 사용해야 collate utf8mb4_bin합니다. 따라서 가장 강력한 솔루션은 항상 값을 가장 유연한 문자 집합으로 변환하고 해당 문자 집합에 이진 데이터 정렬을 사용하는 것입니다.

열이 아닌 값에 convertand collate를 적용하는 이유는 무엇 입니까?

비교하기 전에 열에 변환 함수를 적용하면 열에 대한 인덱스 엔진이 있으면 쿼리 엔진에서 인덱스를 사용하지 못하게되어 쿼리 속도가 크게 느려질 수 있습니다. 따라서 가능한 경우 항상 값을 변환하는 것이 좋습니다. 두 문자열 값간에 비교가 수행되고 그 중 하나에 명시 적으로 지정된 데이터 정렬이있는 경우 쿼리 엔진은 적용되는 값에 관계없이 명시 적 데이터 정렬을 사용합니다.

악센트 감도

MySql은 _ci데이터 정렬 (일반적으로 기본값)을 사용하는 열에 대해 대소 문자를 구분하지 않을 뿐만 아니라 악센트를 구분하지 않습니다. 이것은 의미합니다 'é' = 'e'. 이진 데이터 정렬 사용binary 연산자)을 사용하면 문자열 비교가 악센트와 대소 문자를 구분합니다.

무엇입니까 utf8mb4?

utf8MySql 의 문자 집합은 4 바이트 문자를 지원하지 않기 때문에 최근 버전에서 더 이상 사용되지 않는 별칭입니다. utf8mb3이는 ?과 같은 문자열을 인코딩하는 데 중요합니다. 당신이 사용하고자하는 경우에는 UTF8 문자 인코딩 MySQL과를 당신이 사용되어야한다 캐릭터 세트를.utf8mb4

답변

다음은 5.5 이상의 MySQL 버전입니다.

/etc/mysql/my.cnf에 추가

  [mysqld]
  ...
  character-set-server=utf8
  collation-server=utf8_bin
  ...

내가 시도한 다른 모든 데이터 정렬은 대소 문자를 구분하지 않는 것으로 보였으며 “utf8_bin”만 작동했습니다.

이 후에 mysql을 다시 시작하는 것을 잊지 마십시오 :

   sudo service mysql restart

http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html 에 따르면 “latin1_bin”도 있습니다.

“utf8_general_cs”는 mysql 시작에 의해 받아 들여지지 않았다. ( “_cs”를 “대소 문자 구분”-???)로 읽습니다.