How to convert different MySQL character sets into a unified character set

The MySQL character set processing method described below is to convert different MySQL character sets into a unified character set. This method is for your reference and may be helpful in learning the MySQL character set.

 
 

  
  
After   an   upgrade   to   MySQL   4.1,   the   statement   fails:        
  
  
 
  
  
mysql>   SELECT   SUBSTRING_INDEX(USER(),'@',1);      
  
  
 
  
  
ERROR   1267   (HY000):   Illegal   mix   of   collations      
  
  
 
  
  
(utf8_general_ci,IMPLICIT)   and   (latin1_swedish_ci,COERCIBLE)      
  
  
 
  
  
for   operation   'substr_index'      
  
  
 
  
  
The   reason   this   occurs   is   that   usernames   are   stored   using   UTF8   (see   section   11.6   UTF8   for   Metadata).   As   a   result,   the   USER()   function   and   the   literal   string   '@'   have   different   character   sets   (and   thus   different   collations):        
  
  
 
  
  
mysql>   SELECT   COLLATION(USER()),   COLLATION('@');      
  
  
 
  
  
+-------------------+-------------------+      
  
  
 
  
  
|   COLLATION(USER())   |   COLLATION('@')         |      
  
  
 
  
  
+-------------------+-------------------+      
  
  
 
  
  
|   utf8_general_ci       |   latin1_swedish_ci   |      
  
  
 
  
  
+-------------------+-------------------+      
  
  
 
  
  
One   way   to   deal   with   this   is   to   tell   MySQL to   interpret   the   literal   string   as   utf8:        
  
  
 
  
  
mysql>   SELECT   SUBSTRING_INDEX(USER(),_utf8'@',1);      
  
  
 
  
  
+------------------------------------+      
  
  
 
  
  
|   SUBSTRING_INDEX(USER(),_utf8'@',1)   |      
  
  
 
  
  
+------------------------------------+      
  
  
 
  
  
|   root                                                               |      
  
  
 
  
  
+------------------------------------+      
  
  
 
  
  
Another   way   is   to   change   the   connection   character   set   and   collation   to   utf8.   You   can   do   that   with   SET   NAMES   'utf8'   or   by   setting   the   character_set_connection   and   collation_connection   system   variables   directly.        
  
  
 
 
 

You can use MySQL Version> 4.12 for table encoding and conversion)

 
 

  
  
ALTER   TABLE   tbl_name   CONVERT   TO   CHARACTER   SET   charset_name;      
  
  
 
 
 

Earlier versions can be used:

 
 

  
  
ALTER   TABLE   tbl_name   CHARACTER   SET   charset_name;   
  
  
 
 
 

In-depth study on MySQL's data deletion from multiple tables

Combination of multiple MySQL table result sets

Implementation of MySQL table sharding

MySQL authorization table usage example

Disadvantages of MySQL memory tables