Why whitespace and empty string is same at mariadb?-CodePudding

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

CodePudding user response：

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%';

CodePudding user response：

I am not clear but maybe

SET @whitespace = '\u00A0'