MySQL Character Set and MySQL encoding conversion

Personal suggestion: use the database character set whenever possibleUtf8(UTF-8), so that your data can be smoothly migrated, because the utf8 character set is currently the most suitable character set for conversion between different character sets, although you cannot correctly view the content in the database on the command line tool, I strongly recommend that you use utf8 as the default character set.
The following is a complete example:
1. Create a database table

1. Mysql> create database if not exists my_db default charset utf8 collate utf8_general_ci;

# Pay attention to the following sentence:"Collate utf8_general_ci", It generally means sorting by utf8 encoding format during sorting
# The default character set of all data tables created in this database will be utf8.

1. Mysql> Create Table my_table (name varchar (20) not null default '') type = MyISAM default charset utf8;

# This statement creates a table. The default character set is utf8.

2. Write Data
Insert data directly using PHP:

1. <? PHP
2. Mysql_connect ('localhost', 'user', 'Password ');
3. Mysql_select_db ('My _ db ');
4. // Note that this step is critical. Without this step, all data reads and writes Will be incorrect.
5. // It sets the default character set for data transmission during the database connection.
6. Mysql_query ("set names utf8 ;");
7. // You must convert gb2312 (locally encoded) to UTF-8. You can also use the iconv () function.
8. Mysql_query (mb_convet_encoding ("insert into my_table values ('test');", "UTF-8", "gb2312 "));
9. ?>

Insert data through the page:

1. <? PHP
2. // Output this page encoded as UTF-8
3. Header ("Content-Type: text/html; charset = UTF-8 ");
4. Mysql_connect ('localhost', 'user', 'Password ');
5. Mysql_select_db ('My _ db ');
6. If (isset ($ _ request ['name '))
7. {
8. // Because the character set on this page has been specified as UTF-8, no conversion encoding is required.
9. Mysql_query (sprintf ("insert into my_table values ('% s');", $ _ request ['name']);
10. }
11. $ Q = mysql_query ("select * From my_table ");
12. While ($ r = mysql_fetch_row ($ q ))
13. {
14. Print_r ($ R );
15. }
16. ?>
17. <Meta http-equiv = "Content-Type" content = "text/html; charset = UTF-8">
18. <Form action = "" method = "Post">
19. <Input type = "text" name = "name" value = ">
20. <Input type = "Submit" value = 'submit '>
21. </Form>

Since then, the complete example of using the utf8 character set has ended.
If you want to use gb2312 encoding, we recommend that you use Latin1 as the default Character Set of the data table so that you can insert data directly in the command line tool in Chinese and display it directly. do not use the gb2312 or GBK character sets. If you are worried about query sorting and other issues, you can use the Binary Attribute constraints, such:

1. Create Table my_table (name varchar (20) binary not null default '') type = MyISAM default charset Latin1;

Appendix: Methods for upgrading old data
Take the original character set Latin1 as an example to upgrade it to the utf8 character set. Original table: old_table (default charset = Latin1), new table: new_table (default charset = utf8 ).
Step 1: Export old data

1. Mysqldump -- default-character-set = Latin1-hlocalhost-uroot-B my_db -- tables old_table> old. SQL

Step 2: Convert the Encoding

1. Iconv-T UTF-8-F gb2312-c old. SQL> New. SQL

Here, we assume that the original data is gb2312 by default.
Step 3: Import
Modify old. SQL and add an SQL statement:"Set names utf8;", Save.

1. Mysql-hlocalhost-uroot my_db <New. SQL