萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL 查詢欄位值為整數的資料技巧

問題

表 num 欄位 value 儲存混合數值(整數與小數),需篩選出「值為整數」的列。

方法一:取餘運算(最簡潔)

SELECT * FROM num WHERE value % 1 = 0;

原理:整數對 1 取餘為 0,小數則非 0。適用於數值型欄位(INT、DECIMAL、FLOAT、DOUBLE)。

方法二:正則表達式判斷(通用性強)

建立判斷函數 IsNum

DELIMITER $$
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

方法三:CAST 對比(無需建立函數)

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 驗證是否走索引。

copyright © 萬盛學電腦網 all rights reserved