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.确认并设置MySql的数据库的编码（注意下面橘色字体）mysql> status
--------------
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
Connection id:2
Current database:school
Current user:root@localhost
SSL:Not in use
Current pager:stdout
Using outfile:''
Using delimiter:;
Server version:5.1.73 Source distribution
Protocol version:10
Connection:Localhost via UNIX socket
Server characterset:utf8
Db characterset:utf8
Client characterset:latin1
Conn. characterset:latin1
UNIX socket:/var/lib/mysql/mysql.sock
Uptime:1 min 8 sec
Threads: 1 Questions: 13 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 10 Queries per second avg: 0.191
--------------
2.修改方法
首先进入数据库mysql -uroot -p123
mysql>use school
mysql> alter database school character set utf8;
mysql> alter database school character set utf8 collate utf8_general_ci;
mysql> alter table books character set utf8 collate utf8_general_ci;
3.vim /etc/my.conf //编辑修改mysql配置文件
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
character-set-server = utf8
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
4.以上保存后重启数据库
[root@orange ~]# /etc/init.d/mysqld restart
停止 mysqld： [确定]
正在启动 mysqld： [确定]
5.进入mysql查看字符集
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 | 网站制作直通车 | 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、创建数据库的时候：CREATE DATABASE `test`
CHARACTER SET 'utf8'
COLLATE 'utf8_general_ci';
2、建表的时候 CREATE TABLE `database_user` (
`ID` varchar(40) NOT NULL default '',
`UserID` varchar(40) NOT NULL default '',
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
即建库和建表时都使用相同的编码格式。
查看school数据库的编码格式:
mysql> show create database school;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| school | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
查看books表的编码格式
1 mysql> use school
2 Reading table information for completion of table and column names
3 You can turn off this feature to get a quicker startup with -A
4
5 Database changed
6 mysql> show create table books;
7 +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8 | Table | Create Table |
9 +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10 | books | CREATE TABLE `books` (
11 `bId` int(4) NOT NULL AUTO_INCREMENT,
12 `bName` varchar(255) DEFAULT NULL,
13 `bTypeId` enum('1','2','3','4','5','6','7','8','9','10') DEFAULT NULL,
14 `publishing` varchar(255) DEFAULT NULL,
15 `price` int(4) DEFAULT NULL,
16 `pubDate` date DEFAULT NULL,
17 `author` varchar(30) DEFAULT NULL,
18 `ISBN` varchar(255) DEFAULT NULL,
19 PRIMARY KEY (`bId`)
20 ) ENGINE=MyISAM AUTO_INCREMENT=45 DEFAULT CHARSET=utf8 |
21 +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
22 1 row in set (0.00 sec)