mysql 非合法数字_MySQL剥离非数字字符进行比较

白衣染霜花

获得最高评价的答案(@ user1467716)并不是最快的。为他们提出反弹的工作建议而向他们致以崇高的敬意！这是一个改进的版本：DELIMITER ;;DROP FUNCTION IF EXISTS `STRIP_NON_DIGIT`;;CREATE DEFINER=`root`@`localhost` FUNCTION `STRIP_NON_DIGIT`(input VARCHAR(255)) RETURNS VARCHAR(255) CHARSET utf8READS SQL DATABEGIN   DECLARE output    VARCHAR(255) DEFAULT '';   DECLARE iterator  INT          DEFAULT 1;   DECLARE lastDigit INT          DEFAULT 1;   DECLARE len       INT;   SET len = LENGTH(input) + 1;   WHILE iterator < len DO      -- skip past all digits      SET lastDigit = iterator;      WHILE ORD(SUBSTRING(input, iterator, 1)) BETWEEN 48 AND 57 AND iterator < len DO         SET iterator = iterator + 1;      END WHILE;      IF iterator != lastDigit THEN         SET output = CONCAT(output, SUBSTRING(input, lastDigit, iterator - lastDigit));      END IF;      WHILE ORD(SUBSTRING(input, iterator, 1)) NOT BETWEEN 48 AND 57 AND iterator < len DO         SET iterator = iterator + 1;      END WHILE;   END WHILE;   RETURN output;END;;在测试服务器上测试5000次：-- originalExecution Time : 7.389 secExecution Time : 7.257 secExecution Time : 7.506 sec-- ORD between not string INExecution Time : 4.031 sec-- With less substringsExecution Time : 3.243 secExecution Time : 3.415 secExecution Time : 2.848 sec