Introduction

In MySQL, "is this value an integer" can mean different things. You might be asking whether a column is defined with an integer type, whether a numeric expression has no fractional part, or whether a string looks like an integer literal.

Check the schema when the column type is the question

If the real question is "is this column an integer column," inspect the schema, not the row values.

1SELECT COLUMN_NAME, DATA_TYPE
2FROM INFORMATION_SCHEMA.COLUMNS
3WHERE TABLE_SCHEMA = 'app_db'
4  AND TABLE_NAME = 'orders'
5  AND COLUMN_NAME = 'customer_id';

If the result shows int, bigint, smallint, mediumint, or tinyint, then the column is integer-typed by schema definition.

This is the correct approach when you are auditing a table definition or generating dynamic SQL based on column metadata.

Check whether a numeric value is mathematically an integer

If the value is already numeric and you want to know whether it has a fractional part, compare it with a truncated form.

1SELECT
2  value,
3  value = FLOOR(value) AS is_integer
4FROM (
5  SELECT 5.0 AS value
6  UNION ALL SELECT 5.7
7  UNION ALL SELECT -3.0
8  UNION ALL SELECT -3.2
9) AS t;

This works because values like 5 and 5.0 are still whole numbers mathematically, while 5.7 is not.

You can express the same idea with MOD:

1SELECT
2  value,
3  MOD(value, 1) = 0 AS is_integer
4FROM (
5  SELECT 10.0 AS value
6  UNION ALL SELECT 10.25
7) AS t;

That approach is fine when the input is already numeric.

Validate text values separately

If the value lives in a text column, numeric functions can be misleading because MySQL performs implicit conversion. For example, a string like '12abc' may be coerced to 12 in numeric contexts, which does not mean the original string is a valid integer literal.

For text validation, a regular expression is safer:

1SELECT
2  raw_value,
3  raw_value REGEXP '^[+-]?[0-9]+$' AS looks_like_integer
4FROM (
5  SELECT '42' AS raw_value
6  UNION ALL SELECT '-7'
7  UNION ALL SELECT '+19'
8  UNION ALL SELECT '3.14'
9  UNION ALL SELECT '12abc'
10  UNION ALL SELECT ''
11) AS t;

This checks whether the string is made of an optional sign followed by digits only.

That is usually what you want when cleaning imported CSV data or validating weakly typed input stored in VARCHAR.

Casting can help, but it is not validation by itself

Some people try to detect integers with casting:

SELECT CAST('42' AS SIGNED);

This converts the value, but it does not prove the original text was clean. MySQL may still accept partially numeric strings. That is why regex-based validation is often better for textual input.

Use casting when you want conversion. Use regex when you want strict validation.

Handle NULL deliberately

Decide what NULL should mean in your application. It is not an integer value, but it may need to be treated separately from invalid text.

For example:

1SELECT
2  raw_value,
3  CASE
4    WHEN raw_value IS NULL THEN NULL
5    WHEN raw_value REGEXP '^[+-]?[0-9]+$' THEN 1
6    ELSE 0
7  END AS is_integer
8FROM your_table;

This preserves the difference between "missing value" and "value is present but not an integer."

Pick the right question before writing the query

A lot of confusion comes from trying to use one test for three different jobs:

• schema inspection
• numeric integrality
• string validation

Those are not the same problem, so they should not use the same solution. Once you define which one you actually need, the SQL becomes much clearer.

Common Pitfalls

The biggest mistake is applying numeric tests to strings and trusting MySQL’s implicit conversions. That can make invalid text appear valid because MySQL extracts a numeric prefix.

Another issue is treating 5.0 as non-integer just because it has a decimal representation. If the numeric value has no fractional part, it is still an integer mathematically.

Developers also forget the schema-versus-value distinction. A column can be typed as VARCHAR while containing text that looks like integers, or a numeric column can contain non-integer numeric values such as DECIMAL(10,2).

Finally, do not forget to define rules for signs, blanks, and NULL. Integer validation is only correct when those domain rules are explicit.

Summary

• Use INFORMATION_SCHEMA when you want to know whether a column is integer-typed.
• Use FLOOR(value) or MOD(value, 1) when checking numeric values for a fractional part.
• Use REGEXP when validating whether text looks like an integer literal.
• Do not rely on implicit numeric conversion for strict text validation.
• Decide up front whether you are checking schema, numeric value, or string content.