mysql - mysql 在 cast() 时按大小写
问题描述
我需要对id字段进行排序，但是id字段是动态的，排序前需要判断，如果是纯数字，那么我将其转换为数字类型再排序。但是当cast（）时我有一些问题，有人可以帮助我吗？
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for BASIS_EXP_ORG
-- ----------------------------
DROP TABLE IF EXISTS `BASIS_EXP_ORG`;
CREATE TABLE `BASIS_EXP_ORG` (
`NAME` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'name',
`ORG_ID` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT 'org',
PRIMARY KEY (`ORG_ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of BASIS_EXP_ORG
-- ----------------------------
INSERT INTO `BASIS_EXP_ORG` VALUES ('OU_2', '101');
INSERT INTO `BASIS_EXP_ORG` VALUES ('INV_2', '141');
INSERT INTO `BASIS_EXP_ORG` VALUES ('OU_1', '85');
SET FOREIGN_KEY_CHECKS = 1;
----------------------------------------------------------------
select distinct ORG_ID as id,
NAME as text
from BASIS_EXP_ORG
where 1 = 1
and ORG_ID in (101,141,85)
order by
case when ORG_ID REGEXP '(^[0-9])'
then ORG_ID +0
ELSE ORG_ID END
asc
解决方案
如果您使用的是 MySQL 8+ 和 MariaDB 10+，则可以使用REGEXP_REPLACE删除字符串然后CAST它：
SELECT DISTINCT ORG_ID AS id,CAST(REGEXP_REPLACE(ORG_ID,'[a-zA-Z]','') AS SIGNED),
NAME AS TEXT
FROM BASIS_EXP_ORG
WHERE 1 = 1
ORDER BY CAST(REGEXP_REPLACE(ORG_ID,'[a-zA-Z]','') AS SIGNED) ASC;
在此处查看演示：https ://www.db-fiddle.com/f/itK8PM7WjURn5Jaynurz4N/0
我在小提琴中添加了一些行数据并删除了and ORG_ID in (101,141,85)inWHERE条件以进行测试。
编辑： 下面的这个查询应该能够让它在旧的 MySQL 上工作：
SELECT DISTINCT ORG_ID AS id,SUBSTRING(ORG_ID,
LEAST(
IF(LOCATE(1,org_id)=0,99,LOCATE(1,org_id)),
IF(LOCATE(2,org_id)=0,99,LOCATE(2,org_id)),
IF(LOCATE(3,org_id)=0,99,LOCATE(3,org_id)),
IF(LOCATE(4,org_id)=0,99,LOCATE(4,org_id)),
IF(LOCATE(5,org_id)=0,99,LOCATE(5,org_id)),
IF(LOCATE(6,org_id)=0,99,LOCATE(6,org_id)),
IF(LOCATE(7,org_id)=0,99,LOCATE(7,org_id)),
IF(LOCATE(8,org_id)=0,99,LOCATE(8,org_id)),
IF(LOCATE(9,org_id)=0,99,LOCATE(9,org_id))),99)+0 AS num,
NAME AS TEXT
FROM BASIS_EXP_ORG
WHERE 1 = 1
ORDER BY num ASC;
MySQL 5.7 演示：https ://www.db-fiddle.com/f/itK8PM7WjURn5Jaynurz4N/3
如您所见，我正在使用一系列函数从原始数据中删除字符串。首先，我使用LOCATE查找范围从 1 到 9 的数字及其位置。以下查询和结果有助于澄清更多信息：
SELECT *,
LOCATE(1,org_id),
LOCATE(2,org_id),
LOCATE(3,org_id),
LOCATE(4,org_id),
LOCATE(5,org_id),
LOCATE(6,org_id),
LOCATE(7,org_id),
LOCATE(8,org_id),
LOCATE(9,org_id) FROM BASIS_EXP_ORG;
+-------+----------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
| NAME | ORG_ID | LOCATE | LOCATE | LOCATE | LOCATE | LOCATE | LOCATE | LOCATE | LOCATE | LOCATE |
| | | (1,org_id) | (2,org_id) | (3,org_id) | (4,org_id) | (5,org_id) | (6,org_id) | (7,org_id) | (8,org_id) | (9,org_id) |
+-------+----------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
| OU_1 | 00000001 | 8 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| OU_2 | 101 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| INV_2 | 141 | 1 | 0 | 0 | 2 | 0 | 0 | 0 | 0 | 0 |
| OU_3 | 81 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 |
| OU_1 | 85 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 1 | 0 |
| INV_2 | a101 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| OU_1 | b40 | 0 | 0 | 0 | 2 | 0 | 0 | 0 | 0 | 0 |
| OU_1 | c0001 | 5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| OU_2 | c101 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+-------+----------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
LOCATE这背后的逻辑是要知道（如果有的话）中所述数字的位置在哪里。然后，如果它返回0（意味着数字不存在），给它，99所以当它LEAST发生时，它不会看到零。然而，使用的主要原因LEAST是获得找到的第一个数字而不是找到的最小数字。话虽如此，请参考上面的示例并查找带有 的数据ORG_ID=81。(LOCATE(1,org_id)在位置 2 中找到数字 1，而在位置 1 中(LOCATE(8,org_id)找到数字 8。如果我们取最小的数字来判断，那么我们将在SUBSTRING函数中得到数字 1 而不是 81（这花了我一段时间才弄清楚；P）。然后我们在中使用它们，并在最后一个检索到的值处SUBSTRING添加+0。
tl;dr > 如果我们将其转换为普通查询，我们将基本上得到如下内容：
SELECT DISTINCT ORG_ID AS id, SUBSTRING(ORG_ID,**8**,99)+0 as num
FROM BASIS_EXP_ORG
WHERE 1 = 1
AND ORG_ID='00000001'
ORDER BY num ASC;
获得**8**是所有过程发生的地方。