有个学生问到mysql中文按拼音排序的问题,因是j2ee应用,最好保持原应用的页面和数据库编码utf-8不变。现贴出自己的方案如下,不知是否有其他就更好的方法希朋友们指出?1.说明:
windowXP
版本:mysql5.1.14
jdbc驱动:mysql-connector-java-5.0.4-bin.jar
jdbc URL: jdbc:mysql://localhost/test?user=root&password=root
页面编码utf-8:<%@ page language="java" import="java.util.*"
contentType="text/html; charset=utf-8" pageEncoding="UTF-8"%>2.mysql数据库字符集:
方式一:
启动服务:mysqld --default-character-set=utf8
方式二:
在my.ini中设置[mysqld]小节:default-character-set=utf8
,然后启动mysql服务。3.创建测试库、表:
create database test_db
use test
create table test(
name varchar(20)
)character set gb2312 collate gb2312_chinese_ci;
alter table test character set utf8;
4.核实测试表:
show create table test;
+-------+-------------------------------------------------
| Table | Create Table
+-------+-------------------------------------------------
| test | CREATE TABLE `test` (
`name` varchar(20) CHARACTER SET gb2312 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------
存储引擎根据实际情况可以修改为innoDB
5.插入测试数据:
mysql>set names gb2312;
mysql>show variables where variable_name like '%character%';
+--------------------------+-------------------------------------------
| Variable_name | Value
+--------------------------+-------------------------------------------
| character_set_client | gb2312
| character_set_connection | gb2312
| character_set_database | utf8
| character_set_filesystem | binary
| character_set_results | gb2312
| character_set_server | utf8
| character_set_system | utf8
insert into test values('必');
insert into test values('吴');
insert into test values('在');
insert into test values('哦');
insert into test values('恶');
insert into test values('是');
insert into test values('提');
insert into test values('阿'); select * from test ordber by name;6.从jsp页面上检索、提交中文数据,正常。
windowXP
版本:mysql5.1.14
jdbc驱动:mysql-connector-java-5.0.4-bin.jar
jdbc URL: jdbc:mysql://localhost/test?user=root&password=root
页面编码utf-8:<%@ page language="java" import="java.util.*"
contentType="text/html; charset=utf-8" pageEncoding="UTF-8"%>2.mysql数据库字符集:
方式一:
启动服务:mysqld --default-character-set=utf8
方式二:
在my.ini中设置[mysqld]小节:default-character-set=utf8
,然后启动mysql服务。3.创建测试库、表:
create database test_db
use test
create table test(
name varchar(20)
)character set gb2312 collate gb2312_chinese_ci;
alter table test character set utf8;
4.核实测试表:
show create table test;
+-------+-------------------------------------------------
| Table | Create Table
+-------+-------------------------------------------------
| test | CREATE TABLE `test` (
`name` varchar(20) CHARACTER SET gb2312 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------
存储引擎根据实际情况可以修改为innoDB
5.插入测试数据:
mysql>set names gb2312;
mysql>show variables where variable_name like '%character%';
+--------------------------+-------------------------------------------
| Variable_name | Value
+--------------------------+-------------------------------------------
| character_set_client | gb2312
| character_set_connection | gb2312
| character_set_database | utf8
| character_set_filesystem | binary
| character_set_results | gb2312
| character_set_server | utf8
| character_set_system | utf8
insert into test values('必');
insert into test values('吴');
insert into test values('在');
insert into test values('哦');
insert into test values('恶');
insert into test values('是');
insert into test values('提');
insert into test values('阿'); select * from test ordber by name;6.从jsp页面上检索、提交中文数据,正常。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货