MySQL Database Chinese garbled problem

Mysql> SELECT * FROM books;+-----+---------------------------------+---------+-------------+-------+----------- -+--------+-------------+| BId | bname | Btypeid | Publishing | Price | PubDate | Author | ISBN |+-----+---------------------------------+---------+-------------+-------+------------+--------+-----------   --+|                         1 |??????? |    2 |????????    | 34 |     2004-10-01 |?? |   7505380796 | |                         2 |??????? |     6 |???????    | 41 |    2002-07-01 |??? |   7121010925 | | 3 |???????? ASP |   2 |?????????    | 43 |     2005-02-01 |?? |   75053815x | | 4 |             PageMaker 7.0?????? |     9 |???????    | 43 |    2005-01-01 |??? |   7121008947 | |                        5 |???????? |   6 |?????????    | 44 |    2003-06-29 |??? |   7120000233 | | 6 |              Dreamweaver 4????? |     2 |???????    | 44 |    2004-06-01 |??? | 7505397699 | 

1. Confirm and set the MySQL database encoding (note below orange font) mysql> status--------------MySQL  Ver 14.14 distrib 5.1.73, for Redhat-linux-gnu ( x86_64) using ReadLine 5.1Connection id:2current database:schoolcurrent user:[email protected]ssl:not in Usecurrent pager:stdoutusing outfile: ' Using delimiter:; Server version:5.1.73 Source distributionprotocol version:10connection:localhost via UNIX socketServer characterset:utf8db     characterset:utf8client characterset:latin1conn.  Characterset:latin1UNIX socket:/var/lib/mysql/mysql.sockuptime:1 min 8 secthreads:1  questions:13  Slow queries:0  opens:17  Flush tables:1  Open tables:10  queries per second avg:0.191--------------

2. Modify method First Enter database mysql-uroot-p123 mysql>use schoolmysql> ALTER DATABASE school character set utf8;mysql> alter data Base school Character set UTF8 collate utf8_general_ci;
mysql> ALTER TABLE books character set UTF8 collate utf8_general_ci;3.vim/etc/my.conf//edit modify MySQL configuration file [mysqld]datadir =/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# disabling symbolic-links is recommended to prevent Assorted Security Riskssymbolic-links=0character-set-server = utf8[mysqld_safe]log-error=/var/log/ mysqld.logpid-file=/var/run/mysqld/mysqld.pid[client]default-character-set=utf8[mysql]default-character-set=                                              utf84. Restart the database after saving [[email protected] ~]#/etc/init.d/mysqld restart stop mysqld: [OK] starting mysqld: [OK]5. Enter MySQL to view character set Mysql> show variables like "%char%"; +-- ------------------------+----------------------------+| variable_name | Value |+--------------------------+----------------------------+| character_set_client | UTF8 | | character_set_connection | UTF8 | | Character_set_database |     Utf8                  || Character_set_filesystem | binary | | Character_set_results | UTF8 | | Character_set_server | UTF8 | | Character_set_system | UTF8 | | Character_sets_dir | /usr/share/mysql/charsets/|+--------------------------+----------------------------+8 rows in Set (0.00 sec) MySQL > Show variables like "%colla%" +----------------------+-----------------+| variable_name | Value |+----------------------+-----------------+| collation_connection | Utf8_general_ci | | Collation_database | Utf8_general_ci | | Collation_server | Utf8_general_ci |+----------------------+-----------------+3 rows in Set (0.00 sec)

Mysql> SELECT * FROM books;+-----+---------------------------------------------------------+---------+--------- --------------------------+-------+------------+-----------+-------------+| BId | bname | Btypeid | Publishing | Price | PubDate | Author | ISBN |+-----+---------------------------------------------------------+---------+-------------------------------   ----+-------+------------+-----------+-------------+| 1 | Website Production through Train | 2 |    Computer Enthusiasts Magazine | 34 | 2004-10-01 | Miao Zhuang |   7505380796 | | 2 | Hackers and cyber security | 6 |    Aviation Industry Press | 41 | 2002-07-01 | Baili |   7121010925 | | 3 | Network program and Design-asp | 2 |    North Jiaotong University Press | 43 | 2005-02-01 | Wangyue |   75053815x | | 4 | PageMaker 7.0 Short-term Training Tutorials | 9 | China Electric Power OutVersion of the club | 43 | 2005-01-01 | Sun Liying |   7121008947 | | 5 | Secrets of hacker Attack Prevention | 6 |    Beijing Teng Map Electronic publishing House | 44 | 2003-06-29 | Zhao Thunderstorm |   7120000233 | | 6 | Dreamweaver 4 Getting Started and improving | 2 |    Tsinghua University Press | 44 | 2004-06-01 | Yeu Yubo | 7505397699 |

First, avoid creating database and table in Chinese garbled and view encoding method
1: Create database ' Test '
CHARACTER SET ' UTF8 '
COLLATE ' Utf8_general_ci ';
2. Create table ' Database_user ' (
' ID ' varchar (+) not NULL default ' ',
' UserID ' varchar (+) not NULL default ' ',
) Engine=innodb DEFAULT Charset=utf8;

The same encoding format is used when building the library and building the table.

To view the encoding format for the school database:

Mysql> Show CREATE Database school;+----------+---------------------------------------------------------------- -+| Database | Create Database                                                 |+----------+-----------------------------------------------------------------+| School   | CREATE DATABASE ' School '/*!40100 DEFAULT CHARACTER SET UTF8 */|+----------+------------------------------------------ -----------------------+1 Row in Set (0.00 sec)

To view the encoding format of a books table

1 MySQL> UseSchool2Reading table Information forCompletion of table and column names3Can turn off this feature to get a quicker startup with-A4 5 Database changed6 MySQL>Show create table books;7+-------+------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------+8| Table |                                                                                                                                                                                                                                        Create Table |9+-------+------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------+Ten| Books |CREATE TABLE ' books ' ( One' BId ' int (4) notNULLAuto_increment, A' bname ' varchar (255)DEFAULT NULL, -' Btypeid ' enum (' 1 ', ' 2 ', ' 3 ', ' 4 ', ' 5 ', ' 6 ', ' 7 ', ' 8 ', ' 9 ', ' 10 ')DEFAULT NULL, -' Publishing ' varchar (255)DEFAULT NULL, the' Price ' int (4)DEFAULT NULL, -' PubDate 'Date DEFAULT NULL, -' Author ' varchar (30)DEFAULT NULL, -' ISBN ' varchar (255)DEFAULT NULL, +PRIMARYKEY(' bId ') -) Engine=myisam auto_increment=45DEFAULTCharset=utf8 | ++-------+------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------+ A1 row in Set (0.00 sec)

MySQL Database Chinese garbled problem