天道酬勤,学无止境

Making unique key case insensitive

This is my schema for mysql table, im using mysql 5

--
-- Table structure for table `DATA_USER_ROLE`
--

DROP TABLE IF EXISTS `DATA_USER_ROLE`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `DATA_USER_ROLE` (
  `ID` int(11) NOT NULL,
  `NAME` varchar(128) NOT NULL,
  `VAL_ID` int(11) NOT NULL,
  `CREATION_TIME` datetime NOT NULL,
  `ROLE_TYPE` int(11) NOT NULL,
  `STORAGE_TYPE` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `BV_AC_ROLE_KEY_IDX` (`NAME`,`VAL_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Needed UNIQUE KEY case in sensitive,

It should allow to enter the value like('a',0) & ('A', 0)

tried changing collation to latin_1 and latin_generic_ci

评论

The simpliest is to add BINARY on the DDL statement,

`NAME` varchar(128) BINARY NOT NULL
  • SQLFiddle Demo

If you need case sensitive:

ALTER TABLE `DATA_USER_ROLE` CHANGE `NAME` `NAME` VARCHAR( 128 )
CHARACTER SET latin1 COLLATE latin1_bin NULL DEFAULT NULL 

If you need case insensitive:

ALTER TABLE `DATA_USER_ROLE` CHANGE `NAME` `NAME` VARCHAR( 128 )
CHARACTER SET latin1 COLLATE latin1_general_ci NULL DEFAULT NULL 

You can do it in such a way..

create table testTable (col_name varchar(100) character set utf8 
collate utf8_bin not null ) Engine=innodb default charset=utf8 

The COLLATE can be added on the whole table, i.e. every text column, e.g.

CREATE TABLE `DATA_USER_ROLE` (
  `ID` int(11) NOT NULL,
  `NAME` varchar(128) NOT NULL,
  `VAL_ID` int(11) NOT NULL,
  `CREATION_TIME` datetime NOT NULL,
  `ROLE_TYPE` int(11) NOT NULL,
  `STORAGE_TYPE` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `BV_AC_ROLE_KEY_IDX` (`NAME`,`VAL_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

This follows Monolune's recommendation for CHARSET and COLLATE from https://www.monolune.com/mysql-utf8-charsets-and-collations-explained/ and
https://www.monolune.com/what-is-the-utf8mb4_0900_ai_ci-collation/

These are the defauts since MySQL 8.0.1 .

Just use utf8_general_ci (case insensitive) charset by default for your db or only for one row, if you needed

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.

相关推荐