MariaDB Tutorial / Choose Numeric Types: Integers, DECIMAL, FLOAT, and DOUBLE

Use integer types for counts and identifiers, and consider DECIMAL when a value needs an exact decimal representation with a defined scale. FLOAT and DOUBLE store approximations: they suit many measurements, but increasing floating-point precision does not make decimal equality comparisons automatically exact.

Start with the meaning of the number

Decide whether negative values are valid, how large the number may grow, and what its fractional digits mean. An order quantity is normally an integer; a temperature measurement may be continuous. A telephone number or product code can contain only digits and still belong in a string column because leading zeros and formatting are meaningful. Storage types do not replace a calculation’s rounding and conversion policy; Calculate, Round, and Convert Numbers with ROUND and CAST compares original values with those results.

Choosing the smallest type solely to save space can create future range problems. Choosing the largest type for everything is not a design policy either. Match the actual values and operations, then add the input constraints required by the business.

Compare integer ranges and UNSIGNED

SIGNED permits negative and positive values and is the default for these integers. UNSIGNED moves the range to zero and above. INT(11) does not mean an integer with an eleven-digit storage range: the display-width notation does not enlarge INT. See the INT reference for that distinction.

Also check the application and driver: their numeric representations may not preserve every BIGINT value exactly. The database range, a programming language’s safe integer range, and a form’s accepted input range are separate limits.

Read DECIMAL precision and scale correctly

DECIMAL(6,2) has six total digits, of which two are fractional. Four digits remain for the integer part, so its largest positive value is 9999.99. DECIMAL(10,2) has eight integer digits and two fractional digits. The sign and decimal point do not count toward M.

An input of 1.235 cannot remain unchanged in a two-decimal-place column. It is rounded to fit the scale. Even in strict mode, fractional rounding can produce a warning note rather than the same error as an out-of-range value. Inspect both the stored value and SHOW WARNINGS. Exact storage does not decide when an application should round tax or discount calculations; define that policy separately. The DECIMAL reference explains precision, scale, and rounding.

Compare the same values in three numeric types

Connect as course_user to the practice server prepared in the installation guides, with the shopcourse database available. The command-line client and SQL-file guide explains the connection and SOURCE command. Run these checks inside that connection, not in the operating-system shell:

SELECT VERSION() AS server_version, DATABASE() AS selected_database;
USE shopcourse;
SELECT DATABASE() AS selected_database;

The selected database should be shopcourse. Save a complete normal example as a UTF-8 .sql file and run it with SOURCE and its actual local path. Keep the intentionally failing statements separate: an error can stop a source file. Use a learning instance, not a connection containing production data.

The complete example saves the current connection’s SQL mode and selects STRICT_TRANS_TABLES for predictable range-error checks. It changes the session, not the server’s global mode. Run this normal file before trying the separate error statements:

USE shopcourse;
SET @saved_sql_mode = @@SESSION.sql_mode;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
CREATE TABLE numeric_lab (
  item_id INT PRIMARY KEY,
  quantity TINYINT NOT NULL,
  exact_value DECIMAL(6,2) NOT NULL,
  float_value FLOAT NOT NULL,
  double_value DOUBLE NOT NULL
) ENGINE=InnoDB;
INSERT INTO numeric_lab VALUES
  (1, 10, 0.10, 0.1, 0.1),
  (2, 20, 0.20, 0.2, 0.2);
SELECT SUM(exact_value) AS exact_total,
       SUM(exact_value) = 0.30 AS exact_equal,
       SUM(float_value) = 0.30 AS float_equal,
       SUM(double_value) = 0.30 AS double_equal
FROM numeric_lab;
INSERT INTO numeric_lab VALUES (3, 127, 1.235, 1.235, 1.235);
SHOW WARNINGS;
SELECT item_id, quantity, exact_value FROM numeric_lab ORDER BY item_id;

In these comparisons, 1 is true and 0 is false. The number of decimal digits printed for an approximate result can depend on the client and expression. Observe the comparison rather than memorizing a long printed fraction. The FLOAT and DOUBLE references distinguish their precision and representation.

Try out-of-range inputs separately

After the normal example, execute these statements one at a time in the same strict-mode connection. The first exceeds signed TINYINT’s upper limit of 127; the second exceeds DECIMAL(6,2)’s integer range. Both should fail with an out-of-range error:

INSERT INTO numeric_lab VALUES (4, 128, 1.00, 1.0, 1.0);

INSERT INTO numeric_lab VALUES (4, 1, 10000.00, 1.0, 1.0);

After each failure, SELECT COUNT(*) FROM numeric_lab; should still report three rows. Do not insert intentional failures in the middle of the normal source file, where they may stop the remaining statements.

Turning strict mode off can replace a rejected input with a clipped value and a warning, losing the original value’s meaning. Read SHOW WARNINGS immediately after the statement being diagnosed. A later query can change the available diagnostics.

Review the type choice and restore the session

• Keep non-arithmetic codes and telephone numbers as strings when formatting matters.
• Choose an integer range and signedness that fit plausible growth.
• Document decimal scale and the point at which calculations are rounded.
• For approximate measurements, define an appropriate tolerance rather than relying on exact decimal equality.

SET SESSION sql_mode = @saved_sql_mode;

Restore these session settings in the same connection that saved the variables. If you disconnected, those variables no longer contain the original values; inspect the new connection instead of applying an empty saved variable.

Before repeating the exercise, use SELECT DATABASE(); and SHOW CREATE TABLE table_name;, substituting each actual table name, to confirm that the targets are the disposable tables created here. Back up any data you need to keep. Only after verifying those targets, remove them with:

DROP TABLE shopcourse.numeric_lab;

This removes both the named tables and their rows. A data-definition statement such as DROP TABLE is not undone by an ordinary ROLLBACK. You can then rerun the complete normal example. The existing products table from the first product-table exercise is deliberately outside this reset.