MySQL Tutorials - Herong's Tutorial Examples - v4.46, by Herong Yang
Table Column Types for Exact Numbers
Describes 4 types of table columns to store exact numbers: NUMERIC, DECIMAL, INTEGER and SMALLINT.
There are 7 types of table columns to store exact numbers: NUMERIC, DECIMAL, INTEGER and SMALLINT.
"NUMERIC(precision, scale)" - Exact numeric value with specified scale and specified precision.
"DECIMAL(precision, scale) or DEC(precision, scale)" - Exact numeric value with specified scale and specified precision or higher.
"INTEGER" - Exact number value with scale of 0. Precision is defined by the implementation. MySQL defines "INTEGER (or INT) with a 4-byte signed integer storage.
"SMALLINT" - Exact number value with scale of 0. Precision is defined by the implementation and should be lower than INTEGER. MySQL defines "INTEGER (or INT) with a 2-byte signed integer storage.
"MEDIUMINT" - MySQL extension to the SQL standard with a 3-byte signed integer storage.
"BIGINT" - MySQL extension to the SQL standard with a 8-byte signed integer storage.
Examples of exact number column types, ExactNumberColumns.sql:
-- ExactNumberColumns.sql -- Copyright (c) 1999 HerongYang.com. All Rights Reserved. -- CREATE TABLE ExactNumber ( Line VARCHAR(8), Price NUMERIC(8,2), -- (precision, scale) Rate DECIMAL(8,2), Population INTEGER, Quantity SMALLINT ); INSERT INTO ExactNumber VALUES ( '1', 1.11, 2.22, 33, 4 ); INSERT INTO ExactNumber VALUES ( '2', 11.111, 22.222, 3333.3, 44.4 ); INSERT INTO ExactNumber VALUES ( '3', 1111.1, 2222.2, 333333, 444 ); INSERT INTO ExactNumber VALUES ( '4', 111111.119, 222222.229, 33333333, 4444 ); INSERT INTO ExactNumber VALUES ( '5', 11111111.119, 22222222.229, 3333333333, 44444 ); SELECT * FROM ExactNumber; DROP TABLE ExactNumber;
Running ExactNumberColumns.sql on MySQL 8.0 and 5.7 servers gives you an error because the MySQL server is rejecting numeric literals that have more digits than their column precisions.
herong> %mysql%\bin\mysql --user=root --password=TopSecret test \ < ExactNumberColumns.sql ERROR 1264 (22003) at line 39: Out of range value for column 'Price' at row 1
But running ExactNumberColumns.sql on MySQL 5.6, 5.0, and 4.0 servers gives you no errors:
Line Price Rate Population Quantity 1 1.11 2.22 33 4 2 11.11 22.22 3333 44 3 1111.10 2222.20 333333 444 4 111111.12 222222.23 33333333 4444 5 9999999.99 9999999.99 2147483647 32767
Note that:
Table of Contents
MySQL Introduction and Installation
Introduction of MySQL Programs
Perl Programs and MySQL Servers
Java Programs and MySQL Servers
Character Strings and Bit Strings
►Table Column Types for Different Types of Values
Table Column Types for Character Strings
Table Column Types for Byte Strings
Table Column Types for Bit Strings
►Table Column Types for Exact Numbers
Table Column Types for Approximate Numbers
Table Column Types for Date and Time Values
Table Column Types for LOB (Large OBject)
Table Column Types for Look Up Values
Table Column Types for JSON Documents
Table Column Types for Spatial Geometry Data
Using DDL to Create Tables and Indexes
Using DML to Insert, Update and Delete Records
Using SELECT to Query Database
Window Functions for Statistical Analysis
Use Index for Better Performance
Transaction Management and Isolation Levels
Defining and Calling Stored Procedures
Variables, Loops and Cursors Used in Stored Procedures
System, User-Defined and Stored Procedure Variables
Storage Engines in MySQL Server
InnoDB Storage Engine - Primary and Secondary Indexes
Performance Tuning and Optimization
Installing MySQL Server on Linux