Small talk about MySQL character set

Mysql

First of all, this article is purely my personal experience, applicable to my common environment and projects.
Personally, the database character set uses UTF8 (the HTML page corresponds to UTF-8) so that your data can be migrated smoothly because the UTF8 character set is the character set that is best suited to implement conversions between different character sets. Although you may not be able to view the contents of the database correctly on command-line tools, I strongly recommend using UTF8 as the default character set.
The next complete example is:
1. Create a database table
Mysql>create DATABASE IF not EXISTS my_db default charset UTF8 COLLATE;
#注意后面这句话 "COLLATE utf8_general_ci", roughly meaning to sort by UTF8 checksum set
#那么在这个数据库下创建的所有数据表的默认字符集都会是utf8了

Mysql>create table my_table (name varchar () NOT NULL default ") Type=myisam default CharSet UTF8;
#这句话就是创建一个表了, set the default character set to UTF8

2. Write Data
Example 1 is to insert data directly through PHP:
a.php
<?php
mysql_connect (' localhost ', ' user ', ' password ');
mysql_select_db (' my_db ');

Note that this step is critical, and without this step, all data will be read and written incorrectly.
Its role is to set the default character set for data transmission during this database join process
Other programming languages/interfaces are similar, for example. Net/c#/odbc
JDBC Sets the connection string to resemble "jdbc:mysql://localhost/db?user=user&password=123456&useunicode=true& Characterencoding=utf-8 "
mysql_query ("Set names UTF8;");

You must convert the gb2312 (local encoding) to Utf-8, or you can use the Iconv () function
mysql_query (mb_convet_encoding ("INSERT into my_table values (' Test ');", "Utf-8", "gb2312"));
?>

Examples are insert data by page Submission 2:
b.php
<?php
Output This page is encoded as Utf-8
Header ("content-type:text/html; Charset=utf-8 ");

mysql_connect (' localhost ', ' user ', ' password ');
mysql_select_db (' my_db ');

if (isset ($_request[' name '))
{
Since the character set on this page has been specified above, no conversion encoding is required Utf-8
mysql_query (sprintf ("INSERT into my_table values ('%s ');", $_request[' name '));
}

$q = mysql_query ("SELECT * from my_table");
while ($r = Mysql_fetch_row ($q))
{
Print_r ($R);
}
?>

<meta http-equiv= "Content-type" content= "text/html; Charset=utf-8 ">
<form action= "" method= "POST" >
<input type= "text" name= "name" value= "" >
<input type= "Submit" value= ' Submit ' >
</form>

Since then, the complete example of using the UTF8 character set has ended.
If you want to use the gb2312 encoding, it is recommended that you use Latin1 as the default character set for the datasheet, so that you can insert the data directly in the command line tool in Chinese and display it directly. Instead of using character sets such as gb2312 or GBK, and if you're worried about sorting queries, You can use the Binary property constraint, for example:
CREATE TABLE my_table (name varchar () binary NOT NULL default ') Type=myisam default CharSet latin1;

Appendix 1: The Old data upgrade method
Take the original character set for Latin1 as an example, upgrade to become the UTF8 character set. Original table: old_table (Default charset=latin1), new table: new_table (Default Charset=utf8).
First step: Export old data
Mysqldump--default-character-set=latin1-hlocalhost-uroot-b my_db--tables old_table > Old.sql
Step Two: Convert code (similar to Unix/linux environment)
Iconv-t Utf-8-F gb2312-c old.sql > New.sql
Or you can remove the-f parameter and let Iconv automatically determine the original character set
Iconv-t Utf-8-C old.sql > New.sql
In this case, assume that the original data is gb2312 encoded by default.
Step Three: Import
Modify Old.sql to add an SQL statement before the INSERT/UPDATE statement begins: "SET NAMES UTF8;", save.
Mysql-hlocalhost-uroot my_db < New.sql
Done!!

Appendix 2: The MySQL client that supports viewing the UTF8 character set has
1.) Mysql-front, it is said that the project has been the MySQL AB stopped, I do not know why, if there are many cracked version can be downloaded (does not mean that I recommend the use of cracked version:-P).
2.) Navicat, another very good MySQL client, the Chinese version just came out, but also invited me to try, overall still good, but also need to pay.
3. phpMyAdmin, open source PHP project, very good.
4.) Linux under the Terminal tool (Linux terminal), the end of the character set to UTF8, connected to MySQL, the implementation of set NAMES UTF8; can also read and write UTF8 data.

Appendix 3: Direct use of the ALTER syntax conversion character set provided by MySQL
This to the vast number of UTF8 and want to turn into a utf8 of users, is a great news, I also learned the MySQL manual is only found. The specific usage is as follows:
ALTER TABLE old_table CONVERT to CHARACTER SET charset_name [COLLATE collation_name];
Before converting, remember to back up the old table, just in case. The following is a practical example:
ALTER TABLE ' T_yejr ' CONVERT to CHARACTER SET UTF8;
This method should be from the MySQL 4.1 only to provide, you can check your version is supported, if not support, have to follow the above mentioned conversion. Enjoy it!!!