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

This article introduces the knowledge of "how Mysql makes field content case-insensitive". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Cause storage to be case-sensitive, but case-insensitive when retrieving. This is also inconsistent by default in Oracle, which uniformly uses case-sensitive encoding. So there is a difference between the default and the oracle database.

1. Insert two records of 'Ab',' ab'

Root@test 09:11:51 > insert into test values (3, 'Ab')

Query OK, 1 row affected (0.01sec)

Root@test 09:12:15 > insert into test values (3 memorials ab')

Query OK, 1 row affected (0.01sec)

Root@test 09:12:39 > select * from test

+-+ +

| | id | name |

+-+ +

| | 1 | aa11 |

| | 2 | 1234 |

| | 3 | Ab |

| | 3 | ab |

+-+ +

4 rows in set (0.00 sec)

two。 Press' ab' to retrieve two records, and if it is Oracle, only one piece of data will be returned.

Root@test 09:12:43 > select * from test where name='ab'

+-+ +

| | id | name |

+-+ +

| | 3 | Ab |

| | 3 | ab |

+-+ +

2 rows in set (0.00 sec)

3. Check that the current encoding format of gbk COLLATION is gbk_chinese_ci, that is, it is case-insensitive and the stored binary code is the same.

So passing 'ab',' aB', into the where clause will be translated into the same code in advance, so two records will be returned.

Root@information_schema 10:13:57 > select * from tables where table_schema='test' and table_name='TEST'\ G

* * 1. Row *

TABLE_CATALOG: def

TABLE_SCHEMA: test

TABLE_NAME: test

TABLE_TYPE: BASE TABLE

ENGINE: InnoDB

VERSION: 10

ROW_FORMAT: Compact

TABLE_ROWS: 5

AVG_ROW_LENGTH: 3276

DATA_LENGTH: 16384

MAX_DATA_LENGTH: 0

INDEX_LENGTH: 0

DATA_FREE: 0

AUTO_INCREMENT: NULL

CREATE_TIME: 2016-08-18 11:14:42

UPDATE_TIME: NULL

CHECK_TIME: NULL

TABLE_COLLATION: gbk_chinese_ci

CHECKSUM: NULL

CREATE_OPTIONS:

TABLE_COMMENT:

Proof is stored in lowercase.

Root@information_schema 10:23:02 > select ascii (name), id from test.test

+-+ +

| | ascii (name) | id | |

+-+ +

| | 97 | 1 |

| | 49 | 2 |

| | 65 | 3 |

| | 97 | 3 |

| | 49 | 12 | |

+-+ +

4. How to set up a database so that field values are case-sensitive: set the encoding mode to a non-default COLLATE=gbk_bin

Root@test 01:34:58 > create table a (name varchar (10)) ENGINE=InnoDB DEFAULT CHARSET=gbk COLLATE=gbk_bin

Query OK, 0 rows affected (0.01 sec)

Root@test 01:35:06 > insert into a values ('a')

Query OK, 1 row affected (0.00 sec)

Root@test 01:35:16 > insert into a values ('A')

Query OK, 1 row affected (0.00 sec)

Root@test 01:35:20 > select * from a where name='a'

+-+

| | name |

+-+

| | a |

+-+

1 row in set (0.00 sec)

Root@test 01:35:30 > select * from a where name='A'

+-+

| | name |

+-+

| | A |

+-+

1 row in set (0.00 sec)

Root@test 01:35:35 > create table b (name varchar (10))

Query OK, 0 rows affected (0.00 sec)

Remarks:

Utf8_bin stores each character in a string in binary data, case-sensitive.

Utf8_genera_ci is not case-sensitive, and ci is an abbreviation for case insensitive, that is, case-insensitive.

Utf8_general_cs is case-sensitive, and cs is an abbreviation for case sensitive, that is, case-sensitive.

This is the end of "how Mysql makes field content case-insensitive". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.