Mysql works strangely with strings of spaces

Question:

1) SELECT SUBSTR(' ',1,1)='';

Such a request will return an answer of 1, and not 0, as it seems intuitively.

2) CREATE TABLE symbols (id INT AUTO_INCREMENT, ch CHAR(1)); INSERT INTO symbols (ch) VALUES (' ');

(a) SELECT * FROM symbols WHERE ch=''; The answer will be: id = 1, ch = ''

(b) SELECT * FROM symbols WHERE ch=' '; The answer will be: id = 1, ch = ''

(c) SELECT * FROM symbols WHERE ch=' '; The answer will be: id = 1, ch = ''

3) The functions RIGHT (), SUBSTR (), LOCATE () also behave in a similar non-obvious way.

4) Replacing the field type CHAR with VARCHAR in example (2) does not change the strange behavior of Mysql.

Maybe there are some nuances in setting up the config file, using COLLATE, which will allow you to work with a space in exactly the same way as with other characters?

PS MySQL 5.7, table encoding is utf8, config is almost standard.

Answer:

To summarize from the comments:

  1. Space is not stored in the CHAR field. They suggest replacing CHAR with VARCHAR (1) as a solution.

  2. It is advised here to add BINARY

     SELECT * FROM symbols WHERE BINARY ch = ' ';
Scroll to Top