MySQL Data Types, Constraints, and Views Comprehensive Guide

Data Types in MySQL

MySQL supports various data types organized into several categories:

Category	Examples
Integer Types	TINYINT, SMALINT, MEDIUMINT, INT, BIGINT
Floating Point	FLOAT, DOUBLE
Decimal Numbers	DECIMAL
Bit Types	BIT
Date/Time Types	YEAR, TIME, DATE, DATETIME, TIMESTAMP
String Types	CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT
Enumerated Types	ENUM
Set Types	SET
Binary Types	BINARY, VARBINARY, BLOB variants
JSON Types	JSON objects and arrays
Spatial Types	GEOMETRY, POINT, LINESTRING, POLYGON

Common attributes for data types include:

Keyword	Purpose
NULL	Allows NULL values
NOT NULL	Prohibits NULL values
DEFAULT	Sets default value
PRIMARY KEY	Defines primary key
AUTO_INCREMENT	Auto-incrementing integer
UNSIGNED	Non-negative numbers
CHARACTER SET	Specifies character encoding

Integer Data Types

Overview

Integer types provide different storage capacities:

Type	Bytes	Signed Range	Unsigned Range
TINYINT	1	-128 to 127	0 to 255
SMALLINT	2	-32,768 to 32,767	0 to 65,535
MEDIUMINT	3	-8,388,608 to 8,388,607	0 to 16,777,215
INT	4	-2,147,483,648 to 2,147,483,647	0 to 4,294,967,295
BIGINT	8	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	0 to 18,446,744,073,709,551,615

Optional Attributes

Display Width: The M parameter specifies display width, requiring ZEROFILL to take effect.

CREATE TABLE sample_integers (
    x TINYINT,
    y SMALLINT,
    z MEDIUMINT,
    m INT,
    n BIGINT
);

Unsigned Attribute: The UNSIGNED modifier restricts values to non-negative numbers.

CREATE TABLE unsigned_example (
    positive_value INT UNSIGNED
);

Zero Fill: The ZEROFILL attribute pads values with leading zeros up to specified width.

Usage Recommendations

TINYINT: For limited enumeration scenarios
SMALLINT: Small statistical datasets
MEDIUMINT: Larger integer calculations
INT: General purpose, most commonly used
BIGINT: Extremely large integers

Floating Point Types

Types Overview

FLOAT: Single precision floating point
DOUBLE: Double precision floating point
REAL: Defaults to DOUBLE

Precision Considerations

Floating point types may suffer from precision errors due to binary representation limitations.

CREATE TABLE float_examples (
    single_prec FLOAT,
    double_prec DOUBLE
);

Decimal Types

Accurate Number Storage

DECIMAL provides exact numeric representation without precision loss.

CREATE TABLE accurate_numbers (
    precise_value DECIMAL(10,2)
);

When precision is critical (such as financial calculations), use DECIMAL instead of floating point types.

Date and Time Types

Available Types

Type	Bytes	Format	Range
YEAR	1	YYYY	1901-2155
TIME	3	HH:MM:SS	-838:59:59 to 838:59:59
DATE	3	YYYY-MM-DD	1000-01-01 to 9999-12-03
DATETIME	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 to 9999-12-31 23:59:59
TIMESTAMP	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00 to 2038-01-19 03:14:07

Key Differences

TIMESTAMP adjusts for time zones, while DATETIME maintains fixed values regardless of location.

String Types

Fixed vs Varible Length

CHAR(M): Fixed length, pads with spaces
VARCHAR(M): Variable length, stores actual content

Storage Considerations

Choose CHAR for consistently sized data and VARCHAR for variable-length content.

Enum and Set Types

Enumerated Values

ENUM allows selection from predefined list:

CREATE TABLE enum_example (
    season ENUM('spring', 'summer', 'fall', 'winter')
);

Set Values

SET allows multiple selections from predefined options:

CREATE TABLE set_example (
    permissions SET('read', 'write', 'execute')
);

Database Constraints

Constraint Categories

NOT NULL: Prohibits null values
UNIQUE: Ensures uniqueness across records
PRIMARY KEY: Uniquely identifies records
FOREIGN KEY: Maintains referential integrity
CHECK: Validates data conditions
DEFAULT: Provides default values

Primary Key Implementation

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL
);

Foreign Key Relationships

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Views in MySQL

Creating Views

Views provide virtual tables based on query results:

CREATE VIEW customer_summary AS
SELECT 
    customer_id,
    COUNT(*) as order_count,
    SUM(amount) as total_spent
FROM orders
GROUP BY customer_id;

Benefits of Views

Simplify complex queries
Enhance security by limiting data exposure
Provide consistent data presentation
Reduce redundancy in query logic

View Maintenance

Views reflect changes in underlying tables automatically, but require updates when base table structures change.

Best Practices

Use appropriate data types for optimal storage
Apply constraints to maintain data integrity
Leverage views for complex reporting needs
Consider performance implications of view usage
Maintain clear naming conventions for all database objects

Tags: MySQL Database Design Data Types Constraints views

Posted on Tue, 07 Jul 2026 17:25:42 +0000 by mattheww

Freaks City