Mysql进行中英文首字母排序和在Laravel中的应用
Mysql中英文混排字段按首字母进行排序，中文整体在前，英文在后
测试环境：mysql5.7.15，Laravel5.3版本，下面有测试用表
为了实现需求，将搜索结果进行排序，中文在前，英文在后面，都是按a-z的顺序排列，中文是按第一个汉字的拼音首字母进行的排序。
查询有两种方案：1. 根据mysql截取第一个字符，进行正则匹配，是中文的在前面 2. ascii码进行判断，>128的是中文，中文来进行降序。
创建获取汉字首字母的函数，命名为first_letter
CREATE FUNCTION first_letter(P_NAME VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
BEGIN
DECLARE V_RETURN VARCHAR(255);
SET V_RETURN = ELT(INTERVAL(CONV(HEX(left(CONVERT(P_NAME 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');
RETURN V_RETURN;
END
查询语句
SELECT * FROM system_logs ORDER BY CASE WHEN ascii(action) >=128 THEN 0 ELSE 1 END, IFNULL(first_letter(action), LEFT(action, 1)) ASC
或者
SELECT * FROM system_logs ORDER BY LEFT(action, 1) REGEXP "[u0391-uFFE5]", IFNULL(first_letter(action), LEFT(action, 1)) ASC
在laravel5.3中使用：、
正则和ascii判断是选其中之一使用，推荐使用ascii，正则会很影响效率
//->orderByRaw('left(action, 1) REGEXP "[u0391-uFFE5]"', 'asc')
->orderByRaw('CASE WHEN ascii(action) >=128 THEN 0 ELSE 1 END')
->orderByRaw('IFNULL(FirstPinyin(action), left(action, 1))', 'asc')->get();
数据库：
CREATE TABLE `system_logs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`operator` int(11) NOT NULL COMMENT '操作人员id',
`role_ids` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT '角色ids组',
`action` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT '操作动作',
`ip` int(11) NOT NULL COMMENT 'ip地址',
`others` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT '其他',
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- ----------------------------
-- Records of system_logs
-- ----------------------------
INSERT INTO `system_logs` VALUES ('1', '1', '1,2,5,6,7', '查看测试权限页面', '2130706433', '', '2016-12-13 14:01:02', '2016-12-13 14:01:02', null);
INSERT INTO `system_logs` VALUES ('2', '1', '1,2,5,6,7', '查看全部权限', '2130706433', '', '2016-12-13 14:02:08', '2016-12-13 14:02:08', null);
INSERT INTO `system_logs` VALUES ('3', '1', '1,2,5,6,7', '删除id为2的操作日志', '2130706433', '', '2016-12-13 14:02:44', '2016-12-13 14:02:44', null);
INSERT INTO `system_logs` VALUES ('4', '1', '1,2,5,6,7', '查看全部的配置', '2130706433', '', '2016-12-13 14:03:42', '2016-12-13 14:03:42', null);
INSERT INTO `system_logs` VALUES ('5', '1', '1,2,5,6,7', '修改配置文件', '2130706433', '', '2016-12-13 14:03:54', '2016-12-13 14:03:54', null);
INSERT INTO `system_logs` VALUES ('6', '1', '1,2.5,6,7', 'Ad operator', '2130706433', 'just test order ', '2016-12-13 14:11:52', '2016-12-13 14:11:54', null);
INSERT INTO `system_logs` VALUES ('7', '1', '1,2.5,6,7', 'System Setting', '2130706433', 'modify end fiel', '2016-12-13 14:19:03', '2016-12-13 14:19:06', null);
INSERT INTO `system_logs` VALUES ('8', '1', '1,2.5,6,7', 'Xdebug', '2130706433', 'Xdebug profix', '2016-12-13 14:19:42', '2016-12-13 14:19:44', null);
INSERT INTO `system_logs` VALUES ('9', '1', '1,2,5,6,7', 'Z阿一', '2130706433', '阿一', '2016-12-13 14:31:53', '2016-12-13 14:31:55', null);
INSERT INTO `system_logs` VALUES ('10', '1', '1,2,5,6,7', '安宁ABCD', '2130706433', '测试中英文', '2016-12-13 16:11:46', '2016-12-13 16:11:48', null);
SET FOREIGN_KEY_CHECKS=1;
原创文章，转载请保留原地址，谢谢。