Mysql database encoding problem (modify database table field encoding to utf8

Because the utf8 character set is currently the most suitable character set for converting from one character set to another, I strongly recommend using utf8 as the default character set, although you may not be able to view the database correctly on the command line tool
Here's a complete example:
1. Create database tables
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 according to the utf8 check set
The default character set for all tables created in this database will be utf8

mysql > create table my_table (name varchar(20) not null default '')type=myisam default charset utf8;
Create a table and set the default character set to utf8

2. Write the data
Example 1 is to insert data directly through php:
a.php

 
<?php 
mysql_connect('localhost','user','password'); 
mysql_select_db('my_db'); 
// Please note that , This is a crucial step , If you don't have this step , All data reads and writes will be incorrect  
// It is used to set up this database join process , The default character set for data transfer  
// Other programming languages / The interface is similar, for example  .net/c#/odbc 
//jdbc Set the connection string to similar "jdbc:mysql://localhost/db?user=user&password=123456&useUnicode=true&characterEncoding=UTF-8" 
mysql_query("set names utf8;"); 
// Must be gb2312( Local codes ) Converted to utf-8, You can also use iconv() function  
mysql_query(mb_convet_encoding("insert into my_table values(' test ');", "utf-8", "gb2312")); 
?> 

The example is inserting data through page submission 2:
b.php

 
<?php 
// Output this page is coded 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')) 
{ 
// As specified above, the character set of this page is utf-8 the , So you don't have to convert the encoding  
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, a complete example using the utf8 character set has been completed.
If you want to use gb2312 coding, so suggest you use latin1 as the default character set of data table, so it can directly insert data in the command line tools in Chinese, and can be directly displayed. And don't use gb2312 or gbk character sets, such as if worried about query sort, can use binary attribute constraints, such as:
create table my_table ( name varchar(20) binary not null default '')type=myisam default charset latin1;

Annex 1: old data upgrade method
Take the original character set latin1 as an example and upgrade it to utf8. The original table: old_table (default charset=latin1), the new table: new_table(default charset=utf8).
Step 1: export the old data
mysqldump --default-character-set=latin1 -hlocalhost -uroot -B my_db --tables old_table > old.sql
Step 2: convert the encoding (similar to unix/linux)
iconv -t utf-8 -f gb2312 -c old.sql > new.sql
Alternatively, you can remove the -f parameter and let iconv automatically determine the original character set
iconv -t utf-8 -c old.sql > new.sql
Here, assume that the original data defaults to gb2312 encoding.
Step 3: import
Modify old.sql by adding an sql statement before the insert/update statement begins: "SET NAMES utf8;" , save.
mysql -hlocalhost -uroot my_db < new.sql
Done!!

Attachment 2: MySQL client has support for viewing the utf8 character set
1.) MySQL-Front, it is said that this project has been ordered to stop by MySQL AB. I do not know why, if there are still many cracked versions available for download in China (it does not mean that I recommend the cracked version: -P).
2.) Navicat, another very good MySQL client. The Chinese version has just come out, and I was invited to try it out.
3.) PhpMyAdmin, open source php project, very good.
4.) terminal tools under Linux (Linux terminal), set the terminal character set to utf8, connect to MySQL, and execute SET NAMES UTF8; Also can read and write utf8 data.

Attachment 3: convert the character set directly using the ALTER syntax provided by MySQL
This is great news for the majority of non-utf8 users who want to convert to utf8, and I found out after studying the MySQL manual. The specific usage is as follows:
ALTER TABLE OLD_TABLE CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];
Remember to back up the old tables before converting, in case you need to. Here's a practical example:
ALTER TABLE `t_yejr` CONVERT TO CHARACTER SET UTF8;
This method should be provided since MySQL 4.1. You can check whether your own version is supported or not. If not, you will have to follow the transformation mentioned above. enjoy it!!!!!!