mysql将逗号分隔的数字加减乘除

你可以使用 MySQL 中的字符串函数和数学函数来实现将逗号分隔的数字加减乘除。具体方法如下：

1. 使用 REPLACE 函数将逗号替换为空格，然后使用 TRIM 函数去除空格：

SELECT TRIM(REPLACE('1,2,3,4,5', ',', ' '));

2. 使用 FIND_IN_SET 函数将字符串转换为集合，然后使用数学运算符实现加减乘除操作：

加法：

SELECT SUM(FIND_IN_SET(num, '1,2,3,4,5'))
FROM (
  SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(str, ' ', n), ' ', -1)) AS num
  FROM (
    SELECT TRIM(REPLACE('1,2,3,4,5', ',', ' ')) AS str
  ) AS t1
  CROSS JOIN (
    SELECT a.N + b.N * 10 + 1 AS n
    FROM (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS a
    CROSS JOIN (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS b
    ORDER BY n
  ) AS t2
  WHERE n <= 1 + (LENGTH(str) - LENGTH(REPLACE(str, ' ', '')))
) AS t3;

减法：

SELECT (FIND_IN_SET('1', '1,2,3,4,5') - 1) - SUM(FIND_IN_SET(num, '1,2,3,4,5'))
FROM (
  SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(str, ' ', n), ' ', -1)) AS num
  FROM (
    SELECT TRIM(REPLACE('1,2,3,4,5', ',', ' ')) AS str
  ) AS t1
  CROSS JOIN (
    SELECT a.N + b.N * 10 + 1 AS n
    FROM (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS a
    CROSS JOIN (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS b
    ORDER BY n
  ) AS t2
  WHERE n <= 1 + (LENGTH(str) - LENGTH(REPLACE(str, ' ', '')))
) AS t3;

乘法：

SELECT EXP(SUM(LOG(FIND_IN_SET(num, '1,2,3,4,5'))))
FROM (
  SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(str, ' ', n), ' ', -1)) AS num
  FROM (
    SELECT TRIM(REPLACE('1,2,3,4,5', ',', ' ')) AS str
  ) AS t1
  CROSS JOIN (
    SELECT a.N + b.N * 10 + 1 AS n
    FROM (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS a
    CROSS JOIN (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS b
    ORDER BY n
  ) AS t2
  WHERE n <= 1 + (LENGTH(str) - LENGTH(REPLACE(str, ' ', '')))
) AS t3;

除法：

SELECT FIND_IN_SET('1', '1,2,3,4,5') / SUM(FIND_IN_SET(num, '1,2,3,4,5'))
FROM (
  SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(str, ' ', n), ' ', -1)) AS num
  FROM (
    SELECT TRIM(REPLACE('1,2,3,4,5', ',', ' ')) AS str
  ) AS t1
  CROSS JOIN (
    SELECT a.N + b.N * 10 + 1 AS n
    FROM (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS a
    CROSS JOIN (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS b
    ORDER BY n
  ) AS t2
  WHERE n <= 1 + (LENGTH(str) - LENGTH(REPLACE(str, ' ', '')))
) AS t3;

这样就可以将逗号分隔的数字加减乘除了。