• I have a first column typed as varchar(190) that is using utf8mb4_bin collation.

When I perform the following query I only get back all of Joe as expected:

SELECT first, last FROM person WHERE first = 'Joe'

What I would like to get is Joe, joe, jOe, joE, jOE, JoE, JOE, and JOe. Basically a case-insensitive search on a case-sensitive field.

How do I do this?

CREATE TABLE `person` (
  `id` int NOT NULL AUTO_INCREMENT,
  `first` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `middle` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `last` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `job` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `names_unq` (`first`,`middle`,`last`,`job`),
  KEY `index_job` (`job`),
  KEY `index_first` (`first`,`job`),
  KEY `index_first_last` (`first`,`last`,`job`),
  KEY `index_middle` (`middle`,`job`),
  KEY `index_last` (`job`,`last`)
) ENGINE=InnoDB AUTO_INCREMENT=99750823 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CodePudding user response：

You can specify a collation in a string comparison expression to override the collation used in the comparison. Read https://dev.mysql.com/doc/refman/8.0/en/charset-literal.html for more details on this.

CREATE TABLE `person` (
  `first` text COLLATE utf8mb4_bin,
  `last` text COLLATE utf8mb4_bin
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

mysql> select first, last from person where first = 'Joe';
 ------- ------- 
| first | last  |
 ------- ------- 
| Joe   | Grant |
 ------- ------- 

mysql> select first, last from person where first = 'joe';
Empty set (0.00 sec)

mysql> select first, last from person where first = 'joe' collate utf8mb4_unicode_ci;
 ------- ------- 
| first | last  |
 ------- ------- 
| Joe   | Grant |
 ------- ------- 

CodePudding user response：

use "collate utf8mb4_unicode_ci" as it makes one-to-one comparison with character to whatever the filter condition you have given.

CodePudding user response：

The simplest way to do this is to use UPPER().

SELECT first, last FROM person WHERE UPPER(first) = 'JOE';

If you are matching a parameter you might need to use upper on both side as in

SELECT first, last FROM person WHERE UPPER(first) = UPPER(@name);