Why whitespace and empty string is same at mariadb?

Question

As you know, whitespace and empty string is definitly diffrent each other.
BUT mariadb returns true at below code.

SET @whitespace = '   '
SET @emptyStr = '';
SELECT @whitespace, @emptyStr; # '   ', ''
SELECT @whitespace = @emptyStr; # 1
SELECT LENGTH(@whitespace), LENGTH(@emptyStr); # 3, 0

I wonder the criteria of processing comparative syntax at mariadb..

It was diffrent object at python like that..

whitespace = '   '
emptyStr = ''
print(whitespace, emptyStr) # '   ', ''
print(whitespace == emptyStr) # Flase
print(len(whitespace), len(emptyStr)); # 3, 0

Additionally, it returns TRUE when I tried to run ```SELECT @emptyStr IS NULL``` — Jung Sea, May 26 '22 at 05:42
Does this answer your question? [MySQL comparison operator, spaces](https://stackoverflow.com/questions/10495692/mysql-comparison-operator-spaces) — Nick, May 26 '22 at 06:29

score 0 · Answer 1 · answered May 26 '22 at 08:57

By default MariaDB (but also MySQL) use PAD collations, which means that trailing spaces will be ignored, NOPAD collations were introduced in MariaDB 10.2.

If you need to compare with trailing spaces, you have the following options:

use a NOPAD collation:

select("1 " COLLATE "utf8mb3_nopad_bin") = "1";
use LIKE opeator:

select "1 " like "1";
cast to BINARY:

select BINARY "1 " = "1";

The list of available nopad collations can be obtained with

show collation like '%nopad%';

Why whitespace and empty string is same at mariadb?

1 Answers1