Data Types
When you create a new table, you must decide on the data types for each column. You already encountered some of the general column types, such as VARCHAR and INT, in Lesson 14. Now let's look at the most common data types in more detail.
|
INTEGER
An integer is a whole number. The range of values that can be stored in an integer data type depends on the size of the integerMariaDB has five different sizes:
-
TINYINT can store numbers from 128 to 127, or 0 to 255 if unsigned.
-
SMALLINT can store numbers from 32,768 to 32,767, or 0 to 65,535 if unsigned.
-
MEDIUMINT can store numbers from 8,388,608 to 8,388,607, or 0 to 16,777,215 if unsigned.
-
INTEGER or INT can store numbers from 2,147,483,648 to 2,147,483,647, or 0 to 4,294,967,295 if unsigned.
-
BIGINT can store numbers from 9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, or 0 to 18,446,744,073,709,551,615 if unsigned.
|
In the sample database, quantity is defined as TINYINT UNSIGNEDit is expected that no single order will include more than 255 of the same product. The column is declared UNSIGNED because it is nonsense to allow a negative quantity.
DECIMAL
DECIMAL is an exact, fixed-point number. It is declared as DECIMAL(M,D), where M is the total number of digits and D is the number of digits after the decimal point.
In the sample database, we used DECIMAL for both the weight and price columns on the products table. Each was declared as DECIMAL(6,2), meaning that the largest value that could be stored in each is 9999.99six numbers in total, with two appearing after the decimal point.