表 num 欄位 value 儲存混合數值(整數與小數),需篩選出「值為整數」的列。
SELECT * FROM num WHERE value % 1 = 0;
原理:整數對 1 取餘為 0,小數則非 0。適用於數值型欄位(INT、DECIMAL、FLOAT、DOUBLE)。
IsNumDELIMITER $$
DROP FUNCTION IF EXISTS IsNum $$
CREATE FUNCTION IsNum(str VARCHAR(255)) RETURNS INT
BEGIN
DECLARE iResult INT DEFAULT 0;
IF ISNULL(str) THEN RETURN 0; END IF;
IF str = '' THEN RETURN 0; END IF;
SELECT str REGEXP '^[0-9]+$' INTO iResult;
RETURN iResult;
END $$
DELIMITER ;
SELECT * FROM num WHERE IsNum(value) = 1;
SELECT IsNum('123'); -- 1
SELECT IsNum('12.3'); -- 0
SELECT IsNum('abc'); -- 0
SELECT * FROM num WHERE value = CAST(value AS UNSIGNED);
或針對帶符號整數:
SELECT * FROM num WHERE value = CAST(value AS SIGNED);
| 方法 | 優點 | 缺點 | 適用場景 |
|---|---|---|---|
| value % 1 = 0 | 極簡、無函數呼叫開銷、可用索引(若 value 有索引) | 僅限數值型欄位 | 純數值欄位、高頻查詢 |
| REGEXP / IsNum | 可處理字串型數字、格式靈活 | 無法使用索引、CPU 較高 | 字串欄位、需驗證格式 |
| CAST 對比 | 標準 SQL、可用索引 | 隱式轉換規則需注意 | 通用數值欄位 |
數值欄位首選 value % 1 = 0 或 value = CAST(value AS SIGNED);字串欄位或需嚴格格式驗證時,使用 REGEXP 封裝函數。實際上線前請用 EXPLAIN 驗證是否走索引。