mysql 实现按首字母字典序排序以及根据字段汉字首字母搜索

mysql 实现按首字母字典序排序

mysql 一个表中的某一个字段存储的是人的名字 name 字符编码utf8_general_ci 单纯的order by name是无法实现排序的功能。

最后通过 order by convert(name USING gbk) 实现了排序的功能

mysql 根据字段汉字首字母搜索

如果是第一个汉字可以

  select * from 表名 where ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(`字段名`USING gbk),1)),16,10), 0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6, 0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1), 'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P', 'Q','R','S','T','W','X','Y','Z'
) = '字母'

如果取第二个字符首字母可以

sql += " and ELT(INTERVAL(CONV(HEX(LEFT(Convert(substring(字段名,2,1) USING gbk),1)),16,10), 0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6, 0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1), 'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P', 'Q','R','S','T','W','X','Y','Z') = '"+m[0]+"' order by convert(字段名 USING gbk)";