FREE THOUGHT · FREE SOFTWARE · FREE WORLD

Home  »  MySQL  »  Speed Up init_connect in my.cnf for UTF8

by comment

Most info on the Net says to do the below. So I wanted to combine those into just 1 statement instead of 2.

init-connect='SET NAMES utf8'
init_connect='SET CHARACTER SET=utf-8'

Combining init_connect

Add to your /etc/my.cnf

[mysqld]
init_connect='SET collation_connection = utf8_general_ci,NAMES utf8'
character-set-server = utf8
collation-server = utf8_general_ci

You can change the default server character set and collation with the --character-set-server and --collation-server options when you start the server. The collation must be a legal collation for the default character set.

Misc my.cnf

init_connect='SET collation_connection = utf8_general_ci,NAMES utf8'
collation_server            = utf8_general_ci
collation_connection        = utf8_general_ci
character_set_client        = utf8
character_set_connection    = utf8
character_set_results       = utf8
character_set_server        = utf8
character_set_system        = utf8

10.1.4. Connection Character Sets and Collations

Getting Relevant MySQL Variables

# mysql INFORMATION_SCHEMA -tBe "SELECT * FROM GLOBAL_VARIABLES WHERE VARIABLE_NAME LIKE '%coll%' OR VARIABLE_NAME LIKE '%char%' OR VARIABLE_NAME='init_connect';"
+--------------------------+-------------------------------------------------------+
| VARIABLE_NAME            | VARIABLE_VALUE                                        |
+--------------------------+-------------------------------------------------------+
| CHARACTER_SETS_DIR       | /usr/share/mysql/charsets/                            |
| CHARACTER_SET_RESULTS    | utf8                                                  |
| CHARACTER_SET_CONNECTION | utf8                                                  |
| COLLATION_DATABASE       | utf8_general_ci                                       |
| CHARACTER_SET_SERVER     | utf8                                                  |
| COLLATION_SERVER         | utf8_general_ci                                       |
| CHARACTER_SET_SYSTEM     | utf8                                                  |
| CHARACTER_SET_FILESYSTEM | binary                                                |
| CHARACTER_SET_DATABASE   | utf8                                                  |
| CHARACTER_SET_CLIENT     | utf8                                                  |
| INIT_CONNECT             | SET collation_connection = utf8_general_ci,NAMES utf8 |
| COLLATION_CONNECTION     | utf8_general_ci                                       |
+--------------------------+-------------------------------------------------------+
 
# mysql -tBe "SHOW VARIABLES WHERE VARIABLE_NAME LIKE '%coll%' OR VARIABLE_NAME LIKE '%char%' OR VARIABLE_NAME='init_connect';"
+--------------------------+-------------------------------------------------------+
| Variable_name            | Value                                                 |
+--------------------------+-------------------------------------------------------+
| character_set_client     | latin1                                                |
| character_set_connection | latin1                                                |
| character_set_database   | utf8                                                  |
| character_set_filesystem | binary                                                |
| character_set_results    | latin1                                                |
| character_set_server     | utf8                                                  |
| character_set_system     | utf8                                                  |
| character_sets_dir       | /usr/share/mysql/charsets/                            |
| collation_connection     | latin1_swedish_ci                                     |
| collation_database       | utf8_general_ci                                       |
| collation_server         | utf8_general_ci                                       |
| init_connect             | SET collation_connection = utf8_general_ci,NAMES utf8 |
+--------------------------+-------------------------------------------------------+

Procedure MySQL Uses

To sum this up, the encoding/transcoding procedure MySQL uses to process the query and its results is a multi-step-thing:

  1. MySQL treats the incoming query as being encoded in character_set_client.
  2. MySQL transcodes the statement from character_set_client into character_set_connection
  3. When comparing string values to column values MySQL transcodes the string value from character_set_connection into the chracter set of the given database column and uses the column collation to do sorting and comparison.
  4. MySQL builds up the result set encoded in character_set_results (this includes result data as well as result metadata such as column names and so on)

my.cnf Definitions and Uses

init-connect
A string to be executed by the server for each client that connects. The string consists of one or more SQL statements, separated by semicolon characters. For example, each client session begins by default with autocommit mode enabled.
default-character-set
Use charset_name as the default character set for the client and connection. A common issue that can occur when the operating system uses utf8 or another multi-byte character set is that output from the mysql client is formatted incorrectly, due to the fact that the MySQL client uses the latin1 character set by default. You can usually fix such issues by using this option to force the client to use the system character set instead.
character-set-client
The character set for statements that arrive from the client. The session value of this variable is set using the character set requested by the client when the client connects to the server. The global value of the variable is used to set the session value in cases when the client-requested value is unknown or not available, or the server is configured to ignore client requests.
character-set-system
The character set used by the server for storing identifiers. The value is always utf8.
character-set-server
The server's default character set.
collation-connection
The collation of the connection character set.
collation-server

More Info

You can find more information on the following manual pages:

Tags

March 13th, 2013

Comments Welcome

Popular Articles
My Online Tools

Related Articles
Newest Posts
Twitter


  •  t.co/ShKrGdqXuJ 
  • RUN GCC! This is a typical shirt I wear, from the  t.co/46LYbFr4k2  shop. A clerk at the LQ recognized it!  t.co/jjmT0dkCPu 
  • Merlin the Magician  t.co/iMmRbanUi4 
  • ROGUE CODE - Latest novel from @markrussinovich  t.co/apkn0LoPIt 
  • RTFM - surprisingly very helpful and way more comprehensive than it looks! @redteamfieldman #pwnAllTheThings  t.co/xiaJ5g0aC9 
  • Dear Hacker - Letters to the Editor of 2600, from Emmanuel Goldstein  t.co/JCfLab7FAJ 
  • The Mythical Man-Month - Essays on Software Engineering, by Frederick P. Brooks, Jr.  t.co/ilWN5GHElr 
  • "where wizards stay up late" - The Origins of the Internet. Favorite book detailing the birth of the net and IMPs  t.co/gY9VTGJgZz 
  • ZERO DAY - read before Trojan horse  t.co/pPMLGDJv8P 
  • Trojan Horse, a novel!  t.co/Hf8EtYaZVa 
  • The Hacker Playbook - very nice high level overview of attacks  t.co/lHwNVWi61u 
  • Clean Code - A Handbook of Agile Software Craftsmanship  t.co/hnJX0x1qIc 
  • Secrets of the JavaScript Ninja - By my absolute favorite JS hacker John Resig!  t.co/tZ42ljmcCl 
  • Hacking Exposed 7: Network Security Secrets & SolutionsMy all time favorite, basic but thorough and accurate.  t.co/jycW0RDVtZ 

Hacking and Hackers

The use of "hacker" to mean "security breaker" is a confusion on the part of the mass media. We hackers refuse to recognize that meaning, and continue using the word to mean someone who loves to program, someone who enjoys playful cleverness, or the combination of the two. See my article, On Hacking.
-- Richard M. Stallman






[hide]

It's very simple - you read the protocol and write the code. -Bill Joy

Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution 3.0 License, just credit with a link.
This site is not supported or endorsed by The Apache Software Foundation (ASF). All software and documentation produced by The ASF is licensed. "Apache" is a trademark of The ASF. NCSA HTTPd.
UNIX ® is a registered Trademark of The Open Group. POSIX ® is a registered Trademark of The IEEE.

| Google+ | askapache

Site Map | Contact Webmaster | License and Disclaimer | Terms of Service

↑ TOPMain