MySQL and UTF-8 recipe

If you're leaving the secure world of ASCII characters you're usually enter a dangerous land of confusion. It's actually a very complex topic and from a technical point of view there is no one to blame.

But if you don't care about the basics, don't care why or how, and just want a reliable and working system, simply follow these three advices to attain happiness and inner peace in your developer's life.

Advice 1

Define the character set for every column where you plan to store international character data:

CREATE TABLE t1 ( col1 VARCHAR(42) CHARACTER SET utf8 );

Or at least for every table:

CREATE TABLE t1 ( col1 VARCHAR(42) ) CHARACTER SET utf8;

The later example saves you some typing, but usually you don't need UTF-8 in every string column.

If you omit this parameter, MySQL assumes a default value which may be different from system to system.

Advice 2

After connecting to MySQL always do a SET NAMES 'utf8' before doing anything else.

For example in PHP:
$mysqli=mysqli_connect('localhost','demo','demo','demo');
$mysqli->query("SET NAMES 'utf8'");

If you omit this parameter, MySQL assumes a default value which may be different from system to system.

Advice 3

Before sending any text to the browser set the charset in the Content-Type header of your HTTP response.

For example in PHP:

header("Content-Type: text/html; charset=utf-8");

If you omit this parameter, your browser assumes a default value which may be different from system to system.

A final word...

This is a very simple recipe made for people who don't want to be bothered with technical details, but it's very important to follow all these advices, and not just one or two.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Kai 'Oswald' Seidler writes about his life as co-founder of Apache Friends, creator of XAMPP, and technology evangelist for web tier products at Sun Microsystems.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today