mysql - 在 MySQL/MariaDB 中将 CHAR 主键转换为 INT

Question

我有一个使用 CHAR 作为客户主键的表。我正在尝试将此表加载到一个模式中，这样主键应该是 INT。

DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers` (
  `customer_id` char(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `customers` VALUES ('99944'),('99946'),('99976'),('A0014'),('A0049'),('A0124'),('C01AH'),('C01AQ'),('C01AW'),('C01AX'),('C01AY'),('C01AZ');

Fiddle

我尝试了 select cast(customer_id AS UNSIGNED) FROM customers; 的变体，但只为非整数行返回 0。如何将非整数行转换为一致的 INT 结果？

理想的结果应该是这样的:对于纯整数的客户 ID，请不要管它们。对于包含任何字母的客户 ID，请将 ID 中的所有内容替换为唯一的数字标识符。

预期结果:

SELECT * FROM Customers;

`customer_id`
-------
99944
99946
99976
13871911
13871912
13871913
13872128
13872229
13872293
13872505
13872512
13872561

Answer 1

GMB 确实给了我另一个想法。
使用 HEX() 和 CONV(.., 16, 10) 将十六进制转换为十进制

查询

SELECT
   customers.customer_id
 , CASE 
    WHEN (customers.customer_id >> 0) > 0
    THEN customers.customer_id >> 0
    ELSE
     CONV(HEX(customers.customer_id), 16, 10)
    END
   AS customer_id_int
FROM 
 customers;

结果

| customer_id | customer_id_int |
| ----------- | --------------- |
| 99944       | 99944           |
| 99946       | 99946           |
| 99976       | 99976           |
| A0014       | 279981338932    |
| A0049       | 279981339705    |
| A0124       | 279981404724    |
| C01AH       | 288571343176    |
| C01AQ       | 288571343185    |
| C01AW       | 288571343191    |
| C01AX       | 288571343192    |
| C01AY       | 288571343193    |
| C01AZ       | 288571343194    |

附上它可能会生成一个很大的 int，您需要使用 BIGINT 数据类型。
见demo

已更新

另一种生成较小整数 (UNSIGNED INT) 的方法使用“SQL 数字生成器”、SUBSTRING()、ORD() 和 GROUP_CONCAT()。

查询

    SELECT 
       customers.customer_id
     CASE 
       WHEN customers.customer_id >> 1 > 0
       THEN customers.customer_id 
       ELSE
         GROUP_CONCAT(
           CASE
             WHEN SUBSTRING(customers.customer_id, number_generator.number, 1) NOT BETWEEN 'A' AND 'Z'
             THEN SUBSTRING(customers.customer_id, number_generator.number, 1) >> 1
             ELSE ORD(SUBSTRING(customers.customer_id, number_generator.number, 1))
           END
           ORDER BY 
             number_generator.number ASC
           SEPARATOR ''
         )                            
     END                            
   ) AS customer_id_int                  
    FROM (
      SELECT 
       record_1.number
      FROM (
        SELECT 1 AS number UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
      ) AS record_1   
    ) AS number_generator
    CROSS JOIN
     customers
    GROUP BY 
     customers.customer_id
    ORDER BY 
     customers.customer_id ASC

结果

| customer_id | customer_id_int |
| ----------- | --------------- |
| 99944       | 99944           |
| 99946       | 99946           |
| 99976       | 99976           |
| A0014       | 650002          |
| A0049       | 650024          |
| A0124       | 650012          |
| C01AH       | 67006572        |
| C01AQ       | 67006581        |
| C01AW       | 67006587        |
| C01AX       | 67006588        |
| C01AY       | 67006589        |
| C01AZ       | 67006590        |

参见 demo