Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to solve the case-insensitive problem of mysql query field content, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

About the problem that the contents of query fields are not case-sensitive.

Root@localhost 20:32: [pwb_inn] > select * from sys_account_login where corp_code='TEST'

| | ID | ACCOUNT_ID | LOGIN_NAME | LOGIN_PASS | REALNAME | EMAIL | STATUS | USER_TYPE | BIRTHDAY | SEX | REMARK | LOGIN_COUNT | LAST_IP | LAST_LOGIN_TIME | MOBILE | ID_CARD | CREATE_LOGIN_ID | GROUP_ID | CREATE_TIME | MODIFIED_TIME | DELETED | CORP_CODE |

| | 0 | 1 | admin | 9db06bcff9248837f86d1a6bcf41c9e7 | lwwww122211 | xxxxx.qq.1111 | 1 | 0 | NULL | NULL | NULL | 555 | 192.168.60.197 | 2017-06-01 20:27:37 | 1300000000012222 | NULL | NULL | NULL | 2015-03-30 19:52:25 | 2017-06-01 20:27:40 | 0 | test |

| | 18 | NULL | wangcx | 9db06bcff9248837f86d1a6bcf41c9e7 | wangcx1 | chixu.wang@zhiyoubao.com | 1 | 2 | NULL | 0 | NULL | 1300000000 | | NULL | NULL | 2017-05-18 14:36:24 | 2017-05-18 15:06:55 | 1 | test |

| | 19 | 1 | 11 | 28c8edde3d61a0411511d3b1866f0636 | 12 | 12 | 1 | 2 | NULL | 0 | NULL | NULL | | NULL | 12 | NULL | NULL | 2017-05-18 14:50:58 | 2017-05-18 15:06:55 | 1 | test |

| | 20 | 1 | 3 | 38026ed22fc1a91d92b5d2ef93540f20 | 3 | 3 | 1 | 2 | NULL | 0 | NULL | NULL | | NULL | 3 | NULL | NULL | 2017-05-18 14:52:38 | 2017-05-18 15:06:55 | 1 | test |

| | 21 | 1 | 1111112 | 3ed80171b1f4ab825f2038fc203c887c | 1 | 1 | 1 | 2 | NULL | 0 | NULL | NULL | | NULL | 1 | NULL | NULL | 2017-05-18 14:56:31 | 2017-05-18 15:06:55 | 1 | test |

| | 22 | 1 | wangcx222 | 63ee451939ed580ef3c4b6f0109d1fd0 | 211111 | | 1 | 2 | NULL | 0 | NULL | NULL | | NULL | 2017-05-18 15:32:54 | 2017-05-18 15:34:25 | 1 | test |

| | 23 | 1 | wangcx | 14e1b600b1fd579f47433b88e8d85291 | wangcx | | 1 | 2 | NULL | 0 | NULL | 1 | 192.168.60.197 | 2017-05-27 10:03:13 | NULL | NULL | 2017-05-27 10:02:47 | 2017-05-27 10:03:23 | 0 | test |

| | 24 | 1 | wangqq | 9db06bcff9248837f86d1a6bcf41c9e7 | Wang Qiangqiang | 451766647@qq.com | 1 | 2 | NULL | 0 | NULL | 13639887172 | | NULL | NULL | 2017-05-31 09:40:56 | 2017-05-31 10:36:49 | 1 | test |

| | 25 | 1 | wangqq | 9db06bcff9248837f86d1a6bcf41c9e7 | Wang Qiangqiang | | 0 | 2 | NULL | 0 | NULL | 5 | 192.168.60.103 | 2017-05-31 16:46:49 | 13632145698 | | NULL | NULL | 2017-05-31 10:37:25 | 2017-05-31 16:59:42 | 1 | test |

| | 26 | 1 | wangqq | 9db06bcff9248837f86d1a6bcf41c9e7 | wangqq | | 1 | 2 | NULL | 0 | NULL | 8 | 192.168.60.103 | 2017-06-01 10:37:46 | NULL | NULL | 2017-06-01 09:33:22 | 2017-06-01 10:37:50 | 0 | test |

Query uppercase but lowercase also shows

Suspected character set problem

Root@localhost 20:29: [pwb_inn] > show create table sys_account_login\ G

* * 1. Row *

Table: sys_account_login

Create Table: CREATE TABLE `sys_account_ login` (

`ID`varchar (20) NOT NULL COMMENT 'SEQENCE'

`ACCOUNT_ ID`varchar (20) DEFAULT NULL COMMENT 'account information primary key'

`NAME`varchar (LOGIN_) DEFAULT NULL COMMENT 'login name'

`PASS`varchar (LOGIN_) DEFAULT NULL COMMENT 'login password'

`REALNAME` varchar (128) DEFAULT NULL COMMENT 'real name'

`EMAIL` varchar (128) DEFAULT NULL COMMENT 'email'

`STATUS` varchar (64) DEFAULT''COMMENT' user status 0: locked 1: normal\ r\ n'

`TYPE` varchar (16) NOT NULL DEFAULT'0' COMMENT 'user type: 1 Shenzhen University Guest Service 2 Inn Manager 3 Inn operator (after the second phase of the change, only type 2, that is, supplier login, 0 is the business owner account and cannot be deleted)'

`BIRTHDAY`datetime DEFAULT NULL COMMENT 'birthday yyyy-MM-dd'

`SEX`varchar (16) DEFAULT'0' COMMENT'1 male, 0 female'

`REMARK` varchar (1024) DEFAULT NULL COMMENT 'remarks'

`LOGIN_ COUNT`varchar (20) DEFAULT NULL COMMENT 'login times + 1' per login

`LAST_ IP` varchar (64) DEFAULT''COMMENT' Last login to IP'

`TIME`datetime DEFAULT NULL COMMENT 'LAST_LOGIN_ last login time'

`MOBILE`varchar (64) DEFAULT''COMMENT' mobile phone number'

`ID_ CARD`varchar (64) DEFAULT''COMMENT' ID number'

`CREATE_LOGIN_ ID`varchar (20) DEFAULT NULL COMMENT 'user creator'

`GROUP_ ID`varchar (20) DEFAULT NULL

`TIME`datetime DEFAULT NULL COMMENT 'CREATE_ time'

`TIME`datetime DEFAULT NULL COMMENT 'MODIFIED_ time'

`DELETED` smallint (6) NOT NULL DEFAULT'0' COMMENT 'deletion flag 0: not deleted 1: deleted'

`CORP_ Code`varchar (32) NOT NULL COMMENT 'Enterprise Code (Phase II)'

PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

The last query is the problem of character set proofreading.

Mysql collate rules:

* _ bin: indicates binary case sensitive collation, that is, it is case-sensitive

* _ cs: case sensitive collation, case sensitive

* _ ci: case insensitive collation, case-insensitive

Modify proofreading rules

Root@localhost 20:44: [pwb_inn] > ALTER TABLE sys_account_login CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_cs

ERROR 1273 (HY000): Unknown collation: 'utf8_general_cs'

5.6 utf8_general_cs is not supported

Root@localhost 20:44: [pwb_inn] > ALTER TABLE sys_account_login CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

Root@localhost 20:44: [pwb_inn] > ALTER TABLE sys_account_login CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin

Query OK, 17 rows affected (0.01sec)

Records: 17 Duplicates: 0 Warnings: 0

Root@localhost 20:45: [pwb_inn] > select * from sys_account_login where corp_code='TEST'

Empty set (0.00 sec)

On how to solve the mysql query field content can not be case-sensitive problem to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.