现在我有个表A,表A里面有一个字段如ENGLISH,如下:
ENGLISH EXPLAINabaseChineseIBMbook....现在我需要查找ENGLISH字段里面的所有数据 结果按升序排列,但是要忽略英文字母的大小写,这个语句应该怎么写?select ENGLISH from A order by ENGLISH ASC 这样写的话,字母还是区分了大小写,比较急。在线等。
ENGLISH EXPLAINabaseChineseIBMbook....现在我需要查找ENGLISH字段里面的所有数据 结果按升序排列,但是要忽略英文字母的大小写,这个语句应该怎么写?select ENGLISH from A order by ENGLISH ASC 这样写的话,字母还是区分了大小写,比较急。在线等。
示例如下:(示例中没有主键,只是为了图方便,你自己可以调整)
1. 建一个表区分大小写
mysql> create table test_srt(id varchar(32)) DEFAULT CHARACTER SET=gbk COLLATE=gbk_bin;
Query OK, 0 rows affected (0.03 sec)
2. 插入示例数据:
mysql> insert into test_srt values('ad'), ('ABC'), ('english'), ('Chinese'), ('IBM'), ('book');
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from test_srt order by id;
+---------+
| id |
+---------+
| ABC |
| Chinese |
| IBM |
| ad |
| book |
| english |
+---------+
6 rows in set (0.00 sec)mysql> alter table test_srt DEFAULT CHARACTER SET=gbk COLLATE=gbk_chinese_ci;
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0mysql> select * from test_srt order by id;
+---------+
| id |
+---------+
| ABC |
| Chinese |
| IBM |
| ad |
| book |
| english |
+---------+
6 rows in set (0.00 sec)
这时候我们看到修改排序规则不起作用(我不太确定这个算不算mysql5.0.9的bug,有兴趣可以到新版本试一下)
3. 这样的话,我们建一张新表装这些数据
mysql> create table test_srt2(id varchar(32)) DEFAULT CHARACTER SET=gbk COLLATE=gbk_chinese_ci;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test_srt2 select * from test_srt;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0mysql> select * from test_srt2 order by id;
+---------+
| id |
+---------+
| ABC |
| ad |
| book |
| Chinese |
| english |
| IBM |
+---------+
6 rows in set (0.00 sec)
这时我们看到,排序以后不区分大小写。4. 表重命名
mysql> alter table test_srt rename test_srt_old;
Query OK, 0 rows affected (0.00 sec)mysql> alter table test_srt2 rename test_srt;
Query OK, 0 rows affected (0.01 sec)
好像就不会区分大小写的,试试看,换个collation