mysql date_format 中文问题

[cfd406635982]

mysql> select date_format(create_date,'%Y年%m') as '月份',count(*) from reg_users 
group by date_format(create_date,'%Y年%m');
+------+----------+
| 月份    | count(*) |
+------+----------+
| 2009 |       15 |
+------+----------+
1 row in set, 16 warnings (0.00 sec)

mysql> select date_format(create_date,'%Ynian%m') as '月份',count(*) from reg_us
ers group by date_format(create_date,'%Ynian%m');
+------------+----------+
| 月份          | count(*) |
+------------+----------+
| 2009nian05 |        1 |
| 2009nian07 |        3 |
| 2009nian08 |        3 |
| 2009nian09 |        1 |
| 2009nian10 |        7 |
+------------+----------+
5 rows in set (0.00 sec)

如上，为什么在select中date_format 有中文就不行了？
但是如果直接如下操作则可以：

mysql> select date_format(now(),'%Y年') from dual;
+---------------------------+
| date_format(now(),'%Y年')   |
+---------------------------+
| 2010年                      |
+---------------------------+
1 row in set (0.00 sec)

[cfd406635982]

版本：mysql 5.13

drop table if exists reg_users;

CREATE TABLE REG_USERS
 (
   ID int,
   NAME VARCHAR(30),
   CREATE_DATE datetime
 );

Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (2, 'lisa', str_to_date('10/27/2009 23:24:58','%m/%d/%Y %H:%i:%s'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (12, 'sd', str_to_date('05/27/2009 00:04:00', '%m/%d/%Y %H:%i:%s'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (13, 'lisi', str_to_date('08/27/2009 02:02:00', '%m/%d/%Y %H:%i:%s'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (23, 'skote', str_to_date('08/27/2009 23:52:58', '%m/%d/%Y %H:%i:%s'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (1, 'zhangsan', str_to_date('09/27/2009 23:52:58', '%m/%d/%Y %H:%i:%s'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (3, 'wangwu', str_to_date('10/27/2009 23:52:58', '%m/%d/%Y %H:%i:%s'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (4, 'zhanglong', str_to_date('08/27/2009 23:52:58', '%m/%d/%Y %H:%i:%s'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (5, 'zhaohu', str_to_date('10/27/2009 23:52:58', '%m/%d/%Y %H:%i:%s'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (6, 'wangchao', str_to_date('07/27/2009 23:52:58', '%m/%d/%Y %H:%i:%s'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (7, 'mahan', str_to_date('10/27/2009 23:52:58', '%m/%d/%Y %H:%i:%s'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (8, 'zhanzhao', str_to_date('07/27/2009 23:52:58', '%m/%d/%Y %H:%i:%s'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (9, 'jojoy', str_to_date('10/27/2009 23:52:58', '%m/%d/%Y %H:%i:%s'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (10, 'lisi', str_to_date('10/27/2009 23:52:58', '%m/%d/%Y %H:%i:%s'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (11, 'sd', str_to_date('10/27/2009 23:52:58', '%m/%d/%Y %H:%i:%s'));
Insert into REG_USERS
   (ID, NAME, CREATE_DATE)
 Values
   (22, 'lisi', str_to_date('07/27/2009 23:53:25', '%m/%d/%Y %H:%i:%s'));

[ACMAIN_CHM]

按下贴中的方法检查你的字符集相关设置。

http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/12/4174186.aspx
MySQL 中文显示乱码

[cfd406635982]

哦 好了 确实是乱码的问题。。
set names 'gbk';
然后再执行就好了。