Mysql database encoding problem (modify the database, table, and field encoding to utf8)

Because the utf8 character set is currently the most suitable character set for conversion between different character sets, although you may not be able to correctly view the content in the database on the command line tool, I still strongly recommend using utf8 as the default Character Set
The following is a complete example:
1. Create a database table
Mysql> create database if not exists my_db default charset utf8 COLLATE utf8_general_ci;
# Note the following sentence "COLLATE utf8_general_ci", which roughly means sorting by utf8 validation set during sorting
# The default character set of all data tables created in this database will be utf8.

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
Example 1: insert data directly using php:
A. php
Copy codeThe Code is as follows:
<? Php
Mysql_connect ('localhost', 'user', 'Password ');
Mysql_select_db ('My _ db ');
// Note that this step is critical. Without this step, all data reads and writes Will be incorrect.
// It sets the default character set for data transmission during the database connection.
// Other programming languages/interfaces are similar, such as. net/c #/odbc
// For jdbc, set the connection string to "jdbc: mysql: // localhost/db? User = user & password = 123456 & useUnicode = true & characterEncoding = UTF-8"
Mysql_query ("set names utf8 ;");
// You must convert gb2312 (locally encoded) to UTF-8. You can also use the iconv () function.
Mysql_query (mb_convet_encoding ("insert into my_table values ('test');", "UTF-8", "gb2312 "));
?>

The example is to submit the insert data through the page 2:
B. php
Copy codeThe Code is as follows:
<? Php
// Output this page 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 '))
{
// Because the character set on this page has been specified as UTF-8, no conversion encoding is required.
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 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:
Create table my_table (name varchar (20) binary not null default '') type = myisam default charset latin1;

Appendix 1: 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
Mysqldump -- default-character-set = latin1-hlocalhost-uroot-B my_db -- tables old_table> old. SQL
Step 2: Convert the encoding (similar to the unix/linux environment)
Iconv-t UTF-8-f gb2312-c old. SQL> new. SQL
You can also remove the-f parameter to enable iconv to automatically determine the original character set.
Iconv-t UTF-8-c old. SQL> new. SQL
Here, we assume that the original data is gb2312 by default.
Step 3: Import
Modify the old. SQL statement and add an SQL statement "SET NAMES utf8;" before the insert/update statement starts. Save the statement.
Mysql-hlocalhost-uroot my_db <new. SQL
Success !!

Appendix 2: MySQL clients that support viewing utf8 character sets have
1.) MySQL-Front, it is said that this project has been ordered by MySQL AB to stop. For some reason, if there are many cracked versions in China, you can download it (it does not mean I recommend using the cracked version:-P ).
2.) Navicat, another very good MySQL client, just came out of the Chinese version and invited me to try it out. In general, it is still good, but it also needs to be paid.
3.) PhpMyAdmin, an open-source php project, is very good.
4) Linux terminal tool (Linux terminal), SET the character SET of the terminal to utf8, connect to MySQL, execute set names UTF8; can also read and write utf8 data.

Appendix 3: Convert character sets directly using the ALTER syntax provided by MySQL
This is a great news for many users who want to convert them to utf8. I also found this only when I was learning the MySQL manual. The usage is as follows:
Alter table OLD_TABLE convert to character set charset_name [COLLATE collation_name];
Before conversion, remember to back up the old table first, just in case. The following is an actual example:
Alter table 't_ yejr' convert to character set UTF8;
This method should have been provided since MySQL 4.1. You can check whether your version is supported or not. If not, you have to follow the conversion mentioned above. Enjoy it !!!