Solutions to Chinese problems in two MySQL stored procedures

MySQL databaseThe following problems often occur in stored procedures:

1. garbled storage information, especially by executing SQL scripts to add data.

2. The where clause is used to compare Chinese strings, which is also very common.

To solve the problem of garbled storage information, be sure to pay attention to the terminal that executes the script. The default character encoding of the system is what you require. This problem is ultimately caused by mysql.Character Set. MySQL Character Set Support includes Characterset and Collation ).

The support for character sets is refined to four levels: server, database, table, and connection ).

MySQL can refine the character set designation to a database, a table, and a column.

Character Set-related commands:

View the default character set (mysql uses latin1 (ISO_8859_1) by default)

 
 

  
  
mysql> SHOW VARIABLES LIKE 'character%';    
  
  
 
  
  
mysql> SHOW VARIABLES LIKE 'collation_%';   
 
 

Run the following command to modify the character set:

 
 

  
  
<pre name="code" class="html">     
  
  
 
  
  
mysql> SET character_set_client = utf8 ;    
  
  
 
  
  
mysql> SET character_set_connection = utf8 ;    
  
  
 
  
  
mysql> SET character_set_database = utf8 ;    
  
  
 
  
  
mysql> SET character_set_results = utf8 ;    
  
  
 
  
  
mysql> SET character_set_server = utf8 ;    
  
  
 
  
  
mysql> SET collation_connection = utf8 ;    
  
  
 
  
  
mysql> SET collation_database = utf8 ;    
  
  
 
  
  
mysql> SET collation_server = utf8 ;   
 
 

The cause of Issue 1 is that the default character set for the table is set to utf8 and the query is sent by UTF-8 encoding, but the connection layer encoding is still incorrect. The solution is to execute the following statement before sending the query:

 
 

  
  
SET NAMES 'utf8'; 
 
 

It is equivalent to the following three commands:

 
 

  
  
<pre name="code" class="html">SET character_set_client = utf8;    
  
  
 
  
  
SET character_set_results = utf8;    
  
  
 
  
  
SET character_set_connection = utf8;   
 
 

Problem 2 solution:

Transcode COLLATE utf8_unicode_ci for variables or constants to be compared, for example:

 
 

  
  
declare cur_preferences cursor for select id from preferences where @name like concat("%",rTitle COLLATE utf8_unicode_ci )  ； 
 
 

If the preceding statement is not optimized successfully, run the following statement:

 
 

  
  
declare cur_preferences cursor for select id from preferences where @name  COLLATE utf8_unicode_ci  like concat("%",rTitle COLLATE utf8_unicode_ci )  ； 
 
 

This will solve this problem.

For more information about MySQL database stored procedures