Question about string spaces in query conditions in MySQL _ MySQL

BitsCN.com

MySQL's question about string spaces in query conditions

Assume that the current mysql database has a table: sysuser

There is a field: sysUseName

The record content in the field is: robin

See the following two SQL statements:

SELECT * from sysuser s where s. sysUseName = 'Robin'

SELECT * from sysuser s where s. sysUseName = 'Robin space'

SELECT * from sysuser s where s. sysUseName = 'Robin space'

All three statements can query the record.

The official document says that the MySQL checking rule belongs to PADSPACE. comparing CHAR and VARCHAR values ignores trailing spaces, and does not matter with server configuration or MySQL version.

MySQL installation directory has a doc Directory (Windows ), the section "Data Types-> String Types-> The CHAR and VARCHAR Types" in The section describes The problem and related examples. (if The version is different, The chapter numbers may change, see the title): All MySQL collations are of type PADSPACE. this means that all CHAR and VARCHAR values in MySQL are compared without regard to any trailing spaces.

The question is: how do I need to precisely match the content of robin? Suppose there is a login function. I want the user to enter 'Robin 'to log on, but enter 'Robin space' but cannot log on. how can this problem be achieved.

The solution is as follows:

SELECT * from sysuser s where s. sysUseName = BINARY 'Robin'

BINARY is not a function, but a type conversion operator. it is used to force the string following it to be a BINARY string. it can be understood as case-sensitive and exact match during string comparison.

In addition, because some MySQL, especially 4. * used to have inaccurate Chinese search results, binary can be added during the search.

BitsCN.com