Clever Geek Handbook

How to change 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 use the Workbench on Windows). I

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

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

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

- utf8mb4_default

and when I changed it back to utf8mb4_general_ci

and applied this change, it says no changes were detected!

Column type is VARBINARY, I tried this:

MySQL insensitive search on varbinary field?

but it takes a long time, it is not acceptable.

This is the t 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?

+3


source to share


2 answers


It looks like you have the following possibilities:

  • Convert your binary column to some kind of binary text column using temp column because binary columns cannot be case sensitive
  • Use the Convert function like the link you mentioned
  • Use the Lower or Upper methods


If you really want the column to always be case sensitive, I would say go to option 1.

+2


source


Mysql has a collation for every column in addition to general table collation. You will need to change the sort for each individual column.



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

0


source






More articles:


All Articles