db-practise/daten-manipulieren/datatypes.md

MySQL Data Types Cheatsheet

1. Numeric Data Types

Integer Types

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

Floating-Point Types

Data Type	Storage	Range	Precision
FLOAT(m,d)	4 bytes	Approx. ±3.4E38	Rounded due to floating-point approximation
DOUBLE(m,d) / REAL	8 bytes	Approx. ±1.8E308	Higher precision than FLOAT but still subject to rounding
DECIMAL(m,d) / NUMERIC(m,d)	Varies	Exact fixed-point values	Precise representation, no rounding errors

Floating-Point Precision Considerations

• FLOAT and DOUBLE are subject to rounding errors due to floating-point representation and should not be used where exact precision is required (e.g., financial applications).
• DECIMAL is a fixed-point type that maintains exact precision and is ideal for storing monetary values, making it the preferred choice for banking applications.

2. String Data Types

Data Type	Storage	Description
CHAR(n)	1 byte per character	Fixed-length string (0-255 characters)
VARCHAR(n)	1 byte per character + 1 or 2 bytes	Variable-length string (0-65,535 characters)
TEXT	Varies	Large text field (0-4GB depending on type)
TINYTEXT	Up to 255 bytes	Small text field
TEXT	Up to 65,535 bytes	Standard text field
MEDIUMTEXT	Up to 16,777,215 bytes	Medium text field
LONGTEXT	Up to 4GB	Very large text field
BLOB	Varies	Binary large object (same size variations as TEXT)

3. Date and Time Data Types

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

4. Boolean Type

Data Type	Alias For	Description
BOOLEAN	TINYINT(1)	Stores 0 (false) or 1 (true)

5. Spatial Data Types

Data Type	Description
GEOMETRY	Stores any spatial value
POINT	Stores (X,Y) coordinate
LINESTRING	Stores a line of multiple points
POLYGON	Stores a polygon

6. JSON Data Type

Data Type	Description
JSON	Stores JSON-formatted data efficiently

Notes:

• Use UNSIGNED for numeric types when negative values are not needed.
• DECIMAL(m,d) is preferred for exact decimal values (e.g., financial applications).
• Use TEXT for large text fields instead of VARCHAR when indexing is not required.
• TIMESTAMP is useful for automatic time zone conversion, while DATETIME is better for absolute timestamps.
• JSON is useful for semi-structured data storage.