CODEWITHSUNDEEP
mysqlcollationcase-sensitive
Andi Keikha
Andi Keikha

Reputation: 1316

How to change the collation to make mysql case insensitive

I found out that when I query one of my tables it is case sensitive, so I tried to change the collation (I'm using Workbench in windows). I 

    right clicked on the table -> alter table -> collation 
-> changed from utf8mb4_default to utf8mb4_general_ci

But it didn't work and the queries are still case sensitive. and when I 

right click on the table -> alter table -> collation

is utf8mb4_default

and when I change it to utf8mb4_general_ci again, and apply the change, it says no changes detected!

The column type is VARBINARY, I tried this:

MySQL case insensitive search on varbinary field?

but it takes a lot of time, it is not acceptable.

This is t create statement:

CREATE TABLE `page` (
  `page_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `page_namespace` int(11) NOT NULL DEFAULT '0',
  `page_title` varbinary(255) NOT NULL DEFAULT '',
  `page_restrictions` tinyblob NOT NULL,
  `page_counter` bigint(20) unsigned NOT NULL DEFAULT '0',
  `page_is_redirect` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `page_is_new` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `page_random` double unsigned NOT NULL DEFAULT '0',
  `page_touched` varbinary(14) NOT NULL DEFAULT '',
  `page_links_updated` varbinary(14) DEFAULT NULL,
  `page_latest` int(8) unsigned NOT NULL DEFAULT '0',
  `page_len` int(8) unsigned NOT NULL DEFAULT '0',
  `page_content_model` varbinary(32) DEFAULT NULL,      
  PRIMARY KEY (`page_id`),
  UNIQUE KEY `name_title` (`page_namespace`,`page_title`),
  KEY `page_random` (`page_random`),
  KEY `page_len` (`page_len`),
  KEY `page_redirect_namespace_len` (`page_is_redirect`,`page_namespace`,`page_len`),
  KEY `idx_page_page_is_new` (`page_is_new`),
  KEY `idx_page_page_title_is_new` (`page_title`,`page_is_new`)
) ENGINE=InnoDB AUTO_INCREMENT=44062999 DEFAULT CHARSET=utf8mb4;

Any other suggestions?

Upvotes: 3

Views: 2259

Answers (2)

Arash.m.h
Arash.m.h

Reputation: 355

Looks like you have the following options:

  1. Convert your binary column to a none binary text column, using a temp column because binary columns cannot be case in-sensitive
  2. Use the Convert function as the link you mentioned
  3. Use the Lower or Upper methods

If you really want the column be always case in-sensitive, I'd say go for option 1.

Upvotes: 2

Peter Bowers
Peter Bowers

Reputation: 3093

In mysql there is a collation for each column in addition to the overall collation of the table. You will need to change the collation for each individual column.

(I believe the overall table collation determines the default collation if you create a new column, but don't quote me on that.)

Upvotes: 0

Related Questions