Different behavior with MySQL 5.7 and 8.0

Question

I'm trying to use MySQL 8.0 but I'm having some problems. I have installed MySQL 5.7 and 8.0, and have different behavior with CHAR columns.

For MySQL 5.7:

mysql> create table test (id integer, c5 char(5));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(0, 'a');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test where c5 = 'a    ';
+------+------+
| id   | c5   |
+------+------+
|    0 | a    |
+------+------+
1 row in set (0.00 sec)

mysql>

For MySQL 8.0:

mysql> create table test (id integer, c5 char(5));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values(0, 'a');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test where c5 = 'a    ';
Empty set (0.00 sec)

mysql>

Both servers have same configuration.

MySQL 5.7:

[mysqld]
port=3357
datadir=/opt/mysql_57/data
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
default_storage_engine=innodb
character-set_server=utf8mb4
socket=/opt/mysql_57/mysql57.sock
max_allowed_packet=4194304
server_id=1
lower_case_table_names=0

MySQL 8.0:

[mysqld]
port=3380
datadir=/opt/mysql_80/data
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
default_storage_engine=innodb
character-set_server=utf8mb4
socket=/opt/mysql_80/mysql80.sock
max_allowed_packet=4194304
server_id=1
lower_case_table_names=0

A brief overview of the MySQL 8.0 changelog didn't give me any information. Where described this behavior changes?

Best regards.

Answer 1

How MySQL handled trailing spaces, depends on the collation being used. See https://dev.mysql.com/doc/refman/8.0/en/charset-binary-collations.html for details.

What has changed between 5.7 and 8.0, is that the default character set is now UTF8mb4 with NOPAD collations.

If you want another behavior, you should change character set/collation for your column/table/database. Check the INFORMATION_SCHEMA table COLLATIONS for available PAD collations. (One warning: The older PAD SPACE collations may be less efficient. Quite some work has been made to improve the performance of the new Unicode collations based on UCA 9.0.0.)

Answer 2

See PAD_CHAR_TO_FULL_LENGTH in MySQL documentation