Ép kiểu và cập nhật giá trị số từ cột chuỗi chỉ khi hợp lệ trong MySQL

Giới thiệu

Sử dụng CAST() kết hợp CEIL() để chuyển đổi cột VARCHAR chứa hỗn hợp số và chữ sang số nguyên, gán 0 cho giá trị không phải số.

1. Tạo bảng và dữ liệu

mysql> create table DemoTable (
    -> Value varchar(20),
    -> UpdateValue int
    -> ); 
Query OK, 0 rows affected (1.08 sec)

mysql> insert into DemoTable(Value) values('100'); 
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable(Value) values('false'); 
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable(Value) values('true'); 
Query OK, 1 row affected (0.07 sec)
mysql> insert into DemoTable(Value) values('1'); 
Query OK, 1 row affected (0.07 sec)

2. Dữ liệu ban đầu

mysql> select *from DemoTable;

+-------+-------------+
| Value | UpdateValue |
+-------+-------------+
| 100   |        NULL |
| false |        NULL |
| true  |        NULL |
| 1     |        NULL |
+-------+-------------+
4 rows in set (0.00 sec)

3. Cập nhật với CAST và CEIL

mysql> update DemoTable
    -> set UpdateValue = ceil(cast(Value AS char(7))); 
Query OK, 4 rows affected (0.18 sec)
Rows matched: 4 Changed: 4 Warnings: 0

Cơ chế:

• CAST(Value AS CHAR(7)): Ép kiểu sang chuỗi (đảm bảo độ dài)
• CEIL(): Làm tròn lên. MySQL tự chuyển chuỗi số thành số, chuỗi không phải số thành 0
• '100' → 100, '1' → 1, 'true'/'false' → 0

4. Kết quả

mysql> select *from DemoTable;

+-------+-------------+
| Value | UpdateValue |
+-------+-------------+
| 100   |         100 |
| false |           0 |
| true  |           0 |
| 1     |           1 |
+-------+-------------+
4 rows in set (0.00 sec)

Cách an toàn hơn (MySQL 8.0+): Dùng REGEXP kiểm tra trước:

UPDATE DemoTable
SET UpdateValue = CASE 
    WHEN Value REGEXP '^[0-9]+$' THEN CAST(Value AS UNSIGNED)
    ELSE 0
END;