0

I am used to doing a case-insensitive match in mysql on utf8 fields, such as:

select * from connections where full_name like '%david%'

However, it seems that utf8mb4 does not support case-insensitive searches. How would I do the same against that in mysql?

The table I have is:

CREATE TABLE `connections` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` varchar(120) NOT NULL DEFAULT '',
  `full_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7160 DEFAULT CHARSET=utf8;
David542
  • 104,438
  • 178
  • 489
  • 842
  • 1
    What's the collation of the `name` field? – zerkms Dec 13 '18 at 19:31
  • @zerkms updated the question with a table definition. – David542 Dec 13 '18 at 19:34
  • `utf8mb4_bin` collation is not case insensitive. https://stackoverflow.com/q/5526334/251311 – zerkms Dec 13 '18 at 19:36
  • @zerkms I see -- so basically if a field supports emojis in mysql it cannot do case-insensitive searching? Or is there an alternative approach to get case-insensitive matching with emojis? – David542 Dec 13 '18 at 19:40
  • 1
    You can change `COLLATE` on runtime `SELECT * FROM full_name COLLATE utf8mb4_unicode_ci LIKE '%david%'` This should pick out `David` or `david` see [demo](https://www.db-fiddle.com/f/7y7kbji7w8o5nKz5P6b83e/1) i would place this as a answer if the topic wasn't closed. – Raymond Nijland Dec 13 '18 at 19:40
  • @RaymondNijland perfect, this is what I was looking for with the answer. Thank you. – David542 Dec 13 '18 at 19:41
  • 1
    @David542 they can. Charset and collation are tangential concepts https://dev.mysql.com/doc/refman/8.0/en/adding-collation.html – zerkms Dec 13 '18 at 19:41
  • utf8mb4_unicode_520_ci is "Case Insensitive" and (I think) also distinguishes Emoji. Other `COLLATIONs` may be different. You must use `CHARACTER SET utf8mb4` to handle Emoji. – Rick James Dec 14 '18 at 05:31

0 Answers0