Mysql character encoding method

Setting character encoding in Mysql can solve some common problems. For example, if an error occurs when using different character sets, the following describes how to set character encoding in Mysql to solve this problem.

The error is that there are two character sets in your result set.

For example, if you perform a joint query on two tables, the character set of one table is latin1 and the other is utf8,
In this way, there are two character sets in your result set. mysql will report an error.

It is also true that different fields in a table use different character sets.

Use show create table table_name; to see the specific character set settings.

I checked the help manual, saying that the user's character set is not set. The default is utf8, And it is converted to the latin1 or gb2312 character set.

Solution:

Convert different character sets into a unified character set. The following describes how to set character encoding for Mysql.

 
 

  
  
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; 
 
 

Statement for modifying fields in mysql

How to delete multiple tables in MySQL

How MySQL connects to remote SQL

MySQL cluster introduction and Configuration

Special notes for MySQL database