Is "SET CHARACTER SET utf8" necessary?

user_138023 0
Viewed 38343

I´m rewritting our database class (PDO based), and got stuck at this. I´ve been taught to both use SET NAMES utf8 and SET CHARACTER SET utf8 when working with UTF-8 in PHP and MySQL.

In PDO I now want to use the PDO::MYSQL_ATTR_INIT_COMMAND parameter, but it only supports one query.

Is SET CHARACTER SET utf8 necessary?

pdo utf-8 mysql php
4 Answers

The answer many people are looking for is if these queries are needed at all.

As stated in the docs:

If you want the client program to communicate with the server using a character set different from the default, you'll need to indicate which one.

Note that this is an if clause. It means these queries are needed only if you want to use a charset different from the default your MySQL is using. Doing the same query over and over if not needed like this is a useless waste of resources and should be avoided:

enter image description here

At the time of writing (MySQL 8.0.29) the default MySQL server character set is utf8mb4 and there are no plans to change it for the foreseeable future. You should first check what the current values are, if you get results like these, these queries can be safely removed:

mysql> select @@character_set_client;
+------------------------+
| @@character_set_client |
+------------------------+
| utf8mb4                |
+------------------------+
1 row in set (0.00 sec)

mysql> select @@character_set_connection;
+----------------------------+
| @@character_set_connection |
+----------------------------+
| utf8mb4                    |
+----------------------------+
1 row in set (0.00 sec)

mysql> select @@character_set_results;
+-------------------------+
| @@character_set_results |
+-------------------------+
| utf8mb4                 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select @@collation_connection;
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_0900_ai_ci     |
+------------------------+
1 row in set (0.00 sec)

mysql> select @@collation_database;
+----------------------+
| @@collation_database |
+----------------------+
| utf8mb4_0900_ai_ci   |
+----------------------+
1 row in set (0.00 sec)

If you get different results but you control the MySQL configuration, you should change them in the MySQL config files, unless you have different applications requiring different charset. (But nowadays utf8mb4 is the standard and there are very few valid reasons to use a different charset)

user_33936630
Related