MySQL - Encoding

Tags: charset

MySQL server-system-variables.html

Usually, set the #4 as below is enough.

1.SET NAMES

SET NAMES utf8
or
charset utf8
is equivalent to
SET character_set_client = utf8 (client input charset)
SET character_set_results = utf8 (return charset)
SET character_set_connection = utf8 (MySQL internal stored charset)

SET character set ‘utf8′ is equivalent to
SET character_set_client = utf8 (client input charset)
SET character_set_results = utf8 (return charset)
SET collation_connection = @@collation_database;

To view current charset:

SHOW VARIABLES LIKE ‘character_set_%’;
SHOW VARIABLES LIKE ‘collation_%’;

2. Config (my.ini)
a. [mysqld]
only affect none-admin user (not root)

init_connect='SET NAMES utf8'

b.[client]
default value, can be override by “init_connect” in [mysqld]

default-character-set = utf8

3. When create schema

DROP DATABASE IF EXISTS `mydb1`;
CREATE DATABASE `mydb1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `mydb1`;
CREATE TABLE IF NOT EXISTS `mytable1` (
`id` SERIAL,
`name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

4. When execute query

Query this after select database (“mysql_select_db” in php)

mysql_query("SET NAMES 'utf8'");