还是字符集问题

Question

-- 官方文档：
-- 默认数据库的字符集和校对规则可以用作character_set_database和collation_database系统变量。
-- 无论何时默认数据库更改了，服务器都设置这两个变量的值。
-- 如果没有 默认数据库，这两个变量与相应的服务器级别的变量
--（character_set_server和collation_server）具有相同的值。
root:bbbb>show variables like 'char%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8                                                    |
| character_set_connection | utf8                                                    |
| character_set_database   | gbk                                                     |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8                                                    |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.00 sec)root:bbbb>create database bbbb1;
Query OK, 1 row affected (0.02 sec)root:bbbb>show create database bbbb1;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| bbbb1    | CREATE DATABASE `bbbb1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)-- 这时database 默认字符集是latin1 ，按照官方文档说,应该是character_set_database的值啊，为什么还是用的 character_set_system？？？？root:bbbb>use bbbb1;
Database changed-- 但是root:bbbb1>create table aa1(
    -> di int
    -> );
Query OK, 0 rows affected (0.05 sec)root:bbbb1>show create table aa1;
+-------+-----------------------------------------------------------------------------------------+
| Table | Create Table                                                                            |
+-------+-----------------------------------------------------------------------------------------+
| aa1   | CREATE TABLE `aa1` (
  `di` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)-- 这个表的默认字符集怎么也是latin1 ？？ 我已经设置了 character_set_database=gbk了啊。-- 按我个人的想法，应该表的默认字符集应该跟character_set_database一样啊。（我认为从上到下继承是关系）

Answer 1

参考一下这个贴子中的说明。http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/12/4174186.aspx
MySQL 中文显示乱码

Answer 2

character_set_database    只不过是数据库的默认字符集设置，也就是说如果在这个数据库中创建的表如果不指明字符集，则这些表的默认字符集就以数据库的默认字符集为准（注意默认两个字）如果你在创建DATABASE的时候不指定其默认字符集，则就以character_set_server     为准。

Answer 3

ACMAIN_CHM 大哥好啊
那个中文乱码的帖子，我看了几遍了。都没有我想要的答案。其实是这样的你仔细看我创建的表character_set_database是默认gbk。那么说。我没有明确执行表的字符集的时候，就应该是gbk但是。却不是的 。创建的表的默认字符集是latin1。

Answer 4

刚才就是解释这个问题。为什么你的DATABASE是LATIN1
在你 use bbbb1; 后再 show variables like 'char%'; 贴出来看一下。

Answer 5

root:bbbb1>show variables like 'char%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8                                                    |
| character_set_connection | utf8                                                    |
| character_set_database   | latin1                                                  |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8                                                    |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.00 sec)  我看了半天，我还真没明白，为什么character_set_database 值会变  character_set_database随着character_set_server变？  那请问character_set_database有什么用呢？  

Answer 6

没什么杯具的，验证一下就知道了：
1. 初始值都为gbk
mysql> show variables like 'char%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | gbk                                       |
| character_set_connection | gbk                                       |
| character_set_database   | gbk                                       |
| character_set_results    | gbk                                       |
| character_set_server     | gbk                                       |
| character_set_system     | utf8                                      |
| character_sets_dir       | d:\mysql-5.0.9-beta-win32\share\charsets/ |
+--------------------------+-------------------------------------------+
2. 改database charset 为'latin1'，并验证create database
mysql> set @@character_set_database='latin1'; create database foo; show create database foo;
Query OK, 0 rows affected (0.00 sec)Query OK, 1 row affected (0.00 sec)+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| foo      | CREATE DATABASE `foo` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show variables like 'character_set_database%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set (0.00 sec)
可以看到的是创建的database为gbk3. 设置server charset为latin1，并重新创建数据库
mysql> drop database foo; set @@character_set_server='latin1'; set @@character_set_database='gbk';create database foo; show create database foo;
Query OK, 0 rows affected (0.03 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 1 row affected (0.00 sec)+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| foo      | CREATE DATABASE `foo` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)mysql> show variables like 'char%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | gbk                                       |
| character_set_connection | gbk                                       |
| character_set_database   | gbk                                       |
| character_set_results    | gbk                                       |
| character_set_server     | latin1                                    |
| character_set_system     | utf8                                      |
| character_sets_dir       | d:\mysql-5.0.9-beta-win32\share\charsets/ |
+--------------------------+-------------------------------------------+
7 rows in set (0.00 sec)可以看到创建的database的字符集为latin1
这下明白了吧。

Answer 7

楼主不妨先到手册中查一下  character_set_database 系统变量是什么？手册中已经解释得很清楚了。这个是和你当前数据库有关。你USE不同的数据库自然会不同。