[MySQL] 从安装mysql到初学时遇到的问题及解决方案

c:>mysql -u root   //进入mysql
  mysql>show databases;
  mysql>use mysql;
  mysql>update user set password=password("密码") where      user='root';
  mysql>flush privileges;
  mysql>quit

mysql> update users
-> set username='关羽'
-> where userid=2;
ERROR 1366 (HY000): Incorrect string value: '\xB9\xD8\xD3\xF0' for column 'usern
ame' at row 1

mysql> select * from users;
+--------+----------+
| userid | username |
+--------+----------+
| 2 | ???? |
| 3 | ???? |
| 4 | ?í?ù |
+--------+----------+
3 rows in set (0.00 sec)

mysql> status
--------------
mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (i686) using readline 5.0 Connection id: 30
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout Using outfile: ''
Using delimiter: ;
Server version: 5.0.45 Source distribution
Protocol version: 10
Connection:Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1 Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 33 days 58 min 53 sec
 Threads: 1 Questions: 72 Slow queries: 0 Opens: 25 Flush tables: 1 Open tables: 18 Queries per second avg: 0.000
--------------

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| users |
+----------------+
1 row in set (0.00 sec)
//更改表的字符集。
mysql> alter table users character set GBK;
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0
//查看表的结构：
mysql> show create table users;
+-------+-----------------------------------------------------------------------
------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
`userid` int(11) default NULL,
`username` char(20) character set latin1 default NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+-----------------------------------------------------------------------
------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> desc users;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| userid | int(11) | YES | | NULL | |
| username | char(20) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> insert into users values(88,'中文');
ERROR 1366 (HY000): Incorrect string value: '\xD6\xD0\xCE\xC4' for column 'usern
ame' at row 1

mysql> alter table users modify username char(20) character set gbk;
ERROR 1366 (HY000): Incorrect string value: '\xC0\xEE\xCB\xC4' for column 'usern
ame' at row 1

mysql> truncate table users;
Query OK, 3 rows affected (0.01 sec)

mysql> alter table users modify username char(20) character set gbk;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into users values(88,'中文');
Query OK, 1 row affected (0.01 sec)
mysql> select * from users;
+--------+----------+
| userid | username |
+--------+----------+
| 88 | 中文 |
+--------+----------+
1 row in set (0.00 sec)
mysql>