Computer Science · Ch 9 — Structured Query Language (SQL)
Data Type of Attribute
Data Type of Attribute
A database table is made up of attributes (columns), and every attribute must be of a specific data type. The data type tells the database two things: what kind of value can be stored in that column, and what operations are allowed on that data. For instance, you can add or multiply numbers, but you cannot multiply a name. Choosing the right data type is essential for correctness and efficiency.
MySQL provides three broad categories of data types: numeric types, date and time types, and string types. The textbook lists the most commonly used ones in a table, and each is explained below.
String Types: CHAR and VARCHAR
CHAR(n) stores fixed-length character strings. The value of n can be from 0 to 255. When you declare a column as CHAR(10), MySQL reserves space for exactly 10 characters — no matter how many characters you actually store. If you store the word 'city' (4 characters), MySQL pads the remaining 6 positions with spaces on the right. This makes CHAR very fast for data that is always the same length, like a fixed-code or a state abbreviation.
VARCHAR(n) stores variable-length character strings. Here, n can be from 0 to 65,535. Unlike CHAR, declaring VARCHAR(30) means a maximum of 30 characters can be stored, but the actual storage used depends on the length of the entered string. So 'city' in a VARCHAR(30) column will only occupy space for 4 characters (plus a small overhead to store the length). VARCHAR is more space-efficient for data with varying lengths, like names or addresses.
The key difference: CHAR wastes space by padding, but is slightly faster. VARCHAR saves space but has a tiny overhead. Use CHAR for fixed-length codes, VARCHAR for variable-length text.
Numeric Types: INT and FLOAT
INT stores whole numbers (integers). Each INT value occupies exactly 4 bytes of storage. The range of unsigned (non-negative) values that can be stored in a 4-byte integer is from 0 to 4,294,967,295. If you need numbers larger than that, you must use BIGINT, which occupies 8 bytes and can hold much larger values.
FLOAT stores numbers with decimal points (approximate numeric values). Each FLOAT value also occupies 4 bytes. It is used for scientific calculations or any data where a small amount of rounding is acceptable.
INT and FLOAT are just the basic types. MySQL also supports TINYINT, SMALLINT, MEDIUMINT, BIGINT, DOUBLE, and DECIMAL. Activity 9.2 in the textbook asks you to explore these other variants.
Date and Time Type: DATE
The DATE type is used to store calendar dates. The standard format is 'YYYY-MM-DD', where YYYY is the four-digit year, MM is the two-digit month (01 to 12), and DD is the two-digit day (01 to 31). The supported range of dates is from '1000-01-01' to '9999-12-31'. This format is unambiguous and allows MySQL to sort and compare dates correctly.
Always use the 'YYYY-MM-DD' format when inserting or updating date values in MySQL. Any other format may be misinterpreted or rejected. …
| Data type | Description |
|---|---|
| CHAR(n) | Specifies character type data of length n where n could be any value from 0 to 255. CHAR is of fixed length, means, declaring CHAR (10) implies to reserve spaces for 10 characters. If data does not have 10 characters (e.g., 'city' has four characters), MySQL fills the remaining 6 characters with spaces padded on the right. |
| VARCHAR(n) | Specifies character type data of length where n could be any value from 0 to 65535. But unlike CHAR, VARCHAR(n) is a variable-length data type. That is, declaring VARCHAR (30) means a maximum of 30 characters can be stored but the actual allocated bytes will depend on the length of entered string. So 'city' in VARCHAR (30) will occupy space needed to store 4 characters only. |
| INT | INT specifies an integer value. Each INT value occupies 4 bytes of storage. The range of unsigned values allowed in a 4 byte integer type are 0 to 4,294,967,295. For values larger than that, we have to use BIGINT, which occupies 8 bytes. |