Changing the collation of the connection will have little to no impact on performance, but also on what you probably want to achieve. From the manual:
collation_connection is important for comparisons of literal strings.
For comparisons of strings with column values, collation_connection
does not matter because columns have their own collation, which has a
higher collation precedence
If you want to make comparisons in with or without case sensitivity/accent sensitivity, you will have to update the actual column's collation, or force the collation on every comparison manually (e.g. WHERE ). The latter may have low impact if no index is required, or a huge impact if the usage of an index is prevented because the comparison and the column use different collation.
In practice:
mysql> SET NAMES utf8mb4; -- we will be using this charset to send literals
mysql> SELECT 'A' = 'a';
+-----------+
| 'A' = 'a' |
+-----------+
| 1 |
+-----------+
1 row in set (0.000 sec)
mysql> SET SESSION collation_connection = 'utf8mb4_bin';
Query OK, 0 rows affected (0.000 sec)
mysql> SELECT 'A' = 'a'; -- it works on literals, but you will have to force it manually on columns
+-----------+
| 'A' = 'a' |
+-----------+
| 0 |
+-----------+
1 row in set (0.000 sec)
mysql> MariaDB [(none)]> SELECT 'A' = 'a' COLLATE utf8mb4_bin; -- may prevent index usage on columns
+-------------------------------+
| 'A' = 'a' COLLATE utf8mb4_bin |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (0.000 sec)
mysql> select 'A' = 'a' COLLATE utf8mb4_unicode_520_ci; -- may prevent index usage on columns
+------------------------------------------+
| 'A' = 'a' COLLATE utf8mb4_unicode_520_ci |
+------------------------------------------+
| 1 |
+------------------------------------------+
1 row in set (0.004 sec)