Creating a stored procedure: setting the character set and collation

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 statusG

*************************** 1. row ***************************
                  Db: MslLandingSequence
                Name: DeploySkycrane
                Type: PROCEDURE
             Definer: <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="741701061d1b071d000d34181b1715181c1b0700">[email protected]</a>
            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.

Contents hide

Answers:

Method 1

Method 2

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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!

Method 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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating