The problem is that there is a database with many tables and a lot of existing data. most of the data is stored in utf8 (incomplete, and latin1 ). The storage fails due to the user's emoji storage. it is known that utf8mb4 is required for proper storage. The problem with mysql... is as follows:
There is a database with many tables and a lot of existing data. most of the data is stored in utf8 (not all, and latin1 ).
The storage fails due to the user's emoji storage. it is known that utf8mb4 is required for proper storage.
Mysql has been upgraded from 5.1 to 5.6.26. The following ideas and existing attempts:
1. if the whole database is updated to uft8mb4, the risk is too high. because of the mess of various calls, poor maintenance, and direct conversion to utf8mb4, the entire system may be abnormal.
2. Consider storing only the table (hereinafter referred toTable E) Updated to uft8mb4. However, thinkphp is used in the system, and the encoding settings are placed in config. php. if you modify the settings, thinkphp will be completely destroyed.
3. if thinkphp is still subject to uft8Table EUse uft8mb4 for query. TryM()->query('SET NAMES \'utf8mb4\'');
Error:SQLSTATE[HY000]: General error
4. the method 2 is still considered and cannot be found. Because even if thisTable EAfter the operation is successful, other table operations are performed. uft8mb4 may cause the following query errors.
Is there a good way to perform utf8mb4 encoding only for this table without affecting other table operations.
PS: Since the production environment has been online for a year and there are already a lot of data and online users, this problem is reported only after testing. The risk of updating the encoding of the database as a whole is too high. The leader means that if there are any pitfalls, they will pretend to be invisible.
Fortunately, I installed the production environment when I set up the system. mysql was the latest version 5.6.26 at the beginning. The method for re-Transcoding of emoji is only used as the final solution.
Reply content:
The problem is as follows:
There is a database with many tables and a lot of existing data. most of the data is stored in utf8 (not all, and latin1 ).
The storage fails due to the user's emoji storage. it is known that utf8mb4 is required for proper storage.
Mysql has been upgraded from 5.1 to 5.6.26. The following ideas and existing attempts:
1. if the whole database is updated to uft8mb4, the risk is too high. because of the mess of various calls, poor maintenance, and direct conversion to utf8mb4, the entire system may be abnormal.
2. Consider storing only the table (hereinafter referred toTable E) Updated to uft8mb4. However, thinkphp is used in the system, and the encoding settings are placed in config. php. if you modify the settings, thinkphp will be completely destroyed.
3. if thinkphp is still subject to uft8Table EUse uft8mb4 for query. TryM()->query('SET NAMES \'utf8mb4\'');
Error:SQLSTATE[HY000]: General error
4. the method 2 is still considered and cannot be found. Because even if thisTable EAfter the operation is successful, other table operations are performed. uft8mb4 may cause the following query errors.
Is there a good way to perform utf8mb4 encoding only for this table without affecting other table operations.
PS: Since the production environment has been online for a year and there are already a lot of data and online users, this problem is reported only after testing. The risk of updating the encoding of the database as a whole is too high. The leader means that if there are any pitfalls, they will pretend to be invisible.
Fortunately, I installed the production environment when I set up the system. mysql was the latest version 5.6.26 at the beginning. The method for re-Transcoding of emoji is only used as the final solution.