在 my.ini 中的[mysqld]节下加入一个default-character-set=gb2312 ---------------------------------------------------------------- 我试过了,可是还是一样。我又添加了很多记录,现在的排序更乱了,比如:姓刘的中间夹着姓麦的,还有在查询的时候 我打入一个关键字,比如“梁”(是想找出姓梁的),结果会多找出一个叫“陈海同”的。真是奇怪。是我的sql语句有问题么? select * from address where name like '%$s_name%' order by name s_name是html页的单行文本编辑器的name
字段不要加 Binary 属性
我的每一个字段都没设Binary 属性!!!
字段 类型 属性 Null 缺省值 额外 执行操作 id int(11) 否 auto_increment 改变 丢弃 键名 索引 唯一 全文检索 name varchar(8) 否 - 改变 丢弃 键名 索引 唯一 全文检索 sex char(2) 是 - 改变 丢弃 键名 索引 唯一 全文检索 com varchar(30) 是 - 改变 丢弃 键名 索引 唯一 全文检索 hphone varchar(20) 是 - 改变 丢弃 键名 索引 唯一 全文检索 ophone varchar(20) 是 - 改变 丢弃 键名 索引 唯一 全文检索 mobile varchar(11) 是 - 改变 丢弃 键名 索引 唯一 全文检索 bp varchar(15) 是 - 改变 丢弃 键名 索引 唯一 全文检索 email varchar(20) 是 - 改变 丢弃 键名 索引 唯一 全文检索 以下是my.in文件: # Example mysql config file. # Copy this file to c:\my.cnf to set global options # # One can use all long options that the program supports. # Run the program with --help to get a list of available options# This will be passed to all mysql clients [client] #password=my_password port=3306 #socket=MySQL# Here is entries for some specific programs # The following values assume you have at least 32M ram# The MySQL server [mysqld] port=3306 #socket=MySQL skip-locking # default-character-set=latin1 default-character-set=gb2312 set-variable = key_buffer=16M set-variable = max_allowed_packet=1M set-variable = thread_stack=128K set-variable = flush_time=1800 skip-innodb# Uncomment the following rows if you move the MySQL distribution to another # location basedir = c:/apache/mysql/ datadir = c:/apache/mysql/data/# Uncomment the following rows if you are using InnoDB tables # Create the directories ibdata and iblogs before to start the server. # If necessary, modify the size of the files#innodb_data_file_path = ibdata1:200M #innodb_data_home_dir = c:\ibdata #set-variable = innodb_mirrored_log_groups=1 #innodb_log_group_home_dir = c:\iblogs #set-variable = innodb_log_files_in_group=3 #set-variable = innodb_log_file_size=30M #set-variable = innodb_log_buffer_size=8M #innodb_flush_log_at_trx_commit=1 #innodb_log_arch_dir = c:\iblogs #innodb_log_archive=0 #set-variable = innodb_buffer_pool_size=80M #set-variable=lower_case_table_names =0 #set-variable = innodb_additional_mem_pool_size=10M #set-variable = innodb_file_io_threads=4 #set-variable = innodb_lock_wait_timeout=50# Uncomment the following row if you are using a Max server and you don't want the # InnoDb tables#skip-innodb[mysqldump] quick set-variable = max_allowed_packet=16M[mysql] no-auto-rehash[isamchk] set-variable= key=16M[client_fltk] help_file= c:\mysql\sql_client\MySQL.help client_file= c:\mysql\MySQL.options history_length=20 database = test queries_root= c:\mysql\queries last_database_file= c:\mysql\lastdb
岑覃 这两个字是生僻字, 内码值比较大,所以排在后面一点字段要加 Binary 属性?????????? 呵呵, 你用的是哪一版的 MySQL
你用的是哪一版的 MySQL 呵呵,的确很奇怪, 我仍认为是不可能的,可能是你的某些问题没注意 mysql> SELECT VERSION(); +---------------+ | VERSION() | +---------------+ | 4.0.13-nt-log | +---------------+ 1 row in set (0.00 sec)mysql> SHOW VARIABLES LIKE 'character_set'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | character_set | gb2312 | +---------------+--------+ 1 row in set (0.00 sec) mysql> CREATE TABLE t1 ( -> id int(11) NOT NULL auto_increment, -> name varchar(10) default NULL, -> PRIMARY KEY (id) -> ) TYPE=MyISAM; Query OK, 0 rows affected (0.01 sec)mysql> INSERT INTO t1 VALUES -> ("1", "测试"), -> ("2", "检测"),("3", "检查"), -> ("4", "李光"),("5", "麦田"), -> ("6", "刘三"),("7", "馐三"); Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM `t1` WHERE `name` LIKE '刘%'; +----+------+ | id | name | +----+------+ | 6 | 刘三 | +----+------+ 1 row in set (0.00 sec)mysql> SELECT * FROM `t1` WHERE `name` LIKE '测%'; +----+------+ | id | name | +----+------+ | 1 | 测试 | +----+------+ 1 row in set (0.00 sec)mysql> SELECT * FROM `t1` WHERE `name` LIKE '%测'; +----+------+ | id | name | +----+------+ | 2 | 检测 | +----+------+ 1 row in set (0.00 sec)mysql> SELECT * FROM `t1` WHERE `name` LIKE '%测%'; +----+------+ | id | name | +----+------+ | 1 | 测试 | | 2 | 检测 | +----+------+ 2 rows in set (0.00 sec)mysql> SELECT * FROM `t1` WHERE `name` LIKE '%馐%'; +----+------+ | id | name | +----+------+ | 7 | 馐三 | +----+------+ 1 row in set (0.00 sec) mysql> ALTER TABLE `t1` CHANGE `name` `name` CHAR(10) BINARY; Query OK, 6 rows affected (0.02 sec) Records: 6 Duplicates: 0 Warnings: 0mysql> SELECT * FROM `t1` WHERE `name` LIKE '刘%'; +----+------+ | id | name | +----+------+ | 6 | 刘三 | +----+------+ 1 row in set (0.00 sec)mysql> SELECT * FROM `t1` WHERE `name` LIKE '测%'; +----+------+ | id | name | +----+------+ | 1 | 测试 | +----+------+ 1 row in set (0.00 sec)mysql> SELECT * FROM `t1` WHERE `name` LIKE '%测'; +----+------+ | id | name | +----+------+ | 2 | 检测 | +----+------+ 1 row in set (0.00 sec)mysql> SELECT * FROM `t1` WHERE `name` LIKE '%测%'; +----+------+ | id | name | +----+------+ | 1 | 测试 | | 2 | 检测 | +----+------+ 2 rows in set (0.00 sec)mysql> SELECT * FROM `t1` WHERE `name` LIKE '%馐%'; +----+------+ | id | name | +----+------+ | 1 | 测试 | | 7 | 馐三 | +----+------+ 2 rows in set (0.00 sec)注意 两个 mysql> SELECT * FROM `t1` WHERE `name` LIKE '%馐%'; 语句的结果差别 测试 的内码为 B2 E2 CA D4 馐 的内码为 E2 CA
当不加 Binary 属性时,上面的语句没有出错, 但加了 Binary 属性时就有问题了
可以使用 Mysql 的 locate 函数来判断。以上述问题为例,使用方法为: SELECT * FROM table WHERE locate(field,'李') > 0; 本站使用的就是这种方法,感觉还不错。:P 把您的Select语句改成这样,SELECT * FROM TABLE WHERE FIELDS LIKE BINARY '%FIND%'即可!
select * from mytable order by binary name ASC
在my.cnf(或my.ini)中加
default-character-set=gb2312
[mysqld]
节下加入一个default-character-set=gb2312
----------------------------------------------------------------
我试过了,可是还是一样。我又添加了很多记录,现在的排序更乱了,比如:姓刘的中间夹着姓麦的,还有在查询的时候
我打入一个关键字,比如“梁”(是想找出姓梁的),结果会多找出一个叫“陈海同”的。真是奇怪。是我的sql语句有问题么?
select * from address where name like '%$s_name%' order by name
s_name是html页的单行文本编辑器的name
id int(11) 否 auto_increment 改变 丢弃 键名 索引 唯一 全文检索
name varchar(8) 否 - 改变 丢弃 键名 索引 唯一 全文检索
sex char(2) 是 - 改变 丢弃 键名 索引 唯一 全文检索
com varchar(30) 是 - 改变 丢弃 键名 索引 唯一 全文检索
hphone varchar(20) 是 - 改变 丢弃 键名 索引 唯一 全文检索 ophone varchar(20) 是 - 改变 丢弃 键名 索引 唯一 全文检索 mobile varchar(11) 是 - 改变 丢弃 键名 索引 唯一 全文检索
bp varchar(15) 是 - 改变 丢弃 键名 索引 唯一 全文检索
email varchar(20) 是 - 改变 丢弃 键名 索引 唯一 全文检索 以下是my.in文件:
# Example mysql config file.
# Copy this file to c:\my.cnf to set global options
#
# One can use all long options that the program supports.
# Run the program with --help to get a list of available options# This will be passed to all mysql clients
[client]
#password=my_password
port=3306
#socket=MySQL# Here is entries for some specific programs
# The following values assume you have at least 32M ram# The MySQL server
[mysqld]
port=3306
#socket=MySQL
skip-locking
# default-character-set=latin1
default-character-set=gb2312
set-variable = key_buffer=16M
set-variable = max_allowed_packet=1M
set-variable = thread_stack=128K
set-variable = flush_time=1800
skip-innodb# Uncomment the following rows if you move the MySQL distribution to another
# location
basedir = c:/apache/mysql/
datadir = c:/apache/mysql/data/# Uncomment the following rows if you are using InnoDB tables
# Create the directories ibdata and iblogs before to start the server.
# If necessary, modify the size of the files#innodb_data_file_path = ibdata1:200M
#innodb_data_home_dir = c:\ibdata
#set-variable = innodb_mirrored_log_groups=1
#innodb_log_group_home_dir = c:\iblogs
#set-variable = innodb_log_files_in_group=3
#set-variable = innodb_log_file_size=30M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#innodb_log_arch_dir = c:\iblogs
#innodb_log_archive=0
#set-variable = innodb_buffer_pool_size=80M
#set-variable=lower_case_table_names =0
#set-variable = innodb_additional_mem_pool_size=10M
#set-variable = innodb_file_io_threads=4
#set-variable = innodb_lock_wait_timeout=50# Uncomment the following row if you are using a Max server and you don't want the
# InnoDb tables#skip-innodb[mysqldump]
quick
set-variable = max_allowed_packet=16M[mysql]
no-auto-rehash[isamchk]
set-variable= key=16M[client_fltk]
help_file= c:\mysql\sql_client\MySQL.help
client_file= c:\mysql\MySQL.options
history_length=20
database = test
queries_root= c:\mysql\queries
last_database_file= c:\mysql\lastdb
看不出来有什么问题呀,
也不好说了
你的 SELECT SQL 语句呢,你用 MySQL-Front 将数据库导出来,在其它的机器上再试一试呢
现在可以了,但还是有点小问题,其他人都能按照拼音字母排下来,有两个人不对一个姓“岑”(cen),一个姓“覃”(qin),排在了最后!不解,难道是mysql对中文支持不够好的原因?
呵呵,
你用的是哪一版的 MySQL
呵呵,的确很奇怪,
我仍认为是不可能的,可能是你的某些问题没注意
mysql> SELECT VERSION();
+---------------+
| VERSION() |
+---------------+
| 4.0.13-nt-log |
+---------------+
1 row in set (0.00 sec)mysql> SHOW VARIABLES LIKE 'character_set';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| character_set | gb2312 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> CREATE TABLE t1 (
-> id int(11) NOT NULL auto_increment,
-> name varchar(10) default NULL,
-> PRIMARY KEY (id)
-> ) TYPE=MyISAM;
Query OK, 0 rows affected (0.01 sec)mysql> INSERT INTO t1 VALUES
-> ("1", "测试"),
-> ("2", "检测"),("3", "检查"),
-> ("4", "李光"),("5", "麦田"),
-> ("6", "刘三"),("7", "馐三");
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM `t1` WHERE `name` LIKE '刘%';
+----+------+
| id | name |
+----+------+
| 6 | 刘三 |
+----+------+
1 row in set (0.00 sec)mysql> SELECT * FROM `t1` WHERE `name` LIKE '测%';
+----+------+
| id | name |
+----+------+
| 1 | 测试 |
+----+------+
1 row in set (0.00 sec)mysql> SELECT * FROM `t1` WHERE `name` LIKE '%测';
+----+------+
| id | name |
+----+------+
| 2 | 检测 |
+----+------+
1 row in set (0.00 sec)mysql> SELECT * FROM `t1` WHERE `name` LIKE '%测%';
+----+------+
| id | name |
+----+------+
| 1 | 测试 |
| 2 | 检测 |
+----+------+
2 rows in set (0.00 sec)mysql> SELECT * FROM `t1` WHERE `name` LIKE '%馐%';
+----+------+
| id | name |
+----+------+
| 7 | 馐三 |
+----+------+
1 row in set (0.00 sec)
mysql> ALTER TABLE `t1` CHANGE `name` `name` CHAR(10) BINARY;
Query OK, 6 rows affected (0.02 sec)
Records: 6 Duplicates: 0 Warnings: 0mysql> SELECT * FROM `t1` WHERE `name` LIKE '刘%';
+----+------+
| id | name |
+----+------+
| 6 | 刘三 |
+----+------+
1 row in set (0.00 sec)mysql> SELECT * FROM `t1` WHERE `name` LIKE '测%';
+----+------+
| id | name |
+----+------+
| 1 | 测试 |
+----+------+
1 row in set (0.00 sec)mysql> SELECT * FROM `t1` WHERE `name` LIKE '%测';
+----+------+
| id | name |
+----+------+
| 2 | 检测 |
+----+------+
1 row in set (0.00 sec)mysql> SELECT * FROM `t1` WHERE `name` LIKE '%测%';
+----+------+
| id | name |
+----+------+
| 1 | 测试 |
| 2 | 检测 |
+----+------+
2 rows in set (0.00 sec)mysql> SELECT * FROM `t1` WHERE `name` LIKE '%馐%';
+----+------+
| id | name |
+----+------+
| 1 | 测试 |
| 7 | 馐三 |
+----+------+
2 rows in set (0.00 sec)注意 两个 mysql> SELECT * FROM `t1` WHERE `name` LIKE '%馐%'; 语句的结果差别
测试 的内码为 B2 E2 CA D4
馐 的内码为 E2 CA
当不加 Binary 属性时,上面的语句没有出错,
但加了 Binary 属性时就有问题了
SELECT * FROM table WHERE locate(field,'李') > 0;
本站使用的就是这种方法,感觉还不错。:P
把您的Select语句改成这样,SELECT * FROM TABLE WHERE FIELDS LIKE BINARY '%FIND%'即可!