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

This article mainly introduces how to solve the problem of where conditional text matching of sql statements in Mysql database. It is very detailed and has a certain reference value. Interested friends must read it!

The problem of where conditional text matching in sql statements in Mysql Database

Problem description

When the field name type is varchar (both CHAR,VARCHAR and TEXT are the same)

Sentence one

Select * from aaa where name='a'

Sentence two

Select * from aaa where name='a'

The result is the same: you can't tell whether there is a space at the end

Recommendations:

1. If you must match exactly (including case and trailing spaces), it is recommended to convert it to binary comparison and use select * from aaa where name=BINARY'a'.

2. If you need to accurately check whether there is a space at the end, but keep the case fuzzy match (the default calibration rule _ ci is not case-sensitive), it is recommended to add length (name) to compare the length to distinguish

3. If you need to check the case accurately, but leave the trailing space ignored, you can use select * from aaa where name='a' collate utf8_cs; (the character is utf8).

4. By default

A 、 where name='a'

B, where name='a'; (with a space at the end)

C 、 where name='A'

B, where name='A'; (with a space at the end)

The result of the match in the four cases is the same.

After consulting the official documents:

All MySQL classifications are of type PAD SPACE. This means that all CHAR,VARCHAR and text values are compared regardless of any trailing spaces. "compare" in this context does not include the LIKE pattern matching operator, whose trailing space is important. For example:

Mysql > CREATE TABLE names (myname CHAR (10))

Query OK, 0 rows affected (0.03 sec)

Mysql > INSERT INTO names VALUES ('Monty')

Query OK, 1 row affected (0.00 sec)

Mysql > SELECT myname = 'Monty', myname =' Monty' FROM names; +-+-+

| | myname = 'Monty' | myname =' Monty' | |

+-+ +

| | 1 | 1 |

+-+ +

1 row in set (0.00 sec)

Mysql > SELECT myname LIKE 'Monty', myname LIKE' Monty' FROM names; +-+-- + |

Myname LIKE 'Monty' | myname LIKE' Monty' |

+-+

| | 1 | 0 |

+-+

1 row in set (0.00 sec)

This is true for all MySQL versions and is not affected by the server SQL mode.

Be careful

For more information about the MySQL character set and classification, see Chapter 10, character set, Classification, Unicode. For more information about storage requirements, see Section 11.7, "data Type Storage requirements."

For cases where trailing padding characters are removed or relatively ignored, inserting into a column value that is only different from the number of trailing padding characters will result in a duplicate key error if the column index requires a unique value. For example, if a table contains' akeys', trying to store'a' will result in a duplicate key error.

The above is all the contents of the article "how to solve the problem of where conditional text matching of sql statements in Mysql database". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.