mysql - 如果值以逗号分隔，如何将 VARCHAR 值转换为 INT

Question

我正在使用的 MySQL 数据库有一列以逗号分隔的值，类似于 -

mysql> select * from performance;
+----+------------------+
| id | maximums         |
+----+------------------+
|  1 | 10000RPM, 60KM/h |
|  2 | 5000RPM, 30KM/h  |
|  3 | 25mph, 3000RPM   |
|  4 | 200KM/h, 2000RPM |
+----+------------------+
4 rows in set (0.00 sec)

我正在尝试将找到的数字转换到它们自己的 INT 列中。

mysql> select maximums,  
CASE WHEN maximums like "%mph%" THEN CAST(SUBSTRING_INDEX(maximums, 'mph', 1) AS UNSIGNED) END AS mph_int, 
CASE WHEN maximums like "%KM/h%" THEN CAST(SUBSTRING_INDEX(maximums, 'KM/h', 1) AS UNSIGNED) END AS kmh_int, 
CASE WHEN maximums like "%RPM%" THEN CAST(SUBSTRING_INDEX(maximums, 'RPM', 1) AS UNSIGNED) END AS rpm_int 
from performance;
+------------------+---------+---------+---------+
| maximums         | mph_int | kmh_int | rpm_int |
+------------------+---------+---------+---------+
| 10000RPM, 60KM/h |    NULL |   10000 |   10000 |
| 5000RPM, 30KM/h  |    NULL |    5000 |    5000 |
| 25mph, 3000RPM   |      25 |    NULL |      25 |
| 200KM/h, 2000RPM |    NULL |     200 |     200 |
+------------------+---------+---------+---------+
4 rows in set, 4 warnings (0.00 sec)

我希望输出在新列中以 INT 形式显示值，但我不确定如何实现这一点。

Answer 1

让我们尝试一下，使用老式的钝器方法。我猜你只需要这个工作一次，将旧的、设计不良的模式转换为更可行的模式。鉴于此，我没有在优雅或性能方面做出任何努力。

(如果您不使用它来修复您的数据架构，那么您应该使用它，因为您现在经历的痛苦只是一个开始。)

首先，我们需要将maximums值分成两部分并分别处理。上半部分是:

SUBSTRING_INDEX(`maximum`, ',', 1)

后半部分类似，但有一个杂散空间:

TRIM(SUBSTRING_INDEX(`maximum`, ',', -1))

从现在开始，让我们始终进行修剪，以防数据出现变化。现在我们需要查看第一部分中是否有“mph”，如果有，则捕获您在问题中所做的值(这本质上与您的示例类似，但仅对最大值的第一部分进行操作值):

IF(TRIM(SUBSTRING_INDEX(`maximum`, ',', 1)) LIKE '%mph', SUBSTRING_INDEX(TRIM(SUBSTRING_INDEX(`maximum`, ',', 1)), 'mph', 1), NULL)

让我们将该代码块命名为“上半场的每小时测试”。下半场的英里/小时测试几乎相同，只是使用-1作为索引。最后，我们需要使用 COALESCE 将非空值(如果有)放入列中。一旦我们创建了测试的所有六个变体，我们最终会得到以下结果:

SELECT 
    ...
    COALESCE([mph test on first half], [mph test on second half]) AS mph_int, 
    COALESCE([kph test on first half], [kph test on second half]) AS kph_int,
    COALESCE([rpm test on first half], [rpm test on second half]) AS rpm_int
WHERE
    ...

您实际上并不需要将数字字符串正式转换为整数；如果您要插入包含这些类型的列的表，MySQL 将为您转换该值。