Creating a stored procedure: setting the character set and collation

Question

When creating a MySQL stored procedure how does one set the character set and collation? The MySQL documentation does not provide any examples and to the general syntax is slightly unclear.

mysql> show procedure status\G

*************************** 1. row ***************************
                  Db: MslLandingSequence
                Name: DeploySkycrane
                Type: PROCEDURE
             Definer: curiosity@localhost
            Modified: 2012-08-04 00:05:16
             Created: 2011-11-12 00:02:45
       Security_type: DEFINER
             Comment: 
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci

The last three items should be Unicode. Thanks.

Answer 1

character_set_client is the session value of the character_set_client system variable when the routine was created. collation_connection is the session value of the collation_connection system variable when the routine was created. Database Collation is the collation of the database with which the routine is associated. These columns were added in MySQL 5.1.21.

http://dev.mysql.com/doc/refman/5.1/en/show-procedure-status.html

In short, open a mysql command line, type in

SET NAMES UTF8;

then drop and reimport the stored procedures. This fixed my problem. I don't even want to think about pre 5.1.21 users. They are fumbling in the dark!

Answer 2

Pay attention to the bug report. It shows that procedure and its parameters are not associated with default character set of the database, but always with Binary String.

So you have to explicitly specify Character set attribute for parameters.

Simple recreate procedure might not solve the problem.